This topic describes the performance test scenario of AnalyticDB for MySQL.

The following tables are created for the performance test on AnalyticDB for MySQL:

  • The CUSTOMER table
    CREATE TABLE `customer` (
     `c_custkey` INT NOT NULL,
     `c_name` VARCHAR NOT NULL,
     `c_address` VARCHAR NOT NULL,
     `c_nationkey` INT NOT NULL,
     `c_phone` VARCHAR NOT NULL,
     `c_acctbal` DECIMAL(12, 2) NOT NULL,
     `c_mktsegment` VARCHAR NOT NULL,
     `c_comment` VARCHAR NOT NULL,
      KEY c_mktsegment_key (c_mktsegment),
      KEY c_acctbal_key (c_acctbal),
    PRIMARY KEY (c_custkey)
    )
    DISTRIBUTE BY HASH(`c_custkey`)
    INDEX_ALL='N';
  • The LINEITEM table
    CREATE TABLE `lineitem` (
     `l_orderkey` BIGINT NOT NULL,
     `l_partkey` INT NOT NULL,
     `l_suppkey` INT NOT NULL,
     `l_linenumber` BIGINT NOT NULL,
     `l_quantity` DECIMAL(12, 2) NOT NULL,
     `l_extendedprice` DECIMAL(12, 2) NOT NULL,
     `l_discount` DECIMAL(12, 2) NOT NULL,
     `l_tax` DECIMAL(12, 2) NOT NULL,
     `l_returnflag` VARCHAR NOT NULL,
     `l_linestatus` VARCHAR NOT NULL,
     `l_shipdate` DATE NOT NULL,
     `l_commitdate` DATE NOT NULL,
     `l_receiptdate` DATE NOT NULL,
     `l_shipinstruct` VARCHAR NOT NULL,
     `l_shipmode` VARCHAR NOT NULL,
     `l_comment` VARCHAR NOT NULL,
      KEY l_shipdate_key (l_shipdate),
      KEY l_quantity_key (l_quantity),
      KEY l_discount_key (l_discount),
      KEY l_returnflag_key (l_returnflag),
      KEY l_receiptdate_key (l_receiptdate),
      KEY l_shipmode_key (l_shipmode),
      KEY l_shipinstruct_key (l_shipinstruct),
     PRIMARY KEY(l_orderkey,l_linenumber,l_shipdate)
    )
    DISTRIBUTE BY HASH(`l_orderkey`)
    PARTITION BY VALUE(`date_format(l_shipdate, '%Y%m')`)
    LIFECYCLE 90
    INDEX_ALL='N';
  • The NATION table
    CREATE TABLE `nation` (
     `n_nationkey` INT NOT NULL,
     `n_name` VARCHAR NOT NULL,
     `n_regionkey` INT NOT NULL,
     `n_comment` VARCHAR,
     PRIMARY KEY (n_nationkey)
    ) DISTRIBUTE BY BROADCAST INDEX_ALL='Y';
  • The ORDERS table
    CREATE TABLE `orders` (
     `o_orderkey` BIGINT NOT NULL,
     `o_custkey` INT NOT NULL,
     `o_orderstatus` VARCHAR NOT NULL,
     `o_totalprice` DECIMAL(12, 2) NOT NULL,
     `o_orderdate` DATE NOT NULL,
     `o_orderpriority` VARCHAR NOT NULL,
     `o_clerk` VARCHAR NOT NULL,
     `o_shippriority` INT NOT NULL,
     `o_comment` VARCHAR NOT NULL,
      KEY o_orderdate_key (o_orderdate),
      KEY o_orderstatus_key (o_orderstatus),
     PRIMARY KEY (o_orderkey,o_orderdate)
    )
    DISTRIBUTE BY HASH(`o_orderkey`)
    PARTITION BY VALUE(`date_format(o_orderdate, '%Y%m')`)
    LIFECYCLE 90
    INDEX_ALL='N';
  • The PART table
    CREATE TABLE `part` (
     `p_partkey` INT NOT NULL,
     `p_name` VARCHAR NOT NULL,
     `p_mfgr` VARCHAR NOT NULL,
     `p_brand` VARCHAR NOT NULL,
     `p_type` VARCHAR NOT NULL,
     `p_size` INT NOT NULL,
     `p_container` VARCHAR NOT NULL,
     `p_retailprice` DECIMAL(12, 2) NOT NULL,
     `p_COMMENT` VARCHAR NOT NULL,
      KEY p_type_key (p_type),
      KEY p_size_key (p_size),
      KEY p_brand_key (p_brand),
      KEY p_container_key (p_container),
    PRIMARY KEY (p_partkey)
    )
    DISTRIBUTE BY HASH(`p_partkey`)
    INDEX_ALL='N';
  • The PARTSUPP table
    CREATE TABLE `partsupp` (
     `ps_partkey` INT NOT NULL,
     `ps_suppkey` INT NOT NULL,
     `ps_availqty` INT NOT NULL,
     `ps_supplycost` DECIMAL(12, 2) NOT NULL,
     `ps_comment` VARCHAR NOT NULL,
     PRIMARY KEY (ps_partkey,ps_suppkey)
    )
    DISTRIBUTE BY HASH(`ps_partkey`)
    INDEX_ALL='N';
  • The REGION table
    CREATE TABLE `region` (
     `r_regionkey` INT NOT NULL,
     `r_name` VARCHAR NOT NULL,
     `r_comment` VARCHAR,
     PRIMARY KEY (r_regionkey)
    )
    DISTRIBUTE BY BROADCAST
    INDEX_ALL='Y';
  • The SUPPLIER table
    CREATE TABLE `supplier` (
     `s_suppkey` INT NOT NULL,
     `s_name` varchar NOT NULL,
     `s_address` VARCHAR NOT NULL,
     `s_nationkey` INT NOT NULL,
     `s_phone` VARCHAR NOT NULL,
     `s_acctbal` DECIMAL(12, 2) NOT NULL,
     `s_comment` VARCHAR NOT NULL,
     PRIMARY KEY (s_suppkey)
    )
    DISTRIBUTE BY HASH(`s_suppkey`)
    INDEX_ALL='N';