《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 select “10” > 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 的加锁范围变成了：

Posted in Mysql

Tags:

You can leave a response , or trackback from your own site.