MySQL面试前准备

作者 |花花和Java
来源 | 花花和Java(ID：gh_02ed7983d1ec)

1 MySQL事务特性是什么?怎样实现ACID特性?

MySQL事务指：一组操作要么同时成功要同时失败。所以事务四大特性：一致性、原子性、持久性、隔离性。

事务原子性：

MySQL中含有undo log日志又叫做回滚日志。用于记录数据变化的逻辑。当系统发送错误或者rollback时候，根据undo log更改为原来未修改的数据。

持久性：

在数据库中数据存储在磁盘中，如果每次读写都操作磁盘效率很低。InnoDB中含有缓存，每次查询时候先在查缓存，查询到数据返回，查询不到数据去数据库查。每次更新数据，先更新缓存，然后缓存按照checkpoint规则刷新到数据库中。MySQL中含有redo log，将更改后的记录到redo log缓存中，当提交事务后，将redo log缓存异步刷新到redo log磁盘中。即使内存的数据没有同步到数据库，或者同步数据一半数据库宕机。在重启后利用redo log恢复新的数据。

隔离性：

MySQL的隔离级有，读未提交、读已提交、可重复读。实现是通过锁机制+mvcc来实现。读未提交数据：串行化：select在无索引对全表加锁，主键索引对行加记录锁，联合索引在范围加锁(间隙锁)，读已提交数据(RC)：是通过mvcc(并发版本控制)来实现，read view每次快照读都会创建read view。可重复读：是通过mvcc(并发版本控制)来实现，read view每次快照读会使用第一次创建的read view。

一致性：

一致性是事务最终目的，前面的原子性、隔离性、永久性保证了数据库一致性的实现。

2 MySQL的事务隔离级别有哪些?解决什么问题?默认隔离级别是什么?

• 读未提交：读取到未提交事务数据。会产生脏读、不可重复读;
• 幻读 读已提交： 读取到别人已经提交数据。产生不可重复读、幻读 ;
• 可重复读：在一次事务中，两次相同的读取。读取不到别人修改已提交事务数据，能读取到别人新增数据。能够产生幻读 ;
• 序列化： 不会产生脏读、不可重复读、幻读问题。
• MySQL中默认隔离级别是可重复读。

3 MySQL中如何解决脏读和幻读?解决原理?幻读、脏读如何避免?

将MySQL隔离级别设置为读已提交可以解决脏读问题。在RR隔离级别通过mvcc+锁解决部分幻读问题。串行化完全可以解决幻读问题。

4 MySQL中索引有哪些(组合索引、二级索引)?这些索引有什么区别?

• 主键索引：一种特殊唯一索引，不允许有null。(聚集索引，其他为非聚集索引、二级索引)
• 普通索引：没有任何限制索引。
• 唯一索引：不允许重复但是可以允许为null。
• 组合索引：是多个字段组合成索引，遵循最左前缀法。全文索引：一种搜索索引。

5 索引的底层结构是什么样(B+和hash)?

基于InnoDB，索引是基于B+树和自适应hash。主键索引的B+结构，节点都是主键值，然后叶子节点是完整的数据。非主键索引，节点都是索引值，叶子节点都是主键值。B+一般高是3-4层，故需要3-4次查询。而hash索引时间复杂度为O(1)，InnoDB引擎监控各个索引页的查询，如果发现hash索引可以提升速度，建立hash索引。

6 索引为什么使用B+树，而不去使用红黑树和B树，平衡二叉查找树?

降低磁盘读写代价：

因为b+树的分支节点存储都是索引值，并没有数据值，在一块相同的磁盘读取到的索引值数量更多，从而降低磁盘io次数

查询效率更加稳定：

因为数据存储在主键索引的叶子节点。所以每次查询时候都会从根节点到叶子节点获取数据。每次查询数据路径长度相同，每一个数据查询效率相当。

方便扫库：

因为数据全部存储在b+树的叶子节点，而且是双链表的，所以进行扫库，仅仅扫描下叶子节点就可以解决。

二 如何创建索引，创建索引需要注意什么?

对查询频率高，数据量大的表建立索引。使用短索引，因为索引创建后也是磁盘存储，短索引也可以降低磁盘的IO. 将where条件后面的创建组合索引，注意最左前缀。尽量使用唯一索引，效率高。

1 如何查询sql执行计划(慢查询执行情况)?执行计划的各个字段表示什么含义?type一般到什么级别合理?

使用explain + sql来查看sql执行计划。主要字段如下：id: 查询序列号。select_type: 查询类型(简单表，关联查询、子查询) table: 查询表 type: 访问类型，查询索引执行情况，最重要的指标 possible_keys: 可能使用索引 key: 实际使用索引 key_len: 索引长度 rows：扫描函数 extra: 执行情况描述。

查询type最少保证range级别，最好达到ref级别。

2 索引失效，举例sql失效场景，为什么?(in、联合索引失效、like、二级索引失效情况)

like查询使用%开头出现索引失效，尾部模糊匹配索引有效。在组合索引中，索引含有多个列。查询从最左的索引列开始，中间不能跳过索引列，即左前缀法则。如果违反最左前缀法则索引失效。在索引列上进行运算操作，导致索引失效。字符串不加单引号，也会索引失效。用or进行查询时候，or前有索引后没索引。则查询时候索引失效。

3 如何优化sql查询?

对大数据量的表，如果能使用唯一索引，加入唯一索引。尽量使用组合索引形成索引覆盖。防止回表查询，提高性能。count优化：因为很多业务中都有分页操作，然而数据量大，需要遍历全表计数统计，效率低。可以在分页时候不展示总记录数，或者redis维护总记录数，在新增和删除时候对数据进行动态更新。order by优化：使用有序的数据进行排序，不需要额外排序，效率高。group by优化：实际也会排序，可以执行order by null 禁止 排序。

4 MySQL中有哪些锁?什么时候走行锁，什么时候走表锁?InnoDB锁算法?

按照操作方式分类：共享锁(读)、排它锁(写)。select不会默认加共享锁，需要手动加锁。按照锁粒度分：行级锁、表锁。按照使用方式分：乐观锁、悲观锁。

InnoDB支持行锁和表锁，手动添加排它锁，如果该字段是索引是行级锁，如果该字段不是索引是表锁。MyISAM仅支持表锁，需要手动添加表锁。

InnoDB锁算法如下：记录锁、间隙锁、next-key

5 MySQL锁粒度?什么是 GAP Lock ?

锁分为表锁行锁，行锁分为记录锁、间隙锁(gap lock)、next-key临键锁(记录锁和间隙锁)。gap lok又叫做间隙锁

6 MySQL的MVCC(多版本并发控制)是什么?是为了解决什么问题而提出来的?如何实现?

多版本并发控制，保存数据历史版本。通过版本号来决定数据是否显示。通过不加锁来实现事务的隔离级别。一般解决不可重复读、幻读，是采用锁机制。mvcc的机制就是用来解决这个问题，取代锁，降低系统开销。

mvcc是由隐藏字段(trx_id、row_id、roll_ptr)、undo log(存放数据历史版本)、read view(trx_list事务链表、up_limit_id事务中最小id、low_limit_id事务待分配id)实现。事务的id是依次递增的，在每次快照读时候会生成一个read view。然后根据当前行最后修改的事务id,和trx_list列表比较。如果trx_id小于trx_list列表最小值，说明说明当前的read view创建前，数据修改操作已完成，即读取最新数据。如果trx_id大于等于trx_list列表待分配事务，说明产生read view时候，修改事务还没提交，即读取不到最后修改数据的值。如果trx_id大于等于trx_list最小值，小于trx_list最大值，如果trx_id在trx_list中，说明产生read view没有提交事务，则读取不到被修改数据。如果如果trx_id不在trx_list中，说明read view创建时候，trx_id已提交，可以读取到修改数据。

7 MySQL中死锁什么?举几个例子。

两个或者多个进程之间存在资源互斥。现实开发目前没有。

8 MySQL具体什么时候加锁，加什么锁?

在mysql中任何隔离级别，简单select不添加锁，在insert、update、delete添加排它锁(写锁)。

三 主从复制实现原理

1. MySQL中数据丢失如何恢复?具体什么操作?

2. MySQL分布式锁如何实现?

• 基于版本号和时间戳来实现。

3.MySQL一致性哈希?

4. MySQL 如何平滑扩展?

5. 用过哪些sql函数?

• avg、count、max、min、sum

6. MySQL的三大范式?

第一范式：

• 每一列都是最小的不可再分割的，满足第一范式。

第二范式：

• 满足第一范式基础上，所有非主属性必须完全依赖主属性(可以是一个单独属性，也可以是多个属性组合)

第三范式：

• 满足第二范式基础上，属性不依赖其他非主属性。

7. 磁盘上表的存储数据格式?

8. MySQL的char和varchar区别、效率?为什么有了varchar还要char?

varchar设置字段，字段长度可以发生改变，相比char效率更高。char在设置字段长度是不可以改变。对一些数据存储长度相似，使用char，数据内存一次性分配完毕，减少内存碎片化。

9. MySQL 存储引擎 InnoDB 和 MylSAM 的区别

InnoDB ：

• 支持事务和行级锁、支持外键而且数据和索引是在一起的。

MylSAM:

• 不支持事务，支持表锁，数据和索引是分开的。