T O P

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

  • By - 楼主

  • 2021-09-03 14:34:47
  • 一,概述

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

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

     1 CREATE TABLE ow_pkg.TT_FLOW_IN
     2 (
     3    IN_UUID varchar2(32),
     4    IN_SHEET_CD varchar2(255) NOT NULL,
     5    IN_TIME date NOT NULL,
     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), 或普通索引(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),正常情况下,数据的存储也是按时间顺序的。


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

     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):

    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;


    以上 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/
     

    本帖子中包含资源

    您需要 登录 才可以下载,没有帐号?立即注册