您当前的位置:首页 > 电脑百科 > 数据库 > 百科

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

时间:2021-10-19 09:16:11  来源:博客园  作者:

一,概述

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

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/
 


转载请注明出处: http://www.cnblogs.com/jacklondon ; 欢迎访问 http://www.zheguisoft.com/ 并提建议。


Tags:单个表   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
一,概述 一般而言,我们对关系型数据库系统,进行表结构设计时,会按数据的种类,进行分类,一般有如下种类: 1)主数据,其数据量基本稳定,不随时间而线性增长。比如,分公司,产品,经销商。...【详细内容】
2021-10-19  Tags: 单个表  点击:(42)  评论:(0)  加入收藏
▌简易百科推荐
1增1.1【插入单行】insert [into] <表名> (列名) values (列值)例:insert into Strdents (姓名,性别,出生日期) values (&#39;开心朋朋&#39;,&#39;男&#39;,&#39;1980/6/15&#3...【详细内容】
2021-12-27  快乐火车9d3    Tags:SQL   点击:(1)  评论:(0)  加入收藏
最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询...【详细内容】
2021-12-23  linux上的码农    Tags:sql   点击:(9)  评论:(0)  加入收藏
《开源精选》是我们分享Github、Gitee等开源社区中优质项目的栏目,包括技术、学习、实用与各种有趣的内容。本期推荐的HasorDB 是一个全功能数据库访问工具,提供对象映射、丰...【详细内容】
2021-12-22  GitHub精选    Tags:HasorDB   点击:(5)  评论:(0)  加入收藏
作者丨Rafal Grzegorczyk译者丨陈骏策划丨孙淑娟【51CTO.com原创稿件】您是否还在手动对数据库执行各种脚本?您是否还在浪费时间去验证数据库脚本的正确性?您是否还需要将...【详细内容】
2021-12-22    51CTO  Tags:Liquibase   点击:(3)  评论:(0)  加入收藏
场景描述:由于生产环境的表比较复杂,字段很多。这里我们做下简化,只为说明今天要聊的问题。有两张表 tab1,tab2: tab1 数据如下: tab2 数据如下: 然后给你看下,我用来统计 name=&#3...【详细内容】
2021-12-20  Bald    Tags:SQL   点击:(5)  评论:(0)  加入收藏
前言知识无底,学海无涯,知识点虽然简单,但是比较多,所以将MySQL的基础写出来,方便自己以后查找,还有就是分享给大家。一、SQL简述1.SQL的概述Structure Query Language(结构化查...【详细内容】
2021-12-16  谣言止于独立思考    Tags:SQL基础   点击:(13)  评论:(0)  加入收藏
前言作为一名测试工程师,工作中在对测试结果进行数据比对的时候,或多或少要和数据库打交道的,要和数据库打交道,那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙...【详细内容】
2021-12-14  柠檬班软件测试    Tags:SQL   点击:(15)  评论:(0)  加入收藏
话说C是面向内存的编程语言。数据要能存得进去,取得出来,且要考虑效率。不管是顺序存储还是链式存储,其寻址方式总是很重要。顺序存储是连续存储。同质结构的数组通过其索引表...【详细内容】
2021-12-08  小智雅汇    Tags:数据存储   点击:(17)  评论:(0)  加入收藏
概述DBConvert Studio 是一款强大的跨数据库迁移和同步软件,可在不同数据库格式之间转换数据库结构和数据。它将成熟、稳定、久经考验的 DBConvert 和 DBSync 核心与改进的现...【详细内容】
2021-11-17  雪竹聊运维    Tags:数据库   点击:(26)  评论:(0)  加入收藏
一、前言 大家好,我是小诚,《从0到1-全面深刻理解MySQL系列》已经来到第四章,这一章节的主要从一条SQL执行的开始,由浅入深的解析SQL语句由客户端到服务器的完整执行流程,最...【详细内容】
2021-11-09  woaker    Tags:SQL   点击:(35)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条