一护侮、索引概述

1. 簡介

索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結(jié)構(gòu)拳氢，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息践剂。

舉例說明索引：如果把數(shù)據(jù)庫中的某一張看成一本書弓摘，那么索引就像是書的目錄，可以通過目錄快速查找書中指定內(nèi)容的位置刃宵，對于數(shù)據(jù)庫表來說衡瓶，可以通過索引快速查找表中的數(shù)據(jù)。

2. 索引的原理

索引一般以文件形式存在磁盤中（也可以存于內(nèi)存中）牲证，存儲的索引的原理大致概括為以空間換時間哮针，數(shù)據(jù)庫在未添加索引的時候進行查詢默認的是進行全量搜索，也就是進行全局掃描坦袍，有多少條數(shù)據(jù)就要進行多少次查詢十厢，然后找到相匹配的數(shù)據(jù)就把他放到結(jié)果集中，直到全表掃描完捂齐。而建立索引之后蛮放，會將建立索引的KEY值放在一個n叉樹上（BTree）。因為B樹的特點就是適合在磁盤等直接存儲設(shè)備上組織動態(tài)查找表奠宜，每次以索引進行條件查詢時包颁，會去樹上根據(jù)key值直接進行搜索瞻想。

3. 索引的優(yōu)點

建立索引的目的是加快對表中記錄的查找或排序！

① 建立索引的列可以保證行的唯一性娩嚼，生成唯一的rowId

② 建立索引可以有效縮短數(shù)據(jù)的檢索時間

③ 建立索引可以加快表與表之間的連接

④ 為用來排序或者是分組的字段添加索引可以加快分組和排序順序

4. 索引的缺點

① 創(chuàng)建索引和維護索引需要時間成本蘑险，這個成本隨著數(shù)據(jù)量的增加而加大

② 創(chuàng)建索引和維護索引需要空間成本，每一條索引都要占據(jù)數(shù)據(jù)庫的物理存儲空間待锈，數(shù)據(jù)量越大漠其，占用空間也越大（數(shù)據(jù)表占據(jù)的是數(shù)據(jù)庫的數(shù)據(jù)空間）

③ 會降低表的增刪改的效率嘴高，因為每次增刪改索引需要進行動態(tài)維護竿音，導(dǎo)致時間變長

二、索引的使用場景

數(shù)據(jù)庫中表的數(shù)據(jù)量較大的情況下拴驮，對于查詢響應(yīng)時間不能滿足業(yè)務(wù)需求春瞬，可以合理的使用索引提升查詢效率。

三套啤、索引的分類和創(chuàng)建和修改刪除等命令

1. 基本索引類型

① 普通索引（單列索引）

② 復(fù)合索引（組合索引）

③ 唯一索引

④ 主鍵索引

⑤ 全文索引

2. 創(chuàng)建的語句

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

• unique|fulltext為可選參數(shù)宽气，分別表示唯一索引、全文索引

• index和key為同義詞潜沦，兩者作用相同萄涯，用來指定創(chuàng)建索引

• col_name為需要創(chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中該定義的多個列中選擇

• index_name指定索引的名稱唆鸡，為可選參數(shù)涝影，如果不指定，默認col_name為索引值

• length為可選參數(shù)争占，表示索引的長度燃逻，只有字符串類型的字段才能指定索引長度

• asc或desc指定升序或降序的索引值存儲

3. 索引的創(chuàng)建、查詢和刪除

索引的創(chuàng)建

① 普通索引（單列索引）

普通索引（單列索引）：單列索引是最基本的索引臂痕，它沒有任何限制伯襟。

（1）直接創(chuàng)建索引

CREATE INDEX index_name ON table_name(col_name);

（2）修改表結(jié)構(gòu)的方式添加索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

（3）創(chuàng)建表的時候同時創(chuàng)建索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(255))
)

（4）刪除索引

DROP INDEX index_name ON table_name;
或者
alter table `表名` drop index 索引名;

② 復(fù)合索引（組合索引）

復(fù)合索引：復(fù)合索引是在多個字段上創(chuàng)建的索引。復(fù)合索引遵守“最左前綴”原則握童，即在查詢條件中使用了復(fù)合索引的第一個字段姆怪，索引才會被使用。因此澡绩，在復(fù)合索引中索引列的順序至關(guān)重要稽揭。

（1）創(chuàng)建一個復(fù)合索引

create index index_name on table_name(col_name1,col_name2,...);

（2）修改表結(jié)構(gòu)的方式添加索引

alter table table_name add index index_name(col_name,col_name2,...);

③ 唯一索引

唯一索引：唯一索引和普通索引類似，主要的區(qū)別在于英古，唯一索引限制列的值必須唯一淀衣，但允許存在空值（只允許存在一條空值）。

如果在已經(jīng)有數(shù)據(jù)的表上添加唯一性索引的話：

• 如果添加索引的列的值存在兩個或者兩個以上的空值召调，則不能創(chuàng)建唯一性索引會失敗膨桥。（一般在創(chuàng)建表的時候蛮浑，要對自動設(shè)置唯一性索引，需要在字段上加上 not null）
• 如果添加索引的列的值存在兩個或者兩個以上的null值只嚣，還是可以創(chuàng)建唯一性索引沮稚，只是后面創(chuàng)建的數(shù)據(jù)不能再插入null值 ，并且嚴格意義上此列并不是唯一的册舞，因為存在多個null值蕴掏。

對于多個字段創(chuàng)建唯一索引規(guī)定列值的組合必須唯一。
比如：在order表創(chuàng)建orderId字段和 productId字段 的唯一性索引调鲸，那么這兩列的組合值必須唯一盛杰！

“空值” 和”NULL”的概念： 
1：空值是不占用空間的 .
2: MySQL中的NULL其實是占用空間的.

長度驗證：注意空值的之間是沒有空格的。

> select length(''),length(null),length(' ');
+------------+--------------+-------------+
| length('') | length(null) | length(' ') |
+------------+--------------+-------------+
|          0 |         NULL |           1 |
+------------+--------------+-------------+

（1）創(chuàng)建唯一索引

# 創(chuàng)建單個索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

# 創(chuàng)建多個索引
CREATE UNIQUE INDEX index_name on table_name(col_name,...);

（2）修改表結(jié)構(gòu)

# 單個
ALTER TABLE table_name ADD UNIQUE index index_name(col_name);
# 多個
ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);

（3）創(chuàng)建表的時候直接指定索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE index_name_unique(title)
)

④ 主鍵索引

主鍵索引是一種特殊的唯一索引藐石，一個表只能有一個主鍵即供，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引：

（1）主鍵索引(創(chuàng)建表時添加)

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`)
)

（2）主鍵索引(創(chuàng)建表后添加)

alter table tbl_name add primary key(col_name);

CREATE TABLE `order` (
    `orderId` varchar(36) NOT NULL,
    `productId` varchar(36)  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL
)

alter table `order` add primary key(`orderId`);

⑤ 全文索引

在一般情況下于微，模糊查詢都是通過 like 的方式進行查詢逗嫡。但是，對于海量數(shù)據(jù)株依，這并不是一個好辦法驱证，在 like "value%" 可以使用索引，但是對于 like "%value%" 這樣的方式恋腕，執(zhí)行全表查詢抹锄，這在數(shù)據(jù)量小的表，不存在性能問題吗坚，但是對于海量數(shù)據(jù)祈远，全表掃描是非常可怕的事情,所以 like 進行模糊匹配性能很差商源。

這種情況下车份，需要考慮使用全文搜索的方式進行優(yōu)化。全文搜索在 MySQL 中是一個 FULLTEXT 類型索引牡彻。FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB扫沼，而之前的版本只支持 MyISAM 表。

全文索引主要用來查找文本中的關(guān)鍵字庄吼，而不是直接與索引中的值相比較缎除。fulltext索引跟其它索引大不相同，它更像是一個搜索引擎总寻，而不是簡單的where語句的參數(shù)匹配器罐。fulltext索引配合match against操作使用，而不是一般的where語句加like渐行。目前只有char轰坊、varchar铸董，text 列上可以創(chuàng)建全文索引。

小技巧：
在數(shù)據(jù)量較大時候肴沫，先將數(shù)據(jù)放入一個沒有全局索引的表中粟害，然后再用CREATE index創(chuàng)建fulltext索引，要比先為一張表建立fulltext然后再將數(shù)據(jù)寫入的速度快很多颤芬。

（1）創(chuàng)建表的適合添加全文索引

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` text  NOT NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
     PRIMARY KEY (`id`),
    FULLTEXT (content)
)

（2）修改表結(jié)構(gòu)添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name)

（3）直接創(chuàng)建索引

CREATE FULLTEXT INDEX index_fulltext_content ON table_name(col_name)

注意： 默認 MySQL 不支持中文全文檢索悲幅！

MySQL 全文搜索只是一個臨時方案，對于全文搜索場景站蝠，更專業(yè)的做法是使用全文搜索引擎汰具，例如 ElasticSearch 或 Solr。

索引的查詢和刪除

#查看:
show indexes from `表名`;
#或
show keys from `表名`;
 
#刪除
alter table `表名` drop index 索引名;

注:MySQl的客戶端工具也可以進索引的創(chuàng)建沉衣、查詢和刪除郁副，如 Navicat Premium!

四、簡單實例演示

查看索引使用情況

show status like 'Handler_read%';

handler_read_key:這個值越高越好豌习，越高表示使用索引查詢到的次數(shù)
handler_read_rnd_next:這個值越高，說明查詢低效

常見索引失效的情況：

創(chuàng)建一個students表：
其中stud_id為主鍵拔疚！

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `stud_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(1) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`stud_id`)
 
)

INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) VALUES ('1', 'admin', 'student1@gmail.com', '18729902095', '1983-06-25');
INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) VALUES ('2', 'root', '74298110186@qq.com', '2', '1983-12-25');
INSERT INTO `learn_mybatis`.`students` (`stud_id`, `name`, `email`, `phone`, `create_date`) VALUES ('3', '110', '7429811086@qq.com', '3dsad', '2017-04-28');

使用 explain 查看 索引是否生效肥隆！Mysql中explain用法和結(jié)果字段的含義介紹

1. 在where后使用or，導(dǎo)致索引失效（盡量少用or）

簡單實例演示：
創(chuàng)建兩個普通索引稚失，

CREATE INDEX index_name_email ON students(email);
CREATE INDEX index_name_phone ON students(phone);

使用下面查詢sql栋艳，

# 使用了索引
EXPLAIN select * from students where stud_id='1'  or phone='18729902095'
# 使用了索引
EXPLAIN select * from students where stud_id='1'  or email='742981086@qq.com'

#--------------------------

# 沒有使用索引
EXPLAIN select * from students where phone='18729902095' or email='742981086@qq.com'

# 沒有使用索引
EXPLAIN select * from students where stud_id='1'  or phone='222' or email='742981086@qq.com'

2.使用like ，like查詢是以%開頭

在1的基礎(chǔ)上句各，還是使用 index_name_email 索引吸占。

使用下面查詢sql

# 使用了index_name_email索引
EXPLAIN select * from students where email like '742981086@qq.com%'

# 沒有使用index_name_email索引，索引失效
EXPLAIN select * from students where email like '%742981086@qq.com'

# 沒有使用index_name_email索引凿宾，索引失效
EXPLAIN select * from students where email like '%742981086@qq.com%'

3.復(fù)合索引遵守“最左前綴”原則矾屯，即在查詢條件中使用了復(fù)合索引的第一個字段，索引才會被使用

刪除1的基礎(chǔ)創(chuàng)建的 index_name_email 和 index_name_phone 索引初厚。

重新創(chuàng)建一個復(fù)合索引：

create index index_email_phone on students(email,phone);

使用下面查詢sql

# 使用了 index_email_phone 索引
EXPLAIN select * from students where email='742981086@qq.com' and  phone='18729902095'

# 使用了 index_email_phone 索引
EXPLAIN select * from students where phone='18729902095' and  email='742981086@qq.com'

# 使用了 index_email_phone 索引
EXPLAIN select * from students where email='742981086@qq.com' and name='admin'

# 沒有使用index_email_phone索引件蚕，復(fù)合索引失效
EXPLAIN select * from students where phone='18729902095' and name='admin'

4. 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來,否則不使用索引

給name創(chuàng)建一個索引产禾！

CREATE INDEX index_name ON students(name);

# 使用索引
EXPLAIN select * from students where name='110'

# 沒有使用索引
EXPLAIN select * from students where name=110

5. 使用in導(dǎo)致索引失效

# 使用索引
EXPLAIN select * from students where name='admin'

# 沒有使用索引
EXPLAIN SELECT * from students where name in ('admin')

6. DATE_FORMAT()格式化時間排作，格式化后的時間再去比較，可能會導(dǎo)致索引失效亚情。

刪除 students 上的創(chuàng)建的索引妄痪！重新在create_date創(chuàng)建一個索引！

CREATE INDEX index_create_date ON students(create_date);

# 使用索引
EXPLAIN SELECT * from students where create_date >= '2010-05-05'

# 沒有使用索引
EXPLAIN SELECT * from students where DATE_FORMAT(create_date,'%Y-%m-%d') >= '2010-05-05'

7. 對于order by楞件、group by 衫生、 union僧著、 distinc 中的字段出現(xiàn)在where條件中時，才會利用索引障簿！

8. 更多索引的使用注意可以參看這一篇博文：

索引使用注意規(guī)則（索引失效--存在索引但不使用索引）

五盹愚、總結(jié)

MySQL改善查詢性能改善的最好方式，就是通過數(shù)據(jù)庫中合理地使用索引站故！

一般當數(shù)據(jù)量較大的時候皆怕，遇到sql查詢性能問題，首先想到的應(yīng)該是查詢的sql時候使用了索引西篓，如果使用了索引性能還是提高不大愈腾，就要檢查索引是否使用正確，索引是否在sql查詢中生效了岂津！

如果索引生效了虱黄，并且索引的使用也是合理的，最后sql性能還是不高吮成，那就考慮重新優(yōu)化sql語句橱乱！

六、參考博文

• MySQL數(shù)據(jù)庫幾種常用的索引類型使用介紹
• MySQL如何設(shè)計索引
• MySQL索引使用的注意事項
• mysql索引之三：索引使用注意規(guī)則（索引失效--存在索引但不使用索引）
• 數(shù)據(jù)庫索引原理及優(yōu)化
• 阿里巴巴Java開發(fā)手冊(終極版)【索引規(guī)約】

如果您覺得這篇博文對你有幫助粱甫，請點贊或者喜歡泳叠，讓更多的人看到，謝謝茶宵！

如果帥氣（美麗）危纫、睿智（聰穎），和我一樣簡單善良的你看到本篇博文中存在問題乌庶，請指出种蝶，我虛心接受你讓我成長的批評，謝謝閱讀瞒大！
祝你今天開心愉快螃征！

歡迎訪問我的csdn博客，我們一同成長糠赦！

不管做什么会傲，只要堅持下去就會看到不一樣！在路上拙泽，不卑不亢!