interview mysql
list
- 索引
- 日志
- 事务
- 锁
- MVCC
- 主从复制
- 分库分表
- 扩容和缩容
- 常见问题
- 索引失效场景
- 生产隔离为什么RC而非RR
索引
-
优缺点
-
索引类型:主键索引、唯一索引、普通索引、组合索引、全文索引
-
字符串列的前缀索引
-
组合索引的最左前缀原则
-
聚簇索引、回表、覆盖索引、索引下推
索引下推:对索引包含的字段先做判断,过滤不符合条件的记录,再进行回表
-
-
数据结构:B+Tree、Hash
日志
类型 | 内容 | 补充 |
---|---|---|
错误日志 | 启停日志;错误信息… | |
查询日志 | 所有命令 | 建议调试才开启 |
慢查询日志 | 记录超过指定时间的语句 | |
事务日志 | redo和undo | 保证事务ACID |
二进制日志 | 结构变更;数据修改 | 数据恢复;主从复制… |
中继日志 | 主从复制 |
事务日志
redo log
-
WAL机制:先写redo log buffer,更新内存,事务commit完成
-
redo log是顺序IO
-
redo log buffer写redo logfile,先写OS Buffer,然后系统调用fsync刷盘
innodb_flush_log_at_trx_commit: 0延迟写,1实时写+实时刷新,2实时写+延迟刷新
延迟写:仅写log buffer,每秒写OS buffer
实时写+实时刷新:写log buffer,写OS buffer,fsync刷盘
实时写+延迟刷新:写log buffer,写OS buffer,每秒fsync刷盘
-
后台线程将数据异步写到磁盘,随机IO
-
-
文件结构:固定大小,如配置一组为4个1G文件,一共可记录4G,循环写
-
作用:保证事务完整,持久性
undo log
- 作用:
- 失败进行回滚,原子性
- MVCC版本链
二进制日志
内容:记录数据库结构变更、表数据修改的所有操作
作用:数据恢复、主从复制…
补充:
- 默认关闭,需配置开启
- 二进制文件名称数字部分(6位数)循环
格式:
- statement:原生sql
- row:实际数据
- mixed:优先基于statement,基于语句的无法精确复制时采用基于row的复制
update和写日志
开启事务,先undo,写,后redo,提交时redo刷盘?
redo和binlog两阶段提交
事务提交时,redo log和binary log两阶段提交,避免redo恢复的数据和binary恢复的数据不一致。
- prepare:redo写buffer并fsync,打上prepare标示
- commit:binary写buffer并fsync,redo改为commit
能否去掉一个日志?
- redo物理日志,binlog逻辑日志,binlog代替不了redo?
- redo循环写,不能用于数据恢复
事务
-
定义
-
ACID
-
并发事务的问题
-
并发写写问题:丢失修改
-
并发读写问题:脏读、不可重复读、幻读
-
-
隔离级别
-
SQL标准:RU、RC、RR、Serializable
-
mysql的隔离级别
-
由锁和MVCC共同实现
-
默认隔离级别是:RR(当前读加锁避免幻读)
-
-
锁
锁:共享锁和排他锁
粒度:表锁和行锁
意向锁:todo
基于锁实现隔离级别:三级封锁协议、两阶段协议(标准意义上的隔离级别)
协议 | 内容 | 解决问题 | 隔离级别 |
---|---|---|---|
一级 | 写前X,事务结束释放 | 丢失修改 | RU |
二级 | 一级之上,读前S,读完释放 | 脏读 | RC |
三级 | 一级之上,读前S,事务结束释放 | 脏读、不可重复读 | RR |
四级 | 三级之上,加表锁 | 脏读、不可重复读、幻读 | Serializable |
补充:基于锁+MVCC实现,可在RR下可解决幻读
MVCC
MVCC仅作用于RC和RR隔离级别
undo版本链:
- 隐藏列:事务ID、回滚指针
- 链:回滚指针链,头结点为最新值
ReadView:用于确认RC和RR读取的版本链的哪个节点数据
- 生成:某个时刻表记录的快照,可获取当前记录相关的事务中,哪些事务是已提交稳定事务、哪些是正活跃四五、哪些是生成快照后开启的事务
- 可见性算法:基于事务ID的比较算法
- RC和RR的ReadView:RC每次读取都生成ReadView,RR只在事务首次读取生成ReadView
快照读和当前读
-
快照读:读取MVCC的一个版本。非阻塞读
-
标准RR下,select加S锁到事务结束,可阻止其他事务的写
MySQL的RR下,select不加锁,其他事务写基于旧值(当前读,并非旧值),可能导致写覆盖,需要另外加锁(疑问:写是当前读,如何使用该数据?若先select,利用select的旧数据就行update,操作是否正确?)
-
-
当前读:读取MVCC最新版本。阻塞读
- X锁:加行锁,间隙锁(避免幻读问题)
补充:
- RC下,每次select都生成ReadView,快照读和当前读读取的都是最新版本
- RR下,首次读取生成ReadView,快照读基于该视图,当前读读取最新数据
主从复制
作用:
- 读写分离
- 故障切换
结构:一主一从、一主多从、多主多从、双主复制、级联复制
原理:
- 三个线程:
- master:log dump线程
- slave:IO线程、SQL线程
- 复制方式:
- 同步复制:性能受影响
- 异步复制:master宕机,可能丢失部分数据
- 半同步复制:折中方案
分库分表
略
常见问题
索引失效场景
- or两端包含非索引,失效
- 索引列进行运算或内置函数,失效
- where负向查询,可能失效(负向查询:NOT、!=、<>、NOT IN、NOT LIKE等)
- like左模糊,失效
- 隐式类型转换,失效
生产环境,隔离级别为什么选择RC
-
5.0以前,主从复制只能基于statement,RC可能导致主从不一致的问题,所以需要选择RR
不一致问题:两个事务t1,t2
- t1:插入、删除、后提交
- t2:插入、先提交
RC下,基于statement的主从复制,slave可能t2插入的数据也被t1删除,产生不一致
-
5.0以后,主从复制可以基于row,RC+row可以避免主从不一致问题,所以既可以RR也可以RC+row
-
RC和RR,生产下推荐RC,原因:
- RC性能比RR好
- RR死锁概率更高
- RC读取其他事务已提交数据影响不大