文档首页/ 数据仓库服务 GaussDB(DWS)/ 最佳实践/ 性能调优/ 基于表结构设计和调优提升GaussDB(DWS)查询性能/ 步骤4：创建新表并加载数据
更新时间：2024-10-10 GMT+08:00
查看PDF
分享

步骤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)支持多分布列特性，可以更好地满足数据分布的均匀性要求。

相关文档