Copy of MySQL面试题

1、一条sql语句的执行过程？

1. 客户端向MySQL服务端发送一个查询语句的请求；
2. MySQL服务端的Service层先进行处理，首先是连接器，建立客户端与服务端之间的连接，并鉴权；
3. MySQL服务端会先查询缓存，如果缓存命中，则直接返回缓存中数据页中的数据结果；否则进入下一阶段；
4. 分析器对sql文本进行解析，生成解析树；
5. 优化器对解析树进行优化，比如调整where查询条件的顺序与索引列一致，生成一个执行计划；
6. 执行器基于优化器生成的执行计划，调用存储引擎底层的api来执行，并将结果返回给客户端。

2、MySQL中 IN 和 EXISTS 的区别？

MySQL中in语句和exists语句都可以用在子查询里，如下：

select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)

• EXISTS：先对外表进行全表遍历，对外表的每一行数据去关联子表进行查询，如果子表不为空则外表查询时会使用到索引进行查询；
• IN：先执行子查询，将子查询的结果作为主查询的条件，再执行主查询。

二者查询效率比较：

• 当外表大，内表小时，用IN；
• 当外表小，内表大时，用EXISTS。

3、主键使用自增id还是uuid，为什么？

建议使用自增id作为主键。
因为在插入记录时，如果插入的数据页满了，在遵循一个数据页内记录按主键值从小到大的顺序排序，多个数据页之前也是按照主键值的顺序组成的双向链表，会新生成一个数据页，之前数据页的记录会按前面介绍的规则分布在老数据页和新数据页内，这个过程伴随着页分裂和数据从老数据页移动到新数据页的过程，是很消耗性能的。主键使用uuid上述情况就会发生，但是主键使用自增id就不会发生，因为自增id的记录插入，永远是一个数据页写满了再插入新的数据页，不会有记录在数据页之间移动的性能损耗。

4、 MySQL自增主键用完了怎么办？

1. 首先这个问题没有遇到过，因为自增主键一般用int类型，MySQL中int类型占4个字节，共32个比特位，以无符号整数来说，最大值可以到43亿，单表一般数据超过百万级别就应该考虑分库分表，因此正常情况下不会出现自增主键用完的场景。
2. 如果一定要基于这个几乎不可能出现的场景分析，一个解决方案就是将自增主键的id类型由int改为Bigint，BigInt类型占8个字节，64的比特位，数量肯定是够用的；

3. 如果修改自增主键类型，就涉及到一个如何在线修改数据库表结构的问题，这里有两个方法：

   1. 使用MySQL提供的ALTER语句，该语句在线修改表结构，不会阻塞原表的读取和写入：

      ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

   2. 采用第三方工具，比如pt-osc和gh-ost。

5、查询一条记录，基于MVCC，是怎么样的流程？

1. 获取事务自己的版本号，即事务ID
2. 获取Read View
3. 查询得到的数据，然后Read View中的事务版本号进行比较
4. 如果不符合Read View的可见性规则，即就需要Undo log中历史快照
5. 最后返回符合规则的数据

InnoDB实现MVCC，是通过Read View + Undo log实现的，Undo log保存了历史快照，Read View可见性规则帮助判断当前版本的数据是否可见。
MVCC

6、InnoDB和MyISAM的区别？

MySQL在5.0版本前的默认存储引擎使MyISAM，5.0版本后的默认存储引擎使InnoDB。

• InnoDB支持事务，MyISAM不支持事务，这也是MySQL将默认存储引擎设置为InnoDB的原因之一；
• InnoDB支持外键，MyISAM不支持外键；
• InnoDB支持行级锁和表级锁，MyISAM仅支持表级锁；
• InnoDB的聚簇索引，索引本身就是数据，而MyISAM的索引和数据是分开存放的。
• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数。

7、Mysql查询缓存

注意：MySQL 8.0取消了查询缓存这一设置。
查询缓存就是在MySQL逻辑架构中的Server层做的，一般默认查询缓存功能是关闭的，如果需要打开，可以修改MySQL的这个配置参数query_cache_type，query_cache_type参数可能的取值有三个：

MySQL 将缓存存放在一个引用表（不要理解成 table，可以认为是类似于 HashMap 的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。
既然是缓存，就会失效，那查询缓存何时失效呢？MySQL 的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL 必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

1. 任何的查询语句在开始之前都必须经过检查是否缓存命中，即使这条SQL语句永远不会命中缓存；
2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

SELECT SQL_CACHE ...

8、事务的ACID四大特性有哪些？

• 原子性（Atomicity）：针对一组操作，要么都发生，要么都不发生。原子性在MySQL中是由undo日志保证，undo日志保存了记录的操作历史，事务回滚时根据undo日志里记录的操作回滚。
• 一致性（Consistency）：事务在运行前后，对数据库的一致性约束不变。举例：现有完整性约束a+b=10，事务的发生改变了a的值，就一定要改变b的值，使事务结束后依然满足a+b=10的约束。一致性是由事务的原子性和隔离性来保证的，二者是手段，一致性是结果。
• 隔离性（Isolation）：多个事务同时执行时互不影响。MySQL中保证事务隔离性可以采用非锁机制的MVCC版本链机制，也可以加锁；

• 持久性（Durability）：事务一旦提交，它所做的操作会永久地保存在数据库上，即使数据库宕机也不会丢失。MySQL中通过redo日志保证事务的持久性，增删改操作同时在内存和redo日志中记录，事务提交的时候会将redo日志刷新到磁盘中，数据库宕机时可以通过redo日志恢复。

  9、脏写、脏读、不可重复读和幻读是指什么？InnoDB中是如何避免它们的？

• 脏写：两个写事务，事务A先更新记录，事务B后更新记录，事务A再提交，事务B再提交，此时事务A发现UPDATE后的的数据不是事务A想更新的记录，而是事务B更新的记录。由于脏写的性质最为恶劣，所有事务隔离级别都不允许脏写情况的发生，InnoDB中通过加锁（独占锁）的方式避免脏写；

• 脏读：一个事务读到了另一个未提交事务修改过的记录叫脏读，InnoDB中通过MVCC机制避免脏读；
• 不可重复读：事务A根据某个搜索条件不断地查询，事务B不断地更新满足事务A中搜索条件的记录，导致事务A每次根据相同搜索条件查询出来的记录值不一样。InnoDB中通过MVCC机制避免不可重复读；

• 幻读：事务A根据某个搜索条件不断地查询，事务B不断地插入满足事务A中搜索条件的记录，导致事务A每次根据相同搜索条件都会多查询出来记录，多出来的记录就叫做幻影记录。InnoDB针对快照读通过MVCC机制避免幻读，针对当前读通过对next-key locks加锁避免幻读。

  10、InnoDB支持哪几种事务隔离级别？

• READ UNCOMMITTED：未提交读，脏读、不可重复读和幻读都会发生；

• READ COMMITTED：可提交读，脏读不会发生，不可重复读和幻读还会发生；
• REPEATABLE READ：可重复读，SQL标准规定脏读、不可重复读不会发生，幻读还会发生，InnoDB保证了脏读、不可重复读和幻读都不会发生；
• SERIALIZABLE：可串行化，通过加独占锁的方式保证事务的串行执行，脏读、不可重复读和幻读都不会发生。

11、InnoDB存储引擎为什么采用B+树而不是B树作为索引的数据结构？

先说一下B树和B+树的结构不同：
B树：

• B树的非叶子节点和叶子节点都存储data值（data值可以理解为用户记录）；
• B树的相邻叶子结点之间没有指针连接。

B+树：

• 非叶子节点只存页目录记录；
• 叶子节点只存放具体数据记录；
• 所有叶子节点组成一个双向链表，便于遍历数据。

再答为什么B树和B+树数据结构的差异导致索引选B+树？

• B+树的非叶子节点仅存放关键字和下一层的索引，不存储具体的数据，具体的数据在叶子节点中存储，就会使非叶子节点可以存储更多的页目录记录，使整个B+树呈矮胖形，降低B+树的高度，减少磁盘IO次数，提高查询效率；
• B+树所有叶子节点之间形成一个双向链表的结构，遍历查询时效率要高；

• B+树的所有记录数据都存储在叶子节点上，B+树的查询都要从根节点走到叶子节点，使每一条数据的查询效率相当。

  12、什么是回表？

  回表是指在查询语句中，查询的字段没有被索引列完全覆盖，有些查询字段不在索引列中，此时会先到索引列对应的二级索引或者联合索引的B+树中查询，然后根据主键值再到聚簇索引的B+树中查询，将聚簇索引叶子节点保持的所用用户记录返回。到聚簇索引中再次查询的过程被称为回表。

  13、什么是索引覆盖？

  索引覆盖是指查询语句中，查询的字段都在索引列中被覆盖完全了，查询时只需要查询二级索引或者联合索引的B+树而不需要回表查询聚簇索引的B+树。

  14、什么是索引下推？

  索引下推，是MySQL 5.6版本后引入的新特性，是针对查询条件是二级索引的场景做的查询优化。具体点，在根据查询条件对二级索引进行查询时，不忙着将查询得到的二级索引记录进行回表查询，而是根据二级索引列的其他查询条件进一步过滤检查，仅将符合后者查询条件的二级索引记录进行回表，这样做减少了回表的次数，减少了随机IO，提升了查询效率。使用到了索引下推的查询语句的执行计划中，Extra列是Using index condition。

  15、什么是最左前缀原则（最左匹配原则）？

  这个是创建索引时，以及使用索引进行查询时要遵循的规则，具体故规则如下：

• 如果是多个列等值查询，查询条件用到的列必须依次是索引列的顺序，比如创建联合索引（a, b, c），想要索引生效，查询条件只能使用以下组合：a，ab，ac，abc；

• 如果是范围查询时，索引只能用到索引列中最左边的列的范围查询。

  16、什么情况下不宜建立索引？

• 表中数据量比较小的时候不要建立索引（百量级的就不需要建立索引全表扫描性价比更高，十万量级的就需要考虑建立索引了）；

• 频繁更新的字段不适合建立索引（因为每次更新字段都要维护索引记录的排序，还会伴随而来的页分裂的数据移动都会带来性能损耗）；
• 不在搜索条件、分组条件和排序条件中的字段不需要建立索引；

• 基数较小的列不适合建立索引（全表扫描性价比更高，因为要考虑回表带来的性能损耗）；

  17、什么时候建立的索引会失效？

• 对多个列建立非聚簇索引，搜索条件中第一个等值或者范围搜索条件中的列不是索引列中的最左边的列；（没有满足最左匹配原则）

• 列值可能为NULL的列建立索引可能失效；
• 当查询条件为“非”的语义时索引不会生效，此时优化器更倾向于全量查询，“非”的语义是指查询条件里有例如：<>、NOT、in、not exists；
• 模糊查询时，模糊匹配条件通配的是字符串前缀，比如”%ob”，这样索引无法比较模糊匹配中的字符串大小，如果模糊匹配条件是”jo%”通配后面的字符是可以的；
• 对索引列使用函数或者其他表达式，比如upper(name)=’SUNYANG’, number / 2 > 4；
• 当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的number类型，但是在查询时，却用了下面的形式。

• 当优化器发现需要回表的数据列过多（超过总数据了的30%），会放弃使用二级索引 + 回表的方式，而选择全表扫描的方式。

  18、InnoDB中锁的类型有哪些？

  MySQL锁
  首先可以分为共享锁和排他锁。

• 共享锁：也叫读锁，S锁，手动加S锁可以使用LOCK IN SHARE MODE的sql语句，共享锁页也分为表的S锁和记录行的S锁，以记录行的S锁为例，如果一个事务获取了某条记录的S锁，其他事务也可以获取该记录的S锁，但不能获取该记录的X锁；

• 独占锁：也叫写锁，X锁，手动加X锁可以使用FOR UPDATE的sql语句，独占锁也分为表的X锁和记录行的X锁，以记录行的X锁为例，如果一个事务获取了某条记录的X锁，其他事务不可以获取该记录的X锁，也不可以获取该记录的S锁。

按照锁的粒度的不同，又可以分为表锁和行锁。

• 表锁：是给整张表加锁的，表锁又分为表级别的S锁、X锁和表级别的IX锁和IS锁，IX和IS锁也叫意向锁，主要为了确保在加表锁之前表中没有行锁，避免通过遍历的方式去查询表中是否有上行锁：
  • 表级别的S锁：如果一个事务获取了某张表的S锁，其他事务可以继续获取该表的S锁以及表内记录行的S锁，不可以获取该表的X锁以及表内记录的X锁；
  • 表级别的X锁：如果一个事务获取了某张表的X锁，其他事务不可以获取该表的S锁和X锁，不可以获取表内记录的S锁和X锁；
  • 表级别的IS锁：当事务准备为某条记录加上S锁（行锁）时，需要先在表级别上加一个IS锁；
  • 表级别的IX锁：当事务准备为某条记录加上X锁（行锁）时，需要先在表级别上加一个IX锁。
• 行锁：行锁的粒度自然是每个记录行，InnoDB中行锁是给索引上的索引项记录加锁，行锁又分为以下几种：
  • Record Locks：给每一条具体的索引加锁，也分为共享锁和独占锁；
  • Gap Locks：又叫间隙锁，间隙锁的作用范围是某条索引项记录到它上一条索引项记录之间的范围，不包含当前索引项记录。
  • Next-key Locks：可以看做是Record Locks和Gap Locks的二合一，锁的作用范围是当前索引项记录，以及当前索引项记录到它上一条索引项记录之间的范围，InnoDB正式通过Next-key Locks锁防止当前读中的幻读发生的；
  • Insert Intention Locks：插入意向锁，事务在插入数据时，即使由于插入范围被其他事务锁住暂时阻塞住，也会在内存空间中生成一个插入意向锁，表明该事务想在某个间隙插入数据，锁的trx_id是该事务的id，is_waiting属性为true，当该事务获取到锁时，会将is_waiting属性改为false；
  • 隐式锁：一个事务首先插入了一条记录时，会将该记录的trx_id值设置为当前事务的id，此时如果其他事务向获取该记录的X锁或者S锁时，会检查一下该记录的trx_id值对应的事务是否是当前活跃的事务（未提交的事务），如果是就会帮当前事务创建一个X锁，is_waiting属性为false，自己进入等待状态（就是为自己也创建一个锁结构，is_waiting属性是true）。

19、MySQL主从复制原理

Mysql的主从复制中主要有三个线程：Master有一个线程：binlog dump thread，Slave有两个线程：I/O thread 和SQL thread。
具体步骤如下：

1. 当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中；
2. Salve从库连接master主库，Master有多少个Salve节点，Master节点就会创建多少个binlog dump线程；
3. 当Master节点的binlog发生变化时，binlog dump 线程会通知所有的Salve节点，并将相应的binlog内容推送给Salve节点；
4. Salve节点的I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log（中继日志）；
5. Salve节点的SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的回放操作。

这里有一个非常重要的一点，就是从库同步主库数据的过程（Salve节点的SQL线程）是串行化的，也就是说主库上并行的操作，在从库上会串行执行。由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。这就会造成微小时间段内的主从库的数据不一致问题。

20、MySQL主从复制方式

（1）异步复制
MySQL主从复制的默认方式就是异步复制，所谓异步复制是指MySQL会在后台另起线程去完整数据同步的工作，具体的步骤可以参考问题19。需要注意的是：对于支持事务的存储引擎来说，异步复制，是事务提交、将更改的操作写入到Master的bin log后，才向客户端返回结果。
（2）半同步复制
异步复制的机制会导致这样一个问题：当主库提交一个事务，将修改写入主库的bin log日志，之后向客户端返回，而此时从库尚未收到主库推送的这条事务对应的bin log日志，如果此时主库宕机造成主库上事务对应的bin log日志丢失，会导致从库无法同步该事务对应的bin log，会造成主从数据不一致。
为了解决这个问题，MySQL引入了半同步复制。半同步复制是指：主库在提交事务后，提交事务的线程会一直等待，直到至少有一个半同步的从库确认已接收到bin log；从库收到主库推送来的bin log后，会将其写入到从库的relay log并刷新到磁盘，之后才会向主库返回ack确认；主库收到从库返回的ack确认后，才会向客户端返回响应结果。
需要注意的是：

• 主库和从库都需要启用半同步复制模式才会进行半同步复制功能，否则主库会还原为默认额异步复制；
• 当主库等待从库返回ack的过程中超时时，也会还原为默认的异步复制，当至少有一个从库的数据赶上主库时，主库才会恢复到半同步复制模式。

（3）并行复制
并行复制又叫多线程复制。MySQL5.5版本之前不支持并行复制；5.6版本的并行复制仅是不同数据库（schema）之间是并行复制，同一个数据库中数据的复制还是串行复制；5.7版本的并行复制是基事务提交组的复制，做到了同一个数据库中的数据也可以并行复制。

• 5.6版本：并行只是基于schema的，也就是基于库的。当有多个库时多个库可以并行进行复制，而库与库之间互不干扰。其核心思想是：不同schema下的表并发提交时的数据不会相互影响，即slave节点可以对relay log中不同的schema各分配一个类似SQL功能的线程，来重放relay log中主库已经提交的事务，保持数据与主库一致。
• 5.7版本：5.7版本是基于组提交的并行复制，即同一个schema下的数据也可以并行复制，相当于把并行复制的粒度变细了一个维度。其核心思想：组与组之间提交的事务都是可以并行回放（配合binary log group commit），slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。

  21、MySQL读写分离

  读写分离是建立在主从复制的基础上，解决的问题是对数据库读多写少的问题。事务性的写操作（增、删、改）走主库，查请求走从库。需要注意的是配置好数据库的主从复制集群后并没有自带读写分离的效果，需要通过数据库中间件来实现主从集群的读写分离效果，常用的数据库中间接比如MyCat、Sharding-Sphere等，有关Shardingf-Sphere实现读写分离的文章见：https://www.yuque.com/docs/share/c1a37ca0-ccd7-4c4a-aa04-ea3dea1f1699?# 《Sharding JDBC学习笔记》

22、MySQL分库分表

垂直分库

垂直分库的定义：垂直分库是指按照业务将表和库进行分类，不同业务的微服务对应到不同的数据库上，每个库可以放在不同的服务器上（避免都放在一台服务器上使硬件资源成为系统性能瓶颈），它的核心理念是专库专用。

垂直分表

垂直分表的定义：将一张宽表（列的数目较多的表）按照字段特性（冷热、数据类型）拆分成若干小表（列数目较少的表），每个小表存放大表中部分字段。垂直分表相比水平分表一个很大的不同就是垂直分表是改变了表结构，而水平分表没有。

水平分库

水平分库的定义：水平分库是把同一个表的数据库按照一定的规则拆分到不同的数据库中，每个数据库实例可以部署在不同的环境上。垂直分库不会改变表结构，只是对表内的数据行进行拆分。

水平分表

水平分表的定义：水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

23、数据库三范式

• 第一范式：数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性；
• 第二范式（2NF）：满足 1NF 后，要求表中的所有列，都必须完全依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情；
• 第三范式：必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）；

24、binlog和redo log有什么区别？

• bin log会记录所有与数据库有关的日志记录，包括InnoDB、MyISAM等存储引擎的日志，而redo log只记InnoDB存储引擎的日志。
• 记录的内容不同，bin log记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而redo log记录的是关于每个页（Page）的更改的物理情况。
• 写入的时间不同，bin log仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有redo ertry被写入redo log中。
• 写入的方式也不相同，redo log是循环写入和擦除，bin log是追加写入，不会覆盖已经写的文件。

25、where和having的区别

• Where是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用聚合函数
• Having是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用聚合函数。
• 注意：HAVING不是一定要和GROUP BY配合使用。