企业官网建设流程全解析

接手线上MySQL慢查询优化这类活儿，看着是加几个索引的事，实际上是一整套“读表逻辑”的博弈。索引优化策略不只是“在WHERE条件字段上建索引”这么简单，它背后涉及索引结构、查询执行计划、数据分布、写入成本之间的复杂权衡。这篇就把我在实际项目中总结的索引优化完整思路盘一遍，从准备分析到设计落地再到常见踩坑，全是我实测验证过的东西。

1. 动手优化之前，先把这几件事查清楚

1.1 慢查询日志和当前索引状态

拿到一个慢查询优化的需求，第一步不是看SQL，而是先把“现状”捞出来。我通常先看三样东西：慢查询日志、表结构和现有索引、执行计划。

慢查询日志能告诉我们哪些SQL是真正有问题的、执行频率多高、扫描了多少行。开启方式很简单，但不建议直接在线上改全局参数，可以在会话级别临时开：

-- 查看当前慢查询日志状态 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 如果没开启，会话级别临时开启（生产环境谨慎，建议先确认） SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;

第二步是看表结构和现有索引。

SHOW CREATE TABLE users\G SHOW INDEX FROM users;

不要小看这两条，它能直接告诉你哪些索引已经存在、哪些其实已经冗余或失效。有的表里明明有联合索引，但查询写出来连最左前缀都没满足，等于索引完全没走。

第三步是看执行计划，也就是EXPLAIN。这一步是整个优化的入口。

1.2 EXPLAIN这张“体检报告”怎么看

EXPLAIN输出里的关键字段就这么几个：type、key、rows、Extra。我一般先看type，它直接暗示了访问级别：ALL是灾难，index是折中，range是基本合格，ref和eq_ref是比较理想，const是极致。

rows是预估扫描行数，这个数字不是真实行数，但能反映优化器的大致判断。Extra里的几个关键词要特别注意，比如Using filesort（文件排序）、Using temporary（临时表）、Using index（覆盖索引）、Using where（回表后过滤）。

打个比方，MySQL执行一条查询就相当于在一家没有货架的仓库里找一件商品。没有索引时只能挨个箱子翻（全表扫描），有索引就好比按货架分区存放，能直接按分区找，而覆盖索引就是“贴着货架标签直接看到库存数量”，连开箱都不用。

1.3 优化前先确认数据分布和基数

这一点经常被忽略，但它恰恰会影响索引设计的成败。对一个字段建索引前，我会先看它的区分度：

SELECT COUNT(*) AS total, COUNT(DISTINCT col) AS distinct_col FROM table;

如果区分度太低，比如性别字段只有两个值，那么这个字段上的单列索引基本没有意义。MySQL优化器一算发现走索引还不如全表扫，直接就放弃索引。

另外要确认字段的数据类型、字符集、排序规则。我有一次优化接手的库，两张关联表的字段字符集不一致，一个utf8mb4一个latin1，导致关联查询索引失效，这种问题从SHOW CREATE TABLE就能看出来。

2. 索引设计，重点不是“加索引”而是“改查询结构”

2.1 联合索引不是简单拼凑，要按查询模式设计

联合索引设计的核心是最左前缀原则。既然联合索引的B+树先按第一列排序、再按第二列排序，那么索引列的顺序就必须贴合实际查询使用模式。

设计联合索引时，我通常按一个顺序考虑列：

• 等值条件列放前面
• 范围查询列放后面
• 排序列优先考虑放进索引，避免文件排序
• 覆盖查询用到的列，再放到后面

举个例子，一个订单表有user_id、status、create_time，常见查询是：

SELECT * FROM orders WHERE user_id = 123 AND status = 1 ORDER BY create_time DESC;

如果建三个单列索引(user_id)、(status)、(create_time)，MySQL只能选其中一个用得最好的，其他索引就是浪费。更好的方案是建联合索引(user_id, status, create_time)，理由如下：

• user_id和status都是等值条件，先定位到一个很小的集合
• create_time按索引顺序天然有序，直接省掉filesort，尤其对ORDER BY create_time DESC LIMIT 10这类分页查询效果极其明显
• 如果SELECT只取需要的列，甚至可以顺势做成覆盖索引

这个索引对查询的优化，实测下来扫描行数可以从几十万降到几十行，排序耗时直接清零。

2.2 覆盖索引：最容易被低估的优化手段

覆盖索引，简单说就是查询需要读取的所有字段都能从索引树本身获取，绕过回表。回表可不像看起来那么轻巧，每回一次表就是一次随机I/O，数据量大时性能差距会成倍显现。

举个常见例子，统计某用户的订单数量：

SELECT COUNT(*) FROM orders WHERE user_id = 123;

如果只有(user_id)普通索引，InnoDB的辅助索引叶子节点虽然存储了主键值，但COUNT(*)其实不需要回表，所以这里天然有优势。再看另一种：

SELECT id, user_id, status FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 50;

如果只有联合索引(user_id, status, create_time)，这里会回表拿其他字段（比如没在索引里的列）。改成(status, create_time, id, user_id)这种覆盖查询组合，就能完全避免回表。这里列顺序还要根据最左前缀来定，如果status单独做等值条件，它必须在最左边。

覆盖索引不适合所有场景，特别是SELECT *，几乎不可能把全部列都塞进索引。但在统计类、列表分页类、汇总类查询里，收益非常可观。我遇到很多慢查询其实不是索引没建，而是建了索引后SELECT的列太宽导致回表开销过大。

2.3 前缀索引和函数索引：各有用武之地

长文本字段的索引是个麻烦事。比如一个表里存URL或长描述，直接对整个字段加索引，B+树的每个节点能容纳的键值数量就变少，树变高，检索效率反而下降。

解决办法之一就是前缀索引。

ALTER TABLE articles ADD INDEX idx_url_prefix (url(64));

选取合适的N，可以让区分度接近完整列，同时大幅缩小索引体积。选取方法很简单：

SELECT COUNT(DISTINCT LEFT(url, 4)) / COUNT(*) AS ratio4, COUNT(DISTINCT LEFT(url, 8)) / COUNT(*) AS ratio8, COUNT(DISTINCT LEFT(url, 12)) / COUNT(*) AS ratio12 FROM articles;

前缀索引的代价是无法用于覆盖索引和ORDER BY，因为索引里存的不是完整值。我一般是在区分度达到90%以上才选，不然意义不大。

MySQL 8.0还引入了函数索引，语法上使用(表达式)作为索引列。这对条件里必须加函数的场景非常有用，比如统计日期的：

SELECT COUNT(*) FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2025-06-01';

这种写法被很多老手称为“索引杀手”，因为只要列套上函数，普通索引就失效了。解决思路其实有两个：一个是改写成范围查询：

SELECT COUNT(*) FROM orders WHERE create_time >= '2025-06-01 00:00:00' AND create_time < '2025-06-02 00:00:00';

另一个就是在MySQL 8.0里建函数索引：

ALTER TABLE orders ADD INDEX idx_date_create ((DATE_FORMAT(create_time, '%Y-%m-%d')));

函数索引本质是把表达式的结果存储到索引里，代价是每次写数据都要计算一次，读多写少的场景用起来很划算。

2.4 关于冗余索引和“索引越多越好”的误解

很多开发者的潜意识里是“查询慢就加索引”，结果表上挂了十几个索引，反而更新变慢、磁盘占用变大。InnoDB里每个索引都是额外的B+树，写入时要同步维护，插入一条记录可能要同时更新四五棵树。

冗余索引的典型例子是已经有了(a, b)联合索引，再建一个单独的(a)索引。因为联合索引的最左前缀已经能覆盖只查a的场景，单独的(a)索引就是完全多余的。

但反过来，(b)索引可不能省，因为最左前缀决定了单独查b用不上(a, b)索引。

检查冗余索引我一般直接用pt-duplicate-key-checker扫描一遍，或者在运维窗口里自己查一下information_schema里的索引统计。人工检查的逻辑也很清晰：如果一个索引是另一个索引的最左前缀，通常就是冗余。

3. 性能调优实操：一个真实订单查询的优化过程

3.1 建表与原始慢查询

这里我拿一个简化过的订单表来演示，表结构如下：

CREATE TABLE `orders` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` bigint NOT NULL, `order_no` varchar(64) NOT NULL, `status` tinyint NOT NULL DEFAULT '0', `amount` decimal(12,2) NOT NULL, `create_time` datetime NOT NULL, `update_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表里有大约300万行数据。线上反馈说一个统计页面打开极慢，对应SQL是这样的：

SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total_amount FROM orders WHERE create_time >= '2025-01-01' AND create_time < '2025-04-01' AND status = 1 GROUP BY user_id ORDER BY cnt DESC LIMIT 100;

这是一条典型的“时间段内统计用户订单数和金额”的报表查询。原始状态下，没有任何索引能同时服务create_time的范围条件、status的等值条件、GROUP BY user_id的分组。执行计划大概率是全表扫描或只在一个单列索引上做范围扫描，然后临时表分组加文件排序。

3.2 第一次优化：联合索引定位数据范围

我先在(create_time, status, user_id)上建立一个联合索引，理由是按时间范围框定数据区间，再按状态过滤，最后用user_id分组。

ALTER TABLE orders ADD INDEX idx_time_status_user (create_time, status, user_id);

执行计划的type从ALL变成了range，rows从300万降到约60万，初步过滤是有效果的。但问题依然存在：status和user_id其实都是等值或分组条件，把时间范围列放第一位，让status和user_id没法利用到索引的有序性，GROUP BY user_id还是走了临时表和文件排序。

3.3 第二次优化：调整列顺序，照顾GROUP BY

联合索引的调整思路是让等值条件优先。把status提到最前，create_time范围列放在后面，user_id继续放末尾以满足分组排序：

ALTER TABLE orders ADD INDEX idx_status_time_user (status, create_time, user_id);

这次type变成了ref，rows降到约2万行，Extra里的Using temporary; Using filesort依然存在，但临时表的数据量小了很多，查询时间从秒级降到了几百毫秒。

这里补充一个关键点：GROUP BY user_id使用索引排序的条件是索引列顺序完全匹配分组顺序，而且分组列必须是索引的最左部分。现在索引顺序是(status, create_time, user_id)，排序时先按status再按create_time再按user_id，显然无法直接为GROUP BY user_id工作。要想彻底消除临时表和文件排序，理想情况是让user_id成为索引中分组相关的第一个键。

3.4 换个思路：用覆盖索引消除回表

既然这条SQL只需要user_id、amount、create_time、status这几个字段，那么干脆做一个完全覆盖查询的索引：

ALTER TABLE orders ADD INDEX idx_status_user_time_amount (status, user_id, create_time, amount);

这里把status放最前是等值过滤，user_id放第二是三方的分组依据，create_time放在后面满足时间范围过滤，amount作为最后一个叶子列则是为了让整个查询不需要回表取amount，直接就能从索引里拿到求和需要的所有数据。

执行计划里出现了Using index，扫描行数进一步减少，没有Using temporary了，Using filesort可能还在，因为最终排序是针对分组后的聚合结果。但从查询时间看，原本2秒多的一条统计查询，调到稳定在150毫秒以内，这就是覆盖索引加联合索引组合拳的效果。

3.5 用实际参数说话

优化前后对比，我最关注的指标是这几个：

从这张表能直观感受到索引策略的每一步是如何啃掉一块性能瓶颈的。

3.6 善用索引下推和排序优化

MySQL 5.6引入的索引条件下推（ICP）是个容易被忽视的优化点。它允许存储引擎在读取索引的过程中就过滤掉部分不满足条件的行，而不是全查出来再回表过滤。上面最终方案里，如果条件里再夹杂一些非索引字段判断，ICP也能帮上忙，前提是数据库版本至少5.6，并且optimizer_switch里的index_condition_pushdown是开启状态。

排序优化是另一个常被忽略的维度。如果在ORDER BY上有高成本的排序需求，我通常分两种情况处理：

• 排序列能放进索引且顺序与查询一致，直接走索引排序，没有filesort
• 排序列无法进入索引，先缩小数据集合再排序，必要时对结果集做缓存或落临时表

这里有个血泪教训：不要在ORDER BY的字段上使用表达式，比如ORDER BY DATE_FORMAT(create_time, '%Y-%m-%d')，一旦带上函数或表达式，即使这个字段有索引，也无法利用索引排序。所以排序条件尽量写成裸列名。

4. 索引失效的常见场景和排查方法

4.1 隐式类型转换

这类问题出现得非常多。比如order_no字段类型是varchar(64)，但查询条件却用了整数：

SELECT * FROM orders WHERE order_no = 1234567890;

MySQL对字符串和数字比较做过特殊处理，实际上会在这个列上触发隐式类型转换，把order_no的每一行都转成数字再比较，索引自然就废了。处理方式很简单：条件写成字符串形式，比如order_no = '1234567890'。

另一个隐藏很深的点在字符集。两张表关联时，如果a.user_id是utf8mb4，b.user_id是utf8，关联也会因隐式转换导致无法走索引。建表时统一字符集是个基础要求，我之前接手过历史项目，两个库的字符集居然都不一样，每次关联查询都在慢查询日志里躺了一长串。

4.2 对索引列做计算或函数操作

这一点在上面说过。只要索引列被函数或表达式包裹，优化器就基本放弃了索引。常见的还有：

WHERE create_time + INTERVAL 1 DAY > NOW() WHERE amount * 1.1 > 100 WHERE YEAR(create_time) = 2025

这类我都建议改写为范围比较条件，让索引列保持“裸列”状态。比如YEAR(create_time) = 2025可改写成：

WHERE create_time >= '2025-01-01 00:00:00' AND create_time < '2026-01-01 00:00:00'

这是在应用查询中最简单的优化手段，改动量小、收益直接。

4.3 LIKE以通配符开头

LIKE '%abc'这类查询注定无法使用索引，因为B+树只能按前缀定位。需要搜索中间片段时，方案无非是：

• 全文索引（FULLTEXT）
• 外接搜索引擎（比如Elasticsearch）
• 存储冗余字段专门做反向前缀匹配
• 如果数据量不大，接受全表扫描并在应用层做缓存

LIKE 'abc%'则可以走索引，因为它是前缀匹配。text或varchar大字段的LIKE优化尤其恼人，尽量别把它作为常规查询条件。

4.4 OR连接条件和非等值场景

OR条件是我排查慢查询时最头疼的写法之一。比如：

SELECT * FROM orders WHERE user_id = 123 OR status = 1;

user_id有索引，status也有索引，但MySQL在特定条件下可能不使用索引合并，而是把OR条件转化为类似全表扫描的处理。即使使用了index_merge，性能往往也不如查两次再合并。实际工作中我更建议把这类SQL拆成两条：

SELECT * FROM orders WHERE user_id = 123 UNION ALL SELECT * FROM orders WHERE status = 1 AND user_id <> 123;

当然这个改动要看业务逻辑是否允许这样拆分，语义等价性需要自己确认。

另外，NOT IN、NOT EXISTS、!=这类否定型条件也经常让优化器放弃索引，因为数据库认为“排除少数行”不如“扫描大部分行”划算。具体取决于数据分布，也可能受null值影响。遇到!=导致的全表扫描，如果取的数据确实少，可以考虑改写成范围查询，比如col < ? OR col > ?，但有没有收益需要实际验证。

4.5 数据分布变化导致优化器“不长眼”

有时候索引明明建好了，执行计划却走了全表扫描。这可能是统计信息没有更新导致的。MySQL用统计信息基准确认数据的分布情况，而InnoDB的统计信息是采样估算的。遇到数据量剧烈变化、大批量导入后，可以执行：

ANALYZE TABLE orders;

这会重新收集表的统计信息。我遇到过一次线上系统批量导入了上百万数据，第二天慢查询突然变多，执行计划从ref变成了ALL，执行完ANALYZE TABLE后一切恢复。原因就是优化器用旧统计信息估算了错误的行数。

4.6 快速定位索引失效的排查清单

我习惯用表格整理排查步骤，方便复盘和交接：

5. 运维层面的索引管理与线上变更注意事项

5.1 索引变更不要直接在线执行（除非用在线DDL）

索引优化策略里有一个环节常被忽略：怎么把新索引安全地加到线上。早期MySQL版本的ALTER TABLE ADD INDEX会锁表，对线上业务是致命的。MySQL 5.6开始支持在线DDL（ALGORITHM=INPLACE），也就是在变更过程中允许DML同时进行。但要注意：

• 在线DDL仍然会增加主从延迟
• 大表上的索引创建可能需要数小时，期间磁盘I/O压力巨大
• 建议使用gh-ost或pt-online-schema-change这类工具来平滑处理超大表的索引变更

我处理过一张5亿行的流水表加索引，直接用原生ALTER TABLE跑了一个多小时，监控显示主从延迟飙到几千秒。后来改用pt-online-schema-change加上流量控制参数，业务基本无感。

5.2 定期清理无效和重复索引

索引不是越多越安全。除了上面提到的冗余索引，还有一些长期不被使用的“僵尸索引”。判断依据很简单，查询performance_schema.table_io_waits_summary_by_index_usage可以看到索引的使用统计。如果某索引的COUNT_STAR增长极慢、几乎为零，说明优化器从没选过它，可以考虑在业务低峰期删除。

但删除索引前一定要确认历史慢查询和定时任务里没有隐式的依赖路径。我曾经清理了一个看起来“半年没被用过的索引”，结果月底报表凌晨任务全部超时，因为报表SQL只有在特定日期才能命中那个索引，平时测试环境根本不会走到那条路径。教训就是：删索引比加索引更需要走审批流程和灰度验证。

5.3 分区表与索引的选择

分区表不是独立的索引优化手段，但常被拿来和索引一起考虑。比如按月份的订单表做了RANGE分区，每个分区有独立的索引树，查询时如果查询条件包含分区键，可以显著减少扫描数据量。

但分区表也有陷阱：如果查询条件不包含分区键，优化器依然要做全分区分区扫描，索引效果大打折扣。而且分区表在新增分区DDL、全局唯一索引等场景上都有额外限制。我对大多数项目的建议是：先优化索引，数据量到了一定规模再考虑分区，不要一上来就为“可能变大”而分区。

5.4 监控和回归：索引优化的闭环

优化完索引不是结束，还要做回归。常规手段是：

• 对比优化前后的慢查询日志，确认目标SQL消失或耗时大幅下降
• 观察一两个完整业务周期（比如一天或一周），防止夜间批处理任务偶发慢查询
• 关注线上实例的QPS、磁盘I/O、CPU使用量变化，确认没有引入负面效应
• 把优化的执行计划备份到文档里，作为后续复查基线

我自己习惯写一份简单的“SQL优化登记表”，记录优化日期、SQL指纹、原执行计划关键指标、新执行计划关键指标、影响业务方、回滚方案。这个习惯帮我在后续数据库版本升级、统计信息大范围更新时，能快速定位哪些SQL可能会“回归变慢”。

6. 关于索引优化策略，我的一些实际感悟

做了这么多年的MySQL性能优化，我最大的体会是：索引优化不是一个孤立的数据库操作，而是对业务查询模式的深度理解。你越了解业务到底在查什么、查多频繁、返回多少行，索引就越能建到点子上。完全不看业务场景，拿着数据库理论往里套，失败概率非常高。

还有一点心得是：优化索引要舍得“做减法”。一个表上塞满索引，看着是每个查询都有保障，实际上每一个写入请求、每一次数据更新都要付出额外的维护成本。很多看似复杂的性能问题，反而是因为索引太冗余导致优化器决策困难，选择范围过大反而容易“选错路”。

如果让我给出一条可复制的执行路径，我会推荐这么做：先收集慢查询和表状态，理清查询模式和业务优先级，再针对最高频、最耗时的SQL做联合索引和覆盖索引设计，然后小范围上线、观察执行计划和慢查询日志，最后定期清理无效索引和统计信息。这条路径看起来很朴素，但跑下来的效果往往比各种“高深优化技巧”实在得多。

最后分享一个小细节：每次改完索引，我都会把EXPLAIN结果存档，文件名按日期和业务模块来命名。别小看这个习惯，等到三个月后业务方追一句“这个接口怎么变慢了”，你能立刻把执行计划调出来对比，省下的排查时间不是一星半点。