0001.索引
1.覆蓋索引
1.1 定義
覆蓋索引是select的數據列只用從索引中就能夠取得，不必讀取數據行，換句話說查詢列要被所建的索引覆蓋。
1.2 覆蓋索引不能生效的情況有2種
- 查詢列是表的所有列的情況
- 解決方案有
- 子查詢，走覆蓋索引，然後進行表關聯即可
- 查詢列中進行索引列的顯示
- 解決方案有
- 查詢條件使用like且是前綴的方式
2.聚簇索引
- 如果表設置了
主鍵，則主鍵就是聚簇索引 - 如果表沒有主鍵，則會默認第一個NOT NULL，且唯一（UNIQUE）的列作為聚簇索引
- 以上都沒有，則會默認創建一個隱藏的row_id作為聚簇索引
- InnoDB的聚簇索引的
葉子節點存儲的是行記錄（其實是頁結構，一個頁包含多行數據） - InnoDB必須要有
至少一個聚簇索引。 - 由此可見，使用聚簇索引查詢會很快，因為可以直接定位到行記錄。
3.普通索引
- 普通索引也叫二級索引，除聚簇索引外的索引，即非聚簇索引。
- InnoDB的普通索引葉子節點存儲的是主鍵（聚簇索引）的值，而MyISAM的普通索引存儲的是記錄指針。
4.聚簇索引與普通索引的區別
- InnoDB聚集索引的葉子節點
存儲行記錄，因此，InnoDB必須要有，且只有一個聚集索引：- 如果表定義了PK，則PK就是聚集索引；
- 如果表沒有定義PK，則第一個not NULL unique列是聚集索引；
- 否則，InnoDB會創建一個隱藏的row-id作為聚集索引；
- InnoDB普通索引的葉子節點
存儲主鍵值。
5.索引覆蓋使用場景
場景1：全表count查詢優化
-- 原表為：
user(PK id, name, sex)；
-- 直接：
select count(name) from user;
-- 不能利用索引覆蓋。
-- 添加索引：
alter table user add key(name);
-- 就能夠利用索引覆蓋提效。
場景2：列查詢回表優化
-- 將單列索引(name)升級為聯合索引(name, sex)，即可避免回表
select id,name,sex ... where name='shenjian';
場景3：分頁查詢
select id,name,sex ... order by name limit 500,100;
-- 但是這個分頁不是最優的，當前是從索引覆蓋角度考慮的，如果綜合考慮性能，需要在where id >=500, 主要考慮 id 很大時，IO的分頁載入到記憶體是很耗時的
6.回表查詢
先通過普通索引的值定位聚簇索引值，再通過聚簇索引的值定位行記錄數據，需要掃描兩次索引B+樹，它的性能較掃一遍索引樹更低。
7.如何創建有效的索引
7.1 索引選擇性
這個針對列是字元串類型的。需要對字元串的一部分前綴作為索引。這時需要引入索引選擇性，索引選擇性是指不重複的索引值與數據表的記錄總數的比值，可以看出索引選擇性越高則查詢效率越高，當索引選擇性為1時，效率是最高的，但是在這種場景下，很明顯索引選擇性為1的話我們會付出比較高的代價，索引會很大(存放索引的物理空間增大，同時載入索引到記憶體頁的IO頻繁)
7.2 如果創建有效索引
- 選擇前綴
- 計算該列完整列的選擇性，使得前綴選擇性接近於完整列的選擇性
- 使用多列索引
- 選擇合適的索引列順序
- 經驗是將選擇性最高的列放到索引最前列，可以在查詢的時候過濾出更少的結果集
- 但這樣並不總是最好的，如果考慮到
group by或者order by等情況，再比如考慮到一些特別場景下的guest帳號等數據情況，上面的經驗法則可能就不是最適用的
- 覆蓋索引
- 所謂覆蓋索引就是指索引中包含了查詢中的所有欄位，這種情況下就不需要再進行回表查詢了
- MySQL 中只能使用
B-Tree索引做覆蓋索引，因為哈希索引等都不存儲索引的列的值，覆蓋索引對於MyISAM和InnoDB都非常有效，可以減少系統調用和數據拷貝等時間 - Tips：減少
select *操作
- 使用索引掃描來做排序
- MySQL 生成有序的結果有兩種方法：通過排序操作，或者按照索引順序掃描；使用排序操作需要佔用大量的 CPU 和記憶體資源，而使用 index 性能是很好的，所以，當我們查詢有序結果時，盡量使用索引順序掃描來生成有序結果集
- 怎樣保證使用索引順序掃描
- 索引列順序和 ORDER BY 順序一致
- 所有列的排序方向一致
- 如果關聯多表，那麼只有當 ORDER BY 子句引用的欄位全部為第一張表時，才能使用索引做排序，限制依然是需要滿足索引的最左前綴要求
- 壓縮索引
- MyISAM 中使用了前綴壓縮技術，會減少索引的大小，可以在記憶體中存儲更多的索引，這部分優化默認也是只針對字元串的，但是可以自定義對整數做壓縮
- 這個優化在一定情況下性能比較好，但是對於某些情況可能會導致更慢，因為前綴壓縮決定了每個關鍵字都必須依賴於前面的值，所以無法使用二分查找等，只能順序掃描，所以如果查找的是逆序那麼性能可能不佳
- 減少重複、冗餘以及未使用的索引
- MySQL 的唯一限制和主鍵限制都是通過索引實現的，所以不需要在同一列上增加主鍵、唯一限制再創建索引，這樣是重複索引
- 盡量減少新增索引，而應該擴展已有的索引，因為新增索引可能會導致 INSERT、UPDATE、DELETE 等操作更慢
- 可以考慮刪除沒有使用到的索引，定位未使用的索引，有兩個辦法，在
Percona Server或者MariaDB中打開 userstates 伺服器變數，然後等伺服器運行一段時間後，通過查詢INFORMATION_SCHEMA.INDEX_STATISTICS就可以查詢到每個索引的使用頻率
- 索引和鎖
- InnoDB 支援行鎖和表鎖，默認使用行鎖，而 MyISAM 使用的是表鎖，所以使用索引可以讓查詢鎖定更少的行，這樣也會提升查詢的性能，如果查詢中鎖定了1000行，但實際只是用了100行，那麼在 5.1 之前都需要提交事務之後才能釋放這些鎖，5.1 之後可以在伺服器端過濾掉行之後就釋放鎖，不過依然會導致一些鎖衝突
- 減少索引和數據碎片
- 首先我們需要了解一下為什麼會產生碎片，比如 InnoDB 刪除數據時，這一段空間就會被留空，如果一段時間內大量刪除數據，就會導致留空的空間比實際的存儲空間還要大，這時候如果進行新的插入操作時，MySQL 會嘗試重新使用這部分空間，但是依然無法徹底佔用，這樣就會產生碎片
- 產生碎片帶來的後果當然是，降低查詢性能，因為這種情況會導致隨機磁碟訪問
- 可以通過
OPTIMIZE TABLE或者重新導入數據表來整理數據
8.索引下推
MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表字數。
8.1示例說明
需求
查詢表中「名字第一個字是張，性別男，年齡為10歲的所有記錄」。那麼，查詢語句是這麼寫的：
select * from tuser where name like '張 %' and age=10 and ismale=1;
根據前面說的「最左前綴原則」，該語句在搜索索引樹的時候，只能匹配到名字第一個字是『張』的記錄（即記錄ID3），接下來是怎麼處理的呢？當然就是從ID3開始，逐個回表，到主鍵索引上找出相應的記錄，再比對age和ismale這兩個欄位的值是否符合。
原理推論
下面圖1、圖2分別展示這兩種情況。
- 圖 1 中，在 (name,age) 索引裡面我特意去掉了 age 的值，這個過程 InnoDB 並不會去看 age 的值，只是按順序把「name 第一個字是』張』」的記錄一條條取出來回表。因此，需要回表 4 次。
- 圖 2 跟圖 1 的區別是，InnoDB 在 (name,age) 索引內部就判斷了 age 是否等於 10，對於不等於 10 的記錄，直接判斷並跳過。在我們的這個例子中，只需要對 ID4、ID5 這兩條記錄回表取數據判斷，就只需要回表 2 次。
總結
如果沒有索引下推優化（或稱ICP優化），當進行索引查詢時，首先根據索引來查找記錄，然後再根據where條件來過濾記錄；在支援ICP優化後，MySQL會在取出索引的同時，判斷是否可以進行where條件過濾再進行索引查詢，也就是說提前執行where的部分過濾操作，在某些場景下，可以大大減少回表次數，從而提升整體性能。
