構建測試表 - AnalyticDB

您需要在AnalyticDB for 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`;