构建测试表
本文介绍云原生数据仓库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`;