从原理到实战的完整指南
目录导读
- 索引的本质与类型 – 理解索引为什么能加速查询
- 创建索引的核心原则 – 避免“索引无用”陷阱
- 常见场景的索引设计 – 单列、联合、覆盖索引怎么选
- 索引维护与性能监测 – 如何发现并修复问题索引
- 高频问答 – 解决开发者最常见的索引困惑
索引的本质与类型
Q：索引为什么能提升查询速度？
索引本质上是一种“有序的查找结构”，想象一本没有目录的《百科全书》——你要找“索引”这个词，只能从第1页翻到第1000页，而索引就像书末的关键词目录，直接告诉你“索引”在第312页，数据库索引通过B+树、哈希表等结构，将全表扫描（O(n)）降为对数级（O(log n)）或常数级（O(1)）查找。
常见索引类型：
- B+树索引（默认InnoDB）：支持范围查询、排序、模糊匹配（如
like 'ab%'），数据按页存储，非叶子节点仅存键值,叶子节点存完整行或主键。 - 哈希索引（Memory引擎）：等值查询极快（如
where id=100）,但不支持范围查询和排序。 - 全文索引：用于
MATCH…AGAINST全文搜索，含词根处理、停用词过滤。 - 空间索引（R树）：处理地理坐标数据。
关键认知：索引并非“越多越快”，每增一个索引，写入（INSERT/UPDATE/DELETE）需多维护一份结构,磁盘IO和日志量都会增加。
创建索引的核心原则
Q：如何判断一个字段是否该建索引？
遵循“高区分度、高频查询、低写入负载”三角原则。
| 场景 | 建议创建索引 | 不建议创建索引 |
|---|---|---|
| 字段值几乎唯一（如用户ID、订单号） | ||
| 常用WHERE条件（如status=‘active’） | ||
| JOIN关联字段（如user_id） | ||
| 性别字段（只有男/女） | ×（区分度太低） | 全表扫描更快 |
| 频繁更新的字段（如last_login_time） | 慎用（索引维护成本高） | 考虑复合索引覆盖 |
核心原则一：最左前缀法则
对于联合索引 (a, b, c)，查询条件必须从最左列开始。WHERE a=1 AND b=2 能使用索引，但 WHERE b=2 则无法使用，设计时，把区分度高的字段放最左边。
核心原则二：避免冗余索引
例如已有 (a, b) 索引，再建 (a) 基本是浪费——前者已覆盖后者，可通过 pt-duplicate-key-checker 或 information_schema 检查。
核心原则三：覆盖索引击败回表
若查询的列全部在索引中（如 SELECT a,b FROM t WHERE a=1，且索引含a,b），则无需回表查询数据行，性能提升显著，常用技巧：将高频查询的字段加入复合索引。
常见场景的索引设计
场景1：高并发订单查询
需求：按用户ID和创建时间查最近订单。
错误设计：单独索引 (user_id) 和 (create_time) → 数据库可能只用一个索引，另一个需全表过滤。
正确设计：联合索引 (user_id, create_time)，原因：先按用户过滤（高区分度），再按时间排序（利用B+树有序性）。
-- 高效查询（利用索引排序，避免filesort） SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;
场景2：模糊搜索（like）
正确用法：WHERE name LIKE '张%' 可以使用索引。反例：'%张%' 无法使用前缀匹配，只能全表扫描。
优化方案：使用全文索引或Elasticsearch等搜索引擎。
场景3：分页大偏移量
传统 LIMIT 100000, 20 需扫描10万行后丢弃。优化：使用“游标分页”（延迟关联）：
-- 先用索引快速定位起始ID SELECT t.* FROM (SELECT id FROM table WHERE … ORDER BY id LIMIT 100000,20) AS tmp JOIN table AS t ON t.id = tmp.id;
原理：子查询在索引上完成分页（无需回表）,再通过主键关联回原表。
场景4：函数操作导致索引失效
WHERE DATE(create_time) = '2023-01-01' 会使 create_time 索引失效。正确写法：
WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';
同样，WHERE id + 1 = 100 应改为 WHERE id = 99。
索引维护与性能监测
Q：如何发现“坏索引”？
- 慢查询日志（
slow_query_log）中，若查询耗时久且线程状态为“Sending data”,可能是索引缺失或使用不当。 EXPLAIN分析：关注type字段（ALL=全表扫描，ref/range=索引利用良好）、Extra中的Using filesort（需在索引设计中解决排序）。sys.schema_unused_indexes：统计从未使用的索引,可考虑删除。
索引维护策略
- 定期重建碎片：
ALTER TABLE t ENGINE=InnoDB（重建表，消除碎片）或OPTIMIZE TABLE（生产环境需谨慎，建议低峰期操作）。 - 监控索引使用率：通过
performance_schema.table_io_waits_summary_by_index_usage获取每次查询的IO次数。
高频问答
Q1：主键是自增ID好还是UUID好？
答：自增ID更好，B+树插入时，自增ID只需在末尾追加，页分裂少；UUID随机无序，频繁页分裂导致性能下降，若有跨库合并需求，可考虑雪花算法（Snowflake）生成有序全局ID。
Q2：联合索引中，字段顺序是否影响查询？
答：极大影响，遵循“等值查询优先，其次范围查询，最后排序”顺序，例：WHERE a=1 AND b>10 ORDER BY c，索引设计为 (a, b, c)，a 等值过滤，b 范围过滤，c 利用索引排序。
Q3：为什么给大表加索引会锁表？
答：MySQL 5.6+ 支持在线DDL（ALGORITHM=INPLACE），但若表数据量大，仍可能导致主从延迟或IO升高，建议使用 pt-online-schema-change 工具无锁添加索引。
Q4：索引字段为NULL怎么办？
答：B+树索引不存储NULL值，WHERE column IS NULL 无法使用索引。建议：用默认值（如空字符串、0）替代NULL，并对“默认值”业务场景合理处理。
Q5：为什么我的查询明明有索引，却走了全表扫描？
常见原因：
- 使用了
OR条件，且部分字段无索引（需改为UNION ALL）。 - 数据量少（MySQL优化器认为全表扫描比索引查找更快）。
- 隐式类型转换：如
WHERE id = '123'（id为整型）导致索引失效。 - 查询结果超过表数据20%左右，行数过多时索引可能被放弃（可强制索引
FORCE INDEX）。
合理创建索引并非一劳永逸，建议在开发阶段通过慢查询日志和 EXPLAIN 持续调优，生产环境每隔半年审查一次索引使用情况。索引是数据库优化的起点，但靠索引解决所有问题是认知的终点——当业务成为瓶颈时，分库分表、读写分离、缓存层才是答案。
