SQL優(yōu)化與診斷

Explain診斷

Explain各參數(shù)的含義如下：

select_type 常見類型及其含義

• SIMPLE：不包含子查詢或者 UNION 操作的查詢
• PRIMARY：查詢中如果包含任何子查詢，那么最外層的查詢則被標記為 PRIMARY
• SUBQUERY：子查詢中第一個 SELECT
• DEPENDENT SUBQUERY：子查詢中的第一個 SELECT，取決于外部查詢
• UNION：UNION 操作的第二個或者之后的查詢
• DEPENDENT UNION：UNION 操作的第二個或者之后的查詢,取決于外部查詢
• UNION RESULT：UNION 產(chǎn)生的結(jié)果集
• DERIVED：出現(xiàn)在 FROM 字句中的子查詢

type常見類型及其含義

• system：這是 const 類型的一個特例，只會出現(xiàn)在待查詢的表只有一行數(shù)據(jù)的情況下
• consts：常出現(xiàn)在主鍵或唯一索引與常量值進行比較的場景下，此時查詢性能是最優(yōu)的
• eq_ref：當連接使用的是完整的索引并且是 PRIMARY KEY 或 UNIQUE NOT NULL INDEX 時使用它
• ref：當連接使用的是前綴索引或連接條件不是 PRIMARY KEY 或 UNIQUE INDEX 時則使用它
• ref_or_null：類似于 ref 類型的查詢，但是附加了對 NULL 值列的查詢
• index_merge：該聯(lián)接類型表示使用了索引進行合并優(yōu)化
• range：使用索引進行范圍掃描，常見于 between、> 、< 這樣的查詢條件
• index：索引連接類型與 ALL 相同，只是掃描的是索引樹，通常出現(xiàn)在索引是該查詢的覆蓋索引的情況
• ALL：全表掃描，效率最差的查找方式

阿里編碼規(guī)范要求：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好

key列

實際在查詢中是否使用到索引的標志字段

Extra列

Extra 列主要用于顯示額外的信息，常見信息及其含義如下：

• Using where ：MySQL 服務(wù)器會在存儲引擎檢索行后再進行過濾
• Using filesort：通常出現(xiàn)在 GROUP BY 或 ORDER BY 語句中，且排序或分組沒有基于索引，此時需要使用文件在內(nèi)存中進行排序，因為使用索引排序的性能好于使用文件排序，所以出現(xiàn)這種情況可以考慮通過添加索引進行優(yōu)化
• Using index：使用了覆蓋索引進行查詢，此時不需要訪問表，從索引中就可以獲取到所需的全部數(shù)據(jù)
• Using index condition：查找使用了索引，但是需要回表查詢數(shù)據(jù)
• Using temporary：表示需要使用臨時表來處理查詢，常出現(xiàn)在 GROUP BY 或 ORDER BY 語句中

如何查看Mysql優(yōu)化器優(yōu)化之后的SQL

# 僅在服務(wù)器環(huán)境下或通過Navicat進入命令列界面
explain extended  SELECT * FROM `student` where `name` = 1 and `age` = 1;

# 再執(zhí)行
show warnings;

# 結(jié)果如下：
/* select#1 */ select `mytest`.`student`.`age` AS `age`,`mytest`.`student`.`name` AS `name`,`mytest`.`student`.`year` AS `year` from `mytest`.`student` where ((`mytest`.`student`.`age` = 1) and (`mytest`.`student`.`name` = 1))

image.gif

為什么要做這個事呢？我們知道Mysql有一個最左匹配原則，那么如果我的索引建的是age，name，那我以name，age這樣的順序去查詢能否使用到索引呢？實際上是可以的，就是因為Mysql查詢優(yōu)化器可以幫助我們自動對SQL的執(zhí)行順序等進行優(yōu)化，以選取代價最低的方式進行查詢（注意是代價最低，不是時間最短）

SQL優(yōu)化

超大分頁場景解決方案

如表中數(shù)據(jù)需要進行深度分頁，如何提高效率？在阿里出品的Java編程規(guī)范中寫道：

利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁場景

說明：MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后返回放棄前 offset 行，返回 N 行，那當 offset 特別大的時候，效率就非常的低下，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進行 SQL 改寫

# 反例（耗時129.570s）
select * from task_result LIMIT 20000000, 10;

# 正例（耗時5.114s）
SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id;

# 說明
task_result表為生產(chǎn)環(huán)境的一個表，總數(shù)據(jù)量為3400萬，id為主鍵，偏移量達到2000萬

image.gif

獲取一條數(shù)據(jù)時的Limit 1

如果數(shù)據(jù)表的情況已知，某個業(yè)務(wù)需要獲取符合某個Where條件下的一條數(shù)據(jù)，注意使用Limit

說明：在很多情況下我們已知數(shù)據(jù)僅存在一條，此時我們應(yīng)該告知數(shù)據(jù)庫只用查一條，否則將會轉(zhuǎn)化為全表掃描

# 反例（耗時2424.612s）
select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48';

# 正例（耗時1.036s）
select * from task_result where unique_key = 'ebbf420b65d95573db7669f21fa3be3e_861414030800727_48' LIMIT 1;

# 說明
task_result表為生產(chǎn)環(huán)境的一個表，總數(shù)據(jù)量為3400萬，where條件非索引字段，數(shù)據(jù)所在行為第19486條記錄

image.gif

批量插入

# 反例
INSERT into person(name,age) values('A',24)
INSERT into person(name,age) values('B',24)
INSERT into person(name,age) values('C',24)

# 正例
INSERT into person(name,age) values('A',24),('B',24),('C',24);

# 說明
比較常規(guī)，就不多做說明了

image.gif

like語句的優(yōu)化

like語句一般業(yè)務(wù)要求都是 '%關(guān)鍵字%'這種形式，但是依然要思考能否考慮使用右模糊的方式去替代產(chǎn)品的要求，其中阿里的編碼規(guī)范提到：

頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決

# 反例（耗時78.843s）
EXPLAIN select * from task_result where taskid LIKE '%tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1;

# 正例（耗時0.986s）
select * from task_result where taskid LIKE 'tt600e6b601677b5cbfe516a013b8e46%' LIMIT 1

##########################################################################
# 對正例的Explain
1   SIMPLE  task_result     range   adapt_id    adapt_id    98      99  100.00  Using index condition

# 對反例的Explain
1   SIMPLE  task_result     ALL                                     33628554    11.11   Using where

# 說明
task_result表為生產(chǎn)環(huán)境的一個表，總數(shù)據(jù)量為3400萬，taskid是一個普通索引列，可見%%這種匹配方式完全無法使用索引，從而進行全表掃描導致效率極低，而正例通過索引查找數(shù)據(jù)只需要掃描99條數(shù)據(jù)即可

image.gif

避免SQL中對where字段進行函數(shù)轉(zhuǎn)換或表達式計算

# 反例
select * from task_result where id + 1 = 15551;

# 正例
select * from task_result where id = 15550;

##########################################################################
# 對正例的Explain
1   SIMPLE  task_result     const   PRIMARY PRIMARY 8   const   1   100.00  

# 對反例的Explain
1   SIMPLE  task_result     ALL                                 33631512  100.00    Using where

# 說明
其實在知道了有SQL優(yōu)化器之后，我個人感覺這種普通的表達式轉(zhuǎn)換應(yīng)該可以提前進行處理再進行查詢，這樣一來就可以用到索引了，但是問題又來了，如果mysql優(yōu)化器可以提前計算出結(jié)果，那么寫sql語句的人也一定可以提前計算出結(jié)果，所以矛盾點在這個地方，導致5.7版本以前的此種情況都無法使用索引吧，未來可能會對其進行優(yōu)化

image.gif

使用 ISNULL()來判斷是否為 NULL 值

說明：NULL 與任何值的直接比較都為 NULL

# 1） NULL<>NULL 的返回結(jié)果是 NULL，而不是 false。 
# 2） NULL=NULL 的返回結(jié)果是 NULL，而不是 true。 
# 3） NULL<>1 的返回結(jié)果是 NULL，而不是 true。

image.gif

多表查詢

我所在的公司基本禁止了多表查詢，那如果必須使用到的話，我們可以一起參考一下阿里的編碼規(guī)范

Eg：超過三個表禁止 join。需要 join 的字段，數(shù)據(jù)類型必須絕對一致；多表關(guān)聯(lián)查詢時，保證被關(guān)聯(lián)的字段需要有索引

明明有索引為什么還走全表掃描

之前回答一些面試問題的時候，對某一個點的理解出現(xiàn)了偏差，即我認為只要查詢的列有索引則一定會使用索引去Push數(shù)據(jù)

然而實際上不僅僅是這樣，真正應(yīng)該是：針對查詢的數(shù)據(jù)行占總數(shù)據(jù)量過多時會轉(zhuǎn)化成全表查詢

那么這個過多指代的是多少呢？

我的測試結(jié)果是50%，但個人認為MySQL優(yōu)化器不會完全糾結(jié)于行數(shù)區(qū)分是否全表，而是有很多其他因素綜合考慮發(fā)現(xiàn)全表掃描的效率更高等等，所以充分認識到該問題即可

count(*) 還是 count(id)

阿里的Java編碼規(guī)范中有以下內(nèi)容：

【強制】不要使用 count(列名) 或 count(常量) 來替代 count(*)

count(*) 是 SQL92 定義的標準統(tǒng)計行數(shù)的語法，跟數(shù)據(jù)庫無關(guān)，跟 NULL 和非 NULL 無關(guān)。

說明：count(*)會統(tǒng)計值為 NULL 的行，而 count(列名)不會統(tǒng)計此列為 NULL 值的行

字段類型不同導致索引失效

阿里的Java編碼規(guī)范中有以下內(nèi)容：

【推薦】防止因字段類型不同造成的隱式轉(zhuǎn)換，導致索引失效

實際上數(shù)據(jù)庫在查詢的時候會作一層隱式的轉(zhuǎn)換，比如 varchar 類型字段通過 數(shù)字去查詢

# 正例
EXPLAIN SELECT * FROM `user_coll` where pid = '1';
type：ref
ref：const   
rows:1  
Extra:Using index condition

# 反例
EXPLAIN SELECT * FROM `user_coll` where pid = 1;
type：index
ref：NULL    
rows:3(總記錄數(shù))
Extra:Using where; Using index

# 說明
pid字段有相應(yīng)索引，且格式為varchar 

image.gif