MySQL 索引选择原则

2013/12/06 17:45
阅读数 1W
  MySQL 索引选择原则  2012-08-27 22:19:12

分类: Mysql/postgreSQL

目的

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

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

设计

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

1、数据表设计

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

 


点击(此处)折叠或打开

  1. CREATE TABLE `students_origin` (

  2.   `id` int(11) NOT NULL,

  3.   `name` varchar(30) DEFAULT NULL,

  4.   `age` int(11) DEFAULT NULL,

  5.   `major` varchar(20) DEFAULT NULL,

  6.   `rank` int(11) DEFAULT NULL,

  7.   `total` int(11) DEFAULT NULL,

  8.   `comment` varchar(20) DEFAULT NULL,

  9.   PRIMARY KEY (`id`)

  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  11.  

  12. CREATE TABLE `students` (

  13.   `id` int(11) NOT NULL,

  14.   `name` varchar(30) DEFAULT NULL,

  15.   `age` int(11) DEFAULT NULL,

  16.   `major` varchar(20) DEFAULT NULL,

  17.   `rank` int(11) DEFAULT NULL,

  18.   `total` int(11) DEFAULT NULL,

  19.   `comment` varchar(20) DEFAULT NULL,

  20.   PRIMARY KEY (`id`),

  21.   UNIQUE KEY `name` (`name`),

  22.   KEY `idx_major` (`major`),

  23.   KEY `idx_rank` (`rank`),

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

  25. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

2、测试语句设计

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


点击(此处)折叠或打开

  1. 1、select id from students;(students_origin)

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

  3. 3、select id, rank from students;

  4. 4、select id, rank, total from students;

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

 


点击(此处)折叠或打开

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

  2.     {

  3.       if (nr == usable_clustered_pk)

  4.         continue;

  5.       if (usable_keys->is_set(nr))

  6.       {

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

  8.         {

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

  10.           best=nr;

  11.         }

  12.       }

  13.     }

 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

 

         从查询计划来看,在rankidx_rankidx_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条件,对查询内容进行过滤。该测试主要用于查看,rankwhere过滤条件中时,与没有过滤条件的测试4进行比较,查看查询优化器选择索引的原则。

5.1 源码分析

         通过源码可知,对于有where过滤条件的SQL查询,查询优化器在make_join_statistics()函数中,根据基于代价的原则,通过调用update_ref_and_keys()函数(sql\sql_select.cc:3963),查找rank可以使用的索引有idx_rankidx_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、对有过滤条件、无索引覆盖的查询。查询优化器基于代价的方式对过滤条件进行处理,生成查询计划。

 

参考

1secondary 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

展开阅读全文
打赏
0
21 收藏
分享
加载中
更多评论
打赏
0 评论
21 收藏
0
分享
返回顶部
顶部