wjq-9082

公告

一文教你搞懂數據庫索引，再也不怕面試官問MySQL索引了

本文主要介紹MySQL數據庫的索引，從底層了解兩種數據引擎的索引原理。

1，索引介紹

1.1 索引的概念

索引是一個單獨的、存儲在磁盤上的數據庫結構，它們包含著對數據表里所有記錄的引用指針。使用索引用于快速找出在某個或多個列中有一特定值的行，所有MySQL列類型都可以被索引，對相關列使用索引是提高查詢操作速度的最佳途徑。
MySQL索引的建立對于MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度。
索引的創建條件：創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)，而不是在select的字段中，實際上，索引也是一張“表”，該表保存了主鍵與索引字段，并指向實體表的記錄，雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件，建立索引會占用磁盤空間的索引文件。說白了索引就是用來提高速度的，但是就需要維護索引造成資源的浪費，所以合理的創建索引是必要的。

1.2 索引的優缺點

1.2.1 優點

索引的存在減小了服務器查詢數據時需要掃描的數據量，大大加快數據的檢索速度，降低了數據庫的IO成本。
索引可以幫助服務器對數據進行排序，降低了CPU的消耗
索引對于InnoDB（對索引支持行級鎖）非常重要，因為它可以讓查詢鎖更少的元組，提高了表訪問并發性。
關于InnoDB、索引和鎖：InnoDB在二級索引上使用共享鎖（讀鎖），但訪問主鍵索引需要排他鎖（寫鎖）
通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性。
在使用分組和排序子句進行數據檢索時（如order by），可以顯著減少查詢中分組和排序的時間。

例子：我們可以在數據庫中插入100萬條數據進行測試索引的作用

--創建表
CREATE TABLE app_user (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(50)  DEFAULT '' COMMENT '用戶昵稱',
  `email` VARCHAR(50)  NOT NULL COMMENT '用戶郵箱',
  `phone` VARCHAR(20)  DEFAULT '' COMMENT '手機號',
  `gender` TINYINT(4)  UNSIGNED DEFAULT '0' COMMENT '性別（0：男  1：女）',
  `password` VARCHAR(100)  NOT NULL COMMENT '密碼',
  `age` TINYINT(4)  DEFAULT '0' COMMENT '年齡',
  `create_time` DATETIME  DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用戶表'

-- 插入100萬數據b (函數)

DELIMITER $$ -- 寫函數之前必須要寫，標志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
  DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  WHILE i<num DO
    INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
    VALUES(CONCAT('用戶',i),'123345@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i = i+1;
  END WHILE;
  RETURN i;
END;

-- 執行函數
SELECT mock_data();

SELECT * FROM app_user;

-- 函數中間的插入腳本
INSERT INTO app_user(`name`,
`email`,
`phone`,
`gender`,
`password`,
`age`)
VALUES(CONCAT('用戶X'),
'123345@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100));

測試

-- 加索引前
SELECT * FROM app_user WHERE `name` = '用戶9999'; -- 0.440 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用戶9999';

-- 創建索引
-- id_表名_字段名  索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`);
 -- 加索引后
SELECT * FROM app_user WHERE `name` = '用戶9999'; -- 0.002 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用戶9999';

加索引前

加索引后

可以看出執行成功時間從0.795s加快到了0.002s。

1.2.2 缺點

創建索引和維護索引要耗費時間和磁盤空間，這種消耗隨著數據量的增加而增加。
對表中的數據進行增、刪、改的時候，索引也要動態的維護，這就降低了更新表的效率。
如果某個數據列包含許多重復的內容，或者數據量很小，則會造成資源的浪費。

2 ，索引的類型

2.1 邏輯分類

主鍵索引：一張表只能有一個主鍵索引，且索引列中的值必須是唯一的，不允許有空值。

唯一索引：一張表可以有多個唯一索引，但是索引列不允許重復，可以為空

普通索引：一張表可以創建多個普通索引，一個普通索引可以包含多個字段，允許數據重復，允許為空

全文索引：只能在文本類型CHAR,VARCHAR,TEXT類型字段上創建全文索引。字段長度比較大時，如果創建普通索引，在進行like模糊查詢時效率比較低，這時可以創建全文索引。

單例索引：一張表可以有多個單例索引，但是索引只能包含一個列。

組合索引：一個組合索引包含兩個或兩個以上的列。查詢的時候遵循 mysql 組合索引的 “最左前綴”原則，即使用 where 時條件要按照建立索引的時候字段的排列方式放置索引才會生效。

2.2 物理分類

聚簇索引和非聚簇索引（有時也稱輔助索引或二級索引）：

2.2.1 聚簇索引

不是單獨的一種索引類型，而是一種數據存儲方式

目的：為了提高某個屬性(或屬性組)的查詢速度，把這個或這些屬性(稱為聚簇碼)上具有相同值的元組集中存放在連續的物理塊。

實現：是依靠B+樹來實現的，根據表的主鍵構造一棵B+樹且B+樹葉子節點存放的都是表的行記錄數據時，方可稱該主鍵索引為聚簇索引。

聚簇索引的順序就是數據的物理存儲順序，并且索引與數據放在一塊，通過索引可以直接獲取數據，一個數據表中僅有一個聚簇索引。

優點：

數據訪問更快，因為聚簇索引將索引和數據保存在同一個B+樹中，找到索引也就找到了數據。
聚簇索引對于主鍵的排序查找和范圍查找速度非常快

缺點：

插入速度嚴重依賴于插入順序，按照主鍵的順序插入是最快的方式，否則將會出現頁分裂，嚴重影響性能。因此，對于InnoDB表，我們一般都會定義一個自增的ID列為主鍵。
更新主鍵的代價很高

2.2.2 非聚簇索引

非聚簇索引：索引順序與數據物理排列順序無關，索引文件與數據是分開存放。

注：InnoDB和MyISAM存儲引擎都默認使用B+樹結構存儲索引，但是只有InnoDB的主鍵索引才是聚簇索引，InnoDB中的輔助索引以及MyISAM使用的都是非聚簇索引。

2.2.3 聚簇索引和非聚簇索引的區別

聚簇索引的葉子節點存放的是數據行（主鍵值也是行內數據），支持覆蓋索引；而非聚簇索引的葉子節點存放的是主鍵值或指向數據行的指針。
由于葉子節點(數據頁)只能按照一棵B+樹排序，故一張表只能有一個聚簇索引。非聚簇索引的存在不影響聚簇索引中數據的組織，所以一張表可以有多個輔助索引。

3，索引的數據結構

3.1 Hash表

哈希索引就是采用一定的哈希算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找，只需一次哈希算法即可立刻定位到相應的位置，速度非常快。我們使用Hash表存儲表數據Key可以存儲索引列，Value可以存儲行記錄或者行磁盤地址。Hash表在等值查詢時效率很高，時間復雜度為O(1)；但是不支持范圍快速查找，范圍查找時還是只能通過掃描全表方式。

3.2 B-TREE

B樹是在二叉樹的基礎出現的，MySQL的數據是存儲在磁盤文件中的，查詢處理數據時，需要先把磁盤中的數據加載到內存中，磁盤IO 操作非常耗時，B樹的出現就是要盡量減少磁盤 IO 操作。正常訪問二叉樹的每一層就會發生一次IO，如果想要減少磁盤IO操作，就需要盡量降低樹的高度。

B樹，又叫多路搜索樹，樹高一層意味著多一次的磁盤I/O，下圖是3階B樹，將每個節點存儲多個元素以降低樹的高度。

特征：

B樹的每個節點存儲不止倆個元素，每個節點有多個分叉
節點中的元素包含鍵值和數據，節點中的鍵值從大到小排列。
任何一個關鍵字出現且只出現在一個結點中，也就是父節點當中的元素不會出現在子節點中。
所有的葉子結點都位于同一層，葉節點具有相同的深度，葉節點之間沒有指針連接

3.3 B+TREE

B+樹是對B樹的進一步優化，同樣是一種多路搜索樹。B+樹和B樹最主要的區別在于非葉子節點是否存儲數據

B+樹特征：

只有葉子節點才會存儲數據，非葉子節點只存儲鍵值。葉子節點之間使用雙向指針連接，最底層的葉子節點形成了一個雙向有序鏈表。
不可能在非葉子結點命中，因為非葉子節點只存鍵值
非葉子結點相當于是葉子結點的索引（稀疏索引），葉子結點相當于是存儲（關鍵字）數據的數據層；
每一個葉子節點都包含指向下一個葉子節點的指針，從而方便葉子節點的范圍遍歷。

4，InnoDB和MyISAM的索引實現

創建表：

REATE TABLE `user`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

--插入數據
insert into `user` values('15','BOb','34');
insert into `user` values('18','Ailce','24');
insert into `user` values('20','Jim','5');

4.1 InnoDB索引實現

InnoDB使用B+TREE存儲數據，每個InnoDB表都有一個聚簇索引，其它索引均為非聚簇索引。當一個表沒有創建主鍵索引時，InnoDB會自動創建一個ROWID字段來構建聚簇索引。InnoDB表的索引和數據是存儲在一起的，其全部放在.idb中

在表上定義主鍵PRIMARY KEY，InnoDB將主鍵索引用作聚簇索引。
如果表沒有定義主鍵，InnoDB會選擇第一個不為NULL的唯一索引列用作聚簇索引。
如果以上兩個都沒有，InnoDB 會使用一個6 字節長整型的隱式字段 ROWID字段構建聚簇索引。該ROWID字段會在插入新行時自動遞增。

4.1.1 聚簇索引（主鍵索引）

葉子節點包含了完整的數據記錄，這就是聚簇索引。因為InnoDB的數據文件（.idb）按主鍵聚集，所以InnoDB必須有主鍵（MyISAM可以沒有）。

分析：

B+樹單個葉子節點內的行數據按主鍵順序排列，物理空間是連續的（聚簇索引的數據的物理存放順序與索引順序是一致的）；
葉子節點之間是通過指針連接，相鄰葉子節點的數據在邏輯上也是連續的（根據主鍵值排序）。

4.1.2 非聚簇索引（輔助索引）

除聚簇索引之外的所有索引都稱為輔助索引，InnoDB的輔助索引只會存儲主鍵值而非磁盤地址。輔助索引訪問數據總是需要二次查找，首先通過輔助索引找到主鍵值，然后到主鍵索引樹中通過主鍵值找到數據行。

注：InnoDB中主鍵不宜定義太大，因為輔助索引也會包含主鍵列，如果主鍵定義的比較大，其他索引也將很大。

4.2 MyIsam索引實現

MyISAM也使用B+Tree作為索引結構，但具體實現方式卻與InnoDB截然不同。MyISAM使用的都是非聚簇索引。其數據文件和索引文件是分開存儲的，.MYD表數據文件 .MYI`表索引文件。葉子節點中存儲的鍵值為索引列的值，數據為索引所在行的磁盤地址。

4.2.1 主鍵索引

從索引原理圖可以看出葉子節點的存放的是數據記錄的地址，索引和行數據記錄是沒有保存在一起的，所以進一步驗證了MyISAM的主鍵索引是非聚簇索引。

注：在 MyISAM 中,輔助索引和主鍵索引的結構是一樣的，沒有任何區別，葉子節點的數據存儲的都是行記錄的磁盤地址。只是主鍵索引的鍵值是唯一的，而輔助索引的鍵值可以重復。

5，重要知識點

5.1 最左前綴原理

最左前綴匹配原則和聯合索引的索引存儲結構和檢索方式是有關系的。在MySQL建立聯合索引時會遵守最左前綴匹配原則，即最左優先（查詢條件精確匹配索引的左邊連續一列或幾列，則構建對應列的組合索引樹），在檢索數據時也從聯合索引的最左邊開始匹配，mysql會一直向右匹配直至遇到范圍查詢(>、<、between、like)就停止匹配。

5.2 回表

查詢的列數據作為索引樹的鍵值，直接在索引樹中得到反饋（存在于索引節點），不用遍歷如InnoDB中的葉子節點（存放數據表各行數據）就可得到查詢的數據（不用回表）。如果未在索引樹中得到反饋，就需要進行回表操作，從InnoDB中的葉子節點得到查詢的數據，即為回表。

5.3 覆蓋索引

覆蓋索引并不是說是索引結構，覆蓋索引是一種很常用的優化手段。比如在InnoDB中的主鍵索引就是聚簇索，輔助索引是非聚簇索引。如果我們使用輔助索引時，當我們拿到主鍵值時，仍需要根據主鍵值到主鍵索引中再次獲得完整的數據。但是當搜索的索引鍵中的字段恰好是查詢的字段（或是組合索引鍵中的其它字段）時，我們就可以直接返回，不需要回表了，這就是覆蓋索引。

5.4 IO操作

在我們讀取數據時，需要先把磁盤中的數據加載到內存中，這就需要進行磁盤IO 操作，磁盤IO 操作非常耗時，因此索引的出現就是為了盡量減少磁盤 IO 操作。
下面以MyISAM的主鍵查詢為例子，介紹一下具體的磁盤操作：

作者：王二黑_Leon

歡迎任何形式的轉載，但請務必注明出處。
限于本人水平，如果文章和代碼有表述不當之處，還請不吝賜教。
本文章僅作為自學所用。

posted on 2022-03-26 20:28 王二黑_Leon 閱讀(171) 評論(0) 收藏舉報

刷新頁面返回頂部