大佬们求救，慢 SQL 问题

[zzhpeng]

问题

描述：订单表，Innodb 存储引擎。排名前几个的慢 sql 语句，有些用了 count(*)，弄到 rds 数据库 cpu 满了，服务器 502.

1 、慢 sql 排名图，如下:

2 、排名第一的慢 sql 执行情况:

3 、explain 排名第一的慢 sql:

自我分析：sql 23ms 也正常呀，难道是锁的问题吗？

4、突然就炸了

5、图看不清问题，可以手动另外打开，尴尬。。。。网站没有放大图功能

6 、show processlist


6 、show processlist

7、阿里的DAS数据库工具

显示大概就是突然的大量激增,首先要防范的就是做限流。可是我的nginx做了限流，但数据库那边顶不住。看来还是要在sql语句设计上着手解决问题。

[takemeaway]

你确定是这一句？

[fiypig]

explain 看看啊

[zzhpeng]

@fiypig 第三张图哈，这个平台不支持放大图片，右键可以看清图

[lovecy]

你这个慢 SQL 排名用的是啥生成的？是读取的慢日志么
索引用的是 idx_storeid_isvirtual，强制指定一个合适的索引试试

[wangyanrui]

看不到图~
八成没有走索引导致的呗，想办法用上即可

[zzhpeng]

@lovecy 阿里云的 rds 的监控工具

[lsongiu]

我咋看截图里的时间单位是 秒？ 这么吓人?

[JasonLaw]

我觉得你的 SQL 语句不太合理。

1. 为什么 LIMIT 1 和 COUNT(*)一起使用？
2. 为什么使用了 COUNT(*)，还要进行无谓的 ORDER BY ？

[pushback]

表结构呢？

[moonvstod]

看索引
看有没有隐式转换
where 条件里面排除条目最多的条件排最前面

[pushback]

count(id),where 和 order 后的字段建立联合索引，然后你这 limit 1 ？

[JasonLaw]

sima_order.is_virtual 是不是只有 0 和 1 两种值？每个值对应的行数是多少？

[JasonLaw]

如果有人好奇“为什么加了 LIMIT 1，COUNT(*)还可以正常工作的话”，可以看一下 https://stackoverflow.com/questions/17020842/mysql-count-with-limit 。

[zzhpeng]

@JasonLaw
1. 为什么 LIMIT 1 和 COUNT(*)一起使用？
回复：框架 orm 自动加上
2. 为什么使用了 COUNT(*)，还要进行无谓的 ORDER BY ？
回复：和订单列表数据共用了一个逻辑数据层并且解耦的有问题，但是这个影响我去掉在查通过数据响应时间看，影响不大

[RadishWind]

看看索引怎么加的

[GTim]

@lovecy ThinkPHP，看到 tp_count 太眼熟了

[egfegdfr]

感觉这个 sql 问题不大，就是一个简单查询， 你在确认下是不是找错了，可能是其他 sql 引起的

[GTim]

@JasonLaw 理解了 Limit 的作用范围就好理解了。如果没有聚合函数，select 是在 order by -> limit 后面执行的，但是有了聚合函数，select 会优于 order by -> limit 执行。

不知道我有没有记错，哎

[wangritian]

盯上了倒数第三行，扫描行数高一个数量级

[Airon]

这个 sql 只是执行次数大，rdsCPU100%之后，平均等待时间长。造成 cpu 飙升的应该不是这一句

[zzhpeng]

@JasonLaw
sima_order.is_virtual 是不是只有 0 和 1 两种值？每个值对应的行数是多少？
回复：确实是只有 0 和 1，1 几乎很少，所以基本上是 0

[zzhpeng]

@RadishWind 看第三张图 explain

[JasonLaw]

@zzhpeng #21 所以你的索引基本没什么用，甚至会降低速度，因为对于每个符合条件的 secondary index 子节点，都会去 clustered index 搜索。

[zzhpeng]

@Airon 这条排在第一的语句很早就有了，我也觉得不像是这条的问题。排在前面的几条 count()语句是在首页的请求。

[GTim]

@zzhpeng 要不监控着，等 cpu 上去的时候执行一下 show processlist 看看是哪个超时了。

[zzhpeng]

@JasonLaw 那我有个疑问哈，为啥么我 explain 的时候是使用了索引的，而且速度是比较快的 23m 。难道是索引太多查找匹配问题，加大了数据库 cpu 资源消耗？

[Airon]

楼上也提到了，看倒数第三行，数据量级变了，应该是直接扫表了，次数还不少。索引和各种限制条件都得优化。

[realpg]

innodb 的表，最好就少 count
另外排除无关干扰把那个 orderby 干掉 虽然感觉不是这里的原因
盲猜索引差异度少，导致用索引效率更低

[JasonLaw]

@GTim #18 你理解错了，具体情况具体分析吧。

1. 你说“如果没有聚合函数，select 是在 order by -> limit 后面执行的”，此问题的例子就能够说明这种说法是错的。
2. 你说“但是有了聚合函数，select 会优于 order by -> limit 执行”，假设 SQL 是 select sum(c1) from t group by c2 order by c2，如果先执行 select sum(c1)，之后的 order by c2 怎么能够成功呢？

[mingl0280]

我建议你在服务器没有压力的时候清空排序数据一条一条测,第一条这个不像是有问题...

[zzhpeng]

@GTim 这个难，那么多超时

[MoYi123]

倒数第三句 sql 看起来更加可疑。

[leoWeek]

可能是其他慢 sql 导致服务器性能下降，sql 都阻塞住了，要看下其他慢 sql 现在的执行情况分析下

[zzhpeng]

@mingl0280 我建议你在服务器没有压力的时候清空排序数据一条一条测,第一条这个不像是有问题...
回复：这个就是当时爆发产生的情况，并不是累计的。

[JasonLaw]

@zzhpeng #26

我主要是表达“条件为 is_virtual = 0，并且大多数的 is_virtual 都是 0”的时候，idx_storeid_isvirtual 其实是没有什么用处的。

你那条语句是直接在同一个环境运行的吗？如果是的话，单独运行肯定跟同其他语句并发运行是不一样的，我不清楚具体的情况，无法给你答案。

[GTim]

@zzhpeng @zzhpeng 看 cpu 图，这个只能在高压的时候看，平时没问题说明语句都挺正常，当然如果这个时候有不正常的，那就太不正常了。找到那条语句优化下

[cnoder]

你这个管理 sql 的界面叫啥

[qwerthhusn]

都 count 了，何不把 order by 去掉？？

[zzhpeng]

@cnoder 阿里的 rds

[wps353]

本质是 in +order by 出问题了。

[zzhpeng]

@qwerthhusn 逻辑复用没解耦问题

[july1115]

排名第一的慢 sql 感觉问题不大啊。？？ 最近也在解决慢 sql 的问题，因为某 sql 语句连表查询太多，直接新建了一个表，先把整理的数据保存的新表里。

[zhangysh1995]

Range 查询 IN 没有用到索引，慢吧？你可以试试其他条件去掉，只来 EXPLAIN 用 IN 这个条件试试。

[RadishWind]

不是 我想看看索引在表结构里面的定义

[xuanbg]

上上周我们的阿里云 RDS 遇到了奇怪的慢 SQL 暴增的现象，但这些慢 SQL 在前一天还都正常得不得了。。。到最后，查询一张只有几十条数据的表都要好几秒！！！估计是物理机 IO 打满、CPU 打满，没办法，只能跑路。迁移可用区后，一切恢复正常。

[mingl0280]

@zzhpeng 就是说你爆发的时候的数据是不准确的啊！！！！！让你平时没炸的时候测，可以更准确地定位有问题的语句（因为不会出现短请求不断重复却因为其它单个长请求卡死）的啊……

[594duck]

总表有多大，你这 Select 语句可能因为超出索引 直接变全表扫描了。读写分离考虑一下。

[594duck]

还有要小心 MYSQL 索引 有索引合并，如果一不小心你没命中索引 就又麻烦了。建议仔细看看。

[594duck]

你看慢请求里，集中在 SIMA 表，扫描行数都是 5K 以上。你总表有多大，然后自己看看有没有用到索引 。这二个是优化点。

[blackeeper]

1，你的这个排序是按照 [执行次数] 来排序的，这个并不准确。
2，你可以看看最大扫描行，和最大返回行的前三排序。

[JasonLaw]

最近在看《数据密集型应用系统设计》，如果对你现在的情况合理的话，或许你应该分开 OLTP 和 OLAP 。

[zhaozs1]

排除法，把每个条件单独去掉看看，然后确认问题哪给条件的问题。

[encro]

看到最后一张图，select * from store 正在 send data，explain 看下行数。是不是全表返回了。

[encro]

还需要需要贴出阿里云 RDS 监控图表，看瓶颈在哪里。

[kimqcn]

是不是 IS NULL=全表扫描

[CoderGeek]

@JasonLaw 俗点说 读写分离？ 2333

[zzhpeng]

@xuanbg 这。。。。阿里出现这种问题，迁移也要花费好多时间了

[JasonLaw]

@kimqcn #55 “是不是 IS NULL=全表扫描”，可以看一下这个相关讨论 https://www.v2ex.com/t/694500 。

[hxy91819]

看样子是偶尔慢。索引扫描行数 6000+，可以优化，但是问题不大。问题应该不是出在索引这里。需要考虑下锁的竞争问题，分析下所有跟这个表有关的 SQL，看下有没有可能是锁定太多行了，尽量根据 id 去更新数据。

[x537196]

提工单让阿里云协查，说不定是他们自己 IO 的问题，之前我们的数据库也是 CPU 满，死活查不出原因，提了工单，阿里云说物理机有问题

[Yano]

感觉你这个是突发情况，在 qps 没有明显变化的情况下，突然所有 sql 的执行时间都在 1 秒以上，感觉就不是 mysql 语句的问题了。应该先排查是不是阿里云的问题。

[zzhpeng]

@594duck 感谢提供解决思路！ order 表目前 200w+，读写分离目前不用考虑。表已有个关键的索引字段 store_id，已经可以区分大部分数据。数据库配置提及下，2h4g 通用性####最大 IOPS：2000###最大连接数：1200 ；目前慢 sql 这一块我觉得问题都不太大，可能有部分需要稍微改改。还有这些慢 sql 都是首页的数据，由于首页数据可以无限制按 F5，所以怀疑是数据库压力太大导致出现慢 sql

[zzhpeng]

@JasonLaw 这本书豆瓣评分好高，我找时间看看。OLTP 和 OLAP 架构设计这块有点高级

[stach]

倒数第三的 sql 完整语句是什么?
MySQL 是否有定时高频执行的存储过程, 以及是否有定时高频访问 mysql 的脚本?

[zzhpeng]

@encro #53 #54 还有部分没截取到，不会是 select * from store 。当时就 cpu 满载了，其他正常

[zzhpeng]

@x537196
@Yano 感谢，提交个工单问问阿里

[zzhpeng]

@stach 也是和第一条差不多的统计语句，无用存储过程，无脚本

[594duck]

@zzhpeng 你的 CPU 和内存低了，另外如果是前台的东西，考虑动态静态化。另外前面加个 Redis 考虑一下。

[zzhpeng]

@hxy91819 感谢提供解决思路！跟这个表相关的 sql 操作有点多，在那段时间的慢 sql 只有 select 操作也没加 for update 排它锁。

[realpg]

反正吧，这种共享 MYSQL 出性能问题，先考虑无脑加 CPU 和内存吧
真的没法太深度操作
这也是我不爱用 RDS 类产品的原因

[zzhpeng]

@594duck 主要 B 端用户，量也不多，常用的也就 400 来家店。静态化不好弄，毕竟是实时订单交易工作台数据。现在主要考虑请求数量控制优化

[zzhpeng]

@realpg 能用钱解决的问题都不是问题，233333

[xuewuchen]

反正我在遇到这种问题的时候，第一反应加索引 ，第二反应 去掉语句中的*号，第三反应。。多语句的话按段测试

[zibber]

加个复合索引,还是不走索引就强制索引

[encro]

既然是 rds 的 cpu，那么就是运算或者转换除了问题了。

看看 RDS 其他是否也达到峰值了，比如 iops 和磁盘。


@realpg

RDS 提供了面板和性能监控，我通常配置一些预警，一出问题，我就知道是哪里出问题了。比如通常是某某参数先达到 70%，然后其他参数再出问题，那么瓶颈就在最先报的上面。

个人认为相当有用，比自己去配要节省非常多时间，定位问题也快。

[jjshare]

通过 explain 看，是没有没有走索引，扫了 6430 行，一定会慢的
没有表结构不好判断。
另外几个地方：
1 、IN 要看_name 字段索引长度，或者别用字符串
2 、delete_time IS NULL 这个可能用不上索引
3 、order by 如果没有走索引，会是灾难