一个奇怪的MySQL慢查询，打懵了一群不懂业务的DBA！-低调大师

一个奇怪的MySQL慢查询，打懵了一群不懂业务的DBA！

2020-04-23 491

云栖号资讯：【点击查看更多行业资讯】
在这里您可以找到不同行业的第一手的上云资讯，还在等什么，快来！

前言

最近，开发人员需要定期的删除表里一定时间以前的数据，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
本文作者：互联网架构师
本文来自：“互联网架构师微信公众号”，了解相关信息可以关注“互联网架构师”

微信关注我们

原文链接：https://yq.aliyun.com/articles/757161

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道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的二进制位的数量，之前我们写...

2020-04-24

756

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...

2020-04-24

499

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。