语句耗时

如果select长时间不返回，使用show processlist查看当前语句处于什么状态
- 比如MDL写锁阻塞了别的session的读
- 可以查询是什么session阻塞，但是在MySQL启动时需要设置performance_schema = ON，这样会有10%的性能损失

sys.schema_table_lock_waits，查询这张表可以找到导致阻塞的processid，kill掉即可
还有可能被flush阻塞了，一般flush的两个语句是flush table t with read lock锁表t，flush tables with read lock锁所有的表，本身刷脏页的速度很快，但是可能flush命令被别的session阻塞了
或者别的线程占用写锁，那么申请lock in share mode的读锁就会被阻塞。通过查找sys.innodb_lock_waits表即可
如果在RR场景下，或者在一个事务中，使用select，如果此时别的session有一个update语句，update了一百万次，那么自己的select语句就会特别慢，因为是一致性读，会存在非常大的undolog需要不断查找，而如果使用select in lock share mode，就是当前读，速度就会很快。

幻读

RR下，普通查询是快照读，幻读只有在当前读的环境下才会出现，因为快照读不会看到别人提交的数据。并且，如果别人修改了一行，在当前读的两次查询中，第二次查询才读到，那不是幻读。幻读只有是读到别人新插入的行

幻读会破坏加锁的语义，因为先对d=5的行加锁，在别的session中insert了一条d=5，a=1的行，并修改a=1的行将b改成2，这样的话，也就是修改了d=5的行，破坏了最初对d=5的行加锁的语义
幻读 + statement格式的binlog会导致数据不一致性，因为binlog是在commit的时候才会记录，
即便给所有的行都加上锁，也无法解决幻读的问题，因为原本不存在的行，无法加锁，所以即便给所有的行都加锁，也没有办法阻止插入新的行
所以需要加上间隙锁，在值和值的中间加锁，避免插入新的值
解决幻读，需要加行锁和间隙锁(gap lock，开区间)
行锁 + 间隙锁 = next-key lock (临键锁，左开右闭)

死锁

如果对不存在的一行加锁，比如id=9，不存在
select * from t where id = 9 for update
- 那么本来应该加行锁+间隙锁，但是因为id=9不存在，所以只会加间隙锁
本来select for update是两个排他锁，但是因为不存在这一行，所以两个session都可以对id=9这一行使用select for update，因为都是间隙锁，所以不会冲突
此时session B判断不存在这一行，尝试添加id=9的记录，但是因为session A中有间隙锁，所以被阻塞
session A也判断不存在这一行，尝试添加id=9的记录，但是因为session B中存在间隙锁，所以被阻塞，导致死锁

所以RR会导致死锁，因为只有RR才有间隙锁，一般会设置为RC + row格式的binlog，这样可以解决死锁，并且不会出现数据日志不一致问题
一般在金融等，或者表备份的时候，才是RR

加锁

加锁的基本单位是next-key lock，前开后闭
查找中访问的对象才会加锁
索引上等值查询，唯一索引的时候next-key lock 退化为行锁
等值查询，最右边的值不满足等值查询的时候，退化为间隙锁
唯一索引上范围查询会找到不满足条件的第一个值

现在一张表，字段是id，c, d，c上有普通索引，id是主键

内含数据(0, 0, 0)(5, 5, 5)(10, 10, 10)(15, 15, 15)(20, 20, 20)(25, 25, 25)

举例1 - 等值查询间隙锁

sessionA	session B	session C
begin;
update t set d = d + 1 where id = 7;
	insert into t values(8, 8, 8);
		update t set d = d + 1 where id = 10;

对于session A表中没有id=7的行，加锁单位是临键锁，前开后闭，所以范围是(5, 10]
id=7是等值查询，找到最后边的值10，不满足等值条件，所以退化为间隙锁，加锁范围就是(5, 10)
session B插入id=8就会被阻塞，但是session C插入id=10不会被锁

举例2 - 非唯一索引等值查询

sessionA	session B	session C
begin;
select id from t where c = 5 lock in share mode;
	update t set d = d + 1 where id = 5;
		insert into t values(7, 7, 7);

加临键锁，对(0, 5]
c是普通索引，所以会访问到右边第一个不满足的值为止，也就是查询到10，所以(5, 10]加临键锁
c=5是等值查询，最右边一个值10不满足等值条件，退化为间隙锁(5, 10)
访问到的对象才会加锁，覆盖索引不需要访问主键，所以主键索引不需要加锁，因此session B可以完成，session C插入(7, 7, 7)会被阻塞
lock in share mode只会锁覆盖索引，但是for update会判断你要更新，就会给主键索引上满足条件加行锁

举例3 - 主键索引范围查询

sessionA	session B	session C
begin;
select * from t where id >= 10 and id < 11 for update;
	insert into t values(8, 8, 8);
	insert into t values(13, 13, 13);
		update t set d = d + 1 where id = 15;

先找到id = 10，next-key lock (5, 10]，但是唯一索引会退化为行锁
但是范围查找，找到id=15停止，所以会有临键锁(10, 15]
所以B插入(8, 8, 8)可以通过，插入(13, 13, 13)被阻塞；C更新15也被阻塞

举例4 - 非唯一索引范围查询

sessionA	session B	session C
begin;
select * from t where c >= 10 and c < 11 for update;
	insert into t values(8, 8, 8);
		update t set d = d + 1 where c = 15;

查找c = 10的时候，临键锁(5, 10]，不是唯一索引，不会退化为行锁
session A加上了两个临键锁(5, 10]和(10, 15]
B插入(8, 8, 8)阻塞

举例5 - 唯一索引范围查询bug

sessionA	session B	session C
begin;
select * from t where id > 10 and id <= 15 for update;
	update t set d = d + 1 where id = 20;
		insert into t values(16, 16, 16);

A是范围查询，索引id加到(10, 15]临键锁，id是唯一索引，所以判断到id=15结束
但是会访问到第一个不满足条件的值为止，所以会找到id=20，范围查询，所以会加上(15, 20]的临键锁
因此B更新20会阻塞，C插入16也会阻塞

举例6 - 非唯一索引存在等值

比如同时有两行c=10，分别是(10, 10, 10)，和(30, 10, 30)

非唯一索引上包含主键值，所以不会重复

sessionA	session B	session C
begin;
delete from t where c = 10;
	insert into t values(12, 12, 12);
		update t set d = d + 1 where c = 15;

A遍历找到第一个c=10的记录，所以临键锁加在(5 5, 10 10]上
A向右查找，碰到第一个不满足条件的行，也就是15 15停止，等值查询，退化为10 10到15 15的行锁
所以最后是(5 5, 10 10]临键锁，(10 10, 15 15)行锁，因此B插入12会被阻止，C修改15会通过

举例7 - limit加锁

sessionA	session B
begin;
delete from t where c = 10 limit 2;
	insert into t values(12, 12, 12);

因为有limit 2，所以遍历到10 30后，就已经满足两条了，循环结束
A加锁范围就是(5 5, 10 10]临键锁(10 10, 10 30]临键锁
B插入12通过
所以删除的时候可以加limit，限制删除的条数，操作更安全。
- 但是唯一索引上删除不需要，因为唯一索引本身就只加行锁
- 可能更适合普通索引，但是数据是唯一的情况

举例8 - 死锁

sessionA	session B
begin;
select id from t where c = 10 lock in share mode;
	update t set d = d + 1 where c = 10;
insert into t values (8, 8, 8)

A在c上加了(5, 10]临键锁和(10, 15)间隙锁
B要在c上加(5, 10]临键锁，所以等待A释放
A 想插入8，需要加锁，等待B释放，死锁
最后B回滚
B的临键锁没有申请成功，但是B加锁(5, 10]先加间隙锁，加锁成功，再加c=10的行锁，阻塞，两段执行

总结

RR才有间隙锁，并且两阶段提交，事务commit了，锁释放
RC没有间隙锁，并且语句执行完就会释放不满足条件的行的行锁
RC在外键也会有间隙锁

优化

show variables like '%connections%;查看max_connections，也就是最大连接数，超过最大连接数的就会被阻止，最大连接数不是越大越好。

处理掉占用连接但是没有工作的线程
- show variables like 'wait_timeout';查看超时时间，超过时间以后就会断开连接
- 可以使用kill connection + id，但是这样客户端不会马上知道，只有在发起下一个请求以后才会报错，就变成了MySQL一直都没有恢复，但是再次调用接口就可以访问了
减少连接消耗
- 用–skip-grant-tables跳过权限验证，但是风险极高，不建议使用，找回密码可以用
- 8.0版本如果使用跳过权限验证，会同时--skip-networking打开这个，保证数据库只有本地访问
性能问题导致短链接增加
- 慢查询
  - 索引没设计好
    - 5.6版本后可以使用Online DDL创建索引，alert table即可
    - 如果一主一备，在从库处理，set sql_log_bin=off，不写binlog，加索引
    - 主备切换
    - 再在从库set sql_log_bin=off，加索引
  - 语句有问题
    - 使用存储过程query_rewrite重写某一个语句
  - 选错索引
    - 使用query_rewrite加上索引即可，或者语句加索引
  - 可以在上线前处理好，上线前将slow log打开，long_query_time=0记录查询语句
  - 测试表模拟线上数据，回归测试
  - 留意row_examined数据
  - 全量回归测试可以使用开源工具pt-query-digest
- QPS暴增
  - 如果是全新业务有bug，下掉业务，数据库端去除白名单
  - 新功能是单独数据库用户，删除用户，断开现有连接
  - 如果和现有业务部署在一起，查询重写功能重写语句，压力最大的SQL直接select 1返回
    - 但是会有误伤，如果别的业务也用了这个SQL，或者这个语句还有别的用处，都会导致其他业务失败
- 更新语句

数据更新

binlog在事务执行时写入binlog cache，事务提交时写入binlog，清空cache
binlog_cache_size控制cache大小，超过大小的暂时写入磁盘
多个线程有自己的binlog cache，但是共用一个binlog
write是写入cache，速度快，fsync是写binlog文件，sync_binlog参数控制时机
- sync_binlog = 0，每次提交只write，不fsync
- sync_binlog = 1每次提交都会fsync
- sync_binlog = N(N > 1)，每次都write，累计N个事务再fsync
- IO非常大的话，可以适当调大这个值，100 - 1000，但是如果重启了，就会丢失N条binlog，如果容错率很低，不能设置为1
redo log三种状态，分别是redo log buffer，write到page cache，没有持久化和fsync到磁盘，innodb_flush_log_at_trx_commit控制
- = 0 表示每次都只保存再buffer中
- = 1 表示每次提交都持久化到磁盘
- = 2 表示每次提交都write到page cache
- InnoDB后台线程每隔1s都会将redo log buffer中的内容write到pagecache再fsync到磁盘，所有的线程共用一个redo log buffer，与binlog不同，所以没有提交的事务的redo log，也有可能被持久化到磁盘
- 同样，如果redo log buffer占用innodb_log_buffer_size即将到达一半，就会触发主动写盘，但是只write，没有fysnc
- 如果并行事务，并且innodb_flush_log_at_trx_commit=1，那么别的事务提交的时候，会把自己没有提交的redo log 持久化到磁盘
- 如果=1，那么prepare阶段就会持久化一次，所以只需要每隔1s的刷盘，崩溃恢复的逻辑，只需要binlog写入磁盘，redo log prepare write了，就不需要fsync了
- 如果sync_binlog=1， innodb_flush_log_at_trx_commit=1，那么事务提交前，现需要等两次刷盘，一次是redo log的prepare，一次是binlog
- 也就是如果MySQL``TPS两万，就会有四万次写磁盘，所以使用了组提交的方法减少写盘。
日志逻辑序列号是LSN，单调递增，对应redo log的写入点，写入长度为len，那么LSN + len，可以避免多次重复提交redo log
- 比如三个事务同时在redo log buffer中，事务1写盘的时候，会带上事务23写盘，这样LSN之前的事务都完成了写盘，只需要看LSN后的事务即可
- binlog也是有组提交，但是效果不如redo log，因为redo log时间较短，所以binlog等不到那么多

设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count，满足一个即可，减少binlog的写盘次数，故意等待时间，跟sync_binlog不同，sync是没有写盘就commit事务，但是这两个参数是直到写盘了，才commit事务，会增加响应时间，但是不会像sync一样丢数据
- innodb_flush_log_at_trx_commit不建议为0，这样redo log只会在内存中，宕机了会丢数据
- innodb_flush_log_at_trx_commit设置成2，性能差不多，只有在主机关机才会丢数据，如果服务宕机，重启以后数据还在

binlog不能中断，必须连续写，所以每个线程一个binlog cache，不然主从不一致；redo log甚至可以跟着别的线程一块写，所以都是一个redo log buffer
所以可以sync_binlog=1,innodb_flush_log_at_trx_commit=2,这样redo log如果丢失了，就通过binlog恢复就可以
一般设置非双一，就是innodb_flush_log_at_trx_commit=2、sync_binlog=1000
- 业务高峰期，会有预案主库改写为非双1
- 从库延迟，为了让从库赶上主库，减少binlog生成频率，赶上了可以改回来
- 用备份恢复主库的副本，也就是使用binlog的时候
- 批量导入数据也可以修改，不然binlog=row产生内容太多
主从首先保证了最终一致性，只要主库正确提交，就有binlog，那么从库正常就会重放binlog
主从延迟就是从库执行完的时间点-主库执行完的时间点
从库上执行show slave status，可以看到seconds_behind_master，表示延迟了多少
主从延迟最主要是从库消费relay log的速度比主库生成binlog的速度更慢
主从延迟来源
- 从库性能比主库机器性能差(但是现在一般不会)
- 从库压力大，CPU资源占用较多，导致重放relay log速度变慢
  - 一主多从，让多个从库分担压力
  - binlog输出到外部，比如Hadoop，让外部提供查询能力
- 大事务，比如一个事务再主库上执行了十分钟，就延迟了十分钟
- 大表上DDL

主从复制

5.6版本之前，支持单线程，但是会导致应用日志较慢，造成延迟
多线程就是拆sql thread为多个线程
- 比如coordinator就是原来的sql thread，但是不更新了，只是负责读中转日志，并分发日志，work线程才更新日志，worker线程个数根据slave_parallel_workers决定，一般32核设置8-16即可，
- coordinator分发规则：
  - 不能更新覆盖，同一行的两个事务必须在同一个worker中
  - 不能拆分事务，同一个事务必须在同一个worker中
按表分发策略
- 如果两个事务更新不同的表，就可以分发到不同的worker中
- 如果事务跨表，需要将两张表一起考虑
- 每个worker中对应一张hash表，保存当前worker中正在执行的事务里面涉及的表，值是多少个事务在更新这个表
- 如果coordinator收到事务T，判断其中修改的表
  - 没有worker中包含这些表，那就分给最空闲的worker
  - 如果跟多于一个worker冲突，就等待
  - 如果只跟一个worker冲突，那就分给这个worker
- 所以如果热点表，就变成了单线程，如果负载均衡，就很好解决问题
按行分发策略
- 需要binlog = row
- 必须有主键，不能有外键
- 但是对于大事务，耗费内存，耗费CPU资源更大
5.6版本，按库分发并行
5.7版本slave-parallel-type控制并行策略，配置为DATABASE就是按库并行，配置LOGICAL_CLOCK
- 同处于prepare的事务，备库可以并行
- 处于prepare和commit的事务可以并行

一主多从基本架构
45_ms

主库负责写入和一部分读，从库负责读
切换主库的时候需要找同步位点，5.6版本使用GTID，全局事务ID，在事务提交时生成
两个部分，格式为server_uuid:gno
- server_uuid是实例启动产生的，全局唯一
- gno是整数，初始值为1，每次提交事务的时候分配给这个事务，就加1
- 启动GTID在启动时加上参数gtid_mode=on 和 enforce_gtid_consistency=on即可
  使用基于GTID的主备切换，就不需要指定位点，因为位点是不精确的。所以建议使用基于GTID的主从复制

主从延迟怎么解决

强制走主库方案；
- 比如交易平台，卖家发布商品以后，马上返回主页面，看商品是否发布成功，这个就强制走主库，因为需要拿到最新的结果
- 如果是买家，晚几秒看到商品也可以接受，就走从库
sleep方案
- sleep(1)大部分同步1s内能搞定，但是不太稳妥
判断主备无延迟方案
1. show slave status中的seconds_behind_master是否等于0
  - 单位是秒，如果精度不够，可以使用位点和GTID
2. Master_Log_File和Read_Master_Log_Pos表示读到主库的最新位点
  Relay_Master_Log_File和Exec_Master_Log_Pos表示备库执行的最新位点
  如果Master_Log_File=Relay_Master_Log_File&&Read_Master_Log_Pos=Exec_Master_Log_Pos表示日志同步完成
3. GTID中
  Auto_Position=1表示主备使用了GTID协议
  Retrieved_Gtid_Set是备库收到的所有日志GTID集合
  Executed_Gtid_Set是备库已经执行完的GTID集合
  如果两个集合相同，则日志同步完成
4. 但是上面的方法是判断备库收到的binlog已经执行完了，但是实际上可能主库有binlog，但是备库还没有收到，这时需要使用半同步复制
5. 业务高峰期，主库位点和GTID更新很快，位点判断就会一直不成立，那么从库就一直都无法响应
配合semi-sync方案
- 牺牲一定可用性，保持一致性
- 但是因为半同步只要一个从库同步就返回ack，实际上请求可能打到别的没有同步的从库上
- 所以半同步配合位点方案，会有问题
  - 一主多从，会有过期读的问题
  - 如果一直有延迟，就一直无法select，但是实际上不需要等待到完全没有延迟，而是只要一个事务完成以后，select到哪个事务即可，不需要等待其他事务也同步完
等主库位点方案
- select master_pos_wait(file, pos[, timeout]);
  - file pos是主库上的文件名和位置
  - 从库上执行
  - timeout可选，表示超时
  - 返回一个正整数M，表示命令开始执行，到应用完file和pos表示的binlog位置，一共执行了多少个事务
  - 如果备库挂了，那就返回NULL
  - 如果超时，返回-1
  - 如果执行时，已经执行过这个位置了，返回0
- 具体流程
  - 一个事务执行完，马上在主库上执行show master status得到主库执行到的File和Pos(不需要完全精确)
  - 选一个从库查询
  - 从库上执行select master_pos_wait(File, Pos, 1);
  - 如果返回>=0，则再这个从库上执行查询语句
  - 否则就去主库查询
- 所以如果不允许过期读的情况出现，那么就超时放弃，或者转到主库查询，并做限流
等 GTID 方案
- WAIT_FOR_EXECUTED_GTID_SET(gtid_set[, timeout])，超时时间默认为0，如果为0，表示一直等待
  - 语句作用：等待，直到这个库执行的事务包含传入的gtid_set，返回0
  - 超时返回1
- 5.7版本后，不需要主动执行show master status，而是更新以后返回事务的GTID，减少一次查询
  - session_track_gtids = OWN_GTID
  - 通过API从mysql_session_track_get_first解析GTID即可
- 具体流程
  - 事务完成，接受到事务的GTID，gtid1
  - 选一个从库查询，执行WAIT_FOR_EXECUTED_GTID_SET(gtid1, 1)
  - 返回0就在这个从库查询
  - 否则去主库查询

MySQL笔记_6

语句耗时

幻读

死锁

加锁

举例1 - 等值查询间隙锁

举例2 - 非唯一索引等值查询

举例3 - 主键索引范围查询

举例4 - 非唯一索引范围查询

举例5 - 唯一索引范围查询bug

举例6 - 非唯一索引存在等值

举例7 - limit加锁

举例8 - 死锁

总结

优化

数据更新

主从复制

主从延迟怎么解决