之前一致以為索引就是簡單的在原表的數據上加了一些編號,讓查詢更加快捷。後來發現裡面還有更深的知識。 索引用於快速查找具有特定列值的行。如果沒有索引,MySQL 必須從第一行開始,然後通讀整個表以找到相關行。表數據越多,成本就越高。如果表有相關列的索引,MySQL 可以快速確定要在數據文件中間查找的位 ...
之前一致以為索引就是簡單的在原表的數據上加了一些編號,讓查詢更加快捷。後來發現裡面還有更深的知識。
索引用於快速查找具有特定列值的行。如果沒有索引,MySQL 必須從第一行開始,然後通讀整個表以找到相關行。表數據越多,成本就越高。如果表有相關列的索引,MySQL 可以快速確定要在數據文件中間查找的位置,而無需查看所有數據。這比順序讀取每一行要快得多。
自從MySQL5.5版本之後,MySQL的預設存儲引擎就變成了InnoDB。
-- 查看當前資料庫支持的搜素引擎
show ENGINES;
當我們創建一個表時,InnoDB引擎會根據主鍵給我們創建一個聚簇索引樹。
會形成一個只有葉子節【最下麵的節點】點存儲數據的B+ tree。
除了葉子節點,其餘的節點存儲的是主鍵的值以及指向下一個節點的指針信息。
先看官方說明:
簡而言之:
InnoDB預設會給創建一個根據主鍵id的聚簇索引的B+ tree,如果沒有主鍵就根據下麵的規則:
InnoDB聚集索引的葉子節點存儲行記錄,因此, InnoDB必須要有,且只有一個聚集索引:
(1)如果表定義了主鍵,則主鍵就是聚集索引;
(2)如果表沒有定義主鍵,則第一個not NULL unique列是聚集索引;
(3)否則,InnoDB會創建一個隱藏的row-id作為聚集索引;
後面我們再創建的索引統一被稱之為二級索引(非主鍵索引、輔助索引)
,當然也會創建一個B+ tree。
只不過相較於聚簇索引的B+ tree來說,二級索引葉子節點上存儲的為數據為主鍵的值,聚簇索引的葉子節點上存儲的為真正的一條數據。
好處:
避免了當數據發生修改的時候,大量B+ tree跟著修改。
那二級索引被觸發後,是怎麼查詢到數據的?
答:
這涉及到了兩個概念:
-
回表
通過二級索引(輔助索引)樹查詢索引數據,然後再通過聚集索引樹查詢完整數據的過程稱為回表。
-
覆蓋
select欄位已經包含在用到的索引中的時候稱為覆蓋索引。
比如說:我們創建了一個關於
name
欄位的索引,當我們查詢name的時候就會觸發覆蓋索引
可以通過EXPLAIN
關鍵字查看時候出發了覆蓋
:
執行計劃中出現Using index 字樣,表示用到了覆蓋索引,沒有產生回表的操作。
-- 創建一個組合索引
create index idx_name_sex on t_user(name,sex);
-- 觸發了覆蓋索引
EXPLAIN SELECT name,sex from t_user where name = 'name4999008'
-- 觸發了覆蓋索引
EXPLAIN SELECT name from t_user where name = 'name4999008' and sex = '男'
-- 沒有觸發覆蓋索引
EXPLAIN SELECT * from t_user where name = 'name4999008'