MySQL面试题集

Q1：MySQL中MyISAM与InnoDB引擎的核心区别有哪些？为什么现在默认用InnoDB？

核心差异：

特性	MyISAM	InnoDB
事务支持	❌ 不支持	✅ 支持（ACID）
锁粒度	表级锁	行级锁（支持MVCC）
外键约束	❌ 不支持	✅ 支持
崩溃恢复	❌ 无自动恢复能力	✅ 通过redo log自动恢复
COUNT(*)性能	✅ 极快（保存总行数）	较慢（需全表扫描）
全文索引	✅ 原生支持	MySQL 5.6+ 支持

追问点：为什么MyISAM的COUNT(*)更快？—— 因为其元数据中保存了表行数（`table_rows`），但该值在有`WHERE`条件或并发写入时可能不准确；而InnoDB需实际扫描数据页计数，更可靠但更慢。

为什么默认用InnoDB？

自MySQL 5.5起（2010年），官方将InnoDB设为默认引擎，原因有三：

1. 事务需求普及：电商订单、支付流水等业务必须强一致性；
2. 并发写入场景激增：表锁易导致锁等待，行锁更适应高并发；
3. 高可用演进需求：InnoDB支持热备（Percona XtraBackup）、在线DDL等特性，是主从复制、集群的基础。

Q2：CHAR与VARCHAR的区别？TEXT类型何时使用？

CHAR：固定长度字符串，存储时用空格补足长度；查询时自动去除尾部空格（除非`PAD_CHAR_TO_FULL_LENGTH`模式）。适合长度固定的数据（如性别、省份编码）。

VARCHAR：可变长度字符串，仅存储实际内容+1~2字节长度标识。适合长度不定的数据（如用户名、地址）。

TEXT家族：

• `TINYTEXT`：最大255字节
• `TEXT`：最大65KB
• `MEDIUMTEXT`：最大16MB
• `LONGTEXT`：最大4GB

关键注意：TEXT字段无法使用普通索引（需指定前缀长度，如`INDEX(content(255))`），且在排序、GROUP BY时会使用临时表，影响性能。建议：

• 能用VARCHAR就不用TEXT（如微博正文用VARCHAR(500)）；
• 大文本内容可拆分存储（主表存摘要+ID，详情表存全文）；
• 全文检索用`FULLTEXT`索引或Elasticsearch。

Q3：MySQL的事务四大特性（ACID）如何实现？

虽然面试常被问及定义，但企业更关注实现机制：

• Atomicity（原子性）：通过undo log实现回滚。每条写操作前先记录undo日志，失败时反向执行undo操作；
• Consistency（一致性）：由应用层+数据库约束共同保障（如外键、CHECK约束、触发器）；
• Isolation（隔离性）：通过锁机制（next-key lock）+ MVCC（多版本并发控制）实现；
• Durability（持久性）：通过redo log（物理日志，循环写入）+ double write buffer（防止部分写失败）保障。

延伸追问：RR（可重复读）隔离级别下，InnoDB如何防止幻读？

答案：使用Next-Key Lock（记录锁+间隙锁的组合）。例如：
`SELECT * FROM t WHERE id > 10 FOR UPDATE;`
会锁定(10, +∞)区间，包括11、12等记录，同时阻塞新记录插入，从而避免幻读。

Q4：如何编写高性能的JOIN语句？驱动表选择有哪些坑？

MySQL的JOIN实现为Nested-Loop Join（嵌套循环连接），小表驱动大表是铁律。但注意：

• 驱动表≠小表：优化器会自动选择过滤后行数更少的表为驱动表；
• 被驱动表必须有索引：JOIN字段（如`users.id = orders.user_id`）需在被驱动表建索引；
• BNL算法陷阱：若被驱动表无索引，MySQL会启用BNL（Block Nested-Loop），用join buffer缓存驱动表数据，但内存不足时性能极差。

优化案例：

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

Q5：索引失效的10大场景（附真实案例）

以下情况会导致索引失效，需重点规避：

失效场景	错误示例	正确写法
函数/表达式操作	`WHERE YEAR(create_time)=2023`	`WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'`
隐式类型转换	`WHERE phone = 13800138000`（phone为VARCHAR）	`WHERE phone = '13800138000'`
NOT / != / <>	`WHERE status != 'invalid'`	`WHERE status = 'valid'`（若有效值少）
LIKE以%开头	`WHERE name LIKE '%张'`	`WHERE name LIKE '张%'`
OR条件未全建索引	`WHERE a=1 OR b=2`（a、b单列索引）	`WHERE a=1 UNION ALL SELECT ... WHERE b=2 AND a!=1`

进阶技巧：联合索引需遵循最左前缀原则。例如索引`INDEX(a,b,c)`：

• `WHERE a=1 AND b=2` ✅ 可用
• `WHERE b=2 AND c=3` ❌ 不可用
• `WHERE a=1 AND c=3` ✅ 可用a，但c需回表

SELECT * FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 10;

SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders
  ORDER BY create_time
  LIMIT 1000000, 10
) t ON o.id = t.id;

SELECT user_id, status, amount
FROM orders
WHERE user_id = 123;

Q6：MySQL主从复制的原理与延迟问题排查

核心流程：

1. 主库写入binlog（Binary Log）；
2. 从库IO线程拉取binlog并写入relay log；
3. 从库SQL线程重放relay log更新数据。

延迟常见原因：

• 大事务：主库一个事务写入10万行，从库需串行重放；
• 从库压力大：从库同时承担读请求，CPU/IO资源被抢占；
• 参数配置不当：如`sync_binlog=1`导致频繁刷盘；
• 单线程复制：MySQL 5.6前SQL线程单线程，5.7起支持组提交（GTID+多线程）。

排查命令：

SHOW MASTER STATUS;

SHOW SLAVE STATUS\G

Q7：分库分表方案选型（ShardingSphere vs MyCat）

当单表数据超500万，或写入QPS超2000，需考虑分片：

方案	优势	劣势	适用场景
ShardingSphere（Apache）	Java生态原生，支持读写分离、分布式事务	需业务改造，运维复杂	互联网中大型项目
MyCat	对应用透明（伪装MySQL）	社区活跃度下降，高可用依赖外部组件	传统企业系统改造
ProxySQL	轻量级，专注读写分离+查询缓存	不支持分片	读多写少场景

分片键选择原则：

• 高频查询字段（如`user_id`）；
• 分布均匀（避免数据倾斜）；
• 不可变（如订单号，非创建时间）。

Q8：如何防止SQL注入？MyBatis中#与$的区别？

本质区别：

• `#{param}`：预编译参数（PreparedStatement），值作为参数传入，自动转义；
• `${param}`：字符串拼接，直接替换SQL文本，存在注入风险。

错误示例：

SELECT * FROM users WHERE name = '${param}';

正确写法：

SELECT * FROM users WHERE name = #{param};

额外防护：

• 最小权限原则：应用账号仅授予必要权限（如SELECT/INSERT/UPDATE，禁用DROP/ALTER）；
• 输入校验：后端校验参数类型、长度、格式（如邮箱、手机号正则）；
• 开启审计日志：记录所有SQL执行（需配置`general_log`或使用Percona Server）。

Q9：MySQL密码安全策略与弱口令检测

MySQL 5.7+ 引入`validate_password`插件：

SHOW VARIABLES LIKE 'validate_password%';

SET GLOBAL validate_password.length = 8;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

弱口令检测脚本：

SELECT user, host, authentication_string
FROM mysql.user
WHERE authentication_string = '' OR
       authentication_string IS NULL OR
       password = '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' ;

Q10：大表DDL如何在线变更而不锁表？

传统`ALTER TABLE`会锁表，导致写入阻塞。解决方案：

• pt-online-schema-change（Percona工具）：
  1. 新建空表，结构为变更后版本；
  2. 创建触发器同步新旧表数据；
  3. 分批拷贝数据；
  4. 切换新表名，删除旧表。
• MySQL 5.6+ INPLACE DDL：支持部分操作（如ADD COLUMN）在线执行，但需满足条件；
• 云数据库方案：阿里云DMS、AWS Schema Change Optimization可自动优化DDL流程。

案例：某电商订单表（2亿行）需新增字段`is_deleted`，使用pt-online-schema-change耗时4小时，期间无业务中断。

Q11：如何诊断“慢查询”？慢查询日志配置与分析

开启慢查询日志：

SET GLOBAL long_query_time = 1;

SET GLOBAL log_queries_not_using_indexes = ON;

SHOW VARIABLES LIKE 'slow_query_log_file';

分析工具：

• `mysqldumpslow -s c -t 10 /var/log/mysql/slow.log`：统计TOP10慢查询；
• `pt-query-digest`：深度分析，给出优化建议；
• 云厂商控制台（如阿里云DBAudit）：可视化慢查询热力图。

关键指标：查询时间、扫描行数、锁等待时间、临时表使用次数。

Q12：内存使用过高？InnoDB Buffer Pool调优实战

`innodb_buffer_pool_size`是影响性能最关键的参数，通常设为物理内存的50%~70%（独占数据库服务器）。

调优步骤：

1. 监控`Innodb_buffer_pool_read_requests`（逻辑读）与`Innodb_buffer_pool_reads`（物理读）；
2. 计算命中率：
   `命中率 = (逻辑读 - 物理读) / 逻辑读 × 100%`
   正常应>99%，若<95%需增大Buffer Pool；
3. 动态调整（MySQL 5.7+支持在线扩容）：
   `SET GLOBAL innodb_buffer_pool_size = 4*1024*1024*1024;`

避坑：不要设为100%，需预留内存给OS、Query Cache、线程栈等。

MySQL与PostgreSQL对比：选型决策指南

企业常纠结于MySQL vs PostgreSQL。核心对比：

维度	MySQL	PostgreSQL
事务支持	InnoDB完善	全引擎支持（MVCC更激进）
JSON支持	5.7+ 原生JSON类型	✅ 更强大（JSONB + 操作符）
GIS空间数据	基础支持（GeoJSON）	✅ PostGIS扩展（工业级）
扩展性	插件少（如TokuDB已废弃）	✅ 插件丰富（ TimescaleDB时序、pg_bigm全文检索）
运维复杂度	低（云厂商支持完善）	中（需专业DBA）

选型建议：

• 电商/内容类：首选MySQL（社区支持强、工具链成熟）；
• 金融/GIS/复杂查询：选PostgreSQL（数据模型灵活、扩展性强）；
• 混合场景：用MySQL存业务数据，Redis存缓存，Elasticsearch做搜索。

数据库设计常见错误（附修复方案）

• 错误1：用自增ID做主键（高并发场景）
  → 修复：改用UUID或Snowflake ID，避免热点写入；
• 错误2：字段设计未考虑未来扩展
  → 修复：预留字段（如`ext_json`存动态属性），避免频繁ALTER TABLE；
• 错误3：过度范式化
  → 修复：读多写少的维度表可冗余字段（如用户表存省份名，避免JOIN）；
• 错误4：未建逻辑删除字段
  → 修复：`is_deleted TINYINT DEFAULT 0` + `deleted_at DATETIME`，避免物理删除导致关联数据丢失。