索引概述

MySQL官方對(duì)索引的定義為：索引（index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）。在數(shù)據(jù)之外线梗，數(shù)據(jù)庫系統(tǒng)還維護(hù)者滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)怠益，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)瘾婿， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引抢呆。如下面的示意圖所示 :

左邊是數(shù)據(jù)表笛谦，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）恳邀。為了加快Col2的查找灶轰，可以維護(hù)一個(gè)右邊所示的二叉查找樹，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針笋颤，這樣就可以運(yùn)用二叉查找快速獲取到相應(yīng)數(shù)據(jù)。

一般來說索引本身也很大赋除，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)在磁盤上茬祷。索引是數(shù)據(jù)庫中用來提高性能的最常用的工具并蝗。

索引優(yōu)勢(shì)劣勢(shì)

優(yōu)勢(shì)

• 1、類似于書籍的目錄索引滚停，提高數(shù)據(jù)檢索的效率键畴，降低數(shù)據(jù)庫的IO成本。
• 2起惕、通過索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本问词，降低CPU的消耗嘀粱。

劣勢(shì)

• 1、實(shí)際上索引也是一張表垄分，該表中保存了主鍵與索引字段娃磺，并指向?qū)嶓w類的記錄，所以索引列也是要占用空間的偷卧。

• 2、雖然索引大大提高了查詢效率炉奴，同時(shí)卻也降低更新表的速度蛇更，如對(duì)表進(jìn)行INSERT赛糟、UPDATE砸逊、DELETE。因?yàn)楦卤頃r(shí)司倚，MySQL 不僅要保存數(shù)據(jù)篓像，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息盒粮。

索引結(jié)構(gòu)

索引是在MySQL的存儲(chǔ)引擎層中實(shí)現(xiàn)的奠滑，而不是在服務(wù)器層實(shí)現(xiàn)的。所以每種存儲(chǔ)引擎的索引都不一定完全相同摊崭，也不是所有的存儲(chǔ)引擎都支持所有的索引類型的杰赛。MySQL目前提供了以下4種索引：

• BTREE 索引： 最常見的索引類型，大部分索引都支持 B 樹索引淆攻。
• HASH 索引：只有Memory引擎支持 嘿架， 使用場(chǎng)景簡(jiǎn)單 耸彪。
• R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型蝉娜，通常使用較少，不做特別介紹南缓。
• Full-text （全文索引）：全文索引也是MyISAM的一個(gè)特殊索引類型荧呐，主要用于全文索引纸镊，InnoDB從Mysql5.6版本開始支持全文索引概疆。

MyISAM、InnoDB凯旭、Memory三種存儲(chǔ)引擎對(duì)各種索引類型的支持

我們平常所說的索引使套，如果沒有特別指明，都是指B+樹（多路搜索樹弄贿，并不一定是二叉的）結(jié)構(gòu)組織的索引矫膨。其中聚集索引、復(fù)合索引危尿、前綴索引馁痴、唯一索引默認(rèn)都是使用 B+tree 索引，統(tǒng)稱為 索引济欢。

BTREE 結(jié)構(gòu)

BTree又叫多路平衡搜索樹小渊，一顆m叉的BTree特性如下：

• 樹中每個(gè)節(jié)點(diǎn)最多包含m個(gè)孩子。
• 除根節(jié)點(diǎn)與葉子節(jié)點(diǎn)外酬屉，每個(gè)節(jié)點(diǎn)至少有[ceil(m/2)]個(gè)孩子呐萨。
• 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有兩個(gè)孩子谬擦。
• 所有的葉子節(jié)點(diǎn)都在同一層。
• 每個(gè)非葉子節(jié)點(diǎn)由n個(gè)key與n+1個(gè)指針組成谜悟，其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree為例，key的數(shù)量：公式推導(dǎo)[ceil(m/2)-1] <= n <= m-1狼牺。所以 2 <= n <=4 礼患。當(dāng)n>4時(shí)，中間節(jié)點(diǎn)分裂到父節(jié)點(diǎn)缅叠，兩邊節(jié)點(diǎn)分裂肤粱。

插入 C N G A H E K Q M F W L T Z D P R X Y S 數(shù)據(jù)為例。

演變過程如下：

• 1)领曼、插入前4個(gè)字母 C N G A

  
  

• 2)庶骄、插入H，n>4单刁，中間元素G字母向上分裂到新的節(jié)點(diǎn)

  
  

• 3)羔飞、插入E，K逻淌，Q不需要分裂

  
  

• 4)恍风、插入M，中間元素M字母向上分裂到父節(jié)點(diǎn)G

  
  

• 5)、插入F窜骄，W，L糠亩，T不需要分裂

  
  

6)、插入Z廷没，中間元素T向上分裂到父節(jié)點(diǎn)中

7)垂寥、插入D，中間元素D向上分裂到父節(jié)點(diǎn)中狭归。然后插入P文判，R，X疚宇，Y不需要分裂

• 8)赏殃、最后插入S，NPQR節(jié)點(diǎn)n>5讼撒，中間節(jié)點(diǎn)Q向上分裂股耽，但分裂后父節(jié)點(diǎn)DGMT的n>5，中間節(jié)點(diǎn)M向上分裂

  
  

到此炎滞，該BTREE樹就已經(jīng)構(gòu)建完成了诬乞， BTREE樹 和 二叉樹 相比， 查詢數(shù)據(jù)的效率更高震嫉， 因?yàn)閷?duì)于相同的數(shù)據(jù)量來說，BTREE的層級(jí)結(jié)構(gòu)比二叉樹小扼睬，因此搜索速度快悴势。

B-Tree中的每個(gè)節(jié)點(diǎn)根據(jù)實(shí)際情況可以包含大量的關(guān)鍵字信息和分支措伐，如下圖所示為一個(gè)3階的B-Tree：

每個(gè)節(jié)點(diǎn)占用一個(gè)盤塊的磁盤空間侥加，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹根節(jié)點(diǎn)的指針粪躬，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤塊的地址。兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹的數(shù)據(jù)的范圍域短蜕。以根節(jié)點(diǎn)為例，關(guān)鍵字為17和35岖研，P1指針指向的子樹的數(shù)據(jù)范圍為小于17警检，P2指針指向的子樹的數(shù)據(jù)范圍為17~35，P3指針指向的子樹的數(shù)據(jù)范圍為大于35拓售。

模擬查找關(guān)鍵字29的過程：

• 1镶奉、根據(jù)根節(jié)點(diǎn)找到磁盤塊1，讀入內(nèi)存鸽凶〗ㄇ停【磁盤I/O操作第1次】
• 2、比較關(guān)鍵字29在區(qū)間（17,35）亿蒸，找到磁盤塊1的指針P2。
• 3边锁、根據(jù)P2指針找到磁盤塊3，讀入內(nèi)存矢门』彝埽【磁盤I/O操作第2次】
• 4、比較關(guān)鍵字29在區(qū)間（26,30）摩梧，找到磁盤塊3的指針P2仅父。
• 5、根據(jù)P2指針找到磁盤塊8笙纤，讀入內(nèi)存《端【磁盤I/O操作第3次】
• 6腥椒、在磁盤塊8中的關(guān)鍵字列表中找到關(guān)鍵字29。

分析上面過程洒放，發(fā)現(xiàn)需要3次磁盤I/O操作滨砍，和3次內(nèi)存查找操作。由于內(nèi)存中的關(guān)鍵字是一個(gè)有序表結(jié)構(gòu)惋戏，可以利用二分法查找提高效率日川。而3次磁盤I/O操作是影響整個(gè)B-Tree查找效率的決定因素。B-Tree相對(duì)于AVLTree縮減了節(jié)點(diǎn)個(gè)數(shù)龄句，使每次磁盤I/O取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用，從而提高了查詢效率傀蓉。

B+TREE 結(jié)構(gòu)

B+Tree為BTree的變種职抡，B+Tree與BTree的區(qū)別為：

• 1)、n叉B+Tree最多含有n個(gè)key谱净，而BTree最多含有n-1個(gè)key。
• 2)冈钦、B+Tree的葉子節(jié)點(diǎn)保存所有的key信息李请，依key大小順序排列。
• 3)导盅、所有的非葉子節(jié)點(diǎn)都可以看作是key的索引部分。

由于B+Tree只有葉子節(jié)點(diǎn)保存數(shù)據(jù)信息乍炉，查詢?nèi)魏蝛ey都要從root走到葉子嘁字。所以B+Tree的查詢效率更加穩(wěn)定。

MySQL中的B+Tree

MySql索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(jīng)典的B+Tree進(jìn)行了優(yōu)化纪蜒。在原B+Tree的基礎(chǔ)上，增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針随珠，就形成了帶有順序指針的B+Tree猬错，提高區(qū)間訪問的性能。

B+Tree相對(duì)于B-Tree有幾點(diǎn)不同：

• 非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息显沈。
• 所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針逢唤。
• 數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中。

MySQL中的 B+Tree 索引結(jié)構(gòu)示意圖:

通常在B+Tree上有兩個(gè)頭指針魔慷，一個(gè)指向根節(jié)點(diǎn)著恩，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)蜻展，而且所有葉子節(jié)點(diǎn)（即數(shù)據(jù)節(jié)點(diǎn)）之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)邀摆。因此可以對(duì)B+Tree進(jìn)行兩種查找運(yùn)算：一種是對(duì)于主鍵的范圍查找和分頁查找，另一種是從根節(jié)點(diǎn)開始片挂，進(jìn)行隨機(jī)查找贞盯。

可能上面例子中只有22條數(shù)據(jù)記錄，看不出B+Tree的優(yōu)點(diǎn)沪饺，下面做一個(gè)推算：

InnoDB存儲(chǔ)引擎中頁的大小為16KB躏敢，一般表的主鍵類型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)），指針類型也一般為4或8個(gè)字節(jié)整葡，也就是說一個(gè)頁（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐导啵瑸榉奖阌?jì)算，這里的K取值為103）遭居。也就是說一個(gè)深度為3的B+Tree索引可以維護(hù)103 * 103 * 103 = 10億 條記錄啼器。

實(shí)際情況中每個(gè)節(jié)點(diǎn)可能不能填充滿，因此在數(shù)據(jù)庫中端壳，B+Tree的高度一般都在2—4層。mysql的InnoDB存儲(chǔ)引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的枪蘑，也就是說查找某一鍵值的行記錄時(shí)最多只需要1—3次磁盤I/O操作损谦。

數(shù)據(jù)庫中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）。上面的B+Tree示例圖在數(shù)據(jù)庫中的實(shí)現(xiàn)即為聚集索引岳颇，聚集索引的B+Tree中的葉子節(jié)點(diǎn)存放的是整張表的行記錄數(shù)據(jù)照捡。輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)，而是存儲(chǔ)相應(yīng)行數(shù)據(jù)的聚集索引鍵话侧，即主鍵栗精。當(dāng)通過輔助索引來查詢數(shù)據(jù)時(shí)，InnoDB存儲(chǔ)引擎會(huì)遍歷輔助索引找到主鍵瞻鹏，然后再通過主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)悲立。

索引分類

• 1、單值索引 ：即一個(gè)索引只包含單個(gè)列乙漓，一個(gè)表可以有多個(gè)單列索引级历。
• 2、唯一索引 ：索引列的值必須唯一叭披，但允許有空值寥殖。
• 3玩讳、復(fù)合索引 ：即一個(gè)索引包含多個(gè)列。

索引語法

索引在創(chuàng)建表的時(shí)候嚼贡，可以同時(shí)創(chuàng)建熏纯， 也可以隨時(shí)增加新的索引。
準(zhǔn)備環(huán)境:

create database demo_01 default charset=utf8mb4;
use demo_01;
CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

創(chuàng)建索引

語法 ：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
index_col_name : column_name[(length)][ASC | DESC]

示例 ： 為city表中的city_name字段創(chuàng)建索引

create index idx_city_name on city(city_name)

查看索引

語法：

show index from table_name;

示例：查看city表中的索引信息粤策；

show index from city\G

刪除索引

語法 ：

DROP INDEX index_name ON tbl_name;

示例 ： 想要?jiǎng)h除city表上的索引idx_city_name樟澜，可以操作如下：

drop index idx_city_name on city;

ALTER命令

1). alter table tb_name add primary key(column_list);
該語句添加一個(gè)主鍵，這意味著索引值必須是唯一的叮盘，且不能為NULL

2). alter table tb_name add unique index_name(column_list);
這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外秩贰，NULL可能會(huì)出現(xiàn)多次）

3). alter table tb_name add index index_name(column_list);
添加普通索引， 索引值可以出現(xiàn)多次柔吼。

4). alter table tb_name add fulltext index_name(column_list);
該語句指定了索引為FULLTEXT毒费， 用于全文索引

索引設(shè)計(jì)原則

索引的設(shè)計(jì)可以遵循一些已有的原則，創(chuàng)建索引的時(shí)候請(qǐng)盡量考慮符合這些原則愈魏，便于提升索引的使用效率觅玻，更高效的使用索引。

• 對(duì)查詢頻次較高培漏，且數(shù)據(jù)量比較大的表建立索引溪厘。

• 索引字段的選擇，最佳候選列應(yīng)當(dāng)從where子句的條件中提取牌柄，如果where子句中的組合比較多畸悬，那么應(yīng)當(dāng)挑選最常用、過濾效果最好的列的組合友鼻。

• 使用唯一索引傻昙，區(qū)分度越高，使用索引的效率越高彩扔。

• 索引可以有效的提升查詢數(shù)據(jù)的效率妆档，但索引數(shù)量不是多多益善，索引越多虫碉，維護(hù)索引的代價(jià)自然也就水漲船高贾惦。對(duì)于插入、更新敦捧、刪除等DML操作比較頻繁的表來說须板，索引過多，會(huì)引入相當(dāng)高的維護(hù)代價(jià)兢卵，降低DML操作的效率习瑰，增加相應(yīng)操作的時(shí)間消耗。另外索引過多的話秽荤，MySQL也會(huì)犯選擇困難病甜奄，雖然最終仍然會(huì)找到一個(gè)可用的索引柠横，但無疑提高了選擇的代價(jià)。

• 使用短索引课兄，索引創(chuàng)建之后也是使用硬盤來存儲(chǔ)的牍氛，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率烟阐。假如構(gòu)成索引的字段總長(zhǎng)度比較短搬俊，那么在給定大小的存儲(chǔ)塊內(nèi)可以存儲(chǔ)更多的索引值蜒茄，相應(yīng)的可以有效的提升MySQL訪問索引的I/O效率。

• 利用最左前綴扩淀，N個(gè)列組合而成的組合索引楔敌，那么相當(dāng)于是創(chuàng)建了N個(gè)索引驻谆，如果查詢時(shí)where子句中使用了組成該索引的前幾個(gè)字段胜臊，那么這條查詢SQL可以利用組合索引來提升查詢效率。

創(chuàng)建復(fù)合索引:

CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相當(dāng)于

對(duì)name 創(chuàng)建索引 ;
對(duì)name伙判、email 創(chuàng)建了索引 ;
對(duì)name、email宴抚、status 創(chuàng)建了索引 ;

參考：
https://blog.csdn.net/xingqibaing/article/details/82744110