文章插图
 
图12
MySQL官方文档中关于此数据库的页面为http://dev.mysql.com/doc/employee/en/employee.html 。里面详细介绍了此数据库，并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的MySQL可以参考文中内容 。
最左前缀原理与相关优化高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理 。
这里先说一下联合索引的概念 。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义 。另外，单列索引可以看成联合索引元素数为1的特例 。
以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> 。为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），这里我们将辅助索引drop掉：
ALTER TABLE employees.titles DROP INDEX 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 from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| id | select_type | table| type| possible_keys | key| key_len | ref| rows | Extra |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+|1 | SIMPLE| titles | const | PRIMARY| PRIMARY | 59| const,const,const |1 ||+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+效果是一样的 。
情况二：最左前缀匹配 。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 from_date='1986-06-26';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| id | select_type | table| type | possible_keys | key| key_len | ref| rows | Extra|+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+|1 | SIMPLE| titles | ref| PRIMARY| PRIMARY | 4| const |1 | Using where |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+