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

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

文章插图
1 CREATE TABLE ow_pkg.TT_FLOW_IN_DETAIL 2 ( 3IN_DETAIL_UUID varchar2(32),--pk 4IN_UUID varchar2(32),--fk 5PROJ_ID decimal(38,0) NOT NULL, 6STATUS_ID decimal(38,0), 7CONTAINER_ID decimal(38,0) NOT NULL, 8REAL_QTY decimal(10,0), 9PLAN_QTY decimal(10,0),10CREATED_BY varchar2(20),11CREATED_DT date,12UPDATED_BY varchar2(20),13UPDATED_DT date,14UPDATE_CNT INTEGER DEFAULT 0NOT 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 iwhere 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) xxWHERE 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)