MySQL 索引选择原则

目的

         MySQL查询优化器是基于代价（cost-based）的查询方式。因此，在查询过程中，最重要的一部分是根据查询的SQL语句，依据多种索引，计算查询需要的代价，从而选择最优的索引方式生成查询计划。

         然而，在分析MySQL查询优化器过程中，是以查询优化器主线的原则进行研究，而忽略了很多细节的内容。因此，对MySQL索引选择进行进一步的研究和分析，给出创建和使用索引的规则，从而有助于分析SQL查询。

设计

         设计原则主要依据为尽可能的测试索引，而不考虑索引的合理性。一方面可以更加全面的测试在多种索引存在的情况下，查询优化器是如何进行选择的。另一方面可以根据选择索引的原则，评估索引的合理性。

1、数据表设计

         数据表设计如下所示，其中students_origin表中只有主键索引，students表设计主键索引（Primary Key）、唯一索引（Unique Key）、B+索引、联合索引等。

 

CREATE TABLE `students_origin` (

  `id` int(11) NOT NULL,

  `name` varchar(30) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `major` varchar(20) DEFAULT NULL,

  `rank` int(11) DEFAULT NULL,

  `total` int(11) DEFAULT NULL,

  `comment` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `students` (

  `id` int(11) NOT NULL,

  `name` varchar(30) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `major` varchar(20) DEFAULT NULL,

  `rank` int(11) DEFAULT NULL,

  `total` int(11) DEFAULT NULL,

  `comment` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`),

  KEY `idx_major` (`major`),

  KEY `idx_rank` (`rank`),

  KEY `idx_rank_total` (`rank`,`total`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

2、测试语句设计

         具体的查询SQL语句如下所示：

1、select id from students;(students_origin)

2、select id, name from students; (students_origin)

3、select id, rank from students;

4、select id, rank, total from students;

5、select id, rank, total from students where rank = 2;

 测试

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()函数的核心代码如下所示：

 

for (uint nr=0; nr < table->s->keys ; nr++)

    {

      if (nr == usable_clustered_pk)

        continue;

      if (usable_keys->is_set(nr))

      {

        if (table->key_info[nr].key_length < min_length)

        {

          min_length=table->key_info[nr].key_length;

          best=nr;

        }

      }

    }

 1.2 执行计划

首先对仅有主键索引的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