高性能的索引策略（一）

索引有许多有点，比如常见的B-TRee索引，是按照顺序存储数据，索引MySQL可以用来做ORDER BY和GROUP BY操作，因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。当然因为索引中存储了实际的列值，所以有些查询不需要扫描表，只需要索引就可以完成查询（即SELECT column1而不是SELECT *）。优点总结如下：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机I/O变为顺序I/O。

虽然索引优点很明显，但是正确的创建和使用索引也是很关键的，下面介绍一下常见的能提高性能的创建索引方式。

1. 独立的列

查询的时候，要保证查询中的列是独立的，否则MySQL不会使用索引。“独立的列”是指索引列不能够是表达式的一部分，也不能是函数的参数。例如下面这个查询无法使用actor_id列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id +1 = 5;

其实WHERE中等价于actor_id = 4，但MySQL不会自动解析。所以我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

2. 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢，此时通常可以索引开始的部分字符，这样能节省索引空间，提高索引效率。但这会降低索引的选择性。索引选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1 / #T到1之间。索引的选择性越高，查询效率越高，因为可以让MySQL在查找时过滤掉更多的行。

所以诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间），前缀的“基数”应该接近于完整列的“基数”。选择好前缀索引后，便可以进行创建，语句如下：

mysql> ALTER TABLE city_demo ADD KEY (city(7));

当然前缀索引时一种能使索引更下，更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

3. 多列索引

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

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

mysql> SELECT file_id, actor_id FROM sakila.file_actor WHERE actor_id = 1 OR film_id = 1;

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

mysql> SELECT file_id, actor_id FROM sakila.file_actor WHERE actor_id = 1
	-> UNION ALL
	-> SELECT file_id, actor_id FROM sakila.file_actor WHERE film_id = 1	
	-> AND actor_id <> 1;

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

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