MySQL日常維護中，性能瓶頸是每個運維人員都頭疼的問題之一，那么該如何有效解決這個問題呢？慢SQL問題則是重中之重。因此本文重點講解的是從慢日志的抓取、執行計劃的解讀、優化的原則到各種類型的案例解析等方面來全方位的講解慢SQL優化。

1、開啟慢查詢

實時獲取有性能問題的SQL，數據庫參數設定如下：

2、慢日志解析

（需提前安裝PT工具）：

pt-query-digest slow.log --since 2020-06-09 10:43:00 --until2020-06-09 10:45:00> /tmp/slow.log

處理原則：優先優化高并發SQL，頻率低的大SQL次之。

3、慢SQL詳細信息

此處可以看到的信息有SQL查詢的數據庫，用戶，具體的SQL內容等。

1、EXPLAIN語法

EXPLAIN與DESCRIBE、DESC是同義詞，具有相同的作用。

Type訪問類型是SQL優化的一個重要指標，結果值從好到壞順序：

system> const > eq_ref > ref > fulltext > ref_or_null >index_merge > unique_subquery > index_subquery > range >index > ALL

2、EXPLAINFORMAT=json解讀

有時候使用explain解析出來的執行計劃不太詳細，而不知道該如何去優化時，可以使用explainformat=json +sql來獲取更詳細的執行計劃信息。

查看執行計劃附加信息，showwarningsG;

3、MYSQL8.0新功能

EXPLAIN FORMAT = TREE --顯示查詢計劃和成本估算

EXPLAINANALYZE—顯示實際執行時間及成本

1 、嚴禁使用SELECT*方式查詢語句，必須明確查詢字段，INSERT語句必須明確要插入的字段。

2、嚴禁單條SQL關聯表超過3張，關聯字段必須有索引且數據類型一致。

3、嚴禁單條SQL子查詢超過2層。

4、嚴禁在SQL中進行計算或嵌套判斷邏輯。

5、嚴禁查詢條件中字段無索引，表的索引數量不要超過6個。

6、嚴禁在where條件中字段使用函數或者表達式(例如wherecol/3>=100)。

7、嚴禁負向查詢條件(!=、<>、not...)、單表行數大于5萬的禁止左模糊、全模糊查詢(例如：colA like ‘%服務’)。

8、嚴禁傳入變量類型與查詢條件中字段類型不匹配。

9、嚴禁表無主鍵或使用復合索引作為主鍵，嚴禁使用無序數據作為主鍵內容。

10、嚴禁使用外鍵、視圖、觸發器、存儲過程、自定義函數和分區表。

1、隱式轉換

原則：禁止隱式轉換，保持變量類型與字段類型一致

SQL1（正確）：selectemp_no,from_date from dept_emp1 where dept_no=404838;

SQL2（錯誤）：selectemp_no,from_date from dept_emp1 where dept_no=404838;

2、WHERE子查詢

優化子查詢原則：使用連接代替子查詢，效率更佳  

A.使用連接方式改寫子查詢，案例如下  

例1: SELECTDISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROMt2);

改寫: SELECTDISTINCT t1.column1 FROM t1, t2

WHEREt1.column1 = t2.column1;

或：SELECTDISTINCT t1.column1 FROM t1 JOIN ON t1.column1 = t2.column1;

例2: SELECT *FROM t1 WHERE id NOT IN (SELECT id FROM t2);

改寫:  SELECT* FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

或LEFT JOIN：

SELECT table1.*   FROM table1 LEFT JOIN table2

ONtable1.id=table2.id   WHERE table2.id IS NULL; 

B.對于只返回一行的無關聯子查詢用‘=’代替‘in’

例:  SELECT *FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

改寫:  SELECT* FROM t1 WHERE t1.col_name= (SELECT a FROM t2 WHERE b = some_const);

總結：對于數據庫來說，在絕大部分情況下， 連接會比子查詢更快。使用連接的方式，MySQL優化器一般可以生成更佳的執行計劃，更高效地處理查詢。而子查詢往往需要運行重復的查詢，子查詢生成的臨時表上也沒有索引， 因此效率會更低。

3、OR語句

原則：有關or的優化，A.建立相關索引，B.將or轉化為in或union

A.or子句全部相同，則改為in

示例：select* from t1 where a=1 or a=3;

改為：select* from t1 where a in(1,3);

B.or子句具有公共子序列前綴的，請在or公共部分建立索引

示例：（如下需要在a列上創建索引）

select * from t1 where (a=1 and b=2) or (a=3 and c=4);

C.若無公共，則建議改為unionall，并為每部分建立索引

示例   select* from t1 where a=1 or b=2;

可以使用 Indexmerge

或者轉換（效率更高）：
    select * from t1 where a=1

union all

select * from t1 where b=2;

4、GROUP/ORDER BY

4.1、orderby子句，盡量使用Index方式排序，在索引列上遵循索引的最佳左前綴原則。如下：

Key(a,b,c)，Orderby 能使用索引情況

--order  by  a

--order  by  a, b

--order  by  a, b,c

--order  by  a desc ,b desc ,c desc

4.2、如果where使用索引的最左前綴定義為常量，則orderby能使用索引

--wherea=const  order by b,c

--wherea=const   and b=const  order by c

--wherea=const  and b>const order by b,c

總結：分組統計可以禁止排序，默認情況下，有分組必排序，如果想避免排序結果的消耗，可以指定orderby null禁止排序。

5、LIMIT偏移量過大

禁止分頁查詢偏移量過大，如limit100000,10

優化方法一：

A.limit查詢轉換成某個位置的查詢，即把limitm,n轉換成limitn；

B.利用自增主鍵，避免offset使用;

C. 限制用戶翻頁。

調整LIMIT1000

優化方法二：分頁查詢盡可能地使用索引覆蓋掃描，而不是所有的列，然后再做一次關聯操作再返回所需的列。

#優化前

select film_id,description from film order by title limit 50,5

#優化后

select a.film_id, a.description from film a inner join (selectfilm_id from film order by title limit 50,5) b on a.film_id =b.film_id

6、模糊查詢

全模糊或左模糊不使用索引

優化建議：全模糊查詢改為dsps_staff_id like  ’HE12065%’，優化后效率提升百倍。

7、覆蓋索引

包含所有滿足查詢需要的數據的索引成為覆蓋索引，也就是平時所說的不需要回表操作,對于一個索引覆蓋查詢，顯示為usingindex。

這里最主要看Extra，它的值為Usingindex，它在這句查詢中含義就是直接訪問film_id這個索引就足已獲取到所需要的數據，不需要再通過索引回表查詢了。

使用覆蓋索引的前提條件是，查詢返回的字段數足夠少，select* 類不可以。

8、表/字段 別名

問題點：

A：原sql基表是t5，大量使用臨時表、排序，效率低下

B：order bycrttime 使用別名，導致索引失效

建議：orderby crt_time 使用字段名代替別名

9、字符集不同

關聯字段的字符集不一致，導致索引不可用。

例如：knowledge_rel的字符集及校驗規則，與關聯表knowledge不一致，數據關聯時，影響SQL執行效率。

注：utf8mb4_bin比較方法就是直接將所有字符看作二進制串，然后從最高位往最低位比對，所以它是區分大小寫的。

10、left join

使用leftjoin一定要注意：

A：條件中盡量有強過濾，將驅動表為小

B：右表的條件列一定要加上索引（主鍵、唯一索引、前綴索引等），最好能夠使type達到range及以上（ref,eq_ref,const,system）

C：無視以上兩點，一般不要用leftjoin~~！

原SQL：存在強過濾，但是在所有數據join后的結果集上過濾，差！

優化后：調整在where后，將驅動表實行強過濾后變小，再與其它表leftjoin，提高效率。