【MySQL】全索引扫描的bug

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 一 简介  在检查某业务数据库的slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where条件字段存在正确的组合索引,正确的情况下优化器应该选择组合索引,而非为啥会导致慢查询呢? 且看本文慢慢分析。
一 简介
  在检查某业务数据库的slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where条件字段存在正确的组合索引,正确的情况下优化器应该选择组合索引,而非为啥会导致慢查询呢? 且看本文慢慢分析。
二 分析
  案例中的MySQL数据库版本 5.6.16 将生产环境的sql做适当修改,where条件不变。读者朋友可以测试一下其他的版本。
  1. root@rac1 10:48:11>explain select id,
  2.     -> gmt_create,
  3.     -> gmt_modified,
  4.     -> order_id,
  5.     -> service_id,
  6.     -> seller_id,
  7.     -> seller_nick,
  8.     -> sale_type
  9.     -> from lol
  10.     -> where seller_id= 1501204
  11.     -> and service_id= 1
  12.     -> and sale_type in(3, 4)
  13.     -> and use_status in(3, 4, 5, 6)
  14.     -> and process_node_id= 6 order by id desc limit 0,20 \G
  15. *************************** 1. row ***************************
  16.            id: 1
  17.   select_type: SIMPLE
  18.         table: lol
  19.          type: index
  20. possible_keys: idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtype
  21.           key: PRIMARY
  22.       key_len: 8
  23.           ref: NULL
  24.          rows: 3076
  25.         Extra: Using where
  26. 1 row in set (0.00 sec)
分析
MySQL选择的执行计划利用主键进行访问数据。注意执行计划中的 access type是index,而index 意味着这个SQL在查询二级索引的时候,对二级索引进行了全索引扫描,根本没有进行过滤
这个行为是不合理的,因为where条件中含有 in 查询,合理的执行计划的access type应该是range。
我们采用强制索引,看看结果

  1. root@rac1 10:48:07>explain select id,
  2.     -> gmt_create,
  3.     -> gmt_modified,
  4.     -> order_id,
  5.     -> service_id,
  6.     -> seller_id,
  7.     -> seller_nick,
  8.     -> sale_type
  9.     -> from lol force index(idx_sidustsvidtype)
  10.     -> where seller_id= 1501204
  11.     -> and service_id= 1
  12.     -> and sale_type in(3, 4)
  13.     -> and use_status in(3, 4, 5, 6)
  14.     -> and process_node_id= 6 order by id desc limit 0,20 \G
  15. *************************** 1. row ***************************
  16.            id: 1
  17.   select_type: SIMPLE
  18.         table: lol
  19.          type: range
  20. possible_keys: idx_sidustsvidtype
  21.           key: idx_sidustsvidtype
  22.       key_len: 19
  23.           ref: NULL
  24.          rows: 5178
  25.         Extra: Using where; Using filesort
  26. 1 row in set (0.00 sec)
分析
   强制加上索引之后的执行计划是符合预期的,执行sql的时间由 1.57s 减少为 0.01s 。因此我们推测是在优化器选择索引的时候出现了问题
结合源码和optimize_trace我们发现第一阶段优化的时候,优化器确实选择了idx_sidustsvidtype 并且选择采用range访问,因为sql 语句中含有order by,在optimizer试图优化 order by limit的时候
清空了保存访问方式的quick变量(原本保存的是range,但是被请空),最终发现采用排序索引(这里是id)的代价高于组合索引(这里是idx_sidustsvidtype)时,还是选择了idx_sidustsvidtype
但是悲剧的是这时候正确的访问方式已经被清空,无法还原,这就是这个 bug# 78993  的根本成因。
根据分析,我们还可以使用另一种解决方法----去掉 order by 。当然这个对业务所有入侵必须和开发沟通确认sql的结果集是否唯一,如果不唯一还是要使用其他方法。

  1. root@rac1 10:48:15>explain select id,
  2.     -> gmt_create,
  3.     -> gmt_modified,
  4.     -> order_id,
  5.     -> service_id,
  6.     -> seller_id,
  7.     -> seller_nick,
  8.     -> sale_type
  9.     -> from lol
  10.     -> where seller_id= 1501204
  11.     -> and service_id= 1
  12.     -> and sale_type in(3, 4)
  13.     -> and use_status in(3, 4, 5, 6)
  14.     -> and process_node_id= 6 \G
  15. *************************** 1. row ***************************
  16.            id: 1
  17.   select_type: SIMPLE
  18.         table: lol
  19.          type: range
  20. possible_keys: idx_sellerid,idx_uts_stp,idx_sid_stpe,idx_sidustsvidtype
  21.           key: idx_sidustsvidtype
  22.       key_len: 19
  23.           ref: NULL
  24.          rows: 5178
  25.         Extra: Using where
  26. 1 row in set (0.00 sec)
三 总结 
a 修改SQL,添加正确hint。
b 去掉不必要的order by 需要和开发沟通确认是否影响业务逻辑。
c 修改优化的bug,保留多个访问路径,不清理保存访问方式的quick变量,发现orderby 的代价高于组合索引时,可以选择最优的访问路径。
特别感谢 江疑 的分析,同时也推荐 排序sql升级5.6变慢原因分析   
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
22 0
|
1月前
|
关系型数据库 MySQL 数据库
mysq优化---mysql执行计划解释、优化查询、数据库结构、服务器
mysq优化---mysql执行计划解释、优化查询、数据库结构、服务器
|
8月前
|
关系型数据库 MySQL 索引
MySQL命中索引代码实验案例
MySQL命中索引代码实验案例
|
存储 关系型数据库 MySQL
MySQL-索引优化篇(2)_使用索引扫描来优化排序
MySQL-索引优化篇(2)_使用索引扫描来优化排序
69 0
|
存储 关系型数据库 MySQL
【Mysql】索引的创建删除以及使用的代价
【Mysql】索引的创建删除以及使用的代价
|
存储 SQL 关系型数据库
mysql为什么选错索引?怎么解决?
mysql为什么选错索引?怎么解决?
|
关系型数据库 MySQL 索引
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(四)
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(四)
116 0
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(四)
|
存储 SQL 关系型数据库
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(二)
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(二)
168 0
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(二)
|
关系型数据库 MySQL 索引
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(一)
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(一)
119 0
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(一)
|
关系型数据库 MySQL 索引
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(三)
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(三)
117 0
MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描(三)