高性能索引
什么是索引
索引是存儲引擎用于快速找到記錄的一種數據結構。索引工作流程
如果想在一本書中找到某個特定主體，一般會先看書的“索引”，然后找到對應的頁碼。在MYSQL中，存儲引擎用類似的方法使用索引，先在索引中找到對應值，然后根據匹配的索引記錄找到對應的數據行。例如：要查找user_id = 2 的 用戶信息，其中在user_id上建有索引： SELECT name,age FROM user WHERE user_id = 2; 則MYSQL先在索引上按值進行查找到 user_id = 2的行，然后返回所有包含該值的數據行。 索引可以包含一個或者多個列的值。如果索引包含多個列，那么索引只能高效的使用索引的最左前綴列。索引類型和實現原理
索引類型
- B-Tree索引
- 哈希索引
- 空間數據索引
- 全文索引
B-Tree索引
B-Tree索引底層使用B-Tree數據結構來存儲數據。索引定義中提到索引是一種快速找到記錄的數據結構。那么為什么這種數據結構能夠快速找到記錄呢？先來了解一下B-Tree數據結構。 B-Tree （B+Tree）又叫平衡多路查找樹。從名字可以看到：- 首先它是一棵查找樹，左子樹上所有結點的值均小于它的根結點的值，右子樹上所有的節點的值均大于它的根節點的值。（二叉）
- 其次它是一棵平衡樹，保證樹的深度由各個子節點均攤
- 最后它是多路，每層有多個節點分支，減少訪問深度，減少磁盤I/O次數
- 葉子節點的指針指向的是被索引的數據，而不是其他的節點頁
- InnoDB 的邏輯頁/數據頁 16K的含義：這和盤塊 大小有關。位于同一盤塊的數據可以一次讀取出來，設置成盤塊的整數倍這樣可以減少磁道查找時間。而且可以減少I/O次數。（待討論）
?
其中需要注意的點：索引對多個值進行排序的依據是CREATE TABLE 語句中定義索引列的順序。為什么呢？因為索引樹在建立后插入的順序就是根據此規則插入的。順序也就是：KEY(last_name,first_name,dob)
依據上述例子，可以用到B-Tree索引的查詢有：全鍵值、鍵值范圍或鍵前綴查找（最左前綴）。具體如下：全值匹配（和索引中的所有列進行匹配）：
查找姓名為Cuba Allen、出生于1960-01-01的人匹配最左前綴：
查找所有姓為Allen的人匹配列前綴：
查找所有以J開頭的姓的人匹配范圍值:
查找姓在Allen和Barry之間的人精確匹配某一列并范圍匹配另外一列：
查找所有姓為Allen,并且名字是字母與K開頭的人只訪問索引的查詢
總結來說也就是三種情況：1、就是索引全部用到了2、就是索引部分用到，且符合最左匹配原則3、就是使用索引的范圍查詢。（注意：如果查詢中有某個列的范圍查詢，那么這個列右邊的所有列（不包括該范圍列）都無法使用索引優化查找。其中：LIKE 屬于范圍查找）索引好處
索引可以讓服務器快速的定位到表的指定位置。從而可以提升查詢速度。 對于最常見的B-Tree索引，因為存儲數據是順序的，所以MYSQL可以用來做ORDER BY和GROUP BY操作。（使用索引掃描來做排序）。因為索引中存儲了實際的列值，所以某些查詢只使用索引就能夠完成全部查詢。（覆蓋索引）?？偨Y下來索引有如下三個優點：- 索引大大減少了服務器需要掃描的數據量(B-Tree為查找樹，時間復雜度O(logxN))
- 索引可以幫助服務器避免排序和臨時表(查找樹中序遍歷本身是有序的，當可以使用到索引排序時)
- 索引可以將隨機I/O變為順序I/O(使用覆蓋索引時不用回表查詢)
三星系統
- 索引將相關的記錄放到一起則獲取一星
- 如果索引中的數據順序和查找中的排列順序一致則獲得二星
- 如果索引中的列包含了查詢中需要的全部列則獲得三星（覆蓋索引）
確保正確使用了索引
獨立的列（單列的時候）
獨立的列是指索引不能是表達式的一部分，也不能是函數的參數。下面兩種情況下不會使用索引：表示式的一部分：（其中user_id上有索引，主鍵索引）
SELECT * FROM user WHERE user_id ＋1 < 5;索引作為表達式的一部分(CURRENT_DATE為索引列)
WHERE TO_DAYS(CURRENT_DATE)=1234545;多列索引（多列的時候）
多列索引要選擇合適得索引列順序。因為索引是按照最左列進行排序。多列索引討論的就是如何排序索引列讓查詢更合理。 多列索引的順序問題需要考慮的因素：- 索引列的經驗法則：將選擇性最高的列放到索引最前列。（選擇性高就是那一列能篩選出更少的數據，那一列的選擇性就越高。例如：gender字段能大約篩選出 1/2 的數據，主鍵能篩選出唯一一條數據，那么主鍵的選擇性就更高，如果不考慮其它因素，涉及到這兩個列過濾時，就應該把主鍵列放在篩選條件的第一位）
- 高頻范圍字段一般要靠后放：例如：年齡字段。因為使用了索引范圍 右邊的索引列就無法再使用到索引。
- 對于可列舉字段使用到索引的方法：使用 IN關鍵字。例如 WHERE gender IN('m','f')。這樣后面也是可以用到索引的。同樣的還有 NOT IN
聚簇索引
聚簇索引 并不是一種單獨的索引類型，而是一種數據存儲方式。 聚簇索引和B-Tree索引有啥區別： B-Tree索引存放的是索引列，聚簇索引存放行的全部數據。數據分布如下： 值得注意的是：一個表只能有一個聚簇索引，InnoDB通過主鍵聚集數據，如果沒有定義主鍵，選擇一個唯一非空索引代替。 優缺點如下：| 優點 | 缺點 |
| 可以把相關數據保存在一起。 數據訪問更快。直接在索引中找，不用回表? ...... | 插入速度嚴重依賴插入順序 更新聚簇索引列代價高 可能導致全表掃描變慢 ...... |
覆蓋索引
定義：如果一個索引包含（或覆蓋）所有需要查詢的字段的值，就稱之為覆蓋索引。（包含：索引列比查找列多或相等 覆蓋：剛好一樣），MYSQL只能使用B-Tree索引做覆蓋索引。 覆蓋索引的好處：- 極大減少數據訪問量，速度快+1
- 索引順序存儲，所以I/O為順序I/O，速度快+2
- 如果嚴格符合所以定義，那肯定使用了覆蓋索引，哈哈
- 使用EXPALIN關鍵字 查看。如果在 Extra列 看到"Using index"，證明使用了覆蓋索引。
| 字段值 | 含義 | 常見值及含義 | ||
| select_type | 查詢類型 | SIMPLE(簡單SELECT，不使用UNION或子查詢等，普通WHERE可以) PRIMARY(最外層的SELECT) UNION(SELECT 之后使用了UNION) | ||
| type | 連接使用了哪種類別,有無使用索引，從最好到最差的連接類型為 const、eq_reg、ref、 range、index和ALL | const:表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行， 在這行的列值可被優化器剩余部分認為是常數。const表很快，因為它們只讀取一次 ref:基于索引做掃描，可以用于單表掃描或者連接 range:只檢索給定范圍的行，使用一個索引來選擇行 index:僅僅掃描了索引 all:全表掃描，不適用索引，直接讀取表上的數據 | ||
| key | 使用的索引 | 如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引， 在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX | ||
| key_len | 鍵長度 | 在不損失精確度的情況下，長度越短越好 | ||
| ref | 參考 | ref列顯示使用哪個列或常數與key一起從表中選擇行 | ||
| rows | 行數 | MySQL認為它執行查詢時必須檢查的行數。 | ||
| Extra | 查詢詳細信息 | Distinct:一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了? Using filesort:使用文件排序 Using index:覆蓋索引 Using temporary：MySQL需要創建臨時表來存儲結果 Using where:使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶 | ||
使用索引掃描來排序
注意：如果索引不能覆蓋查詢所需的全部的列，那就不得不每掃描一條索引記錄就回表查詢一次對應的行。這基本上都是隨機I/O，因此按索引順序讀取數據的速度通常要比順序的全表掃描慢，尤其在I/O密集型工作時。查看是否使用了索引來排序：
如果 EXPLAIN 出來的 type 列的結果為 index，那說明MySQL使用了索引掃描來排序。還是上面的那個例子： EXPLAIN SELECT id FROM tbl_role; 使用索引掃描排序好處： 查詢速度比沒有使用更快。因為在使用索引查詢時，找到索引記錄之后需要回表查詢數據，這種按索引順序讀取一般都是隨機I/O,比較慢。 使用到索引掃描來排序的條件： 只有當索引的列順序和ORDER BY子句的順序完全一致，并且所有列的排序方向都一樣時，MySQL才能夠使用索引來對結果做排序。如果查詢需要關聯多張表，則只有當ORDER BY子句引用的字段全部為第一個表時，才能使用索引做排序。 上述條件注意的地方：- 索引列和ORDER BY子句的順序完全一致：1、不包括主鍵索引2、滿足索引的最左匹配原則，比如索引列為KEY(age,name),那么ORDER BY age是可以的。
- 所有的方向都一樣：就是DESC 和ASC,不能再ORDER BY子句里面 出現有的正向排序，有的逆向排序，這也同時說明了可以 DESC 或ASC，但是一般使用其中一種
- 關聯多張表，只要當ORDER BY子句引用的字段全部為第一個表時才可以。這里的第一個表并不是指SQL語句中第一個表，而是優化器優化后的第一個表。
?發現type 不是 index，但是提示使用了索引
EXPLAIN SELECT * FROM user WHERE birth = '1993-01-02' ORDER BY grade,age;如果更改where 子句為 范圍比較，則不能使用到索引（而是文件排序）。（圖1）因為這里使用到了范圍查詢，后面的索引無法使用到，導致順序有問題。同理，IN 關鍵字也是不可以使用的。（圖2）
EXPLAIN SELECT * FROM user WHERE birth > '1993-01-02' ORDER BY grade,age圖1
圖2
如果想使用索引，可以把birth放到ORDER BY后面
?還有一種情況是WHERE 子句不涉及索引字段，這時候無法使用索引掃描排序。
?冗余和重復索引
重復索引：是指在相同列上按照相同順序創建的相同類型的索引。應該盡量避免冗余和重復索引，如果發現，一般是要刪掉。- 相同列，相同順序，相同類型
- 盡量避免，發現刪除
優化排序
如何處理排序和分頁時 用戶查看翻頁到很靠后的數據？ SELECT * FROM profiles WHERE sex = 'M' ORDER BY rating limit 10000,10總結
在選擇索引和編寫利用這些索引的查詢時，有如下三個原則始終需要記住：轉載于:https://www.cnblogs.com/uodut/p/7070633.html
總結
- 上一篇： java hascode
- 下一篇： MySQL慢查询日志ES索引模板