一次MySQL线上慢查询分析及索引使用

秒速五厘米 2022-04-05 08:10 196阅读 0赞

本文由作者郑智辉授权网易云社区发布。

# 0.前言 #

本文通过分析线上MySQL慢查询日志，定位出现问题的SQL，进行业务场景分析，结合索引的相关使用进行数据库优化。在两次处理问题过程中，进行的思考。

# 1.简要描述 #

在九月底某个新上的游戏业务MySQL慢查询日志

# Time: 2017-09-30T14:56:13.974292+08:00
    # Query_time: 6.048835  Lock_time: 0.000038 Rows_sent: 0  Rows_examined: 12884410SET timestamp=1506754573;SELECT status, sdkid, appid, app_orderid, matrix_orderid, pay_orderid, platform, sdk_version, app_channel, pay_channel, serverid, roleid, INET6_NTOA(userip), deviceid, devic
    e_name, productid, product_count, product_name, matrix_uid, app_uid, order_currency, order_price, activityid, create_time, expired_time, pay_method, pay_mode, ship_url, rese
    rved, pay_time, recv_time, ship_time, pay_sub_method, pay_amount, free_amount, pay_currency, pay_total_money, pay_free_money, credit, pay_fee, extra_columns, is_test    FROM MatrixOrderSucc    WHERE status >= 200 AND status < 300 AND recv_time < DATE_SUB(NOW(), interval 20 SECOND) AND recv_time > DATE_SUB(NOW(), interval 24 HOUR)    ORDER BY retry    LIMIT 1;

*  第一次处理方式：在该表上添加了(recv\_time,status)索引，然后慢查询没有；

正当以为事情解决的时候，该游戏10月份大推，然后数据量激增，然后慢查询又出现了。

*  第二次处理方式：删除之前的索引，然后改为对(status,recv\_time）添加索引。然后至今该SQL未出现慢查询了。

线上环境说明：

*  MySQL 5.7.18
 *  表引擎为Innodb
 *  系统内核：Debian 3.16.43-2

接下来说说这两次处理过程中的测试和分析。

# 2.SQL分析 #

*  sql分析：
    
     *  当时九月底时该表的数据达到1200w行，但是由于没有匹配得上的索引,所以全表扫描耗时6秒多。
 *  业务分析:
    
     *  联系了开发同事，了解一下这个语句的业务场景。 该语句用于查找失败订单(status标记）并且时间在20秒之前一天以内(recv\_time)的数据。并得知其实满足status条件的订单其实只是少量的。

小结：  
可以看出数据和固定时间范围内的数据量有关系。10月份大推后，固定时间范围内的数据激增。

# 3.第一次处理 #

### 3.1 数据情况 ###

将数据导到测试环境进行了数据测试。  
通过下图的sql，数据基本分析如下：

*  满足单独status条件的数据大概就3w条   
    *  满足单独recv_time条件的数据大概是77w条  
    *  虽然status字段的数据离散型不是很好，但是满足条件的数据很少，数据的筛选性还是很不错的。

![image][]

### 3.2 测试 ###

加了索引之后。(recv\_time,status)

mysql> explain select status, sdkid, appid, app_orderid, matrix_orderid, pay_orderid, platform, sdk_version, app_channel, pay_channel, serverid, roleid, INET6_NTOA(userip), deviceid, device_name, productid, product_count, product_name, matrix_uid, app_uid, order_currency, order_price, activityid, create_time, expired_time, pay_method, pay_mode, ship_url, reserved, pay_time, recv_time, ship_time, pay_sub_method, pay_amount, free_amount, pay_currency, pay_total_money, pay_free_money, credit, pay_fee, extra_columns, is_test from MatrixOrderSucc WHERE status >= 200 AND status < 300 AND recv_time < DATE_SUB('2017-10-12 14:48:49', interval 20 SECOND) AND recv_time > DATE_SUB('2017-10-12-14:48:49', interval 24 HOUR);
    +----+-------------+-----------------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------+
    | id | select_type | table           | partitions | type  | possible_keys | key       | key_len | ref  | rows    | filtered | Extra                 |
    +----+-------------+-----------------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------+
    |  1 | SIMPLE      | MatrixOrderSucc | NULL       | range | recv_time     | recv_time | 6       | NULL | 1606844 |    11.11 | Using index condition |
    +----+-------------+-----------------+------------+-------+---------------+-----------+---------+------+---------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)

执行计划：刚加上的索引确实被用上了。  
正式环境临时添加了该索引之后慢查询确实消失了。

### 隐忧： ###

从执行计划里的key\_len可以知道该sql，在进行数据筛选的时候只以recv\_time进行数据过滤的，status字段并没有用上场。因为联合索引左侧字段用了范围查询，则其他字段无法用上。

背景知识
    数据查找过程：1. 如果走了辅助索引* 先去辅助索引查找。返回索引字段和主键字段(index_column, pk column)，假设数据N行，那么这里是N次的数据顺序访问* 再去聚集索引查找整行数据：N次随机访问
    数据搜索代价：b+树高度次随机访问+N次顺序访问+N次随机访问。
    ps:当然如果辅助索引能覆盖了SQL查询的字段，就不需要去主表查完整整行数据了。
    
    
    2.如果直接全表扫描：
    数据搜索代价：全表总数次顺序访问
    
    磁盘顺序访问和随机访问时间消耗大概查了两个数量级。
    
    所以有可能：MySQL会估算一下，两者的代价来决定是否走索引查找。

![image][]

所以上面的sql在mysql 5.6之前执行过程：

1.  通过recv\_time条件在辅助索引搜索，返回N条记录
2.  聚集索引查找整行数据
3.  返回到server 段然后再进行status字段的条件筛选
4.  server层返回数据给客户端

然而，MySQL 5.6之后多了index condition push down的优化功能，就是能将索引筛选下推。  
例如:  
执行计划里的Using index condition是index push down的意思，是mysql 5.6后做的优化，  
这个功能的效果就是，能将步骤3的数据筛选放在步骤2之前，因为既然从辅助索引取回的数据包含status字段，那么进行一下数据过滤，然后再去主表拿数据，就能减少随机访问的次数。

# 4.第二次处理 #

### 4.1 线上数据 ###

*  10月游戏大推每日数据激增。此时全表数据大概2800w。
 *  再去通过explain 查看执行计划的时候，已经从原来的走索引，又变回了全表扫描。
 *  慢查询的时间从之前的6秒上升到18秒

### 4.2 问题 ###

*  为什么之前走索引现在会不走了？  
    有同事说：在应用层 force index强制走之前的索引就好了。因为可能是MySQL的优化器优化得不够好。导致走了不良的执行计划。 我认为：这个问题和应用问题和MySQL优化关系不大，是索引建得不对。如果在应用层做修改，第一需要经过测试回归才能发布版本，耗时长；第二，force index 感觉比较死板，万一以后表结构发生变更，这个索引不存在了，会存在问题。

线上数据分析:

*  单独满足recv\_time条件的数据达到600多万行。（因为游戏大推，每日数据激增),原来只有77w行。
 *  单独满足status条件的数据变化不大。

MySQL采用全表扫描的结论：

*  因为辅助索引返回的数据激增，导致主表随机访问的次数增加，发现还不如直接全表扫描来得快。

当然MySQL的SQL优化代价模型应该包含很多因素，后续有待研究。

### 4.3 测试 ###

还是利用之前导出的1200w的测试数据，对(status,recv\_time)条件索引进行测试。  
通过下图可以看到:

*  查询能走上索引，并且key\_len=10，表明索引的两列都派上用上了。
 *  并且执行计划里的rows数量明显比(recv\_time，status)索引的查询要少很多。![image][]

### 4.4 问题 ###

#### 4.4.1 上文不是联合索引用了范围查询，第二列排不上用场吗？ 为什么这里能用recv\_time搜索数据？ ####

我的理解：  
1.status虽然在sql里看起来是范围查询，但是MySQL能感知到status数据的离散程度，然后将status查询改为IN(200)，IN在MySQL里不算范围查询。  
2.其实这个挺好理解的。结合索引的B+树的结构。 如果是IN，相当于在辅助索引里通过第一列得出的是N个B+子树(以第二索引字段进行构建的子树）,那么肯定还是可以对第二列进行二叉树搜索的。

所以关键就是在第一列搜索完后，剩下的数据是否能对第二列recv\_time进行二叉树搜索。

### 4.4.2 为什么recv\_time范围查询没做上面的IN操作转换？ ###

因为recv\_time真的是足够离散。

### 4.5 索引选择 ###

在索引选择，在有(recvtime,status) (status,recvtime) (status)三个索引下

可以看出系统选择了(status,recv\_time)索引。  
因此在正式环境删除之前的索引，建新的索引，慢查询消失。

## 5.小结 ##

5.1 不是离散性不好的字段就不能加索引，也要看数据筛选性能  
5.2 时间类型的字段不大合适放在联合索引的左边  
5.3 索引最左匹配原则 5.4 测试说明  
5.4.1 数据是通过mysqldump不加锁方式导到测试环境重新import建立的。  
5.4.2 测试的SQL:最好不要选select count() from table ，因为在这个场景中select count() 会走索引扫描，是不必再到主表拿整行数据的；和实际场景的SQL是不一样。

参考文档

*  [数据库索引设计概要][Link 1]
 *  [MySQL索引背后的数据结构及算法原理][MySQL]
 *  [MySQL索引原理及慢查询优化][MySQL 1]
 *  [B+Tree Indexes and InnoDB][B_Tree Indexes and InnoDB]
 *  [MySQL官网手册][MySQL 2]

更多网易技术、产品、运营经验分享请访问[网易云社区][Link 2]。

相关文章：  
【推荐】 [质量评估面面观--聊一聊软件上线前的质量评估][--]  
【推荐】 [Question | 移动端虚拟机注册等作弊行为的破解之道][Question _]  
【推荐】 [视觉设计师的进化][Link 3]

[image]: 
[Link 1]: https://draveness.me/sql-index-intro
[MySQL]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html
[MySQL 1]: https://tech.meituan.com/mysql-index.html
[B_Tree Indexes and InnoDB]: https://www.slideshare.net/ovaistariq/pluk2011-btreeindexesandinnodb
[MySQL 2]: https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html
[Link 2]: https://sq.163yun.com/blog?tag=M_tg_919_72
[--]: https://juejin.im/post/5bd69045518825291c252cad
[Question _]: https://juejin.im/post/5bad90f8f265da0a87266732
[Link 3]: https://www.cnblogs.com/163yun/p/9547949.html