MySQL索引

索引

什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

高效性：

利用索引可以提高数据库的查询效率

完整性：

用户可以加速表和表之间的连接，实现表与表之间的参照完整性

唯一性：

索引可以确保所查的数据的唯一性

特殊能力：

通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能

#####

索引按照物理实现⽅式，索引可以分为 2 种：聚簇（聚集）和⾮聚簇（⾮聚集）索引。我们也把⾮聚集索引称为⼆级索引或者辅助索引。

聚簇索引

特点：

• 使⽤记录主键值的大小进⾏记录和⻚的排序，这包括三个⽅⾯的含义： 页内的记录是按照主键的⼤⼩顺序排成⼀个 单向链表 。 各个存放 ⽤户记录的页也是根据页中⽤户记录的主键大小顺序排成⼀个 双向链表 。 存放目录项记录的页分为不同的层次，在同⼀层次中的⻚也是根据⻚中⽬录项记录的主键大小顺序排成⼀个 双向链表 。

• B+树的叶⼦节点 存储的是完整的⽤户记录。

• 所谓完整的⽤户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

• 数据访问更快 ，因为聚簇索引将索引和数据保存在同⼀个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

• 聚簇索引对于主键的排序查找和范围查找速度⾮常快

• 按照聚簇索引排列顺序，查询显示⼀定范围数据的时候，由于数据都是紧密相连，数据库不⽤从多个数据块中提取数据，所以节省了⼤量的io操作 。

缺点：

插⼊速度严重依赖于插⼊顺序 ，按照主键的顺序插⼊是最快的⽅式，否则将会出现⻚分裂，严重影响性能。因此，对于InnoDB表，我们⼀般都会定义⼀个⾃增的ID列为主键

更新主键的代价很⾼ ，因为将会导致被更新的⾏移动。因此，对于InnoDB表，我们⼀般定义主键 为 不可更新

⼆级索引访问需要两次索引查找 ，第⼀次找到主键值，第⼆次根据主键值找到⾏数据

⼆级索引

（辅助索引、⾮聚簇索引）

概念：回表 以某列⼤⼩排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 某列的值查 找到完整的⽤户记录的话，仍然需要到 聚簇索引 中再查⼀遍，这个过程称为 回表 。也就 是根据某列的 值查询⼀条完整的⽤户记录需要使⽤到 2 棵B+树！

索引的分类

主键索引

CREATE TABLE `表名` (
`字段1` INT(11) AUTO_INCREMENT PRIMARY KEY,
#或 PRIMARY KEY(`字段1`)
)
​

唯一索引

CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL UNIQUE,
#或 UNIQUE KEY(`字段1`)
)

常规索引

CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL,
INDEX/KEY(`字段1`)
)

全文索引

CREATE TABLE `表名` (
`字段1` VARCHAR(32) NOT NULL,,
fulltext key (字段名，字段名，字段名) with parser ngram
)ENGINE=innodb

​

索引的缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。

2.因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘 空间的索引文件。

1. 如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

为什么不⽤红⿊树⽽是⽤B+tree

• 磁盘访问效率

• 数据组织

• 高度平衡

• 空间利用率

• 并发控制

Btree 和 B+tree 的区别

B树（B-Tree）和B+树（B+-Tree）是两种常用的数据结构，特别是在数据库和文件系统中用于实现索引。它们的主要区别：

1. 数据存储位置

B树: 数据可以存储在非叶子节点和叶子节点中。每个节点包含一组键值对，这些键值对按顺序排列。

B+树: 所有数据记录都存储在叶子节点中。非叶子节点仅包含键值，用于指导搜索路径。

1. 节点结构

B树: 每个节点包含多个键值对和多个子节点指针。节点内的键值对和子节点指针数量之间存在一定的关系，以保持平衡。

B+树: 每个非叶子节点包含多个键值和相同数量的子节点指针。叶子节点除了包含数据记录外，还包含指向相邻叶子节点的指针，形成一个链表。

1. 搜索性能

B树: 搜索操作可能需要访问非叶子节点，搜索路径上的每个节点都需要比较和跳转。

B+树: 搜索操作只需访问叶子节点。由于叶子节点形成一个链表，搜索过程中可以快速遍历所有键值。

1. 插入和删除操作

B树: 插入和删除操作可能导致节点分裂或合并，维护树的平衡。

B+树: 插入和删除操作仅影响叶子节点，非叶子节点仅需更新键值和指针信息。

1. 应用场景

B树: 适用于需要快速访问中间节点数据的场景，如文件系统中的目录结构。

B+树: 适用于需要频繁搜索、插入和删除操作的场景，如数据库索引。由于所有数据都在叶子节点，B+树在范围查询和排序方面更具优势。

导致索引失效的情况

最佳左前缀法则

主键插入顺序

计算、函数导致索引失效

类型转换导致索引失效

范围条件右边的列索引失效

不等于(!= 或者<>)索引失效

is null可以使用索引，is not null无法使用索引

like以通配符%开头索引失效

OR 前后存在非索引的列，索引失效

数据库和表的字符集统一使用utf8mb4