mysql的锁

全局锁（FTWRL）

Mysql提供的针对数据库级别的对数据加读锁的功能:Flush tables with read lock。之后针对这个数据库的增删改、DDL、事物提交语句都会被堵塞住。他主要的用途是用于数据库全库的逻辑备份。

• 优点：
  • 全局锁是数据库级别的，所有表引擎都支持，在数据的导出对库实例加锁，保持导出数据逻辑的一致性。
  • 和设置数据库只读（set global readonly=true）相比，全局锁在当前链接异常或者中断的情况下可以自动释放，而设置数据库只读不能。
• 缺点：导出操作如果对数据库加锁，数据库的变更操作会被lock住，解决方案：对于Innodb引擎，可以用mysql自带的mysqldump工具使用参数–single-transaction.

表级锁

mysql的表级锁分俩种，一种是表锁，一种是元数据锁（meta data lock MDL）

表锁

预发是 lock tables … read/write。可以用unlock主动释放锁。也可以在链接中断时候自动释放。
表锁对于自己和其他线程读写操作的限制如下：如果对一个表进行read/write锁。其他线程在写or读/写时都会堵塞；同时本线程也只能读or读写该表，其他表无法访问。

元数据锁（MDL）

该锁不用显示的使用，在访问表的时候会自动加上，他用来保证访问数据时候数据表结构的稳定性，他有如下特点：

• 对一个表的数据CRUD时候，加MDL读锁；当修改表结构时候，加MDL写锁
• 读锁是不互斥的，因为这些操作不会该表结构，所以可以多个线程同时对表做CRUD；
• 读写锁之间，写锁之间是互斥的，也就是说当对表进行CRUD时候，为了保证返回数据的稳定性，DDL操作是堵塞的。

由于上面MDL的读写锁机制，就会有下面这种情况，修改一个访问量很高的小表，会导致整个库挂掉：
比如:

1. 大量的select语句，加了MDL读锁，这时候是不会堵塞的;
2. 这时候有一条alter表的语句需要执行，加了MDL写锁，开始block。
3. 由于select量很大，alter会一直堵塞，这时候后续的select也会堵塞，很块连接池就被用完了。

解决方法：
MariaDB 已经合并了 AliSQL的方法

1
2

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

行锁

为了加强数据库的并发度引入的锁的机制，他有如下几个特点：

• 行锁是由mysql的表引擎决定，Innodb支持行锁，MyISM不支持行锁。
• Innodb的行锁是两阶段锁：即数据需要锁的时候对数据涉及到的行加锁；在事务结束后才释放行锁；

如何减少行锁的锁冲突

为了减少锁的冲突，我们在事务中要把会引起锁冲突的语句往后放。因为事务是原子性的所以，在一个事务中，要么都成功要么都失败，所以我们在一个事务中根据业务需求可以把一些没有锁冲突的操作或者语句放在事务的前面先只是，可能会造成锁冲突的语句放在后面，减少锁冲突加大并发。

死锁和死锁检测

• 出现原因：循环的资源等待。举个例子：俩个事务，事务1:用户uid=1点赞,事务2：用户uid=1取消赞。如下：这时候在并发时候可能会造成死锁当事务1、事务2都执行完第一条语句时候，这时候事务1等待事务2执行完第二条语句释放，这时候事务2执行完第二条语句等待事务一释放

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

#事务1 点赞
BEGIN TRANSACTION;

UPDATE reply SET like_ammount=like_ammount+1 WHERE reply_id=1 ;

UPDATE user_sum SET like_sum=like_sum+1 WHERE uid=1;

COMMIT;

#事务2取消赞
BEGIN TRANSACTION;

UPDATE user_sum SET like_sum=like_sum-1 WHERE uid=1;

UPDATE reply SET like_ammount=like_ammount-1 WHERE reply_id=1 ;

COMMIT;

• 解决方法
  • 等待锁超时，用innodb_lock_wait_timeout来设置；（默认50s）
  • 用死锁检查的方式innodb_deadlock_detect设置为on，表示开启，一旦发生死锁回滚死锁链条中的某个事务让其他事务可以进行；

死锁检查真的很美好么？

死锁检查是一个O(n*n)的时间复杂度的操作，因为每一个被堵塞的线程都会检查是否是自己导致了死锁。导致会有大量的线程做无谓的死锁检查，最终现象是CPU很高，但是并发度很低。

解决方案：

• 粗暴的做法：如果确定没有死锁情况，关掉死锁检查。
• 控制并发度：客户端自己控制，最多只有N个线程同时进行修改，但是不排除一个库有多个客户端（比如多连接池）
• 中间件开发：如果发现请求数据一样的，进行排队减少冲突。（成本高）

间隙锁(gap lock) 与幻读

什么是幻读？为什么会出现幻读？幻读有什么危害？

• 事物在RR隔离级别下；
• 一个事物中，俩次查询涉及的条件相同，后面的语句出现了之前查询没有存在的数据。（注意：出现了新增的数据）；
• 为什么出现幻读：RR隔离级别下，当数据发生改变，采用当前读的原则
• 幻读的危害：破坏了业务的语意；违反了数据的一致性

为什么需要间隙锁？当数据库的隔离级别是RR的情况下，由于Mysql的行锁在为数据上锁的时候需要数据在表中，也就是说对于新插入的数据mysql是无法上锁的。这样就会出现幻读的情况。innodb为了解决幻读引入了间隙锁。

innodb是如何加间隙锁的

1. 当一个涉及到为数据加锁的语句执行时候，会为涉及到的数据加行锁，同时为整个表的数据之间加间隙锁（gap lock）,这个行锁+间隙锁也叫next-key-lock。
2. 间隙锁是个左开，右闭的空间。比如一张表有如下数据
3. 间隙锁只会锁插入语句，其他的操作是不互斥的。

具体例子说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14

#表t(id, c(有索引))有数据（1，3），（1，5），（1，7），有如下3个事物，是不冲突的 t1 ,t2锁不冲突， t3会被间隙锁block
#间隙锁是(-∞,3],(3,5],(5,7],(7,supernum]

#t1
begion transaction;
select * from  t where c=5 for update;

#t2
begion transaction;
update t set c=1 where c=5 for share mode;

#t3
insert into t values(3,4);

间隙锁的缺点

间隙锁也并不是万能的在某些情况下会造成死锁比如，下面的情况：

1. 判断断C=5是否存在；
2. 不存在就insert,存在就update；
3. 图中在并发的情况下，且c=5不存在，行锁无效，触发间隙锁，这时候事物2的insert需要等待事物1commit之后释放间隙锁。
4. 但是事物1，因为也insert导致了，不能释放锁就触发了死锁。

这种方式的解决方案，在满足业务的前提下，将事物的隔离级别改为RC，并且将binlog置为row。

innodb对于数据加锁的原则

Q:锁是加在数据上还是加载索引中？
A:锁是加载索引中。（因为Mysql的数据是在磁盘中的，而索引是在内存中切有序的。所以按照mysql的能用内存用内存的原则是锁索引的）

1. 加锁的基本单位是next key lock；
2. 查找到访问的对象会加锁；
3. 对于查询等值唯一索引的数据加锁，会退化成行锁；
4. 对于查询等值非唯一索引的数据加锁，当向右扫描到第一个不符合索引的数据时候，会退化成间隙锁；
5. 注意：对于范围查找唯一索引，会访问到不满足条件的第一个值位置；

所以准确的说，innodb在对数据加锁不是简单的为涉及到的数据加行锁，而是对要访问的数据加一个nextKeyLock，然后innodb在根据如上条件判断哪些数据加行锁，哪些数据不是行锁是间隙锁；（确定是查询范围锁的数据是x锁，中间不存在的数据间隙锁）

其他

查询长事物的语句

1

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

mysql更新数据的原理和流程-草稿

本文介绍mysql更新数据的原理和流程

为什么mysql更新的效率很高？

1. WAL机制：即更新内存中数据页，同时写redolog+binglog然后返回。后台慢慢将数据页刷到磁盘中。（随机写改为顺序写）
2. mysql的chagenbuffer。

为什么mysql偶尔会抖一下

刷新脏页
什么叫脏页？
什么时候触发脏页的flush。

1. redolog写满；（尽量避免，因为这时候所有更新都被block主）
2. 内存不足：需要加载新的数据页但是内存不够，将数据页淘汰，如果是脏页，就flush到磁盘；（至于为什么不直接淘汰，下次读的时候从redologmerge是出于性能考虑，每次加载的都是干净的数据页）
3. 正常的时候，定时刷新
4. 正常关闭mysql时候

2是抖一下的原因
buf1fpool数据页三种状态（未使用，使用了但是干净页，使用了但是脏页）。
当数据页没在内存中时候，申请一个页面，如果没有未使用页面就需要淘汰页面，俩种情况，如果淘汰的页面过多，会影响性能

1. 干净页直接淘汰

2. 脏页flush，淘汰

   fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

如何判断MySql的主从出现问题

本章主要介绍如何对MySql主从进行健康检查

SELECT 1

定期在数据库上执行“SELECT 1”,缺点是指能判断MySql的进程是否存在，不能判断实际的使用情况，因为Mysql的并发度往往受参数innodb_thread_concurrency影响：

innodb_thread_concurrency代表数据库的并发度，即同一时间有多少线程并发执行任务；默认是0即无限制，但是为了防止CPU被打满我们往往设置64~1·28中的一个值；
注意一下俩点：

• innodb_thread_concurrency和最大连接数不一样，最大连接数只是保持了链接状态，并没有多消耗CPU资源；
• 当链接进入sleep状态的时候，innodb_thread_concurrency参数会减一，即释放一个并发度；

查表

我们可以在mysql中创建一个表比如讲heal_check，定期执行下面的语句：

1

mysql> select * from mysql.health_check;

但是依然有个缺点，我们不能检查出MySql写的能力。

定期执行修改的方式

1

mysql> update mysql.health_check set t_modified=now();

缺点从库无法判断,我们可以修改下我们的语句用如下的方式定期轮训

1
2
3
4
5
6
7
8

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

缺点，只能被动的轮询无法及时发现问题。

内部统计–performance_schema库

我们可以利用performance_schema，注意如果打开会因为检测导致性能下降10%左右。

我们执行语句可以看到如下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

select * from performance_schema.file_summary_by_event_name where event_name='wait/io/file/innodb/innodb_log_file'\G

*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/innodb/innodb_log_file
               COUNT_STAR: 107  #IO总次数
           SUM_TIMER_WAIT: 101355337720 #单位皮秒
           MIN_TIMER_WAIT: 498680
           AVG_TIMER_WAIT: 947245845
           MAX_TIMER_WAIT: 81456567920
               COUNT_READ: 7 #读
           SUM_TIMER_READ: 1321141640
           MIN_TIMER_READ: 498680
           AVG_TIMER_READ: 188734455
           MAX_TIMER_READ: 790074740
 SUM_NUMBER_OF_BYTES_READ: 70144 #字节
              COUNT_WRITE: 47 #写
          SUM_TIMER_WRITE: 874801200
          MIN_TIMER_WRITE: 12836460
          AVG_TIMER_WRITE: 18612685
          MAX_TIMER_WRITE: 33429760
SUM_NUMBER_OF_BYTES_WRITE: 40960
               COUNT_MISC: 53 #fsync次数--其他
           SUM_TIMER_MISC: 99159394880
           MIN_TIMER_MISC: 891800
           AVG_TIMER_MISC: 1870931790
           MAX_TIMER_MISC: 81456567920

为了节省性能，我们可以只打开我们需要的统计项，执行如下命令，打开redolog和binlog的统计项

1

mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';

检测性能方式如下，比如监控binlog和redolog超过200ms时，即监控MAX_TIMER_WAIT值

1
2
3
4

mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

#获取异常信息时候可以清空表，继续累计
mysql> truncate table performance_schema.file_summary_by_event_name;

mysql排序 order by的原理

原理

下面的语句用按照姓名排序，我们看下执行计划，Extra有一段‘Using filesort’，说明Mysql用的用到了排序

1
2
3
4

explain select city,name,age from t where city='杭州' order by name limit 1000  ;

#执行计划
1 SIMPLE t NULL ref city city 66 const 1 100.00 Using index condition; Using filesort

排序的过程

mysql在排序操作中会用到sort_buffer和临时文件，sort_buffer的大小由sort_buffer_size参数决定

• sort_buffer:是Mysql为排序操作开辟的一块内存空间，会将需要查询的字段以及排序字段放在内存空间中，进行排序操作。
• 临时文件：如果字段太多sort_buffer容量不够了会借用到临时文件进行存储，会将需要排序的文件分成N份，同时排序排序完成后在进行合并。

全字段排序

全字段排序需要的流程

1. 初始化sort_buffer,确定要放进去的字段
2. 根据索引等条件取出满足条件的主键ID
3. 回表查询出需要显示的字段，将需要的字段放到sort_buffer中，如果用到临时文件，加载到临时文件中
4. 内存中排序
5. 返回结果

rowid排序

如果Mysql判断单行占用的空间很大，会采用rowid方式进行排序,可以用如下方式进行设置。受max_length_for_sort_data指标来控制

1

SET max_length_for_sort_data = 16;

1. 初始化sort_buffer,确定排序字段和主键ID
2. 根据索引等条件取出满足条件的主键ID
3. 将需要排序和主键ID放到sort_buffer中，如果用到临时文件，加载到临时文件中
4. 内存中排序，得到排好序的排序字段值–主键ID
5. 回表查询数据
6. 返回结果

全字段排序 vs rowid 排序

rowid比全字段排序多了一次回表操作，理论上来说mysql能用内存尽量用内存，减少磁盘操作，所以理论上来说rowid排序性能会低于全字段排序

如何优化排序

使用索引：因为索引本身是有序的，合理利用索引，这样排序就可以用索引避免使用临时文件和sort_buffer进行排序。如例子中的语句可以创建（city,name）的索引，可以用执行计划看出来。

1
2
3
4
5

#创建索引（city,name）注意顺序
explain select city,name,age from t where city='杭州' order by name limit 1000  ;

#执行计划
1 SIMPLE t NULL ref idx idx 66 const 1 100.00 Using index condition

附录

判断排序语句是否用了临时文件，是那种排序方式的语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';

/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;

如何判断排序类型

1. SELECT * FROM information_schema.OPTIMIZER_TRACE\G，之后的json对象看filesort_summary段
   1. sort_mode是sort_key, rowid是全字段排序
   2. sort_mode是sort_key, packed_additional_fields采用全字段排序

1
2
3
4
5
6
7
8
9
10
11

            "filesort_summary": {
              "rows": 1001, #取的字段
              "examined_rows": 110501, #扫描行数
              "number_of_tmp_files": 0, #历史文件个数
              "sort_buffer_size": 17024,
              "sort_mode": "<sort_key, rowid>"
            }
#如果是rowid排序实际扫描行数是examined_rows+rows（因为回了一次表）

mysql读写分离的坑

本章主要介绍mysql读写分离的架构，读写分离遇到的问题，以及解决方案。

mysql读写分离结构介绍

直连架构

见图：

proxy

见图：

小结：

• 直连的架构，往往后端会有一个管理器（zookeeper）来管理所有的节点，动态的做主从切换，踢掉失联节点，和动态更新节点。
• 客户端链接proxy，业务往往只关注业务逻辑即可。现在大多采用此方案

mysql读写分离架构遇到的挑战以及解决方案

无论采用直连还是proxy架构都会遇到主从不同步的问题，这里我们叫过期读，我们该采用什么方式解决呢？

强制走主库

业务方自己判断某些查询走主库某些查询走备库。比如：刚修改的数据立刻就获取的最新的数据从主库查询；数据分析等离线的情况走从库；

• 优点：比较简单，由业务自己掌控，大部分情况都可以用此种方式解决；
• 缺点：可能会遇到所有查询都需要从主库查询的情况，这样一主多从的架构就失去了作用；

sleep方案

修改和插入数据时候等待一段时间，然后在从备库查询，例如：页面ajax提交了修改请求，页面直接返回结果，当下次刷新时候从从库取出数据；

• 优点：业务方决定，比较简单，且可以利用一主多从
• 缺点：过期时间的不确定性，可能会导致数据一致性有问题

判断主备无延迟方案

在sql语句执行前线判断主库和从库是否有延迟，没延迟读取从库，有延迟读取主库。

判断seconds_behind_master参数

当参数为0说明无延迟，可以从从库读取，但是时间不精确只能精确到秒

通过位点来判断

从库通过show slave status命令取得如下俩对参数：Master_Log_File和Read_Master_Log_Pos，Relay_Master_Log_File和Exec_Master_Log_Pos，当这俩对参数相等说明主备无延迟，

通过gtid来判断

• Auto_Position=1 ，表示这对主备关系使用了GTID
• Retrieved_Gtid_Set，表示备库收到的日志GTID_SET
• Executed_Gtid_Set，表示备库执行完成的GTID_SET

当着俩个集合相等说明主备无延迟

但是，这种方案有个缺点就是，备库判断的是自己的,relaylog和binlog的差异，但是如果这时候主库提交了个事物，备库还没有转成relaylog，备库会认为没有延迟，但是依然从备库差不到刚才主库刚提交的事务的数据，如下图：

tx3事务的数据主库提交了，但是备库会看不到

结合semi-sync方案

上图的问题，结合semi-sync（办同步）方案可解决，semi-sync原理是什么呢？

1. 开启semi-sync后，主库在提交时候后会发送binlog给备库
2. 备库收到binlog以后，发给主库一个ack信号，说明已经收到
3. 主库收到这个信号后才能给客户端返回事务已经提交

这种方案会有如下俩个缺点：

1. 对于一主多从的结构，当一个从库发送ack信号给主库，主库会返回一个事务已经提交，这时候如果从其他从库查询还是有可能出现过期读；
2. 如果数据库更新频繁，你会发现g
3. tid或者位点主从会一直不相等，这样就失去了从库读写分离的意义。

等主库位点（gtid）

我们针对上面的痛点可以采用等主库位点或者gtid的方式来处理这种情况

等主库位点

命令:select master_pos_wait(file, pos[, timeout]);

• 该命令含义：返回从命令执行开始，经过timeout秒后，从库执行到file（binlog）的pos位置执行了多少个事物。
• 返回值含义:
  • NULL:失联
  • -1：超时
  • 0：执行即到了改位置。执行了0个事物
  • N：从库到这个位置执行了N个事物。

具体流程如图:

1. tx1提交后，在master上执行show master status;获取主库的binlog和binlog_pos。
2. 在从库上执行查询语句前，先执行select master_pos_wait(file, pos[, timeout]);
3. 如果返回>=0,slect在从库执行，否则主库执行

等GTID

命令:select wait_for_executed_gtid_set(gtid_set, timeout);

• 该命令含义：等待从库timeout秒后，执行的gtid_set包含了gtid_set
• 返回值含义:
  • 0：包含
  • 1：超时

具体流程如图:

1. tx1提交后，在master上执行show master status;获取主库的gtid_set。（5.7后可以通过函数直接获取该事务返回的gtid,具体见后面）
2. 在从库上执行查询语句前，先执行select wait_for_executed_gtid_set(gtid_set, timeout);
3. 如果返回0从库，1主库

注：5.7之后获取事务提交后gtid的方法

set session_track_gtids=OWN_GTID
执行事务并且提交
客户端通过函数：mysql_session_track_get_first 这个函数只针对客户端调用

注：开启semi-sync待完善

shell的积累

日常工作中linux的命令的积累

删除N天的文件的命令-用于清理log

1

find ./ -mtime +1 -name "*.log*" -exec rm -rf {} \;

按天筛选log

1

sed -n '/2018-10-15 16:55:00/,/2018-10-15 23:59:59/p' log_path |grep "condition"

合并redis，用于单机环境的redis导入

1
2
3
4
5
6

#!/bin/bash
redis-cli -h srcIp -p 6379 -n 0 keys "*" | while read key
do
    redis-cli -h srcIp -p 6379 -n 0 --raw dump $key | perl -pe 'chomp if eof' | redis-cli -h dest -p 6379 -n 1 -x restore $key 0
    echo "migrate key $key"
done

mysql事务的隔离级别以及实现原理

事务基础

启动事务的几种方式

1. 显式启动事务语句， begin 或 start transaction，提交是commit，回滚是rollback
2. set autocommit=0,每个CURD都会启动事务且需要手动commit/rollback。
3. 在实际使用用，我们一般不采用2这种方式启动事务。

如何查看mysql事务的隔离级别，下面说明事务的隔离级别是读提交

1
2
3
4
5
6
7
8
9
10
11

show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

事务的几种隔离级别

mysql的事务隔离级别分为：读未提交、读已提交（RC）、可重复读（RR）、串行，他们具体表现如下：

• 读未提交：事务中每个查询语句都获取当前数据的最新值。
• 读已提交：事务中每个查询语句获取的都是其他事务提交后的相关数据的最新值，Oracle的默认事务隔离级别。
• 可重复读：Mysql的Innodb的默认事务隔离级别。
• 串行：每个事务需要一个一个的执行。

mysql事务隔离方式的实现方式（只讨论RC 和 RR）

mysql的事务隔离实现机制采用的是一致性读视图（consistent read view）。即Mysql在事务启动时对整个数据库拍了一个快照。

• RR是在事务开始时候创建一致性视图
• RC是在事务中的每条sql语句执行前执行

具体的实现逻辑如下：

1. Innodb对没个每个事务会分配一个transaction id,该ID是严格自增的ID。
2. Innodb中每行数据是有多版本的，每个版本的数据会包含一个row_txid。将修改成这个版本数据的transaction id赋值给row_txid。
3. 这个版本不是物理存在的是虚拟的，即在一个事务中一旦发现该行数据不可见，则需要根据row_txid对象的事务+undolog找到可见的数据，具体见下面5。
4. 当一个事务启动时候会，会维护一个数组，这个数组包含这个事务开始之后，这时候所有活跃的transaction id（所谓即未提交的事务）。数组的取值范围如下：低水位是当前事务中最小的transId，高水位数组中最大的transaction id+1；数组关系可见下图：
5. 当这个事务去读数据时候，会有如下情况：
   1. 判断如果这条数据row tx_id<低水位，说明当前的数据在本事务创建前已经被提交过了，所以可见；
   2. 判断如果这条数据row tx_id>高水位，说明当前的数据在本事务创建之后被提交过了，所以不可见，需要通过undolog找回之前的版本；
   3. 判断如果这条数据row tx_id在数组区间，如果row tx_id是数组范围内的值，说明事务未提交过，所以不可见；发只，说明该数据是已经被其他事务提交生成的，所以可见；

上面的规则比较拗口，翻译过来就是：如果数据的版本是先于本事务开始前生成的可见，如果后于本事务开始前生成的本可见。

mysql如何保证多个事务同时修改一条数据的准确性

思考下面问题：

1
2

因为mysql在update时候实际上是先select在更新，所以在RR隔离级别中。
俩个同时开始未提交的事务修改同一条语句这时候因为一致性视图，会不会导致一个事务的修改是无效的呢？

答案是不会的。
因为mysql在修改时会遵循一个原则：读当前值，当更新数据时候都是先读后写，所以这时候值能读当前值。俩个事务同时更新一条数据的流程和原理如下：

1. 当并发进行更新时，对数据进行加x锁，后面的更新操作会block住，直到其他的事务提交后才继续下去；
2. 由于更新是当前读，所以数据被本事务被更新过后，由于row tx_id就是当前事务，所以读到的值是新的值；

隔离级别RR如此那RC呢

其实原理是一样的。只是RR是每一次事务开始时候创建一致性视图，而隔离级别RC则是在事务中每一条sql语句在执行前都要创建一个一致性视图，这样视图是动态的，每条sql语句根据上面提到的规则都要去确定数据的可见性。

MySql的主从切换

大部分业务数据库都是读多写少，我们在实际使用情况上往往会遇到读性能，本章主要讲述如何解决读延迟

一主多从的架构

我们一般会采取一主多从架构，主库承担所有的写和一部分读的任务，丛库根据承担读的任务。

如图：

1. A和A’互为主备
2. B、C、D是A的从库

下面我们来通论下当主库A发生故障后，A’成为主库，BCD的主库也要都指向A’，下面我们来讨论下遇到这种情况，mysql是如何做主从切换的。
如图：

基于位点的主备切换

当把B置为A’的从库时候要执行如下命令

1
2
3
4
5
6
7

CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
MASTER_LOG_FILE=$master_log_name
MASTER_LOG_POS=$master_log_pos

• host_name、port、user_name、password主库的IP、端口、用于主从同步的数据库用户名、密码
• master_log_name，和master_log_pos就是A’库binlog的文件名和位置成为位点；

原来的B是A的从库，所以B中记录的是A的位点，在change master之后，B要将位点置为A’的位点，由于A和A’记录的位点不一样，所以要大概估算出一个靠前一点的位点，在忽略掉主从不同步的情况。为什么要靠前一点的主要是有下面这种情况，下面语句是分析mysqlbinlog找到位点

1
2
3

mysqlbinlog File --stop-datetime=T --start-datetime=T

#比如这是看到了pos是123，我们B在chenagemaster时候可以往前一点比如110；

在A插入了一行R，同时同步给B和A’这时候A出现故障，B和A’都已经有了R行数据，如果这时候changemaster会提示主键冲突，停止同步。

解决的方案：

1. 主动跳过1个事务,如下的命令，每次遇到这样的错误都跳过知道最后没有错误为止
2. 通过设置slave_skip_errors参数，直接跳过指定错误，常见的错误如下，可以直接跳过“1062，1032”
   1. 1062 错误是插入数据时唯一键冲突；
   2. 1032 错误是删除数据时找不到行。

1
2

set global sql_slave_skip_counter=N;
start slave;

注意：前提是这俩种操作是对业务无损的。

GTID– since v5.6

基于位面做主从切换比较复杂且很容易出错，所以mysql在5.6后引入了gtid彻底解决了这个问题。

GTID全程是Global Transaction Identifier,即全局事物ID，即一个事物提交后生成的全局唯一ID，他有俩个部分组成

GTID=server_uuid:gno（官方定义：GTID=source_id:transaction_id）
其中：

1. server_uuid是mysql的实例ID
2. gno是一个递增的整数，

如何启动gtid呢？

1
2

gtid_mode=on
enforce_gtid_consistency=on

下图就是我在主库上执行了一条插入看到的binlog,其中SET @@SESSION.GTID_NEXT= ‘b01b34d1-25cf-11e9-8e68-9ec1c2413262:1’就是这条事物的GTID

1
2
3
4
5
6
7
8
9
10
11
12

mysql> show binlog events IN 'mysql-bin.000028';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000028 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.25-log, Binlog ver: 4                             |
| mysql-bin.000028 | 123 | Previous_gtids |         1 |         154 |                                                                   |
| mysql-bin.000028 | 154 | Gtid           |         1 |         219 | SET @@SESSION.GTID_NEXT= 'b01b34d1-25cf-11e9-8e68-9ec1c2413262:1' |
| mysql-bin.000028 | 219 | Query          |         1 |         293 | BEGIN                                                             |
| mysql-bin.000028 | 293 | Table_map      |         1 |         340 | table_id: 108 (liuhao.t)                                          |
| mysql-bin.000028 | 340 | Write_rows     |         1 |         384 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000028 | 384 | Xid            |         1 |         415 | COMMIT /* xid=23 */                                               |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

gtid的分配方式:

1. 当gtid_next=automatic时候，代表使用默认值:
   1. 记录binlog时候先记录一行SET @@SESSION.GTID_NEXT=
   2. 把这个gtid加入到本地实例的gtid集合中（Executed_Gtid_set）
2. 如果gtid_next=是一个指定的gtid值时候，比如通过set gtid_next=’current_gtid’指定为当前的current_gtid时候，
   1. 如果current_gtid已经存在于gtid集合，接下来执行这个事务会跳过这个事务；
   2. 如果current_gtid不存在于gtid这个集合中，将这个current_gtid分配给新的事务，说明系统不需要分配新的gtid,gno也不需要加1

gtid的使用方法

加入一个库X是有一条数据（1,1),他的的gtid=aaaaaaaa-cccc-dddd-eeee-0000000000000:1
同时X作为了Y的从库，Y写入了一条数据（1,1）,他的gtid=aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10

我们在X上执行如下的命令

1
2
3
4
5
6
7
8
9

#将X的gtid_next置为Y库的aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10
set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
#提交一个空事物，把“aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”这个gtid置为X的Executed_Gtid_set中，遇到冲突可以跳过
begin;
commit;
#置gtid_next为automatic,下一个事物就变为了“aaaaaaaa-cccc-dddd-eeee-ffffffffffff:11”
set gtid_next=automatic;
#开启从库
start slave;

基于GTID的主备切换

执行如下命令注意,master_auto_position表示的这个主备关系支持GTID协议，我们之前的MASTER_LOG_FILE和MASTER_LOG_POS已经不需要指定了。

1
2
3
4
5
6

CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
master_auto_position=1

B通过CHANGE MASTER变为A’的从库，B的gtid_set为set_b，A的gtid_set为set_a，在start slave时候逻辑如下：

1. A和B建立主备关系;
2. B把set_b发个A’;
3. A’计算set_a和set_b的差集，差集说明是B需要执行的binlog,同时判断A是否包含了这些binlog
   1. 不包含，直接报错，说明A已经把这些BINLOG删除了
   2. 如果全部包含，A’从自己的binlog里找出第一个差集中的binlog开始同步
4. 之后从这个事务开始一直往B同步

这种方式，由之前通过位点同步的，由从库来决定从哪里同步，到主库通过获取从库的gtid来计算出从哪里开始同步。

Spring多线程执行任务

用Spring执行定时任务，spring默认是单线程执行的也就是说，多个任务会存在等待阻塞情况

多线程方案

用如下方法注入

1
2
3
4
5
6
7
8
9

@Configuration
public class ScheduleConfigure implements SchedulingConfigurer {

    @Override
    public void configureTasks(ScheduledTaskRegistrar taskRegistrar) {
        //设定一个长度10的定时任务线程池
        taskRegistrar.setScheduler(Executors.newScheduledThreadPool(2));
    }
}