1. 當使用索引列進行查詢的時候儘量不要使用表示式，把計算放到業務層而不是資料庫層
如下圖 兩個sql的結果是一樣的，但是兩個sql的執行計劃是不一樣，在type中index的效率遠不如const where條件中 actor_id+4 表示式影響了執行計劃
2. 儘量使用主鍵查詢，而不是其他索引，主鍵查詢不會出現回表查詢。
我們所有的表基本都會有主鍵的，所以平時開發中能用索引就用索引，能用主鍵索引就用主鍵索引。
3. 使用字首索引
很多時候我們的索引其實都是字串，不可避免會出現長字串，就會導致索引佔用過大，降低其效率。尤其是對於blob,text, varchar這樣的長列。這時候處理方式就是不使用欄位的全值作為索引，而是隻取其前半部分即可（選擇的這部分字首索引的選擇性接近於整個列）。這樣可以大大減少索引空間，從而提高效率，壞處就是降低了索引的選擇性。
索引選擇性：不重複的索引值和資料表記錄總數的比值(#T),範圍從1/#T到1之間。索引的選擇性越高查詢效率也高，因為資料的區分度很高，可以過濾掉更多的行。唯一性索引的選擇性是1，其效能也最好。
例如公司的員工表中郵箱欄位，一個公司的郵箱字尾都是一樣的如xxxx@qq.com, 其實用郵箱作為索引有效的就xxxx部分，因為@qq.com都是一樣的，對索引是無意義的，明顯只用xxxx作為索引，其選擇性和整個值的是一樣的，但是xxxx作為索引明顯就會減少索引空間。
下面我們已employee表為例子(表結構和資料看文末)
我們以email欄位建立索引為例：
這個資料的郵箱其實是手機號+@qq.com為例的，其實前11位後面都是相同的。我用下面的sql來看看這些資料的選擇性（分別取前10，11，12）位計算。
-- 當是11個字首的時候選擇性是1，在增加欄位長度，選擇性也不會變化select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11, count(distinctleft(email,12))/count(*) as e12 from employee;
複製程式碼
從上圖我們可以看出前10，前11，前12的選擇性分別是0.14，1.0，1.0 ,在第11位的時候索引選擇性是最高的1，就沒必要使用全部作為索引，增加了索引的空間。
-- 建立字首索引
alter table employee add key(email(11));
複製程式碼我們也可以使用count計算頻率來統計（出現的次數越少，說明重複率越低，選擇性越大）
-- 查詢字首出現的頻率
select count(*) as cnt,left(email,11) as pref from employee group by pref order by cnt desc limit 10;
複製程式碼
4.使用索引掃描來排序
我們經常會有排序的需求，使用order by 但是order by是比較影響效能的，它是通過把資料載入到記憶體去排序的，如果資料量很大記憶體放不下，只能分多次處理。但是索引本身就是有序的，直接通過索引完成排序更省事。
掃描索引本身是很快的，因為只需要從一條索引記錄移動到緊接著的下一條記錄，但如果索引不能覆蓋查詢所需的所有列時，就不得不每掃描一條索引記錄就回表查詢一次對應行，這基本都是隨機IO。因此按索引順序讀取資料的速度通常比順序的全表掃描慢。
mysql可以使用同一個索引即滿足排序，又用於查詢行。如果可以的話請考慮建立這種索引。
只有當索引列順序和order by子句的順序完全一致，並且所有列的排序方向(倒敘或者正序)都是一樣的，mysql才能使用索引對結果做排序。如果查詢需要關聯多張表，只有當order by子句的欄位全是第一張表時才能使用索引排序。order by 查詢同時也需要滿足組合索引的最左字首，否則也不能使用索引排序。
其實在開發中主要注意兩點：
- where條件中的欄位和order by中的欄位能夠是組合索引而且滿足最左字首。
- order by中的欄位的順序需要一致，不能存在desc,又存在asc。
5. union all ,in,or都能夠使用索引，但是推薦使用in
如上union all 會有兩次執行，而in 和or只有一次。同時看出or和in的執行計劃是一樣的,
但是我們在看一下他們的執行時間。如下圖使用set profiling=1可以看到詳細時間，使用show profiles 檢視具體時間。下圖看出or的時間0.00612000，in的時間0.00022800，差距還是很大的(測試的表資料只有200行)
union all: 查詢分為了兩階段，其實還有一個union，在平時開發中必須使用到union的時候推薦使用union all，因為union中多出了distinct去重的步驟。所以儘量用union all。
6. 範圍列可以用到索引
範圍的條件：>,>=,<,<=,between
範圍列可以用到索引，但是範圍列後面的列就無法用到索引了(索引最多用於一個範圍列)
比如一個組合索引age+name 如果查詢條件是where age>18 and name="紀"後面的name是用不到的索引的。
曾經面試被問到不等於是否能夠走某個索引，平時沒有注意過也沒有回答成功，這次親自做個實驗，關於結論請看文末。
7. 強制型別轉換會全表掃描
我在employee表中定義了mobile欄位是varchar型別且建立索引，我分別用數字和字串查詢.
看看結果: 兩者type是不一樣的，而且只有字串才用到索引。
如果條件的值的型別和表中定義的不一致，那麼mysql會強制進行型別轉換，但是結果是不會走索引，索引在開發中我們需要根據自己定義的型別輸入對應的型別值。
8. 資料區分度不高，更新頻繁的欄位不宜建立索引
- 索引列更新會變更B+樹的，頻繁更新的會大大降低資料庫效能。
- 類似於性別這類(只有男女，或者未知)，不能有效過濾資料。
- 一般區分度在80%以上就可以建立索引，區分度可以使用count(distinct(列名))/count(*)
9. 建立索引的列不允許為null，可能會得到不符合預期的結果
也就是建立索引的欄位儘量不要為空，可能會有些意想不到的問題，但是實際工作中也不太可能不為空，所以根據實際業務來處理吧，儘量避免這種情況。
10. 當需要進行表連線的時候，最好不要超過三張表
表連線其實就是多張表迴圈巢狀匹配，是比較影響效能的， 而且需要join的欄位資料型別必須一致，提高查詢效率。關於表連線原理後面專門寫一篇吧。
11. 能使用limit的時候儘量使用limit。
limit的作用不是僅僅用了分頁，本質作用是限制輸出。
limit其實是挨個遍歷查詢資料，如果只需要一條資料新增 limit 1的限制，那麼索引指標找到符合條件的資料之後就停止了，不會繼續向下判斷了，直接返回。如果沒有limit，就會繼續判斷。
但是如果分頁取1萬條後的5條limit 10000,10005 就需要慎重了，他會遍歷1萬條之後取出5條，效率很低的。小技巧：如果主鍵是順序的，可以直接通過主鍵獲取分頁資料。
12. 單表索引儘量控制在5個內
建立/維護索引也是需要代價的，也需要佔用空間的。索引並不是越多越好，要合理使用索引。
13. 單個組合索引的欄位個數不宜超過5個
欄位越多，索引就會越大，佔用的儲存空間就越多。
索引並不是越多越好，而且索引並不需要在開始建表的時候就全部設計出來，過早優化反而不會是高效索引，需要在瞭解業務，根據相關業務sql做個統計權衡之後再去構建相關索引，這樣考慮的更周全，建立的索引更有效和高效。
以上就是對應索引優化的小細節，希望能夠幫助你寫出嗖嗖的sql.
作者：紀先生
連結：juejin.cn/post/6997049092505337893
來源：掘金
本作品採用《CC 協議》，轉載必須註明作者和本文連結