【MySql】6- 实践篇(四)-SQL语句性能分析/SQL查询时间长/幻读

Tiger_shl

已于 2023-11-28 00:13:45 修改

阅读量338

点赞数

分类专栏： MySql 文章标签： mysql 数据库

于 2023-10-11 00:19:41 首次发布

本文链接：https://blog.csdn.net/tiger_shl/article/details/133754657

版权

MySql 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

文章目录

1. 为何SQL语句逻辑相同，性能却差异巨大

1.1 性能差异大的SQL语句问题

1.1.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` 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 字段上有索引，但在生产库中执行了这条语句，但却发现执行了特别久，才返回了结果。因为**MySQL 规定，如果对字段做了函数计算，就用不上索引 **

但是为何where t_modified='2018-7-1’的时候可以用上索引，而改成 where month(t_modified)=7 的时候就不行了？

下面是这个 t_modified 索引的示意图。方框上面的数字就是 month() 函数对应的值
图 1 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 这个索引；这条语句扫描了整个索引的所有值；Extra 字段的 Using index，表示的是使用了覆盖索引。

由于加了 month() 函数操作，MySQL 无法再使用索引快速定位功能，而只能使用全索引扫描。

优化器"偷懒"行为

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

1.1.2 案例二:隐式类型转换

mysql> select * from tradelog where tradeid=110717;

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

数据类型转换的规则是什么？

MySQL 里的转换规则：在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

因此上述sql就相当于:

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

为什么有数据类型转换，就需要走全索引扫描？

对索引字段做函数操作，优化器会放弃走树搜索功能。

1.1.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; /*语句Q1*/

在这里插入图片描述
结果：

第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行，这个步骤用上了主键索引，rows=1 表示只扫描一行；
第二行 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 索引能够快速定位到等值的行。但，这里并没有。

原因是因为:这两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。

所以第3步执行相当于以下语句:

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

其中，$L2.tradeid.value 的字符集是 utf8mb4。
CONVERT() 函数，在这里的意思是把输入的字符串转成 utf8mb4 字符集。

对索引字段做函数操作，优化器会放弃走树搜索功能。

对比验证
执行一下语句:

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。

此时第3步执行相当于以下语句:

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

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

SQL优化思路
如果要优化语句:

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;

修改 SQL 语句

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

2. 为何只查询一行的SQL执行很慢

构造一个表，基于这个表来说明今天的问题。这个表有两个字段 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();

2.1 场景一:查询长时间不返回

在表 t 执行下面的 SQL 语句：

mysql> select * from t where id=1;

查询结果长时间不返回。一般碰到这种情况的话，大概率是表 t 被锁住了。

分析原因的时候，一般都是首先执行一下show processlist 命令，看看当前语句处于什么状态。

2.1.1 等MDL锁

使用 show processlist 命令查看 Waiting for table metadata lock 的示意图
在这里插入图片描述
出现这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。

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

通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可（MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)
图 4 查获加表锁的线程 id

2.1.2 等 flush

这是另外一种查询被堵住的情况。
在表 t 上，执行下面的 SQL 语句：

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

图 5 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 语句。

2.1.2 等行锁

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

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

查出是谁占着这个写锁。如果用的是 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 上的行锁。

2.1 场景二:查询慢

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

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

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

mysql> select * from t where id=1；

在这里插入图片描述
通过slow log看到，虽然扫描行数是 1，但执行时间却长达 800 毫秒，这些时间都花在哪里了？

再执行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 这一行状态如下：

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

思考
加锁读的时候，用的是这个语句，select * from t where id=1 lock in share mode。由于 id 上有索引，所以可以直接定位到 id=1 这一行，因此读锁也是只加在了这一行上。

如果是下面的 SQL 语句，

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

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

3. 幻读

建表和初始化语句如下：

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 行记录上，会不会被加锁呢？

3.1 幻读是什么

如果只在 id=5 这一行加锁，而其他行的不加锁的话，假设以下场景:
在这里插入图片描述
session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。
它们的 SQL 语句相同，都是 select * from t where d=5 for update。查所有 d=5 的行，而且使用的是当前读，并且加上写锁。

这三条 SQL 语句，返回结果:

Q1 只返回 id=5 这一行；
在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

“幻读”说明：

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

这三个查询都是加了 for update，都是当前读。而当前读的规则，就是要能读到所有已经提交的记录的最新值

3.2 幻读带来的问题

首先是语义上的，语义被破坏了。破坏了加锁的申明
数据一致性的问题。锁的设计是为了保证数据的一致性。这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性

即使把所有的记录都加上锁，还是阻止不了新插入的记录

3.2 如何解决幻读

为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

间隙锁，锁的就是两个值之间的空隙。开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。
在这里插入图片描述
执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

间隙锁跟之前碰到过的锁都不太一样。
比如行锁，分成读锁和写锁。
图 6 两种行锁间的冲突关系
跟行锁有冲突关系的是“另外一个行锁”。

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

间隙锁和行锁合称 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, +supremum]。

间隙锁和 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;

这个逻辑一旦有并发，就会碰到死锁。

用两个 session 来模拟并发，并假设 N=9。
在这里插入图片描述
按语句执行顺序来分析一下：

session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

至此，两个 session 进入互相等待状态，形成死锁。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。 间隙锁是在可重复读隔离级别下才会生效的。如果把隔离级别设置为读提交的话，就没有间隙锁了。

来自林晓斌《MySql实战45讲》