前言

sql陳述句性能達不到你的要求，執行效率讓你忍無可忍，一般會時下面幾種情況，

• 網速不給力，不穩定，
• 服務器記憶體不夠，或者SQL 被分配的記憶體不夠，
• sql陳述句設計不合理
• 沒有相應的索引，索引不合理
• 沒有有效的索引視圖
• 表資料過大沒有有效的磁區設計
• 資料庫設計太差，存在大量的資料冗余
• 索引列上缺少相應的統計資訊，或者統計資訊過期
• …

本片文章主要介紹的是如何sql優化方法跟技巧，

使用explain 分析你SQL的計劃

日常開發寫SQL的時候建議用explain分析一下自己書寫的SQL陳述句，尤其是走不走索引這一塊，使用 Explain 關鍵字可以模擬優化器執行SQL查詢陳述句，從而知道 MySQL 是如何處理你的 SQL 陳述句的，分析你的查詢陳述句或是表結構的性能瓶頸，

（1）語法：Explain + SQL陳述句
（2）執行計劃包含的資訊（如果有磁區表的話還會有partitions）

• id（select 查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序），

  • id相同，執行順序從上往下
  • id全不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行
  • id部分相同，執行順序是先按照數字大的先執行，然后數字相同的按照從上往下的順序執行

• select_type（查詢型別，用于區別普通查詢、聯合查詢、子查詢等復雜查詢）

  • SIMPLE ：簡單的select查詢，查詢中不包含子查詢或UNION
  • PRIMARY：查詢中若包含任何復雜的子部分，最外層查詢被標記為PRIMARY
  • SUBQUERY：在select或where串列中包含了子查詢
  • DERIVED：在from串列中包含的子查詢被標記為DERIVED，MySQL會遞回執行這些子查詢，把結果放在臨時表里
  • UNION：若第二個select出現在UNION之后，則被標記為UNION，若UNION包含在from子句的子查詢中，外層 select將被標記為DERIVED
  • UNION RESULT：從UNION表獲取結果的select

• table（顯示這一行的資料是關于哪張表的）

• type（顯示查詢使用了那種型別，從最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

  • system：表只有一行記錄（等于系統表），是 const 型別的特例，平時不會出現
  • const：表示通過索引一次就找到了，const 用于比較 primary key 或 unique 索引，因為只要匹配一行資料，所以很快，如將主鍵置于 where 串列中，mysql 就能將該查詢轉換為一個常量
  • eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描
  • ref：非唯一性索引掃描，范圍匹配某個單獨值得所有行，本質上也是一種索引訪問，他回傳所有匹配某個單獨值的行，然而，它可能也會找到多個符合條件的行，多以他應該屬于查找和掃描的混合體
  • range：只檢索給定范圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引，一般就是在你的where陳述句中出現了between、<、>、in等的查詢，這種范圍掃描索引比全表掃描要好，因為它只需開始于索引的某一點，而結束于另一點，不用掃描全部索引
  • index：Full Index Scan，index于ALL區別為index型別只遍歷索引樹，通常比ALL快，因為索引檔案通常比資料檔案小，（也就是說雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬碟中讀的）
  • ALL：Full Table Scan，將遍歷全表找到匹配的行

一般來說，得保證查詢至少達到range級別，最好到達ref

• possible_keys（顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的欄位若存在索引，則該索引將被列出，但不一定被查詢實際使用）

• key

  • 實際使用的索引，如果為NULL，則沒有使用索引

  • 查詢中若使用了覆寫索引，則該索引和查詢的 select 欄位重疊，僅出現在key串列中

• key_len

  • 表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度，在不損失精確性的情況下，長度越短越好
  • key_len顯示的值為索引欄位的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的

• ref（顯示索引的哪一列被使用了，如果可能的話，是一個常數，哪些列或常量被用于查找索引列上的值）

• rows（根據表統計資訊及索引選用情況，大致估算找到所需的記錄所需要讀取的行數）

• Extra（包含不適合在其他列中顯示但十分重要的額外資訊）

  • using filesort: 說明mysql會對資料使用一個外部的索引排序，不是按照表內的索引順序進行讀取，mysql中無法利用索引完成的排序操作稱為“檔案排序”，常見于order by和group by陳述句中

  • Using temporary：使用了臨時表保存中間結果，mysql在對查詢結果排序時使用臨時表，常見于排序order by和分組查詢group by，

  • using index：表示相應的select操作中使用了覆寫索引，避免訪問了表的資料行，效率不錯，如果同時出現using where，表明索引被用來執行索引鍵值的查找；否則索引被用來讀取資料而非執行查找操作

  • using where：使用了where過濾

  • using join buffer：使用了連接快取

  • impossible where：where子句的值總是false，不能用來獲取任何元祖

  • select tables optimized away：在沒有group by子句的情況下，基于索引優化操作或對于MyISAM存盤引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化

  • distinct：優化distinct操作，在找到第一匹配的元祖后即停止找同樣值的動作

舉例子：

• 第一行（執行順序4）：id列為1，表示是union里的第一個select，select_type列的primary表示該查詢為外層查詢，table列被標記為，表示查詢結果來自一個衍生表，其中derived3中3代表該查詢衍生自第三個select查詢，即id為3的select，【select d1.name…】

• 第二行（執行順序2）：id為3，是整個查詢中第三個select的一部分，因查詢包含在from中，所以為derived，【select id,name from t1 where other_column=’’】

• 第三行（執行順序3）：select串列中的子查詢select_type為subquery，為整個查詢中的第二個select，【select id from t3】

• 第四行（執行順序1）：select_type為union，說明第四個select是union里的第二個select，最先執行【select name,id from t2】

• 第五行（執行順序5）：代表從union的臨時表中讀取行的階段，table列的<union1,4>表示用第一個和第四個select的結果進行union操作，【兩個結果union操作】

關鍵字的慎用

避免判斷 null 值

應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引從而進行全表掃描，如：select id from t where num is null
可以在 num 上設定默認值 0，確保表中 num 列沒有 null 值，然后這樣查詢：
select id from t where num=0

避免使用 or 邏輯

應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10
union all
select id from t where num=20

mysql是有優化器的，處于效率與成本考慮，遇到or條件，索引可能失效，看起來也合情合理，

慎用 in 和 not in 邏輯

in和 not in也要慎用，否則會導致全表掃描，如：

select id from t1 where num in(select id from t2 where id > 10)

此時外層查詢會全表掃描，不使用索引，可以修改為：

select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id

此時索引被使用，可以明顯提升查詢效率，

Inner join 、left join、right join，優先使用Inner join，如果是left join，左邊表結果盡量小

• Inner join 內連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結果集
• left join在兩張表進行連接查詢時，會回傳左表所有的行，即使在右表中沒有匹配的記錄，
• right join在兩張表進行連接查詢時，會回傳右表所有的行，即使在左表中沒有匹配的記錄，

都滿足SQL需求的前提下，推薦優先使用Inner join（內連接），如果要使用left join，左邊表資料結果盡量小，如果有條件的盡量放到左邊處理，

原因：

• 如果inner join是等值連接，或許回傳的行數比較少，所以性能相對會好一點，
• 同理，使用了左連接，左邊表資料結果盡量小，條件盡量放到左邊處理，意味著回傳的行數可能比較少，

exists的合理使用

很多時候用exists代替in是一個好的選擇：

select num from a where num in(select num from b)

用下面的陳述句替換：

select num from a where exists(select 1 from b where num=a.num)

慎用distinct關鍵字

distinct關鍵字一般用來過濾重復記錄，以回傳不重復的記錄，在查詢一個欄位或者很少欄位的情況下使用時，給查詢帶來優化效果，但是在欄位很多的時候使用，卻會大大降低查詢效率，

• 反例：
  SELECT DISTINCT * from user;

• 正例：
  select DISTINCT name from user;

• 理由：
  帶distinct的陳述句cpu時間和占用時間都高于不帶distinct的陳述句，因為當查詢很多欄位時，如果使用distinct，資料庫引擎就會對資料進行比較，過濾掉重復資料，然而這個比較，過濾的程序會占用系統資源，cpu時間，

盡量用 union all 替換 union

如果檢索結果中不會有重復的記錄，推薦union all 替換 union，

理由：如果使用union，不管檢索結果有沒有重復，都會嘗試進行合并，然后在輸出最終結果前進行排序，如果已知檢索結果沒有重復記錄，使用union all 代替union，這樣會提高效率，

查詢優化

GROUP BY關鍵字優化

• group by實質是先排序后進行分組，遵照索引建的最佳左前綴
• 當無法使用索引列，增大max_length_for_sort_data 引數的設定，增大sort_buffer_size引數的設定
• where高于having，能寫在where限定的條件就不要去having限定了

查詢SQL盡量不要使用select *，而是select具體欄位

任何地方都不要使用 select * from t，用具體的欄位串列代替“*”，不要回傳用不到的任何欄位，
優點：

• 只取需要的欄位，節省資源、減少網路開銷，
• select *進行查詢時，很可能就不會使用到覆寫索引了，就會造成回表查詢，

優化limit分頁

我們日常做分頁需求時，一般會用 limit 實作，但是當偏移量特別大的時候，查詢效率就變得低下，
因此我們有以下優化方案：

• 回傳上次查詢的最大記錄(偏移量)
  當偏移量最大的時候，查詢效率就會越低，因為Mysql并非是跳過偏移量直接去取后面的資料，而是先把偏移量+要取的條數，然后再把前面偏移量這一段的資料拋棄掉再回傳的，回傳上次最大查詢記錄（偏移量），這樣可以跳過偏移量，效率提升不少，

• 使用order by + 索引
  使用order by+索引，也是可以提高查詢效率的，

• 在業務允許的情況下限制頁數
  建議跟業務討論，有沒有必要查這么后的分頁啦，因為絕大多數用戶都不會往后翻太多頁，

知道查詢結果為一條記錄，建議使用limit 1

• 如果知道查詢結果只有一潭訓者只要最大/最小一條記錄，建議用limit 1，當加上limit 1后，只要找到了對應的一條記錄,就不會繼續向下掃描了,效率將會大大提高，
• 當然，如果name是唯一索引的話，是不必要加上limit 1了，因為limit的存在主要就是為了防止全表掃描，從而提高性能,如果一個陳述句本身可以預知不用全表掃描，有沒有limit ，性能的差別并不大，

注意模糊查詢

如果用到模糊關鍵字查詢，很容易想到like，但是like很可能讓你的索引失效從而導致全表掃描，如下所示：

select id from t where name like '%abc%'

模糊查詢如果是必要條件時，可以使用 select id from t where name like 'abc%'來實作模糊查詢，此時索引將被使用，如果頭匹配是必要邏輯，建議使用全文搜索引擎（Elasticsearch、Lucene、Solr 等），

• 把%放前面，并不走索引，把% 放關鍵字后面，還是會走索引的，

避免查詢條件中欄位計算

應盡量避免在 where 子句中對欄位進行運算式操作，這將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

避免查詢條件中對欄位進行函式操作

應盡量避免在 where 子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描，
如：

select id from t where substring(name,1,3)='abc'  --name 以 abc 開頭的 id

應改為:

select id from t where name like 'abc%'

原因：需要什么資料，就去查什么資料，避免回傳不必要的資料，節省開銷

避免不等值判斷

應盡量避免在 where 子句中使用!=或<>運算子，否則將引擎放棄使用索引而進行全表掃描，
例如：

select age,name  from user where age !=18;

以上使用!=或者<>很可能會導致索引失效從而進行全表掃描，所以我們應該使用下面的方式：

//可以考慮分開兩條sql寫
select age,name  from user where age <18;
select age,name  from user where age >18;

對查詢進行優化，應考慮在 where 及 order by 涉及的列上建立索引，盡量避免全表掃描，

where子句中考慮使用默認值代替null，

• 并不是說使用了is null或者is not null就會不走索引了，這個跟mysql版本以及查詢成本都有關，
• 如果把null值，換成默認值，很多時候讓走索引成為可能，同時，表達意思會相對清晰一點，

where子句 “= ” 左邊注意點

不要在where 子句中的“=”左邊進行函式、算術運算或其他運算式運算，否則系統將可能無法正確使用索引，

不要定義無意義的查詢

不要寫一些沒有意義的查詢，如需要生成一個空表結構：

select col1,col2 into #t from t where 1=0

這類代碼不會回傳任何結果集，但是會消耗系統資源的，應改成這樣：

create table #t(...)

索引優化

在適當的時候，使用覆寫索引，

覆寫索引能夠使得你的SQL陳述句不需要回表，僅僅訪問索引就能夠得到所有需要的資料，大大提高了查詢效率，

索引無關優化

• 不使用*、盡量不使用 union，union all等關鍵字、盡量不使用 or關鍵字、盡量使用等值判斷，

• 表連接建議不超過 5 個，如果超過 5 個，則考慮表格的設計，（互聯網應用中）

• 表連接方式使用外聯優于行內，

• 外連接有基礎資料存在，如：A left join B,基礎資料是 A，

• A inner join B，沒有基礎資料的，先使用笛卡爾積完成全連接，在根據連接條件得到內連接結果集，

• 大資料量級的表格做分頁查詢時，如果頁碼數量過大，則使用子查詢配合完成分頁邏輯，

Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)

索引也可能失效

并不是所有索引對查詢都有效，SQL 是根據表中資料來進行查詢優化的，當索引列有大量資料重復時，SQL 查詢可能不會去利用索引，如一表中有欄位 sex，male、female 幾乎各一半，那么即使在 sex 上建了索引也對查詢效率起不了作用，

組合索引使用

在使用索引欄位作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓欄位順序與索引順序相一致，

索引優化總結

• 全值匹配牛逼
• 最佳左前綴法則，比如建立了一個聯合索引(a,b,c)，那么其實我們可利用的索引就有(a), (a,b), (a,b,c)
• 不在索引列上做任何操作（計算、函式、(自動or手動)型別轉換），會導致索引失效而轉向全表掃描
• 存盤引擎不能使用索引中范圍條件右邊的列
• 盡量使用覆寫索引(只訪問索引的查詢(索引列和查詢列一致))，減少select
• is null ,is not null 也無法使用索引
• like “xxxx%” 是可以用到索引的，like “%xxxx” 則不行(like “%xxx%” 同理)，like以通配符開頭(’%abc…’)索引失效會變成全表掃描的操作，
• 字串不加單引號索引失效
• 少用or，用它來連接時會索引失效
• <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 則不行，會導致全表掃描

其它注意事項

表格欄位型別選擇

盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存盤開銷，這是因為引擎在處理查詢和連接時會逐個比較字串中每一個字符，而對于數字型而言只需要比較一次就夠了，

盡可能的使用 varchar 代替char，因為首先可變長度欄位存盤空間小，可以節省存盤空間，其次對于查詢來說，在一個相對較小的欄位內搜索效率顯然要高些，

如果插入資料過多，考慮批量插入

當我們插入資料過多時，一次性插入會嚴重影響性能，同時會造成卡頓，浪費時間，因此建議分批次插入資料，
反例：

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入，分批進行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

count(*) 和 count(1)和count(列名)區別

從執行效果上：

• count(*)包括了所有的列，相當于行數，在統計結果的時候，不會忽略列值為NULL
• count(1)包括了所有列，用1代表代碼行，在統計結果的時候，不會忽略列值為NULL
• count(列名)只包括列名那一列，在統計結果的時候，會忽略列值為空（這里的空不是只空字串或者0，而是表示null）的計數，即某個欄位值為NULL時，不統計，

執行效率上：

• 列名為主鍵，count(列名)會比count(1)快
• 列名不為主鍵，count(1)會比count(列名)快
• 如果表多個列并且沒有主鍵，則 count(1) 的執行效率優于 count(*)
• 如果有主鍵，則select count（主鍵）的執行效率是最優的
• 如果表只有一個欄位，則select count(*)最優，

有一個問題經常問：count(*)會造成全表掃描嗎？

大家可以看以下這篇文章：【https://zhuanlan.zhihu.com/p/149874583?from_voters_page=true】
說明得很細，

參考：
https://juejin.im/post/6850037271233331208#heading-65
https://www.jianshu.com/p/074f3eafcadf