高性能索引策略二
多列索引
一个误区:一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多了索引。
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 | | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
当查询条件精确匹配索引的左边连续一个或几个列时,如<emp_no>或<emp_no, title>,所以可以被用到,但是只能用到一部分,即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引,但是key_len为4,说明只用到了索引的第一列前缀。
范围查询
EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' and title > 12; +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
可以用到最左前缀匹配的索引,但范围列后面的列无法使用到索引
同时多列索引遵守独立的列原则,查询中有函数或表达式,mysql不会用到索引