企业官网建设流程全解析

最左前缀法则

第一步：创建实验表

我们创建一张order_detail（订单明细表），并建立一个联合索引(user_id, order_date, product_id)。

sql

-- 1. 创建表 CREATE TABLE `order_detail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '用户ID', `order_date` date NOT NULL COMMENT '下单日期', `product_id` int(11) NOT NULL COMMENT '商品ID', `price` decimal(10,2) DEFAULT NULL COMMENT '价格', PRIMARY KEY (`id`), -- 核心：建立一个联合索引，顺序为 (user_id, order_date, product_id) KEY `idx_user_date_product` (`user_id`, `order_date`, `product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 2. 插入几条测试数据 INSERT INTO `order_detail` (`user_id`, `order_date`, `product_id`, `price`) VALUES (1001, '2026-08-01', 501, 99.00), (1001, '2026-08-02', 502, 120.00), (1002, '2026-08-01', 503, 45.00);

第二步：最左前缀法则的核心原理（B+树排序）

在联合索引(user_id, order_date, product_id)中，数据的排序规则是：

1. 首先按照user_id排序；

2. 如果user_id相同，则按照order_date排序；

3. 如果order_date也相同，则按照product_id排序。

法则口诀：查询条件必须从索引的最左列开始，并且不能跳过中间的列。一旦跳过某一列，该列右侧的列将无法使用索引进行查找（但可能会使用索引覆盖扫描，这个我们后面讲）。

第三步：实战对比（用 EXPLAIN 验证）

我们通过 5 个常见的 SQL 场景，直观看懂“走索引”与“不走索引”的区别。

✅ 场景 1：完全匹配最左三列（完美命中）

sql

EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001 AND order_date = '2026-08-01' AND product_id = 501;

• 结果：key_len较长，type=ref。

• 结论：完全走索引。三个字段都用于缩小范围。

✅ 场景 2：匹配最左边两列（命中前两列）

sql

EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001 AND order_date = '2026-08-01';

• 结果：走索引idx_user_date_product。

• 结论：完全走索引。虽然没查product_id，但user_id和order_date依然有序，没问题。

✅ 场景 3：只匹配最左边一列（命中第一列）

sql

EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001;

• 结果：走索引。

• 结论：走索引。只要包含最左列user_id，就会走索引，只是效率比场景2低一点。

❌ 场景 4：跳过中间列（最左前缀失效——重点！）

sql

EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001 AND product_id = 501;

• 分析：条件中有user_id（第一列）和product_id（第三列），跳过了第二列order_date。

• 结果：key_len只显示用到了user_id的长度（比如 4 字节），product_id未参与索引下推。

• 结论：部分走索引。只有user_id走了索引缩小范围，product_id是在回表后（或索引扫描时）被过滤掉的，无法利用索引的有序性。这会导致 Using Index Condition（ICP），虽然比全表扫描好，但无法达到最精准的定位。

❌ 场景 5：不包含最左列（彻底失效）

sql

EXPLAIN SELECT * FROM order_detail WHERE order_date = '2026-08-01' AND product_id = 501;

• 结果：type=ALL（全表扫描），key=NULL。

• 结论：索引完全失效。因为 B+ 树无法跳过user_id直接去查找order_date，因为所有order_date是分散在各个user_id底下的。

第四步：补充一个极易踩坑的“范围查询”陷阱

规则：如果最左列中的某一列使用了范围查询（>,<,between,like），则该列右侧的列也会停止走索引。

sql

-- 查询 user_id=1001，且日期大于 8月1日 的商品 EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001 AND order_date > '2026-08-01' AND product_id = 501;

• 分析：user_id和order_date都走索引，但由于order_date是范围查询，导致它右边的product_id无法再参与索引查找。

• 结论：product_id = 501只能作为过滤条件（回表后过滤），而不是索引定位条件。

第五步：给你的实战避坑指南（总结）

特别提醒：

1. MySQL 优化器会自动重排：如果你写WHERE B=2 AND A=1，优化器会把它变成A=1 AND B=2，所以不需要担心书写顺序，关键看字段是否存在。

2. 如何救回跳过的列？如果你必须查A=1 and C=3，建议把索引改为(A, C)，或者单独给C建一个索引。

范围查询

1. 核心结论（务必死磕这句）

在联合索引中，一旦某一列使用了范围查询（>、<、>=、<=、BETWEEN、LIKE 'abc%'），该列右侧的所有索引列，将停止参与“查找（Ref）”，只能退化为“过滤（Filter）”。

2. 为什么范围查询会让右侧列失效？（B+树排序原理）

还是我们的索引(user_id, order_date, product_id)。想象索引在 B+ 树叶子节点上的物理排序规则：

• 第一优先级：user_id升序（1, 1, 1, 2, 2...）

• 第二优先级：当user_id相等时，order_date升序（8-01, 8-02, 8-03...）

• 第三优先级：当user_id和order_date都相等时，product_id升序（501, 502...）

关键逻辑来了：
假设你要查user_id = 1且order_date > '2026-08-01'且product_id = 502。

1. MySQL 通过user_id=1和order_date > '2026-08-01'，在 B+ 树中定位到了第一个满足条件的起点（即 2026-08-02 的第一条数据）。

2. 接下来，MySQL 会沿着链表向后扫描，扫描所有user_id=1且日期大于 8月1日的记录（8-02, 8-03, 8-04...）。

3. 致命点：在这个扫描范围内，product_id是完全无序的！因为在同一天（比如 8-02）内，product_id是有序的，但跨天（8-02 和 8-03）时，product_id的大小关系是乱的。所以，MySQL 根本无法利用product_id来做二分查找，只能把扫描到的每一行数据拿出来，回表后判断product_id是不是 502。

3. 用我们那张表做“实验对比”

继续使用索引idx_user_date_product (user_id, order_date, product_id)。

🟢 场景 A：等值查询（完美命中三列）

sql

EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001 AND order_date = '2026-08-01' AND product_id = 501;

• 结果：key_len很长（假设 4+3+4=11字节），type=ref。

• 解读：三个列都精准定位，直接命中 B+ 树的一个叶子节点。这是最高效的。

🔴 场景 B：中间列是范围（右侧列失效——重点！）

sql

EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001 AND order_date > '2026-08-01' -- 范围查询在这里 AND product_id = 501; -- 右边的列，悲剧了

• 结果：key_len只会显示user_id+order_date的长度（比如 4+3=7字节），product_id的长度（4字节）没有计入key_len。

• 解读：product_id=501并没有参与 B+ 树的索引下推（ICP）查找。MySQL 会先找出所有user_id=1001且日期大于 8月1日的所有数据（可能 1000 条），然后把这 1000 条全部回表，再挨个过滤出product_id=501的行。

• 性能损耗：如果这 1000 条数据分布在不同的磁盘页，就会产生大量的随机 I/O。

4. 一个极易混淆的“特例”和“坑”

坑 1：BETWEEN一定是范围查询吗？

• 对于普通字段：BETWEEN '2026-08-01' AND '2026-08-03'等价于>=和<=，属于范围查询，右侧列失效。

• 对于主键/唯一约束：如果BETWEEN包含的值极少（例如主键 id），优化器可能把它当成多个等值查询（IN），但一般不建议依赖这种优化。

坑 2：IN查询属于范围查询吗？

• 答：不属于！WHERE user_id IN (1001, 1002)在 MySQL 优化器中通常被处理为多个等值查询（相当于OR合并），它不会阻断右侧列的索引使用。

• 例外：如果IN列表里有成千上万个值，优化器可能认为全表扫描更快，或者退化为范围扫描，此时才可能阻断右侧列。

坑 3：LIKE通配符的位置决定生死

• WHERE order_date LIKE '2026-08%'—— 这是范围查询（相当于>= 2026-08-01 AND < 2026-09-01），右侧列product_id失效。

• WHERE order_date LIKE '%2026-08%'—— 通配符在前面，索引彻底失效（连order_date都用不上），更别提右侧列了。

5. 为了绕开“范围阻断”，高手怎么调优？

如果业务必须按照user_id+order_date（范围）+product_id来查询，该怎么办？

• 方案一（改索引顺序）：把等值条件放左边，范围条件放最后。

  • 把索引改为(user_id, product_id, order_date)。

  • 这样user_id和product_id精准定位，order_date只负责范围扫描。右侧没有列了，互不干扰！

• 方案二（覆盖索引，不回表）：

  • 如果你只查询索引中包含的字段（比如SELECT user_id, order_date, product_id），虽然product_id不能用于查找，但可以通过覆盖索引（Using index）直接返回数据，避免了回表的随机 I/O，性能损耗大幅降低。

6. 给你一张极简的“红绿灯”速查表（针对联合索引 A, B, C）

最后给你一句保命口诀：

“等值写在最前面，范围写在最后面；一旦范围出现后，右侧列全都不顶用。”

覆盖索引

1. 什么是覆盖索引？（一句话定义）

覆盖索引：指SELECT 查询的字段，直接全部包含在你建立的索引树中，MySQL不需要回表去主键索引（聚簇索引）里取数据，直接从索引树里把数据拿走。

口语化比喻：

• 没有覆盖索引：你去图书馆查书，索引卡上写着“书在3楼5号架”，你得跑过去把书拿来（回表）。

• 有覆盖索引：索引卡上直接印着这本书的全部内容，你连书架都不用去，看一眼索引卡就完事了（不回表）。

2. 如何判断是否用了覆盖索引？（看执行计划）

在EXPLAIN结果中，Extra列如果出现Using index，就代表这条查询用了覆盖索引。

特别注意：Using index和Using index condition（索引下推 ICP）是两码事。前者是“不回表”，后者是“回表前先过滤”，性能差了一个数量级。

3. 实验对比（基于我们的表）

我们的表字段有：id（主键）、user_id、order_date、product_id、price。
我们的索引是：idx_user_date_product (user_id, order_date, product_id)。

🔴 场景 1：没有覆盖索引（需要回表）

sql

EXPLAIN SELECT * FROM order_detail WHERE user_id = 1001 AND order_date > '2026-08-01';

• 分析：索引树里只有user_id、order_date、product_id和主键id。但SELECT *需要取出price字段，索引树里没有price。

• 结果：Extra显示Using index condition（或者Using where）。MySQL 必须拿着查出来的主键id，回主键索引树里去把price取出来。这是随机 I/O，很慢。

🟢 场景 2：完美覆盖索引（不回表） —— 救回“范围查询”的经典用法

我们把刚才那条 SQL 的SELECT *改成只查索引中包含的字段：

sql

EXPLAIN SELECT user_id, order_date, product_id FROM order_detail WHERE user_id = 1001 AND order_date > '2026-08-01';

• 分析：

  1. 虽然order_date用了范围查询，导致product_id不能用于缩小查找范围（上节课内容）。

  2. 但是！因为SELECT只要user_id、order_date、product_id，这三个字段全都长在索引树上。

  3. MySQL 直接扫描索引树（range类型），把扫描到的行直接返回，完全不需要回表。

• 结果：Extra显示Using index。性能等级从“慢查询”直接拉升到“飞快”。

🟢 场景 3：覆盖索引 + 最左前缀缺失（依然能救急）

如果我们非要查order_date和product_id，但没带最左列user_id（导致索引无法用于查找），但查询字段全在索引里：

sql

EXPLAIN SELECT user_id, order_date, product_id FROM order_detail WHERE order_date = '2026-08-01';

• 分析：虽然order_date不是最左列，正常情况下会全表扫描。但这里 MySQL 觉得，索引树比全表（聚簇索引）小得多，直接扫描整个联合索引树（type = index）就能拿到所有数据，不用回表。

• 结果：Extra显示Using index。虽然它扫描了全索引（不是最优，但比全表扫描快），但依然避免了最严重的回表开销。

4. 覆盖索引的“终极大坑”：SELECT *

刚才的例子告诉我们，覆盖索引最大的天敌就是SELECT *。

• 只要你的SELECT里多了一个不在索引里的字段（比如price），Using index就会立刻消失，变成回表。

• 尤其是在order_date >这种范围查询下，如果回表，扫描的行数可能成千上万，随机 I/O 会直接把数据库拖垮。

5. 实战避坑：如何利用“覆盖索引”优化慢查询？

假设业务需求必须查：用户ID、日期范围、商品ID、和价格（price）。

sql

-- 这是慢查询（原版） SELECT user_id, order_date, product_id, price FROM order_detail WHERE user_id = 1001 AND order_date > '2026-08-01';

优化方案 1：偷懒修改索引（推荐）
把索引改成(user_id, order_date, price, product_id)，或者(user_id, order_date, product_id, price)。

• 这样，price也被塞进了索引树，SELECT的所有字段都在索引里，立刻触发Using index。

优化方案 2：拆分成两步（迫不得已）

1. 先走覆盖索引查出主键id：SELECT id FROM order_detail WHERE user_id=1001 AND order_date > '...'（秒出）。

2. 再用查出来的少量id去IN查询，回表拿price（因为这时候id是主键，回表是顺序读，很快）。

6. 覆盖索引 VS 索引下推（ICP）—— 执行计划速判表

7. 给你的终极口诀（结合前两节课）

最左前缀管查找，范围查询断右道；
若想查询飞起来，SELECT只把索引要；
一旦出现Using index，回表开销全扔掉。

最后送你一个习惯：以后写查询，尤其是在联合索引下，先看一眼SELECT的字段。如果发现多了一个不在索引里的“捣蛋鬼”字段，问自己一句：我能不能把它也加进索引里，或者干脆不查它？这一个习惯，能帮你避开 90% 的性能陷阱。

前缀索引：

create index idx_XXX on table_name(column(n));

选择性：select count(distinct substring(phone,1,4)) / count(*) from user_info;