本文总结数据库索引的核心知识包括索引的作用、创建方式、索引的自动维护机制以及如何在查询速度与空间/写入开销之间做权衡。以 MySQLInnoDB / B 树索引为主要示例。一、索引的作用索引本质是一种排好序的数据结构多数用 B 树核心作用如下作用说明加速查询把全表扫描O(n)变成树查找O(log n)这是索引最主要的价值加速排序/分组ORDER BY、GROUP BY命中索引可省去额外排序加速连接JOIN 时关联字段有索引能大幅提速保证唯一性唯一索引可强制列值不重复覆盖索引查询字段全在索引里时无需回表读数据行代价占用额外存储空间写操作INSERT/UPDATE/DELETE需同步维护索引会变慢。所以索引不是越多越好。二、如何创建索引以 MySQL 为例1. 建表时创建CREATETABLEusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,-- 主键索引emailVARCHAR(100),nameVARCHAR(50),ageINT,UNIQUEKEYuk_email(email),-- 唯一索引KEYidx_name(name),-- 普通索引KEYidx_name_age(name,age)-- 联合索引);2. 对已有表创建-- 普通索引CREATEINDEXidx_nameONusers(name);-- 唯一索引CREATEUNIQUEINDEXuk_emailONusers(email);-- 联合索引多列CREATEINDEXidx_name_ageONusers(name,age);-- 或用 ALTER TABLEALTERTABLEusersADDINDEXidx_age(age);3. 查看 / 删除SHOWINDEXFROMusers;-- 查看索引DROPINDEXidx_nameONusers;-- 删除索引三、语法解读表名(列1, 列2, ...)CREATEINDEXidx_name_ageONusers(name,age);│ │ └──┬───┘ 索引名称 表名 索引列users—— 表名表示这个索引建在users表上(name, age)—— 列名列表表示用name和age这两列的值来构建索引联合索引复合索引当括号里有多个列时就是联合索引。它会先按name排序name相同时再按age排序nameageAmy18Amy25Bob20Bob22最左前缀原则联合索引(name, age)的列顺序很重要查询能否用上索引取决于是否从最左列开始WHEREnameBob-- ✅ 用上索引命中最左列 nameWHEREnameBobANDage22-- ✅ 用上索引name age 都命中WHEREage22-- ❌ 用不上跳过了最左列 name类比「电话簿」先按姓排、姓相同再按名排。知道姓能快速定位只知道名不知道姓还是得一页页翻。四、更新字段时索引由引擎自动维护对表做 INSERT / UPDATE / DELETE 时数据库引擎会在同一个事务里自动把相关索引一起改掉保证数据和索引始终一致无需手动维护。以UPDATE users SET age 26 WHERE id 1存在索引idx_age(age)为例1. 修改数据行聚簇索引 / 主键那份真实数据 2. 从 idx_age 索引里删掉旧值 age25 的索引项 3. 往 idx_age 索引里插入新值 age26 的索引项并重新排到正确位置 ↑ 这些都在一个事务里原子完成要么全成功要么全回滚关键点只维护「被改动的列」相关的索引。若只UPDATE name则idx_age不受影响。索引的写入代价操作索引层面发生的事INSERT每个索引都要插入一条新索引项并维持有序DELETE每个索引都要删除对应索引项UPDATE若改的列在索引中 → 删旧项 插新项可能引发 B 树的页分裂/合并所以索引越多写操作越慢——读的时候爽写的时候还债。认知要点顺序会自动维持age 从 25 改成 26索引里位置会被自动挪到正确排序位。崩溃也不怕靠 redo log / WAL 等机制宕机重启后数据和索引依然一致。可能变慢的场景频繁更新索引列、或更新导致 B 树页分裂时写入开销更明显。例外——全文索引某些搜索引擎类索引如 Elasticsearch可能是异步/近实时更新但普通 B 树索引都是同步实时的。五、如何权衡查询提速 vs 空间/写入开销这本质是一个成本收益分析。1. 量化「收益」——查询快了多少核心工具EXPLAIN/EXPLAIN ANALYZEEXPLAINANALYZESELECT*FROMusersWHEREnameBobANDage22;重点指标指标含义加索引前后对比type访问类型ALL全表扫描→ref/range走索引就是收益rows预估扫描行数从「几百万」降到「几十」就是巨大收益key实际用的索引从NULL变成索引名 生效了实际执行耗时ANALYZE 给出真实时间前后各跑一次直接对比判断原则rows大幅下降如 100万 → 100说明索引价值高值得加。2. 量化「成本」——空间和写入开销查看索引占用空间SELECTindex_name,ROUND(stat_value*innodb_page_size/1024/1024,2)ASsize_mbFROMmysql.innodb_index_statsWHEREtable_nameusersANDstat_namesize;索引总大小可能达到数据本身的 20%~50% 甚至更多。写入放大方面表上每多一个索引写操作就多维护一份。写多读少的表要克制读多写少的表可以多建。3. 平衡的决策框架场景建议高频查询 选择性高区分度大值得建收益远大于成本低频查询一天几次通常不值得写密集表严格控制索引数量只留最关键的选择性低如性别、状态只有几个值别建扫描比例太高索引意义不大多个查询条件优先用联合索引覆盖多个查询4. 用更少索引拿更多收益的技巧联合索引 多个单列索引一个(a, b, c)联合索引能同时服务a、a,b、a,b,c三类查询。覆盖索引让索引直接包含查询要的列避免回表。CREATEINDEXidx_coverONusers(name,age);SELECTname,ageFROMusersWHEREnameBob;-- 无需回表定期清理无用索引-- MySQL 8.0SELECT*FROMsys.schema_unused_indexes;控制单表索引数量经验值单表一般不超过 5 个。5. 完整评估流程1. 找出慢查询 → 开慢查询日志 / 监控 2. EXPLAIN 分析瓶颈 → 确认是不是缺索引 3. 试建索引 → 在测试环境加上 4. 再次 EXPLAIN 压测 → 量化查询提速多少 5. 查索引占用空间 → 评估空间成本 6. 评估写入影响 → 这张表写频繁吗 7. 收益 成本 ? 保留 : 放弃 8. 上线后持续监控 → 定期清理无用索引六、一句话总结对高频、选择性高的查询建索引收益大用联合索引和覆盖索引减少索引数量成本低对写密集表和低频查询保持克制上线后靠监控持续做减法。
