單個表上億行資料的主鍵、索引設計，及分頁查詢

一，概述

一般而言，我們對關系型資料庫系統，進行表結構設計時，會按資料的種類，進行分類，一般有如下種類:

1)主資料，其資料量基本穩定，不隨時間而線性增長，比如，分公司，產品，經銷商， 這種資料庫表，我們一般以 tm_ 作為表名的前綴, 意思是 table of master data，
2)系統級資料，其資料量基本穩定，不隨時間而線性增長，比如，用戶權限控制，配置引數， 這種資料庫表，我們一般以 ts_ 作為表名的前綴, 意思是 table of system，
3)日志資料表，隨時間而線性增長，但會安排定時任務定期洗掉舊資料，保持總體資料量穩定， 這種資料庫表，我們一般以 tl_ 作為表名的前綴, 意思是 table of log，
4)介面資料表，隨時間而線性增長，但會安排定時任務定期洗掉舊資料，保持總體資料量穩定， 這種資料庫表，我們一般以 ti_ 作為表名的前綴, 意思是 table of interface，
5)業務交易資料，隨時間而線性增長，用戶平常關注最近若干天的資料，少數情況下會查閱很久以前的資料， 這種資料庫表，我們一般以 tt_ 作為表名的前綴, 意思是 table of transaction data，
6)關系資料，可能是以上 1,2,5 的關系表，我們分別以 tmr_, tsr_, ttr_ 作為表名的前綴，

通常，資料量大的，都是上述"5. 業務交易資料"，

 

二、業務交易表的主鍵、索引設計

業務交易資料，按通常的理解，一般有主表、明細表兩種，

業務交易主表的主鍵，一般是 id/uuid；另在某個時間欄位上，加上索引，比如:

 1 CREATE TABLE ow_pkg.TT_FLOW_IN
 2 (
 3    IN_UUID varchar2(32),
 4    IN_SHEET_CD varchar2(255) NOT NULL,
 5    IN_TIME date NOT NULL,
 6 
 7    SEND_NODE_ID decimal(38,0) NOT NULL,
 8    RECEIVE_NODE_ID decimal(38,0) NOT NULL,
 9 
10    CREATED_BY varchar2(20),
11    CREATED_DT date,
12    UPDATED_BY varchar2(20),
13    UPDATED_DT date,
14    UPDATE_CNT INTEGER DEFAULT 0  NOT NULL
15 )
16 ;

其中, in_uuid 為主鍵，

對于交易主表的主鍵，可用按 SQL 語法，創建 primary key, 也可以只創建成唯一索引(UNIQUE INDEX), 或普通索引(INDEX)，之所以會有這種的做法，是因為有的資料庫，比如 MS SQL Server, 默認在主鍵上創建聚集索引(clustered index, 不同的資料庫，名詞可能有所差異)，資料的存盤，按主鍵的數值順序，如果我們使用 uuid 做主鍵，這可能不是我們期望的，
在主鍵上創建普通索引，是在使用 uuid 作為主鍵資料時，因 uuid 本身就能保證資料的唯一性，不需要使用資料庫的 primary key 或 UNIQUE INDEX 語法來保證資料唯一性，且有的架構師，擔心每行資料 insert 到表時，擁有 primary key 或 UNIQUE INDEX 定義的表，資料庫會自動進行主鍵資料的唯一性檢查，如果資料量極大，這個唯一性檢查的步驟有可能需要花費額外的時間，還不如使用普通索引，跳過主鍵資料的唯一性檢查，

這里我們創建唯一性索引，

CREATE UNIQUE INDEX idx_tt_flow_in_in_uuid ON ow_pkg.TT_FLOW_IN(IN_UUID); 

一般在交易主表的某個時間欄位上，創建普通索引，或者聚集索引(clustered index)，比如:

CREATE INDEX idx_tt_flow_in_in_time ON ow_pkg.TT_FLOW_IN(IN_TIME);

交易表的資料，一般是 insert 多、delete 少，如果不定義主鍵、不創建聚集索引(clustered index)，正常情況下，資料的存盤也是按時間順序的，

對于業務交易明細表，一般創建明細表主鍵、在明細表指向主表的欄位上創建普通索引，比如:

 1 CREATE TABLE ow_pkg.TT_FLOW_IN_DETAIL
 2 (
 3    IN_DETAIL_UUID varchar2(32),                --pk
 4    IN_UUID varchar2(32),                    --fk
 5    PROJ_ID decimal(38,0) NOT NULL,
 6    STATUS_ID decimal(38,0),
 7    CONTAINER_ID decimal(38,0) NOT NULL,
 8    REAL_QTY decimal(10,0),
 9    PLAN_QTY decimal(10,0),
10    CREATED_BY varchar2(20),
11    CREATED_DT date,
12    UPDATED_BY varchar2(20),
13    UPDATED_DT date,
14    UPDATE_CNT INTEGER DEFAULT 0  NOT NULL,
15 )
16 ;
17 CREATE UNIQUE INDEX idx_tt_flow_in_detail_in_detail_uuid ON ow_pkg.TT_FLOW_IN_DETAIL(IN_DETAIL_UUID);
18 CREATE INDEX idx_tt_flow_in_detail_in_uuid ON ow_pkg.TT_FLOW_IN_DETAIL(IN_UUID);

交易明細表不需要在某個時間欄位上，創建索引，此時基于 in_uuid 查找 tt_flow_in_detail 表，資料量不會超過 30 行，

 

三、分頁查詢

SQL 標準中，有分頁查詢的語法，一般只針對業務主表進行查詢分頁、然后點擊查找結果的某行，彈出視窗顯示業務明細表資料，

這里的分頁查詢 SQL 為(基于 Oracle):

1 SELECT * FROM (
2     SELECT ROW_NUMBER() OVER (ORDER BY i.in_time desc,i.IN_SHEET_CD,i.in_uuid ) as rownum_xx
3     ,i.*
4     from TT_FLOW_IN i
5     where i.in_time between to_date('2020-01-01 00:00' ,'yyyy-mm-dd hh24:mi') and to_date('2020-01-02 00:00' ,'yyyy-mm-dd hh24:mi')
6     and i.IN_SHEET_CD is not null
7 )
8 WHERE rownum_xx >= 0 and rownum_xx <= 20;

以上 SQL 的 where 中的引數，可以動態引數，比如對于 java ，可以使用占位符 ? ，使用 Java 的 PreparedStatement , 進行執行，

通常大家忽略的是 order by 這部分，這一部分一般按順序依次為: 業務主表的時間欄位(逆序排序)、業務主表的單證編號、其它可見欄位、業務主表的主鍵，

不加排序(order by) 的分頁是耍流氓，沒意義的；排序欄位中必須包含用戶能理解的資料項，如果只按后臺 id/uuid 排序，用戶會覺得資料混亂無序；如果 order by 最后不加主鍵，有可能導致某些行的資料，既出現在第 n 頁、又出現在第 n+1 頁，

 

四、分頁查詢的性能

以上分頁查詢 SQL, 在單個表資料量為 1.3 億行的情況下，查詢時間范圍跨度為 15 天的情況下，每查詢一次改一下查詢時間范圍的小時數，多次測驗，分別用時:

0.047 秒、0.062 秒、0.047 秒、0.062 秒，

平均用時 0.055 秒，

性能可以說是非常的好，

=======歡迎轉載，轉載請注明出處，https://www.cnblogs.com/jacklondon/
 

標籤：其他

上一篇：SQL 入門教程：創建視圖

下一篇：TDSQL 在微信支付資料密集型應用落地實踐