分类：
2012-08-29 10:50:23
原文地址：MySQL 索引选择原则 作者：king_wangheng
目的
MySQL查询优化器是基于代价（cost-based）的查询方式。因此，在查询过程中，最重要的一部分是根据查询的SQL语句，依据多种索引，计算查询需要的代价，从而选择最优的索引方式生成查询计划。
然而，在分析MySQL查询优化器过程中，是以查询优化器主线的原则进行研究，而忽略了很多细节的内容。因此，对MySQL索引选择进行进一步的研究和分析，给出创建和使用索引的规则，从而有助于分析SQL查询。
设计
设计原则主要依据为尽可能的测试索引，而不考虑索引的合理性。一方面可以更加全面的测试在多种索引存在的情况下，查询优化器是如何进行选择的。另一方面可以根据选择索引的原则，评估索引的合理性。
1、数据表设计
数据表设计如下所示，其中students_origin表中只有主键索引，students表设计主键索引（Primary Key）、唯一索引（Unique Key）、B+索引、联合索引等。
|
点击(此处)折叠或打开
|
2、测试语句设计
具体的查询SQL语句如下所示：
|
点击(此处)折叠或打开
|
1、主键查询
对主键id查询的测试，主要为了查看对于主键查询时，在多种索引并存的情况下，MySQL查询是如何执行的。以及如果使用了索引，选择索引的原则。
1.1 源码分析
首先从源码角度分析的MySQL查询优化器的处理逻辑，从而了解MySQL是如何进行处理的。
MySQL查询优化器的核心处理逻辑，在JOIN::optimizer()函数（sql\sql_select.cc:854）中，通过基于代价的查询处理，选择代价最低查询方式执行。对于该查询来说，由于没有任何过滤条件，因此在调用make_join_statistics()函数（sql\sql_select.cc:2651）执行基于代价的查询处理过程后，查询执行的类型仍然为JT_ALL。也就是说，执行该查询仍然是全表扫描方式。特别说明，MySQL查询优化器基于代价的查询处理，主要是根据给出的查询条件来进行优化的，如where条件、ON条件等。
基于代价的查询处理之后，会根据查到的最优计划，生成最终的执行计划。该过程通过调用make_join_readinfo()函数（sql\sql_select.cc:6818）生成最终的查询计划。对于查询类型为JT_ALL，如果查询的表中有索引，则会调用find_shortest_key()函数（sql\sql_select.cc:13438）根据查询字段的索引覆盖情况，以及索引字段的键值长度，查找最短的键值，进行全表扫描。根据基于代价的查询处理之后，该查询的查询类型为JT_ALL。如果students中没有多个索引，只有主键索引的情况下，查询使用主键索引进行查询。但由于students表中有多个索引，因此，查找键值长度最短的索引idx_rank进行全表扫描。
find_shortest_key()函数的核心代码如下所示：
|
点击(此处)折叠或打开
|
首先对仅有主键索引的students_origin表，执行SQL查询，查询计划如下所示：
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
row |
Extra |
|
1 |
SIMPLE |
students_origin |
index |
NULL |
PRIMARY |
4 |
NULL |
10 |
Using index |
对studentns数据表进行相同的SQL查询，查询的执行计划如下所示：
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
row |
Extra |
|
1 |
SIMPLE |
students |
index |
NULL |
idx_rank |
5 |
NULL |
10 |
Using index |
从执行计划来看，第一个查询的查询类型为index，键值为主键索引，而第二个查询使用idx_rank索引进行全表扫描。
对于相同的SQL查询语句，第二个查询使用的索引发生了变化。原因与Innodb数据和索引的存储有关，具体Innodb数据和索引的存储内容在MySQL官方文档和《High Performance MySQL》中都有相关的讲解。并且对于Innodb数据索引存储的源码实现逻辑，将进一步深入分析和测试，这里仅简要解释相关的内容。首先Innodb是聚集存储的，每条记录的数据以主键进行聚集存储，通过主键进行索引。如果没有定义主键，系统默认使用6个字符作为主键进行聚集存储。而辅助索引（secondary indexes）中的每条记录包含主键（primary key）和索引字段。
因此，对于该查询，查询优化器使用辅助索引进行查询，通过辅助索引就可以找到查询的id，查找的代价更低。
2、唯一索引查询
对唯一索引字段进行查询的测试，主要用于查看辅助索引在查询索引字段的情况下，MySQL选择索引的原则。
2.1 源码分析
从源码分析MySQL查询优化器，与1.1的逻辑处理流程基本一致。由于没有任何过滤条件，因此查询优化器调用make_join_statistics()函数进行基于代价的查询过程后，查询类型为JT_ALL。在生成查询计划时，调用find_shortest_key()函数查找最短的键值，进行查询全表。由于唯一索引name能够被索引覆盖，因此通过唯一索引来查询全表。
2.2查询计划
对students_origin表执行SQL查询，查询计划如下所示：
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
row |
Extra |
|
1 |
SIMPLE |
students_origin |
ALL |
NULL |
NULL |
NULL |
NULL |
10 |
|
对students表执行SQL查询，查询计划如下所示：
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
row |
Extra |
|
1 |
SIMPLE |
students |
index |
NULL |
name |
93 |
NULL |
10 |
Using index |
由查询计划可知，对name字段没有唯一索引的表进行查询时，查询进行全表扫描，查询类型为ALL。而对name字段使用唯一索引的students表查询时，使用唯一索引进行全表扫描。查询类型为index，而非ALL。
通过以上分析可知，当查询的SQL可以通过辅助索引得到查询结果时，MySQL查询优化器会选择辅助索引进行查询优化，这有效的降低了查询的代价。
3、多个索引查询
对索引字段创建索引和联合索引，查看MySQL查询优化器是如何选择索引，来提高查询效率的。
3.1 源码分析
从源码分析来看，MySQL查询优化器的逻辑处理流程与1.1基本一致。对于全表查询，make_join_statistics()函数进行基于代价的查询处理后，查询类型为JT_ALL。由于rank字段有两个索引，在调用find_shortest_key()函数，查找idx_rank（长度为5）和idx_rank_total（长度为10）中最短的键值，最终选择idx_rank进行查询。由于查询字段id, rank能够被索引覆盖，因此通过idx_rank索引来查询。
3.2查询计划
对students表执行SQL查询，查询计划如下所示：
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
row |
Extra |
|
1 |
SIMPLE |
students |
index |
NULL |
idx_rank |
5 |
NULL |
10 |
Using index |
从查询计划来看，在rank被idx_rank和idx_rank_total两个索引都覆盖的情况下，MySQL查询优化器选择键值长度短的idx_rank进行查询。
4、联合索引查询
使用联合索引，主要用于对比在查询中使用过滤条件时，选择索引的不同，从而分析MySQL查询优化器的处理逻辑。
4.1 源码分析
从源码分析来看，MySQL查询优化器的逻辑处理流程与3.1基本一致。不同之处在于调用find_shortest_key()函数时，仅有idx_rank_total索引覆盖查询字段。因此查询优化器选择idx_rank_total索引进行覆盖索引查询。
4.2查询计划
对students表执行SQL查询，查询计划如下所示：
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
row |
Extra |
|
1 |
SIMPLE |
students |
index |
NULL |
idx_rank_total |
10 |
NULL |
10 |
Using index |
从查询计划来看，MySQL查询优化器使用覆盖索引idx_rank_total进行查询，该查询测试主要用于对比测试5中的结果。
5、条件索引查询
通过where条件，对查询内容进行过滤。该测试主要用于查看，rank在where过滤条件中时，与没有过滤条件的测试4进行比较，查看查询优化器选择索引的原则。
5.1 源码分析
通过源码可知，对于有where过滤条件的SQL查询，查询优化器在make_join_statistics()函数中，根据基于代价的原则，通过调用update_ref_and_keys()函数（sql\sql_select.cc:3963），查找rank可以使用的索引有idx_rank和idx_rank_total两个索引，然后调用check_quick_keys()函数（sql\opt_range.cc:7628）查找对查询条件索引的记录数，MySQL查询优化器选择查询代价最低的索引idx_rank。查询优化器通过基于代价的处理后，查询类型为JT_REF。因此，生成查询计划时，由于已经选定了使用的索引，所以不必再调用find_shortest_key()函数再去查找键值最短的索引。
5.2 查询计划
对students表执行SQL查询，查询计划如下所示：
|
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
row |
Extra |
|
1 |
SIMPLE |
students |
ref |
idx_rank,idx_rank_total |
idx_rank |
5 |
const |
1 |
Using where |
从查询计划来看，查询类型为ref，查询使用的索引为idx_rank，并且查询使用的是where条件，而没有使用索引覆盖进行查询。也就是说，首先通过索引idx_rank查找到键值，通过主键id查找对应的记录。
与测试4.2相比，查询计划发生了改变。主要原因是由于where条件改变了查询优化器的处理逻辑。查询优化器根据查询条件通过基于代价的处理，选择查询代价最低的索引，从而生成查询计划。而与4.2相比，查询优化器选择通过覆盖索引来进行查询，而不进行全表扫描。
在实际应用中，某些情况下，通过where条件进行基于代价的处理，选择where条件字段的索引，反而不如通过覆盖索引进行过滤where条件的查询代价低。正如测试5的查询，如果查询的字段可以通过覆盖索引进行查询，并通过where条件过滤的方式，可能比查询优化器通过where条件进行查找最优的索引查找，再通过对应主键进行查询更高效。该问题已超出范围，将在之后进行详细测试和分析。
结论
通过以上测试，对MySQL查询优化器选择索引的原则有较深入的理解，通过对SQL查询进行分析，有助于判断查询类型和选择的索引。
MySQL查询优化器的在索引选择的规则可以概括为：
1、对无过滤条件、索引可以覆盖的查询。查询优化器选择覆盖索引键值最短的索引进行查询；
2、对无过滤条件、无索引覆盖的查询。查询优化器选择全表扫描；
3、对有过滤条件、索引可以覆盖的查询。查询优化器优先基于代价的方式对过滤条件进行处理。如果可以索引查找，将选择代价最低的索引进行查找。如果是全表扫描，则通过查找键值最短的覆盖索引进行查询，并通过过滤条件进行过滤。
4、对有过滤条件、无索引覆盖的查询。查询优化器基于代价的方式对过滤条件进行处理，生成查询计划。
参考
1、secondary index：
http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_secondary_index
2、 Clustered and Secondary Indexes：
http://dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html
