1.mysql一条查询语句是如何执行的?
mysql内部分为：连接器，分析器，优化器，执行器和缓存查询。
连接器：用于客户端与server端进行账密连接，及权限校验。
缓存查询：一条执行的查询sql先查缓存是否存在这样的数据如果有直接返回结果（不建议使用缓存查询，命中率太低，对于更新频繁的表更倾向于操作引擎返回结果，设置query_cache_type = DEMAND 默认不使用查询缓存，我们可以在sql显示先查询缓存例如：select SQL_CACHE * from t）
分析器：对sql进行分析，sql语句是否合法，是否规范，还会校验表，字段等是否存在等
优化器：分析器执行完成之后，优化器会对sql语句进行优化如对索引的选用
执行器：执行sql语句，对于表无索引的执行流程(从表第一行开始全表扫描取到当前行做对比如果符合条件加入到结果集，直到最后一行将结果集返回)，对于有索引（通过索引数进行查找符合的加入结果集，直到不满足的行将结果集返回）
2.mysql一条更新语句是如何执行的?
更新语句与查询语句大致相同，不一样的是更新语句涉及到两个日志模块：redo log(重做日志)，binlog(归档日志)
redo log日志：更新语句记录在redo log上，当redo log日志写满之后会将buffer pool脏页更新到磁盘中，向前推进checkpoint。redolog是InnoDB引擎特有的日志
binlog日志：binlog是归档日志，可以追加写入，当binlog日志文件到达一定大小时候会切换到下一个文件进行追加写入。
看下这条更新语句如何执行
//首先创建一张表
create table test(id int primary key, column int);
//执行更新操作
update test set column = column +1 where id = 2；
1.执行器根据id=2的主键进行扫描，如果缓存中（buffer pool）中如果有直接返回给执行器，如果没有从磁盘上扫描将数据加入到缓存中（buffer pool）返回给执行器。
2.执行器拿到这行数据读取column值改成column+1，调用引擎将数据更新，
3.引擎将更新的数据再写入buffer pool，同时更新操作记录到redo log中，此时redo log 处于prepare状态，待执行器执行完毕随时可以提交事务。
4.执行器又将这个更新操作记录成binglog模式，将binlog写入到磁盘
5.执行器调用引擎事务提交接口，引擎将刚刚记录的redo log改成commit，执行操作完成。
为什么redo log 有prepare 和 commit (两阶段提交) 两个步骤呢？
两阶提交是为了保证两个日志文件的逻辑一致。例如更新这条语句假设column原值为0
update test set column = 1 where id = 2；
不妨我们使用反证法进行验证：1.先写redo log 后写 binlog 2.先binlog 后 redo log
1种情况：先写redo log 后写 binlog
假设写完redo log之后 发生crash binlog未写入，此时redo log 的column值为1，binlog未记录，mysql重启之后将id为2的记录更新成1，当需要binlog来恢复临时库时候binlog并未记录更新的操作，导致数据与主库的数据不一致。
2中情况：先binlog 后 redo log
假设写完binlog之后发生crash redolog未写入事务是无效的会回滚，binlog的日志记录column为1，使用binlog来恢复数据后发现column值为1了与原来逻辑不一致。
两阶段提交的作用
两阶段提交过程中可以出现的crashredo log 写入成功 binlog 未写入 与 redo log 写入成功 binlog 写入但未commit
这种情况由于redolog写入成功处于prepare状态，当mysql重启会读取redolog进行复盘，发现有个日志未commit，会拿这个日志的xid去binlog日志中找是否有这个日志，如果有事务提交，否则事务回滚（binlog未写入，该事务回滚）都保证了两分日志的逻辑一致性
3.ACID和mysql事务隔离级别?
A（原子性）：一个事务要么成功，要么失败。
C（一致性）：一个
I（隔离性）：
D（持久性）：
Read Uncommited（读未提交）: 一个事务还没提交，做的变更其他事务是可以访问到。
Read commited（读已提交）：一个事务提交后，做的变更其他事务才可以访问到。
Repeatable read（可重复读）：一个事务执行过程看到的数据，总是和这个事务在启动时候看到的数据是一致的。
serializable（串行化）：对于一条记录，写会加写锁，读会加读锁，当读写锁发生冲突后访问到的事务必须等待前一个事务提交之后才可以执行
其实对于RC，RR两种情况数据库会创建视图，RC情况下每次SQL执行前创建视图，RR情况是每次事务开始时候创建视图。
mysql记录每条更新语句都会记录一条回滚操作，记录的新值通过回滚可以得到上一个状态值，如图所示：
假设一个字段呗A，B，C事务进行一次更新2,3,4对应的回滚记录就是3,2,1。同一条记录在系统中有多个版本，这就是数据库的多版本控制（MVCC），对于A事务需要得到1，就要依次回滚视图中所有的操作。
？？什么时候会删掉记录版本呢----答：不需要的时候 ？？？？什么时候不需要--答：当版本不处于任何事务之内的就可以删除了，所以我们建议尽量不使用长事务。如果长事务时间过长在这个事务内的视图都需要保留，导致系统内有过多的视图，占用更多的存储空间。
4.mysql索引
索引作用：提高数据查询效率，
索引模型：数组，hash表，B+树
通过索引如何查找数据
//创建T表，索引k，主键索引ID
create table T (ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))engine=InnoDB;
//插入数据
insert into T values(100,1, 'aa'),
(200,2,'bb'),
(300,3,'cc'),
(500,5,'ee'),
(600,6,'ff'),
(700,7,'gg');
//查询语句 需要执行几次树搜索操作
select * from T where k between 3 and 5
sql执行流程
1.在k索引树上进行搜索k=3记录取得ID=300；
2.ID=300在主键索引搜索取得R3放入结果集
3.继续遍历k索引树k=3的下个值为k=5取得ID=500；
4.ID=500在主键索引搜索取得R4放入结果集。
5.继续遍历k索引树k=5的下个值为k=6不满足调节循环结束返回结果集
在这个执行过程中在k索引树上执行时1,3,5。在主键索引树上执行2,4（回表操作）搜索了4次
索引覆盖
上面的查询语句是查询所有字段，如果我们将sql改成
select ID,k from T where k between 3 and 5
sql执行流程
1.在k索引树上进行搜索k=3记录取得将ID=300，k=3放入结果集；
2.继续遍历k索引树k=3的下个值为k=5取得ID=500，k=5放入结果集；
3.继续遍历k索引树k=5的下个值为k=6 条件不满足 循环退出 返回结果集；
使用覆盖索引避免回表扫描行数就是2次
联合索引优势
1.覆盖索引
2.最左前缀index（a,b,c,d）包含a的索引都不是使用
3.索引下推 如 a=10，b>5 会判断b是否大于5，避免了小于=5的数据进行回表扫描
普通索引与唯一索引如何选择
待补充。。。
5.mysql全局锁和表锁及行锁
全局锁：
flash table with read lock（FTWRL）：将表设置只读状态，当遇到 DDL或者DML操作会阻塞，影响性能 客户端与数据库断开连接锁也就释放了，不建议使用。
mysqldump --single-transaction ：需要结合事务隔离级别为RR，使用数据备份场景，不影响其他事务的读写操作，推荐使用
set global read_only=1：这种场景是设置是判断是否是从库，对于更高权限的系统用户是无效的，客户端与数据库异常断开连接不会释放锁。不推荐使用
表锁：
table lock XXX read/write：例如table lock A read B write；设置A表只读，B表 读写操作，别的事务对于这A表写和B表读写处于阻塞状态。不推荐使用
隐式的加锁Metadata Lock（MDL）：对表做增删改查（DML）时候对表进行加读锁，对表进行DDL时候加的是写锁，读写锁互斥也就是说DDL语句后的DML语句都会被阻塞
举个例子也是可能会被问到的知识点
SessionA: begin; select * from t limit 1; 启动sessionA
SessionB: begin; select * from t limit 1; 紧接着启动sessionB
SessionC: alter table t add f int；接着启动sessionC
SessionD: begin; select * from t limit 1; 然后启动sessionD
SessionE: begin; select * from t limit 1; 最后启动sessionE
※SessionA，B可以正常启动拿到MDL读锁进行查询，
SessionC获取MDL写锁等待A，B执行commit之后才可以执行，
SessionD，E获取到MDL读锁由于C写锁互斥处于阻塞状态。
当SessionA，B事务进行Commit之后，开始执行SessionC，C执行完释放写锁，D，E继续执行。
我们都能想到A->B->C->D->E正确的流程？？？
实际上顺序是A->B->D->E->C才是正确的流程，我来解释下为什么是这个样子的。
※我们需要知道Online DDL真实的流程
1.获取MDL写锁
2.将MDL写锁降级为读锁
3.执行DDL操作（比较耗时，期间可以执行DML操作）
4.升级MDL读锁为写锁
5.释放MDL写锁
当第二步C将MDL写锁降级成读锁时候，此时D，E两个session是可以执行的，当C执行完DDL操作需要升级锁时候发现有MDL读锁未提交，
等待DE事务commit之后才可以，所以我们看到的流程是ABDEC
InnoDB引擎行锁
行锁就是对数据表中的行记录锁定，如事务A更新一行数据，而事务B也更新事务A的同一行，那么事务B就要等事务A提交之后才可以更新这行数据。
在InnoDB事务中行锁是需要加的时候才会加，但并不是不需要的时候就立即释放的，而是要等到事务提交之后才释放，两阶段锁协议。这样的话，很容易出现死锁
如图：事务A中有两条更新语句，B也如此，A先更新id=1再更新id=2，B先更新id=2再更新id=1，在事务A执行更新id=1时候对这行记录加上锁，此时id=2这行数据是没有锁的，事务B可以更新id=2这行记录，当A，B都执行完第一更新语句时候，（两阶段提交id=1和id=2已经都被锁住了）导致A更新id=2时候发现id=2这行记录有锁，等待，事务B更新id=1时候发现id=1这行记录有锁，等待，这就导致了A，B事务都带等待互相持有的锁，导致了死锁
死锁两个策略
1.设置等待时间通过innodb_lock_wait_timeout参数设置锁等待时间
弊端：默认50s但是如果等待50s后退出，这个时间又不可接受，如果设置太小1s又会误伤很多事务。
2.死锁检测，发现死锁，主动回滚某一个事务，让其他事物继续执行，将参数innodb_deadlock_detect=no标识开启这个逻辑
6.mysql锁与隔离
RR隔离级别启动事务时候会拍一个快照，这个快照是基于整个库的，这个快照并不是复制整个库，而是创建一个trx_id做为当前快照的版本。而对于表的row会有多版本控制（MVCC）对于哪个版本可见那就要看版本的trx_id与当前快照trx_id值对比了，当然对于低版本的trx_id也可能是不可见的，（低版本的trx_id处于未提交的事务 不可见）。
当前读概念
select * from t lock in share mode/ for update（共享锁/排他锁）当前读是读取到最新值，如果当前读有未执行完的事务需要等到事务提交释放行锁，当前读才可以执行（DML写锁与读锁互斥）
7.mysql ChangeBuffer,BufferPool,redolog之间关系
什么是ChangeBuffer
changeBuffer是从BufferPool内存中申请的一块内存，用于存放更新数据的记录。在更新一条记录时：
1.如果该数据所在的数据页在内存中（bufferpool中）直接更新bufferpool即可
2.如果该数据所在的数据页不在内存中（不在bufferpool中）直接将更新的操作记录在changebuffer中，当下次访问这个数据页时候将数据页从磁盘读到内存bufferpool中，然后将changebuffer和数据页进行merger合并，此时数据页成了脏页，等待刷入磁盘。
使用changebuffer好处
减少在更新数据时候需要磁盘随机访问IO，有种场景不适合使用changebuffer
1.如果更新的数据有唯一索引（更新唯一索引会磁盘随机访问到数据判断更新的值是否会唯一键冲突，这样数据页会加载到内存，内存操作和changebuffer一样，这样没有避免这次磁盘的随机访问）
2.对于刚更新的数据立马就查询的场景也不适合使用changebuffer，刚更新逻辑数据加入到changebuffer上，立马进行查询会进行磁盘随机访问将数据页加入到内存中，然后内存数据页与changebuffer进行merge合并，这种情况changebuffer数据还很少，磁盘的io访问并未减少。
针对上面两种不适合使用的场景反之就是合适的场景（1.普通索引，2.更新多读少的场景如账单，日志类等使用changebuffer性能明显）
这三者之间如何联系呢
//表t k字段普通索引
insert into t(id,k) values(id1,k1),(id2,k2);
假设k1索引查询的要插入所在页在内存page1，k2位置所在页不在内存中
1. Page 1 在内存中（buffer pool中），直接更新内存，直接把数据插入到表t中的位置
2.Page 2 没有在内存中，就在内存的 change buffer 区域，记录下 “ 我要往 Page 2 插入一行 ” 这个信息
3.将上述两个动作记入 redo log 中（图中 3 和 4 ）（包含了数据的变更和 change buffer 的变更）
这样的一个事务，写了两处内存（直接在内存更新和在内存中的change buffer记录），然后写了一处磁盘（两次操作合在一起写了一次磁盘中redolog）
如果查询
select * from t where k in (k1, k2)
读 Page 1 的时候，直接从内存返回更新后的内容，要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志(add 某个数据 to page2).最后生成正确的版本并返回结果
8.mysql选错索引
场景1：不断地删除历史数据和新增历史数据，mysql会选错索引
重新统计索引信息：analyze table t；
场景2.表T有10万条数据。表结构为id主键索引，a，b两个字段int类型都是普通索引，执行sql为select * from T where (a between 1and 1000) and (b between 50000 and 100000 ) order by b limit 10;这个sql执行explain计划可以看到，使用索引的是b扫描行为5万多行，为什么没有选择a索引呢，使用a索引只需要扫描1000行即可。分析下：由于order by 根据b排序，优化器觉得扫描b索引天然排序效率更快。如何使用a索引呢？两个方案：方案一，在order by b，a即可，让优化器知道不仅要根据b排序还要根据a排序，就会重新分析走a索引。方案二使用force index(a)让sql指定使用a索引。
图片.png
我们可以总结一下：索引选错情况如何优化
1.使用analyze table XXX；（这种适合不断删除和新增的表）
2.使用force index(xxx) 如 select * from t force index(xxx) where xxx = aaa;
3.修改sql语句
4.删除多余的索引重建索引等
9.如何给字符串字段加索引
1.直接给该字段创建索引，占用的空间可能比较多。
2.使用前缀索引，占得空间相对较小，增加了查询次数，而且不能使用覆盖索引（因为使用前缀索引扫描到的值不能准确知道是否完整的字段需要回表查询）
3.使用倒叙存储，在使用前缀索引降低了索引的重复度，但是增加了字符串反转的计算的开销，而且不能使用覆盖索引及范围查询。
4.创建hash值字段创建索引，有额外的存储和计算开销，而且不能使用范围查询
10.Mysql为什么隔一段时间查询会抖动一次？
数据查询突然很慢，而且这个场景很难复现，他不仅是随机的，而且持续时间很短。针对这个可能发生了再刷脏页。发生刷脏页可能有一下四个场景。
场景一：redolog日志快写满了，需要将redolog日志进行擦除一部分来腾出空间。而擦除的这部分空间数据对应的bufferpool数据页属于脏页需要进行flush更新到磁盘。
场景二：当bufferpool内存不足情况，有新的查询数据需要从磁盘数据页写入到内存中，就需要淘汰bufferpool中原有的数据页。要淘汰的数据页如果是脏页，就需要写入到磁盘。
场景三：mysql认为系统不是很忙时候，主动进行将内存部分脏页进行写入磁盘。
场景四：当mysql正常关闭时需要将bufferpool中的脏页数据写入到磁盘中。
针对以上四个场景分析下影响性能的条件:
对于场景三属于mysql空闲干的事，无需考虑，场景四是mysql处于正常关闭也和性能无关。对于场景一redolog日志写满了需要腾出空间继续写入，对于这种刷脏页是需要尽量避免的，因为这种情况刷脏页由于redolog空间不足，写入也就停滞了，影响写的性能。对于场景二 bufferpool内存不足刷新脏页这种是常态，但是出现这两种场景，都会影响性能。1.redolog日志满了，需要刷脏页，此时写入线程被堵塞，对于敏感业务来书是不能接受的。2.如果一个查询需要淘汰的内存脏数据页过多，写入磁盘数量也会过多，这必定会影响查询的性能。
InnoDB刷脏页的控制策略
了解系统IO性能，配置mysql可执行的IO能力，这样可用提升mysql刷脏页的性能，配置innodb_io_capacity=1000。尽量避免刷脏页的IO速度小于产生脏页的速度。mysql有个刷脏页的机制，如果准备刷脏页的下一个数据页也是脏页，也会被一起刷掉，“连坐机制”，通过配置innodb_flush_neighbors=1连坐 0 只刷自己
11.删除表数据，表文件大小不变
删除某行记录，Innodb引擎只会把这行记录标记为删除，在别的数据插入可以复用这个位置。但是磁盘大小不会缩小，如果是删除整个页，那么整个数据页就可以被复用了。
这些被标记的可复用的位置我们俗称“空洞”；
产生空洞的原因有:
1.数据删除，被标记可复用的位置为空洞。
2.插入数据导致页分裂，分裂的页末尾会空出一个位置产生空洞。
如果把这些由于增删产生的空洞数据去掉可以达到收缩表空间的目的---重建表。
方式1：alter table A engine=InnoDB
方式2：optimize table t ==recreate+analyze
知识点
一个表t文件大小问1TB，执行 alter table t engine=InnoDB，执行完之后空间没有变小反而变成了1.01TB？这种是什么原因？？？
alter table t engine=InnoDB是重建表，对于有很多的空洞情况效果明显，但是如果原本每个数据页存储的数据都是紧凑的，当我们执行重建表语句后，mysql会将每个页预留10%空间，可能发生某些数据页发生了也分裂情况，导致我们的文件大小不降反增加了
12.查询表行数方式对比优缺点（count(*),count(1),count(id),count(column)）
Count()函数的含义：count()是一个聚合函数，对于要返回的结果集进行一行行的判断，如果count函数的参数不为NULL，累计值就加一，否则不加，最后返回累计值。
所以count(),count(id),count(1)都表示返回满足条件的总行数，对于count(column)则表示返回满足条件的column列不为NULL的个数。
count(ID)：InnoDB引擎会遍历整张表，把每行的id都取出来，返回给server层，server层拿到id判断不可能为空，按行累加。
count(1)：InnoDB引擎遍历整个表，不会取数据，server层对于返回一行就放一个1进去判断不可能为空，按行累加。
count(column)：1.如果这个字段定义not null InnoDB遍历整个表一行行返回数据读出该字段，判断不能为null，按行累加。
2.如果这个字段定义允许为null InnoDB引擎遍历表一行行读取数据返回给server层，server层判断该字段值是否为空，不为空才累加。
count(*):不会把所以字段取出来，count（）肯定不为null，按行累加。
按照上述效率排序 count(字段) < count(ID) < Count(1) <= count(*)尽量使用count（*）
13.order by 语句工作流程
//表结构
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;
//查询语句
select city,name,age from t where city='杭州' order by name limit 1000 ;
explain命令看看语句的执行情况。
图片.png
使用了索引查询（using index condition）和排序（using filesort mysql会为每个线程分配一块内存用于排序）
排序的过程可能在内存中，也可能需要使用外部排序，这取决于排序所需要的内存参数sort_buffer_size（全字段排序）;
max_length_for_sort_data是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法（rowId排序）
全字段排序执行流程如下：
1.初始化sort_buffer，存放，city，name，age这三个字段
2.根据city索引找到索引为 city='杭州' 第一个值id为ID_X
3.通过主键索引找到id=ID_X的行信息，取出city，name，age这三个字段放入sort_buffer中。
4.通过city索引找到ID_X的下个值满足条件重复3.步骤放入到sort_buffer中直到city不满足条件为止。
5.将sort_buffer中的数据按照name字段进行排序。
6.取出前1000行的数据返回给客户端。
rowId排序执行流程：与全字段执行流程相似，多的步骤是需要回表查询字段，因为在使用rowid排序max_length_for_sort_data对于行字段大小有限制，只能放下要排序的字段和主键Id
14.索引失效
1.条件语句使用函数如：select * from t where f1（index） = “kjjjk”；
2.隐式类型转换 select * from t where str = 9；str 是字符隐式转换成select * from t where CAST(str USING int) = 9
3.隐式字符集转换
4.条件中使用or，存在无索引的条件，则索引失效，要想使用索引or中条件必须都要有索引。
5.多列索引（联合索引），需要使用最左前缀才能使用索引
6.like查询需要'xxx%'在查询字段结尾才可使用
7.mysql认为全表扫描比索引更快，则不使用索引。
15.加锁过程
RR隔离级别执行：select * from t where column=10 for update / select * from t where column=10 lock in share mode。全表扫描给每个行加上行锁，将行之间的间隙加上间隙锁，统称为next-key lock。加上锁别的事务不可以修改该行，间隙锁不允许在各条记录之间插入新的行数据。
RC隔离级别执行：select * from t where column=10 for update / select * from t where column=10 lock in share mode。全表扫描给每行加上上锁，然后将column != 10的行锁释放。不会加间隙锁。
对于RR隔离级别间隙锁规则：
1.查询过程中访问到的对象才会加锁。
2.加锁的基本单位是next-key lock（前开后闭）。
3.等值查询上MySQL的优化： 索引上的等值查询.
如果是唯一索引，next-key lock会退化为行锁，
如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁。
4.范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止
//表结构
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
//插入的数据
insert into t values(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);
案例一:等值查询间隙锁
![]()
图片.png
由于表t上没有id=7的值。
1.加锁最小单位是next_key lock SessionA加的锁范围(5,10];
2.等值查询，唯一索引，next_key lock会退化成行锁，由于（id=7）10不等上锁范围为(5,10)；
所以SessionB在插入Id=8由于间隙锁原因被阻塞，而SessionC id=10不在间隙锁内执行成功
案例二：非唯一索引等值锁（覆盖索引上上锁）
1.使用非唯一索引等值查询查询，锁最小单位是next_key lock 上(0,5]，
2.由于非唯一索引等值查询需要继续扫描知道条件不满足，下个值是（10,10,10）加的next_key lock 为(0,5],(5,10]。
3.由于非唯一索引等值查询会将next_key lock 退化成间隙锁（5,10) 由于覆盖索引未回表无法在主键索引加锁。
SessionB 可以执行成功， Session由于间隙锁阻塞。
注：例子中使用的是lock in share mode 只锁覆盖索引，但是如果使用for update 就不一样了，执行for update 系统会认为你要更新数据，也会给主键索引上满足条件的加锁。
案例三：主键索引范围锁
mysql> select * from t where id>=10 and id<11 for update;
1.加锁最小单位next_key lock 主键索引(5,10]等值查询唯一索引退化成行锁 10。
2.id<11，范围查询查到第一个不满足条件的值加的锁为(10,15],加锁范围是行锁10和next_key lock (10,15]。
所以SessionB插入（8，8，8）成功，插入（13,13,13）阻塞。SessionC更新id15阻塞。
案例四：费唯一索引范围锁
1.在普通索引上加第一个锁为(5,10]
2.范围查询需要查询到第一个不满足条件的加上锁（10,15],加的锁为next_key lock 为(5,10],(10,15]。
所以SessionB插入的（8,8,8）和Session更新的c=15锁住
案例五：唯一索引范围锁 bug
1.id>10 id<=15加锁next_key lock 为10,15].
2.无论是否是唯一索引范围查询都需要查询不满足条件的第一个记录id=20不满足加的next_key lock为(15,20]加锁next_key lock为(10,15],(15,20]。
所以SessionB更新id=20和id=16都会被阻塞。
16.Redo log,binlog写入机制
redolog写入机制：
事务在执行过程中生成的redolog日志是需要写入到redolog buffer中，接着写入到page cache文件中，最后才是fsync持久化到磁盘上。
redolog写入策略，InnoDB提供了innodb_flush_log_at_trx_commint参数，取值含义分别为：
1.innodb_flush_log_at_trx_commint=0,表示每次事务提交时只把redolog留在redolog buffer中，弊端（当mysql重启会丢失redolog buffer中数据导致，这些数据未持久化到磁盘上）；
2.innodb_flush_log_at_trx_commint=1,表示每次事务提交都将redolog持久化到磁盘上，弊端（每次事务提交都持久化到磁盘，iops高）。
3.innodb_flush_log_at_trx_commint=2,表示每次事务提交都将redolog写到page cache中。弊端（当主机重启会丢失page cache中的数据）；
InnoDB后台有个线程每隔一秒，会将redolog buffer中的日志，写入到page cache中，然后调用fsync持久化到磁盘。
未提交事务的redolog也可能持久化到磁盘上:
1.InnoDB后台线程在持久化磁盘时候也会将为提交事务的redolog持久化到磁盘上
2.redolog buffer占用的空间到达redolog_buffer_size配置的空间一半时候，后台线程会主动写盘。
3.当innodb_flush_log_at_trx_commit=1时候当有事务提交需要持久化到磁盘，会携带未提交事务的redolog一起写盘。
binlog写入机制：
事务在执行过程中先写入到binlog cache中，接着写入到binlog files（page cache）中，随后fsync持久化到磁盘上。
binlog cache:它适用于缓存binlog event的内存，大小有binlog_cache_size指定大小。
binlog cache临时文件：是一个临时磁盘文件，存储由于binlog cache不足溢出的binlog cache的event，由max_binlog_size配置大小。
binlog写入时策略，由参数sync_binlog控制：
1.sync_binlog=0时，表示每次提交事务都只将binlog_cache日志写入到page cache中，不执行fsync写入磁盘。（主机发生重启 page cache中的事务会丢失）
2.sync_binlog=1时，表示每次事务提交会将binlog_cache日志写入到page_cache中，然后执行fsync写入磁盘。
3.sync_binlog=N(N>1)时表示每次事务提交都写入到page cache中，累计到N个事务提交时候，一起执行fsync写入磁盘，提高IO性能。（主机发生重启 page cache中的小于N个事务会丢失）
组提交机制写入磁盘
redolog在写入磁盘时候携带别的已提交的事务一起写入的磁盘。提高IO性能。由参数1.binlog_group_commit_sync_delay表示延迟多少微妙才调用fsync写入磁盘。
2.binlog_group_commit_sync_no_delay_count表示当累计多少个组员后调用fsync写入磁盘。
数据丢失情况
1.innodb_flush_log_at_trx_commint=0(只写入redolog buffer中) sync_binlog=1（每次事务提交持久化到磁盘）当mysql异常重启redolog buffer中的数据还未来得及持久化就会丢失，binlog日志完整导致数据不一致。
2.innodb_flush_log_at_trx_commint=0(只写入redolog buffer中) sync_binlog=N(N>1)（累计到达多少个事务才会持久化磁盘）如果主机重启redolog buffer中的数据还未来得及持久化就会丢失，存储binlog的page cache日志 会丢失<N的数据。
3..innodb_flush_log_at_trx_commint=0(只写入redolog buffer中) sync_binlog=0异常重启或主机重启数据丢失
4..innodb_flush_log_at_trx_commint=1(每次事务提交持久化磁盘) sync_binlog=0 mysql异常重启或主机重启数据丢失。
5.innodb_flush_log_at_trx_commint=1； sync_binlog=1（不会发生数据丢失）
6.innodb_flush_log_at_trx_commint=1； sync_binlog=N（N>1）主机重启存储binlog的page cache丢失数据。
7.innodb_flush_log_at_trx_commint=2（每次事务提交都只写入到page cache中）； sync_binlog=0主机断电两边数据都丢失，mysql重启存存储binlog的page cache丢失数据。
8.innodb_flush_log_at_trx_commint=2； sync_binlog=1 主机断电redolog日志丢失数据。
9，innodb_flush_log_at_trx_commint=2； sync_binlog=N 主机断电两边日志都丢失
17.Mysql主备
主从结构有：一主一从，双M结构两个节点都可以当主库来使用。
主备延迟的来源
1.主备机器性能不一致导致的。（备库执行同步速度低于主库产生binlog速度）
2.备库压力大，觉得备库提供读能力，不克制随意的执行读操作，可能耗费大量的cpu资源影响同步速度，造成主备延迟。
3.大事物导致的，在做一个更新操作耗时很多，导致同步给备库执行，延迟的时间就是这个大事务的耗时时间（一次性删除过多的数据，耗时太久。或者一张大表执行DDL操作）
4.备库在执行备份操作
5.设置了是延迟备库
6.备库空间不足
备库并行同步数据
mysql5.6之前只支持单线程同步数据（sql_thread），如果主库高并发，就会出现主备延迟问题。
mysql5.6之后对其进行优化使用多个线程进行数据复制。
图片.png
coordinator线程也就是之前的sql_thread线程coordinator只负责读取中转日志和分发事务，不在同步数据了，同步数据的任务交给worker线程执行。在coordinator分发事务时候遵循两个条件：
1.不能造成更新覆盖，这也就要求更新同一行的两个事务，必须发给同一个worker线程执行（避免了如果分发给两个worker执行，由于cup调度可能会发生执行顺序不一样，覆盖更新）
2.同一个事务不能被拆分，必须放到同一个worker中。
mysql5.7提够了并行复制策略由 参数binlog-transaction-dependency-tracking控制
1.COMMIT_ORDER:表示同时进入prepare和commit的事务可以并行复制
2.WRITESET表示对于事务涉及更新的每一行，计算出这行的hash值，组成集合writeset，如果两个事务没有更新同一行，也就是两个writeset没有交集，就可以并行。
3.WRITESET_SESSION表示在WRITESET的基础上多了一个约束，即在主库上同一个线程先后执行多个事务，在备库执行的时候也要保证相同的执行顺序。
18.主从切换复制数据出现错误如何解决
1.使用sql_slave_skip_counter命令主动跳过出现错误的事务。
2.使用slave_skip_errors参数设置跳过指定的错误如slave_skip_errors=1032,1062。其中1032是删除数据找不到行，1062错误是插入数据唯一键冲突。
3.上面两种方法操作都比较复杂，我们还可以使用GTID来解决；GTID=source_id:transaction_id，启动mysql配置参数gtid_mode=on和enforce_gtid_consistency=on
19.读写分离有哪些坑
解决主从数据不一致，过期读的方案
1.对于要求实时性比较高的数据，主从延迟导致主从数据不一致，可以将这部分查询数据放到主库上读。
2.在读从库时候可以Sleep休眠一下，可以解决但是是不靠谱，不好控制休眠时间。
3.使用判断主备是否有延迟在进行查询从库，show slave status 可以查到second_behind_master值如果为0，说明无延迟。单位是s级别的，也不是很准确
3.semi-sync方式，事务提交主库生成的binlog发送给从库，从库收到binlog以后，会发给主库一个ack表示我已收到。主库收到这个ack时候响应给客户端事务完成。弊端：一主一从方案满足，但是一主多从方案可能也会产生过期度。
20.查询数据那么多不会OOM吗？
InnoDB保存数据在主键索引上的，全表扫描实际上扫描的是主键索引，查到的每一行都直接放入到结果集返回给客户端，获取到数据放入到net_buffer中，大小可通过参数net_buffer_length控制，默认16k，当net_buffer写满调用发送接口发送给客户端，发送成功情况net_buffer继续读取数据。
buffer pool淘汰数据页策略：使用的LRU算法，淘汰最久未使用的数据。将Buffer pool的链表分成yong区和old区比例5:3，如何淘汰一个数据页，有新的数据页需要加入到buffer pool中需要淘汰处于old区链表的尾部的数据页，
yong区数据被访问直接将该数据页加入到yong链表头部，
old区数据页被访问需要做判断，如果这个数据页在old区超过1s就需要将该数据页移动到yong区的头部，如果短于1s位置不变。
21.join到底该如何使用
使用join有这几种方式
1.simple Nested-Loop Join算法：对比数据是在数据库层面做对比，
Blocked Nested-Loop join算法：数据读到内存（join_buffer_size 控制内存大小）进行判断比较，如果数据过多可以将数据分块读取。(注意项：如果驱动表是大的冷数据表，如果join_buffer不够大会全表扫描多次，不仅会导致IO压力大还有一个致命的问题，多次扫描会导致buffer pool中的yong区数据页会被淘汰，导致查询频繁的数据页需要重新从磁盘上扫描出来放入buffer pool中)
INdex Nested-Loop join：使用索引进行查询比较效率高，扫描的行数较少。
22.为什么自增id不是连续的？
产生这种不连续的原因有，1.一个事务执行发生异常回滚了，在这个事务执行过程中有其他事物对自增主键incr了，为什么发生回滚不回滚id，如果也会滚id导致下次增加插入数据主键冲突
2.当执行批量插入的时候，为了避免其他事物需要等待，mysql在批量插入引入事先申请自增的id，如：第一次插入申请2个，第二次插入申请4个每次申请都是上次申请的2倍，这样就会导致最后一次申请的id过多，导致出现较大的id不连续。
3.随意修改主键的值
23.InnoDB引擎四大特性
1.插入缓冲；2.doubleWrite；3自适性hash；4预读
