mysql流式查询下的性能隐患 | 黑微狗的技术博客

URL

date

AI summary

slug

status

什么是流式查询

MySQL JDBC从MySQL Server查询数据的时候有三种方式：

普通查询

游标查询

流式查询

普通查询

也就是不做任何特殊配置的普通查询，客户端会把mysql服务端传输过来的数据全部读取完毕再返回给上层调用(statement.executeQuery(sql))。这种方式在大数据量的查询下可能会产生内存溢出问题。

游标查询

这种查询相当于是一个驱动级别的分页，需要在连接参数里配置开启游标（useCursorFetch=true）。并且在查询时需要设置fetchSize来指定从mysql每批次获取的记录数量。这样它就不会阻塞到所有结果都被读取完才返回，只要读取到fetchSize的数据量或者读完就返回给上层调用（statement.executeQuery(sql)）。整体上它分为两个阶段：

数据准备阶段

数据传输阶段

由于MySQL不知道客户端什么时候将数据消费完，而自身的对应表可能会有DML写入操作，此时MySQL需要建立一个临时空间来存放需要拿走的数据，这也就是数据准备阶段要做的事情。因此对于当你启用useCursorFetch读取大表的时候会看到MySQL上的几个现象：

IOPS飙升，因为存在大量的IO读取和写入，这个动作是正在准备要返回的数据到临时空间中，此时监控MySQL的网络输出是没有变化的。由于IO写入很大，如果是普通硬盘，此时可能会引起业务写入的抖动

磁盘空间飙升，这块临时空间可能比原表更大，如果这个表在整个库内部占用相当大的比重有可能会导致数据库磁盘写满，空间会在结果集读取完成后或者客户端发起Result.close()时由MySQL去回收。

CPU和内存会有一定比例的上升，根据CPU的能力决定。

客户端JDBC发起SQL后，长时间等待SQL响应数据，这段时间就是服务端在准备数据，这个等待与原始的JDBC不设置任何参数的方式也表现出等待，在内部原理上是不一样的，前者是一直在读取网络缓冲区的数据，没有响应给业务，现在是MySQL数据库在准备临时数据空间，没有响应给JDBC。

在数据准备完成后，开始传输数据的阶段，网络响应开始飙升，IOPS由“读写”转变为“读取”。

不过这里我有一个疑问，为什么要用临时表，不能用mvcc机制吗？另外，每一个查询都建立一个临时表，是不是同时来几个查询，IOPS直接就满了，然后磁盘直接就干爆了？

流式查询

流式查询利用了tcp通信协议里的滑动窗口原理，数据通过tcp连接从mysql发送到client，client实时消费，当client消费速度跟不上的时候，滑动窗口的size会降为0，此时server无法发送数据，只能阻塞，等到client消费速度跟上，就又可以发送数据了。

流式查询有一点需要注意：必须先读取（或关闭）结果集中的所有行，然后才能对连接发出任何其他查询，否则将引发异常，其查询会独占连接。我有一篇文章就碰到了类似的问题，有兴趣的同学可以去看看

mysql流式查询中的一个坑 | 黑微狗的技术博客

这是一个由NotionNext生成的站点

https://blog.hwgzhu.com/article/a-trap-of-mysql-streaming-query

Metadata Lock

不是说流式查询是解决大数据量查询避免OOM的利器么？它会存在什么性能隐患呢？我们先来了解一下medadata lock。

在MySQL5.5.3之前，有一个著名的bug#989，大致如下:


session1:
BEGIN;
INSERT INTO t ... ;
COMMIT;

session2:
DROP TABLE t;

然而上面的操作流程在binlog记录的顺序是


DROP TABLE t;
BEGIN;
INSERT INTO t ... ;
COMMIT;

很显然备库执行binlog时会先删除表t，然后执行insert 会报1032 error，导致复制中断。为了解决该bug，MySQL 在5.5.3引入了MDL锁（medadata lock），来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。

再举一个简单的例子，如果你在查询一个表的过程中，另外一个session对该表删除了一个列，那前面的查询到底该显示什么呢？如果在RR隔离级别下，事务中再次执行相同的语句还会和之前结果一致吗？为了防止这种情况，表查询开始MySQL会在表上加一个锁，来防止被别的session修改了表定义，这个锁就叫‘metadata lock’，简称MDL，翻译成中文也叫‘元数据锁’。

简单来说，也就是你无法在一个查询中或者事务中修改表结构，因为表结构没有版本快照一说，只存在一份metadata，你改了，那么就可能会影响到进行中的查询或者事务。下面这篇文章有提到让DDL支持MVCC

PolarDB 数据库内核月报

数据库内核月报, 来着阿里云 PolarDB 数据库内核团队。

http://mysql.taobao.org/monthly/2023/05/02/

Medadata Lock为什么会造成系统崩溃

因为如果DDL执行的时候刚好有长查询或者是长事务在运行，导致DDL等待medadata lock，会导致该表后续的读写全部被阻塞。阻塞所有读写请求，不用我多说，你该知道有多么恐怖了吧。收拾细软吧朋友。

比如下面的例子，session1启动一个事务，对表t1执行一个简单的查询；session2对t1加一个字段；session3来对t1做一个查询；session4来对t1做一个update，各个session串行操作。


session1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=1;
+----+------+------+-------+
| id | name | age | birth |
+----+------+------+-------+
| 1 | aa | 10 | NULL |
+----+------+------+-------+
1 row in set (0.00 sec)

session2：
mysql> alter table t1 add col1 int;

阻塞中...

session3:
mysql> select * from t1 limit 1;

阻塞中...

session4:
mysql> update t1 set name='aaaa' where id=2;

阻塞中

也就是由于session1的一个事务没有提交，导致session2的ddl操作被阻塞，session3和session4本身不会被session1阻塞，但由于在锁队列中，session2排队更早，它准备加的是metadata lock写锁，阻塞了session3和session4的读锁。如果t1是一个执行频繁的表，show processlist会发现大量‘waiting for table metadata lock’的线程，数据库连接很快就会消耗完，导致业务系统无法正常响应。DML被阻塞，这个对业务的影响无疑是毁灭性的。

此时如果session1提交，是session2的alter语句先执行还是session3和session4先执行呢？之前一直以为先到的先执行，当然是session2先执行，但经过测试，在5.7中，session3和session4先执行，session2最后执行，也就会出现alter长时间无法执行的情况；而在8.0中，session2先执行，session3和session4后执行，由于5.6以后ddl是online的，session2并不会阻塞session3和session4，感觉这样才是合理的，alter不会被‘饿死’。

看到这里，你会发现文章的标题不是很严谨，因为不仅是流式查询，其实只要涉及到“长查询”或者是“长事务”，都会受到MDL锁的性能影响。只不过对于流式查询来说，叠加了客户端的消费逻辑，会让整个过程的时间拉得更长，更不可控。

解决方案

设置获取MDL锁的超时时间

对于DDL操作，是不是可以设置获取MDL锁的超时时间，一旦超时，那么就直接终止本次DDL操作，不要影响后续的DML。这个貌似只有在阿里自研的mysql分支上支持类似的语法，开源版本的mysql里暂未支持。

mysql里有配置获取MDL锁的超时时间的参数——lock_wait_timeout，可以在执行DDL前，在对应的session里配置，这样也可以一定程度上缓解这个问题。

普通DDL执行改表时，也设置 lock_wait_timeout，以免meta lock 影响DML

Updated May 28, 2020

无锁变更DDL

这种方式相当于不在原表上做DDL变更，而是创建一张新表，变更表结构，并实时同步老表数据，等到新老表数据一致时再原子的交换2张表名。这种方式相当于是牺牲了性能来换稳定性，但是mysql内核一直在优化，比如增加列已经是instant级别的了。再用无锁变更就有点。。。

另外，无锁变更这种方式对于canal这种同步工具来说也可能会存在一定的问题

几种无锁结构变更方案的对比_数据管理 DMS-阿里云帮助中心

锁表变更对业务甚至是致命的，DDL无锁变更通过引入非触发器的方式可以解决大表无锁变更这个难题。本文介绍几种无锁结构变更方案的对比。

https://help.aliyun.com/zh/dms/user-guide/comparison-of-lock-free-schema-change-solutions

快速找到长时间持有MDL锁的语句


SELECT
    locked_schema,
    locked_table,
    locked_type,
    waiting_processlist_id,
    waiting_age,
    waiting_query,
    waiting_state,
    blocking_processlist_id,
    blocking_age,
    substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
    sql_kill_blocking_connection
FROM
    (
        SELECT
            b.OWNER_THREAD_ID AS granted_thread_id,
            a.OBJECT_SCHEMA AS locked_schema,
            a.OBJECT_NAME AS locked_table,
            "Metadata Lock" AS locked_type,
            c.PROCESSLIST_ID AS waiting_processlist_id,
            c.PROCESSLIST_TIME AS waiting_age,
            c.PROCESSLIST_INFO AS waiting_query,
            c.PROCESSLIST_STATE AS waiting_state,
            d.PROCESSLIST_ID AS blocking_processlist_id,
            d.PROCESSLIST_TIME AS blocking_age,
            d.PROCESSLIST_INFO AS blocking_query,
            concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
        FROM
            performance_schema.metadata_locks a
        JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
        AND a.OBJECT_NAME = b.OBJECT_NAME
        AND a.lock_status = 'PENDING'
        AND b.lock_status = 'GRANTED'
        AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
        AND a.lock_type = 'EXCLUSIVE'
        JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
        JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
    ) t1,
    (
        SELECT
            thread_id,
            group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin'
                                                         THEN "transaction_begin"
                                                         ELSE sql_text
                                                         END
                                                         ORDER BY event_id SEPARATOR ";" ) AS sql_text
        FROM
           performance_schema.events_statements_history
        GROUP BY thread_id
    ) t2
WHERE
      t1.granted_thread_id = t2.thread_id;

参考

https://blog.csdn.net/xieyuooo/article/details/83109971

https://developer.jdcloud.com/article/1526

http://mysql.taobao.org/monthly/2022/10/01/

http://mysql.taobao.org/monthly/2023/05/02/