← 返回首页 ❓ 面试题库
📑 本篇章节导航 (共7章,点击直达)
一、MySQL架构二、索引原理三、SQL调优四、事务与锁五、分库分表六、面试高频综合题面试速记口诀

Q135 SQL与数据库调优深度实战

20K Java+AI 岗位高频考点 — SQL调优是后端工程师的核心技能,面试必考索引原理、执行计划、慢SQL优化、事务隔离级别。覆盖MySQL架构、索引优化、SQL调优、分库分表、数据迁移。

一、MySQL架构

1. MySQL整体架构?


┌──────────────────────────────────┐
│         客户端连接层               │  连接管理、认证、线程池
├──────────────────────────────────┤
│         Server层                  │
│  ┌──────────────────────────┐    │
│  │ 解析器(Parser)            │    │  词法/语法分析
│  │ 优化器(Optimizer)         │    │  选择执行计划
│  │ 执行器(Executor)          │    │  执行SQL
│  └──────────────────────────┘    │
│  查询缓存(8.0已移除)              │
├──────────────────────────────────┤
│         存储引擎层                │
│  InnoDB(默认) | MyISAM | Memory  │
└──────────────────────────────────┘

InnoDB特性:
- 事务支持(ACID)
- 行级锁
- MVCC多版本并发控制
- 聚簇索引
- 外键支持
- 崩溃恢复(redo/undo log)

MyISAM(已淘汰):
- 表级锁
- 不支持事务
- 全文索引

2. InnoDB存储结构?


InnoDB逻辑结构:
表空间(Tablespace) → 段(Segment) → 区(Extent) → 页(Page) → 行(Row)

页(Page):16KB(默认)
- 数据页
- 索引页
- Undo页
- 系统页

行格式:
Compact: 变长字段长度列表 + NULL列表 + 记录头 + 列数据
Dynamic: 类似Compact,长字段存溢出页

Buffer Pool:
- 缓存数据页和索引页
- 默认128MB(生产建议70-80%物理内存)
- LRU变种:热数据前5/8,冷数据后3/8
- innodb_buffer_pool_size = 8G

Redo Log:
- WAL(Write-Ahead Logging):先写日志再写数据
- 环形写入,固定大小(默认2个文件各1GB)
- innodb_log_file_size = 1G
- 保证崩溃恢复

Undo Log:
- 事务回滚
- MVCC读视图

二、索引原理

3. B+树索引?


为什么用B+树而不是B树/红黑树/Hash?

B+树优势:
1. 磁盘友好:节点大小=页大小(16KB),一次IO读一个节点
2. 范围查询高效:叶子节点链表连接
3. 树高度低:3层B+树可存2000万行(假设每页100个指针)
   第1层:1页 = 100指针
   第2层:100页 = 10000指针
   第3层:10000页 = 1000000指针(数据)

聚簇索引(Clustered Index):
- 主键索引,叶子节点存完整行数据
- 一张表只有一个聚簇索引
- 数据按主键顺序存储

二级索引(Secondary Index):
- 非主键索引,叶子节点存主键值
- 查询需要回表(Bookmark Lookup)
  SELECT * FROM users WHERE name = '张三'
  → name索引找主键ID → 回聚簇索引找完整行

覆盖索引(Covering Index):
- 索引包含查询的所有列,无需回表
  SELECT id, name FROM users WHERE name = '张三'
  → name索引已包含id和name,直接返回

4. 索引优化策略?


最左前缀原则:
INDEX(a, b, c) 可用于:
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
不可用于:
WHERE b = 2(跳过了a)
WHERE c = 3(跳过了a和b)

索引失效场景:
1. 对索引列使用函数:WHERE YEAR(created_at) = 2024
   → 改为 WHERE created_at >= '2024-01-01'
2. 隐式类型转换:WHERE varchar_col = 123
   → 改为 WHERE varchar_col = '123'
3. LIKE '%abc'(前缀模糊)
4. OR条件(部分列无索引)
5. NOT IN / NOT EXISTS
6. 联合索引跳过前缀列

索引设计原则:
1. 选择性高的列优先(区分度大)
2. 联合索引把高频查询列放前面
3. 避免冗余索引 (a,b) 和 (a)
4. 单表索引不超过5个
5. 频繁更新的列少建索引
6. 小表全表扫描可能更快

5. EXPLAIN执行计划?


EXPLAIN SELECT * FROM orders WHERE user_id = 123;

关键列:
type(访问类型,从好到差):
system > const > eq_ref > ref > range > index > ALL
- const:主键/唯一索引等值查询
- eq_ref:关联查询主键
- ref:非唯一索引等值查询
- range:范围扫描(BETWEEN/>/</IN)
- index:全索引扫描
- ALL:全表扫描(必须优化)

key:实际使用的索引
rows:预估扫描行数
filtered:过滤比例(越高越好)
Extra:
- Using index:覆盖索引(好)
- Using where:在Server层过滤
- Using temporary:使用临时表(需优化)
- Using filesort:额外排序(需优化)
- Using index condition:索引下推(ICP)

优化示例:
-- Bad: Using filesort
SELECT * FROM orders ORDER BY created_at LIMIT 10;
-- 无索引,需要全表扫描+排序

-- Good: Using index
ALTER TABLE orders ADD INDEX idx_created(created_at);
SELECT * FROM orders ORDER BY created_at LIMIT 10;

三、SQL调优

6. 慢查询优化?


-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;

-- 分析慢查询
mysqldumpslow -s t -t 10 slow.log  # 按时间Top10

-- 优化步骤:
-- 1. EXPLAIN查看执行计划
-- 2. 检查type是否ALL
-- 3. 检查Extra是否有filesort/temporary
-- 4. 添加合适的索引
-- 5. 改写SQL

7. SQL优化技巧?


-- 1. 避免SELECT *
SELECT id, name, email FROM users WHERE id = 1;

-- 2. 小表驱动大表
-- Bad
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);
-- Good(users小表在前)
SELECT o.* FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 1;

-- 3. EXISTS替代IN(大子查询)
-- Bad
SELECT * FROM orders WHERE user_id IN (SELECT id FROM big_table);
-- Good
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM big_table b WHERE b.id = o.user_id);

-- 4. 分页优化
-- Bad: 深分页慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- Good: 游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 5. 批量操作
-- Bad: 循环单条INSERT
-- Good: 批量INSERT
INSERT INTO orders (user_id, amount) VALUES (1, 100), (2, 200), (3, 300);

-- 6. 避免大事务
-- Bad: 一条SQL更新100万行
UPDATE orders SET status = 'CANCELLED' WHERE created_at < '2024-01-01';
-- Good: 分批更新
UPDATE orders SET status = 'CANCELLED' WHERE created_at < '2024-01-01' LIMIT 1000;

-- 7. COUNT优化
SELECT COUNT(*) FROM orders;  -- 不用COUNT(1)或COUNT(列)
-- 近似值(不需要精确)
EXPLAIN SELECT COUNT(*) FROM orders; -- 看rows估算

8. 分页查询优化?


问题:深分页 LIMIT 1000000, 10 需要扫描1000010行

方案一:游标分页(推荐)
-- 第一页
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 10;
-- 下一页(记住最后一条的id)
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

方案二:延迟关联
-- 先查主键,再关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY created_at LIMIT 1000000, 10) t
ON o.id = t.id;

方案三:覆盖索引
-- 确保排序字段有索引
ALTER TABLE orders ADD INDEX idx_created(created_at);
SELECT * FROM orders ORDER BY created_at LIMIT 1000000, 10;

方案四:业务限制
-- 不提供跳页,只有上一页/下一页
-- 限制最大页数(如最多看100页)
-- 搜索引擎式分页(总数不精确)

四、事务与锁

9. 事务隔离级别?


| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 实现 |
|---------|------|-----------|------|------|
| READ UNCOMMITTED | 有 | 有 | 有 | 无锁 |
| READ COMMITTED | 无 | 有 | 有 | MVCC快照 |
| REPEATABLE READ | 无 | 无 | 部分有 | MVCC快照+间隙锁 |
| SERIALIZABLE | 无 | 无 | 无 | 表锁 |

MySQL默认:REPEATABLE READ

MVCC(Multi-Version Concurrency Control):
- 每行有隐藏列:trx_id(事务ID), roll_pointer(指向undo log)
- Read View:记录当前活跃事务列表
- RC:每次SELECT创建新Read View
- RR:第一次SELECT创建,后续复用

MVCC读规则:
- trx_id < min_active_id → 可见(事务已提交)
- trx_id > max_id → 不可见(未来事务)
- trx_id在活跃列表中 → 不可见(未提交)
- trx_id不在活跃列表中 → 可见(已提交)
- 不可见 → 通过roll_pointer找undo log历史版本

10. InnoDB锁机制?


锁类型:
共享锁(S锁/读锁):SELECT ... LOCK IN SHARE MODE
排他锁(X锁/写锁):SELECT ... FOR UPDATE
意向锁(IS/IX):表级锁,表示打算加行锁

行锁:
- Record Lock:锁定索引记录
- Gap Lock:锁定索引间隙(防止插入)
- Next-Key Lock:Record + Gap(左开右闭)

加锁规则(RR级别):
1. 默认加Next-Key Lock
2. 等值查询唯一索引 → 退化为Record Lock
3. 等值查询到末尾 → 退化为Gap Lock
4. 范围查询 → 所有扫描过的间隙加锁

死锁检测:
SHOW ENGINE INNODB STATUS; → 查看最近死锁

避免死锁:
1. 按固定顺序访问表和行
2. 保持事务简短
3. 使用低隔离级别
4. 添加合适索引(避免锁升级)

五、分库分表

11. 分库分表策略?


垂直拆分:
- 垂直分库:按业务拆(订单库、用户库、商品库)
- 垂直分表:按列拆(热数据+冷数据分离)

水平拆分:
- 水平分库:按规则分到不同数据库实例
- 水平分表:按规则分到同库的不同表

分片键(Sharding Key)选择:
- user_id(最常见)
- order_id
- 日期

分片策略:
1. Hash取模:user_id % 4 → 0,1,2,3号表
   优点:数据均匀
   缺点:扩容需要迁移数据

2. Range范围:按日期/ID范围
   优点:扩容方便
   缺点:热点问题(最新数据集中)

3. 一致性Hash:
   扩容只影响相邻节点

分库分表中间件:
- ShardingSphere(Apache,推荐)
- MyCat(老牌,维护少)

ShardingSphere配置:
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.rules.sharding.tables.orders.actual-data-nodes=ds0.orders_$->{0..1},ds1.orders_$->{0..1}
spring.shardingsphere.rules.sharding.tables.orders.database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.tables.orders.database-strategy.standard.precise-algorithm-class-name=com.example.UserIdShardingAlgorithm

12. 分布式ID方案?


分库分表后不能用自增ID

方案一:雪花算法(推荐)
- 1位符号 + 41位时间 + 10位机器 + 12位序列
- 每毫秒4096个ID
- 趋势递增,可排序

方案二:号段模式(Leaf)
- 数据库预分配号段
- 一次取1000个,本地消耗
- 减少数据库压力

方案三:UUID
- 128位,无序
- 不适合做主键(索引效率差)

方案四:Redis INCR
- 简单,但依赖Redis
- 持久化风险

推荐:雪花算法
MyBatis-Plus @TableId(type = IdType.ASSIGN_ID)默认雪花

六、面试高频综合题

13. 一条SQL的执行流程?


SELECT * FROM users WHERE id = 1;

1. 客户端发送SQL到MySQL
2. 连接器:验证用户名密码、权限
3. 解析器:词法分析→语法分析→AST
4. 优化器:选择执行计划(选索引、决定JOIN顺序)
5. 执行器:
   a. 检查权限
   b. 调用存储引擎接口
   c. InnoDB:B+树查找id=1
   d. 聚簇索引直接返回行数据
   e. 返回结果集

UPDATE users SET name = '张三' WHERE id = 1;

1-4. 同上
5. 执行器:
   a. 找到id=1的行(同SELECT)
   b. 加X锁(排他锁)
   c. 写Undo Log(旧值)
   d. 更新内存中的数据页
   e. 写Redo Log(prepare状态)
   f. 写Binlog
   g. Redo Log改为commit状态(两阶段提交)
   h. 返回结果

14. 如何设计高性能表结构?


1. 字段类型选择:
   - 整数:TINYINT/INT/BIGINT(不用字符串存数字)
   - 字符串:VARCHAR(不用TEXT存短文本)
   - 时间:DATETIME/TIMESTAMP(不用VARCHAR存时间)
   - 金额:DECIMAL(18,2)(不用FLOAT/DOUBLE)
   - 状态:TINYINT + 枚举(不用VARCHAR)
   - 布尔:TINYINT(1)

2. 主键设计:
   - 自增BIGINT(推荐,索引紧凑)
   - 雪花算法BIGINT(分库分表场景)
   - 不用UUID(无序,索引碎片)
   - 不用联合主键(二级索引太大)

3. 索引设计:
   - 主键索引:必选
   - 唯一索引:业务唯一约束
   - 联合索引:(status, created_at) 高频查询
   - 不超过5个索引

4. 反范式化(适当冗余):
   订单表冗余用户名(避免JOIN)
   但要考虑数据一致性

15. 数据迁移方案?


大表数据迁移(不停机):

1. 双写方案:
   a. 新增双写开关(默认关)
   b. 开启双写(新老库同时写)
   c. 历史数据迁移(分批INSERT)
   d. 数据校验(对比新老库数据)
   e. 切换读流量到新库
   f. 停止写老库

2. Canal监听Binlog:
   a. Canal监听老库Binlog
   b. 增量同步到新库
   c. 历史数据单独迁移
   d. 校验一致后切换

3. 中间件方案:
   ShardingSphere影子库功能
   流量先写新老库,读老库
   确认无误后切读新库

面试速记口诀

索引两类型:聚簇(主键存行数据)、二级(存主键需回表)

>

优化六招:避免SELECT*/小表驱动大表/游标分页/批量操作/分批更新/加合适索引

>

事务四隔离:读未提交→读已提交→可重复读(MySQL默认)→串行化

>

分库分表三步:选分片键→选策略(Hash/Range)→选中间件(ShardingSphere)