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架构
end
  • 作者:suoyue_zhan(联系作者)
  • 发表时间:2021-02-01 13:34:44
  • 版权声明:自由转载-非商用-非衍生-保持署名(创意共享3.0许可证)
  • 转载声明:如果是转载栈主转载的文章,请附上原文链接
  • 公众号转载:请在文末添加作者公众号二维码(公众号二维码见右边,欢迎关注)
  • 评论

    JustTes2020
    test