最近优化了不少SQL，简单的SQL不好意思发出来了，复杂的很考验逻辑思想，但是又不想分享出来，怕被人抄袭思路?。
本次内容仅仅分享不同数据库优化器性能比较。
一条复杂的标量子查询 SQL(未优化) 作为引子（金融行业，SQL需要保密），单纯测试标量子查询CBO算法性能：
ORACLE（11.2.0.4）数据库执行时间+执行计划：
"SQL_ID 3t2mg3b91ygqx, child number 0" ------------------------------------- Plan hash value: 670471803 ---------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 20 |00:00:01.38 | 2491K| 4946 | | | | | 1 | TABLE ACCESS BY INDEX ROWID | MEMMEMMEM | 189K| 1 | 189K|00:00:00.13 | 379K| 0 | | | | |* 2 | INDEX UNIQUE SCAN | SYS_C0011239 | 189K| 1 | 189K|00:00:00.07 | 189K| 0 | | | | | 3 | TABLE ACCESS BY INDEX ROWID | REGSEATREGSEAT | 189K| 1 | 189K|00:00:00.12 | 379K| 0 | | | | |* 4 | INDEX UNIQUE SCAN | SYS_C0011240 | 189K| 1 | 189K|00:00:00.07 | 189K| 0 | | | | | 5 | TABLE ACCESS BY INDEX ROWID | RCLRCLRCLRCL | 189K| 1 | 189K|00:00:00.12 | 379K| 0 | | | | |* 6 | INDEX UNIQUE SCAN | SYS_C0011241 | 189K| 1 | 189K|00:00:00.06 | 189K| 0 | | | | | 7 | TABLE ACCESS BY INDEX ROWID | MEMMEMMEM | 189K| 1 | 189K|00:00:00.12 | 379K| 0 | | | | |* 8 | INDEX UNIQUE SCAN | SYS_C0011239 | 189K| 1 | 189K|00:00:00.06 | 189K| 0 | | | | | 9 | TABLE ACCESS BY INDEX ROWID | REGSEATREGSEAT | 189K| 1 | 189K|00:00:00.12 | 379K| 0 | | | | |* 10 | INDEX UNIQUE SCAN | SYS_C0011240 | 189K| 1 | 189K|00:00:00.06 | 189K| 0 | | | | | 11 | TABLE ACCESS BY INDEX ROWID | RCLRCLRCLRCL | 189K| 1 | 189K|00:00:00.12 | 379K| 0 | | | | |* 12 | INDEX UNIQUE SCAN | SYS_C0011241 | 189K| 1 | 189K|00:00:00.06 | 189K| 0 | | | | | 13 | TABLE ACCESS BY INDEX ROWID | PARVRTYPARVRTY | 63167 | 1 | 63165 |00:00:00.05 | 126K| 0 | | | | |* 14 | INDEX UNIQUE SCAN | SYS_C0011242 | 63167 | 1 | 63165 |00:00:00.03 | 63167 | 0 | | | | | 15 | SORT AGGREGATE | | 133K| 1 | 133K|00:00:00.12 | 82856 | 0 | | | | | 16 | TABLE ACCESS BY INDEX ROWID| REGLEASERETURNAPPVRTY | 133K| 1 | 66471 |00:00:00.10 | 82856 | 0 | | | | |* 17 | INDEX RANGE SCAN | IDX_RETURN_APP_NO_VRTY | 133K| 1 | 66471 |00:00:00.05 | 16533 | 0 | | | | |* 18 | VIEW | | 1 | 200K| 20 |00:00:01.38 | 2491K| 4946 | | | | | 19 | COUNT | | 1 | | 280K|00:00:01.49 | 2491K| 4946 | | | | | 20 | VIEW | | 1 | 200K| 280K|00:00:01.48 | 2491K| 4946 | | | | | 21 | SORT UNIQUE | | 1 | 200K| 280K|00:00:01.46 | 2491K| 4946 | 92M| 3299K| 82M (0)| |* 22 | HASH JOIN RIGHT OUTER | | 1 | 200K| 280K|00:00:00.08 | 4959 | 4946 | 7999K| 3415K| 8824K (0)| | 23 | TABLE ACCESS FULL | REGLEASERETURNAPPVRTY | 1 | 100K| 100K|00:00:00.01 | 486 | 480 | | | | | 24 | TABLE ACCESS FULL | SERETURNAPPREGGOLDLEA | 1 | 200K| 200K|00:00:00.01 | 4473 | 4466 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 2 - access(""M1"".""F_MEMBER_ID""=:B1)" " 4 - access(""S1"".""F_SEAT_ID""=:B1)" " 6 - access(""C1"".""F_CLIENT_ID""=:B1)" " 8 - access(""M2"".""F_MEMBER_ID""=:B1)" " 10 - access(""S2"".""F_SEAT_ID""=:B1)" " 12 - access(""C2"".""F_CLIENT_ID""=:B1)" " 14 - access(""Y"".""F_VARIETY_ID""=:B1)" " 17 - access(""O"".""URN_APP_NOETU""=:B1)" " 18 - filter((""NUM"">=1 AND ""NUM""<=20))" " 22 - access(""R"".""URN_APP_NOETU""=""V"".""URN_APP_NOETU"")"
MySQL（8.0.35）数据库执行时间+执行计划：
+----+--------------------+------------+------------+--------+------------------------+------------------------+---------+------------------------------+--------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+------------------------+------------------------+---------+------------------------------+--------+----------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 125859 | 11.11 | Using where | | 2 | DERIVED | R | NULL | ALL | IDX_RETURN_APP_NO | NULL | NULL | NULL | 197795 | 50.00 | Using where; Using temporary; Using filesort | | 2 | DERIVED | V | NULL | ref | IDX_RETURN_APP_NO_VRTY | IDX_RETURN_APP_NO_VRTY | 83 | scott.R.URN_APP_NOETU | 1 | 100.00 | NULL | | 10 | DEPENDENT SUBQUERY | O | NULL | ref | IDX_RETURN_APP_NO_VRTY | IDX_RETURN_APP_NO_VRTY | 83 | scott.R.URN_APP_NOETU | 1 | 100.00 | NULL | | 9 | DEPENDENT SUBQUERY | Y | NULL | eq_ref | PRIMARY | PRIMARY | 42 | scott.V.F_VARIETY_ID | 1 | 100.00 | NULL | | 8 | DEPENDENT SUBQUERY | C2 | NULL | eq_ref | PRIMARY | PRIMARY | 42 | scott.R.F_BORROWER_CLIENT_ID | 1 | 100.00 | NULL | | 7 | DEPENDENT SUBQUERY | S2 | NULL | eq_ref | PRIMARY | PRIMARY | 42 | scott.R.F_BORROWER_SEAT_ID | 1 | 100.00 | NULL | | 6 | DEPENDENT SUBQUERY | M2 | NULL | eq_ref | PRIMARY | PRIMARY | 42 | scott.R.F_BORROWER_MEMBER_ID | 1 | 100.00 | NULL | | 5 | DEPENDENT SUBQUERY | C1 | NULL | eq_ref | PRIMARY | PRIMARY | 42 | scott.R.F_LENDER_CLIENT_ID | 1 | 100.00 | NULL | | 4 | DEPENDENT SUBQUERY | S1 | NULL | eq_ref | PRIMARY | PRIMARY | 42 | scott.R.F_LENDER_SEAT_ID | 1 | 100.00 | NULL | | 3 | DEPENDENT SUBQUERY | M1 | NULL | eq_ref | PRIMARY | PRIMARY | 42 | scott.R.F_LENDER_MEMBER_ID | 1 | 100.00 | NULL | +----+--------------------+------------+------------+--------+------------------------+------------------------+---------+------------------------------+--------+----------+----------------------------------------------+ 11 rows in set, 10 warnings (0.00 sec) 20 rows in set (4.46 sec)
DM 数据库 （V8 03134284094-20231011-204277-20067）数据库执行时间+执行计划：
1 #NSET2: [1, 1->20, 632] 2 #PIPE2: [1, 1->20, 632] 3 #PIPE2: [1, 1->20, 632] 4 #PIPE2: [1, 1->20, 632] 5 #PIPE2: [1, 1->20, 632] 6 #PIPE2: [1, 1->20, 632] 7 #PIPE2: [1, 1->20, 632] 8 #PIPE2: [1, 1->20, 632] 9 #PIPE2: [1, 1->20, 632] 10 #PRJT2: [1, 1->20, 632]; exp_num(24), is_atom(FALSE) 11 #SLCT2: [1, 1->20, 632]; D.num >= var10 12 #PRJT2: [1, 20->20, 632]; exp_num(24), is_atom(FALSE) 13 #RNSK: [1, 20->20, 632]; 14 #PRJT2: [1, 20->20, 632]; exp_num(23), is_atom(FALSE) 15 #SORT3: [1, 20->20, 632]; key_num(23), is_distinct(TRUE), is_adaptive(0), MEM_USED(26624KB), DISK_USED(0KB) 16 #PRJT2: [1, 2000000->214275, 632]; exp_num(23), is_atom(FALSE) 17 #INDEX JOIN LEFT JOIN2: [1, 2000000->214275, 632]: col_num(16) ret_null(0) 18 #CSCN2: [46, 200000->200000, 632]; INDEX33555471(t_reg_gold_lease_return_app) 19 #BLKUP2: [1500, 10->66598, 48]; idx_return_app_no_vrty(t_reg_lease_return_app_vrty) 20 #SSEK2: [1500, 10->66598, 48]; scan_type(ASC), idx_return_app_no_vrty(t_reg_lease_return_app_vrty), scan_range[R.f_return_app_no,R.f_return_app_no] 21 #SPL2: [1, 1, 78]; key_num(1), spool_num(7), has_variable(1), sites(-) 22 #PRJT2: [1, 1->214275, 78]; exp_num(1), is_atom(TRUE) 23 #AAGR2: [1, 1->214275, 78]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 24 #BLKUP2: [1, 10->100022, 78]; idx_return_app_no_vrty(t_reg_lease_return_app_vrty) 25 #SSEK2: [1, 10->100022, 78]; scan_type(ASC), idx_return_app_no_vrty(t_reg_lease_return_app_vrty), scan_range[var9,var9] 26 #SPL2: [1, 1, 144]; key_num(1), spool_num(6), has_variable(1), sites(-) 27 #PRJT2: [1, 1->66598, 144]; exp_num(1), is_atom(TRUE) 28 #DISTINCT: [1, 1->66598, 144], MEM_USED(6KB), DISK_USED(0KB) 29 #PRJT2: [1, 1->66598, 144]; exp_num(1), is_atom(FALSE) 30 #BLKUP2: [1, 1->66598, 144]; INDEX33555475(t_par_vrty) 31 #SSEK2: [1, 1->66598, 144]; scan_type(ASC), INDEX33555475(t_par_vrty), scan_range[var8,var8] 32 #SPL2: [1, 1, 96]; key_num(1), spool_num(5), has_variable(1), sites(-) 33 #PRJT2: [1, 1->214275, 96]; exp_num(1), is_atom(TRUE) 34 #BLKUP2: [1, 1->214275, 96]; INDEX33555476(t_reg_client) 35 #SSEK2: [1, 1->214275, 96]; scan_type(ASC), INDEX33555476(t_reg_client), scan_range[var7,var7] 36 #SPL2: [1, 1, 96]; key_num(1), spool_num(4), has_variable(1), sites(-) 37 #PRJT2: [1, 1->214275, 96]; exp_num(1), is_atom(TRUE) 38 #BLKUP2: [1, 1->214275, 96]; INDEX33555478(t_reg_seat) 39 #SSEK2: [1, 1->214275, 96]; scan_type(ASC), INDEX33555478(t_reg_seat), scan_range[var6,var6] 40 #SPL2: [1, 1, 96]; key_num(1), spool_num(3), has_variable(1), sites(-) 41 #PRJT2: [1, 1->214275, 96]; exp_num(1), is_atom(TRUE) 42 #BLKUP2: [1, 1->214275, 96]; INDEX33555477(t_reg_mem) 43 #SSEK2: [1, 1->214275, 96]; scan_type(ASC), INDEX33555477(t_reg_mem), scan_range[var5,var5] 44 #SPL2: [1, 1, 96]; key_num(1), spool_num(2), has_variable(1), sites(-) 45 #PRJT2: [1, 1->214275, 96]; exp_num(1), is_atom(TRUE) 46 #BLKUP2: [1, 1->214275, 96]; INDEX33555476(t_reg_client) 47 #SSEK2: [1, 1->214275, 96]; scan_type(ASC), INDEX33555476(t_reg_client), scan_range[var4,var4] 48 #SPL2: [1, 1, 96]; key_num(1), spool_num(1), has_variable(1), sites(-) 49 #PRJT2: [1, 1->214275, 96]; exp_num(1), is_atom(TRUE) 50 #BLKUP2: [1, 1->214275, 96]; INDEX33555478(t_reg_seat) 51 #SSEK2: [1, 1->214275, 96]; scan_type(ASC), INDEX33555478(t_reg_seat), scan_range[var3,var3] 52 #SPL2: [1, 1, 96]; key_num(1), spool_num(0), has_variable(1), sites(-) 53 #PRJT2: [1, 1->214275, 96]; exp_num(1), is_atom(TRUE) 54 #BLKUP2: [1, 1->214275, 96]; INDEX33555477(t_reg_mem) 55 #SSEK2: [1, 1->214275, 96]; scan_type(ASC), INDEX33555477(t_reg_mem), scan_range[var2,var2] Statistics ----------------------------------------------------------------- 0 data pages changed 0 undo pages changed 7166698 logical reads 0 physical reads 0 redo size 7900 bytes sent to client 3363 bytes received from client 1 roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed 0 io wait time(ms) 4684 exec time(ms) 已用时间: 00:00:04.683. 执行号:1303.
KingBase 数据库 （V008R006C008B0014）数据库执行时间+执行计划：
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------- Subquery Scan on d (cost=19168423.10..19191401.02 rows=1 width=1535) (actual time=6110.510..6205.985 rows=20 loops=1) Filter: ((d.num >= 1) AND (d.num <= 20)) Rows Removed by Filter: 213922 -> Count (cost=19168423.10..19191401.02 rows=0 width=1535) (actual time=6110.508..6200.856 rows=213942 loops=1) -> Unique (cost=19168423.10..19185656.54 rows=287224 width=1527) (actual time=6110.495..6172.907 rows=213942 loops=1) -> Sort (cost=19168423.10..19169141.16 rows=287224 width=1527) (actual time=6110.493..6135.878 rows=214275 loops=1) Sort Key: r.f_return_app_no DESC, r.f_lease_reg_no, r.f_app_date, r.f_trans_date, r.f_orig_app, r.f_lender_member_id, ((SubPlan 1)), r.f_lender_s eat_id, ((SubPlan 2)), r.f_lender_client_id, ((SubPlan 3)), r.f_borrower_member_id, ((SubPlan 4)), r.f_borrower_seat_id, ((SubPlan 5)), r.f_borrower_client_id, ((SubP lan 6)), r.f_lease_interest, r.f_state, r.f_handler, r.f_source, ((SubPlan 7)), ((SubPlan 8)) Sort Method: quicksort Memory: 115897kB -> Hash Left Join (cost=2991.00..19142383.64 rows=287224 width=1527) (actual time=11.746..5138.695 rows=214275 loops=1) Hash Cond: ((r.f_return_app_no)::text = (v.f_return_app_no)::text) -> Seq Scan on t_reg_gold_lease_return_app r (cost=0.00..6652.00 rows=200000 width=155) (actual time=0.007..9.762 rows=200000 loops=1) -> Hash (cost=1741.00..1741.00 rows=100000 width=24) (actual time=11.588..11.589 rows=100000 loops=1) Buckets: 131072 Batches: 1 Memory Usage: 6493kB -> Seq Scan on t_reg_lease_return_app_vrty v (cost=0.00..1741.00 rows=100000 width=24) (actual time=0.005..4.519 rows=100000 loops= 1) SubPlan 1 -> Index Scan using idx_member_id on t_reg_mem m1 (cost=0.29..8.31 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=214275) Index Cond: ((f_member_id)::text = (r.f_lender_member_id)::text) SubPlan 2 -> Index Scan using idx_seat_id on t_reg_seat s1 (cost=0.29..8.31 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=214275) Index Cond: ((f_seat_id)::text = (r.f_lender_seat_id)::text) SubPlan 3 -> Index Scan using idx_client_id on t_reg_client c1 (cost=0.29..8.31 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=214275) Index Cond: ((f_client_id)::text = (r.f_lender_client_id)::text) SubPlan 4 -> Index Scan using idx_member_id on t_reg_mem m2 (cost=0.29..8.31 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=214275) Index Cond: ((f_member_id)::text = (r.f_borrower_member_id)::text) SubPlan 5 -> Index Scan using idx_seat_id on t_reg_seat s2 (cost=0.29..8.31 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=214275) Index Cond: ((f_seat_id)::text = (r.f_borrower_seat_id)::text) SubPlan 6 -> Index Scan using idx_client_id on t_reg_client c2 (cost=0.29..8.31 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=214275) Index Cond: ((f_client_id)::text = (r.f_borrower_client_id)::text) SubPlan 7 -> Index Scan using t_par_vrty_pkey on t_par_vrty y (cost=0.29..8.30 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=214275) Index Cond: ((f_variety_id)::text = (v.f_variety_id)::text) SubPlan 8 -> Aggregate (cost=8.44..8.45 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=214275) -> Index Scan using idx_return_app_no_vrty on t_reg_lease_return_app_vrty o (cost=0.42..8.44 rows=1 width=6) (actual time=0.004.. 0.004 rows=0 loops=214275) Index Cond: ((f_return_app_no)::text = (r.f_return_app_no)::text) Planning Time: 0.345 ms Execution Time: 6208.437 ms (41 行记录)
OceanBase、OpenGauss、GaussDB 等数据库由于没有环境所以测试不了，估计时间都在4s~6s左右。
总结：ORACLE数据库性能最牛逼，执行时间：1.38s 。
MySQL我以为性能是垫底的，没想到8.0版本以后性能提升了这么多，执行时间：4.6s 。
其次为DM数据库，和MySQL旗鼓相当，性能挺不错的，执行时间：4.683s 。
成绩最糟糕的是KingBase数据库，执行时间：6.208s ，没测试之前我一直以为KingBase数据库能排名第二 ?。
国产数据库路还是很长啊，任重而道远。