mysql语句面试题，提高面试几率

在Java相关岗位的面试中，mysql类的问题经常会被cue到，无论大家是想要进入开发岗位还是运维岗位，总会有那么相关的几道题，所以，熟知mysql语句面试题，对于日后的面试有很大的帮助，大家可以跟着小编一起在来熟悉一下：

1.MySQL 怎么知道 binlog 是完整的?

一个事务的 binlog 是有完整格式的：

statement 格式的 binlog，最后会有 COMMIT;

row 格式的 binlog，最后会有一个 XID event。

2.什么是 WAL 技术，有什么优点?

WAL，中文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。

好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过redo log 恢复，所以能够实现快速响应 SQL 语句。

3.binlog 日志的三种格式

binlog 日志有三种格式

• Statement：基于SQL语句的复制((statement-based replication,SBR))
• Row：基于行的复制。(row-based replication,RBR)
• Mixed：混合模式复制。(mixed-based replication,MBR)

Statement格式

• 每一条会修改数据的 SQL 都会记录在 binlog 中
• 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
• 缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。

Row格式

• 不记录 SQL 语句上下文相关信息，仅保存哪条记录被修改。
• 优点：binlog 中可以不记录执行的 SQL 语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。
• 缺点:可能会产生大量的日志内容。

Mixed格式

• 实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。

4.redo log日志格式

redo log buffer (内存中)是由首尾相连的四个文件组成的，它们分别是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

• write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
• checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
• write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。
• 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
• 有了 redo log，当数据库发生宕机重启后，可通过 redo log将未落盘的数据(check point之后的数据)恢复，保证已经提交的事务记录不会丢失，这种能力称为crash-safe。

5.原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么?如何解决?

原因：从大到小可分为四种情况

• MySQL 数据库本身被堵住了，比如：系统或网络资源不够。
• SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。
• 确实是索引使用不当，没有走索引。
• 表中数据的特点导致的，走了索引，但回表次数庞大。

解决：

• 考虑采用 force index 强行选择一个索引
• 考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
• 第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
• 如果确定是索引根本没必要，可以考虑删除索引。

6.InnoDB 数据页结构

一个数据页大致划分七个部分

• File Header：表示页的一些通用信息，占固定的38字节。
• page Header：表示数据页专有信息，占固定的56字节。
• inimum+Supermum：两个虚拟的伪记录，分别表示页中的最小记录和最大记录，占固定的26字节。
• User Records：真正存储我们插入的数据，大小不固定。
• Free Space：页中尚未使用的部分，大小不固定。
• Page Directory：页中某些记录的相对位置，也就是各个槽对应的记录在页面中的地址偏移量。
• File Trailer：用于检验页是否完整，占固定大小 8 字节。

数据相关

7.MySQL 是如何保证数据不丢失的?

只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据

在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据;如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

8.误删数据怎么办?

DBA 的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：

• 权限控制与分配(数据库和服务器权限)
• 制作操作规范
• 定期给开发进行培训
• 搭建延迟备库
• 做好 SQL 审计，只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核
• 做好备份。备份的话又分为两个点 (1)如果数据量比较大，用物理备份 xtrabackup。定期对数据库进行全量备份，也可以做增量备份。(2)如果数据量较少，用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的
• 如果发生了数据删除的操作，又可以从以下几个点来恢复:
• DML 误操作语句造成数据不完整或者丢失。可以通过 flashback，美团的 myflash，也是一个不错的工具，本质都差不多
• 都是先解析 binlog event，然后在进行反转。把 delete 反转为insert，insert 反转为 delete，update前后 image 对调。
• 所以必须设置binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。
• DDL语句误操作(truncate和drop)，由于DDL语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。
• 只能通过全量备份+应用 binlog 的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长
• rm 删除：使用备份跨机房，或者最好是跨城市保存。

以上就是“mysql语句面试题，提高面试几率”，你能回答上来吗?如果想要了解更多的Java面试题相关内容，可以关注动力节点Java官网。