测试面试题集-MySQL数据库灵魂拷问加强版-技术圈

VOL 168

2020-10

今天距2021年70天

这是ITester软件测试小栈第168次推文

点击上方蓝字“ITester软件测试小栈“关注我，每周一、三、五早上 08:30准时推送，每月不定期赠送技术书籍。

微信公众号后台回复“资源”、“测试工具包”领取测试资源，回复“微信群”一起进群打怪。

本文4948字，阅读约需13分钟

在上一篇测试面试题集：测试面试题集-MySQL数据库灵魂拷问，主要是对MySQL的基础拷问，以下为MySQL加强版。

MySQL是如何实现 ACID 特性的？

A=原子性：undo log来保证原子性，异常或执行失败后进行回滚；

C=一致性：事务的最终目的，即需要数据库层面保证，又需要应用层面进行保证；

I=隔离性：事务间的读写靠MySQL的锁机制来保证隔离，事务间的写操作靠MVCC机制（快照读、当前读）来保证隔离性；

D=持久性：redo log和binlog来保证持久性，确保当MySQL宕机或停电后，可以通过redo log最终将数据保存至磁盘中；

MySQL如何做大表DDL？

MySQL 5.7 已经支持原生在线DDL语句，但是涉及到一些参数配置，并可能不知道配置多少合适，所以一般大表还是使用percona-tools。

MySQL主从复制的原理和好处？

主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

MySQL主从复制的`好处`：

在业务复杂的系统中，假如有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
做数据的热备。
有利于架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL主从复制的`原理`：

从库IO线程请求主库的binlog，主库binlog dump线程写binlog，推送给从库，从库IO 线程接受binlog并把写入本地的relay log，从库SQL线程回访relay log中的内容。

MySQL内部的线程，各自负责什么工作？

master主线程：innodb1.2 版本负责插入缓冲；
purge线程：负责undo页回收；
page clean线程：负责脏页刷新；
redo log线程：将日志缓存的内容刷新到redo log文件中；
change buffer线程：将插入缓冲中的内容刷新到磁盘；
purge线程：删除无用的undo页；
error monitor线程：负责数据库报错的监控线程；
lock monitor线程：负责锁的监控线程；

Explain执行计划中要关注哪些要素？

type：查询表联接类型，从这里可以看到本次查询大概的效率；
key：最终选择的索引，如果没有索引的话，本次查询效率通常很差；
key_len：实际用上的索引长度（很多情况下，索引不一定会全部使用上，通过长度判断）；
rows：预计需要扫描的记录数，预计需要扫描的记录数越小越好；
extra：额外附加信息，主要确认是否出现 Using filesort、Using temporary 类似情况；

MySQL主从复制延迟原因与表现？

主从复制延迟的原因：

一个服务器开放Ｎ个链接给客户端来连接，这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个，当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致，即主从延迟。MySQL提供了从服务器状态命令，可以通过 show slave status 进行查看，比如可以查看Seconds_Behind_Master参数的值来判断，是否有发生主从延时。

主从复制延迟的表现：

网络延迟：Read_Master_Log_Pos 变化非常慢，Seconds_Behind_Master逐步加大；
大表DDL：Read_Master_Log_Pos不变，status 显示正在alter 表，Seconds_Behind_Master逐步加大；
大事务：Read_Master_Log_Pos；

MySQL备份原理？

首先记录开始的LSN（全备的话就是从0开始，增备的话从指定路径，或者从表中获取）并启动一个xtrabackup_log后台检测的 fork进程，实时检测mysql redo的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中；
复制innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方(流式备份就没有这个目录咯)；
复制结束后，执行flush table with read lock操作（5.7以及之前） 8.0使用备份锁，所以percona工具xtrabackup8.0只能备份mysql8.0；
复制.frm .myd .myi文件，并在这一时刻获得binary log 的位置；
将表进行解锁unlock tables （8.0 使用 UNLOCK INSTANCE）；
停止xtrabackup_log进程；

MySQL锁到底锁住了什么？

MySQL中的锁机制是为了解决共享资源并发访问的问题，从不同程度控制资源的读写，以保证数据库的完整性和一致性，MySQL同时锁住了主键与辅助索引。

谈谈MySQL锁超时，以及如何避免死锁？

MySQL锁超时排查：

查出的线程杀死：

kill SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

设置锁的超时时间：Innodb 行锁的等待时间（单位秒）。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值，该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：
```
set innodb_lock_wait_timeout=1000; 
```
设置当前会话 Innodb 行锁等待超时时间，单位秒。

MySQL避免死锁：

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

MySQL锁的优化策略？

读写分离；
分段加锁；
减少锁持有的时间；
多个线程尽量以相同的顺序去获取资源；

MySQL有哪些日志，请简要介绍？

重做日志（redo log）：确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
回滚日志（undo log）：保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
二进制日志（binlog）：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
错误日志（errorlog）：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。
慢查询日志（slow query log）：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。
一般查询日志（general log）：记录了服务器接收到的每一个查询或是命令，无论这些查询或是命令是否正确甚至是否包含语法错误，general log 都会将其记录下来，记录的格式为 {Time ，Id ，Command，Argument }。也正因为mysql服务器需要不断地记录日志，开启General log会产生不小的系统开销。因此，Mysql默认是把General log关闭的。
中继日志（relay log）：主从复制时使用的日志。

重做日志和二进制日志的区别？

涉及存储引擎不一样：binlog记录的是所有存储引擎的操作记录 redo log只记录innodb存储引擎的日志；
记录内容不一样：binlog记录的是关于一个事务的具体操作内容。为逻辑日志而redo log记录的是每个页更改的物理情况；
写的时间不一样：binlog文件仅在事务提交前进行提交，即只写磁盘一次而在事务进行过程中，却不断有重做日志条目被写入到重做日志文件中；

重做日志和二进制日志如何保证一致性？

MySQL通过两阶段提交(内部XA的两阶段提交)很好地解决了这一问题：

第一阶段：协调者向所有参与者发送事务内容，询问是否可以执行事务提交操作，并开始等待各参与者响应。事务操作，资源管理器此时会将undo日志和redo日志计入事务日志中。如果参与者成功执行了事务操作，那么就反馈给协调者Yes响应，表示事务可以执行；如果参与者没有成功执行事务，那么就反馈给协调者No响应，表示事务不可以执行。
第二阶段：提交或者中断事务。

MySQL半同步，5.6、5.7 半同步的区别？