本文更新于2019-08-18，使用MySQL 5.7，作業系統為Deepin 15.4，
目錄
- 優化SQL陳述句的步驟
- 通過SHOW STATUS了解SQL陳述句的執行情況
- 定位執行效率低下的SQL陳述句
- 通過EXPLAIN或DESC分析SQL的執行計劃
- 通過SHOW PROFILES和SHOW PROFILE分析SQL
- 通過trace分析優化器如何選擇執行計劃
- 分析、檢查、優化、修復表
- 常用SQL優化
- 大批量匯入資料
- 優化INSERT陳述句
- 優化ORDER BY陳述句
- 優化GROUP BY陳述句
- 優化子查詢
- 優化OR條件
- 優化分頁查詢
- 使用SQL提示
- 常用SQL技巧
優化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：訪問型別，即在表中查找所需行的方式，
以下取值性能由最差至最好：
- ALL：全表掃描，遍歷所有行，
- index：索引全掃描，遍歷整個索引，
- range：索引范圍掃描，常見于
<、<=、>、>=、BETWEEN等運算子， - ref：使用非唯一索引掃描或唯一索引的前綴掃描，回傳匹配某個值的所有記錄行，其經常出現在
JOIN操作中， - eq_ref：類似ref，區別在于使用唯一索引，其出現在使用
PRIMARY KEY或UNIQUE INDEX作為關聯條件的表連接中， - const/system：單表中最多有一個匹配行，因此這個匹配行中的其他列能被優化器當做常量來使用，如根據
PRIMARY KEY或UNIQUE INDEX進行過濾的查詢，system是const的特例，當表中只有一條記錄時的const就為system， - 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獲取更多的分組聚合資訊，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/6042.html
標籤：MySQL
上一篇：druid配置
下一篇：關于sql