构建测试表

您需要在云原生数据仓库 AnalyticDB MySQL 版中创建八张数据表和一个视图以进行性能测试。

开启Native计算引擎和XUANWU_V2引擎(可选)

重要

Native计算引擎目前在邀测中。仅3.2.2.1及以上版本集群支持执行SET命令开启Native计算引擎和XUANWU_V2引擎。3.2.2.1以下内核版本,请提交工单联系技术支持升级内核版本,再直接执行SET命令开启Native计算引擎和XUANWU_V2引擎。

升级内核版本时会重启集群,集群将发生连接闪断,请在业务低峰期执行该操作,并确保应用程序具备重连机制。

如果您只需测试AnalyticDB for MySQL原生计算引擎和XUANWU引擎的性能,可跳过该步骤,直接创建测试表。如果您想测试Native计算引擎和XUANWU_V2引擎的性能,请在构建测试表之前打开Native计算引擎和和XUANWU_V2引擎,方法如下:

SET ADB_CONFIG native_engine_opt_enabled = true;
SET ADB_CONFIG rc_ddl_engine_rewrite_xuanwuv2 = true;

创建测试表

  • 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 '',
      dummy VARCHAR,
      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 '',
      dummy VARCHAR,
      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 DOUBLE NOT NULL COMMENT '',
      p_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      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 DOUBLE NOT NULL COMMENT '',
      s_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      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 DOUBLE NOT NULL COMMENT '',
      ps_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      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 DOUBLE NOT NULL COMMENT '',
      c_mktsegment VARCHAR(10) NOT NULL COMMENT '',
      c_comment VARCHAR NOT NULL COMMENT '',
      dummy VARCHAR,
      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 DOUBLE 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 '',
      dummy VARCHAR,
      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 DOUBLE NOT NULL COMMENT '',
      l_extendedprice DOUBLE NOT NULL COMMENT '',
      l_discount DOUBLE NOT NULL COMMENT '',
      l_tax DOUBLE 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 '',
      dummy VARCHAR,
      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`;