本文介绍 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。
将 LEFT JOIN 改为 JOIN 后执行时间为 57.34 ms，
注意
由于 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 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_enum_1。
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)在 test_rewrite_enum_1 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_1_c1 ON test_rewrite_enum_1 (c1); Query OK, 0 rows affected (1.021 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_1_c2 ON test_rewrite_enum_1 (c2); Query OK, 0 rows affected (1.019 sec)向 test_rewrite_enum_1 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.030 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.005 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)创建测试表 test_rewrite_enum_2。
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)在 test_rewrite_enum_2 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_2_c1 ON test_rewrite_enum_2 (c1); Query OK, 0 rows affected (1.250 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_2_c2 ON test_rewrite_enum_2 (c2); Query OK, 0 rows affected (1.041 sec)向 test_rewrite_enum_2 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.059 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)查询表 test_rewrite_enum_1，test_rewrite_enum_2。
MySQL [db1]> SELECT * FROM test_rewrite_enum_1;输出结果如下：
+----+------+------+------+---------------------+ | 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)MySQL [db1]> SELECT * FROM test_rewrite_enum_2;输出结果如下：
+----+------+------+------+---------------------+ | 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)使用 LEFT JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)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';输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)使用 JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)MySQL [db1]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+----------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.创建测试表 test_rewrite_enum_1。
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)在 test_rewrite_enum_1 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_enum_1_c1 ON test_rewrite_enum_1 (c1); Query OK, 0 rows affected (0.62 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_enum_1_c2 ON test_rewrite_enum_1 (c2); Query OK, 0 rows affected (0.44 sec)向 test_rewrite_enum_1 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.00 sec)MySQL [test]> INSERT INTO test_rewrite_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)创建测试表 test_rewrite_enum_2。
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)在 test_rewrite_enum_2 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_enum_2_c1 ON test_rewrite_enum_2 (c1); Query OK, 0 rows affected (0.54 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_enum_2_c2 ON test_rewrite_enum_2 (c2); Query OK, 0 rows affected (0.55 sec)向 test_rewrite_enum_2 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.03 sec)MySQL [test]> INSERT INTO test_rewrite_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)查询表 test_rewrite_enum_1，test_rewrite_enum_2。
MySQL [test]> SELECT * FROM test_rewrite_enum_1;输出结果如下：
+----+------+------+------+---------------------+ | 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)MySQL [test]> SELECT * FROM test_rewrite_enum_2;输出结果如下：
+----+------+------+------+---------------------+ | 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)使用 LEFT JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_enum_1 t1 LEFT JOIN test_rewrite_enum_2 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)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';输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)使用 JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)MySQL [test]> SELECT * FROM test_rewrite_enum_1 t1 JOIN test_rewrite_enum_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+------+---------------------+----+------+------+------+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_enum_1 与 test_rewrite_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 | =================================================================== 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), 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 版本：
OceanBase 数据库 V3.2.x 版本：
Test case 2 详细测试输出
OceanBase 数据库 V4.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_no_enum_1。
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);在 test_rewrite_no_enum_1 表上创建索引。
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)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)向 test_rewrite_no_enum_1 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.036 sec)MySQL [db1]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec)MySQL [db1]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)创建测试表 test_rewrite_no_enum_2。
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)在 test_rewrite_no_enum_2 表上创建索引。
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)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)向 est_rewrite_no_enum_2 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.039 sec)MySQL [db1]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)查询表 test_rewrite_no_enum_1，test_rewrite_no_enum_2。
MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1;输出结果如下：
+----+------+------+----+---------------------+ | 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)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)使用 LEFT JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)使用 JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+--------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+--------------------------------------------------------------------------------------------------------------------------------+ | 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)
OceanBase 数据库 V3.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_no_enum_1。
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);在 test_rewrite_no_enum_1 表上创建索引。
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)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)向 test_rewrite_no_enum_1 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.00 sec)MySQL [test]> INSERT INTO test_rewrite_no_enum_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)创建测试表 test_rewrite_no_enum_2。
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)在 test_rewrite_no_enum_2 表上创建索引。
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)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)向 est_rewrite_no_enum_2 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.02 sec)MySQL [test]> INSERT INTO test_rewrite_no_enum_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)查询表 test_rewrite_no_enum_1，test_rewrite_no_enum_2。
MySQL [test]> SELECT * FROM test_rewrite_no_enum_1;输出结果如下：
+----+------+------+----+---------------------+ | 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)MySQL [test]> 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.00 sec)使用 LEFT JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_no_enum_1 t1 LEFT JOIN test_rewrite_no_enum_2 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)使用 JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_no_enum_1 t1 JOIN test_rewrite_no_enum_2 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+----+---------------------+----+------+------+----+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句的执行计划。
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';输出结果如下：
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 版本
OceanBase 数据库 V3.2.x 版本
Test case 3 详细测试输出
OceanBase 数据库 V4.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_enum_11。
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);在 test_rewrite_enum_11 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_11_c1 ON test_rewrite_enum_11 (c1); Query OK, 0 rows affected (1.238 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_11_c2 ON test_rewrite_enum_11 (c2); Query OK, 0 rows affected (1.106 sec)向 test_rewrite_enum_11 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.055 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.005 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.008 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)创建测试表 test_rewrite_enum_12。
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)在 test_rewrite_enum_12 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_12_c1 ON test_rewrite_enum_12 (c1); Query OK, 0 rows affected (1.122 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_12_c2 ON test_rewrite_enum_12 (c2); Query OK, 0 rows affected (1.064 sec)向 test_rewrite_enum_12 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.020 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.005 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.000 sec)查询表 test_rewrite_enum_11，test_rewrite_enum_12。
MySQL [db1]> SELECT * FROM test_rewrite_enum_11;输出结果如下：
+----+------+------+------+---------------------+ | 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)MySQL [db1]> SELECT * FROM test_rewrite_enum_12;输出结果如下：
+----+------+------+----+---------------------+ | 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)使用 LEFT JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)MySQL [db1]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句的执行计划。
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';输出结果如下：
+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句的执行计划。
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';输出结果如下：
+-------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)
OceanBase 数据库 V3.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_enum_11。
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)在 test_rewrite_enum_11 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_enum_11_c1 ON test_rewrite_enum_11 (c1); Query OK, 0 rows affected (0.44 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_enum_11_c2 ON test_rewrite_enum_11 (c2); Query OK, 0 rows affected (0.43 sec)向 test_rewrite_enum_11 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_enum_11(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.00 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)创建测试表 test_rewrite_enum_12。
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)在 test_rewrite_enum_12 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_enum_12_c1 ON test_rewrite_enum_12 (c1); Query OK, 0 rows affected (0.54 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_enum_12_c2 ON test_rewrite_enum_12 (c2); Query OK, 0 rows affected (0.44 sec)向 test_rewrite_enum_12 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_enum_12(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)查询表 test_rewrite_enum_11，test_rewrite_enum_12。
MySQL [test]> SELECT * FROM test_rewrite_enum_11;输出结果如下：
+----+------+------+------+---------------------+ | 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)MySQL [test]> SELECT * FROM test_rewrite_enum_12;输出结果如下：
+----+------+------+----+---------------------+ | 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)使用 LEFT JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_enum_11 t1 LEFT JOIN test_rewrite_enum_12 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)MySQL [test]> SELECT * FROM test_rewrite_enum_11 t1 JOIN test_rewrite_enum_12 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句的执行计划。
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';输出结果如下：
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_enum_11 与 test_rewrite_enum_12 表的查询语句的执行计划。
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';输出结果如下：
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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，未走索引。
具体详细测试输出如下。
OceanBase 数据库 V4.2.x 版本
OceanBase 数据库 V3.2.x 版本
Test case 4 详细测试输出
OceanBase 数据库 V4.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_enum_21。
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);在 test_rewrite_enum_21 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_21_c1 ON test_rewrite_enum_21 (c1); Query OK, 0 rows affected (0.987 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_21_c2 ON test_rewrite_enum_21 (c2); Query OK, 0 rows affected (0.949 sec)向 test_rewrite_enum_21 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.027 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.003 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)创建测试表 test_rewrite_enum_22。
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)在 test_rewrite_enum_22 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_22_c1 ON test_rewrite_enum_22 (c1); Query OK, 0 rows affected (1.066 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_enum_22_c2 ON test_rewrite_enum_22 (c2); Query OK, 0 rows affected (1.084 sec)向 test_rewrite_enum_22 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.009 sec)MySQL [db1]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.007 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)查询表 test_rewrite_enum_21，test_rewrite_enum_22。
MySQL [db1]> 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.003 sec)MySQL [db1]> 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.058 sec)使用 LEFT JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)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';输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)使用 JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)MySQL [db1]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 ttest_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句的执行计划。
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';输出结果如下：
+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句的执行计划。
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';输出结果如下：
+-------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)
OceanBase 数据库 V3.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_enum_21。
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)在 test_rewrite_enum_21 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_enum_21_c1 ON test_rewrite_enum_21 (c1); Query OK, 0 rows affected (0.54 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_enum_21_c2 ON test_rewrite_enum_21 (c2); Query OK, 0 rows affected (0.44 sec)向 test_rewrite_enum_21 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_enum_21(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)创建测试表 test_rewrite_enum_22。
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)在 est_rewrite_enum_22 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_enum_22_c1 ON test_rewrite_enum_22 (c1); Query OK, 0 rows affected (0.44 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_enum_22_c2 ON test_rewrite_enum_22 (c2); Query OK, 0 rows affected (0.54 sec)向 test_rewrite_enum_22 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_enum_22(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)查询表 test_rewrite_enum_21，test_rewrite_enum_22。
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)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)使用 LEFT JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_enum_21 t1 LEFT JOIN test_rewrite_enum_22 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)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';输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)使用 JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)MySQL [test]> SELECT * FROM test_rewrite_enum_21 t1 JOIN test_rewrite_enum_22 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+----+---------------------+----+------+------+------+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_enum_21 与 test_rewrite_enum_22 表的查询语句的执行计划。
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';输出结果如下：
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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], [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，未走索引。
具体详细测试输出如下。
OceanBase 数据库 V4.2.x 版本：
OceanBase 数据库 V3.2.x 版本：
Test case 5 详细测试输出
OceanBase 数据库 V4.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_geometry_1。
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)在 test_rewrite_geometry_1 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_1_c1 ON test_rewrite_geometry_1 (c1); Query OK, 0 rows affected (1.078 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_1_c2 ON test_rewrite_geometry_1 (c2); Query OK, 0 rows affected (1.084 sec)向 test_rewrite_geometry_1 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.012 sec)MySQL [db1]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec)MySQL [db1]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.004 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)创建测试表 test_rewrite_geometry_2。
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)在 test_rewrite_geometry_2 表上创建索引。
MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_2_c1 ON test_rewrite_geometry_2 (c1); Query OK, 0 rows affected (0.861 sec)MySQL [db1]> CREATE INDEX idx_test_rewrite_geometry_2_c2 ON test_rewrite_geometry_2 (c2); Query OK, 0 rows affected (0.977 sec)向 test_rewrite_geometry_2 表插入数据。
MySQL [db1]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.047 sec)MySQL [db1]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.004 sec)提交。
MySQL [db1]> COMMIT; Query OK, 0 rows affected (0.001 sec)查询表 test_rewrite_geometry_1，test_rewrite_geometry_2。
MySQL [db1]> SELECT * FROM test_rewrite_geometry_1;输出结果如下：
+----+------+------+------+---------------------+ | 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)MySQL [db1]> SELECT * FROM test_rewrite_geometry_2;输出结果如下：
+----+------+------+----+---------------------+ | 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)使用 LEFT JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 JOIN 关联 test_rewrite_no_enum_1 与 test_rewrite_no_enum_2 表的查询语句。
MySQL [db1]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)MySQL [db1]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句的执行计划。
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';输出结果如下：
+--------------------------------------------------------------------------------------------------------------------------------+ | 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)使用 explain 展示使用 JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句的执行计划。
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';输出结果如下：
+---------------------------------------------------------------------------------------------------------------------+ | 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)
OceanBase 数据库 V3.2.x 版本上的模拟测试示例
登录 OceanBase 数据库 MySQL 租户。
[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.创建测试表 test_rewrite_geometry_1。
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)在 test_rewrite_geometry_1 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_1_c1 ON test_rewrite_geometry_1 (c1); Query OK, 0 rows affected (0.45 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_1_c2 ON test_rewrite_geometry_1 (c2); Query OK, 0 rows affected (0.44 sec)向 test_rewrite_geometry_1 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.02 sec)MySQL [test]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_geometry_1(n1,c1,c2) VALUES(3,'A3','B3'); Query OK, 1 row affected (0.00 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)创建测试表 test_rewrite_geometry_2。
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)在 test_rewrite_geometry_2 表上创建索引。
MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_2_c1 ON test_rewrite_geometry_2 (c1); Query OK, 0 rows affected (0.44 sec)MySQL [test]> CREATE INDEX idx_test_rewrite_geometry_2_c2 ON test_rewrite_geometry_2 (c2); Query OK, 0 rows affected (0.43 sec)向 test_rewrite_geometry_2 表插入数据。
MySQL [test]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(1,'A1','B1'); Query OK, 1 row affected (0.01 sec)MySQL [test]> INSERT INTO test_rewrite_geometry_2(n1,c1,c2) VALUES(2,'A2','B2'); Query OK, 1 row affected (0.01 sec)提交。
MySQL [test]> COMMIT; Query OK, 0 rows affected (0.00 sec)查询表 test_rewrite_geometry_1，test_rewrite_geometry_2。
MySQL [test]> SELECT * FROM test_rewrite_geometry_1;输出结果如下：
+----+------+------+------+---------------------+ | 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)MySQL [test]> SELECT * FROM test_rewrite_geometry_2;输出结果如下：
+----+------+------+----+---------------------+ | 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)使用 LEFT JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_geometry_1 t1 LEFT JOIN test_rewrite_geometry_2 t2 ON (t1.n1 = t2.n1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)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';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句。
MySQL [test]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1);输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)MySQL [test]> SELECT * FROM test_rewrite_geometry_1 t1 JOIN test_rewrite_geometry_2 t2 ON (t1.c1 = t2.c1) WHERE t2.c2 = 'B1';输出结果如下：
+----+------+------+------+---------------------+----+------+------+----+---------------------+ | 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)使用 explain 展示使用 LEFT JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句的执行计划。
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';输出结果如下：
| 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)使用 explain 展示使用 JOIN 关联 test_rewrite_geometry_1 与 test_rewrite_geometry_2 表的查询语句的执行计划。
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';输出结果如下：
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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，未走索引。
具体详细测试输出如下。
OceanBase 数据库 V4.2.x 版本：
OceanBase 数据库 V3.2.x 版本：
适用版本
OceanBase 数据库 V3.2.x，V4.2.x 版本。
