問題描述

以 innodb 為例。

下面很多圖都截圖來自于 http://blog.codinglabs.org/ar...

CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `age` int(11) unsigned NOT NULL, `name` varchar(30) NOT NULL DEFAULT ’’, PRIMARY KEY (`id`), KEY `idx_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入下列數(shù)據(jù)

mysql> select * from test;+----+-----+-------+| id | age | name |+----+-----+-------+| 15 | 34 | Bob || 18 | 77 | Alice || 20 | 5 | Jim || 30 | 91 | Eric || 49 | 22 | Tom || 50 | 89 | Rose |+----+-----+-------+6 rows in set (0.00 sec)

因?yàn)橹麈I是單調(diào)遞增的，所以這里會(huì)以主鍵聚集，如圖

這樣如果我做范圍查找

mysql> explain select * from test where id>16G;*************************** 1. row *************************** id: 1 select_type: SIMPLEtable: test partitions: NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 5 filtered: 100.00Extra: Using where1 row in set, 1 warning (0.00 sec)

查找的過程比較清晰，查詢到 B+樹最左端的葉子節(jié)點(diǎn)，然后 mysql 對(duì) B+ 樹做了優(yōu)化，各個(gè)葉子節(jié)點(diǎn)之間多了指針串連。

因?yàn)?Innodb 的特性，數(shù)據(jù)和聚簇索引都在一起了，就是上圖的截圖

當(dāng)使用 name 做精確查找的時(shí)候，比較清晰，根據(jù)索引找到主鍵，然后再通過主鍵再去查詢。

mysql> explain select * from test where name=’Bob’G;*************************** 1. row *************************** id: 1 select_type: SIMPLEtable: test partitions: NULL type: refpossible_keys: idx_name key: idx_name key_len: 92 ref: const rows: 1 filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)

新增一個(gè)索引

ALTER TABLE `test` ADD INDEX `idx_age` (`age`);

mysql> explain select * from test FORCE INDEX(idx_age) where age>20G;*************************** 1. row *************************** id: 1 select_type: SIMPLEtable: test partitions: NULL type: rangepossible_keys: idx_age key: idx_age key_len: 4 ref: NULL rows: 5 filtered: 100.00Extra: Using index condition1 row in set, 1 warning (0.00 sec)我的困惑，根據(jù)idx_age索引找到了各個(gè)葉子頁(yè)的數(shù)據(jù)，但是葉子頁(yè)上只是有對(duì)應(yīng)主鍵的指針，后面根據(jù)idx_age所有查詢的主鍵，再去聚簇索引中查詢的過程是怎樣的。

假如對(duì)應(yīng)的主鍵的數(shù)據(jù)都不在一頁(yè)上，那是不是有多少行，就要多少次從聚簇索引頂端到葉子頁(yè)的查詢；

還是先查詢出所有的主鍵了，先根據(jù)主鍵排序，然后查詢到最小的主鍵，然后依次往后找其他主鍵數(shù)據(jù)，最后再按照 age 的順序?qū)?shù)據(jù)排序返回？

問題解答

根據(jù)個(gè)人理解以及看書是拿到主鍵id，然后根據(jù)id去逐條回表隨機(jī)訪問。一般的索引建立，都會(huì)帶上主鍵。比如你上面的age，其實(shí)你的索引是(age,id)。按照你的理解來說，既然都查詢到了所有的主鍵，然后排序花費(fèi)nLog(n)的復(fù)雜度，接著還是逐條回表隨機(jī)訪問，有啥區(qū)別？