本系列文章主目录：MySQL数据库InnoDB存储引擎查询优化器实现的分析

 附录一

create table aaa (
a1 int,
a2 int,
a3 int,
a4 int,
a5 int,
a6 int,
a7 int,
a8 int,
a9 int) engine = innodb;

 

create index a1 on aaa (a1);
create index a1a2 on aaa (a1,a2);
create index a1a2a3 on aaa (a1,a2,a3);
create index a1a2a3a4 on aaa (a1,a2,a3,a4);
create index a1a2a3a4a5 on aaa (a1,a2,a3,a4,a5);
create index a1a2a3a4a5a6 on aaa (a1,a2,a3,a4,a5,a6);
create index a1a2a3a4a5a6a7 on aaa (a1,a2,a3,a4,a5,a6,a7);
create index a1a2a3a4a5a6a7a8 on aaa (a1,a2,a3,a4,a5,a6,a7,a8);
create index a1a2a3a4a5a6a7a8a9 on aaa (a1,a2,a3,a4,a5,a6,a7,a8a9);
create index a2 on aaa (a2);
create index a3 on aaa (a3);
create index a4 on aaa (a4);
explain select * from aaa where a1 = 1 and a2 = 2 and a3 = 3 and a4 = 4 and a5 = 5 and a6 = 6;

 

mysql> explain select * from aaa where a1 = 1 and a2 = 2 and a3 = 3 and a4 = 4 and a5 = 5 and a6 = 6;

+―-+――――-+――-+――+――――――――――――――――――――――――――――――――――――-+――+―――+――-+――+――――-+

| id | select_type | table | type | possible_keys                                                                                               | key  | key_len | ref   | rows | Extra       |

+―-+――――-+――-+――+――――――――――――――――――――――――――――――――――――-+――+―――+――-+――+――――-+

|  1 | SIMPLE      | aaa   | ref  | a1,a1a2,a1a2a3,a1a2a3a4,a1a2a3a4a5,a1a2a3a4a5a6,a1a2a3a4a5a6a7,a1a2a3a4a5a6a7a8,a1a2a3a4a5a6a7a8a9,a2,a3,a4 | a1   | 5       | const |    1 | Using where |

+―-+――――-+――-+――+――――――――――――――――――――――――――――――――――――-+――+―――+――-+――+――――-+

 

以上的查询，由于a1列打头的索引太多，导致possible_keys太多，一共有9个a1+3个其他= 12个possible_keys，每个possible_keys都需要做两次的search_path to leaf page。

导致在查询优化的过程中，就有24次的IO(假设叶节点不命中)，这个开销要远远大于查询本身的开销。

我的建议是，以同一个列打头的索引，越少越好

附录二

测试语句：

语句一：select c3, c5 from nkeys where c3 > 0;

语句二：select c3, c5 from nkeys where c3 > 0 for update;

 

源代码调整:

在Opt_range.cc文件的get_key_scans_params函数中，将索引覆盖扫描计算出的found_read_time增加，如下：

/*

          We can resolve this by only reading through this key.

          0.01 is added to avoid races between range and ‘index’ scan.

        */

found_read_time= get_index_only_read_time(param,found_records,keynr) +

cpu_cost + 0.01;

found_read_time += 1000.0;

保证索引覆盖扫描的read_time一定大于全表扫描，此时看mysql最终是否还是会选择索引覆盖扫描？

在innodb的row0sel.c::row_search_for_mysql中设置断点，最后发现，查询仍旧走的是nkey1索引(c3,c5)，虽然此索引计算出来的代价1001.6 >>全表扫描代价2.2.

为什么会如此，是因为mysql在代价估算完成之后，针对索引覆盖扫描，仍旧会再次优化，具体可见3.1单表range查询章节的第6步以及下面的详细分析：

 

// 若当前为全表扫描，JT_ALL，同时有一个或者多个索引可以进行索引覆盖扫描，则优先

// 选择索引覆盖扫描，进行查询

sql_select.cc::make_join_readinfo ->

//find_shortest_key函数，遍历表上的所有可以进行覆盖扫描的索引

//找出其中键值(key_length)最小的索引，最为最优的覆盖扫描索引

//并且以此索引，替换全表扫描

tab->index = find_shortest_key(table, & table->covering_keys) ->

// 进行索引覆盖扫描

tab->read_first_record = join_read_first;

tab->type = JT_NEXT;

 

不足分析：

针对语句二，select for update，mysql同样做了Coverage index scan的优化，最后选择的仍旧是(c3, c5)的组合索引。但是，由于当前读需要回聚簇索引加事务锁，因此回表是必须的，不能做索引覆盖扫描。这个优化对于当前读来说，有可能适得其反。

 

附录三

best_extension_by_limited_search函数实现伪代码：

@code

    procedure best_extension_by_limited_search(

      pplan in,             // in, partial plan of tables-joined-so-far

      pplan_cost,           // in, cost of pplan

      remaining_tables,     // in, set of tables not referenced in pplan

      best_plan_so_far,     // in/out, best plan found so far

      best_plan_so_far_cost,// in/out, cost of best_plan_so_far

      search_depth)         // in, maximum size of the plans being considered

    {

      for each table T from remaining_tables

      {

        // Calculate the cost of using table T as above

        cost = complex-series-of-calculations;

        // Add the cost to the cost so far.

        pplan_cost+= cost;

        if (pplan_cost >= best_plan_so_far_cost)

          // pplan_cost already too great, stop search

          continue;

        pplan= expand pplan by best_access_method;

        remaining_tables= remaining_tables - table T;

        if (remaining_tables is not an empty set

            and

            search_depth > 1)

        {

          best_extension_by_limited_search(pplan, pplan_cost,

                                           remaining_tables,

                                           best_plan_so_far,

                                           best_plan_so_far_cost,

                                           search_depth - 1);

        }

        else

        {

          best_plan_so_far_cost= pplan_cost;

          best_plan_so_far= pplan;

        }

      }

    }

    @endcode

附录四

nkeys表定义：

mysql> show create table nkeys;

+――-+――――――――――

―――――――――――-+

| Table | Create Table

|

+――-+――――――――――

―――――――――――-+

| nkeys | CREATE TABLE `nkeys` (

`c1` int(11) NOT NULL,

`c2` int(11) DEFAULT NULL,

`c3` int(11) DEFAULT NULL,

`c4` int(11) DEFAULT NULL,

`c5` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`),

UNIQUE KEY `c3` (`c3`),

UNIQUE KEY `c4` (`c4`),

KEY `nkey1` (`c3`,`c5`),

KEY `c1c2` (`c1`,`c2`),

KEY `c1c2c3` (`c1`,`c2`,`c3`),

KEY `c1c2c3c4` (`c1`,`c2`,`c3`,`c4`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

 

aaa表定义：

create table aaa (

a1 int,

a2 int,

a3 int,

a4 int,

a5 int,

a6 int,

a7 int,

a8 int,

a9 int

) engine = innodb;

 

create index a1 on aaa (a1);

create index a1a2 on aaa (a1,a2);

create index a1a2a3 on aaa (a1,a2,a3);

create index a1a2a3a4 on aaa (a1,a2,a3,a4);

create index a1a2a3a4a5 on aaa (a1,a2,a3,a4,a5);

create index a1a2a3a4a5a6 on aaa (a1,a2,a3,a4,a5,a6);

create index a1a2a3a4a5a6a7 on aaa (a1,a2,a3,a4,a5,a6,a7);

create index a1a2a3a4a5a6a7a8 on aaa (a1,a2,a3,a4,a5,a6,a7,a8);

create index a1a2a3a4a5a6a7a8a9 on aaa (a1,a2,a3,a4,a5,a6,a7,a8a9);

create index a2 on aaa (a2);

create index a3 on aaa (a3);

create index a4 on aaa (a4);

建议继续学习：

1. Mysql查询优化器浅析（上）    (阅读：2541)
2. MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询    (阅读：2489)
3. Mysql查询优化器浅析（下）    (阅读：2098)
4. MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询    (阅读：2109)
5. MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询    (阅读：2032)
6. MySQL数据库InnoDB存储引擎查询优化器实现的分析    (阅读：1984)
7. MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数    (阅读：1915)
8. MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息    (阅读：1661)
9. MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析    (阅读：1558)