索引的数据结构及算法原理--索引使用策略及优化（中）

情况二：最左前缀匹配

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

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

情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
2. ---- ------------- -------- ------ --------------- --------- --------- ------- ------ -------------
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. ---- ------------- -------- ------ --------------- --------- --------- ------- ------ -------------
5. | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
6. ---- ------------- -------- ------ --------------- --------- --------- ------- ------ -------------

此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以增加一个辅助索引<emp_no, from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

首先我们看下title一共有几种不同的值：

1. SELECT DISTINCT(title) FROM employees.titles;
2. --------------------
3. | title |
4. --------------------
5. | Senior Engineer |
6. | Staff |
7. | Engineer |
8. | Senior Staff |
9. | Assistant Engineer |
10. | Technique Leader |
11. | Manager |
12. --------------------

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：这次key_len为59，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询，这里检查了7个key。看下两种查询的性能比较：

1. SHOW PROFILES;
2. ---------- ------------ -------------------------------------------------------------------------------
3. | Query_ID | Duration | Query |
4. ---------- ------------ -------------------------------------------------------------------------------
5. | 10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'|
6. | 11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ... |
7. ---------- ------------ ---------------------------------------------------------

“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显。当然，如果title的值很多，用填坑就不合适了，必须建立辅助索引。