MySQL面试常见问题

本文主要是介绍MySQL面试常见问题，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

为什么要用B+树？

1. 使用 B+ 树可以减少访问磁盘的次数。如果B+树的一个节点存储1200个左右的索引数据，这个树高是4层的话，那么这个索引树就可以存储1200 的 3 次方个值，这已经 17 亿行记录。而索引树的前两层在内存中，这样很大的减少访问磁盘的次数。

2. B+树的查询性能稳定。每次查询的路径都是从根节点到叶子节点。

3. 所有的叶子节点之间是按照从小到大的顺序连接的，当进行范围查询的时候，只要找到最小的值就可以顺序找到其他的数据。

脏读&幻读&不可重复读

幻读：事务A第一次查询到了数据，这时事务B插入一条数据，事务A在做第二次查询比第一次查询多一条数据，这就是幻读。

脏读：事务A读取到被事务B修改的数据，但是事务B进行了roll back 回滚，这时候事务A读取到的就是脏数据，这就是脏读。

不可重复读：事务A读取到了数据，在事务A执行的过程中，事务B修改了这条数据，导致事务A再次读这条数据的时候发现数据不一致，这就是不可重复读。

事务隔离级别

1. 读未提交 : 一个事务还没提交时，它做的变更就能被别的事务看到。

2. 读提交: 一个事务提交之后，它做的变更才会被其他事务看到。

3. 可重复读 : （InnoDB默认可重复读）一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

4. 串行化 (xing) : 对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

如何根据使用场景选择合适的事务隔离级别？

内连接&外连接

2. 内连接（...inner join...）：当多表关联做等值查询的时候，内连接会返回两个表中所有符合等值查询的结果。

3. 左外连接（...left join .. on..）：当多表关联做等值查询的时候，是以左表为主表，查询的结果不只会返回等值条件的结果。还会返回左表中没有匹配到等值条件的结果，对应的结果集中右表中的字段会显示为null

4. 右外连接（...right join...on...）：当多表关联做等值查询的时候，是以右表为主表，查询的结果不只会返回等值条件的结果。还会返回右表中没有匹配到等值条件的结果，对应的结果集中左表中的字段会显示为null。

5. 全外连接（...full join... on...）：当多表关联做等值查询的时候，结果集不只会返回所有符合等值条件的结果，还会返回这两个表没有匹配到等值条件的所有行，对应的另一个表中的字段会显示为null。

bin log & redo log

bin log

• 逻辑日志，存储的是更新操作。

redo log

• 物理日志，存储的是数据表的更新。

• 存储引擎层的日志

• 预写式日志 （WAL：Write-Ahead Logging）

  • 当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

• redo log 是循环写的，大下是固定的，write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

• crash-safe

  • 数据库发生异常重启，之前提交的记录都不会丢失

  

SQL调优的思路

主要从索引方面考虑。

如果表比较大，我们需要根据查询条件建立适当的索引。

加索引的时候需要从这几方面考虑，以得到最优的查询效率。

1. 尽量满足覆盖索引，减少回表的次数。也就是尽量使在建立的索引树上就有我们需要的字段，就不需要会表了。

2. 考虑组建联合索引，并且将区分度高的放在最左边，同时需要考虑最左匹配原则。

3. 对所有进行函数操作或者表达式计算会导致索引失效。

4. 每次都使用explain命令查看sql的执行计划，看看自己写的sql是否符合走了索引，走了什么索引。

从锁的角度考虑：在一个事务中要可能的减少锁的持有时间，比如事务内需要进行插入数据和修改数据，那可以先插入后修改，因为修改是更新操作，会加行锁，在并发下可能会导致多个事务的请求等待行锁的释放。

读写分离

推荐阅读：https://snailclimb.gitee.io/javaguide/#/?id=%e8%af%bb%e5%86%99%e5%88%86%e7%a6%bbamp%e5%88%86%e5%ba%93%e5%88%86%e8%a1%a8

什么是读写分离？

读写分离就是对主数据库只进行写操作，从数据库只进行读操作，并且通过主从复制，保证主数据库和从数据库之间的数据一致。这样就减少了数据库读写锁之间的阻塞等待，分摊了单一数据库的压力，提升了性能（对读操作性能提升很大，对写操作有较小的性能提升）。

MySQL主从复制的原理

1. 主数据库开启日志功能后，执行的SQL命令会记录在binlog日志中

2. 从数据库使用主数据库提供给他的账号连接到主数据库

3. 从数据库会创建一个 I/O 线程向主库请求更新的 binlog

4. 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收

5. 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。

6. 从库的 SQL 线程读取 relay log ，执行相同的 SQL 语句。

怎么实现读写分离？

使用数据库中间件mycat实现读写分离：在mycat中配置主数据库和从数据库，mycat 会帮我自动实现读写分离（读操作访问从数据库，写操作访问主数据库）。

分库分表

读写分离可以有效解决数据库读操作的高并发问题，但是没有解决数据库的存储压力大的问题。

分库

就是将数据库中的表分别放在不用的数据库中。例如：将数据库中的用户表和用户订单表分别放在两个不同的数据库。或者，先对用户表中的数据水平切分，然后将切分后的两张用户表放两个不同的数据库中。

分表

就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直拆分：对数据表列的拆分，把一张列比较多的表拆分为多张表。

水平拆分：对数据表行的拆分，把一张行比较多的表拆分为多张表。

什么情况下需要分库分表？

• 单表的数据达到千万级别以上，数据库读写速度比较缓慢（分表）。

• 数据库中的数据占用的空间越来越大，备份时间越来越长（分库）。

• 应用的并发量太大（分库）。

分库分表会带来什么问题呢？

• join 操作 ： 同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。

• 事务问题 ：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。

• 分布式 id ：分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 id 了。

• ......

另外，引入分库分表之后，一般需要 DBA 的参与，同时还需要更多的数据库服务器，这些都属于成本。

分库分表后，数据怎么迁移呢？

分库分表之后，我们如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

比较简单同时也是非常常用的方案就是停机迁移，写个脚本老库的数据写到新库中。比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。然后，你写一个脚本将老库的数据都同步到新库中。

如果你不想停机迁移数据的话，也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景，实现起来要稍微麻烦一些。具体原理是这样的：

• 我们对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。 这样就能保证，咱们新库里的数据是最新的。

• 在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。

• 重复上一步的操作，直到老库和新库的数据一致为止。

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

分库分表有没有什么比较推荐的方案？

ShardingSphere 项目（包括 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar）是当当捐入 Apache 的，目前主要由京东数科的一些巨佬维护。

ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。

这篇关于MySQL面试常见问题的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！