在做MySQL資料的效能定位前，需要先知道MySQL查詢時資料庫內部的執行過程。只有弄清SQL的執行過程，才能對執行過程中的每一步的效能做定位分析。如圖6-2-1所示。
圖6-2-1
從圖中可以看到，當查詢出資料以後，會將資料先返回給執行器，此時執行器先將結果寫到查詢快取裡面，這樣在下次查詢相同的資料時，就可以直接從快取中查詢並且返回，同時將結果返回給客戶端。分析器會對待查詢的SQL語句做執行計劃的分析，而最佳化器會對SQL語句做重新最佳化，以便SQL的查詢效能達到最佳。
6.2.1 慢SQL
每條SQL語句在執行時都需要消耗一定的I/O資源，SQL語句執行的快慢直接決定了硬體資源被佔用時長的長短，慢SQL一般指查詢很慢的SQL語句。在MySQL資料庫中，可以透過慢查詢來檢視所有執行超時的SQL語句。在預設情況下，一般慢SQL是關閉的，可以透過執行show variables like 'slow_query%' 來檢視資料庫是否開啟了慢查詢，如圖6-2-2所示。
從圖6-2-2中看到slow_query_log的值為OFF表示慢查詢未開啟，可以透過執行命令“set global slow_query_log=1; ”或者“set global slow_query_log=ON;”來臨時開啟慢查詢，如圖6-2-3所示。
如果需要永久開啟，就需要修改/etc/my.cnf配置檔案，在[mysqld]處加入如下配置，再重啟資料庫即可生效，如下所示。
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/localhost-slow.log
修改完成重啟資料庫後，再次執行show variables like 'slow_query%'，發現慢查詢已經被開啟，如圖6-2-4所示。
透過執行“show variables like 'long_query%';”可以查詢慢查詢的記錄時間，如圖6-2-5所示。慢查詢的記錄時間預設是10秒，可以透過執行“set long_query_time=需要修改的時長;”來修改慢查詢的記錄時間。
透過執行“show status like 'slow_queries';”可以檢視慢查詢發生的次數，如圖6-2-6所示。
從慢查詢日誌中，我們也可以看到慢查詢發生的詳細資訊，如圖6-2-7所示。慢查詢日誌中會記錄每次慢查詢發生的時間、執行查詢時的資料庫使用者、執行緒id、查詢執行的SQL語句等資訊。
在獲取到慢查詢的SQL語句後，就可以藉助資料庫的執行計劃來對慢查詢的SQL語句做進一步的分析。（節選自《軟體效能測試、分析與調優實踐之路》(第2版)，作者張永清，轉載請註明出處）
6.2.2 執行計劃
在MySQL中使用explain關鍵字可以模擬檢視資料庫是如何執行SQL查詢語句，也就是常說的檢視一條SQL語句在資料庫中的執行計劃。圖6-2-8所示就是執行EXPLAIN SELECT * FROM test.test 後返回的SELECT * FROM test.test查詢的執行計劃。
查詢結果返回的欄位說明如下所示。
（1）id。查詢的順序編號，表示查詢中執行的順序。id的值越大，執行的優先順序越高；如果id相同，則從上往下執行。
（2）select_type。查詢型別，常見查詢型別說明如下：
- SIMPLE：表示簡單查詢方式。SQL語句中一般不會不使用UNION和子查詢等。
- PRIMARY：表示包含子查詢的SQL語句的最外層查詢語句的查詢型別，即當查詢中包含子查詢時，最外層的查詢語句就會顯示為PRIMARY。
- UNION：在查詢語句中，如果在UNION關鍵字之後出現了第二個SELECT，則被標記為UNION。
- UNION RESULT：表示查詢中有多個查詢結果集執行UNION操作。
- DEPENDENT UNION：表示在子查詢中存在UNION操作時，從UNION之後的第二個及之後的SELECT語句都是DEPENDENT UNION。
- DEPENDENT SUBQUERY：子查詢中UNION 中第一個SELECT查詢為DEPENDENT SUBQUERY。
- SUBQUERY：子查詢內層查詢的第一個SELECT。
- DERIVED：在查詢語句中，如果from子句的子查詢中出現了union關鍵字，則外層select查詢將被標記為DERIVED。
- MATERIALIZED：表示子查詢被物化，物化透過將子查詢結果作為一個臨時表來加快查詢執行速，從而能夠使得子查詢只執行一次。
- UNCACHEABLE SUBQUERY：表示查詢結果集無法快取的子查詢，需要逐次查詢。
- UNCACHEABLE UNION：表示子查詢不可被物化，需要逐次執行（即需要執行多次） （節選自《軟體效能測試、分析與調優實踐之路》(第2版)，作者張永清，轉載請註明出處）
（3）table。查詢涉及的表名或者表的別名。
（4）type。表示表連線的型別，包括的型別說明如下。這些型別的效能從高到低的順序是null→system→const→eq_ref→ref→fulltext→ref_or_null→index_merge→unique_subquery→index_subquery→range→index→ALL。
- null：表示不訪問任何的表。
- system：表示表中只有一條記錄，相當於系統表。一般可以認為是const型別的特例。
- const：表示主鍵或者唯一索引的常量查詢，表中最多隻有1行記錄符合查詢要求。通常const使用到主鍵或者唯一索引進行定值查詢、常量查詢，查詢的速度非常快。
- eq_ref：表示join 查詢過程中，關聯條件欄位使用主鍵或者唯一索引，出來的行數不止一行。eq_ref是一種查詢效能很高的 join 操作。
- ref：表示非聚集索引的常量查詢。
- fulltext：表示查詢的過程中，使用了fulltext型別的索引。
- ref_or_null：跟ref查詢類似，在ref的查詢基礎上會多加一個null值的條件查詢。
- index_ merg：表示索引聯合查詢。
- unique_ subquery：表示查詢使用主鍵的子查詢。
- index_subquery：表示查詢使用非聚集索引的子查詢。
- range：表示查詢透過使用索引範圍的查詢。一般包括：=、<>、>、>=、<、<=、IS NULL、BETWEEN、IN、<=> 等範圍。
- index：表示透過索引進行掃描查詢。
- ALL：表示全表掃描，效能最差。
（5）possible_keys。查詢時預計可能會使用的索引。這裡說的索引只是可能會用到，實際查詢不一定會用到。
（6）key。實際查詢時真實使用的索引。
（7）key_len。使用的索引長度。
（8）ref 。關聯資訊。
（9）rows。查詢時掃描的資料記錄行數。
（10）extra。表示查詢特性的使用情況。常用的查詢特性如下所示：
- Using index：表示使用了索引（通常也可以叫覆蓋索引）。
- Using index condition：表示使用了索引進行過濾。
- Using MRR：表示使用了索引進行內部排序。
- Using where：表示使用了where條件。
- Using temporary：表示使用了臨時表。
- Using filesort：表示使用檔案排序（一般指無法利用索引來完成的排序）。
6.2.3 MySQL資料庫效能定位步驟
MySQL資料庫效能定位的常見步驟總結如圖6-2-8所示。
圖6-2-8
（1）首先透過本書第2章中伺服器的效能監控與分析，找到當前伺服器的資源使用情況，重點關注CPU、記憶體、磁碟等使用率。
（2）根據伺服器資源的使用率情況，初步判斷當前MySQL資料庫可能在執行的操作型別。（節選自《軟體效能測試、分析與調優實踐之路》(第2版)，作者張永清，轉載請註明出處）
- 如果是記憶體和CPU使用率過高，或者磁碟讀取資料頻繁，說明可能存在大量SQL查詢或者統計計算操作。
- 如果是磁碟寫入頻繁，說明可能存在大量頻繁的insert或者update 操作。
（3）透過在資料庫中執行SHOW FULL PROCESSLIST 命令觀察當前正在執行的SQL 命令，可以每間隔5~10s 多執行幾次該命令，找到哪些SQL操作是持續一直在執行中。
- 如果是insert 或者update 語句，透過檢視本書的6.1.2小節中的知識講解，來檢視MySQL資料庫當前執行的事務與鎖，獲取insert 或者update的SQL操作是否存在鎖等待等情況，從而導致insert和update 一直處於等待中。
- 如果是查詢操作，可以結合慢查詢日誌一起，找到慢查詢的SQL語句，使用explain關鍵字檢視SQL的執行計劃來定位SQL為什麼查詢慢。
（節選自《軟體效能測試、分析與調優實踐之路》(第2版)，作者張永清，轉載請註明出處）
