查看 OceanBase 执行计划

使用之前创建的ceshimysql租户，连接到test实例。

确认sql审计打开：

show variables like '%audit%'

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

| Variable_name | Value |

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

| ob_enable_sql_audit | ON |

| ob_sql_audit_percentage | 3 |

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

show parameters like '%audit%';

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

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

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

建表：

MySQL [test]> create table tb1 as select TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,COLUMN_TYPE, COLUMN_KEY from information_schema.columns;

Query OK, 2687 rows affected (0.06 sec)

建索引：

MySQL [test]> ALTER TABLE tb1 ADD INDEX idx1 (TABLE_NAME);

MySQL [test]> ALTER TABLE tb1 ADD INDEX idx2 (ORDINAL_POSITION);

MySQL [test]> ALTER TABLE tb1 ADD INDEX idx3 (DATA_TYPE);

跑几条查询sql，每个都连续执行三次：

select * from tb1 where ORDINAL_POSITION=20; --返回49行，应该走索引

select * from tb1 where DATA_TYPE='timestamp'; --返回262行，应该也走索引

select * from tb1 where ORDINAL_POSITION=20 and DATA_TYPE='timestamp'; --前者基数明显大，而且最终返回2行，应该走ORDINAL_POSITION字段上的索引

SELECT tenant_id,svr_port,sql_id, query_sql,plan_id,count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time

FROM gv$sql_audit

WHERE user_name='root' and query_sql like 'select%tb1%'

GROUP BY sql_id order by avg_elapsed_time desc;

找出需要查看执行计划的sql（第2条sql是为了判断第3条sql的执行计划，不看了）：

*************************** 11. row ***************************

sql_id: 75E303D9E23927DFDEBB81F32136F5E6

query_sql: select * from tb1 where ORDINAL_POSITION=20 and DATA_TYPE='timestamp'

plan_id: 73

count(*): 1

avg_elapsed_time: 1902

avg_exec_time: 298

*************************** 14. row ***************************

sql_id: BC8B7CA8D0392ECC2327F86E05FCEDE1

query_sql: select table_name,ORDINAL_POSITION from tb1 where ORDINAL_POSITION=20

plan_id: 74

count(*): 1

avg_elapsed_time: 1646

avg_exec_time: 307

分别进行查看。先看预执行计划：

MySQL [test]> explain select * from tb1 where ORDINAL_POSITION=20 \G

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

Query Plan: ========================================

----------------------------------------

|0 |TABLE SCAN|tb1(idx2)|49 |369 |

========================================

Outputs & filters:

-------------------------------------

0 - output([tb1.TABLE_NAME], [tb1.COLUMN_NAME], [tb1.ORDINAL_POSITION], [tb1.COLUMN_DEFAULT], [tb1.IS_NULLABLE], [tb1.DATA_TYPE], [tb1.COLUMN_TYPE], [tb1.COLUMN_KEY]), filter(nil),

access([tb1.ORDINAL_POSITION], [tb1.TABLE_NAME], [tb1.COLUMN_NAME], [tb1.COLUMN_DEFAULT], [tb1.IS_NULLABLE], [tb1.DATA_TYPE], [tb1.COLUMN_TYPE], [tb1.COLUMN_KEY]), partitions(p0)

MySQL [test]> explain select * from tb1 where ORDINAL_POSITION=20 and DATA_TYPE='timestamp' \G

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

Query Plan: ========================================

----------------------------------------

|0 |TABLE SCAN|tb1(idx2)|1 |392 |

========================================

Outputs & filters:

-------------------------------------

0 - output([tb1.TABLE_NAME], [tb1.COLUMN_NAME], [tb1.ORDINAL_POSITION], [tb1.COLUMN_DEFAULT], [tb1.IS_NULLABLE], [tb1.DATA_TYPE], [tb1.COLUMN_TYPE], [tb1.COLUMN_KEY]), filter([tb1.DATA_TYPE = 'timestamp']),

access([tb1.ORDINAL_POSITION], [tb1.DATA_TYPE], [tb1.TABLE_NAME], [tb1.COLUMN_NAME], [tb1.COLUMN_DEFAULT], [tb1.IS_NULLABLE], [tb1.COLUMN_TYPE], [tb1.COLUMN_KEY]), partitions(p0)

以上预执行计划全部符合预期。

加hint试试：

MySQL [test]> explain select * from tb1 ignore index(idx2) where ORDINAL_POSITION=20 and DATA_TYPE='timestamp' \G

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

Query Plan: ========================================

----------------------------------------

|0 |TABLE SCAN|tb1(idx3)|3 |1657|

========================================

Outputs & filters:

-------------------------------------

access([tb1.ORDINAL_POSITION], [tb1.DATA_TYPE], [tb1.TABLE_NAME], [tb1.COLUMN_NAME], [tb1.COLUMN_DEFAULT], [tb1.IS_NULLABLE], [tb1.COLUMN_TYPE], [tb1.COLUMN_KEY]), partitions(p0)

OK。接下来看看真实的执行计划，查视图：gv$plan_cache_plan_explain

这个视图需要提供四要素：ip.port.tenant_id.plan_id。否则返回零行！

加足条件，就能查出来：

MySQL [test]> SELECT plan_depth, plan_line_id,operator,name,rows,cost from oceanbase.gv$plan_cache_plan_explain where tenant_id=1003 and ip = '192.168.17.199' and port=2882 and plan_id=73;

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

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

| 0 | 0 | PHY_TABLE_SCAN | tb1(idx2) | 1 | 391 |

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

1 row in set (0.00 sec)

MySQL [test]> SELECT plan_depth, plan_line_id,operator,name,rows,cost from oceanbase.gv$plan_cache_plan_explain where tenant_id=1003 and ip = '192.168.17.199' and port=2882 and plan_id=74;

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

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

| 0 | 0 | PHY_TABLE_SCAN | tb1(idx2) | 49 | 341 |

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

1 row in set (0.01 sec)

本案例中，预执行计划和真实执行计划一致。