【MySQL】索引

目录

一、全表扫描

二、索引

三、总结

四、拓展

---

一、全表扫描

在 MySQL 中执行一个查询，如果查询涉及到的表没有适当的索引或者查询条件无法利用索引，那么 MySQL 就需要对整个表进行全表扫描

全表扫描意味着数据库需要从磁盘上读取表的所有数据行

全表扫描的大致过程：

1、当 MySQL 接收到一个查询请求时，它先分析 SQL 语句，确定需要执行的操作

2、如果查询条件无法利用现有的索引，则 MySQL 会选择全表扫描作为获取数据的方式之一

3、MySQL 根据表名和存储引擎定位到相应的物理文件

对于 InnoDB 存储引擎来说，表数据是以 B+Tree 的形式组织的，即使没有索引也会存在一个隐藏的主键索引

4、MySQL 读取数据页到缓冲池中，这是 InnoDB 存储引擎的一个内存区域，用于缓存数据页和索引页

数据库系统以数据页为单位从磁盘读取数据，每个数据页包含多个数据行。在 InnoDB 的缓冲池中的数据页使用了一个改进版的LRU算法：“二阶段LRU替换算法” 【文末有对该策略的简单介绍】的策略进行管理的，以减少磁盘I/O次数

5、MySQL 遍历每个数据页中的所有数据行，检查它们是否符合查询条件。对于符合条件的数据行，MySQL 会将其加入结果集

6、所有符合条件的数据行被收集并返回给客户端

全表扫描是一种低效的查询方式，特别是在大数据量的表上。为了提高查询性能，应该优先考虑使用索引，并优化查询语句和表结构。

二、索引

为了减少对数据库表的全表扫描，提升数据的访问速度，也就有了索引

MySQL 中的索引是一种特殊的数据结构，它可以帮助数据库系统更快地检索数据

类似于书籍的目录或索引页，可以快速找到所需的信息，而无需从第一页开始逐页查找

在数据库中，当你对表中的列创建索引时，MySQL 会维护一个额外的数据结构（例如 BTree、Hash 等），这个结构包含了指向数据行的指针，这些指针是按照索引列的值排序的。

当查询涉及到该索引列时，数据库就可以使用索引来加速数据的查找过程

这里有几种常见的索引类型：

1、主键索引 (Primary Key Index)

• 每个表只能有一个主键
• 主键必须是唯一的，并且不能为 NULL
• 通常情况下，主键索引是聚集索引（Clustered Index），这意味着表的数据行本身按照主键的顺序存储

2、唯一索引 (Unique Index)

• 保证索引列中的值都是唯一的，但允许有 NULL 值
• 与主键类似，但一个表可以有多个唯一索引

3、普通索引 (Index 或 General Index)

• 不需要具有唯一性，是最基本的索引类型

4、全文索引 (Full-text Index)

• 用于全文搜索，适用于大型文本字段的搜索操作

5、空间索引 (Spatial Index 或 R-tree Index)

• 用于地理空间数据的索引，如 GIS 应用中的坐标点

6、多列索引 (Multi-column Index)

• 同时基于多个列创建的索引，可以提高涉及这些列组合的查询性能

7、覆盖索引 (Covering Index)

• 包含了查询所需的全部列的索引，使得数据库不需要访问表中的实际数据行，从而提高性能

8、哈希索引 (Hash Index)

• 使用哈希算法来存储索引键，适用于等值查询

9、自定义索引 (Custom Index)

• MySQL 的一些存储引擎支持特定类型的索引，例如 InnoDB 存储引擎支持B树索引，而 MyISAM 支持 BTree 和哈希索引

创建索引可以显著提升查询性能，但同时也有一些缺点，比如增加了写入操作的时间开销（因为每次插入、更新或删除数据时都需要维护索引），并且占用更多的磁盘空间。因此，在设计数据库时，需要权衡索引带来的好处与可能的负面影响。

在这里也存在一些误解！！！

误解1：有些人认为应该尽可能多地为表中的字段创建索引，以确保每个可能的查询都能从中受益

正确做法：实际上，这种做法可能会导致不必要的性能问题。过多的索引不仅会增加存储空间的需求，还会降低写操作（如 INSERT、UPDATE、DELETE）的速度，因为每次写操作都需要更新相关的索引

正确的做法是根据具体的查询模式和业务需求来合理选择需要创建索引的字段

误解2：有些人则担心索引会消耗大量空间，并且会严重影响写操作的性能，因此倾向于尽量少建索引

正确做法：索引确实会消耗一定的存储空间，会对写操作产生一定影响，但合理的索引策略可以极大地提高读取操作的性能。在大多数情况下，索引带来的性能提升足以弥补其带来的负面影响

正确的做法是综合考虑读写性能的需求，平衡索引的数量和类型

误解3：有些人认为唯一性的验证应该完全由应用程序层来处理，即先查询数据库是否存在相同的记录，然后再决定是否插入新记录

正确做法：使用唯一索引来确保数据的唯一性是一个更高效且安全的方法。在数据库级别实现唯一性约束可以防止并发插入相同记录的问题，在数据层面上提供更强的一致性和完整性保障。此外，唯一索引也可以被用来优化查询性能

三、总结

1、不建议无限制地创建索引，而应该基于查询需求和业务场景来选择合适的索引

2、尽管索引会带来一些存储和写入成本，但在许多情况下，它所带来的读取性能提升是值得的

3、业务逻辑中的唯一性约束最好通过唯一索引来实现，这不仅可以提高数据一致性，还可以改善查询性能

在设计数据库索引策略时，重要的是要找到一个平衡点，既要满足查询性能的要求，也要考虑到存储空间和写入性能的影响

四、拓展

1）二阶段 LRU 替换算法

InnoDB 的二阶段 LRU 替换算法分为两个列表：

• 旧数据列表（Old List）：存放较早访问的数据页
• 新数据列表（New List）：存放最近访问的数据页

当一个新的数据页被加载到缓冲池时，它会被添加到新数据列表的末尾

随着时间的推移，如果数据页不再被访问，它会被移动到旧数据列表中

当缓冲池满时，InnoDB 会从旧数据列表中淘汰数据页

2）LFU 策略

虽然 LFU 策略在某些缓存系统中被使用，但它并不是 InnoDB 缓冲池所采用的主要策略

然而，有些数据库管理系统或缓存系统可能会使用 LFU 策略来管理缓存资源，特别是当数据访问频率变化较大时，LFU 可能会比 LRU 更加有效

一  页  知  秋，奥  妙  玄  心