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

Z时代
2024-01-10
分类：综合

database

单个表数据量超过1亿的，需要精心设计表的主键、索引，其分页查询" title="分页查询">分页查询也不能乱写，否则性能不佳。

此文章特介绍作者心得。

一，概述

一般而言，我们对关系型数据库系统，进行表结构设计时，会按数据的种类，进行分类，一般有如下种类:

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；另在某个时间字段上，加上索引。比如:

 1CREATETABLE ow_pkg.TT_FLOW_IN
 2(
 3    IN_UUID varchar2(32),
 4    IN_SHEET_CD varchar2(255) NOTNULL,
 5    IN_TIME date NOTNULL,
 6
 7    SEND_NODE_ID decimal(38,0) NOTNULL,
 8    RECEIVE_NODE_ID decimal(38,0) NOTNULL,
 9
10    CREATED_BY varchar2(20),
11   CREATED_DT date,
12    UPDATED_BY varchar2(20),
13   UPDATED_DT date,
14    UPDATE_CNT INTEGERDEFAULT0NOTNULL
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 定义的表，数据库会自动进行主键数据的唯一性检查，如果数据量极大，这个唯一性检查的步骤有可能需要花费额外的时间，还不如使用普通索引，跳过主键数据的唯一性检查。

这里我们创建唯一性索引。

CREATEUNIQUEINDEX idx_tt_flow_in_in_uuid ON ow_pkg.TT_FLOW_IN(IN_UUID);

一般在交易主表的某个时间字段上，创建普通索引，或者聚集索引(clustered index)，比如:

CREATEINDEX idx_tt_flow_in_in_time ON ow_pkg.TT_FLOW_IN(IN_TIME);

交易表的数据，一般是 insert 多、delete 少，如果不定义主键、不创建聚集索引(clustered index)，正常情况下，数据的存储也是按时间顺序的。

对于业务交易明细表，一般创建明细表主键、在明细表指向主表的字段上创建普通索引。比如:

 1CREATETABLE 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) NOTNULL,
 6    STATUS_ID decimal(38,0),
 7    CONTAINER_ID decimal(38,0) NOTNULL,
 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 INTEGERDEFAULT0NOTNULL,
15)
16;
17CREATEUNIQUEINDEX idx_tt_flow_in_detail_in_detail_uuid ON ow_pkg.TT_FLOW_IN_DETAIL(IN_DETAIL_UUID);
18CREATEINDEX 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):

1SELECT*FROM (
2SELECT ROW_NUMBER() OVER (ORDERBY i.in_time desc,i.IN_SHEET_CD,i.in_uuid ) as rownum_xx
3     ,i.*
4from TT_FLOW_IN i
5where 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")
6and i.IN_SHEET_CD isnotnull
7)
8WHERE rownum_xx >=0and 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/

转载请注明出处:

http://www.cnblogs.com/jacklondon ;

欢迎访问 http://www.zheguisoft.com/ 并提建议。

以上是单个表上亿行数据的主键、索引设计，及分页查询的全部内容，来源链接： utcz.com/z/535908.html

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

其他人也看了：