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

2021-09-02 15:59

 

单个表数据量超过1亿的,需要精心设计表的主键、索引,其分页查询也不能乱写,否则性能不佳。此文章特介绍作者心得。

 

一,概述

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

序号 表分类 前缀 前缀含义

数据量随时间

线性增长

定期删除 唯一主键 唯一索引 时间字段索引 外键索引 备注
1 主数据 tm_ table of master data N N Y   N/A   分公司,产品,经销商
2 系统数据 ts_ table of system N N Y   N/A   用户权限控制,配置参数
3 日志数据 tl_ table of log Y Y - - Y   info,waring,error
4 接口数据 ti_ table of interface Y Y Y   Y   各种接口数据,临时保存若干天。
5 业务交易数据 tt_ table of transaction data Y N Y(可选) Y(可选) Y   销售订单,每日工时登记,物流运输单
6 关系数据

tmr_

tsr_

ttr_

table of system N/A N       Y 各类关系表数据

通常,数据量大的,都是上述"5. 业务交易数据"。

 

二、业务交易表的主键、索引设计

业务交易数据,按通常的理解,一般有主表、明细表两种。

业务交易主表的主键,一般是 id/uuid;另在某个时间字段上,加上索引。比如:


CREATE TABLE ow_pkg.TT_FLOW_IN
(
   IN_UUID varchar2(32),				--pk
   IN_SHEET_CD varchar2(255) NOT NULL,
   IN_TIME date NOT NULL,				--index column of time

   SEND_NODE_ID decimal(38,0) NOT NULL,
   RECEIVE_NODE_ID decimal(38,0) NOT NULL,

   CREATED_BY varchar2(20),
   CREATED_DT date,
   UPDATED_BY varchar2(20),
   UPDATED_DT date,
   UPDATE_CNT INTEGER DEFAULT 0  NOT NULL
)
;		
		

其中, 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)的效果相同。

 

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


CREATE TABLE ow_pkg.TT_FLOW_IN_DETAIL
(
   IN_DETAIL_UUID varchar2(32),				--pk
   IN_UUID varchar2(32),					--fk
   PROJ_ID decimal(38,0) NOT NULL,
   STATUS_ID decimal(38,0),
   CONTAINER_ID decimal(38,0) NOT NULL,
   REAL_QTY decimal(10,0),
   PLAN_QTY decimal(10,0),
   CREATED_BY varchar2(20),
   CREATED_DT date,
   UPDATED_BY varchar2(20),
   UPDATED_DT date,
   UPDATE_CNT INTEGER DEFAULT 0  NOT NULL,
)
;
CREATE UNIQUE INDEX idx_tt_flow_in_detail_in_detail_uuid ON ow_pkg.TT_FLOW_IN_DETAIL(IN_DETAIL_UUID); 
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 数据库,其它数据库 SQL 基本一样):


SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY i.in_time desc,i.IN_SHEET_CD,i.in_uuid ) as rownum_xx
    ,i.*
    from TT_FLOW_IN i
    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')
    and i.IN_SHEET_CD is not null
) xx
WHERE rownum_xx >= 0 and rownum_xx <= 20;
		

以上 SQL 的 where 中,可以使用动态参数。比如对于 java ,可以使用占位符 ? ,使用 Java 的 PreparedStatement , 进行执行。

 

通常大家忽略的是 order by 这部分。这一部分一般按顺序依次为: 业务主表的时间字段(逆序排序)、业务主表的单证编号、其它用户可见字段、业务主表的主键。

不加排序(order by) 的分页是耍流氓,没意义的;

排序字段中必须包含用户能理解的数据项。如果只按后台 id/uuid 排序,用户会觉得数据混乱无序;如果 order by 最后不加主键,有可能导致某些行的数据,既出现在第 n 页、又出现在第 n+1 页,特别是使用 Hibernate 之类的软件时。

 

按时间范围搜索,折桂周转包装管理系统

按时间范围搜索,折桂周转包装管理系统

 

截图示例(折桂打印平台系统+折桂上传平台系统,时间范围搜索, web 前端使用 jqGrid)

按时间范围搜索,折桂打印平台系统 + 折桂上传平台系统, 时间范围搜索, web 前端使用 jqGrid

 

四、分页查询的性能

以上分页查询 SQL, 在单个表数据量为 1.3 亿行的情况下,查询时间范围跨度为 15 天的情况下,每查询一次改一下查询时间范围的小时数,多次测试,分别用时:

0.047 秒、0.062 秒、0.047 秒、0.062 秒。

平均用时 0.055 秒。

性能可以说是非常的好。

 

===以下为 2021/9/8 补充 ====

五、分页查询的用户自定义排序

有的 web 程序,允许用户点击某列,将查询结果数据按此列进行数据排序。

此时 order by 按顺序依次为: web 界面用户选定的排序字段(升序/逆序, 来自业务交易主表/明细表)、业务主表的时间字段(逆序排序)、其它可见字段(业务交易主表)、业务主表的主键。

 

截图示例(折桂打印平台系统+折桂上传平台系统,web 前端使用 jqGrid)

截图示例(用户自选排序字段,折桂打印平台系统+折桂上传平台系统, web 前端使用 jqGrid)

用户自选排序字段,折桂打印平台系统 + 折桂上传平台系统, web 前端使用 jqGrid

 

六、页面查询分页性能优化其它技巧

某些数据库,比如 Oracle, MS SQL Server,执行 SQL select count(*) from ... where ... 会很耗时间,此时,不查询总行数、不计算总页数,会极大提高查询翻页的整体性能。具体软件界面,可提供对应的用户操作选项。

 

截图示例(不计算总记录数以提高性能,折桂打印平台系统+折桂上传平台系统, web 前端使用 jqGrid)

不计算总记录数以提高性能,折桂打印平台系统 + 折桂上传平台系统, web 前端使用 jqGrid

 

七、不同数据库的分页查询 SQL

SQL 标准中的分页查询,写成如下格式:


SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY i.in_time desc,i.IN_SHEET_CD,i.in_uuid ) as rownum_xx
    ,i.*
    from TT_FLOW_IN i
    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')
    and i.IN_SHEET_CD is not null
) xx
WHERE rownum_xx >= 0 and rownum_xx <= 20;
		

部分数据库,对于 SQL 标准,支持得不到位。有的需要略改一下,比如 Oracle 的早期版本,能运行的 SQL 如下:


SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY i.in_time desc,i.IN_SHEET_CD,i.in_uuid ) as rownum_xx
    ,i.*
    from TT_FLOW_IN i
    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')
    and i.IN_SHEET_CD is not null
) 
WHERE rownum_xx >= 0 and rownum_xx <= 20;
		

差别在于第 7 行的 SQL 别名。

 

 

欢迎转载,转载请注明出处: https://www.zheguisoft.com/staff_blogs/jacklondon_chen/2022, 及 https://my.oschina.net/jacklondon/blog/5220057,https://www.cnblogs.com/jacklondon/p/big_table_design_and_paing.html