MYSQL索引

什么是索引

索引是一种提高检索效率的数据结构，好比书的目录。

索引需要存到本地磁盘，随便查表变快但是更新表变慢，因为还要去保存更新索引。

InnoDB底层使用的是B+树。

• B树：非叶子节点和叶子节点都会存储数据。

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

B+树结构

数据库中每一行的数据的存储是以页为单位的，一页的大小为16KB，一页可以存储多行数据。

索引的分类

聚簇索引：

搜索条件是主键值，叶子结点存储的就是实打实的每一行的数据，innodb会自动为我们创建聚簇索引。

非聚簇索引（辅助索引）：

叶子结点存储的不是整行数据，而是搜索条件字段的值和主键值，然后根据主键值回表进行主键的聚簇索引查找来得到整行数据。（聚簇索引只有一个，非聚簇索引可以有多个，如果在非聚簇索引的叶子结点中也都存放整行数据的话会浪费很多磁盘空间，非常冗余）

联合索引（属于非聚簇索引）：

联合索引指的是，对于表上多个列进行索引。联合索引的非叶子节点键值的数量大于等于二，是按照联合索引的顺序排列的，也就是说先排联合索引的第一个键值，再在联合索引第一个键值相同的情况下排第二个键值，如下图：

联合索引满足最左前缀法则：比如创建了索引（a,b,c）使用查询时where后面的条件可以是 a = xxx,b = xxx,c = xxx;也可以是：a = xxx b =xxx;不可以是：b = xxx, c = xxx。

覆盖索引：

InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，因为该联合索引，就是通过需要查找的值来建立的二叉树，在二叉树的每个节点上就包含了需要查询的字段，就不需要通过主键回表再查了。

比如：你已经以a,b两个字段创建了联合索引，此时再当你select a, b where a =xxx;（满足最左前缀法则）时就不需要通过主键回表了。

索引的创建

主键索引（聚簇索引）创建：

在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。

如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

---

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

create table test(

id int(10),

name varchar(20),

index id_index (id));

索引的使用取舍

适合创建索引的情况

1. 字段有唯一性的，加索引找到之后就不用继续再往后找了，因为是唯一的。
2. 频繁作为where查询条件的字段
3. 经常group by和order by的字段，如果都需要，那么加联合索引效果最佳。
4. 用到distinct去重操作的字段，创建索引之后按一定规则排序了，所以查询到的结果都是挨着的，去重的时候就很快了
5. 连表查询时用于连表的字段。
6. 字段类型所占空间小的比较适合创建索引，因为创建索引也是需要占空间的。
7. 以很长的字符串作为索引的时候最好建立前缀索引（inde（address（12））取前12位作为索引）。根据字符串的区分度来决定取前多少位（怎样让索引又尽可能的短又能尽可能区分不同的字符串）。
8. 区分度高的适合创建索引，如性别就不适合创建。

不适合创建索引的情况

1. 频繁更新的字段，因为更新要连着索引一起更新。
2. 无序的字段不适合，如uuid。索引的叶子结点数据是按顺序排列的，此时插入一个uuid可能插到中间到时页分裂。自增的字段作为索引好一点。