内部表查询 SQL 优化

部分 OceanBase 内部表在定义时，已创建 primary key、索引、partition 等，在 SQL 查询该类表时，合理利用这些信息可以优化其执行计划、提升查询效率。

因此，当有关该类表的 SQL 查询耗时异常时，可以利用这些索引、分区信息来优化 SQL，从而提升查询效率。有关该类内部表的索引、分区创建信息，可以使用 show create table 查看。

适用版本

OceanBase 数据库 V2.x、V3.x、V4.x 版本。

适用的租户

适用于 OceanBase 数据库的 SYS 租户、MySQL 租户和 Oracle 租户。

通过 gv$sql_audit 查询 SQL 优化

1. 通过 gv$ob_sql_audit 查询时，执行时间过长。

   obclient > select plan_id,elapsed_time,query_sql from gv$ob_sql_audit where QUERY_SQL like 'select * from t1%';
   

   返回结果如下：

   +---------+--------------+------------------+
   | plan_id | elapsed_time | query_sql        |
   +---------+--------------+------------------+
   |   37751 |        36328 | select * from t1 |
   +---------+--------------+------------------+
   1 row in set (2.212 sec)
   

2. 查看 SQL 语句的执行计划。

   obclient > explain select plan_id,elapsed_time,query_sql from gv$ob_sql_audit where QUERY_SQL like 'select * from t1%';
   

   返回结果如下：

   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                           |
   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ==================================================================                                                                                                                   |
   | |ID|OPERATOR       |NAME                   |EST.ROWS|EST.TIME(us)|                                                                                                                   |
   | ------------------------------------------------------------------                                                                                                                   |
   | |0 |TABLE FULL SCAN|__all_virtual_sql_audit|100000  |6150        |                                                                                                                   |
   | ==================================================================                                                                                                                   |
   | Outputs & filters:                                                                                                                                                                   |
   | -------------------------------------                                                                                                                                                |
   |   0 - output([__all_virtual_sql_audit.plan_id], [__all_virtual_sql_audit.elapsed_time], [__all_virtual_sql_audit.query_sql]), filter([(T_OP_LIKE, __all_virtual_sql_audit.query_sql, |
   |        'select * from t1%', '\\')])                                                                                                                                                  |
   |       access([__all_virtual_sql_audit.query_sql], [__all_virtual_sql_audit.plan_id], [__all_virtual_sql_audit.elapsed_time]), partitions(p1)                                         |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                                    |
   |       range_key([__all_virtual_sql_audit.svr_ip], [__all_virtual_sql_audit.svr_port], [__all_virtual_sql_audit.tenant_id], [__all_virtual_sql_audit.request_id]),                    |
   |        range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true                                                                                                                           |
   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   

3. 通过 show create table 查询 gv$ob_sql_audit 相应内部表 DDL。

   

   查看内部表的建表语句，可以发现，该内部表已创建 primary key、索引等。

4. 优化 gv$ob_sql_audit 查询。

   • 利用索引优化。

     obclient > select plan_id,elapsed_time,query_sql from gv$ob_sql_audit where tenant_id = 1 and request_id=72421751 and QUERY_SQL like 'select * from t1%';
     

     返回结果如下：

     +---------+--------------+------------------+
     | plan_id | elapsed_time | query_sql        |
     +---------+--------------+------------------+
     |   37751 |        36328 | select * from t1 |
     +---------+--------------+------------------+
     1 row in set (0.037 sec)
     

   • 利用 primary key 优化。

     obclient > select plan_id,elapsed_time,query_sql from gv$ob_sql_audit where svr_ip = 'xxx.xxx.xxx.xxx' and svr_port = 2882 and tenant_id = 1 and request_id=72421751 and QUERY_SQL like 'select * from t1%';
     

     返回结果如下：

     +---------+--------------+------------------+
     | plan_id | elapsed_time | query_sql        |
     +---------+--------------+------------------+
     |   37751 |        36328 | select * from t1 |
     +---------+--------------+------------------+
     1 row in set (0.050 sec)
     

     利用索引、primary key 优化，可以发现，查询效率明显提升。