数据库的逻辑优化，是查询优化过程中的关键阶段，旨在通过调整查询语句的逻辑结构和操作顺序，生成更高效的执行计划，而不涉及底层存储结构或硬件资源。其核心在于利用关系代数的等价变换和查询重写技术，减少计算量和中间结果规模。
1. 逻辑优化—谓词重写
谓词重写，又称为等价谓词重写，是指将原执行效率低的谓词改写为效率高的谓词并重写SQL，从而提高SQL的整体执行效率的一种优化手段。其本质是在于不同谓词的处理效率存在差异所导致。常见的谓词重写规则如下
1).优化规则
1.png
2).数据库支持情况
2.png
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 = var17 #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).优化规则
3.png
2).数据库支持情况
4.png
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: 1Invalid 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 = var14 #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]; FALSE4 #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 functionLINE 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).优化规则
5.png
2).数据库支持情况
6.png
3).数据库测评
受篇幅限制，略去操作部分。
4. 逻辑优化—索引优化
如果语句中不仅包含有Select（选择）、Project（投影）、Join（连接） 三种基础操作，还有其他类操作（如分组等）。此时，优化器是可以根据索引进行一定的优化。
1).优化规则
7.png
2).数据库支持情况
8.png
3).数据库测评
受篇幅限制，略去操作部分。
