ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 国内数据库 > Oceanbase SQL 优化实战

Oceanbase SQL 优化实战

国内数据库 作者：hunterjoy 时间：2024-11-02 18:43:47 0 删除 编辑

1、 定位 top SQL

查找某段时间范围内 top SQL

SELECT

  /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000), PARALLEL(4)*/

  sql_id,

  COUNT(1),

  AVG(elapsed_time),

  AVG(execute_time),

  AVG(total_wait_time_micro),

  AVG(return_rows),

  AVG(affected_rows),

  query_sql,

  ret_code

FROM

  v$OB_SQL_AUDIT

WHERE

  tenant_id = 1002

  AND svr_ip IN ('127.0.0.1')

  AND (

    CAST(USEC_TO_TIME(request_time) as datetime) BETWEEN CAST('2023-09-03 14:00:46' AS DATETIME)

    AND CAST('2023-09-04 14:01:46' AS DATETIME)

  )

GROUP BY

  sql_id

ORDER BY

  AVG(elapsed_time) DESC

LIMIT

  10\G


查找最近 1 分钟的 top SQL

可以使用如下 SQL，查询最近 1 分钟的 SQL_audit 信息。

SELECT

  /*+ READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000), PARALLEL(4)*/

  sql_id,

  AVG(elapsed_time),

  AVG(execute_time),

  AVG(total_wait_time_micro),

  AVG(return_rows),

  AVG(affected_rows),

  substr(query_sql, 1, 50) query_sql,

  ret_code

FROM

  v$OB_SQL_AUDIT

WHERE TIME_TO_USEC(NOW(6)) - request_time < 60000000 

GROUP BY sql_id, query_sql, ret_code

ORDER BY 3 DESC

LIMIT 10;

从返回结果看，第一条返回结果中的 query_sql 平均执行时间最长，即 sql_id 为 9F0760673D021682CDA40080D598E5A4 的 SQL，平均执行时间为 32550 微秒

+----------------------------------+-------------------+-------------------+----------------------------+------------------+--------------------+----------------------------------------------------+----------+

| sql_id                           | AVG(elapsed_time) | AVG(execute_time) | AVG(total_wait_time_micro) | AVG(return_rows) | AVG(affected_rows) | query_sql                                          | ret_code |

+----------------------------------+-------------------+-------------------+----------------------------+------------------+--------------------+----------------------------------------------------+----------+

| DB9FE488781AF5A3DE810062B8A168AB |        32550.0000 |        32337.7500 |                  1160.5000 |           3.0000 |             0.0000 | select /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000)  |        0 |

| 98F8E1830AFEA651EFE0E2A3A969973B |        31707.0000 |        29293.0000 |                   326.0000 |           1.0000 |             0.0000 | select /*+ MONITOR_AGENT QUERY_TIMEOUT(100000000)  |        0 |

| 12E090814B25D8CEEAF8F0B4E3213333 |        34284.0000 |        28670.0000 |                 19715.0000 |           0.0000 |             0.0000 | SELECT /*+ READ_CONSISTENCY(WEAK), QUERY_TIMEOUT   |        0 |

| 0DB6C6690C2CF7D532A67C34A4B99B00 |        23445.2500 |        22620.2500 |                     0.0000 |          18.0000 |             0.0000 | select /* MONITOR_AGENT */ con_id tenant_id, case  |        0 |

| 1DF15473BF500C0D6F79C1C41FDCE4CC |        17670.5000 |        16707.2500 |                     0.0000 |           3.0000 |             0.0000 | select /* MONITOR_AGENT */ con_id tenant_id, sum(t |        0 |

| 8618B27D0ECBC1148471E79BED39012F |        17333.5000 |        16640.0000 |                     0.0000 |         228.0000 |             0.0000 | select /* MONITOR_AGENT */ con_id tenant_id, stat_ |        0 |

| F171B4806D7261AD7C4293472A6741F5 |        10951.6949 |        10931.9153 |                     0.0000 |           1.0000 |             0.0000 | select sync_scn, min_wrs from (select min(greatest |        0 |

| 12E090814B25D8CEEAF8F0B4E3213333 |         9585.6000 |         9585.6000 |                  9442.8000 |           0.0000 |             0.0000 |                                                    |        0 |

| D41C8F7EB77D0CE6A82CA496EE7A5474 |         5414.5000 |         4683.7500 |                     0.0000 |           2.0000 |             0.0000 | select /* MONITOR_AGENT */ case when cnt is null t |        0 |

| B6E0A8CB0ABBE253671FA9FA0449FA27 |         4214.0000 |         3494.7500 |                     0.0000 |           2.0000 |             0.0000 | select coalesce(t1.tenant_id, -1) as tenant_id, te |        0 |

+----------------------------------+-------------------+-------------------+----------------------------+------------------+--------------------+----------------------------------------------------+----------+

10 rows in set

获取 top SQL

可以使用如下所示 SQL 获取 TopSQL 来判断是否存在 SQL 问题。

select SQL_ID, 

avg(ELAPSED_TIME),

avg(QUEUE_TIME), 

avg(ROW_CACHE_HIT + BLOOM_FILTER_CACHE_HIT + BLOCK_CACHE_HIT + DISK_READS) avg_logical_read, 

avg(execute_time) avg_exec_time, 

count(*) cnt, 

avg(execute_time - TOTAL_WAIT_TIME_MICRO ) avg_cpu_time, 

avg( TOTAL_WAIT_TIME_MICRO ) avg_wait_time, 

WAIT_CLASS, 

avg(retry_cnt) 

from v$OB_SQL_AUDIT 

group by 1 

order by avg_exec_time * cnt desc limit 10;

从查询结果看， SQL_ID  为  98F8E1830AFEA651EFE0E2A3A969973B  的 SQL 为 TopSQL 之首，消耗了一定的的租户资源，但是逻辑读不高，执行次数也很低，仅执行一次，可以不考虑优化。判断是否需要优化，要综合看各个资源的消耗，资源消耗最多，且执行次数最多的，一般考虑进行优化。

+----------------------------------+-------------------+-----------------+------------------+---------------+------+--------------+---------------+------------+----------------+

| SQL_ID                           | avg(ELAPSED_TIME) | avg(QUEUE_TIME) | avg_logical_read | avg_exec_time | cnt  | avg_cpu_time | avg_wait_time | WAIT_CLASS | avg(retry_cnt) |

+----------------------------------+-------------------+-----------------+------------------+---------------+------+--------------+---------------+------------+----------------+

| 1D0BA376E273B9D622641124D8C59264 |          264.6326 |          0.0000 |           0.0000 |      247.6813 | 1048 |     247.6813 |        0.0000 | OTHER      |         0.0000 |

| 24D486F20861EBD9E0217692DB0E9C61 |          258.4783 |          0.0000 |           0.0000 |      195.4206 | 1265 |     195.4206 |        0.0000 | OTHER      |         0.0000 |

| DB9FE488781AF5A3DE810062B8A168AB |        27025.0000 |         11.2500 |          58.0000 |    26838.2500 |    4 |   26838.2500 |        0.0000 | OTHER      |         0.0000 |

| 1DF15473BF500C0D6F79C1C41FDCE4CC |        18674.2500 |        887.5000 |           0.0000 |    17716.0000 |    4 |   17716.0000 |        0.0000 | OTHER      |         0.0000 |

| 0DB6C6690C2CF7D532A67C34A4B99B00 |        15994.2500 |         53.7500 |           0.0000 |    15858.7500 |    4 |   15858.7500 |        0.0000 | OTHER      |         0.0000 |

| 8618B27D0ECBC1148471E79BED39012F |        14492.2500 |       1220.0000 |           0.0000 |    13178.5000 |    4 |   13178.5000 |        0.0000 | OTHER      |         0.0000 |

| B6F2A13C4C81145FFAA2F2A5CF9587A9 |         2095.4000 |          0.0000 |           0.0000 |     2017.0500 |   20 |    2017.0500 |        0.0000 | OTHER      |         0.0000 |

| 98F8E1830AFEA651EFE0E2A3A969973B |        39083.0000 |        152.0000 |          48.0000 |    38820.0000 |    1 |   38820.0000 |        0.0000 | OTHER      |         0.0000 |

| 735537F7B5DB7C4E0E946C9B26108560 |          104.7970 |          0.0000 |           0.0000 |       68.4446 |  542 |      68.4446 |        0.0000 | OTHER      |         0.0000 |

| 9050622DE000F09344C9A882DAF34A75 |           69.3207 |          0.0000 |           0.0000 |       38.3427 |  820 |      38.3427 |        0.0000 | OTHER      |         0.0000 |

+----------------------------------+-------------------+-----------------+------------------+---------------+------+--------------+---------------+------------+----------------+

10 rows in set  

获取 SQL 执行计划

通过 explain 命令获取 SQL 的执行计划

MySQL 模式下，可以通过 explain 命令直接获取 SQL 的执行计划。

explain select * from t1,t2 where t1.id=t2.id;

通过视图查询执行计划信息

  1. 查询  GV$OB_SQL_AUDIT 视图获取  trace_id。

SELECT trace_id FROM oceanbase.GV$OB_SQL_AUDIT WHERE query_sql like '%insert into%' ORDER BY REQUEST_TIME DESC LIMIT 1;

+-----------------------------------+

 | trace_id                          |

 +-----------------------------------+

 | YB427F000001-00060407BD1E10C3-0-0 |

 +-----------------------------------+

 1 row in set 


查询  GV$SQL_PLAN_MONITOR  视图获取执行计划概要或者详情。

SELECT

     PROCESS_NAME,

     PLAN_LINE_ID,

     PLAN_OPERATION,

     COUNT(*) PARALLEL,

     AVG(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) AVG_REFRESH_TIME,

     MAX(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) MAX_REFRESH_TIME,

     MIN(LAST_REFRESH_TIME - FIRST_REFRESH_TIME) MIN_REFRESH_TIME,

     AVG(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) AVG_CHANGE_TIME,

     MAX(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) MAX_CHANGE_TIME,

     MIN(LAST_CHANGE_TIME - FIRST_CHANGE_TIME) MIN_CHANGE_TIME,

     SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,

     SUM(STARTS) TOTAL_RESCAN_TIMES

   FROM

     oceanbase.GV$SQL_PLAN_MONITOR

   WHERE

     trace_id = 'YB427F000001-00060407CD2E5987-0-0'

   GROUP BY

     PLAN_LINE_ID

   ORDER BY

     PLAN_LINE_ID ASC;

结果展示 trace_id 为  YB427F000001-00060407CD2E5987-0-0  的执行计划详情。

+--------------+--------------+------------------------+----------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+--------------------+

| PROCESS_NAME | PLAN_LINE_ID | PLAN_OPERATION         | PARALLEL | AVG_REFRESH_TIME | MAX_REFRESH_TIME | MIN_REFRESH_TIME | AVG_CHANGE_TIME | MAX_CHANGE_TIME | MIN_CHANGE_TIME | TOTAL_OUTPUT_ROWS | TOTAL_RESCAN_TIMES |

+--------------+--------------+------------------------+----------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+--------------------+

|        16755 |            0 | PHY_PX_FIFO_COORD      |        1 |     0.0137190000 |         0.013719 |         0.013719 |    0.0063460000 |        0.006346 |        0.006346 |               300 |                  0 |

|        16883 |            1 | PHY_PX_REDUCE_TRANSMIT |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0010606667 |        0.003182 |        0.000000 |               300 |                  0 |

|        16883 |            2 | PHY_HASH_JOIN          |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0010606667 |        0.003182 |        0.000000 |               300 |                  0 |

|        16883 |            3 | PHY_PX_FIFO_RECEIVE    |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0000000000 |        0.000000 |        0.000000 |                75 |                  0 |

|        16889 |            4 | PHY_PX_DIST_TRANSMIT   |        3 |     0.0024853333 |         0.003176 |         0.002140 |    0.0003526667 |        0.001058 |        0.000000 |                25 |                  0 |

|        16889 |            5 | PHY_GRANULE_ITERATOR   |        3 |     0.0024853333 |         0.003176 |         0.002140 |    0.0003526667 |        0.001058 |        0.000000 |                25 |                  0 |

|        16889 |            6 | PHY_TABLE_SCAN         |        3 |     0.0024853333 |         0.003176 |         0.002140 |    0.0003526667 |        0.001058 |        0.000000 |                25 |                  1 |

|        16883 |            7 | PHY_GRANULE_ITERATOR   |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0013980000 |        0.004194 |        0.000000 |                300 |                  0 |

|        16883 |            8 | PHY_TABLE_SCAN         |        3 |     0.0031260000 |         0.005230 |         0.001036 |    0.0013980000 |        0.004194 |        0.000000 |               300 |                  1 |

+--------------+--------------+------------------------+----------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-------------------+--------------------+

9 rows in set 

查询优化

对 SQL 进行优化，一般考虑访问路径和联接算法两个方向。

在 OceanBase 集群中，访问路径的代价取决于很多因素，比如扫描的行数、回表的行数、投影的列数和谓词的个数等。一般可以考虑没有合适的过滤条件或者过滤条件过于宽泛；没有索引或者索引的列设置不合理；多表之前的关联方法不合理，导致扫描行产生了笛卡尔积或者扫表的顺序不合理等方向。

OceanBase 集群的联接算法有 Nested Loop Join、Hash Join 和 Merge Join，三种算法在不同的场景下各有优劣，优化器会自主选择最佳联接算法。如果执行计划中联接算法的代价比较高，一般考虑两表关联的字段不合理，两表关联的顺序不合理等方向。

优化 SQL 案例

构建慢 sql

  1. 切换测试 database。

obclient [oceanbase]> use testdb

Database changed

创建 3 张测试表。

obclient [testdb]> CREATE TABLE tbl1 (c1 INT, c2 VARCHAR(50));

Query OK, 0 rows affected (0.070 sec)


obclient [testdb]>  CREATE TABLE tbl2 (c1 char, c2 VARCHAR(20));

Query OK, 0 rows affected (0.034 sec)


obclient [testdb]>  CREATE TABLE tbl3 (c1 VARCHAR(20), c2 VARCHAR(20));

Query OK, 0 rows affected (0.041 sec)

往测试表中插入数据。

创建 3 个存储过程。

obclient [testdb]> DELIMITER $

obclient [testdb]> 

obclient [testdb]>  CREATE PROCEDURE insert_tbl1(IN args INT)

    -> BEGIN

    -> DECLARE i INT DEFAULT 1;

    -> START TRANSACTION;

    -> WHILE i<=args DO

    -> INSERT INTO tbl1(c1,c2) VALUE (i,CONCAT("jerry",i));

    -> SET i=i+1;

    -> END WHILE;

    -> COMMIT;

    -> END

    -> $

Query OK, 0 rows affected (0.034 sec)


obclient [testdb]> 

obclient [testdb]>  DELIMITER ;

obclient [testdb]> DELIMITER $

obclient [testdb]> 

obclient [testdb]>  CREATE PROCEDURE insert_tbl2(IN args INT)

    -> BEGIN

    -> DECLARE i INT DEFAULT 1;

    -> START TRANSACTION;

    -> WHILE i<=args DO

    -> INSERT INTO tbl2(c1,c2) VALUE ('i',CONCAT("jerry",i));

    -> SET i=i+1;

    -> END WHILE;

    -> COMMIT;

    -> END

    -> $

Query OK, 0 rows affected (0.022 sec)


obclient [testdb]> 

obclient [testdb]>  DELIMITER ;

obclient [testdb]> DELIMITER $

obclient [testdb]> 

obclient [testdb]>  CREATE PROCEDURE insert_tbl3(IN args INT)

    -> BEGIN

    -> DECLARE i INT DEFAULT 1;

    -> START TRANSACTION;

    -> WHILE i<=args DO

    -> INSERT INTO tbl3(c1,c2) VALUE ('i',CONCAT("jerry",i));

    -> SET i=i+1;

    -> END WHILE;

    -> COMMIT;

    -> END

    -> $

Query OK, 0 rows affected (0.024 sec)


obclient [testdb]> 

obclient [testdb]>  DELIMITER ;

obclient [testdb]> call insert_tbl1(1000000);

Query OK, 0 rows affected (30.145 sec)


obclient [testdb]>  call insert_tbl2(1000);

Query OK, 0 rows affected (0.107 sec)


obclient [testdb]>  call insert_tbl3(100);

Query OK, 0 rows affected (0.054 sec)


obclient [testdb]> select tbl1.c2,tbl2.c2,tbl3.c2 

    -> from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

Empty set (0.568 sec)

构建慢 sql。

多次执行如下 sql。


obclient [testdb]> select tbl1.c2,tbl2.c2,tbl3.c2  from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

Empty set (0.461 sec)


obclient [testdb]> select tbl1.c2,tbl2.c2,tbl3.c2  from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

Empty set (0.459 sec)


obclient [testdb]> select tbl1.c2,tbl2.c2,tbl3.c2  from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

Empty set (0.449 sec)


obclient [testdb]> select tbl1.c2,tbl2.c2,tbl3.c2  from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

Empty set (0.446 sec)


obclient [testdb]> select tbl1.c2,tbl2.c2,tbl3.c2  from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

Empty set (0.475 sec)


排查思路

本案例的排查和优化思路如下：

  1. 通过查询  GV$OB_SQL_AUDIT 视图定位 TOP SQL。

    执行如下 SQL 语句查询  GV$OB_SQL_AUDIT 视图定位问题 SQL。通过该视图可以查询问题发生 30 分钟内的 SQL 的执行次数和 CPU 的使用时间等信息。这里为了排查性能差的 SQL，直接根据 SQL 文本来查询。

obclient [testdb]> select SQL_ID, query_sql,

    ->   avg(ELAPSED_TIME),

    ->   avg(QUEUE_TIME),

    ->   avg(ROW_CACHE_HIT + BLOOM_FILTER_CACHE_HIT + BLOCK_CACHE_HIT + DISK_READS) avg_logical_read,

    ->   avg(execute_time) avg_exec_time,

    ->   count(*) cnt,

    ->   avg(execute_time - TOTAL_WAIT_TIME_MICRO ) avg_cpu_time,

    ->   avg( TOTAL_WAIT_TIME_MICRO ) avg_wait_time,

    ->   WAIT_CLASS,

    ->   avg(retry_cnt)

    ->   from oceanbase.v$OB_SQL_AUDIT

    ->   Where query_sql like '%tbl1.c1%';

从查询结果可以看出，sql 文本为 select tbl1.c2,tbl2.c2,tbl3.c2  from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3'  的 SQL 的执行多次，资源占用都比较高。

+----------------------------------+---------------------------------------------------------------------------------------------------------------+-------------------+-----------------+------------------+---------------+------+--------------+---------------+------------+----------------+

| SQL_ID                           | query_sql                                                                                                     | avg(ELAPSED_TIME) | avg(QUEUE_TIME) | avg_logical_read | avg_exec_time | cnt  | avg_cpu_time | avg_wait_time | WAIT_CLASS | avg(retry_cnt) |

+----------------------------------+---------------------------------------------------------------------------------------------------------------+-------------------+-----------------+------------------+---------------+------+--------------+---------------+------------+----------------+

| A6FDB8C0C7AEB27AF56296362F8D3D09 | select tbl1.c2,tbl2.c2,tbl3.c2 

from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3' |       476223.1667 |         32.0000 |         629.0000 |   474744.3333 |    6 |  473901.5000 |      842.8333 | USER_IO    |         0.0000 |

+----------------------------------+---------------------------------------------------------------------------------------------------------------+-------------------+-----------------+------------------+---------------+------+--------------+---------------+------------+----------------+

1 row in set (0.031 sec)


查询问题 SQL 的 Explain 信息，分析影响性能的关键点。

通过执行  EXPLAIN 命令:


obclient [testdb]> explain select tbl1.c2,tbl2.c2,tbl3.c2 

    -> from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';


得到该问题 SQL 的 Explain 信息：

+---------------------------------------------------------------------------------------------------------------------------+

| Query Plan                                                                                                                |

+---------------------------------------------------------------------------------------------------------------------------+

| =============================================================                                                             |

| |ID|OPERATOR                     |NAME|EST.ROWS|EST.TIME(us)|                                                             |

| -------------------------------------------------------------                                                             |

| |0 |NESTED-LOOP JOIN CARTESIAN   |    |49006   |50191       |                                                             |

| |1 |├─NESTED-LOOP JOIN CARTESIAN |    |4951    |48895       |                                                             |

| |2 |│ ├─TABLE FULL SCAN          |tbl1|9901    |48738       |                                                             |

| |3 |│ └─MATERIAL                 |    |1       |7           |                                                             |

| |4 |│   └─TABLE FULL SCAN        |tbl3|1       |7           |                                                             |

| |5 |└─MATERIAL                   |    |10      |54          |                                                             |

| |6 |  └─TABLE FULL SCAN          |tbl2|10      |53          |                                                             |

| =============================================================                                                             |

| Outputs & filters:                                                                                                        |

| -------------------------------------                                                                                     |

|   0 - output([tbl1.c2], [tbl2.c2], [tbl3.c2]), filter(nil), rowset=256                                                    |

|       conds(nil), nl_params_(nil), use_batch=false                                                                        |

|   1 - output([tbl1.c2], [tbl3.c2]), filter(nil), rowset=256                                                               |

|       conds(nil), nl_params_(nil), use_batch=false                                                                        |

|   2 - output([tbl1.c2]), filter([cast(tbl1.c1, DECIMAL(11, 0)) = cast(cast('3', CHAR(1)), DECIMAL(-1, -1))]), rowset=256  |

|       access([tbl1.c1], [tbl1.c2]), partitions(p0)                                                                        |

|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                         |

|       range_key([tbl1.__pk_increment]), range(MIN ; MAX)always true                                                       |

|   3 - output([tbl3.c2]), filter(nil), rowset=256                                                                          |

|   4 - output([tbl3.c2]), filter([cast(cast('3', CHAR(1)), DECIMAL(-1, -1)) = cast(tbl3.c1, DECIMAL(-1, -1))]), rowset=256 |

|       access([tbl3.c1], [tbl3.c2]), partitions(p0)                                                                        |

|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                         |

|       range_key([tbl3.__pk_increment]), range(MIN ; MAX)always true                                                       |

|   5 - output([tbl2.c2]), filter(nil), rowset=256                                                                          |

|   6 - output([tbl2.c2]), filter([tbl2.c1 = '3']), rowset=256                                                              |

|       access([tbl2.c1], [tbl2.c2]), partitions(p0)                                                                        |

|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                         |

|       range_key([tbl2.__pk_increment]), range(MIN ; MAX)always true                                                       |

+---------------------------------------------------------------------------------------------------------------------------+

31 rows in set (0.004 sec)

  1. 根据上述  EXPALIN 信息，了解得到该条 SQL 的执行总代价估算为 50191 ， HASH JOIN 的两个分支都是  TABLE FULL SCAN，其中代价最高的为 48738。从执行计划，可以分析出，当前 3 个表都走了全表扫，并且表关联 cost 很大，可以初步定位出该案例中性能问题的瓶颈点。

    接下来再分析 Outputs & filters 信息，其中2,4 号算子的  equal_conds([cast(tbl1.c1, DECIMAL(11, 0)) = cast(tbl2.c1, DECIMAL(-1, -1))]), other_conds(nil) 中的 'cast' 关键字表明在访问时发生了数据类型的转换，由此得知可能是关联字段的数据类型不一致而导致产生了隐式数据类型转换。而 2,4,6 号算子的  range(MIN ; MAX)always true 表明该 SQL 执行过程中没有做索引匹配。基于此结论再结合 DDL 定义做进一步的分析。

  2. 查询表的 DDL 定义以及数据量，确定出现性能问题的原因。

    • 通过 'show create table' 查看表的定义。

    • 查看表 1 的定义


obclient [testdb]> show create table tbl1\G

*************************** 1. row ***************************

       Table: tbl1

Create Table: CREATE TABLE `tbl1` (

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(50) DEFAULT NULL

) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

1 row in set (0.004 sec)


obclient [testdb]> show create table tbl2\G

*************************** 1. row ***************************

       Table: tbl2

Create Table: CREATE TABLE `tbl2` (

  `c1` char(1) DEFAULT NULL,

  `c2` varchar(20) DEFAULT NULL

) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

1 row in set (0.003 sec)


obclient [testdb]> show create table tbl3\G

*************************** 1. row ***************************

       Table: tbl3

Create Table: CREATE TABLE `tbl3` (

  `c1` varchar(20) DEFAULT NULL,

  `c2` varchar(20) DEFAULT NULL

) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

1 row in set (0.002 sec)


  1. 从上述建表语句可以看出，3 个表没有索引也没有主键，同时关联的列 c1 的数据类型完全不一致，跟上述分析一致。

  • 通过 'select count(*)' 查看表的数据量

    • 查看表 1 的数据量


obclient [testdb]> select count(*) from tbl1;

+----------+

| count(*) |

+----------+

|  1000000 |

+----------+

1 row in set (0.390 sec)


obclient [testdb]> select count(*) from tbl2 where tbl2.c1='3';

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.002 sec)


obclient [testdb]> select count(*) from tbl3;

+----------+

| count(*) |

+----------+

|      100 |

+----------+

1 row in set (0.001 sec)

  • 从上述建表语句可以看出，3 个表中数据量存在量级的差别，其中表 1 的数据量最大，表 2 根据过滤条件过滤后数据量最小。根据 '小表驱动大表' 原则，当前的表关联顺序需要进行调整。

优化思路

本案例中针对表的 DDL 优化方案如下：

  • 对三张表的 c1 列修改统一的数据类型为 'varchar(20)';


obclient [testdb]> alter table tbl1 modify c1 varchar(20);

Query OK, 0 rows affected (3.304 sec)


r(20);nt [testdb]>  alter table tbl2 modify c1 varcha 

Query OK, 0 rows affected (0.343 sec)


obclient [testdb]> desc tb11;

ERROR 1146 (42S02): Table 'testdb.tb11' doesn't exist

obclient [testdb]> desc tbl1;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| c1    | varchar(20) | YES  |     | NULL    |       |

| c2    | varchar(50) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.005 sec)


obclient [testdb]> desc tbl2;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| c1    | varchar(20) | YES  |     | NULL    |       |

| c2    | varchar(20) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.005 sec)

分别对三张表的 c1 列添加索引;

obclient [testdb]> ALTER TABLE tbl1 ADD INDEX tbl1_c1 (c1);

Query OK, 0 rows affected (3.275 sec)


obclient [testdb]>  ALTER TABLE tbl2 ADD INDEX tbl2_c1 (c1);

Query OK, 0 rows affected (0.249 sec)


obclient [testdb]>  ALTER TABLE tbl3 ADD INDEX tbl3_c1 (c1);

Query OK, 0 rows affected (0.253 sec)

  • 通过 hint  /*+ leading(tbl2 tbl1 tbl3)*/ 修改表的关联顺序为  tbl2,tbl1,tbl3。

优化效果

  • 执行时间减少

    优化后的 sql 文本如下：

obclient [testdb]> select /*+ leading(tbl2 tbl1 tbl3)*/ tbl1.c2,tbl2.c2,tbl3.c2 

    -> from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

Empty set (0.008 sec)


obclient [testdb]> explain select /*+ leading(tbl1 tbl2 tbl3)*/ tbl1.c2,tbl2.c2,tbl3.c2 from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1.c1=tbl3.c1 and tbl2.c1='3';

+------------------------------------------------------------------------------+

| Query Plan                                                                   |

+------------------------------------------------------------------------------+

| ======================================================================       |

| |ID|OPERATOR                     |NAME         |EST.ROWS|EST.TIME(us)|       |

| ----------------------------------------------------------------------       |

| |0 |NESTED-LOOP JOIN CARTESIAN   |             |1       |19          |       |

| |1 |├─NESTED-LOOP JOIN CARTESIAN |             |1       |13          |       |

| |2 |│ ├─TABLE RANGE SCAN         |tbl1(tbl1_c1)|1       |7           |       |

| |3 |│ └─MATERIAL                 |             |1       |7           |       |

| |4 |│   └─TABLE RANGE SCAN       |tbl2(tbl2_c1)|1       |7           |       |

| |5 |└─MATERIAL                   |             |1       |7           |       |

| |6 |  └─TABLE RANGE SCAN         |tbl3(tbl3_c1)|1       |7           |       |

| ======================================================================       |

| Outputs & filters:                                                           |

| -------------------------------------                                        |

|   0 - output([tbl1.c2], [tbl2.c2], [tbl3.c2]), filter(nil), rowset=16        |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   1 - output([tbl1.c2], [tbl2.c2]), filter(nil), rowset=16                   |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   2 - output([tbl1.c2]), filter(nil), rowset=16                              |

|       access([tbl1.__pk_increment], [tbl1.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl1.c1], [tbl1.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl1.c1 = '3'])                                            |

|   3 - output([tbl2.c2]), filter(nil), rowset=16                              |

|   4 - output([tbl2.c2]), filter(nil), rowset=16                              |

|       access([tbl2.__pk_increment], [tbl2.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl2.c1], [tbl2.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl2.c1 = '3'])                                            |

|   5 - output([tbl3.c2]), filter(nil), rowset=16                              |

|   6 - output([tbl3.c2]), filter(nil), rowset=16                              |

|       access([tbl3.__pk_increment], [tbl3.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl3.c1], [tbl3.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl3.c1 = '3'])                                            |

+------------------------------------------------------------------------------+

34 rows in set (0.005 sec)

  • 执行后明显可以看到执行时间缩小了，此次执行时间为：0.006 sec。

  • 执行计划 cost 缩小

    优化后 SQL 的 Explain 信息如下，能看到 3 张表都走了范围扫，cost 总计为 19，性能得到了很大的提升。

obclient [testdb]> explain select /*+ leading(tbl2 tbl1 tbl3)*/ tbl1.c2,tbl2.c2,tbl3.c2 from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1

.c1=tbl3.c1 and tbl2.c1='3';

+------------------------------------------------------------------------------+

| Query Plan                                                                   |

+------------------------------------------------------------------------------+

| ======================================================================       |

| |ID|OPERATOR                     |NAME         |EST.ROWS|EST.TIME(us)|       |

| ----------------------------------------------------------------------       |

| |0 |NESTED-LOOP JOIN CARTESIAN   |             |1       |19          |       |

| |1 |├─NESTED-LOOP JOIN CARTESIAN |             |1       |13          |       |

| |2 |│ ├─TABLE RANGE SCAN         |tbl2(tbl2_c1)|1       |7           |       |

| |3 |│ └─MATERIAL                 |             |1       |7           |       |

| |4 |│   └─TABLE RANGE SCAN       |tbl1(tbl1_c1)|1       |7           |       |

| |5 |└─MATERIAL                   |             |1       |7           |       |

| |6 |  └─TABLE RANGE SCAN         |tbl3(tbl3_c1)|1       |7           |       |

| ======================================================================       |

| Outputs & filters:                                                           |

| -------------------------------------                                        |

|   0 - output([tbl1.c2], [tbl2.c2], [tbl3.c2]), filter(nil), rowset=16        |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   1 - output([tbl1.c2], [tbl2.c2]), filter(nil), rowset=16                   |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   2 - output([tbl2.c2]), filter(nil), rowset=16                              |

|       access([tbl2.__pk_increment], [tbl2.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl2.c1], [tbl2.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl2.c1 = '3'])                                            |

|   3 - output([tbl1.c2]), filter(nil), rowset=16                              |

|   4 - output([tbl1.c2]), filter(nil), rowset=16                              |

|       access([tbl1.__pk_increment], [tbl1.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl1.c1], [tbl1.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl1.c1 = '3'])                                            |

|   5 - output([tbl3.c2]), filter(nil), rowset=16                              |

|   6 - output([tbl3.c2]), filter(nil), rowset=16                              |

|       access([tbl3.__pk_increment], [tbl3.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl3.c1], [tbl3.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl3.c1 = '3'])                                            |

+------------------------------------------------------------------------------+

34 rows in set (0.006 sec)


obclient [testdb]> explain select /*+ leading(tbl2,tbl1,tbl3)*/ tbl1.c2,tbl2.c2,tbl3.c2 from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1

.c1=tbl3.c1 and tbl2.c1='3';

+------------------------------------------------------------------------------+

| Query Plan                                                                   |

+------------------------------------------------------------------------------+

| ======================================================================       |

| |ID|OPERATOR                     |NAME         |EST.ROWS|EST.TIME(us)|       |

| ----------------------------------------------------------------------       |

| |0 |NESTED-LOOP JOIN CARTESIAN   |             |1       |19          |       |

| |1 |├─NESTED-LOOP JOIN CARTESIAN |             |1       |13          |       |

| |2 |│ ├─TABLE RANGE SCAN         |tbl2(tbl2_c1)|1       |7           |       |

| |3 |│ └─MATERIAL                 |             |1       |7           |       |

| |4 |│   └─TABLE RANGE SCAN       |tbl1(tbl1_c1)|1       |7           |       |

| |5 |└─MATERIAL                   |             |1       |7           |       |

| |6 |  └─TABLE RANGE SCAN         |tbl3(tbl3_c1)|1       |7           |       |

| ======================================================================       |

| Outputs & filters:                                                           |

| -------------------------------------                                        |

|   0 - output([tbl1.c2], [tbl2.c2], [tbl3.c2]), filter(nil), rowset=16        |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   1 - output([tbl1.c2], [tbl2.c2]), filter(nil), rowset=16                   |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   2 - output([tbl2.c2]), filter(nil), rowset=16                              |

|       access([tbl2.__pk_increment], [tbl2.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl2.c1], [tbl2.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl2.c1 = '3'])                                            |

|   3 - output([tbl1.c2]), filter(nil), rowset=16                              |

|   4 - output([tbl1.c2]), filter(nil), rowset=16                              |

|       access([tbl1.__pk_increment], [tbl1.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl1.c1], [tbl1.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl1.c1 = '3'])                                            |

|   5 - output([tbl3.c2]), filter(nil), rowset=16                              |

|   6 - output([tbl3.c2]), filter(nil), rowset=16                              |

|       access([tbl3.__pk_increment], [tbl3.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl3.c1], [tbl3.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl3.c1 = '3'])                                            |

+------------------------------------------------------------------------------+

34 rows in set (0.012 sec)


obclient [testdb]> explain select /*+ leading(tbl2,tbl3,tbl1)*/ tbl1.c2,tbl2.c2,tbl3.c2 from tbl1,tbl2,tbl3 where tbl1.c1=tbl2.c1 and tbl1

.c1=tbl3.c1 and tbl2.c1='3';

+------------------------------------------------------------------------------+

| Query Plan                                                                   |

+------------------------------------------------------------------------------+

| ======================================================================       |

| |ID|OPERATOR                     |NAME         |EST.ROWS|EST.TIME(us)|       |

| ----------------------------------------------------------------------       |

| |0 |NESTED-LOOP JOIN CARTESIAN   |             |1       |19          |       |

| |1 |├─NESTED-LOOP JOIN CARTESIAN |             |1       |13          |       |

| |2 |│ ├─TABLE RANGE SCAN         |tbl2(tbl2_c1)|1       |7           |       |

| |3 |│ └─MATERIAL                 |             |1       |7           |       |

| |4 |│   └─TABLE RANGE SCAN       |tbl3(tbl3_c1)|1       |7           |       |

| |5 |└─MATERIAL                   |             |1       |7           |       |

| |6 |  └─TABLE RANGE SCAN         |tbl1(tbl1_c1)|1       |7           |       |

| ======================================================================       |

| Outputs & filters:                                                           |

| -------------------------------------                                        |

|   0 - output([tbl1.c2], [tbl2.c2], [tbl3.c2]), filter(nil), rowset=16        |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   1 - output([tbl2.c2], [tbl3.c2]), filter(nil), rowset=16                   |

|       conds(nil), nl_params_(nil), use_batch=false                           |

|   2 - output([tbl2.c2]), filter(nil), rowset=16                              |

|       access([tbl2.__pk_increment], [tbl2.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl2.c1], [tbl2.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl2.c1 = '3'])                                            |

|   3 - output([tbl3.c2]), filter(nil), rowset=16                              |

|   4 - output([tbl3.c2]), filter(nil), rowset=16                              |

|       access([tbl3.__pk_increment], [tbl3.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl3.c1], [tbl3.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl3.c1 = '3'])                                            |

|   5 - output([tbl1.c2]), filter(nil), rowset=16                              |

|   6 - output([tbl1.c2]), filter(nil), rowset=16                              |

|       access([tbl1.__pk_increment], [tbl1.c2]), partitions(p0)               |

|       is_index_back=true, is_global_index=false,                             |

|       range_key([tbl1.c1], [tbl1.__pk_increment]), range(3,MIN ; 3,MAX),     |

|       range_cond([tbl1.c1 = '3'])                                            |

+------------------------------------------------------------------------------+

34 rows in set (0.005 sec)

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/543979/viewspace-3044997/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oceanbase多表连接的hint控制
下一篇： 没有了~
请登录后发表评论 登录
全部评论
hunterjoy

注册时间：2007-12-31

  • 博文量
    168
  • 访问量
    389686

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593