MySQL45讲(第16-21讲)

目录

“orderby”是怎么工作的

全字段排序

rowid排序

全字段排序 VS rowid排序

如何正确地显示随机消息

内存临时表

磁盘临时表

随机排序方法

对索引字段做函数操作,破坏索引有效性,放弃走树搜索功能,但不代表不遍历该索引

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

案例二:隐式类型转换

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

查一行的语句,也执行这么慢

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

等MDL锁

等flush

等行锁

第二类:查询慢

幻读是什么,幻读有什么问题

幻读是什么?

幻读有什么问题?

如何解决幻读?

next-key lock(行锁+间隙锁)加锁规则 5.x系列<=5.7.24,8.0系列 <=8.0.13

案例一:等值查询间隙锁

案例二:非唯一索引等值锁

案例三:主键索引范围锁

案例四:非唯一索引范围锁

案例五:唯一索引范围锁bug

案例六:非唯一索引上存在"等值"的例子

案例七:limit 语句加锁

案例八:一个死锁的例子


“orderby”是怎么工作的

假设这个表的部分定义是这样的:

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  ;

这个语句看上去逻辑很清晰,但是你了解它的执行流程吗?今天,我就和你聊聊这个语句是怎么执行的,以及有什么参数会影响执行的行为。

全字段排序

前面我们介绍过索引,所以你现在就很清楚了,为避免全表扫描,我们需要在city字段加上索引。

在city字段上创建索引之后,我们用explain命令来看看这个语句的执行情况。

Extra这个字段中的“Using filesort”表示的就是需要排序,MySQL会给每个线程分配一块内存用于排序,称为sort_buffer。

从图中可以看到,满足city='杭州’条件的行,是从ID_X到ID_(X+N)的这些记录。

通常情况下,这个语句执行流程如下所示 :

  1. 初始化sort_buffer,确定放入name、city、age这三个字段;

  2. 从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X;

  3. 到主键id索引取出整行,取name、city、age三个字段的值,存入sort_buffer中;

  4. 从索引city取下一个记录的主键id;

  5. 重复步骤3、4直到city的值不满足查询条件为止,对应的主键id也就是图中的ID_Y;

  6. 对sort_buffer中的数据按照字段name做快速排序;

  7. 按照排序结果取前1000行返回给客户端。

我们暂且把这个排序过程,称为全字段排序,执行流程的示意图如下所示。

图中“按name排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size,就是MySQL为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。

你可以用下面介绍的方法,来确定一个排序语句是否使用了临时文件。

/* 打开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;

这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的,你可以从 number_of_tmp_files中看到是否使用了临时文件。

number_of_tmp_files表示的是,排序过程中使用的临时文件数。你一定奇怪,为什么需要12个文件?内存放不下时,就需要使用外部排序,外部排序一般使用归并排序算法。可以这么简单理解,MySQL将需要排序的数据分成12份,每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

如果sort_buffer_size超过了需要排序的数据量的大小,number_of_tmp_files就是0,表示排序可以直接在内存中完成。

否则就需要放在临时文件中排序。sort_buffer_size越小,需要分成的份数越多,number_of_tmp_files的值就越大。

我们的示例表中有4000条满足city='杭州’的记录,所以你可以看到 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。

这是因为查询OPTIMIZER_TRACE这个表时,需要用到临时表,而internal_tmp_disk_storage_engine的默认值是InnoDB。如果使用的是InnoDB引擎的话,把数据从临时表取出来的时候,会让Innodb_rows_read的值加1。

rowid排序

在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字段很多的话,那么sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序的性能会很差。

所以如果单行很大,这个方法效率不够好。

如果MySQL认为排序的单行长度太大会怎么做呢?

接下来,我来修改一个参数,让MySQL采用另外一种算法。

SET max_length_for_sort_data = 16;

max_length_for_sort_data,是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是,如果单行的长度超过这个值,MySQL就认为单行太大,要换一个算法。

city、name、age 这三个字段的定义总长度是36,我把max_length_for_sort_data设置为16,我们再来看看计算过程有什么改变。

新的算法放入sort_buffer的字段,只有要排序的列(即name字段)和主键id。

但这时,排序的结果就因为少了city和age字段的值,不能直接返回了,整个执行流程就变成如下所示的样子:

  1. 初始化sort_buffer,确定放入两个字段,即name和id;

  2. 从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X;

  3. 到主键id索引取出整行,取name、id这两个字段,存入sort_buffer中;

  4. 从索引city取下一个记录的主键id;

  5. 重复步骤3、4直到不满足city='杭州’条件为止,也就是图中的ID_Y;

  6. 对sort_buffer中的数据按照字段name进行排序;

  7. 遍历排序结果,取前1000行,并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

这个执行流程的示意图如下,我把它称为rowid排序。

对比图3的全字段排序流程图你会发现,rowid排序多访问了一次表t的主键索引,就是步骤7。

需要说明的是,最后的“结果集”是一个逻辑概念,实际上MySQL服务端从排序后的sort_buffer中依次取出id,然后到原表查到city、name和age这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的。

根据这个说明过程和图示,你可以想一下,这个时候执行select @b-@a,结果会是多少呢?

现在,我们就来看看结果有什么不同。

首先,图中的examined_rows的值还是4000,表示用于排序的数据是4000行。但是select @b-@a这个语句的值变成5000了。

因为这时候除了排序过程外,在排序完成后,还要根据id去原表取值。由于语句是limit 1000,因此会多读1000行。

图6 rowid排序的OPTIMIZER_TRACE部分输出

从OPTIMIZER_TRACE的结果中,你还能看到另外两个信息也变了。

  • sort_mode变成了<sort_key, rowid>,表示参与排序的只有name和id这两个字段。
  • number_of_tmp_files变成10了,是因为这时候参与排序的行数虽然仍然是4000行,但是每一行都变小了,因此需要排序的总数据量就变小了,需要的临时文件也相应地变少了。

全字段排序 VS rowid排序

我们来分析一下,从这两个执行流程里,还能得出什么结论。

如果MySQL实在是担心排序内存太小,会影响排序效率,才会采用rowid排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据。

优点:更好的利用内存的sort_buffer进行排序操作,尽量减少对磁盘的访问
缺点:回表的操作是随机IO,会造成大量的随机读,不一定就比全字段排序减少对磁盘的访问

如果MySQL认为内存足够大,会优先选择全字段排序,把需要的字段都放到sort_buffer中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。

缺点:
1.造成sort_buffer中存放不下很多数据,因为除了排序字段还存放其他字段,对sort_buffer的利用效率不高
2.当所需排序数据量很大时,会有很多的临时文件,排序性能也会很差
优点:MySQL认为内存足够大时会优先选择全字段排序,因为这种方式比rowid 排序避免了一次回表操作

这也就体现了MySQL的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。

对于InnoDB表来说,rowid排序会要求回表多造成磁盘读,因此不会被优先选择。

所以,我们可以在这个市民表上创建一个city和name的联合索引,对应的SQL语句是:

alter table t add index city_user(city, name);

作为与city索引的对比,我们来看看这个索引的示意图。

图7 city和name联合索引示意图

在这个索引里面,我们依然可以用树搜索的方式定位到第一个满足city='杭州’的记录,并且额外确保了,接下来按顺序取“下一条记录”的遍历过程中,只要city的值是杭州,name的值就一定是有序的。

这样整个查询过程的流程就变成了:

  1. 从索引(city,name)找到第一个满足city='杭州’条件的主键id;

  2. 到主键id索引取出整行,取name、city、age三个字段的值,作为结果集的一部分直接返回;

  3. 从索引(city,name)取下一个记录主键id;

  4. 重复步骤2、3,直到查到第1000条记录,或者是不满足city='杭州’条件时循环结束。

可以看到,这个查询过程不需要临时表,也不需要排序。接下来,我们用explain的结果来印证一下。

从图中可以看到,Extra字段中没有Using filesort了,也就是不需要排序了。而且由于(city,name)这个联合索引本身有序,所以这个查询也不用把4000行全都读一遍,只要找到满足条件的前1000条记录就可以退出了。也就是说,在我们这个例子里,只需要扫描1000次。

覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。

按照覆盖索引的概念,我们可以再优化一下这个查询语句的执行流程。

针对这个查询,我们可以创建一个city、name和age的联合索引,对应的SQL语句就是:

alter table t add index city_user_age(city, name, age);

这时,对于city字段的值相同的行来说,还是按照name字段的值递增排序的,此时的查询语句也就不再需要排序了。这样整个查询语句的执行流程就变成了:

  1. 从索引(city,name,age)找到第一个满足city='杭州’条件的记录,取出其中的city、name和age这三个字段的值,作为结果集的一部分直接返回;

  2. 从索引(city,name,age)取下一个记录,同样取出这三个字段的值,作为结果集的一部分直接返回;

  3. 重复执行步骤2,直到查到第1000条记录,或者是不满足city='杭州’条件时循环结束。

然后,我们再来看看explain的结果。

可以看到,Extra字段里面多了“Using index”,表示的就是使用了覆盖索引,性能上会快很多。

当然,这里并不是说要为了每个查询能用上覆盖索引,就要把语句中涉及的字段都建上联合索引,毕竟索引还是有维护代价的。这是一个需要权衡的决定。


假设你的表里面已经有了city_name(city, name)这个联合索引,然后你要查杭州和苏州两个城市中所有的市民的姓名,并且按名字排序,显示前100条记录。如果SQL查询语句是这么写的 :

mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;

那么,这个语句执行的时候会有排序过程吗,为什么?

如果业务端代码由你来开发,需要实现一个在数据库端不需要排序的方案,你会怎么实现呢?

进一步地,如果有分页需求,要显示第101页,也就是说语句最后要改成 “limit 10000,100”, 你的实现方法又会是什么呢?

虽然有(city,name)联合索引,对于单个city内部,name是递增的。但是由于这条SQL语句不是要单独地查一个city的值,而是同时查了"杭州"和" 苏州 "两个城市,因此所有满足条件的name就不是递增的了。也就是说,这条SQL语句需要排序。

那怎么避免排序呢?

这里,我们要用到(city,name)联合索引的特性,把这一条语句拆成两条语句,执行流程如下:

  1. 执行select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的,客户端用一个长度为100的内存数组A保存结果。

  2. 执行select * from t where city=“苏州” order by name limit 100; 用相同的方法,假设结果被存进了内存数组B。

  3. 现在A和B是两个有序数组,然后你可以用归并排序的思想,得到name最小的前100值,就是我们需要的结果了。

如果把这条SQL语句里“limit 100”改成“limit 10000,100”的话,处理方式其实也差不多,即:要把上面的两条语句改成写:

select * from t where city="杭州" order by name limit 10100; 

 select * from t where city="苏州" order by name limit 10100。

这时候数据量较大,可以同时起两个连接一行行读结果,用归并排序算法拿到这两个结果集里,按顺序取第10001~10100的name值,就是需要的结果了。

当然这个方案有一个明显的损失,就是从数据库返回给客户端的数据量变大了。

所以,如果数据的单行比较大的话,可以考虑把这两条SQL语句改成下面这种写法:

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的name、id的值,然后拿着这100个id到数据库中去查出所有记录。


如何正确地显示随机消息

建表语句和初始数据的命令如下:

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

内存临时表

首先,你会想到用order by rand()来实现这个逻辑。

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

这个语句的意思很直白,随机排序取前3个。虽然这个SQL语句写法很简单,但执行流程却有点复杂的。

我们先用explain命令来看看这个语句的执行情况。

Extra字段显示Using temporary,表示的是需要使用临时表;Using filesort,表示的是需要执行排序操作。

因此这个Extra的意思就是,需要临时表,并且需要在临时表上排序。

全字段排序
rowid排序

对于InnoDB表来说,执行全字段排序会减少磁盘访问,因此会被优先选择。

对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。优化器没有了这一层顾虑,那么它会优先考虑的,就是用于排序的行越少越好了,所以,MySQL这时就会选择rowid排序。

这条语句的执行流程是这样的:

  1. 创建一个临时表。这个临时表使用的是memory引擎,表里有两个字段,第一个字段是double类型,为了后面描述方便,记为字段R,第二个字段是varchar(64)类型,记为字段W。并且,这个表没有建索引。

  2. 从words表中,按主键顺序取出所有的word值。对于每一个word值,调用rand()函数生成一个大于0小于1的随机小数,并把这个随机小数和word分别存入临时表的R和W字段中,到此,扫描行数是10000。

  3. 现在临时表有10000行数据了,接下来你要在这个没有索引的内存临时表上,按照字段R排序。

  4. 初始化 sort_buffer。sort_buffer中有两个字段,一个是double类型,另一个是整型。

  5. 从内存临时表中一行一行地取出R值和位置信息(rowid),分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描,此时扫描行数增加10000,变成了20000。

  6. 在sort_buffer中根据R的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。

  7. 排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出word值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了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;

其中,Rows_examined:20003就表示这个语句执行过程中扫描了20003行,也就验证了我们分析得出的结论。

图中的pos就是位置信息,你可能会觉得奇怪,这里的“位置信息”是个什么概念?在上一篇文章中,我们对InnoDB表排序的时候,明明用的还是ID字段。

MySQL的表是用什么方法来定位“一行数据”的。

其实不是的。如果你创建的表没有主键,或者把一个表的主键删掉了,那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。

这也就是排序模式里面,rowid名字的来历。实际上它表示的是:每个引擎用来唯一标识数据行的信息。

  • 对于有主键的InnoDB表来说,这个rowid就是主键ID;
  • 对于没有主键的InnoDB表来说,这个rowid就是由系统生成的;
  • MEMORY引擎不是索引组织表。在这个例子里面,你可以认为它就是一个数组。因此,这个rowid其实就是数组的下标。

order by rand()使用了内存临时表,内存临时表排序的时候使用了rowid排序方法。

磁盘临时表

那么,是不是所有的临时表都是内存表呢?

其实不是的。tmp_table_size这个配置限制了内存临时表的大小,默认值是16M。如果临时表大小超过了tmp_table_size,那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是InnoDB,是由参数internal_tmp_disk_storage_engine控制的。

当使用磁盘临时表的时候,对应的就是一个没有显式索引的InnoDB表的排序过程。

为了复现这个过程,我把tmp_table_size设置成1024,把sort_buffer_size设置成 32768, 把 max_length_for_sort_data 设置成16。

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

然后,我们来看一下这次OPTIMIZER_TRACE的结果。

因为将max_length_for_sort_data设置成16,小于word字段的长度定义,所以我们看到sort_mode里面显示的是rowid排序,这个是符合预期的,参与排序的是随机值R字段和rowid字段组成的行。

R字段存放的随机值就8个字节,rowid是6个字节(至于为什么是6字节,就留给你课后思考吧),数据总行数是10000,这样算出来就有140000字节,超过了sort_buffer_size 定义的 32768字节了。但是,number_of_tmp_files的值居然是0,难道不需要用临时文件吗?

这个SQL语句的排序确实没有用到临时文件,采用是MySQL 5.6版本引入的一个新的排序算法,即:优先队列排序算法。接下来,我们就看看为什么没有使用临时文件的算法,也就是归并排序算法,而是采用了优先队列排序算法。

其实,我们现在的SQL语句,只需要取R值最小的3个rowid。但是,如果使用归并排序算法的话,虽然最终也能得到前3个值,但是这个算法结束后,已经将10000行数据都排好序了。

也就是说,后面的9997行也是有序的了。但,我们的查询并不需要这些数据是有序的。所以,想一下就明白了,这浪费了非常多的计算量。

而优先队列算法,就可以精确地只得到三个最小值,执行流程如下:

  1. 对于这10000个准备排序的(R,rowid),先取前三行,构造成一个堆;

(对数据结构印象模糊的同学,可以先设想成这是一个由三个元素组成的数组)

  1. 取下一个行(R’,rowid’),跟当前堆里面最大的R比较,如果R’小于R,把这个(R,rowid)从堆中去掉,换成(R’,rowid’);

  2. 重复第2步,直到第10000个(R’,rowid’)完成比较。

图6是模拟6个(R,rowid)行,通过优先队列排序找到最小的三个R值的行的过程。整个排序过程中,为了最快地拿到当前堆的最大值,总是保持最大值在堆顶,因此这是一个最大堆。

图5的OPTIMIZER_TRACE结果中,filesort_priority_queue_optimization这个部分的chosen=true,就表示使用了优先队列排序算法,这个过程不需要临时文件,因此对应的number_of_tmp_files是0。

这个流程结束后,我们构造的堆里面,就是这个10000行里面R值最小的三行。然后,依次把它们的rowid取出来,去临时表里面拿到word字段,这个过程就跟上一篇文章的rowid排序的过程一样了。

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

你可能会问,这里也用到了limit,为什么没用优先队列排序算法呢?原因是,这条SQL语句是limit 1000,如果使用优先队列算法的话,需要维护的堆的大小就是1000行的(name,rowid),超过了我设置的sort_buffer_size大小,所以只能使用归并排序算法。

总之,不论是使用哪种类型的临时表,order by rand()这种写法都会让计算过程非常复杂,需要大量的扫描行数,因此排序过程的资源消耗也会很大。

随机排序方法

我们先把问题简化一下,如果只随机选择1个word值,可以怎么做呢?思路上是这样的:

  1. 取得这个表的主键id的最大值M和最小值N;

  2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;

  3. 取不小于X的第一个ID的行。

我们把这个算法,暂时称作随机算法1。这里,我直接给你贴一下执行语句的序列:

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中间可能有空洞,因此选择不同行的概率不一样,不是真正的随机。

比如你有4个id,分别是1、2、4、5,如果按照上面的方法,那么取到 id=4的这一行的概率是取得其他行概率的两倍。

如果这四行的id分别是1、2、40000、40001呢?这个算法基本就能当bug来看待了。

所以,为了得到严格随机的结果,你可以用下面这个流程:

  1. 取得整个表的行数,并记为C。

  2. 取得 Y = floor(C * rand())。 floor函数在这里的作用,就是取整数部分。

  3. 再用limit Y,1 取得一行。

我们把这个算法,称为随机算法2。下面这段代码,就是上面流程的执行语句的序列。

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;

由于limit 后面的参数不能直接跟变量,所以我在上面的代码中使用了prepare+execute的方法。你也可以把拼接SQL语句的方法写在应用程序中,会更简单些。

这个随机算法2,解决了算法1里面明显的概率不均匀问题。

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

当然,随机算法2跟直接order by rand()比起来,执行代价还是小很多的。

你可能问了,如果按照这个表有10000行来计算的话,C=10000,要是随机到比较大的Y值,那扫描行数也跟20000差不多了,接近order by rand()的扫描行数,为什么说随机算法2的代价要小很多呢?我就把这个问题留给你去课后思考吧。

现在,我们再看看,如果我们按照随机算法2的思路,要随机取3个word值呢?你可以这么做:

  1. 取得整个表的行数,记为C;

  2. 根据相同的随机方法得到Y1、Y2、Y3;

  3. 再执行三个limit Y, 1语句得到三行数据。

我们把这个算法,称作随机算法3。下面这段代码,就是上面流程的执行语句的序列。

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;

上面的随机算法3的总扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1),实际上它还是可以继续优化,来进一步减少扫描行数的。

我的问题是,如果你是这个需求的开发人员,你会怎么做,来减少扫描行数呢?说说你的方案,并说明你的方案需要的扫描行数。

  • 如果一定要用数据库来做,老师的方案1比较好,空洞的问题,如果单词库不变,可以在上线前整理数据,把空洞处理调。比如:原来单词存在A表,新建B表 ,执行 insert into B(word) select word from A. B的id是自增的,就会生成连续的主键。当然如果A表写比较频繁,且数据量较大,业务上禁用 这种写法,RR的隔离级别会锁A表
  • 可以按Y排个序,第一条取完,拿到对应id,然后有一条语句就是where id大于xxx,limit y2-y1,1
  • 假设Y1,Y2,Y3是由小到大的三个数,则可以优化成这样,这样扫描行数为C+Y3
    id1 = select * from t limit @Y1,1;
    id2= select * from t where id > id1 limit @Y2-@Y1,1;
    select * from t where id > id2 limit @Y3 - @Y2,1;
  • 取Y1、Y2和Y3里面最大的一个数,记为M,最小的一个数记为N,然后执行下面这条SQL语句:

    mysql> select * from t limit N, M-N+1;
    

    再加上取整个表总行数的C行,这个方案的扫描行数总共只需要C+M+1行。

    当然也可以先取回id值,在应用中确定了三个id值以后,再执行三次where id=X的语句也是可以的。


对索引字段做函数操作,破坏索引有效性,放弃走树搜索功能,但不代表不遍历该索引

——避免对索引字段做函数操作,可以在索引字段的参数上做函数操作

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

表的建表语句如下:

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设,现在已经记录了从2016年初到2018年底的所有数据,运营部门有一个需求是,要统计发生在所有年份中7月份的交易记录总数。这个逻辑看上去并不复杂,你的SQL语句可能会这么写:

mysql> select count(*) from tradelog where month(t_modified)=7;

由于t_modified字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果(对字段做了函数计算,就用不上索引)。

如果你的SQL语句条件用的是where t_modified='2018-7-1’的话,引擎就会按照上面绿色箭头的路线,快速定位到 t_modified='2018-7-1’需要的结果。

实际上,B+树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。

但是,如果计算month()函数的话,你会看到传入7的时候,在树的第一层就不知道该怎么办了。

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

在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引t_modified,优化器对比索引大小后发现,索引t_modified更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引t_modified。

使用explain命令,查看一下这条SQL语句的执行结果。

key="t_modified"表示的是,使用了t_modified这个索引;我在测试表数据中插入了10万行数据,rows=100335,说明这条语句扫描了整个索引的所有值;Extra字段的Using index,表示的是使用了覆盖索引。

也就是说,由于在t_modified字段加了month()函数操作,导致了全索引扫描。为了能够用上索引的快速定位能力,我们就要把SQL语句改成基于字段本身的范围查询。按照下面这个写法,优化器就能按照我们预期的,用上t_modified索引的快速定位能力了。

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

当然,如果你的系统上线时间更早,或者后面又插入了之后年份的数据的话,你就需要再把其他年份补齐。

 不过优化器在个问题上确实有“偷懒”行为,即使是对于不改变有序性的函数,也不会考虑使用索引。比如,对于select * from tradelog where id + 1 = 10000这个SQL语句,这个加1操作并不会改变有序性,但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以,需要你在写SQL语句的时候,手动改写成 where id = 10000 -1才可以。

案例二:隐式类型转换

SQL语句:

mysql> select * from tradelog where tradeid=110717;

交易编号tradeid这个字段上,本来就有索引,但是explain的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid的字段类型是varchar(32),而输入的参数却是整型,所以需要做类型转换。

看 select “10” > 9的结果:“将字符串转成数字”

刚刚那个语句相当于:

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

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

假设系统里还有另外一个表trade_detail,用于记录交易的操作细节。为了便于量化分析和复现,我往交易日志表tradelog和交易详情表trade_detail这两个表里插入一些数据。

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

这时候,如果要查询id=2的交易的所有操作步骤信息,SQL语句可以这么写:

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

这个结果:

  1. 第一行显示优化器会先在交易记录表tradelog上查到id=2的行,这个步骤用上了主键索引,rows=1表示只扫描一行;

  2. 第二行key=NULL,表示没有用上交易详情表trade_detail上的tradeid索引,进行了全表扫描。

在这个执行计划里,是从tradelog表中取tradeid字段,再去trade_detail表里查询匹配字段。因此,我们把tradelog称为驱动表,把trade_detail称为被驱动表,把tradeid称为关联字段。

图中:

  • 第1步,是根据id在tradelog表里找到L2这一行;
  • 第2步,是从L2中取出tradeid字段的值;
  • 第3步,是根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是,这个过程是通过遍历主键索引的方式,一个一个地判断tradeid的值是否匹配。

因为这两个表的字符集不同,一个是utf8,一个是utf8mb4,所以做表连接查询的时候用不上关联字段的索引。字符集utf8mb4是utf8的超集,所以当这两个类型的字符串在做比较的时候,MySQL内部的操作是,先把utf8字符串转成utf8mb4字符集,再做比较。

实际上这个语句等同于下面这个写法:

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

CONVERT()函数,在这里的意思是把输入的字符串转成utf8mb4字符集。

“查找trade_detail表里id=4的操作,对应的操作者是谁”,再来看下这个语句和它的执行计划。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

这个语句里trade_detail 表成了驱动表,但是explain结果的第二行显示,这次的查询操作用上了被驱动表tradelog里的索引(tradeid),扫描行数是1。

这也是两个tradeid字段的join操作,为什么这次能用上被驱动表的tradeid索引呢?我们来分析一下。

假设驱动表trade_detail里id=4的行记为R4,那么在连接的时候(图5的第3步),被驱动表tradelog上执行的就是类似这样的SQL 语句:

select operator from tradelog  where traideid =$R4.tradeid.value; 

这时候$R4.tradeid.value的字符集是utf8, 按照字符集转换规则,要转成utf8mb4,所以这个过程就被改写成:

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 

这里的CONVERT函数是加在输入参数上的,这样就可以用上被驱动表的traideid索引。

理解了原理以后,就可以用来指导操作了。如果要优化语句

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

的执行过程,有两种做法:

  • 比较常见的优化方法是,把trade_detail表上的tradeid字段的字符集也改成utf8mb4,这样就没有字符集转换的问题了。
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
  • 如果能够修改字段的字符集的话,是最好不过了。但如果数据量比较大, 或者业务上暂时不能做这个DDL的话,那就只能采用修改SQL语句的方法了。
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

这里,我主动把 l.tradeid转成utf8,就避免了被驱动表上的字符编码转换,从explain结果可以看到,这次索引走对了。


表结构如下:

mysql> CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

假设现在表里面,有100万行数据,其中有10万行数据的b的值是’1234567890’, 假设现在执行语句是这么写的:

mysql> select * from table_a where b='1234567890abcd';

这时候,MySQL会怎么执行呢?

最理想的情况是,MySQL看到字段b定义的是varchar(10),那肯定返回空呀。可惜,MySQL并没有这么做。

那要不,就是把’1234567890abcd’拿到索引里面去做匹配,肯定也没能够快速判断出索引树b上并没有这个值,也很快就能返回空结果。

但实际上,MySQL也不是这么做的。

这条SQL语句的执行很慢,流程是这样的:

  1. 在传给引擎执行的时候,做了字符截断。因为引擎里面这个行只定义了长度是10,所以只截了前10个字节,就是’1234567890’进去做匹配;

  2. 这样满足条件的数据有10万行;

  3. 因为是select *, 所以要做10万次回表;

  4. 但是每次回表以后查出整行,到server层一判断,b的值都不是’1234567890abcd’;

  5. 返回结果是空。

虽然执行过程中可能经过函数操作,但是最终在拿到结果后,server层还是要做一轮判断的。


查一行的语句,也执行这么慢

基于这个表来说明今天的问题。这个表有两个字段id和c,并且我在里面插入了10万行记录。

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

接下来,我会用几个不同的场景来举例,有些是前面的文章中我们已经介绍过的知识点,你看看能不能一眼看穿,来检验一下吧。

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

如图1所示,在表t执行下面的SQL语句:

mysql> select * from t where id=1;

一般碰到这种情况的话,大概率是表t被锁住了。接下来分析原因的时候,一般都是首先执行一下show processlist命令,看看当前语句处于什么状态。

然后我们再针对每种状态,去分析它们产生的原因、如何复现,以及如何处理。

等MDL锁

如图2所示,就是使用show processlist命令查看Waiting for table metadata lock的示意图

出现这个状态表示的是,现在有一个线程正在表t上请求或者持有MDL写锁,把select语句堵住了。

不过,在MySQL 5.7版本下复现这个场景,也很容易。如图3所示,我给出了简单的复现步骤。

session A 通过lock table命令持有表t的MDL写锁,而session B的查询需要获取MDL读锁。所以,session B进入等待状态。

这类问题的处理方式,就是找到谁持有MDL写锁,然后把它kill掉。

但是,由于在show processlist的结果里面,session A的Command列是“Sleep”,导致查找起来很不方便。不过有了performance_schema和sys系统库以后,就方便多了。(MySQL启动时需要设置performance_schema=on,相比于设置为off会有10%左右的性能损失)

通过查询sys.schema_table_lock_waits这张表,我们就可以直接找出造成阻塞的process id,把这个连接用kill 命令断开即可。

等flush

接下来,我给你举另外一种查询被堵住的情况。

我在表t上,执行下面的SQL语句:

mysql> select * from information_schema.processlist where id=1;

我查出来这个线程的状态是Waiting for table flush,你可以设想一下这是什么原因。

这个状态表示的是,现在有一个线程正要对表t做flush操作。MySQL里面对表做flush操作的用法,一般有以下两个:

flush tables t with read lock;

flush tables with read lock;

这两个flush语句,如果指定表t的话,代表的是只关闭表t;如果没有指定具体的表名,则表示关闭MySQL里所有打开的表。

但是正常这两个语句执行起来都很快,除非它们也被别的线程堵住了。

所以,出现Waiting for table flush状态的可能情况是:有一个flush tables命令被别的语句堵住了,然后它又堵住了我们的select语句。

现在,我们一起来复现一下这种情况,复现步骤如图6所示:

在session A中,我故意每行都调用一次sleep(1),这样这个语句默认要执行10万秒,在这期间表t一直是被session A“打开”着。然后,session B的flush tables t命令再要去关闭表t,就需要等session A的查询结束。这样,session C要再次查询的话,就会被flush 命令堵住了。

图7是这个复现步骤的show processlist结果。这个例子的排查也很简单,你看到这个show processlist的结果,肯定就知道应该怎么做了。(kill id 为7的用户连接)

等行锁

现在,经过了表级锁的考验,我们的select 语句终于来到引擎里了。

mysql> select * from t where id=1 lock in share mode; 

由于访问id=1这个记录时要加读锁,如果这时候已经有一个事务在这行记录上持有一个写锁,我们的select语句就会被堵住。

复现步骤和现场如下:

显然,session A启动了事务,占有写锁,还不提交,是导致session B被堵住的原因。

这个问题并不难分析,但问题是怎么查出是谁占着这个写锁。如果你用的是MySQL 5.7版本,可以通过sys.innodb_lock_waits 表查到。

查询方法是:

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

可以看到,这个信息很全,4号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式,就是KILL QUERY 4或KILL 4。

不过,这里不应该显示“KILL QUERY 4”。这个命令表示停止4号线程当前正在执行的语句,而这个方法其实是没有用的。因为占有行锁的是update语句,这个语句已经是之前执行完成了的,现在执行KILL QUERY,无法让这个事务去掉id=1上的行锁。

实际上,KILL 4才有效,也就是说直接断开这个连接。这里隐含的一个逻辑就是,连接被断开的时候,会自动回滚这个连接里面正在执行的线程,也就释放了id=1上的行锁。

第二类:查询慢

经过了重重封“锁”,我们再来看看一些查询慢的例子。

先来看一条你一定知道原因的SQL语句:

mysql> select * from t where c=50000 limit 1;

由于字段c上没有索引,这个语句只能走id主键顺序扫描,因此需要扫描5万行。

作为确认,你可以看一下慢查询日志。注意,这里为了把所有语句记录到slow log里,我在连接后先执行了 set long_query_time=0,将慢查询日志的时间阈值设置为0。

Rows_examined显示扫描了50000行。你可能会说,不是很慢呀,11.5毫秒就返回了,我们线上一般都配置超过1秒才算慢查询。但你要记住:坏查询不一定是慢查询。我们这个例子里面只有10万行记录,数据量大起来的话,执行时间就线性涨上去了。

扫描行数多,所以执行慢,这个很好理解。

但是接下来,我们再看一个只扫描一行,但是执行很慢的语句。

如图12所示,是这个例子的slow log。可以看到,执行的语句是

mysql> select * from t where id=1;

虽然扫描行数是1,但执行时间却长达800毫秒。

是不是有点奇怪呢,这些时间都花在哪里了?

如果我把这个slow log的截图再往下拉一点,你可以看到下一个语句,select * from t where id=1 lock in share mode,执行时扫描行数也是1行,执行时间是0.2毫秒。

看上去是不是更奇怪了?按理说lock in share mode还要加锁,时间应该更长才对啊。

可能有的同学已经有答案了。如果你还没有答案的话,我再给你一个提示信息,图14是这两个语句的执行输出结果。

第一个语句的查询结果里c=1,带lock in share mode的语句返回的是c=1000001。看到这里应该有更多的同学知道原因了。如果你还是没有头绪的话,也别着急。我先跟你说明一下复现步骤,再分析原因。

你看到了,session A先用start transaction with consistent snapshot命令启动了一个事务,之后session B才开始执行update 语句。

session B执行完100万次update语句后,id=1这一行处于什么状态呢?你可以从图16中找到答案。

session B更新完100万次,生成了100万个回滚日志(undo log)。

带lock in share mode的SQL语句,是当前读,因此会直接读到1000001这个结果,所以速度很快;而select * from t where id=1这个语句,是一致性读,因此需要从1000001开始,依次执行undo log,执行了100万次以后,才将1这个结果返回。


下面的SQL语句,

begin;
select * from t where c=5 for update;
commit;

这个语句序列是怎么加锁的呢?加的锁又是什么时候释放呢?

版本5.7.13
rc模式下:
session 1:
begin;
select * from t where c=5 for update;
session 2:
delete from t where c=10 --等待
session 3:
insert into t values(100001,8) --成功
session 1:
commit
session 2:事务执行成功
rr模式下:
begin;
select * from t where c=5 for update;
session 2:
delete from t where c=10 --等待
session 3:
insert into t values(100001,8) --等待
session 1:
commit
session 2:事务执行成功
session 3:事务执行成功
从上面这两个简单的例子,可以大概看出上锁的流程.
不管是rr模式还是rc模式,这条语句都会先在server层对表加上MDL S锁,然后进入到引擎层。

rc模式下,由于数据量不大只有10W。通过实验可以证明session 1上来就把该表的所有行都锁住了。
导致其他事务要对该表的所有现有记录做更新,是阻塞状态。为什么insert又能成功?
说明rc模式下for update语句没有上gap锁,所以不阻塞insert对范围加插入意向锁,所以更新成功。
session 1commit后,session 2执行成功。表明所有行的x锁是在事务提交完成以后才释放。

rr模式下,session 1和session 2与rc模式下都一样,说明rr模式下也对所有行上了X锁。
唯一的区别是insert也等待了,是因为rr模式下对没有索引的更新,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。由于gap锁阻塞了insert要加的插入意向锁,导致insert也处于等待状态。只有当session 1 commit完成以后。session 1上的所有锁才会释放,S2,S3执行成功

由于例子中的数据量还比较小,如果数据量达到千万级别,就比较直观的能看出,上锁是逐行上锁的一个过程.扫描一条上一条,直到所有行扫描完,rc模式下对所有行上x锁。rr模式下不仅对所有行上X锁,还对所有区间上gap锁.直到事务提交或者回滚完成后,上的锁才会被释放。


幻读是什么,幻读有什么问题

建表和初始化语句如下(为了便于本期的例子说明,我把上篇文章中用到的表结构做了点儿修改):

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

这个表除了主键id外,还有一个索引c,初始化语句在表中插入了6行数据。

上期我留给你的问题是,下面的语句序列,是怎么加锁的,加的锁又是什么时候释放的呢?

begin;
select * from t where d=5 for update;
commit;

比较好理解的是,这个语句会命中d=5的这一行,对应的主键id=5,因此在select 语句执行完成后,id=5这一行会加一个写锁,而且由于两阶段锁协议,这个写锁会在执行commit语句的时候释放。

由于字段d上没有索引,因此这条查询语句会做全表扫描。那么,其他被扫描到的,但是不满足条件的5行记录上,会不会被加锁呢?

我们知道,InnoDB的默认事务隔离级别是可重复读,所以本文接下来没有特殊说明的部分,都是设定在可重复读隔离级别下。

幻读是什么?

现在,我们就来分析一下,如果只在id=5这一行加锁,而其他行的不加锁的话,会怎么样。

下面先来看一下这个场景:

可以看到,session A里执行了三次查询,分别是Q1、Q2和Q3。它们的SQL语句相同,都是select * from t where d=5 for update。这个语句的意思你应该很清楚了,查所有d=5的行,而且使用的是当前读,并且加上写锁。现在,我们来看一下这三条SQL语句,分别会返回什么结果。

  1. Q1只返回id=5这一行;

  2. 在T2时刻,session B把id=0这一行的d值改成了5,因此T3时刻Q2查出来的是id=0和id=5这两行;

  3. 在T4时刻,session C又插入一行(1,1,5),因此T5时刻Q3查出来的是id=0、id=1和id=5的这三行。

其中,Q3读到id=1这一行的现象,被称为“幻读”。也就是说,

幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。

这里,我需要对“幻读”做一个说明:

  1. 在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现。

  2. 上面session B的修改结果,被session A之后的select语句用“当前读”看到,不能称为幻读。幻读仅专指“新插入的行”。

因为这三个查询都是加了for update,都是当前读。而当前读的规则,就是要能读到所有已经提交的记录的最新值。并且,session B和sessionC的两条语句,执行后就会提交,所以Q2和Q3就是应该看到这两个事务的操作效果,而且也看到了,这跟事务的可见性规则并不矛盾。

幻读有什么问题?

首先是语义上的。session A在T1时刻就声明了,“我要把所有d=5的行锁住,不准别的事务进行读写操作”。而实际上,这个语义被破坏了。

如果现在这样看感觉还不明显的话,我再往session B和session C里面分别加一条SQL语句,你再看看会出现什么现象。

session B的第二条语句update t set c=5 where id=0,语义是“我把id=0、d=5这一行的c值,改成了5”。

由于在T1时刻,session A 还只是给id=5这一行加了行锁, 并没有给id=0这行加上锁。因此,session B在T2时刻,是可以执行这两条update语句的。这样,就破坏了 session A 里Q1语句要锁住所有d=5的行的加锁声明。

session C也是一样的道理,对id=1这一行的修改,也是破坏了Q1的加锁声明。

其次,是数据一致性的问题。

我们知道,锁的设计是为了保证数据的一致性。而这个一致性,不止是数据库内部数据状态在此刻的一致性,还包含了数据和日志在逻辑上的一致性。

给session A在T1时刻再加一个更新语句,即:update t set d=100 where d=5。

update的加锁语义和select ...for update 是一致的,所以这时候加上这条update语句也很合理。session A声明说“要给d=5的语句加上锁”,就是为了要更新数据,新加的这条update语句就是把它认为加上了锁的这一行的d值修改成了100。

现在,我们来分析一下图3执行完成后,数据库里会是什么结果。

  1. 经过T1时刻,id=5这一行变成 (5,5,100),当然这个结果最终是在T6时刻正式提交的;

  2. 经过T2时刻,id=0这一行变成(0,5,5);

  3. 经过T4时刻,表里面多了一行(1,5,5);

  4. 其他行跟这个执行序列无关,保持不变。

这样看,这些数据也没啥问题,但是我们再来看看这时候binlog里面的内容。

  1. T2时刻,session B事务提交,写入了两条语句;

  2. T4时刻,session C事务提交,写入了两条语句;

  3. T6时刻,session A事务提交,写入了update t set d=100 where d=5 这条语句。

我统一放到一起的话,就是这样的:

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行,d改成100*/

好,你应该看出问题了。这个语句序列,不论是拿到备库去执行,还是以后用binlog来克隆一个库,这三行的结果,都变成了 (0,5,100)、(1,5,100)和(5,5,100)。

也就是说,id=0和id=1这两行,发生了数据不一致。这个问题很严重,是不行的。

这个数据不一致到底是怎么引入的?

我们分析一下可以知道,这是我们假设“select * from t where d=5 for update这条语句只给d=5这一行,也就是id=5的这一行加锁”导致的。

那怎么改呢?我们把扫描过程中碰到的行,也都加上写锁,再来看看执行效果。

由于session A把所有的行都加了写锁,所以session B在执行第一个update语句的时候就被锁住了。需要等到T6时刻session A提交以后,session B才能继续执行。

这样对于id=0这一行,在数据库里的最终结果还是 (0,5,5)。在binlog里面,执行序列是这样的:

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行,d改成100*/

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

可以看到,按照日志顺序执行,id=0这一行的最终结果也是(0,5,5)。所以,id=0这一行的问题解决了。

但同时你也可以看到,id=1这一行,在数据库里面的结果是(1,5,5),而根据binlog的执行结果是(1,5,100),也就是说幻读的问题还是没有解决。为什么我们已经这么“凶残”地,把所有的记录都上了锁,还是阻止不了id=1这一行的插入和更新呢?

原因很简单。在T3时刻,我们给所有行加锁的时候,id=1这一行还不存在,不存在也就加不上锁。

也就是说,即使把所有的记录都加上锁,还是阻止不了新插入的记录,这也是为什么“幻读”会被单独拿出来解决的原因。

如何解决幻读?

现在你知道了,产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB只好引入新的锁,也就是间隙锁(Gap Lock)

顾名思义,间隙锁,锁的就是两个值之间的空隙。比如文章开头的表t,初始化插入了6个记录,这就产生了7个间隙。

这样,当你执行 select * from t where d=5 for update的时候,就不止是给数据库中已有的6个记录加上了行锁,还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

也就是说这时候,在一行行扫描的过程中,不仅将给行加上了行锁,还给行两边的空隙,也加上了间隙锁。

现在你知道了,数据行是可以加上锁的实体,数据行之间的间隙,也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

比如行锁,分成读锁和写锁。下图就是这两种类型行锁的冲突关系。

也就是说,跟行锁有冲突关系的是“另外一个行锁”。

但是间隙锁不一样,跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

这句话不太好理解,我给你举个例子:

这里session B并不会被堵住。因为表t里并没有c=7这个记录,因此session A加的是间隙锁(5,10)。而session B也是在这个间隙加的间隙锁。它们有共同的目标,即:保护这个间隙,不允许插入值。但,它们之间是不冲突的。

间隙锁和行锁合称next-key lock,每个next-key lock是前开后闭区间。也就是说,我们的表t初始化以后,如果用select * from t for update要把整个表所有记录锁起来,就形成了7个next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +suprenum]。

你可能会问说,这个suprenum从哪儿来的呢?

这是因为+∞是开区间。实现上,InnoDB给每个索引加了一个不存在的最大值suprenum,这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和next-key lock的引入,帮我们解决了幻读的问题,但同时也带来了一些“困扰”。

业务逻辑这样的:任意锁住一行,如果这一行不存在的话就插入,如果存在这一行就更新它的数据,代码如下:

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;

可能你会说,这个不是insert ... on duplicate key update 就能解决吗?但其实在有多个唯一键的时候,这个方法是不能满足这位提问同学的需求的。至于为什么,我会在后面的文章中再展开说明。

现在,我们就只讨论这个逻辑。

这个同学碰到的现象是,这个逻辑一旦有并发,就会碰到死锁。你一定也觉得奇怪,这个逻辑每次操作前用for update锁起来,已经是最严格的模式了,怎么还会有死锁呢?

这里,我用两个session来模拟并发,并假设N=9。

你看到了,其实都不需要用到后面的update语句,就已经形成死锁了。我们按语句执行顺序来分析一下:

  1. session A 执行select ... for update语句,由于id=9这一行并不存在,因此会加上间隙锁(5,10);

  2. session B 执行select ... for update语句,同样会加上间隙锁(5,10),间隙锁之间不会冲突,因此这个语句可以执行成功;

  3. session B 试图插入一行(9,9,9),被session A的间隙锁挡住了,只好进入等待;

  4. session A试图插入一行(9,9,9),被session B的间隙锁挡住了。

至此,两个session进入互相等待状态,形成死锁。当然,InnoDB的死锁检测马上就发现了这对死锁关系,让session A的insert语句报错返回了。

间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的


一个思考题。

实际上,这里session B和session C的insert 语句都会进入锁等待状态。

你可以试着分析一下,出现这种情况的原因是什么?

可重复读隔离级别下,经试验:
SELECT * FROM t where c>=15 and c<=20 for update; 会加如下锁:
next-key lock:(10, 15], (15, 20]
gap lock:(20, 25)

SELECT * FROM t where c>=15 and c<=20 order by c desc for update; 会加如下锁:
next-key lock:(5, 10], (10, 15], (15, 20]
gap lock:(20, 25)

session C 被锁住的原因就是根据索引 c 逆序排序后多出的 next-key lock:(5, 10]


看看session A的select语句加了哪些锁:

  1. 由于是order by c desc,第一个要定位的是索引c上“最右边的”c=20的行,所以会加上间隙锁(20,25)和next-key lock (15,20]。

  2. 在索引c上向左遍历,要扫描到c=10才停下来,所以next-key lock会加到(5,10],这正是阻塞session B的insert语句的原因。

  3. 在扫描过程中,c=20、c=15、c=10这三行都存在值,由于是select *,所以会在主键id上加三个行锁。

因此,session A 的select语句锁的范围就是:

  1. 索引c上 (5, 25);

  2. 主键索引上id=10、15、20三个行锁。


next-key lock(行锁+间隙锁)加锁规则 5.x系列<=5.7.24,8.0系列 <=8.0.13

两个“原则”、两个“优化”和一个“bug”,默认可重复读级别。

  1. 原则1:加锁的基本单位是next-key lock。next-key lock是前开后闭区间。

  2. 原则2:查找过程中访问到的对象才会加锁

  3. 优化1:索引上的等值查询给唯一索引加锁的时候,next-key lock退化为行锁

  4. 优化2:索引上的等值查询向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。

  5. 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止

表t的建表语句和初始化语句如下。

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

接下来的例子基本都是配合着图片说明的,所以我建议你可以对照着文稿看,有些例子可能会“毁三观”,也建议你读完文章后亲手实践一下。

案例一:等值查询间隙锁

第一个例子是关于等值条件操作间隙:

由于表t中没有id=7的记录,所以用我们上面提到的加锁规则判断一下的话:

  1. 根据原则1,加锁单位是next-key lock,session A加锁范围就是(5,10];

  2. 同时根据优化2,这是一个等值查询(id=7),而id=10不满足查询条件,next-key lock退化成间隙锁,因此最终加锁的范围是(5,10)。

所以,session B要往这个间隙里面插入id=8的记录会被锁住,但是session C修改id=10这行是可以的。

案例二:非唯一索引等值锁

第二个例子是关于覆盖索引上的锁:

看到这个例子,你是不是有一种“该锁的不锁,不该锁的乱锁”的感觉?我们来分析一下吧。

这里session A要给索引c上c=5的这一行加上读锁。

  1. 根据原则1,加锁单位是next-key lock,因此会给(0,5]加上next-key lock。

  2. 要注意c是普通索引,因此仅访问c=5这一条记录是不能马上停下来的,需要向右遍历,查到c=10才放弃。根据原则2,访问到的都要加锁,因此要给(5,10]加next-key lock。

  3. 但是同时这个符合优化2:等值判断,向右遍历,最后一个值不满足c=5这个等值条件,因此退化成间隙锁(5,10)。

  4. 根据原则2 ,只有访问到的对象才会加锁,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁,这就是为什么session B的update语句可以执行完成。

但session C要插入一个(7,7,7)的记录,就会被session A的间隙锁(5,10)锁住。

需要注意,在这个例子中,lock in share mode只锁覆盖索引,但是如果是for update就不一样了。 执行 for update时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明,锁是加在索引上的;同时,它给我们的指导是,如果你要用lock in share mode来给行加读锁避免数据被更新的话,就必须得绕过覆盖索引的优化,在查询字段中加入索引中不存在的字段。比如,将session A的查询语句改成select d from t where c=5 lock in share mode。你可以自己验证一下效果。

案例三:主键索引范围锁

第三个例子是关于范围查询的。

举例之前,你可以先思考一下这个问题:对于我们这个表t,下面这两条查询语句,加锁范围相同吗?

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

你可能会想,id定义为int类型,这两个语句就是等价的吧?其实,它们并不完全等价。

在逻辑上,这两条查语句肯定是等价的,但是它们的加锁规则不太一样。现在,我们就让session A执行第二个查询语句,来看看加锁效果。

现在我们就用前面提到的加锁规则,来分析一下session A 会加什么锁呢?

  1. 开始执行的时候,要找到第一个id=10的行,因此本该是next-key lock(5,10]。 根据优化1, 主键id上的等值条件,退化成行锁,只加了id=10这一行的行锁。

  2. 范围查找就往后继续找,找到id=15这一行停下来,因此需要加next-key lock(10,15]。

所以,session A这时候锁的范围就是主键索引上,行锁id=10和next-key lock(10,15]。这样,session B和session C的结果你就能理解了。

这里你需要注意一点,首次session A定位查找id=10的行的时候,是当做等值查询来判断的,而向右扫描到id=15的时候,用的是范围查询判断。

案例四:非唯一索引范围锁

接下来,我们再看两个范围查询加锁的例子,你可以对照着案例三来看。

需要注意的是,与案例三不同的是,案例四中查询语句的where部分用的是字段c。

这次session A用字段c来判断,加锁规则跟案例三唯一的不同是:在第一次用c=10定位记录的时候,索引c上加了(5,10]这个next-key lock后,由于索引c是非唯一索引,没有优化规则,也就是说不会蜕变为行锁,因此最终sesion A加的锁是,索引c上的(5,10] 和(10,15] 这两个next-key lock。

所以从结果上来看,sesson B要插入(8,8,8)的这个insert语句时就被堵住了。

这里需要扫描到c=15才停止扫描,是合理的,因为InnoDB要扫到c=15,才知道不需要继续往后找了。

案例五:唯一索引范围锁bug

前面的四个案例,我们已经用到了加锁规则中的两个原则和两个优化,接下来再看一个关于加锁规则中bug的案例。

session A是一个范围查询,按照原则1的话,应该是索引id上只加(10,15]这个next-key lock,并且因为id是唯一键,所以循环判断到id=15这一行就应该停止了。

但是实现上,InnoDB会往前扫描到第一个不满足条件的行为止,也就是id=20。而且由于这是个范围扫描,因此索引id上的(15,20]这个next-key lock也会被锁上。

所以你看到了,session B要更新id=20这一行,是会被锁住的。同样地,session C要插入id=16的一行,也会被锁住。

照理说,这里锁住id=20这一行的行为,其实是没有必要的。因为扫描到id=15,就可以确定不用往后再找了。但实现上还是这么做了,因此我认为这是个bug。

我也曾找社区的专家讨论过,官方bug系统上也有提到,但是并未被verified。所以,认为这是bug这个事儿,也只能算我的一家之言,如果你有其他见解的话,也欢迎你提出来。

案例六:非唯一索引上存在"等值"的例子

接下来的例子,是为了更好地说明“间隙”这个概念。这里,我给表t插入一条新记录。

mysql> insert into t values(30,10,30);

新插入的这一行c=10,也就是说现在表里有两个c=10的行。那么,这时候索引c上的间隙是什么状态了呢?你要知道,由于非唯一索引上包含主键的值,所以是不可能存在“相同”的两行的。

可以看到,虽然有两个c=10,但是它们的主键值id是不同的(分别是10和30),因此这两个c=10的记录之间,也是有间隙的。

图中我画出了索引c上的主键id。为了跟间隙锁的开区间形式进行区别,我用(c=10,id=30)这样的形式,来表示索引上的一行。

现在,我们来看一下案例六。

这次我们用delete语句来验证。注意,delete语句加锁的逻辑,其实跟select ... for update 是类似的,也就是我在文章开始总结的两个“原则”、两个“优化”和一个“bug”。

图7 delete 示例

这时,session A在遍历的时候,先访问第一个c=10的记录。同样地,根据原则1,这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。

然后,session A向右查找,直到碰到(c=15,id=15)这一行,循环才结束。根据优化2,这是一个等值查询,向右查找到了不满足条件的行,所以会退化成(c=10,id=10) 到 (c=15,id=15)的间隙锁。

也就是说,这个delete语句在索引c上的加锁范围,就是下图中蓝色区域覆盖的部分。

这个蓝色区域左右两边都是虚线,表示开区间,即(c=5,id=5)和(c=15,id=15)这两行上都没有锁。

案例七:limit 语句加锁

例子6也有一个对照案例,场景如下所示:

这个例子里,session A的delete语句加了 limit 2。你知道表t里c=10的记录其实只有两条,因此加不加limit 2,删除的效果都是一样的,但是加锁的效果却不同。可以看到,session B的insert语句执行通过了,跟案例六的结果不同。

这是因为,案例七里的delete语句明确加了limit 2的限制,因此在遍历到(c=10, id=30)这一行之后,满足条件的语句已经有两条,循环就结束了。

因此,索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间,如下图所示:

可以看到,(c=10,id=30)之后的这个间隙并没有在加锁范围里,因此insert语句插入c=12是可以执行成功的。

这个例子对我们实践的指导意义就是,在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数,让操作更安全,还可以减小加锁的范围。

案例八:一个死锁的例子

前面的例子中,我们在分析的时候,是按照next-key lock的逻辑来分析的,因为这样分析比较方便。最后我们再看一个案例,目的是说明:next-key lock实际上是间隙锁和行锁加起来的结果。

现在,我们按时间顺序来分析一下为什么是这样的结果。

  1. session A 启动事务后执行查询语句加lock in share mode,在索引c上加了next-key lock(5,10] 和间隙锁(10,15);

  2. session B 的update语句也要在索引c上加next-key lock(5,10] ,进入锁等待;

  3. 然后session A要再插入(8,8,8)这一行,被session B的间隙锁锁住。由于出现了死锁,InnoDB让session B回滚。

你可能会问,session B的next-key lock不是还没申请成功吗?

其实是这样的,session B的“加next-key lock(5,10] ”操作,实际上分成了两步,先是加(5,10)的间隙锁,加锁成功;然后加c=10的行锁,这时候才被锁住的。

也就是说,我们在分析加锁规则的时候可以用next-key lock来分析。但是要知道,具体执行的时候,是要分成间隙锁和行锁两段来执行的。

读提交隔离级别下还有一个优化,即:语句执行过程中加上的行锁,在语句执行完成后,就要把“不满足条件的行”上的行锁直接释放了,不需要等到事务提交。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值