MySQL 优化实战

老张2026/4/18大约 7 分钟Java面试MySQL索引事务MVCC分库分表慢SQL

MySQL 优化实战

索引原理 B+树、EXPLAIN 分析、慢 SQL 优化、事务/MVCC 深度、分库分表——美团/字节数据库必考模块。

核心原理

B+ 树索引结构

B+ 树非叶子节点只存 key，叶子节点存 key + data（完整行或主键值），叶子节点通过双向链表连接，支持高效范围查询。一棵高度为 3 的 B+ 树可以存储约 2000 万行数据（非叶子节点可存约 1200 个 key，叶子节点每个页存约 16 条数据）。

InnoDB 聚簇索引 vs 二级索引：

-- 聚簇索引（主键索引）：叶子节点存完整行数据
-- 二级索引（普通索引）：叶子节点存 key + 主键值
-- 回表：通过二级索引找到主键，再走聚簇索引查完整行
SELECT * FROM user WHERE name = '张三';
-- 执行：name 索引→主键→聚簇索引（两次 B+树查找）

-- 覆盖索引：查询字段全在索引中，无需回表
SELECT id, name FROM user WHERE name = '张三';  -- idx(name,id)，Extra: Using index

为什么推荐自增主键：

顺序插入，新数据追加在 B+ 树最右侧，无页分裂
UUID 主键随机插入导致频繁页分裂和页合并，写放大严重

MVCC（多版本并发控制）原理

InnoDB 在 REPEATABLE READ 和 READ COMMITTED 隔离级别下使用 MVCC 实现非锁定读：

三个关键组件：

隐藏字段： 每行记录包含 DB_TRX_ID（修改该行的最新事务 ID）和 DB_ROLL_PTR（指向 Undo Log 的回滚指针）
Undo Log 版本链： 每次更新记录都在 Undo Log 中保存旧版本，形成版本链
ReadView： 快照读时创建，记录当前活跃事务 ID 列表，判断哪个版本对当前事务可见

ReadView 可见性判断规则：
- trx_id < min_trx_id（已提交的最小活跃事务）→ 可见
- trx_id > max_trx_id（ReadView 创建后才开始的事务）→ 不可见
- trx_id 在 min 和 max 之间：检查是否在活跃事务列表中
  - 在列表中（未提交）→ 不可见（读 Undo Log 旧版本）
  - 不在列表（已提交）→ 可见

RR 和 RC 的区别： RC 每次快照读都创建新 ReadView（可读到其他事务已提交的最新版本）；RR 在事务第一次快照读时创建 ReadView 并复用（保证整个事务看到一致的数据快照）。

高频面试题

Q: EXPLAIN 各字段如何分析？（字节必考）

EXPLAIN SELECT u.name, o.total FROM user u
    JOIN orders o ON u.id = o.user_id
    WHERE u.city = '北京' AND o.status = 1;
关键字段解读：
字段最优 → 最差说明
type system > const > eq_ref > ref > range > index > ALL ALL = 全表扫描，必须优化
key 非 NULL 实际使用的索引，NULL 表示未用索引
rows 越小越好预估扫描行数（非精确值）
Extra Using index（最好）→ Using where → Using filesort / Using temporary（需优化）
Extra 中的 "Using filesort" 表示无法利用索引排序，需要额外排序操作，性能差：
-- 触发 filesort：ORDER BY 字段不在索引中
SELECT * FROM orders WHERE user_id = 1 ORDER BY amount DESC;
-- 优化：建立 idx(user_id, amount) 联合索引

字段	最优 → 最差	说明
`type`	system > const > eq_ref > ref > range > index > ALL	ALL = 全表扫描，必须优化
`key`	非 NULL	实际使用的索引，NULL 表示未用索引
`rows`	越小越好	预估扫描行数（非精确值）
`Extra`	Using index（最好）→ Using where → Using filesort / Using temporary（需优化）

Q: 索引失效的场景有哪些？如何避免？（阿里 P6 高频）

8 大索引失效场景：

-- 1. 对索引列做函数或运算
WHERE DATE(create_time) = '2024-01-01'  -- 索引失效
WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02'  -- 改为范围查询，走索引

-- 2. 隐式类型转换（最坑！）
-- phone 字段为 varchar，用数字查
WHERE phone = 13812345678  -- MySQL 将 varchar 转 double，索引失效
WHERE phone = '13812345678'  -- 走索引

-- 3. LIKE 左模糊
WHERE name LIKE '%张'  -- 失效
WHERE name LIKE '张%'  -- 走索引（右模糊可以）
-- 如果必须左模糊，考虑全文索引（FULLTEXT）或 ES

-- 4. OR 连接非索引列（整个条件失效）
WHERE id = 1 OR address = '北京'  -- address 无索引，整体走全表扫描
-- 优化：为 address 建索引，或改 UNION ALL

-- 5. 联合索引不符合最左前缀
-- 索引：idx(a, b, c)
WHERE b = 1 AND c = 2  -- 未从 a 开始，失效
WHERE a = 1 AND c = 2  -- a 走索引，c 失效（跳过 b）

-- 6. 范围查询后的列失效
WHERE a = 1 AND b > 5 AND c = 3  -- b 是范围，c 失效

-- 7. NOT IN / NOT EXISTS（大多数情况走全表扫描）
-- 8. 使用 != 或 <>（小数据量可能走索引，大数据量失效）

Q: 如何定位和优化慢 SQL？（美团生产问题必问）

完整排查流程：

Step 1：开启慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;    -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未使用索引也记录
-- 查看日志路径：SHOW VARIABLES LIKE 'slow_query_log_file';

Step 2：分析慢查询工具

# mysqldumpslow：按执行时间排序，找 Top 10 慢 SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# pt-query-digest（更详细）
pt-query-digest /var/lib/mysql/slow.log | head -100

Step 3：EXPLAIN 分析执行计划（见上题）

Step 4：常见优化手段

-- 优化1：大分页改游标分页（LIMIT 100000,10 性能极差）
-- 慢：扫描 100010 行，丢弃前 100000 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

-- 快：游标分页（id 走索引，O(log n)）
SELECT * FROM orders WHERE id > :lastId ORDER BY id ASC LIMIT 10;

-- 优化2：覆盖索引避免回表
SELECT id, name FROM user WHERE status = 1;  -- idx(status, name, id)

-- 优化3：批量操作替代循环单条操作
-- 慢：for 循环 1000 次 INSERT
-- 快：INSERT INTO ... VALUES (...),(...),(...) 批量插入

-- 优化4：JOIN 优化：小表驱动大表，驱动表不超过 1000 行
-- 优化5：用 EXISTS 替代 IN（in 的子查询结果集大时）

Q: 分库分表方案如何选择？ShardingJDBC 如何使用？（阿里/美团架构题）

何时需要分库分表：
单表数据量 > 500 万行（业界经验值）且 SQL 已充分优化
单库 TPS > 1000，数据库成为瓶颈
分片策略：
策略方式优点缺点
取模分片 id % 4 数据均匀扩容需迁移数据
范围分片 id 1-100万 → shard0 扩容简单热点问题
一致性哈希虚拟节点环扩容迁移少实现复杂
雪花 ID 分片按时间/机器位分片分布均匀需要统一 ID 生成
# ShardingSphere-JDBC 分片配置（application.yml）
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0: { url: jdbc:mysql://host0:3306/db }
      ds1: { url: jdbc:mysql://host1:3306/db }
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds${0..1}.orders_${0..3}   # 2库4表
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-inline
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: tbl-inline
        sharding-algorithms:
          db-inline:
            type: INLINE
            props:
              algorithm-expression: ds${user_id % 2}
          tbl-inline:
            type: INLINE
            props:
              algorithm-expression: orders_${order_id % 4}
分库分表后的问题：
跨库 JOIN：拆成多次查询（先查一库，再 IN 查另一库）或在业务层聚合
分布式事务：Seata AT 模式
分页排序：各分片各查 N 条，业务层归并排序（性能差），或引入 ES 搜索
全局唯一 ID：雪花算法（Leaf/美团）、Redis INCR、数据库号段

策略	方式	优点	缺点
取模分片	`id % 4`	数据均匀	扩容需迁移数据
范围分片	id 1-100万 → shard0	扩容简单	热点问题
一致性哈希	虚拟节点环	扩容迁移少	实现复杂
雪花 ID 分片	按时间/机器位分片	分布均匀	需要统一 ID 生成

Q: MySQL 的锁有哪些？行锁的加锁规则？（字节后端 P6 必问）

InnoDB 锁类型：
共享锁（S Lock）： SELECT ... LOCK IN SHARE MODE，允许多个事务同时持有
排他锁（X Lock）： SELECT ... FOR UPDATE / UPDATE / DELETE，独占
意向锁（IS/IX）： 表级锁，说明事务意图在行级加共享/排他锁，避免表锁行锁冲突检查全表
间隙锁（Gap Lock）： 锁定索引记录之间的间隙，防止幻读（RR 级别特有）
临键锁（Next-Key Lock）： 行锁 + 间隙锁的组合，InnoDB 默认的行锁形态
加锁规则（RR 级别）：
-- 精确匹配唯一索引 → 退化为行锁（无间隙锁）
SELECT * FROM user WHERE id = 5 FOR UPDATE;  -- 只锁 id=5 这一行

-- 范围查询 → 临键锁（锁范围 + 右边界）
SELECT * FROM user WHERE id BETWEEN 5 AND 10 FOR UPDATE;
-- 锁定：(4,5], (5,6], ..., (9,10], (10,+∞) -- 包含右边界的 gap

-- 非唯一索引等值查询 → 临键锁（锁记录 + 左右间隙）
SELECT * FROM user WHERE age = 25 FOR UPDATE;
-- 锁 age=25 的记录 + 左右间隙

知识星球深度内容

完整大厂面经实录（字节/阿里/腾讯/美团）、简历 1v1 修改、每周高频题精讲，扫码加入「AI 工程师加速社区」知识星球 👉 立即加入