更新时间:2024-10-10 GMT+08:00
分享

步骤4:创建新表并加载数据

为每张表选择了存储方式、压缩级别、分布方式和分布列后,使用这些属性创建表并重新加载数据。以便对比表设计前后的系统性能。

  1. 执行CREATE TABLE创建表前,删除前面创建的表。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    DROP TABLE store_sales;
    DROP TABLE date_dim;
    DROP TABLE store;
    DROP TABLE item;
    DROP TABLE time_dim;
    DROP TABLE promotion;
    DROP TABLE customer_demographics;
    DROP TABLE customer_address;
    DROP TABLE household_demographics;
    DROP TABLE customer;
    DROP TABLE income_band;
    
    DROP FOREIGN TABLE obs_from_store_sales_001;
    DROP FOREIGN TABLE obs_from_date_dim_001;
    DROP FOREIGN TABLE obs_from_store_001;
    DROP FOREIGN TABLE obs_from_item_001;
    DROP FOREIGN TABLE obs_from_time_dim_001;
    DROP FOREIGN TABLE obs_from_promotion_001;
    DROP FOREIGN TABLE obs_from_customer_demographics_001;
    DROP FOREIGN TABLE obs_from_customer_address_001;
    DROP FOREIGN TABLE obs_from_household_demographics_001;
    DROP FOREIGN TABLE obs_from_customer_001;
    DROP FOREIGN TABLE obs_from_income_band_001;
    

  2. 创建具有存储方式和分布方式的表。

    限于篇幅,此处仅给出再次创建store_sales的语法。请从附录设计调优后第二次创建表中拷贝其他表的语法进行创建。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    CREATE TABLE store_sales
    (
        ss_sold_date_sk           integer                       ,
        ss_sold_time_sk           integer                       ,
        ss_item_sk                integer               not null,
        ss_customer_sk            integer                       ,
        ss_cdemo_sk               integer                       ,
        ss_hdemo_sk               integer                       ,
        ss_addr_sk                integer                       ,
        ss_store_sk               integer                       ,
        ss_promo_sk               integer                       ,
        ss_ticket_number          bigint               not null,
        ss_quantity               integer                       ,
        ss_wholesale_cost         decimal(7,2)                  ,
        ss_list_price             decimal(7,2)                  ,
        ss_sales_price            decimal(7,2)                  ,
        ss_ext_discount_amt       decimal(7,2)                  ,
        ss_ext_sales_price        decimal(7,2)                  ,
        ss_ext_wholesale_cost     decimal(7,2)                  ,
        ss_ext_list_price         decimal(7,2)                  ,
        ss_ext_tax                decimal(7,2)                  ,
        ss_coupon_amt             decimal(7,2)                  ,
        ss_net_paid               decimal(7,2)                  ,
        ss_net_paid_inc_tax       decimal(7,2)                  ,
        ss_net_profit             decimal(7,2)                  
    ) 
    WITH (ORIENTATION = column,COMPRESSION=middle)
    DISTRIBUTE BY hash (ss_item_sk);
    

  3. 为这些表加载样例数据
  4. 在基准表中记录加载时间。

    基准

    优化前

    优化后

    加载时间(11张表)

    341584ms

    257241ms

    占用存储

    Store_Sales

    42GB

    -

    Date_Dim

    11MB

    -

    Store

    232kB

    -

    Item

    110MB

    -

    Time_Dim

    11MB

    -

    Promotion

    256kB

    -

    Customer_Demographics

    171MB

    -

    Customer_Address

    170MB

    -

    Household_Demographics

    504kB

    -

    Customer

    441MB

    -

    Income_Band

    88kB

    -

    总存储空间

    42GB

    -

    查询执行时间

    查询1

    14552.05ms

    -

    查询2

    27952.36ms

    -

    查询3

    17721.15ms

    -

    总执行时间

    60225.56ms

    -

  5. 执行ANALYZE更新统计信息。

    1
    ANALYZE;
    

    返回ANALYZE后,表示执行成功。

    1
    ANALYZE
    

  6. 检查数据倾斜性。

    对于Hash分表策略,如果分布列选择不当,可能导致数据倾斜,查询时出现部分DN的I/O短板,从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查,以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性

    1
    SELECT a.count,b.node_name FROM (SELECT count(*) AS count,xc_node_id FROM table_name GROUP BY xc_node_id) a, pgxc_node b WHERE a.xc_node_id=b.node_id ORDER BY a.count desc;
    

    其中xc_node_id对应DN,一般来说,不同DN的数据量相差5%以上即可视为倾斜,如果相差10%以上就必须要调整分布列。GaussDB(DWS)支持多分布列特性,可以更好地满足数据分布的均匀性要求。

相关文档