文章目錄
- 如何定位并優化慢查詢SQL？
- 如何使用慢查詢日志？
- 慢查詢例子示範，新手都能看懂
- 查詢語句慢怎麼辦？explain帶你分析sql執行計劃
- 當主鍵索引、唯一索引、普通索引都存在，查詢優化器如何選擇？
1.如何定位并優化慢查詢SQL？
一般有3個思考方向 1.根據慢日志定位慢查詢sql 2.使用explain等工具分析sql執行計劃 3.修改sql或者盡量讓sql走索引
2.如何使用慢查詢日志？
先給出步驟，後面說明
有3個步驟
1.開啟慢查詢日志
首先開啟慢查詢日志，由參數slow_query_log決定是否開啟，在MySQL指令行下輸入下面的指令：
set global slow_query_log=on;
預設環境下，慢查詢日志是關閉的，是以這裡開啟。
2.設定慢查詢門檻值
set global long_query_time=1;
隻要你的SQL實際執行時間超過了這個門檻值，就會被記錄到慢查詢日志裡面。這個門檻值預設是10s，線上業務一般建議把long_query_time設定為1s，如果某個業務的MySQL要求比較高的QPS，可設定慢查詢為0.1s。
發現慢查詢及時優化或者提醒開發改寫。一般測試環境建議long_query_time設定的閥值比生産環境的小，比如生産環境是1s，則測試環境建議配置成0.5s。便于在測試環境及時發現一些效率的SQL。
甚至某些重要業務測試環境long_query_time可以設定為0，以便記錄所有語句。并留意慢查詢日志的輸出，上線前的功能測試完成後，分析慢查詢日志每類語句的輸出，重點關注Rows_examined（語句執行期間從存儲引擎讀取的行數），提前優化。
3.确定慢查詢日志的檔案名和路徑
show global variables like 'slow_query_log_file'
結果會發現慢日志預設路徑就是MySQL的資料目錄，我們可以來看一下MySQL資料目錄
show global variables like 'datadir';
不用關注這裡為什麼不是MySQL 8.0，這和版本沒什麼關系的。
來，直接上菜，幹巴巴的定義我自己都看不下去
我們先來檢視一下變量，我框出了需要注意的點
查詢帶有quer的相關變量
show global variables like '%quer%';
這裡設定慢查詢門檻值為1s
set global long_query_time=1;
可以看到已經修改過來了
但是重新開機mysql用戶端設定和統計慢查詢日志條數就會清零，即所有配置修改會還原
指令修改配置之後，在指令行net stop mysql關閉MySQL服務，再net start mysql開啟MySQL服務，接着執行show global variables like '%quer%';會發現配置還原了。
在配置檔案修改才能永久改變，否則重新開機資料庫就還原了
3.慢查詢例子示範，新手都能看懂
資料表結構，偷懶沒寫comment
CREATE TABLE `person_info_large` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`account` VARCHAR (10),
`name` VARCHAR (20),
`area` VARCHAR (20),
`title` VARCHAR (20),
`motto` VARCHAR (50),
PRIMARY KEY (`id`),
UNIQUE(`account`),
KEY `index_area_title`(`area`,`title`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
這裡的資料是200W條。請注意表結構，記住哪幾個字段有索引即可，後續圍繞這個表進行分析。
這個3.36s并不是實際執行時間，實際執行時間得去慢查詢日志去看Query_time參數
可以看到Query_time: 6.337729s，超過了1s，是以會被記錄，一個select語句查詢這麼久，簡直無法忍受。
圖中其他的參數解釋如下：
- Time：慢查詢發生的時間
- Query_time：查詢時間
- Lock_time：等待鎖表的時間
- Rows_sent：語句傳回的行數
- Rows_exanined：語句執行期間從存儲引擎讀取的行數
上面這種方式是用系統自帶的慢查詢日志檢視的，如果覺得系統自帶的慢查詢日志不友善檢視，可以使用pt-query-digest或者mysqldumpslow等工具對慢查詢日志進行分析。
注意：有的慢查詢正在執行，結果已經導緻資料庫負載過高，而由于慢查詢還沒執行完，是以慢查詢日志看不到任何語句，此時可以使用show processlist指令檢視正在執行的慢查詢。show processlist顯示哪些線程正在運作，如果有PROCESS權限，則可以看到所有線程。否則，隻能看到目前會話線程。
4.查詢語句慢怎麼辦？explain帶你分析sql執行計劃
根據上一節的表結構可以知道，account是添加了唯一索引的字段。explain分析一下執行計劃。
我們重點需要關注select_type、type、possible_keys、key、Extra這些列，我們來一一說明，看到select_type列，這裡是SIMPLE簡單查詢，其他值下面給大家列出。
type列，這裡是index，表示全索引掃描
表格從上到下代表了sql查詢性能從最優到最差，如果是type類型是all，說明sql語句需要優化。
注意：如果type = NULL，則表明個MySQL不用通路表或者索引，直接就能得到結果，比如explain select sum(1+2);
possible_keys代表可能用到的索引列，key表示實際用到的索引列，以實際用到的索引列為準，這是查詢優化器優化過後選擇的，然後我們也可以根據實際情況強制使用我們自己的索引列來查詢。
Extra列，這裡是Using index
一定要注意，Extra中出現Using filesort、Using temporary代表MySQL根本不能使用索引，效率會受到嚴重影響，應當盡可能的去優化。
出現Using filesort說明MySQL對結果使用一個外部索引排序，而不是從表裡按索引次序讀到相關内容，有索引就維護了B+樹，資料本來就已經排好序了，這說明根本沒有用到索引，而是資料讀完之後再排序，可能在記憶體或者磁盤上排序。也有人将MySQL中無法利用索引的排序操作稱為“檔案排序”。
出現Using temporary表示MySQL在對查詢結果排序時使用臨時表，常見于order by和分組查詢group by
回到上一個話題，我們看到account是添加了唯一索引的字段。explain分析了執行計劃後
直接按照account降序來查
檢視慢查詢日志發現，使用索引之後，查詢200W條資料的速度快了2s
接着我們分析一下查詢name的sql執行計劃
然後給name字段加上索引
加上索引之後，繼續看看查詢name的sql執行計劃
對比一下前面name不加索引時的執行計劃就會發現，加了索引後，type由ALL全表掃描變成index索引掃描。order by并沒有 using filesort，而是using index，這裡B+樹已經将這個非聚集索引的索引字段的值排好序了，而不是等到查詢的時候再去排序。
接着我們繼續執行查詢語句，此時name已經是添加了索引的。
結果發現，name添加索引之前，降序查詢name是花費6.337729s，添加索引之後，降序查詢name花費了3.479827s，原因就是B+樹的結果集已經是有序的了。
5.當主鍵索引、唯一索引、普通索引都存在，查詢優化器如何選擇？
查詢一下資料的條數，這裡count(id)，分析一下sql執行計劃
這裡實際使用的索引是account唯一索引。
分析一下：實際使用哪個索引是查詢優化器決定的，B+樹的葉子結點就是連結清單結構，周遊連結清單就可以統計數量，但是這張表，有主鍵索引、唯一索引、普通索引，優化器選擇了account這個唯一索引，這肯定不會使用主鍵索引，因為主鍵索引是聚集索引，每個葉子包含具體的一個行記錄（很多列的資料都在裡面），而非聚集索引每個葉子隻包含下一個主鍵索引的指針，很顯然葉子結點包含的資料是越少越好，查詢優化器就不會選擇主鍵索引
當然，也可以強制使用主鍵索引，然後分析sql執行計劃
我們看一下優化器預設使用唯一索引大緻執行時間676ms
強制使用主鍵索引大緻執行時間779ms
我們可以用force index強制指定索引，然後去分析執行計劃看看哪個索引是更好的，因為查詢優化器選擇索引不一定是百分百準确的，具體情況可以根據實際場景分析來确定是否使用查詢優化器選擇的索引。