关于MySQL索引的相关总结

1. 基本介绍

1.1 什么是索引

索引是一种帮助MySQL高效获取数据的数据结构。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往存储在磁盘上的文件（可能存储在单独的索引文件，也可能和数据一起存储在数据文件中）。

MySQL的索引默认都是基于B+树存储的。

1.2 索引的优缺点

索引的优点：

索引能够大大提高数据检索的速度
通过索引列对数据进行排序，可以降低数据排序的成本，降低CPU的消耗。这是因为索引列会自动进行排序，如果按照索引列的顺序进行排序，对应使用order by语句来说，效率会提高很多。

索引的缺点：

维护索引需要耗费数据库资源，因为索引需要占用磁盘空间
虽然提高了数据检索的效率，但是会降低更新表的效率。例如当对表的数据进行增删改操作的时候，因为要维护索引（保存或更新对应的索引文件），所以速度会受到影响。

1.3 索引的使用场景

应该创建索引的场景：

频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

不需要使用索引的场景：

表的记录太少
经常增删改操作的表
查询条件用不到的字段
数据量小的字段

2. 存储引擎

2.1 基本介绍

存储引擎: 定义MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

MySQL的主要的存储引擎有InnoDB和MyISAM，不同的存储引擎支持的不同的索引.InnoDB和MyISAM的区别如下：

Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

关于存储索引的选择

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

InnoDB存储引擎支持的索引：
主键索引、单值索引（单列索引、普通索引）、唯一索引、复合索引（组合索引）。

MyISAM存储引擎支持的索引：
MyISAM存储引擎支持InnoDB存储引擎支持的索引，另外MyISAM存储引擎另外支持Full Text全文索引。
Full Text全文索引，MySQL5.7之前只有MyISAM存储引擎支持使用Full Text索引。全文索引类型为Full Text，在定义索引的列上支持值的全文查找，允许这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。

2.2 MyISAM索引与InnoDB索引的区别

MyISAM索引与InnoDB索引的区别？

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

3. 索引的基本操作

3.1 建表时创建

建表时创建：

CREATE TABLE 表名(

字段名 数据类型 [完整性约束条件],
......

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

);

说明：

UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度:可选。指索引的长度，必须是字符串类型才可以使用。
ASC:可选。表示升序排列。
DESC:可选。表示降序排列。
索引方法默认为B+树

3.2 建表后创建

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；
或
CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；

3.3 查看

show index from 表名;

3.4 删除

DROP INDEX 索引名 ON 表名
或
ALTER TABLE 表名 DROP INDEX 索引名

4. 索引类型

4.1 主键索引

主键索引，规定索引列中的值必须是唯一的，不允许有空值。

主键索引是在建表的时候自动创建的，主键索引的默认索引名为PRIMARY，默认索引使用的数据结构是B+树。

创建一个表t_user

create table t_user(
   id varchar(20) primary key,
   username varchar(20)
)

查看该表的索引

show index from t_user;

拓展：

无序插入数据后，表中的数据按主键进行排序。这是为什么呢？
原因：MySQL底层默认会为主键创建索引，并对索引的列进行排序，排序后可以显著提高查询效率。

4.2 单值索引（单列索引、普通索引）

单值索引是MySQL基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

（1）建表时创建

CREATE TABLE t_user1(
 id VARCHAR(20) PRIMARY KEY,
 username VARCHAR(20),
 age TINYINT,
 INDEX(username)
);

SHOW INDEX FROM t_user1;

查看索引创建情况：

可以看到在创建表的时候创建索引的话，索引名默认是等于所添加索引的列名。并且该列可以为空

（2）在已有的表中添加普通索引

给表t_user1的age列添加普通索引

CREATE INDEX idx_age ON t_user1(age);

查看t_user1表的索引情况：

4.3 唯一索引

唯一索引，索引列中的值必须是唯一的，但是允许为空值。

（1）建表的时候创建唯一索引

CREATE TABLE t_user2(
 id VARCHAR(20) PRIMARY KEY,
 username VARCHAR(20),
 age TINYINT,
 UNIQUE(username)
)

(2) 在已有的表中添加唯一索引

create unique index 索引名 on 表名(列名)

4.4 复合(组合、联合)索引

组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下，如需要使用多条件联合查询时，推荐使用组合索引替代多个单列索引使用。

最左前缀原则：
假设组合索引为：a,b,c的话；那么当SQL中对应有：a或a，b或a，b，c的时候，可称为完全满足最左原则；当SQL中查询条件对应只有a，c的时候，可称为部分满足最左原则；当SQL中没有a的时候，可称为不满足最左原则。

注：MySQL5.7开始，会自动优化，如：会把c，b，a优化为a，b，c使之完全遵循最左原则；会把c，a优化为a，c使之部分遵循最左原则。即：SQL语句中的对应条件的先后顺序无关。

推荐的介绍组合索引的文章：

多个单列索引和联合索引的区别详解

（1）建表的时候创建复合索引

可以使用index或者key来创建

CREATE TABLE t_user3(
 id VARCHAR(20) PRIMARY KEY,
 username VARCHAR(20),
 age TINYINT,
 KEY(username,age)
)

查看表t_user3的索引

可以看到复合索引名的默认命名为第一个列的列名。
(2) 在已有的表中添加复合索引

CREATE INDEX idx_nameage ON t_user4(username,age);

4.5 全文索引

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MySQL5.7之后，MyISAM和InnoDB中都可以使用全文索引。

4.6 空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

4.7 前缀索引

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

5. 底层原理

5.1 B树

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。

因为在MySQL的InnoDB存储引擎一次IO能读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：

B树的节点中存储着多个元素，每个内节点有多个分叉。
节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
父节点当中的元素不会出现在子节点中。
所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

举个例子，在b树中查询数据的情况：

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块5。

第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

B树的缺点：

B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

5.2 B+树

MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。

B树：非叶子节点和叶子节点都会存储数据。
B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

为了进一步提高查询的效率，MySQL又进行了优化，基于页的形式来管理索引。如图：

如果要找id=4的数据，会先到页目录中进行查找，然后再到数据目录中查找。

上面这种索引的数据结构就叫B+树数据结构

B+树是B树基础上的优化，使其更合适实现外存储索引结构。

B树结构图可以看到每个节点中不仅包含数据的key值，还有data值。而每一页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量较小，当存储的数据量很大时同样会导致B树的深度较大，增加查询时的磁盘I/O次数，进而影响查询效率。

在B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样大大加大每个节点（磁盘块）存储的key值数量，降低B+树的高度。

B+树和B树的不同：

B+树非叶子节点只存储键值信息
B+树所有叶子节点之间都有一个双向指针连接
B+树数据记录都放在叶子节点中。

6. 聚簇索引和非聚簇索引

聚簇索引：将数据和索引存储放到一块，索引结构的叶子节点保存行数据

非聚簇索引：将数据和索引分开存储，索引结构的叶子节点指向数据的对应位置，叶子节点保存的数据是数据的物理地址

注意：在Innodb存储引擎中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，如复合索引、前缀索引、唯一索引。辅助索引的叶子节点保存的数据不是数据的物理地址，而是主键值，辅助索引访问的数据总是需要二次查找，即查找到主键后再到主键索引查找。

6.1 聚簇索引

InnoDB使用的是聚簇索引，将主键组织到一个B+树上，而行数据在叶子节点上，若使用“where id=1 ”的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件索引，则需要两个步骤：

在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。
使用主键在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

聚簇索引默认就是主键索引，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。如果已经设置了主键为聚簇索引，又希望再单独设置聚簇索引，必须先删除主键，然后再添加我们想要的聚簇索引，最后恢复设置主键即可。

使用聚簇索引的优势：
问题：每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率要明显低于非聚簇索引，聚簇索引的优势在哪？

原因：
由于行数据和聚簇索引的叶子节点存储在一起，同一页会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了缓存中，再次访问时，会在内存中完成访问，不必访问磁盘，从而避免了多一次IO操作。这样主键和行数就是一起被加载到内存中的，找到叶子节点就可以立刻将行数据返回了，如果根据主键Id来组织数据，获得数据更快。

注意：
当使用主键作为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序并且可能出现新增记录的uuid会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响IO操作读取到的数据量。

6.2 非聚簇索引

MyISAM使用的是非聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同。主键索引B+树的节点键值存储了主键，辅助键索引B+树存储了辅助键。表数据结构存储在独立的地方，这两颗B+树的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键索引无需访问主键的索引树。

6.3 使用场景

关于应该使用聚簇索引还是非聚簇索引的场景如下：

7. 索引失效情况

常见的索引失效的情况如下：

查询情况中使用LIKE关键字
在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，则索引就会被使用。
查询语句中使用多列索引，没有遵循最左前缀原则
查询语句中使用!或者<>符号，
查询语句中使用OR关键字
查询语句只有OR关键字，如果OR前后两个条件的列都是索引，那么查询将使用索引。有一个条件的列不是索引，那么查询不会使用索引。可以使用union联合查询解决。
使用IN和NOT IN的情况，如果in条件过多的情况下，可能导致全表扫描。很多情况下推荐使用EXISTS关键字来代替IN。