之前线上有个订单列表接口用户一直反馈页面要转好几秒才出数据。我拉了一下慢查询日志定位到一条按 user_id 和时间范围查 orders 表的 SQL在 340 万行的表里跑了 2.6 秒。第一反应不是去改 SQL 写法而是先看这张表到底有没有走索引——结果发现 WHERE 里的两个关键条件完全没有可用的二级索引。补了一个复合索引之后这条 SQL 直接从 2.6 秒降到了 12 毫秒。类似的场景我遇到过很多次。给 mysql 表添加索引这件事是所有 SQL 优化里投入产出比最高的动作之一但也是最容易“想当然”的操作。很多开发者拿到慢查询就加索引加完发现 EXPLAIN 里还是 typeALL或者索引确实走了但性能也没什么变化。这篇内容我按自己的实际排查经验来写从最基础的索引设计思路、类型选型、添加索引的完整操作讲到索引失效、复合索引进阶和线上常见问题希望能帮你少走几次弯路。1. 加索引前先搞清楚你的表到底需要什么索引直接动手执行 ALTER TABLE ADD INDEX 之前我建议先花十分钟想清楚一件事这条 SQL 是怎么查数据的瓶颈到底在哪。索引不是加得越多越好加错了不仅是磁盘空间浪费还会拖慢写入。1.1 一条 SQL 是怎么在表里找到数据的先说个生活化类比。一本几百页的书你要找“MySQL 索引优化”这几个字如果这本书没有目录你只能从第 1 页翻到最后一页一行一行找这就是全表扫描。有了目录之后你可以先定位到“索引”相关章节再精确翻到那一页这就是索引查找。InnoDB 存储引擎里每张表的数据都按主键顺序组织成一棵 B 树这棵树叫聚簇索引。你根据主键查数据直接走这棵树就能找到完整行记录。但如果你用其他字段查比如WHERE user_id 123MySQL 需要先在一棵专门为 user_id 建的二级索引树里找到对应的主键值再拿着主键回聚簇索引查整行数据这个过程叫回表。这也是为什么我给 orders 表加索引能带来质的提升原来的查询没有可用索引MySQL 只能把 340 万行记录全部读出来再用 user_id 和时间条件逐行过滤。加了复合索引之后MySQL 直接通过索引树定位到满足 user_id 条件的少量主键再回表查数据IO 量差了三个数量级。理解这个底层机制你才能解释很多现象为什么覆盖索引能避免回表从而更快为什么索引字段上有函数运算会导致索引失效为什么 SELECT * 有时候不如只查索引字段快。这些在后面都会展开讲。1.2 判断要不要加索引可以看这三个信号我不会一看到慢 SQL 就加索引通常会先看三个信号。第一个是慢查询日志。MySQL 里通过slow_query_log和long_query_time两个参数控制long_query_time设置为 1 或 2 秒比较合理。线上如果某条 SQL 频繁出现在慢日志里说明它有性能问题值得进一步分析。我自己习惯把慢日志开到表里方便按执行次数排序快速找到“高频慢 SQL”。第二个是 EXPLAIN 的执行计划。很多刚接触优化的同学不太看这个但其实EXPLAIN SELECT ...是不真正执行查询的只是让优化器输出执行计划成本极低。重点关注type列如果是ALL说明全表扫描大概率有加索引的空间如果是ref或range说明已经用上了索引但可能还有优化余地如果是index要小心这可能是扫描了整个索引树并不代表效率高。第三个是表的数据量和写入频率。只有几千行的表全表扫描可能比走索引还快因为 InnoDB 读数据以页为单位小表一两次 IO 就搞定了走索引反而要多读索引页。但表到了几十万、几百万行之后全表扫描的代价就上来了。另外如果你的表是典型的高并发写入表比如订单流水、日志表每加一个索引都会让 INSERT、UPDATE 多维护一棵 B 树这时候就需要权衡查询收益和写入成本。提示判断加不加索引最忌讳的是“看一条慢 SQL 就无脑加”。先把相同的查询条件、数据分布、表大小搞清楚再动手。2. 索引类型选型单列、复合、唯一、全文和前缀执行ALTER TABLE ADD INDEX之前你还得选对索引类型。MySQL 里索引不是只有一种形态不同的业务需求对应的索引结构差别很大。我见过不少表把所有查询字段都单独建了单列索引结果一条多条件查询还是慢这就是典型的“索引类型没选对”。2.1 六种常用索引类型快查下面这张表我按实际使用频率整理每一条都写了对应的 SQL 示例。索引类型创建语句示例典型适用场景注意事项主键索引ALTER TABLE t ADD PRIMARY KEY (id)每张 InnoDB 表都应有主键一个表只能有一个主键建议用自增或雪花 ID唯一索引ALTER TABLE t ADD UNIQUE KEY uk_mobile (mobile)手机号、身份证号等需要唯一性约束的字段唯一索引可以允许一个 NULL但多个 NULL 也重复普通单列索引CREATE INDEX idx_user_id ON t (user_id)单字段高频过滤条件过滤度很低的字段不建议建索引比如性别复合索引CREATE INDEX idx_user_time ON t (user_id, create_time)多字段组合查询、排序、分组注意字段顺序受最左前缀法则约束前缀索引CREATE INDEX idx_title_prefix ON t (title(20))长字符串字段如标题、URL只对前 N 个字符建索引可能损失精度全文索引CREATE FULLTEXT INDEX ft_content ON t (content)文章内容、商品描述等大文本搜索中文分词依赖插件配置MySQL 8.0 也非强项很多人会忽略唯一索引的隐藏价值它不只是约束还能给优化器提供更精确的估算。如果某个字段的业务逻辑上就必须唯一直接建唯一索引而不是普通索引既省一个索引又保证数据质量。2.2 主键索引不是唯一索引我经常被问到一个问题“我用了一个唯一约束字段是不是就不用建主键索引了”这是两个完全不同的概念。主键索引是 InnoDB 的数据组织方式每张表都必须有它决定了数据行在磁盘上的物理排列。唯一索引只是一个二级索引它约束字段值不能重复但数据行的存储仍然依赖主键。如果你建表时没指定主键InnoDB 会选一个非空的唯一索引作为聚簇索引如果连唯一索引都没有它会生成一个隐藏的 6 字节 rowid 作为聚簇索引。这种情况下你的二级索引回表时查的是隐藏 rowid既不可控也可能因为索引页利用率低产生额外开销。所以我的习惯是所有表必须有主键而且尽量选自增整数或雪花 ID 这类单调递增的值避免页分裂。从 EXPLAIN 里辨认主键索引也很简单key列显示PRIMARYtype通常是const或eq_ref比如WHERE id 123走主键查询时优化器知道最多返回一行所以成本估算最精准。2.3 复合索引是大部分慢查询的答案单列索引能力有限。举个例子WHERE user_id 123 AND create_time 2024-01-01如果你只在 user_id 上建了单列索引MySQL 能用它定位到 user_id123 的所有记录然后再在内存里过滤 create_time 条件。如果这个用户有几万条订单回表和过滤的代价依然不小。更合理的做法是建复合索引(user_id, create_time)。这样索引树里先按 user_id 排序再在 user_id 相同的情况下按 create_time 排序查询时通过索引就能同时完成过滤甚至排序。这就是为什么我会把第 5 节单独拿出来讲复合索引因为它才是生产环境里解决慢查询的主力。3. 实操给 MySQL 表添加索引的完整流程理论说得再多不如直接上手操作一遍。这一节我带你完整走一遍“查当前索引状态 → 添加索引 → 验证执行计划”的流程并解释每一条命令背后的含义。3.1 加索引的两种写法ALTER TABLE 还是 CREATE INDEXMySQL 里添加索引的 SQL 有两条常用路径ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);CREATE INDEX idx_user_time ON orders (user_id, create_time);两条语句本质上做的事情是一样的但 ALTER TABLE 的扩展性更强它可以在一条语句里调整多种表结构比如同时加索引、修改字段、改表注释。CREATE INDEX 则只负责建索引语义更聚焦。我个人的习惯是单加索引用 CREATE INDEX涉及表结构调整时用 ALTER TABLE 统一处理。还有一点值得重点说明在 MySQL 5.6 及以后版本ALTER TABLE 默认支持在线 DDL。之前大家普遍担心“加索引会锁表导致业务停摆”在 5.6 之前确实会但在 5.7、8.0 里添加二级索引默认使用ALGORITHMINPLACE, LOCKNONE意思是只锁定很短的时间做元数据变更索引数据是后台逐步构建的。不过这不代表你可以无视线上负载极大数据量下构建索引仍然会占用大量 IO后文我会单独写这个问题。3.2 EXPLAIN 验证索引是否真正生效先看一个具体例子。假设 orders 表结构如下CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, status TINYINT NOT NULL DEFAULT 0, KEY idx_user_time (user_id, create_time) ) ENGINEInnoDB;然后我们执行EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND create_time 2024-06-01;输出结果里你会看到几个关键列type这里是range说明通过索引做了范围扫描比ALL好很多。keyidx_user_time说明优化器实际选用了这个索引。key_len这个值很有用它表示索引里用了多少个字节。user_id 是 INT占 4 字节create_time 是 DATETIME在 MySQL 5.6 占 5 字节含 1 字节小数秒标识所以这里 key_len 可能显示 9 左右说明两个索引列都被使用上了。rows优化器预估扫描的行数这个值越小越好。Extra如果出现Using index condition说明触发了索引下推如果NULL就表示回表查了完整行。如果你发现type还是ALL或者key是 NULL就得回头检查 SQL 里是不是写了让索引失效的表达式比如在索引列上用了函数或者隐式类型转换。这类问题下一节详细说。3.3 用 SHOW INDEX 查看索引状态并清理冗余加完索引后我习惯再执行一次SHOW INDEX FROM orders看看索引列表里有没有之前的历史残留。这个命令会输出表的全部索引信息其中有几个字段需要关注Cardinality表示索引去重后的估计值它与表行数的比值越接近越好如果某个索引的 Cardinality 很小说明这个索引的区分度很低比如性别字段那它大概率没有价值。但这个值是统计信息采样得来的不是精确值建议定期ANALYZE TABLE orders;更新统计信息否则优化器可能因为陈旧统计选错索引。另外很多表上线久了之后会有大量冗余索引比如 user_id 上有单列索引又建了(user_id, create_time)复合索引那么前者的价值就几乎为零。你可以用pt-duplicate-key-checker这类工具扫描重复索引人工确认后删除。删除索引要谨慎先在测试环境验证避免因为删掉一个“看似没用”的索引导致另一个查询变慢。4. 为什么索引失效比忘记加索引更隐蔽的坑如果只是“没加索引”问题反而好解决加上就行。真正让人头疼的是明明建了索引EXPLAIN 里却看不到索引生效或者时灵时不灵。这类问题通常和 SQL 写法、字符集、优化器行为有关。4.1 七种典型失效场景我把自己踩过和帮同事排查过的场景整理如下每一条都配了问题示例和修复方向。在索引列上使用函数比如WHERE DATE(create_time) 2024-06-01MySQL 要先把每行的 create_time 都算成 DATE 才能比较索引就失效了。正确做法是写create_time 2024-06-01 AND create_time 2024-06-02让查询条件和索引列直接可比。前导模糊查询WHERE title LIKE %MySQL%开头就是通配符B 树的有序性无法利用。只有WHERE title LIKE MySQL%才能走索引这是范围扫描。隐式类型转换字段是 VARCHAR查询条件却写WHERE order_no 10086MySQL 会把字符串转成数字比较导致索引失效。经验是保持查询条件和字段类型一致。OR 连接非索引条件WHERE user_id 1001 OR status 1如果 status 没有索引MySQL 可能直接放弃整个索引扫描。改成两个查询用 UNION ALL 合并或给 status 也建合适索引。联合索引没有满足最左前缀索引是(a, b, c)查询条件是WHERE b 1 AND c 2跳过了最左列 a索引无法被有效利用。在第 5 节会详细讲。大量负向查询WHERE status ! 1、WHERE user_id NOT IN (...)这些“排除式”条件很难利用索引有序性。如果业务必须这么做建议改成等值匹配或设计更合理的状态字段。优化器判断走索引反而更慢当表只有几万行且查询要返回表中大部分数据时优化器会认为全表扫描更划算这时候key列就是 NULL这是正常的不代表索引是坏的。4.2 学会看 EXPLAIN 的 Extra 列排查索引失效时最容易忽略的是 Extra 列。它记录了执行计划里的附加信息很多问题在这里比看 key 列更直观。Using where表示存储引擎返回记录后Server 层又做了条件过滤。如果这个过滤可以下推到索引条件里通常说明索引设计还没最优。Using index这是最理想的情况说明查询所需的字段都在索引树里不需要回表也就是覆盖索引。Using index condition表示用了索引下推ICP意思是部分 WHERE 条件在索引遍历过程中就被过滤掉了减少了回表次数。在 MySQL 5.6 之后的版本中比较常见是好事。Using filesort说明 ORDER BY 没走索引顺序需要额外排序。排序数据量大时会生成临时文件这是性能隐患。Using temporary说明查询用了临时表常见于 GROUP BY 和去重操作也说明索引设计可能不合理。我排查问题的顺序通常是先看 type 是不是 ALL再看 key 是否为空最后看 Extra 有没有Using filesort或Using temporary。这三关都过了SQL 一般就没什么大问题。5. 复合索引进阶最左前缀、ORDER BY 和索引下推生产环境里我已经很少建单列索引了基本上都是复合索引。但复合索引有个天生的特性叫“最左前缀”很多人在这里翻车。同时它对 ORDER BY 的优化也是很多慢查询的解法值得单独讲透。5.1 复合索引的字段顺序与最左前缀法则假设我们有一个复合索引idx_a_b_c (a, b, c)。这个索引的真实存储逻辑是先按 a 排序a 相同时按 b 排序b 相同时再按 c 排序。所以 MySQL 能利用它的查询条件并不是任意的组合而是要满足“从左往右匹配”的规则。查询条件是否能利用索引说明WHERE a 1能使用索引的 a 列WHERE a 1 AND b 2能使用索引的 a、b 列WHERE a 1 AND b 2 AND c 3能使用完整索引WHERE b 2 AND c 3不能跳过了 a无法使用索引WHERE a 1 AND c 3部分a 能走索引c 无法利用因为中间断了 bWHERE a 1 AND b 2部分a 用于范围扫描b 无法用于过滤这里有个细节很多人会忽略范围查询会让后续字段失去排序能力。比如WHERE a 1 AND b 2a 是范围条件b 的排序是在 a 范围内的顺序不一定全局有序所以优化器不会用 b 去精确定位。实际开发中我建议把等值条件放在前面范围条件放在后面这样索引利用率最高。另一个经验是先分清高频查询字段的过滤度。过滤度是指某个字段去重后的值数量与总行数的比值比如性别字段只有 2 个值过滤度极低。复合索引的字段顺序应该把过滤度高的字段放前面而不是想当然地按表结构顺序排。5.2 用复合索引直接解决 ORDER BY 的 filesort慢查询日志里有一类很典型的 SQLWHERE user_id 1001 ORDER BY create_time DESC。如果你只用(user_id)单列索引MySQL 能通过索引快速找到 user_id1001 的所有记录但 ORDER BY create_time 还需要再排序一次数据量大时就会出现Using filesort。如果把索引改成(user_id, create_time)情况就完全不同了。因为索引树本身就是按 user_id、create_time 两级排序的user_id1001 对应的记录天然就是按 create_time 有序排列的MySQL 直接按索引顺序读取即可不需要额外排序。这个优化思路还可以扩展到 GROUP BY。比如统计每个用户每天的订单量SELECT user_id, DATE(create_time) AS day, COUNT(*) FROM orders GROUP BY user_id, DATE(create_time);如果索引是(user_id, create_time)但 GROUP BY 里对 create_time 做了 DATE() 函数索引会失效。更合理的方案是让时间字段的粒度直接满足业务比如单独存一个day字段然后建(user_id, day)索引让 GROUP BY 的字段和索引从左到右对齐。5.3 覆盖索引与索引下推让查询少回一次表覆盖索引这个概念很多教材里都有但实际用得好的却不多。所谓覆盖索引就是查询的所有字段都包含在某个二级索引里这样 MySQL 不需要回表查聚簇索引直接遍历二级索引就能返回结果。比如SELECT user_id, create_time FROM orders WHERE user_id 1001 AND create_time 2024-06-01;上述查询所需的 user_id、create_time 两个字段都在idx_user_time里EXPLAIN 的 Extra 会显示Using index说明完全不需要回表。相反如果 SELECT 后面还带了 order_no、status 等不在索引里的字段就需要回表查完整行。索引下推则是 MySQL 5.6 引入的优化手段。以前如果一个复合索引是(user_id, create_time)像WHERE user_id 1001 AND create_time 2024-06-01存储引擎遍历索引时会拿着 user_id 条件定位等回表后再过滤 create_time。有了 ICP 之后create_time 的过滤被下推到索引遍历阶段回表的次数大幅减少。EXPLAIN 里出现Using index condition就是在告诉你 ICP 生效了。这也解释了为什么我对推荐索引时的建议通常很明确查询字段尽量控制在索引字段范围内查询条件尽量用等值开头范围条件靠后。这样覆盖索引和索引下推能同时发挥最大作用。6. 常见问题与排查技巧实录这一节整理几个 Fréquent 被问到的问题都是线上实战里真实碰到的。如果你按照前面的流程操作之后还是有问题可以对照这里来查。6.1 大表加索引怕卡业务怎么办给百万级、千万级的大表加索引最担心的就是执行期间业务卡死。前面提过MySQL 5.6 之后二级索引的在线 DDL 已经做了很多优化默认情况下不会长时间锁表但实际执行时仍可能因为后台构建索引而拖慢 IO尤其是机械硬盘环境。如果你运维的是 MySQL 5.5 或更早版本或者想要更可控的加索引窗口我会用pt-online-schema-change这类工具它通过创建一个新表结构、在旧表上建触发器同步增量数据、再把新表重命名的方式完成加索引业务影响很小。不过这个方案需要额外部署工具过程也比较复杂除非业务对可用性非常敏感默认还是推荐直接用在线 DDL。个人建议的操作策略是先在从库上执行加索引并观察一段时间确认没有主从延迟放大器问题之后再在业务低峰期操作主库。加索引本身是一件低风险操作但“低风险”不等于“零风险”保留一个可以回滚的窗口很重要。6.2 索引建了还是慢问题还可能出在哪很多同学遇到过这种怪事索引加上了EXPLAIN 也显示走了索引但 SQL 还是很慢。这时通常要往三个方向排查。第一个是回表太多。二级索引帮你快速定位到一批主键但如果这批主键对应的数据行物理分布很分散回表会产生大量随机 IO。表现在慢日志里就是每次查询的耗时波动很大。解决办法是扩大覆盖索引范围把要查询的字段都塞进索引或者优化 SQL 只查询必要字段。第二个是统计信息和实际数据分布严重不一致。索引选择是靠优化器估算的如果表刚经历大量增删改或者很久没跑 ANALYZE TABLECardinality 就会失真。有时你用FORCE INDEX指定某个索引反而比默认选择的索引快得多。第三个是字符集和排序规则不一致导致的隐式转换。比如两个表字段都是 VARCHAR但一个表的排序规则是utf8mb4_general_ci另一个是utf8mb4_unicode_ciJOIN 时 MySQL 可能会做隐式转换让索引失效。排查时看SHOW CREATE TABLE的CHARSET和COLLATE是否一致。6.3 加索引后写入变慢索引数量怎么权衡索引能加速查询但每次 INSERT、UPDATE、DELETE 都要同步维护索引树。如果一张表有五个索引写入时就要更新五棵 B 树。对于日志表、流水表这类高频写入场景这个代价很容易被放大。取舍逻辑我一般这样定单表索引数量尽量控制在 5 到 6 个以内少建低区分度字段上的索引大字段如 TEXT、超长 VARCHAR尽量用前缀索引或单独拆分表如果写入是核心诉求甚至可以考虑在业务上把查询分流到只读从库主库只承担写入和事务查询。另外一点MySQL 对索引键长度有限制默认 767 字节8.0 可选扩大到 3072 字节。索引多个长字段前要估算长度比如 VARCHAR(255) utf8mb4 字段本身占 1020 字节直接建索引就会超出限制这时必须用前缀索引或者把字段长度改短。6.4 关于全文索引和前缘扩展的补充很多从 Oracle 或 PostgreSQL 转过来的开发者会顺手问一句“这两个库都能建很多索引MySQL 行不行”。MySQL 对单表的索引数量没有硬性上限但列总长度、索引键大小是有限制的复合索引最多可包含 16 个列。所以只要你遵循“按查询设计、控制冗余、控制长度”这几个原则就不太会碰到数据库层面的限制更多时候是加索引前没想清楚业务查询路径。全文索引在 MySQL 里也能用但说实话中文全文检索并不是 MySQL 的强项分词效果和召回率都不如 Elasticsearch 这类专业搜索引擎。如果只是简单的中英文模糊查询全文索引可以救急如果业务量再大一点尽早把搜索功能拆出去性能和管理都比堆在 MySQL 里更可控。在实际操作中我最深的体会是加索引不难真正难的是克制。上线新需求时每一条主要查询路径都值得你多花十分钟看看它的 WHERE、ORDER BY、GROUP BY 到底长什么样再决定索引字段和顺序。等系统流量上来之后你会发现一个设计合理的复合索引比事后拍脑袋补的五个单列索引有用得多。另外索引不是一劳永逸的表结构变更、数据分布变化都会影响之前的索引效果每隔一段时间重新抓一遍慢查询对照执行计划做一次索引体检是我现在做数据库维护时的固定动作。最后分享一个小经验如果你不确定某个索引该不该留就把它单独摘出来跑一遍相关业务的压测脚本让数据帮你做决定比凭感觉分配合理得多。 MySQL索引优化实战：从慢查询定位到复合索引设计
之前线上有个订单列表接口用户一直反馈页面要转好几秒才出数据。我拉了一下慢查询日志定位到一条按 user_id 和时间范围查 orders 表的 SQL在 340 万行的表里跑了 2.6 秒。第一反应不是去改 SQL 写法而是先看这张表到底有没有走索引——结果发现 WHERE 里的两个关键条件完全没有可用的二级索引。补了一个复合索引之后这条 SQL 直接从 2.6 秒降到了 12 毫秒。类似的场景我遇到过很多次。给 mysql 表添加索引这件事是所有 SQL 优化里投入产出比最高的动作之一但也是最容易“想当然”的操作。很多开发者拿到慢查询就加索引加完发现 EXPLAIN 里还是 typeALL或者索引确实走了但性能也没什么变化。这篇内容我按自己的实际排查经验来写从最基础的索引设计思路、类型选型、添加索引的完整操作讲到索引失效、复合索引进阶和线上常见问题希望能帮你少走几次弯路。1. 加索引前先搞清楚你的表到底需要什么索引直接动手执行 ALTER TABLE ADD INDEX 之前我建议先花十分钟想清楚一件事这条 SQL 是怎么查数据的瓶颈到底在哪。索引不是加得越多越好加错了不仅是磁盘空间浪费还会拖慢写入。1.1 一条 SQL 是怎么在表里找到数据的先说个生活化类比。一本几百页的书你要找“MySQL 索引优化”这几个字如果这本书没有目录你只能从第 1 页翻到最后一页一行一行找这就是全表扫描。有了目录之后你可以先定位到“索引”相关章节再精确翻到那一页这就是索引查找。InnoDB 存储引擎里每张表的数据都按主键顺序组织成一棵 B 树这棵树叫聚簇索引。你根据主键查数据直接走这棵树就能找到完整行记录。但如果你用其他字段查比如WHERE user_id 123MySQL 需要先在一棵专门为 user_id 建的二级索引树里找到对应的主键值再拿着主键回聚簇索引查整行数据这个过程叫回表。这也是为什么我给 orders 表加索引能带来质的提升原来的查询没有可用索引MySQL 只能把 340 万行记录全部读出来再用 user_id 和时间条件逐行过滤。加了复合索引之后MySQL 直接通过索引树定位到满足 user_id 条件的少量主键再回表查数据IO 量差了三个数量级。理解这个底层机制你才能解释很多现象为什么覆盖索引能避免回表从而更快为什么索引字段上有函数运算会导致索引失效为什么 SELECT * 有时候不如只查索引字段快。这些在后面都会展开讲。1.2 判断要不要加索引可以看这三个信号我不会一看到慢 SQL 就加索引通常会先看三个信号。第一个是慢查询日志。MySQL 里通过slow_query_log和long_query_time两个参数控制long_query_time设置为 1 或 2 秒比较合理。线上如果某条 SQL 频繁出现在慢日志里说明它有性能问题值得进一步分析。我自己习惯把慢日志开到表里方便按执行次数排序快速找到“高频慢 SQL”。第二个是 EXPLAIN 的执行计划。很多刚接触优化的同学不太看这个但其实EXPLAIN SELECT ...是不真正执行查询的只是让优化器输出执行计划成本极低。重点关注type列如果是ALL说明全表扫描大概率有加索引的空间如果是ref或range说明已经用上了索引但可能还有优化余地如果是index要小心这可能是扫描了整个索引树并不代表效率高。第三个是表的数据量和写入频率。只有几千行的表全表扫描可能比走索引还快因为 InnoDB 读数据以页为单位小表一两次 IO 就搞定了走索引反而要多读索引页。但表到了几十万、几百万行之后全表扫描的代价就上来了。另外如果你的表是典型的高并发写入表比如订单流水、日志表每加一个索引都会让 INSERT、UPDATE 多维护一棵 B 树这时候就需要权衡查询收益和写入成本。提示判断加不加索引最忌讳的是“看一条慢 SQL 就无脑加”。先把相同的查询条件、数据分布、表大小搞清楚再动手。2. 索引类型选型单列、复合、唯一、全文和前缀执行ALTER TABLE ADD INDEX之前你还得选对索引类型。MySQL 里索引不是只有一种形态不同的业务需求对应的索引结构差别很大。我见过不少表把所有查询字段都单独建了单列索引结果一条多条件查询还是慢这就是典型的“索引类型没选对”。2.1 六种常用索引类型快查下面这张表我按实际使用频率整理每一条都写了对应的 SQL 示例。索引类型创建语句示例典型适用场景注意事项主键索引ALTER TABLE t ADD PRIMARY KEY (id)每张 InnoDB 表都应有主键一个表只能有一个主键建议用自增或雪花 ID唯一索引ALTER TABLE t ADD UNIQUE KEY uk_mobile (mobile)手机号、身份证号等需要唯一性约束的字段唯一索引可以允许一个 NULL但多个 NULL 也重复普通单列索引CREATE INDEX idx_user_id ON t (user_id)单字段高频过滤条件过滤度很低的字段不建议建索引比如性别复合索引CREATE INDEX idx_user_time ON t (user_id, create_time)多字段组合查询、排序、分组注意字段顺序受最左前缀法则约束前缀索引CREATE INDEX idx_title_prefix ON t (title(20))长字符串字段如标题、URL只对前 N 个字符建索引可能损失精度全文索引CREATE FULLTEXT INDEX ft_content ON t (content)文章内容、商品描述等大文本搜索中文分词依赖插件配置MySQL 8.0 也非强项很多人会忽略唯一索引的隐藏价值它不只是约束还能给优化器提供更精确的估算。如果某个字段的业务逻辑上就必须唯一直接建唯一索引而不是普通索引既省一个索引又保证数据质量。2.2 主键索引不是唯一索引我经常被问到一个问题“我用了一个唯一约束字段是不是就不用建主键索引了”这是两个完全不同的概念。主键索引是 InnoDB 的数据组织方式每张表都必须有它决定了数据行在磁盘上的物理排列。唯一索引只是一个二级索引它约束字段值不能重复但数据行的存储仍然依赖主键。如果你建表时没指定主键InnoDB 会选一个非空的唯一索引作为聚簇索引如果连唯一索引都没有它会生成一个隐藏的 6 字节 rowid 作为聚簇索引。这种情况下你的二级索引回表时查的是隐藏 rowid既不可控也可能因为索引页利用率低产生额外开销。所以我的习惯是所有表必须有主键而且尽量选自增整数或雪花 ID 这类单调递增的值避免页分裂。从 EXPLAIN 里辨认主键索引也很简单key列显示PRIMARYtype通常是const或eq_ref比如WHERE id 123走主键查询时优化器知道最多返回一行所以成本估算最精准。2.3 复合索引是大部分慢查询的答案单列索引能力有限。举个例子WHERE user_id 123 AND create_time 2024-01-01如果你只在 user_id 上建了单列索引MySQL 能用它定位到 user_id123 的所有记录然后再在内存里过滤 create_time 条件。如果这个用户有几万条订单回表和过滤的代价依然不小。更合理的做法是建复合索引(user_id, create_time)。这样索引树里先按 user_id 排序再在 user_id 相同的情况下按 create_time 排序查询时通过索引就能同时完成过滤甚至排序。这就是为什么我会把第 5 节单独拿出来讲复合索引因为它才是生产环境里解决慢查询的主力。3. 实操给 MySQL 表添加索引的完整流程理论说得再多不如直接上手操作一遍。这一节我带你完整走一遍“查当前索引状态 → 添加索引 → 验证执行计划”的流程并解释每一条命令背后的含义。3.1 加索引的两种写法ALTER TABLE 还是 CREATE INDEXMySQL 里添加索引的 SQL 有两条常用路径ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);CREATE INDEX idx_user_time ON orders (user_id, create_time);两条语句本质上做的事情是一样的但 ALTER TABLE 的扩展性更强它可以在一条语句里调整多种表结构比如同时加索引、修改字段、改表注释。CREATE INDEX 则只负责建索引语义更聚焦。我个人的习惯是单加索引用 CREATE INDEX涉及表结构调整时用 ALTER TABLE 统一处理。还有一点值得重点说明在 MySQL 5.6 及以后版本ALTER TABLE 默认支持在线 DDL。之前大家普遍担心“加索引会锁表导致业务停摆”在 5.6 之前确实会但在 5.7、8.0 里添加二级索引默认使用ALGORITHMINPLACE, LOCKNONE意思是只锁定很短的时间做元数据变更索引数据是后台逐步构建的。不过这不代表你可以无视线上负载极大数据量下构建索引仍然会占用大量 IO后文我会单独写这个问题。3.2 EXPLAIN 验证索引是否真正生效先看一个具体例子。假设 orders 表结构如下CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, status TINYINT NOT NULL DEFAULT 0, KEY idx_user_time (user_id, create_time) ) ENGINEInnoDB;然后我们执行EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND create_time 2024-06-01;输出结果里你会看到几个关键列type这里是range说明通过索引做了范围扫描比ALL好很多。keyidx_user_time说明优化器实际选用了这个索引。key_len这个值很有用它表示索引里用了多少个字节。user_id 是 INT占 4 字节create_time 是 DATETIME在 MySQL 5.6 占 5 字节含 1 字节小数秒标识所以这里 key_len 可能显示 9 左右说明两个索引列都被使用上了。rows优化器预估扫描的行数这个值越小越好。Extra如果出现Using index condition说明触发了索引下推如果NULL就表示回表查了完整行。如果你发现type还是ALL或者key是 NULL就得回头检查 SQL 里是不是写了让索引失效的表达式比如在索引列上用了函数或者隐式类型转换。这类问题下一节详细说。3.3 用 SHOW INDEX 查看索引状态并清理冗余加完索引后我习惯再执行一次SHOW INDEX FROM orders看看索引列表里有没有之前的历史残留。这个命令会输出表的全部索引信息其中有几个字段需要关注Cardinality表示索引去重后的估计值它与表行数的比值越接近越好如果某个索引的 Cardinality 很小说明这个索引的区分度很低比如性别字段那它大概率没有价值。但这个值是统计信息采样得来的不是精确值建议定期ANALYZE TABLE orders;更新统计信息否则优化器可能因为陈旧统计选错索引。另外很多表上线久了之后会有大量冗余索引比如 user_id 上有单列索引又建了(user_id, create_time)复合索引那么前者的价值就几乎为零。你可以用pt-duplicate-key-checker这类工具扫描重复索引人工确认后删除。删除索引要谨慎先在测试环境验证避免因为删掉一个“看似没用”的索引导致另一个查询变慢。4. 为什么索引失效比忘记加索引更隐蔽的坑如果只是“没加索引”问题反而好解决加上就行。真正让人头疼的是明明建了索引EXPLAIN 里却看不到索引生效或者时灵时不灵。这类问题通常和 SQL 写法、字符集、优化器行为有关。4.1 七种典型失效场景我把自己踩过和帮同事排查过的场景整理如下每一条都配了问题示例和修复方向。在索引列上使用函数比如WHERE DATE(create_time) 2024-06-01MySQL 要先把每行的 create_time 都算成 DATE 才能比较索引就失效了。正确做法是写create_time 2024-06-01 AND create_time 2024-06-02让查询条件和索引列直接可比。前导模糊查询WHERE title LIKE %MySQL%开头就是通配符B 树的有序性无法利用。只有WHERE title LIKE MySQL%才能走索引这是范围扫描。隐式类型转换字段是 VARCHAR查询条件却写WHERE order_no 10086MySQL 会把字符串转成数字比较导致索引失效。经验是保持查询条件和字段类型一致。OR 连接非索引条件WHERE user_id 1001 OR status 1如果 status 没有索引MySQL 可能直接放弃整个索引扫描。改成两个查询用 UNION ALL 合并或给 status 也建合适索引。联合索引没有满足最左前缀索引是(a, b, c)查询条件是WHERE b 1 AND c 2跳过了最左列 a索引无法被有效利用。在第 5 节会详细讲。大量负向查询WHERE status ! 1、WHERE user_id NOT IN (...)这些“排除式”条件很难利用索引有序性。如果业务必须这么做建议改成等值匹配或设计更合理的状态字段。优化器判断走索引反而更慢当表只有几万行且查询要返回表中大部分数据时优化器会认为全表扫描更划算这时候key列就是 NULL这是正常的不代表索引是坏的。4.2 学会看 EXPLAIN 的 Extra 列排查索引失效时最容易忽略的是 Extra 列。它记录了执行计划里的附加信息很多问题在这里比看 key 列更直观。Using where表示存储引擎返回记录后Server 层又做了条件过滤。如果这个过滤可以下推到索引条件里通常说明索引设计还没最优。Using index这是最理想的情况说明查询所需的字段都在索引树里不需要回表也就是覆盖索引。Using index condition表示用了索引下推ICP意思是部分 WHERE 条件在索引遍历过程中就被过滤掉了减少了回表次数。在 MySQL 5.6 之后的版本中比较常见是好事。Using filesort说明 ORDER BY 没走索引顺序需要额外排序。排序数据量大时会生成临时文件这是性能隐患。Using temporary说明查询用了临时表常见于 GROUP BY 和去重操作也说明索引设计可能不合理。我排查问题的顺序通常是先看 type 是不是 ALL再看 key 是否为空最后看 Extra 有没有Using filesort或Using temporary。这三关都过了SQL 一般就没什么大问题。5. 复合索引进阶最左前缀、ORDER BY 和索引下推生产环境里我已经很少建单列索引了基本上都是复合索引。但复合索引有个天生的特性叫“最左前缀”很多人在这里翻车。同时它对 ORDER BY 的优化也是很多慢查询的解法值得单独讲透。5.1 复合索引的字段顺序与最左前缀法则假设我们有一个复合索引idx_a_b_c (a, b, c)。这个索引的真实存储逻辑是先按 a 排序a 相同时按 b 排序b 相同时再按 c 排序。所以 MySQL 能利用它的查询条件并不是任意的组合而是要满足“从左往右匹配”的规则。查询条件是否能利用索引说明WHERE a 1能使用索引的 a 列WHERE a 1 AND b 2能使用索引的 a、b 列WHERE a 1 AND b 2 AND c 3能使用完整索引WHERE b 2 AND c 3不能跳过了 a无法使用索引WHERE a 1 AND c 3部分a 能走索引c 无法利用因为中间断了 bWHERE a 1 AND b 2部分a 用于范围扫描b 无法用于过滤这里有个细节很多人会忽略范围查询会让后续字段失去排序能力。比如WHERE a 1 AND b 2a 是范围条件b 的排序是在 a 范围内的顺序不一定全局有序所以优化器不会用 b 去精确定位。实际开发中我建议把等值条件放在前面范围条件放在后面这样索引利用率最高。另一个经验是先分清高频查询字段的过滤度。过滤度是指某个字段去重后的值数量与总行数的比值比如性别字段只有 2 个值过滤度极低。复合索引的字段顺序应该把过滤度高的字段放前面而不是想当然地按表结构顺序排。5.2 用复合索引直接解决 ORDER BY 的 filesort慢查询日志里有一类很典型的 SQLWHERE user_id 1001 ORDER BY create_time DESC。如果你只用(user_id)单列索引MySQL 能通过索引快速找到 user_id1001 的所有记录但 ORDER BY create_time 还需要再排序一次数据量大时就会出现Using filesort。如果把索引改成(user_id, create_time)情况就完全不同了。因为索引树本身就是按 user_id、create_time 两级排序的user_id1001 对应的记录天然就是按 create_time 有序排列的MySQL 直接按索引顺序读取即可不需要额外排序。这个优化思路还可以扩展到 GROUP BY。比如统计每个用户每天的订单量SELECT user_id, DATE(create_time) AS day, COUNT(*) FROM orders GROUP BY user_id, DATE(create_time);如果索引是(user_id, create_time)但 GROUP BY 里对 create_time 做了 DATE() 函数索引会失效。更合理的方案是让时间字段的粒度直接满足业务比如单独存一个day字段然后建(user_id, day)索引让 GROUP BY 的字段和索引从左到右对齐。5.3 覆盖索引与索引下推让查询少回一次表覆盖索引这个概念很多教材里都有但实际用得好的却不多。所谓覆盖索引就是查询的所有字段都包含在某个二级索引里这样 MySQL 不需要回表查聚簇索引直接遍历二级索引就能返回结果。比如SELECT user_id, create_time FROM orders WHERE user_id 1001 AND create_time 2024-06-01;上述查询所需的 user_id、create_time 两个字段都在idx_user_time里EXPLAIN 的 Extra 会显示Using index说明完全不需要回表。相反如果 SELECT 后面还带了 order_no、status 等不在索引里的字段就需要回表查完整行。索引下推则是 MySQL 5.6 引入的优化手段。以前如果一个复合索引是(user_id, create_time)像WHERE user_id 1001 AND create_time 2024-06-01存储引擎遍历索引时会拿着 user_id 条件定位等回表后再过滤 create_time。有了 ICP 之后create_time 的过滤被下推到索引遍历阶段回表的次数大幅减少。EXPLAIN 里出现Using index condition就是在告诉你 ICP 生效了。这也解释了为什么我对推荐索引时的建议通常很明确查询字段尽量控制在索引字段范围内查询条件尽量用等值开头范围条件靠后。这样覆盖索引和索引下推能同时发挥最大作用。6. 常见问题与排查技巧实录这一节整理几个 Fréquent 被问到的问题都是线上实战里真实碰到的。如果你按照前面的流程操作之后还是有问题可以对照这里来查。6.1 大表加索引怕卡业务怎么办给百万级、千万级的大表加索引最担心的就是执行期间业务卡死。前面提过MySQL 5.6 之后二级索引的在线 DDL 已经做了很多优化默认情况下不会长时间锁表但实际执行时仍可能因为后台构建索引而拖慢 IO尤其是机械硬盘环境。如果你运维的是 MySQL 5.5 或更早版本或者想要更可控的加索引窗口我会用pt-online-schema-change这类工具它通过创建一个新表结构、在旧表上建触发器同步增量数据、再把新表重命名的方式完成加索引业务影响很小。不过这个方案需要额外部署工具过程也比较复杂除非业务对可用性非常敏感默认还是推荐直接用在线 DDL。个人建议的操作策略是先在从库上执行加索引并观察一段时间确认没有主从延迟放大器问题之后再在业务低峰期操作主库。加索引本身是一件低风险操作但“低风险”不等于“零风险”保留一个可以回滚的窗口很重要。6.2 索引建了还是慢问题还可能出在哪很多同学遇到过这种怪事索引加上了EXPLAIN 也显示走了索引但 SQL 还是很慢。这时通常要往三个方向排查。第一个是回表太多。二级索引帮你快速定位到一批主键但如果这批主键对应的数据行物理分布很分散回表会产生大量随机 IO。表现在慢日志里就是每次查询的耗时波动很大。解决办法是扩大覆盖索引范围把要查询的字段都塞进索引或者优化 SQL 只查询必要字段。第二个是统计信息和实际数据分布严重不一致。索引选择是靠优化器估算的如果表刚经历大量增删改或者很久没跑 ANALYZE TABLECardinality 就会失真。有时你用FORCE INDEX指定某个索引反而比默认选择的索引快得多。第三个是字符集和排序规则不一致导致的隐式转换。比如两个表字段都是 VARCHAR但一个表的排序规则是utf8mb4_general_ci另一个是utf8mb4_unicode_ciJOIN 时 MySQL 可能会做隐式转换让索引失效。排查时看SHOW CREATE TABLE的CHARSET和COLLATE是否一致。6.3 加索引后写入变慢索引数量怎么权衡索引能加速查询但每次 INSERT、UPDATE、DELETE 都要同步维护索引树。如果一张表有五个索引写入时就要更新五棵 B 树。对于日志表、流水表这类高频写入场景这个代价很容易被放大。取舍逻辑我一般这样定单表索引数量尽量控制在 5 到 6 个以内少建低区分度字段上的索引大字段如 TEXT、超长 VARCHAR尽量用前缀索引或单独拆分表如果写入是核心诉求甚至可以考虑在业务上把查询分流到只读从库主库只承担写入和事务查询。另外一点MySQL 对索引键长度有限制默认 767 字节8.0 可选扩大到 3072 字节。索引多个长字段前要估算长度比如 VARCHAR(255) utf8mb4 字段本身占 1020 字节直接建索引就会超出限制这时必须用前缀索引或者把字段长度改短。6.4 关于全文索引和前缘扩展的补充很多从 Oracle 或 PostgreSQL 转过来的开发者会顺手问一句“这两个库都能建很多索引MySQL 行不行”。MySQL 对单表的索引数量没有硬性上限但列总长度、索引键大小是有限制的复合索引最多可包含 16 个列。所以只要你遵循“按查询设计、控制冗余、控制长度”这几个原则就不太会碰到数据库层面的限制更多时候是加索引前没想清楚业务查询路径。全文索引在 MySQL 里也能用但说实话中文全文检索并不是 MySQL 的强项分词效果和召回率都不如 Elasticsearch 这类专业搜索引擎。如果只是简单的中英文模糊查询全文索引可以救急如果业务量再大一点尽早把搜索功能拆出去性能和管理都比堆在 MySQL 里更可控。在实际操作中我最深的体会是加索引不难真正难的是克制。上线新需求时每一条主要查询路径都值得你多花十分钟看看它的 WHERE、ORDER BY、GROUP BY 到底长什么样再决定索引字段和顺序。等系统流量上来之后你会发现一个设计合理的复合索引比事后拍脑袋补的五个单列索引有用得多。另外索引不是一劳永逸的表结构变更、数据分布变化都会影响之前的索引效果每隔一段时间重新抓一遍慢查询对照执行计划做一次索引体检是我现在做数据库维护时的固定动作。最后分享一个小经验如果你不确定某个索引该不该留就把它单独摘出来跑一遍相关业务的压测脚本让数据帮你做决定比凭感觉分配合理得多。