慢查询排查以及优化

排查方式

SHOW PROCESSLIST

SHOW PROCESSLIST 只能查看到当前用户正在运行的线程,如果你是ROOT用户,那么是可以看到所有用户正在运行的线程
如果想看到其他用户的线程,则必须为此用户赋予 PROCESS 权限。

危害:

  1. 造成机器的 CPU 以及 IO 过高,影响到数据库实例。
  2. 阻塞 DDL 查询语句,会造成其他正常业务出现耗时过高。
  3. 会导致 mysql 出现 MDL 锁,影响到该机器上正常 SQL 的执行。

对正在运行的业务锁产生的影响

如果是 Java 应用,还有可能会导致 GC 耗时增大,线程 block 增多,最终引起整个服务的波动

如何监控慢查询:

开启 mysql 的慢查询日志,再通过 FileBeat 将数据同步至 ELK 即可

慢SQL 优化:

EXPLAIN SQL 查看语句是否走了索引,以及 extra 信息。

extra

  1. Using temporary 代表采用的是临时表
  2. Using filesort采用文件索引
  3. Using index 采用覆盖索引
  4. Using join buffer (Block Nested Loop) BNL 优化,出现此项则代表多表 JOIN 连接没有走索引

索引失效:

  1. 索引字段不能使用函数
  2. 避免查询的字段发生隐式转换
  3. 如果索引字段使用 like key% 可以走索引,其他方式均不走
  4. 联合作引有最左原则,因此需要 where 条件中的索引出现符合最左原则
  5. order by 的字段必须走索引,否则会出现 filesort
  6. 如果通过 explain 发现走全表扫描的成本还低于走索引的,那么证明索引不合理,需要重新区分
  7. 表链接一定要小表驱动大表

系统优化:

1、数据冷热处理

大数据量的查询走 hive,生产环境中不应该存在mysql 大数据量的查询,定期将一些冷门数据同步至冷库,而不应该占用生产核心数据库

如果生产环境部分客户查询到了冷库数据,需要进行限流,被限流的查询可以发送异步消息,将冷库数据取出,然后同步至缓存

  • 冷库数据建议直接同步至 hive 或者 ES 中,并且将数据通过日期拆分,例如 每周、每月等,提高查询效率

2、分库分表

目前业界采用的是 Sharding-JDBC+LVS+Keepalived

作者

Somersames

发布于

2021-08-31

更新于

2021-12-05

许可协议

评论