🧠《MySQL 索引从入门到精通》系列目录
📌 第一章：什么是索引？——你查得快，全靠它！
- 生活中的类比：图书馆、快递仓库、外卖订单系统
- 为什么没索引会很慢？
- MySQL 是怎么“查找”的？
📌 第二章：索引长啥样？B+树来一棵！
- 什么是 B+树？为啥不是 B 树或哈希？
- 图解索引结构
- 聚簇索引 VS 非聚簇索引（InnoDB 的套路）
📌 第三章：索引都有啥类型？认识你能建的这几种
- 普通索引 / 唯一索引 / 主键索引 / 全文索引
- 单列 VS 联合索引
- 建立索引的语法大全 + 手把手示例
📌 第四章：索引怎么失效？（这才是踩坑大头）
- 万恶的
like '%xx' - 函数包裹字段、隐式转换
- 排序 / or 条件 / != 运算符
- 索引失效的实战案例演示
📌 第五章：MySQL explain 用法详解（让它告诉你慢在哪）
- 每一列参数讲解（type、key、rows、extra等）
- explain + SQL 案例对照看懂执行路径
- 如何判断某条语句有没有用上索引？
📌 第六章：联合索引的正确姿势
- 什么是“最左前缀原则”？
- 为什么顺序错了，索引就废了？
- 索引覆盖、回表、下推
📌 第七章：建索引前要三思！什么时候别建？
- 误用索引的真实代价（空间 + 写入速度）
- 什么字段适合建？什么字段别碰？
- 选择度是个啥？建索引先看它
📌 第八章：实战！从慢 SQL 到优化飞起
- 几条常见慢 SQL 优化案例（含 explain）
- 如何快速判断：要不要加索引？
- explain + 索引策略调整 + 对比图表
📌 第九章：高频面试题讲透（附标准答案）
- 联合索引 + 最左匹配 + 覆盖索引
- 为什么索引失效？
- explain 里 type = ALL 是不是就很糟糕？
📦 附录/Bonus 内容：
- 创建测试表脚本（含10万条数据）
- 常用 explain 模板对照表
- MySQL 索引优化 checklist（上线前必查清单）
第一章：什么是索引？——你查得快，全靠它！
💡你以为你在查表？其实你查的是“表的导航”！
🥚小白困惑时间
你是不是也有这样的经历👇：
- 表建好了，字段填满了，就是查得贼慢？
- 明明一张只有十几万行的表，
WHERE id = 123456都能查半天？ - DBA 一句话：“你这没加索引。”然后你就装作听懂的样子点点头。
好，今天我们把这事儿说清楚：索引到底是啥？为啥有它就快了？
📚我们来类比一下
假设你有 10 万本书，堆在一起。你要找一本叫《MySQL八股文》的，怎么办？
- ❌ 方法一：一本一本翻，翻到天荒地老。
- ✅ 方法二：先查目录，目录告诉你它在第 37893 本。直接拿就完了！
MySQL 也是一样。“目录” 就是 “索引”。
有了它，MySQL 不用一个个数据页去翻，而是：
- 通过索引快速找到数据位置；
- 一把锁定，拿走你要的那一条记录。
🧠MySQL 索引到底是个啥？
通俗点说：
索引就是数据库帮你提前做好的“查找表”，你查的时候，它用这张表来“定位”到目标数据。
它的底层实现是一棵多路平衡搜索树（B+ 树），像这样👇：
[30 | 70]
/ \
[10 20] [40 50 60] [80 90]
我们不急着讲 B+ 树的原理（下章再整），这里只要记住：它查找特别快，通常是 O(log n) 的效率。
🔍实操小实验：有索引 VS 没索引
来，我们造一张大表。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50),
email VARCHAR(100),
INDEX idx_email (email) -- 给 email 建个索引
);
插入数据：
INSERT INTO users (username, email)
SELECT
CONCAT('user', LPAD(id, 5, '0')),
CONCAT('user', LPAD(id, 5, '0'), '@test.com')
FROM (
SELECT @row := @row + 1 AS id FROM
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
(SELECT @row := 0) t0
) a LIMIT 50000;
执行查询：
-- 用索引查
EXPLAIN SELECT * FROM users WHERE email = 'user12345@test.com';
-- 去掉索引，再查
ALTER TABLE users DROP INDEX idx_email;
EXPLAIN SELECT * FROM users WHERE email = 'user12345@test.com';
看执行计划的 type 和 rows 字段，秒懂“有索引”和“没索引”的差距。
🔧你查得快，是因为有人提前把路铺好了
你以为你写了一条 SQL，数据库就硬刚 10 万行去扫？并不是。
有索引的时候：
- 你是在走“高速公路”
- 没索引的时候：
- 你是在“摸黑爬山路”
🧾本章小结（划重点时间）
| 概念 | 内容解释 |
|---|---|
| 索引（Index） | 一种加速数据查询的结构，类似于图书馆目录 |
| 作用 | 降低查找时间复杂度，从 O(n) 降为 O(log n) |
| 结构 | MySQL 默认使用 B+ 树 实现索引（尤其是 InnoDB 引擎） |
| 注意 | 索引不是越多越好！会占空间，也会影响写入性能 |
🎁彩蛋：你能靠这张图，向面试官讲清楚“索引是干嘛的”👇
- 有索引：EXPLAIN 结果 type = ref/index，rows < 1000
- 没索引：type = ALL，rows = 全表行数
- 对比图表：索引平均查询 < 1ms，全表扫描可能要 100ms+
第二章：索引长啥样？🌳B+树来一棵！
“不是所有的树都能爬，但数据库偏偏就爱爬B+树。”
🎯 一、MySQL 索引背后的秘密武器：B+ 树
当你写下一句：
SELECT * FROM users WHERE id = 123;
你以为 MySQL 是“从头翻到尾”找到 123 吗？错！
实际上，它打开了 InnoDB 的“小黄页”（也就是 B+树索引），顺着枝干走几步，直接定位到了那条记录。效率不是翻倍，是“乘以十倍”起步！
🌲 二、啥是 B+ 树？它和 B 树、二叉树有啥区别？
| 树类型 | 特点 | 用不用来做索引？ |
|---|---|---|
| 二叉搜索树（BST） | 一边大一边小，容易变斜 | ❌ 太不稳定 |
| B 树 | 多路搜索树，数据存在每个节点 | 🟡 一般般 |
| B+树 | 数据都在叶子节点，叶子之间还有链表 | ✅ 索引首选，查找 + 范围都高效！ |
一图胜千言：
[17, 35]
/ | \
[3, 9] [17, 25] [35, 50]
所有的数据（比如 3、9、17、25、35、50）都在叶子节点，内部节点只是导航用。
你就像个快递员，从门牌号导航直接走到你要送的门，效率嗖嗖滴！
🧠 三、InnoDB 的“聚簇索引” VS “二级索引”
InnoDB 存储引擎（你99%都在用）默认使用的是聚簇索引（Clustered Index），这和你想象的有点不一样：
- ✅ 主键索引（聚簇索引）：数据和索引放一起。
- ✅ 辅助索引（Secondary Index）：只存索引值 + 主键指针，真正数据还得回表查。
🍜 四、来点实际的 SQL 示例：一棵小小 B+树是怎样长出来的？
👷 创建一个简单表：
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
age INT
) ENGINE=InnoDB;
InnoDB 会自动以 id 为聚簇索引，数据直接挂在 B+树叶子上。
🍱 再来加点料：创建辅助索引
CREATE INDEX idx_username ON users(username);
这是个辅助索引（非聚簇索引），它记录的是 username + id，真要查别的字段（比如 email），还得“回表”找。
🚦 五、B+树为什么这么牛？一棵树能有多高？
MySQL 的 B+树每个节点通常能存 几百条指针。
就算你有 2000 万条记录，B+树也只需“走三层”就能命中目标。就像电梯直达 28 楼，不用一层层走楼梯。
🧪 六、实战：插入、查询、观察索引效果
-- 插入测试数据
INSERT INTO users (id, username, email, age)
SELECT id, CONCAT('user', id), CONCAT('user', id, '@test.com'), FLOOR(20 + RAND()*30)
FROM (
SELECT @row := @row + 1 AS id FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
(SELECT @row := 0) r
) a
LIMIT 10000;
-- 用辅助索引查 username
EXPLAIN SELECT * FROM users WHERE username = 'user999';
你应该会看到：
key: idx_username
type: ref
rows: 1
说明它用上了刚才那个 idx_username 辅助索引！
🧘 七、小结 + 彩蛋冷知识
- 索引是一棵 B+ 树，主键索引是数据本身的家。
- 辅助索引只是“路标”，真正数据还在主键索引里。
- 索引多≠一定好，建错了、用错了都是浪费空间+性能灾难。
💡彩蛋：InnoDB 的主键索引是强绑定的，没有主键它会自动找一个唯一键，没有唯一键它会悄悄加一个隐藏自增ID列来做主键！
第三章：索引都有哪些类型？认识你能建的这几种
“给我来一打索引！”
—— 一位刚学完索引的新手程序员，挥着建表语句喊道。
MySQL 提供了多种索引，就像你点外卖时能选黄焖鸡、炸鸡排、鸡公煲……
不同场景，不同口味，别乱点！
🥩 1. 普通索引（普通但不简单）
最常见的索引类型，支持快速查询，但不强制唯一。
语法：
CREATE INDEX idx_username ON users(username);
你可以在 username 上建个普通索引，用于模糊查人、分页等操作。
🧲 2. 唯一索引（想插重复的？没门）
强制不能有重复值，MySQL 会帮你“看大门”。
语法：
CREATE UNIQUE INDEX uniq_email ON users(email);
也可以直接在建表时声明：
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255) UNIQUE
);
💡适用于手机号、身份证号、邮箱这种天然唯一的字段。
👑 3. 主键索引（建表送一棵）
每个表只能有一个主键索引，默认也是唯一索引。InnoDB 引擎里，它就是聚簇索引（后面讲）。
建表时声明：
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
MySQL 会自动在 id 上建主键索引，你不说它也会自己安排。
🔍 4. 组合索引（加个群，查得快）
又叫联合索引，是多个字段合并成的一棵索引树。适合用于多个条件联合查询。
语法：
CREATE INDEX idx_user_info ON users(username, email);
查询时只要匹配 username 或 username + email，这个索引就生效。
⚠️ 注意最左前缀法则，顺序不能乱！后面专章讲。
🧬 5. 全文索引（支持模糊匹配的利器）
用于文本搜索（类似搜索引擎）。支持 MATCH ... AGAINST 语法，但别搞错，它不能支持 like ‘%xxx%’。
语法：
CREATE TABLE articles (
id INT PRIMARY KEY,
title TEXT,
FULLTEXT(title)
);
然后这样查：
SELECT * FROM articles
WHERE MATCH(title) AGAINST('MySQL 索引');
🌟 MyISAM 默认支持全文索引，InnoDB 需 MySQL 5.6+ 才支持。
⚙️ 6. 空间索引（GIS场景专用）
仅用于存储空间数据（如经纬度坐标），用得不多，小白可以先跳过。
🎬 实战示例：一口气建几个索引！
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
bio TEXT,
created_at DATETIME,
UNIQUE KEY uniq_email (email),
INDEX idx_username (username),
INDEX idx_user_phone (username, phone),
FULLTEXT KEY ft_bio (bio)
);
🔍 查数据就能利用这些索引加速：
-- 命中 username 索引
SELECT * FROM users WHERE username = 'coderxiaobai';
-- 命中联合索引 username + phone（注意字段顺序）
SELECT * FROM users WHERE username = 'coderxiaobai' AND phone = '13812345678';
-- 命中全文索引 bio
SELECT * FROM users WHERE MATCH(bio) AGAINST('DBA');
🧠 总结一句话：
| 类型 | 是否唯一 | 适用场景 |
|---|---|---|
| 普通索引 | 否 | 常规字段加速查询 |
| 唯一索引 | 是 | 邮箱、手机号、身份证 |
| 主键索引 | 是 | 每张表只能有一个 |
| 联合索引 | 依赖顺序 | 多条件组合查询 |
| 全文索引 | 否 | 文本搜索 |
| 空间索引 | 否 | 地图/GIS应用 |
别一口气加太多索引，否则你的写入就像被加上了 10 个拖拉机轮子，动弹不得。
📌 第四章：索引怎么就突然“失效”了？！（含实战 SQL）
“你以为你用了索引，结果 MySQL 偷懒全表扫了！”
💥 索引失效是怎么回事？
MySQL 建了索引，不代表你一定能用上。
就像你花重金装修了厨房，但天天点外卖。MySQL 也可能选择 不用索引，转而“全表扫描”，一顿瞎忙。
这章我们来拆解：到底是什么操作，导致索引无效？
❌ 1. LIKE '%abc' —— 通配符开头，索引全废！
-- 假设 name 上有索引
SELECT * FROM users WHERE name LIKE '%张三';
解释：
MySQL 无法用索引做定位，因为 % 开头，它无法从 B+ 树根节点快速定位，只能老老实实扫全表。
✅ 推荐方式：
-- 用右匹配可以使用索引
SELECT * FROM users WHERE name LIKE '张三%';
❌ 2. 对索引字段使用函数 or 运算
-- 索引字段被函数包裹了
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';
这条语句没法用到 create_time 上的索引，因为 DATE(create_time) 改变了字段原始值，MySQL 无法用 B+ 树对比了。
✅ 推荐做法：
-- 改成范围查询，让索引派上用场
SELECT * FROM orders
WHERE create_time >= '2024-01-01 00:00:00'
AND create_time < '2024-01-02 00:00:00';
❌ 3. 隐式类型转换，坑你没商量
-- user_id 是 INT 类型，但你写了字符串
SELECT * FROM users WHERE user_id = '123';
虽然很多时候 MySQL 能“猜”出来，但可能造成索引失效（特别是在一些字符集或非默认配置中）。
✅ 推荐写法：
SELECT * FROM users WHERE user_id = 123;
❌ 4. 使用 or 且字段未同时建立索引
SELECT * FROM products
WHERE category_id = 1 OR name = '玩具';
只有 category_id 建了索引，而 name 没有，MySQL 就干脆全表扫。
✅ 推荐做法：
- 给两个字段都建索引，或拆成 union 查询。
SELECT * FROM products WHERE category_id = 1
UNION
SELECT * FROM products WHERE name = '玩具';
❌ 5. 使用 !=、<>、NOT IN、NOT LIKE
SELECT * FROM employees WHERE salary != 5000;
这些操作让 MySQL 无法使用范围查找，因此大概率放弃索引。
✅ 推荐方式：
能换成 IN、BETWEEN 就换。
🧪【实战：索引失效案例对比】
准备数据：
CREATE TABLE test_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT,
INDEX idx_name (name)
);
-- 插入一些数据
INSERT INTO test_users (name, age)
SELECT CONCAT('name', FLOOR(RAND()*1000)), FLOOR(RAND()*100)
FROM dual
CONNECT BY LEVEL <= 10000;
对比 explain：
-- 使用了索引
EXPLAIN SELECT * FROM test_users WHERE name LIKE 'name1%';
-- 索引失效，全表扫
EXPLAIN SELECT * FROM test_users WHERE name LIKE '%1';
看 type 字段：
- 使用索引一般是
range、ref - 没用就是
ALL
📌 小结：常见索引失效行为列表
| 错误操作 | 原因说明 |
|---|---|
LIKE '%abc' | 左通配符不能走索引 |
函数包裹字段 DATE(col) | 改变字段值 |
不匹配类型 WHERE id = '1' | 隐式转换 |
OR 条件未全部建索引 | 查询合并不走部分索引 |
!= / NOT IN / NOT LIKE | 无法使用 B+ 树范围 |
📌 第五章：MySQL Explain 用法详解
——“让它告诉你：你的 SQL 慢在哪”
你是不是也经历过这种场景：
“SQL 跑得慢？我加了索引啊，怎么还是不行？”
别急！你得听听 MySQL 自己怎么说——这时候 EXPLAIN 就派上用场了。
🧪 什么是 EXPLAIN？
EXPLAIN 可以让你看到 MySQL 执行 SQL 时的“执行计划”，就像医生的体检报告，告诉你有没有用上索引，是不是全表扫描，有没有回表等等。
语法也很简单：
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
或者新版语法更酷一点：
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = 'Alice';
🔍 EXPLAIN 输出字段逐个看
| 字段名 | 作用解释 |
|---|---|
id | 查询中 SELECT 的序号，越大越先执行 |
select_type | 简单查询 or 子查询 or union 等 |
table | 当前正在访问的表名 |
type | 连接类型，越靠前越好（比如 const > ref > ALL） |
possible_keys | 可能使用的索引 |
key | 实际使用的索引 |
key_len | 使用索引字段的长度 |
ref | 哪个列或常量与 key 做比较 |
rows | 预估扫描行数 |
Extra | 额外信息，比如 Using where, Using index, Using temporary 等 |
🧪 示例 SQL + EXPLAIN 结果解析
建个小表：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
email VARCHAR(100),
age INT,
INDEX idx_name (name),
INDEX idx_age (age)
);
插点假数据：
INSERT INTO users (name, email, age)
SELECT
CONCAT('User', FLOOR(RAND() * 10000)),
CONCAT('user', FLOOR(RAND() * 10000), '@test.com'),
FLOOR(RAND() * 80)
FROM information_schema.tables LIMIT 10000;
示例1：使用了索引的查询
EXPLAIN SELECT * FROM users WHERE name = 'User123';
返回：
| id | select_type | table | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | users | ref | idx_name | idx_name | 1 | Using index condition |
✅ type=ref，表示使用了 name 索引，性能不错。
示例2：索引失效的写法
EXPLAIN SELECT * FROM users WHERE LEFT(name, 4) = 'User';
| id | select_type | table | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | users | ALL | NULL | NULL | 10000 | Using where |
⚠️ type=ALL + key=NULL 表示全表扫描，索引完全没用上。
原因是用了 LEFT(name, 4)，MySQL 没法用 name 的索引，索引字段不能被函数包住！
示例3：多条件查询匹配联合索引
ALTER TABLE users ADD INDEX idx_name_age (name, age);
EXPLAIN SELECT * FROM users WHERE name = 'User123' AND age = 25;
| id | select_type | table | type | key | key_len | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | users | ref | idx_name_age | ??? | 1 | Using index condition |
✅ idx_name_age 起飞了，联合索引最左匹配原则满足！
🧠 常见 type 类型（性能由好到差）
const：常量，最优，比如id=1eq_ref：唯一索引且只返回一条（比如主键）ref：普通索引匹配多条range：范围扫描（比如BETWEEN、<）index：扫描整个索引ALL：最差，全表扫描
🧾 总结 Checklist
✅ 看 type，是否为 ALL（全表扫描）
✅ key 是否为空？表示有没有用上索引
✅ rows 是否太大？太大可能是慢的原因
✅ Extra 里有没有 Using temporary、Using filesort，这些会拖慢查询速度
📘 第七章：建索引前要三思！什么时候别建？
🧠 开篇一问：索引不是越多越好吗？
很多新手误以为：“索引能提高查询速度，那我每个字段都建索引就好了啊！”
——大错特错。
就像你给所有衣服都贴上标签，查找是快了，但你要花的时间贴标签、维护标签、搬衣服也变多了。而数据库的“搬衣服”过程，就是写入性能和空间开销的增加。
🚫 什么时候不要建索引？
❶ 经常更新的字段（高频 UPDATE/DELETE）
举个栗子🌰：
CREATE TABLE user_status (
id INT PRIMARY KEY,
user_id INT,
last_active_time DATETIME,
login_count INT
);
如果你对 last_active_time 或 login_count 频繁更新，而且它们也不是主要的查询条件，就不要建索引，不然每次更新都要维护索引页，得不偿失。
❷ 低基数字段（例如性别、布尔类型）
SELECT * FROM users WHERE gender = 'male';
gender 字段只有两种值（男/女），不论怎么建索引，MySQL 扫的记录还是占大半，优化不了啥。
一般建议：字段不同值太少（比如不超过10种），大概率不值得建索引。
❸ 很少参与 WHERE 条件的字段
如果你几乎从不拿某个字段当查询条件、也不用来排序、分组，那建索引干嘛呢？
比如：
CREATE TABLE orders (
id INT PRIMARY KEY,
product_id INT,
order_notes TEXT
);
order_notes 是备注字段，不查它，不用它排序，也不 GROUP BY 它，建它干嘛？省点磁盘空间不好吗？
📏 小技巧：评估一个字段值的“选择度”
选择度（selectivity）= 不同值的个数 / 总记录数
- 值越多 → 选择度高 → 越适合建索引
- 值越少 → 选择度低 → 建了也白建
用 SQL 简单评估一个字段选择度：
SELECT COUNT(DISTINCT gender) / COUNT(*) AS selectivity FROM users;
建议：选择度 < 0.1 的字段，一般不建索引（除非用于联合索引）
🛠 示例实操：建索引的前后对比
创建测试表
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
department_id INT,
gender CHAR(1),
salary INT
);
-- 插入模拟数据
INSERT INTO employees (name, department_id, gender, salary)
SELECT
CONCAT('User_', id),
FLOOR(1 + RAND() * 10), -- department_id 取值范围：1-10
IF(RAND() > 0.5, 'M', 'F'),
FLOOR(3000 + RAND() * 7000)
FROM (SELECT @id := @id + 1 AS id FROM information_schema.columns LIMIT 100000) a;
查询效率对比：gender 字段建不建索引
-- 不建索引执行查询
SELECT * FROM employees WHERE gender = 'M';
-- 建索引再查
CREATE INDEX idx_gender ON employees(gender);
SELECT * FROM employees WHERE gender = 'M';
然后用 EXPLAIN 对比两者的 rows 和 key 字段差异。
🎯 总结一下本章核心：
| 条件 | 是否适合建索引 |
|---|---|
| 经常更新的字段 | ❌ 不推荐 |
| 低基数字段（性别、状态） | ❌ 通常不推荐 |
| 不参与查询条件的字段 | ❌ 没必要 |
| 选择度高、经常查询 | ✅ 强烈推荐 |
| 用于排序、分组、JOIN 的字段 | ✅ 建起来！ |
📘 第八章：实战！从慢 SQL 到优化飞起
🔍 一句慢 SQL 是怎么炼成的？
一个查询慢，不外乎：
- 表太大
- 没有用上索引
- 用了低效的 SQL 写法
- 索引失效
- 回表太多
- 查询方式不合理（比如用
%like%）
下面我们一条条来实战讲解 + 优化示例。
🎯 场景一：没用上索引导致全表扫描
👎 慢 SQL 示例
SELECT * FROM employees WHERE department_id + 1 = 6;
你建了索引却没用：
CREATE INDEX idx_dept ON employees(department_id);
为什么没用？因为你写了 department_id + 1，MySQL 无法用索引做等值匹配。
✅ 优化写法
SELECT * FROM employees WHERE department_id = 5;
或者在应用层处理表达式。
🔍 EXPLAIN 对比
| 查询 | type | key | rows | Extra |
|---|---|---|---|---|
| 原SQL | ALL | NULL | 100000 | Using where |
| 优化后 | ref | idx_dept | 1000 | Using index |
🧊 场景二：范围查询 + 排序导致无法使用索引
👎 慢 SQL 示例
SELECT * FROM employees WHERE salary > 5000 ORDER BY department_id;
你以为有 salary 和 department_id 两个索引就行了？不对。
✅ 优化策略：覆盖联合索引 + 顺序写 WHERE
CREATE INDEX idx_salary_dept ON employees(salary, department_id);
SELECT * FROM employees WHERE salary > 5000 ORDER BY department_id;
MySQL 遇到范围条件（比如
>,<）后，会停止继续使用联合索引的剩余部分。所以字段顺序很重要！
⚠️ 场景三：LIKE 模糊查询导致索引失效
👎 典型反例
SELECT * FROM employees WHERE name LIKE '%张三%';
以 % 开头 → 索引无法使用，变全表扫描。
✅ 可选优化方式
- 改成右匹配：
SELECT * FROM employees WHERE name LIKE '张三%'; -- 可用索引
- 使用全文索引（MySQL 5.6+）：
ALTER TABLE employees ADD FULLTEXT(name);
SELECT * FROM employees WHERE MATCH(name) AGAINST ('张三');
🧪 实战演练：抓一条慢 SQL 优化
❌ 初始查询：
SELECT * FROM employees WHERE gender = 'M' AND salary > 6000 ORDER BY department_id;
EXPLAIN 输出（简化）：
| type | key | rows | Extra |
|---|---|---|---|
| ALL | NULL | 100000 | Using where; filesort |
✅ 优化步骤：
- 创建覆盖联合索引：
CREATE INDEX idx_gender_salary_dept ON employees(gender, salary, department_id);
- 改写查询（避免 SELECT *）：
SELECT id, name, department_id FROM employees
WHERE gender = 'M' AND salary > 6000
ORDER BY department_id;
优化后 EXPLAIN：
| type | key | rows | Extra |
|---|---|---|---|
| range | idx_gender_salary_dept | 2000 | Using index |
🟢 索引生效，filesort 消失，速度飞起！
📌 小总结：优化思路
| 优化方向 | 技巧 |
|---|---|
| 索引能否命中 | WHERE 条件别乱写表达式 |
| 排序是否走索引 | 联合索引顺序别写错 |
| 避免全表扫描 | SELECT 字段要精准 |
| 避免回表 | 使用覆盖索引 |
| 模糊匹配优化 | 尽量右匹配或全文索引 |
| EXPLAIN 解读 | 看 type、rows、key |
📘 第九章：EXPLAIN 全解析 —— 看懂它，你就能读懂 MySQL 的大脑！
EXPLAIN 是 SQL 优化的放大镜，它能告诉你这条语句 是怎么执行的、用了啥索引、扫了多少行、有没有排序、有没有临时表……
来看个例子：
EXPLAIN SELECT name FROM employees WHERE department_id = 3;
结果大概长这样：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | ref | idx_dept | idx_dept | 5 | const | 100 | Using where |
我们来逐个解释每一列是什么意思👇
🧠 字段详解：你必须会读懂的部分
🔹 id：查询步骤的 ID
- 单表查询就是
1 - 多表关联时，ID 越大，表示执行顺序越靠后（子查询优先执行）
🔹 select_type：查询类型
常见值有：
| 值 | 含义 |
|---|---|
| SIMPLE | 简单查询（非子查询/UNION） |
| PRIMARY | 最外层 SELECT |
| SUBQUERY | 子查询 |
| DERIVED | 派生表（from 子查询） |
| UNION | UNION 中的第二个及以后查询 |
🔹 table：当前这一行对应的是哪个表
🔹 type：访问类型（越靠左越高效）
| type | 说明 |
|---|---|
| system | 表只有一行（rare） |
| const | 最优！用主键或唯一索引等值匹配 |
| eq_ref | 每次从索引中取一行（JOIN 时常见） |
| ref | 用了索引，匹配多行 |
| range | 范围扫描（BETWEEN、>、<） |
| index | 全索引扫描（没有用 WHERE） |
| ALL | 最差！全表扫描 |
🔥 优化目标是让
type尽量往上靠。
🔹 possible_keys：哪些索引可以用
如果是 NULL，说明 SQL 写法有问题（比如字段运算、模糊匹配等）导致 一个索引都没用上。
🔹 key：实际用的是哪个索引
不等于 possible_keys 中的第一个，不代表一定选最优的索引（优化器有自己的想法😅）。
🔹 key_len：用到的索引长度（字节数）
判断是否用上了联合索引的所有字段，可以推断是否发生了“索引下推”或“索引裁剪”。
🔹 ref：用哪个列和索引比较
比如：const, func, NULL, 或者 table.column
🔹 rows：预估要扫描多少行
这个值越大越慢，可以配合 ANALYZE 来精确估算。一般用于衡量慢 SQL 是否需要优化。
🔹 Extra：其他重要信息
常见值：
| Extra | 含义 |
|---|---|
| Using where | 用了 WHERE 过滤条件 |
| Using index | 使用了覆盖索引（无需回表） |
| Using filesort | 额外排序操作（可能慢） |
| Using temporary | 用了临时表（常出现在 GROUP BY） |
| Using join buffer | JOIN 时无法用索引，走了内存缓冲区 |
❗
Using filesort和Using temporary是性能杀手，要特别注意。
🧪 示例：一条普通 SQL 的 EXPLAIN 解读
SELECT id, name FROM employees WHERE department_id = 2 ORDER BY name;
EXPLAIN：
| id | select_type | table | type | key | key_len | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | ref | idx_dept | 5 | 100 | Using where; filesort |
解读：
- ✅
ref表示走了索引 - ❌
filesort表示用了排序，没法走索引排序 - 👉 想消掉
filesort？加个联合索引：
CREATE INDEX idx_dept_name ON employees(department_id, name);
再执行一次：
| id | select_type | table | type | key | key_len | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | ref | idx_dept_name | 10 | 100 | Using where |
🟢 排序问题解决，执行效率更高！
✅ 小结：EXPLAIN 是 MySQL 的 CT 扫描仪
你查得慢，MySQL心里都写在 EXPLAIN 里了。
掌握它，你就能：
- 看出有没有走索引
- 判断是否发生了回表、排序、临时表
- 推断查询扫描的行数，评估是否该加索引
- 理解优化器的“奇怪行为”背后逻辑
📘 第十章：索引设计实战宝典
—— 加错字段，白加索引！
🎯 一、索引设计的终极目标
索引不是越多越好，而是用得好、加得巧才有效。设计好的索引能：
- 大幅提高查询速度
- 减少磁盘IO
- 降低服务器负载
设计差了，非但没提升，反而拖垮性能，写入变慢还浪费空间。
🧩 二、实战原则 1：明确“最常用查询场景”
问问自己：
- 业务中最常用的查询条件是什么？
- 你最频繁用哪个字段做
WHERE、JOIN、ORDER BY、GROUP BY？ - 查询结果返回哪些字段？是否能用覆盖索引？
🔑 三、实战原则 2：遵循“最左前缀原则”
联合索引的字段顺序很关键！索引只会利用从左边开始的连续字段。
例如：
CREATE INDEX idx_name_age ON users(name, age);
WHERE name = '张三'会用上索引WHERE name = '张三' AND age = 30也会用- 但
WHERE age = 30不会用这个索引！
🧰 四、实战原则 3：避免冗余索引，合并重复字段
比如：
- 有索引
(a,b) - 又有索引
(a)
这时候可以只保留 (a,b)，因为它包含了 (a) 的查询能力。
🚦 五、实战原则 4：适当利用覆盖索引
覆盖索引指的是查询需要的字段都在索引里，MySQL 无需回表。
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三';
这时查询很快，因为索引本身就是数据。
⚠️ 六、实战原则 5：避开会导致索引失效的写法
- 避免字段上用函数（如
DATE(col)） - 避免在索引字段上进行运算（如
col + 1） - 避免
LIKE '%abc'，尽量LIKE 'abc%' - 避免隐式类型转换
🛠 七、实战示例
假设你有个订单表：
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
product_id INT,
status VARCHAR(20),
created_at DATETIME,
INDEX idx_user_status (user_id, status),
INDEX idx_created_at (created_at)
);
你经常执行：
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
这个查询完美利用 (user_id, status) 联合索引。
但是你执行：
SELECT * FROM orders WHERE status = 'paid';
则无法用联合索引，只能用索引中的 status 单列索引（没有建单列索引的话就全表扫）。
🎯 八、总结
| 设计原则 | 注意点 |
|---|---|
| 明确业务查询场景 | 聚焦常用查询字段 |
| 最左前缀原则 | 联合索引字段顺序不能乱 |
| 合理合并索引 | 避免冗余 |
| 利用覆盖索引 | 提高查询速度，减少回表 |
| 避免索引失效写法 | 函数、运算、隐式转换、左模糊查询慎用 |
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
