國產(chǎn)集中庫SQL能力評測 - 訪問路徑

作者：韓鋒 2024-12-05 07:31:16

從數(shù)據(jù)庫架構(gòu)上看，考慮到分布式與集中式的差異較大，本次將重點(diǎn)放在集中式數(shù)據(jù)庫上。從之前接觸用戶到第三方調(diào)查機(jī)構(gòu)的報(bào)告來看，數(shù)據(jù)庫的集中式架構(gòu)仍然是主流架構(gòu)，占據(jù)近八成左右的市場份額。因此選擇以集中式數(shù)據(jù)庫為評測對象。

隨著國產(chǎn)數(shù)據(jù)庫應(yīng)用步入深水區(qū)，用戶開始在更核心、更多元的場景使用國產(chǎn)庫。在使用過程之中，用戶非常關(guān)心的一個(gè)問題，就是國產(chǎn)數(shù)據(jù)庫的SQL支持情況怎么樣？是不是能如 Oracle 那樣，針對復(fù)雜多變的 SQL 也能生成相對優(yōu)秀的執(zhí)行計(jì)劃，進(jìn)而保證良好的執(zhí)行效率。之前也曾聽聞過用戶吐槽，國產(chǎn)數(shù)據(jù)庫的優(yōu)化器存在諸多不足。這也促使筆者考慮針對國產(chǎn)數(shù)據(jù)庫做些 SQL 能力的評測，方便用戶有著更深入的了解。這將是一個(gè)系列，筆者看個(gè)人精力會(huì)逐步完成。受限于個(gè)人能力水平及時(shí)間精力等因素，測試過程及結(jié)果僅代表個(gè)人，不能完全反映廠商產(chǎn)品能力，歡迎批評指正。

1. 評測方案說明

1)評測對象架構(gòu)：集中式

2)評測功能標(biāo)準(zhǔn)：Oracle

長期以來，Oracle 數(shù)據(jù)庫一直是數(shù)據(jù)庫業(yè)內(nèi)的標(biāo)桿性產(chǎn)品，特別是在集中式數(shù)據(jù)庫領(lǐng)域。因此，本次測試會(huì)以O(shè)racle 的能力為標(biāo)準(zhǔn)與國內(nèi)數(shù)據(jù)庫進(jìn)行對比。此外，考慮到國內(nèi)大部分已有業(yè)務(wù)也都是基于 Oracle 去開發(fā)的，因此遷移到國產(chǎn)數(shù)據(jù)庫采用與Oracle為參照物也具有很好的參考意義。

3)評測產(chǎn)品范圍：主流+代表性

國內(nèi)數(shù)據(jù)庫廠商及產(chǎn)品非常多，選擇哪些廠商及產(chǎn)品是個(gè)很頭疼的事情。這里本著主流或有代表性的原則進(jìn)行選擇。從現(xiàn)有集中式數(shù)據(jù)庫的市場占有率方面，選擇頭部的廠商達(dá)夢、電科金倉為代表。從生態(tài)方面選擇 openGauss 生態(tài)的海量數(shù)據(jù)；MySQL生態(tài)上沒有太好選擇，故使用最新社區(qū)版本；PG 生態(tài)上由之前的電科金倉來代表。自研方面，則采用的崖山數(shù)據(jù)庫，畢竟其主打也是Oracle的兼容能力。最后也選擇 Oracle 在國內(nèi)仍然大規(guī)模使用的版本作為參照對象。

4)評測環(huán)境&版本

測試環(huán)境：采用Docker鏡像方式
測試版本：采用官方鏡像（可能非最新）見下文
測試數(shù)據(jù)：自行構(gòu)造
測試配置：數(shù)據(jù)庫默認(rèn)配置，未優(yōu)化

圖片

2. Oracle 訪問路徑能力說明

這里主要談 Oracle 數(shù)據(jù)的表及索引的訪問路徑問題。

1)表訪問路徑

? 全表掃描

為實(shí)現(xiàn)全表掃描，Oracle讀取表中所有的行，并檢查每一行是否滿足語句的WHERE限制條件。Oracle順序地讀取分配給表的每個(gè)數(shù)據(jù)塊，直到讀到表的最高水線處。一個(gè)多塊讀操作可以使一次I/O能讀取多塊數(shù)據(jù)塊，而不是只讀取一個(gè)數(shù)據(jù)塊，這極大的減少了I/O總次數(shù)，提高了系統(tǒng)的吞吐量，所以利用多塊讀的方法可以十分高效地實(shí)現(xiàn)全表掃描，而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下，每個(gè)數(shù)據(jù)塊只被讀一次。這也是最為常規(guī)的訪問路徑，下文將以此方式為主。

? ROWID掃描

行的ROWID指出了該行所在的數(shù)據(jù)文件、數(shù)據(jù)塊以及行在該塊中的位置，所以通過ROWID來存取數(shù)據(jù)可以快速定位到目標(biāo)數(shù)據(jù)上，是Oracle存取單行數(shù)據(jù)的最快方法。為了通過ROWID存取表，Oracle首先要獲取被選擇行的ROWID，或者從語句的WHERE子句中得到，或者通過表的一個(gè)或多個(gè)索引的索引掃描得到。Oracle然后以得到的ROWID為依據(jù)定位每個(gè)被選擇的行。這種存取方法不會(huì)用到多塊讀操作，一次I/O只能讀取一個(gè)數(shù)據(jù)塊。我們會(huì)經(jīng)常在執(zhí)行計(jì)劃中看到該存取方法，如通過索引查詢數(shù)據(jù)。

? 采樣掃描

將從全部數(shù)據(jù)塊中讀取指定比例的數(shù)據(jù)之后，然后再通過過濾返回滿足條件的行。在每次執(zhí)行時(shí)，都會(huì)從全部的數(shù)據(jù)塊中讀取指定比例的數(shù)據(jù)塊。所以每次讀取的數(shù)據(jù)塊都是不同的，當(dāng)某個(gè)數(shù)據(jù)塊被選定為讀取對象時(shí)，塊中所有行將被全部讀取。此種訪問路徑常見于統(tǒng)計(jì)信息收集等場景之中。

2)索引訪問路徑

? 索引唯一掃描

通過唯一索引查找一個(gè)數(shù)值經(jīng)常返回單個(gè)ROWID。如果存在UNIQUE或PRIMARY KEY約束(它保證了語句只存取單行)的話，Oracle經(jīng)常實(shí)現(xiàn)唯一性掃描。在大部分情況下該掃描方式主要被使用在檢索唯一ROWID的查詢中，為了進(jìn)行索引唯一掃描而必須基于主鍵來創(chuàng)建索引或者創(chuàng)建唯一索引，且在SQL語句中必須為索引列使用"="比較運(yùn)算符。否則即使基于具有唯一值的列創(chuàng)建了索引，在執(zhí)行時(shí)優(yōu)化器也不能可能選擇索引唯一掃描，而會(huì)選擇范圍掃描。

? 索引范圍掃描

索引最普遍的數(shù)據(jù)讀取方式，優(yōu)化器選擇該掃描方式的情況有兩種，即由開始值與結(jié)束值的情況和有一個(gè)以上的行但沒有結(jié)束的情況。索引范圍掃描在尋找開始位置的時(shí)候使用隨機(jī)讀取，但之后所執(zhí)行的全部都是連續(xù)掃描。如果再精確描述，即在查找分支塊時(shí)使用的是隨機(jī)讀取，在經(jīng)過分支塊查找到開始的葉塊之后所執(zhí)行的就是連續(xù)掃描。在掃描方向上，又可分為升序掃描和降序掃描。

? 索引全掃描

索引全掃描不讀取索引結(jié)構(gòu)中的每個(gè)塊，這與其名稱表面上相悖。索引全掃描處理索引的所有葉塊，但為了查找到第一個(gè)葉塊需要處理足夠多的分支塊。一旦在索引中獲得一個(gè)葉塊，則其前和后的葉塊將按順序被鏈接起來。即，葉塊不僅可以通過分支塊導(dǎo)航；而且，一旦獲得一個(gè)葉塊，也可以隨指針獲得下一個(gè)葉塊。事實(shí)上，使用這種雙向鏈表可以在索引結(jié)構(gòu)中前進(jìn)或后退。索引全掃描使用單塊IO按順序讀取索引，它從根開始，通過分支塊到達(dá)第一個(gè)葉塊。這些塊都是每次讀取一塊。當(dāng)獲取第一個(gè)葉塊時(shí)，可按順序讀取每個(gè)葉塊，同樣是一次一塊。索引全掃描從索引中按順序讀取數(shù)據(jù)。因此，索引全掃描可以避免排序。

? 索引快速全掃描

索引快速全掃描將索引等同于表的一個(gè)縮小版本。它一次讀取索引多個(gè)數(shù)據(jù)塊，處理葉塊數(shù)據(jù)，并忽略分支塊。它能夠比索引全掃描更快地讀取索引結(jié)構(gòu)，因?yàn)樗鞘褂昧硕鄩Kio。掃描索引中的所有的數(shù)據(jù)塊，與索引全掃描很類似，但是一個(gè)顯著的區(qū)別就是它不對查詢出的數(shù)據(jù)進(jìn)行排序，即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時(shí)間。索引快速全掃描每次I/O讀取的是多個(gè)數(shù)據(jù)塊，這也是該方式與索引全掃描之間的主要區(qū)別。

? 索引跳躍掃描

索引跳過掃描改進(jìn)了非前綴列的索引掃描。通常，掃描索引塊比掃描表數(shù)據(jù)塊更快。跳過掃描允許將復(fù)合索引在邏輯上拆分為更小的子索引。在跳過掃描中，查詢中未指定復(fù)合索引的初始列。換句話說，它被跳過了。邏輯子索引的數(shù)量由初始列中不同值的數(shù)量決定。如果復(fù)合索引的前導(dǎo)列中只有很少的不同值，而索引的非前導(dǎo)鍵中有很多不同值，則跳過掃描是有利的。

3)Oracle 測試示例

-- 表掃描：全表掃描
SQL> explain plan for select * from emp;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   273K|    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  | 10000 |   273K|    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- 索引掃描：索引唯一掃描（index unique scan）
SQL> explain plan for select * from emp where emp_id=111;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------

-- 索引掃描：索引范圍掃描（index range scan）
SQL> explain plan for select * from emp where emp_id<100;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    99 |  2772 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |    99 |  2772 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_PK |    99 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

-- 索引掃描：索引快速全掃描（index fast full scan）
SQL> explain plan for select emp_name from emp;
SQL> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              | 10000 | 80000 |    14   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IDX_EMP_NAME | 10000 | 80000 |    14   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 索引掃描：索引全掃描（index full scan）
SQL> exec dbms_stats.set_table_stats(ownname=>'TESTUSER',tabname=>'EMP',numrows=>1000000,numblks=>5000);
SQL> select num_rows,blocks from user_tables where table_name='EMP';
  NUM_ROWS     BLOCKS
---------- ----------
   1000000       5000
//通過偽造統(tǒng)計(jì)信息，放大表掃描的成本，讓優(yōu)化器選擇使用索引全掃描

SQL> explain plan for select emp_name from emp order by 1;
SQL> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |  1000K|  7812K|    46   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_EMP_NAME |  1000K|  7812K|    46   (0)| 00:00:01 |
---------------------------------------------------------------------------------

-- 索引掃描：索引跳躍掃描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
SQL> insert into t select 3 ,object_name from dba_objects;
SQL> insert into t select 4 ,object_name from dba_objects;
SQL> create index idx_t on t(id,object_name);
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
SQL> explain plan for select * from t where object_name='TEST';
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     7 |   189 |     6   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_T |     7 |   189 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

3. 國產(chǎn)庫訪問路徑能力評測

下文將對國產(chǎn)數(shù)據(jù)庫（含MySQL）做測試對比。在之前先看下結(jié)論，國產(chǎn)數(shù)據(jù)庫在訪問路徑方面能力都還可以，部分?jǐn)?shù)據(jù)庫還是稍有不足，具體可參考下面及之后的測試步驟。

圖片

1)MySQL

-- 表掃描：全表掃描
mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9796 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

-- 索引掃描：索引單鍵掃描
mysql> explain select * from emp where emp_id=111;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

-- 索引掃描：索引范圍掃描
mysql> explain select * from emp where emp_id<100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   99 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

-- 索引掃描：索引掃描
mysql> explain select emp_name from emp;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_name | 33      | NULL | 9796 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

-- 索引掃描：索引跳躍掃描
mysql> create table t as select * from information_schema.tables;
mysql> insert into t select * from t;
...
mysql> insert into t select * from t;
mysql> alter table t add id int;
mysql> create index idx_tmp on t(table_type,table_name);
mysql> analyze table t;
mysql> explain select table_type,table_name from t where table_name='COLLATIONS';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_tmp       | idx_tmp | 195     | NULL | 18045 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
-- 表掃描：全表掃描
mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9796 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

-- 索引掃描：索引單鍵掃描
mysql> explain select * from emp where emp_id=111;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

-- 索引掃描：索引范圍掃描
mysql> explain select * from emp where emp_id<100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   99 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

-- 索引掃描：索引掃描
mysql> explain select emp_name from emp;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_name | 33      | NULL | 9796 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

-- 索引掃描：索引跳躍掃描
mysql> create table t as select * from information_schema.tables;
mysql> insert into t select * from t;
...
mysql> insert into t select * from t;
mysql> alter table t add id int;
mysql> create index idx_tmp on t(table_type,table_name);
mysql> analyze table t;
mysql> explain select table_type,table_name from t where table_name='COLLATIONS';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_tmp       | idx_tmp | 195     | NULL | 18045 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+

2)DM

-- 表掃描：全表掃描
SQL> explain select * from emp;
1   #NSET2: [1, 10000, 163]
2     #PRJT2: [1, 10000, 163]; exp_num(6), is_atom(FALSE)
3       #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)

-- 索引掃描：索引唯一掃描
SQL> explain select * from emp where emp_id=111;
1   #NSET2: [1, 1, 163]
2     #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)
3       #BLKUP2: [1, 1, 163]; INDEX33555485(EMP)
4         #SSEK2: [1, 1, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range[exp_cast(111),exp_cast(111)], is_global(0)
* 沒有唯一掃描方式，DM都認(rèn)為是范圍掃描

-- 索引掃描：索引范圍掃描
SQL> explain select * from emp where emp_id<100;
1   #NSET2: [1, 99, 163]
2     #PRJT2: [1, 99, 163]; exp_num(6), is_atom(FALSE)
3       #BLKUP2: [1, 99, 163]; INDEX33555485(EMP)
4         #SSEK2: [1, 99, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range(null2,exp_cast(100)), is_global(0)
* 被認(rèn)為是從NULL到指定數(shù)值的范圍掃描

-- 索引掃描：索引全掃描
SQL> explain select emp_name from emp;
1   #NSET2: [1, 10000, 60]
2     #PRJT2: [1, 10000, 60]; exp_num(2), is_atom(FALSE)
3       #SSCN: [1, 10000, 60]; IDX_EMP_NAME(EMP); btr_scan(1); is_global(0)
* 直接使用索引掃描，不用再回表查

-- 索引掃描：索引跳躍掃描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
SQL> insert into t select 3 ,object_name from dba_objects;
SQL> insert into t select 4 ,object_name from dba_objects;
SQL> create index idx_t on t(id,object_name);
SQL> CALL SP_TAB_INDEX_STAT_INIT ('TESTUSER', 'T');
SQL> explain select * from t where object_name='TEST';
1   #NSET2: [1, 109, 64]
2     #PRJT2: [1, 109, 64]; exp_num(3), is_atom(FALSE)
3       #SLCT2: [1, 109, 64]; T.OBJECT_NAME = 'TEST'
4         #SSCN: [1, 109, 64]; IDX_T(T); btr_scan(1); is_global(0)
* 直接使用索引掃描，實(shí)現(xiàn)了跳躍掃描功能
-- 表掃描：全表掃描
SQL> explain select * from emp;
1   #NSET2: [1, 10000, 163]
2     #PRJT2: [1, 10000, 163]; exp_num(6), is_atom(FALSE)
3       #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)

-- 索引掃描：索引唯一掃描
SQL> explain select * from emp where emp_id=111;
1   #NSET2: [1, 1, 163]
2     #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)
3       #BLKUP2: [1, 1, 163]; INDEX33555485(EMP)
4         #SSEK2: [1, 1, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range[exp_cast(111),exp_cast(111)], is_global(0)
* 沒有唯一掃描方式，DM都認(rèn)為是范圍掃描

-- 索引掃描：索引范圍掃描
SQL> explain select * from emp where emp_id<100;
1   #NSET2: [1, 99, 163]
2     #PRJT2: [1, 99, 163]; exp_num(6), is_atom(FALSE)
3       #BLKUP2: [1, 99, 163]; INDEX33555485(EMP)
4         #SSEK2: [1, 99, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range(null2,exp_cast(100)), is_global(0)
* 被認(rèn)為是從NULL到指定數(shù)值的范圍掃描

-- 索引掃描：索引全掃描
SQL> explain select emp_name from emp;
1   #NSET2: [1, 10000, 60]
2     #PRJT2: [1, 10000, 60]; exp_num(2), is_atom(FALSE)
3       #SSCN: [1, 10000, 60]; IDX_EMP_NAME(EMP); btr_scan(1); is_global(0)
* 直接使用索引掃描，不用再回表查

-- 索引掃描：索引跳躍掃描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
SQL> insert into t select 3 ,object_name from dba_objects;
SQL> insert into t select 4 ,object_name from dba_objects;
SQL> create index idx_t on t(id,object_name);
SQL> CALL SP_TAB_INDEX_STAT_INIT ('TESTUSER', 'T');
SQL> explain select * from t where object_name='TEST';
1   #NSET2: [1, 109, 64]
2     #PRJT2: [1, 109, 64]; exp_num(3), is_atom(FALSE)
3       #SLCT2: [1, 109, 64]; T.OBJECT_NAME = 'TEST'
4         #SSCN: [1, 109, 64]; IDX_T(T); btr_scan(1); is_global(0)
* 直接使用索引掃描，實(shí)現(xiàn)了跳躍掃描功能

3)KingBase

-- 表掃描：全表掃描
TEST=# explain select * from emp;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on emp  (cost=0.00..192.00 rows=10000 width=39)
* 表的順序掃描

-- 索引掃描：索引唯一掃描
TEST=# explain select * from emp where emp_id=111;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using EMP_PK on emp  (cost=0.29..8.30 rows=1 width=39)
   Index Cond: (emp_id = '111'::numeric)
* 標(biāo)準(zhǔn)索引掃描
   
-- 索引掃描：索引范圍掃描
TEST=# explain select * from emp where emp_id<100;
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using EMP_PK on emp  (cost=0.29..93.77 rows=99 width=39)
   Index Cond: (emp_id < '100'::numeric)
* 標(biāo)準(zhǔn)索引掃描

-- 索引掃描：索引全掃描
TEST=# explain select emp_name from emp;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on emp  (cost=0.00..192.00 rows=10000 width=13)
* 默認(rèn)走了全表掃描(即使增加到100萬的記錄也是如此)

TEST=# set enable_hint=on;
TEST=# explain analyze verbose select /*+IndexOnlyScan(emp idx_emp_name)*/ emp_name from emp;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_emp_name on public.emp  (cost=0.42..69579.87 rows=1000000 width=15) (actual time=0.209..151.571 rows=1000000 loops=1)
   Output: emp_name
   Heap Fetches: 1100000
 Planning Time: 0.123 ms
 Execution Time: 172.407 ms
* 嘗試強(qiáng)制走索引掃描，成本更高。

-- 索引掃描：索引跳躍掃描（index skip scan）
TEST=# create table t as select 1 id,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
TEST=# insert into t select 2 ,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
...
TEST=# create index idx_t on t(id,attname );
TEST=# analyze verbose t;
TEST=# explain select * from t where attname ='TEST';
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using idx_t on t  (cost=0.29..889.09 rows=11 width=92)
   Index Cond: (attname = 'TEST'::name)
* 直接使用索引掃描，實(shí)現(xiàn)了跳躍掃描功能
-- 表掃描：全表掃描
TEST=# explain select * from emp;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on emp  (cost=0.00..192.00 rows=10000 width=39)
* 表的順序掃描

-- 索引掃描：索引唯一掃描
TEST=# explain select * from emp where emp_id=111;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using EMP_PK on emp  (cost=0.29..8.30 rows=1 width=39)
   Index Cond: (emp_id = '111'::numeric)
* 標(biāo)準(zhǔn)索引掃描
   
-- 索引掃描：索引范圍掃描
TEST=# explain select * from emp where emp_id<100;
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using EMP_PK on emp  (cost=0.29..93.77 rows=99 width=39)
   Index Cond: (emp_id < '100'::numeric)
* 標(biāo)準(zhǔn)索引掃描

-- 索引掃描：索引全掃描
TEST=# explain select emp_name from emp;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on emp  (cost=0.00..192.00 rows=10000 width=13)
* 默認(rèn)走了全表掃描(即使增加到100萬的記錄也是如此)

TEST=# set enable_hint=on;
TEST=# explain analyze verbose select /*+IndexOnlyScan(emp idx_emp_name)*/ emp_name from emp;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_emp_name on public.emp  (cost=0.42..69579.87 rows=1000000 width=15) (actual time=0.209..151.571 rows=1000000 loops=1)
   Output: emp_name
   Heap Fetches: 1100000
 Planning Time: 0.123 ms
 Execution Time: 172.407 ms
* 嘗試強(qiáng)制走索引掃描，成本更高。

-- 索引掃描：索引跳躍掃描（index skip scan）
TEST=# create table t as select 1 id,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
TEST=# insert into t select 2 ,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;
...
TEST=# create index idx_t on t(id,attname );
TEST=# analyze verbose t;
TEST=# explain select * from t where attname ='TEST';
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using idx_t on t  (cost=0.29..889.09 rows=11 width=92)
   Index Cond: (attname = 'TEST'::name)
* 直接使用索引掃描，實(shí)現(xiàn)了跳躍掃描功能

4)YashanDB

-- 表掃描：全表掃描
SQL> explain plan for select * from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |     10000|       41( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引唯一掃描（index unique scan）
SQL> explain select * from emp where emp_id=111;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 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 UNIQUE SCAN            | EMP_PK               | TESTUSER   |         1|        1( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引范圍掃描（index range scan）
SQL> explain select * from emp where emp_id<100;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  TABLE ACCESS BY INDEX ROWID   | EMP                  | TESTUSER   |       100|        1( 0)|                                |
|* 2 |   INDEX RANGE SCAN             | EMP_PK               | TESTUSER   |       100|        1( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引快速全掃描（index fast full scan）
SQL> explain select emp_name from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  INDEX FAST FULL SCAN          | IDX_EMP_NAME         | TESTUSER   |     10000|       29( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引全掃描（index full scan）
exec dbms_stats.set_table_stats('TESTUSER','EMP',null,1000000,5000,34);
//通過偽造統(tǒng)計(jì)信息，放大表掃描的成本，讓優(yōu)化器選擇使用索引全掃描

SQL> explain select emp_name from emp order by 1;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  INDEX FULL SCAN               | IDX_EMP_NAME         | TESTUSER   |   1000000|       29( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引跳躍掃描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
...
SQL> create index idx_t on t(id,object_name);
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
SQL> explain select * from t where object_name='TEST';
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  INDEX SKIP SCAN               | IDX_T                | TESTUSER   |         1|        3( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
-- 表掃描：全表掃描
SQL> explain plan for select * from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  TABLE ACCESS FULL             | EMP                  | TESTUSER   |     10000|       41( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引唯一掃描（index unique scan）
SQL> explain select * from emp where emp_id=111;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 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 UNIQUE SCAN            | EMP_PK               | TESTUSER   |         1|        1( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引范圍掃描（index range scan）
SQL> explain select * from emp where emp_id<100;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  TABLE ACCESS BY INDEX ROWID   | EMP                  | TESTUSER   |       100|        1( 0)|                                |
|* 2 |   INDEX RANGE SCAN             | EMP_PK               | TESTUSER   |       100|        1( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引快速全掃描（index fast full scan）
SQL> explain select emp_name from emp;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  INDEX FAST FULL SCAN          | IDX_EMP_NAME         | TESTUSER   |     10000|       29( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引全掃描（index full scan）
exec dbms_stats.set_table_stats('TESTUSER','EMP',null,1000000,5000,34);
//通過偽造統(tǒng)計(jì)信息，放大表掃描的成本，讓優(yōu)化器選擇使用索引全掃描

SQL> explain select emp_name from emp order by 1;
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|  1 |  INDEX FULL SCAN               | IDX_EMP_NAME         | TESTUSER   |   1000000|       29( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

-- 索引掃描：索引跳躍掃描（index skip scan）
SQL> create table t as select 1 id,object_name from dba_objects;
SQL> insert into t select 2 ,object_name from dba_objects;
...
SQL> create index idx_t on t(id,object_name);
SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
SQL> explain select * from t where object_name='TEST';
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| Id | Operation type                 | Name                 | Owner      | Rows     | Cost(%CPU)  | Partition info                 |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
|  0 | SELECT STATEMENT               |                      |            |          |             |                                |
|* 1 |  INDEX SKIP SCAN               | IDX_T                | TESTUSER   |         1|        3( 0)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

5)Vertbase

-- 表掃描：全表掃描
vastbase=> explain select * from emp;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on emp  (cost=0.00..218.00 rows=10000 width=44)
* 表的順序掃描

-- 索引掃描：索引唯一掃描
vastbase=> explain select * from emp where emp_id=111;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using emp_pk on emp  (cost=0.00..8.27 rows=1 width=44)
   Index Cond: (emp_id = 111::number)
* 標(biāo)準(zhǔn)索引掃描
   
-- 索引掃描：索引范圍掃描
vastbase=> explain select * from emp where emp_id<100;
                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using emp_pk on emp  (cost=0.00..9.13 rows=50 width=44)
   Index Cond: (emp_id < 100::number)
* 標(biāo)準(zhǔn)索引掃描

-- 索引掃描：索引全掃描
vastbase=> explain select emp_name from emp;
-- 表掃描：全表掃描
vastbase=> explain select * from emp;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on emp  (cost=0.00..218.00 rows=10000 width=44)
* 表的順序掃描

-- 索引掃描：索引唯一掃描
vastbase=> explain select * from emp where emp_id=111;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using emp_pk on emp  (cost=0.00..8.27 rows=1 width=44)
   Index Cond: (emp_id = 111::number)
* 標(biāo)準(zhǔn)索引掃描
   
-- 索引掃描：索引范圍掃描
vastbase=> explain select * from emp where emp_id<100;
                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using emp_pk on emp  (cost=0.00..9.13 rows=50 width=44)
   Index Cond: (emp_id < 100::number)
* 標(biāo)準(zhǔn)索引掃描

-- 索引掃描：索引全掃描
vastbase=> explain select emp_name from emp;

責(zé)任編輯：武曉燕來源：韓鋒頻道

數(shù)據(jù)庫架構(gòu)分布式