优化MySQL索引的N种方法

正确创建和使用索引是实现高性能查询的基础。

独立的列

索引列不能是表达式的一部分，也不能是函数的参数，必须是独立的列。如果查询中的列不是独立的，则MySQL就不会使用索引。

前缀索引和索引选择性

有时候需要索引很长的字符列，这回让索引变得大且慢。那么，就可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

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

前缀所以是一种能使索引更小、更快的有效办法，但另一方面，它也限制了使用前缀索引做ORDER BY 和GROUP BY的可能，也无法使用前缀索引做覆盖扫描。

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新的版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。在更早的版本上，MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。

例如，表film_actor在film_id和actor_id上各有一个单列索引。但对于下面这个查询WHERE条件，这两个单列索引都不是好的选择

1	SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 or film_id = 1

在老的MySQL版本中，MySQL对这个查询会使用全表扫描。除非改写成吐下两个查询UNION的方式：

SELECT film_id, actor_id FROM sakila.film_actor where actor_id = 1
UNION ALL
SELECT film_id, actor_id FROM sakila.film_acotr where film_id = 1
AND actor_id <> 1;

但在MySQL5.0和更新的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合，AND条件的小脚，组合前两种状况的联合及相交。

MySQL会使用这类技术优化复杂查询，所以在某些语句的Extra列中还可以看到嵌套操作。

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

当出现服务器对多个索引做相交操作时（通常有多个AND），通常意味着需要一个包含所有相关列的多列索引
当服务器需要对多个索引做联合操作时（通常有多个OR），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并带扫描返回大量数据的时候。

选择合适的索引列顺序

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页上。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能由一个聚簇索引。

InnoDB通过主键聚集数据，也就是说，主键列会建一个聚簇索引。

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引有一些重要的优点：

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

同时聚簇索引也有一些缺点：

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

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为覆盖索引。覆盖索引是非常有用的工具，能够极大地提高性能。

索引条目通常远小于数据行大小，所以如果只需要读取索引，那就会极大地较少减少数据访问量。
因为索引是按照列值顺序存储的（至少单个页内是如此），所以，对于IO密集型的范围查询回避随机从磁盘读取每一行数据IO要少得多
一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统缓存，一次访问数据需要一次系统调用
由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

使用索引扫描来做排序

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则MySQL都需要执行排序操作。

压缩（前缀压缩）索引

MyISAM使用前缀要说来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数配置也可以对整数做压缩。MyIASM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。

冗余和重复索引

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

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

未使用的索引

删除即可。

索引和锁

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

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