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

June 22nd, 2019 by JasonLe's Tech 1,015 views

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 的加锁范围变成了:

Mysql 关键知识点

April 16th, 2019 by JasonLe's Tech 853 views

transaction-isolation:

推荐配置为READ-COMMITTED。

binlog_format参数

format 定义 优点 缺点
statement 记录的是修改SQL语句 日志文件小,节约IO,提高性能 准确性差,对一些系统函数不能准确复制或不能复制,如now()、uuid()等
row(推荐) 记录的是每行实际数据的变更,记两条,更新前和更新后 准确性强,能准确复制数据的变更 日志文件大,较大的网络IO和磁盘IO
mixed statement和row模式的混合 准确性强,文件大小适中 有可能发生主从不一致问题

sync_binlog参数

0:当事务提交后,Mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
n:在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。

innodb_flush_log_at_trx_commit参数

0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。
1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认。
2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

innodb_lock_wait_timeout

死锁超时时间,默认值50s。缺点:如果设置时间太短但容易把长时间锁等待释放掉。

innodb_deadlock_detect

发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。缺点:假设1000个线程更新同一行,则死锁检测要执行100万次。

innodb_file_per_table

OFF 存在共享表空间里,也就是跟数据字典放在一起;
ON 单独的文件,每个innodb表数据存储在以.ibd为后缀的文件中。

tmp_table_size

内存临时表的大小,默认是 16M。如果内存不够则使用磁盘临时表。

三、开发时需要注意的

1. 什么时候需要RR,一般都为RC

做数据校对,例如判断上个月的余额和当前余额的差额,是否与本月的账单明细一致。希望在校对过程中,即使有用户发生了一笔新的交易,也不影响校对结果。

2. 如何安全地给小表加字段?

查看information_schema 库的 innodb_trx 表中的当前事务,等待事务结束或者 kill 该事务。(另外MariaDB支持DDL NOWAIT/WAIT n 语法避免长时间等待导致业务不可用)

3. 从性能和存储空间方面考量,推荐使用自增主键

自增主键的插入数据模式,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。并且自增主键在非主键索引占用的空间最小。

4. 如何安排联合索引顺序

假设a、b两个字段都需要索引,a字段存储空间比b字段大,则建议建(a,b)和 b 两个索引。
假设有PRIMARY KEY(a,b)和KEY c,则不需要建(c,a)索引,可以建(c,b)索引。

5. 避免长事务

6. 如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

7. 怎么解决由热点行更新导致的性能问题

  1. 如果能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉
  2. 控制并发度,降低死锁检测
  3. 将一行改为多行,比如把余额分成10个子余额,但这样需要考虑扣钱问题

8. 怎么删除表的前 10000 行

在一个连接中循环执行 20 次 delete from T limit 500,避免长事务(delete from T limit 10000),避免多线程(20 个连接中同时执行 delete from T limit 500)

9. 从性能的角度考虑,选择唯一索引还是普通索引呢

尽量选择普通索引,因为当更新记录的目标页不在内存中时,唯一索引需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;而普通索引来说,则是将更新记录在change buffer,语句执行就结束了。但是如果业务不能保证重复,就需要唯一索引保证。

10. MySQL有时候会选错索引

平常不断地删除历史数据和新增数据的场景,mysql有可能会选错索引。sql太慢就用explain看看,有可能就是索引选错了。

11. 怎么给字符串字段加索引

直接使用字符串建索引有时候可能效率较低,存储空间较大

  1. 使用前缀索引
  2. 例如身份证等前面相似度较大的字符串,可以采用倒序存储
  3. 建另外的字段(如hash字段)建索引

12. MySQL偶尔执行很慢

偶尔慢一下的那个瞬间,可能在刷脏页(flush)。innodb的redo log写满、buffer pool内存不足等情况。
合理地设置 innodb_io_capacity 的值,平时要多关注脏页比例,不要让它经常接近 75%。

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

 

13. 为什么表数据删掉一半,表文件大小不变

删除某行,innodb只会标记删除。如果之后在该行范围内插入新数据就会复用。假如表本身没有多少空洞,重建索引可能会使表文件变大。
重建主键索引 alter table T engine=InnoDB;

不推荐drop,再add。并且不论是删除主键还是创建主键,都会将整个表重建。

14. count(*)慢怎么办

Innodb需要一行一行读出来累积计数,MyISAM 引擎保存总行数,所以count很快。

  1. 用缓存系统保存计数
  2. 在数据库保存计数

不同的 count 用法效率
count(字段)<count(主键 id)<count(1)≈count(*),所以建议尽量使用 count(*)

15. order by

Extra中”Using filesort”表示排序,mysql会给每个线程分配一个块内存(sort_buffer)用来排序。
假设从某个索引上取出来的行天然按照递增排序,就不需要再进行排序了。但维护索引是有代价的,所以需要权衡。

16. 对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

17. 为什么只查一行的SQL也执行这么慢

  1. 查询长时间不返回:等MDL锁;等flush;等行锁
  2. 查询慢:扫描行数多;其他长事务导致undo log快照过多

18. 覆盖索引不给主键索引加锁,所以更新主键索引(没有建索引的列)不更新覆盖索引的情况不会等待。也就是只锁被访问到的对象

19. 在删除数据时,尽量加limit

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

20. 避免字段函数操作、避免隐式转换、隐式字符编码转换

23. 读写分离

  • 实时请求强制走主库方案
  • sleep几秒(不推荐)
  • 判断主备无延迟方案,对于一些从库还没有收到的请求还是会有延迟
  • 配合 semi-sync,半同步只要一个从库返回ack就返回给客户端成功,但不能确保所有从库都同步完成
  • 等主库位点方案,主库更新完后执行show master status 得到当前主库执行到的File 和 Position,拿到信息后查询前去从库select master_pos_wait(File, Position, 1);判断是否同步完成
    GTID 方案,事务完成直接返回事务的 GTID,根据这个id去从库查询select wait_for_executed_gtid_set(gtid1, 1);判断是否同步完成

24. Join

让小表做驱动表、被驱动表有索引。
如果被驱动表没有索引会走BNL算法,将驱动表加载到 join_buffer 中,将被驱动表中的数据一行行读出来与内存中的驱动表数据对比。
如果被驱动表是个大表,会把冷数据的page加入到buffer pool(join_buffer 用了其中的内存),并且BNL要扫描多次,两次扫描的时间可能会超过1秒,使上节提到的分代LRU优化失效,把热点数据从buffer pool中淘汰掉,影响正常业务的查询效率。

Join优化

Multi-Range Read 优化
Batched Key Access:缓存读取多行传给被驱动表

BNL 算法的性能

除了给被驱动表加索引之外,还可以使用临时表,创建临时表然后加索引

25. 临时表的应用

  • 临时表只能被创建它的 session 访问,对其他线程不可见。所以在这个 session 结束的时候,会自动删除临时表。
  • 临时表可以与普通表同名(还是不要这么做)。
  • session A 内有同名的临时表和普通表的时候,show create 语句,以及增删改查语句访问的是临时表。
  • show tables 命令不显示临时表。

分表分库跨库查询

分库分表系统都有一个中间层 proxy,如果 sql 能够直接确定某个分表,这种情况是最理想的。
但如果涉及到跨库,一般有两种方式:

  1. 在 proxy 层的进程代码中实现排序,但对 proxy 的功能和性能要求较高。
  2. 把各个分库拿到的数据,汇总到一个 MySQL 实例的一个表中,然后在这个汇总实例上做逻辑操作。如果每个分库的计算量都不饱和,那么直接可以在把临时表放到某个分库上。

26. MySQL 什么时候会使用内部临时表?

  1. 如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果;
  2. join_buffer 是无序数组,sort_buffer 是有序数组,临时表是二维表结构;
  3. 如果执行逻辑需要用到二维表特性,就会优先考虑使用临时表。比如,union 需要用到唯一索引约束, group by 还需要用到另外一个字段来存累积计数。

27. group by使用的指导原则:

  1. 如果对 group by 语句的结果没有排序要求,要在语句后面加 order by null;
  2. 尽量让 group by 过程用上表的索引,确认方法是 explain 结果里没有 Using temporary 和 Using filesort;
  3. 如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表;
  4. 如果数据量实在太大,使用 SQL_BIG_RESULT 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果。

29. 怎么最快地复制一张表

  1. mysqldump 方法
  2. 导出 CSV 文件
  3. mysql5.6 物理拷贝

假设我们现在的目标是在 db1 库下,复制一个跟表 t 相同的表 r:

  • 执行 create table r like t,创建一个相同表结构的空表;
  • 执行 alter table r discard tablespace,这时候 r.ibd 文件会被删除;
  • 执行 flush table t for export,这时候 db1 目录下会生成一个 t.cfg 文件;
  • 在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd;这两个命令;
  • 执行 unlock tables,这时候 t.cfg 文件会被删除;
  • 执行 alter table r import tablespace,将这个 r.ibd 文件作为表 r 的新的表空间,由于这个文件的数据内容和 t.ibd 是相同的,所以表 r 中就有了和表 t 相同的数据。

30. 分区表

innodb 只会锁一个分区,而 MyISAM 会锁所有的。

应用

分区表的一个显而易见的优势是对业务透明,相对于用户分表来说,使用分区表的业务代码更简洁。还有,分区表可以很方便的清理历史数据。
按照时间分区的分区表,就可以直接通过 alter tablet drop partition …这个语法删掉分区,从而删掉过期的历史数据。

31. explain

SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好。

type字段

  1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
  2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
  3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
  4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
  5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
  6. index_merge:该联接类型表示使用了索引合并优化方法。
  7. unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
  8. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
  9. range:只检索给定范围的行,使用一个索引来选择行。
  10. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
  11. ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

Extra

  1. Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
  2. Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
  3. range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
  4. Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
  5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
  6. Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
  7. Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
  8. Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
  9. Using index for group-by:类似于访问表的Using index方式,
  10. Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

基于Redis的分布式锁的思考

March 31st, 2019 by JasonLe's Tech 889 views

最近在实现一个分布式锁,网上有很多种版本,有的是使用zk实现,有的是使用db,我这里主要使用的是redis。

网上对于基于redis的分布式锁有很多版本,有的实现是错误的,需要进行辨别,我这里主要依赖的是redis的特性,setnx在原本实现里,是没有过期时间的,因此如果遇到系统没有解锁,就会导致死锁,因此网上针对这种问题衍生了很多版本,比如在value里面塞入expireTime来保证是否过期。

但是这种实现方式导致代码很冗余,读起来很晦涩,因此在redis在2.6.12后,set支持setnx的操作,并且支持过期时间,另外该原语是原子的,因此实现分布式锁就两行代码,比网上的实现简化了很多。

Set key to hold the string value. If key already holds a value, it is overwritten, regardless of its type. Any previous time to live associated with the key is discarded on successful SET operation.

Options
Starting with Redis 2.6.12 SET supports a set of options that modify its behavior:

EX seconds — Set the specified expire time, in seconds.
PX milliseconds — Set the specified expire time, in milliseconds.
NX — Only set the key if it does not already exist.
XX — Only set the key if it already exist.
Note: Since the SET command options can replace SETNX, SETEX, PSETEX, it is possible that in future versions of Redis these three commands will be deprecated and finally removed.

Return value
Simple string reply: OK if SET was executed correctly. Null reply: a Null Bulk Reply is returned if the SET operation was not performed because the user specified the NX or XX option but the condition was not met.

在很多不是太注重高并发的场景下还是可以当用的,但使用这种分布式锁还是有些问题的,下面这种场景就是一种情况,

假设锁服务本身是没有问题的,它总是能保证任一时刻最多只有一个客户端获得锁。上图中出现的lease这个词可以暂且认为就等同于一个带有自动过期功能的锁。客户端1在获得锁之后发生了很长时间的GC pause,在此期间,它获得的锁过期了,而客户端2获得了锁。当客户端1从GC pause中恢复过来的时候,它不知道自己持有的锁已经过期了,它依然向共享资源(上图中是一个存储服务)发起了写数据请求,而这时锁实际上被客户端2持有,因此两个客户端的写请求就有可能冲突(锁的互斥作用失效了)。

因此一种方法,称为fencing token。fencing token是一个单调递增的数字,当客户端成功获取锁的时候它随同锁一起返回给客户端。而客户端访问共享资源的时候带着这个fencing token,这样提供共享资源的服务就能根据它进行检查,拒绝掉延迟到来的访问请求(避免了冲突)

 

虽然解决了该问题,但是本质上是因为Redlock的安全性(safety property)对系统的时钟有比较强的依赖,一旦系统的时钟变得不准确,算法的安全性也就保证不了了。Martin在这里其实是要指出分布式算法研究中的一些基础性问题,或者说一些常识问题,即好的分布式算法应该基于异步模型(asynchronous model),算法的安全性不应该依赖于任何记时假设(timing assumption)。

  • 如果是为了效率(efficiency)而使用分布式锁,允许锁的偶尔失效,那么使用单Redis节点的锁方案就足够了,简单而且效率高。Redlock则是个过重的实现(heavyweight)。
  • 如果是为了正确性(correctness)在很严肃的场合使用分布式锁,那么不要使用Redlock。它不是建立在异步模型上的一个足够强的算法,它对于系统模型的假设中包含很多危险的成分(对于timing)。而且,它没有一个机制能够提供fencing token。那应该使用什么技术呢?Martin认为,应该考虑类似Zookeeper的方案,或者支持事务的数据库。

参考

https://blog.csdn.net/jek123456/article/details/72954106

https://redis.io/commands/set

Mysql 业务设计题

March 16th, 2019 by JasonLe's Tech 831 views

问题描述:

业务上有这样的需求,A、B 两个用户,如果互相关注,则成为好友。设计上是有两张表,一个是 like 表,一个是 friend 表,like 表有 user_id、liker_id 两个字段,我设置为复合唯一索引即uk_user_id_liker_id。语句执行逻辑是这样的:

以 A 关注 B 为例:
第一步,先查询对方有没有关注自己(B 有没有关注 A)select * from like where user_id = B and liker_id = A;
如果有,则成为好友  insert into friend;
没有,则只是单向关注关系 insert into like;

但是如果 A、B 同时关注对方,会出现不会成为好友的情况。因为上面第 1 步,双方都没关注对方。**第 1 步即使使用了排他锁也不行,因为记录不存在,行锁无法生效。**请问这种情况,在 MySQL 锁层面有没有办法处理?
表结构:

CREATE TABLE `like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `liker_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;

CREATE TABLE `friend` (
  id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_1_id` int(11) NOT NULL,
  `firned_2_id` int(11) NOT NULL,
  UNIQUE KEY `uk_friend` (`friend_1_id`,`firned_2_id`)
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

由于一开始A和B之间没有关注关系,所以两个事务select都为空。因此分别插入一个单向关注关系,这结果对业务来说就是bug了。
另外一个方法,来解决这个问题。

给“like”表增加一个字段,比如叫作 relation_ship,并设为整型,取值 1、2、3。

  • 值是 1 的时候,表示 user_id 关注 liker_id;
  • 值是 2 的时候,表示 liker_id 关注 user_id;
  • 值是 3 的时候,表示互相关注。
begin; /* 启动事务 */
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;
/* 代码中判断返回的 relation_ship,
如果是 1,事务结束,执行 commit
如果是 3,则执行下面这两个语句:
*/
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;

如果A>B,则执行下面的逻辑:

mysql&gt; begin; /* 启动事务 */
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;
/* 代码中判断返回的 relation_ship,
  如果是 2,事务结束,执行 commit
  如果是 3,则执行下面这两个语句:
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;

这个设计,让”like”表里的数据保证user_id < liker_id,这样无论A关注B,B关注A,在操作“like”表时,如果反向关系已存在,就会操作同一行出现行锁冲突。
然后,insert … on duplicate 语句,确保事务强行占住行锁,之后select 判断 relation_ship 这个逻辑时就确保了是在行锁保护下的读操作。
操作符 “|” 按位或,和最后一句 insert 语句里的 ignore,保证重复调用时的幂等性。

《MySQL实战45讲》学习笔记 1~15讲

March 16th, 2019 by JasonLe's Tech 898 views

01 | 基础架构:一条SQL查询语句是如何执行的?

MySQL分为Server层存储引擎层两部分。

连接器:负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存:查询请求先访问缓存(key 是查询的语句,value 是查询的结果)。命中直接返回。不推荐使用缓存,更新会把缓存清除(关闭缓存:参数 query_cache_type 设置成 DEMAND)。
分析器:对 SQL 语句做解析,判断sql是否正确。
优化器:决定使用哪个索引,多表关联(join)的时候,决定各个表的连接顺序。
执行器:执行语句,先判断用户有无查询权限,使用表定义的存储引擎。

MySQL 8.0 版本直接将查询缓存的整块功能删掉了,也就是说8.0之后没有查询缓存这一步了。

02 | 日志系统:一条SQL更新语句是如何执行的?

redo log
MySQL WAL 技术,先写日志,再写磁盘。保证掉电重启,数据不丢失(crash-safe)。redo log 是 InnoDB 引擎特有的日志。当记录更新时,Innodb 先记录 redo log 再更新内存,这时更新就算完成。引擎往往会在系统空闲时刷盘。

redo log 是实现了类似环形缓冲区,一个指针 write pos 是当前记录的位置,另一个指针 checkpoint 是当前要擦除的位置,write pos 和checkpoint 之间是空闲部分。如果 write pos 快追上 checkpoint 时,代表缓冲区快满了,需要暂停刷盘。

innodb_flush_log_at_trx_commit参数:
0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。
1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认。
2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

binlog(归档日志)

Server层日志。binlog 日志只能用于归档,没有crash-safe能力。
三个用途:

  1. 恢复:利用binlog日志恢复数据库数据
  2. 复制:主从同步
  3. 审计:通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入攻击
format  定义  优点  缺点
statement 记录的是修改SQL语句 日志文件小,节约IO,提高性能 准确性差,对一些系统函数不能准确复制或不能复制,如now()、uuid()等
row(推荐) 记录的是每行实际数据的变更,记两条,更新前和更新后 准确性强,能准确复制数据的变更 日志文件大,较大的网络IO和磁盘IO
mixed statement和row模式的混合 准确性强,文件大小适中 有可能发生主从不一致问题

sync_binlog参数:
0:当事务提交后,Mysql仅仅是将binlog_cache中的数据写入Binlog文件,但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
n:在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。

不同点:

redo log 是物理日志,记录的是“在某个数据页上做了什么修改”。binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。binlog 文件到一定大小,会切换到下一个文件。

update执行过程:

mysql> update T set c=c+1 where ID=2;

两阶段提交
1 prepare阶段 2 写binlog 3 commit
当在2之前崩溃时
重启恢复:后发现没有commit,回滚。备份恢复:没有binlog 。
当在3之前崩溃
重启恢复:虽没有commit,但满足prepare和binlog完整,所以重启后会自动commit。备份:有binlog 。

03 | 事务隔离:为什么你改了我还看不见?

事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)

总结:
RR下,事务在第一个Read操作时,会建立read-view
RC下,事务在每次Read操作时,都会建立read-view
不同业务选择不同的隔离级别。

回滚段

rollback segment称为回滚段,每个回滚段中有1024个undo log segment。每个undo操作在记录的时候占用一个undo log segment。
undo log有两个作用:提供回滚和多个行版本控制(MVCC)。
在数据修改的时候,不仅记录了redo,还记录了相对应的undo,如果因为某些原因导致事务失败或回滚了,可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。

04 | 深入浅出索引(上)

索引的常见模型

哈希表,不适合做区间搜索。有序数组,只适合静态数据,插入麻烦。二叉搜索树,N叉树。

InnoDB 的索引模型

在 MySQL 中,索引是在存储引擎层实现的。以主键顺序存在B+树中。

主键索引(聚簇索引) 的叶子节点存的是整行数据。主键查询主需要扫描主键索引。
非主键索引(二级索引)的叶子节点内容是主键的值。通过二级索引需要扫描二级索引树,找到主键后再扫描主键索引。该过程称为回表。

索引维护

当插入到索引树最后,只需直接插入。但当插入到索引树中间,需要逻辑上挪动后面的数据,空出位置,并且当数据页满时,需要申请一个新的数据页,然后挪动部分数据过去(页分裂)。
当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。自增索引(追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂)业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。二级索引的叶子节点为主键,业务字段做主键时会占大量存储空间。什么时候可以使用业务字段做主键? 只有一个索引;该索引必须是唯一索引。

索引重建

alter table T engine=InnoDB

不推荐drop,再add。并且不论是删除主键还是创建主键,都会将整个表重建。

05 | 深入浅出索引(下)

覆盖索引

当查询值已经在二级索引上时,不需要回表。

最左前缀原则

联合索引合理安排顺序,可以少维护索引,或者减少存储空间。

CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

索引ca可以去掉,因为c和主键ab,和ca和主键ab相同。

索引下推

MySQL 5.6 引入的索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?

mysql锁大致可以分成全局锁、表级锁和行锁三类

全局锁

全局锁的典型使用场景是,做全库逻辑备份。tables with read lock;

官方自带的逻辑备份工具是 mysqldump,当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。
但当引擎不支持事务时,只能使用FTWRL 命令了。不推荐不使用 set global readonly=true,readonly会被其他逻辑使用(比如判断主从),readonly发生异常会保持该状态。

表级锁

MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。表锁的语法是 lock tables … read/write。MDL不需要显式使用,在访问一个表的时候会被自动加上。
当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

当一个长事务还没提交,进行表结构变更操作,会导致后面的事务block。当客户端有重试机制时,新起session请求,会导致库的线程很快就会爆满。

如何安全地给小表加字段?

避免长事务。
在 alter table 语句里面设定等待时间。
MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...

ALTER TABLE tbl_name WAIT N add column ...

07 | 行锁功过:怎么减少行锁对性能的影响?

行锁

Mysql行锁由引擎层实现

两阶段锁

行锁需要事务结束时才释放,这就是两阶段锁。
所以需要合理安排事务中sql执行顺序,尽量把容易冲突的更新语句放在后面。

死锁和死锁检测

  1. 设置超时时间,innodb_lock_wait_timeout。
  2. 死锁检测,发现死锁主动回滚某个事务,innodb_deadlock_detect 默认on。
    假设1000个同时更新一行,则死锁检测操作就是 100 万这个量级的。即使没有死锁,检测也会消耗大量的 CPU 资源。

解决方案:

  1. 业务不会出现死锁,可以临时关闭。
  2. 在客户端控制并发。
  3. 修改MySQL 源码,并发进入引擎之前排队。
  4. 将一行数据改为多行,如将一个余额账户分为多个,但在数据减少操作时需考虑小于0的情况。

08 | 事务到底是隔离的还是不隔离的?

快照”在 MVCC 里是怎么工作的?

InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向InnoDB 的事务系统申请的,是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id赋值给这个数据版本的事务 ID,记为 row trx_id。也就是说,数据表中的一行记录,其实可能有多个版本 (row),每个版本有自己的 row trx_id。

上图中的三个虚线箭头就是undo log。
某个事务建立快照,只需根据transaction id。只认事务启动时小于数据版本的数据,除自己更新的数据。

快照实现

InnoDB在每个事务启动瞬间,构造了数组保存了当前启动但未提交的事务ID。
数组ID最小值为低水位,当前系统最大事务ID+1为高水位。
数组和高水位,组成了当前事务的一致性事务(read-view)。

黄色部分需分为以下两种情况,因为有可能大于低水位的某个事务已经提交:

若 row trx_id 在数组中,表示这个版本是由还没提交的事务生成的,不可见;
若 row trx_id 不在数组中,表示这个版本是已经提交了的事务生成的,可见。
select read-view创建在03 | 事务隔离中提过了,就不写了。

更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read)。
如果当前的记录的行锁被其他事务占用的话,就需要进入锁等待。

不同隔离级别:

对于可重复读,查询只承认在事务启动前就已经提交完成的数据
对于读提交,查询只承认在语句启动前就已经提交完成的数据
而当前读,总是读取已经提交完成的最新版本。

09 | 普通索引和唯一索引,应该怎么选择?

查询过程

操作成本相差无几。

更新过程

change buffer概念
change buffer是持久化数据,在内存中有拷贝,也会写到磁盘上。
当更新数据页时,如数据页在内存中直接更新。如果不在,在不影响数据一致性的前提下,innodb会将更新操作先缓存到change buffer中,当下次查询该数据页时,执行change buffer中与该页相关的操作。该操作称为merge,除了该情况,系统后台线程也会定期merge,数据库正常关闭也会merge。
change buffer可以减少读磁盘,而且数据读入内存会占用buffer pool。

什么条件下可以使用 change buffer 呢?
对于唯一索引,更新操作都需要判断操作是否违反唯一约束,所以需要将数据都读入到内存,所以会直接更新内存。
所以只有普通索引会使用change buffer。
change buffer使用buffer pool里的内存,参数innodb_change_buffer_max_size设置为50时,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

当更新记录的目标页不在内存中时,InnoDB 的处理流程如下:

对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
对于普通索引来说,则是将更新记录在change buffer,语句执行就结束了。
所以这种情况,唯一索引会导致磁盘大量随机IO的访问(机械硬盘瓶颈)。
但这种情况不是绝对的,写多读少的场景change buffer记录的变更多,收益越大。常见业务模型账单类、日志类的系统。对于写完马上读取的情况,会立即触发merge,反而增加了维护change buffer的成本。
所以尽量选择普通索引。

change buffer 和 redo log

假设当前 k 索引树的状态,查找到位置后,k1 所在的数据页在内存(InnoDB buffer pool) 中,k2 所在的数据页不在内存中。下图所示是带 change buffer 的更新状态图。

操作顺序:

  1. Page 1 在内存中,直接更新内存
  2. Page 2 没有在内存中,就在内存的change buffer 区域,记录下“我要往 Page 2 插入一行”这个信息
  3. 将上述两个动作记入 redo log 中(图中 3 和 4)

图中的两个虚线箭头,是后台操作,不影响更新的响应时间。
执行查询操作:select * from t where k in (k1, k2);

假设内存中的数据都还在,此时的这两个读操作就与系统表空间(ibdata1)和 redo log(ib_log_fileX)无关。

读 Page 1 的时候,直接从内存返回。不需要等内存中的数据更新后返回。
要读 Page 2 的时候,需要把 Page 2 从磁盘读入内存中,然后应用 change buffer 里面的操作日志(可能有多个),依次merge一个正确的版本。然后写redo log,redo log中包含数据变更和change buffer 变更。此时内存中数据页为脏页,刷脏是后台线程的流程。如果某个数据页刷脏完成,当redo log中对应的该条刷盘时会识别出来并且跳过。

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

最后到底怎么选索引:

  1. 业务正确性优先,业务可以保证不重复,普通索引提升效率。业务不能保证重复,就需要唯一索引保证。
  2. 历史数据归档库没有唯一索引冲突,可以选择普通索引。

10 | MySQL为什么有时候会选错索引?

平常不断地删除历史数据和新增数据的场景,mysql有可能会选错索引。

优化器的逻辑

优化器选择索引的目的就是选择一个扫描行数最少的方案。行数越少,磁盘读取越少。
扫描行数不是唯一标准,优化器还会结合是否使用临时表,是否排序等因素。

扫描行数怎么判断?
真正执行语句之前,mysql不知道具体有多少条,只能根据统计信息估算。
这个统计信息就是索引的“区分度”。索引上不同值越多,区分度越好。而一个索引上不同值的个数称为“基数”。
使用show index可以查看。下图中,每行三个字段值都是一样的,但在统计信息中,基数值都不准确。

mysql怎么得到索引的基数?

mysql采用采样统计,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
参数 innodb_stats_persistent有两种不同的模式

  • 设置为 on 的时候,表示统计信息会持久化存储。默认 N 是 20,M 是 10。
  • 设置为 off 的时候,表示统计信息只存储在内存中。默认 N 是 8,M 是 16。

如果统计信息不对,可以使用analyze table t 命令重新统计。

索引选择异常和处理

  1. force index 强行选择一个索引
  2. 修改语句,引导 MySQL 使用我们期望的索引
  3. 新建索引,或者删除误用的索引

11 | 怎么给字符串字段加索引?

mysql支持前缀索引,可以以字符串一部分作为索引。默认包含整个字符串。alter table t index idx(a(6));

使用前缀索引虽然可以减少存储空间,但有可能会增加回表次数。
建前缀索引前可以使用下面的sql统计一下重复数:select count(distinct left(a,字符长度));

并且前缀索引会影响覆盖索引。其他方式

  1. 倒序存储
    由于身份证前面的地区码都是相同的,所以存储身份证时,可以将它倒过来存。身份证后6位作为前缀索引有一定的区分度。select field_list from t where id_card = reverse(‘input_id_card_string’);
  2. 使用hash字段
    可以在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引。插入新数据,使用crc32()得到该字段填入。查询语句如下:select field_list from t where id_card_crc=crc32(‘input_id_card_string’) and id_card=’input_id_card_string’;

另外,如果前缀后缀都重复,可以考虑去掉前缀后缀,只存中间一部分数据。

12 | 为什么我的MySQL会“抖”一下?

当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。

MySQL 偶尔慢一下的那个瞬间,可能在刷脏页(flush)。
什么时候会触发刷脏?

  • innodb的redo log写满了,这时候系统会停止所有更新。把checkpoint 往前推进。
  • buffer pool内存不足,此时需要淘汰一些数据页,有可能会淘汰脏页,就要先把脏页刷到磁盘。
  • 刷脏页一定会写盘,就保证了每个数据页有两种状态:
    a. 内存里的一定是正确数据。
    b. 内存里没有,磁盘上的一定是正确数据。
  • mysql认为系统空闲时,会刷盘。当然系统繁忙时,也会见缝插针刷盘。
  • mysql正常关闭。

InnoDB 刷脏页的控制策略

告诉 InnoDB 所在主机的 IO 能力,正确地设置innodb_io_capacity 参数,使用fio工具统计:fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=1

innodb_max_dirty_pages_pct是脏页比例上限,默认值是 75%。
平时要多关注脏页比例,不要让它经常接近 75%。
脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到:

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_dirty’;
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’;
select @a/@b;

另外还有一个策略,当刷脏页时,该页边上也是脏页,也会把边上的脏页一起刷掉。而且该逻辑会一直蔓延。innodb_flush_neighbors 参数就是来控制该行为的,值为1会有上述机制,0则不会。
机械硬盘可能会有不错的效果,但ssd建议设置为0。并且mysql 8.0 innodb_flush_neighbors 默认为0。

13 | 为什么表数据删掉一半,表文件大小不变?

mysql8.0 之前,表结构以.frm为后缀的文件里。而8.0版本允许表结构定义放在系统数据表中,因为该部分占用空间很小。参数 innodb_file_per_table表数据既可以存在共享表空间里,也可以是单独的文件。

  • OFF,表示表的数据放在系统共享表空间,也就是跟数据字典放在一起。drop table及时表删掉了,空间也不会回收。
  • ON(5.6.6版本后默认值),表示每个innodb表数据存储在以.ibd为后缀的文件中。drop table系统会直接删除这个文件。

以下内容基于innodb_file_per_table on展开。

假设要删除R4,innodb只会标记R4删除。如果之后插入一个ID在300和600之间的记录时,可能会复用该位置。如果删掉整页,整个数据页可以被复用。所以磁盘文件大小不会缩小。
但记录复用,只能插入符合范围的数据。不能插入300~600范围外的数据。
页的复用,可以插入任何新数据。如pageA数据删除后,可以插入ID=50的数据。
如果相邻数据页利用率都很小,系统会把两个页的数据合到其中一个页上,另一个标记为可复用。
如果使用delete命令,那么所有数据页标记为可复用。

插入数据也会产生空洞,如果按索引递增插入,那么索引是紧凑的。如果数据插入随机,可能造成索引数据页分裂。
当某页满时,再插入数据,就会申请一个新页,将旧页的部分数据保存到新页中。所以旧页中可能有空洞。
更新索引,可能理解为删除旧值,插入新值。也会造成空洞。

重建表

重建表,可以新建一个表,将旧表中的数据一行一行读出来插入到新表中。然后以新表替换旧表。
可以使用 alter table A engine=InnoDB 命令来重建表。在mysql 5.5版本前,该命令流程与上述流程类似。
在此过程中,不能更新旧表数据。

MySQL 5.6 版本开始引入的 Online DDL,对该操作流程做了优化。

  1. 建立一个临时文件,扫描表 A 主键的所有数据页;
  2. 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
  3. 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中,对应的是图中 state2 的状态;
  4. 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件,对应的就是图中state3 的状态;
  5. 用临时文件替换表 A 的数据文件。

重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗 IO 和 CPU 资源的。
如果是线上服务,要控制操作时间。如果想要比较安全的操作,推荐使用github开源的gh-ost。

optimize table、analyze table和 alter table 这三种方式重建表的区别。

  • 从 MySQL 5.6 版本开始,alter table t engine = InnoDB(也就是 recreate)默认是上图的流程;
  • analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
  • optimize table t 等于 recreate+analyze。

14 | count(*)这么慢,我该怎么办?

count(*) 的实现方式

  • MyISAM 引擎保存总行数,所以count很快。但如果加了where不能很快返回。
  • Innodb需要一行一行读出来累积计数。

innodb由于多版本并发控制(MVCC)的原因,多个事务count的行数不同,所以不能保存总行数。
但count(*)做了优化,引擎会选择最小的普通索引树,来计数。而不是直接统计聚集索引树。

show table status 命令输出TABLE_ROWS 显示这个表当前有多少行,但它也是采样估算来的。官方文档说误差可能达到 40% 到 50%。

用缓存系统保存计数

两个问题:

  1. 缓存会丢失
  2. 缓存不准确,因为缓存计数和插入数据不是原子操作,有可能在中间过程,其他事务读取了数据。

在数据库保存计数

使用一张表保存计数,由于事务可以解决使用缓存问题。

不同的 count 用法

下面的讨论还是基于 InnoDB 引擎的

  1. count(主键 id) ,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。
  2. count(1),InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。
  3. count(字段)
    a. 如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;
    b. 如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。
  4. count(*),并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定不是 null,按行累加。

按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以建议尽量使用 count(*)。

15 | 答疑文章(一):日志和索引相关问题

如果redo处理perpare阶段,写binlog之前崩溃(crash),恢复时事务回滚。
如果binlog写完了,redo未commit前崩溃(crash):

  1. 如果redo log事务完整,有了commit标识,直接提交;
  2. 如果redo log里事务只有完整的perpare,则判断对应事务binlog是否完整:
    a. 如果是,则提交事务;
    b. 否则回滚。

追问 1:MySQL 怎么知道 binlog 是完整的?

回答:一个事务的binlog是有完整格式的:

  • statement 格式的 binlog,最后会有 COMMIT;
  • row 格式的 binlog,最后会有一个 XID event。

mysql 5.6.2版本以后,引入binlog-checksum验证binlog内容是否正确。

追问 2:redo log 和 binlog 是怎么关联起来的?

回答:它们有个共同的数据字段:XID。

追问 3:处于 prepare 阶段的 redo log 加上完整 binlog,重启就能恢复,MySQL 为什么要这么设计?

回答:因为写入binlog后,会被从库使用,为了保证主备一致性。

追问 4:如果这样的话,为什么还要两阶段提交呢?干脆先 redo log 写完,再写 binlog。崩溃恢复的时候,必须得两个日志都完整才可以。是不是一样的逻辑?

回答:两阶段提交是经典分布式系统问题,并不是mysql独有的。
innodb,如果redo log提交完成,事务就不能回滚(如果还允许回滚,可能覆盖掉别的事务的更新)。但如果redo log直接提交,binlog写失败时,innodb回滚不了 ,数据和binlog日志会不一致。两阶段提交就是为了每个“人”都ok,在一起提交。
追问 5:不引入两个日志,也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复,又能支持归档,不就可以了?

回答:不可以,历史原因,innodb不是mysql原生引擎,binlog不支持崩溃恢复,所以innodb实现了redo log。

追问 6:那能不能反过来,只用 redo log,不要 binlog

回答:如果从崩溃恢复角度来讲是可以的。但redo log是循环写,历史日志没法保留,而binlog有归档功能。binlog还有可以实现复制主从同步。

追问 7:redo log 一般设置多大?

回答:redo log太小会导致很快写满,然后就会强行刷redo log。如果几个TB硬盘,直接将redo log设置为4个文件,每个文件1G。

追问 8:正常运行中的实例,数据写入后的最终落盘,是从 redo log 更新过来的还是从 buffer pool 更新过来的呢?

redo log没有记录数据页完整数据,所以它没有能力自己去更新磁盘数据页。

  • 如果再次运行的实例,数据页被修改,跟磁盘数据页不一致,称为脏页。最终数据落盘,就是把内存中的数据页写盘。这过程和redo log毫无关系。
  • 在崩溃恢复场景,Innodb如果判断一个数据页可能在崩溃恢复时丢失更新,就会将它读到内存,然后让redo log更新内存内容。更新完成内存也变成脏页,就回到第一种情况。

回答:在一个事务的更新过程中,日志是要写多次的。比如下面这个事务:

begin;
insert into t1 …
insert into t2 …
commit;

这个事务往两个表中插记录过程中,生成的日志都要先保存起来,但不能在未commit的时候写到redo log里。
所以redo log buffer就是一块内存,用来先存redo日志。也就是说,在执行第一个 insert 的时候,数据的内存被修改了,redo log buffer 也写入了日志。
但是,真正写redo log文件(文件名是ib_logfile+数字),是在执行commit时做的。单独执行一个更新语句,innodb会自己启动一个事务,过程和上述内容一致。