伪MySQL面试宝典（一）

简单说明：

以下信息整理至网络博客，是总结汇总，后期看情况可能有后续博文，不确定

数据库事务ACID：

原子性(Atomic)：事务中的多个操作，不可分割，要么都成功，要么都失败
一致性(Consistency)：事务是由单条或者多条DML组成
    事务发生后，表结构、约束，和事务不相关的表对象等数据和结构保持不变
    例1，表某字段有主键约束，事务DML不会造成主键失效，并且不满足主键唯一的约束时，事务失败
    例2，create table testabc ( n1 number,n2 number,check(n1+n2=10));
    当事务update改变了n1的值，而没有改变n2的值，check约束不满足，则事务失败
隔离性(Isolation)： 多个事务之间就像是串行执行一样，不相互影响
持久性(Durability)：事务提交后被持久化到永久存储，不会丢失也不是回滚

ACID中隔离性最复杂：

READ UNCOMMITTED：RU，可以读未提交数据，未提交数据为脏数据，即可脏读，也就是事务之间没有隔离
                  不允许重复读，允许幻读和脏读
READ COMMITTED：  RC，只能读取已经提交的数据，事务之间隔离，数据提交后可被其他事务读取
                  允许幻读，不允许重复读，不允许脏读
REPEATABLE READ： RR，可重复读，一个事物中多次执行同一个select，数据不变，MVCC实现
                  允许幻读和可重复读，不允许脏读
SERIALIZABLE:     不允许脏读和幻读，可重复读，相当于单会话单事务库

关于隔离性的几个概念：

脏读：其他事务可以读取未提交数据
重复读：一个事物中多次执行同一个select，数据不变叫可重复读，多版本并发控制MVCC实现
        数据变化(其他会话的其他事务做了update并提交)，叫不可重复读
幻读：一个事务中多次执行同一个select, 读取到的数据行发生改变，行数减少或者增加
        其他会话的其他事务做了insert并且提交
重复读和幻读区别：
重复读主要是并发会话的update操作是否对当前会话造成影响
幻读主要是并发会话的delete/insert操作是否对当前会话造成影响

RC和RR比较：

Oracle默认RC，并且实现了SERIALIZABLE(基于undo的闪回版本查询)

MySQL默认事务隔离级别是RR，使用多版本并发控制MVCC实现

MySQL使用gap lock+row lock形成的next-key lock来尽量避免幻读
SERIALIZABLE使用读和写均加锁的方式实现

也有很多场景将MySQL的事务隔离级别从RR降级成RC的，RC和RR比较如下：
RR在RC的基础上加了gap lock，它和RC的row lock形成的next-key lock锁
所以RC的并发好于RR，锁少并发高，隔离性差
比如 一个会话 delete 一张表，where条件有两个，第一个限定了影响范围是5条数据
第二个限定了5条数据中的确定1行，如果是RC的话，row lock只会锁最终被delete的那一行
如果RR的话，会话不提交，那么根据第一个条件筛选出来的记录以及记录前后的记录都会被锁定
这个锁是gap lock造成的，然后事务提交或回滚后才会释放，这就导致了被gap lock锁定的记录无法被delete掉
这就是RR模式下使用gap lock来尽量避免幻读的情况，gap lock出现于范围过滤条件

多版本并发控制MVCC：

MySQL的默认事务隔离级别是RR，由多版本并发控制MVCC实现
1，每一行记录后面都有隐藏的两列，记录创建版本号和删除版本号
每一个事务在启动的时候，都有一个唯一的递增的版本号
2，记录的创建版本号实际上就是记录在被insert的时候的事务号
3，update操作时，标记记录为已删除，记录的删除版本号就是update事务的版本号
然后插入一行新的记录，记录创建版本号就是update事务的版本号
4，delete操作时，把事务版本号作为删除版本号
5，MVCC实现可重复读，是并行事务update操作对当前事务的影响
   设当前事务ID是5，并行事务ID是8，记录的创建版本号是3
   1°事务5查询到的记录R是创建版本号为3的记录
     记录R，创建版本号3，删除版本号nil
   2°事务8做update的操作对记录的影响：
   记录R，创建版本号3，删除版本号8
   记录R，创建版本号8，删除版本号nil
   3°事务5查询到的记录R是创建版本号为3的记录
   4°MVCC版本控制原理：
     删除版本号大于当前会话的事务版本号，删除操作发生于当前事务之后
     创建版本号小于等于当前事务版本号，记录是在当前事务或者之前事务插入的
     当这两个条件满足时，相应的记录查询出来，提高系统性能的思路：通过版本号来减少锁的争用
 6，以上只是对MVCC的简陋解释，实际是由记录的隐藏列+UNDO构建版本链，生成查询快照ReadView来实现的

MySQL 锁和索引：

InnoDB：行锁和表锁，默认行锁

1，行锁是通过给索引上的索引项加锁来实现的
比如一张表主键id有索引，update更新id=1的这一行，最终锁定的是主键索引的索引项
只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁，这一点要特别注意

2，相同的索引项不同记录之间会出现冲突，非唯一性索引会锁住多个记录行
多个会话where条件class=1 and name=...，class列有索引，name列无索引，会话之间会有锁冲突

3，表有多个索引，不同的事务使用不同的索引锁定不同的行
行锁适用于所有索引类型：主键索引、唯一索引或普通索引

InnoDB是支持事务的存储引擎，默认事务隔离性RR级别
在行级锁row lock之外增加了间隙锁gap lock，形成了next key lock来尽量避免幻读，没有完全解决幻读

4，间隙锁gap lock出现在范围条件过滤数据之时，比如n>1 and n<8，n列有索引
那么会在n>=2且n<=8的记录上加gap lock，来界定记录范围
如果n=2的记录不存在，则顺延到n=3
如果n=8的记录不存在，则顺延到n=9
在RR隔离级别下，gap lock锁住了更多范围的数据，避免了一部分的幻读，但是也造成了锁等待

5，表锁应用场景：
事务需要更新大部分或全部数据，表又比较大
事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚
应用中这两种事务如果太多，则要考虑MyISAM引擎

6，MyISAM不存在死锁，InnoDB存在，自动检测，自动回退某事务，解决死锁

MySQL备份和恢复：

逻辑备份：mysqldump，实际上是生成建库、建表和数据插入等等的SQL语句的文本文件
是逻辑备份，如果要做恢复的话，需要借助binlog追平日志，需要备份开始时候的binlog
备份时先将隔离级别强制改成RR，开启备份进程，做回滚点标记，记录LSN(log sequence number)
备份中不含该标记之后的事务，生成建库、建表和数据插入的SQL，备份完成后释放回滚点，隔离级别恢复
通过参数--master-data=1/2来控制打印binlog日志信息和pos位置

物理备份：Percona的XtraBackup，支持所有MySQL发行版，完全开源，是物理备份恢复工具
可以实现增量备份，具体就是比较数据文件的page上的LSN，比已经备份的LSN大则进行备份
试用于innodb，MyISAM没有增量，只有全量的数据文件复制

PXB备份过程梳理：
innobackupex启动xtrabackup进程
xtrabackup首先启动单线程的redo备份，顺序拷贝redo，然后启动数据文件备份
数据文件备份完成后，通知innobackupex，创建文件，redo的复制继续
innobackupex执行FLUSH TABLES WITH READ LOCK (FTWRL)，备份非innodb文件
此时数据库处于全局只读状态，需要预估全局只读对业务的影响，或者使用备库备份
备份非innodb文件完成后，xtrabackup停止redo备份，然后通知innobackupex
innobackupex执行UNLOCK TABLES
恢复时需要备份完成之后的binlog做恢复，也就是说备份文件中的LSN是备份完成之时的LSN
xtrabackup_info和xtrabackup_binlog_info文件记录binlog信息

MySQL主备复制原理：

需要的条件：主库开启binlog，备库可以通过网络访问到主库

主要线程：主库IO，从库IO和SQL，一个用于binlog数据传输，一个用于SQL应用

原理剖析：
主库开启binlog，配置用户，做备份
备库根据备份做恢复
备库配置主从信息master.info：主库的IP、端口、用户和binlog文件以及POS位置信息
start slave，开始复制：
备库打开IO和SQL进程，备库IO通讯主库，使主库打开IO进程
备库根据master.info中的binlog和pos信息以及主库的用户信息
请求主库的对应binlog，存入relay-log，刷新master.info
备库SQL进程应用relay-log

show slave status 查看主从复制的延迟，具体是Seconds_Behind_Master

MySQL主备复制的GTID复制：

5.6开始支持的特性，GTID即是全局事务ID，是事务的唯一标识
分成两部分，一部分是服务的UUID，保存在auto.cnf文件中，该文件不能删除，是每个MySQL唯一的
另外一部分就是事务ID，随着事务的增加，值递增

主库发生事务，会产生GTID，记录到binlog中
备库的IO线程将主库binlog写入relay log中
备库的SQL线程从relay log中获取GTID，然后比较备库的binlog确定该事务是否已经在备库执行
如果执行则忽略，如果没有才会去执行，然后写入备库binlog

优点：
1，每个事务都拥有唯一的GTID，执行前会确认是否已经执行过，保证事务只执行一次
2，使用GTID进行主备复制，不再需要指定binlog位置和pos
传统的主备是将主库的binlog日志发送到备库，然后将信息追加到relay log中
而使用GTID进行主备复制，直接传输主库变更的binlog到relay log中
3，三节点或者更多节点的主备复制切换中，可以更有效的定为事务是否被执行，减少事务丢失

限制：
1，只支持innodb事务引擎
2，create table as select 不支持，该类SQL实际上是两个SQL，
   其中还有DDL会造成隐式提交，需要两个GTID，但是目前只能生成一个
3，不允许一个SQL同时更新innodb和非innodb的表
4，主备复制要么各节点均开启GTID，要么均关闭
5，5.6模式开启需要重启，5.7不需要
6，开启GTID后，传统模式将关闭
7，不支持临时表的创建和删除
8，不支持sql_slave_skip_counter

MySQL MHA 架构原理剖析：

MHA需要部署在至少是三节点的主备复制集群之上，一台master，一台备master，一台从库
是对Master实时监控，故障自动切换Slave为Master的高可用套件
MHA由一个Manager节点和多个Node节点组成，每个MySQL无论主备都要部署Node节点
Manager节点对Node节点实时监控，发现Master不可用时，自动提升某个Slave为Master
一个Manager节点可以同时管理多套主备复制集群
MHA控制主备切换时，会依据新主的relay-log向slave进行同步

MySQL 读写分离 两种实现：

主备复制之后 代码层面多数据源 设置select和其他DML分别接入备库和主库实现 代码层的实现
使用MySQL Proxy 中间套件实现 实际上就是所有web接入 MySQL Proxy
由 MySQL Proxy 统一接入到MySQL 并分析SQL类型接入到Master还是Slave

MySQL性能监控：

使用Zabbix的通用模板对MySQL进行监控，这是Zabbix自动化监控告警的方式

日检或者周检以及巡检的时候监控点：
1，show processlist/show full processlist
监控当前或者问题时间点的SQL执行情况，分析多少会话在执行SQL
并发执行的SQL是什么，等待事件是什么，平均执行时间等
2，show global status
监控该命令两次输出数据的差值来计算诸如：
这期间有多少次查询请求、select/insert/update/delete操作数
innodb行读取或者DML操作次数、Innodb_buffer相关等等的一系列监控
使用shell脚本，以高级awk命令生成报表进行监控
3，定期对slow log进行切割转储，并使用工具生成报表，使用的工具是mysqlsla

详见《MySQL健康检查（一）》、《MySQL健康检查（二）》

MySQL 在线DDL和pt-osc工具：

innodb对表进行DDL的操作流程是创建一个相同结构的临时表执行DDL
锁定原表，将原表数据insert到临时表，原表和临时表改名切换，解锁
原表锁定阶段无法DML，如果表比较大，锁表时间就会很长，会影响正常的业务DML

5.6开始支持在线DDL
剔除锁表操作，将在线DDL期间发生的DML操作写入到缓存中
最后将缓存应用到临时表，表名切换，完成在线DDL

Percona的开源工具pt-osc(pt-online-schema-change)
创建原表相同结构的空表，DDL改变结构
原表上增加DML触发器
复制数据，包括触发器生成的数据
表改名替换，删除原表和触发器

限制条件和注意事项：
表要有主键、不能有触发器，如果有外键约束需要专门处理

[TOC]