【愚公系列】2022年01月 Mysql數據庫-MySQL索引

二、MySQL索引

我們之前學習過集合，其中的ArrayList集合的特點之一就是有索引。那么有索引會帶來哪些好處呢？

沒錯，查詢數據快！我們可以通過索引來快速查找到想要的數據。那么對于我們的MySQL數據庫中的索引功能也是類似的！

MySQL數據庫中的索引：是幫助MySQL高效獲取數據的一種數據結構！所以，索引的本質就是數據結構。

在表數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式指向數據， 這樣就可以在這些數據結構上實現高級查找算法，這種數據結構就是索引。

一張數據表，用于保存數據。 一個索引配置文件，用于保存索引，每個索引都去指向了某一個數據(表格演示)

舉例，無索引和有索引的查找原理

功能分類

普通索引： 最基本的索引，它沒有任何限制。

唯一索引：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值組合必須唯一。

主鍵索引：一種特殊的唯一索引，不允許有空值。一般在建表時同時創建主鍵索引。

組合索引：顧名思義，就是將單列索引進行組合。

外鍵索引：只有InnoDB引擎支持外鍵索引，用來保證數據的一致性、完整性和實現級聯操作。

全文索引：快速匹配全部文檔的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

結構分類

B+Tree索引 ：MySQL使用最頻繁的一個索引數據結構，是InnoDB和MyISAM存儲引擎默認的索引類型。

Hash索引 : MySQL中Memory存儲引擎默認支持的索引類型。

數據準備

-- 創建db12數據庫 CREATE DATABASE db12; -- 使用db12數據庫 USE db12; -- 創建student表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), age INT, score INT ); -- 添加數據 INSERT INTO student VALUES (NULL,'張三',23,98),(NULL,'李四',24,95), (NULL,'王五',25,96),(NULL,'趙六',26,94),(NULL,'周七',27,99);

創建索引

注意：如果一個表中有一列是主鍵，那么就會默認為其創建主鍵索引！(主鍵列不需要單獨創建索引)

-- 標準語法 CREATE [UNIQUE|FULLTEXT] INDEX 索引名稱 [USING 索引類型] -- 默認是B+TREE ON 表名(列名...); -- 為student表中姓名列創建一個普通索引 CREATE INDEX idx_name ON student(NAME); -- 為student表中年齡列創建一個唯一索引 CREATE UNIQUE INDEX idx_age ON student(age);

查看索引

-- 標準語法 SHOW INDEX FROM 表名; -- 查看student表中的索引 SHOW INDEX FROM student;

alter語句添加索引

-- 普通索引 ALTER TABLE 表名 ADD INDEX 索引名稱(列名); -- 組合索引 ALTER TABLE 表名 ADD INDEX 索引名稱(列名1,列名2,...); -- 主鍵索引 ALTER TABLE 表名 ADD PRIMARY KEY(主鍵列名); -- 外鍵索引(添加外鍵約束，就是外鍵索引) ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY (本表外鍵列名) REFERENCES 主表名(主鍵列名); -- 唯一索引 ALTER TABLE 表名 ADD UNIQUE 索引名稱(列名); -- 全文索引(mysql只支持文本類型) ALTER TABLE 表名 ADD FULLTEXT 索引名稱(列名); -- 為student表中name列添加全文索引 ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name); -- 查看student表中的索引 SHOW INDEX FROM student;

刪除索引

-- 標準語法 DROP INDEX 索引名稱 ON 表名; -- 刪除student表中的idx_score索引 DROP INDEX idx_score ON student; -- 查看student表中的索引 SHOW INDEX FROM student;

-- 創建product商品表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id NAME VARCHAR(10), -- 商品名稱 price INT -- 商品價格 ); -- 定義存儲函數，生成長度為10的隨機字符串并返回 DELIMITER $ CREATE FUNCTION rand_string() RETURNS VARCHAR(255) BEGIN DECLARE big_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ'; DECLARE small_str VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 1; WHILE i <= 10 DO SET small_str =CONCAT(small_str,SUBSTRING(big_str,FLOOR(1+RAND()*52),1)); SET i=i+1; END WHILE; RETURN small_str; END$ DELIMITER ; -- 定義存儲過程，添加100萬條數據到product表中 DELIMITER $ CREATE PROCEDURE pro_test() BEGIN DECLARE num INT DEFAULT 1; WHILE num <= 1000000 DO INSERT INTO product VALUES (NULL,rand_string(),num); SET num = num + 1; END WHILE; END$ DELIMITER ; -- 調用存儲過程 CALL pro_test(); -- 查詢總記錄條數 SELECT COUNT(*) FROM product; -- 查詢product表的索引 SHOW INDEX FROM product; -- 查詢name為OkIKDLVwtG的數據 (0.049) SELECT * FROM product WHERE NAME='OkIKDLVwtG'; -- 通過id列查詢OkIKDLVwtG的數據 (1毫秒) SELECT * FROM product WHERE id=999998; -- 為name列添加索引 ALTER TABLE product ADD INDEX idx_name(NAME); -- 查詢name為OkIKDLVwtG的數據 (0.001) SELECT * FROM product WHERE NAME='OkIKDLVwtG'; /* 范圍查詢 */ -- 查詢價格為800~1000之間的所有數據 (0.052) SELECT * FROM product WHERE price BETWEEN 800 AND 1000; /* 排序查詢 */ -- 查詢價格為800~1000之間的所有數據,降序排列 (0.083) SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC; -- 為price列添加索引 ALTER TABLE product ADD INDEX idx_price(price); -- 查詢價格為800~1000之間的所有數據 (0.011) SELECT * FROM product WHERE price BETWEEN 800 AND 1000; -- 查詢價格為800~1000之間的所有數據,降序排列 (0.001) SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;

索引是在MySQL的存儲引擎中實現的，所以每種存儲引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引類型。這里我們主要介紹InnoDB引擎的實現的B+Tree索引。

B+Tree是一種樹型數據結構，是B-Tree的變種。通常使用在數據庫和操作系統中的文件系統，特點是能夠保持數據穩定有序。我們逐步的來了解一下。

系統從磁盤讀取數據到內存時是以磁盤塊（block）為基本單位的

位于同一個磁盤塊中的數據會被一次性讀取出來，而不是需要什么取什么。

InnoDB存儲引擎中有頁（Page）的概念，頁是其磁盤管理的最小單位。InnoDB存儲引擎中默認每個頁的大小為16KB。

InnoDB引擎將若干個地址連接磁盤塊，以此來達到頁的大小16KB，在查詢數據時如果一個頁中的每條數據都能有助于定位數據記錄的位置，這將會減少磁盤I/O次數，提高查詢效率。

BTree結構的數據可以讓系統高效的找到數據所在的磁盤塊。為了描述BTree，首先定義一條記錄為一個二元組[key, data] ，key為記錄的鍵值，對應表中的主鍵值，data為一行記錄中除主鍵外的數據。對于不同的記錄，key值互不相同。BTree中的每個節點根據實際情況可以包含大量的關鍵字信息和分支，如下圖所示為一個3階的BTree：

根據圖中結構顯示，每個節點占用一個盤塊的磁盤空間，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指針，指針存儲的是子節點所在磁盤塊的地址。兩個關鍵詞劃分成的三個范圍域對應三個指針指向的子樹的數據的范圍域。以根節點為例，關鍵字為17和35，P1指針指向的子樹的數據范圍為小于17，P2指針指向的子樹的數據范圍為17~35，P3指針指向的子樹的數據范圍為大于35。

查找順序：

模擬查找15的過程 : 1.根節點找到磁盤塊1，讀入內存。【磁盤I/O操作第1次】 比較關鍵字15在區間（<17），找到磁盤塊1的指針P1。 2.P1指針找到磁盤塊2，讀入內存。【磁盤I/O操作第2次】 比較關鍵字15在區間（>12），找到磁盤塊2的指針P3。 3.P3指針找到磁盤塊7，讀入內存。【磁盤I/O操作第3次】 在磁盤塊7中找到關鍵字15。 -- 分析上面過程，發現需要3次磁盤I/O操作，和3次內存查找操作。 -- 由于內存中的關鍵字是一個有序表結構，可以利用二分法查找提高效率。而3次磁盤I/O操作是影響整個BTree查找效率的決定因素。BTree使用較少的節點個數，使每次磁盤I/O取到內存的數據都發揮了作用，從而提高了查詢效率。

B+Tree是在BTree基礎上的一種優化，使其更適合實現外存儲索引結構，InnoDB存儲引擎就是用B+Tree實現其索引結構。

從上一節中的BTree結構圖中可以看到每個節點中不僅包含數據的key值，還有data值。而每一個頁的存儲空間是有限的，如果data數據較大時將會導致每個節點（即一個頁）能存儲的key的數量很小，當存儲的數據量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁盤I/O次數，進而影響查詢效率。在B+Tree中，所有數據記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存儲key值信息，這樣可以大大加大每個節點存儲的key值數量，降低B+Tree的高度。

B+Tree相對于BTree區別：

非葉子節點只存儲鍵值信息。

所有葉子節點之間都有一個連接指針。

數據記錄都存放在葉子節點中。

將上一節中的BTree優化，由于B+Tree的非葉子節點只存儲鍵值信息，假設每個磁盤塊能存儲4個鍵值及指針信息，則變成B+Tree后其結構如下圖所示：

通常在B+Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即數據節點）之間是一種鏈式環結構。因此可以對B+Tree進行兩種查找運算：

【有范圍】對于主鍵的范圍查找和分頁查找

【有順序】從根節點開始，進行隨機查找

實際情況中每個節點可能不能填充滿，因此在數據庫中，B+Tree的高度一般都在2~4層。MySQL的InnoDB存儲引擎在設計時是將根節點常駐內存的，也就是說查找某一鍵值的行記錄時最多只需要1~3次磁盤I/O操作。

索引的設計可以遵循一些已有的原則，創建索引的時候請盡量考慮符合這些原則，便于提升索引的使用效率，更高效的使用索引。

創建索引時的原則

對查詢頻次較高，且數據量比較大的表建立索引。

使用唯一索引，區分度越高，使用索引的效率越高。

索引字段的選擇，最佳候選列應當從where子句的條件中提取，如果where子句中的組合比較多，那么應當挑選最常用、過濾效果最好的列的組合。

使用短索引，索引創建之后也是使用硬盤來存儲的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構成索引的字段總長度比較短，那么在給定大小的存儲塊內可以存儲更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率。

索引可以有效的提升查詢數據的效率，但索引數量不是多多益善，索引越多，維護索引的代價自然也就水漲船高。對于插入、更新、刪除等DML操作比較頻繁的表來說，索引過多，會引入相當高的維護代價，降低DML操作的效率，增加相應操作的時間消耗。另外索引過多的話，MySQL也會犯選擇困難病，雖然最終仍然會找到一個可用的索引，但無疑提高了選擇的代價。

聯合索引的特點

在mysql建立聯合索引時會遵循最左前綴匹配的原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配，

對列name列、address和列phone列建一個聯合索引

ALTER TABLE user ADD INDEX index_three(name,address,phone);

聯合索引index_three實際建立了(name)、(name,address)、(name,address,phone)三個索引。所以下面的三個SQL語句都可以命中索引。

SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '張三'; SELECT * FROM user WHERE name = '張三' AND address = '北京'; SELECT * FROM user WHERE name = '張三';

上面三個查詢語句執行時會依照最左前綴匹配原則，檢索時分別會使用索引

(name,address,phone) (name,address) (name)

進行數據匹配。

索引的字段可以是任意順序的，如：

-- 優化器會幫助我們調整順序，下面的SQL語句都可以命中索引 SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '張三';

Mysql的優化器會幫助我們調整where條件中的順序，以匹配我們建立的索引。

聯合索引中最左邊的列不包含在條件查詢中，所以根據上面的原則，下面的SQL語句就不會命中索引。

-- 聯合索引中最左邊的列不包含在條件查詢中，下面的SQL語句就不會命中索引 SELECT * FROM user WHERE address = '北京' AND phone = '12345';

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。