目錄

1.索引的概念;

2.索引的分類;

3.索引的結(jié)構(gòu)和原理;

4.索引的使用策略;

一.概念

在解釋數(shù)據(jù)庫索引之前, 我們先來回憶下平時想要查詢書中的某個章節(jié),一般情況下,我們不會漫無目的的翻閱直到找到想要找到的內(nèi)容, 而是會查找書的目錄,獲取到章節(jié)的頁碼后直接翻到對應(yīng)的頁碼; 其實在這里目錄就充當著書的索引, 來加快查詢的速度.
MySQL官方對索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu).

二.常見索引分類

1.1唯一索引和普通索引

普通索引：是MySQL中的基本索引類型九串，允許在定義索引的列中插入重復(fù)值和空值，用來提高數(shù)據(jù)庫的查找速度寺鸥。
唯一索引：索引列的值必須唯一猪钮，但允許有NULL，如果列是字符類型胆建，插入多條為null的數(shù)據(jù)不會觸發(fā)唯一索引烤低，但是如果是多條為空字符的會觸發(fā)唯一索引。如果是組合索引笆载，則列值的組合必須唯一扑馁。
主鍵索引：是一種特殊的唯一索引，不允許有空值

主鍵和唯一索引的區(qū)別:
主鍵:
1.用于唯一標識表中的每一條數(shù)據(jù)凉驻，不能重復(fù)腻要，不能為空;
2.一般使用Long類型搭配auto_increment;(設(shè)置自增長的字段必須是主鍵，也就是說只有設(shè)為主鍵的列才可以設(shè)置為自增長).
唯一索引:
1.用于唯一標識表中的每一條數(shù)據(jù)涝登，不能重復(fù)雄家，可以為空;
2.一張表中只能有一個主鍵，但是一張表中可以有多個唯一鍵;

1.2創(chuàng)建示例

建表時添加:
CREATE TABLE `test`(
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) NOT NULL COMMENT '名稱',
    `code` varchar(32) NOT NULL COMMENT '編碼',
    `order_id` varchar(64) NOT NULL COMMENT '單號',
    `inserttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入庫時間',
    `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),   ---------------------------->主鍵
    KEY `idx_inserttime` (`inserttime`),---->普通索引(單列索引)
    UNIQUE KEY `idx_order_id` (`order_id`),----->唯一索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='測試表';

在已有表上添加:
主鍵:
ALTER TABLE `test ` ADD PRIMARY KEY(`id`);
普通索引:
ALTER TABLE `test ` ADD KEY `idx_inserttime`(`inserttime `);
唯一索引:
ALTER TABLE `test ` ADD UNIQUE KEY `idx_order_id `(`order_id `);

2.1單列索引和組合索引

單列索引：顧名思義單列索引就是以某一個字段來創(chuàng)建的索引,可以是普通索引,也可以是唯一索引胀滚。
組合索引：以2個或2個以上字段聯(lián)合創(chuàng)建的索引稱為組合索引或者多列索引趟济。

建表時添加:
CREATE TABLE `test`(
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL COMMENT '名稱',
    `code` varchar(20) NOT NULL COMMENT '編碼',
    `order_id` varchar(64) NOT NULL COMMENT '單號',
    `inserttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入庫時間',
    `updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),   ---------------------------->主鍵
    KEY `idx_name_order_id` (`name`,`order_id`),---->組合索引(普通索引)
    UNIQUE KEY `idx_name_code` (`name`,`code`),----->組合索引(唯一索引)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='測試表';

在已有表上添加:
組合索引:
ALTER TABLE `test ` ADD KEY `idx_name_order_id`(`name`,`order_id`);
單列索引:
ALTER TABLE `test ` ADD KEY `idx_order_id `(`order_id `);

三.索引的結(jié)構(gòu)和原理

3.1磁盤結(jié)構(gòu)以及數(shù)據(jù)存儲

在了解索引結(jié)構(gòu)前,我們先來看下正常情況下我們mysql數(shù)據(jù)庫表中的數(shù)據(jù)是怎么存儲在磁盤中的。
其中磁頭是用于向磁盤讀寫信息的工具咽笼，磁盤上的一圈圈的圓周被稱之為磁道顷编，每圈磁道上的扇形小區(qū)域被稱為扇區(qū)(以下稱之為block)，扇區(qū)中又存在著很多存儲單元用于存儲比特信息褐荷。每一個block大小是一樣的,假設(shè)是16384byte;操作系統(tǒng)讀取數(shù)據(jù)都是按照block(也稱之為一頁)為單位進行勾效。磁頭可以旋轉(zhuǎn)伸縮來定位一個block。

磁盤如何存儲數(shù)據(jù)庫數(shù)據(jù):
在上面test表中:
id --> bigint --> 8byte
name --> varchar(20) --> 20byte
code --> varchar(20) --> 20byte
order_id --> varchar(64) --> 64byte
inserttime --> timestamp --> 8byte
updatetime --> timestamp --> 8byte
所以一條數(shù)的大小為128byte;
假設(shè)總共有1000條這樣的數(shù)據(jù),那么存儲這些數(shù)據(jù)需要128 * 1000 / 16384 = 7.8 個block, 假設(shè)當前沒有設(shè)置索引的情況下查詢一條記錄,最多需要查找8個block叛甫。下面來看下索引是如何提高查詢速度的;

索引的數(shù)據(jù)結(jié)構(gòu)
提到數(shù)據(jù)結(jié)構(gòu),就不得不提存儲引擎, 因為在不同的存儲引擎中索引的結(jié)構(gòu)可能是不相同的, 最常見到的存儲引擎有MyISAM 和 InnoDB, 在這兩種存儲引擎中索引的數(shù)據(jù)結(jié)構(gòu)都是B+樹,但在存儲的過程中又稍有不同, 在MyISAM的主鍵索引B+樹的葉子節(jié)點中存儲的是該條數(shù)據(jù)的地址,0x....;而在InnoDB的主鍵索引B+樹的葉子節(jié)點中存儲的是真正的數(shù)據(jù).

MyISAM
從上面我們可以知道數(shù)據(jù)順序的寫在磁盤上, 占用8個block; 現(xiàn)在我們使用id來創(chuàng)建一個索引, 并且還把當前這個id對應(yīng)的數(shù)據(jù)在磁盤中的位置也記錄下來(下面用pointer來描述),這樣test表中的每一行數(shù)據(jù)都會有一條這樣的記錄层宫。

結(jié)構(gòu).png

那在磁盤上存儲這張索引表,需要占據(jù)多少個block呢?
id 8byte
pointer 8byte
所以一條數(shù)據(jù)為16byte,1000條需要使用 16 * 1000 / 16384 = 0.9 個block; 所以在這樣的 情況下查找一條test表中的數(shù)據(jù)最多只需要1次存儲索引數(shù)據(jù)的block數(shù)據(jù)讀入和1次存儲test表數(shù)據(jù)的block數(shù)據(jù)讀入就行了, 相比于沒有索引的情況效率提升了很多

但是在上面的操作中如果數(shù)據(jù)量大的情況還是需要很多次IO,在MyISAM中使用了B+樹的數(shù)據(jù)結(jié)構(gòu),即只有在葉子節(jié)點才存儲行數(shù)據(jù)對應(yīng)的地址(在InnoDB中存儲真正的行數(shù)據(jù))，而非葉子節(jié)點里面的內(nèi)容其實是鍵值和指向數(shù)據(jù)頁的指針其监。

InnoDB
存儲結(jié)構(gòu)下的B+數(shù)大概樣子:

B+.png

下面來算一下一個2層的B+數(shù)能存儲多少數(shù)據(jù),一個block也就是一頁是16384byte, id 8byte, pointer 8byte 總共為16byte,所以第一層最多可存儲16384 / (8 + 8) = 1024條數(shù)據(jù), 所以第二層就對應(yīng)1024個block的首地址, 一個block可以存儲16384 / 128 = 128條數(shù)據(jù), 所以一個2層的B+樹可以存儲1024 * 128 = 131072條數(shù)據(jù),也就是說十幾萬的數(shù)據(jù)量,如果要是查詢的話最多2次IO就可以了;
一個3層的B+樹就可以存儲1024 * 1024 *128條數(shù)據(jù), 可見效率相當之高萌腿。

需要注意的是上面是基于主鍵索引的, 也就是主鍵索引的B+數(shù)中葉子節(jié)點上存儲的是真正的數(shù)據(jù), 而普通索引,如用order_id創(chuàng)建的索引,其葉子節(jié)點上存儲的不是整條的數(shù)據(jù),而是數(shù)據(jù)對應(yīng)的主鍵值, 查詢到主鍵值后再從主鍵的B+數(shù)中獲取到真正的數(shù)據(jù)。