自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

國(guó)產(chǎn)集中庫(kù)SQL能力評(píng)測(cè) - 訪問(wèn)路徑

原創(chuàng) 精選
作者： 韓鋒
數(shù)據(jù)庫(kù) 其他數(shù)據(jù)庫(kù)
從數(shù)據(jù)庫(kù)架構(gòu)上看，考慮到分布式與集中式的差異較大，本次將重點(diǎn)放在集中式數(shù)據(jù)庫(kù)上。從之前接觸用戶到第三方調(diào)查機(jī)構(gòu)的報(bào)告來(lái)看，數(shù)據(jù)庫(kù)的集中式架構(gòu)仍然是主流架構(gòu)，占據(jù)近八成左右的市場(chǎng)份額。因此選擇以集中式數(shù)據(jù)庫(kù)為評(píng)測(cè)對(duì)象。

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

1. 評(píng)測(cè)方案說(shuō)明

1)評(píng)測(cè)對(duì)象架構(gòu)：集中式

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

2)評(píng)測(cè)功能標(biāo)準(zhǔn)：Oracle

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

3)評(píng)測(cè)產(chǎn)品范圍：主流+代表性

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

4)評(píng)測(cè)環(huán)境&版本

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

圖片圖片

2. Oracle 訪問(wèn)路徑能力說(shuō)明

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

1)表訪問(wèn)路徑

? 全表掃描

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

? ROWID掃描

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

? 采樣掃描

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

2)索引訪問(wèn)路徑

? 索引唯一掃描

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

? 索引范圍掃描

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

? 索引全掃描

索引全掃描不讀取索引結(jié)構(gòu)中的每個(gè)塊，這與其名稱表面上相悖。索引全掃描處理索引的所有葉塊，但為了查找到第一個(gè)葉塊需要處理足夠多的分支塊。一旦在索引中獲得一個(gè)葉塊，則其前和后的葉塊將按順序被鏈接起來(lái)。即，葉塊不僅可以通過(guò)分支塊導(dǎo)航；而且，一旦獲得一個(gè)葉塊，也可以隨指針獲得下一個(gè)葉塊。事實(shí)上，使用這種雙向鏈表可以在索引結(jié)構(gòu)中前進(jìn)或后退。索引全掃描使用單塊IO按順序讀取索引，它從根開始，通過(guò)分支塊到達(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ū)別就是它不對(duì)查詢出的數(shù)據(jù)進(jìn)行排序，即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中，可以使用多塊讀功能，也可以使用并行讀入，以便獲得最大吞吐量與縮短執(zhí)行時(shí)間。索引快速全掃描每次I/O讀取的是多個(gè)數(shù)據(jù)塊，這也是該方式與索引全掃描之間的主要區(qū)別。

? 索引跳躍掃描

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

3)Oracle 測(cè)試示例

-- 表掃描：全表掃描
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
//通過(guò)偽造統(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. 國(guó)產(chǎn)庫(kù)訪問(wèn)路徑能力評(píng)測(cè)

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

圖片圖片

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萬(wàn)的記錄也是如此)

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萬(wàn)的記錄也是如此)

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);
//通過(guò)偽造統(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);
//通過(guò)偽造統(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é)任編輯：武曉燕 來(lái)源： 韓鋒頻道
數(shù)據(jù)庫(kù)架構(gòu)分布式
相關(guān)推薦

2025-01-09 07:30:49

SQL能力評(píng)測(cè)

2024-12-10 07:30:46

2024-12-19 07:30:34

2024-12-26 07:33:02

2025-02-18 07:30:35

2025-04-08 07:30:40

數(shù)據(jù)庫(kù)對(duì)象索引

2025-03-27 07:30:28

2025-04-23 07:31:14

2025-03-11 07:31:04

2024-08-22 08:02:04

OracleSQL語(yǔ)句

2025-03-28 07:33:09

數(shù)據(jù)庫(kù)AI助手設(shè)計(jì)

2009-03-05 10:38:00

Cisco2600路由器

2021-08-06 18:37:50

SQL表連接方式

2009-09-15 10:02:44

Linq to SQL

2014-12-16 10:23:59

2024-12-04 08:44:25

OS集中式數(shù)據(jù)庫(kù)

2010-11-09 12:20:53

SQL Server查

2011-08-05 13:16:36

終端控制臺(tái)路由器

2011-12-16 13:42:45

路由器

2023-09-18 16:24:33

數(shù)據(jù)研究
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)