mysql经典笔试题及答案_mysql面试~经典题目

一 mysql双写( 目标机械盘)

1 double write是什么

1 内存中开启的一个连续空间

2 共享表空间 ibdata中的一个缓存区间

3 大小2M,由2个段构成(每个段64个页 一共128个页)

目的 是为了避免页损坏/页中断后无法恢复的情况,实现原子写特性

2 描述下double write的具体过程

1 每次脏也刷新 先通过函数拷贝到这个连续空间,然后通过这个空间刷新

2 先把doublewrite buffer的页数据写入到共享表空间ibdata1磁盘上(顺序写刷新页,数据文件的缓存,最多一次性刷新64个页(1个段))

3 再把double write buffer的页数据写入各个表空间(每次以512字节写入,防止页损坏，随机写)

3 为什么mysql需要双写

mysql数据页是16K 而linux系统的数据页是4K

16K的数据，写入4K 时，发生了系统断电/os crash ，只有一部分写是成功的，这种情况下就是 partial page write 问题。(页损坏,页中断) 重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

4 双写带来的问题

1 会导致系统有更多的fsync操作, 而硬盘的fsync性能是很慢的, 所以它会降低mysql的整体性能

5 为什么日志不需要双写

1　因为redolog写入的单位就是512字节，也就是磁盘IO的最小单位，所以无所谓数据损坏

6 为什么SSD不需要开启双写

因为SSD能保证16KB数据的原子性写入。

二 MDL锁

1 MDL锁简介

MDL锁是server层的锁,目的是保护保护表的元数据,防止表的元数据在事务活动期间被更改,导致问题 MDL锁是针对语句级别的,并非事务本身

2 MDL 锁分类

SRO锁(MDL_SHARED_READ_ONLY) -> 对应操作 lock table read 说明

可阻止所有尝试更新表内容数据和元数据，持有这种锁的连接可以读取表元数据并读取表数据。

RW锁 (MDL_SHARED_WRITE)-> 对应操作 DML

说明 当执行DML语句会申请,优先级低于SRO

RA锁(MDL_SHARED_READ)->对应操作 SELECT

说明 读取表的数据/元数据锁.,而且操作类型不能是DDL和lock table

MDL_SHARED_UPGRADABLE-》对应操作 DDL

说明共享元数据锁，用于需要从表中读取数据的情况 并阻止对其进行所有并发修改(针对数据和元数据)。

3 MDL 申请对象

1 SELECT 申请意向( db级别EXCLUSIVE锁) RA锁 释放意向(db级别EXCLUSIVE锁) RA读锁

2 DML操作 申请意向(db级别EXCLUSIVE锁) RW锁 释放意向(db级别EXCLUSIVE锁) RW锁

3 针对DDL操作 分为阻塞和不阻塞 请求关键锁(MDL_EXCLUSIVE 排他锁,可以修改字典和数据 阻止其他DDL操作).MDL_EXCLUSIVE与 读锁和写锁 互斥,不能同时拥有

4 全局性操作 申请 SRO锁,全局锁定

4 为什么发生DDL阻塞

一旦有操作无法获取应有的MDL锁,就会陷入等待状态,导致后续都会变成串行化操作(实际生产环境常见于大量查询操作)

事务1 查询 获取 SHARED_READ 事务2 为DDL操作,需要获取(MDL_EXCLUSIVE)，这两者是互斥的,事务1 不释放 ,DDL会一直处于等待状态,导致后续串行化

三 ahi

0 目的: 建立数据页与索引对应的关联性,避免从根节点再进行扫描,加速查询

1 AHI的具体结构是什么

满足 AHI 缓存建立的条件后，记录 hash value(索引的前缀索引hash值) -> page block(热点数据页) 之间的对应关系

2 AHI构建的条件

关键词 AHI成功次数 前缀索引信息

在数据页 block 上使用 AHI 成功的次数大于此数据页上用户记录的 1/16 且当前前缀索引的条件下使用 AHI 成功的次数大于 100 时， 如果此数据页使用 AHI 潜在成功的次数大于 2 倍该数据页上的用户记录或者当前推荐的前缀索引信息发生了变化的时，则需要为数据页构造 AHI 缓存信息

3 AHI 注意点

所以在打开 AHI 的系统中不建议经常变换查询条件,因为每次会进行判断,如果查询条件判断没有变化,就会增加潜在利用AHI的次数,反之,则会清空状态,重新进行统计