【面试题】[mysql]----10道经典mysql 面试题 答案+例子

1. 如何理解最左前缀原则？

最左前缀原则是联合索引的匹配规则：索引会优先匹配查询条件中最左侧的列，再依次匹配右侧列，若跳过左侧列直接使用右侧列，索引会失效。

• 例：联合索引(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 a=1 and c=3（跳过中间b）。

2. 如何理解行锁、GAP锁、临键锁？

三者是InnoDB的锁机制，用于保证事务隔离性，核心区别在锁定范围：

• 行锁：仅锁定单行数据，粒度最细。
  • 例：where id=10（id是主键），仅锁定id=10这一行，其他事务可操作其他行。
• GAP锁：锁定“间隙”（无数据的区间），防止插入数据导致幻读。
  • 例：索引列有值10、20，执行where id>10 and id<20时，GAP锁会锁定(10,20)区间，阻止插入15。
• 临键锁（Next-Key Lock）：行锁+GAP锁的组合，锁定“行+相邻间隙”，是InnoDB默认行级锁（非唯一索引查询时触发）。
  • 例：索引列有10、20，执行where id=15（无此数据），临键锁会锁定(10,20]区间（包含20这一行）。

3. 如何理解MVCC？

MVCC（多版本并发控制）是InnoDB实现“读写不阻塞”的核心机制，通过数据多版本实现并发访问：

• 原理：每行数据隐藏列记录DB_TRX_ID（修改事务ID）和DB_ROLL_PTR（回滚指针），通过undo日志保存历史版本。
• 例：事务A修改id=1的行（值从10→20），事务B此时查询，会通过回滚指针找到A修改前的版本（值10），无需等待A提交。

4. 如何理解count(*)和count(1)？

两者均用于统计结果集总行数，InnoDB中性能几乎无差异：

• count(*)：统计所有行（包括NULL），InnoDB优化后直接读行数（不访问具体列）。
• count(1)：对每行生成虚拟值1，同样统计所有行，优化逻辑与count(*)一致。
• 区别于count(列名)：会过滤该列值为NULL的行，性能更低（需读取列数据）。
  • 例：表中3行数据，其中1行name为NULL，则count(name)结果为2，count(*)和count(1)结果为3。

5. 如何理解Online DDL？

Online DDL（在线数据定义语言）是MySQL 5.6+的特性，解决传统DDL锁表问题：

• 传统DDL（如加索引）会全程锁表，导致读写阻塞；Online DDL仅在“准备”和“提交”阶段短暂锁表，中间阶段允许读写。
• 例：给大表user加索引idx_age(age)，Online DDL会先创建临时索引，同步新增数据，最后原子替换，期间select * from user和insert into user可正常执行。

6. 哪些情况会导致索引失效？

常见场景违反索引匹配逻辑或触发优化器放弃索引：

• 索引列参与函数/运算：where SUBSTR(name,1,1)='A'（对name列用函数，索引失效）。
• 不等于/范围查询：where age != 20、where age not in (10,20)（可能失效，视数据分布）。
• 字符串不加引号：where name=123（隐式转换为字符串，索引失效）。
• 联合索引跳过左侧列：where b=2（联合索引(a,b)，跳过a）。
• 结果集过大：where age>1（若表中90%数据满足，优化器会选择全表扫描）。

7. 如何理解filesort？

filesort是MySQL的外部排序机制，当查询无法通过索引排序时触发：

• 触发场景：order by的列无索引，或与索引顺序不匹配。
  • 例：索引为(a,b)，但查询是select * from t where a=1 order by c（c无索引），会触发filesort。
• 影响：需将数据加载到内存/磁盘排序，数据量大时性能极差。
• 优化：让order by列匹配索引（如建联合索引(a,c)），避免filesort。

8. 哪些情况会导致锁表？

锁表通常因锁粒度升级或特殊操作：

• DDL操作：alter table（传统DDL全程锁表）、drop table。
• MyISAM引擎：写操作（如update）会锁全表，读操作需等待。
• InnoDB无索引查询：where name='test'（name无索引），会触发全表扫描并锁全表。
• 显式锁表：执行lock tables t write，会手动锁表t。

9. 如何理解MySQL的死锁机制？

死锁是两个事务互相等待对方释放锁导致的阻塞，InnoDB有自动检测与解决机制：

• 产生条件：事务1持有锁A，等待锁B；事务2持有锁B，等待锁A（循环等待）。
  • 例：事务1先锁id=1，再申请锁id=2；事务2先锁id=2，再申请锁id=1，形成死锁。
• 处理：InnoDB定期检测死锁，选择“代价小”的事务回滚（如 undo 日志少的），释放锁让另一事务继续。

10. 如何优化慢查询？

核心思路：减少IO和数据处理量，步骤如下：

1. 定位问题：开启慢查询日志（slow_query_log=1），用explain分析执行计划（看type是否为ALL（全表扫描）、Extra是否有Using filesort）。
2. 优化索引：给过滤列（where）、排序列（order by）、关联列（join）加索引。
   • 例：慢查询select * from user where age>30 order by reg_time，可建索引(age, reg_time)。
3. 优化SQL：避免select *（用覆盖索引）、拆分大事务、减少join表数量。
4. 表结构优化：拆分大表（如按时间分表）、用合适类型（如tinyint代替int存性别）。
5. 配置优化：调大innodb_buffer_pool_size（缓存更多数据）、增加sort_buffer_size（减少filesort磁盘写入）。
   如果文章对你有一点点帮助，欢迎【点赞、留言、+ 关注】，
   您的关注是我持续创作的重要动力！有问题欢迎随时交流！多一个朋友多一条路！