MySQL面試 - 索引篇

程序員五年狀態(tài).jpg

目錄

  • MySQL索引是什么？
  • 為什么要使用索引？
  • 創(chuàng)建，查看，刪除索引的方式
      1. 創(chuàng)建索引的三種方式：
      1. 查看索引的兩種方式：
      1. 刪除索引的兩種方式：
  • MySQL索引分類
  • MySQL索引使用原則
  • B-Tree索引的底層實現(xiàn)是什么？
      1. BTree和B+Tree結構在存儲數(shù)據(jù)上的區(qū)別？
      • <1> BTree 和 B+Tree結構上的明顯區(qū)別：
      • <2> BTree 和 B+Tree在存儲數(shù)據(jù)上的區(qū)別：
      1. B+Tree為什么適合做索引？為什么不用紅黑樹或者BTree呢？
      • <1> 為什么不用紅黑樹？
      • <2> 為什么不用BTree而用B+Tree做索引？
      1. 歸納總結
  • 什么是哈希索引？
    • <1> 哈希索引的數(shù)據(jù)結構：
    • <2> 哈希索引的限制：
  • 什么是自適應哈希索引？
  • 什么是聚簇索引，非聚簇索引？
    • <1> InnoDB的聚簇索引
    • <2> 聚簇索引和非聚簇索引的區(qū)別？
    • <3> 聚簇索引的優(yōu)缺點
  • 什么是覆蓋索引？
  • 主鍵索引，唯一索引，普通索引

MySQL索引是什么？

MySQL索引(鍵(key)).png

為什么要使用索引？（即索引的優(yōu)缺點是什么？）

索引.png

創(chuàng)建，查看，刪除索引的方式

1. 創(chuàng)建索引的三種方式：

第一種：建表時創(chuàng)建索引

CREATE TABLE student (
    id int unsigned not null auto_increment,
    num int unsigned not null,
    name varchar(255),
    primary key(id),
    unique key uk_num(num),
    index idx_name(name)
) ENGINE=InnoDB;

第二種：使用create index創(chuàng)建索引：能創(chuàng)建普通索引和唯一索引，不能創(chuàng)建主鍵索引

CREATE INDEX 索引名 ON 表名(column_list);
CREATE UNIQUE INDEX 索引名 ON 表名(column_list);
舉例：mysql> CREATE INDEX idx_name ON student(name);

第三種：使用alter table創(chuàng)建索引

ALTER TABLE 表名 ADD INDEX [索引名](column_list);  -- 索引名可選，不寫的話，默認索引名等于列名
ALTER TABLE 表名 ADD UNIQUE [索引名](column_list);
ALTER TABLE 表名 ADD PRIMARY KEY(column_list);
說明：索引名可選，不寫的話，默認生成的索引名等于列名；如果是多列的聯(lián)合索引，默認生成的索引名等于第一列的名字
舉例：
mysql> ALTER TABLE student ADD INDEX idx_name(name); 
mysql> ALTER TABLE student ADD UNIQUE (num); -- 默認生成的唯一索引名字為num

2. 查看索引的兩種方式：

第一種：SHOW INDEX FROM 表名;
第二種：SHOW KEYS FROM 表名;

3. 刪除索引的兩種方式：

第一種：DROP INDEX 索引名 ON 表名;
第二種：ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP PRIMARY KEY; 
說明：
表只可能有一個primary key主鍵索引，所以不需要指定索引名
如果沒有創(chuàng)建primary key主鍵索引，但表有一個或多個unique索引，則將刪除第一個unique索引

總結：

MySQL創(chuàng)建查看刪除索引.png

MySQL索引分類

MySQL索引.png

MySQL索引使用原則

MySQL索引使用原則.png

B-Tree索引的底層實現(xiàn)是什么？

B-Tree索引的底層實現(xiàn)是一個B+Tree而不是BTree

1. BTree和B+Tree結構在存儲數(shù)據(jù)上的區(qū)別？

先分別來看一下BTree 和 B+Tree的結構：

BTree.png
B+Tree.png

<1> BTree 和 B+Tree結構上的明顯區(qū)別：

  • B+Tree 所有的數(shù)據(jù)都存儲在葉子節(jié)點上。
  • B+Tree 每個葉子節(jié)點都有指向下一個葉子節(jié)點的鏈接，形成一種鏈式結構，這樣的好處是：可以從任意一個葉子節(jié)點開始遍歷，來獲取所有的數(shù)據(jù)。

再來看看BTree 和 B+Tree在存儲數(shù)據(jù)上的結構對比：

BTree存儲數(shù)據(jù)結構.png
B+Tree存儲數(shù)據(jù)結構.png

<2> BTree 和 B+Tree在存儲數(shù)據(jù)上的區(qū)別：

  • 在BTree的節(jié)點中存放有孩子指針，關鍵字key，以及key對應的數(shù)據(jù)，而在B+Tree中，內部非葉子節(jié)點只存放孩子指針和關鍵字key，而key對應的所有數(shù)據(jù)存放在葉子節(jié)點上。
  • B+Tree非葉子節(jié)點不存儲key對應的數(shù)據(jù)，因此相對于BTree，在同一塊磁盤頁上(InnoDB默認為16kb)可以存儲更多的key和孩子指針，所以B+Tree存儲的數(shù)據(jù)量比BTree大很多。

那具體看一下BTree和B+Tree 存儲關鍵字key和數(shù)據(jù)量的情況：

BTree存儲關鍵字key和數(shù)據(jù)量.png

BTree一個磁盤頁能存儲多少個關鍵字key呢？

這里假設關鍵字key，指針p都占用4byte，數(shù)據(jù)data占用1kb
計算：N*key + (N+1)p + Ndata = 16kb
則有：4Nb +4(N+1)b + 1000Nb = 16000b，1008Nb + 4b = 16000b，忽略較小的數(shù)，N = 16
BTree一個磁盤頁能存儲16個關鍵字key，孩子指針p有17個，存儲數(shù)據(jù)總量 = 17 * 16kb = 272kb

B+Tree存儲關鍵字key和數(shù)據(jù)量.png

B+Tree一個磁盤頁能存儲多少個關鍵字key呢？

計算：N*key + (N+1)p = 16kb
則有：4Nb + 4(N+1)b = 16000b，忽略較小的數(shù)，8Nb = 16000b，N = 2000
B+Tree一個磁盤頁能存儲2000個關鍵字key，孩子指針p有2001個，存儲數(shù)據(jù)總量 = 2001 * 16kb = 32016kb

由此可見B+Tree能存儲的關鍵字key和數(shù)據(jù)量跟BTree不是一個數(shù)量級別。

2. B+Tree為什么適合做索引？為什么不用紅黑樹或者BTree呢？

<1> 為什么不用紅黑樹？

總結：

  • 紅黑樹是二叉的，即一個節(jié)點最多有兩個孩子，而BTree或B+Tree可以有很多孩子，即一個節(jié)點可以存儲很多關鍵字
  • 遍歷時，每次讀入內存的key值更多，相對來說I/O就降低，因此BTree或B+Tree性能更好一些

<2> 為什么不用BTree而用B+Tree做索引？

總結：

  • B+Tree 非葉子節(jié)點只存儲 key 值和孩子指針，不存儲key對應的數(shù)據(jù)，因此相對于BTree節(jié)點可以存儲更多的關鍵字和數(shù)據(jù)量，每次讀入內存的key值就更多，相對來說I/O就降低。
  • B+Tree 的葉子節(jié)點存儲的是全量數(shù)據(jù)，并且有序，只需要遍歷葉子節(jié)點就可以對所有的 key 進行掃描，查詢范圍時效率更高。

3. 歸納總結

B-Tree索引.png

什么是哈希索引？

哈希索引基于Hash表實現(xiàn)，必須精確匹配索引所有列的查詢才有效。

Hash表中的key：對于每一行數(shù)據(jù)，對所有索引列計算的一個哈希碼
Hash表中的value：指向每個數(shù)據(jù)行的指針

<1> 哈希索引的數(shù)據(jù)結構：

哈希索引數(shù)據(jù)結構.png

<2> 哈希索引的限制：

  • 哈希索引只包含哈希值和行指針，不存儲字段值，不能使用索引中的值來避免讀取行
  • 哈希索引數(shù)據(jù)不是按照索引值順序存儲的，無法用于排序
  • 哈希索引使用全部索引列來計算哈希值，不支持部分索引列匹配查找
  • 哈希索引只支持等值比較查詢：=，in()，<=>，不支持任何范圍查找
  • 當出現(xiàn)哈希沖突時，存儲引擎必須遍歷鏈表中所有行指針，逐行比較

什么是自適應哈希索引？

InnoDB引起有一個特殊的功能叫做“自適應哈希索引”，當索引值被使用的非常頻繁時，它會在內存中基于B-Tree索引之上再創(chuàng)建一個哈希索引，這樣也就有了哈希索引的一些優(yōu)點，如快速的哈希查找。

什么是聚簇索引，非聚簇索引？

聚簇索引是將數(shù)據(jù)存放在索引樹的葉子節(jié)點上，找到葉子節(jié)點就可以讀取這行數(shù)據(jù)。InnoDB存儲引擎的索引方式就是聚簇索引。一個表只能有一個聚簇索引，一般會根據(jù)主鍵或者唯一索引，或者以數(shù)據(jù)庫內部生成的rowid為主鍵，來建立聚簇索引。

非聚簇索引是在索引樹的葉子節(jié)點上存放數(shù)據(jù)的地址，找到該地址后，需要到磁盤中查詢一次才能獲取到數(shù)據(jù)。MyISAM存儲引擎的索引方式就是非聚簇索引，只在索引樹的葉子節(jié)點上存放地址。

聚簇索引也被稱為主鍵索引，非聚簇索引也被稱為二級索引。

對比上面的索引分類，聚簇索引不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式：數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起。

聚簇與非聚簇索引.png

<1> InnoDB的聚簇索引

InnoDB的聚簇索引本質：是在同一個結構中保存了B-Tree索引和數(shù)據(jù)行。

InnoDB通過主鍵聚集數(shù)據(jù)：

  • 主鍵為聚簇索引
  • 若沒有主鍵，選擇一個唯一非空索引代替
  • 若沒有唯一非空索引，InnoDB會隱式定義一個主鍵來作為聚簇索引

<2> 聚簇索引和非聚簇索引的區(qū)別？

InnoDB支持聚簇索引，MyISAM不支持聚簇索引

對比InnoDB和MyISAM的數(shù)據(jù)分布：

InnoDB數(shù)據(jù)分布.png
MyISAM數(shù)據(jù)分布.png

區(qū)別總結如下：

  • InnoDB聚簇索引通過主鍵來聚集數(shù)據(jù)；若沒有主鍵，則選擇唯一非空索引，若沒有唯一非空索引，則隱式生成一個主鍵
  • InnoDB聚簇索引聚集的就是表的數(shù)據(jù)行，每個葉子節(jié)點包含了主鍵值，事務ID，事務回滾指針以及數(shù)據(jù)行所有的剩余列。
  • InnoDB二級索引(非聚簇索引)不聚集數(shù)據(jù)，葉子節(jié)點存儲的也不是數(shù)據(jù)的地址(即指向數(shù)據(jù)行物理位置的指針)，而是數(shù)據(jù)行的主鍵值。因此二級索引查找數(shù)據(jù)行需要兩次索引查找。
  • MyISAM不支持聚簇索引，主鍵索引和二級索引都不聚集數(shù)據(jù)，葉子節(jié)點存儲的是數(shù)據(jù)的地址。

<3> 聚簇索引的優(yōu)缺點

聚簇索引.png

什么是覆蓋索引？

覆蓋索引是指查詢的列正好是索引的一部分，那么它直接從索引上獲取數(shù)據(jù)，而不需要到磁盤中查找數(shù)據(jù)，這種查詢效率非常高。

比如：有一個用戶表user，在用戶名username上建立了索引，現(xiàn)在要查詢user表的所有用戶名：select username from user;
索引的葉子節(jié)點中包含了username的值，不需要再回表查數(shù)據(jù)行，直接取索引列的值即可，也就是說索引覆蓋了要查詢的username字段的值。

如下圖結構所示：username上建立了索引，索引覆蓋了要查詢的username字段的值

索引覆蓋username字段.png

覆蓋索引其它相關總結如下：

覆蓋索引.png

主鍵索引，唯一索引，普通索引

主鍵索引：不允許重復，不允許有空值，是唯一索引的一種特例
唯一索引：列值不允許重復，但允許有空值(NULL)。
普通索引：最基本的索引，沒有任何限制，支持上面提到的三種創(chuàng)建索引方式。

主鍵唯一普通索引.png

持續(xù)更新......

最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容