• 【MySql】6- 实践篇(四)


    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

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

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

    虽然 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;
    
    • 1

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

    1. 数据类型转换的规则是什么?

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

    因此上述sql就相当于:

    mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;
    
    • 1
    1. 为什么有数据类型转换,就需要走全索引扫描?

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

    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');
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

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

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

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

    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 索引能够快速定位到等值的行。但,这里并没有。

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

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

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

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

    其中,$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;
    
    • 1

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

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

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

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

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

    select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
    
    • 1
    1. 比较常见的优化方法是,把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4,这样就没有字符集转换的问题了。
    alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
    
    • 1
    1. 修改 SQL 语句
    mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 
    
    • 1

    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();
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

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

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

    mysql> select * from t where id=1;
    
    • 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;
    
    • 1

    图 5 Waiting for table flush 状态示意图
    这个状态表示的是,现在有一个线程正要对表 t 做 flush 操作。

    MySQL 里面对表做 flush 操作的用法,一般有以下两个:

    flush tables t with read lock;
    
    flush tables with read lock;
    
    • 1
    • 2
    • 3

    这两个 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; 
    
    • 1

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

    查出是谁占着这个写锁。如果用的是 MySQL 5.7 版本,可以通过 sys.innodb_lock_waits 表查到。

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

    在这里插入图片描述
    可以看到,这个信息很全,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;
    
    • 1

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

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

    mysql> select * from t where id=1
    • 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;
    
    • 1
    • 2
    • 3

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


    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);
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

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

    下面的语句序列,是怎么加锁的,加的锁又是什么时候释放的呢?

    begin;
    select * from t where d=5 for update;
    commit;
    
    • 1
    • 2
    • 3

    这个语句会命中 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 语句,返回结果:

    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,都是当前读。而当前读的规则,就是要能读到所有已经提交的记录的最新值

    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;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

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

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

    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 进入互相等待状态,形成死锁。

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

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

  • 相关阅读:
    Android相机-架构3
    java酒店管理系统设计与实现计算机毕业设计MyBatis+系统+LW文档+源码+调试部署
    SpringBoot实现AOP详解
    使用 GPU 进行 Lightmap 烘焙 - 简单 demo
    centos 7 yum install -y nagios
    【EMC专题】案例:非接开启后液晶屏闪烁怎么就不是非接的问题?
    关于quartus 13.1出现的问题的一些总结
    <学习笔记>从零开始自学Python-之-常用库篇(十二)Matplotlib
    intellij plugin(插件)的项目解析及研读
    教你一文解决 js 数字精度丢失问题
  • 原文地址:https://blog.csdn.net/Tiger_shl/article/details/133754657