云數據庫MySQL如何管理長(cháng)時(shí)間執行的查詢(xún)

出現原因

在使用云數據庫MySQL的過(guò)程中，由于某些原因，例如被SQL注入、SQL執行效率較差、DDL語(yǔ)句引起表元數據鎖等待等，會(huì )出現運行時(shí)間很長(cháng)的查詢(xún)。

說(shuō)明：
元數據鎖等待的問(wèn)題請參考解決MDL鎖導致無(wú)法操作數據庫的問(wèn)題。

• 由于SQL執行效率差而導致的長(cháng)時(shí)間查詢(xún)。
• 由于被SQL注入而導致的長(cháng)時(shí)間查詢(xún)。
• 由于DDL語(yǔ)句引起表元數據鎖等待。

長(cháng)時(shí)間執行的查詢(xún)帶來(lái)的問(wèn)題

通常來(lái)說(shuō)，除非是BI/報表類(lèi)查詢(xún)，否則長(cháng)時(shí)的查詢(xún)對于應用缺乏意義，而且會(huì )消耗系統資源，比如大量長(cháng)時(shí)間查詢(xún)可能會(huì )引起CPU、IOPS和連接數過(guò)高等問(wèn)題，導致系統不穩定。

如何避免長(cháng)時(shí)間執行的查詢(xún)

避免長(cháng)時(shí)間執行查詢(xún)的方法請參考如下。

• 應用方面應注意增加防止SQL注入的保護措施。
• 在新功能模塊上線(xiàn)前，進(jìn)行壓力測試，避免執行效率很差的SQL大量執行。
• 盡量在業(yè)務(wù)低峰期進(jìn)行索引創(chuàng )建刪除、表結構修改、表維護和表刪除操作。

如何處理長(cháng)時(shí)間執行的查詢(xún)

異常情況下的元數據鎖MDL（metadata lock）會(huì )阻塞后續對表的操作。

操作步驟

1. 在數據庫管理工具（DMS）的SQL窗口中執行show full processlist命令，查看數據庫所有線(xiàn)程狀態(tài)。

2. 找到State列出現Waiting for table metadata lock的被阻塞會(huì )話(huà)，通過(guò)其Info列信息溯源造成阻塞的會(huì )話(huà)，記錄該會(huì )話(huà)Id。

3. 在命令行輸入kill <會(huì )話(huà)ID>，中斷造成阻塞的會(huì )話(huà)，解鎖MDL鎖。

后續維護

• 在業(yè)務(wù)低峰期執行相關(guān)場(chǎng)景操作，包括：
  • 創(chuàng )建、刪除索引。
  • 修改表結構。
  • 表維護操作（optimize table、repair table等）。
  • 刪除表。
  • 獲取表級寫(xiě)鎖。
• 開(kāi)啟事務(wù)自動(dòng)提交autocommit。
• 設置參數lock_wait_timeout為較小值。
• 考慮使用事件來(lái)終止長(cháng)時(shí)間運行的事務(wù)。