单个表上亿行数据的主键、索引设计，及分页查询 _单个表

一，概述
一般而言，我们对关系型数据库系统，进行表结构设计时，会按数据的种类，进行分类，一般有如下种类:
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. 业务交易数据" 。

分类前缀数据量随时间
线性增长
定期删除唯一主键唯一索引时间字段索引外键索引主数据tm_NNY N/A 系统数据ts_NNY N/A 日志数据tl_YY--Y 接口数据ti_YYY Y 业务交易数据tt_YNY(可选)Y(可选)Y 关系数据tmr_
tsr_
ttr_
N/AN Y
二、业务交易表的主键、索引设计
业务交易数据，按通常的理解，一般有主表、明细表两种。
业务交易主表的主键，一般是 id/uuid；另在某个时间字段上，加上索引。比如:

文章插图
1 CREATE TABLE ow_pkg.TT_FLOW_IN 2 ( 3IN_UUID varchar2(32),--pk 4IN_SHEET_CD varchar2(255) NOT NULL,5IN_TIME date NOT NULL,--index column of time 67SEND_NODE_ID decimal(38,0) NOT NULL, 8RECEIVE_NODE_ID decimal(38,0) NOT NULL, 9 10CREATED_BY varchar2(20),11CREATED_DT date,12UPDATED_BY varchar2(20),13UPDATED_DT date,14UPDATE_CNT INTEGER DEFAULT 0NOT NULL15 )16 ;

文章插图
其中, in_uuid 为主键。
对于交易主表的主键，可用按 SQL 语法，创建 primary key, 也可以只创建成唯一索引(UNIQUE 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) ，正常情况下，数据的存储也是按时间顺序的，与创建聚集索引(clustered index)的效果相同。