欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

数据库--sql语句性能

程序员文章站 2022-05-09 23:43:26
...

                    一条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 语句执行的很慢的原因。

这里我声明一下,系统判断是否走索引,扫描行数的预测其实只是原因之一,这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

强制走索引的方式来查询

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语句性能

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

实际上,B+ 树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。但是,如果计算 month() 函数的话,你会看到传入 7 的时候,在树的第一层就不知道该怎么办了。也就是说,对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。需要注意的是,优化器并不是要放弃使用这个索引。

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

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

数据库--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。

数据库--sql语句性能

从图中可知,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; /* 语句。

数据库--sql语句性能

我们一起来看下这个结果:

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

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

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

接下来,我们看下这个 explain 结果表示的执行流程。

数据库--sql语句性能

图中:

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

数据库--sql语句性能

这个语句里 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)

数据库--sql语句性能

这里,我主动把 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;

数据库--sql语句性能

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

表 t 执行下面的 SQL 语句

mysql> select * from t where id=1;

查询结果长时间不返回。

数据库--sql语句性能

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

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

等 MDL 锁

就是使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。

数据库--sql语句性能

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

在第 6 篇文章《全局锁和表锁 :给表加个字段怎么有这么多阻碍?》中,我给你介绍过一种复现方法。但需要说明的是,那个复现过程是基于 MySQL 5.6 版本的。而 MySQL 5.7 版本修改了 MDL 的加锁策略,所以就不能复现这个场景了。

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

数据库--sql语句性能

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 命令断开即可。

数据库--sql语句性能

等 flush

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

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

数据库--sql语句性能

这个状态表示的是,现在有一个线程正要对表 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 语句。

 

现在,我们一起来复现一下这种情况

数据库--sql语句性能

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

图 7 是这个复现步骤的 show processlist 结果。这个例子的排查也很简单,你看到这个 showprocesslist 的结果,肯定就知道应该怎么做了

数据库--sql语句性能

等行锁

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

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

复现步骤和现场如下:

数据库--sql语句性能

数据库--sql语句性能

显然,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

数据库--sql语句性能

可以看到,这个信息很全,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。

数据库--sql语句性能

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

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

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

 

mysql> select * from t where id=1;

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

数据库--sql语句性能

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

如果我把这个 slow log 的截图再往下拉一点,你可以看到下一个语句,select * from t where

id=1 lock in share mode,执行时扫描行数也是 1 行,执行时间是 0.2 毫秒。

数据库--sql语句性能

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

数据库--sql语句性能

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

数据库--sql语句性能

你看到了,session A 先用 start transaction with consistent snapshot 命令启动了一个事

务,之后 session B 才开始执行 update 语句。

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

数据库--sql语句性能

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的目的是方便你看图。