国产集中式数据库SQL评测 — 逻辑优化

韩锋频道 2025-02-18

236

数据库的逻辑优化，是查询优化过程中的关键阶段，旨在通过调整查询语句的逻辑结构和操作顺序，生成更高效的执行计划，而不涉及底层存储结构或硬件资源。其核心在于利用关系代数的等价变换和查询重写技术，减少计算量和中间结果规模。

【系列文章】

国产集中库SQL能力评测 - 访问路径

国产集中库SQL能力评测 - 表连接

国产集中库SQL能力评测 - 子查询

国产集中库SQL能力评测 - 排序和分组聚合

国产集中库SQL能力评测 - 视图

1. 逻辑优化—谓词重写

谓词重写，又称为等价谓词重写，是指将原执行效率低的谓词改写为效率高的谓词并重写SQL，从而提高SQL的整体执行效率的一种优化手段。其本质是在于不同谓词的处理效率存在差异所导致。常见的谓词重写规则如下

1).优化规则

2).数据库支持情况

3).数据库测评


-- Oracle
-- Like(未改写)
SQL> select * from emp where emp_name like 'emp12%';
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   118 |  3304 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   118 |  3304 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMP_NAME" LIKE 'emp12%')

-- Between And（改写）
SQL> select * from emp where salary between 1000 and 1200;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1799 | 50372 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |  1799 | 50372 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SALARY"<=1200 AND "SALARY">=1000)
   
-- IN-OR/IN-ANY（改写）
SQL> select * from emp where dept_id in (20,30);
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   189 |  5292 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   189 |  5292 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=20 OR "DEPT_ID"=30)
   
-- OR-ANY(未改写)
SQL> select * from emp where dept_id=20 or dept_id=30;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   189 |  5292 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   189 |  5292 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=20 OR "DEPT_ID"=30)
   
--ALL/ANY-MIN/MAX(改写)
SQL> SELECT * FROM emp WHERE salary > ANY (  SELECT salary FROM emp WHERE dept_id=20);
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  8999 |   325K|    32   (7)| 00:00:01 |
|   1 |  MERGE JOIN SEMI    |      |  8999 |   325K|    32   (7)| 00:00:01 |
|   2 |   SORT JOIN         |      | 10000 |   273K|    16   (7)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP  | 10000 |   273K|    15   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE       |      |   101 |   909 |    16   (7)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |   101 |   909 |    15   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access(INTERNAL_FUNCTION("SALARY")>INTERNAL_FUNCTION("SALARY"))
       filter(INTERNAL_FUNCTION("SALARY")>INTERNAL_FUNCTION("SALARY"))
   5 - filter("DEPT_ID"=20)
-- NOT（改写）
SQL> select * from emp where not dept_id!=20;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   101 |  2828 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   101 |  2828 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=20)
-- OR-UNION（未改写）
SQL> select * from emp where dept_id=1 or dept_id=2;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   141 |  3948 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   141 |  3948 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=1 OR "DEPT_ID"=2)
   
-- DM
-- Like(改写)
SQL> explain select * from emp where emp_name like 'emp12%';
1   #NSET2: [1, 111, 163]
2     #PRJT2: [1, 111, 163]; exp_num(6), is_atom(FALSE)
3       #BLKUP2: [1, 111, 163]; IDX_EMP_NAME(EMP)
4         #SSEK2: [1, 111, 163]; scan_type(ASC), IDX_EMP_NAME(EMP), scan_range['emp12','emp13'), is_global(0)

-- Between And（改写）
SQL> explain select * from emp where salary between 1000 and 1200;
1   #NSET2: [1, 1762, 163]
2     #PRJT2: [1, 1762, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 1762, 163]; (EMP.SALARY >= var1 AND EMP.SALARY <= var2) SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)
   
-- IN-OR/IN-ANY（不改写）
SQL> explain select * from emp where dept_id in (20,30);
1   #NSET2: [3, 500, 193]
2     #PRJT2: [3, 500, 193]; exp_num(6), is_atom(FALSE)
3       #HASH2 INNER JOIN: [3, 500, 193];  KEY_NUM(1); KEY(DMTEMPVIEW_889193477.colname=EMP.DEPT_ID) KEY_NULL_EQU(0)
4         #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1)
5         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
   
-- OR-ANY(未改写)
SQL> explain select * from emp where dept_id=20 or dept_id=30;
1   #NSET2: [3, 500, 193]
2     #PRJT2: [3, 500, 193]; exp_num(6), is_atom(FALSE)
3       #HASH2 INNER JOIN: [3, 500, 193];  KEY_NUM(1); KEY(DMTEMPVIEW_889193481.colname=EMP.DEPT_ID) KEY_NULL_EQU(0)
4         #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1)
5         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
   
--ALL/ANY-MIN/MAX(未改写)
SQL> explain SELECT * FROM emp WHERE salary > ANY (  SELECT salary FROM emp WHERE dept_id=20);
1   #NSET2: [5, 500, 60]
2     #PRJT2: [5, 500, 60]; exp_num(6), is_atom(FALSE)
3       #HASH RIGHT SEMI JOIN32: [5, 500, 60]; op any;, key_num(0) join condition(EMP.SALARY > DMTEMPVIEW_889193482.colname)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
5         #PRJT2: [1, 250, 60]; exp_num(1), is_atom(FALSE)
6           #SLCT2: [1, 250, 60]; EMP.DEPT_ID = var1
7             #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)

-- NOT（改写）
SQL> explain select * from emp where not dept_id!=20;
1   #NSET2: [1, 250, 163]
2     #PRJT2: [1, 250, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 250, 163]; EMP.DEPT_ID = var1 SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)

-- OR-UNION（未改写）
SQL> explain select * from emp where dept_id=1 or dept_id=2;
1   #NSET2: [3, 500, 193]
2     #PRJT2: [3, 500, 193]; exp_num(6), is_atom(FALSE)
3       #HASH2 INNER JOIN: [3, 500, 193];  KEY_NUM(1); KEY(DMTEMPVIEW_889193490.colname=EMP.DEPT_ID) KEY_NULL_EQU(0)
4         #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1)
5         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
   
-- MySQL

-- Like(未改写)
mysql> explain select * from emp where emp_name like 'emp12%';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | range | idx_emp_name  | idx_emp_name | 33      | NULL |  111 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`emp_name` like 'emp12%') |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- Between And（未改写）
mysql> explain select * from emp where salary between 1000 and 1200;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | range | idx_emp_salary | idx_emp_salary | 5       | NULL | 1328 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`salary` between 1000 and 1200) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- IN-OR/IN-ANY（未改写）
mysql> explain select * from emp where dept_id in (20,30);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
                                                                                                                                       |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`dept_id` in (20,30)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- OR-ANY(未改写)
mysql> explain select * from emp where dept_id=20 or dept_id=30;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    19.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where ((`testdb`.`emp`.`dept_id` = 20) or (`testdb`.`emp`.`dept_id` = 30)) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--ALL/ANY-MIN/MAX(改写)
mysql> explain SELECT * FROM emp WHERE salary > ANY (  SELECT salary FROM emp WHERE dept_id=20);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    66.67 | Using where |
|  2 | SUBQUERY    | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where <nop>((`testdb`.`emp`.`salary` > (/* select#2 */ select min(`testdb`.`emp`.`salary`) from `testdb`.`emp` where (`testdb`.`emp`.`dept_id` = 20)))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- NOT（改写）
mysql> explain select * from emp where not dept_id!=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
                                                                                                                                 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`dept_id` = 20) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- OR-UNION（未改写）
mysql> explain select * from emp where dept_id=1 or dept_id=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    19.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where ((`testdb`.`emp`.`dept_id` = 1) or (`testdb`.`emp`.`dept_id` = 2)) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- Kingbase

-- Like（未改写）
TEST=# explain select * from emp where emp_name like 'emp12%';
                               QUERY PLAN
------------------------------------------------------------------------
 Gather  (cost=1000.00..16394.33 rows=100 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..15384.33 rows=42 width=42)
         Filter: ((emp_name)::text ~~ 'emp12%'::text)
         
-- Between And (改写)
TEST=# explain select * from emp where salary between 1000 and 1200;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on emp  (cost=4857.22..17791.45 rows=183882 width=42)
   Recheck Cond: ((salary >= '1000'::double precision) AND (salary <= '1200'::double precision))
   ->  Bitmap Index Scan on idx_emp_salary  (cost=0.00..4811.24 rows=183882 width=0)
         Index Cond: ((salary >= '1000'::double precision) AND (salary <= '1200'::double precision))
         
-- IN-OR/IN-ANY（改写为ANY）
TEST=# explain select * from emp where dept_id in (20,30);
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..18284.33 rows=19000 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..15384.33 rows=7917 width=42)
         Filter: (dept_id = ANY ('{20,30}'::numeric[]))

-- OR-ANY（未改写）
TEST=# explain select * from emp where dept_id=20 or dept_id=30;
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..19317.00 rows=18910 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..16426.00 rows=7879 width=42)
         Filter: ((dept_id = '20'::numeric) OR (dept_id = '30'::numeric))
         
 -- ALL/ANY-MIN/MAX(未改写)
TEST=# explain SELECT * FROM emp WHERE salary > ANY (  SELECT salary FROM emp WHERE dept_id=20);
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Gather  (cost=1000.42..1009054.30 rows=333333 width=42)
   Workers Planned: 2
   ->  Nested Loop Semi Join  (cost=0.42..974721.00 rows=138889 width=42)
         ->  Parallel Seq Scan on emp  (cost=0.00..14342.67 rows=416667 width=42)
         ->  Index Scan using idx_emp_salary on emp emp_1  (cost=0.42..6667.14 rows=3156 width=8)
               Index Cond: (salary < emp.salary)
               Filter: (dept_id = '20'::numeric)
               
-- NOT（改写）               
TEST=# explain select * from emp where not dept_id!=20;
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..17331.03 rows=9467 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..15384.33 rows=3945 width=42)
         Filter: (dept_id = '20'::numeric)
         
-- OR-UNION（未改写）
TEST=# explain select * from emp where dept_id=1 or dept_id=2;
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..18889.70 rows=14637 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..16426.00 rows=6099 width=42)
         Filter: ((dept_id = '1'::numeric) OR (dept_id = '2'::numeric))    
         
-- YashanDB
-- Like(未改写)
SQL> explain select * from emp where emp_name like 'emp12%';
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  TABLE ACCESS BY INDEX ROWID   | EMP                  | TESTUSER   |       118|        1( 0)|                                |
|* 2 |   INDEX RANGE SCAN             | IDX_EMP_NAME         | TESTUSER   |        60|        1( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   2 - Predicate : access("EMP"."EMP_NAME" LIKE 'emp12%')
                   filter("EMP"."EMP_NAME" LIKE 'emp12%')

-- Between And（改写）
SQL> explain select * from emp where salary between 1000 and 1200;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  TABLE ACCESS BY INDEX ROWID   | EMP                  | TESTUSER   |       880|        6( 0)|                                |
|* 2 |   INDEX RANGE SCAN             | IDX_EMP_SALARY       | TESTUSER   |       880|        2( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   2 - Predicate : access("EMP"."SALARY" >= 1000 AND "EMP"."SALARY"  <= 1200)
   
-- IN-OR/IN-ANY（不改写）
SQL> explain select * from emp where dept_id in (20,30);
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |       200|       46( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" IN [20, 30])
   
-- OR-ANY(未改写)
SQL> explain select * from emp where dept_id=20 or dept_id=30;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |       152|       47( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" = 20 OR "EMP"."DEPT_ID" = 30)
   
--ALL/ANY-MIN/MAX(改写)
SQL> explain SELECT * FROM emp WHERE salary > ANY (  SELECT salary FROM emp WHERE dept_id=20);
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  SUBQUERY                      | QUERY[1]             |            |          |             |                                |
|  2 |   AGGREGATE                    |                      |            |         1|        1( 0)|                                |
|  3 |    FIRST ROW                   |                      |            |         1|        1( 0)|                                |
|* 4 |     TABLE ACCESS BY INDEX ROWID| EMP                  | TESTUSER   |         1|        1( 0)|                                |
|  5 |      INDEX FULL SCAN (MIN/MAX) | IDX_EMP_SALARY       | TESTUSER   |         1|        1( 0)|                                |
|* 6 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |      3301|       47( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Subquery NDV info - NDV percentage: 0.000000, NDV Expression: ()
   4 - Predicate : filter("EMP"."DEPT_ID" = 20)
   6 - Predicate : filter("EMP"."SALARY" > QUERY[1])
   
-- NOT（改写）
SQL> explain select * from emp where not dept_id!=20;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |       103|       46( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" = 20)
   
-- OR-UNION（不改写）
SQL> explain select * from emp where dept_id=1 or dept_id=2;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |       151|       47( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" = 1 OR "EMP"."DEPT_ID" = 2)

-- Vastbase

-- Like（未改写）
vastbase=> explain select * from emp where emp_name like 'emp12%';
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on emp  (cost=0.00..243.00 rows=1 width=44)
   Filter: ((emp_name)::text ~~ 'emp12%'::text)
         
-- Between And (改写)
vastbase=> explain select * from emp where salary between 1000 and 1200;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on emp  (cost=50.84..196.05 rows=1814 width=44)
   Recheck Cond: ((salary >= 1000::double precision) AND (salary <= 1200::double precision))
   ->  Bitmap Index Scan on idx_emp_salary  (cost=0.00..50.39 rows=1814 width=0)
         Index Cond: ((salary >= 1000::double precision) AND (salary <= 1200::double precision))
                  
-- IN-OR/IN-ANY（改写为ANY）
vastbase=> explain select * from emp where dept_id in (20,30);
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on emp  (cost=0.00..243.00 rows=204 width=44)
   Filter: (dept_id = ANY ('{20,30}'::number[]))

-- OR-ANY（未改写）
vastbase=> explain select * from emp where dept_id=20 or dept_id=30;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on emp  (cost=0.00..268.00 rows=203 width=44)
   Filter: ((dept_id = 20::number) OR (dept_id = 30::number))
         
 -- ALL/ANY-MIN/MAX(未改写)
vastbase=> explain SELECT * FROM emp WHERE salary > ANY (  SELECT salary FROM emp WHERE dept_id=20);
                            QUERY PLAN
------------------------------------------------------------------
 Nested Loop Semi Join  (cost=0.00..9677.06 rows=3333 width=44)
   Join Filter: (testuser.emp.salary > testuser.emp.salary)
   ->  Seq Scan on emp  (cost=0.00..218.00 rows=10000 width=44)
   ->  Materialize  (cost=0.00..243.46 rows=91 width=8)
         ->  Seq Scan on emp  (cost=0.00..243.00 rows=91 width=8)
               Filter: (dept_id = 20::number)
               
-- NOT（改写）               
vastbase=> explain select * from emp where not dept_id!=20;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on emp  (cost=0.00..243.00 rows=91 width=44)
   Filter: (dept_id = 20::number)
         
-- OR-UNION（未改写）
vastbase=> explain select * from emp where dept_id=1 or dept_id=2;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on emp  (cost=0.00..268.00 rows=196 width=44)
   Filter: ((dept_id = 1::number) OR (dept_id = 2::number))

2. 逻辑优化—条件化简

条件化简，是指将语句中的条件子句部分优化，选择执行代价更小或更容易利用到索引、约束等的情况。从形式上看，条件子句可能由一元操作符、二元操作符、多元操作符组成。对于一元操作符而言，如果是相等运算，则比其他运算更易利用索引，且元组更少(选择率低的可能性更大)；如果是范围运算，则应尽可能利用索引；如果能利用约束，则可以简化一些表达式，如非空约束有助于在条件表达式中判断对应列所在的表达式的值；如果表达式可以求值，则先求值有利于整个条件子句的计算。条件子句优化的本质是:尽早推知运算的结果以有利于对元组数进行计算，使得根据代价估算模型(元组数是重要的计算依据)可以准确地推演出最优查询执行计划。

1).优化规则

2).数据库支持情况

3).数据库测评


-- Oracle
-- Having 并入 Where（不支持）
SQL> SELECT * FROM emp where dept_id=100 HAVING salary > 5000;
SELECT * FROM emp where dept_id=100 HAVING salary > 5000
                                           *
ERROR at line 1:
ORA-00979: not a GROUP BY expression
--去除冗余括号
SQL> SELECT * FROM emp WHERE ((dept_id=2));
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   102 |  2856 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   102 |  2856 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=2)
   
-- 常量传递（支持）
SQL> select * from emp where dept_id=10 and emp_id=dept_id;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    28 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=10)
   2 - access("EMP_ID"=10)
   
-- 消除死码（支持）
SQL> select * from emp where (0>1) or dept_id=10;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    98 |  2744 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    98 |  2744 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=10)
   
-- 表达式计算（支持）
SQL> select * from emp where dept_id=1+2;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   107 |  2996 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   107 |  2996 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"=3)
   
-- 等式变换（不支持）
SQL> select * from emp where -dept_id=-10;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  2800 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   100 |  2800 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((-"DEPT_ID")=(-10))
   
-- 不等式转换（支持）
SQL> select * from emp where salary >1100 and salary>2000;
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    28 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    28 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP_SALARY |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SALARY">2000)
   
--谓词传递闭包（支持）
SQL> select * from emp where emp_id>dept_id and dept_id>900;
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    28 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    28 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP_DEPTID |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMP_ID">900 AND "EMP_ID">"DEPT_ID")
   2 - access("DEPT_ID">900)
   
-- 等价合取范式（支持）
SQL> select * from emp where (0>1) and dept_id=10;
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    28 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    98 |  2744 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)
   2 - filter("DEPT_ID"=10)
   
-- AND 操作符交换（不支持）
SQL> select * from emp where dept_id+emp_id=1000 and salary>900;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    90 |  2520 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    90 |  2520 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPT_ID"+"EMP_ID"=1000 AND "SALARY">900)
   
-- DM
-- Having 并入 Where（不支持）
SQL> explain  SELECT * FROM emp where dept_id=100 HAVING salary > 5000;
explain  SELECT * FROM emp where dept_id=100 HAVING salary > 5000;
[-4028]:Error in line: 1
Invalid having item.

--去除冗余括号（支持）
SQL> explain SELECT * FROM emp WHERE ((dept_id=2));
1   #NSET2: [1, 250, 163]
2     #PRJT2: [1, 250, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 250, 163]; EMP.DEPT_ID = var1 SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)
   
-- 常量传递（不支持）
SQL> explain select * from emp where dept_id=10 and emp_id=dept_id;
1   #NSET2: [1, 1, 163]
2     #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 1, 163]; EMP.DEPT_ID = var1
4         #BLKUP2: [1, 1, 163]; INDEX33555485(EMP)
5           #SSEK2: [1, 1, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range[exp_cast(10),exp_cast(10)], is_global(0)
   
-- 消除死码（支持）
SQL> explain select * from emp where (0>1) or dept_id=10;
1   #NSET2: [1, 250, 163]
2     #PRJT2: [1, 250, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 250, 163]; EMP.DEPT_ID = var1 SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)
   
-- 表达式计算（支持）
SQL> explain select * from emp where dept_id=1+2;
1   #NSET2: [1, 250, 163]
2     #PRJT2: [1, 250, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 250, 163]; EMP.DEPT_ID = var2 SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)
   
-- 等式变换（不支持）
SQL> explain select * from emp where -dept_id=-10;
1   #NSET2: [1, 250, 163]
2     #PRJT2: [1, 250, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 250, 163]; -EMP.DEPT_ID = var2 SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)
   
-- 不等式转换（支持）
SQL> explain select * from emp where salary >1100 and salary>2000;
1   #NSET2: [1, 1, 163]
2     #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)
3       #BLKUP2: [1, 1, 163]; IDX_EMP_SALARY(EMP)
4         #SSEK2: [1, 1, 163]; scan_type(ASC), IDX_EMP_SALARY(EMP), scan_range(exp_cast(2000),max], is_global(0)

--谓词传递闭包（不支持）
SQL> explain select * from emp where emp_id>dept_id and dept_id>900;
1   #NSET2: [1, 25, 163]
2     #PRJT2: [1, 25, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 25, 163]; (EMP.DEPT_ID > var1 AND EMP.EMP_ID > EMP.DEPT_ID) SLCT_PUSHDOWN(TRUE)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)
   
-- 等价合取范式（支持）
SQL> explain select * from emp where (0>1) and dept_id=10;
1   #NSET2: [1, 1, 163]
2     #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 1, 163];  FALSE
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
   
-- AND 操作符交换（支持）
SQL> explain select * from emp where dept_id+emp_id=1000 and salary>900;
1   #NSET2: [1, 225, 163]
2     #PRJT2: [1, 225, 163]; exp_num(6), is_atom(FALSE)
3       #SLCT2: [1, 225, 163]; (EMP.SALARY > var1 AND EMP.DEPT_ID+EMP.EMP_ID = var2)
4         #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)

-- MySQL

-- Having 并入 Where（未改写）
mysql> explain SELECT * FROM emp where dept_id=100 HAVING salary > 5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`dept_id` = 100) having (`testdb`.`emp`.`salary` > 5000) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--去除冗余括号
mysql> explain SELECT * FROM emp WHERE ((dept_id=2));
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                        |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`dept_id` = 2) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- 常量传递（支持）
mysql> explain select * from emp where dept_id=10 and emp_id=dept_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra
         |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+

-- 消除死码（支持）
mysql> explain select * from emp where (0>1) or dept_id=10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`dept_id` = 10) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- 表达式计算（支持）
mysql> explain select * from emp where dept_id=1+2;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (`testdb`.`emp`.`dept_id` = <cache>((1 + 2))) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- 等式变换（不支持）
mysql> explain select * from emp where -dept_id=-10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (-(`testdb`.`emp`.`dept_id`) = <cache>(-(10))) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- 不等式转换（不支持）
mysql> explain select * from emp where salary >1100 and salary>2000;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | range | idx_emp_salary | idx_emp_salary | 5       | NULL |  421 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where ((`testdb`.`emp`.`salary` > 1100) and (`testdb`.`emp`.`salary` > 2000)) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

--谓词传递闭包（不支持）
mysql> explain select * from emp where emp_id>dept_id and dept_id>900;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10117 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where ((`testdb`.`emp`.`emp_id` > `testdb`.`emp`.`dept_id`) and (`testdb`.`emp`.`dept_id` > 900)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- 等价合取范式（支持）
mysql> explain select * from emp where (0>1) and dept_id=10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where false |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- AND 操作符交换（支持）
mysql> explain select * from emp where dept_id+emp_id=1000 and salary>900;
+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_emp_salary | NULL | NULL    | NULL | 10117 |    88.96 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                       |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | * select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where (((`testdb`.`emp`.`dept_id` + `testdb`.`emp`.`emp_id`) = 1000) and (`testdb`.`emp`.`salary` > 900)) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- KingBase
-- Having 并入 Where（不支持）
TEST=# explain SELECT * FROM emp where dept_id=100 HAVING salary > 5000;
ERROR:  column "emp.emp_id" must appear in the GROUP BY clause or be used in an aggregate function

--去除冗余括号
TEST=# explain SELECT * FROM emp WHERE ((dept_id=2));
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..17401.03 rows=10167 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..15384.33 rows=4236 width=42)
         Filter: (dept_id = '2'::numeric)

-- 常量传递（支持）
TEST=# explain select * from emp where dept_id=10 and emp_id=dept_id;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using EMP_PK on emp  (cost=0.42..8.45 rows=1 width=42)
   Index Cond: (emp_id = '10'::numeric)
   Filter: (dept_id = '10'::numeric)
   
-- 消除死码（支持）
TEST=# explain select * from emp where (0>1) or dept_id=10;
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..17377.63 rows=9933 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..15384.33 rows=4139 width=42)
         Filter: (dept_id = '10'::numeric)

-- 表达式计算（支持）
TEST=# explain select * from emp where dept_id=1+2;
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..17447.63 rows=10633 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..15384.33 rows=4430 width=42)
         Filter: (dept_id = '3'::numeric)

-- 等式变换（不支持）
TEST=# explain select * from emp where -dept_id=-10;
                                QUERY PLAN
--------------------------------------------------------------------------
 Gather  (cost=1000.00..17926.00 rows=5000 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..16426.00 rows=2083 width=42)
         Filter: ((- dept_id) = '-10'::numeric)

-- 不等式转换（支持）
TEST=#  explain select * from emp where salary >1100 and salary>2000;
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_emp_salary on emp  (cost=0.42..8.44 rows=1 width=42)
   Index Cond: (salary > '2000'::double precision)

-- 谓词传递闭包（不支持）
TEST=# explain select * from emp where emp_id>dept_id and dept_id>900;
                               QUERY PLAN
------------------------------------------------------------------------
 Gather  (cost=1000.00..17429.00 rows=30 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..16426.00 rows=12 width=42)
         Filter: ((emp_id > dept_id) AND (dept_id > '900'::numeric))

-- 等价合取范式（支持）
TEST=# explain select * from emp where (0>1) and dept_id=10;
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false

-- AND 操作符交换（支持）
TEST=# explain select * from emp where dept_id+emp_id=1000 and salary>900;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..18919.77 rows=4521 width=42)
   Workers Planned: 2
   ->  Parallel Seq Scan on emp  (cost=0.00..17467.67 rows=1884 width=42)
         Filter: ((salary > '900'::double precision) AND ((dept_id + emp_id) = '1000'::numeric))
         
-- YashanDB
-- Having 并入 Where（不支持）
SQL> explain SELECT * FROM emp where dept_id=100 HAVING salary > 5000
[1:52]YAS-04316 not a single-group group function

--去除冗余括号（支持）
SQL> explain SELECT * FROM emp WHERE ((dept_id=2));
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |        89|       46( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" = 2)
   
-- 常量传递（不支持）
SQL> explain select * from emp where dept_id=10 and emp_id=dept_id;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |         1|       47( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."EMP_ID" = "EMP"."DEPT_ID" AND 10 = "EMP"."DEPT_ID")
   
-- 消除死码（支持）
SQL> explain select * from emp where (0>1) or dept_id=10;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |        99|       46( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" = 10)
   
-- 表达式计算（支持）
SQL> explain select * from emp where dept_id=1+2;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |       114|       46( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" = 3)
   
-- 等式变换（支持）
SQL> explain select * from emp where -dept_id=-10;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |        99|       46( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" = 10)
   
-- 不等式转换（支持）
SQL> explain select * from emp where salary >1100 and salary>2000;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  TABLE ACCESS BY INDEX ROWID   | EMP                  | TESTUSER   |         1|        1( 0)|                                |
|* 2 |   INDEX RANGE SCAN             | IDX_EMP_SALARY       | TESTUSER   |         1|        1( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   2 - Predicate : access("EMP"."SALARY" > 2000)
     
--谓词传递闭包（支持）
SQL> explain select * from emp where emp_id>dept_id and dept_id>900;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |         1|       47( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID" > 900 AND "EMP"."EMP_ID" > 900 AND "EMP"."EMP_ID" > "EMP"."DEPT_ID")
   
-- 等价合取范式（支持）
SQL> explain select * from emp where (0>1) and dept_id=10;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  RESULT                        |                      |            |          |        1( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter(FALSE)
   
-- AND 操作符交换（不支持）
SQL> explain select * from emp where dept_id+emp_id=1000 and salary>900;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |        10|       47( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
Operation Information (identified by operation id):
---------------------------------------------------
   1 - Predicate : filter("EMP"."DEPT_ID"+"EMP"."EMP_ID" = 1000 AND "EMP"."SALARY" > 900)
   
-- Vastbase
-- Having 并入 Where（不支持）
vastbase=> explain SELECT * FROM emp where dept_id=100 HAVING salary > 5000;
ERROR:  column "emp.emp_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: explain SELECT * FROM emp where dept_id=100 HAVING salary > ...

--去除冗余括号（支持）
vastbase=> explain SELECT * FROM emp WHERE ((dept_id=2));
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on emp  (cost=0.00..243.00 rows=106 width=44)
   Filter: (dept_id = 2::number)

-- 常量传递（支持）
vastbase=> explain select * from emp where dept_id=10 and emp_id=dept_id;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using emp_pk on emp  (cost=0.00..8.27 rows=1 width=44)
   Index Cond: (emp_id = 10::number)
   Filter: (dept_id = 10::number)
   
-- 消除死码（支持）
vastbase=> explain select * from emp where (0>1) or dept_id=10;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on emp  (cost=0.00..243.00 rows=91 width=44)
   Filter: (dept_id = 10::number)

-- 表达式计算（支持）
vastbase=> explain select * from emp where dept_id=1+2;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on emp  (cost=0.00..243.00 rows=91 width=44)
   Filter: (dept_id = 3::number)

-- 等式变换（不支持）
vastbase=> explain select * from emp where -dept_id=-10;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on emp  (cost=0.00..268.00 rows=50 width=44)
   Filter: ((- dept_id) = (-10)::number)

-- 不等式转换（不支持）
vastbase=> explain select * from emp where salary >1100 and salary>2000;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using idx_emp_salary on emp  (cost=0.00..8.27 rows=1 width=44)
   Index Cond: ((salary > 1100::double precision) AND (salary > 2000::double precision))

-- 谓词传递闭包（不支持）
vastbase=> explain select * from emp where emp_id>dept_id and dept_id>900;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on emp  (cost=0.00..268.00 rows=1 width=44)
   Filter: ((emp_id > dept_id) AND (dept_id > 900::number))

-- 等价合取范式（支持）
vastbase=> explain select * from emp where (0>1) and dept_id=10;
                         QUERY PLAN
------------------------------------------------------------
 Result  (cost=0.00..218.00 rows=1 width=44)
   One-Time Filter: false
   ->  Seq Scan on emp  (cost=0.00..218.00 rows=1 width=44)

-- AND 操作符交换（支持）
vastbase=> explain select * from emp where dept_id+emp_id=1000 and salary>900;
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Seq Scan on emp  (cost=0.00..293.00 rows=45 width=44)
   Filter: ((salary > 900::double precision) AND ((dept_id + emp_id) = 1000::number))

3. 逻辑优化—连接消除

在多表连接的过程中，查询优化器可以找出多表连接的最优查询执行计划，这意味着多个表的最优的连接次序被确定。如果根据表的连接次序确定析取条件的优先判断次序，存在加速判断的可能(处于表达式后面的条件可能不用判断了)。

1).优化规则

2).数据库支持情况

3).数据库测评

受篇幅限制，略去操作部分。

4. 逻辑优化—索引优化

如果语句中不仅包含有Select（选择）、Project（投影）、Join（连接）三种基础操作，还有其他类操作（如分组等）。此时，优化器是可以根据索引进行一定的优化。

1).优化规则

2).数据库支持情况

3).数据库测评

受篇幅限制，略去操作部分。

文章转载自韩锋频道，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

国产集中式数据库SQL评测 — 逻辑优化

评论