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

1、開啟慢查詢

實時獲取有性能問題的SQL，數(shù)據(jù)庫參數(shù)設(shè)定如下：

2、慢日志解析

（需提前安裝PT工具）：

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

處理原則：優(yōu)先優(yōu)化高并發(fā)SQL，頻率低的大SQL次之。

3、慢SQL詳細(xì)信息

此處可以看到的信息有SQL查詢的數(shù)據(jù)庫，用戶，具體的SQL內(nèi)容等。

1、EXPLAIN語法

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

Type訪問類型是SQL優(yōu)化的一個重要指標(biāo)，結(jié)果值從好到壞順序：

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

2、EXPLAINFORMAT=json解讀

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

查看執(zhí)行計劃附加信息，showwarningsG;

3、MYSQL8.0新功能

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

EXPLAINANALYZE—顯示實際執(zhí)行時間及成本

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

2、嚴(yán)禁單條SQL關(guān)聯(lián)表超過3張，關(guān)聯(lián)字段必須有索引且數(shù)據(jù)類型一致。

3、嚴(yán)禁單條SQL子查詢超過2層。

4、嚴(yán)禁在SQL中進(jìn)行計算或嵌套判斷邏輯。

5、嚴(yán)禁查詢條件中字段無索引，表的索引數(shù)量不要超過6個。

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

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

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

9、嚴(yán)禁表無主鍵或使用復(fù)合索引作為主鍵，嚴(yán)禁使用無序數(shù)據(jù)作為主鍵內(nèi)容。

10、嚴(yán)禁使用外鍵、視圖、觸發(fā)器、存儲過程、自定義函數(shù)和分區(qū)表。

1、隱式轉(zhuǎn)換

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

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子查詢

優(yōu)化子查詢原則：使用連接代替子查詢，效率更佳  

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.對于只返回一行的無關(guān)聯(lián)子查詢用‘=’代替‘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);

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

3、OR語句

原則：有關(guān)or的優(yōu)化，A.建立相關(guān)索引，B.將or轉(zhuǎn)化為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列上創(chuàng)建索引）

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

或者轉(zhuǎn)換（效率更高）：
    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

總結(jié)：分組統(tǒng)計可以禁止排序，默認(rèn)情況下，有分組必排序，如果想避免排序結(jié)果的消耗，可以指定orderby null禁止排序。

5、LIMIT偏移量過大

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

優(yōu)化方法一：

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

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

C. 限制用戶翻頁。

調(diào)整LIMIT1000

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

#優(yōu)化前

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

#優(yōu)化后

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、模糊查詢

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

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

7、覆蓋索引

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

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

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

8、表/字段 別名

問題點(diǎn)：

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

B：order bycrttime 使用別名，導(dǎo)致索引失效

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

9、字符集不同

關(guān)聯(lián)字段的字符集不一致，導(dǎo)致索引不可用。

例如：knowledge_rel的字符集及校驗規(guī)則，與關(guān)聯(lián)表knowledge不一致，數(shù)據(jù)關(guān)聯(lián)時，影響SQL執(zhí)行效率。

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

10、left join

使用leftjoin一定要注意：

A：條件中盡量有強(qiáng)過濾，將驅(qū)動表為小

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

C：無視以上兩點(diǎn)，一般不要用leftjoin~~！

原SQL：存在強(qiáng)過濾，但是在所有數(shù)據(jù)join后的結(jié)果集上過濾，差！

優(yōu)化后：調(diào)整在where后，將驅(qū)動表實行強(qiáng)過濾后變小，再與其它表leftjoin，提高效率。