索引相關
型別隱式轉換 大坑
**欄位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,無法使用索引,所以專門用作排除性查詢的,不建議建立索引。