MySQL高频面试题－划重点、敲难点

MySQL的体系结构？

InnoDB的体系结构？

可先谈谈MySQL的体系结构，再从以下三个方面介绍InnoDB体系结构：

• 内存
  包含：insert_buffer、data_buffer、index_buffer、redo_log_buffer、double_write
  刷新到磁盘的机制：redo log buffer、脏页、binlog catch
• 线程
  master_thread、purgr_thread、redo log thread、read thread、write thread、page cleaner thread
• 磁盘
  存放数据的文件：redo log、undo log、binlog

MySQL主要存储引擎MyISAM与InnoDB的区别？

• 事务：InnoDB支持事务，MyISAM不支持
• 锁力度：InnoDB行锁，MyISAM表锁
• 存储空间：InnoDB既缓存索引文件，又缓存数据文件，MyISAM只缓存索引文件
• 存储结构：InnoDB所有表都保存在同一个数据文件里，MyISAM数据文件扩展名为.MYD myData，索引文件的扩展名是.MYI myIndex
• 统计记录行数：select count(*) InnoDB便利全表，MyISAM保存有表的总行数，可直接取值使用

MySQL两阶段提交过程？

• 准备提交（transaction prepare）：事务SQL语句先写入redo log buffer，然后做一个事务准备标记，再将log buffer中的数据刷新到redo log
• 提交阶段（commit）：将事务产生的binlog写入文件，刷入磁盘

InnoDB的三大特性？

• 插入缓存（change buffer）
  作用：把普通索引上的DML操作从随机I/O变成顺序I/O，提高I/O效率
  原理：判断插入的普通索引是否在缓冲池中，在则直接插入，不在则先放到change buffer中，然后进行change buffer和普通索引的合并操作，将多个插入合并到一个操作中，提高普通索引的插入性能
  

• 两次写（double write）
  作用：保证数据写入安全，防止在MySQL实例发生宕机时，发生数据页部分页写（partial page write）的问题。

• 自适应哈希索引（adaptive hash index）

MySQL有哪些索引类型？

• 数据结构角度：B＋tree索引、hash索引、fulltext索引
• 存储角度：聚簇索引和非聚簇索引
• 逻辑角度：primary key、normal key、单例、复合和覆盖索引

为什么MySQL默认文件16K？

• 假设一行数据大小为1K，那么一页就能存16条数据，即一个叶子节点能存16条数据；
• 对于非叶子节点，假设ID为bigint类型，则长度为8B，指针大小在InnoDB源码中为6B，共14B，那么一页就可以存储16KB/14B=1170个（主键＋指针）。
• 一颗高度为2的B+树能存储的数据为：1170*16=18720条（万级数据）
• 一颗高度为3的B+树能存储的数据为：1170＊1170*16=21902400条（千万级数据）

表中只有一个字段 VARCHAR(N)类型，utf8 编码，则 N 最大值？

由于 utf8 的每个字符最多占用 3 个字节。而 MySQL 定义行的长度不能超过65535，因此 N 的最大值计算方法为：(65535-1-2)/3。

• 减去 1 的原因是实际存储从第二个字节开始，
• 减去 2 的原因是因为要在列表长度存储实际的字符长度，
• 除以 3 是因为 utf8 限制：每个字符最多占用 3 个字节。

自增表有id 从1到5的5条数据，删除最后两条数据（id为4和5）后重启MySQL服务器，又新增一条数据，问新增数据的id？

• 如果表为 MyISAM 引擎，那么 id 就是 6
• 如果是 InnoDB 引擎，在 MySQL 8.0之前， id 就是 4， 8.0之后 InnoDB 会把索引持久化到日志中，重启服务之后自增索引是不会丢失的，即 id 为 6

MySQL 文件？

• 参数文件
• 错误日志文件（error log）
• 二进制日志文件（binary log）
• 慢查询日志文件（slow log）
• 全量日志文件（general log）
• 中继日志文件（relay log）
• Pid文件：单进程多线程DB会将自己唯一的进程号记录到Pid文件中
• Socker文件：网络连接和本地连接两种连接方式
• 表结构文件
• InnoDB存储引擎文件：redo和undo两种日志文件

MySQL binlog有几种格式？

• statement格式，生产环境不建议使用
  优点：不需要记录每一行的变化，减少了binlog日志量，节约I/O，提高性能
  缺点：使用特殊函数或跨库操作时容易丢失数据
• row格式，生产环境建议使用
  优点：记录每行数据信息，安全性高
  缺点：会产生大量binlog，网络开销也较大
• mixed格式，生产环境不建议使用
  MySQL5.1的一个过渡版本，DDL语句会记录成statement，DML会记录成row

undo log和redo log是什么？作用？

undo log和redo log是mysql中InnoDB存储引擎的基本组成

• undo log保存了事务执⾏前数据的值，以便于事务回滚时能回到事务执⾏前的数据版本，多次更 新会有undo log的版本链
• redo log在物理层⾯上记录了事务操作的⼀系列信息，保证就算遇到mysql宕机等因素还没来得 及将数据刷到磁盘⾥，通过redo log也能恢复事务提交的数据。

redo log怎样保证事务不丢失的？

当⼀个事务提交成功后，虽然缓冲池中的数据不⼀定来得及⻢上落地到磁盘中，但是redo log记录的 事务信息持久化到磁盘中了、且含有commit标记，此时如果mysql宕机导致缓冲池中的、已经被事务更新 过的内存数据丢失了，此时在mysql重启时，将磁盘中的redo log中将事务变更信息给加载到缓冲池中， 保证事务信息不会丢失。或者redo log刷盘了，binlog写成功了，在重启时会⾃动给上commit标记，在重放数据。

事务是先提交还是先刷盘？

事务先提交后刷盘

• Redo log刷盘成功
• Binlog刷盘
• BinLog名称和⽂件路径信息、commit标志写到Redo log 中，事务两阶段提交的⽅式来保证。

更新操作为什么不直接更新磁盘反⽽设计这样⼀个复杂的InnoDB存储引擎来完成？

直接更新磁盘是随机IO写，存在磁盘地址寻址操作，性能非常低，承载不了⾼并发场景； 而转换为InnoDB中，内存高速读写、redo log和undo log顺序写磁盘性能相对于随机IO写性能会高的多，而这种性能上的提高足以抵消这种架构上带来的复杂，可在⼀定QPS内承载⾼并发场景。

mysql 支持的复制类型?

• 基于语句的复制： 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高
• 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从 mysql5.0 开始支持
• 混合类型的复制: 默认采用基于语句的复制，一旦发现无法精确的复制时，就会采用基于行的复制

MySQL主从复制的原理？

主服务器把数据更新记录到二进制日志中，从服务器通过I/O thread，向主库发起binlog请求，主服务器通过I/O dump thread 把二进制日志传递给从库，从库通过I/O thread 记录到自己的中继日志中。然后通过SQL thread应用中继日志SQL的内容

MySQL主从延迟的原理？

• MySQL 5.7之前，主库可以并发写入，但从库只能通过单SQL thread完成任务，这是出现主从延迟的核心原因
• MySQL主从同步并不是实时同步，而是异步的同步，既主库提交事务后，从库才再执行
• 主库上对没有索引大表的列进行delete或update操作
• 从库的硬件配置没有主库的好
• 网络抖动导致I/O线程复制延迟

如何监控主从延迟？

• 传统方法：通过比较主从服务器之间的position号的差异值；通过查看seconds_behind_master估算主从延迟时间
• 使用第三方工具：percona-toolkit中的pt-heartbeat命令

如何解决主从延迟问题？

• 使用MySQL 5.7的基于组提交的并行复制功能
• 采用PXC架构，可实现多节点写入，达到实时同步
• 业务规划初期选择合适的分库分表策略，避免单表或单库过大，造成复制压力
• 避免无用I/O消耗，增加高转速的磁盘、SSD或PCIE-SSD设备
• 阵列级别选择RAID 10，raid cache策略采用WB
• I/O调度选择deadline模式
• 适当调整buffer pool的大小
• 避免让数据库进行大量运算
  数据库只是用来存储数据的

数据库中的双一是什么？

sync_binlog＝1
innode_flush_log_at_trx_commit=1

这两个参数控制MySQL磁盘写入策略和数据安全性的
innode_flush_log_at_trx_commit设置为1，每次事务提交时都会把log buffer的数据写入log file并刷到磁盘中
sync_binlog＝N（N>0），在每写N次二进制日志binary log时，会使用fdatasync()函数将其写入二进制日志binary log同步到磁盘

为什么要为InnoDB表设置自增列做主键？

使用自增列做主键，写入顺序是自增的，和B＋树叶子节点分裂顺序一致。
InnoDB表的数据写入顺序能和B＋树索引的叶子节点顺序一致时，存储效率是最高的。

如何优化一条慢SQL语句？

专业的笑一来就加索引的，加索引的笑不懂的

• 回归表的设计层面，数据类型选择是否合理
• 大表碎片的整理是否完善
• 表的统计信息是不是准确的
• 审查表的执行计划，判断字段上有无合适的索引
• 针对索引的选择性，建立合适的索引

服务器负载过高或网页打开缓慢的优化思路？

首先发现问题，通过以下四个维度找到问题所在

• 操作系统
• 数据库
• 程序设计
• 硬件
  其次制定优化方案；
  再在测试环境进行优化方案的测试并记录；
  最后通过测试结果分析，找到最好的解决方案并实施

什么是死锁？锁等待？通过数据库哪些表可以监控？

死锁是指两个或多个事务在同一资源上互相占用，并请求加锁时导致的恶性循环想象。
当多个事务以不同顺序试图加锁同一资源时，就会产生死锁。
锁等待：MySQL数据库中，不同session在更新同行数据时会出现锁等待现象。
重要的三张锁的监控表：innodb_trx、innodb_locks和innodb_lock_waits

处理过MySQL的哪些案例？

可从MySQL的五大知识模块出发：

• 体系结构
• 数据的备份恢复
• 复制
• 高可用集群架构
• 优化

例如：

• MySQL版本的升级
• 处理集群架构中的各种“坑”和问题
• 根据业务合理设计库、表和后期架构
• 定期进行灾备恢复演练
• 恢复误删除的数据信息

接触过哪些MySQL的主流架构？

• M-S
• MHA
• MM-Keepalived
• PXC
• 利用中间件ProxySQL配合PXC架构