Q73 - MySQL高级优化与数据库实战
共50道题 | 难度分布:★★★★★平均 | 20K+ Java后端岗位必考
一、MySQL架构与存储引擎(1-8)
MySQL的整体架构?
★★★★★
- 连接层:连接管理、认证、线程池。
- SQL层(Server层):
- 查询缓存(8.0已移除)。
- 解析器:词法分析 + 语法分析 → AST。
- 优化器:选择执行计划(CBO成本优化)。
- 执行器:调用存储引擎API执行。
- 存储引擎层:InnoDB、MyISAM、Memory等,可插拔。
- 面试要点:理解SQL请求从连接到执行的完整路径。
InnoDB vs MyISAM?
★★★★★
- InnoDB(默认):
- 支持事务(ACID)。
- 行级锁(高并发)。
- MVCC(多版本并发控制)。
- 外键支持。
- 聚簇索引(数据和主键索引在一起)。
- 崩溃安全(Redo/Undo Log)。
- MyISAM:
- 不支持事务。
- 表级锁(并发差)。
- 非聚簇索引。
- COUNT(*)快(存储行数)。
- 全文索引(5.6前只有MyISAM支持)。
- 结论:99%场景选InnoDB——MyISAM已被淘汰。
- 面试要点:能说出InnoDB的5个核心优势。
InnoDB的Buffer Pool?
★★★★★
- Buffer Pool是InnoDB的内存缓存池,缓存数据页和索引页。
- 默认大小:128MB(生产建议设置为物理内存的60-80%)。
- LRU优化:
- 传统LRU的问题:全表扫描会污染LRU。
- InnoDB改进:Young区(热数据,前5/8)+ Old区(冷数据,后3/8)。
- 新读入的页先放入Old区头部,再次被访问且超过
innodb_old_blocks_time才移入Young区。
- 预读(Read-Ahead):线性预读和随机预读。
- 多Instance:
innodb_buffer_pool_instances(>1GB时建议多个)。
- 面试要点:Buffer Pool的LRU优化防止全表扫描污染。
InnoDB的Redo Log?
★★★★★
- Redo Log保证事务的持久性(Durable)。
- WAL(Write-Ahead Logging):先写日志再写数据。
- 结构:
- 固定大小的循环文件(
ib_logfile0、ib_logfile1)。
write pos:当前写入位置。
checkpoint:已刷盘的位置。
- 流程:
1. 事务修改数据 → 修改Buffer Pool中的页(Dirty Page)。
2. 同时写入Redo Log Buffer。
3. Redo Log Buffer刷入Redo Log文件(innodb_flush_log_at_trx_commit控制)。
4. 后台线程异步将Dirty Page刷入数据文件。
innodb_flush_log_at_trx_commit:
1:每次提交都fsync(最安全,推荐)。
2:每次提交写入OS缓存,每秒fsync(可能丢1秒数据)。
0:每秒写入并fsync(可能丢1秒数据)。
- 面试要点:Redo Log的WAL机制和fsync策略是必考点。
InnoDB的Undo Log?
★★★★★
- Undo Log保证事务的原子性(Atomicity)和MVCC。
- 作用:
- 回滚:事务失败时用Undo Log恢复数据。
- MVCC:通过Undo Log构建数据的旧版本(Read View)。
- 存储:Undo Log存储在系统表空间或Undo表空间中。
- 类型:
- Insert Undo Log:Insert操作产生的,事务提交后可删除。
- Update Undo Log:Update/Delete操作产生的,需要保留给MVCC使用。
- Purge:后台线程定期清理不再被任何事务需要的Undo Log。
- 面试要点:Undo Log是回滚+MVCC的基础。
InnoDB的MVCC?
★★★★★
- MVCC(Multi-Version Concurrency Control)实现非锁定读。
- 核心概念:
- 隐藏列:每行数据有
DB_TRX_ID(最后修改的事务ID)和DB_ROLL_PTR(指向Undo Log)。
- Read View:事务开始时创建的"快照",包含当前活跃事务列表。
- Read View的判断规则:
- 如果
DB_TRX_ID < min_active_id:可见(事务已提交)。
- 如果
DB_TRX_ID > max_active_id:不可见(事务在Read View创建后开始)。
- 如果
DB_TRX_ID在活跃列表中:不可见(事务未提交)。
- 如果不在活跃列表中:可见(事务已提交)。
- 不可见时,通过
DB_ROLL_PTR找到Undo Log的旧版本,递归判断。
- RC级别:每条SQL创建新的Read View。
- RR级别:事务开始时创建一个Read View(整个事务复用)。
- 面试要点:理解Read View的判断规则和RC/RR的区别。
InnoDB的锁机制?
★★★★★
- 锁类型:
- 共享锁(S Lock):读锁,允许多个事务同时持有。
- 排他锁(X Lock):写锁,只允许一个事务持有。
- 意向锁(IS/IX Lock):表级锁,表示事务打算加行锁(快速判断表级冲突)。
- 行锁类型:
- Record Lock:锁定索引记录。
- Gap Lock:锁定索引记录之间的间隙(防止幻读)。
- Next-Key Lock:Record Lock + Gap Lock(左开右闭区间)。
- Insert Intention Lock:插入意向锁(插入到Gap时使用)。
- 加锁规则(RR级别):
1. 默认加Next-Key Lock。
2. 等值查询唯一索引且命中 → 退化为Record Lock。
3. 等值查询未命中 → 退化为Gap Lock。
4. 范围查询 → Next-Key Lock。
- 面试要点:理解Next-Key Lock的加锁规则是解决死锁的关键。
InnoDB的Doublewrite?
★★★★☆
- 问题:数据页(16KB)写入可能是部分写入(如只写了8KB就断电)。
- Doublewrite Buffer:
- 共享表空间中的2MB区域(128个页)。
- 脏页刷盘前先写入Doublewrite Buffer。
- 再写入实际数据文件。
- 如果写入数据文件时断电,恢复时从Doublewrite Buffer恢复。
- 面试要点:Doublewrite解决页撕裂(Partial Write)问题。
二、索引优化(9-18)
B+树索引结构?
★★★★★
- B+树特点:
- 非叶子节点只存Key,叶子节点存Key+Data。
- 叶子节点通过双向链表连接(范围查询快)。
- 树的高度低(3-4层可存储千万级数据)。
- 为什么不用B树?
- B树非叶子节点也存数据 → 每个节点能存的Key更少 → 树更高 → IO更多。
- 为什么不用Hash?
- Hash不支持范围查询(
>, <, BETWEEN)。
- Hash不支持排序(
ORDER BY)。
- Hash不支持最左前缀匹配。
- 面试要点:B+树的3个优势——矮胖、范围查询、有序。
聚簇索引 vs 非聚簇索引?
★★★★★
- 聚簇索引(Clustered Index):
- 数据和主键索引存储在一起。
- 每个表只有一个聚簇索引(通常是主键)。
- 主键查询只需要一次IO(直接定位数据)。
- 非聚簇索引(Secondary Index):
- 叶子节点存储主键值(不是数据地址)。
- 查询时先查非聚簇索引得到主键 → 再回表查聚簇索引。
- 回表(Bookmark Lookup):二级索引 → 主键索引 → 数据。
- 覆盖索引:查询的字段都在二级索引中,不需要回表。
- 面试要点:理解回表和覆盖索引。
索引优化原则?
★★★★★
- 最左前缀原则:联合索引
(a,b,c)可以匹配a、a,b、a,b,c,不能匹配b或c。
- 避免索引失效:
- 不在索引列上做函数运算:
WHERE YEAR(create_time) = 2024 → 索引失效。
- 不做隐式类型转换:
WHERE varchar_col = 123 → 索引失效。
- 不使用
!=、<>、NOT IN(可能导致索引失效)。
- 不使用
LIKE '%abc'(前缀模糊索引失效)。
- 不使用
OR连接非索引列。
- 索引下推(ICP,Index Condition Pushdown):
- MySQL 5.6+优化:在存储引擎层用索引列过滤,减少回表次数。
EXPLAIN中Using index condition表示使用了ICP。
- 面试要点:能说出5个以上索引失效的场景。
EXPLAIN执行计划?
★★★★★
- 关键字段:
type:访问类型(system > const > eq_ref > ref > range > index > ALL)。
key:实际使用的索引。
rows:预估扫描行数。
Extra:
Using index:覆盖索引(不回表)。
Using where:Server层过滤。
Using index condition:索引下推。
Using filesort:额外排序(可能慢)。
Using temporary:使用临时表(需要优化)。
- 优化目标:
type至少到range,避免ALL(全表扫描)。
- 面试要点:能读懂EXPLAIN结果并识别性能问题。
联合索引的设计?
★★★★☆
- 设计原则:
- 高选择性列在前:
SELECT COUNT(DISTINCT col)/COUNT(*)。
- 等值条件列在前,范围条件列在后。
- 尽量覆盖查询字段(避免回表)。
- 示例:
WHERE status = 1 AND create_time > '2024-01-01' ORDER BY user_id。
- 索引:
(status, create_time, user_id)。
status等值过滤 → create_time范围过滤 → user_id排序(索引有序)。
- 索引合并(Index Merge):MySQL使用多个索引合并结果(通常不如联合索引高效)。
- 面试要点:联合索引的列顺序影响查询性能。
索引的代价?
★★★★☆
- 空间代价:每个索引都是一棵B+树,占用磁盘空间。
- 时间代价:
- INSERT/UPDATE/DELETE需要维护索引(增加写开销)。
- 索引越多,写入越慢。
- 内存代价:Buffer Pool需要缓存索引页。
- 索引数量建议:单表索引不超过5-6个。
- 面试要点:索引不是越多越好——需要平衡查询和写入性能。
前缀索引?
★★★★☆
- 对长字符串列(如VARCHAR(255))只索引前N个字符。
ALTER TABLE user ADD INDEX idx_email (email(20))。
- 选择前缀长度:
SELECT COUNT(DISTINCT LEFT(email, N)) / COUNT(*) 选择选择性接近1的N。
- 劣势:不支持覆盖索引(需要回表比较完整值)。
- 面试要点:前缀索引是长字符串列的折中方案。
自适应哈希索引(AHI)?
★★★★☆
- InnoDB自动为热点页建立Hash索引。
- 触发条件:某个索引页被频繁访问(>100次)。
- 优势:将B+树的O(logN)降为O(1)。
- 监控:
SHOW ENGINE INNODB STATUS查看AHI使用情况。
- 关闭:
innodb_adaptive_hash_index=0(某些场景AHI反而降低性能,如高并发更新)。
- 面试要点:AHI是InnoDB自动优化的机制——了解即可。
分区表?
★★★★☆
- 分区类型:
- RANGE:按范围分区(如按时间)。
- LIST:按枚举值分区。
- HASH:按Hash值分区。
- KEY:类似HASH但用MySQL内部函数。
CREATE TABLE orders (
id BIGINT, order_date DATE, ...
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
- 优势:大表查询只扫描相关分区(Partition Pruning)。
- 劣势:分区键必须包含在唯一索引中;跨分区查询可能更慢。
- 面试要点:分区表适合大表的历史数据管理。
Online DDL?
★★★★☆
- MySQL 5.6+支持Online DDL——ALTER TABLE期间允许DML操作。
- 算法:
COPY:创建新表 → 复制数据 → 替换(长时间锁表)。
INPLACE:在原表上修改(不复制数据,部分操作支持)。
INSTANT:只修改元数据(MySQL 8.0+,最快)。
ALTER TABLE user ADD INDEX idx_name (name), ALGORITHM=INPLACE, LOCK=NONE;
- 大表DDL建议:
- 使用
pt-online-schema-change工具(Percona)。
- 在低峰期执行。
- 监控DDL进度。
- 面试要点:大表加索引需要用Online DDL或pt-osc。
三、事务与并发控制(19-28)
事务的ACID特性?
★★★★★
- Atomicity(原子性):事务要么全部成功要么全部回滚(Undo Log保证)。
- Consistency(一致性):事务前后数据库状态一致(应用层保证)。
- Isolation(隔离性):并发事务互不影响(锁 + MVCC保证)。
- Durability(持久性):事务提交后数据不丢失(Redo Log保证)。
- 面试要点:每个特性对应哪个底层机制。
事务隔离级别?
★★★★★
- Read Uncommitted:脏读(能读到未提交的数据)。
- Read Committed(RC):不可重复读(同一事务两次读可能不同)。
- Repeatable Read(RR):可重复读(InnoDB默认,通过MVCC实现)。
- Serializable:串行化(最高隔离级别,性能差)。
- InnoDB在RR级别还通过Next-Key Lock防止幻读。
- 面试要点:能说出4个隔离级别和每个级别解决的问题。
RC vs RR的区别?
★★★★★
- RC(Read Committed):
- 每条SQL创建新的Read View。
- 能看到其他事务已提交的修改。
- 不可重复读(同一事务两次SELECT可能不同)。
- 加锁:只锁匹配的行(Record Lock)。
- RR(Repeatable Read):
- 事务开始时创建一个Read View,后续复用。
- 看到的是事务开始时的快照。
- 可重复读。
- 加锁:使用Next-Key Lock(Record Lock + Gap Lock)。
- 选择建议:
- 大多数业务用RC(更少的锁冲突,更高并发)。
- 需要一致性快照的报表用RR。
- 面试要点:RC vs RR的核心区别是Read View的创建时机。
死锁的产生与解决?
★★★★★
- 死锁条件:两个事务互相等待对方持有的锁。
- 示例:
- T1:
UPDATE user SET age=20 WHERE id=1(持有id=1的锁)。
- T2:
UPDATE user SET age=30 WHERE id=2(持有id=2的锁)。
- T1:
UPDATE user SET age=25 WHERE id=2(等待id=2的锁)。
- T2:
UPDATE user SET age=35 WHERE id=1(等待id=1的锁)→ 死锁。
- InnoDB处理:检测到死锁后回滚持有锁最少的事务。
- 预防:
- 按固定顺序访问表和行。
- 保持事务短小。
- 使用低隔离级别(RC比RR锁更少)。
- 添加合适索引(避免锁升级)。
- 查看:
SHOW ENGINE INNODB STATUS中的LATEST DETECTED DEADLOCK。
- 面试要点:死锁预防的核心是"固定顺序访问"。
当前读 vs 快照读?
★★★★☆
- 快照读(Snapshot Read):
- 普通SELECT(不加锁)。
- 通过MVCC读取历史版本。
- RC:每条SQL新快照。RR:事务开始时的快照。
- 当前读(Current Read):
SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE。
UPDATE、DELETE、INSERT。
- 读取最新已提交数据 + 加锁。
- 面试要点:普通SELECT是快照读;FOR UPDATE是当前读。
行锁变表锁的场景?
★★★★☆
- 不使用索引:
UPDATE user SET name='x' WHERE age=20(如果age没有索引)。
- 索引失效:隐式类型转换、函数运算导致索引失效。
- 锁升级:InnoDB在RR级别使用Next-Key Lock,锁定范围可能很大。
- 检查:
EXPLAIN查看是否使用索引。
- 面试要点:确保UPDATE/DELETE使用索引——否则锁全表。
乐观锁与悲观锁?
★★★★☆
- 悲观锁:
SELECT ... FOR UPDATE,先锁再改。
- 适用:写冲突多的场景。
- 问题:锁等待影响并发。
- 乐观锁:版本号或CAS,先改再检查。
UPDATE account SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 10;
- 适用:读多写少。
- 问题:冲突时需要重试。
- 面试要点:根据冲突频率选择乐观锁或悲观锁。
MySQL的GTID?
★★★★☆
- GTID(Global Transaction Identifier):全局唯一事务标识。
- 格式:
server_uuid:transaction_id(如3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5)。
- 优势:
- 主从切换简单(不需要找binlog position)。
- 从库自动定位复制位置。
- 方便验证主从一致性。
- 配置:
gtid_mode=ON、enforce_gtid_consistency=ON。
- 面试要点:GTID简化了主从复制的运维。
MySQL的主从复制?
★★★★★
- 异步复制(默认):
- 主库提交事务 → 写入Binlog → 返回客户端。
- 从库IO线程拉取Binlog → 写入Relay Log → SQL线程重放。
- 问题:主库宕机时从库可能丢数据。
- 半同步复制:
- 主库提交事务 → 等待至少一个从库确认收到Binlog → 返回客户端。
rpl_semi_sync_master_enabled=1。
- 优势:不丢数据(至少一个从库有)。
- 劣势:性能比异步差(多一次网络RT)。
- 组复制(Group Replication):
- 基于Paxos协议的多主复制。
- 强一致性(所有节点确认)。
- 面试要点:理解异步/半同步/组复制的区别。
MySQL的高可用方案?
★★★★☆
- MHA(Master High Availability):自动主从切换,经典方案。
- Orchestrator:GitHub开源的MySQL高可用管理工具。
- MySQL InnoDB Cluster:MySQL官方方案(Group Replication + MySQL Shell + MySQL Router)。
- Vitess:YouTube开源的MySQL分片和高可用方案。
- 阿里云RDS:云厂商托管方案(自动主从切换)。
- 面试要点:能说出2-3种MySQL高可用方案。
四、SQL优化实战(29-38)
慢查询分析?
★★★★★
- 开启慢查询:
slow_query_log=ON、long_query_time=1(超过1秒记录)。
- 分析工具:
mysqldumpslow:汇总慢查询。
pt-query-digest(Percona):详细的慢查询分析。
EXPLAIN:分析执行计划。
performance_schema:实时性能监控。
- 优化步骤:
1. 找到慢SQL。
2. EXPLAIN分析执行计划。
3. 识别全表扫描、临时表、文件排序。
4. 添加/优化索引。
5. 重写SQL。
分页查询优化?
★★★★★
- 问题:
SELECT * FROM order ORDER BY id LIMIT 1000000, 10。
- 需要扫描1000010行再丢弃前1000000行。
- 优化方案:
- 延迟关联:先查主键,再回表。
SELECT * FROM order o
JOIN (SELECT id FROM order ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;
- 游标分页(推荐):
WHERE id > last_id ORDER BY id LIMIT 10。
- 覆盖索引:只查索引列(不回表)。
- 面试要点:深度分页用"延迟关联"或"游标分页"。
COUNT优化?
★★★★☆
COUNT(*):统计所有行(InnoDB不存储行数,需要遍历)。
COUNT(1):与COUNT(*)相同(MySQL优化器已统一处理)。
COUNT(列):统计该列非NULL的行数。
- 优化方案:
- 使用覆盖索引(避免回表)。
- 维护计数表(通过触发器/应用层更新)。
- 使用Redis缓存计数(适合近似值)。
- 信息-schema(近似值):
SHOW TABLE STATUS。
- 面试要点:
COUNT(*)在InnoDB中需要遍历索引。
ORDER BY优化?
★★★★☆
- Using filesort:没有使用索引排序,需要额外排序操作。
- 优化:
- 添加合适的联合索引(排序列在最后)。
ORDER BY字段与WHERE字段组合成联合索引。
- 确保
ORDER BY方向一致(都是ASC或都是DESC)。
- 示例:
SELECT * FROM user WHERE status = 1 ORDER BY create_time。
- 索引:
(status, create_time)。
- Extra:
Using index(覆盖索引排序,无filesort)。
- 面试要点:避免filesort = 排序列使用索引。
GROUP BY优化?
★★★★☆
- MySQL 8.0之前:
GROUP BY默认隐式排序(Using temporary + Using filesort)。
- MySQL 8.0+:
GROUP BY不排序(性能提升)。
- 优化:
- 添加联合索引(
GROUP BY列在前)。
- 使用
ORDER BY NULL(旧版本避免排序)。
- 使用覆盖索引。
- 面试要点:MySQL 8.0的GROUP BY不再隐式排序。
子查询优化?
★★★★☆
- 相关子查询:依赖外层查询,每行执行一次(性能差)。
SELECT * FROM order WHERE user_id IN (SELECT id FROM user WHERE age > 18);
SELECT o.* FROM order o JOIN user u ON o.user_id = u.id WHERE u.age > 18;
- 使用EXISTS替代IN(某些场景更高效)。
- 使用临时表:先查子查询存入临时表,再JOIN。
- 面试要点:子查询性能差 → 改为JOIN。
大表JOIN优化?
★★★★☆
- Nested Loop Join:MySQL默认的JOIN算法。
- 驱动表的每一行 → 在被驱动表中查找匹配。
- 优化:确保被驱动表的JOIN列有索引。
- Block Nested Loop Join(BNL):无索引时使用Join Buffer。
- 将驱动表的数据块加载到Join Buffer → 批量匹配。
join_buffer_size:Buffer大小(默认256KB)。
- Hash Join(MySQL 8.0+):无索引时的替代方案。
- 在内存中构建Hash表 → 匹配。
- 比BNL快10倍以上。
- 优化策略:
- 小表做驱动表。
- JOIN列添加索引。
- 减少JOIN的列(只选需要的列)。
- 面试要点:MySQL 8.0的Hash Join是无索引JOIN的救星。
临时表与文件排序?
★★★★☆
- Using temporary:MySQL创建临时表处理查询(GROUP BY、UNION、子查询)。
- Using filesort:MySQL需要额外排序(没有使用索引排序)。
- 优化:
- 添加合适的索引避免filesort。
- 增大
sort_buffer_size(排序缓冲区)。
- 增大
tmp_table_size/max_heap_table_size(内存临时表大小)。
- 使用
EXPLAIN定位临时表和filesort。
- 面试要点:Using temporary和Using filesort是需要消除的。
批量操作优化?
★★★★☆
- 批量INSERT:
- 多行INSERT:
INSERT INTO t VALUES (1,'a'),(2,'b'),(3,'c')。
rewriteBatchedStatements=true(JDBC参数,将多条INSERT合并)。
- 批量UPDATE:
INSERT ... ON DUPLICATE KEY UPDATE(MySQL特有的UPSERT)。
- CASE WHEN批量更新。
- 大事务拆分:每1000条提交一次。
- 面试要点:
rewriteBatchedStatements是Java批量操作的必配参数。
分库分表策略?
★★★★★
- 垂直分库:按业务模块拆分(用户库、订单库、商品库)。
- 垂直分表:将大表的列拆分(基础信息表 + 扩展信息表)。
- 水平分库分表:按规则将数据分散到多个库/表。
- Range分片:按ID范围(1-100万在t1,100万-200万在t2)。
- Hash分片:
hash(id) % N(数据均匀)。
- 一致性Hash:减少扩容时的数据迁移。
- 工具:ShardingSphere、MyCat、Vitess。
- 难点:跨库JOIN、分布式事务、全局ID、数据迁移。
- 面试要点:分库分表是"最后的手段"——先优化SQL和索引。
五、面试综合设计题(39-50)
如何设计一个高性能的订单表?
★★★★★
- 表结构:
- 主键:BIGINT自增或雪花ID。
- 索引:
(user_id, create_time)、(status, create_time)、(order_no)。
- 分区:按月RANGE分区(便于历史数据归档)。
- 字段:避免大字段(商品详情存JSON需考虑)。
- 优化策略:
- 冷热分离:活跃订单和历史订单分表。
- 读写分离:写入主库,查询走从库。
- 缓存:热点订单缓存到Redis。
- 面试要点:订单表设计 = 索引 + 分区 + 冷热分离。
MySQL主从延迟的处理?
★★★★★
- 原因:
- 主库并行写入,从库单线程重放(MySQL 5.6前)。
- 大事务。
- 从库硬件差。
- 解决方案:
- 并行复制(MySQL 5.7+
slave_parallel_workers)。
- 组提交(
binlog_group_commit)。
- 半同步复制(减少数据丢失)。
- 读写分离时强制走主库(对一致性要求高的查询)。
- MTS(Multi-Threaded Slave):按库并行或按组并行。
- 面试要点:主从延迟的解决方案 = 并行复制 + 合理的读写分离策略。
MySQL备份恢复方案?
★★★★☆
- 物理备份:
xtrabackup(Percona):热备,不锁表。
- MySQL Enterprise Backup(官方)。
- 快照备份(LVM/云磁盘快照)。
- 逻辑备份:
mysqldump:单线程,适合小库。
mydumper:多线程逻辑备份。
- 恢复策略:
- 全量备份 + 增量备份 + Binlog重放。
- PITR(Point-In-Time Recovery):恢复到指定时间点。
- 面试要点:
xtrabackup是生产环境的备份标准。
MySQL的参数调优?
★★★★☆
innodb_buffer_pool_size:物理内存的60-80%。
innodb_flush_log_at_trx_commit=1:数据安全。
sync_binlog=1:Binlog安全。
innodb_io_capacity=2000:SSD建议2000。
innodb_io_capacity_max=4000:SSD建议4000。
max_connections=500:最大连接数。
thread_cache_size=50:线程缓存。
query_cache_type=0:关闭查询缓存(MySQL 8.0已移除)。
- 面试要点:Buffer Pool和Redo Log的fsync是最关键的参数。
MySQL 8.0新特性?
★★★★☆
- Window Functions:窗口函数(
ROW_NUMBER()、RANK()、LEAD()、LAG())。
- CTE(Common Table Expression):
WITH子句递归查询。
- Invisible Indexes:隐藏索引(测试索引删除影响)。
- Descending Indexes:降序索引(
ORDER BY a ASC, b DESC可用索引)。
- JSON增强:
JSON_TABLE()、->>操作符。
- Functional Indexes:
ADD INDEX ((UPPER(name)))。
- 面试要点:MySQL 8.0的窗口函数和CTE是SQL能力的重大提升。
数据库连接池配置?
★★★★☆
- HikariCP(Spring Boot默认):
maximumPoolSize:CPU核心数 × 2 + 磁盘数(经验公式)。
minimumIdle:与maximumPoolSize相同(避免连接伸缩)。
connectionTimeout:30s。
idleTimeout:10min。
maxLifetime:30min(避免MySQL 8小时断连问题)。
- MySQL端:
wait_timeout=28800(8小时,与连接池maxLifetime配合)。
max_connections=500。
- 面试要点:HikariCP的
maximumPoolSize不是越大越好。
防SQL注入?
★★★☆☆
- 永远使用参数化查询(PreparedStatement)。
- MyBatis中使用
#{}(预编译)而不是${}(字符串拼接)。
- 输入验证:白名单验证。
- ORM框架天然防注入(JPA/Hibernate/MyBatis-Plus)。
- 面试要点:
#{}vs ${}的区别是MyBatis的必考点。
MySQL的监控方案?
★★★★☆
- Prometheus + Grafana:
mysqld_exporter:MySQL指标采集。
- 关键指标:QPS、TPS、Slow Queries、Connection Usage、Buffer Pool Hit Rate、Replication Lag。
- PMM(Percona Monitoring and Management):专业的MySQL监控。
- 告警:
- 慢查询突然增多。
- 连接数接近max_connections。
- 主从延迟 > 10秒。
- Buffer Pool命中率 < 95%。
- 面试要点:Buffer Pool命中率和慢查询是最核心的MySQL监控指标。
如何在线给大表加字段?
★★★★☆
- 方案一:MySQL 8.0 INSTANT DDL(
ALGORITHM=INSTANT)。
- 只修改元数据,秒级完成。
- 支持操作:添加列(在表末尾)、修改列默认值等。
- 方案二:Online DDL(
ALGORITHM=INPLACE)。
- 不锁表,但需要重建表(大表耗时)。
- 方案三:pt-online-schema-change(Percona工具)。
- 创建新表 → 增量同步 → 重命名替换。
- 适合超大表(>10GB)。
- 方案四:gh-ost(GitHub工具)。
- 基于Binlog触发器,更稳定。
- 面试要点:MySQL 8.0 INSTANT > Online DDL > pt-osc > gh-ost。
设计一个分库分表方案?
★★★★★
- 场景:订单表10亿条数据,单表性能下降。
- 分片策略:
- 分库:按用户ID Hash分16个库。
- 分表:每个库8张表(共128张表)。
- 分片键:
user_id(保证同一用户的订单在同一库)。
- 全局ID:雪花算法生成唯一OrderID。
- 跨库查询:
- 禁止跨库JOIN → 应用层聚合。
- 搜索需求 → ES同步。
- 报表需求 → 数仓同步。
- 工具:ShardingSphere-JDBC(客户端分片)。
- 数据迁移:双写 + Canal同步 + 数据校验。
- 面试要点:分片键选择和跨库查询处理是核心问题。
MySQL高并发写入优化?
★★★★★
- 写入优化:
- 批量INSERT(
rewriteBatchedStatements=true)。
- 关闭唯一性检查(
unique_checks=0,导入时)。
- 关闭外键检查(
foreign_key_checks=0,导入时)。
- 使用LOAD DATA INFILE(比INSERT快20倍)。
- 架构优化:
- 读写分离(写主库,读从库)。
- 分库分表(分散写入压力)。
- 消息队列削峰(异步写入)。
- 缓存(Redis挡住读压力,减少数据库负载)。
- 面试要点:高并发写入 = 批量操作 + 读写分离 + 分库分表 + 异步。
面试设计题:设计一个支持千万级并发的数据库架构?
★★★★★
1. 分库分表:16库 × 64表 = 1024张表(每表约100万数据)。
2. 读写分离:每库1主3从。
3. 缓存层:Redis Cluster(热点数据缓存)。
4. 搜索层:Elasticsearch(全文搜索和复杂查询)。
5. 消息队列:Kafka(异步写入和数据同步)。
- 写入路径:Client → API → Kafka → Consumer → MySQL(异步写入)。
- 读取路径:Client → API → Redis → MySQL(缓存优先)。
- 搜索路径:Client → API → Elasticsearch(Canal同步数据)。
- 一致性:Canal监听Binlog → 同步到Redis/ES。
- 容量估算:1024表 × 100万/表 × 1KB/行 ≈ 1TB数据,支持千万级并发查询。
- 面试答题模板:分库分表 → 读写分离 → 缓存 → 搜索 → 数据同步。