一,概述
一般而言,我们对关系型数据库系统,进行表结构设计时,会按数据的种类,进行分类,一般有如下种类:
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_ | N | N | Y | N/A | ||
系统数据 | ts_ | N | N | Y | N/A | ||
日志数据 | tl_ | Y | Y | - | - | Y | |
接口数据 | ti_ | Y | Y | Y | Y | ||
业务交易数据 | tt_ | Y | N | Y(可选) | Y(可选) | Y | |
关系数据 |
tmr_ tsr_ ttr_ |
N/A | N | Y |
二、业务交易表的主键、索引设计
业务交易数据,按通常的理解,一般有主表、明细表两种。
业务交易主表的主键,一般是 id/uuid;另在某个时间字段上,加上索引。比如:
1 CREATE TABLE ow_pkg.TT_FLOW_IN 2 ( 3 IN_UUID varchar2(32), --pk 4 IN_SHEET_CD varchar2(255) NOT NULL, 5 IN_TIME date NOT NULL, --index column of time 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)。之所以会有这种的做法,是因为有的数据库,比如 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)的效果相同。
对于业务交易明细表,一般创建明细表主键、在明细表指向主表的字段上创建普通索引。比如:
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):
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 页。
截图示例(按时间范围搜索,折桂周转包装管理系统)
---------------------
截图示例(按时间范围搜索,折桂打印平台系统+折桂上传平台系统,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)
六、页面查询分页性能优化其它技巧
某些数据库,比如 Oracle, MS SQL Server,执行 SQL select count(*) from ... where ... 会很耗时间,此时,不查询总行数、不计算总页数,会极大提高查询翻页的整体性能。具体软件界面,可提供用户操作选项。
截图示例(不计算总记录数以提高性能,折桂打印平台系统+折桂上传平台系统,web 前端使用 jqGrid)
七、不同数据库的分页查询 SQL
SQL 标准中的分页查询,写成如下格式:
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 ) xx 8 WHERE rownum_xx >= 0 and rownum_xx <= 20;
部分数据库,对于 SQL 标准,支持得不到位。有的需要略改一下,比如 Oracle 的早期版本,能运行的 SQL 如下:
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;
差别在于第 7 行。
=======欢迎转载,转载请注明出处,https://www.cnblogs.com/jacklondon/