前言
要搞明白覆蓋索引首先就得明白主鍵索引和輔助索引的區(qū)別,以及查詢時引擎的工作方式。
當然,以上都是基于innoDB引擎來說。
主鍵索引
葉子節(jié)點保存數(shù)據(jù),
輔助索引
葉子節(jié)點保存主鍵值
查詢一條數(shù)據(jù)是如何工作的呢
由于輔助索引只存儲主鍵的值,如果使用輔助索引搜索數(shù)據(jù)就必須先從輔助索引取到主鍵的值,再使用主鍵的值去主鍵索引上查詢,直到找到葉子節(jié)點上的數(shù)據(jù)返回。 ---- 這個也稱之為"回表"
那么如何避免回表查詢的發(fā)生呢?
如果輔助索引上已經(jīng)存在我們需要的數(shù)據(jù),那么引擎就不會去主鍵上去搜索數(shù)據(jù)了。 ---- 這個就是所謂的"覆蓋索引"
概念
如果一個索引覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。我們知道在InnoDB存儲引擎中，如果不是主鍵索引，葉子節(jié)點存儲的是主鍵和列值。最終還是要“回表”，也就是要通過主鍵再查找一次。這樣就會比較慢。而覆蓋索引就是把要查詢出的列和索引是對應(yīng)的，不做回表操作。
舉例
例如 user 上有 id name age email address 四個字段 其中 name age email 三個字段是復(fù)合索引 id(主鍵索引忽略不看)
那么 select name age email from user where email = '123@qq.com'
這樣的查詢就叫覆蓋索引, 就是要查詢的內(nèi)容與建立索引的個數(shù)順序相等
驗證
CREATE TABLEuser(idint(11) unsigned NOT NULL AUTO_INCREMENT,namevarchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,ageint(11) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id), KEYidx_name_age(name,age) USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
生成測試數(shù)據(jù)
delimiter $$ DROP PROCEDURE IF EXISTS proc_batch_insert; CREATE PROCEDURE proc_batch_insert() BEGIN DECLARE pre_name BIGINT; DECLARE ageVal INT; DECLARE i INT; SET ageVal=100; SET i=1; WHILE i < 100000 DO INSERT INTO user(name`,age) VALUES(substring(MD5(RAND()),1,8),(ageVal+i)%30);
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END $$
delimiter ;
call proc_batch_insert();`
查詢數(shù)據(jù)
select * from user where age = 10
查看耗時
分析一下語句
explain select * from user where age = 20
查看執(zhí)行計劃
這樣的查詢速度快嗎?
只需要稍微改變一下查詢的字段, 我們就發(fā)現(xiàn)其中的區(qū)別了。
select age,name from user where age = 20
查看一下耗時
可以看到耗時減少了!
發(fā)生了什么呢,我們再來分析一下語句
可以看到extra列有一個 using idnex , 這個的意思就是使用了覆蓋索引,無需回表查詢了。
總結(jié)
實踐是檢驗原理的唯一標準。 通過此次實踐,想必你已經(jīng)充分了解并且體驗到覆蓋索引的概念及其意義了。其核心就是只從輔助索引要數(shù)據(jù)。那么, 普通索引(單字段)和聯(lián)合索引,以及唯一索引都能實現(xiàn)覆蓋索引的作用。
