您需要在云原生数据仓库 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`;
文档内容是否对您有帮助?