本文更新於2019-08-18，使用MySQL 5.7，操作系統為Deepin 15.4。優化SQL語句的步驟通過SHOW STATUS瞭解SQL語句的執行情況操作的計數，是對執行次數進行計數，不論提交還是回滾都會累加。 Com_xxx形式的參數表示每個xxx語句執行的次數，對所有的存儲引擎都 ...

本文更新於2019-08-18，使用MySQL 5.7，操作系統為Deepin 15.4。

優化SQL語句的步驟

通過`SHOW STATUS`瞭解SQL語句的執行情況

操作的計數，是對執行次數進行計數，不論提交還是回滾都會累加。

Com_xxx形式的參數表示每個xxx語句執行的次數，對所有的存儲引擎都會進行累計，如：

Com_select：執行SELECT的次數。
Com_insert：執行INSERT的次數，對批量插入的操作只累加一次。
Com_update：執行UPDATE的次數。
Com_delete：執行DELETE的次數。
Com_commit：事務提交的次數。
Com_rollback：事務回滾的次數。

Innodb_rows_xxx形式的參數只對InnoDB存儲引擎進行累計，其累計的方式也與Com_xxx不同：

Innodb_rows_read：執行SELECT返回的行數。
Innodb_rows_inserted：執行INSERT插入的行數。
Innodb_rows_updated：執行UPDATE更新的行數。
Innodb_rows_deleted：執行DELETE刪除的行數。

Handler_read_xxx形式的參數可表示索引的使用情況：

Handler_read_key：一個行被索引值讀的次數。高表示索引被經常使用。
Handler_read_rnd_next：在數據文件中讀下一個行的次數。高表示索引不經常使用，進行大量的表掃描。

以下參數便於瞭解資料庫的基本情況：

Connections：試圖連接伺服器的次數。
Uptime：伺服器工作時間。
Slow_queries：慢查詢次數。

定位執行效率低下的SQL語句

通過慢查詢日誌定位執行效率低下的SQL語句。
通過SHOW PROCESSLIST查看伺服器當前的線程，包括線程的狀態、是否鎖表等，可以實時查看SQL的執行情況。

通過`EXPLAIN`或`DESC`分析SQL的執行計劃

DESC和EXPLAIN分析SQL執行計劃的使用和作用是一樣的。

執行EXPLAIN statement後再執行SHOW WARNINGS，可以看到被優化器改寫後真正執行的SQL。

一個執行計劃包括若幹行，每行包括如下的列：

id:值越大越先執行（值越大越位於下方），一樣大從上至下執行。
select_type：查詢類型，可取如下值：
- DERIVED：派生表的查詢。
- SIMPLE：簡單查詢，即不使用子查詢和UNION的查詢。
- SUBQUERY：子查詢。
- PRIMARY：主查詢，即包含子查詢的最外層查詢，或UNION中的第一個查詢。
- UNION：UNION中的第二個或之後的查詢。
table：輸出結果集的表。
partitions：訪問的分區。
type：訪問類型，即在表中查找所需行的方式。

以下取值性能由最差至最好：
1. ALL：全表掃描，遍歷所有行。
2. index：索引全掃描，遍歷整個索引。
3. range：索引範圍掃描，常見於<、<=、>、>=、BETWEEN等操作符。
4. ref：使用非唯一索引掃描或唯一索引的首碼掃描，返回匹配某個值的所有記錄行。其經常出現在JOIN操作中。
5. eq_ref：類似ref，區別在於使用唯一索引。其出現在使用PRIMARY KEY或UNIQUE INDEX作為關聯條件的表連接中。
6. const/system：單表中最多有一個匹配行，因此這個匹配行中的其他列能被優化器當做常量來使用。如根據PRIMARY KEY或UNIQUE INDEX進行過濾的查詢。system是const的特例，當表中只有一條記錄時的const就為system。
7. NULL：不用訪問表或索引，就能直接得到結果。
還可取其他的值，如：
- ref_or_null：與ref類似，區別在於條件中包含對NULL的查詢。
- index_merge：索引合併。
- unique_subquery：IN後面是一個查詢唯一索引欄位的子查詢。
- index_subquery：與unique_subquery類型，區別在於IN後面是一個查詢非唯一索引欄位的子查詢。
possible_keys：查詢時可能使用的索引。
key：實際使用的索引。
key_len：實際使用到的索引的位元組長度。
ref：實際使用的索引在其他表的關聯欄位。如果是常數等值查詢，則為const。
rows：掃描的行數。
filtered：存儲引擎返回的數據過濾後，滿足查詢條件的記錄的比例。
Extra：執行情況的說明，包括不適合在其他列中顯示但是對執行計劃非常重要的額外信息。
- Using filesort：filesort排序，而不是通過索引直接返回排序結果。
- Using index：覆蓋索引掃描，直接訪問索引就能獲取所需的數據，不需要通過索引回表。
- Using index condition：使用ICP（Index Condition Pushdown，參看“索引”章節）優化查詢，將某些情況下的條件過濾操作下放到存儲引擎層完成，降低不必要的IO訪問。
- Using where：優化器除了利用索引加速訪問外，還需根據索引回表查詢數據。
- Using union：多次查詢後對結果集合併，如使用OR查詢。

通過`SHOW PROFILES`和`SHOW PROFILE`分析SQL

profiling預設是關閉的，可通過設置變數@@profiling進行打開或關閉。

SHOW PROFILES結果包括以下欄位：

Query_ID：查詢ID。
Duration：查詢耗時。
Query：查詢語句。

SHOW PROFILE [ALL|CPU|{BLOCK IO}|{PAGE FAULTS}|SOURCE][, ...] FOR QUERY query_id（query_id為SHOW PROFILES結果的Query_ID欄位）結果包括以下欄位：

Status：查詢執行過程中的狀態。各狀態含義如下：
- starting
- Waiting for query cache lock
- checking query cache for query
- checking permissions
- Opening tables
- inti
- System lock
- optimizing
- statistics
- preparing
- executing
- Sending data：開始訪問數據行並把結果返回客戶端，包含大量的磁碟操作。
- end
- query end
- closing tables
- removing tmp table
- freeing items
- storing result in query cache
- logging slow query
- cleaning up
Duration：耗時。
CPU_user
CPU_system
Context_voluntary
Context_involuntary
Block_ops_in
Block_ops_out
Messages_sent
Messages_received
Page_faults_major
Page_faults_minor
Swaps
Source_function
Source_file
Source_line

通過trace分析優化器如何選擇執行計劃

需打開trace，設置格式為JSON，設置trace最大能使用的記憶體大小。如：

SET @@optimizer_trace="enabled=on";
SET @@end_markers_in_json=on;
SET @@optimizer_trace_max_size=1000000;

執行SELECT * FROM information_schema.OPTIMIZER_TRACE即可得到結果。

分析、檢查、優化、修複表

ANALYZE、CHECK、OPTIMIZE、REPAIR執行期間都會對錶進行鎖定。

分析表，使得SQL能夠生成正確的執行計劃。如果感覺實際的執行計劃並不符合預期，執行一次分析表可能會解決問題：

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...]

檢查表，用於檢查表或視圖是否有錯誤。如視圖定義中被引用的表不存在：

CHECK TABLE tablename[, ...] [{QUICK|FAST|MEDIUM|EXTENDED|CHANGED}[ ...]]

優化表，可以將表中的空間碎片進行合併。如果已經刪除表的很大一部分數據，或已經對含有可變長度行（含有VARCHAR、*BLOB或*TEXT的列）的表進行很多更改，則應該進行優化表：

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...]

修複表，對壞表進行修複：

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE tablename[, ...] [{QUICK|EXTENDED|USE_FRM}[ ...]]

常用SQL優化

大批量導入數據

對MyISAM存儲引擎，可通過關閉和打開非唯一索引的更新提高導入效率：

ALTER TABLE tablename DISABLE KEYS;
# import data
ALTER TABLE tablename ENALBE KEYS;

對InnoDB存儲引擎：

因為InnoDB表是按照主鍵的順序保存的，所以將導入的數據按照主鍵的順序排列，可以提高導入效率。
在導入數據前執行SET unique_checks=0關閉唯一性校驗，在導入結束後執行SET unique_checks=1恢復唯一性校驗，可提高導入效率。
如果應用使用自動提交的方式，建議導入前執行SET autocommit=0關閉自動提交，導入結束後執行SET autocommit=1恢復自動提交，可提高導入效率。

優化`INSERT`語句

當從同一客戶端插入很多行時，應儘量使用多個值列表的INSERT語句。
如果從不同客戶端插入很多行，可以使用INSERT DELAYED，讓INSERT`馬上返回（實際上數據被放在MySQL伺服器記憶體隊列中）。
將索引文件和數據文件在不同的磁碟存放（利用建表中的選項）。
如果進行批量INSERT，可以增加bulk_insert_buffer_size變數值來提高速度（只對MyISAM表使用）。
當從一個文件裝載一個表時，使用LOAD DATA INFILE。

優化`ORDER BY`語句

MySQL有兩種排序方式：

通過有序索引掃描直接返回有序數據。在使用EXPLAIN分析時顯示為Using index。
filesort排序。將返回的數據在sort_buffer_size設置的記憶體排序區進行排序，至於是否使用磁碟文件和臨時表等，取決於MySQL伺服器對排序參數的設置和需要排序數據的大小。

對於filesort，MySQL比較查詢取出的欄位總大小和max_length_for_sort_data，判斷使用哪種排序演算法：

兩次掃描演算法（Tow passes）：第一次根據條件獲取排序欄位和行指針信息，併在排序區中排序。第二次根據行指針回表讀取記錄，可能導致大量隨機IO操作。
一次掃描演算法（Single Pass）：一次性取出滿足條件的行的所有欄位，然後在排序區排序後直接輸出結果。這會導致記憶體開銷比較大。

優化ORDER BY語句應該：儘量減少額外的排序，通過索引直接返回有序數據。WHERE條件和ORDER BY使用相同的索引，並且ORDER BY的順序和索引順序相同，並且ORDER BY的欄位都是升序或都是降序。否則肯定需要額外的排序操作，這樣就會出現filesort排序。

儘量SELECT必要的欄位名，而不是SELECT *所有欄位，這樣可以減少排序區的使用，提高性能。

優化`GROUP BY`語句

MySQL會對GROUP BY的所有欄位進行排序。如果想避免排序的消耗，可以使用ORDER BY NULL禁止排序。

優化子查詢

有些情況下，子查詢可以被更有效率的表連接代替。因為表連接不需要在記憶體中創建臨時表。

優化`OR`條件

對於含有OR的查詢，如果要利用索引，則OR之間的每個欄位都必需能利用索引。此時，實際是對OR的各個欄位分別查詢的結果進行UNION操作。

優化分頁查詢

執行LIMIT offset_start, row_count時，MySQL排序出offset_start+row_count條記錄後僅僅返回最後row_count條記錄，前面的offset_start條記錄都會被丟棄，查詢和排序的代價非常高。有兩種優化思路：

在索引上完成排序分頁的操作，最後根據主鍵關聯回表查詢所需的其他列內容。
把查詢轉換成基於某個位置的查詢，使用LIMIT row_count代替LIMIT offset_start, row_count。但這種方式對數據集有特定的要求。

使用SQL提示

SQL提示（SQL HINT）就是在SQL語句中加入一些人為提示來達到優化的目的。

SELECT SQL_BUFFER_RESULT * FROM ...

這個語句強制MySQL生成一個臨時結果集。生成後所有表上的鎖均被釋放，這能在遇到表鎖問題或要花很長時間將結果傳給客戶端時有幫助。

SELECT * FROM tablename USE|IGNORE|FORCE INDEX (indexname[, ...]) WHERE ...

USE INDEX提供希望（實際執行時不一定會被選擇）查詢時使用的索引，IGNORE INDEX忽略指定的索引，FORCE INDEX強制使用指定的索引。

常用SQL技巧

利用ORDER BY RAND()提取隨機行。
利用GROUP BY ... WITH ROLLUP獲取更多的分組聚合信息。

MySQL學習筆記（18）：SQL優化