一、面试问答清单（按考点优先级排序）

索引基础核心考点

1. Q：什么是索引？底层数据结构是什么？
   A：索引是帮助MySQL高效获取数据的排好序的数据结构。InnoDB默认使用B+树，特点是：所有数据都在叶子节点，非叶子节点只存索引键+指针；叶子节点通过双向链表相连，天然支持范围查询、排序和分组；树高通常为3-4层，可存储千万级数据。

2. Q：B+树和B树的区别？
   A：① B树非叶子节点也存数据，B+树非叶子节点只存索引键；② B+树叶子节点通过双向链表相连，B树没有；③ B+树查询更稳定（所有查询都要到叶子节点）；④ B+树更适合范围查询。

3. Q：聚簇索引和非聚簇索引的区别？
   A：① 聚簇索引：InnoDB独有，每张表有且只有一个，叶子节点直接存储整行完整数据，数据物理存储顺序与索引逻辑顺序一致；② 非聚簇索引：叶子节点只存储主键值，查询需要回表操作。

4. Q：为什么InnoDB表必须有主键，并且推荐使用自增主键？
   A：① InnoDB通过主键聚簇索引组织数据，如果没有主键，会自动生成隐藏自增列；② 自增主键是连续的，插入数据时是顺序写入，不会产生页分裂；③ 自增主键占用空间小，二级索引的叶子节点存储主键值，能节省大量空间。

5. Q：什么是最左前缀原则？
   A：联合索引从左到右匹配，遇到范围查询（>、<、BETWEEN）就停止匹配。例如联合索引(a,b,c)，查询条件a=1 AND b>2 AND c=3，只能用到a和b两列。

6. Q：什么是覆盖索引？有什么优势？
   A：查询的所有字段都在索引中，无需回表。优势：① 避免回表操作，减少IO次数；② 索引远小于数据行，能显著提高查询性能。

Explain执行计划核心考点

1. Q：Explain执行计划中最重要的字段有哪些？
   A：id、select_type、type、key、key_len、rows、Extra。

2. Q：type字段的各个级别及性能排序？
   A：性能从优到劣：system > const > eq_ref > ref > range > index > ALL。优化铁律：至少要达到range级别，最好能达到ref级别。

3. Q：key_len字段有什么作用？如何计算？
   A：用于判断联合索引实际使用了多少列。计算规则：整型TINYINT(1)、SMALLINT(2)、INT(4)、BIGINT(8)；CHAR(n)=n字节，VARCHAR(n)=n+2字节；允许NULL额外+1字节。

4. Q：Extra字段中哪些信息需要特别关注？
   A：✅ 好的：Using index（覆盖索引）、Select tables optimized away；❌ 坏的：Using filesort（文件排序）、Using temporary（临时表）。

慢SQL优化核心考点

1. Q：慢SQL优化的完整流程是什么？
   A：① 发现：开启慢查询日志，使用mysqldumpslow或pt-query-digest分析；② 分析：使用EXPLAIN查看执行计划；③ 优化：索引优化、SQL重构、表结构优化、系统级优化；④ 验证：对比优化前后的执行计划和性能。

2. Q：索引失效的常见场景有哪些？
   A：① 违反最左前缀原则；② 索引列上使用函数或表达式；③ 隐式类型转换；④ 模糊查询以%开头；⑤ 使用OR连接非索引列；⑥ 负向查询（NOT IN、!=）；⑦ 优化器认为全表扫描更快。

3. Q：深分页问题如何优化？
   A：① 游标分页：SELECT * FROM table WHERE id > last_id LIMIT 20；② 覆盖索引+延迟关联：SELECT t.* FROM table t JOIN (SELECT id FROM table LIMIT 10000, 20) tmp ON t.id = tmp.id。

4. Q：JOIN查询的优化原则是什么？
   A：① 小表驱动大表；② 确保JOIN字段有索引；③ 尽量减少JOIN的表数量。

5. Q：如何优化GROUP BY和ORDER BY？
   A：让索引的顺序与GROUP BY或ORDER BY的顺序一致，避免产生临时表和文件排序。

高级技巧考点

1. Q：什么是函数索引？适用于什么场景？
   A：MySQL 8.0+支持为函数表达式创建索引。适用于需要在索引列上使用函数的场景，如YEAR(create_time)。

2. Q：什么是索引合并？为什么通常不推荐？
   A：当查询条件涉及多个单列索引时，MySQL可能会合并这些索引的结果。但索引合并需要多次扫描索引并合并结果，效率通常不如一个覆盖所有条件的联合索引。

3. Q：优化的优先级是什么？
   A：SQL与索引 > 表结构 > 配置参数 > 硬件与架构。

二、电商订单查询完整优化实战案例

场景背景

某电商平台订单表orders有500万条数据，用户在订单中心查询"我的订单"时，页面加载需要3-5秒，严重影响用户体验。

步骤1：发现慢SQL

通过慢查询日志发现以下SQL：

SELECT * FROM orders 
WHERE user_id = 123456 AND order_status IN (1, 2, 3) 
ORDER BY create_time DESC 
LIMIT 0, 20;

执行时间：2.8秒，扫描行数：约120万行。

步骤2：分析执行计划

EXPLAIN SELECT * FROM orders 
WHERE user_id = 123456 AND order_status IN (1, 2, 3) 
ORDER BY create_time DESC 
LIMIT 0, 20;

执行计划结果：

问题诊断：

1. type=ALL：全表扫描，没有使用任何索引
2. Extra=Using filesort：无法利用索引排序，使用了文件排序
3. rows=5000000：扫描了整个表的所有数据

步骤3：第一次优化 - 添加基础索引

为查询条件涉及的字段添加索引：

-- 为user_id添加普通索引
CREATE INDEX idx_user_id ON orders(user_id);

再次执行EXPLAIN：

优化效果：

• 执行时间：0.6秒（提升79%）
• 扫描行数：12万行（减少97.6%）

剩余问题：

• 仍然有Using filesort，需要对12万行数据进行排序
• 当用户翻到第100页（LIMIT 2000, 20）时，执行时间又回到2秒以上

步骤4：第二次优化 - 创建联合索引

遵循"等值列在前，范围列在后"原则，创建覆盖查询条件和排序的联合索引：

-- 删除旧索引
DROP INDEX idx_user_id ON orders;

-- 创建联合索引：等值列user_id在前，然后是排序字段create_time
CREATE INDEX idx_user_id_create_time ON orders(user_id, create_time DESC);

再次执行EXPLAIN：

优化效果：

• 执行时间：0.08秒（再提升87%）
• Using filesort消失了，因为索引本身就是按create_time排序的
• 翻到第100页（LIMIT 2000, 20）执行时间：0.1秒

剩余问题：

• 查询使用了SELECT *，需要回表获取整行数据
• 当订单量继续增长到1000万时，深分页问题会再次出现

步骤5：第三次优化 - 使用覆盖索引

将查询需要的字段都包含在索引中，避免回表：

-- 删除旧索引
DROP INDEX idx_user_id_create_time ON orders;

-- 创建覆盖索引：包含查询条件、排序和需要返回的字段
CREATE INDEX idx_user_id_create_time_cover ON orders(
    user_id, create_time DESC, 
    order_id, order_status, total_amount, pay_time
);

修改SQL，只查询需要的字段：

SELECT order_id, order_status, total_amount, pay_time, create_time 
FROM orders 
WHERE user_id = 123456 AND order_status IN (1, 2, 3) 
ORDER BY create_time DESC 
LIMIT 0, 20;

再次执行EXPLAIN：

优化效果：

• 执行时间：0.01秒（再提升87.5%）
• Extra=Using index：使用了覆盖索引，无需回表
• 翻到第1000页（LIMIT 20000, 20）执行时间：0.02秒

步骤6：第四次优化 - 解决深分页问题

当用户翻到第10000页（LIMIT 200000, 20）时，即使使用覆盖索引，也需要扫描200020行数据。使用游标分页彻底解决深分页问题：

前端改造：每次请求带上上一页最后一条记录的create_time和order_id

-- 第一页
SELECT order_id, order_status, total_amount, pay_time, create_time 
FROM orders 
WHERE user_id = 123456 AND order_status IN (1, 2, 3) 
ORDER BY create_time DESC, order_id DESC 
LIMIT 20;

-- 第N页（last_create_time是上一页最后一条记录的create_time，last_order_id是上一页最后一条记录的order_id）
SELECT order_id, order_status, total_amount, pay_time, create_time 
FROM orders 
WHERE user_id = 123456 
  AND order_status IN (1, 2, 3) 
  AND create_time <= '2024-05-01 12:00:00' 
  AND (create_time < '2024-05-01 12:00:00' OR order_id < 123456789)
ORDER BY create_time DESC, order_id DESC 
LIMIT 20;

最终效果：

• 无论翻到多少页，执行时间都稳定在0.01秒以内
• 系统可以轻松支持千万级订单量的查询

优化总结

总性能提升：280倍

三、多表联查订单详情完整优化实战案例

场景背景

电商平台订单详情页需要展示订单基本信息、商品明细、用户信息和收货地址，涉及4张表关联查询：

• orders：订单主表，500万行数据
• order_items：订单明细表，2000万行数据
• products：商品表，10万行数据
• users：用户表，100万行数据

步骤1：发现慢SQL

订单详情页加载时间超过1.5秒，通过慢查询日志定位到以下SQL：

SELECT * 
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN users u ON o.user_id = u.user_id
WHERE o.order_id = 123456789;

执行时间：1.2秒，扫描行数：约320万行。

步骤2：分析执行计划

EXPLAIN SELECT * 
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN users u ON o.user_id = u.user_id
WHERE o.order_id = 123456789;

执行计划结果（关键部分）：

问题诊断：

1. order_items、products、users表全表扫描（type=ALL）
2. 没有为JOIN字段创建索引
3. 使用SELECT *查询了大量不需要的字段
4. 大表order_items作为被驱动表，导致性能极差

步骤3：第一次优化 - 为JOIN字段添加索引

这是多表联查优化的基础，所有JOIN字段必须有索引：

-- 订单明细表的order_id和product_id添加索引
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 商品表的主键已经是product_id，无需额外添加
-- 用户表的主键已经是user_id，无需额外添加

再次执行EXPLAIN：

优化效果：

• 执行时间：0.08秒（提升93%）
• 扫描行数：从320万行减少到6行
• 所有表都使用了索引，没有全表扫描

剩余问题：

• 使用SELECT *查询了大量不需要的字段，增加了IO和网络传输
• 订单明细表需要回表获取整行数据

步骤4：第二次优化 - 使用覆盖索引，避免SELECT *

只查询页面需要的字段，并为订单明细表创建覆盖索引：

-- 删除旧索引
DROP INDEX idx_order_items_order_id ON order_items;

-- 创建覆盖索引：包含JOIN字段和需要返回的字段
CREATE INDEX idx_order_items_order_id_cover ON order_items(
    order_id, product_id, quantity, unit_price, total_price
);

修改SQL，只查询需要的字段：

SELECT 
    o.order_id, o.order_status, o.total_amount, o.create_time, o.pay_time,
    oi.product_id, oi.quantity, oi.unit_price, oi.total_price,
    p.product_name, p.product_image,
    u.username, u.phone, u.address
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN users u ON o.user_id = u.user_id
WHERE o.order_id = 123456789;

再次执行EXPLAIN：

优化效果：

• 执行时间：0.01秒（再提升87.5%）
• Extra=Using index：订单明细表使用了覆盖索引，无需回表
• 数据传输量减少了90%以上

步骤5：第三次优化 - 拆分大查询为多个小查询

虽然现在性能已经很好，但当系统并发量很高时，多表联查仍然会占用较多数据库连接资源。我们可以将一个大查询拆分为多个小查询，利用应用层缓存进一步提升性能：

-- 1. 查询订单基本信息（可缓存）
SELECT order_id, order_status, total_amount, create_time, pay_time, user_id
FROM orders WHERE order_id = 123456789;

-- 2. 查询订单明细（可缓存）
SELECT product_id, quantity, unit_price, total_price
FROM order_items WHERE order_id = 123456789;

-- 3. 查询商品信息（可缓存，缓存时间更长）
SELECT product_name, product_image
FROM products WHERE product_id IN (1001, 1002, 1003);

-- 4. 查询用户信息（可缓存）
SELECT username, phone, address
FROM users WHERE user_id = 123456;

最终效果：

• 执行时间：0.005秒（再提升50%）
• 每个查询都非常简单，易于优化和缓存
• 数据库连接占用时间更短，系统并发能力提升3-5倍
• 可以针对不同数据设置不同的缓存策略，进一步减轻数据库压力

多表联查优化核心原则总结

1. 小表驱动大表：让数据量小的表作为驱动表，减少循环次数
2. 所有JOIN字段必须有索引：这是多表联查优化的基础
3. 优先使用覆盖索引：避免回表操作
4. 避免SELECT *：只查询需要的字段
5. 尽量减少JOIN表数量：超过3张表的JOIN建议拆分
6. 拆分大查询为小查询：利用应用层缓存提升性能

四、MySQL索引与慢SQL优化可打印速查表

📌 索引基础速查

索引设计黄金法则：

• 等值列在前，范围列在后
• 覆盖索引优先
• 单表索引不超过5个
• 索引字段尽量小

📌 Explain执行计划核心字段速查

1. type字段（性能从优到劣）

system > const > eq_ref > ref > range > index > ALL

• ✅ 理想：const、eq_ref、ref
• ⚠️ 合格：range
• ❌ 必须优化：index、ALL

2. 关键字段含义

📌 索引失效常见场景速查

❌ 违反最左前缀原则
❌ 索引列上使用函数/表达式
❌ 隐式类型转换（字符串不加引号）
❌ 模糊查询以%开头
❌ 使用OR连接非索引列
❌ 负向查询（NOT IN、!=、<>）
❌ 优化器认为全表扫描更快

📌 慢SQL优化全流程速查

1. 发现：开启慢查询日志（long_query_time=1）
2. 分析：使用EXPLAIN查看执行计划
3. 优化：
   • 优先添加合适的索引
   • 重构SQL语句（避免SELECT *、优化子查询）
   • 优化表结构（选择合适的数据类型）
   • 系统级优化（配置参数、读写分离）
4. 验证：对比优化前后的执行计划和性能

📌 常见优化技巧速查

深分页优化

-- 游标分页（推荐）
SELECT * FROM table WHERE id > last_id LIMIT 20;

-- 覆盖索引+延迟关联
SELECT t.* FROM table t 
JOIN (SELECT id FROM table LIMIT 10000, 20) tmp 
ON t.id = tmp.id;

JOIN优化

• 小表驱动大表
• JOIN字段必须有索引
• 尽量减少JOIN表数量

排序/分组优化

• 让索引顺序与排序/分组顺序一致
• 避免对大结果集进行排序/分组

📌 优化优先级

SQL与索引 > 表结构 > 配置参数 > 硬件与架构