多列索引

一个误区：一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多了索引。

1. 为什么要建立多了索引

　　在每个列创建独立索引一般是由于听到诸如“吧where条件里面的列都建立上索引”这样模糊的建议导致的，实际上这个建议的非常错误的，在多个列上简历独立的单列索引大部分情况下并不能提高mysql的查询性能。

　　索引合并策略：

　　mysql5.0和更新版本引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行，更早版本的mysql只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有有效的，例如在file_id和actor_id上各有一个单列索引。但对于下面的这个查询where条件，这俩个单列索引都不试最好的选择;

　　mysql>SELECT file_id,actor_id FORM file_actor WHERE file_id = 1 OR actor_id = 1;

　　在老的mysql版本中，mysql会对这个查询使用权标扫描，但在mysql5.0和更新版本中，查询能够同时使用这俩个单列索引进行扫描，并将结果合并，这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前俩种情况下的联合和相交。（也就是说前面三种情况会mysql优化器会用到索引合并）

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

　　什么时候使用多列索引

　　当服务器对多个索引做相交操作时（通常有多个AND条件）

　　当服务器对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作。特别是有一些索引的选择性不高，需要合并扫描返回大量数据的时候。

 

2.选择合适的索引列顺序

　　在一个B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序（最左原则），其次是第二列，等等。（顺便说明本篇文章内容使用于B-Tree索引，哈希索引或者其他类型索引并不会想B-Tree索引一样按照顺序存储数据）

　　对于如何选择索引列顺序有一个检验法则：将选择性最高的列放在做索引的最前列。这个建议有用吗？当不需要考虑排序和分组的时候，将选择性最高的放在前面通常是很好的，这个时候索引的作用只是用于优化where条件的查找。然而性能不只是依赖于所用索引列的选择性（整体基数），也和查询条件的具体指有关，也就是和值的分布有关，所以，可能需要根据哪些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

 

3.使用多列索引

 以employees.titles表为例，下面先查看其上都有哪些索引：

SHOW INDEX FROM employees.titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            2 | title       | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      443308 |      | BTREE      |
| titles |          1 | emp_no   |            1 | emp_no      | A         |      443308 |      | BTREE      |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------

从结果中可以到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>。这样就可以专心分析索引PRIMARY的行为了。

 

全列匹配

 

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | titles | const | PRIMARY       | PRIMARY | 59      | const,const,const |    1 |       |
+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

 

很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒，仍然可以用到索引：

最左前缀匹配

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-