MySQL数据库InnoDB存储引擎查询优化器实现的分析之附录
本系列文章主目录: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);
建议继续学习:
- Mysql查询优化器浅析(上) (阅读:2528)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询 (阅读:2469)
- Mysql查询优化器浅析(下) (阅读:2087)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询 (阅读:2089)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询 (阅读:2002)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析 (阅读:1969)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数 (阅读:1896)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息 (阅读:1642)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析 (阅读:1543)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:Eugene 来源: MySQLOPS 数据库与运维自动化技术分享
- 标签: 查询优化器
- 发布时间:2012-01-08 22:31:30
- Mysql查询优化器浅析(上)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询
- Mysql查询优化器浅析(下)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询
- MySQL数据库InnoDB存储引擎查询优化器实现的分析
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析
- [68] Go Reflect 性能
- [68] 如何拿下简短的域名
- [67] Oracle MTS模式下 进程地址与会话信
- [62] IOS安全–浅谈关于IOS加固的几种方法
- [61] 图书馆的世界纪录
- [60] 【社会化设计】自我(self)部分――欢迎区
- [58] android 开发入门
- [56] 视觉调整-设计师 vs. 逻辑
- [49] 给自己的字体课(一)——英文字体基础
- [48] 读书笔记-壹百度:百度十年千倍的29条法则