本系列文章主目录：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);