本文介绍MySQL性能测试的场景信息。

本次性能测试将在MySQL中创建以下八张数据表。

  • CUSTOMER表
    CREATE TABLE `customer` (
      `C_CUSTKEY` int(11) NOT NULL,
      `C_NAME` varchar(25) NOT NULL,
      `C_ADDRESS` varchar(40) NOT NULL,
      `C_NATIONKEY` int(11) NOT NULL,
      `C_PHONE` varchar(15) NOT NULL,
      `C_ACCTBAL` decimal(12,2) NOT NULL,
      `C_MKTSEGMENT` varchar(10) NOT NULL,
      `C_COMMENT` varchar(117) NOT NULL,
      PRIMARY KEY (`C_CUSTKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • LINEITEM表
    CREATE TABLE `lineitem` (
      `L_ORDERKEY` bigint(20) NOT NULL,
      `L_PARTKEY` int(11) NOT NULL,
      `L_SUPPKEY` int(11) NOT NULL,
      `L_LINENUMBER` bigint(20) 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(1) NOT NULL,
      `L_LINESTATUS` varchar(1) NOT NULL,
      `L_SHIPDATE` date NOT NULL,
      `L_COMMITDATE` date NOT NULL,
      `L_RECEIPTDATE` date NOT NULL,
      `L_SHIPINSTRUCT` varchar(25) NOT NULL,
      `L_SHIPMODE` varchar(10) NOT NULL,
      `L_COMMENT` varchar(44) NOT NULL,
      PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`,`L_SHIPDATE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • NATION表
    CREATE TABLE `nation` (
      `N_NATIONKEY` int(11) NOT NULL,
      `N_NAME` varchar(25) NOT NULL,
      `N_REGIONKEY` int(11) NOT NULL,
      `N_COMMENT` varchar(152) DEFAULT NULL,
      PRIMARY KEY (`N_NATIONKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • ORDERS表
    CREATE TABLE `orders` (
      `O_ORDERKEY` bigint(20) NOT NULL,
      `O_CUSTKEY` int(11) NOT NULL,
      `O_ORDERSTATUS` varchar(1) NOT NULL,
      `O_TOTALPRICE` decimal(12,2) NOT NULL,
      `O_ORDERDATE` date NOT NULL,
      `O_ORDERPRIORITY` varchar(15) NOT NULL,
      `O_CLERK` varchar(15) NOT NULL,
      `O_SHIPPRIORITY` int(11) NOT NULL,
      `O_COMMENT` varchar(79) NOT NULL,
      PRIMARY KEY (`O_ORDERKEY`,`O_ORDERDATE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • PART表
    CREATE TABLE `part` (
      `P_PARTKEY` int(11) NOT NULL,
      `P_NAME` varchar(55) NOT NULL,
      `P_MFGR` varchar(25) NOT NULL,
      `P_BRAND` varchar(10) NOT NULL,
      `P_TYPE` varchar(25) NOT NULL,
      `P_SIZE` int(11) NOT NULL,
      `P_CONTAINER` varchar(10) NOT NULL,
      `P_RETAILPRICE` decimal(12,2) NOT NULL,
      `P_COMMENT` varchar(23) NOT NULL,
      PRIMARY KEY (`P_PARTKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • PARTSUPP表
    CREATE TABLE `partsupp` (
      `PS_PARTKEY` int(11) NOT NULL,
      `PS_SUPPKEY` int(11) NOT NULL,
      `PS_AVAILQTY` int(11) NOT NULL,
      `PS_SUPPLYCOST` decimal(12,2) NOT NULL,
      `PS_COMMENT` varchar(199) NOT NULL,
      PRIMARY KEY (`PS_PARTKEY`,`PS_SUPPKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • REGION表
    CREATE TABLE `region` (
      `R_REGIONKEY` int(11) NOT NULL,
      `R_NAME` varchar(25) NOT NULL,
      `R_COMMENT` varchar(152) DEFAULT NULL,
      PRIMARY KEY (`R_REGIONKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • SUPPLIER表
    CREATE TABLE `supplier` (
      `S_SUPPKEY` int(11) NOT NULL,
      `S_NAME` varchar(25) NOT NULL,
      `S_ADDRESS` varchar(40) NOT NULL,
      `S_NATIONKEY` int(11) NOT NULL,
      `S_PHONE` varchar(15) NOT NULL,
      `S_ACCTBAL` decimal(12,2) NOT NULL,
      `S_COMMENT` varchar(101) NOT NULL,
      PRIMARY KEY (`S_SUPPKEY`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在100GB数据规模下,缺省MySQL表结构无法完成全部测试,可以通过增加索引提升数据查询性能。本手册性能白皮书中公布的数据,均为有索引情况下的测试结果。

create index idx_li_sd on lineitem(l_shipdate);
create index idx_li_rf_ls on lineitem(l_returnflag,l_linestatus);
create index idx_p_pk on part(p_partkey);
create index idx_ps_pk on partsupp(ps_partkey);
create index idx_s_sk on supplier(s_suppkey);
create index idx_ps_sk on partsupp(ps_suppkey);
create index idx_p_s on part(p_size);
create index idx_p_t on part(p_type);
create index idx_s_nk on supplier(s_nationkey);
create index idx_n_nk on nation(n_nationkey);
create index idx_n_rk on nation(n_regionkey);
create index idx_r_rk on region(r_regionkey);
create index idx_r_n on region(r_name);
create index idx_ps_sc on partsupp(ps_supplycost);
create index idx_c_mk on customer(c_mktsegment);
create index idx_c_ck on customer(c_custkey);
create index idx_o_ck on orders(o_custkey);
create index idx_li_ok on lineitem(l_orderkey);
create index idx_o_ok on orders(o_orderkey);
create index idx_o_od on orders(o_orderdate);
create index idx_o_op on orders(o_orderpriority);
create index idx_li_sk on lineitem(l_suppkey);
create index idx_c_nk on customer(c_nationkey);
create index idx_li_dc on lineitem(l_discount);
create index idx_li_q on lineitem(l_quantity);
create index idx_n_n on nation(n_name);
create index idx_li_rf on lineitem(l_returnflag);
create index idx_li_sm on lineitem(l_shipmode);
create index idx_li_cd on lineitem(l_commitdate);
create index idx_li_rd on lineitem(l_receiptdate);
create index idx_li_pk on lineitem(l_partkey);
create index idx_p_b on part(p_brand);
create index idx_p_c on part(p_container);
create index idx_o_os on orders(o_orderstatus);