踩坑日记:线上接口超时问题排查

1.背景:

上线后,功能测试.  进入小程序页面发现很慢,耗时超过5秒,打开skywalking发现大量接口耗时都很高.

2.top命令

服务器top命令查看cpu资源发现占用并不高

3.mysql查看sql运行情况

# 当前运行的所有事务

select * from information_schema.innodb_trx;

1 |                NULL |
| 422058432413352 | RUNNING   | 2024-10-16 19:19:56 | NULL                  | NULL             |          0 |               33722 | SELECT count(1) FROMt_account_tradeLEFT JOIN t_user u ON u.user_id = t_account_trade.user_id oru.TG_id = t_account_trade.user_id                                  | fetching rows       |                 2 |                 0 |                0 |                  1128 |               0 |                 0 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                1 |                          1 |                NULL |
| 422058432416616 | RUNNING   | 2024-10-16 19:00:54 | NULL                  | NULL             |          0 |               33589 | SELECT count(1) FROMt_account_tradeLEFT JOIN t_user u ON u.user_id = t_account_trade.user_id oru.TG_id = t_account_trade.user_id                                  | fetching rows       |                 2 |                 0 |                0 |                  1128 |               0 |                 0 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                1 |                          1 |                NULL |
| 422058432414984 | RUNNING   | 2024-10-16 19:00:46 | NULL                  | NULL             |          0 |               33588 | SELECT count(1) FROMt_account_tradeLEFT JOIN t_user u ON u.user_id = t_account_trade.user_id oru.TG_id = t_account_trade.user_id                                  | fetching rows       |                 2 |                 0 |                0 |                  1128 |               0 |                 0 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                1 |                          1 |                NULL |

大量查询sql处于running状态:  

SELECT count(1) FROM
        t_account_trade
        LEFT JOIN t_user u ON u.user_id = t_account_trade.user_id or
        u.TG_id = t_account_trade.user_id

很明显是sql里面里面加了or 导致没走索引, 线上百万数据必然会卡死 .

结合业务去掉or u.TG_id = t_account_trade.user_id ,重新发布,重启mysql,问题解决