聚簇索引、哈希索引、覆盖索引、索引分类、最左前缀原则、判断索引使用情况、索引失效条件、优化查询性能

聚簇索引

        聚簇索引像一本按目录排版的书，用空间换时间，适合读多写少的场景。设计数据库时，主键的选择（如自增ID vs 随机UUID）会直接影响聚簇索引的性能。

什么是聚簇索引？

• 数据即索引：聚簇索引的叶子节点直接存储整行数据（而不是指向数据的指针），因此表中的数据行物理上按索引键的顺序存储。

• 唯一性：一张表只能有一个聚簇索引（就像一本书只能按一种顺序排版）。

类比生活场景：

• 普通索引（非聚簇索引）：像一本书末尾的“关键词索引”，每个关键词后标注页码，你需要先查索引，再翻到对应页。

• 聚簇索引：像书的目录本身，章节内容严格按照目录顺序排版。找某一章时，直接按目录顺序翻到对应页即可，无需二次跳转。

优点：

• 查询快：范围查询（如 WHERE id BETWEEN 1000 AND 2000）效率高，因为数据在磁盘上是连续的。

• 排序快：如果按聚簇索引的字段排序（如主键），数据库可以直接按物理顺序读取，无需额外操作。

缺点：

• 插入/更新慢：新增数据时，如果新数据需要插入到中间位置，可能导致数据页分裂（类似电话簿插页时需要撕开重新装订）。

• 依赖主键设计：如果主键设计不合理（例如用随机UUID），可能导致存储碎片化，降低性能。

常见应用：

• 主键默认是聚簇索引（如MySQL的InnoDB引擎）。

• 适合频繁查询的字段：比如用户表的用户ID、订单表的订单时间。

哈希索引

        哈希索引通过哈希函数将数据的关键字（如手机号）直接转换成存储位置，适合快速等值查找，但无法支持范围查询或排序。

什么是哈希索引？

• 哈希函数：将任意长度的输入（如字符串、数字）转换成固定长度的哈希值（如 0x3A7F）。

• 直接定位数据：通过哈希值直接找到数据在内存或磁盘上的存储位置，无需遍历。

类比生活场景：

• 普通索引（如B树索引）：像图书馆按书名首字母分类的书架，需要按顺序查找。

• 哈希索引：像快递柜的编号，输入手机号就能秒定位到具体柜子。

优点：

• 查询极快：等值查询（如 WHERE id = 123）的时间复杂度接近 O(1)。

• 无数据顺序要求：适合随机读写场景（如用户登录、缓存）。

缺点：

• 不支持范围查询：无法高效执行 WHERE id > 100 或 ORDER BY。

• 哈希冲突：不同关键字可能生成相同的哈希值（如手机号A和B被分配到同一个柜子），需要额外处理（如链表串联）。

• 动态扩容成本高：数据量增长时，哈希表可能需要重新分配空间并重新计算所有哈希值。

常见应用：

• 内存数据库：如Redis的键值存储。

• 快速查找表：如用户Session、短链映射。

• 数据库的Hash Join：关联查询时临时使用哈希表加速。

哈希索引 vs B树索引

覆盖索引

使用普通索引时通常需要两步1.查找索引2.回表操作

使用覆盖索引则通常是直接从索引中直接获取数据，也就是省略了回表步骤

覆盖索引的优点：

        减少IO操作：查询时直接从索引中获取数据，无需访问表中的数据行，显著提高查询性能，特别是在数据量很大或者磁盘IO成本很高。

        索引中的数据：索引通常由被索引的列及其指向的行标识符组成，覆盖索引会存储更多的数据以满足查询需求

索引分类

按物理存储方式分类：

        聚簇索引、非聚簇索引

按功能分类：

        唯一索引、普通索引、全文索引、空间索引

按索引结构分类：

        BTree索引、Hash索引

按应用场景分类：

        单列索引、复合索引、覆盖索引

按存储引擎支持情况分类：

• InnoDB 支持 B-Tree 索引、唯一索引、全文索引（从 MySQL 5.6 开始）。
• MyISAM 支持 B-Tree 索引、全文索引。
• Memory 支持 Hash 索引和 B-Tree 索引。

最左前缀原则

        “最左前缀原则”是数据库（尤其是 MySQL 中）在使用复合索引时的一条重要规则，它规定：在复合索引中，查询条件必须从索引的最左侧开始，连续地使用索引列，才能使该索引被有效利用。

        如果创建了一个复合索引(col1, col2, col3)，当使用索引时必须是从左到右使用，如果跳过前面的去使用后面的话就会导致索引失效。

• 提高查询效率：
  通过利用复合索引，数据库可以直接定位到符合条件的数据行，减少扫描数据量，显著提升查询速度。

• 优化设计索引：
  在设计复合索引时，应考虑查询的常用模式，将最常用的查询条件放在索引的最左侧。例如，如果大多数查询都是根据 col1 进行过滤，再根据 col2 进行细分，那么将 col1 放在最前面可以最大程度发挥索引优势。

• 避免索引失效：
  如果查询条件没有覆盖复合索引的最左前缀，那么索引将无法被利用，这样会导致查询性能下降。因此在编写 SQL 语句和设计索引时，必须充分考虑这一原则。

= 和 IN 条件的顺序无关性：

        对于等值查询（=）和 IN 查询，条件的顺序不影响索引使用。MySQL 优化器会自动调整条件顺序以匹配索引的最左前缀。

WHERE b = 2 AND a = 1 AND c = 3;

索引 (a, b, c)：

• 优化器会将条件重排为 a = 1 AND b = 2 AND c = 3，完全匹配索引。

• 即使条件顺序不同，索引仍能覆盖所有列。

判断索引使用情况

以MySQL为例：

查看索引的使用统计：

-- 查看表的索引使用情况（需开启性能模式）
SELECT * FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_NAME = 'your_table';-- 查看索引的碎片化程度
SHOW INDEX FROM your_table;

分析查询是否使用索引：

EXPLAIN SELECT * FROM your_table WHERE indexed_column = 'value';

• 关键字段：

  • type：ref 或 range 表示使用了索引。

  • key：显示实际使用的索引名称。

  • rows：扫描的行数，值越小效率越高。

优化数据库表

OPTIMIZE TABLE table_name;

作用

1. 回收未使用的磁盘空间：
   删除大量数据后，会有空闲的磁盘空间，OPTIMIZE TABLE 可以释放这些空间。

2. 重新组织索引：
   对于索引密集的表，优化索引布局可以提高查询性能。

3. 提高数据读取速度：
   通过整理数据和索引，可以减少磁盘 I/O 操作，提升读取速度。

索引失效条件

        使用范围查询、不满足最左前缀原则、使用了不等于运算符、使用函数或表达式、隐式类型转换、模糊查询以通配符开头、使用OR、查询返回过多数据、数据分布不均匀、索引未覆盖查询（无法使用覆盖索引）、表统计信息不准确（数据库优化器依赖表的统计信息进行查询计划的选择。如果统计信息过时或不准确，可能导致错误地放弃索引）、使用了不支持索引的操作

如果避免索引失效：

• 遵循最左前缀原则，合理设计索引。
• 避免在索引列上使用函数、表达式或隐式类型转换。
• 在模糊查询中尽量避免 % 开头。
• 对 OR 条件进行优化，例如重构为 UNION ALL。
• 确保表的统计信息及时更新。
• 使用覆盖索引，减少回表查询。
• 确保查询返回的数据量较少。

优化查询性能的方法

减少请求的数据量：

• 只返回必要的列，最好不要使用SELECT *语句、使用LIMIT语句来限制返回的数据。
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别是在要查询的数据经常被查询时，使用缓存会极大的提升查询性能

减少服务器端扫描的行数：

• 使用索引来覆盖查询 
• 避免索引失效

查询重构优化：

• 避免使用复杂的JOIN和子查询
• 批量操作代替循环操作（循环执行单条 SQL 会增加网络和事务开销。）

  -- 不推荐：循环插入
  INSERT INTO logs (message) VALUES ('log1');
  INSERT INTO logs (message) VALUES ('log2');-- 推荐：批量插入
  INSERT INTO logs (message) VALUES ('log1'), ('log2');

数据库配置优化：

• 调整缓冲区大小

  -- 增加 InnoDB 缓冲池大小（通常设为物理内存的 70%~80%）
  SET GLOBAL innodb_buffer_pool_size = 4G;

• 配置数据库连接池（避免短连接：使用连接池（如 HikariCP、Druid）复用连接。

  参数调优：设置合理的最大连接数和空闲超时时间）

架构层面优化：

读写分离

• 主库：处理写操作和高一致性读。

• 从库：处理读操作，通过复制同步数据。

分库分表

• 垂直分表：将大字段（如 TEXT、BLOB）拆分到独立表。

• 水平分库：按业务分库（如订单库、用户库）。

• 水平分表：按哈希或范围分表（如 user_0、user_1）。