构建测试表

本次性能测试将在AnalyticDB MySQL中创建以下八张数据表及一个视图。

• NATION表

  CREATE TABLE nation (
    n_nationkey int NOT NULL COMMENT '',
    n_name varchar NOT NULL COMMENT '',
    n_regionkey int NOT NULL COMMENT '',
    n_comment varchar COMMENT '',
    PRIMARY KEY (n_nationkey)
  ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

• REGION表

  CREATE TABLE region (
    r_regionkey int NOT NULL COMMENT '',
    r_name varchar NOT NULL COMMENT '',
    r_comment varchar COMMENT '',
    PRIMARY KEY (r_regionkey)
  ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

• PART表

  CREATE TABLE part (
    p_partkey int NOT NULL COMMENT '',
    p_name varchar NOT NULL COMMENT '',
    p_mfgr varchar NOT NULL COMMENT '',
    p_brand varchar(10) NOT NULL COMMENT '',
    p_type varchar NOT NULL COMMENT '',
    p_size int NOT NULL COMMENT '',
    p_container varchar(10) NOT NULL COMMENT '',
    p_retailprice decimal(15, 2) NOT NULL COMMENT '',
    p_comment varchar NOT NULL COMMENT '',
    PRIMARY KEY (p_partkey)
  ) DISTRIBUTED BY HASH (p_partkey) INDEX_ALL = 'Y' compression='lz4hc';

• SUPPLIER表

  CREATE TABLE supplier (
    s_suppkey int NOT NULL COMMENT '',
    s_name varchar NOT NULL COMMENT '',
    s_address varchar NOT NULL COMMENT '',
    s_nationkey int NOT NULL COMMENT '',
    s_phone varchar(15) NOT NULL COMMENT '',
    s_acctbal decimal(15, 2) NOT NULL COMMENT '',
    s_comment varchar NOT NULL COMMENT '',
    PRIMARY KEY (s_suppkey)
  ) DISTRIBUTED BY HASH (s_suppkey) INDEX_ALL = 'Y' compression='lz4hc';

• PARTSUPP表

  CREATE TABLE partsupp (
    ps_partkey int NOT NULL COMMENT '',
    ps_suppkey int NOT NULL COMMENT '',
    ps_availqty int NOT NULL COMMENT '',
    ps_supplycost decimal(15, 2) NOT NULL COMMENT '',
    ps_comment varchar NOT NULL COMMENT '',
    PRIMARY KEY (ps_partkey, ps_suppkey)
  ) DISTRIBUTED BY HASH (ps_partkey) INDEX_ALL = 'Y' compression='lz4hc';

• CUSTOMER表

  CREATE TABLE customer (
    c_custkey int NOT NULL COMMENT '',
    c_name varchar NOT NULL COMMENT '',
    c_address varchar NOT NULL COMMENT '',
    c_nationkey int NOT NULL COMMENT '',
    c_phone varchar(15) NOT NULL COMMENT '',
    c_acctbal decimal(15, 2) NOT NULL COMMENT '',
    c_mktsegment varchar(10) NOT NULL COMMENT '',
    c_comment varchar NOT NULL COMMENT '',
    PRIMARY KEY (c_custkey)
  ) DISTRIBUTED BY HASH (c_custkey) INDEX_ALL = 'Y' compression='lz4hc';

• ORDERS表

  CREATE TABLE orders (
    o_orderkey bigint NOT NULL COMMENT '',
    o_custkey int NOT NULL COMMENT '',
    o_orderstatus varchar(1) NOT NULL COMMENT '',
    o_totalprice decimal(15, 2) NOT NULL COMMENT '',
    o_orderdate date NOT NULL COMMENT '',
    o_orderpriority varchar(15) NOT NULL COMMENT '',
    o_clerk varchar(15) NOT NULL COMMENT '',
    o_shippriority int NOT NULL COMMENT '',
    o_comment varchar NOT NULL COMMENT '',
    KEY idx_o_custkey (o_custkey),
    KEY idx_o_orderdate (o_orderdate),
    KEY idx_o_orderkey (o_orderkey),
    KEY idx_o_orderstatus (o_orderstatus)
  ) DISTRIBUTED BY HASH (o_orderkey) partition by value(date_format(o_orderdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';

• LINEITEM表

  CREATE TABLE lineitem (
    l_orderkey bigint NOT NULL COMMENT '',
    l_partkey int NOT NULL COMMENT '',
    l_suppkey int NOT NULL COMMENT '',
    l_linenumber int NOT NULL COMMENT '',
    l_quantity decimal(15, 2) NOT NULL COMMENT '',
    l_extendedprice decimal(15, 2) NOT NULL COMMENT '',
    l_discount decimal(15, 2) NOT NULL COMMENT '',
    l_tax decimal(15, 2) NOT NULL COMMENT '',
    l_returnflag varchar(1) NOT NULL COMMENT '',
    l_linestatus varchar(1) NOT NULL COMMENT '',
    l_shipdate date NOT NULL COMMENT '',
    l_commitdate date NOT NULL COMMENT '',
    l_receiptdate date NOT NULL COMMENT '',
    l_shipinstruct varchar(25) NOT NULL COMMENT '',
    l_shipmode varchar(10) NOT NULL COMMENT '',
    l_comment varchar NOT NULL COMMENT '',
    KEY idx_l_orderkey (l_orderkey),
    KEY idx_l_partkey (l_partkey),
    KEY idx_l_receiptdate (l_receiptdate),
    KEY idx_l_returnflag (l_returnflag),
    KEY idx_l_shipdate (l_shipdate),
    KEY idx_l_shipinstruct (l_shipinstruct),
    KEY idx_l_shipmode (l_shipmode),
    KEY idx_l_suppkey (l_suppkey)
  ) DISTRIBUTED BY HASH (l_orderkey) partition by value(date_format(l_shipdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';

• REVENUE0视图

  CREATE VIEW `revenue0` AS
  SELECT
    `l_suppkey` supplier_no,
    `sum`(
      (
        `l_extendedprice` * (1 - `l_discount`)
      )
    ) total_revenue
  FROM
    lineitem
  WHERE
    (
      (`l_shipdate` >= DATE '1996-01-01')
      AND (
        `l_shipdate` < (
          DATE '1996-01-01' + INTERVAL '3' MONTH
        )
      )
    )
  GROUP BY
    `l_suppkey`;