凌晨十二点四十分监控群里弹出一条慢查询告警。我点开详情一张记录用户操作行为的日志表单条 SELECT 跑了 17 秒扫描行数三百五十万。执行计划里 type 列写着 ALL问题的原因一眼就看清了——这张接近千万行的表查询条件字段上压根没有索引。我在服务器上敲下 MySQL 创建索引的语句几秒钟后同样的查询降到几十毫秒。一个语法上的小动作性能跨度接近千倍。这就是索引的价值它把一个复杂度 O(n)、要翻遍全表的问题改成了 O(log n)、只需要跳几次 BTree 就能定位的问题。这篇文章把 MySQL 索引从创建语法到底层原理、从类型选型到失效排查、再从冗余清理到大表操作完整讲一遍。适合刚接触 MySQL 的初级开发也适合那些已经会建索引但说不清“为什么这样建”的中级工程师。为了让内容好读我会尽量用实际场景说话少堆理论术语。1. 全表扫描为什么慢先搞懂索引在优化什么1.1 没有索引时InnoDB 是怎么找数据的InnoDB 存储引擎管理数据的最小单位是页Page默认大小 16KB。数据行按主键顺序存在页里页和页之间通过双向链表串联最终构成 BTree 的叶子节点层。当一张表没有可用索引时查询执行器只能从第一个数据页开始把每一行数据都读出来逐行比对 WHERE 条件这个过程叫全表扫描。全表扫描最直观的代价是磁盘 IO。假设一行记录平均占用 500 字节一个 16KB 的页大概能装 32 行300 万行就对应约 9 万多个数据页。真要全部读出来磁盘 IO 次数以万计再快的 SSD 也扛不住这种遍历。等表到了千万行、亿行级别全表扫描基本等于把整个磁盘的 IO 预算一次性打光。这也是为什么一张表刚建立、只有几百行数据时完全不需要建索引——顺序扫描很快索引的存在反而增加写入成本。换句话说索引不是越多越好它解决的是“数据量大到顺序扫描不可接受”的问题。1.2 BTree 为什么能让查询快几个数量级BTree 是多路平衡搜索树核心特点是数据全在叶子节点非叶子节点只存键值和下一层指针。这样做的好处是每个页能容纳大量键值树的高度被压得很低。以主键 BIGINT8 字节 指针6 字节为例一个 16KB 的页大约可以塞下 1170 个键值对。三层 BTree 最多能存 1170 * 1170 * 1170约 16 亿行。也就是说即使一张表有上亿行沿着主键索引查找一条记录也只需要读三个页根节点页、中间节点页、叶子节点页。三次磁盘 IO 完成定位和全表扫描上万个页相比差距是数量级的。这个原理同样解释了为什么二级索引非主键索引的叶子节点里存的是主键值而不是数据行的物理地址。InnoDB 的表本身就是聚簇结构数据行按主键排序挂在主键索引上。二级索引负责先把主键找到再根据主键回到聚簇索引里捞数据行后者就是常说的回表。理解这一点后面讲覆盖索引和索引失效时会顺畅很多。1.3 什么场景才真正需要建索引一句话只要某条 SQL 里出现了 WHERE 条件、JOIN 关联、ORDER BY 排序、GROUP BY 分组而数据量又大到顺序读取不可接受这些字段就值得考虑进索引。但有两个前提必须评估。第一是区分度。性别、状态、是否删除这类字段可选值就那么两三个每条记录都能命中其中一种即使建了索引优化器算一下发现全表扫描更便宜最终也不会用。真正适合建索引的字段数据分布要足够“散”比如用户 ID、订单号、手机号、时间戳。第二是查询频率。一个字段虽然区分度很高但一年到头没人拿它做查询条件建了也是纯消耗。索引在写入时要同步维护插入一条记录对应索引树也要更新写入性能会随着索引数量增加而下降。所以索引设计的第一原则是围绕真实业务查询来建而不是为了“集齐所有字段”去建。2. 创建索引的四种写法语法细节与适用边界2.1 CREATE INDEX 和 ALTER TABLE到底有什么区别建索引的首选语法是CREATE INDEX通用形式如下CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (column1, column2, ...);比如给 user_operation_log 表的 create_time 字段建普通索引CREATE INDEX idx_create_time ON user_operation_log (create_time);如果要求字段值唯一就加 UNIQUECREATE UNIQUE INDEX uk_username ON t_user (user_name);另一种常用写法是ALTER TABLEALTER TABLE t_user ADD INDEX idx_phone (phone); ALTER TABLE t_user ADD UNIQUE INDEX uk_email (email);这两者在绝大多数场景下效果一样。唯一需要记住的差异是CREATE INDEX不能创建主键索引而ALTER TABLE可以ALTER TABLE t_user ADD PRIMARY KEY (id);约束、索引管理跟主键绑定的场景比如修改表结构并指定主键时只能用 ALTER TABLE。平时线上加普通索引两种写法都行我个人更习惯用ALTER TABLE ... ADD INDEX因为它和删除索引、改字段类型等 DDL 操作放在一起读起来更统一。2.2 建表时直接定义索引的写法如果表还没建可以在CREATE TABLE语句里直接声明索引一张表的所有索引归属一目了然CREATE TABLE t_user ( id BIGINT UNSIGNED AUTO_INCREMENT, user_name VARCHAR(50) NOT NULL, email VARCHAR(100) DEFAULT NULL, phone VARCHAR(20) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_user_name (user_name), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里KEY是INDEX的别名随便用哪个。建表时就设计好索引能避免后续上线时补索引带来的大表 DDL 风险。不过实际业务里表结构设计初期往往预测不到未来的查询路径所以“先建表、观察慢查询、再补索引”也是正常节奏。真正要注意的是不要把建表时想到的索引一股脑全加上等数据量大了再做减法删索引同样有成本。2.3 SHOW INDEX一个容易被忽略的体检工具索引建好之后用这个命令可以查看一张表的全部索引信息SHOW INDEX FROM t_user;输出结果里我最关心的三列是Key_name索引名称用来区分是哪条索引Seq_in_index字段在索引中的顺序从 1 开始复合索引尤其要注意Cardinality索引基数估算值也就是大致有多少个不同的值。Cardinality 是个估算值它决定了优化器认为这条索引的区分度够不够。如果一张表几百万行但 Cardinality 只有几十说明这个字段的值高度重复优化器大概率会放弃索引。想及时刷新这个统计值可以手动执行ANALYZE TABLE t_user;。我有个习惯数据发生大规模变更后跑一次 ANALYZE避免统计信息滞后导致执行计划判断失误。3. 索引类型选型B-Tree、前缀索引、全文索引的取舍3.1 B-Tree 索引默认是它足够应对绝大多数场景InnoDB 引擎默认使用 B-Tree 索引准确说是 BTree。创建普通索引、唯一索引时生成的都是这类结构。它天然支持等值查询、范围查询和排序比如SELECT * FROM orders WHERE status 1 AND create_time 2024-01-01;B-Tree 索引都能提供高效定位。由于数据在叶子节点上已经排好序ORDER BY 在索引覆盖范围内时可以省掉 filesort 排序操作。MySQL 8.0 中还有降序索引支持ORDER BY create_time DESC直接反向扫描这里不展开实际用到的场景不算多。3.2 前缀索引处理大字段的省钱方案拿邮箱地址、URL、富文本这类 VARCHAR 长字段做索引时整列建索引会有两个问题索引占用空间大页容量变小BTree 高度可能上升写入时维护成本变高。更经济的办法是做前缀索引只取字段前 N 个字符进索引ALTER TABLE t_user ADD INDEX idx_email_prefix (email(20));问题是 N 取多少取少了区分度不够取多了没起到省空间的效果。我一般是这么操作的先用 SQL 测不同前缀长度的区分度找到长度翻倍但区分度不再明显提升的那个临界点。SELECT COUNT(DISTINCT email) AS total, COUNT(DISTINCT LEFT(email, 10)) AS prefix_10, COUNT(DISTINCT LEFT(email, 15)) AS prefix_15, COUNT(DISTINCT LEFT(email, 20)) AS prefix_20 FROM t_user;比如 total 是 100 万prefix_10 是 80 万prefix_15 是 99 万prefix_20 是 100 万那取 15 就很划算。代价是前缀索引不能用来做覆盖索引扫描也不适合 ORDER BY email适用范围要提前想清楚。3.3 全文索引MySQL 里的轻量级全文检索方案搜索引擎或复杂全文检索交给 Elasticsearch 这类专业组件但项目规模不大、不想引入额外中间件时MySQL 全文索引是够用方案。MySQL 5.6 开始 InnoDB 支持 FULLTEXT5.7 之后内置 ngram 分词器中文分词也能做。ALTER TABLE articles ADD FULLTEXT INDEX ft_content (title, content) WITH PARSER ngram;查询语法SELECT * FROM articles WHERE MATCH (title, content) AGAINST (数据库 索引 IN NATURAL LANGUAGE MODE);注意全文索引和普通索引的维护逻辑完全不同它是通过倒排索引实现的。创建全文索引时要指定 WITH PARSER ngram 才能处理中文文本。更要清楚的是全文索引不是 LIKE 替代品LIKE %关键词%依然不会走全文索引如果需要做分词、打分、高亮MySQL 的能力始终有限别硬撑。3.4 函数索引与虚拟列解决“对字段做了处理就失效”的问题很多人在 WHERE 里写WHERE LOWER(email) xxxyyy.com结果发现 email 字段上的索引根本没走原因就是对列使用了函数之后索引无法匹配原始值。MySQL 8.0.13 开始支持直接把表达式放进索引定义CREATE INDEX idx_lower_email ON t_user ( (LOWER(email)) );8.0.13 之前的版本则用虚拟列索引的方案ALTER TABLE t_user ADD COLUMN email_lower VARCHAR(100) GENERATED ALWAYS AS (LOWER(email)) VIRTUAL; CREATE INDEX idx_email_lower ON t_user (email_lower);本质上是一样的思路把计算好的值存成独立一列再对该列建索引查询时也使用同样表达式。我建议升级 8.0 后用原生函数索引逻辑清晰避免在应用层额外维护一个冗余字段。3.5 哈希索引和空间索引简单提一下为什么很少碰InnoDB 不支持显式创建哈希索引默认 B-Tree 里也看不到 HASH 字样。MEMORY 引擎支持哈希索引但只适合等值查询无法处理范围查询和排序生产环境用得极少。空间索引SPATIAL是给 GIS 数据准备的日常业务几乎不会涉及。普通项目把精力放在 B-Tree 和前缀索引上就足够了。4. 复合索引的字段顺序最左前缀原则与排序优化4.1 最左前缀原则的底层逻辑复合索引在 BTree 中按字段从左到右排序。以 (a, b, c) 为例先按 a 排序a 相同的再按 b 排b 也相同再按 c 排。这个有序性是复合索引的核心优势也带来一个约束查询条件必须从最左边的字段开始连续匹配才能利用索引排序结构。换句话说(a, b, c) 索引实际能高效支持这些匹配组合单独 aababc。查询条件如果是 b或者 bc索引就没法直接用因为跳过了最左边的 aBTree 的排序顺序对 b 来说并不是全局有序的。这就是最左前缀原则。一个常见误解是“WHERE a1 AND c2 完全没用到索引”。实际上 a1 的条件能走索引定位到 a1 的小范围后MySQL 的索引下推Index Condition PushdownICP会把 c2 的条件传给存储引擎在读取二级索引时就过滤掉大部分记录减少回表次数。c 没有参与索引定位但也没白搭性能仍比全表扫描强很多。4.2 等值条件放前面范围条件放后面复合索引字段顺序最基本的经验法则是等值条件放前面范围条件放后面。原因在于一旦索引扫描进入了范围匹配后面的字段就无法继续使用索引了。举个例子有一张订单表查询模式绝大多数是WHERE status 1 AND create_time 2024-06-01。两种索引方案对比如下索引执行情况(status, create_time)status 等值定位create_time 范围扫描两个字段都发挥作用(create_time, status)create_time 范围扫描后status 无法继续用索引过滤建 (create_time, status) 时索引已经按 create_time 排好序范围扫描一下劈出大片数据status 是索引里的第二个字段但这部分数据内部的 status 并不是全局有序的无法继续索引查找只能回表后用 status 过滤。所以“区分度高的字段放前面”这类笼统说法不总是对核心还是看字段是等值还是范围。4.3 让 ORDER BY 也搭上索引的顺风车复合索引的叶子节点天然有序如果 ORDER BY 字段恰好和索引顺序一致MySQL 可以顺着索引顺序读取避免 filesort。这类优化在高频查询里收益很明显。典型场景分页查某个用户的订单按创建时间倒序取前 20 条。SELECT * FROM orders WHERE user_id 123 ORDER BY create_time DESC LIMIT 20;此时索引 (user_id, create_time) 是最优解user_id 先定位create_time 在 user_id 内部有序直接反向扫描取 20 条即可。反过来如果索引是 (create_time, user_id)user_id 不是索引第一列这个查询还需要把 create_time 满足条件的数据全部抓出来再过滤效率完全不同。设计复合索引时可以用这个思路自测把所有常用查询里的 WHERE、ORDER BY、GROUP BY 字段列出来把高频组合优先放进同一索引并保证排序字段跟在等值条件字段之后。一个复合索引同时服务过滤和排序常常比单独建两个单列索引更实用。5. 执行计划与索引失效排查为什么建了索引还是慢5.1 先学会看 EXPLAIN 再谈优化索引建完不等于一定被使用优化器会基于表的统计信息、数据分布、索引成本综合决定。判断 SQL 是否真的用到索引靠的是执行计划EXPLAIN SELECT * FROM orders WHERE user_id 123 ORDER BY create_time DESC LIMIT 20;重点看四列type访问方式。性能从好到差大致是 system const eq_ref ref range index ALL。看到 ALL 代表全表扫描index 代表全索引扫描都不是理想状态业务查询中至少希望到 range更好的是 ref 或 const。key实际命中的索引名。如果为 NULL说明索引没被用上。rows优化器估算需要扫描的行数数字越小越好。注意这是估算值不一定精确。Extra这里最容易暴露问题。出现Using filesort表示排序没走索引要在内存或磁盘额外排序出现Using temporary说明用了临时表通常和 GROUP BY、去重关联出现Using index是好事表示覆盖索引扫描避免回表。5.2 索引失效场景清单踩一次就记住了下面这些坑基本每个用 MySQL 的人都会至少遇到一次。第一类对索引列做函数或计算-- 错误示例即使 create_time 有索引也不会走 SELECT * FROM orders WHERE DATE(create_time) 2024-06-01;正确写法是把范围条件化开SELECT * FROM orders WHERE create_time 2024-06-01 AND create_time 2024-06-02;第二类隐式类型转换如果字段是 varchar条件里传了数字MySQL 会把字段值转成数字再比较索引就失效了-- 假设 mobile 是 varchar SELECT * FROM t_user WHERE mobile 13800138000;这里 mobile 列需要发生类型转换导致无法索引查找。正确写法是传字符串SELECT * FROM t_user WHERE mobile 13800138000;第三类LIKE 通配符放开头SELECT * FROM t_user WHERE name LIKE %小明;索引无法支持前导模糊匹配因为 BTree 是前缀有序的。只有 LIKE 小明% 这种前缀匹配才能走索引。第四类OR 连接的条件不完整SELECT * FROM orders WHERE user_id 123 OR status 1;如果只有 user_id 有索引而 status 没有优化器很难合并两条索引路径最稳妥的方案是全表扫描。解决方法是给两个字段都合理的索引或者改写成 UNION 查询让各自走各自的索引。第五类复合索引上跳了最左字段前面讲过多。索引 (a, b, c) 上直接查 b基本等于没用。第六类优化器认为全表扫描更便宜数据量小、字段区分度低、统计信息陈旧都会导致优化器放弃索引。这类情况严格说不叫失效而是优化器做了成本权衡。这时别急着FORCE INDEX硬刚先检查统计信息是否需要刷新数据分布是否真的适合这条索引。5.3 覆盖索引与回表为什么 SELECT 字段也有讲究InnoDB 的二级索引叶子节点存的是索引字段 主键值。例如索引 (user_id, create_time) 上的记录只有这两个字段和主键 id。如果查询需要的字段恰好都在索引里MySQL 扫描完索引就能拿到全部结果不需要回聚簇索引里再捞一遍这就是覆盖索引。-- 索引 (user_id, create_time) SELECT user_id, create_time, id FROM orders WHERE user_id 123;执行计划里 Extra 一旦显示Using index说明这条查询已经被二级索引覆盖省掉了回表 IO。相反如果写成SELECT *MySQL 必须根据主键回表取剩余字段。当查询频率极高时把高频 SELECT 的字段塞进复合索引末尾换取 Using index是投入产出比很高的一步优化。但别走极端——为了一两个查询把整张表的所有字段都塞进索引索引会膨胀得比表还大得不偿失。6. 实践中的索引维护别建完就忘6.1 用慢查询日志定位“该建没建”的索引索引设计很难一次到位线上业务演进会不断产生新的查询模式。我常用的做法是打开慢查询日志抓那些执行时间长、扫描行数多的 SQLSET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;阈值设成 1 秒通常足够抓出问题语句。拿到慢 SQL 后逐条 EXPLAIN看看 type、rows、Extra把高频且 rows 巨大的查询提取出来反推需要补哪些索引。这个闭环比凭感觉建索引靠谱得多。MySQL 还提供现成的 sys 表可以直接查未使用索引例如SELECT * FROM sys.schema_unused_indexes;它会把已经建立但迟迟没被用到的索引列出来是清理冗余索引的第一手依据。不过要说明的是这张表统计的是服务器启动以来的使用情况如果刚建索引不久数据还来不及更新清之前最好结合业务判断。6.2 冗余索引清理索引不是越多越好冗余索引有两种典型形态。一是完全重复的两条索引比如先建了 idx_a (a)又建了 idx_ab (a, b)MySQL 不会自动帮你去重两条都真实存在。二是重复前缀的索引idx_ab 其实已经覆盖了 idx_a 的功能单独保留 idx_a 纯粹浪费写入空间。删除冗余索引前建议用 Percona Toolkit 里的 pt-duplicate-key-checker 扫一遍pt-duplicate-key-checker --hostlocalhost --userroot --ask-pass它会给出建议删除的重复索引清单。删除时用 DROP INDEXALTER TABLE orders DROP INDEX idx_a;索引少了插入和更新时的维护成本立刻下降。我见过一张宽表上挂了十几个索引写 QPS 被拖垮的案例删掉三个冗余索引后写入性能回升了差不多 20%这个收益是实打实的。6.3 大表在线加索引的实操注意事项给千万级、亿级的大表加索引不能像小表那样直接一条 ALTER 命令跑完就收工必须在操作前想清楚三个问题锁不锁写、主从延迟、超时中断。MySQL 5.6 之后 InnoDB 支持在线 DDL添加普通二级索引时可以使用 INPLACE 算法和 NONE 锁级别意思是执行过程中不阻塞业务读写ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time), ALGORITHMINPLACE, LOCKNONE;MySQL 8.0 中默认就是这种在线模式可以省掉后面两个关键字。但“不阻塞”不等于“没成本”加索引需要全表扫描数据构建索引树会占用大量 IO 和 CPU如果有主从复制备库执行同样 DDL 时可能产生延迟。所以即使语法支持在线也应该放在业务低峰期执行并监控主从延迟。更稳妥的方案是用 pt-online-schema-change 工具它通过创建临时表、复制数据、触发器增量同步的方式完成 DDL对在线业务更友好适合超大表的核心操作。工具本身不复杂真正要重视的是演练不要在生产环境第一次用这种工具就直接干大表先在测试库把流程跑熟回滚思路也准备好。最后分享一个小习惯算是这么多年踩坑攒下来的。每次上线新 SQL 之前我都会把所有涉及到的查询拉出来 EXPLAIN 一遍确认 type 至少到 rangekey 命中了预期索引Extra 里没有 Using filesort 和 Using temporary。遇到“明明建了索引却不走索引”的情况我不会急着 FORCE INDEX而是先排查是不是函数包裹了索引列是不是字段类型隐式转换是不是复合索引最左前缀没满足。大多数时候问题不在 MySQL而在我们对数据和查询模式的理解还不够。