MySQL数据库索引选择原则

来源:王恒博客  发布日期:2012-08-28 09:56:28
目的
         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语句如下所示:

select id from students;(students_origin)
select id, name from students; (students_origin)
select id, rank from students;
select id, rank, total from students;
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)和索引字段。
www.it165.net
因此,对于该查询,查询优化器使用辅助索引进行查询,通过辅助索引就可以找到查询的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、对有过滤条件、无索引覆盖的查询。查询优化器基于代价的方式对过滤条件进行处理,生成查询计划。 
Tag标签: MySQL数据库   索引  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规