MySQL · 答疑解惑 · MySQL 优化器 range 的代价计算
本文我们从一个索引选择的问题出发,来研究一下 MySQL 中 range 代价的计算过程,进而分析这种计算过程中存在的问题。
第一种情况:situation_unique_key_id
mysql show create table cpa_order\G *************************** 1. row *************************** Table: cpa_order Create Table: CREATE TABLE `cpa_order` ( `cpa_order_id` bigint(20) unsigned NOT NULL, `settle_date` date DEFAULT NULL COMMENT, `id` bigint(20) NOT NULL, PRIMARY KEY (`cpa_order_id`), UNIQUE KEY `id` (`id`), KEY `cpao_settle_date_id` (`settle_date`,`id`), ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.00 sec) mysql explain select * from cpa_order where settle_date=2015-11-05 and id 15 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cpa_order type: ref possible_keys: id,cpao_settle_date_id key: cpao_settle_date_id key_len: 4 ref: const rows: 7 Extra: Using index condition 1 row in set (0.00 sec)
SQL 语句执行过程可以看出,当 id 为 unique key 的时候,key_len= 4, 不难发现联合索引只使用了字段 cpao_settle_date_id ,而 id 并没有使用;
第二种情况:situation_without_key_id
mysql alter table cpa_order drop index id; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql explain select * from cpa_order where settle_date=2015-11-05 and id 15 \G (我们称之为 situation_without_key_id) *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cpa_order type: range possible_keys: cpao_settle_date_id key: cpao_settle_date_id key_len: 12 ref: NULL rows: 3 Extra: Using index condition 1 row in set (0.00 sec)
第三种情况: situation_plain_key_id
mysql explain select * from cpa_order where settle_date=2015-11-05 and id 15 \G (我们称之为 situation_plain_key_id) *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cpa_order type: range possible_keys: cpao_settle_date_id,id key: cpao_settle_date_id key_len: 12 ref: NULL rows: 3 Extra: Using index condition 1 row in set (0.01 sec)
以上的两个 SQL 语句在使用索引 cpao_settle_date_id 的时候两个字段都使用到了,因此过滤性应该更好,我们将上面的3种情况分别称之为 situation_unique_key_id,situation_without_key_id,situation_plain_key_id,以方便我们分析问题。
为什么在 id 为 unique 的时候联合索引只使用了其中的一个字段而没有字段 id ?
MySQL 有一个很好的东东叫 optimizer trace,它提供了 MySQL 执行计划生成的各个阶段的详细信息,其中索引部分的分析更是详细,但是由于 optimizer trace 的东西比较多,我们在分析的时候只将本文相关的内容进行展开,optimizer trace 的详细使用。
打开并使用 optimizer_trace 功能,观察situation_unique_key_id 的代价生成过程的:
mysql set optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec) mysql select * from cpa_order where settle_date=2015-11-05 and id 15 \G 3 rows in set (0.00 sec) mysql select * from information_schema.OPTIMIZER_TRACE\G
在 range 代价计算后,优化器会选择一个代价较小的 index 生成一个 read_plan 缓存起来,根据下面的代价计算过程可以看到,索引在代价计算过程中虽然是相等的,但先入为主,选择的其实是 id 这个索引。
range 部分的代价计算过程:
"range_scan_alternatives": [ "index": "id", "ranges": [ "15 id" "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": true "index": "cpao_settle_date_id", "ranges": [ "2015-11-05 = settle_date = 2015-11-05 AND 15 id" "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": false, "cause": "cost"
表的索引选择过程,主要是 ref range 的索引方式的选择:
"considered_execution_plans": [ "plan_prefix": [ "table": "`cpa_order`", "best_access_path": { "considered_access_paths": [ "access_type": "ref", "index": "cpao_settle_date_id", "rows": 7, "cost": 3.4, "chosen": true "access_type": "range", "rows": 3, "cost": 5.21, "chosen": false "cost_for_plan": 3.4, "rows_for_plan": 7, "chosen": true
可以看到优化器在比较 ref range 的代价的时候,ref 的代价更小,所以选择的是ref,到这里我们觉得选择 ref 是“合理”的,但是当我们想到联合索引的作用时,我们应该觉得这是“不正常的”,至少这不应该是最终的索引选择方式。
观察 situation_without_key_id 的代价及生成过程,其 optimizer_trace 如下:
range 部分的代价计算过程:
"analyzing_range_alternatives": { "range_scan_alternatives": [ "index": "cpao_settle_date_id", "ranges": [ "2015-11-05 = settle_date = 2015-11-05 AND 15 id" "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": true "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans"
表的索引选择过程:
"considered_execution_plans": [ "plan_prefix": [ "table": "`cpa_order`", "best_access_path": { "considered_access_paths": [ "access_type": "ref", "index": "cpao_settle_date_id", "rows": 7, "cost": 3.4, "chosen": true "access_type": "range", "rows": 3, "cost": 5.21, "chosen": false "cost_for_plan": 3.4, "rows_for_plan": 7, "chosen": true
可以看到,由于 where 条件中只有 cpao_settle_date_id id 部分,索引选择的仍是ref, 其代价的计算结果与 situation_unique_key_id 中的代价是一致的,但是在 optimizer_trace 后面发现了如下的优化:
"attaching_conditions_to_tables": { "original_condition": "((`cpa_order`.`settle_date` = 2015-11-05) and (`cpa_order`.`id` 15))", "attached_conditions_computation": [ "access_type_changed": { "table": "`cpa_order`", "index": "cpao_settle_date_id", "old_type": "ref", "new_type": "range", "cause": "uses_more_keyparts"
这里我们不难看出,在计算的结尾处优化器做了个优化,就是把 id 字段也考虑了进来,我们根据 attached_conditions_computation 的提示找到了如下代码:
if (tab- type == JT_REF // 1) !tab- ref.depend_map // 2) tab- quick // 3) (uint) tab- ref.key == tab- quick- index // 4) tab- ref.key_length tab- quick- max_used_key_length) // 5) tab- type=JT_ALL; use_quick_range=1; tab- use_quick=QS_RANGE; tab- ref.key= -1; tab- ref.key_parts=0;
结合注释,我们可以这样理解:
ref 与 range 使用的是相同的索引; 当前 table 选择的索引采用的是ref; ref key 的使用的长度小于 range 的长度,则优先使用 range。因此,在 situation_without_key_id 时,三个条件都满足,所以使用了 range 中的联合索引,那为什么 situation_unique_key_id 没有使用 id 呢,原因是在range 的代价计算过程中使用的是 id 这个索引,导致 unique id 这个索引与联合索引 cpao_settle_date_id 并不是同样的索引,不满足第一个条件,因此不进行优化。
有了上面的分析,我们观察 situation_plain_key_id 的代价及生成过程,situation_plain_key_id 在 range 的代价计算过程中选择的是 cpao_settle_date_id 索引,计算过程是将后者的计算结果与前者进行比较,因此即使相等,也是先入为主,其optimizer_trace如下:
range 部分的代价计算过程:
"range_scan_alternatives": [ "index": "cpao_settle_date_id", "ranges": [ "2015-11-05 = settle_date = 2015-11-05 AND 15 id" "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": true "index": "id", "ranges": [ "15 id" "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": false, "cause": "cost"
表的索引选择过程:
"considered_execution_plans": [ "plan_prefix": [ "table": "`cpa_order`", "best_access_path": { "considered_access_paths": [ "access_type": "ref", "index": "cpao_settle_date_id", "rows": 7, "cost": 3.4, "chosen": true "access_type": "range", "rows": 3, "cost": 5.21, "chosen": false "cost_for_plan": 3.4, "rows_for_plan": 7, "chosen": true
结合上面的分析我们发现,ref range 选择都是索引 cpao_settle_date_id,因此在最后的选择阶段也会进行索引的优化,与开头的问题表现相符。
range 代价计算过程优化器在索引选择的过程中会将where 条件、join 条件等信息进行收集,对于非等值的索引会放到 possible keys 中,进行 range 部分的代价计算,对于等值相关字段的索引会进行 ref 部分的代价计算,如果是单表,其主要过程如下:
调用 get_key_scans_params 从已知的索引中选择一个代价最小的 read_plan,利用 read_plan 生成一个读表的计划,缓存至 tab- quick 中;
在 best_access_path 中计算: 全表的代价 如果有覆盖索引则计算覆盖索引的代价 如果有quick,则利用一些校验值计算上一步产生的 range 的代价然后取其中最小的值用做当前表的代价;
在 make_join_select 中对已经生成的执行计划进行较正,如 situation_plain_key_id 的优化部分。多表的计算过程更为复杂,不在此描述。
为什么在 id 为 unique 的时候联合索引只使用了其中的一个字段而没有字段 id ?
由于 situation_unique_key_id 中在计算 range 的过程中使用的是索引 id 而不是 cpao_settle_date_id,因此不符合最后优化的条件,因此只使用了 cpao_settle_date_id 的前一部分而没有使用 id,这是优化器在实现过程中的问题。
range 代价计算过程可能引起的问题我们已经了解了 range 代价计算的过程,可以发现可能会有以下问题:
当多个索引得到的代价是相同的,由于先入为主,只能缓存第一个,所以会有索引出错的问题; 每一次计算 range 的代价都会将缓存清空,如 order by limit 操作,这样有可能将之前的索引清空且走错索引,详情见 bug#78993。当执行计划出错的时候,我们可以有效的利用 optimizer_trace 来进行初步的分析,大部分还是有解的。另外由于执行计划的内容比较多,从本篇起,小编会尽量将优化器相关的东西给大家介绍一下,主要包括 optimizer_swith 的选项、含义、作用、以及在内核中是如何实现的,达到一起学习的目的。
【七天深入MySQL实战营】答疑汇总Day7 MySQL分支 - AliSQL 的性能优化实战 【最后一课】【MySQL分支 - AliSQL 的性能优化实战】 讲师:黄忠,阿里云资深技术专家。 课程内容:深入介绍AliSQL的几个核心通用性能优化技术;抽丝剥茧,带你整体学习从问题提出到解决方案落地全过程。 答疑汇总:特别感谢班委@陈亮 同学
【七天深入MySQL实战营】答疑汇总Day6 MySQL表和索引优化实战 【开营第六课】【MySQL表和索引优化实战】 讲师:田杰,阿里云高级运维专家。 课程内容:InnoDB表和索引设计最佳实践;索引设计的分析与优化。 答疑汇总:特别感谢班委@李敏 同学
【七天深入MySQL实战营】答疑汇总Day5 MySQL 开发规约实战 【开营第五课】【MySQL 开发规约实战】 讲师:芦火,阿里云运维专家。 课程内容:SQL语句编写规范;事务的使用与优化;开发中的常见问题与最佳实践。 答疑汇总:特别感谢班委@陈亮 同学
【七天深入MySQL实战营】答疑汇总Day4 MySQL查询优化实战 【开营第四课】【 MySQL查询优化实战】 讲师:苏坡,袋鼠云高级数据库工程师。 课程内容:核心概念及原理;优化流程思路;常见场景下的优化。 答疑汇总:特别感谢班委@李敏 同学
【七天深入MySQL实战营】答疑汇总Day3 MySQL Java 开发实战 【开营第三课,MySQL Java 开发实战】 讲师:义泊,阿里云技术专家。 课程内容:深入浅出ORM框架MyBatis;连接池框架HikariCP和druid的剖析和最佳实践;Java应用性能问题诊断技巧。 答疑汇总:特别感谢班委@陈亮 同学
【七天深入MySQL实战营】答疑汇总Day2 MySQL 高并发场景实战 【开营第二课,MySQL 高并发场景实战】 讲师: 凌洛,阿里云数据库解决方案专家。 课程内容:高并发场景下MySQL数据库的技术挑战;如何用RDS MySQL支撑高并发业务;高并发场景数据库运维最佳实践。 答疑汇总:特别感谢班委@李敏 同学
【七天深入MySQL实战营】答疑汇总Day1 MySQL MGR 8.0 高可用实战 【开营第一课,MySQL MGR 8.0 高可用实战】 讲师:沃趣科技 MySQL DBA张彦东老师。 课程内容:掌握集群架构和数据同步原理;掌握集群性能分析和流量控制;掌握MGR使用场景和常见高可用方案。 答疑汇总:特感谢班委@李敏 同学
MySQL · 答疑解惑 · MySQL 的那些网络超时错误 我们在使用/运维 MySQL 过程中,经常会遇到一些网络相关的错误,比如: Aborted connection 134328328 to db: test user: root host: 127.0.0.1 (Got timeout reading communication packets)
MySQL · 答疑解惑 · MySQL 锁问题最佳实践 最近一段时间处理了较多锁的问题,包括锁等待导致业务连接堆积或超时,死锁导致业务失败等,这类问题对业务可能会造成严重的影响,没有处理经验的用户往往无从下手。下面将从整个数据库设计,开发,运维阶段介绍如何避免锁问题的发生,提供一些最佳实践供RDS的用户参考。 在数据库设计阶段,引擎
db匠 rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/
相关文章
- MySQL 遇到的问题以及解决方案
- Mysql 5.7 索引使用规则和设计优化
- 【MySQL】计算 TPS,QPS 的python 脚本
- mysql中使用instr替换like
- mysql function动态执行不同sql语句
- mysql 必知必会整理—sql 计算函数[六]
- 【转载】彻底解决Linux下MySQL 5.5的中文乱码问题
- mysql xtrabackup在线搭建主从
- mysql入门
- MySQL Explain中key_len的计算
- 数据库内核月报 - 2015 / 06-MySQL · TokuDB · TokuDB数据文件大小计算
- mysql 必知必会整理—sql 计算函数[六]
- MySQL · TokuDB · checkpoint过程
- [转]MySQL导入.sql文件及常用命令
- PHP MySQL 插入多条数据
- mysql_server下载安装
- mysql命令行中包含table的命令
- MySQL-系统信息函数
- [ 云计算 | Azure ] 配置Azure Database for MySQL数据库的时区