首页 注册 登录
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 · 6914 次点击
    这是一个创建于 1571 天前的主题，其中的信息可能已经有所发展或是发生改变。

    问题

    描述：订单表，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语句设计上着手解决问题。

  • SQL
  • 排名
  • explain
  • RDS
    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   ·   实用小工具   ·   1208 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 18:04 · PVG 02:04 · LAX 10:04 · JFK 13:04
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.