一文帶你分清MySQL回表查詢與索引覆蓋
1、兩類索引
為了更好地闡釋這個問題,我們還是從索引來介紹吧。
InnoDB 索引分為兩大類,一類是聚集索引(Clustered Index),一類是非聚集索引(Secondary Index)
1.1 聚集索引(聚簇索引)
InnoDB聚集索引的葉子節(jié)點存儲行記錄,因此InnoDB必須要有且只有一個聚集索引。
如果表定義了PK(Primary Key,主鍵),那么PK就是聚集索引。
如果表沒有定義PK,則第一個NOT NULL UNIQUE的列就是聚集索引。
否則InnoDB會另外創(chuàng)建一個隱藏的ROWID作為聚集索引。
這種機制使得基于PK的查詢速度非常快,因為直接定位的行記錄。
1.2 非聚集索引(普通索引、非聚簇索引、二級索引)
普通索引也叫二級索引,除聚簇索引外的索引,即非聚簇索引。
InnoDB的普通索引葉子節(jié)點存儲的是主鍵(聚簇索引)的值,而MyISAM的普通索引存儲的是記錄指針。
Q:為什么非主鍵索引結(jié)構葉子結(jié)點存儲的是主鍵值?
A:減少了出現(xiàn)行移動或者數(shù)據(jù)頁分裂時二級索引的維護工作(當數(shù)據(jù)需要更新的時候,二級索引不需要修改,只需要修改聚簇索引,一個表只能有一個聚簇索引,其他的都是二級索引,這樣只需要修改聚簇索引就可以了,不需要重新構建二級索引)
在使用非聚集索引時,為了取到具體數(shù)據(jù),則需要通過PK回到聚集索引里去查詢數(shù)據(jù)。這就叫回表查詢,掃描了2次索引樹,所以效率相對較低。
2、應用示例
一例勝千言,show me you code!
2.1 建表操作
mysql> create table user( -> id int(10) auto_increment, -> name varchar(30), -> sex tinyint(4), -> type varchar(8), -> primary key (id), -> index idx_name (name) -> )engine=innodb charset=utf8mb4;
id 字段是聚簇索引,name 字段是普通索引(二級索引)
2.2 填充數(shù)據(jù)
mysql> select * from user; +----+--------+------+------+ | id | name | sex | type | +----+--------+------+------+ | 1 | sj | m | A | | 3 | zs | m | A | | 5 | ls | m | A | | 9 | ww | f | B | +----+-----+-----+-----+
2.3 索引結(jié)構
- 聚簇索引(ClusteredIndex)
id 是主鍵,所以是聚簇索引,其葉子節(jié)點存儲的是對應行記錄的數(shù)據(jù)
- 普通索引(secondaryIndex)
name 是普通索引(二級索引),非聚簇索引,其葉子節(jié)點存儲的是聚簇索引的的值
2.4 查找過程
- 普通索引查找過程
如果查詢條件為主鍵(聚簇索引),則只需掃描一次B+樹即可通過聚簇索引定位到要查找的行記錄數(shù)據(jù)。
select * from user where name = 'lisi';
普通索引因為無法直接定位行記錄,其查詢過程在通常情況下是需要掃描兩遍索引樹的。
實際執(zhí)行過程:
路徑需要掃描兩遍索引樹,第一遍先通過普通索引定位到主鍵值id=5,然后第二遍再通過聚集索引定位到具體行記錄。
這就是所謂的回表查詢,即先定位主鍵值,再根據(jù)主鍵值定位行記錄,性能相對于只掃描一遍聚集索引樹的性能要低一些。
3、索引覆蓋
索引覆蓋是一種避免回表查詢的優(yōu)化策略。
只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù),無需回表,速度更快。
3.1 如何實現(xiàn)覆蓋索引
將要查詢的數(shù)據(jù)作為索引列建立普通索引(可以是單列索引,也可以一個索引語句定義所有要查詢的列,即聯(lián)合索引),這樣的話就可以直接返回索引中的的數(shù)據(jù),不需要再通過聚集索引去定位行記錄,避免了回表的情況發(fā)生。
explain select id, name from user where name = 'lisi';
explain分析:因為name是普通索引,使用到了name索引,通過一次掃描B+樹即可查詢到相應的結(jié)果,這樣就實現(xiàn)了覆蓋索引
- END -
希望今天的講解對大家有所幫助,謝謝!
到此這篇關于一文帶你分清MySQL回表查詢與索引覆蓋的文章就介紹到這了,更多相關MySQL回表查詢與索引覆蓋內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
mysql 查詢數(shù)據(jù)庫中的存儲過程與函數(shù)的語句
mysql 查詢數(shù)據(jù)庫中的存儲過程與函數(shù)的語句,需要的朋友可以參考下。2011-05-05使用dreamhost空間實現(xiàn)MYSQL數(shù)據(jù)庫備份方法
使用dreamhost空間實現(xiàn)MYSQL數(shù)據(jù)庫備份方法...2007-07-07