《MySQL实战45讲》学习笔记 16~31讲

June 22nd, 2019 by JasonLe's Tech Leave a reply »

16 | “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;
 

假设按照下面的sql查询并排序。 select city,name,age from t where city=’杭州’ order by name limit 1000 ;

Extra中”Using filesort”表示排序,mysql会给每个线程分配一个块内存(sort_buffer)用来排序。
city索引示意图:

sql执行过程:

  1. 初始化sort_buffer,确定放入name、city、age 这三个字段;
  2. 从city索引找到第一个city=’杭州’的主键id,图中的ID_X;
  3. 根据id去聚集索引取这三个字段,放到sort_buffer;
  4. 在从city索引取下一个;
  5. 重复3、4查询所有的值;
  6. 在sort_buffer按name快速排序;
  7. 按照排序结果取前1000行返回给客户端。
  8. 如果sort_buffer太小,内存放不下排序的数据,则需要使用外部排序,利用磁盘临时文件辅助排序。这取决于排序所需内存和参数 sort_buffer_size。

下面方法可以确定排序是否使用临时文件:

<php>

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;
</php>

通过查看 OPTIMIZER_TRACE,number_of_tmp_files表示排序使用的临时文件数,外部排序一般使用归并排序算法。
rows表示满足city=’杭州’有4000条,examined_rows=4000表示4000行参与排序。
sort_mode packed_additional_fields表示排序过程字符串做了“紧凑”处理。name字段定义varchar(16),排序过程中按照实际长度分配空间。
最后一个查询语句 select @b-@a返回结果是 4000,表示只扫描了4000行。

这边老师把internal_tmp_disk_storage_engine 设置成MyISAM,否则,select @b-@a结果为 4001。因为innodb把数据从临时表取出来时,会让Innodb_rows_read 的值加 1。

rowid 排序

如果排序的单行长度太大mysql会使用另一种算法。

SET max_length_for_sort_data = 16;

city、name、age 这三个字段的定义总长度是 36 > max_length_for_sort_data,所以会使用别的算法。
该算法和全字段排序的差别:

sort_buffer只会确定放入name 和 id字段,所以只会取这两个字段。
最后根据name排完序,会根据id字段去原表取city、name 和 age 三个字段返回给客户端。
需要注意,不做合并操作,而是直接将原表查到的字段返回给客户端。
和上述过程对比:

examined_rows和rows没有变化,但select @b-@a会变成5000。因为排完序需要去原表再取1000行。

全字段排序 VS rowid 排序
对于 InnoDB 表来说,rowid 排序会要求回表多造成磁盘读,因此不会被优先选择。
假设从city索引上取出来的行天然按照name递增排序,就不需要再进行排序了。
所以可以建一个city和name的联合索引:alter table t add index city_user(city, name);

整个查询流程就变成了:

  1. 从索引(city, name)找到第一个city=’杭州’的主键id;
  2. 到聚集索引取name、city、age三个字段,作为结果集一部分直接返回;
  3. 从索引(city, name)取下一个。
  4. 重复2、3,直到查到1000条记录,或不满足city=’杭州’时结束。

没有”Using filesort”。
使用覆盖索引:alter table t add index city_user_age(city, name, age);

但维护索引是有代价的,所以需要权衡。

小结

mysql> select * from t where city in (‘杭州’,” 苏州 “) order by name limit 100;

上述sql需要排序,因为name不是递增的。
可以将sql拆分成两条,最后通过程序内存取前100条。
进一步,如果需要分页,“limit 10000,100”,则可以使用下面的思想:

select * from t where city=” 杭州 ” order by name limit 10100;
select * from t where city=” 苏州 ” order by name limit 10100。

根据,name排序,然后取10001~10100,但这样返回的数据量较大,所以可以改成:

select id,name from t where city=” 杭州 ” order by name limit 10100;
select id,name from t where city=” 苏州 ” order by name limit 10100。

根据,name排序,然后取10001~10100,然后在通过id查询100条数据。

另外
评论区大神多,特别是@某、人,看到好多次了。下面是他的回答:
问题一 :这种无条件查列表页除了全表扫还有其他建立索引的办法么
1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。
因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序
2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.
因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。这部分老师明天会讲
问题二 : 如果加入 group by , 数据该如何走
如果是group by a,a上不能使用索引的情况,是走rowid排序。
如果是group by limit,不能使用索引的情况,是走堆排序
如果是只有group by a,a上有索引的情况,又根据选取值不同,索引的扫描方式又有不同
select * from t group by a –走的是索引全扫描,至于这里为什么选择走索引全扫描,还需要老师解惑下
select a from t group by a –走的是索引松散扫描,也就说只需要扫描每组的第一行数据即可,不用扫描每一行的值
问题三 :老师之后的文章会有讲解 bigInt(20) 、 tinyint(2) 、varchar(32) 这种后面带数字与不带数字有何区别的文章么 。 每次建字段都会考虑长度 ,但实际却不知道他有何作用
bigint和int加数字都不影响能存储的值。
bigint(1)和bigint(19)都能存储2^64-1范围内的值,int是 2^32-1。只是有些前端会根据括号里来截取显示而已。建议不加varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。

17 | 如何正确地显示随机消息?

mysql> CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
declare i int;
set i=0;
while i<10000 do
insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
set i=i+1;
end while;
end;;
delimiter ;

call idata();

需求:每次随机获取三个word;

内存临时表

mysql> select word from words order by rand() limit 3;

explain

这个 Extra 的意思就是,需要临时表,并且需要在临时表上排序。
上一篇文章的一个结论:对于 InnoDB 表来说,执行全字段排序会减少磁盘访问,因此会被优先选择。
**对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。**所以,MySQL 这时就会选择 rowid 排序。
上述sql的执行流程:

  1. 创建一个memory引擎的临时表,第一个字段double类型,假设字段为R,第二个字段varchar(64),记为字段W。并且这个表没有索引。
  2. 从 words 表中,按主键顺序取出所有的 word 值。对于每一个 word 值,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并把这个随机小数和 word分别存入临时表的 R 和 W 字段中,到此,扫描行数是 10000。
  3. 接着在没有索引的内存临时表上,按字段R排序。
  4. 初始化sort_buffer。sort_buffer和临时表一直两个字段。
  5. 临时表全表扫描去取R值和位置信息(稍后解释),放入sort_buffer两个字段,此时扫描行数增加10000,变成20000。
  6. 在sort_buffer对R值排序。
  7. 排序完成取前三行,总扫描行数变成20003行。

通过慢查询日志(slow log)可以看到

# Query_time: 0.900376 Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

磁盘临时表

tmp_table_size限制了内存临时表的大小,默认16M。如果内存大于tmp_table_size,则会转成磁盘临时表。
磁盘临时表使用的引擎默认是 InnoDB,由参数 internal_tmp_disk_storage_engine 控制。
复现:

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace,只对本线程有效 */
SET optimizer_trace=’enabled=on’;
/* 执行语句 */
select word from words order by rand() limit 3;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

部分PTIMIZER_TRACE 的结果如下:

由于max_length_for_sort_data 设置成 16,所以参与排序的是R字段和row_id字段组成的行。
R字段8个字节,rowid是6个字节,总行数10000,这样总共140000字节,超过sort_buffer_size,但没有使用临时文件。
是因为MySQL 5.6 版本引入的一个新的排序算法,即:优先队列排序算法。
因为sql只需要去R值最小的3个rowid,所以不需要将所有的数据排序,所以没有使用临时文件(归并排序算法)。

优先级队列算法执行流程如下:

  1. 先取前三行,构造成一个堆。
  2. 取下一行(R’,rowid’),跟当前堆最大的R比较,如果 R’小于 R,把这个 (R,rowid)从堆中去掉,换成 (R’,rowid’);
  3. 重复第 2 步,直到第 10000 个 (R’,rowid’) 完成比较。

上图OPTIMIZER_TRACE 结果中,filesort_priority_queue_optimization 这个部分的chosen=true,就表示使用了优先队列排序算法。select city,name,age from t where city=‘杭州’ order by name limit 1000;

这句sql没有使用优先队列排序算法,因为limit 1000堆大小超过了sort_buffer_size 大小。

随机排序方法

随机选取一个word值。

mysql> select max(id),min(id) into @M,@N from t ;

set @X= floor((@M@N+1)*rand() + @N);

select * from t where id >= @X limit 1;

取 max(id) 和 min(id) 都是不需要扫描索引,而第三步的 select 也可以用索引快速定位,可以认为就只扫描了3行。
但id中间可能有空洞,所以不同行概率不一样。
所以,为了得到严格随机的结果,你可以用下面这个流程:

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat(“select * from t limit “, @Y, “,1”);
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来,丢掉前 Y 个,然后把下一个记录作为返回结果,此这一步需要扫描 Y+1 行。
再加上,第一步扫描的 C 行,总共需要扫描 C+Y+1 行,执行代价比第一个随机算法的代价要高。
另外一个思路:

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1; // 在应用代码里面取 Y1、Y2、Y3 值,拼出 SQL 后执行
select * from t limit @Y2,1;
select * from t limit @Y3,1;

18 | 为什么这些SQL语句逻辑相同,性能却差异巨大

案例一:条件字段函数操作

不要对字段进行计算。select * from tradelog where id + 1 = 10000; select count(*) from tradelog where month(t_modified)=7;

案例二:隐式类型转换

数据类型与字段类型不同的,将导致全表扫描。//判断mysql怎么进行数据类型转换,下面的字符串会转成数字,返回1 select10> 9;

案例三:隐式字符编码转换

两张表编码格式不一致也会导致全表查询。

19 | 为什么我只查一行的语句,也执行这么慢?

本节表结构和数据。

mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i,i)
set i=i+1;
end while;
end;;
delimiter ;
call idata();

第一类:查询长时间不返回

等MDL锁

show processlist;

//或下面sql,可以找出pid(设置 performance_schema=on,相比于设置为 off 会有 10% 左右的性能损失)

select blocking_pid from sys.schema_table_lock_waits;

等flush

select * from information_schema.processlist where id= ‘pid’;

如果查到如下图所示,则表示有线程正要对表进行flush操作。

MySQL 里面对表做 flush 操作的用法,一般有以下两个:flush tables t with read lock; flush tables with read lock;

等行锁 select * from t sys.innodb_lock_waits where locked_table=table_name \G

KILL pid 断开连接,隐含逻辑自动回滚这个连接里面正在执行的线程,释放行锁。

第二类:查询慢

select * from t where c=50000 limit 1;

如果字段c上没有索引,这个语句只能走id主键顺序扫描,需要扫描5万行。
扫描一行却很慢的语句

session B执行100万次后,生成了100万个undo log,所以第一个select 快照读要将undo log执行100万次回到快照的版本。而第二个select当前读。

https://www.cnblogs.com/rjzheng/p/9950951.html

20 | 幻读是什么,幻读有什么问题?

幻读是什么?

当前读,新插入的行。

间隙锁和next-key lock

innodb为了解决幻读加入了间隙锁,锁住一个索引区间(开区间)。
锁住索引记录的区间,或第一条索引记录之前的范围,或者最后一条索引记录之后的范围。

间隙锁和行锁合成next-key lock,前开后闭区间。

如下,间隙锁的引入容易导致死锁。
因为select for update会加入间隙锁。

begin; select * from t where id=N for update; /* 如果行不存在 */

insert into t values(N,N,N); /* 如果行存在 */

update t set d=N set id=N;

commit;

如果业务可以容忍可重复读,可使用重复读+binlog 格式设置为 row,可重复读没有间隙锁。

21 | 为什么我只改一行的语句,锁这么多?

两个“原则”、两个“优化”和一个“bug”

  1. 原则 1:加锁的基本单位是next-key lock。
  2. 原则 2:查找过程中访问到的对象才会加锁。
  3. 优化1:索引等值查询,唯一索引,行锁。
  4. 优化2:索引等值查询,向右遍历且最后一个值不满足等值条件时,next-key lock 退化为间隙锁。
  5. 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

limit加锁
limit删除数据时,只会扫描limit行数,不会继续扫描,所以加锁粒度更小。
在删除数据时,尽量加limit。

22 | MySQL有哪些“饮鸩止渴”提高性能的方法

短连接风暴

max_connections
wait_timeout 参数,一个线程空闲这么多秒后自动断开连接。

  • 断开占着连接不工作线程,先考虑事务外进程。服务端主动断开连接,客户端不一定能正确处理。
  • 减少连接过程的消耗,–skip-grant-tables 参数,不安全。

慢查询性能问题

  • 索引问题
    建索引
    主备架构,先增加备库索引。更新前执行set sql_log_bin=off。
  • 语句问题
  • qps突增问题

23 | MySQL是怎么保证数据不丢的?

binlog写入机制

上图说明事务提交时,执行器把 binlog cache 里的完整事务写入到 binlog 中,并清空binlog cache。
图中的write只是写文件系统的page cache。

write 和 fsync 的时机,是由参数 sync_binlog 控制的:

  1. sync_binlog=0 的时候,表示每次提交事务都只 write,不 fsync;
  2. sync_binlog=1 的时候,表示每次提交事务都会执行 fsync;
  3. sync_binlog=N(N>1) 的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync。

redo log写入机制

redo log buffer不需要每次都持久化硬盘,mysql异常重启,这部分日志就会丢失。
未提交的事务可能会被持久化到硬盘。

关于控制刷盘的innodb_flush_log_at_trx_commit参数,在02 | 日志系统:一条SQL更新语句是如何执行的中提到过。
Innodb还有一个后台线程,每隔一秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的件系统的 page cache,然后调用 fsync 持久化到磁盘。

除了后台线程每秒一次的轮询,还有两个场景会让一个没有提交的事务的redo log刷盘。

  1. 当redo log buffer占用空间即将达到innodb_log_buffer_size一半时,后台线程会主动刷盘。该动作只是写到page cache。
  2. 并行事务提交时,会顺带刷盘。A事务写了一些redo log buffer,另一个事务B提交,innodb_flush_log_at_trx_commit=1,所以事务B要把redo log buffer的日志全部刷盘。这时会把事务A在redo log buffer日志一起刷盘。

如果把innodb_flush_log_at_trx_commit设置成1,redo log在prepare需持久化一次,所以在15 | 答疑文章(一):日志和索引相关问题中,提到redo log 已经prepare,并且已经写完binlog就可以异常恢复。

每秒一次后台轮询刷盘,再加上崩溃恢复的逻辑,InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了,只会 write 到文件系统的 page cache 中就够了。

组提交(group commit)机制
日志逻辑序列号(log sequence number,LSN)是单调递增的,用来对应 redo log 的写入点。每次写入长度为 length 的 redo log,LSN 的值就会加上 length。
LSN 也会写到 InnoDB 的数据页中,来确保数据页不会被多次执行重复的 redo log。

上图三个并发事务(trx1,trx2,trx3)在prepare阶段写完redo log buffer,持久化到磁盘的过程,对应的 LSN 分别是 50、120 和 160。当trx1开始刷盘,trx1会被选为leader,这个组有三个事务,这时LSN变成160,trx1写盘时会把LSN小于160的redo log都持久化,这时trx2和trx3可以直接返回。

实际上write是两步,如下图。

所以binlog也可以组提交,不过通常情况第三步执行较快,binlog组提交效果较差。所以可以优化以下两个参数。

binlog_group_commit_sync_delay参数,表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数,表示累积多少次以后才调用 fsync。

不建议将innodb_flush_log_at_trx_commit设置成0,设置成2只是多一个写page cache,效率相差不大,但2在mysql异常重启不会丢数据,只在主机掉电才会丢数据。

如果 binlog 写完盘以后发生 crash,这时候还没给客户端答复就重启了。等客户端再重连进来,发现事务已经提交成功了,这不是 bug。
数据库的 crash-safe 保证的是:

如果客户端收到事务成功的消息,事务就一定持久化了;
如果客户端收到事务失败(比如主键冲突、回滚等)的消息,事务就一定失败了;
如果客户端收到“执行异常”的消息,应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部(数据和日志之间,主库和备库之间)一致就可以了。
关于1应该是不一定的。

两个留言:
sync_binlog = N:每个事务write后就响应客户端了。刷盘是N次事务后刷盘。N次事务之间宕机,数据丢失。
binlog_group_commit_sync_no_delay_count=N: 必须等到N个后才能提交。换言之,会增加响应客户端的时间。但是一旦响应了,那么数据就一定持久化了。宕机的话,数据是不会丢失的。

innodb的 redo log 在commit的时候不进行fsync,只会write 到page cache中。当sync_binlog>1,如果redo log 完成了prepare持久化落盘,binlog只是write page cache,此时commit标识完成write 但没有落盘,而client收到commit成功,这个时候主机掉电,启动的时候做崩溃恢复,没有commit标识和binglog,事务会回滚。sync_binlog设置为大于1的值,会丢binlog日志,此时数据也会丢失。

24 | MySQL是怎么保证主备一致的?

MySQL 主备的基本原理

binlog 的三种格式对比
表结构:

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

insert into t values(1,1,’2018-11-13′);
insert into t values(2,2,’2018-11-12′);
insert into t values(3,3,’2018-11-11′);
insert into t values(4,4,’2018-11-10′);
insert into t values(5,5,’2018-11-09′);

mysql> delete from t /*comment*/ where a>=4 and t_modified<=’2018-11-10′ limit 1;
当 binlog_format=statement 时,binlog 里面记录的就是 SQL 语句的原文。mysql> show binlog events in ‘master.000001’;

delete 命令的执行效果图:

statement 格式,并且语句中有 limit,这个命令可能是 unsafe 的,可能会出现主备数据不一致。
比如上面那个delete:

如果delete使用索引A,那么会根据索引 a 找到第一个满足条件的行,也就是说删除的是a=4 这一行;
但如果使用的是索引 t_modified,那么删除的就是 t_modified=’2018-11-09’也就是 a=5这一行。
因为主备使用索引不一致会导致删除不同数据。

binlog_format=‘row’

  1. Table_map event,用于说明接下来要操作的表是test 库的表 t;
  2. Delete_rows event,用于定义删除的行为。

上图显示事务从8900开始,借助mysqlbinlog 工具,可以看到详细的log。mysqlbinlog vv data/master.000001 –start-position=8900;

  • 事务在server id 1这个库执行。
  • 每个event都有crc32的值,binlog_checksum控制。
  • Table_map event表示打开的表,map到数字226,如果有多张表,每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字。
  • mysqlbinlog -vv 参数是为了把内容都解析出来,所以从结果里面可以看到各个字段的值(比如,@1=4、 @2=4 这些值)。
  • binlog_row_image 的默认配置是 FULL,因此 Delete_event 里面,包含了删掉的行的所有字段的值。如果设置为 MINIMAL,只会记录 id=4 这个信息。

为什么会有 mixed 格式的 binlog?
因为row格式如果删除10万行,就要记录10万条记录到binlog,会占据大量的存储空间和IO资源。
mixed 格式会判断sql是否会引起主备不一致,有可能,就用 row 格式,否则就用 statement 格式。

主流还是使用row 格式,因为该格式可以恢复数据。
用 binlog 来恢复数据的标准做法是,用 mysqlbinlog 工具解析出来,然后把解析结果发给整个MySQL 执行。类似下面的命令:mysqlbinlog master.000001 –start-position=2738 –stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

循环复制问题

实际生产上使用比较多的是双 M 结构。

业务逻辑在节点 A 上更新了一条语句,然后再把生成的 binlog 发给节点 B,节点 B 执行完这条更新语句后也会生成 binlog。(参数 log_slave_updates 设置为 on,表示备库执行 relay log 后生成 binlog)。
下面逻辑可以解决两个节点间的循环复制的问题:

  • 规定两个库的 server id 必须不同;
  • 一个备库接到 binlog 并在重放的过程中,生成与原 binlog 的 server id 相同的新的 binlog;
  • 每个库收到主库发来的日志,判断server id是否和自己相同,相同直接丢弃日志。

25 | MySQL是怎么保证高可用的?

主备延迟

  1. 主库A完成事务写入binlog,这个时刻记为T1;
  2. 之后传给备库B,备库接受完binlog的时刻记为T2;
  3. 备库B执行完这个事务记为T3。

所谓主备延迟,就是同一个事务T3-T1。
在备库执行show slave status 命令,seconds_behind_master显示了当前备库延迟,精度秒。

延迟来源:

  • 为了省钱,备库机器较差。
  • 备库常用来读,查询压力大。一般可以这样处理:一主多从,或者通过binlog输出到外部系统,比如Hadoop。
  • 大事务,因为主库上必须等事务执行完成才会写入binlog。PS. 关于第三点,我在实习的时候就吃过这个亏,说多了都是泪。还好不是重要的库。
  • 大表DDL。
  • 主备延迟的一个大方向原因,备库的并行复制能力。

可靠性优先策略

优先考虑。
在上图双M结果下,从状态1到状态2切换的详细过程:

  • 判断备库B现在的seconds_behind_master,如果小于某个值(比如 5 秒)继续下一步,否则持续重试这一步;
  • 把主库A改成只读状态,即把readonly 设置成true;
  • 判断备库 B 的 seconds_behind_master的值,直到这个值变成 0 为止;
  • 把备库 B 改成可读写状态,也就是把 readonly 设置为 false;
  • 把业务请求切到备库 B。

上述切换流程,一般由专门的HA系统完成,但会存在一段时间都不可用时间。

可用性优先策略

如果强行把步骤4、5调整到最开始执行,这样就几乎不存在不可用时间,但会引起数据不一致。
数据不一致的例子,表结构如下(自增id):

mysql> CREATE TABLE `t` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`c` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t(c) values(1),(2),(3);

执行两条插入语句:

insert into t(c) values(4);
insert into t(c) values(5);

假设,现在主库其他数据表有大量更新,主备延迟达到5秒。在插入一条c=4语句,发起主备切换。
下图,binlog_format=mixed时

步骤2,主库A执行完insert,插入一行(4,4),之后开始进行主备切换。
步骤3,由于5秒延迟,备库还没来得及应用“插入c=4”这个中转日志,就开始接受客户端“插入c=5”的命令。
步骤4,备库B插入一行(4,5),并把这个binlog发给主库A。
步骤5,备库B执行“插入c=4”,插入一行(5,4)。binlog传给A,插入(5,5).
binlog_format=row时

因为该格式会记录插入行的所有字段值,所以只会有一行不一致。两边的主备同步的应用线程会报错 duplicate key error 并停止。

可靠性异常切换

假设,主库A和备库B主备延迟30分钟,这时A掉电,HA系统要切换B作为主库。这时必须等到备库B seconds_behind_master=0 之后,才能切换。

26 | 备库为什么会延迟好几个小时?

备库通过sql_thread更新数据,5.6版本之前只支持单线程复制,所以主库并发高、TPS高会出现严重的主备延迟。

上图为改进的多线程复制模型,coordinator为原来的sql_thread,但不再直接更新数据,只负责中转日志和分发事务。worker数量由参数 slave_parallel_workers 决定(32核推荐配置8~16)。
coordinator分发需满足两个基本要求:

  1. 不能造成更新覆盖。这就要求更新同一行的两个事务,必须被分发到同一个 worker 中。
  2. 同一个同一个事务不能被拆开,必须放到同一个worker 中。

并行复制策略

按库分发,hash库名到一个worker 中,MySQL 5.6 版本的并行复制策略。
按表分发,需将相同表hash到一个worker 中。
按行分发,按“库名 + 表名 + 唯一索引 a 的名字 +a 的值”hash到一个worker 中。
MariaDB 利用了redo log 组提交 (group commit)特性,因为能在一组中提交,一定不会修改同一行。

MySQL 5.7 并行复制策略由参数 slave-parallel-type 来控制,配置成DATABASE使用5.6版本的策略,LOGICAL_CLOCK使用MariaDB 的策略,但进行了优化(针对两阶段提交)。

MySQL 5.7.22 新增了一个并行复制策略,基于 WRITESET 的并行复制。

27 | 主库出问题了,从库怎么办?

虚线箭头表示的是主备关系,也就是 A 和 A’互为主备, 从库 B、C、D 指向的是主库 A。
相比于一主一备,一主多从结构在切换完成后,A’会成为新的主库,从库 B、C、D 也要改接到 A’。

基于位点的主备切换

当我们把节点 B 设置成节点 A’的从库的时候,需要执行一条change master 命令:

CHANGE MASTER TO
//主库A’的信息
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

最后两个参数表示要从主库的 master_log_name 文件的 master_log_name 文件的 master_log_pos 这个位置的日志继续同步。而这个位置就是我们所说的同步位点,也就是主库对应的文件名和日志偏移量。
同步位点很难取到精确位置,因为不能丢数据,需要需要找一个“稍微靠前”的位点,然后判断跳过已经执行过的事务。

等待新主库 A’把中转日志(relay log)全部同步完成;
在 A’上执行 show master status 命令,得到当前 A’上最新的 File 和 Position;
取原主库 A 故障的时刻 T;
用 mysqlbinlog 工具解析 A’的 File,得到 T 时刻的位点。

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

主动跳过事务:
set global sql_slave_skip_counter=1;
start slave;
跳过设置 slave_skip_errors 参数,直接设置跳过指定错误。“1032”删除找不到行,“1062”主键冲突。同步完成后,稳定一段时间,去掉该设置。

GTID

MySQL 5.6 版本引入了 GTID。
GTID启动加上参数 gtid_mode=on 和 enforce_gtid_consistency=on。
GTID 的全称是 Global Transaction Identifier,也就是全局事务 ID,是一个事务在提交的时候生成的,是这个事务的唯一标识。它由两部分组成,格式是://server_uuid实例第一次启动时生成,全局唯一 //gno一个整数,初始值是 1,每次提交事务的时候分配给这个事务,并+1 //mysql文档中叫GTID=source_id:transaction_id GTID=server_uuid:gno

如果从库中已经存在了某事务,使用以下方式跳过。前三句执行了一个空事务,并把GTID加到了从库的集合中。

set gtid_next=’aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10′;
begin;
commit;
set gtid_next=automatic;
start slave;

基于 GTID 的主备切换

CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
//使用GTID协议
master_auto_position=1

从库会把自己的GTID集合传给切换的主库,主库会计算差集,然后把不同的同步给从库。

28 | 读写分离有哪些坑?

上节的主从是由客户端直联的。另一种架构proxy。

代理架构,客户端不会感知数据库端的细节,只需对接代理。但加一层代理,链路会变长,而且代理也需要高可用架构。
不管是哪种架构,主备都存在延迟。

强制走主库方案

对于一些需要拿到实时结果的请求,分发到主库上。但对一些都需要实时结果的金融业务,就需要放弃读写分离。

sleep方案

延迟几秒再去读从库,但超过这个时间的同步还是拿不到最新的数据。

判断主备无延迟方案

  1. 判断show slave status 结果里的 seconds_behind_master 参数的值是否等于0,但该值精度为秒。
  2. 对比位点确保主备无延迟,Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同,就表示接收到的日志已经同步完成。
  3. 对比 GTID 集合确保主备无延迟,Retrieved_Gtid_Set、Executed_Gtid_Set是否相同。

配合 semi-sync

要解决这个问题,就要引入半同步复制,也就是semi-sync replication。

事务提交的时候,主库把 binlog 发给从库;
从库收到 binlog 以后,发回给主库一个 ack,表示收收到;
主库收到这个 ack 以后,才能给客户端返回“事务完成”的确认。
但一主多从的情况主库只要收到一个从库返回ack,就会提交事务。所以在查询其他从库时,可能还是会存在主备延迟。
其实,判断同步位点的方案还有另外一个潜在的问题,即:如果在业务更新的高峰期,主库的位点或者 GTID 集合更新很快,那么上面的两个位点等值判断就会一直不成立,很可能出现从库上迟迟无法响应查询请求的情况。

等主库位点方案

实际上并不需要等待主备完全同步,其实从库查询trx1时只需要该事务完成就可以返回:

首先看一条sql : select master_pos_wait(file, pos[, timeout]);

  1. 它是在从库执行的;
  2. 参数 file 和 pos 指的是主库上的文件名和位置;
  3. timeout 可选,设置为正整数 N 表示这个函数最多等待N 秒。

这个会返回一个正整数 M,表示从命令开始执行,到应用完 file 和 pos 表示的 binlog 位置,执行了多少事务。除了正常返回之外,还会返回:

  1. 如果执行期间,备库同步线程发生异常,则返回 NULL;
  2. 如果等待超过 N 秒,就返回 -1;
    1. 如果刚开始执行的时候,就发现已经执行过这个位置了,则返回 0。

所以可以这么判断:

  1. trx1 事务更新完成后,马上执行 show master status 得到当前主库执行到的File 和 Position;
  2. 选定一个从库执行查询语句;
  3. 在从库上执行 select master_pos_wait(File, Position, 1);
  4. 如果返回值是 >=0 的正整数,则在这个从库执行查询语句;
  5. 否则,到主库执行查询语句。

所以可能存在将流量打到主库的情况,所以需要做好主库限流策略。

GTID 方案

select wait_for_executed_gtid_set(gtid_set, 1);

  1. 等待,直到这个库执行的事务中包含传入的 gtid_set,返回 0;
  2. 超时返回 1。

MySQL 5.7.6 版本开始,允许在执行完更新类事务后,把这个事务的 GTID 返回给客户端,这样等 GTID 的方案就可以减少一次查询。

  1. trx1 事务更新完成后,从返回包直接获取这个事务的 GTID,记为 gtid1;
  2. 选定一个从库执行查询语句;
  3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
  4. 如果返回值是 0,则在这个从库执行查询语句;
  5. 否则,到主库执行查询语句。

29 | 如何判断一个数据库是不是出问题了?

select 1 判断
当前并发查询数超过innodb_thread_concurrency时, select 1会返回,但执行查询命令时会等待。
该参数默认值是0,表示不限制并发查询数,建议把 innodb_thread_concurrency 设置为 64~128 之间的值。不是并发连接数。

查表判断

在系统库(mysql 库)里创建一个表,比如命名为 health_check,里面只放一行数据,然后定期执行:mysql> select * from mysql.health_check;
但有其他一个问题,更新事务要写 binlog,binlog 所在磁盘的空间占用率达到 100%,那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是,系统这时候还是可以正常读数据的。

更新判断

常见做法是放一个 timestamp 字段,用来表示最后一次执行检测的时间。但备库不能写同一行,所以需要使用多行,id为server_id。mysql> update mysql.health_check set t_modified=now();

但有可能,机器的I/O已经100%,但刚好健康检查的sql拿到了资源,成功返回了。

内部统计

关于磁盘利用率100%的问题。MySQL 5.6 版本以后提供的 performance_schema 库,就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。

老师比较倾向的方案,是优先考虑 update 系统表,,然后再配合增加检测 performance_schema的信息。

30 | 答疑文章(二):用动态的观点看加锁

先复习一下老师在 21 | 为什么我只改一行的语句,锁这么多? 文章中提到了两个“原则”、两个“优化”和一个“bug”。
该文章基于下面的表结构:

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);

不等号条件里的等值查询
等值查询和“遍历”有什么区别?为什么我们文章的例子里面,where 条件是不等号,这个过程里也有等值查询?

begin;
select * from t where id>9 and id<12 order by id desc for update;
上面的规则可以知道加锁区间(0,5]、(5,10] 和 (10, 15)。

  1. 首先这个查询语句的语义是 order by id desc,要拿到满足条件的所有行,优化器必须先找到“第一个 id<12 的值”。
  2. 这个过程是通过索引树的搜索过程得到的,在引擎内部,其实是要找id=12 的这个值,只是最终没找到,但找到了(10,15) 这个间隙。
  3. 然后向左遍历,在遍历过程中,就不是等值查询了,会扫描到 id=5 这一行,所以会加一个 next-key lock (0,5]。

也就是说,在执行过程中,通过树搜索的方式定位记录的时候,用的是“等值查询”的方法。

等值查询的过程

下面这个语句的加锁范围是什么?begin; select id from t where c in(5,20,10) lock in share mode;

in 语句使用了索引 c 并且 rows=3,说明这三个值都是通过 B+ 树搜索定位的。
在查找 c=5 的时候,先锁住了 (0,5]。但是因为 c 不是唯一索引,为了确认还有没有别的记录 c=5,就要向右遍历,找到 c=10 才确认没有了,这个过程满足优化 2,所以加了间隙锁 (5,10)。
同样的,执行 c=10 这个逻辑的时候,加锁的范围是(5,10] 和 (10,15);执行 c=20 这个逻辑的时候,加锁的范围是 (15,20] 和 (20,25)。
这条语句在索引 c 上加的三个记录锁的顺序是:先加 c=5 的记录锁,再加 c=10 的记录锁,最后加 c=20 的记录锁。

怎么看死锁?

select id from t where c in(5,20,10) order by c desc for update;

当执行上述命令时,加锁顺序和会之前那句相反,会产生死锁。show engine innodb status;

三部分:

TRANSACTION,是第一个事务的信息;
TRANSACTION,是第二个事务的信息;
WE ROLL BACK TRANSACTION (1),最终回滚了第一个事务。

得到的结论:

由于锁是一个个加的,要避免死锁,对同一组资源,要按照尽量相同的顺序访问;
在发生死锁的时刻,for update 这条语句占有的资源更多,回滚成本更大,所以 InnoDB 选择了回滚成本更小的 lockin share mode 语句,来回滚。

怎么看锁等待?

session A 并没有锁住 c=10 这个记录,delete之后不能insert。

由于 delete 操作把 id=10 这一行删掉了,原来的两个间隙 (5,10)、(10,15)变成了一个 (5,15)。

update 的例子

虽然session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25] 和 (25,supremum],但update后加锁范围变成了下图:

注意:根据 c>5 查到的第一个记录是 c=10,因此不会加(0,5] 这个 next-key lock。

之后 session B 的第一个 update 语句,要把c=5 改成 c=1,你可以理解为两步:插入 (c=1, id=5) 这个记录;删除 (c=5, id=5) 这个记录。

按照我们上一节说的,索引 c 上 (5,10) 间隙是由这个间隙右边的记录,也就是 c=10 定义的。所以通过这个操作,session A 的加锁范围变成了: