MySQL是一種常用的關系型數據庫管理系統,但是在使用 MySQL 進行數據處理時,可能會遇到運行速度緩慢的情況,這時候需要查看 CPU 使用高的 SQL 語句進行問題排查。
首先,可以通過 MySQL 自帶的 Performance Schema 模塊進行 CPU 使用率監控。使用以下 SQL 語句來查詢其結果:
SELECT events.statement, statements.total_latency, statements.memory_tmp_tables FROM performance_schema.events_statements_summary_by_digest AS digest JOIN performance_schema.events_statements_summary_by_thread_by_event_name AS events ON digest.digest = events.digest JOIN performance_schema.events_statements_summary_by_account_by_event_name AS users ON users.event_name = events.event_name AND users.thread_id = events.thread_id JOIN performance_schema.events_statements_summary_by_user_by_event_name AS accounts ON accounts.event_name = events.event_name AND accounts.user = users.user JOIN performance_schema.events_statements_summary_by_host_by_event_name AS hosts ON hosts.event_name = events.event_name AND hosts.thread_id = events.thread_id JOIN performance_schema.events_statements_summary_by_program_by_event_name AS programs ON programs.event_name = events.event_name AND programs.thread_id = events.thread_id JOIN performance_schema.events_statements_summary_global_by_event_name AS statements ON statements.event_name = events.event_name WHERE digest.digest_text NOT LIKE '%$%' ORDER BY statements.total_latency DESC LIMIT 10;
該 SQL 語句將返回運行最慢的前十個 SQL 語句,并列出這些語句的總延遲時間和內存中臨時表的數量。通過分析這些數據,可以找出 CPU 使用率較高的 SQL 語句來進行優化。