存储优化(2)-排序引起的慢查询优化

# 摘要 排序引起的慢查询，通常不是那么容易发现,经常和数据分布有关系。往往在业务刚开始时并没有什么问题，但是随着业务的发展，数据分布呈现一种特定的规律，导致了慢查询，或者并不是什么慢查询，但是随着并发请求数增加，数据库的IOPS使用率变高，进一步导致cpu/内存使用率飙高。造成线上故障。 # 问题 因为排序引起的问题遇到很多次 ## 例1：某日收到线上cpu告警 然后查看慢sql日志 大量的慢查询指向了这个查询 ``` SELECT id, prize_id, user_id, name, biz_id FROM play WHERE biz_id = xx AND status = 1 AND prize_type = '大奖' ORDER BY id DESC LIMIT 0, 10 ``` play是抽奖记录表，sql是查抽中奖品的前10个大奖中奖者，来吸引其他用户参与抽奖，**biz_id**建了索引 ## 例2 某日上线一个新功能，在第五次压测时，数据库cpu告警 查看数据库慢日志，**没有一条慢sql**（耗时>100ms）。最后通过查阅代码，sql调用统计。发现有大量下面的SQL调用 ``` SELECT id, commit_id FROM commit_record WHERE biz_id = 'xxx' AND id >= #{fromId} AND id <= #{toId} ``` **biz_id有索引** ## 例3 某日线上服务报API响应时间超过X秒 通过查看应用日志，发现大量`com.mongodb.MongoSocketReadTimeoutException:`mongo的错误。经过多重定位，发现从库的IOPS使用率快接近100%了，同时发现有些慢查询 ``` "query":{"find":"historyRecord","filter":{"bizId":1234567,"version":23},"sort":{"_id":-1},"limit":1}} ``` 索引是bizId,version的联合索引 # 问题分析 这几个查询造成的线上问题的形式虽然各有不同，但本质上都是一样，**无法利用索引排序**，需要用到数据库排序，当内存够大或没超过排序上限时，就会在内存中排序，这样单个查询相对比较快，但是并发量高了，内存容量不够了，需要进行磁盘排序时，就会变得很慢。 然后经过仔细观察，发现容易写出这种语句，忽视了排序造成的风险。常常是**根据主键**排序。开发者容易想当然的以为主键是有索引的，所以排序会走索引，所以不会有什么大问题。但其实像例子中那些案例，都是无法利用索引排序的。 曾经在[mongo索引篇](https://blog.csdn.net/FS1360472174/articlehttps://img.qb5200.com/download-x/details/53589555)介绍联合索引如何创建时也提到过。 总结一下，造成数据库服务问题主要根由是 1. 查询没有利用到索引排序 2. 索引过滤后下面数据仍然有很多，需要扫描排序的数据很多 3. 请求的并发量很高，数据库IOPS使用率高，内存占用高。 # 问题解决 首先，日常开发时避免写出这种SQL，尤其针对数据量比较大的表。或者索引下数据分布可能不均匀的情况。 **线上解决** 收到线上警告，发现是此类问题。 1. 判断业务侧能否降级，即减少此类查询。确保不要影响其他业务。 2. 数据库升级配置（需要做到对业务无影响） 线上问题的临时解决方案只能解一时燃煤之急，真正的解决问题还是需要从查询着手。 **查询优化** 1. 业务侧避免此类查询 从业务侧分析，是不是需要此类查询。比如例3，bizId,version_id是不是本身可以作为有序的，版本号version_id可以设计成有序的，这样就不需要根据主键_id来保持有序 2. 减少并发 是不是所有的这类查询都是必须的，能不能接受缓存。 4. 引入其他存储方案 比如例1，业务需要查询按照时间顺序的中大奖的前N个人。这个业务侧可以将数据保存到在redis中，listz中存topN的数据。然后发现有中大奖的人，扔到redis队列即可。 5. 增加一个联合索引 比如例3可以增加一个bizId,version,_id联合索引 ``` "query":{"find":"historyRecord","filter":{"bizId":1234567,"version":23},"sort":{"_id":-1},"limit":1}} ```