企业官网建设流程全解析

同事在群里扔过来一句"这个查询我明明加了索引，怎么还是跑了三秒"，然后甩了一张 EXPLAIN 的截图。这种场景我见得太多，绝大多数时候不是索引没建，而是建了一个数据库根本用不上的索引。SQL 建立索引这件事，说简单也简单，一句 CREATE INDEX 就完事；说难也难，难在你得先想明白数据库拿到这条 SQL 之后，会怎么选路、怎么走这条索引、走完之后还要不要回表。我准备把自己这些年建索引、拆索引、灭掉冗余索引的过程整理一遍，覆盖从原理到 EXPLAIN 逐字段读法，再到那些让索引白建的写法。内容偏实战，涉及 MySQL、SQL Server、Oracle、PostgreSQL 的通用思路，示例以 MySQL 为主。刚接触数据库的同学可以照着跑一遍，写过几年 SQL 的人也可以拿它当一份索引排查清单。

1. 索引到底替数据库省掉了哪一步

1.1 一次没有索引的查询，磁盘上发生了什么

先把最朴素的情况摆出来。假设有一张用户表，十万行，你执行一句：

SELECT * FROM users WHERE phone = '13800000000';

如果 phone 上没有任何索引，InnoDB 会从这张表的第一个数据页开始，把整张聚簇索引的叶子节点顺序读一遍。数据页默认 16KB，十万行记录按每行两百字节算，大约是 2GB 的数据，要读上千个页。即便有 Buffer Pool 缓存，第一次执行也基本是实打实的磁盘 I/O。这就是所谓的全表扫描，EXPLAIN 里体现为 type=ALL。

有人会说十万行不算大，扫过去也就几百毫秒。问题在于，真实业务里的表很少只有十万行，而且这条 SQL 可能每秒被打几千次。单次几百毫秒乘以 QPS，CPU 和磁盘马上就顶不住了。所以索引要解决的核心问题只有一个：把"逐行比对"变成"按位置直接取"。

1.2 B+ 树为什么能撑起这个"直接取"

InnoDB 的索引底层是B+ 树，和教科书里讲的略有差别，但关键特征一致：非叶子节点只存键值和子节点指针，所有真实数据（或主键）都在叶子层，叶子之间用双向链表串起来。

这个结构带来两个直接好处。第一，树的层数很少。InnoDB 的一个数据页 16KB，非叶子节点里一个键值加指针大概十几字节，一页能放上千个条目。三层 B+ 树就能撑住千万级甚至上亿行的记录。也就是说，从根到叶最多三次页读取，第一次读根页，后面两层大概率已经缓存在内存里，真正落到磁盘的往往只有一次。

第二，叶子节点的链表让范围查询变得顺滑。WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'这种条件，定位到起始键之后顺着链表往后读就行，不需要回到上层再重新找。

这里有个容易忽略的点：B+ 树的高度决定了索引的访问代价。当表从百万级涨到亿级时，树的层数可能从三层变成四层，多出来的那一次 I/O 在压测里是能看出来的。这不是危言耸听，而是你在设计大表索引时应该心里有数的东西。

提示：不要指望靠"多建几个索引"解决所有性能问题。索引能加速的是定位行这件事，如果查询本身就要求扫描大量数据（比如统计全表求和），索引帮不上忙，这时候该考虑的是汇总表或缓存，而不是继续加索引。

1.3 聚簇索引与二级索引：回表这件事是怎么发生的

这是整个索引体系里最容易被略过、又最影响性能的一层。

InnoDB 是索引组织表，主键索引的叶子节点直接存放整行数据，这张索引叫聚簇索引。你建的其他任何索引，包括唯一索引和普通索引，都是二级索引，叶子节点里存的是"索引列的值 + 主键值"。

这就解释了回表。执行：

SELECT id, name, phone FROM users WHERE phone = '13800000000';

如果只在 phone 上建了二级索引，数据库会先在这棵树上找到13800000000对应的主键值，比如 id=9527，然后再拿着 9527 回到聚簇索引里把那行完整记录捞出来，从中取出 name 和 phone。这一次"回到主键索引再查一次"的动作，就是回表。

回表的代价是什么？如果命中的行很多，而且这些行在主键索引上分布得很散，那么每回表一次都可能是随机 I/O。一百次回表就是一百次随机读，这个成本远高于走一次索引。所以很多优化到最后都会落到同一个方向：想办法别回表，也就是后面要说的覆盖索引。

理解了这一层，再看 SQL Server 里的聚集索引（Clustered Index）和非聚集索引、Oracle 里的堆表 + ROWID，逻辑其实是相通的——非主结构索引最终都要靠某种行定位符回到真实数据上。区别只在于这个定位符是主键、是 ROWID，还是一个物理位置指针。

2. 从慢查询日志出发，而不是从直觉出发

2.1 先让数据库把慢 SQL 自己交出来

建索引最怕的事情是凭直觉猜。我看到过太多人上来就说"给 created_at 加个索引吧"，加完之后查询没快多少，写还慢了一点。正确的入口应该是让数据库告诉你哪些 SQL 真的慢。

MySQL 的慢查询日志是标准做法：

-- 查看当前配置 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;

long_query_time设成 1 秒是个常见的起点，但别一直留着。业务高峰期这条线上可能一堆正常的批量任务，把日志刷得没法看。我的习惯是先设 1 秒跑一天，看看整体的慢 SQL 分布，然后针对性地收紧到 0.2 秒，专盯高频的短慢查询——那种单次 200ms、每秒几百次的 SQL，比偶尔一次的 3 秒查询更值得优化。

SQL Server 对应的手段是查询存储（Query Store）加动态管理视图：

SELECT TOP 20 qs.total_elapsed_time / qs.execution_count AS avg_elapsed, qs.execution_count, SUBSTRING(st.text, 1, 200) AS sql_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY avg_elapsed DESC;

PostgreSQL 则是pg_stat_statements扩展，按总耗时排序看。

这一步的意义在于先把目标锁定。你手里有一份按总耗时排名的 SQL 清单，优化就不会跑偏。

2.2 EXPLAIN 里那几个必须盯死的字段

拿到慢 SQL 之后，第一件事是 EXPLAIN。很多人只看"有没有用到索引"，这远远不够。下面这几个字段我每次都会逐个看：

key_len这个字段特别值得展开说，因为它是判断复合索引是否被完整利用的最直接证据。以 utf8mb4 的varchar(50)为例，一个可空列在索引里的长度是50 × 4 + 1（NULL 标志）+ 2（变长长度）= 203字节。如果你建了(a, b, c)三列复合索引，而 key_len 只显示 203，那就意味着只用了 a 这一列，后面的 b 和 c 白搭。这个数字能帮你快速确认"最左前缀"到底用到了第几列。

2.3 选择性算一算，别给低区分度列建索引

索引是不是有用，本质取决于选择性。计算方式很简单：

选择性 = 该列不重复值的数量 / 表的总行数

这个值越接近 1 越好。查一下：

SELECT COUNT(DISTINCT status) / COUNT(*) AS sel_status, COUNT(DISTINCT user_id) / COUNT(*) AS sel_user_id FROM orders;

如果 status 只有"待付款、已付款、已发货、已完成、已取消"五个值，选择性就是 0.0000x 级别。给这种列单独建索引，优化器很可能压根不用它——因为走索引定位到 20 万行，再回表 20 万次，还不如直接全表扫一遍顺序读。

经验上的分界线大概是：选择性低于 0.01 的单列索引，基本没有独立存在的价值。但注意，这不等于它不能出现在复合索引里。如果WHERE status = 'pending' AND user_id = 123这种查询频繁出现，把 status 放在复合索引的第二位、user_id 放第一位，反而是合理的。

3. 复合索引的列序，决定它能不能被用上

3.1 最左前缀原则，用一句话讲清楚

复合索引(a, b, c)的排序规则是：先按 a 排，a 相同的按 b 排，b 相同的按 c 排。这就像电话号码簿先按姓排、同姓按名排一样。你可以用 a 单独查，可以用 a+b，可以用 a+b+c，但不能跳着用。直接用 b 查，或者用 b+c 查，这棵树帮不上忙。

具体到 SQL 上，以下几种写法（假设索引是(a, b, c)）：

最后一行是最容易被忽视的陷阱。范围查询之后的列会失去定位能力。所以复合索引的列序有一条几乎通用的原则：等值条件在前，范围条件在后，排序列紧跟等值列。

3.2 一个真实例子：列序错一位，性能差三十倍

我做过一个订单查询，原始 SQL 是：

SELECT order_no, amount, created_at FROM orders WHERE status = 1 AND created_at >= '2024-06-01' AND created_at < '2024-07-01' ORDER BY created_at DESC LIMIT 20;

最初的索引是(created_at, status)。按理说 created_at 选择性极高，放第一位没问题。但实测这条 SQL 每秒执行两百多次时，平均耗时 180ms，P99 超过 800ms。

原因在于：索引先按 created_at 排好，定位到 6 月 1 日之后，然后逐行判断 status 是不是 1。6 月一个月可能有上百万行，其中 status=1 的只占一小部分，剩下的大量行被读了扔掉，还要回表判断。EXPLAIN 的 rows 显示预估扫描 40 万行。

改成(status, created_at)之后：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

数据库先在 status=1 这个等值区间里定位，然后在区间内顺着 created_at 的顺序读，读到 20 行就停。而且因为 ORDER BY 的列正好是索引的第二列，排序这一步完全省掉，Extra 里没有 Using filesort。平均耗时掉到 6ms。列序调换一位，三十倍的差距。

这个案例想说明的是：等值列放前面是为了把范围收窄，范围列放后面是为了让排序顺势而为。如果你的查询里没有等值条件，那范围列当然还是放第一位。

3.3 覆盖索引：把回表这一步直接砍掉

前面说过回表是二级索引的主要成本来源。如果查询需要的所有列都能从索引里拿到，数据库就不需要回表了，这就是覆盖索引。

还是那条 SQL，如果改成：

ALTER TABLE orders ADD INDEX idx_cover (status, created_at, order_no, amount);

那么SELECT order_no, amount, created_at FROM orders WHERE status=1 AND created_at ... ORDER BY created_at DESC LIMIT 20就可以完全在这棵二级索引上完成。EXPLAIN 的 Extra 会显示Using index，这就是覆盖索引的标志。

代价是索引变宽了。order_no 和 amount 两列被复制到索引里，索引占用的磁盘空间增大，写入时也要多维护两个字段。所以覆盖索引不是无脑加列，得看这条 SQL 的调用频次。我的判断标准是：单条 SQL 占总查询量的 5% 以上，或者本身就是核心链路，才值得为它单独设计一个覆盖索引。

3.4 长字段和表达式查询的处理方式

手机号、身份证号、URL 这类长字符串直接建索引，索引体积会很夸张。这时候可以用前缀索引：

ALTER TABLE users ADD INDEX idx_phone_prefix (phone(8));

它只索引前 8 个字符。前缀长度的选取有个简单的验证方法：逐步增加前缀长度，直到选择性接近完整列的选择性为止。

SELECT COUNT(DISTINCT LEFT(phone, 6)) / COUNT(*) AS p6, COUNT(DISTINCT LEFT(phone, 8)) / COUNT(*) AS p8, COUNT(DISTINCT phone) / COUNT(*) AS full FROM users;

如果 p8 已经和 full 很接近，那 8 就够用。

前缀索引有个明确的限制：它无法用于覆盖索引，因为索引里存的不是完整值，数据库没法确定完整内容。另外它对 ORDER BY 和 GROUP BY 的支持也很有限，排序结果可能是前缀序而不是完整序。

表达式查询是另一个场景。WHERE DATE(created_at) = '2024-06-01'这种写法会让 created_at 上的索引用不上，因为索引存的是完整时间戳，不是日期。两种处理方式：改成范围写法created_at >= '2024-06-01' AND created_at < '2024-06-02'，或者在 MySQL 8.0 及以上建函数索引：

ALTER TABLE orders ADD INDEX idx_date ((DATE(created_at)));

我更推荐前者，因为它对数据库版本没有要求，改写成本也低，而且范围写法能同时利用索引的排序能力。

4. 索引建了却不生效，问题通常在这几处

4.1 隐式类型转换，最常见的隐形杀手

这是我在生产环境里遇到次数最多的索引失效原因。典型场景：phone列定义是varchar(20)，但应用传参时传进来一个数字：

-- 索引失效 SELECT * FROM users WHERE phone = 13800000000; -- 索引正常 SELECT * FROM users WHERE phone = '13800000000';

MySQL 在做比较时会遵循一套隐式转换规则：字符串列和数字比较时，会把字符串转成数字。这个转换发生在列上，索引自然就用不了了。EXPLAIN 里 key 是 NULL，type 是 ALL。

排查方法很直接：看一眼表结构里这列是什么类型，再看一眼 SQL 里传的是什么类型。日志里如果打印的是不带引号的参数，基本就能确定问题。

注意：这个坑在 ORM 框架里特别隐蔽。有些框架会根据字段类型自动决定是否加引号，如果你的实体类里把 phone 定义成了 Long，框架就会按数字传参，SQL 层面看毫无问题，索引却默默失效了。

4.2 在索引列上做运算和函数

这条规则可以概括成一句话：索引列必须干净地出现在比较符的一侧。

-- 索引失效 SELECT * FROM orders WHERE YEAR(created_at) = 2024; SELECT * FROM orders WHERE amount * 2 > 1000; SELECT * FROM orders WHERE SUBSTRING(order_no, 1, 4) = '2024'; -- 索引可用 SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'; SELECT * FROM orders WHERE amount > 500; SELECT * FROM orders WHERE order_no LIKE '2024%';

原因还是那棵树：索引是按 created_at 的原始值排序的，不是按年份排序的。你在列上套了函数，数据库就无法用树的排序结构去定位，只能把每一行算一遍。

前置模糊匹配LIKE '%abc'属于同一类问题。它不知道要从前缀的哪个位置开始匹配，只能全扫。如果实在需要这种查询，得考虑全文索引或者额外的倒排结构，单靠普通 B+ 树索引解决不了。

4.3 OR、NOT IN 与不等条件

OR的两侧如果有一侧没索引，整个查询就会退化成全表扫描。

-- 如果 b 没有索引，a 上的索引用不上 SELECT * FROM t WHERE a = 1 OR b = 2;

MySQL 5.7 之后有 index merge 优化，两侧都有索引时可能会合并两个索引的结果，但这种方式并不总是比全表扫快，尤其是两侧命中的行数都很多的时候。更稳的做法是改写成 UNION：

SELECT * FROM t WHERE a = 1 UNION ALL SELECT * FROM t WHERE b = 2 AND a <> 1;

!=、NOT IN、NOT EXISTS这类否定条件也有类似的麻烦。优化器很难判断"不等于"覆盖了多少行，如果比例很高（比如不等于某个罕见值），走索引反而更慢，它会直接选全表扫描。这类查询如果确实高频，通常得从数据模型上想办法，而不是硬在索引上找解法。

4.4 统计信息过期导致执行计划跑偏

有时候索引完全没问题，SQL 也写得很规范，但执行计划就是不选它。这时候要怀疑统计信息。

优化器是靠统计信息估算行数的。如果表在短时间内插入或删除了大量数据，统计信息还停留在旧的状态，优化器就会基于错误的行数估算选错路径。MySQL 里可以手动刷新：

ANALYZE TABLE orders;

SQL Server 里是UPDATE STATISTICS，Oracle 里可以按表或按 schema 收集。

排查这类问题有个好用的手段：看看 EXPLAIN 里的 rows 预估和实际返回行数差多少。差一个数量级以上，基本可以怀疑统计信息。另外可以对比一下EXPLAIN和EXPLAIN ANALYZE（PostgreSQL）或EXPLAIN FORMAT=JSON加EXPLAIN ANALYZE（MySQL 8.0.18+）的结果，前者是预估，后者会真实执行并给出实际行数。

有个坑要提前说：EXPLAIN ANALYZE会真的执行这条 SQL，包括 INSERT 和 UPDATE。别在生产环境随手对一个写语句跑它。

5. 索引的代价：写入变慢、空间占用和冗余清理

5.1 每个索引都在给写入加税

索引不是免费的。每次 INSERT，数据库要往聚簇索引写一份，还要往每一个二级索引各写一份。UPDATE 更麻烦，如果更新的是索引列，还要先删后插。

一张表上挂五六个索引，单条 INSERT 的耗时可能是只有一个索引时的三四倍。这在批量导入场景下非常明显：一个十万行的批量插入，索引多的时候可能从 20 秒变成一分多钟。

所以有一个判断原则：索引数量应该由最核心的读查询决定，而不是把所有被 WHERE 过的列都加上索引。我一般会先看这张表的读写比，读远大于写（比如 100:1）的表可以适当多建；写密集的表（比如日志表、埋点表）则要克制，通常只保留两三个必要的索引。

5.2 冗余索引和重复索引怎么找出来

项目跑一段时间之后，索引往往会失控：A 建了一个(user_id)，B 建了一个(user_id, created_at)，两个索引的第一个列一样，前者其实完全可以删掉，因为它能被后者覆盖。还有更极端的，同一个人在不同时间建了两个完全一样的索引，名字不同而已。

MySQL 里可以用系统视图扫一遍：

SELECT * FROM sys.schema_redundant_indexes; SELECT * FROM sys.schema_unused_indexes;

schema_redundant_indexes会指出哪些索引是另一个索引的前缀，schema_unused_indexes基于performance_schema统计哪些索引从来没被用过。

这里要提醒一句：schema_unused_indexes不能盲信。它只统计了服务器启动以来的使用情况，如果某个索引只在月底结算任务里用一次，而你的实例上周刚重启过，它就会显示"未使用"。所以删除索引之前，至少要确认两件事：统计周期覆盖了完整的业务周期，以及这个索引不是为某个低频但重要的报表任务准备的。

删除索引的语句要写得能回滚：

-- 先记录原定义 SHOW CREATE TABLE orders; -- 再删除 ALTER TABLE orders DROP INDEX idx_status_created;

5.3 碎片与重建

B+ 树在频繁的插入删除之后会产生页分裂，导致索引碎片。表现是索引占用的空间比理论值大很多，扫描时的 I/O 也变多。

MySQL 里查看碎片：

SELECT table_name, index_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb FROM information_schema.tables WHERE table_schema = 'your_db' AND table_name = 'orders';

重建方式在 InnoDB 里其实只有一种推荐做法：

ALTER TABLE orders ENGINE = InnoDB;

这个语句会重建表并重建所有索引。它对大表来说是重量级操作，需要额外磁盘空间，还会持有表锁（MySQL 5.6 之后支持 Online DDL，但仍要评估）。执行前一定要在从库或者测试环境先跑一遍，估算耗时。

SQL Server 里对应的操作是ALTER INDEX ... REBUILD或REORGANIZE，选择逻辑上更清晰：碎片率 5% 到 30% 之间做 REORGANIZE，30% 以上做 REBUILD。这个分界值可以直接照用。

6. 三个改索引的实际场景，从现象到结论

6.1 深分页为什么会越翻越慢

分页查询的经典问题：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

这条 SQL 会先取出前 1000020 行，然后丢掉前 1000000 行。即使走了主键索引，这个读取量也相当可观。

优化方向有两个。第一个是延迟关联，先在索引上把主键捞出来，再回表取数据：

SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 20 ) t ON o.id = t.id;

子查询里的SELECT id是覆盖索引扫描，不需要回表，代价小很多，只有最后 20 行才真正回表。

第二个是游标分页，把上一页最后一条记录的 id 传进来：

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

这种方式在接口设计上需要改成分页参数是last_id而不是page_no。它每页耗时恒定，是三种方案里最快的。代价是不能跳页，适合"下拉加载更多"这种交互。

6.2 联合查询里的驱动表选择

两张表关联时，优化器会选一张作为驱动表，另一张作为被驱动表。被驱动表上的关联列必须有索引，否则每一行驱动数据都要去被驱动表做一次全表扫描。

SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.city = 'hangzhou';

如果 users 上 city 有索引、orders 上 user_id 有索引，优化器可能选 users 做驱动表，用 city 索引筛出一批用户，然后逐个用 orders 的 user_id 索引去查订单。这种执行方式在 EXPLAIN 里表现为驱动表 type=ref、被驱动表 type=ref。

如果发现被驱动表的 type 是 ALL，说明关联列缺索引，这是要立刻补的。判断哪张表在被驱动，看 EXPLAIN 输出的顺序和 key 字段：通常第一行是驱动表。

需要注意的是，MySQL 8.0 之前会为每个关联查询在内存里建临时表（Using join buffer），小于某个阈值就纯内存操作。所以小表驱动大表是优化器的一般倾向，但这不是铁律，实际以 EXPLAIN 为准。

6.3 一个线上问题的完整排查链路

最后分享一次完整的排查过程，我觉得比结论更有价值。

现象是订单列表接口在下午三点到五点之间变慢，P99 从 200ms 涨到 1.2s。数据库监控显示 CPU 使用率从 30% 涨到 65%，慢查询日志里出现了那条订单列表 SQL。

第一步，拿到 SQL，跑 EXPLAIN。type=range，key=idx_status_created，rows 预估 3000。看起来正常，但实际返回只有 20 行。这个差距说明索引选择性在某个时间段内急剧下降。

第二步，查数据分布。发现下午这个时段 status=1 的订单占总量的比例从平常的 5% 涨到了 40%。原因是订单审核任务集中在下午跑，大量订单堆积在 status=1 这个状态。选择性下降，走索引的成本就上来了。

第三步，验证假设。把 status 换成另一个值，同一时间段执行很快。确认问题就出在数据分布倾斜上。

第四步，解决方案。最简单的做法是加一个覆盖索引，让这条 SQL 不需要回表。但更根本的问题是 status=1 这个状态下的数据太集中。最终做法是调整业务逻辑，让审核任务分批执行，同时给索引补上需要的列，扩大覆盖范围。

这个案例想说的是：执行计划正常不等于没问题，要看预估行数和实际行数的差距。数据分布是会变的，索引的效果也会跟着变。定期回看核心 SQL 的 EXPLAIN 结果，比出问题后再救火要划算得多。

6.4 上线新索引前该做的两件事

最后说两个工程习惯。

第一，新索引一定要在接近生产数据量的环境里验证。测试库几千行的表，加什么索引都快，看不出问题。用真实数据的子集（比如抽取一千万行）跑一遍，观察执行计划是不是真的走新索引，以及写入性能下降了多少。

第二，上线要能快速回滚。新增索引本身在 MySQL 5.6 之后支持 Online DDL，对读的影响很小，但依然会占用额外的磁盘和 I/O。我的做法是先记下表的SHOW CREATE TABLE输出，索引命名统一加日期前缀或业务前缀，比如idx_order_status_created_202406，出问题时能一眼认出来是最近加的。索引这东西，加的时候很随意，删的时候才需要勇气，命名规范能省掉很多犹豫。

按我的经验，一套索引体系其实是要跟着数据一起成长的。上线时觉得完美的索引，跑了半年可能就变成了负担，因为查询模式变了，数据分布也变了。与其一次设计到位，不如养成定期看慢查询、定期扫冗余索引的习惯，让索引跟着业务一起调整。