MySQL面试题-附答案

不可重复读与幻读

不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE） 操作。

幻读是针对数据插入（INSERT） 操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

InnoDB事务的隔离级别

• 读未提交(Read Uncommitted)：select语句不加锁，可能读取到不一致的数据，即“读脏”

• 读已提交(Read Committed, RC)：普通读是快照读(基于MVCC)

  • 除了在外键约束检查以及重复键检查时会封锁区间(next-key lock)，其他时刻都只使用记录锁(record lock)；
  • 每次执行语句的时候都重新生成一次快照

• 可重复读(Repeated Read, RR)：普通读是快照读(基于MVCC)

  • 在唯一索引上使用唯一的查询条件，会使用记录锁。范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录造成幻读或不可重复的读
  • 只在事务开始的时候生成一个快照

• 串行化(Serializable)：所有select语句都会被隐式的转化为 select … in share mode，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞

加锁的过程 分为有索引和无索引两种情况，比如sql update user set age=11 where id = 1 ，id 是这张表的主键，是有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，然后干净利落的在对应的索引数据上加行锁就可以了。

对于SQL： update user set age=11 where age=10 ，表中并没有为 age 字段设置索引，所以， MySQL 无法直接定位到这行数据。那怎么办呢，当然也不是加表锁了。MySQL 会为这张表中所有行加行锁，没错，是所有行。但是呢，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引

事务的ACID特性

• 原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
• 持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
• 隔离性：通过锁以及MVCC,使事务相互隔离开。
• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

索引失效的原因

• 查询条件包含or，会导致索引失效。
• 隐式类型转换，会导致索引失效，例如age字段类型是int，当查询where age = “1”，这样就会触发隐式类型转换。
• 最左匹配：like通配符会导致索引失效。注意："ABC%“会走range索引，”%ABC"索引才会失效。
• 最左匹配：联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
• 对索引字段进行函数运算 或 对索引列运算（如，+、-、*、/），索引失效。
• 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
• 索引字段上使用is null， is not null，可能导致索引失效（执行成本较高时不使用索引）。
• 相join的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
• mysql估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景

• 数据量少的不适合加索引
• 更新比较频繁的也不适合加索引
• 离散性低的字段不适合加索引（如性别）
• 字段的值很大时不适合加索引（可以考虑对其使用crc32值加索引、或者只索引指定长度的前缀）

InnoDB内存结构包含四大核心组件

• 缓冲池(Buffer Pool)，可以参考沈健老师文章缓冲池(buffer pool)，这次彻底懂了！！！
• 写缓冲(Change Buffer)，可以参考沈健老师文章写缓冲(change buffer)，这次彻底懂了！！！
• 自适应哈希索引(Adaptive Hash Index)，可以参考沈健老师文章自适应哈希索引
• 日志缓冲(Log Buffer)，可以参考沈健老师文章事务已提交，数据却丢了，赶紧检查下这个配置！！

SQL在MySQL中的执行流程

0. 连接：认证用户名和权限
1. 查询缓存：MySQL执行过的语句及其结果会以key-value形式缓存在内存中，命中缓存直接返回结果，MySQL8.0已删掉该功能
2. 解析sql：语法分析（生成句子），语义分析（确保这些句子讲得通），以及代码生成（为编译准备）
3. 优化执行计划：优化器是在表里面有多个索引时，决定使用哪个索引，或者在一个语句有多表关联（join）时，决定各个表的连接顺序 等。
4. 执行：根据执行计划，调用存储引擎API来查询数据

优化SQL的的思路

• 加索引；优化sql结构；调整MySQL缓存配置
• 避免返回不必要的数据（尤其是text类型字段）
• 适当分批量进行；避免大事务
• 分库分表