阿里数据库性能诊断的利器——SQL执行干预-阿里云开发者社区

概述

在业务数据库性能问题诊断中，如果发现一个业务性能很差跟某个SQL有关，应用连接池几乎被该SQL占满，同时数据库服务器上也不堪重负。此时情况很紧急，业务改SQL重发布已经来不及了，运维能选择的操作不多。如批量重启应用、数据库切换或者重启。此时业务中断一下，很可能很快压力又上来，问题依然在那个SQL。前篇文章阿里数据库性能诊断的利器——SQL全量日志分享了如何定位问题SQL，本文就分享阿里数据库们如何处理这个问题SQL。

ORACLE在SQL性能诊断方面的功能非常丰富，一直被其他数据库模仿。其中Outline和SQL Profile功能就非常有用。当业务SQL在生产环境执行计划走错后(如表连接顺序或算法不对，或者索引选择的不对等等)，此时可以在该问题SQL上使用hint先生成一个正确的执行计划，然后用Outlines存储这个执行计划，然后再跟执行计划缓存里在用的执行计划进行交换，从而让业务SQL回到正确的执行计划上，性能问题也就迎刃而解了。虽然这个过程有点复杂，也有很多注意事项，但终究是一个不错的选择。ORACLE在10g后将Outlines功能进一步改进推出SQL Profiles功能,更容易生成、更改和控制SQL执行计划。详情参考后面文章。

MySQL数据库在SQL性能优化方面能力一般，数据库性能又严重依赖主机IO和CPU能力。面对这个问题社区版的MySQL数据库只有选择切换或重启。

在互联网业务中，数据库请求数QPS非常高，当SQL有性能问题时，很快就会将数据库的某个资源(CPU或IO)耗尽，进而拖慢其他正常的业务SQL。而应用服务器集群里每个APP的连接池也会相应被耗尽，从而可能出现应用相继挂掉，引起雪崩。阿里数据库AliSQL和OceanBase针对这个场景都有一个SQL干预手段，即SQL执行计划修改或者限流。

阿里数据库内核的SQL执行干预功能

AliSQL的SQL执行计划干预和SQL限流

AliSQL是阿里巴巴数据库内核团队曾经维护的一个开源MySQL的分支，针对MySQL内核做了很多加强和优化。其中一个独特的功能就是SQL执行计划干预和SQL限流。

AliSQL的SQL执行计划干预也是利用hint先生成正确的执行计划，然后再替换掉实际运行中的执行计划。能修改的也只是索引。并且其替换并不像ORACLE那样严格的使用SQL ID去替换，而是可以根据SQL特征去匹配替换。这个功能的关键字是sql_hints。其原理是在语法解析后，sql优化前，根据设定的规则，对语法解析树进行修改。模拟在语法解析中，解析到了index hint。

功能：可在Server端设定指定规则，为指定sql动态添加索引hint，以干预其执行计划。
语法：

  Set sql_hints=’+,<schema_name>,<table_name>,<index_name>,<keyword1>~<keyword2>~<keywordn>’;
Example: 
      Set sql_hints=’+,test,t1,idx_id1,select id from~orderid=~status=’;

这个功能也有些限制就是只能针对单表select限制，特征指定(keyword)不能太简单，db名称不能模糊匹配。在电商MySQL场景完全够用了。

示例设置规则：

root@(none) 01:03:17>set global sql_hints='+,xxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` =';

示例查看规则,可以看到规则命中多少次，失败多少次。

root@(none) 01:03:14>show sql_hints; 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rule_id | db                    | table                    | index     | key_num | hits    | errors | cmd_str                                                                                                                                                                              | 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
|       6 | xxxxxx_xxxxxx_0000 | xxxxxx_template_refer_ | idx_refer |       5 |  452076 |      0 | xxxxxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` =                           | 
|       2 | xxxxxx_xxxxxx_0000 | xxxxxx_template_refer_ | idx_refer |       3 | 2181691 |      0 | xxxxxx_xxxxxx_0000,xxxxxx_template_refer_,idx_refer,`xxxxxx_template_refer`.`refer_user_id` =~`xxxxxx_template_refer`.`user_id` =~`xxxxxx_template_refer`.`template_id` = | 
+---------+-----------------------+--------------------------+-----------+---------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
2 rows in set (0.00 sec)

当SQL性能问题不是执行计划走偏导致的时候，上面方法就没有用。这类往往是新发业务，业务上线前没有严格测试性能。此时需要业务改SQL，但是应用修改发布时间没那么快。所以AliSQL还有个功能就是针对SQL限流功能，即限制SQL并发数。

设置sql限流时可以针对select/update/delete 设置限流命令：

set global sql_select_filter='+,并发数,sql特征1~sql特征2';
set global sql_update_filter='+,并发数,sql特征1~sql特征2';
set global sql_delete_filter='+,并发数,sql特征1~sql特征2';

查看当前的sql限流设置命令：

show sql_filters;

举例，限制SQL并发数为4：

root@(none) 01:23:15>set global sql_select_filter='+,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` ='; 
Query OK, 0 rows affected (0.00 sec) 
root@(none) 01:24:34>show sql_filters; 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| type   | item_id | cur_conc | max_conc | key_num | key_str                                                                                               | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| SELECT |       1 |        0 |        4 |       5 | +,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` = | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

备注：

其中的cur_conc 列显示当前读并发数。如果一直为0，表示规则没有命中问题sql。
sql特征不能太泛（误命中其他sql），也不能太具体（漏掉部分sql）。
限制的并发数（自然数）不能太高（超过16意义不大），也不能太低（太低容易限制过死，导致该sql的qps严重低于正常值，很容易引起业务访问量下跌），也要恰到好处。
如果特征中包含中文（不推荐），务必确保中文能正确的被mysql接收。os的session变量export.UTF-8 ,同时把终端字符集设置为 UTF-8。详情参见数据库字符乱码问题分析。

该限流设置还可以撤销。首先查看sql限流规则的 item_id 值，然后通过相同的设置命令取消该项规则。
命令：

set global sql_select_filter='-,规则id';
set global sql_update_filter='-,规则id';
set global sql_delete_filter='-,规则id';

举例：

root@(none) 01:24:34>show sql_filters; 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| type   | item_id | cur_conc | max_conc | key_num | key_str                                                                                               | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
| SELECT |       1 |        0 |        4 |       5 | +,4,select~from~where~`xxxxxx_template_refer`.`template_id` =~`xxxxxx_template_refer`.`user_id` = | 
+--------+---------+----------+----------+---------+-------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
root@(none) 01:24:41>set global sql_select_filter='-,1'; 
Query OK, 0 rows affected (0.00 sec) 
root@(none) 01:24:49>show sql_filters; 
Empty set (0.00 sec)

AliSQL的这个SQL执行计划修改和限流功能，在开源的AliSQL(地址：https://github.com/alibaba/AliSQL)里已经包含了。

OceanBase的SQL Outlines功能

OceanBase是阿里巴巴和蚂蚁金服完全自主研发的通用的分布式关系型数据库，其在SQL执行和性能诊断方面的逻辑大量参考了ORACLE的设计思路。OceanBase也支持SQL Outline功能，能够修改在线运行的SQL执行计划。同时也支持SQL限流功能。

Outline的用法也是通过SQL Hint固定SQL的执行计划，可以调整表连接算法、使用的索引等等。
创建大纲的语法如下：

CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ];

其中stmt为一个带有HINT的DML语句。限流或固定计划，通过stmt中的HINT来区分。
如果期望对含有HINT的语句进行限流和固定计划，则需要TO target_stmt来指明相应的SQL。create outline outline_name on stmt1 to stmt2;的语意是说对stmt2创建outline,让stmt2使用stmt1中的hint。
指定OR REPLACE后，可以对已经存在执行计划或限流规则进行replace。（注：限流规则和执行计划间可以彼此替换）
在使用target_stmt时，严格要求stmt与target_stmt在去掉hint后完全匹配（实现中为去掉hint的signature相同）。若是在创建限流时使用target_stmt，则同时要求fix_param完全匹配。

举例说明：

OceanBase (root@oceanbase)> create outline ol_1 on  select /*+index(t1 c2)*/ * from t1 where c1 =1;
Query OK, 0 rows affected (0.07 sec)

OceanBase (root@oceanbase)> select * from __all_outline\G;
*************************** 1. row ***************************
     gmt_create: 2016-06-08 16:09:39.058537
   gmt_modified: 2016-06-08 16:09:39.058537
      tenant_id: 1
     outline_id: 1099511628777
    database_id: 1099511627777
 schema_version: 1465373379055176
           name: ol_1
      signature: select  * from t1 where c1 =?
outline_content:   /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c2") END_OUTLINE_DATA  */
       sql_text: select /*+index(t1 c2)*/ * from t1 where c1 =1
          owner: root
           used: 0
        version: 60768-local-78cf62842644724e437542cd12c2cc1e76805ee0
     compatible: 1
        enabled: 1
         format: 01 row in set (0.07 sec)

OceanBase (root@oceanbase)> create table t1(c1 int, c2 int, c3 int, key(c2), key(c3,c2));
Query OK, 0 rows affected (0.13 sec)

OceanBase (root@oceanbase)> CREATE OUTLINE ol_1 ON select /*+index(t1 c3)*/ c3,c2 from t1 TO select  c3,c2 from t1;
Query OK, 0 rows affected (0.02 sec)
OceanBase (root@oceanbase)> select * from oceanbase.gv$outline\G
*************************** 1. row ***************************
        tenant_id: 1
      database_id: 1099511627777
       outline_id: 1099511628777
    database_name: oceanbase
     outline_name: ol_1
visible_signature: select  c3,c2 from t1
         sql_text: select /*+index(t1 c3)*/ c3,c2 from t1
   outline_target: select  c3,c2 from t1
      outline_sql: select /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c3") END_OUTLINE_DATA*/ c3,c2 from t1
1 row in set (0.00 sec)

创建完ol_1后，所有*select from t1 where c1 =?语句都会按照/+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c2") END_OUTLINE_DATA /固定的计划执行。其中?可以是任意值。

这里着重介绍下迁移outline过程中会用到的oceanbase.gv$outline视图中的列：

database_name: outline所属的database的名字
outline_name: outline自己的名字
outline_sql: outline_sql是将原始查询和固定计划的hint拼接在一起的字符串。用于还原outline 创建语句，上面这个例子中，就是将select c3,c2 from t1 和固定计划的 /+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "oceanbase.t1"@"SEL$1" "c3") END_OUTLINE_DATA/拼接而成的，
outline_target: outline_target是使用CREATE OUTLINE ol_name ON on_stmt TO to_stmt这种语法创建outline时的to_stmt, 用于支持在带有hint的DML语句上创建outline; 如果使用CREATE OUTLINE ol_name ON on_stmt ；语句创建outline，则outline_target内容为空。

OceanBase Outline更多的时候是用在数据库Failover、分布式数据库集群扩容(增加实例和分库等)等场景中，将正常数据库的SQL执行计划迁移到新的租户中，可以确保SQL性能不会发生意外的变化。

当create outline时，如果指定MAX_CONCURRENT(NUM)，将会对当前SQL进行限流。指定限流规则后会控制一个observer中可以并发执行的限流SQL个数。并发度控制的是限流SQL对应的physical_plan在单一observer可以并发执行的个数；也就是说对于整个集群，并发执行的限流SQL个数大于HINT中指定的并发度。

示例：

OceanBase (root@oceanbase)> create outline ol_1 on select /*+max_concurrent(1)*/ * from t2 where c1 = 1 and c2 = ?;
Query OK, 0 rows affected (0.04 sec)

创建完ol_1后，形如 select * from t2 where c1 = 1 and c2 = ? 的sql在单台observer中可执行的并发度为1；
c2 = ? 表明问号的位置可以被任意的const值代替，例如下面的sql都会被限流：

select  * from t2 where c1 = 1 and c2 = 1;
select  * from t2 where c1 = 1 and c2 = 2;
select  * from t2 where c1 = 1 and c2 = "2";
select  * from t2 where c1 = 1 and c2 = true;

注意：
限流和固定计划的使用方法类似，均是通过指定HINT的形式来实现；限流的HINT为MAX_CONCURRENT(NUM)，其中NUM为并发度。当被限流的SQL达到最大并发个数后，再有新的限流SQL执行时会报，server会返回 SQL reach max concurrent num 错误。
当前限流和固定计划功能均通过create outline语句来实现，为了确保语意的正确性和清晰性，我们规定create outline中限流HINT和其他HINT不能同时存在。同时存在时只执行限流规则，不会固定执行计划。

高级用法：

同一个outline_name可以对应多个具有相同signature的限流规则。
当同一条SQL可以匹配多个限流规则时，会选择并发度最小的进行限流。

OceanBase (root@oceanbase)> create outline ol_1 on select /*+max_concurrent(1)*/ * from t1 where c1 =1 and c2 = 1;
Query OK, 0 rows affected (0.07 sec)

OceanBase (root@oceanbase)> alter outline ol_1 add select /*+max_concurrent(1)*/ * from t1 where c1 =1 and c2 = ?;
Query OK, 0 rows affected (0.09 sec)

OceanBase (root@oceanbase)> alter outline ol_1 add select /*+max_concurrent(10)*/ * from t1 where c1 =? and c2 = 1;
Query OK, 0 rows affected (0.04 sec)

OceanBase支持的Hints

语句级别的hint

FROZEN_VERSION
QUERY_TIMEOUT
READ_CONSISTENCY
LOG_LEVEL
QB_NAME
ACTIVATE_BURIED_POINT
TRACE_LOG
MAX_CONCURRENT

计划相关的hint

FULL
INDEX
LEADING
USE_MERGE
USE_HASH
USE_NL
ORDERED
NO_REWRITE

存储outline的schema信息的系统表

oceanbase.__all_outline
oceanbase.__all_outline_history

固定计划相关虚拟表和试图

展示的均是当前租户的信息：

oceanbase.__tenant_virtual_outline  用于outline迁移使用的虚拟表，同时显示固定计划的信息。

oceanbase.gv$outline 在__tenant_virutal_outline基础上创建的视图。

information_schema.dba_outlines  在__all_table上创建的视图。

限流相关虚拟表和试图

下表展示的均是当前租户的信息：

oceanbase.__tenant_virtual_concurrent_limit_sql： 展示限流信息
oceanbase.gv$concurrent_limit_sql： 在__tenant_virtual_concurrent_limit_sql上创建的视图。

参考

总结

阿里数据库(AliSQL和OceanBase)都支持在运行中干预SQL的执行计划，以及对问题SQL并发进行限流，以快速将数据库和应用从某个问题SQL的影响中恢复出来。

阿里数据库性能诊断的利器——SQL执行干预

概述

阿里数据库内核的SQL执行干预功能

AliSQL的SQL执行计划干预和SQL限流

OceanBase的SQL Outlines功能

OceanBase支持的Hints

参考

总结

推荐关注

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

阿里数据库性能诊断的利器——SQL执行干预

概述

阿里数据库内核的SQL执行干预功能

AliSQL的SQL执行计划干预和SQL限流

OceanBase的SQL Outlines功能

OceanBase支持的Hints

参考

总结

推荐关注

热门文章

最新文章

相关课程

相关电子书

相关实验场景