優化SQL的一般步驟

2024-07-21 02:51:28

字體：大中小

來源：轉載

供稿：網友

1，通過show status 命令了解各種SQL的執行頻率比如Com_select 記錄執行select查詢的操作次數，一次查詢只累加12，定位執行效率較低的SQL語句 1通過慢查詢日志定位，2慢查詢在查詢結束后才記錄，使用show PRocesslist查看3，通過EXPLAIN分析低效SQL的執行計劃

4，通過show profile 分析SQL（>MySQL5.0.37） mysql> select @@have_profiling;+------------------+| @@have_profiling |+------------------+| YES |+------------------+1 row in set (0.00 sec) 表示支持mysql> select @@profiling;+-------------+| @@profiling | +-------------+| 0 |+-------------+1 row in set (0.00 sec) 默認關閉可以set profiling=1;開啟mysql> show profiles;+----------+------------+--------------------------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------------------------+| 1 | 0.02342575 | select * from br_member where user_id<1000 |+----------+------------+--------------------------------------------+1 row in set (0.00 sec)mysql> show profile for query 1;（這里的1 代表show profiles查出的query_id的值）+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000115 || checking permissions | 0.000013 || Opening tables | 0.000044 || System lock | 0.000025 || init | 0.000097 || optimizing | 0.000019 || statistics | 0.000084 || preparing | 0.000020 || executing | 0.000005 || Sending data | 0.022893 || end | 0.000012 || query end | 0.000006 || closing tables | 0.000013 || freeing items | 0.000071 || logging slow query | 0.000005 || cleaning up | 0.000004 |+----------------------+----------+16 rows in set (0.00 sec)在獲取到最消耗時間的線程狀態以后，mysql支持進一步選擇all,cpu,block io,context,switch,page faults等明細類型來查看mysql在使用什么資源上耗費了過高的時間，如下選擇查看cpu的消耗時間mysql> show profile cpu for query 1;+----------------------+----------+----------+------------+| Status | Duration | CPU_user | CPU_system |+----------------------+----------+----------+------------+| starting | 0.000115 | 0.000000 | 0.000000 || checking permissions | 0.000013 | 0.000000 | 0.000000 || Opening tables | 0.000044 | 0.000000 | 0.000000 || System lock | 0.000025 | 0.000000 | 0.000000 || init | 0.000097 | 0.000000 | 0.000000 || optimizing | 0.000019 | 0.000000 | 0.000000 || statistics | 0.000084 | 0.000000 | 0.000000 || preparing | 0.000020 | 0.000000 | 0.000000 || executing | 0.000005 | 0.000000 | 0.000000 || Sending data | 0.022893 | 0.015600 | 0.000000 || end | 0.000012 | 0.000000 | 0.000000 || query end | 0.000006 | 0.000000 | 0.000000 || closing tables | 0.000013 | 0.000000 | 0.000000 || freeing items | 0.000071 | 0.000000 | 0.000000 || logging slow query | 0.000005 | 0.000000 | 0.000000 || cleaning up | 0.000004 | 0.000000 | 0.000000 |+----------------------+----------+----------+------------+16 rows in set (0.00 sec)5、通過trace分析優化器如何選擇執行計劃6、確定問題并采取相應的措施