企业官网建设流程全解析

1. 项目概述：一切从一条慢SQL开始

先说一个我上周刚处理完的真实事故。某业务线的订单列表接口，原本P99延迟在50ms左右，某个周二下午突然飙到2秒以上，超时率接近5%，用户侧反馈“订单页打不开”。我第一时间查慢查询日志，抓出一条典型的全表扫描SQL：

SELECT * FROM order_info WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 20;

下单表大概4800万行，这条SQL之前一直没出过问题，但数据量涨到一定规模后，没有合适索引，MySQL只能一行一行扫全表，再把命中的记录做一次文件排序。EXPLAIN结果也印证了：type是ALL，rows估算4800万，Extra里带着Using filesort。后来我加了一个组合索引，把SQL耗时从1.8秒压到了35ms，接口P99回到60ms以内。从用户体感上讲，这个系统确实“快”了一个数量级还不止。

这就是标题想聊的事：数据库优化不是玄学，核心就两件事，第一会定位慢查询，第二会用索引。这篇文章适合所有写SQL的后端开发、正在救火的DBA，以及想系统搞懂“索引为什么能让查询快10倍”的初中级工程师。

1.1 事故现场：接口P99从50ms涨到2秒

这次事故有两个关键背景，第一是表数据量增长很快，去年还是800万行，今年已经4800万行；第二是业务高峰期并发上来了，多条类似的订单查询同时打到库上，全表扫描的串行成本被放大成灾难。

定位过程不算复杂。我先看监控面板，确认瓶颈在数据库而不是应用服务器。然后打开慢查询日志，用mysqldumpslow把近一小时的慢SQL按耗时排了个序，结果top1就是上面这条订单列表查询。它占了慢查询总量的62%，平均每次1.84秒。再跑一次EXPLAIN，基本就能确认根因：没有索引，走的是ALL全表扫描。加上ORDER BY create_time DESC，MySQL还需要把满足条件的行捞出来做排序，这就是Using filesort的来源。

这里有个非常容易踩的坑：很多人一接到“接口变慢”的反馈，第一反应是去改代码、加缓存、上消息队列。但绝大多数这类问题的第一现场都在数据库。先用慢查询日志把SQL抓出来，剩下的优化才有方向。

1.2 优化不是单点操作，而是一套闭环流程

我自己做慢查询优化，从来不是“找到一条慢SQL，加个索引，完事”。那样只能解决眼前这一条，下个月数据再涨，还会冒出新的慢SQL。我习惯把优化当成一个闭环：

1. 监控和日志：先保证慢查询日志开着，能随时抓到问题SQL。
2. 定位：对慢SQL做EXPLAIN，看访问路径、扫描行数、排序方式。
3. 分析：判断是缺少索引、SQL写法有问题，还是表结构设计不合理。
4. 优化：建索引、改写SQL、拆分查询，或者调整表结构。
5. 验证：上线后对比执行计划、响应时间、系统负载，确认没有副作用。
6. 回归：把这类SQL沉淀成规则，防止后面新增的查询再犯同样的错。

在这次事故里，我前面四步其实只花了半天，后面的验证和回归花了一天。真正需要耐心的是确认“加这个索引不会让写入变慢太多”以及“其他查询会不会因为优化器选择变化受到牵连”。

1.3 为什么说“快10倍”不是夸张

很多人对数据库性能提升的预期是“从300ms优化到250ms”，那确实谈不上10倍。但如果你把一个4800万行的全表扫描改成B+树索引查找，扫描行数从几千万降到几千，这个数量级的差距在数据库里是实打实存在的。

我用一组对比数据来说明。优化前，这条SQL扫描约4800万行，耗时1.84秒；优化后，它通过组合索引直接定位到user_id=12345且status=1的区间，只需要扫描1350行，耗时35ms。从耗时看提升了50倍以上，从扫描量看下降了约3.5万倍。当然接口的最终延迟还要加上网络和业务逻辑开销，所以我说“快10倍”是保守估计，实际体感就是“秒开”和“等半天”的区别。

2. 慢查询日志：怎么把拖垮系统的SQL挖出来

先别急着加索引。任何优化都从定位问题开始。我处理慢查询的第一件事，就是确保慢查询日志开着。很多团队连这个都没开，出问题的时候两眼一抹黑，只能靠猜。

2.1 五个参数打开慢查询日志

MySQL的慢查询日志默认是关闭的，上线后立刻打开，这是最便宜的性能观测手段。动态开启方式很简单：

mysql> SHOW VARIABLES LIKE 'slow_query%'; mysql> SET GLOBAL slow_query_log = ON; mysql> SET GLOBAL long_query_time = 1;

但注意，SET GLOBAL只是临时生效，重启MySQL后配置会丢。要让配置持久化，需要写到my.cnf：

[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1 log_throttle_queries_not_using_indexes = 10

这里有几个参数值得单独解释：

我踩过一个坑：以前图省事把long_query_time设成0，结果每分钟刷几十万条正常查询进日志，把磁盘写满了。生产环境一般设1秒就够了，核心库如果对体验特别敏感，可以设0.5秒，但一定要监控日志增长速度。

2.2 日志分析：手工定位还是工具聚合

慢查询日志打开后，文件会越来越大，靠肉眼一条条看是不现实的。我最常用的两个工具，一个是MySQL自带的mysqldumpslow，一个是Percona Toolkit里的pt-query-digest。

mysqldumpslow适合快速看top N：

mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

-s at表示按照平均查询时间排序，-t 10表示只显示前10条。它会自动把数字参数替换成N，把不同user_id的同类SQL聚合到一起，这点非常实用。比如这次的订单查询，无论user_id是10086还是12345，都会被聚合成一条“SELECT * FROM order_info WHERE user_id=N AND status=N ...”，方便我找到规律。

pt-query-digest更强大，输出报告会按“总耗时”“平均耗时”“出现次数”多个维度排序，还能算出每条SQL在全体慢查询里的占比。我一般先跑这个，把报告拉到本地用文本编辑器看，重点关注执行次数多、平均耗时高的组合。如果一个SQL既频繁又慢，那就是头号嫌疑人。

手工方式也有用武之地，比如你想快速确认某个时间窗口有没有特定SQL，直接grep:

grep "order_info" /var/log/mysql/mysql-slow.log | head -50

不过这种方式的效率低，最终还是建议把工具链建立起来。

2.3 EXPLAIN：给慢SQL做一次全面体检

抓到慢SQL后，下一步是用EXPLAIN看执行计划。不要跳过这一步直接加索引，那样你根本不知道索引加在哪个列上。

EXPLAIN SELECT * FROM order_info WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 20\G

我重点看这几个字段：

type字段从好到坏大致是：const > eq_ref > ref > range > index > ALL。如果看到ALL，基本等于在告诉你要么没索引，要么索引没用上。rows是优化器估算的扫描行数，不是精确值，但数量级很有参考价值。Extra里出现Using filesort意味着MySQL需要额外排序，出现Using temporary意味着用了临时表，这两种都是性能杀手。

我这次查到的结果是type=ALL、key=NULL、rows=48000000、Extra=Using where; Using filesort，看到这里我已经知道，必须靠索引把扫描范围缩小到user_id这一个“扇区”内。

3. 索引为什么能让查询快10倍：B+树原理速讲

如果你只记住了“建索引能变快”，那是不够的，因为你很快会遇到“为什么这个索引没生效”“为什么加了索引反而变慢”这类问题。理解背后的数据结构，才能做出正确判断。

3.1 B+树：数据库把全表扫描变成3次磁盘IO的奥秘

索引在MySQL InnoDB里的实现是B+树。你可以把B+树想象成一本带多层目录的书：最上层的目录页告诉你第1-100章在第几页，第二层目录告诉你第1-10章在第几页，最后一层叶子节点才真正指向正文。

B+树的特点是：非叶子节点只存键值不存数据，所以每个节点能放很多“目录项”；叶子节点存数据，并且用链表串在一起，适合范围查询。因为非叶子节点很“窄”，一棵3到4层的B+树就能容纳几千万行数据。查一条记录，走3到4次磁盘IO就能命中，而全表扫描要把几千万行数据块全部读出来，这个差距就是“快10倍”的根本来源。

这里面还有一层逻辑：索引是为了减少磁盘IO，不是为了让CPU算得更快。一个4800万行的表，全表扫描要读的磁盘页可能成千上万，而B+树查找只需要读几个页。只要理解了“数据库的瓶颈在磁盘IO”，你就能明白为什么索引的价值这么大。

3.2 聚簇索引与非聚簇索引：别忽略存储引擎的影响

讲到索引，必然牵扯到InnoDB和MyISAM的差异。现在的生产库基本都是InnoDB，但面试里“主键索引和唯一索引的区别”“存储引擎的影响”这类问题，本质都和“聚簇”这个概念有关。

InnoDB是聚簇索引组织表：表里的每一行数据，就存放在主键索引的B+树叶子节点上。也就是说，你用主键查询，一次索引查找就能拿到整行数据，不需要再回表。MyISAM则是典型的非聚簇索引：索引文件和数据文件分离，主键索引的叶子节点只存数据行的物理地址，查询主键还得再按地址读一次数据文件。

这件事带来一个实战建议：每张InnoDB表都要有主键，最好还是自增或趋势递增的主键。不然InnoDB会自己找一列唯一字段当聚簇索引，找不到就生成一个隐藏主键。用UUID当主键则容易造成大量随机的页分裂，写入性能会明显受损。

3.3 主键索引和唯一索引的区别

这个问题我在面试别人时经常问，看起来简单，其实不少人答不清楚。

唯一索引的高频误区是“唯一索引能保证NULL不重复”。实际上InnoDB不把NULL当作相等，一张表里可以插入多条phone=NULL的记录，哪怕你在phone上建了唯一索引。如果业务上要求“空值只能出现一次”，唯一索引满足不了，需要在应用层做额外控制。

3.4 覆盖索引：让查询连回表都省了

二级索引（普通索引、组合索引）的叶子节点存的是主键值，不是整行数据。当你通过二级索引查到记录后，还要拿主键再去聚簇索引里回表，才能读到完整数据。回表次数一多，性能也会下降。

覆盖索引就是让查询所需的列全部包含在索引中，这样连回表都省了。比如这次订单优化，我后来把业务语句改成只查必要字段：

SELECT user_id, status, create_time FROM order_info WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 20;

当组合索引是(user_id, status, create_time)时，这三个字段都在索引里，EXPLAIN的Extra会变成Using index，意思是MySQL直接从索引树里取到了全部返回数据，不再回表。对一些高频小查询，覆盖索引的效果比单纯加一个索引更明显。

4. 组合索引实战：where条件里有a和b，到底怎么建字段顺序

这是很多同事最头疼的部分：单列索引好理解，但WHERE条件有两个甚至三个列，组合索引该怎么建？顺序错了，索引就会白建。

4.1 最左前缀原则：别把组合索引理解成“多个独立索引”

组合索引(a, b, c)的内部结构，相当于先按a排序，a相同再按b排序，b相同再按c排序。你可以把它想象成一本先按“姓氏”排、再按“名字首字母”排、再按“名字长度”排的通讯录。

这意味着索引能高效命中的查询模式是：

• WHERE a = ?，可以用到a这一列
• WHERE a = ? AND b = ?，可以用到a和b两列
• WHERE a = ? AND b = ? AND c = ?，可以完整用到三列

但如果直接跳过a，比如WHERE b = ?或者WHERE c = ?，索引就帮不上忙。这就是最左前缀原则。很多新人以为组合索引(a,b,c)等价于给a、b、c各建了一个单列索引，实际不等于。

4.2 字段顺序怎么排：从选择性、等值、范围到排序

回到这次订单表的例子，查询条件是user_id = 12345 AND status = 1，排序是ORDER BY create_time DESC。设计组合索引字段顺序时，我一般按这个顺序来思考：

1. 区分度高的列优先。user_id的取值数量远大于status的取值数量，所以user_id放在最左边，能最快缩小范围。
2. 等值条件放前面，范围条件放后面。两个条件都是等值，那区分度高的优先。
3. 排序字段尽量排进索引。create_time虽然不走过滤条件，但它参与排序，把它放进索引的末尾，有机会消除filesort。
4. 最终方案：(user_id, status, create_time)。

ALTER TABLE order_info ADD INDEX idx_user_status_createtime (user_id, status, create_time);

这个索引有三重价值。第一，查询定位到user_id=12345的区间；第二，在区间内继续用status=1过滤；第三，区间内记录已经按create_time有序，ORDER BY create_time DESC可以反向扫描直接取20条，省掉filesort。

对比一下错误示范：如果建的是(status, user_id, create_time)，status在前，区分度低，优化器可能还是能走索引，但扫描范围会大很多；如果建的是(user_id, create_time, status)，那status过滤就要在查完user_id区间后再逐行判断，效率略低。这些细微差别在千万级数据上会放大成明显延迟。

4.3 排序与范围查询：ORDER BY怎么才能不高价

很多人没意识到，ORDER BY同样可以走索引。如果查询已经通过索引确定了user_id=12345、status=1这个区间，而组合索引恰好包含create_time在末尾，那这个区间内的记录天然就是按create_time升序排列的，MySQL直接反向扫一遍就能拿到DESC结果。

但如果你建的索引只到(user_id, status)，没有create_time，MySQL会把找到的记录单独排一次序。数据量小无所谓，几百万行时就会出现Using filesort，排序消耗的CPU和临时空间都不少。

这里还有个更隐蔽的场景：WHERE a = 1 AND b > 100 ORDER BY c。如果索引是(a, b, c)，b是范围条件，b之后的c列无法继续用于排序，MySQL还是可能filesort。因为b>100跨越了多个b值，在这些b值下的c不保证全局有序。所以遇到范围条件时要特别小心，它后面的索引列基本就“废”了。

4.4 降序索引与“双向索引”：反向排序不再当冤大头

MySQL 8.0之前，索引列只能按升序存储，你在建索引时写DESC其实也会被忽略。这就导致ORDER BY create_time DESC要么反向扫描，性能稍差，要么干脆filesort。MySQL 8.0引入了降序索引，允许在创建索引时指定降序：

ALTER TABLE order_info ADD INDEX idx_user_status_createtime (user_id, status, create_time DESC);

如果业务查询大量是ORDER BY create_time DESC，这个降序索引就能更自然地从左向右顺序扫描，减少反向扫描的开销。社区里偶尔会听到“双向索引”的说法，其实就是在说B+树叶子节点通过双向链表连接，正序反序都能高效访问，配合降序索引就能把两个方向的排序都优化到位。如果你还在用MySQL 5.7，不要指望索引能同时优化升序和降序混合排序，必要时可以把业务排序字段提前到组合索引靠前的位置。

5. 索引失效排查：建了索引却不走，多半是踏进了这六个坑

这是让我踩过最多坑的部分。明明EXPLAIN显示possible_keys里能看到索引，但key是NULL，或者rows还是几百万。下面这六个场景是生产环境里最常见的索引失效原因，建议收藏当速查表。

5.1 六个容易让索引失效的写法

场景一：在索引列上做函数运算

WHERE DATE(create_time) = '2024-01-01'

只要对索引列套了函数，优化器就无法再利用B+树的有序性，因为函数会把原始列值变换掉。改写方式是范围查询：

WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'

场景二：隐式类型转换

表字段是VARCHAR，查询却传了数字：

WHERE phone = 13800000000

MySQL会把索引列做隐式转换，导致索引失效。改成字符串写法就好：

WHERE phone = '13800000000'

判断核心就一句话：是索引列本身被转换，还是查询值被转换。只有索引列被转换才失效。

场景三：LIKE以通配符开头

WHERE name LIKE '%abc'

以%开头的模糊查询，B+树不知道从哪开始匹配，只能全表扫。但'abc%'这种前缀匹配是可以走索引的。业务上真要搜中间字符串，一般建议走全文索引或搜索引擎。

场景四：OR连接多个条件

WHERE user_id = 12345 OR status = 1

OR会让优化器需要同时判断两个条件，如果其中一列没有索引，就很可能放弃索引全表扫描。即使两列都有索引，MySQL的Index Merge机制也不是什么时候都高效。更稳的方式是拆成两个查询用UNION ALL，或者尽量让其中一个条件过滤性足够强。

场景五：违反最左前缀原则

组合索引(a, b, c)，却直接WHERE b = 1 AND c = 2，跳过a，索引大概率用不上。SQL要能走上这个组合索引，最先出现条件里必须包含a。

场景六：范围查询后面的列继续当过滤条件

WHERE user_id = 12345 AND create_time > '2024-01-01' AND status = 1

如果索引是(user_id, create_time, status)，create_time是范围条件，它后面的status无法继续利用索引，只能做回表后的过滤。正确做法往往是把等值条件status放在create_time前面，比如(user_id, status, create_time)。

5.2 判断索引是否生效：EXPLAIN和optimizer trace

遇到“索引建了但没生效”，先用EXPLAIN确认现状，重点看type、key、rows三个字段。如果type不是ALL了，key有值了，rows明显下降了，说明索引生效。如果还是ALL，就把上面六个场景逐个对照SQL。

有时候优化器不太听话，明明有索引它就是不用。这种情况我会开optimizer trace，看看优化器到底怎么评估成本：

SET optimizer_trace = "enabled=on"; -- 执行一遍目标SQL SELECT * FROM order_info WHERE user_id = 12345 AND status = 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace = "enabled=off";

trace里能看到优化器比较全表扫描和索引扫描成本的具体数值。说实话，大部分时候是统计信息过期了，让优化器低估了索引的效果。这时候执行ANALYZE TABLE通常能解决问题。

5.3 关于视图加索引和索引表空间的几个疑问

后台经常收到这几个关联问题，我一起说清楚。

第一个是“Oracle视图能加索引吗”。普通视图本质是一段SQL虚拟出来的结果集合，没有物理存储，所以没法直接给它建索引。你需要在视图查询涉及到的基表列上建索引。如果查询很复杂、聚合很重、还希望有缓存效果，Oracle里可以考虑物化视图，物化视图有实体数据，就可以建索引了。MySQL原生没有物化视图，业务上基本都是用汇总表自己实现。

第二个是“索引表空间”相关。InnoDB里，索引和数据存储在同一个.ibd表空间文件里。innodb_file_per_table=ON的情况下，每张表独立一个表空间，这样便于恢复和空间回收。如果关闭了这个参数，所有表和索引都堆在共享表空间ibdata1里，出问题很难清理。我强烈建议保持ON。

第三个是“索引不是越多越好”。每多一个索引，B+树就要多维护一份，插入、更新、删除时都要同步变更，写入性能会跟着下降。有些表我见过建了七八个单列索引，其实完全可以用两三个组合索引覆盖掉。删除冗余索引之前，至少观察一段时间的慢查询日志，确认没有查询依赖它。

6. 常见问题与排查技巧实录

最后把这几年积累的排查经验整理成速查表，遇到同类的报错和故障，可以直接照着排查。

6.1 常见问题速查表

这张表最下面那一行尤其值得注意。优化器选择全表扫描有时候是对的，比如你要查询的行占了全表的30%，用二级索引回表可能比顺序扫描还慢，因为回表是随机IO。不要为了“看起来走了索引”而牺牲实际性能。

6.2 我在实际调优中养成的几个习惯

第一个习惯，永远先量后优。上grep、慢查询日志、EXPLAIN、性能监控，任何一步缺了，优化都可能变成拍脑袋。

第二个习惯，组合索引的设计要“以SQL为中心”，不是“以为表为中心”。很多人问我这张表该建什么索引，我说不知道，你得先告诉我这张表上最频繁、最关键的查询长什么样。同一个用户表，有的场景是登录查询，有的场景是列表分页，需要的索引完全不同。

第三个习惯，上线后一定要做前后对比。我这次优化后，把EXPLAIN的type、rows、Extra输出和优化前放在同一个文档里，明显看到type从ALL变成ref，rows从4800万降到1350，Using filesort消失。做压力测试时，接口P99从2s以上稳定在60ms以内。没有这些对比数据，你很难知道一次优化到底有没有真正解决瓶颈。

第四个习惯，持续把慢查询优化做成例行机制。我会定期扫一遍慢查询日志，把新出现的慢SQL归档，然后在测试库复现、优化、记录。这套流程跑熟了以后，很多问题在用户感知到之前就已经处理掉了。

最后分享一个小技巧。我办公桌上一直放着个文档，每次给慢SQL做EXPLAIN，都会把type、rows、Extra这三个字段抄下来，优化完成后再抄一遍新的。坚持几个月后，你对“这条SQL应该配什么样的索引”会形成一种很自然的直觉。慢查询和索引优化说到底，就是反复练习判断“数据库读取路径”的过程，练得多了，速度提升其实是水到渠成的事。