文档

构建测试表

更新时间:

本文介绍云原生数据仓库AnalyticDB MySQL版性能测试的场景信息。

本次性能测试将在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`;

  • 本页导读 (1)
文档反馈