《MySQL》索引

前提知识

下面例子都以Innodb为例

数据是存储在磁盘上的，MySQL是一款专门管理数据的软件。既然MySQL要管理数据，而数据又在磁盘上，所以MySQL在管理数据的时候，一定会进行IO操作。

• MySQL进行IO操作的基本单位是16KB（page）
• MySQL为了减少IO操作，设置有Buffer Pool大内存空间（128M）进行各种缓存，来和磁盘数据进行IO交互。

索引定义和结构

索引：在内存中存在的一种数据结构。

理解数据文件结构

MySQL中的数据文件基本都是通过一个个page构成的。

MySQL是由C语言制作的，page是一个16KB大小的结构体，用于存储MySQL里的数据文件内容。

page的表结构是双向链表。

之前说过，MySQL是尽量减少IO操作，缩短等待时间。为例减少IO，提高查询效率，page引入了目录，并且进行了分级目录，最终的大结构如下：

如图，索引的底层结构就是B+树。该数据结构存在BufferPool里

一张索引表对应一棵B+树。该结构只有叶子节点存数据，叶子结点间形参双向链表；非叶子节点只存目录项。

B+树结构来存储数据的优势

相对于链表，二叉搜索树，红黑树，查找快，减少了IO操作；相比于hash表，还支持范围查找

索引分类

• 聚簇索引：数据和索引在一起（Innodb）

聚簇索引会出现回表查询，过程就是普通索引找到主键，再由主键索引找数据。（如图）

• 非聚簇索引：数据和索引分离（MyISAM）

索引操作

主键索引：建立主键时自动创建，删除主键时自动删除。
唯一索引：添加唯一键属性时自动创建，删除唯一键属性时自动删除。（唯一键索引属于其他索引）
其他索引：

# 添加索引
alter table 表名 add index(字段);

# 查看索引
show index from 表名;
show key from 表名;

# 删除索引
alter table 表名 drop index 索引名
drop index 索引名 on 表名

拓展知识

索引覆盖

在查询过程中，数据库只需通过索引就能够满足查询的需求，而不需要再去访问数据表。换句话说，查询所需的列都包含在索引中，不需要再去读取数据表中的其他列。

复合索引

复合索引的创建方式是将多个列的索引组合在一起，按照指定的顺序创建。

例如，可以创建一个包含两个列的复合索引：(列1, 列2)。这样的索引可以提供按照列1和列2的组合进行查询的能力。

在复合索引中还有个一个索引最左匹配原则，这是指在使用复合索引进行查询时，查询条件必须按照索引中列的顺序进行匹配，而且只能使用索引的最左边的连续列。使用复合索引时，如果不按照最左匹配原则，则会导致索引失效

索引失效

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

全文索引

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。