描述:订单表,Innodb 存储引擎。排名前几个的慢 sql 语句,有些用了 count(*),弄到 rds 数据库 cpu 满了,服务器 502.
1 、慢 sql 排名图,如下:
2 、排名第一的慢 sql 执行情况:
3 、explain 排名第一的慢 sql:
自我分析:sql 23ms 也正常呀,难道是锁的问题吗?
4、突然就炸了
5、图看不清问题,可以手动另外打开,尴尬。。。。网站没有放大图功能
6 、show processlist
7、阿里的DAS数据库工具
显示大概就是突然的大量激增,首先要防范的就是做限流。可是我的nginx做了限流,但数据库那边顶不住。看来还是要在sql语句设计上着手解决问题。
1
takemeaway 2020-08-03 15:42:16 +08:00
你确定是这一句?
|
2
fiypig 2020-08-03 15:44:17 +08:00 via iPhone
explain 看看啊
|
4
lovecy 2020-08-03 16:11:53 +08:00
你这个慢 SQL 排名用的是啥生成的?是读取的慢日志么
索引用的是 idx_storeid_isvirtual,强制指定一个合适的索引试试 |
5
wangyanrui 2020-08-03 16:13:58 +08:00
看不到图~
八成没有走索引导致的呗,想办法用上即可 |
7
lsongiu 2020-08-03 16:33:41 +08:00
我咋看截图里的时间单位是 秒? 这么吓人?
|
8
JasonLaw 2020-08-03 16:33:59 +08:00 1
我觉得你的 SQL 语句不太合理。
1. 为什么 LIMIT 1 和 COUNT(*)一起使用? 2. 为什么使用了 COUNT(*),还要进行无谓的 ORDER BY ? |
9
pushback 2020-08-03 16:38:46 +08:00
表结构呢?
|
10
moonvstod 2020-08-03 16:38:56 +08:00
看索引
看有没有隐式转换 where 条件里面排除条目最多的条件排最前面 |
11
pushback 2020-08-03 16:40:17 +08:00
count(id),where 和 order 后的字段建立联合索引,然后你这 limit 1 ?
|
12
JasonLaw 2020-08-03 16:40:47 +08:00
sima_order.is_virtual 是不是只有 0 和 1 两种值?每个值对应的行数是多少?
|
13
JasonLaw 2020-08-03 16:49:02 +08:00 1
如果有人好奇“为什么加了 LIMIT 1,COUNT(*)还可以正常工作的话”,可以看一下 https://stackoverflow.com/questions/17020842/mysql-count-with-limit 。
|
14
zzhpeng OP @JasonLaw
1. 为什么 LIMIT 1 和 COUNT(*)一起使用? 回复:框架 orm 自动加上 2. 为什么使用了 COUNT(*),还要进行无谓的 ORDER BY ? 回复:和订单列表数据共用了一个逻辑数据层并且解耦的有问题,但是这个影响我去掉在查通过数据响应时间看,影响不大 |
15
RadishWind 2020-08-03 16:54:09 +08:00
看看索引怎么加的
|
17
egfegdfr 2020-08-03 16:58:51 +08:00
感觉这个 sql 问题不大,就是一个简单查询, 你在确认下是不是找错了,可能是其他 sql 引起的
|
18
GTim 2020-08-03 17:00:35 +08:00
@JasonLaw 理解了 Limit 的作用范围就好理解了。如果没有聚合函数,select 是在 order by -> limit 后面执行的,但是有了聚合函数,select 会优于 order by -> limit 执行。
不知道我有没有记错,哎 |
19
wangritian 2020-08-03 17:01:05 +08:00
盯上了倒数第三行,扫描行数高一个数量级
|
20
Airon 2020-08-03 17:02:03 +08:00
这个 sql 只是执行次数大,rdsCPU100%之后,平均等待时间长。造成 cpu 飙升的应该不是这一句
|
21
zzhpeng OP |
22
zzhpeng OP @RadishWind 看第三张图 explain
|
23
JasonLaw 2020-08-03 17:06:33 +08:00
@zzhpeng #21 所以你的索引基本没什么用,甚至会降低速度,因为对于每个符合条件的 secondary index 子节点,都会去 clustered index 搜索。
|
24
zzhpeng OP @Airon 这条排在第一的语句很早就有了,我也觉得不像是这条的问题。排在前面的几条 count()语句是在首页的请求。
|
26
zzhpeng OP @JasonLaw 那我有个疑问哈,为啥么我 explain 的时候是使用了索引的,而且速度是比较快的 23m 。难道是索引太多查找匹配问题,加大了数据库 cpu 资源消耗?
|
27
Airon 2020-08-03 17:15:36 +08:00
楼上也提到了,看倒数第三行,数据量级变了,应该是直接扫表了,次数还不少。索引和各种限制条件都得优化。
|
28
realpg 2020-08-03 17:16:38 +08:00
innodb 的表,最好就少 count
另外排除无关干扰把那个 orderby 干掉 虽然感觉不是这里的原因 盲猜索引差异度少,导致用索引效率更低 |
29
JasonLaw 2020-08-03 17:16:48 +08:00
@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 怎么能够成功呢? |
30
mingl0280 2020-08-03 17:20:09 +08:00
我建议你在服务器没有压力的时候清空排序数据一条一条测,第一条这个不像是有问题...
|
32
MoYi123 2020-08-03 17:23:40 +08:00
倒数第三句 sql 看起来更加可疑。
|
33
leoWeek 2020-08-03 17:25:57 +08:00
可能是其他慢 sql 导致服务器性能下降,sql 都阻塞住了,要看下其他慢 sql 现在的执行情况分析下
|
34
zzhpeng OP @mingl0280 我建议你在服务器没有压力的时候清空排序数据一条一条测,第一条这个不像是有问题...
回复:这个就是当时爆发产生的情况,并不是累计的。 |
35
JasonLaw 2020-08-03 17:28:21 +08:00
@zzhpeng #26
我主要是表达“条件为 is_virtual = 0,并且大多数的 is_virtual 都是 0”的时候,idx_storeid_isvirtual 其实是没有什么用处的。 你那条语句是直接在同一个环境运行的吗?如果是的话,单独运行肯定跟同其他语句并发运行是不一样的,我不清楚具体的情况,无法给你答案。 |
36
GTim 2020-08-03 17:32:14 +08:00
|
37
cnoder 2020-08-03 17:33:23 +08:00
你这个管理 sql 的界面叫啥
|
38
qwerthhusn 2020-08-03 17:34:19 +08:00
都 count 了,何不把 order by 去掉??
|
40
wps353 2020-08-03 17:35:58 +08:00
本质是 in +order by 出问题了。
|
41
zzhpeng OP @qwerthhusn 逻辑复用没解耦问题
|
42
july1115 2020-08-03 17:36:48 +08:00
排名第一的慢 sql 感觉问题不大啊。?? 最近也在解决慢 sql 的问题,因为某 sql 语句连表查询太多,直接新建了一个表,先把整理的数据保存的新表里。
|
43
zhangysh1995 2020-08-03 20:54:08 +08:00
Range 查询 IN 没有用到索引,慢吧?你可以试试其他条件去掉,只来 EXPLAIN 用 IN 这个条件试试。
|
44
RadishWind 2020-08-03 21:32:54 +08:00
不是 我想看看索引在表结构里面的定义
|
45
xuanbg 2020-08-03 22:10:59 +08:00
上上周我们的阿里云 RDS 遇到了奇怪的慢 SQL 暴增的现象,但这些慢 SQL 在前一天还都正常得不得了。。。到最后,查询一张只有几十条数据的表都要好几秒!!!估计是物理机 IO 打满、CPU 打满,没办法,只能跑路。迁移可用区后,一切恢复正常。
|
46
mingl0280 2020-08-04 07:11:45 +08:00 via Android
@zzhpeng 就是说你爆发的时候的数据是不准确的啊!!!!!让你平时没炸的时候测,可以更准确地定位有问题的语句(因为不会出现短请求不断重复却因为其它单个长请求卡死)的啊……
|
47
594duck 2020-08-04 08:16:35 +08:00
总表有多大,你这 Select 语句可能因为超出索引 直接变全表扫描了。读写分离考虑一下。
|
48
594duck 2020-08-04 08:22:39 +08:00
还有要小心 MYSQL 索引 有索引合并,如果一不小心你没命中索引 就又麻烦了。建议仔细看看。
|
49
594duck 2020-08-04 08:29:16 +08:00
你看慢请求里,集中在 SIMA 表,扫描行数都是 5K 以上。你总表有多大,然后自己看看有没有用到索引 。这二个是优化点。
|
50
blackeeper 2020-08-04 09:02:07 +08:00
1,你的这个排序是按照 [执行次数] 来排序的,这个并不准确。
2,你可以看看最大扫描行,和最大返回行的前三排序。 |
51
JasonLaw 2020-08-04 09:21:57 +08:00
最近在看《数据密集型应用系统设计》,如果对你现在的情况合理的话,或许你应该分开 OLTP 和 OLAP 。
|
52
zhaozs1 2020-08-04 09:26:18 +08:00
排除法,把每个条件单独去掉看看,然后确认问题哪给条件的问题。
|
53
encro 2020-08-04 09:35:17 +08:00
看到最后一张图,select * from store 正在 send data,explain 看下行数。是不是全表返回了。
|
54
encro 2020-08-04 09:39:17 +08:00
还需要需要贴出阿里云 RDS 监控图表,看瓶颈在哪里。
|
55
kimqcn 2020-08-04 09:50:30 +08:00
是不是 IS NULL=全表扫描
|
58
JasonLaw 2020-08-04 10:05:08 +08:00
@kimqcn #55 “是不是 IS NULL=全表扫描”,可以看一下这个相关讨论 https://www.v2ex.com/t/694500 。
|
59
hxy91819 2020-08-04 10:05:30 +08:00
看样子是偶尔慢。索引扫描行数 6000+,可以优化,但是问题不大。问题应该不是出在索引这里。需要考虑下锁的竞争问题,分析下所有跟这个表有关的 SQL,看下有没有可能是锁定太多行了,尽量根据 id 去更新数据。
|
60
x537196 2020-08-04 10:10:07 +08:00
提工单让阿里云协查,说不定是他们自己 IO 的问题,之前我们的数据库也是 CPU 满,死活查不出原因,提了工单,阿里云说物理机有问题
|
61
Yano 2020-08-04 10:15:19 +08:00
感觉你这个是突发情况,在 qps 没有明显变化的情况下,突然所有 sql 的执行时间都在 1 秒以上,感觉就不是 mysql 语句的问题了。应该先排查是不是阿里云的问题。
|
62
zzhpeng OP @594duck 感谢提供解决思路! order 表目前 200w+,读写分离目前不用考虑。表已有个关键的索引字段 store_id,已经可以区分大部分数据。数据库配置提及下,2h4g 通用性####最大 IOPS:2000###最大连接数:1200 ;目前慢 sql 这一块我觉得问题都不太大,可能有部分需要稍微改改。还有这些慢 sql 都是首页的数据,由于首页数据可以无限制按 F5,所以怀疑是数据库压力太大导致出现慢 sql
|
64
stach 2020-08-04 10:36:30 +08:00
倒数第三的 sql 完整语句是什么?
MySQL 是否有定时高频执行的存储过程, 以及是否有定时高频访问 mysql 的脚本? |
65
zzhpeng OP @encro #53 #54 还有部分没截取到,不会是 select * from store 。当时就 cpu 满载了,其他正常
|
69
zzhpeng OP @hxy91819 感谢提供解决思路!跟这个表相关的 sql 操作有点多,在那段时间的慢 sql 只有 select 操作也没加 for update 排它锁。
|
70
realpg 2020-08-04 11:09:01 +08:00
反正吧,这种共享 MYSQL 出性能问题,先考虑无脑加 CPU 和内存吧
真的没法太深度操作 这也是我不爱用 RDS 类产品的原因 |
71
zzhpeng OP @594duck 主要 B 端用户,量也不多,常用的也就 400 来家店。静态化不好弄,毕竟是实时订单交易工作台数据。现在主要考虑请求数量控制优化
|
73
xuewuchen 2020-08-04 11:26:23 +08:00
反正我在遇到这种问题的时候,第一反应加索引 ,第二反应 去掉语句中的*号,第三反应。。多语句的话按段测试
|
74
zibber 2020-08-04 17:09:04 +08:00
加个复合索引,还是不走索引就强制索引
|
75
encro 2020-08-04 18:45:44 +08:00
既然是 rds 的 cpu,那么就是运算或者转换除了问题了。
看看 RDS 其他是否也达到峰值了,比如 iops 和磁盘。 @realpg RDS 提供了面板和性能监控,我通常配置一些预警,一出问题,我就知道是哪里出问题了。比如通常是某某参数先达到 70%,然后其他参数再出问题,那么瓶颈就在最先报的上面。 个人认为相当有用,比自己去配要节省非常多时间,定位问题也快。 |
76
jjshare 2020-08-04 21:07:05 +08:00
通过 explain 看,是没有没有走索引,扫了 6430 行,一定会慢的
没有表结构不好判断。 另外几个地方: 1 、IN 要看_name 字段索引长度,或者别用字符串 2 、delete_time IS NULL 这个可能用不上索引 3 、order by 如果没有走索引,会是灾难 |