MySQL查詢最佳化方案彙總(索引相關)

小松聊PHP进阶發表於2024-03-08

索引相關

型別隱式轉換 大坑

**欄位filed1是varchar型別,且加了索引,如果 where filed1 = 123; type 可能是all,因為123是數字型別,mysql內部會用函式做隱式轉換,用了函式,索引就失效了。**

大資料深度分頁,用主鍵

select field1,field2 from table limit 100000,10;
select field1,field2 from table where id > 100000 limit 10;

避免使用MySQL函式

MySQL內建了很多函式,使用函式可能導致索引失效,儘量讓MySQL只做簡單的增刪改查。

避免型別的隱式轉換

varchar等字串型別的欄位被加了索引,把這欄位當做where條件,及時目標值是數字,也要加引號,否則型別的隱式轉換,會引起索引失效的問題。

避免使用函式或表示式,儘量只讓資料庫做純粹的增刪改查。

用函式的前提是知道初始值,然後在運算元據,很多函式都是要傳參的,所以mysql只能全表查,然後每次迭代將資料丟給函式處理。
表示式也是如此,例如where number + 1 = 10,都會讓索引失效。

避免使用不等值做排除法

避免使用<>、!=、not in、is not null、這些都會使索引失效。

避免使用null值

避免欄位使用null值,一是影響索引(mysql建索引建的是非null的值,大量的null值影響了正常的B+tree結構),而且造成聚合函式統計(如count(該欄位))不準確的問題。

索引無關

減少大欄位查詢,避免使用*,不說磁碟io的損耗,連網路頻寬都跟著損耗。
如果只select僅需欄位,可能會觸發覆蓋索引機制,不用回表,提高效能。

select * from table;
select field1,field2 from table;

查詢是否存在

select count(*) from table where...;
select field from table where ... limit 1;

冗餘最佳化

想要查詢一篇文章的瀏覽量,不用count(瀏覽記錄)。
新建一個在文章表中建立一個瀏覽量的欄位,這使得查詢的時間複雜度從O(n)變成O(1);

避免join,適用於大表關聯小表。

如果想要join的兩個表,一個很大,一個很小,應儘量避免join。
可將小表資料全部取出來組裝成陣列,放入程式語言的記憶體,用程式語言的記憶體匹配的方式去關聯。

哪些場景下索引會失效

  • 使用not in、is not null、<>、!=、這種排除法時會導致索引失效,覆蓋索引除外。
  • 最左匹配原則,左邊的欄位缺少時會出現,覆蓋索引除外。
  • 最左匹配原則,左邊的欄位有區間查詢,導致右邊的欄位無法使用索引。
  • like左邊或兩邊加百分號。
  • 型別的隱式轉換,如varchar的欄位,使用where varchar_field = 123,包括join表,用on連線的欄位。
  • where條件有函式,或表示式。
  • where語句包含or,or中存在非索引列。
  • 大資料量對二級索引欄位排序,如果select * 或者其它欄位,這個過程涉及回表,可能無法使用索引,因為資料量大,走索引的每條資料都需要回表,代價會很大。
  • order by欄位,如果排序與索引順序不一致,則可能導致索引失效,如果order by的每個欄位,都按照索引的順序,或者反順序,則仍舊會走索引。

那些查詢適合建立索引?

  • 需要唯一性約束兜底的欄位。
  • 經常被查詢或者作為where條件的欄位,=、>、<、<=、>=、in、between、like 右百分號。
  • 經常group by或者order by的欄位。
  • delete或update被作為where條件的欄位。
  • distinct的欄位。
  • join on的連線欄位需要加索引,但是需要型別一致,因為MySQL內部有用函式做隱式轉換,用了函式就不適用索引。
  • 區分度(不重複度)高的欄位。
  • 把搜尋最頻繁的列,放在聯合索引的左側,(受聯合索引的最左原則影響)。

那些查詢不適合建立索引?

  • 資料量小,一個表,例如配置表,總類別表,可能最多幾十條記錄,建立不建立區別不大。
  • 寫多讀少,資料的寫操作對索引欄位的開銷比沒有索引要大,而且讀操作還少。
  • 區分度低的欄位,例如性別狀態等,這會導致線性查詢,能提升搜尋效率,但是不明顯,可加可不加。
  • sql語句包含<>、!=、not in、is not null,無法使用索引,所以專門用作排除性查詢的,不建議建立索引。

相關文章