--- title: SQL 中表含 enum 列导致 LEFT JOIN 未 rewrite 为 JOIN-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 SQL 中表含 enum 列导致 LEFT JOIN 未 rewrite 为 JOIN相关的常见问题和使用技巧，帮助您快速解决 SQL 中表含 enum 列导致 LEFT JOIN 未 rewrite 为 JOIN的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # SQL 中表含 enum 列导致 LEFT JOIN 未 rewrite 为 JOIN 更新时间：2026-05-14 09:21 适用版本： V3.2.x、V4.2.x 内容类型：Troubleshoot 本文介绍 SQL 中表含 enum 列导致 LEFT JOIN 未 rewrite 为 JOIN。 ## 问题现象 SQL 中表含 enum 列导致 LEFT JOIN 未 rewrite 为 JOIN，从而导致未走预期的索引，造成查询性能问题。 以下为实际问题，其中 stu 表和 user 表都包含 enum 列，即使 **SQL 中未用到 enum 列**，LEFT JOIN 仍未按预期 rewrite 为 JOIN。 `id_type` enum('1','2') DEFAULT '1' COMMENT '证件类型 1:身份证， 2:其他'``, LEFT JOIN 执行时间为 3.09s。 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit001.png) ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit002.png) 将 LEFT JOIN 改为 JOIN 后执行时间为 57.34 ms， ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit003.png) ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit004.png) #### 注意 由于 `WHERE` 条件中包含 `user` 表的条件，此时 `LEFT JOIN` 等同于 `JOIN`，即此时 `LEFT JOIN` 是没有必要的。 ## 问题原因 目前 OceanBase 数据库 V3.x 和 V4.x 版本中 SQL 查询所有表的列涉及 `ENUM`、`GEOMETRY` 以及其子类型字段类型的 SQL 语句，不会进行任何的改写。 ## 解决方法 方法一：将 `enum` 类型改成 `VARCHAR` 类型。 方法二：将不必要的 `LEFT JOIN` 改为 `JOIN`。 ## 模拟测试 在 OceanBase 数据库 V3.x 和 V4.x 版本中进行详细的模拟测试。 ### Test case 1 详细测试输出 #### OceanBase 数据库 V4.2.x 版本上的模拟测试示例 1. 登录 OceanBase 数据库 MySQL 租户。 ```shell [root@localhost]# mysql -h${DB_HOST} -P2883 -uroot@obmysql#obcluster2 -Ddb1 -p${DB_PASSWORD} Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 93753 Server version: 5.6.25 OceanBase 4.2.1.3 (r103050012024012511-44db4190d80efddf8db98d564d661fa9417b63a6) (Built Jan 25 2024 11:55:53) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ``` 2. 创建测试表 test_rewrite_enum_1。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_enum_1(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.496 sec) ``` 3. 在 test_rewrite_enum_1 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_1_c1 ON test_rewrite_enum_1 (c1); Query OK, 0 rows affected (1.021 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_1_c2 ON test_rewrite_enum_1 (c2); Query OK, 0 rows affected (1.019 sec) ``` 4. 向 test_rewrite_enum_1 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.030 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.005 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec) ``` 5. 提交。 ```shell MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec) ``` 6. 创建测试表 test_rewrite_enum_2。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_enum_2(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.276 sec) ``` 7. 在 test_rewrite_enum_2 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_2_c1 ON test_rewrite_enum_2 (c1); Query OK, 0 rows affected (1.250 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_2_c2 ON test_rewrite_enum_2 (c2); Query OK, 0 rows affected (1.041 sec) ``` 8. 向 test_rewrite_enum_2 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.059 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec) ``` 9. 提交。 ``` 10. 查询表 test_rewrite_enum_1，test_rewrite_enum_2。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_1; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:30 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:30 | | 3 | A3 | B3 | 0 | 2024-02-07 16:10:30 | +----+------+------+------+---------------------+ 3 rows in set (0.002 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_2; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:32 | +----+------+------+------+---------------------+ 2 rows in set (0.004 sec) ``` 11. 使用 LEFT JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:30 | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:30 | 2 | A2 | B2 | 0 | 2024-02-07 16:10:32 | | 3 | A3 | B3 | 0 | 2024-02-07 16:10:30 | NULL | NULL | NULL | NULL | NULL | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 3 rows in set (0.036 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:30 | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 1 row in set (0.013 sec) ``` 12. 使用 JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:30 | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:30 | 2 | A2 | B2 | 0 | 2024-02-07 16:10:32 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 2 rows in set (0.014 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:30 | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 1 row in set (0.006 sec) ``` 13. 使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |HASH RIGHT OUTER JOIN | |2 |15 | | | |1 |├─PX COORDINATOR | |2 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000|2 |8 | | | |3 |│ └─TABLE FULL SCAN |t2 |2 |4 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter([t2.c2 | | = 'B1']), rowset=16 | | equal_conds([t1.n1 = t2.n1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t2.n1]), range(MIN ; MAX)always true | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.005 sec) ``` 14. 使用 explain 展示使用 JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================================================ | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------------------------- | | |0 |HASH JOIN | |1 |15 | | | |1 |├─PX COORDINATOR | |1 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000 |1 |9 | | | |3 |│ └─TABLE RANGE SCAN|t2(idx_test_rewrite_enum_2_c2)|1 |7 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | ================================================================================ | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter(nil), rowset=16 | | equal_conds([t1.c1 = t2.c1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c2], [t2.n1]), range(B1,MIN ; B1,MAX), | | range_cond([t2.c2 = 'B1']) | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.006 sec) ``` #### OceanBase 数据库 V3.2.x 版本上的模拟测试示例 1. 登录 OceanBase 数据库 MySQL 租户。 ```shell [root@localhost]# $mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -A -c Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 74716 Server version: 5.6.25 OceanBase 3.2.4.7 (r107010022023122913-1eba23192ff3365951f818121471f56a49b29a7f) (Built Dec 29 2023 13:55:55) ```shell MySQL [test]> CREATE TABLE test_rewrite_enum_1(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.120 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_1_c1 ON test_rewrite_enum_1 (c1); Query OK, 0 rows affected (0.62 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_1_c2 ON test_rewrite_enum_1 (c2); Query OK, 0 rows affected (0.44 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.00 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec) ```shell MySQL [test]> CREATE TABLE test_rewrite_enum_2(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.05 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_2_c1 ON test_rewrite_enum_2 (c1); Query OK, 0 rows affected (0.54 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_2_c2 ON test_rewrite_enum_2 (c2); Query OK, 0 rows affected (0.55 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.03 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_1; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:31 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:31 | | 3 | A3 | B3 | 0 | 2024-02-07 16:10:31 | +----+------+------+------+---------------------+ 3 rows in set (0.01 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_2; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:32 | +----+------+------+------+---------------------+ 2 rows in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:31 | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:31 | 2 | A2 | B2 | 0 | 2024-02-07 16:10:32 | | 3 | A3 | B3 | 0 | 2024-02-07 16:10:31 | NULL | NULL | NULL | NULL | NULL | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 3 rows in set (0.01 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:31 | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 1 row in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:10:31 | 1 | A1 | B1 | 0 | 2024-02-07 16:10:32 | | 2 | A2 | B2 | 0 | 2024-02-07 16:10:31 | 2 | A2 | B2 | 0 | 2024-02-07 16:10:32 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 2 rows in set (0.01 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell MySQL [test]> explain SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |MERGE OUTER JOIN| |2 |93 | |1 | TABLE SCAN |t1 |3 |46 | |2 | TABLE SCAN |t2 |2 |46 | ========================================= Outputs & filters ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter([t2.c2 = 'B1']), equal_conds([t1.n1 = t2.n1]), other_conds(nil) 1 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) 2 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) ```shell MySQL [test]> explain SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |132 | |1 | TABLE SCAN |t2(idx_test_rewrite_enum_2_c2)|1 |92 | |2 | TABLE SCAN |t1(idx_test_rewrite_enum_1_c1)|1 |39 | =================================================================== 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter(nil), conds(nil), nl_params_([t2.c1]) 1 - output([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), filter(nil), access([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), partitions(p0) 2 - output([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` #### 总结 Test case 1 测试结果 在 OceanBase 数据库 V4.2.x，V3.2.x 二个版本上的测试结果为：两表均含 enum 列，未改写，未走索引以及两表均含 enum 列，LEFT JOIN 未改写为 JOIN，未走索引。 具体详细测试输出如下。 - OceanBase 数据库 V4.2.x 版本： ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit005.png) - OceanBase 数据库 V3.2.x 版本： ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit006.png) ### Test case 2 详细测试输出 ```shell [root@localhost] #mysql -h${DB_HOST} -P2883 -uroot@obmysql#obcluster2 -Ddb1 -p${DB_PASSWORD} Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 93862 Server version: 5.6.25 OceanBase 4.2.1.3 (r103050012024012511-44db4190d80efddf8db98d564d661fa9417b63a6) (Built Jan 25 2024 11:55:53) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ``` 2. 创建测试表 test_rewrite_no_enum_1。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_no_enum_1(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); ``` 3. 在 test_rewrite_no_enum_1 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_no_enum_1_c1 ON test_rewrite_no_enum_1 (c1); Query OK, 0 rows affected (1.118 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_no_enum_1_c2 ON test_rewrite_no_enum_1 (c2); Query OK, 0 rows affected (1.107 sec) ``` 4. 向 test_rewrite_no_enum_1 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.036 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec) ``` 6. 创建测试表 test_rewrite_no_enum_2。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_no_enum_2(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.218 sec) ``` 7. 在 test_rewrite_no_enum_2 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_no_enum_2_c1 ON test_rewrite_no_enum_2 (c1); Query OK, 0 rows affected (1.010 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_no_enum_2_c2 ON test_rewrite_no_enum_2 (c2); Query OK, 0 rows affected (1.003 sec) ``` 8. 向 est_rewrite_no_enum_2 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.039 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec) ``` 10. 查询表 test_rewrite_no_enum_1，test_rewrite_no_enum_2。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:12 | | 3 | A3 | B3 | 0 | 2024-02-07 16:14:12 | +----+------+------+----+---------------------+ 3 rows in set (0.005 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_no_enum_2; +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:14 | +----+------+------+----+---------------------+ 2 rows in set (0.003 sec) ``` 11. 使用 LEFT JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:12 | 2 | A2 | B2 | 0 | 2024-02-07 16:14:14 | | 3 | A3 | B3 | 0 | 2024-02-07 16:14:12 | NULL | NULL | NULL | NULL | NULL | +----+------+------+----+---------------------+----+------+------+----+---------------------+ 3 rows in set (0.032 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ 1 row in set (0.009 sec) ``` 12. 使用 JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:12 | 2 | A2 | B2 | 0 | 2024-02-07 16:14:14 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ 2 rows in set (0.032 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ``` 13. 使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +--------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------+ | =================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------------------- | | |0 |HASH JOIN | |1 |15 | | | |1 |├─PX COORDINATOR | |1 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000 |1 |9 | | | |3 |│ └─TABLE RANGE SCAN|t2(idx_test_rewrite_no_enum_2_c2)|1 |7 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | =================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | equal_conds([t1.n1 = t2.n1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c2], [t2.n1]), range(B1,MIN ; B1,MAX), | | range_cond([t2.c2 = 'B1']) | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +--------------------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.005 sec) ``` 14. 使用 explain 展示使用 JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +--------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------+ | =================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------------------------------------- | | |0 |HASH JOIN | |1 |15 | | | |1 |├─PX COORDINATOR | |1 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000 |1 |9 | | | |3 |│ └─TABLE RANGE SCAN|t2(idx_test_rewrite_no_enum_2_c2)|1 |7 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | =================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | equal_conds([t1.c1 = t2.c1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c2], [t2.n1]), range(B1,MIN ; B1,MAX), | | range_cond([t2.c2 = 'B1']) | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +--------------------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.005 sec) ``` ```shell [admin@localhost]$mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -A -c Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 74723 Server version: 5.6.25 OceanBase 3.2.4.7 (r107010022023122913-1eba23192ff3365951f818121471f56a49b29a7f) (Built Dec 29 2023 13:55:55) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ```shell MySQL [test]> CREATE TABLE test_rewrite_no_enum_1(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_no_enum_1_c1 ON test_rewrite_no_enum_1 (c1); Query OK, 0 rows affected (0.54 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_no_enum_1_c2 ON test_rewrite_no_enum_1 (c2); Query OK, 0 rows affected (0.44 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.00 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> CREATE TABLE test_rewrite_no_enum_2(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.05 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_no_enum_2_c1 ON test_rewrite_no_enum_2 (c1); Query OK, 0 rows affected (0.54 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_no_enum_2_c2 ON test_rewrite_no_enum_2 (c2); Query OK, 0 rows affected (0.54 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.02 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_no_enum_1; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:12 | | 3 | A3 | B3 | 0 | 2024-02-07 16:14:12 | +----+------+------+----+---------------------+ 3 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_no_enum_2; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:14 | +----+------+------+----+---------------------+ 2 rows in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:12 | 2 | A2 | B2 | 0 | 2024-02-07 16:14:14 | | 3 | A3 | B3 | 0 | 2024-02-07 16:14:12 | NULL | NULL | NULL | NULL | NULL | +----+------+------+----+---------------------+----+------+------+----+---------------------+ 3 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ 1 row in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:14:12 | 1 | A1 | B1 | 0 | 2024-02-07 16:14:14 | | 2 | A2 | B2 | 0 | 2024-02-07 16:14:12 | 2 | A2 | B2 | 0 | 2024-02-07 16:14:14 | +----+------+------+----+---------------------+----+------+------+----+---------------------+ 2 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell MySQL [test]> explain SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |132 | |1 | TABLE SCAN |t2(idx_test_rewrite_no_enum_2_c2)|1 |92 | |2 | TABLE GET |t1 |1 |40 | ====================================================================== Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), conds(nil), nl_params_([t2.n1]) 1 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) 2 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) ```shell MySQL [test]> explain SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |132 | |1 | TABLE SCAN |t2(idx_test_rewrite_no_enum_2_c2)|1 |92 | |2 | TABLE SCAN |t1(idx_test_rewrite_no_enum_1_c1)|1 |39 | ====================================================================== Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), conds(nil), nl_params_([t2.c1]) 1 - output([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), filter(nil), access([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), partitions(p0) 2 - output([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` #### 总结 Test case 2 测试结果 在 OceanBase 数据库 V4.2.x，V3.2.x 二个版本上的测试结果为：两表均不含 enum 列，有改写，走了索引以及两表均不含 enum 列，LEFT JOIN 有改写为 JOIN，走了索引。 - OceanBase 数据库 V4.2.x 版本 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit007.png) - OceanBase 数据库 V3.2.x 版本 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit008.png) ### Test case 3 详细测试输出 ```shell [root@localhost]# mysql -h${DB_HOST} -P2883 -uroot@obmysql#obcluster2 -Ddb1 -p${DB_PASSWORD} Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 93968 Server version: 5.6.25 OceanBase 4.2.1.3 (r103050012024012511-44db4190d80efddf8db98d564d661fa9417b63a6) (Built Jan 25 2024 11:55:53) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ``` 2. 创建测试表 test_rewrite_enum_11。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_enum_11(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); ``` 3. 在 test_rewrite_enum_11 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_11_c1 ON test_rewrite_enum_11 (c1); Query OK, 0 rows affected (1.238 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_11_c2 ON test_rewrite_enum_11 (c2); Query OK, 0 rows affected (1.106 sec) ``` 4. 向 test_rewrite_enum_11 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.055 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.005 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.008 sec) ``` 6. 创建测试表 test_rewrite_enum_12。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_enum_12(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.233 sec) ``` 7. 在 test_rewrite_enum_12 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_12_c1 ON test_rewrite_enum_12 (c1); Query OK, 0 rows affected (1.122 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_12_c2 ON test_rewrite_enum_12 (c2); Query OK, 0 rows affected (1.064 sec) ``` 8. 向 test_rewrite_enum_12 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.020 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.005 sec) ```shell MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.000 sec) ``` 10. 查询表 test_rewrite_enum_11，test_rewrite_enum_12。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_11; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:32:19 | | 2 | A2 | B2 | 0 | 2024-02-07 16:32:19 | | 3 | A3 | B3 | 0 | 2024-02-07 16:32:19 | +----+------+------+------+---------------------+ 3 rows in set (0.004 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_12; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:32:22 | | 2 | A2 | B2 | 0 | 2024-02-07 16:32:22 | +----+------+------+----+---------------------+ 2 rows in set (0.007 sec) ``` 11. 使用 LEFT JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:32:19 | 1 | A1 | B1 | 0 | 2024-02-07 16:32:22 | | 2 | A2 | B2 | 0 | 2024-02-07 16:32:19 | 2 | A2 | B2 | 0 | 2024-02-07 16:32:22 | | 3 | A3 | B3 | 0 | 2024-02-07 16:32:19 | NULL | NULL | NULL | NULL | NULL | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 3 rows in set (0.059 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:32:19 | 1 | A1 | B1 | 0 | 2024-02-07 16:32:22 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.022 sec) ``` 12. 使用 JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:32:19 | 1 | A1 | B1 | 0 | 2024-02-07 16:32:22 | | 2 | A2 | B2 | 0 | 2024-02-07 16:32:19 | 2 | A2 | B2 | 0 | 2024-02-07 16:32:22 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 2 rows in set (0.025 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:32:19 | 1 | A1 | B1 | 0 | 2024-02-07 16:32:22 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.007 sec) ``` 13. 使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |HASH RIGHT OUTER JOIN | |2 |16 | | | |1 |├─PX COORDINATOR | |2 |11 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000|2 |9 | | | |3 |│ └─TABLE FULL SCAN |t2 |2 |4 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter([t2.c2 = 'B1']), rowset=16 | | equal_conds([t1.n1 = t2.n1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t2.n1]), range(MIN ; MAX)always true | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ 24 rows in set (0.004 sec) ``` 14. 使用 explain 展示使用 JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +-------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------------------- | | |0 |HASH JOIN | |1 |15 | | | |1 |├─PX COORDINATOR | |1 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000 |1 |9 | | | |3 |│ └─TABLE RANGE SCAN|t2(idx_test_rewrite_enum_12_c2)|1 |7 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | ================================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | equal_conds([t1.c1 = t2.c1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c2], [t2.n1]), range(B1,MIN ; B1,MAX), | | range_cond([t2.c2 = 'B1']) | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.005 sec) ``` ```shell [admin@localhost]$ mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -A -c Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 74752 Server version: 5.6.25 OceanBase 3.2.4.7 (r107010022023122913-1eba23192ff3365951f818121471f56a49b29a7f) (Built Dec 29 2023 13:55:55) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ```shell MySQL [test]> CREATE TABLE test_rewrite_enum_11(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.18 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_11_c1 ON test_rewrite_enum_11 (c1); Query OK, 0 rows affected (0.44 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_11_c2 ON test_rewrite_enum_11 (c2); Query OK, 0 rows affected (0.43 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.00 sec) ```shell MySQL [test]> CREATE TABLE test_rewrite_enum_12(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.06 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_12_c1 ON test_rewrite_enum_12 (c1); Query OK, 0 rows affected (0.54 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_12_c2 ON test_rewrite_enum_12 (c2); Query OK, 0 rows affected (0.44 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_11; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:36:04 | | 2 | A2 | B2 | 0 | 2024-02-07 16:36:04 | | 3 | A3 | B3 | 0 | 2024-02-07 16:36:04 | +----+------+------+------+---------------------+ 3 rows in set (0.01 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_12; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:36:05 | | 2 | A2 | B2 | 0 | 2024-02-07 16:36:05 | +----+------+------+----+---------------------+ 2 rows in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:36:04 | 1 | A1 | B1 | 0 | 2024-02-07 16:36:05 | | 2 | A2 | B2 | 0 | 2024-02-07 16:36:04 | 2 | A2 | B2 | 0 | 2024-02-07 16:36:05 | | 3 | A3 | B3 | 0 | 2024-02-07 16:36:04 | NULL | NULL | NULL | NULL | NULL | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 3 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:36:04 | 1 | A1 | B1 | 0 | 2024-02-07 16:36:05 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:36:04 | 1 | A1 | B1 | 0 | 2024-02-07 16:36:05 | | 2 | A2 | B2 | 0 | 2024-02-07 16:36:04 | 2 | A2 | B2 | 0 | 2024-02-07 16:36:05 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 2 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:36:04 | 1 | A1 | B1 | 0 | 2024-02-07 16:36:05 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.01 sec) ```shell MySQL [test]> explain SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |MERGE OUTER JOIN| |2 |93 | |1 | TABLE SCAN |t1 |3 |46 | |2 | TABLE SCAN |t2 |2 |46 | ========================================= Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter([t2.c2 = 'B1']), equal_conds([t1.n1 = t2.n1]), other_conds(nil) 1 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) 2 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ```shell MySQL [test]> explain SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ==================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |132 | |1 | TABLE SCAN |t2(idx_test_rewrite_enum_12_c2)|1 |92 | |2 | TABLE SCAN |t1(idx_test_rewrite_enum_11_c1)|1 |39 | ==================================================================== Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [enum_to_str('', t1.c3)], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), conds(nil), nl_params_([t2.c1]) 1 - output([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), filter(nil), access([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), partitions(p0) 2 - output([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) ``` #### 总结 Test case 3 测试结果 在 OceanBase 数据库 V4.2.x，V3.2.x 二个版本上的测试结果为：仅驱动表含 enum 列，未改写，未走索引以及仅驱动表含 enum 列，LEFT JOIN 未改写为 JOIN，未走索引。 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit009.png) - OceanBase 数据库 V3.2.x 版本 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit010.png) ### Test case 4 详细测试输出 ```shell [root@localhost]# mysql -h${DB_HOST} -P2883 -uroot@obmysql#obcluster2 -Ddb1 -p${DB_PASSWORD} Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 93982 Server version: 5.6.25 OceanBase 4.2.1.3 (r103050012024012511-44db4190d80efddf8db98d564d661fa9417b63a6) (Built Jan 25 2024 11:55:53) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ``` 2. 创建测试表 test_rewrite_enum_21。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_enum_21(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); ``` 3. 在 test_rewrite_enum_21 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_21_c1 ON test_rewrite_enum_21 (c1); Query OK, 0 rows affected (0.987 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_21_c2 ON test_rewrite_enum_21 (c2); Query OK, 0 rows affected (0.949 sec) ``` 4. 向 test_rewrite_enum_21 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.027 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.003 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec) ``` 6. 创建测试表 test_rewrite_enum_22。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_enum_22(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.213 sec) ``` 7. 在 test_rewrite_enum_22 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_22_c1 ON test_rewrite_enum_22 (c1); Query OK, 0 rows affected (1.066 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_22_c2 ON test_rewrite_enum_22 (c2); Query OK, 0 rows affected (1.084 sec) ``` 8. 向 test_rewrite_enum_22 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.009 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.007 sec) ``` 10. 查询表 test_rewrite_enum_21，test_rewrite_enum_22。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_21; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:27 | | 3 | A3 | B3 | 0 | 2024-02-07 16:37:27 | +----+------+------+----+---------------------+ 3 rows in set (0.003 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_22; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:29 | +----+------+------+------+---------------------+ 2 rows in set (0.058 sec) ``` 11. 使用 LEFT JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:27 | 2 | A2 | B2 | 0 | 2024-02-07 16:37:29 | | 3 | A3 | B3 | 0 | 2024-02-07 16:37:27 | NULL | NULL | NULL | NULL | NULL | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 3 rows in set (0.033 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 1 row in set (0.021 sec) ``` 12. 使用 JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:27 | 2 | A2 | B2 | 0 | 2024-02-07 16:37:29 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 2 rows in set (0.021 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 1 row in set (0.007 sec) ``` 13. 使用 explain 展示使用 LEFT JOIN 关联 ttest_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |HASH RIGHT OUTER JOIN | |2 |15 | | | |1 |├─PX COORDINATOR | |2 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000|2 |8 | | | |3 |│ └─TABLE FULL SCAN |t2 |2 |4 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter([t2.c2 = 'B1']), rowset=16 | | equal_conds([t1.n1 = t2.n1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t2.n1]), range(MIN ; MAX)always true | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ 24 rows in set (0.004 sec) ``` 14. 使用 explain 展示使用 JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +-------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------------------- | | |0 |HASH JOIN | |1 |15 | | | |1 |├─PX COORDINATOR | |1 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000 |1 |9 | | | |3 |│ └─TABLE RANGE SCAN|t2(idx_test_rewrite_enum_22_c2)|1 |7 | | | |4 |└─TABLE FULL SCAN |t1 |3 |4 | | | ================================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter(nil), rowset=16 | | equal_conds([t1.c1 = t2.c1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.d1], [t2.c3]), filter(nil), rowset=16 | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), rowset=16 | | access([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c2], [t2.n1]), range(B1,MIN ; B1,MAX), | | range_cond([t2.c2 = 'B1']) | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), rowset=16 | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.005 sec) ``` ```shell [admin@localhost]$ mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -A -c Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 74757 Server version: 5.6.25 OceanBase 3.2.4.7 (r107010022023122913-1eba23192ff3365951f818121471f56a49b29a7f) (Built Dec 29 2023 13:55:55) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ```shell MySQL [test]> CREATE TABLE test_rewrite_enum_21(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.46 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_21_c1 ON test_rewrite_enum_21 (c1); Query OK, 0 rows affected (0.54 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_21_c2 ON test_rewrite_enum_21 (c2); Query OK, 0 rows affected (0.44 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> CREATE TABLE test_rewrite_enum_22(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 enum('0','1') NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.06 sec) ``` 7. 在 est_rewrite_enum_22 表上创建索引。 ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_22_c1 ON test_rewrite_enum_22 (c1); Query OK, 0 rows affected (0.44 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_enum_22_c2 ON test_rewrite_enum_22 (c2); Query OK, 0 rows affected (0.54 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_21; +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:27 | | 3 | A3 | B3 | 0 | 2024-02-07 16:37:27 | +----+------+------+----+---------------------+ 3 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_22; +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:29 | +----+------+------+------+---------------------+ 2 rows in set (0.01 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:27 | 2 | A2 | B2 | 0 | 2024-02-07 16:37:29 | | 3 | A3 | B3 | 0 | 2024-02-07 16:37:27 | NULL | NULL | NULL | NULL | NULL | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 3 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 1 row in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | | 2 | A2 | B2 | 0 | 2024-02-07 16:37:27 | 2 | A2 | B2 | 0 | 2024-02-07 16:37:29 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 2 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:37:27 | 1 | A1 | B1 | 0 | 2024-02-07 16:37:29 | +----+------+------+----+---------------------+----+------+------+------+---------------------+ 1 row in set (0.01 sec) ``` 13. 使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句的执行计划。 ```shell MySQL [test]> explain SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter([t2.c2 = 'B1']), equal_conds([t1.n1 = t2.n1]), other_conds(nil) 1 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) 2 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MySQL [test]> MySQL [test]> explain SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ==================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |132 | |1 | TABLE SCAN |t2(idx_test_rewrite_enum_22_c2)|1 |92 | |2 | TABLE SCAN |t1(idx_test_rewrite_enum_21_c1)|1 |39 | ==================================================================== Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [enum_to_str('', t2.c3)], [t2.d1]), filter(nil), conds(nil), nl_params_([t2.c1]) 1 - output([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), filter(nil), access([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), partitions(p0) 2 - output([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` #### 总结 Test case 4 测试结果 在 OceanBase 数据库 V4.2.x，V3.2.x 二个版本上的测试结果为：仅非驱动表含 enum 列，未改写，未走索引以及仅非驱动表含 enum 列，LEFT JOIN 未改写为 JOIN，未走索引。 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit011.png) - OceanBase 数据库 V3.2.x 版本： ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit012.png) ### Test case 5 详细测试输出 ```shell [root@localhost]# mysql -h${DB_HOST} -P2883 -uroot@obmysql#obcluster2 -Ddb1 -p${DB_PASSWORD} Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 93996 Server version: 5.6.25 OceanBase 4.2.1.3 (r103050012024012511-44db4190d80efddf8db98d564d661fa9417b63a6) (Built Jan 25 2024 11:55:53) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ``` 2. 创建测试表 test_rewrite_geometry_1。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_geometry_1(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 GEOMETRY SRID 4326, d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.421 sec) ``` 3. 在 test_rewrite_geometry_1 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_1_c1 ON test_rewrite_geometry_1 (c1); Query OK, 0 rows affected (1.078 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_1_c2 ON test_rewrite_geometry_1 (c2); Query OK, 0 rows affected (1.084 sec) ``` 4. 向 test_rewrite_geometry_1 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.012 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec) ``` 6. 创建测试表 test_rewrite_geometry_2。 ```shell MySQL [db1]> CREATE TABLE test_rewrite_geometry_2(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.263 sec) ``` 7. 在 test_rewrite_geometry_2 表上创建索引。 ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_2_c1 ON test_rewrite_geometry_2 (c1); Query OK, 0 rows affected (0.861 sec) ``` ```shell MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_2_c2 ON test_rewrite_geometry_2 (c2); Query OK, 0 rows affected (0.977 sec) ``` 8. 向 test_rewrite_geometry_2 表插入数据。 ```shell MySQL [db1]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.047 sec) ``` ```shell MySQL [db1]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec) ``` 10. 查询表 test_rewrite_geometry_1，test_rewrite_geometry_2。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_geometry_1; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | | 2 | A2 | B2 | NULL | 2024-02-07 16:39:34 | | 3 | A3 | B3 | NULL | 2024-02-07 16:39:34 | +----+------+------+------+---------------------+ 3 rows in set (0.005 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_geometry_2; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:39:36 | | 2 | A2 | B2 | 0 | 2024-02-07 16:39:36 | +----+------+------+----+---------------------+ 2 rows in set (0.002 sec) ``` 11. 使用 LEFT JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句。 ```shell MySQL [db1]> SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:36 | | 2 | A2 | B2 | NULL | 2024-02-07 16:39:34 | 2 | A2 | B2 | 0 | 2024-02-07 16:39:36 | | 3 | A3 | B3 | NULL | 2024-02-07 16:39:34 | NULL | NULL | NULL | NULL | NULL | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 3 rows in set (0.029 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:36 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.021 sec) ```shell MySQL [db1]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:36 | | 2 | A2 | B2 | NULL | 2024-02-07 16:39:34 | 2 | A2 | B2 | 0 | 2024-02-07 16:39:36 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 2 rows in set (0.027 sec) ``` ```shell MySQL [db1]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:36 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.008 sec) ``` 13. 使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +--------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |HASH RIGHT OUTER JOIN | |2 |16 | | | |1 |├─PX COORDINATOR | |2 |11 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000|2 |9 | | | |3 |│ └─TABLE FULL SCAN |t2 |2 |4 | | | |4 |└─TABLE FULL SCAN |t1 |3 |5 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter([t2.c2 = 'B1']) | | equal_conds([t1.n1 = t2.n1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil) | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil) | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil) | | access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t2.n1]), range(MIN ; MAX)always true | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil) | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +--------------------------------------------------------------------------------------------------------------------------------+ 24 rows in set (0.004 sec) ``` 14. 使用 explain 展示使用 JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句的执行计划。 ```shell MySQL [db1]> explain SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +---------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------+ | ==================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ------------------------------------------------------------------------------------ | | |0 |HASH JOIN | |1 |15 | | | |1 |├─PX COORDINATOR | |1 |10 | | | |2 |│ └─EXCHANGE OUT DISTR|:EX10000 |1 |9 | | | |3 |│ └─TABLE RANGE SCAN|t2(idx_test_rewrite_geometry_2_c2)|1 |7 | | | |4 |└─TABLE FULL SCAN |t1 |3 |5 | | | ==================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil) | | equal_conds([t1.c1 = t2.c1]), other_conds(nil) | | 1 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil) | | 2 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil) | | is_single, dop=1 | | 3 - output([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil) | | access([t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), partitions(p0) | | is_index_back=true, is_global_index=false, | | range_key([t2.c2], [t2.n1]), range(B1,MIN ; B1,MAX), | | range_cond([t2.c2 = 'B1']) | | 4 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil) | | access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.n1]), range(MIN ; MAX)always true | +---------------------------------------------------------------------------------------------------------------------+ 25 rows in set (0.004 sec) ``` ```shell [admin@localhost]$ mysql -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_NAME} -A -c Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 74759 Server version: 5.6.25 OceanBase 3.2.4.7 (r107010022023122913-1eba23192ff3365951f818121471f56a49b29a7f) (Built Dec 29 2023 13:55:55) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. ```shell MySQL [test]> CREATE TABLE test_rewrite_geometry_1(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 GEOMETRY SRID 4326, d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.18 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_1_c1 ON test_rewrite_geometry_1 (c1); Query OK, 0 rows affected (0.45 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_1_c2 ON test_rewrite_geometry_1 (c2); Query OK, 0 rows affected (0.44 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.02 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.00 sec) ```shell MySQL [test]> CREATE TABLE test_rewrite_geometry_2(n1 INT PRIMARY KEY, c1 VARCHAR(20),c2 VARCHAR(20),c3 VARCHAR(20) NOT NULL DEFAULT '0', d1 DATETIME DEFAULT current_timestamp); Query OK, 0 rows affected (0.05 sec) ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_2_c1 ON test_rewrite_geometry_2 (c1); Query OK, 0 rows affected (0.44 sec) ``` ```shell MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_2_c2 ON test_rewrite_geometry_2 (c2); Query OK, 0 rows affected (0.43 sec) ```shell MySQL [test]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec) ``` ```shell MySQL [test]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_geometry_1; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | | 2 | A2 | B2 | NULL | 2024-02-07 16:39:34 | | 3 | A3 | B3 | NULL | 2024-02-07 16:39:34 | +----+------+------+------+---------------------+ 3 rows in set (0.01 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_geometry_2; ``` 输出结果如下： ```shell +----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | +----+------+------+----+---------------------+ | 1 | A1 | B1 | 0 | 2024-02-07 16:39:35 | | 2 | A2 | B2 | 0 | 2024-02-07 16:39:35 | +----+------+------+----+---------------------+ 2 rows in set (0.00 sec) ```shell MySQL [test]> SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:35 | | 2 | A2 | B2 | NULL | 2024-02-07 16:39:34 | 2 | A2 | B2 | 0 | 2024-02-07 16:39:35 | | 3 | A3 | B3 | NULL | 2024-02-07 16:39:34 | NULL | NULL | NULL | NULL | NULL | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 3 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:35 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.01 sec) ``` 12. 使用 JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句。 ```shell MySQL [test]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1); ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:35 | | 2 | A2 | B2 | NULL | 2024-02-07 16:39:34 | 2 | A2 | B2 | 0 | 2024-02-07 16:39:35 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 2 rows in set (0.00 sec) ``` ```shell MySQL [test]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +----+------+------+------+---------------------+----+------+------+----+---------------------+ | n1 | c1 | c2 | c3 | d1 | n1 | c1 | c2 | c3 | d1 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ | 1 | A1 | B1 | NULL | 2024-02-07 16:39:34 | 1 | A1 | B1 | 0 | 2024-02-07 16:39:35 | +----+------+------+------+---------------------+----+------+------+----+---------------------+ 1 row in set (0.00 sec) ```shell MySQL [test]> explain SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================= |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------------- |0 |MERGE OUTER JOIN| |2 |93 | |1 | TABLE SCAN |t1 |3 |46 | |2 | TABLE SCAN |t2 |2 |46 | ========================================= Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter([t2.c2 = 'B1']), equal_conds([t1.n1 = t2.n1]), other_conds(nil) 1 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) 2 - output([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), filter(nil), access([t2.n1], [t2.c2], [t2.c1], [t2.c3], [t2.d1]), partitions(p0) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) ```shell MySQL [test]> explain SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1'; ``` 输出结果如下： ```shell +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------------------------------------- |0 |NESTED-LOOP JOIN| |1 |132 | |1 | TABLE SCAN |t2(idx_test_rewrite_geometry_2_c2)|1 |92 | |2 | TABLE SCAN |t1(idx_test_rewrite_geometry_1_c1)|1 |39 | ======================================================================= Outputs & filters: ------------------------------------- 0 - output([t1.n1], [t1.c1], [t1.c2], [t1.c3], [t1.d1], [t2.n1], [t2.c1], [t2.c2], [t2.c3], [t2.d1]), filter(nil), conds(nil), nl_params_([t2.c1]) 1 - output([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), filter(nil), access([t2.c1], [t2.c2], [t2.n1], [t2.c3], [t2.d1]), partitions(p0) 2 - output([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), filter(nil), access([t1.c1], [t1.n1], [t1.c2], [t1.c3], [t1.d1]), partitions(p0) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` #### 总结 Test case 5 测试结果 在 OceanBase 数据库 V4.2.x，V3.2.x 二个版本上的测试结果为：仅驱动表含 GEOMETRY 列，未改写，未走索引以及仅驱动表含 GEOMETRY 列，LEFT JOIN 未改写为 JOIN，未走索引。 ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit013.png) - OceanBase 数据库 V3.2.x 版本： ![image](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240311sqlrewrit014.png) ## 适用版本 OceanBase 数据库 V3.2.x，V4.2.x 版本。 Previous [OceanBase 数据库中快速参数化的含义](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000000107) Next [如何人工控制 CTE 的展开与物化？](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000217858) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线