mysql单列索引和多列索引_浅谈MySQL索引优化
索引基礎知識總結及常見索引優化手段
一、索引簡介
什么是索引？
MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取數據的數據結構。
可以簡單理解為“排好序的快速查找數據結構”。
一般來說索引本身也很大，不可能全部存儲在內存中，因此索引往往以索引文件的形式存儲的磁盤上
索引的優點
類似大學圖書館建書目索引，提高數據檢索的效率，降低數據庫的IO成本
通過索引列對數據進行排序，降低數據排序的成本，降低了CPU的消耗
索引的缺點
雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息
實際上索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄，所以索引列也是要占用空間的
索引結構
MySQL支持諸多存儲引擎，而各種存儲引擎對索引的支持也各不相同，因此MySQL數據庫支持多種索引類型，如BTree索引，哈希索引，全文索引等等。
下面簡單介紹下BTree索引、B+Tree索引、聚簇索引與非聚簇索引
BTree索引
B+Tree索引
InnoDB使用B+Tree作為索引結構
拓展：B+Tree與B-Tree 的區別
1)B樹的關鍵字和記錄是放在一起的，葉子節點可以看作外部節點，不包含任何信息；B+樹的非葉子節點中只有關鍵字和指向下一個節點的索引，記錄只放在葉子節點中。
2)在B樹中，越靠近根節點的記錄查找時間越快，只要找到關鍵字即可確定記錄的存在；而B+樹中每個記錄的查找時間基本是一樣的，都需要從根節點走到葉子節點，而且在葉子節點中還要再比較關鍵字。從這個角度看B樹的性能好像要比B+樹好，而在實際應用中卻是B+樹的性能要好些。因為B+樹的非葉子節點不存放實際的數據，這樣每個節點可容納的元素個數比B樹多，樹高比B樹小，這樣帶來的好處是減少磁盤訪問次數。盡管B+樹找到一個記錄所需的比較次數要比B樹多，但是一次磁盤訪問的時間相當于成百上千次內存比較的時間，因此實際中B+樹的性能可能還會好些，而且B+樹的葉子節點使用指針連接在一起，方便順序遍歷(例如查看一個目錄下的所有文件，一個表中的所有記錄等)，這也是很多數據庫和文件系統使用B+樹的緣故。
思考：為什么說B+樹比B樹更適合實際應用中操作系統的文件索引和數據庫索引？
1) B+樹的磁盤讀寫代價更低
B+樹的內部結點并沒有指向關鍵字具體信息的指針。因此其內部結點相對B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
2) B+樹的查詢效率更加穩定
由于非終結點并不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。
聚簇索引與非聚簇索引
聚簇索引并不是一種單獨的索引類型，而是一種數據存儲方式。
術語‘聚簇’表示數據行和相鄰的鍵值聚簇的存儲在一起。
如下圖，左側的索引就是聚簇索引，因為數據行在磁盤的排列和索引排序保持一致。
聚簇索引的好處：
按照聚簇索引排列順序，查詢顯示一定范圍數據的時候，由于數據都是緊密相連，數據庫不不用從多個數據塊中提取數據，所以節省了大量的io操作。
聚簇索引的限制：
對于mysql數據庫目前只有innodb數據引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于數據物理存儲排序方式只能有一種，所以每個Mysql的表只能有一個聚簇索引。一般情況下就是該表的主鍵。
為了充分利用聚簇索引的聚簇的特性，所以innodb表的主鍵列盡量選用有序的順序id，而不建議用無序的id，比如uuid這種。
MySQL索引分類
單值索引
即一個索引只包含單個列，一個表可以有多個單列索引
單獨建單值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);
刪除索引：
DROP INDEX idx_customer_name on customer;
唯一索引
索引列的值必須唯一，但允許有空值
單獨建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
刪除索引：
DROP INDEX idx_customer_no on customer;
主鍵索引
設定為主鍵后數據庫會自動建立索引，innodb為聚簇索引
單獨建主鍵索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
刪除建主鍵索引：
ALTER TABLE customer drop PRIMARY KEY;
復合索引
即一個索引包含多個列
單獨建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
刪除索引：
DROP INDEX idx_no_name on customer;
基本語法總結
創建:
CREATE [UNIQUE ] INDEX [indexName] ON table_name(column));
刪除:
DROP INDEX [indexName] ON mytable;
查看:
SHOW INDEX FROM table_name;
還可以使用ALTER命令
哪些情況需要創建索引？☆
主鍵自動建立唯一索引
頻繁作為查詢條件的字段應該創建索引
查詢中與其它表關聯的字段，外鍵關系建立索引
單鍵/組合索引的選擇問題， 組合索引性價比更高
查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
查詢中統計或者分組字段
哪些情況不要創建索引？☆
表記錄太少
經常增刪改的表或者字段
雖然提高了查詢速度，但是會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。更新表時，MySQL不僅要保存數據，還要保存一下索引文件。
Where條件里用不到的字段不創建索引
過濾性不好的不適合建索引
二、借助Explain進行性能分析
Explain是什么(查看執行計劃)
使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸。
Explain用途
查看表的讀取順序
查看哪些索引可以使用
查看數據讀取操作的操作類型
查看哪些索引被實際使用
查看表之間的引用
查看每張表有多少行被物理查詢
Explain使用方法
Explain + SQL語句
執行計劃包含的信息:
各字段解釋:☆
id
select查詢的序列號,包含一組數字，表示查詢中執行select子句或操作表的順序
id相同，執行順序由上至下
id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行
id相同不同，同時存在每個id號碼，表示一趟獨立的查詢。一個sql的查詢趟數越少越好。
select_type
查詢的類型，主要是用于區別普通查詢、聯合查詢、子查詢等的復雜查詢
table
顯示這一行的數據是關于哪張表的
partitions
代表分區表中的命中情況，非分區表，該項為null
type☆
type顯示的是訪問類型，是較為重要的一個指標，結果值從最好到最壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般來說，得保證查詢至少達到range級別，最好能達到ref。
possible_keys
顯示可能應用在這張表中的索引，一個或多個。
查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用。key
實際使用的索引。如果為NULL，則沒有使用索引。查詢中若使用了覆蓋索引，則該索引和查詢的select字段重疊。
key_len
表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度。key_len字段能夠幫你檢查是否充分的利用了索引。
ref
顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用于查找索引列上的值
rows
rows列顯示MySQL認為它執行查詢時必須檢查的行數。rows越小，性能越高。
filtered
這個字段表示存儲引擎返回的數據在server層過濾后，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數
Extra
包含不適合在其他列中顯示但十分重要的額外信息
三、查詢優化策略☆
單表優化策略
全列匹配
最左前綴法則
如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始命中并且不跳過索引中的列。
注意：由于MySQL的查詢優化器會自動調整where子句的條件順序以使用適合的索引。
不要在索引列上做任何操作(計算、函數、(自動or手動)類型轉換)，會導致索引失效而轉向全表掃描
存儲引擎不能使用索引中范圍條件右邊的列
mysql 在使用不等于(!= 或者<>)的時候無法使用索引,會導致全表掃描
is not null 無法使用索引,但is null是可以使用索引的
若like以通配符開頭('%abc…')，則mysql索引失效,會變成全表掃描的操作
字符串不加單引號索引失效
練習
對于單鍵索引，盡量選擇針對當前query過濾性更好的索引
在選擇組合索引的時候，當前Query中過濾性最好的字段在索引字段順序中，位置越靠前越好。
在選擇組合索引的時候，盡量選擇可以能夠包含當前query中的where字句中更多字段的索引
在選擇組合索引的時候，如果某個字段可能出現范圍查詢時，盡量把這個字段放在索引次序的最后面
書寫sql語句時，盡量避免造成索引失效的情況
多表關聯查詢優化策略
保證被驅動表的join字段已經被索引
left join 時，選擇小表作為驅動表，大表作為被驅動表。
inner join 時，mysql會自己幫你把小結果集的表選為驅動表。
子查詢盡量不要放在被驅動表，有可能使用不到索引。
能夠直接多表關聯的盡量直接關聯，不用子查詢。
子查詢優化
盡量不要使用not in或者not exists。用left outer join on xxx is null 替代。
排序分組優化
ORDER BY子句，盡量使用Index方式排序,避免使用FileSort方式排序
當范圍條件是group by 或者 order by 的字段出現二選一時 ，優先觀察條件字段的過濾數量，如果過濾的數據足夠多，而需要排序的數據并不多時，優先把索引放在范圍字段上。反之，亦然。
如果不在索引列上，filesort有兩種算法：
mysql就要啟動雙路排序和單路排序
GROUP BY關鍵字優化
group by 使用索引的原則幾乎跟order by一致 ，唯一區別是groupby 即使沒有過濾條件用到索引，也可以直接使用索引。
本文簡單總結了下MySQL常見常用的索引優化策略
—END—
分享知識|一起成長
【DragonWell】
▼
總結
以上是生活随笔為你收集整理的mysql单列索引和多列索引_浅谈MySQL索引优化的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql 列名能不能写成col1、co
- 下一篇： vorwerk 机器人_福维克(Vorw