企业官网建设流程全解析

上个月有个同事找我，说线上有个接口每到晚高峰就超时，代码看了一圈没发现问题，最后定位下来是一条SQL把整个业务拖垮了。这也是我想把SQL优化、索引策略、查询性能提升这些内容系统整理一遍的原因，因为大多数性能事故，最后都落在一条慢SQL上。

这篇内容我会把慢SQL定位、执行计划分析、索引失效排查、索引存储结构与并发锁、以及索引上线验证这些环节整体过一遍。适合后端开发、DBA，也适合正在为线上慢查询头疼的同学。文章里会给出可以直接复用的操作步骤和踩坑经验，有些结论可能和你平时听到的不太一样，但都是我实测过的。

1. 慢SQL定位：先找到真正该优化的查询

很多人一上来就想怎么建索引，这其实是本末倒置。慢SQL优化第一步永远是定位，是找到那条真正拖垮系统的SQL，而不是凭感觉改。定位不准确，后面所有优化都白做。

1.1 慢查询日志怎么开，开启后怎么降低采集开销

MySQL里最直接的定位手段就是慢查询日志。线上环境一般默认是关的，因为写日志本身有开销，但生产环境不开慢日志，出了问题就像没有监控一样盲目。

开启方式：

-- 临时开启，重启后失效 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 2; SET GLOBAL log_queries_not_using_indexes = ON; -- 持久化配置，写入my.cnf [mysqld] slow_query_log = 1 slow_query_log_file = /data/mysql/log/slow-query.log long_query_time = 2 log_queries_not_using_indexes = 0

几个容易踩的坑：

第一，long_query_time不要设置成0。看到很多初学同学为了让慢日志“全量记录”，直接设0，结果一个高并发库瞬间产生几十GB日志，磁盘直接打满。一般业务从2秒开始调，等优化一轮后再逐步收紧到1秒甚至0.5秒。

第二，log_queries_not_using_indexes这个参数要慎开。它会把所有没走索引的查询都记下来，包括那些扫描行数很少、本身就不需要索引的小表查询。在MySQL 5.7之前的版本里，这个参数开在大库上会带来明显的性能抖动，建议定向排查时临时开，排查完就关。

第三，慢日志文件要配合日志切割工具或定时任务做轮转，不然文件越滚越大，后面分析时一条日志动辄几百MB，读取都很费劲。

定位到慢SQL之后，通常会用pt-query-digest对慢日志做聚合分析，按总耗时、平均耗时、扫描行数排序，找出TOP N。注意不要只看单次最慢的SQL，要看“总耗时占比高”的SQL，因为频繁出现的慢语句即使单次只有1秒，累积起来对系统的伤害远大于偶尔一次跑10秒的报表查询。

Oracle环境下思路类似，只是入口不同。AWR报告里的SQL Statistic部分，按Elapsed Time排序找TOP SQL，也可以直接用v$SQL和dba_hist_sqlstat查历史执行统计。SQL Monitor在Oracle 11g之后是个很实用的工具，执行时间超过1秒的语句自动进入监控，会给出执行计划每一步的实际行数和耗时。

1.2 读懂EXPLAIN执行计划的关键列

定位到具体的慢SQL后，第一个动作就是看执行计划。MySQL里就是在SQL前面加EXPLAIN。

EXPLAIN SELECT o.order_id, u.name FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'PENDING' AND o.create_time > '2024-01-01' ORDER BY o.create_time DESC;

执行计划里我一般只看几个关键列：

type是最直观的访问类型，从好到差大致是：system>const>eq_ref>ref>range>index>ALL。如果看到ALL，说明是全表扫描，这是重点关注对象。index也不一定好，它表示遍历了整棵索引树，但没有通过索引精确定位。

key列表示实际用到的索引。key为NULL说明没走索引。rows是优化器预估需要扫描的行数，这个值是估算的，不一定精确，但可以作为量级参考。Extra列信息量很大，看到Using filesort说明排序没有用到索引，看到Using temporary说明用了临时表，这两个都是潜在的性能杀手。

这里有个经验：rows预估和实际扫描行数相差很大时，说明统计信息不准确，或者优化器选择有问题。MySQL 8.0支持ANALYZE TABLE更新统计信息，也可以直接加FORCE INDEX做测试，但要记住这只是临时验证手段，不能作为长期方案。

Oracle的同学对应看执行计划的Operation列和Cardinality列，通过DBMS_XPLAN.DISPLAY查看，核心思路是一样的：看访问路径是否走了索引，估算行数是否合理。

1.3 一条SQL从“看起来正常”到“确认要优化”的判定方法

不是所有慢SQL都需要优化。我见过有人花了一个礼拜优化一条每天只跑一次、耗时10秒的批量任务，结果收益几乎为零，真正该优化的是每秒钟执行几百次、单次耗时200毫秒的查询。

判定标准通常是这样的：

• 高频短查询：QPS很高，单次耗时要压到极低，任何一次全表扫描都是不可接受的，这类是OLTP优化重点。
• 低频长查询：比如凌晨的批处理任务、报表查询，偶尔跑一次，只要不影响其他业务，不一定要死抠性能。
• 中间态查询：高峰期出现、平峰期消失，这种要结合监控确认是不是并发叠加导致的，不要一上来就改SQL。

一个比较实用的判断方法：把目标SQL放进执行计划里，看rows和实际返回行数的比例。如果扫描10万行只返回5行，说明索引选择很差，有明确的优化空间；如果扫描行数和返回行数差不多，那说明数据本身就要读这么多，优化SQL写法意义不大，应该考虑换一种查询方式，比如做聚合、走汇总表，或者分页拆分。

确认了“这条SQL确实需要优化”之后，再进入索引策略分析。

2. 索引失效场景排查：建了索引却不走，多半是这几种写法

最让人头疼的不是没有索引，而是明明建了索引，执行计划里却显示全表扫描。大多数情况下不是优化器抽风，而是SQL写法破坏了索引的可用性。

2.1 隐式类型转换与函数包裹索引列

隐式类型转换是最高发的索引失效原因，而且往往很难一眼看出来。最常见的是手机号用varchar存储，查询时却传了数字。

-- 假设 mobile 列是 varchar(20)，有索引 idx_mobile SELECT * FROM user WHERE mobile = 13800138000;

这条SQL看起来没问题，但MySQL会把mobile列转成数字再比较，相当于对索引列用了隐性函数，索引直接失效。写代码的同学很难发现，因为结果一样，只是慢了很多。修正方法就是写成字符串：

SELECT * FROM user WHERE mobile = '13800138000';

还有一种高频写法是在索引列上包函数：

-- 无法走 create_time 上的索引 SELECT * FROM orders WHERE DATE(create_time) = '2024-12-01'; -- 应改为范围查询 SELECT * FROM orders WHERE create_time >= '2024-12-01 00:00:00' AND create_time < '2024-12-02 00:00:00';

索引中存储的是原始列值，B+树按列原始值排序。对列做了函数或计算之后，索引的有序性无法直接用于匹配，优化器只能把所有数据都算一遍才知道哪些符合条件，所以只能放弃索引。记住一个原则：查询条件里对索引列做任何运算，都是自断经脉。

2.2 最左前缀原则失效的三种典型场景

复合索引遵循最左前缀原则，这个大家都知道，但实际写SQL时还是会经常踩坑。

假设有复合索引idx_user_status_time(user_id, status, create_time)，三种典型失效写法：

第一种，跳过复合索引的第一列。直接查status和create_time，比如WHERE status = 'PAID' AND create_time > '2024-01-01'，优化器无法使用这个复合索引，因为B+树先按user_id排序，跳过第一列之后，后续列的顺序对查询没有任何帮助。

第二种，复合索引中间列作为范围条件。比如WHERE user_id = 123 AND create_time > '2024-01-01' AND status = 'PAID'，虽然也查了user_id，但create_time是范围条件，status这一列就没法继续走索引了。因为索引排序是先user_id、再status、再create_time，中间隔了一个范围筛选的状态，索引无法精确定位status的值。

第三种，查询条件顺序与索引列顺序不一致。其实优化器会自动重排等值条件，所以这个坑现在少了很多，但遇到函数、子查询等复杂写法时，优化器不一定能正确处理。

要特别说明的是：最左前缀规则的“最左”，指的是查询条件中必须包含复合索引最左侧的列，且这类条件必须是等值匹配，才能让后续列继续生效。建复合索引时，把等值查询的列放在前面，范围查询的列放在后面，这是最基础的顺序策略。

2.3 OR、IN、LIKE和范围查询对索引选择的影响

OR条件是最容易被低估的索引杀手。WHERE name = '张三' OR status = 1，如果name和status上各自有单列索引，优化器可能走Index Merge合并两个索引，这倒还好；但如果两个条件里只有一个有索引，优化器需要扫描全表才能满足OR逻辑，索引直接失效。

我的处理习惯是能拆就拆，用UNION ALL代替OR，特别是两个条件分别能走索引时：

-- 各自走索引 SELECT * FROM user WHERE name = '张三' UNION ALL SELECT * FROM user WHERE status = 1;

LIKE查询也有明确边界：LIKE 'abc%'可以走索引，LIKE '%abc'和LIKE '%abc%'无法走索引，因为字符串的排序规则决定了前缀匹配才能利用B+树有序性。如果业务确实需要中间模糊匹配，就该考虑全文索引或专门的搜索引擎，而不是死磕普通索引。

IN和范围查询相对特殊。当IN列表很短时一般能走索引，但列表很长时，优化器可能会认为回表代价太高而选择全表扫描。范围查询也一样，当扫描范围超过全表的一定比例后，优化器会倾向于全表扫描。这个比例不是固定值，InnoDB会结合数据分布、缓冲区大小做成本估算。遇到这种情况，可以尝试分段查询，把一个大的范围拆成多个小区间，让优化器在每个区间内愿意走索引。

3. 索引存储结构与锁语义：主键索引、二级索引与并发更新

很多性能问题，尤其是并发场景下的锁等待和死锁，光会看执行计划是解决不了的。必须理解索引在InnoDB里到底怎么存、和锁有什么关系。

3.1 主键索引和唯一索引的本质区别

主键索引和唯一索引，很多人以为只是约束强度不同，其实它们的存储角色完全不同。

InnoDB中，主键索引就是聚簇索引。聚簇索引的叶子节点存放的是整行数据，也就是说表数据本身就是按照主键构建的一棵B+树。通过主键查询时，直接从这棵树上定位到叶子节点，就能拿到整行数据。

唯一索引在InnoDB里属于二级索引。唯一索引的叶子节点存放的是索引列的值和主键值，它只保存索引字段和指向主键的“指针”。通过唯一索引查询时，先走唯一索引的B+树，找到对应的主键值，再回表到聚簇索引去取整行数据。

几个关键区别：

• 主键索引不允许NULL，唯一索引允许有多个NULL值。虽然MySQL默认的唯一索引在多个NULL时不会冲突，但业务上要小心这种语义差异。
• 一个表只能有一个主键聚簇索引，但可以有多个唯一索引。
• 主键是物理存储的锚点，二级索引的叶子节点里都存着主键值，所以主键字段的大小会直接影响所有二级索引的体积。
• 逻辑上，主键用来唯一标识一行记录，唯一索引用来保证列值唯一和加速查询。

如果建表时没有定义主键，InnoDB会找一个非空的唯一索引作为聚簇索引；如果也没有，就会生成一个隐藏的rowid作为聚簇索引。这是很多开发同学容易忽略的隐性陷阱：表结构里没有主键，唯一索引却被当成了聚簇索引使用。

3.2 回表、覆盖索引与索引下推，如何减少回表次数

回表是理解二级索引性能的关键。之前提到，通过二级索引查数据时，二级索引叶子节点只存了索引列值+主键值，想拿其他列数据就得拿主键去聚簇索引再查一次，这就是回表。

回表次数和扫描行数直接相关。扫描1000行二级索引就要回表1000次，如果每行都是随机主键，性能直接崩塌。减少回表有两条路：一是让二级索引覆盖查询所需的全部列，二是减少扫描行数。

覆盖索引是最理想的场景。比如有索引idx_status(status, create_time)，执行：

SELECT status, create_time FROM orders WHERE status = 'PAID';

查询列全部在索引里，不需要回表，Extra列会显示Using index。

如果查询列包含order_id，但order_id是主键，而二级索引叶子节点本来就存储主键值，所以加主键列也不会破坏覆盖性：

SELECT order_id, status, create_time FROM orders WHERE status = 'PAID';

这里依然不需要回表，因为二级索引里已经有主键值了。很多人没意识到这一点，白白把主键加到查询列里，其实没有回表代价。

索引下推（Index Condition Pushdown，ICP）是另一个容易被忽视的优化。MySQL 5.6之后，二级索引扫描过程中可以直接用索引中包含的其他字段做过滤，减少回表次数。比如复合索引(name, age)，执行WHERE name LIKE '张%' AND age = 20，ICP会把age = 20的过滤下推到二级索引扫描阶段，只有可能匹配的行才回表。

判断方法：执行计划Extra列出现Using index condition，就说明走了ICP。

3.3 二级索引更新时的锁顺序与死锁交叉窗口

这部分是并发SQL优化里特别容易被忽视的点，也是我踩过比较深的坑。

InnoDB在通过二级索引执行UPDATE时，加锁顺序并不是一步到位的。比如：

UPDATE orders SET amount = 100 WHERE order_no = 'A123';

假设order_no上有二级索引，执行过程大致是：先通过二级索引定位到order_no = 'A123'对应的索引项，对它加X锁；然后回表，到聚簇索引中定位到对应主键行，再对主键行加X锁。

这里就出现了一个时间窗口：在“锁住二级索引项”和“回表锁主键行”之间，事务并不是一次性把两把锁都拿齐的。如果两个并发事务的操作存在交叉，就可能形成锁等待甚至死锁。

举个具体例子：

• 事务T1：UPDATE orders SET amount = 100 WHERE order_no = 'A123';
• 事务T2：UPDATE orders SET amount = 200 WHERE id = 789;

如果A123对应的主键正好是789，那么T1需要先锁A123这个二级索引项，再回表锁主键789；T2直接通过主键更新，先锁主键789，同时如果T2还更新了order_no这一列，它又需要锁二级索引项A123。

这个过程中，T1已经锁了二级索引项在等主键锁，T2已经锁了主键在等二级索引锁，两边互相等对方手里的锁，死锁就产生了。

降低这种锁交叉概率的方法，我总结了几个实际可用的手段：

第一，OLTP核心路径尽量通过主键更新。UPDATE ... WHERE id = ?不需要先走二级索引再回表，直接锁主键行，锁顺序更简单。

第二，如果必须用二级索引更新，尽量保持所有并发事务通过同一个二级索引列更新，让锁顺序一致。锁顺序一致是避免死锁最有效的办法。

第三，控制事务大小。锁等待往往不是因为单把锁持有太久，而是事务迟迟不提交，把锁攥在手里不放。及时提交、减少事务里不必要的查询操作，能显著降低锁交叉概率。

第四，监控死锁日志。MySQL里跑SHOW ENGINE INNODB STATUS，重点关注LATEST DETECTED DEADLOCK部分，里面会打出两个事务各自的加锁流程，这是定位死锁根源的一手资料。

4. 复合索引与唯一索引的取舍：索引策略的正面刚

索引不是越多越好，索引设计和业务查询模式强相关。这一节聊聊怎么设计复合索引、怎么取舍唯一索引和普通索引、以及怎么清理冗余索引。

4.1 复合索引字段顺序怎么定，不能只看选择性

网上很多教程说要“把选择性最高的列放在最前面”，这个说法听起来很有道理，但在复合索引里并不完全正确。

复合索引的顺序要优先考虑查询条件的等值/范围类型，而不是单纯看区分度。等值条件的列放在前面，范围条件的列放在后面，这样最左前缀规则才能发挥最大效果。

举个例子，订单表经常有这类查询：

SELECT * FROM orders WHERE user_id = 123 AND status = 'PAID' AND create_time > '2024-01-01';

user_id和status都是等值条件，create_time是范围条件。合理的复合索引应该是(user_id, status, create_time)，而不是(create_time, user_id, status)。因为后一种写法在create_time范围过滤后，user_id和status就没法继续走索引精确定位了。

还有一个常见误区是“每个查询建一个独立索引”。比如某个查询同时过滤a和b，就在a和b上分别建单列索引，以为这样能走两个索引。优化器不一定能高效合并两个单列索引，即使能走Index Merge，性能也远不如一个复合索引。复合索引(a, b)是一棵树，一次扫描就能完成定位，而两个单列索引合并需要分别扫描两棵树再做交集，成本更高。

设计复合索引时要同时考虑多个高频查询的公共列。如果三条高频查询分别是(a, b)、(a, c)、(a, b, d)，那么一个(a, b, d, c)的复合索引可能同时覆盖三条查询，当然具体顺序还要结合范围查询情况调整。

4.2 唯一索引和普通索引的选择，以及Oracle与MySQL的差异

业务上需要保证唯一性的列，比如用户手机号、订单号，必须建唯一索引。这个没有任何犹豫空间，不能用普通索引代替。但在不要求唯一性的场景里，唯一索引和普通索引的取舍是有讲究的。

MySQL InnoDB中，普通索引的插入和更新可以使用Change Buffer做优化。意思是说，更新操作可以先缓存在内存里，不用立刻同步刷到磁盘的索引页，后续再合并。唯一索引因为需要立即检查唯一性约束，必须每次更新都读取对应的索引页确认没有冲突，无法使用Change Buffer。

所以对于写多读少、不要求唯一性的场景，普通索引的性能通常优于唯一索引。但如果业务需要强一致性的唯一约束，就必须用唯一索引，这点性能差异不能作为破坏正确性的理由。

Oracle和MySQL在索引结构上的差异也要注意。MySQL InnoDb的主键索引是聚簇索引，表数据和索引是一体的；Oracle默认是堆表，主键索引本质上是个普通B+树索引，表中数据的物理位置和主键顺序无关。因此，针对MySQL的主键查询有天然聚簇优势，但Oracle里主键查询和普通索引查询一样需要索引访问+表访问。

Oracle还支持一些MySQL没有的索引类型，比如位图索引适合低基数列的OLAP场景，反向键索引（Reverse Key Index）用来分散热点块的I/O压力。有些同学把“反向键索引”听成“双向索引”，其实是两个完全不同的概念。MySQL InnoDB的索引叶子节点页之间确实是通过双向链表连接的，这是为了支持范围扫描的正反两个方向遍历，但这是B+树底层的物理设计，并不是一种叫做“双向索引”的索引类型。

另外一个容易被问到的点就是索引表空间。Oracle允许把索引放到独立的表空间，和表数据分开，方便管理I/O和备份策略。MySQL InnoDB默认情况下索引和表数据在同一个.ibd文件里，没有这种独立的表空间概念，这是数据库架构层面的差异，不是配置没做对。

4.3 冗余索引的识别与清理方法

线上数据库最容易出现的问题不是缺索引，而是索引太多，尤其是冗余索引。

冗余索引最典型的形态：已经有复合索引(a, b)，又单独建了(a)。复合索引(a, b)本身就覆盖了a列上的查询，单列索引(a)就是完全冗余的。后者不仅浪费磁盘空间，每次写操作还要多维护一棵B+树。

识别冗余索引可以从两个入口查：

MySQL 5.7及以后版本，直接查sys.schema_unused_indexes，可以找出一段时间内完全没有被使用的索引：

SELECT * FROM sys.schema_unused_indexes;

更细一点的可以从performance_schema.table_io_waits_summary_by_index_usage看每个索引的读写次数：

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA = 'your_db' ORDER BY COUNT_STAR DESC;

如果某个索引COUNT_STAR长期接近于0，基本可以认为它是冗余索引，可以考虑删除。

清理冗余索引时，我的建议是不要直接DROP INDEX。先把索引改名，比如改成idx_name_deprecated，放到线上跑两三天，观察有没有告警、有没有慢SQL因为缺少这个索引而变慢。如果没有异常，再真正删除。这个流程虽然慢，但比出事故之后回滚安全得多。

5. 索引上线后的性能验证与回滚方案

索引优化不是建完索引就算完事，必须验证效果，而且要有回滚预案。这一节讲怎么验证、怎么压并发、怎么平滑上线。

5.1 用EXPLAIN和实际耗时对比优化前后

验证第一步是对比执行计划。优化前和执行EXPLAIN保存一份，建完索引后再看一次，对比type、key、rows、Extra四列。

优化前：

优化后：

执行计划变好了，不代表线上体验变好了。还要做实际耗时对比。

有一个很关键的点：不要在同一个连接里连续执行多次对比。MySQL有Buffer Pool缓存，第二次执行时数据页可能已经在内存里，耗时会明显低于第一次。正确做法是每条SQL交替执行，比如先用旧的SQL跑5次取中位数，再用新的SQL跑5次取中位数，两边都去掉最大最小值，减少缓存和抖动的影响。

MySQL 5.7下可以用SELECT SQL_NO_CACHE ...来避免查询缓存干扰。MySQL 8.0已经彻底移除了查询缓存，这个参数就不需要了。Oracle里可以用ALTER SESSION SET optimizer_adaptive_plans = OFF等参数控制计划稳定性，但一般不推荐在优化阶段动这类设置，保持默认环境对比更真实。

5.2 并发场景下的抖动与稳定性评估

单条SQL跑得快，不代表并发时不出问题。索引减少了扫描行数，但也可能因为锁范围变化带来新的并发问题，比如之前提过的锁交叉死锁。

压测时可以用sysbench或者JMeter对目标SQL加压，观察几个指标：

• TPS和QPS是否有提升。
• p95和p99延迟是否下降，如果平均耗时降了但p99反而升高了，说明存在某类极端情况的抖动。
• 死锁次数是否增加。通过SHOW ENGINE INNODB STATUS和information_schema.INNODB_TRX观察锁等待情况。

一个常见的现象是：加了新索引之后，查询快了，但写入性能下降了。因为每次INSERT和UPDATE都要额外维护一棵B+树，索引越多写入成本越高。压测时要把写入流量也盘进去，特别是那些写多读少的业务表，新增索引前要评估写入开销的增幅是否能接受。

5.3 索引变更的平滑上线与回滚

线上环境加索引，尤其是千万级以上的大表，不能直接执行ALTER TABLE ADD INDEX，因为早期版本的DDL会锁表，造成业务不可用。

MySQL 5.6及之后虽然支持了在线DDL，可以指定ALGORITHM=INPLACE, LOCK=NONE，但大表执行时仍然会有主从延迟、I/O压力等问题。更稳妥的方式是用pt-online-schema-change，它的原理是创建一张新表，通过触发器同步增量数据，再把表切换过来。整个过程不会长时间锁表，适合核心业务表。

Oracle环境下加索引通常压力小一些，可以用CREATE INDEX ... ONLINE在线建索引，支持DML并发执行。但也要注意在大表上创建索引期间的重做日志和排序段空间消耗，提前规划好表空间。

回滚预案一定要提前写好，不要等出问题了再临时查SQL。我的习惯是每次索引变更都准备一份回滚脚本，内容就是对应的DROP INDEX语句，以及如果出现锁相关问题的监控命令。上线后先在低峰期观察一段时间，确认执行计划稳定、没有出现新的慢SQL，再在高峰期观察一轮，确认没有问题后才算真正完成。

如果上线后出现性能下降，优先怀疑是不是优化器没有选择新索引。这时候可以用FORCE INDEX临时确认一下，但不要直接写死在代码里，而是要找为什么优化器不选新索引，通常原因是统计信息过旧，或者新索引的选择性其实并不好。

一点个人体会

做SQL优化这几年，我最深的感受是：索引不是越多越好，也不是建完就万事大吉。真正重要的是理解每条SQL背后的数据访问路径，理解索引的存储结构和锁行为。很多看起来是“MySQL抽风”的死锁或慢查询，本质上都是因为对二级索引和主键索引的关系理解不够。

我现在的习惯是：每次上线索引变更，都会顺手把相关表的死锁日志和执行计划截图存一份，方便后续排查时对照。这个动作看似多余，但真的能在出问题时省下大量时间。也建议你把优化前后的执行计划、耗时数据、并发压测结果记录成文档，下次遇到类似问题时，这些都是最可信的参考。