003MySQL创建高性能的索引-高性能索引策略

独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

例如：下面这个查询无法使用actor_id列的索引：

mysql>select actor_id from actor where actor_id + 1  = 5;

凭肉眼容易看出where中的表达式其实等价于actor_id=4,但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化where条件的习惯，始终将索引列单独放在比较符号的一侧。

下面是另外一个常见的错误：

mysql>select ...  where to_days(current_date)-to_days(date_col) <=10;

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是通过模拟哈希索引解决。但有时候这样做还不够，还可以做些什么呢？

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指：不重复的索引值（也称为基数，cardinality）和数据表的记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以上MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，是以满足查询性能。对于BLOB,TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的”基数“应该接近于完整列的”基数“。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

找最常见的城市列表：

mysql>select count(*) as cnt,city from city group by city order by cnt desc limit 10;

注意到，上面每个值都出现了45-65次，现在查找到最频繁出现的城市前缀，先从3个前缀字母开始：

mysql>select count(*) as cnt,left(city,3) as pref from city group by pref order by cnt desc limit 10;

index2

每个前缀都比原来的城市出现的次数要多，因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验发现前缀长度为7时最为合适：

mysql>select count(*) as cnt,left(city,7) as pref from city group by pref order by cnt desc limit 10;

index3

计算合适的前缀长度的另外一个方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面展示如何计算完整列的选择性：

mysql>select count(distinct city)/count(*) from city;

index4

通常来说（尽管也有例外情况），这个例子中如果前缀的选择性能够接近于0.031，基本上就可用了。可以在一个查询中针对不同的前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性：

mysql>select 
count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7
from city;

index5

查询显示当前缀长度达7的时候，再增加前缀长度，选择性提升的幅度已经很小了。只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了，但如果数据分布很不均匀，可能就会有陷阱。如果观察前缀为4的最常出现城市的次数，可以看到明显不均匀：

mysql>select count(*) as cnt left(city,4) as pref from city group by pref order by cnt desc limit 5;

index6

如果前缀是4个字节，则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。如果有比这个随机生成的示例更真实的数据，就更有可能看到这种现象。例如在真实的城市名上建一个长度为4的前缀索引，对于以“San”和“New”开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头。

在上面的示例中，已经找到了合适的前缀长度，下面演示一下如何创建前缀索引：

mysql>alter talbe city add key(city(7));

前缀索引是一种能使索引更小更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP　BY，也无法使用前缀索引做覆盖扫描。有时候后缀索引(suffix index)也有用途（例如，找到某个域名的所有电子邮件地址）。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器来维护这种索引。

多列索引

很多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

先来看第一个问题，为每个列创建独立的索引，从show create table 中很容易看到这种情况：

create talbe t (
c1 int,
c2 int,
c3 int,
key(c1),
key(c2),
key(c3)
);

这种索引策略，一般是由于人们听到一些专家诸如“把where条件里面的列都建上索引”这样模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引，那么不如忽略掉where子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫"索引合并”（index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些计算到“查询成本”（ＣＯＳＴ）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响查询的并发性，但如果是单独运行这样的查询，则往往会忽略对并发性的影响。

如果在explain中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用ignore index提示让优化器忽略掉某些索引。

选择合适的索引列顺序

我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要（顺便说明，本节内容适用于B-Tree索引；哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据）。

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序order by,group by和distinct等子句的查询需求。

所以多列索引的列顺序至关重要。

对于如何选择索引的顺序有一个经验法则：将选择性最高的列放到索引最前列。这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机IO和排序那么重要，考虑问题需要更全面（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要）。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的。这时候索引的作用只是用于优化where条件的查找。在这种情况下，这样设计的索引确实能够最快的过滤出需要的行，对于在where子句中使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

以下面的查询为例：

select * from payment where staff_id=2 and customer_id=584;

是应该创建一个(staff_id,customer_id)索引还是应该颠倒一下顺序？可以跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。先用下面的查询预测一下（某些优化极客(geek)）将这称之为“sarg"，这是”可搜索的参数（searchable argument)"的缩写），看看各个where条件的分支对应的数据基数有多大：

mysql>select sum(staff_id=2),sum(customer_id=584) from payment \G;
*************1.row*******************
SUM(staff_id=2):7992
SUM(customer_id=584):30

根据前面的经验法则，应该将索引customer_id放到前面，因为对应条件值的customer_id数量更小。我们再来看看对于这个customer_id的条件值，对应的staff_id列的选择性如何：

mysql>select sum(staff_id=2) from payment where customer_id=584\G;
**************1.row********************
SUM(staff_id=2):17

这样做有一个地方需要注意，查询的结果非常依赖于特定的具体值。如果按上述办法优化，可能对其他一些条件值的查询不公平，服务器的整体性能可能变得更糟，或者其他某些查询的运行变得不如预期。

如果是从诸如pt-query-digest这样的工具的报告中提取“最差”查询，那么再按上述办法选定的索引顺序往往是非常高效的。如果没有类似的具体查询来运行，那么最好按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：

mysql>select count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*)
from payment\G;
***************1.row**************
staff_id_selectivity:0.0001
customer_id_selectivity:0.0373
COUNT(*):16049

customer_id的选择性更高，所以答案是将其作为索引列的第一列：

mysql>alter table payment add key(customer_id,staff_id);

当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。例如，在某些应用程序中，对于没有登录的用户，都将其用户名记录为"guest"，在记录用户行为的会话表和其他记录用户活动的表中"guest"就成为了一个特殊用户ID。一旦查询涉及这个用户，那么和对于正常用户的查询就大不同了，因为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号，和普通账号不同，它并不是一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它向网站的所有用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。

聚簇索引

聚簇索引（Oracle用户可能更熟悉索引组织表（index-organized table）的说法，实际上）是一样的意思。）并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。

因为是存储引擎负责实现索引，因为不是所有的存储引擎都支持聚簇索引。

下图展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。

index7

一些数据库服务器允许选择哪个索引作为聚簇索引。InnoDB将通过主键聚集数据，这也就是说图中的“被索引的列"就是主键列。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

聚簇主键可能对性能有帮助，但也可能导致严重的性能问题。所以需要仔细地考虑聚簇索引，尤其是将表的存储引擎从InnoDB改成其他引擎的时候。

聚簇的数据有一些重要的优点：

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有聚簇索引，则每封邮件都可能导致一次磁盘IO。
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

如果设计表和查询时能充分利用上面的优点，那就能极大地提升性能。同时，聚簇索引也有一些缺点：

聚簇数据最大限度地提高了IO密集型应用的性能　，但如果数据全部放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
插入速度严重依赖于插入顺序。按照主要的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临”页分裂（page split)“的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引（非聚簇索引）可能比想象的要更大，因为在二给索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的”行指针“的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次。对于 InnoDB，自适应哈希索引能够减少这样的重复工作。

InnoDB和MyISAM的数据分布对比

聚簇索引和非聚簇索引的数据分布有区别，以及对应的主要索引和二级索引的数据分布也有区别，通常会让人感到困扰和意外。来看看InnoDB和MyISAM是如何存储下面这个表的：

create table layout_test(
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)
);

假设该表的主键取值为1~10000，按照随机顺序播放并使用optimize table命令做了优化。换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。

MyISAM的数据分布。MyISAM的数据分布非常简单，所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示：

MyISAM表layout_test的数据分布

在行的旁边显示了行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行（MyISAM并不总是使用上图中的行号，而是根据定长还是变长的行使用不同的策略）。

这种分布方式很容易创建索引。下面显示的一系列图，隐藏了页的物理细节，只显示索引中的”节点“，索引中的每个叶子节点包含”行号“。下图显示了表的主键。

MyISAM表layout_test的主键分布

那col2列上的索引又会如何呢？有什么特殊的吗？回答是否定的：它和其他索引没有什么区别。下图展示了col2列上的索引。

MyISAM表layout_test的col2列索引的分布

事实上，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为Primary的唯一非空索引。

InnoDB的数据分布。因为InnoDB支持聚簇索引，所以使用非常不同的方式存储同样的数据。InnoDB以下图的方式存储数据。

InnoDB表layout_test的主键分布

第一眼看上去和MyISAM没什么不同，但再仔细看细节，会注意到该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引”就是“表，所以不像MyISAM那样需要独立的行存储。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回流指针以及所有的剩余列（在这个例子中是col2)。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是”行指针“，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

下图展示了示例表的col2索引。每一个叶子节点都包含了索引列（这里是col2)，紧接着是主键值(col1)。还展示了B-Tree的叶子节点结构，但我们故意活力了非叶子节点这样的细节。InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针（下一级节点可以是非叶子节点，也可以是叶子节点）。这对聚簇索引和二级索引都适用。

InnoDB表layout_test的二级索引分布

下图描述InnoDB和MyISAM如何存放表的抽象图。从图中可看出InnoDB和MyISAM保存数据和索引的区别。

聚簇和非聚簇表对比图

覆盖索引

通常大家都会根据查询的where条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再索引读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也有帮助，因为索引比数据还小，更容易全部放入内存中（这对于MyISAM尤其正确，因为MyISAM能压缩索引以变得更小）。
因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。对于某些存储引擎，例如MyISAM和Percona XtraDB，甚至可以通过Optimize命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
由于 InnoDB的聚簇索引，覆盖索引对于InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，耍哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引来做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引（Memory存储引擎就不支持覆盖索引）。

当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在explain的extra列可以看到“Using index”的信息【很容易把extra列的“Using index”和type列的“index” 搞混淆。其实这两者完全不同，type列和覆盖索引毫无关系；它只是表示这个查询访问数据的方式，或者说是MySQL查找行的方式。MySQL手册中称之为连接方式（join type）】。例如，表inventory有一个多列索引(store_id,film_id）。MySQL如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示：

mysql>explain select store_id,film_id from inventory\G;
*******1.row*********
id:1
select_type:simple
table:inventory
type:index
possible_keys:null
key:idx_storeid_film_id
key_len:3
ref:null
rows:4673
Extra:Using index

索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了where条件中的字段，但不是整个查询涉及的字段。如果条件为假（false），MySQL5.5和更早的版本也问题会回表获取数据行，尽管这并不需要这一行且最终会被过滤掉。

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如：索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描--例如order by desc--就不是很好了。所以在块中查找某一行的操作平均都需要扫描半个索引块。

测试表明，对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是IO密集型应用，对某些查询带来的好处会比成本多很多。

可以在create table 语句中指定pack_keys参数来控制索引压缩的方式。

冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

有时会在不经意间创建了重复索引，例如下面的代码：

create table test(
id int not null primary key,
a int not null,
b int not null,
unique(id),
index(id)
)engine=InnoDB;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求【如果索引类型不同，并不算重复索引。例如经常有很好的理由创建key(col)和fulltext key(col)两种索引】。

冗余索引和重复索引有一些不同。如果创建了索引(a,b)，再创建索引(a)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(a,b)也可以当作索引(a)来使用（这种冗余只是对B-Tree索引来说的）。但是如果再创建索引(b,a)，则不是冗余索引，索引(b)也不是，因为b不是索引(a,b)的最左前缀列。另外其他不同类型的索引（例如哈希索引或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(a,b)而不是扩展已有的索引(a)。还有一种情况是将一个索引扩展为(a,id)，其中id是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

例如：如果在整数列上有一个索引，现在需要额外增加一个很长的varchar列来扩展该索引，那性能可能会急剧下降。特别是有查询把这个索引当作覆盖索引，或者这是MyISAM表并且有很多范围查询（由于MyISAM的前缀压缩）的时候。

索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用 where子句。这时已经无法避免锁定行了：InnoDB已经锁住这些行，到适当的时候才释放。在MySQL5.1及更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁。

转载请注明：MitNick » 003MySQL创建高性能的索引-高性能索引策略