云数据库MySQL如何管理长时间执行的查询

出现原因

在使用云数据库MySQL的过程中，由于某些原因，例如被SQL注入、SQL执行效率较差、DDL语句引起表元数据锁等待等，会出现运行时间很长的查询。

说明：
元数据锁等待的问题请参考解决MDL锁导致无法操作数据库的问题。

• 由于SQL执行效率差而导致的长时间查询。
• 由于被SQL注入而导致的长时间查询。
• 由于DDL语句引起表元数据锁等待。

长时间执行的查询带来的问题

通常来说，除非是BI/报表类查询，否则长时的查询对于应用缺乏意义，而且会消耗系统资源，比如大量长时间查询可能会引起CPU、IOPS和连接数过高等问题，导致系统不稳定。

如何避免长时间执行的查询

避免长时间执行查询的方法请参考如下。

• 应用方面应注意增加防止SQL注入的保护措施。
• 在新功能模块上线前，进行压力测试，避免执行效率很差的SQL大量执行。
• 尽量在业务低峰期进行索引创建删除、表结构修改、表维护和表删除操作。

如何处理长时间执行的查询

异常情况下的元数据锁MDL（metadata lock）会阻塞后续对表的操作。

操作步骤

1. 在数据库管理工具（DMS）的SQL窗口中执行show full processlist命令，查看数据库所有线程状态。

2. 找到State列出现Waiting for table metadata lock的被阻塞会话，通过其Info列信息溯源造成阻塞的会话，记录该会话Id。

3. 在命令行输入kill <会话ID>，中断造成阻塞的会话，解锁MDL锁。

后续维护

• 在业务低峰期执行相关场景操作，包括：
  • 创建、删除索引。
  • 修改表结构。
  • 表维护操作（optimize table、repair table等）。
  • 删除表。
  • 获取表级写锁。
• 开启事务自动提交autocommit。
• 设置参数lock_wait_timeout为较小值。
• 考虑使用事件来终止长时间运行的事务。