深度整理总结MySQL——索引正确使用姿势

前言

这篇文章是补充一些基本概念和实战的一些使用建议.

MySQL索引优缺点分析

✅ 索引的优势

1️⃣ 提升查询性能：索引能够显著加快数据查询速度，数据量越大，效果越明显。
2️⃣ 保证数据唯一性：唯一索引（UNIQUE）可以确保数据表中的某些字段不出现重复值，无需额外添加唯一性约束。
3️⃣ 优化分组与排序：索引可以加速 GROUP BY 和 ORDER BY 语句，减少分组与排序的计算开销。
4️⃣ 提升关联查询性能：在多表 JOIN 操作时，合理的索引（如主键索引、外键索引）能够大幅提高查询效率。
5️⃣ 优化范围查询：B+Tree 索引结构天然有序，使 BETWEEN、>、<、>=、<= 这类范围查询更加高效。
6️⃣ 提高数据库吞吐量：优化 SQL 执行效率，减少查询时间，从而提升数据库整体的吞吐能力。

⚠️ 索引的代价

1️⃣ 占用额外存储空间：索引会生成额外的磁盘文件，尤其是大数据量场景下，索引存储空间可能远超数据本身。
2️⃣ 影响写入性能：数据的增、删、改操作需要同步维护索引，导致写入性能下降。
3️⃣ 增加索引维护成本：每次 INSERT、DELETE 或 UPDATE 操作都可能引发索引的重构或调整，影响整体执行效率。
📌 结论：索引不是越多越好，而是要合理使用！
尽管索引带来的优势远大于劣势，但并不是索引越多越好。
过多的索引不仅会占用大量存储，还可能影响写入性能，因此合理规划索引策略，结合业务场景进行优化，才是最佳实践！ 🚀

如何合理建立索引?——关键原则总结

在设计索引时，仅仅考虑某个字段是否频繁出现在查询条件中是不够的。
一个优秀的索引策略需要综合考虑查询模式、数据特征以及索引类型，以实现最佳性能。以下是建立索引时需要遵循的重要原则：
1️⃣ 针对查询频率高的字段建立索引
对于经常用于 WHERE 条件的字段，应考虑创建索引，以加速查询。
2️⃣ 关联字段必须建立索引
主键（Primary Key）、外键（Foreign Key）及 JOIN 连接字段 应创建索引，以提升多表查询性能。
3️⃣ 选择区分度高的字段作为索引
索引字段的值应该尽可能具有高区分度（Cardinality），即唯一值较多，能有效减少查询扫描的行数。例如，索引 身份证号 是有效的，但索引 性别 作用不大。
4️⃣ 避免索引过长，可使用前缀索引
如果字段值较长（如 VARCHAR(255)），应避免全文索引，可以考虑前缀索引（PREFIX INDEX），这样既能提高查询效率，又能节省存储空间。
5️⃣ 联合索引需遵循最左前缀原则
创建联合索引时，应按照查询使用频率 和 过滤效果 来确定字段顺序。索引的匹配遵循最左前缀法则，即查询条件必须从索引的最左字段开始，否则索引可能无法生效。
6️⃣ 对于排序、分组字段建立索引
ORDER BY、GROUP BY 及范围查询（BETWEEN、>、<、>=、<=） 的字段适合建立索引，利用索引的有序性 可以加快查询。
7️⃣ 唯一索引不用于排序时，可考虑 Hash 结构
如果某字段仅用于唯一性约束，且不会用于范围查询或排序，可以使用 Hash 索引（如 MEMORY 表中的 HASH INDEX），查询性能更高。
8️⃣ 联合索引优于多个单列索引
相较于多个独立索引，联合索引（Composite Index） 更具优势，能有效减少回表查询（避免 Using filesort 和 Using temporary），提高查询效率。
📌 结论：索引优化是门技术活！
合理的索引策略不是盲目加索引，而是结合业务场景，选择合适的索引字段和索引类型，以最大化查询性能，同时避免过多索引带来的存储和维护开销。

重要的优化机制

索引覆盖——通俗的方式讲解

我们先从回表查询 说起——想象一下，你去图书馆查一本书的内容。
回表查询的情况：
你想知道 房间号、房型、价格、入住人姓名，但是前台的客房查询系统（索引） 只存了 房间号和房型，而入住人姓名和价格在纸质登记表（主键索引数据）里。
你先从 索引 里查到房间号，再去 纸质登记表 里翻找到对应的信息，才能拿到最终结果。
这个过程就类似 MySQL 先用索引查 ID，再回表查完整数据，也就是 回表查询。

索引覆盖的情况：
如果你 只想查房间号和房型，那么前台系统（索引） 里已经包含了这些信息，你直接就能得到结果，不用再翻纸质登记表（回表）。

• 这个时候，你查的信息 完全被索引覆盖，数据库不需要再去表里查完整数据，查询效率更高

举个例子,假设有个 hotel_rooms 表，字段如下：

room_id（主键）	room_type	price	guest_name
101	豪华大床房	500	张三
102	标准双床房	300	李四

🚨 回表查询

SELECT * FROM hotel_rooms WHERE room_type = '豪华大床房';

🔹 MySQL 先通过索引找到符合条件的 room_id，然后还要回表查 price 和 guest_name，才能返回完整数据。

✅ 使用索引覆盖

SELECT room_id, room_type FROM hotel_rooms WHERE room_type = '豪华大床房';

🔹 这次查询的 room_id 和 room_type 都在索引里，不用回表，直接返回结果！ 🔹 索引覆盖成功，查询更快！

📌 总结
索引覆盖就像 前台查询系统，如果你查的信息已经在索引里，直接返回；如果你查的信息不全，就得去翻纸质档案（回表）。
所以，合理设计索引结构，可以大大减少回表，提高查询速度！🚀

索引下推

我们用 酒店前台查询 的例子，和索引覆盖的方式类似.
📚 先来看普通查询（不使用索引下推）
假设你是 酒店前台查询入住的客人，你说：“我想查 住在标准双床房，且价格低于 400 元 的客人信息。”
前台（数据库）是这样做的：

• 先查索引：找到所有 “标准双床房” 的 room_id。
• 回表查询：去登记表（主键索引）里 一个个查 price，筛选出价格 < 400 的房间。
  ⚠ 问题：索引本来能筛选部分数据，但 price 这个条件要等回表后才能判断，多了一步，效率低！

✅ 使用索引下推优化查询
索引下推 就像前台自己变聪明了，能直接用索引来筛选一部分数据！
“标准双床房 & 价格 < 400” 这两个条件，前台能直接处理一部分，不用都去翻登记表！"

1. 先查索引，不仅找 room_type = “标准双床房”，还在索引层先筛选 price < 400 的记录！
2. 只对符合条件的 room_id 才回表查询，减少不必要的回表操作。
   🚀 优化点：减少了回表次数，提高查询速度！

🛠 结合 SQL 代码

SELECT guest_name FROM hotel_rooms 
WHERE room_type = '标准双床房' AND price < 400;

如果 room_type 和 price 都建了索引，MySQL 会使用索引下推：

• 先在索引中筛选：找到 room_type = ‘标准双床房’ 的记录，并且 过滤掉 price >= 400 的行！
• 只对符合条件的记录回表，查 guest_name。

📌 总结

优化方式	是否先用索引筛选 price	回表次数	查询速度
❌ 没有索引下推	❌ 否（先找 room_type，再回表筛选 price）	⚠ 回表次数多	⏳ 慢
✅ 使用索引下推	✅ 是（索引层先筛选一部分 price）	✅ 回表次数减少	🚀 快

索引跳跃式扫描

索引跳跃式扫描 是 MySQL 在查询时的一种优化策略，即使没有使用索引的最左列，它仍然可以部分利用索引来加速查询，而不必完全放弃索引。

📚 直观类比：查找书籍时的跳跃式翻找
假设你去图书馆找一本书，图书馆的书架是按照 类别（Category）+ 书名（Title） 的方式排序的，比如这样：

类别（Category）	书名（Title）
计算机	Java入门
计算机	Python进阶
计算机	数据结构与算法
历史	中国古代史
历史	世界历史
文学	红楼梦
文学	哈利波特

🎯 现实场景：你要找所有书名包含“历史”的书
但问题是：书架是按照类别 + 书名排序的，而你没有指定类别！！！
❌ 传统索引扫描（最左匹配失败，无法利用索引）
如果索引是按 (类别, 书名) 排序的，通常你得先指定类别才能用索引查找。但你没指定类别，所以数据库可能会直接全表扫描，一本一本地检查书名里有没有“历史”两字。
✅ 索引跳跃式扫描（Index Skip Scan）
数据库的优化策略是：
虽然你没指定类别，但系统可以按类别分组，一个类别一个类别地跳跃查找书名：

1. 先在“计算机”类别里查找，发现没有“历史”相关书籍，跳过。
2. 再到“历史”类别里查找，发现有《中国古代史》《世界历史》，记下来。
3. 最后查“文学”类别，发现没有匹配的书，跳过。
   这样就不用扫描所有的书，而是按类别跳跃式扫描索引，提高查询效率！ 🚀

🔍 代码示例
假设数据库表 t_books:

CREATE TABLE t_books (category VARCHAR(50),   -- 书籍类别title VARCHAR(100),     -- 书名PRIMARY KEY (category, title)  -- 联合索引（按类别+书名排序）
);

你想查所有书名是 “历史” 的书:

SELECT title FROM t_books WHERE title LIKE '%历史%';

🔥 MySQL 可能使用索引跳跃式扫描：

1. 先按 category 一组一组地跳跃扫描
2. 然后在每组里查 title 是否包含“历史”
   这样比全表扫描快很多！