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

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。 示例数据库

为了讨论索引策略，需要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库关系复杂度适中，且数据量较大。下图是这个数据库的E-R关系图（引用自MySQL官方手册）：

file

MySQL官方文档中关于此数据库的页面为http://dev.mysql.com/doc/employee/en/employee.html。里面详细介绍了此数据库，并提供了下载地址和导入方法，如果有兴趣导入此数据库到自己的MySQL可以参考文中内容。 最左前缀原理与相关优化 高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

这里先说一下联合索引的概念。在上文中，我们都是假设索引只引用了单个的列，实际上，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数，但是这里我不想讨论太多关系代数的话题，因为那样会显得很枯燥，所以这里就不再做严格定义。另外，单列索引可以看成联合索引元素数为1的特例。

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

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

从结果中可以到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引。为了避免多个索引使事情变复杂（MySQL的SQL优化器在多索引时行为比较复杂），这里我们将辅助索引drop掉： ALTER TABLE employees.titles DROP INDEX emp_no;

这样就可以专心分析索引PRIMARY的行为了。 情况一：全列匹配

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' 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 | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
6. +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+------

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

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