[TOC]

1. 索引概述

MySQL官方對索引的定義為：索引（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)高級查找算法怀酷，這種數(shù)據(jù)結(jié)構(gòu)就是索引稻爬。如下面的==示意圖==所示 :

image.png

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

一般來說索引本身也很大檐束，不可能全部存儲在內(nèi)存中辫秧，因此索引往往以索引文件的形式存儲在磁盤上。索引是數(shù)據(jù)庫中用來提高性能的最常用的工具被丧。

2. 索引優(yōu)勢劣勢

優(yōu)勢:

• 類似于書籍的目錄索引盟戏，提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本晚碾。
• 通過索引列對數(shù)據(jù)進(jìn)行排序抓半，降低數(shù)據(jù)排序的成本，降低CPU的消耗格嘁。

劣勢:

• 實(shí)際上索引也是一張表笛求，該表中保存了主鍵與索引字段，并指向?qū)嶓w類的記錄糕簿，所以索引列也是要占用空間的
• 雖然索引大大提高了查詢效率探入，同時卻也降低更新表的速度，如對表進(jìn)行INSERT懂诗、UPDATE蜂嗽、DELETE。因?yàn)楦卤頃r殃恒，MySQL 不僅要保存數(shù)據(jù)植旧，還要保存一下索引文件每次更新添加了索引列的字段辱揭，都會調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息。

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

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

• BTREE 索引 ： 最常見的索引類型域庇，大部分索引都支持 B 樹索引。
• HASH 索引：只有Memory引擎支持 覆积， 使用場景簡單 听皿。
• R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個特殊索引類型，主要用于地理空間數(shù)據(jù)類型宽档，通常使用較少尉姨，不做特別介紹。
• Full-text （全文索引） ：全文索引也是MyISAM的一個特殊索引類型雌贱，主要用于全文索引啊送，InnoDB從Mysql5.6版本開始支持全文索引。

<center><b>MyISAM欣孤、InnoDB馋没、Memory三種存儲引擎對各種索引類型的支持</b></center>

我們平常所說的索引，如果沒有特別指明降传，都是指B+樹（多路搜索樹篷朵，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引婆排、復(fù)合索引声旺、前綴索引、唯一索引默認(rèn)都是使用 B+tree 索引段只，統(tǒng)稱為 索引腮猖。

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

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

• 樹中每個節(jié)點(diǎn)最多包含m個孩子赞枕。
• 除根節(jié)點(diǎn)與葉子節(jié)點(diǎn)外澈缺，每個節(jié)點(diǎn)至少有[ceil(m/2)]個孩子。
• 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)炕婶，則至少有兩個孩子姐赡。
• 所有的葉子節(jié)點(diǎn)都在同一層。
• 每個非葉子節(jié)點(diǎn)由n個key與n+1個指針組成柠掂，其中[ceil(m/2)-1] <= n <= m-1

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

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

1). n叉B+Tree最多含有n個key，而BTree最多含有n-1個key涯贞。

2). B+Tree的葉子節(jié)點(diǎn)保存所有的key信息枪狂，依key大小順序排列危喉。

3). 所有的非葉子節(jié)點(diǎn)都可以看作是key的索引部分。

image.png

由于B+Tree只有葉子節(jié)點(diǎn)保存key信息摘完，查詢?nèi)魏蝛ey都要從root走到葉子姥饰。所以B+Tree的查詢效率更加穩(wěn)定。

3.3 MySQL中的 B+Tree

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

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

image.png

4. 索引分類

1） 單值索引 ：即一個索引只包含單個列态蒂，一個表可以有多個單列索引

2） 唯一索引 ：索引列的值必須唯一杭措，但允許有空值

3） 復(fù)合索引 ：即一個索引包含多個列

5. 索引語法

索引在創(chuàng)建表的時候，可以同時創(chuàng)建钾恢， 也可以隨時增加新的索引手素。

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');

5.1 創(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)建索引 ；

image.png

5.2 查看索引

語法：

show index from table_name;

示例：查看city表中的索引信息瘩蚪；

image.png

image.png

5.3 刪除索引

語法 ：

DROP INDEX index_name ON tbl_name;

示例 ： 想要刪除city表上的索引idx_city_name泉懦，可以操作如下：

image.png

5.4 ALTER命令

1). alter table tb_name add primary key(column_list); 
    該語句添加一個主鍵，這意味著索引值必須是唯一的疹瘦，且不能為NULL 
    
2). alter table tb_name add unique index_name(column_list); 
    這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外崩哩，NULL可能會出現(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邓嘹， 用于全文索引

6. 索引設(shè)計原則

索引的設(shè)計可以遵循一些已有的原則，創(chuàng)建索引的時候請盡量考慮符合這些原則险胰，便于提升索引的使用效率汹押，更高效的使用索引。

• 對查詢頻次較高起便，且數(shù)據(jù)量比較大的表建立索引棚贾。
• 索引字段的選擇，最佳候選列應(yīng)當(dāng)從where子句的條件中提取缨睡，如果where子句中的組合比較多鸟悴，那么應(yīng)當(dāng)挑選最常用、過濾效果最好的列的組合奖年。
• 使用唯一索引细诸，區(qū)分度越高，使用索引的效率越高陋守。
• 索引可以有效的提升查詢數(shù)據(jù)的效率震贵，但索引數(shù)量不是多多益善利赋，索引越多，維護(hù)索引的代價自然也就水漲船高猩系。對于插入媚送、更新、刪除等DML操作比較頻繁的表來說寇甸，索引過多塘偎，會引入相當(dāng)高的維護(hù)代價，降低DML操作的效率拿霉，增加相應(yīng)操作的時間消耗吟秩。另外索引過多的話，MySQL也會犯選擇困難病绽淘，雖然最終仍然會找到一個可用的索引涵防，但無疑提高了選擇的代價。
• 使用短索引沪铭，索引創(chuàng)建之后也是使用硬盤來存儲的壮池，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率杀怠。假如構(gòu)成索引的字段總長度比較短椰憋，那么在給定大小的存儲塊內(nèi)可以存儲更多的索引值，相應(yīng)的可以有效的提升MySQL訪問索引的I/O效率驮肉。
• 利用最左前綴熏矿，N個列組合而成的組合索引，那么相當(dāng)于是創(chuàng)建了N個索引离钝，如果查詢時where子句中使用了組成該索引的前幾個字段票编，那么這條查詢SQL可以利用組合索引來提升查詢效率。

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

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

就相當(dāng)于
    對name 創(chuàng)建索引 ; 
    對name , email 創(chuàng)建了索引 ; 
    對name , email, status 創(chuàng)建了索引 ;