数据库--sql语句性能
一条SQL语句执行很慢的原因的哪些
1、大多数情况是正常的,只是偶尔会出现很慢的情况。
2、在数据量不变的情况下,这条SQL语句一直以来都执行的很慢。
针对这两种情况,我们来分析下可能是哪些原因导致的。
一 、针对偶尔很慢的情况
一条 SQL 大多数情况正常,偶尔才能出现很慢的情况,针对这种情况,我觉得这条SQL语句的书写本身是没什么问题的,而是其他原因导致的,那会是什么原因呢?
1、数据库在刷新脏页
当我们要往数据库插入一条数据、或者要更新一条数据的时候,我们知道数据库会在内存中把对应字段的数据更新了,但是更新之后,这些更新的字段并不会马上同步持久化到磁盘中去,而是把这些更新的记录写入到 redo log 日记中去,等到空闲的时候,在通过 redo log 里的日记把最新的数据同步到磁盘中去。
不过,redo log 里的容量是有限的,如果数据库一直很忙,更新又很频繁,这个时候 redo log 很快就会被写满了,这个时候就没办法等到空闲的时候再把数据同步到磁盘的,只能暂停其他操作,全身心来把数据同步到磁盘中去的,而这个时候,就会导致我们平时正常的SQL语句突然执行的很慢,所以说,数据库在在同步数据到磁盘的时候,就有可能导致我们的SQL语句执行的很慢了。
2、拿不到锁
我们要执行的这条语句,刚好这条语句涉及到的表,别人在用,并且加锁了,我们拿不到锁,只能慢慢等待别人释放锁了。或者,表没有加锁,但要使用到的某个一行被加锁了,这个时候,我也没办法啊。
如果要判断是否真的在等待锁,我们可以用 show processlist这个命令来查看当前的状态哦,这里我要提醒一下,有些命令最好记录一下,反正,我被问了好几个命令,都不知道怎么写,呵呵。
下来我们来访分析下第二种情况,我觉得第二种情况的分析才是最重要的
二、一直都这么慢的情况
如果在数据量一样大的情况下,这条 SQL 语句每次都执行的这么慢,那就就要好好考虑下你的 SQL 书写了,下面我们来分析下哪些原因会导致我们的 SQL 语句执行的很不理想。
例子
我们先来假设我们有一个表,表里有下面两个字段,分别是主键 id,和两个普通字段 c 和 d。
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
1没用到索引
没有用上索引,我觉得这个原因是很多人都能想到的,例如你要查询这条语句
select * from t where 100 <c and c < 100000;
(1)、字段没有索引
刚好你的 c 字段上没有索引,那么抱歉,只能走全表扫描了,你就体验不会索引带来的乐趣了,所以,这回导致这条查询语句很慢。
(2)、字段有索引,但却没有用索引
好吧,这个时候你给 c 这个字段加上了索引,然后又查询了一条语句
select * from t where c - 1 = 1000;
答是不会,如果我们在字段的左边做了运算,那么很抱歉,在查询的时候,就不会用上索引了,所以呢,大家要注意这种字段上有索引,但由于自己的疏忽,导致系统没有使用索引的情况了。
正确的查询应该如下
select * from t where c = 1000 + 1;
有人可能会说,右边有运算就能用上索引?难道数据库就不会自动帮我们优化一下,自动把 c - 1=1000 自动转换为 c = 1000+1。
不好意思,确实不会帮你,所以,你要注意了。
(3)、函数操作导致没有用上索引
如果我们在查询的时候,对字段进行了函数操作,也是会导致没有用上索引的,例如
select * from t where pow(c,2) = 1000;
这里我只是做一个例子,假设函数 pow 是求 c 的 n 次方,实际上可能并没有 pow(c,2)这个函数。其实这个和上面在左边做运算也是很类似的。
所以呢,一条语句执行都很慢的时候,可能是该语句没有用上索引了,不过具体是啥原因导致没有用上索引的呢,你就要会分析了,我上面列举的三个原因,应该是出现的比较多的吧。
2 选错索引
我们知道,主键索引和非主键索引是有区别的,主键索引存放的值是整行字段的数据,而非主键索引上存放的值不是整行字段的数据,而且存放主键字段的值。
也就是说,我们如果走 c 这个字段的索引的话,最后会查询到对应主键的值,然后,再根据主键的值走主键索引,查询到整行数据返回。
好吧扯了这么多,其实我就是想告诉你,就算你在 c 字段上有索引,系统也并不一定会走 c 这个字段上的索引,而是有可能会直接扫描扫描全表,找出所有符合 100 < c and c < 100000 的数据。
1 原因
其实是这样的,系统在执行这条语句的时候,会进行预测:究竟是走 c 索引扫描的行数少,还是直接扫描全表扫描的行数少呢?显然,扫描行数越少当然越好了,因为扫描行数越少,意味着I/O操作的次数越少。
如果是扫描全表的话,那么扫描的次数就是这个表的总行数了,假设为 n;而如果走索引 c 的话,我们通过索引 c 找到主键之后,还得再通过主键索引来找我们整行的数据,也就是说,需要走两次索引。而且,我们也不知道符合 100 c < and c < 10000 这个条件的数据有多少行,万一这个表是全部数据都符合呢?这个时候意味着,走 c 索引不仅扫描的行数是 n,同时还得每行数据走两次索引。
2所以呢,系统是有可能走全表扫描而不走索引的。那系统是怎么判断呢?
判断来源于系统的预测,也就是说,如果要走 c 字段索引的话,系统会预测走 c 字段索引大概需要扫描多少行。如果预测到要扫描的行数很多,它可能就不走索引而直接扫描全表了。
3 那么问题来了,系统是怎么预测判断的呢?
系统是通过索引的区分度来判断的,一个索引上不同的值越多,意味着出现相同数值的索引越少,意味着索引的区分度越高。我们也把区分度称之为基数,即区分度越高,基数越大。
所以呢,基数越大,意味着符合 100 < c and c < 10000 这个条件的行数越少。
所以呢,一个索引的基数越大,意味着走索引查询越有优势。
4 那么问题来了,怎么知道这个索引的基数呢?
系统当然是不会遍历全部来获得一个索引的基数的,代价太大了,索引系统是通过遍历部分数据,也就是通过采样的方式,来预测索引的基数的。
扯了这么多,重点的来了,居然是采样,那就有可能出现失误的情况,也就是说,c 这个索引的基数实际上是很大的,但是采样的时候,却很不幸,把这个索引的基数预测成很小。例如你采样的那一部分数据刚好基数很小,然后就误以为索引的基数很小。然后就呵呵,系统就不走 c 索引了,直接走全部扫描了。
所以呢,说了这么多,得出结论:由于统计的失误,导致系统没有走索引,而是走了全表扫描,而这,也是导致我们 SQL 语句执行的很慢的原因。
这里我声明一下,系统判断是否走索引,扫描行数的预测其实只是原因之一,这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。
5 强制走索引的方式来查询
select * from t force index(a) where c < 100 and c < 100000;
我们也可以通过
show index from t;
来查询索引的基数和实际是否符合,如果和实际很不符合的话,我们可以重新来统计索引的基数,可以用这条命令
analyze table t;
来重新统计分析。
既然会预测错索引的基数,这也意味着,当我们的查询语句有多个索引的时候,系统有可能也会选错索引哦,这也可能是 SQL 执行的很慢的一个原因。
三、三个示例(索引的情况)
在 MySQL 中,有很多看上去逻辑相同,但性能却差异巨大的 SQL 语句。对这些语句使用不当的话,就会不经意间导致整个数据库的压力变大。
1 条件字段函数操作(函数操作导致没有用上索引)
假设你现在维护了一个交易系统,其中交易记录表 tradelog 包含交易流水号(tradeid)、交
易员 id(operator)、交易时间(t_modified)等字段。为了便于描述,我们先忽略其他字
段。这个表的建表语句如下:
mysql> CREATE TABLE `tradelog` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` time 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 字段上有索引,于是你就很放心地在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。如果你问 DBA 同事为什么会出现这样的情况,他大概会告诉你:如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定。
现在你已经学过了 InnoDB 的索引结构了,可以再追问一句为什么?为什么条件是 wheret_modified='2018-7-1’的时候可以用上索引,而改成 where month(t_modified)=7 的时候就不行了?
下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值。
如果你的 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');
当然,如果你的系统上线时间更早,或者后面又插入了之后年份的数据的话,你就需要再把其他年份补齐。
到这里我给你说明了,由于加了 month() 函数操作,MySQL 无法再使用索引快速定位功能,而只能使用全索引扫描。
不过优化器在个问题上确实有“偷懒”行为,即使是对于不改变有序性的函数,也不会考虑使用索引。比如,对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句,这个加 1操作并不会改变有序性,但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以,需要你在写 SQL 语句的时候,手动改写成 where id = 10000 -1 才可以。
2 隐式类型转换(字段有索引,但却没有用索引)
mysql> select * from tradelog where tradeid=110717;
交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换
那么,现在这里就有两个问题:
1. 数据类型转换的规则是什么?
2. 为什么有数据类型转换,就需要走全索引扫描?
先来看第一个问题,你可能会说,数据库里面类型这么多,这种数据类型转换规则更多,我记不住,应该怎么办呢?
这里有一个简单的方法,看 select “10” > 9 的结果:
1. 如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是 1;
2. 如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是 0。
从图中可知,select “10” > 9 返回的是 1,所以你就能确认 MySQL 里的转换规则了:在
MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。
这时,你再看这个全表扫描的语句:
mysql> select * from tradelog where tradeid=110717;
就知道对于优化器来说,这个语句相当于:
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。
3 隐式字符编码转换
假设系统里还有另外一个表 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; /* 语句。
我们一起来看下这个结果:
1. 第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行,这个步骤用上了主键索引,rows=1 表示只扫描一行;
2. 第二行 key=NULL,表示没有用上交易详情表 trade_detail 上的 tradeid 索引,进行了全表扫描。
在这个执行计划里,是从 tradelog 表中取 tradeid 字段,再去 trade_detail 表里查询匹配字段。因此,我们把 tradelog 称为驱动表,把 trade_detail 称为被驱动表,把 tradeid 称为关联字段。
接下来,我们看下这个 explain 结果表示的执行流程。
图中:
第 1 步,是根据 id 在 tradelog 表里找到 L2 这一行;
第 2 步,是从 L2 中取出 tradeid 字段的值;
第 3 步,是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里面第
二行的 key=NULL 表示的就是,这个过程是通过遍历主键索引的方式,一个一个地判断
tradeid 的值是否匹配。
进行到这里,你会发现第 3 步不符合我们的预期。因为表 trade_detail 里 tradeid 字段上是有索引的,我们本来是希望通过使用 tradeid 索引能够快速定位到等值的行。但,这里并没有。如果你去问 DBA 同学,他们可能会告诉你,因为这两个表的字符集不同,一个是 utf8,一个是utf8mb4,所以做表连接查询的时候用不上关联字段的索引。这个回答,也是通常你搜索这个问题时会得到的答案。
但是你应该再追问一下,为什么字符集不同就用不上索引呢?
我们说问题是出在执行步骤的第 3 步,如果单独把这一步改成 SQL 语句的话,那就是:
mysql> select * from trade_detail where tradeid=$L2.tradeid.value;
其中,$L2.tradeid.value 的字符集是 utf8mb4。
参照前面的两个例子,你肯定就想到了,字符集 utf8mb4 是 utf8 的超集,所以当这两个类型的字符串在做比较的时候,MySQL 内部的操作是,先把 utf8 字符串转成 utf8mb4 字符集,再做比较。
因此, 在执行上面这个语句的时候,需要将被驱动数据表里的字段一个个地转换成 utf8mb4,再跟 L2 做比较。也就是说,实际上这个语句等同于下面这个写法:
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
CONVERT() 函数,在这里的意思是把输入的字符串转成 utf8mb4 字符集。
这就再次触发了我们上面说到的原则:对索引字段做函数操作,优化器会放弃走树搜索功能。
到这里,你终于明确了,字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。
个设定很好理解,utf8mb4 是 utf8 的超集。类似地,在程序设计语言里面,做自动类型转换的时候,为了避免数据在转换过程中由于截断导致数据错误,也都是“按数据长度增加的方向”进行转换的
作为对比验证,我给你提另外一个需求,“查找 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 索引。
理解了原理以后,就可以用来指导操作了。如果要优化语句的执行过程,有两种做法:
比较常见的优化方法是,把 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)
这里,我主动把 l.tradeid 转成 utf8,就避免了被驱动表上的字符编码转换,从 explain 结果可以看到,这次索引走对了。
四、 我只查一行的语句,也执行这么慢?(锁的问题)
一般情况下,如果我跟你说查询性能优化,你首先会想到一些复杂的语句,想到查询需要返回大量的数据。但有些情况下,“查一行”,也会执行得特别慢。
需要说明的是,如果 MySQL 数据库本身就有很大的压力,导致数据库服务器 CPU 占用率很高或 ioutil(IO 利用率)很高,这种情况下所有语句的执行都有可能变慢,不属于我们今天的讨论范围。
示例
为了便于描述,我还是构造一个表,基于这个表来说明今天的问题。这个表有两个字段 id 和c,并且我在里面插入了 10 万行记录。
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
第一类:查询长时间不返回
表 t 执行下面的 SQL 语句
mysql> select * from t where id=1;
查询结果长时间不返回。
一般碰到这种情况的话,大概率是表 t 被锁住了。接下来分析原因的时候,一般都是首先执行一下 show processlist 命令,看看当前语句处于什么状态。
然后我们再针对每种状态,去分析它们产生的原因、如何复现,以及如何处理。
等 MDL 锁
就是使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。
出现这个状态表示的是,现在有一个线程正在表 t 上请求或者持有 MDL 写锁,把 select 语句堵住了。
在第 6 篇文章《全局锁和表锁 :给表加个字段怎么有这么多阻碍?》中,我给你介绍过一种复现方法。但需要说明的是,那个复现过程是基于 MySQL 5.6 版本的。而 MySQL 5.7 版本修改了 MDL 的加锁策略,所以就不能复现这个场景了。
不过,在 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 系统库以后,就方便多了。
通过查询 sys.schema_table_lock_waits 这张表,我们就可以直接找出造成阻塞的 processid,把这个连接用 kill 命令断开即可。
等 flush
我在表 t 上,执行下面的 SQL 语句:
mysql> select * from information_schema.processlist where id=6;
这个状态表示的是,现在有一个线程正要对表 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 语句。
现在,我们一起来复现一下这种情况
在 session A 中,我故意每行都调用一次 sleep(1),这样这个语句默认要执行 10 万秒,在这期间表 t 一直是被 session A“打开”着。然后,session B 的 flush tables t 命令再要去关闭表t,就需要等 session A 的查询结束。这样,session C 要再次查询的话,就会被 flush 命令堵住了。
图 7 是这个复现步骤的 show processlist 结果。这个例子的排查也很简单,你看到这个 showprocesslist 的结果,肯定就知道应该怎么做了
等行锁
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 上的行锁。
第二类:查询慢
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 万行记录,数据量大起来的话,执行时间就线性涨上去了。
扫描行数多,所以执行慢,这个很好理解。
但是接下来,我们再看一个只扫描一行,但是执行很慢的语句。
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 还要加锁,时间应该更长才对啊。
第一个语句的查询结果里 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 这个结果返回。
注意,undo log 里记录的其实是“把 2 改成 1”,“把 3 改成 2”这样的操作逻辑,画成减 1的目的是方便你看图。