索引使用策略

一、示例数据库
本文选用MySQL官方文档中提供的示例数据库之一：employees。

二、命中索引原理
高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。
MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列。（单列索引可以看成联合索引元素数为1的特例）

以employees.titles表为例，SHOW INDEX FROM employees.titles;

索引.png
从结果中可以到titles表的主索引为<emp_no, title, from_date>

现在分析各类索引的匹配情况：
1、全列匹配
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

全列匹配1.png

当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。
注意点：理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no='10001' AND from_date='1986-06-26' AND title='Senior Engineer' ;

全列匹配2.png
使用效果一致。

2、最左前缀匹配
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no='10001';

最左前缀匹配png

当查询条件精确匹配索引的左边连续一个或几个列时（如<emp_no>或<emp_no, title>），索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

3、索引中列的部分匹配，且非最左前缀匹配
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no='10001'
AND from_date='1986-06-26';

非最左前缀部分匹配.png

因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。
改善方案：
(a)增加一个辅助索引<emp_no, from_date>
(b)“补坑”法
b.1:检查所缺字段类型（可枚举适用）
SELECT DISTINCT(title) FROM employees.titles;
b.2:
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';

补坑命中结果.png

4、条件未匹配索引第一列
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE from_date='1986-06-26';

未匹配索引.png

5、匹配某列的前缀字符串
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no='10001'
AND title LIKE 'Senior%'

部分匹配.png

6、范围查询
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no < '10001'
AND title = 'Senior'

范围查询.png

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。
索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

7、查询条件含有函数或表达式
例句：
EXPLAIN
SELECT *
FROM employees.titles
WHERE emp_no -1 = '10000'

条件含有函数.png

这个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个表达式，MySQL无法为其使用索引。看来MySQL还没有智能到自动优化常量表达式的程度，因此在写查询语句时尽量避免表达式出现在查询中，而是先手工私下代数运算，转换为无表达式的查询语句。