拒绝“爆雷”!GaussDB(for MySQL)新上线了这个功能

摘要:智能把控大数据量查询,防患系统奔溃于未然。

本文分享自华为云社区《拒绝“爆雷”!GaussDB(for MySQL)新上线了这个功能》,作者:GaussDB 数据库。

什么是最大读取行

一直以来,大数据量查询是数据库DBA们调优的重点,DBA们通常十八般武艺轮番上阵以期提升大数据查询的性能:例如分库分表、给表增加索引、设定合理的WHERE查询条件、限定单次查询的条数……

然而,DBA再厉害,应用程序千千万,写代码的程序员万码奔腾,大数据量的查询像地雷,不定什么时候就爆了。比如隐藏在某段代码里的查询,因为一个新手程序员的经验不足,查询代码写得欠佳,没有WHERE子句或缺少索引引发了不必要的多行读取,甚至全表扫描,给服务器带来了过度的压力,导致业务执行缓慢,甚至最后服务器OOM崩溃。

为了避免这种“爆雷”,GaussDB(for MySQL)近期上线了最大读取行特性。优化器产生执行计划后,如果优化器预估的读取行数超过了所设置的最大读取行阈值,则自动中止查询,将雷的导火索切断。

这种机制的优点在于:执行计划阶段就对查询进行了干预,而不是语句开始执行后在执行过程中进行中断。既杜绝了劣质查询对服务器和业务运行造成的风险,又大大节省了时间和资源。

如何设置最大读取行

在GaussDB(for MySQL)中,设置rds_max_row_read,指定查询允许读取的最大行数。GaussDB(for MySQL)收到查询指令,执行查询之前,会对查询要读取的行数进行估计。当估值超过所设置的最大读取行时,将中止查询,即查询没有机会运行,提前规避不必要的资源消耗。

下面是一份测试数据,说明了开启最大读取行前后的差异。

假设表t1有4M大小的行,当开发人员或应用程序尝试运行以下查询时,运行需要7分钟。

mysql> SELECT  *  FROM t1;

WHERE子句的缺失致使需要全表扫描,查询耗时长。对于更大的表,这类查询将需要更多的耗时,使服务器消耗更多资源,查询耗时甚至可能高达数小时。

最大读取行特性的使用,可以节省宝贵的时间和资源。比如假设将最大读取行数指定为1000000:

mysql> set rds_max_row_read =1000000;
Query OK, 0 rows affected (0.00 sec)

修改后,重新运行不含WHERE子句的查询,收到了读取行超限的提示,查询被停止。

   mysql> SELECT  *  FROM t1;
ERROR HY000: Expected number of read rows exceeds the maximum allowed (see @@rds_max_row_read)

通过最大读取行,相当于拥有了一个工具,DBA或者软件工程师根据业务情况可以自如设置和调整限制规则,保证业务正常运行的同时,限制次优查询,避免性能异常。

适用范围

适用于SELECT、CREATE SELECT和INSERT SELECT。

功能开启

默认情况下,该功能是禁用的,只有当rds_max_row_read设置了值时,该功能才会被激活。

为了功能的稳定,避免无心的错误设置对业务造成不必要的影响,rds_max_row_read做了最低值限制,不允许用户设置比最低值更低的值。

实现原理

拒绝“爆雷”!GaussDB(for MySQL)新上线了这个功能

GaussDB(for MySQL)通过遍历每个查询块并聚合各查询块的贡献来整体评估查询的读取行数:也就是对各join对象的读取行数评估后累加。

如果在累加评估过程中的某一刻,估计值超过了所设置的限制,查询将被终止。

对于关联子查询,评估办法为:评估子查询的读取行数,然后乘以查询被执行的次数。

需要特别说明的是,对每个JOIN对象的估计是执行计划预估返回的行数,可能与真实执行返回的行数有偏差。这虽然是一个相对简单的评估模型,但是我们坚信其具有足够的鲁棒性。

对于复杂查询,GaussDB(for MySQL)还通过optimizer trace提供了更多信息以帮助您确定优化器做决策的原因及如何优化查询。

示例

示例1

mysql> EXPLAIN format=tree SELECT * FROM table_1, table_2;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (no condition)  (cost=6.50 rows=54)
    -> Table scan on table_1  (cost=0.19 rows=9)
    -> Hash
        -> Table scan on table_2  (cost=0.85 rows=6)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET rds_max_row_read =20;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM table_1, table_2;
ERROR 1888 (HY000): The expected number of read rows exceeds the allowed maximum (see @@rds_max_row_read)

查询读取的行太多,我们尝试在optimizer trace的帮助下寻找原因:

SET optimizer_trace="enabled=on";
SELECT * from table_1, table_2;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

在optimizer trace中,可以找到:

{
            "Max_row_read": {
              "select#": 1,
              "current_estimate_of_rows": 54,
              "rows_contributed_by_this_query_block": 54
            }
          }

这表示此查询中的唯一查询块,行读取数为54。

执行计划中的这个评估有多准确呢?

执行如下查询查看语句实际被执行的次数:

mysql> show status like "handler_read_rnd_next";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_read_rnd_next      | 17    |
+----------------------------+-------+
1 rows in set (0.00 sec)

handler_read_rnd_next显示实际上的读取是17行,而不是54行。

这个17是怎么来的呢?

这是一个哈希连接:

-遍历整张表时,左表有9行数据+1行额外行。

-右表有6行+1行额外行。

优化器中会预估返回读取行,例如,54。在这个示例中,它并没有很好地猜测到返回的行数,它高估了行读取的数量。在大多数情况下,读取行数的估计不够精确,但可以肯定的是,它是足够稳健的,能达到相应的目的。

示例2

创建例表t1:

mysql> CREATE TABLE t1(a INT);

在表中填充1536行数据后。将rds_max_row_read设置为500,进行以下测试查询:

mysql> SELECT * FROM t1 WHERE a>6;
ERROR HY000: Expected number of read rows exceeds the maximum allowed (see @@rds_max_row_read)

在optimizer trac的帮助下,可以看到优化器估计的读取行数是512行,因此查询被终止。如果在a字段上添加索引(这是一件明智的事情),同一查询的估计读取行数是1,查询检测顺利通过。

这个简单的示例说明:最大读取行能帮助您编写更加优质的查询语句。

结论

最大读取行特性针对读取过多行的查询,识别和过滤出效率低下的查询。用户可以为读取行数设置阈值,超过该阈值则终止查询。为了识别此类查询,GaussDB(for MySQL)在优化器中进行了读取总行数的粗略估计。当查询终止时,可以检查optimizer trace,从中收集线索,以帮助重写更高效的查询。

简而言之,最大读取行为用户提供了一个工具,使他们可以更充分地利用手上的资源。

 

点击关注,第一时间了解华为云新鲜技术~

原文链接:https://www.cnblogs.com/huaweiyun/p/17268336.html

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:拒绝“爆雷”!GaussDB(for MySQL)新上线了这个功能 - Python技术站

(0)
上一篇 2023年4月18日
下一篇 2023年4月18日

相关文章

  • MySQL触发器概念、原理与用法详解

    MySQL触发器概念 MySQL触发器是一个特殊的存储过程。它是一种数据库对象,用于监控一个表上的特定事件(例如INSERT、UPDATE或DELETE),并在该事件发生时执行指定的代码。触发器是一种非常常用的数据库编程工具,用于实现复杂的数据处理逻辑,比如在插入、修改或删除数据时自动进行某些操作。 MySQL触发器原理 MySQL触发器的原理与存储过程类似…

    MySQL 2023年5月18日
    00
  • 总结MySQL建表、查询优化的一些实用小技巧

    总结MySQL建表、查询优化的一些实用小技巧 MySQL建表和查询优化是数据库开发中非常重要的一部分,正确的建表和优化方式可以有效地提升系统性能和稳定性。本文总结了一些实用的小技巧,希望能够对MySQL的开发者有所帮助。 1. 建表技巧 1.1 使用自增主键 在MySQL中,使用自增主键是创建表时建议的最佳实践之一。自增主键是一种非常方便的方式,可以为每一条…

    MySQL 2023年5月19日
    00
  • MySQL事务还没提交,Canal就能读到消息了?

    【问题描述】 开发有天碰到一个很奇怪的问题,他的场景是这样子的:通过Canal来订阅MySQL的binlog, 当捕获到有数据变化时,回到数据库,反查该数据的明细,然后做进一步处理。有一次,他碰到一个诡异的现象: 1. Canal收到消息,有一条主键id=31019319的数据插入 2. 11:19:51.081, 应用程序去反查数据库,11:19:51.0…

    2023年4月8日
    00
  • 最新版MySQL 8.0.22下载安装超详细教程(Windows 64位)

    以下是针对“最新版MySQL 8.0.22下载安装超详细教程(Windows 64位)”的完整攻略: 下载MySQL 8.0.22 访问MySQL官网,从中选择最新的适合你系统(这里选择的是Windows (x86, 64-bit), ZIP Archive)的MySQL 8.0.22版本,点击下载. 安装MySQL 8.0.22 安装MySQL 8.0.2…

    MySQL 2023年5月18日
    00
  • MySQL 快速删除大量数据(千万级别)的几种实践方案详解

    我来为您讲解“MySQL 快速删除大量数据(千万级别)的几种实践方案详解”。 1. 背景 在实际开发过程中,我们不可避免地会遇到删除大量数据的场景。如果缺乏相应的优化措施,删除操作可能会花费大量的时间导致系统瘫痪。本文将介绍MySQL 快速删除大量数据的实现方法。 2. 方案一:分批删除 要想快速删除大量数据,第一个考虑的方案就是分批删除。程序员可以通过编写…

    MySQL 2023年5月19日
    00
  • 水平分库分表排雷帖

    一、背景 提起分库分表,对于大部分服务器开发来说,其实并不是一个新鲜的名词。随着业务的发展,我们表中的数据量会变的越来越大,字段也可能随着业务复杂度的升高而逐渐增多,我们为了解决单表的查询性能问题,一般会进行分表操作。 同时我们业务的用户活跃度也会越来越高,并发量级不断加大,那么可能会达到单个数据库的处理能力上限。此时我们为了解决数据库的处理性能瓶颈,一般会…

    MySQL 2023年5月6日
    00
  • mysql修改sql_mode报错的解决

    下面是关于“mysql修改sql_mode报错的解决”的完整攻略。 问题背景 在MySQL数据库中,我们可以使用set命令来修改sql_mode的值,如下所示: set global sql_mode=’blahblah’; 但是,在修改sql_mode时,可能会遇到如下错误提示: ERROR 1231 (42000): Variable ‘sql_mode…

    MySQL 2023年5月18日
    00
  • MySql 快速插入千万级大数据的方法示例

    MySQL 快速插入千万级大数据的方法有很多,以下是一些常用的方法: 1.使用LOAD DATA方式批量导入数据 LOAD DATA是MySQL提供的一个非常快速的方式,可以一次性导入成千上万条记录。语法如下: LOAD DATA LOCAL INFILE ‘data.txt’ INTO TABLE table_name FIELDS TERMINATED …

    MySQL 2023年5月19日
    00
合作推广
合作推广
分享本页
返回顶部