← 返回场景决策
1. 索引决策 — 什么时候建 / 什么时候反而有害
💭 类比：索引 = 字典的部首检字表
有索引 = 按部首查字典，直接翻到那一页 → 快
没索引 = 从第一页翻到最后一页 → 全表扫描，慢
索引太多 = 一本字典附了10种检字表，字典变得又厚又重 → 占空间、改字时每个表都得更新
✅ 什么时候建索引
1️⃣ WHERE 条件列：SELECT * FROM user WHERE name = '张三' → name建索引
2️⃣ JOIN 关联列：SELECT * FROM a JOIN b ON a.id = b.a_id → b.a_id建索引
3️⃣ ORDER BY 排序列：SELECT * FROM user ORDER BY age → age建索引（避免filesort）
4️⃣ GROUP BY 分组列：SELECT dept, COUNT(*) FROM user GROUP BY dept → dept建索引
5️⃣ 区分度高的列：手机号/邮箱（区分度≈1）比性别（区分度≈0.5）更适合建索引
6️⃣ 覆盖索引：查询的所有列都在索引里，不需要回表
❌ 什么时候不建 / 建了反而有害
1️⃣ 区分度极低的列：性别（只有男/女）、状态（0/1）→ 索引没用了，MySQL可能还是全表扫
2️⃣ 频繁更新的列：每次UPDATE都要维护索引B+树 → 写入变慢
3️⃣ 数据量极小的表：几百行的表，全表扫比走索引还快
4️⃣ 用不到的索引：建了但从没查过 → 白占空间白拖慢写入
5️⃣ 太多单列索引：不如组合成一个联合索引（最左前缀原则）
联合索引最左前缀原则
-- 建联合索引：idx_abc (a, b, c)
-- ✅ 能走索引的查询：
WHERE a = 1 -- 用到 a
WHERE a = 1 AND b = 2 -- 用到 a, b
WHERE a = 1 AND b = 2 AND c = 3 -- 用到 a, b, c
WHERE a = 1 AND c = 3 -- 只用到 a（c跳过了b）
-- ❌ 不能走索引的查询：
WHERE b = 2 -- 没有a，最左前缀断了
WHERE b = 2 AND c = 3 -- 同上，a不在前面
WHERE c = 3 -- 同上
面试金句
最左前缀原则（Leftmost Prefix Rule）源于 B+ 树索引的有序性：联合索引 (a,b,c) 按 a→b→c 排序存储，a 确定后 b 才有序，b 确定后 c 才有序。跳过 a 直接查 b 相当于在一本按姓→名排序的电话簿里只按名查——只能逐页翻。MySQL 5.6+ 的 Index Condition Pushdown (ICP) 优化了 a=1 AND c=3 的情况：走到索引后下推 c 条件过滤，减少回表次数。
索引失效的 6 种常见场景
| # | 场景 | ❌ 索引失效的写法 | ✅ 正确写法 |
|---|
| 1 | 对索引列用函数 | WHERE YEAR(create_time) = 2024 | WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' |
| 2 | 隐式类型转换 | WHERE phone = 13800138000（varchar列用数字查） | WHERE phone = '13800138000' |
| 3 | LIKE 左模糊 | WHERE name LIKE '%张' | WHERE name LIKE '张%'（右模糊可以） |
| 4 | OR 连接非索引列 | WHERE a=1 OR b=2（b无索引） | 给b也加索引 / 用UNION |
| 5 | 不等于 | WHERE status != 0 | 改用IN / 业务上避免 |
| 6 | IS NOT NULL | WHERE col IS NOT NULL | IS NULL 可以走，IS NOT NULL 优化器可能放弃 |
⚠️ 索引的代价
① 每个索引占独立B+树 → 磁盘空间增加
② INSERT/UPDATE/DELETE 要维护所有索引 → 写入变慢
③ 索引越多，优化器选择越慢 → 可能选错索引
④ 建议：单表索引不超过5个，联合索引优先于多个单列索引
2. JOIN 选型 — 三种 JOIN 什么时候用哪种
💭 类比
INNER JOIN = 取交集：两张名单只有都有的名字才留下
LEFT JOIN = 以左为主：左边名单全留，右边没匹配的填空
RIGHT JOIN = 以右为主：右边名单全留，左边没匹配的填空
三种 JOIN 对比
| JOIN类型 | 结果 | ✅ 什么时候用 | ❌ 什么时候不用 |
|---|
| INNER JOIN |
两表都有的才返回 |
查询两表都存在的数据、不需要空值、关联关系强 |
需要保留某一边的所有数据 |
| LEFT JOIN |
左表全保留，右表没匹配填NULL |
以左表为主、需要保留所有左表记录、右表数据可选 |
不需要左表的全部数据（用INNER更高效） |
| RIGHT JOIN |
右表全保留，左表没匹配填NULL |
以右表为主（少用，一般换LEFT JOIN） |
习惯上优先用LEFT JOIN，RIGHT JOIN可读性差 |
数据演示
-- 学生表
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(20)
);
INSERT INTO student VALUES (1,'张三'),(2,'李四'),(3,'王五');
-- 选课表
CREATE TABLE course (
id INT PRIMARY KEY,
student_id INT,
course_name VARCHAR(20)
);
INSERT INTO course VALUES (1,1,'数学'),(2,2,'英语'),(3,99,'物理');
面试金句
Nested Loop Join 是 MySQL 执行 JOIN 的底层算法：驱动表（小表）每行去被驱动表（大表）找匹配——如果被驱动表的 JOIN 列有索引（如 student_id），走 Index Nested Loop（B+ 树快速定位），复杂度 O(N*logM)；如果无索引则退化为 Block Nested Loop（双层循环全表扫），复杂度 O(N*M)。因此 JOIN 列必须建索引。
💡 王五没选课，INNER JOIN 不显示，LEFT JOIN 保留并填 NULL
⚠️ JOIN 性能陷阱
① 永远在小表上做驱动表（LEFT JOIN 左边放小表）
② JOIN 列必须有索引！否则是 Nested Loop 全表扫
③ 避免超过3张表JOIN → 考虑拆查询或在应用层组装
④ LEFT JOIN + WHERE 右表条件 → 等同于 INNER JOIN（优化器可能自动转）
3. 事务隔离级别 — 四个等级实战怎么选
💭 类比
READ UNCOMMITTED = 看别人草稿纸：考场上偷看别人还没交的答案，可能是错的
READ COMMITTED = 只看交了卷的答案：Oracle默认，别人提交了才能看到
REPEATABLE READ = 你开考时拍的照片为准：MySQL默认，整个考试期间看到的数据一致
SERIALIZABLE = 一个一个考：所有人排队，完全不会冲突，但极慢
四种隔离级别对比
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | ✅ 什么时候用 |
|---|
| READ UNCOMMITTED |
⚠️ 有 | ⚠️ 有 | ⚠️ 有 |
最快 |
几乎不用（除非完全不关心数据一致性） |
| READ COMMITTED |
✅ 无 | ⚠️ 有 | ⚠️ 有 |
较快 |
Oracle默认、追求高并发+可接受不可重复读 |
| REPEATABLE READ |
✅ 无 | ✅ 无 | 基本无* |
中等 |
MySQL默认、大多数场景、需要一致性读取 |
| SERIALIZABLE |
✅ 无 | ✅ 无 | ✅ 无 |
最慢 |
金融核心交易、数据绝对不能出错、并发量低 |
* MySQL InnoDB 的 REPEATABLE READ 通过 MVCC + 间隙锁基本解决幻读，但特定场景（先查后更新）仍可能出现
代码：查看和设置隔离级别
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 结果：REPEATABLE-READ
-- 设置隔离级别（会话级）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置隔离级别（全局）
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
面试金句
SET SESSION 仅影响当前连接，SET GLOBAL 影响所有新连接（不影响已有连接）。MySQL InnoDB 默认 REPEATABLE READ，通过 MVCC（多版本并发控制）+ Undo Log 版本链实现：事务开启时拍一个 ReadView 快照，此后所有 SELECT 都读快照版本。间隙锁（Gap Lock）防止幻读，但也是死锁高发区——降级为 READ COMMITTED 可减少死锁（代价是放弃可重复读）。
⚠️ 什么时候需要降级为 READ COMMITTED？
① 死锁频繁 → RC 没有间隙锁，死锁更少
② 需要实时看到其他事务已提交的数据（如库存实时显示）
③ 但要注意：RC 下每次 SELECT 都生成新 ReadView → 可能不可重复读
4. 分库分表 — 什么时候该做
💭 类比
单库单表 = 所有数据一个表，数据量小时够用
读写分离 = 主库写+从库读：主库处理写入，从库分担查询压力
分表 = 同库拆表：一张大表按规则拆成多张结构相同的表
分库 = 不同库服务不同业务域，物理隔离
决策树
数据量多大？
<500万行
→
优化索引/SQL就够了
500万~5000万
→
读写分离(主从)
>5000万
→
分库分表
决策表
| 场景 | ✅ 用什么 | ❌ 不要过早做 |
|---|
| 慢查询但数据量不大 | 优化索引/SQL/加缓存 | 分库分表（杀鸡用牛刀） |
| 读多写少 | 读写分离 + 缓存 | 分库分表 |
| 单表数据>5000万 | 分表（按时间/ID范围/Hash） | 继续单表 |
| 单库连接数不够 | 分库（不同业务拆不同库） | 继续单库 |
| 需要跨库事务 | 避免分库/用分布式事务 | 随便分库 |
⚠️ 分库分表的代价
① 跨库JOIN → 要在应用层组装数据
② 分布式事务 → 从本地事务变成2PC/TCC，复杂度暴增
③ 全局唯一ID → 不能用自增，要雪花算法
④ 运维复杂 → 数据迁移、扩容、备份都是噩梦
⑤ 能不分就不分！先优化索引、SQL、缓存、读写分离
5. 数据类型选型 — 怎么选最省空间
💭 类比
选数据类型 = 选箱子装东西：小东西用大箱子浪费空间，大东西用小箱子装不下
TINYINT = 邮票盒（1字节）、INT = 鞋盒（4字节）、BIGINT = 行李箱（8字节）
整数类型
| 类型 | 字节 | 无符号范围 | ✅ 什么时候用 |
|---|
| TINYINT | 1 | 0~255 | 状态(0/1/2)、类型枚举 |
| SMALLINT | 2 | 0~65535 | 年龄、数量小范围 |
| INT | 4 | 0~42亿 | 主键、大多数整数场景 |
| BIGINT | 8 | 0~1844亿亿 | 雪花ID、超大数量 |
字符串类型
| 类型 | ✅ 什么时候用 | ❌ 什么时候不用 |
|---|
| CHAR(n) | 固定长度：手机号(11)、MD5(32)、性别(1) | 长度变化大的字符串 |
| VARCHAR(n) | 变长字符串：姓名、地址、标题 | 超长文本（用TEXT） |
| TEXT | 长文本：文章内容、JSON | 需要默认值（TEXT不支持） |
时间类型
| 类型 | 字节 | 范围 | ✅ 什么时候用 |
|---|
| DATETIME | 8 | 1000~9999年 | 业务时间（创建时间/更新时间），不受时区影响 |
| TIMESTAMP | 4 | 1970~2038年 | 系统时间（自动更新），省空间，受时区影响 |
| BIGINT | 8 | 不限 | 存毫秒时间戳，跨语言通用 |
⚠️ 常见坑
① TIMESTAMP 2038年溢出！如果你的业务可能存2038年以后的时间，用DATETIME
② VARCHAR(255) 不是越大越好！MySQL 内存分配和索引前缀都受影响
③ 金额用 DECIMAL(10,2)！不要用 FLOAT/DOUBLE（精度丢失）
④ IP地址用 INT UNSIGNED + INET_ATON() 存储！不要用VARCHAR存IP
-- ✅ 金额：DECIMAL 精确存储
CREATE TABLE `order` (
id BIGINT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL COMMENT '金额，精确到分'
);
-- ❌ 金额用 FLOAT 会丢失精度
SELECT 0.1 + 0.2; -- 结果：0.30000000000000004 💥
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)); -- 结果：0.30 ✅
-- ✅ IP地址存为INT
INSERT INTO login_log (ip) VALUES (INET_ATON('192.168.1.1'));
SELECT INET_NTOA(ip) FROM login_log; -- 192.168.1.1
面试金句
DECIMAL(10,2) 底层将数字转为二进制字符串存储，精确到指定小数位，不产生 IEEE 754 浮点误差（0.1+0.2≠0.3）。金融场景必须用 DECIMAL。INET_ATON/INET_NTOA 将 IPv4 转为 4 字节 INT UNSIGNED 存储，比 VARCHAR(15) 节省 11 字节，且支持高效范围查询（如 BETWEEN INET_ATON('192.168.0.0') AND ...）。
6. 慢查询排查 — 五步法
1.开启慢日志
→
2.EXPLAIN分析
→
3.看type/key
→
4.优化
→
5.验证
Step 1: 开启慢查询日志
-- 查看慢日志状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢日志（超过1秒记录）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
面试金句
慢查询日志（Slow Query Log）是 MySQL 性能诊断的第一道防线：long_query_time 阈值建议生产环境设为 0.5~1s。SET GLOBAL 对新连接生效，已有连接需重连。线上通常配合 pt-query-digest 工具聚合分析慢日志，找出 TOP N 高频慢 SQL。⚠️ 开启慢日志有约 1~3% 的性能开销，但远小于不开导致的生产事故。
Step 2: EXPLAIN 的 type 字段（从好到差）
| type | 含义 | 性能 | ✅ 达标？ |
|---|
| system/const | 单行匹配 | ⭐⭐⭐⭐⭐ | ✅ 最优 |
| eq_ref | 唯一索引关联 | ⭐⭐⭐⭐⭐ | ✅ 最优 |
| ref | 非唯一索引查找 | ⭐⭐⭐⭐ | ✅ 良好 |
| range | 索引范围扫描 | ⭐⭐⭐ | ✅ 可接受 |
| index | 全索引扫描 | ⭐⭐ | ⚠️ 需优化 |
| ALL | 全表扫描 | ⭐ | ❌ 必须优化 |
Step 3: 看重点字段
EXPLAIN SELECT * FROM user WHERE name = '张三';
-- 重点看：
-- type: ALL = 全表扫描 ❌ / ref = 走了索引 ✅
-- key: NULL = 没走索引 ❌ / idx_name = 走了索引 ✅
-- rows: 扫描行数，越小越好
-- Extra: Using filesort = 额外排序 ❌ / Using index = 覆盖索引 ✅
面试金句
EXPLAIN 输出的四列是慢 SQL 优化的核心依据：type（访问类型）从 ALL→index→range→ref→eq_ref→const 性能递增；key 显示实际使用的索引（NULL = 全表扫）；rows 是优化器预估扫描行数；Extra 的 Using index 表示覆盖索引（索引包含了查询所有列，不回表），是最优状态。Using filesort 和 Using temporary 必须优化。
Step 4: 常见优化手段
| 问题 | 优化方式 | 示例 |
|---|
| type=ALL 全表扫描 | 加索引 | WHERE name=? → 给name加索引 |
| Extra=Using filesort | ORDER BY列加索引/联合索引 | ORDER BY age → 给age加索引 |
| Extra=Using temporary | GROUP BY列加索引 | GROUP BY dept → 给dept加索引 |
| 扫描行数过大 | 缩小查询范围/用覆盖索引 | SELECT id,name 不用 SELECT * |
| 索引失效 | 避免函数/隐式转换/左模糊 | 见第1节索引失效6种场景 |
✅ 慢查询排查口诀
① 先开慢日志抓SQL
② EXPLAIN 看type → ALL必须优化
③ 看key → NULL说明没走索引
④ 看rows → 扫描行数是不是远大于返回行数
⑤ 看Extra → Using filesort/Using temporary 要优化
全栈资料库 · 场景决策 · MySQL篇