V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
zzhpeng
V2EX  ›  MySQL

大佬们求救,慢 SQL 问题

  •  
  •   zzhpeng · 2020-08-03 15:40:22 +08:00 · 6901 次点击
    这是一个创建于 1554 天前的主题,其中的信息可能已经有所发展或是发生改变。

    问题

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

    1 、慢 sql 排名图,如下: FnT9vhyTijBX6QlPsUhxihH2KSCt

    2 、排名第一的慢 sql 执行情况: FqdlBIhjzTYuZWudPs-6q1NbJo96

    3 、explain 排名第一的慢 sql: Fs7njOYpJ13Ql-Gulnc10gHj8Ld8

    自我分析:sql 23ms 也正常呀,难道是锁的问题吗?

    第 1 条附言  ·  2020-08-03 16:13:37 +08:00

    4、突然就炸了

    Fp2lZfiyJTV3EZp-XeCjN4RsmkB_

    第 2 条附言  ·  2020-08-03 16:25:32 +08:00

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

    FhwTmmtF2uU-Qz0ENWvzJ6fA9B5P

    第 3 条附言  ·  2020-08-03 17:23:46 +08:00
    6 、show processlist

    ![FvdUmy9rnWaPUKLfS3kbz_XtXNkb]( http://img.zzhpeng.cn/FvdUmy9rnWaPUKLfS3kbz_XtXNkb)
    ![Fnq3xChIO5n7NWeDvIidGxs-O7AE]( http://img.zzhpeng.cn/Fnq3xChIO5n7NWeDvIidGxs-O7AE)
    第 4 条附言  ·  2020-08-03 17:24:03 +08:00

    6 、show processlist

    FvdUmy9rnWaPUKLfS3kbz_XtXNkb Fnq3xChIO5n7NWeDvIidGxs-O7AE

    第 5 条附言  ·  2020-08-04 18:31:29 +08:00

    7、阿里的DAS数据库工具 FhDGVa1ALn6DDsXOj35Ok2yyGUi9

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

    76 条回复    2020-08-04 21:07:05 +08:00
    takemeaway
        1
    takemeaway  
       2020-08-03 15:42:16 +08:00
    你确定是这一句?
    fiypig
        2
    fiypig  
       2020-08-03 15:44:17 +08:00 via iPhone
    explain 看看啊
    zzhpeng
        3
    zzhpeng  
    OP
       2020-08-03 15:59:25 +08:00
    @fiypig 第三张图哈,这个平台不支持放大图片,右键可以看清图
    lovecy
        4
    lovecy  
       2020-08-03 16:11:53 +08:00
    你这个慢 SQL 排名用的是啥生成的?是读取的慢日志么
    索引用的是 idx_storeid_isvirtual,强制指定一个合适的索引试试
    wangyanrui
        5
    wangyanrui  
       2020-08-03 16:13:58 +08:00
    看不到图~
    八成没有走索引导致的呗,想办法用上即可
    zzhpeng
        6
    zzhpeng  
    OP
       2020-08-03 16:14:38 +08:00
    @lovecy 阿里云的 rds 的监控工具
    lsongiu
        7
    lsongiu  
       2020-08-03 16:33:41 +08:00
    我咋看截图里的时间单位是 秒? 这么吓人?
    JasonLaw
        8
    JasonLaw  
       2020-08-03 16:33:59 +08:00   ❤️ 1
    我觉得你的 SQL 语句不太合理。

    1. 为什么 LIMIT 1 和 COUNT(*)一起使用?
    2. 为什么使用了 COUNT(*),还要进行无谓的 ORDER BY ?
    pushback
        9
    pushback  
       2020-08-03 16:38:46 +08:00
    表结构呢?
    moonvstod
        10
    moonvstod  
       2020-08-03 16:38:56 +08:00
    看索引
    看有没有隐式转换
    where 条件里面排除条目最多的条件排最前面
    pushback
        11
    pushback  
       2020-08-03 16:40:17 +08:00
    count(id),where 和 order 后的字段建立联合索引,然后你这 limit 1 ?
    JasonLaw
        12
    JasonLaw  
       2020-08-03 16:40:47 +08:00
    sima_order.is_virtual 是不是只有 0 和 1 两种值?每个值对应的行数是多少?
    JasonLaw
        13
    JasonLaw  
       2020-08-03 16:49:02 +08:00   ❤️ 1
    如果有人好奇“为什么加了 LIMIT 1,COUNT(*)还可以正常工作的话”,可以看一下 https://stackoverflow.com/questions/17020842/mysql-count-with-limit
    zzhpeng
        14
    zzhpeng  
    OP
       2020-08-03 16:51:38 +08:00
    @JasonLaw
    1. 为什么 LIMIT 1 和 COUNT(*)一起使用?
    回复:框架 orm 自动加上
    2. 为什么使用了 COUNT(*),还要进行无谓的 ORDER BY ?
    回复:和订单列表数据共用了一个逻辑数据层并且解耦的有问题,但是这个影响我去掉在查通过数据响应时间看,影响不大
    RadishWind
        15
    RadishWind  
       2020-08-03 16:54:09 +08:00
    看看索引怎么加的
    GTim
        16
    GTim  
       2020-08-03 16:54:21 +08:00   ❤️ 1
    @lovecy ThinkPHP,看到 tp_count 太眼熟了
    egfegdfr
        17
    egfegdfr  
       2020-08-03 16:58:51 +08:00
    感觉这个 sql 问题不大,就是一个简单查询, 你在确认下是不是找错了,可能是其他 sql 引起的
    GTim
        18
    GTim  
       2020-08-03 17:00:35 +08:00
    @JasonLaw 理解了 Limit 的作用范围就好理解了。如果没有聚合函数,select 是在 order by -> limit 后面执行的,但是有了聚合函数,select 会优于 order by -> limit 执行。

    不知道我有没有记错,哎
    wangritian
        19
    wangritian  
       2020-08-03 17:01:05 +08:00
    盯上了倒数第三行,扫描行数高一个数量级
    Airon
        20
    Airon  
       2020-08-03 17:02:03 +08:00
    这个 sql 只是执行次数大,rdsCPU100%之后,平均等待时间长。造成 cpu 飙升的应该不是这一句
    zzhpeng
        21
    zzhpeng  
    OP
       2020-08-03 17:02:28 +08:00
    @JasonLaw
    sima_order.is_virtual 是不是只有 0 和 1 两种值?每个值对应的行数是多少?
    回复:确实是只有 0 和 1,1 几乎很少,所以基本上是 0
    zzhpeng
        22
    zzhpeng  
    OP
       2020-08-03 17:03:47 +08:00
    @RadishWind 看第三张图 explain
    JasonLaw
        23
    JasonLaw  
       2020-08-03 17:06:33 +08:00
    @zzhpeng #21 所以你的索引基本没什么用,甚至会降低速度,因为对于每个符合条件的 secondary index 子节点,都会去 clustered index 搜索。
    zzhpeng
        24
    zzhpeng  
    OP
       2020-08-03 17:09:48 +08:00
    @Airon 这条排在第一的语句很早就有了,我也觉得不像是这条的问题。排在前面的几条 count()语句是在首页的请求。
    GTim
        25
    GTim  
       2020-08-03 17:13:19 +08:00
    @zzhpeng 要不监控着,等 cpu 上去的时候执行一下 show processlist 看看是哪个超时了。
    zzhpeng
        26
    zzhpeng  
    OP
       2020-08-03 17:13:36 +08:00
    @JasonLaw 那我有个疑问哈,为啥么我 explain 的时候是使用了索引的,而且速度是比较快的 23m 。难道是索引太多查找匹配问题,加大了数据库 cpu 资源消耗?
    Airon
        27
    Airon  
       2020-08-03 17:15:36 +08:00
    楼上也提到了,看倒数第三行,数据量级变了,应该是直接扫表了,次数还不少。索引和各种限制条件都得优化。
    realpg
        28
    realpg  
       2020-08-03 17:16:38 +08:00
    innodb 的表,最好就少 count
    另外排除无关干扰把那个 orderby 干掉 虽然感觉不是这里的原因
    盲猜索引差异度少,导致用索引效率更低
    JasonLaw
        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 怎么能够成功呢?
    mingl0280
        30
    mingl0280  
       2020-08-03 17:20:09 +08:00
    我建议你在服务器没有压力的时候清空排序数据一条一条测,第一条这个不像是有问题...
    zzhpeng
        31
    zzhpeng  
    OP
       2020-08-03 17:21:11 +08:00
    @GTim 这个难,那么多超时
    MoYi123
        32
    MoYi123  
       2020-08-03 17:23:40 +08:00
    倒数第三句 sql 看起来更加可疑。
    leoWeek
        33
    leoWeek  
       2020-08-03 17:25:57 +08:00
    可能是其他慢 sql 导致服务器性能下降,sql 都阻塞住了,要看下其他慢 sql 现在的执行情况分析下
    zzhpeng
        34
    zzhpeng  
    OP
       2020-08-03 17:27:08 +08:00
    @mingl0280 我建议你在服务器没有压力的时候清空排序数据一条一条测,第一条这个不像是有问题...
    回复:这个就是当时爆发产生的情况,并不是累计的。
    JasonLaw
        35
    JasonLaw  
       2020-08-03 17:28:21 +08:00
    @zzhpeng #26

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

    你那条语句是直接在同一个环境运行的吗?如果是的话,单独运行肯定跟同其他语句并发运行是不一样的,我不清楚具体的情况,无法给你答案。
    GTim
        36
    GTim  
       2020-08-03 17:32:14 +08:00
    @zzhpeng @zzhpeng 看 cpu 图,这个只能在高压的时候看,平时没问题说明语句都挺正常,当然如果这个时候有不正常的,那就太不正常了。找到那条语句优化下
    cnoder
        37
    cnoder  
       2020-08-03 17:33:23 +08:00
    你这个管理 sql 的界面叫啥
    qwerthhusn
        38
    qwerthhusn  
       2020-08-03 17:34:19 +08:00
    都 count 了,何不把 order by 去掉??
    zzhpeng
        39
    zzhpeng  
    OP
       2020-08-03 17:35:50 +08:00
    @cnoder 阿里的 rds
    wps353
        40
    wps353  
       2020-08-03 17:35:58 +08:00
    本质是 in +order by 出问题了。
    zzhpeng
        41
    zzhpeng  
    OP
       2020-08-03 17:36:46 +08:00
    @qwerthhusn 逻辑复用没解耦问题
    july1115
        42
    july1115  
       2020-08-03 17:36:48 +08:00
    排名第一的慢 sql 感觉问题不大啊。?? 最近也在解决慢 sql 的问题,因为某 sql 语句连表查询太多,直接新建了一个表,先把整理的数据保存的新表里。
    zhangysh1995
        43
    zhangysh1995  
       2020-08-03 20:54:08 +08:00
    Range 查询 IN 没有用到索引,慢吧?你可以试试其他条件去掉,只来 EXPLAIN 用 IN 这个条件试试。
    RadishWind
        44
    RadishWind  
       2020-08-03 21:32:54 +08:00
    不是 我想看看索引在表结构里面的定义
    xuanbg
        45
    xuanbg  
       2020-08-03 22:10:59 +08:00
    上上周我们的阿里云 RDS 遇到了奇怪的慢 SQL 暴增的现象,但这些慢 SQL 在前一天还都正常得不得了。。。到最后,查询一张只有几十条数据的表都要好几秒!!!估计是物理机 IO 打满、CPU 打满,没办法,只能跑路。迁移可用区后,一切恢复正常。
    mingl0280
        46
    mingl0280  
       2020-08-04 07:11:45 +08:00 via Android
    @zzhpeng 就是说你爆发的时候的数据是不准确的啊!!!!!让你平时没炸的时候测,可以更准确地定位有问题的语句(因为不会出现短请求不断重复却因为其它单个长请求卡死)的啊……
    594duck
        47
    594duck  
       2020-08-04 08:16:35 +08:00
    总表有多大,你这 Select 语句可能因为超出索引 直接变全表扫描了。读写分离考虑一下。
    594duck
        48
    594duck  
       2020-08-04 08:22:39 +08:00
    还有要小心 MYSQL 索引 有索引合并,如果一不小心你没命中索引 就又麻烦了。建议仔细看看。
    594duck
        49
    594duck  
       2020-08-04 08:29:16 +08:00
    你看慢请求里,集中在 SIMA 表,扫描行数都是 5K 以上。你总表有多大,然后自己看看有没有用到索引 。这二个是优化点。
    blackeeper
        50
    blackeeper  
       2020-08-04 09:02:07 +08:00
    1,你的这个排序是按照 [执行次数] 来排序的,这个并不准确。
    2,你可以看看最大扫描行,和最大返回行的前三排序。
    JasonLaw
        51
    JasonLaw  
       2020-08-04 09:21:57 +08:00
    最近在看《数据密集型应用系统设计》,如果对你现在的情况合理的话,或许你应该分开 OLTP 和 OLAP 。
    zhaozs1
        52
    zhaozs1  
       2020-08-04 09:26:18 +08:00
    排除法,把每个条件单独去掉看看,然后确认问题哪给条件的问题。
    encro
        53
    encro  
       2020-08-04 09:35:17 +08:00
    看到最后一张图,select * from store 正在 send data,explain 看下行数。是不是全表返回了。
    encro
        54
    encro  
       2020-08-04 09:39:17 +08:00
    还需要需要贴出阿里云 RDS 监控图表,看瓶颈在哪里。
    kimqcn
        55
    kimqcn  
       2020-08-04 09:50:30 +08:00
    是不是 IS NULL=全表扫描
    CoderGeek
        56
    CoderGeek  
       2020-08-04 09:59:39 +08:00
    @JasonLaw 俗点说 读写分离? 2333
    zzhpeng
        57
    zzhpeng  
    OP
       2020-08-04 10:00:50 +08:00
    @xuanbg 这。。。。阿里出现这种问题,迁移也要花费好多时间了
    JasonLaw
        58
    JasonLaw  
       2020-08-04 10:05:08 +08:00
    @kimqcn #55 “是不是 IS NULL=全表扫描”,可以看一下这个相关讨论 https://www.v2ex.com/t/694500
    hxy91819
        59
    hxy91819  
       2020-08-04 10:05:30 +08:00
    看样子是偶尔慢。索引扫描行数 6000+,可以优化,但是问题不大。问题应该不是出在索引这里。需要考虑下锁的竞争问题,分析下所有跟这个表有关的 SQL,看下有没有可能是锁定太多行了,尽量根据 id 去更新数据。
    x537196
        60
    x537196  
       2020-08-04 10:10:07 +08:00
    提工单让阿里云协查,说不定是他们自己 IO 的问题,之前我们的数据库也是 CPU 满,死活查不出原因,提了工单,阿里云说物理机有问题
    Yano
        61
    Yano  
       2020-08-04 10:15:19 +08:00
    感觉你这个是突发情况,在 qps 没有明显变化的情况下,突然所有 sql 的执行时间都在 1 秒以上,感觉就不是 mysql 语句的问题了。应该先排查是不是阿里云的问题。
    zzhpeng
        62
    zzhpeng  
    OP
       2020-08-04 10:16:32 +08:00
    @594duck 感谢提供解决思路! order 表目前 200w+,读写分离目前不用考虑。表已有个关键的索引字段 store_id,已经可以区分大部分数据。数据库配置提及下,2h4g 通用性####最大 IOPS:2000###最大连接数:1200 ;目前慢 sql 这一块我觉得问题都不太大,可能有部分需要稍微改改。还有这些慢 sql 都是首页的数据,由于首页数据可以无限制按 F5,所以怀疑是数据库压力太大导致出现慢 sql
    zzhpeng
        63
    zzhpeng  
    OP
       2020-08-04 10:32:32 +08:00
    @JasonLaw 这本书豆瓣评分好高,我找时间看看。OLTP 和 OLAP 架构设计这块有点高级
    stach
        64
    stach  
       2020-08-04 10:36:30 +08:00
    倒数第三的 sql 完整语句是什么?
    MySQL 是否有定时高频执行的存储过程, 以及是否有定时高频访问 mysql 的脚本?
    zzhpeng
        65
    zzhpeng  
    OP
       2020-08-04 10:38:28 +08:00
    @encro #53 #54 还有部分没截取到,不会是 select * from store 。当时就 cpu 满载了,其他正常
    zzhpeng
        66
    zzhpeng  
    OP
       2020-08-04 10:48:14 +08:00
    @x537196
    @Yano 感谢,提交个工单问问阿里
    zzhpeng
        67
    zzhpeng  
    OP
       2020-08-04 10:50:11 +08:00
    @stach 也是和第一条差不多的统计语句,无用存储过程,无脚本
    594duck
        68
    594duck  
       2020-08-04 10:57:07 +08:00
    @zzhpeng 你的 CPU 和内存低了,另外如果是前台的东西,考虑动态静态化。另外前面加个 Redis 考虑一下。
    zzhpeng
        69
    zzhpeng  
    OP
       2020-08-04 11:05:55 +08:00
    @hxy91819 感谢提供解决思路!跟这个表相关的 sql 操作有点多,在那段时间的慢 sql 只有 select 操作也没加 for update 排它锁。
    realpg
        70
    realpg  
       2020-08-04 11:09:01 +08:00
    反正吧,这种共享 MYSQL 出性能问题,先考虑无脑加 CPU 和内存吧
    真的没法太深度操作
    这也是我不爱用 RDS 类产品的原因
    zzhpeng
        71
    zzhpeng  
    OP
       2020-08-04 11:09:31 +08:00
    @594duck 主要 B 端用户,量也不多,常用的也就 400 来家店。静态化不好弄,毕竟是实时订单交易工作台数据。现在主要考虑请求数量控制优化
    zzhpeng
        72
    zzhpeng  
    OP
       2020-08-04 11:10:50 +08:00
    @realpg 能用钱解决的问题都不是问题,233333
    xuewuchen
        73
    xuewuchen  
       2020-08-04 11:26:23 +08:00
    反正我在遇到这种问题的时候,第一反应加索引 ,第二反应 去掉语句中的*号,第三反应。。多语句的话按段测试
    zibber
        74
    zibber  
       2020-08-04 17:09:04 +08:00
    加个复合索引,还是不走索引就强制索引
    encro
        75
    encro  
       2020-08-04 18:45:44 +08:00
    既然是 rds 的 cpu,那么就是运算或者转换除了问题了。

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


    @realpg

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

    个人认为相当有用,比自己去配要节省非常多时间,定位问题也快。
    jjshare
        76
    jjshare  
       2020-08-04 21:07:05 +08:00
    通过 explain 看,是没有没有走索引,扫了 6430 行,一定会慢的
    没有表结构不好判断。
    另外几个地方:
    1 、IN 要看_name 字段索引长度,或者别用字符串
    2 、delete_time IS NULL 这个可能用不上索引
    3 、order by 如果没有走索引,会是灾难
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4625 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 05:36 · PVG 13:36 · LAX 21:36 · JFK 00:36
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.