Mysql相关知识（三）索引

本文介绍Mysql索引相关知识

索引是什么

索引是一种帮助Mysql高效查询数据的数据结构

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

通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

索引的类型

• 主键索引：索引列中的值必须是唯一的，且不允许有空值。
• 唯一索引：索引列中的值必须是唯一的，但允许为空值。
• 全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。
• 组合索引： 可以是单列上创建的索引，也可以是在多列上创建的索引。
• 普通索引： 最基本的索引类型，没有唯一性之类的限制。

索引相关操作：

主键索引：

#建表的时候创建主键索引
create table 表名 (字段1 数据类型，字段2 数据类型,primary key (列名));

#修改表增加主键索引
alter table 表名 add primary key (列名);

#删除主键索引
alter table 表名 drop primary key;

唯一索引：

#创建
create unique index 索引名 on 表名 (列名);
或
alter table 表名 add unique 索引名 (列名);
或
create table 表名 (字段1 数据类型，字段2 数据类型,unique 索引名 (列名));

全文索引:

create fulltext index 索引名 on 表名 (列名);

alter table 表名 add fulltext 索引名 (列名);

create table 表名 (字段1 数据类型，字段2 数据类型,fulltext 索引名 (列名));

组合索引：

create table 表名 (字段1 数据类型，字段2 数据类型,index 索引名 (列名1，列名2));
#需要满足最左原则，因为select语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。
#  select * from 表名 where 列名1='...' and(or) 列名2='...'

普通索引：

create index 索引名 on 表名 (列名[(length)]);

#(列名[(length)]：length 为可选项，如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列的前 length 个字符来创建索引，这样有利于减小索引文件的大小。

alter table 表名 add index 索引名 (列名);

查看索引：

show index from T;
show keys form T;

Mysql索引的数据结构：B+Tree
不使用下列数据结构的原因：

• 二叉树：极端情况下会成为类似链表的结构，而且树高会增大磁盘IO，影响查询效率
• 平衡二叉树：树高会增大磁盘IO，影响查询效率
• hash表：适合等值查询，不适合范围查询会查询整个表

BTree：

• 叶子节点和非叶子节点都存放数据，不支持范围查询的快速查询（会多词遍历根节点）。
• 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+Tree：

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

InnoDB索引：

主键索引也叫聚簇索引，使用B+Tree构建，叶子节点存放的是数据表的整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

除聚簇索引之外的所有索引都称为辅助索引。辅助索引叶子节点存储的是该行的主键值，在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录，这个过程也称回表。

哪些情况需要创建索引：

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系应该建立索引
4. 频繁更新的字段不适合创建索引
5. where条件里用不到的字段不创建索引
6. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
7. 查询中统计或者分组的字段

哪些情况不需要创建索引：

1. 表记录太少
2. 经常增删改的表
3. 数据列有许多重复的内容

索引优化：

• 覆盖索引
• 最左前缀原则
• 索引下推

参考：

1. Mysql索引相关知识
2. Mysql索引概念相关知识
3. Mysql45讲