MySQL 的索引机制

MySQL 的索引机制

1. 认识索引

一种排好序，能够提升查询性能的数据结构。

索引是数据库管理系统（DBMS）中用于加快数据检索速度的一种数据结构。它通过创建一个指向数据库表中数据值的 “快照” 来提高查询效率，使得数据库能够更快地找到和访问所需的数据行，而无需扫描整个表。

1. 创建索引

   create[ unique] index 索引名 on 表名 (字段名,... ) ;
   

   在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一索引。

2. 查看索引

   show index from 表名;
   

3. 删除索引

   drop index 索引名 on 表名;
   

注意事项：

1. 主键字段，在建表时，会自动创建主键索引
2. 添加唯一约束时，数据库实际上会添加唯一索引

索引的优点：

1. 提高数据查询的效率，降低数据库的 IO 成本。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 消耗。

索引的缺点：

1. 索引会占用存储空间。
2. 索引大大提高了查询效率，同时却也降低了 insert、update、delete 的效率，因为在对数据进行操作时，需要对索引进行维护。

2. 索引的分类

2.1 聚集（簇）索引

聚集索引决定了数据在磁盘上的物理存储顺序。也就是说，表中的记录按照聚集索引键的值进行排序并存储。

一个表只能有一个聚集索引，因为数据记录的物理排列方式只能有一种。在大多数情况下，主键会被用作聚集索引，但这不是必须的。

1. 特点：
   1. 数据行被存放在叶子节点上。
   2. 能够快速地通过主键访问数据，对于范围查询特别高效。
2. 优点：
   1. 对于基于聚集索引字段的查询非常快，因为它直接指向了数据所在的物理位置。
   2. 范围查询效率高，因为数据已经按索引顺序存储。
3. 缺点：
   1. 插入速度可能较慢，特别是当插入的数据不在索引末尾时，可能导致页分裂。
   2. 更新聚集索引列的成本较高，因为这可能导致数据重排。

2.1.1 主键索引

InnoDB 引擎的表一定需要主键，如果不创建，数据库会自动创建并维护一个主键索引。创建主键的列建议是没有业务意义的列。

在开发中要规避没有创建主键的情况！！！

1. 非叶子节点：存储主键值
2. 叶子节点：存储行数据

所以通过主键可以直接在索引树上找到数据，不需要查表，速度非常快

推荐使用主键自增：

1. 因为插入的数据始终会放在最后面，可以快速的找到插入的位置，无需做额外的开销，如移动数据的位置，旋转树等。
2. 如果不是自增，那么就无法判断要插入的数据具体是插入到索引树的哪个一位置，所以也无法判断树中数据的变化与树的旋转。那么就会带来不必要的开销。

2.2 非聚集索引

非聚集索引不决定数据的物理存储顺序，它是一种独立于数据存储的索引结构。

每个表可以有多个非聚集索引。非聚集索引包含索引键值以及指向实际数据行的指针。

1. 特点：
   1. 索引和数据是分开存储的，索引结构中只包含索引键和指向数据行的指针。
   2. 叶子节点并不直接存储数据行，而是存储指向数据行的指针。
2. 优点：
   1. 不影响表中数据的实际存储顺序，因此添加多个非聚集索引对性能的影响相对较小。
   2. 对于非聚集索引覆盖的查询，可以直接从索引中获取所需信息，无需访问数据页，提高查询效率。
3. 缺点：
   1. 对于非聚集索引覆盖之外的查询，需要两次查找，第一次找到索引，第二次通过索引找到对应的数据行。
   2. 占用额外的存储空间，因为需要维护索引结构。

2.2.1 普通索引

即在普通字段上建立索引

1. 非叶子节点：存储索引列值
2. 叶子节点：存储的数据是主键值

为什么非主键索引的叶子节点数据存储的是主键值？

1. 因为如果不存主键值，那么就只能存储数据。
2. 如果数据发生了变化，不仅要维护主键索引，同时还需要维护其他索引。表示修改了一个索引的数据，会同时修改其他索引的数据，这需要带来额外的开销。

2.2.2 唯一索引

1. 唯一索引可以确保索引列中的所有值都是唯一的，但允许包含一个或多个 NULL 值（取决于存储引擎）。
2. 可以在单个列上定义，也可以跨越多个列。

2.2.3 联合索引

给主键以外的多个列创建索引，也叫联合索引（组合索引）

2.2.4 全文索引

• 主要用于全文搜索，适用于 MyISAM 和 InnoDB 存储引擎（从 MySQL 5.6 开始支持 InnoDB）。
• 允许对文本内容进行高效的复杂查询。

2.3 区别

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引（非聚集索引）的叶子节点下挂的是该字段值对应的主键值。

注意：在使用索引进行查询时，尽量避免回表查询。

1. 什么是回表查询？

   例如先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

2. 这么避免回表查询？

   使用覆盖索引，即查询的列都是在索引树上的列。

3. 回表查询的缺点：

   1. 性能损耗：回表查询需要额外的 I/O 操作，因为数据库需要先从非聚集索引中读取相关记录的指针，然后再到实际的数据页中去获取完整的行数据。
   2. 增加系统负载：由于回表查询涉及到多次的磁盘I/O操作，这无疑增加了数据库系统的负载，特别是在高并发场景下，可能会导致系统响应变慢，甚至出现性能瓶颈。
   3. 限制了索引的效果：大部分性能优势被额外的数据检索过程所抵消。

3. 索引的底层

3.1 B 树

B 树存储的是索引值和行数据，所以会导致一个节点存储的数据有限，一个节点共有 16KB 左右，一行数据大概是 1KB，加上索引后导致一个节点只能存储大约 15 个数据，因此存储结构约为：

1. 第一层：存 15 个数据
2. 第二层：存 15 * 15 个数据
3. 第三层：存 15 * 15 * 15 个数据
4. 以此类推

会发现，要想存储大量数据，树的高度会越来越高，那么在查询的时候，走的路径就越长，查询效率就越慢，效率也会很低。

3.2 B+Tree

为了解决 B 树存在的问题，MySQL 底层在 B 树的基础上，引入的 B+Tree，而 B+ 树的非叶子节点存储的是 索引值 和 指向下个索引值 的指针，一个索引值大于占 8B，而指针占 6B，总共 14B，因此一个节点可以存储 1170（16 * 1024B / 14B = 1170）左右个数据，因此树形结构为：

1. 一层存储 1170 个数据
2. 二层存储 1170*1170 个数据
3. 三层如果存储是叶子节点，那么存储的总数据量为 1170 * 1170 * 15 = 2000W+ 的数据

这就已经可以满足 MySQL 一般的表数据结构的存储。

此外，在 B+树的叶子之间也使用了双向指针，提高区间查询的性能。

在阿里巴巴开发规约中，提到 “单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表”，这也就意味着，数据库数据基本不会达到 2000+W，因此在使用 B+树 时，最多经过三次查找，就能查到数据。

4. 创建索引的要求

1. 单表索引不超过 5 个（根据业务可以更多）；
2. 联合索引的字段不超过 5 个（根据业务可以更多）；
3. 经常增删改的字段不适合创建索引；
4. 枚举值字段不适合创建索引；
5. 不经常修改，经常查询的字段适合创建索引；
6. 大长度的字段，可以设置前缀索引（为字段的前几个字符建立索引）

前缀索引：

CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,    username VARCHAR(255), email VARCHAR(255) );
-- 创建前缀索引
CREATE INDEX idx_username_prefix ON users (username(10));

使用指定字段开头的 10 个字符建立索引，如果长度不足 10 位，那么将使用全部的字段用于索引（不会出现为空的情况）。

5. 创建索引语法

1. 主键索引

   命名要求：pk_列名 （primary key —— 主键）

2. 普通索引

   语法：create index 索引名称 on 表名(具体的列名)

   命名要求：idx_列名

3. 唯一索引

   语法：create unique index 索引名称 on 表名(列名)

   命名要求：uk_列名 （unique —— 唯一）

4. 联合索引

   语法：create index 索引名称 on 表名(列1,列2)

   命名要求：idx_列1_列2