知識星球來自：Java識堂（微訊號：erlieStar）
索引的種類
眾所周知，索引類似於字典的目錄，可以提高查詢的效率。
索引從物理上可以分為：聚集索引，非聚集索引
從邏輯上可以分為：普通索引，唯一索引，主鍵索引，聯合索引，全文索引
索引最佳化策略
不要在索引列上進行運算或使用函式
在列上進行運算或使用函式會使索引失效，從而進行全表掃描。如下麵例子在publish_time，id列上分別加上索引，publish_time為datetime型別，id為int型別
-- 全表掃描
select * from article where year(publish_time) 2019
-- 走索引
select * from article where publish_time '2019-01-01'
-- 全表掃描
select * from article where id + 1 = 5
-- 走索引
select * from article where id = 4
小心隱式型別轉換
假設id為varchar型別
-- 全表掃描
select * from article where id = 100
-- 走索引
select * from article where id = '100'
為什麼呢？
select * from article where id = 100
-- 等價於
select * from article where CAST(id AS signed int) = 100
上一條規則說過，不要在索引列上使用函式，隱式型別轉換在索引欄位上做了函式操作，因此會全表掃描
那麼如果id是int，執行下麵這個陳述句是否會導致全表掃描呢？
select * from article where id = '100'
答案是會用到索引
前導模糊查詢不會使用索引
-- 全表掃描
select * from article where author like '%李'
%李,%李%都會導致全表掃描，非前導模糊查詢可以使用索引
-- 走索引
select * from article where author like '李%'
聯合索引最左字首原則
mysql會一直向右匹配直到遇到範圍查詢(>、 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整
1.將區分度最高的欄位放在最左邊
當不需要考慮排序和分組時，將區分度最高的列放在前面通常是很好的。這時候索引的作用只是用於最佳化WHERE條件的查詢
如果在a b列上建立聯合索引，該如何建立，才能使查詢效率最高
select count(distinct a) / count(*), count(distinct b) / count(*), count(*) from table
執行如下陳述句，假設3個輸出依次為0.001,0.373,16049，可以看到b列的選擇性最高，因此將其作為聯合索引的第一列，即建立(b, a)的聯合索引
2.查詢時=可以亂序
如果建立了聯合索引（a, b）。例如下麵的2個寫法是等價的，因為MySQL會將查詢的順序最佳化成和聯合索引的順序一致
select * from table where a = '1' and b = '1'
select * from table where b = '1' and a = '1'
3.最佳化查詢，避免出現filesort
select * from table where a = ? and b = ? order by c
最左字首原則不僅用在查詢中，還能用在排序中。MySQL中，有兩種方式生成有序結果集：
- 透過有序索引順序掃描直接傳回有序資料
- Filesort排序，對傳回的資料進行排序
因為索引的結構是B+樹，索引中的資料是按照一定順序進行排列的，所以在排序查詢中如果能利用索引，就能避免額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using index。
所有不是透過索引直接傳回排序結果的操作都是Filesort排序，也就是說進行了額外的排序操作。EXPLAIN分析查詢時，Extra顯示為Using filesort，當出現Using filesort時對效能損耗較大，所以要儘量避免Using filesort
對於如下sql
select * from table where a = ? and b = ? order by c
可以建立聯合索引（a, b, c）
如果索引中有範圍查詢，那麼索引有序性無法利用，如
select * from table where a > 10 order by b
索引(a，b)無法排序。
放幾個例子
-- 使用了a列
where a = 3
-- 使用了a b列
where a = 3 and b = 5
-- 使用了a b c列
where a = 3 and c = 4 and b = 5
-- 沒有使用索引
where b = 3
-- 使用了a列
where a = 3 and c = 4
-- 使用了a b列
where a = 3 and b > 10 and c = 7
-- 使用了a b 列
where a = 3 and b like 'xx%' and c = 7
union，or，in都能命中索引，建議使用in
select * from article where id = 1
union all
select * from article where id = 2
select * from article where id in (1 , 2)
新版MySQL的or可以命中索引
select * from article where id = 1 or id = 2
效率從高到低為union，in，or。in和union的效率差別可以忽略不計，建議使用in
負向條件索引不會使用索引，建議用in
負向條件有：!=、<>、not in、not exists、not like 等
-- 全表掃描
select * from article where id != 1 and id != 2
知道id的所有取值範圍，可以改為類似如下形式
-- 走索引
select * from article where id in (0, 3, 4)
建立改寫索引
眾所周知，表資料是放在一個聚集索引上的，而建立的索引為非聚集索引，非聚集索引的葉子節點存放索引鍵值，以及該索引鍵指向的主鍵。一般查詢的過程是從非聚集索引上找到資料的主鍵，然後根據該主鍵到聚集索引上查詢記錄，這個過程稱為回表，不清楚的看推薦閱讀。
如有下麵這個sql
select uid, login_time from user where username = ? and passwd = ?
可以建立(username, passwd, login_time)的聯合索引，由於 login_time的值可以直接從索引中拿到，不用再回表查詢，提高了查詢效率
經常更改，區分度不高的列上不宜加索引
更新會變更 B+ 樹，更新頻繁的欄位建立索引會大大降低資料庫效能。
“性別”這種區分度不大的屬性，建立索引是沒有什麼意義的，不能有效過濾資料，效能與全表掃描類似。
一般區分度在80%以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(*) 來計算
明確知道只會傳回一條記錄，可以加limit1
當查詢確定只有一條記錄時，可以加liimit1，讓MySQL停止遊標移動，提高查詢效率
select uid from user where username = ? and passwd = ?
可改為
select uid from user where username = ? and passwd = ? limit 1
對文字建立字首索引
用郵箱登入是一個常見的問題，如果對email整個欄位建立索引，會讓索引變得大且慢
select username from user where email='xxx';
這時我們可以索引開始的部分字元，這樣可以大大節約索引空間，從而提高索引效率，但這樣也會降低索引的區分度。索引的區分度是指，不重覆的索引值和資料表的記錄總數的比值。索引的區分度越高則查詢效率越高，因為區分度高的索引可以讓MySQL在查詢時過濾掉更多的行。
因此我們選擇足夠長的字首保證較高的區分度，同時又不能太長（以便節約空間）
可以進行如下實驗
select count(distinct left(email, 5)) / count(*) as col5,
count(distinct left(email, 6)) / count(*) as col6,
count(distinct left(email, 7)) / count(*) as col7
from user
假設輸出依次為0.0305，0.0309，0.0310
查詢顯示當字首長度達到7的時候，再增加字首長度，區分度提升的幅度已經很小了，因此建立email(7)的字首索引即可
需要註意的一點是，字首索引不能使用改寫索引，因為從索引中獲取不到完整的資料，還得回表查詢
建立索引的列不為NULL
只要列中包含有 NULL 值都將不會被包含在索引中，複合索引中只要有一列含有 NULL值，那麼這一列對於此複合索引就是無效的。
因此，在資料庫設計時，除非有一個很特別的原因使用 NULL 值，不然儘量不要讓欄位的預設值為 NULL。
分頁查詢最佳化
MySQL 並不是跳過 offset 行，而是取 offset+N 行，然後傳回放棄前 offset 行，傳回 N 行，那當 offset 特別大的時候，效率就非常的低下，要麼控制傳回的總頁數，要麼對超過特定閾值的頁數進行 SQL 改寫，單開一文來講
