开发者社区> db匠> 正文

MySQL · 捉虫动态 · 5.6中ORDER BY + LIMIT 错选执行计划

简介: 问题描述 create table t1(id int auto_increment primary key, a int, b int, c int, v varchar(1000), key iabc(a,b,c), key ic(c)) engine = innodb; insert into t1 select null,null,null,null,null; insert into
+关注继续查看

问题描述

create table t1(id int auto_increment primary key, a int, b int, c int, v varchar(1000), key iabc(a,b,c), key ic(c)) engine = innodb;

insert into t1 select null,null,null,null,null;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;

update t1 set a=id/2, b=id/4, c=6-id/8, v=repeat('a',1000);

explain select id from t1 where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | t1    | index | iabc,ic       | iabc | 15      | NULL |   32 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+

explain select id from t1 force index (iabc) where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | t1    | range | iabc          | iabc | 5       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+

从SELECT语句中可以看出,同样的语句,使用同样的INDEX,但使用了FORCE INDEX之后选择的执行计划不一样。当然如果数据量大的话,实际的执行性能也会差别很大。使用RANGE scan显然要优于INDEX scan的全扫描。

另外此bug引发的另一个问题是,由于使用了LIMIT语句,导致选择的INDEX不是最优的INDEX。

问题分析:

使用如下命令打开Optimizer trace

SET OPTIMIZER_TRACE_MAX_MEM_SIZE=268435456(i.e. 256M);
SET optimizer_trace="enabled=on";

执行上面的查询语句,可以看到optimizer trace的输出结果如下,请注意里面重点部位的注释(以’//’开头部分):

"considered_execution_plans": [\
              {\
                "plan_prefix": [\
                ],\
                "table": "`t1`",\
                "best_access_path": {\
                  "considered_access_paths": [\
                    {\
                      "access_type": "range",\  
					  // 这里我们可以看到,优化器通过代价选择的最佳访问方式是RANGE scan
                      "rows": 3,\
                      "cost": 2.2146,\
                      "chosen": true\
                    }\
                  ]\
                },\
                "cost_for_plan": 2.2146,\
                "rows_for_plan": 3,\
                "chosen": true\
              }\
            ]\
但是接下来我们可以看到:
       "refine_plan": [\
              {\
                "table": "`t1`",\
				
                "access_type": "index_scan"\ //这里强制使用了INDEX scan
              }\
            ]\

这里显示的是optimizer在执行优化器的第四个阶段,PLAN REFINEMENT的时候,最后选择了INDEX scan。所以我们可以大致确定错误发生的地方。另外有问题的query带有LIMIT,因此基本可以确定问题发生在了make_join_select函数中。

make_join_select函数中有下面一段逻辑:

if (!tab->const_keys.is_clear_all() &&               // 有依赖于常量的索引条件表达式
                   i == join->const_tables &&        // 是第一个非常量表
                   (join->unit->select_limit_cnt <
                    tab->position->records_read) &&  
					// 有Limit条件且需要返回的行数比估计扫描的行数少
                   !(join->select_options & OPTION_FOUND_ROWS))  // 没有SQL_CALC_FOUND_ROWS
            recheck_reason= LOW_LIMIT;  // 这里MySQL开始对Limit语句进行优化
			...
// 检查是否有RANGE scan可以使用
if ((recheck_reason != DONT_RECHECK) &&
                sel->test_quick_select(thd, usable_keys,
                                       used_tables & ~tab->table->map,
                                       (join->select_options &
                                        OPTION_FOUND_ROWS ?
                                        HA_POS_ERROR :
                                        join->unit->select_limit_cnt),
                                       false,   // don't force quick range
                                       interesting_order) < 0)
            {
这里usable_keys是描述可以用来对ORDER BY列进行索引排序的可能的所有索引的MAP。上面的函数会查找这些可用的索引是否可以进行更高效RANGE
扫描。但是通过问题query的条件表达式,这里没有找到对应的RANGE扫描,所以最后的执行计划输出只是使用了一个COVERING index.

问题解决

解决方式是需要将原来已经选好的RANGE scan与用来进行排序的索引扫描代价进行比较,比较哪种扫描方式对于增加ORDER BY操作后的代价更低,进而选择一个代价最优的扫描方式。下面是一个相关的patch。

if (!tab->const_keys.is_clear_all() &&               // 有依赖于常量的索引条件表达式
                   i == join->const_tables &&        // 是第一个非常量表
                   (join->unit->select_limit_cnt <
                    tab->position->records_read) &&  // 有Limit条件且需要返回的行数比估计的扫描的行数少
                   !(join->select_options & OPTION_FOUND_ROWS))  // 没有SQL_CALC_FOUND_ROWS
            recheck_reason= LOW_LIMIT;  //这里MySQL会去对Limit语句进行优化
			...
+              if (recheck_reason != DONT_RECHECK)
               {
-                recheck_reason= DONT_RECHECK;
+                int best_key= -1;
+                ha_rows select_limit= join->unit->select_limit_cnt;
+
+                // 对所有可用的INDEX计算排序代价,选择一个代价最优的INDEX
				 // 注意:这里的usable_keys包含所有可用索引,而不只是原来版本中只包含可以用来排序的索引
+                test_if_cheaper_ordering(tab, join->order, tab->table,
+                                         usable_keys, -1, select_limit,
+                                         &best_key, &read_direction,
+                                         &select_limit);
				 // 如果没有找到任何可用的INDEX,那就默认使用原来的扫描方式
+                if (best_key < 0)
+                  recheck_reason= DONT_RECHECK; // No usable keys
+                else
+                {
+                  // 找到一个最优的INDEX,我们只需要设置可用的INDEX,接下来查看一下是否有RANGE scan即可
+                  usable_keys.clear_all();
+                  usable_keys.set_bit(best_key);
+                  interesting_order= (read_direction == -1 ? ORDER::ORDER_DESC :
+                                      ORDER::ORDER_ASC);
+                }
               }
             }
if ((recheck_reason != DONT_RECHECK) &&
                sel->test_quick_select(thd, usable_keys,
                                       used_tables & ~tab->table->map,
                                       (join->select_options &
                                        OPTION_FOUND_ROWS ?
                                        HA_POS_ERROR :
                                        join->unit->select_limit_cnt),
                                       false,   // don't force quick range
                                       interesting_order) < 0)
            {
			...

可以看到最终效果是:

EXPLAIN SELECT id FROM t1 WHERE a<3 AND b IN (1, 13) AND c>=3 ORDER BY c DESC LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE		t1		range	iabc,ic			iabc	5	NULL	4	Using index condition; Using filesort

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle 过程中执行动态 SQL 或 DDL 语句
如果你用的是 Oracle 8i 及以上的版本,那简单,在过程中用 execute immediate sql_str 就行, sql_str 是一个拼凑的 SQL 语句,但这个动态语句中带参数,或 Select 的结果要 into 到变量中时就要稍加留心一下了。
1036 0
SQL为什么预估执行计划与真实执行计划会有差异?
SQL为什么预估执行计划与真实执行计划会有差异? 一 问题概要 对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的 Runtime Plan 真实执行计划,偶尔会发现两边有不一致的情况,为什么呢?为什么预估执行计划会不准确?怎样才能避免这种情况的发生? 二 问题解答 这是执行计划相关中会被经常问道的问题,也是困扰自己很长时间的问题。
1161 0
MySQL执行计划解读
Explain语法 EXPLAIN SELECT …… 变体: 1. EXPLAIN EXTENDED SELECT …… 将执行计划“反编译”成SELECT语句,运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 2.
1667 0
我是如何在github企业版本上通过ssrf漏洞导致命令执行的?
本文讲的是我是如何在github企业版本上通过ssrf漏洞导致命令执行的?,在我上一篇文章中,我提到了以后攻击的新目标-github企业版,同样也提到了如何去除混淆的ruby代码,并在其中寻找sql注入。再次之后,我就开始阅读很多大牛挖掘github企业版的思路
2163 0
MySQL · 捉虫动态 · 信号处理机制分析
背景 在 AliSQL 上面有人提交了一个 bug,在使用主备的时候 service stop mysql 不能关闭主库,一直显示 shutting down mysql …,到底怎么回事呢,先来看一下 service stop mysql 是怎么停止数据库的。配置 MySQL 在系统启动时启动需要把 MYSQL_BASEDIR/support-files 目录下的脚本 mysql.sev
1466 0
[20120104]稳定一条sql语句的执行计划.txt
[20120104]稳定一条sql语句的执行计划.txthttp://www.itpub.net/thread-1495845-1-1.htmlhttp://space.
459 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一2.5 执行计划中其他信息的含义
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第2章,第2.5节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1443 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一2.4 执行计划各个操作的含义
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第2章,第2.4节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1768 0
+关注
db匠
rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/
497
文章
0
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载