1.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)就是索引坎藐。如下面的==示意圖==所示 :
左邊是數(shù)據(jù)表兢榨，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快Col2的查找吵聪，可以維護(hù)一個(gè)右邊所示的二叉查找樹，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針兼雄，這樣就可以運(yùn)用二叉查找快速獲取到相應(yīng)數(shù)據(jù)吟逝。
一般來說索引本身也很大，不可能全部存儲在內(nèi)存中赦肋，因此索引往往以索引文件的形式存儲在磁盤上块攒。索引是數(shù)據(jù)庫中用來提高性能的最常用的工具。
2.1索引優(yōu)勢劣勢
優(yōu)勢
1） 類似于書籍的目錄索引佃乘，提高數(shù)據(jù)檢索的效率囱井，降低數(shù)據(jù)庫的IO成本。
2） 通過索引列對數(shù)據(jù)進(jìn)行排序趣避，降低數(shù)據(jù)排序的成本庞呕，降低CPU的消耗。
劣勢
1） 實(shí)際上索引也是一張表程帕，該表中保存了主鍵與索引字段住练，并指向?qū)嶓w類的記錄，所以索引列也是要占用空間的愁拭。
2） 雖然索引大大提高了查詢效率讲逛，同時(shí)卻也降低更新表的速度，如對表進(jìn)行INSERT岭埠、UPDATE盏混、DELETE。因?yàn)楦卤頃r(shí)惜论，MySQL 不僅要保存數(shù)據(jù)许赃，還要保存一下索引文件每次更新添加了索引列的字段，都會調(diào)整因?yàn)楦滤鶐淼逆I值變化后的索引信息来涨。
2.3 索引結(jié)構(gòu)
索引是在MySQL的存儲引擎層中實(shí)現(xiàn)的图焰，而不是在服務(wù)器層實(shí)現(xiàn)的。所以每種存儲引擎的索引都不一定完全相同蹦掐，也不是所有的存儲引擎都支持所有的索引類型的技羔。MySQL目前提供了以下4種索引：
- BTREE 索引 ： 最常見的索引類型，大部分索引都支持 B 樹索引卧抗。
- HASH 索引：只有Memory引擎支持 藤滥， 使用場景簡單 。
- R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個(gè)特殊索引類型社裆，主要用于地理空間數(shù)據(jù)類型拙绊，通常使用較少，不做特別介紹。
- Full-text （全文索引） ：全文索引也是MyISAM的一個(gè)特殊索引類型标沪，主要用于全文索引榄攀，InnoDB從Mysql5.6版本開始支持全文索引。
MyISAM金句、InnoDB檩赢、Memory三種存儲引擎對各種索引類型的支持
我們平常所說的索引，如果沒有特別指明违寞，都是指B+樹（多路搜索樹贞瞒，并不一定是二叉的）結(jié)構(gòu)組織的索引。其中聚集索引趁曼、復(fù)合索引军浆、前綴索引、唯一索引默認(rèn)都是使用 B+tree 索引挡闰，統(tǒng)稱為 索引乒融。
2.3.1 BTREE 結(jié)構(gòu)
BTREE樹 和 二叉樹 相比， 查詢數(shù)據(jù)的效率更高尿这， 因?yàn)閷τ谙嗤臄?shù)據(jù)量來說簇抵，BTREE的層級結(jié)構(gòu)比二叉樹小，因此搜索速度快射众。
2.3.2 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)保存key信息愉舔，查詢?nèi)魏蝛ey都要從root走到葉子。所以B+Tree的查詢效率更加穩(wěn)定伙菜。
2.3.3 MySQL中的B+Tree
MySql索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的B+Tree進(jìn)行了優(yōu)化轩缤。在原B+Tree的基礎(chǔ)上，增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針贩绕，就形成了帶有順序指針的B+Tree火的，提高區(qū)間訪問的性能。
MySQL中的 B+Tree 索引結(jié)構(gòu)示意圖:
2.4 索引分類
1） 單值索引 ：即一個(gè)索引只包含單個(gè)列淑倾，一個(gè)表可以有多個(gè)單列索引
2） 唯一索引 ：索引列的值必須唯一馏鹤，但允許有空值
3） 復(fù)合索引 ：即一個(gè)索引包含多個(gè)列
2.5 索引語法
索引在創(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');
2.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)建索引 勃救；
2.5.2 查看索引
語法：
show index from table_name;
示例：查看city表中的索引信息
2.5.3 刪除索引
語法 ：
DROP INDEX index_name ON tbl_name;
示例 ： 想要?jiǎng)h除city表上的索引idx_city_name，可以操作如下：
2.5.4 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可能會出現(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， 用于全文索引
2.6 索引設(shè)計(jì)原則
索引的設(shè)計(jì)可以遵循一些已有的原則榜田，創(chuàng)建索引的時(shí)候請盡量考慮符合這些原則，便于提升索引的使用效率锻梳，更高效的使用索引箭券。
對查詢頻次較高，且數(shù)據(jù)量比較大的表建立索引疑枯。
索引字段的選擇辩块，最佳候選列應(yīng)當(dāng)從where子句的條件中提取，如果where子句中的組合比較多荆永，那么應(yīng)當(dāng)挑選最常用废亭、過濾效果最好的列的組合。
使用唯一索引具钥，區(qū)分度越高豆村，使用索引的效率越高。
索引可以有效的提升查詢數(shù)據(jù)的效率骂删，但索引數(shù)量不是多多益善掌动，索引越多，維護(hù)索引的代價(jià)自然也就水漲船高宁玫。對于插入粗恢、更新、刪除等DML操作比較頻繁的表來說欧瘪，索引過多眷射，會引入相當(dāng)高的維護(hù)代價(jià)，降低DML操作的效率佛掖，增加相應(yīng)操作的時(shí)間消耗妖碉。另外索引過多的話，MySQL也會犯選擇困難病苦囱，雖然最終仍然會找到一個(gè)可用的索引嗅绸，但無疑提高了選擇的代價(jià)。
使用短索引撕彤，索引創(chuàng)建之后也是使用硬盤來存儲的鱼鸠，因此提升索引訪問的I/O效率猛拴，也可以提升總體的訪問效率。假如構(gòu)成索引的字段總長度比較短蚀狰，那么在給定大小的存儲塊內(nèi)可以存儲更多的索引值愉昆，相應(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)于
對name 創(chuàng)建索引 ;
對name , email 創(chuàng)建了索引 ;
對name , email, status 創(chuàng)建了索引 ;
4.2 索引的使用
4.2.1 準(zhǔn)備環(huán)境
create table `tb_seller` (
`sellerid` varchar (100),
`name` varchar (100),
`nickname` varchar (50),
`password` varchar (60),
`status` varchar (1),
`address` varchar (100),
`createtime` datetime,
primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程序員','黑馬程序員','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
4.2.2 避免索引失效
1). 全值匹配 ，對索引中所有列都指定具體值刹勃。
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;
2). 最左前綴法則
如果索引了多列堪侯，要遵守最左前綴法則。指的是查詢從索引的最左前列開始荔仁，并且不跳過索引中的列伍宦。
匹配最左前綴法則，走索引：
違法最左前綴法則 乏梁， 索引失效：
如果符合最左法則次洼，但是出現(xiàn)跳躍某一列，只有最左列索引生效：
3). 范圍查詢右邊的列遇骑，不能使用索引 卖毁。
根據(jù)前面的兩個(gè)字段name ， status 查詢是走索引的质蕉， 但是最后一個(gè)條件address 沒有用到索引势篡。
4). 不要在索引列上進(jìn)行運(yùn)算操作， 索引將失效模暗。
5). 字符串不加單引號禁悠，造成索引失效。
由于兑宇，在查詢是碍侦，沒有對字符串加單引號，MySQL的查詢優(yōu)化器隶糕，會自動的進(jìn)行類型轉(zhuǎn)換瓷产，造成索引失效。
6). 盡量使用覆蓋索引枚驻，避免select*
盡量使用覆蓋索引（只訪問索引的查詢（索引列完全包含查詢列））濒旦，減少select * 。
如果查詢列再登，超出索引列尔邓，也會降低性能晾剖。
7). 用or分割開的條件， 如果or前的條件中的列有索引梯嗽，而后面的列中沒有索引齿尽，那么涉及的索引都不會被用到。
示例灯节，name字段是索引列 循头， 而createtime不是索引列，中間是or進(jìn)行連接是不走索引的 ：
explain select * from tb_seller where name='黑馬程序員' or createtime = '2088-01-01 12:00:00'\G;
8). 以%開頭的Like模糊查詢炎疆，索引失效卡骂。
如果僅僅是尾部模糊匹配，索引不會失效形入。如果是頭部模糊匹配偿警，索引失效。
解決方案 ：
通過覆蓋索引來解決
9). 如果MySQL評估使用索引比全表更慢唯笙，則不使用索引。
10). is NULL 盒使， is NOT NULL
有時(shí)索引失效崩掘。
11). in 走索引， not in 索引失效少办。
12). 單列索引和復(fù)合索引苞慢。
盡量使用復(fù)合索引，而少使用單列索引 英妓。
創(chuàng)建復(fù)合索引
create index idx_name_sta_address on tb_seller(name, status, address);
就相當(dāng)于創(chuàng)建了三個(gè)索引 ：
name
name + status
name + status + address
創(chuàng)建單列索引
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);
數(shù)據(jù)庫會選擇一個(gè)最優(yōu)的索引（辨識度最高索引）來使用挽放，并不會使用全部索引 。
4.3 查看索引使用情況
show status like 'Handler_read%';
show global status like 'Handler_read%';
Handler_read_first：索引中第一條被讀的次數(shù)蔓纠。如果較高辑畦，表示服務(wù)器正執(zhí)行大量全索引掃描（這個(gè)值越低越好）。
Handler_read_key：如果索引正在工作腿倚，這個(gè)值代表一個(gè)行被索引值讀的次數(shù)纯出，如果值越低，表示索引得到的性能改善不高敷燎，因?yàn)樗饕唤?jīng)常使用（這個(gè)值越高越好）暂筝。
Handler_read_next ：按照鍵順序讀下一行的請求數(shù)。如果你用范圍約束或如果執(zhí)行索引掃描來查詢索引列硬贯，該值增加焕襟。
Handler_read_prev：按照鍵順序讀前一行的請求數(shù)。該讀方法主要用于優(yōu)化ORDER BY ... DESC饭豹。
Handler_read_rnd ：根據(jù)固定位置讀一行的請求數(shù)鸵赖。如果你正執(zhí)行大量查詢并需要對結(jié)果進(jìn)行排序該值較高务漩。你可能使用了大量需要MySQL掃描整個(gè)表的查詢或你的連接沒有正確使用鍵。這個(gè)值較高卫漫，意味著運(yùn)行效率低菲饼，應(yīng)該建立索引來補(bǔ)救。
Handler_read_rnd_next：在數(shù)據(jù)文件中讀下一行的請求數(shù)列赎。如果你正進(jìn)行大量的表掃描宏悦，該值較高。通常說明你的表索引不正確或?qū)懭氲牟樵儧]有利用索引包吝。
