MySQL查詢優化

那個蘿蔔在飄發表於2021-03-04
MySql優化

查詢優化本就不是一蹴而就的，需要學會使用對應的工具、借鑑別人的經驗來對 SQL 進行優化，並且提升自己。

先來鞏固一下索引的優點，檢索資料快、查詢穩定、儲存具有順序性避免伺服器建立臨時表、將隨機的 I/O 變為有序的 I/O。

但索引一旦建立的不規範就會造成以下問題，佔用額外空間，浪費記憶體，降低資料的增、刪、改效能。

所以只有在理解索引資料結構的基礎上才能建立出高效的索引。本文所有操作均在 MySQL 8.0.12。

建立索引規範

在學習索引優化之前，需要對建立索引的規範有一定的瞭解，此規範來自於阿里巴巴開發手冊。

主鍵索引：pk_column_column。

唯一索引：uk_column_column。

普通索引：idx_column_column。

索引失效原因

建立索引需知道在什麼情況下索引會失效，只有瞭解索引失效的原因，在建立索引時才不會出現一些已知錯誤。

帶頭大哥不能死,這局經典的語句就是涵蓋建立索引時一定要符合最左側原則。

例如表結構為 u_id，u_name，u_age，u_sex，u_phone，u_time，建立索引為 idx_user_name_age_sex。

查詢條件必須帶上 u_name 這一列。

不在索引列上做任何操作

不在索引列上做任何計算、函式、自動或者手動的型別轉換，否則會進行全表掃描。簡而言之不要在索引列上做任何操作。

倆邊型別不等

例如建立了索引 idx_user_name，name 欄位型別為 varchar。在查詢時使用 where name = kaka，這樣的查詢方式會直接造成索引失效。

正確的用法為 where name = “kaka”。

不適當的 like 查詢會導致索引失效

建立索引為 idx_user_name，執行語句為 select * from user where name like 'kaka%'；可以命中索引。

執行語句為 select name from user where name like '%kaka'；可以使用到索引（僅在 8.0 以上版本）。

執行語句為 select * from user where name like '%kaka'；會直接導致索引失效。

範圍條件之後的索引會失效

建立索引為：
idx_user_name_age_sex

執行語句：
select * from user where name = ‘kaka’ and age > 11 and sex = 1;

上面這條 SQL 語句只會命中 name 和 age 索引，sex 索引會失效。複合索引失效需要檢視 key_len 的長度即可。

  • 總結：% 在後邊會命令索引，當使用了覆蓋索引時任何查詢方式都可命中索引。

以上就是我關於索引失效會出現的原因總結，在很多文章中沒有標註 MySQL 版本，所以你有可能會看到 is null 、or 索引會失效的結論。

SQL 優化殺手鐗之 Explain

在寫完 SQL 語句之後必須要做的一件事情就是使用 Explain 進行 SQL 語句檢測，看是否命中索引。

上圖就是使用 Explain 輸出格式，接下來將會對輸出格式進行簡單的解釋：

①id：這列就是查詢的編號，如果查詢語句中沒有子查詢或者聯合查詢這個標識就一直是 1。如存在子查詢或者聯合查詢這個編號會自增。

②select_type：最常見的型別就是 SIMPLE 和 PRIMARY，此列知道就行了。

③table：理解為表名即可。

④**type：此列是在優化 SQL 語句時最需要關注的列之一，此列顯示了查詢使用了何種型別。

以下排序從最優到最差：

  • system：表內只有一行資料。
  • const：最多隻會有一條記錄匹配，常用於主鍵或者唯一索引為條件查詢。
  • eq_ref：當連線使用的索引為主鍵和唯一時會出現。
  • ref：使用普通索引 = 或 <=> 運算子進行比較將會出現。
  • fulltext：使用全文索引。
  • ref_or_null：跟 ref 型別類似，只是增加了 null 值的判斷，實際用的不多。語句為 where name = ‘kaka’ and name is null，name 為普通索引。
  • index_merge：查詢語句使用了倆個以上的索引，常見在使用 and、or 會出現，官方文件將此型別放在 ref_or_null 之後，但是在很多的情況下由於讀取索引過多效能有可能還不如 range。
  • unique_subquery：用於 where 中的 in 查詢，完全替換子查詢，效率更高。語句為 value IN (SELECT primary_key FROM single_table WHERE some_expr)
  • index_subquery：子查詢中的返回結果欄位組合是一個索引（或索引組合），但不是一個主鍵或唯一索引。
  • range：索引範圍查詢，常見於使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等運算子的查詢中。
  • index：索引全表掃描，把索引從頭到尾掃一遍。
  • all：全表掃描，效能最差。

⑤possible_keys：此列顯示的可能會使用到的索引。

⑥**key：優化器從 possible_keys 中命中的索引。

⑦key_len：查詢用到的索引長度（位元組數），key_len 只計算 where 條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到 key_len 中。

⑧ref：如果是使用的常數等值查詢，這裡會顯示 const。

如果是連線查詢，被驅動表的執行計劃這裡會顯示驅動表的關聯欄位。如果是條件使用了表示式或者函式，或者條件列發生了內部隱式轉換，這裡可能顯示為 func。

⑨**rows：這是 MySQL 估算的需要掃描的行數（不是精確值）。這個值非常直觀顯示 SQL 的效率好壞, 原則上 rows 越少越好。

⑩filtered：此列表示儲存引擎返回的資料在 server 層過濾後，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。

⑪**extra：在大多數情況下會出現以下幾種情況。

  • Using index：使用了覆蓋索引，查詢列都為索引欄位。
  • Using where：使用了 where 語句。
  • Using temporary：查詢結果進行排序的時候使用了一張臨時表。
  • Using filesort：對資料使用一個外部的索引排序。
  • Using index condition：使用了索引下推。

關於索引下推可以檢視我之前的一篇文章《MySQL 索引》。

總結：以上就是關於 Explain 所有列的說明，在平時開發的過程中，一般只會關注 type、key、rows、extra 這四列。

type 優化目標至少達到 range 級別，要求是 ref 級別，如果可以 consts 最好。key 是查詢使用到的索引，如果此列為空，要麼未建立索引，要麼索引失效。

rows 是這條 SQL 語句掃描的行數，越少越好。extra：此列為擴充套件列，如果出現臨時表、檔案排序則需要優化。

SQL 優化殺手鐗之慢查詢

上文說到了可以直接使用 Explain 來分析自己的 SQL 語句是否合理，接下來再聊一個點那就是慢查詢。

檢視慢查詢是否開啟：

檢視是否記錄沒有使用索引的 SQL 語句：

開啟慢查詢、開啟記錄沒有使用到索引的 SQL 語句：

set global log_queries_not_using_idnexes=‘on’;
set global log_queries_not_using_indexes=‘on’;


查詢以上倆個配置是否開啟：

設定慢查詢時間，這個時間由自己把控，一般 1s 即可：

set globle long_query_time=1;

如果檢視這個時間沒有變，則關於客戶端在重新連線一次即可。

檢視慢查詢儲存位置：

然後隨便執行一條不執行索引的語句即可在這個日誌中檢視到此語句：

上圖中一般需要主要觀察的是 Query_time、SQL 語句內容。以上就是關於如何使用慢查詢來檢視專案中出現問題的 SQL 語句。

優化大法

此處跟大家聊一些常用的 SQL 語句優化方案，以上的倆個工具要好好的利用，輔助我們進行打怪：

  • ①禁止使用 select *，需要什麼欄位查詢什麼欄位。

  • ②where 欄位設定索引。

  • ③group by、order by 欄位設定索引。

  • ④捨棄 offset，limit 分頁，使用延遲關聯來實現分頁（資料量不大時可不用）。

  • ⑤寫分頁時當 count 為 0 時，直接返回避免執行分頁語句。

  • ⑥利用覆蓋索引進行查詢避免回表。

  • ⑦建立複合索引時區分度最高的放在最左側。

  • ⑧統計資料行數只用 count(*)，別整的花裡胡哨的。

  • ⑨關於 in 和 exist，如果查詢的倆個表大小一致則效能差別可忽略，如果子查詢表大用 exist，否則使用 in。

  • ⑩查詢一行資料時加上 limit 1。

  • ⑪選擇合理的資料型別，在滿足條件下資料型別越小越好。

  • ⑫聯合查詢 join 最多三個表，並且需要 join 的欄位資料型別保持一致。

  • ⑬in 操作能避免儘量避免，無法避免的情況下 in 元素控制在 1000 以內。

  • ⑭資料更新頻繁，區分度不高的列不適合建立索引。

  • ⑮explain 中的 type 至少要達到 range，要求為 ref。

  • ⑯聯合索引滿足最左側原則。

相關文章