一个奇怪的MySQL慢查询,打懵了一群不懂业务的DBA!
云栖号资讯:【点击查看更多行业资讯】
在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来!
前言
最近,开发人员需要定期的删除表里一定时间以前的数据,SQL如下:
mysql > delete from testtable WHERE biz_date <= '2017-08-21 00:00:00' AND status = 2 limit 500\G
前段时间在优化的时候,我们已经在相应的查询条件上加上了索引,如下:
KEY `idx_bizdate_st` (`biz_date`,`status`)
但是实际执行的SQL依然非常慢,为什么呢,我们来一步步分析验证下。
分析
表上的字段既然都有索引,那么按照之前的文章分析,是两个字段都可以走上索引的。
既然能够利用索引,表的总大小也就是200M左右,那么为什么形成了慢查呢?
我们查看执行计划,去掉limit 后,发现他选择了走全表扫描。
mysql > desc select * from testtable WHERE biz_date <= '2017-08-21 00:00:00'; +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | testtable | ALL | idx_bizdate_st | NULL | NULL | NULL | 980626 | Using where | +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) -- 只查询biz_date -- 关键点:rows:980626;type:ALL mysql > desc select * from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2; +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | testtable | ALL | idx_bizdate_st | NULL | NULL | NULL | 980632 | Using where | +----+-------------+-----------+------+----------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) -- 查询biz_date + status -- 关键点:rows:980632;type:ALL mysql > desc select * from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2 limit 100; +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ | 1 | SIMPLE | testtable | range | idx_bizdate_st | idx_bizdate_st | 6 | NULL | 490319 | Using index condition | +----+-------------+-----------+-------+----------------+----------------+---------+------+--------+-----------------------+ 1 row in set (0.00 sec) -- 查询biz_date + status+ limit -- 关键点:rows:490319; mysql > select count(*) from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.34 sec) mysql > select count(*) from testtable WHERE biz_date <= '2017-08-21 00:00:00'; +----------+ | count(*) | +----------+ | 970183 | +----------+ 1 row in set (0.33 sec) mysql > select count(*) from testtable; +----------+ | count(*) | +----------+ | 991421 | +----------+ 1 row in set (0.19 sec) mysql > select distinct biz_status from testtable; +------------+ | biz_status | +------------+ | 1 | | 2 | | 4 | +------------+
通过以上查询,我们可以发现如下几点问题:
- 通过 biz_date 预估出来的行数 和 biz_date + status=2 预估出来的行数几乎一样,为98w。
- 实际查询表 biz_date + status=2 一条记录都没有。
- 整表数据量达到了99万,MySQL发现通过索引扫描需要98w行(预估)
因此,MySQL通过统计信息预估的时候,发现需要扫描的索引行数几乎占到了整个表,放弃了使用索引,选择了走全表扫描。
那是不是他的统计信息有问题呢?我们重新收集了下表统计信息,发现执行计划的预估行数还是一样,猜测只能根据组合索引的第一个字段进行预估(待确定)。
那我们试下直接强制让他走索引呢?
mysql > select * from testtable WHERE biz_date <= '2017-08-21 00:00:00' and status = 2; Empty set (0.79 sec) mysql > select * from testtable force index(idx_bizdate_st) WHERE biz_date <= '2017-08-21 00:00:00' and status = 2; Empty set (0.16 sec)
我们发现,强制指定索引后,查询耗时和没有强制索引比较,的确执行速度快了很多,因为没有强制索引是全表扫描嘛!但是!依然非常慢!
那么还有什么办法去优化这个本来应该很快的查询呢?
大家应该都听说过要选择性好的字段放在组合索引的最前面?
选择性好的索引在前面并不是对所有的场景都通用的,这个场景可以将status放前面,sql速度会更快。
那,能不能让他不要扫描索引的那么多范围呢?之前的索引模型中也说过,MySQL是通过索引去确定一个扫描范围,如果能够定位到尽可能小的范围,那是不是速度上会快很多呢?
并且业务逻辑上是定期删除一定日期之前的数据。所以逻辑上来说,每次删除都是只删除一天的数据,直接让SQL扫描一天的范围。那么我们就可以改写SQL啦!
mysql > select * from testtable WHERE biz_date >= '2017-08-20 00:00:00' and biz_date <= '2017-08-21 00:00:00' and status = 2; Empty set (0.00 sec) mysql > desc select * from testtable WHERE biz_date >= '2017-08-20 00:00:00' and biz_date <= '2017-08-21 00:00:00' and status = 2; +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ | 1 | SIMPLE | testtable | range | idx_bizdate_st | idx_bizdate_st | 6 | NULL | 789 | Using index condition | +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec) -- rows降低了很多,乖乖的走了索引 mysql > desc select * from testtable WHERE biz_date >= '2017-08-20 00:00:00' and biz_date <= '2017-08-21 00:00:00' ; +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ | 1 | SIMPLE | testtable | range | idx_bizdate_st | idx_bizdate_st | 5 | NULL | 1318 | Using index condition | +----+-------------+------------------+-------+----------------+----------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec) -- 即使没有status,也是肯定走索引啦
小结
这个问题,我原本打算用hint,强制让他走索引,但是实际上强制走索引的执行时间并不能带来满意的效果。结合业务逻辑,来优化SQL,是最好的方式,也是终极法宝,一定要好好利用。不了解业务的DBA,不是一个好DBA...
【云栖号在线课堂】每天都有产品技术专家分享!
课程地址:https://yqh.aliyun.com/zhibo立即加入社群,与专家面对面,及时了解课程最新动态!
【云栖号在线课堂 社群】https://c.tb.cn/F3.Z8gvnK
原文发布时间:2020-04-24
本文作者:互联网架构师
本文来自:“互联网架构师 微信公众号”,了解相关信息可以关注“互联网架构师”
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
Redis 10亿数据量只需要100MB内存,为什么这么牛?
云栖号资讯:【点击查看更多行业资讯】在这里您可以找到不同行业的第一手的上云资讯,还在等什么,快来! 本文主要和大家分享一下redis的高级特性:bit位操作。本文redis试验代码基于如下环境:操作系统:Mac OS 64位版本:Redis 5.0.7 64 bit运行模式:standalone mode redis位操作 reids位操作也叫位数组操作、bitmap,它提供了SETBIT、GETBIT、BITCOUNT、BITTOP四个命令用于操作二进制位数组。先来看一波基本操作示例: SETBIT语法:SETBIT key offset value即:命令 key 偏移量 0/1setbit命令用于写入位数组指定偏移量的二进制位设置值,偏移量从0开始计数,且只允许写入1或者0,如果写入非0和1的值则写入失败: GETBIT语法:GETBIT key offset即:命令 key 偏移量gitbit命令用于获取位数组指定偏移量上的二进制值: BITCOUNT语法:BITCOUNT key即:命令 keybitcount命令用于获取指定key的位数组中值为1的二进制位的数量,之前我们写...
- 下一篇
Service 的两种启动方式和「Service 与 Activity 数据交互」
1. Service 的两种启动方式 Activity 中可以有两种方式启动 Service,不同方式启动时 Service 的生命周期也不一样,现在在 Activity 中定义四个 Button,分别是 startService、stopService、bindService、unbindService,Service 中各生命周期中分别打印 Log 日志,通过日志查看生命周期执行情况: // MainActivity.kt class MainActivity : AppCompatActivity(){ var mService: MyService? = null var isBind = false override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_main) val intent = Intent(this, MyService::class.java) val conn = obje...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- CentOS7,CentOS8安装Elasticsearch6.8.6
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- Linux系统CentOS6、CentOS7手动修改IP地址
- CentOS7安装Docker,走上虚拟化容器引擎之路
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- SpringBoot2全家桶,快速入门学习开发网站教程
- CentOS7设置SWAP分区,小内存服务器的救世主
- Docker快速安装Oracle11G,搭建oracle11g学习环境