devbox
Li_阴宅
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL性能测试——tpch工具

作者：Li_阴宅 | 2024-08-06 03:03:30

赞

踩

tpch

文章目录

1.背景

tpch是TPC(Transaction Processing Performance Council)组织提供的工具包。用于进行OLAP测试，以评估商业分析中决策支持系统（DSS）的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改，强调测试的是数据库、平台和I/O性能，关注查询能力。

TPC-H是业界常用的一套基准，由TPC委员会制定发布，用于评测数据库的分析型查询能力。TPC-H查询包含8张数据表、22条复杂的SQL查询，大多数查询包含若干表Join、子查询和Group by聚合等。

在使用tpch工具之前，先了解一下并行查询（OLAP）。

并行查询（OLAP）举例（以阿里云的ploarDB为例）
PolarDB MySQL 8.0集群版推出并行查询（Parallel Query）框架。并行查询默认为关闭状态，当开启并行查询后，查询数据量到达一定阈值，就会自动启动并行查询框架，从而使查询耗时下降。

说明:可以通过设置loose_max_parallel_degree参数来开启并行查询，如何设置集群参数请参见设置集群参数。
loose_max_parallel_degree参数说明如下：
最小值为0（关闭并行查询）。
最大值为1024。
建议设置并行查询参数为16。

PolarDB MySQL 8.0集群版在存储层将数据分片到不同的线程上，多个线程并行计算，将结果流水线汇聚到总线程，最后由总线程做简单归并将结果返回给用户，提高查询效率。

并行查询利用多核CPU的并行处理能力，以8核32 GB独享规格的集群为例，并行查询示意图如下所示。
在这里插入图片描述

相关链接：
tpch下载地址（TPC-H需要完成注册后才可以下载）：
http://tpc.org/tpc_documents_current_versions/download_programs
tpch工具github仓库地址：
https://github.com/electrum/tpch-dbgen
参考阿里云tpch工具使用：
https://help.aliyun.com/document_detail/146099.html?spm=a2c4g.11186623.6.773.4797364bCS7aO5

2.tpch的安装

  • step1、解压下载好的安装包
yum install unzip -y
unzip tpch-mysql.zip

  • 1
  • 2
  • step2、进入dbgen目录，进行编译
cd tpch-mysql
cd dbgen
yum install gcc-c++

  • 1
  • 2
  • 3

执行编译

make

  • 1
  • step3、生成数据文件（约12G数据，时间较长,几分钟,耐心等待）
./dbgen -s 12

  • 1
  • step4、进入数据库建表(使用root或高权限用户)有的需要手动建库tpcd

准备工作到此结束，下面开始tpch工具的使用。

说明：统一选取相同同等规格的实例进行测试，写入12G数据量，测试3次，取最终平均值进行对标（单位：s）。

3.tpch工具使用

  • step5、登录到数据库（以阿里云RDSMySQL为例）
mysql -hrm-uf6cbh8aq31a8prtj.mysql.rds.aliyuncs.com -P3306  -umyroot -p****** --local-infile

  • 1

注明：这里登录mysql，加了–local-infile参数，否则在后面从本地导入数据的时候有可能报：
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

use tpcd;

  • 1

建表

\. /root/tpch-mysql/dbgen/dss.ddl

  • 1

创建索引、外键等

\. /root/tpch-mysql/dbgen/dss.ri

  • 1

如果想看看外键和主键是否添加成功，有没有添加对，可以执行 SHOW CREATE TABLE 表名（觉得默认格式很碍眼可以试试看加上\G垂直打印）

  • step6、导入数据
    共需导入8个表，part、region、nation、customer、supplier、orders、partsupp、lineitem，大表的导入时间很长。
    一共8张表，先来导入前5张表（以下语句在mysql中执行）
load data local infile '/root/tpch-mysql/dbgen/part.tbl' into table part fields terminated by '|';
load data local infile '/root/tpch-mysql/dbgen/region.tbl' into table region fields terminated by '|';
load data local infile '/root/tpch-mysql/dbgen/nation.tbl' into table nation fields terminated by '|';
load data local infile '/root/tpch-mysql/dbgen/customer.tbl' into table customer fields terminated by '|';
load data local infile '/root/tpch-mysql/dbgen/supplier.tbl' into table supplier fields terminated by '|';

  • 1
  • 2
  • 3
  • 4
  • 5

如图表示导入成功：
在这里插入图片描述
##再来看另外三张表，注意：这里不要执行。因为这三张表比较大，最好是使用脚本拆解执行。

load data local infile '/root/tpch-mysql/dbgen/orders.tbl' into table orders fields terminated by '|';
load data local infile '/root/tpch-mysql/dbgen/partsupp.tbl' into table partsupp fields terminated by '|';
load data local infile '/root/tpch-mysql/dbgen/lineitem.tbl' into table lineitem fields terminated by '|';

  • 1
  • 2
  • 3

八张表的关系如下：
在这里插入图片描述
其中Nation和Region数据量与SF无关；Nation是固定25，Region的5不是必然；LineItem的数据量是不严格的SF倍数。

  • step7、脚本生成另外三张表
    退出mysql，来到/root/tpch-mysql/dbgen目录
    先看看表有多少行记录。
[root@localhost dbgen]# cat orders.tbl | wc -l
18000000
[root@localhost dbgen]# cat partsupp.tbl | wc -l
9600000
[root@localhost dbgen]# cat lineitem.tbl | wc -l
71985077

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

编辑脚本

vi split_import_file.sh

  • 1

修改（三处）见以下注释

#! /bin/bash
#文件名不带.tbl ！！！,即对应表名
#read -p "please input filename: " filename
filename=orders      # 修改处1：替换文件名orders、partsupp、lineitem、
#获取原文件总行数totalline
totalline=$(cat $filename.tbl | wc -l)
echo totalline=$totalline
#要分割成的每个小文件的行数line
line=1000000   
a=`expr $totalline / $line`    
b=`expr $totalline % $line` 
#获取小文件个数filenum
if (( $b==0 ))   
then
    filenum=$a
else
    filenum=`expr $a + 1`
fi
echo filenum=$filenum
#进行文件分割,分割后第一个小文件名后缀为i,i最小值为1
i=1        # 修改处2：38 修改为1
while(( i<=$filenum ))
do
#每个小文件要截取行数在原文件范围min,max 
    p=`expr $i - 1`
    min=`expr $p \* $line + 1`
    max=`expr $i \* $line`
    sed -n "$min,$max"p ./$filename.tbl > ./$filename.tbl.$i
#将小文件导入数据库，mysql登录信息及小文件路径根据实际修改       # 修改处3：mysql连接信息（加了--local-infile）
#根据自己创建的数据库的用户名、密码、数据库实例的ip、端口号、已经tpc安装包的路径信息进行修改。
    mysql -umyroot -p'******' -h10.185.147.201 -P32307 --local-infile -Dtpcd -e "load data local infile '/root/tpch-mysql/dbgen/$filename.tbl.$i' into table $filename fields terminated by '|';"
    i=`expr $i + 1`
done

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

改完之后保存，退出# :wq

按以上修改编辑好三个脚本，在后台运行脚本（此过程，在拆解数据表，拆解的过程中，同时往数据库中导入数据，所以不需要再执行数据导入的操作）三个脚本同时开启：

nohup sh split_import_file_partsupp.sh>split_import_partsupp.log 2>&1 &
nohup sh split_import_file_orders.sh>split_import_orders.log 2>&1 &
nohup sh split_import_file_lineitem.sh>split_import_lineitem.log 2>&1 &

  • 1
  • 2
  • 3

运行过程中，可以查看进程，看是否跑结束了，# ps -ef 进程号

ps -ef | grep split

  • 1
  • step8、 修改执行测试脚本
    先修改脚本中数据库的信息（账号名、密码、数据库实例的IP、端口号）
vi tpch-benchmark-olap.sh

  • 1

修改mysql连接信息

#!/bin/sh
PATH=$PATH:/usr/local/bin
export PATH
#set -u
#set -x
#set -e
. ~/.bash_profile > /dev/null 2>&1
exec 3>&1 4>&2 1>> tpch-benchmark-olap-`date +'%Y%m%d%H%M%S'`.log 2>&1
I=1
II=3
while [ $I -le $II ]
do
N=1
T=23
while [ $N -lt $T ]
do
  if [ $N -lt 10 ] ; then
    NN='0'$N
  else
    NN=$N
  fi
  echo "query $NN starting"
# /etc/init.d/mysql restart           # 修改这里mysql连接信息
  time mysql -h10.185.147.201 -P32307 -umyroot -p****** -Dtpcd < ./queries/tpch_${NN}.sql
  echo "query $NN ended!"
  N=`expr $N + 1`
  echo -e
  echo -e
done
 I=`expr $I + 1`
done

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • step9、后台执行脚本
nohup sh tpch-benchmark-olap.sh >output.log 2>&1 &

  • 1

在/tpch-mysql/dbgen/路径下，会自动生成日志文件tpch-benchmark-olap-20210528143245.log
查看一下日志：

cat tpch-benchmark-olap-20210528143245.log

  • 1

日志输出如下：

query 01 starting
mysql: [Warning] Using a password on the command line interface can be insecure.
l_returnflag    l_linestatus    sum_qty sum_base_price  sum_disc_price  sum_charge      avg_qty avg_price       avg_disc        count_order
A       F       453004927.00    679255118036.63 645289010691.4043       671106139941.958450     25.499290       38234.734227    0.050007        17765394

real    3m2.575s
user    0m0.024s
sys     0m0.008s
query 01 ended!
...

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

静静等待22条语句执行完毕。

4.tpcd 22条SQL语句分析

使用TPC-H进行性能测试，需要有很多工作配合才能获得较高性能，如建立索引，表数据的合理分布（使用表空间和聚簇技术）等。

这里从查询优化技术的角度，对TPC-H的22条查询语句和主流数据库执行每条语句对应的查询执行计划进行分析，目的在于了解各个主流数据库的查询优化技术，以TPC-H实例进一步掌握查询优化技术，对比主流数据库的实现情况对查询优化技术融会贯通。

  • 1.Q1：价格统计报告查询
    Q1语句是查询lineItems的一个定价总结报告。在单个表lineitem上查询某个时间段内，对已经付款的、已经运送的等各类商品进行统计，包括业务量的计费、发货、折扣、税、平均价格等信息。
    Q1语句的特点是：带有分组、排序、聚集操作并存的单表查询操作。这个查询会导致表上的数据有95%到97%行被读取到。
    Q1的查询语句如下：
select 
    l_returnflag, //返回标志
    l_linestatus, 
    sum(l_quantity) as sum_qty, //总的数量
    sum(l_extendedprice) as sum_base_price, //聚集函数操作
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, 
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, 
    avg(l_quantity) as avg_qty, 
    avg(l_extendedprice) as avg_price, 
    avg(l_discount) as avg_disc, 
    count(*) as count_order //每个分组所包含的行数
from 
    lineitem
where 
    l_shipdate <= date'1998-12-01' - interval '90' day //时间段是随机生成的
group by //分组操作
    l_returnflag, 
    l_linestatus
order by //排序操作
    l_returnflag, 
    l_linestatus;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 2.Q2: 最小代价供货商查询
    Q2语句查询获得最小代价的供货商。得到给定的区域内，对于指定的零件（某一类型和大小的零件），哪个供应者能以最低的价格供应它，就可以选择哪个供应者来订货。
    Q2语句的特点是：带有排序、聚集操作、子查询并存的多表查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。
    Q2的查询语句如下：
select
    s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment /*查询供应者的帐户余额、名字、国家、零件的号码、生产者、供应者的地址、电话号码、备注信息 */
from
    part, supplier, partsupp, nation, region //五表连接
where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and p_size = [SIZE] //指定大小，在区间[1, 50]内随机选择
    and p_type like '%[TYPE]' //指定类型，在TPC-H标准指定的范围内随机选择
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = '[REGION]' //指定地区，在TPC-H标准指定的范围内随机选择
    and ps_supplycost = ( //子查询
        select
            min(ps_supplycost) //聚集函数
        from
            partsupp, supplier, nation, region //与父查询的表有重叠
        where
            p_partkey = ps_partkey
            and s_suppkey = ps_suppkey
            and s_nationkey = n_nationkey
            and n_regionkey = r_regionkey
            and r_name = '[REGION]'
    )
order by //排序
    s_acctbal desc,
    n_name,
    s_name,
    p_partkey;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 3.Q3: 运送优先级查询
    Q3语句查询得到收入在前10位的尚未运送的订单。在指定的日期之前还没有运送的订单中具有最大收入的订单的运送优先级（订单按照收入的降序排序）和潜在的收入（潜在的收入为l_extendedprice * (1-l_discount)的和）。
    Q3语句的特点是：带有分组、排序、聚集操作并存的三表查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。
    Q3的查询语句如下：
select
l_orderkey,
sum(l_extendedprice*(1-l_discount)) as revenue, //潜在的收入，聚集操作
o_orderdate,
o_shippriority
from
customer, orders, lineitem //三表连接
where
c_mktsegment = '[SEGMENT]' //在TPC-H标准指定的范围内随机选择
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '[DATE]' //指定日期段，在在[1995-03-01, 1995-03-31]中随机选择
and l_shipdate > date '[DATE]'
group by //分组操作
l_orderkey, //订单标识
o_orderdate, //订单日期
o_shippriority //运输优先级
order by //排序操作
revenue desc, //降序排序，把潜在最大收入列在前面
o_orderdate;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 4.Q4: 订单优先级查询
    Q4语句查询得到订单优先级统计值。计算给定的某三个月的订单的数量，在每个订单中至少有一行由顾客在它的提交日期之后收到。
    Q4语句的特点是：带有分组、排序、聚集操作、子查询并存的单表查询操作。子查询是相关子查询。
    Q4的查询语句如下：
select
o_orderpriority, //订单优先级
count(*) as order_count //订单优先级计数
from orders //单表查询
where
o_orderdate >= date '[DATE]'
and o_orderdate < date '[DATE]' + interval '3' month //指定订单的时间段--某三个月，DATE是在1993年1月和1997年10月之间随机选择的一个月的第一天
and exists ( //子查询
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by //按订单优先级分组
o_orderpriority
order by //按订单优先级排序
o_orderpriority;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 5.Q5: 某地区供货商为公司带来的收入查询
    Q5语句查询得到通过某个地区零件供货商而获得的收入（收入按sum(l_extendedprice * (1 -l_discount))计算）统计信息。可用于决定在给定的区域是否需要建立一个当地分配中心。
    Q5语句的特点是：带有分组、排序、聚集操作、子查询并存的多表连接查询操作。
    Q5的查询语句如下：
select
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue //聚集操作
from
customer,orders,lineitem,supplier,nation,region //六表连接
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = '[REGION]' //指定地区，在TPC-H标准指定的范围内随机选择
and o_orderdate >= date '[DATE]' //DATE是从1993年到1997年中随机选择的一年的1月1日
and o_orderdate < date '[DATE]' + interval '1' year
group by //按名字分组
n_name
order by //按收入降序排序，注意分组和排序子句不同
revenue desc;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 6.Q6: 预测收入变化查询
    Q6语句查询得到某一年中通过变换折扣带来的增量收入。这是典型的“what-if”判断，用来寻找增加收入的途径。预测收入变化查询考虑了指定的一年中折扣在“DISCOUNT-0.01”和“DISCOUNT＋0.01”之间的已运送的所有订单，求解把l_quantity小于quantity的订单的折扣消除之后总收入增加的数量。
    Q6语句的特点是：带有聚集操作的单表查询操作。查询语句使用了BETWEEN-AND操作符，有的数据库可以对BETWEEN-AND进行优化。
    Q6的查询语句如下：
select
sum(l_extendedprice*l_discount) as revenue //潜在的收入增加量
from
lineitem //单表查询
where
l_shipdate >= date '[DATE]' //DATE是从[1993, 1997]中随机选择的一年的1月1日
and l_shipdate < date '[DATE]' + interval '1' year //一年内
and l_discount between [DISCOUNT] - 0.01 and [DISCOUNT] + 0.01 //between
and l_quantity < [QUANTITY]; // QUANTITY在区间[24, 25]中随机选择

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 7.Q7: 货运盈利情况查询
    Q7语句是查询从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量用以帮助重新谈判货运合同。
    Q7语句的特点是：带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询。
    Q7的查询语句如下：
select
supp_nation, //供货商国家
cust_nation, //顾客国家
l_year, sum(volume) as revenue //年度、年度的货运收入
from ( //子查询
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,lineitem,orders,customer,nation n1,nation n2 //六表连接
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and ( // NATION2和NATION1的值不同，表示查询的是跨国的货运情况
(n1.n_name = '[NATION1]' and n2.n_name = '[NATION2]')
or (n1.n_name = '[NATION2]' and n2.n_name = '[NATION1]')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) as shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 8.Q8: 国家市场份额查询
    Q8语句是查询在过去的两年中一个给定零件类型在某国某地区市场份额的变化情况。
    Q8语句的特点是：带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。
    TPC-H标准定义了Q8语句等价的变形SQL，与上述查询语句格式上基本相同，主要是目标列使用了不同的表达方式，在此不再赘述。
    Q8的查询语句如下：
select
o_year, //年份
sum(case
when nation = '[NATION]'//指定国家，在TPC-H标准指定的范围内随机选择
then volume
else 0
end) / sum(volume) as mkt_share //市场份额：特定种类的产品收入的百分比；聚集操作
from //子查询
(select
extract(year from o_orderdate) as o_year, //分解出年份
l_extendedprice * (1-l_discount) as volume, //特定种类的产品收入
n2.n_name as nation
from
part,supplier,lineitem,orders,customer,nation n1,nation n2,region //八表连接
where
p_partkey = l_partkey
and s_suppkey = l_suppkey
and l_orderkey = o_orderkey
and o_custkey = c_custkey
and c_nationkey = n1.n_nationkey
and n1.n_regionkey = r_regionkey
and r_name = '[REGION]' //指定地区，在TPC-H标准指定的范围内随机选择
and s_nationkey = n2.n_nationkey
and o_orderdate between date '1995-01-01' and date '1996-12-31' //只查95、96年的情况
and p_type = '[TYPE]' //指定零件类型，在TPC-H标准指定的范围内随机选择
) as all_nations
group by //按年分组
o_year
order by //按年排序
o_year;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 9.Q9: 产品类型利润估量查询
    Q9语句是查询每个国家每一年所有被定购的零件在一年中的总利润。
    Q9语句的特点是：带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。子查询中使用了LIKE操作符，有的查询优化器不支持对LIKE操作符进行优化。
    Q9的查询语句如下：
select
nation,
o_year,
sum(amount) as sum_profit //每个国家每一年所有被定购的零件在一年中的总利润
from
(select
n_name as nation, //国家
extract(year from o_orderdate) as o_year, //取出年份
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount //利润
from
part,supplier,lineitem,partsupp,orders,nation //六表连接
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%[COLOR]%' //LIKE操作，查询优化器可能进行优化
) as profit
group by //按国家和年份分组
nation,
o_year
order by //按国家和年份排序，年份大者靠前
nation,
o_year desc;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 10.Q10: 货运存在问题的查询
    Q10语句是查询每个国家在某时刻起的三个月内货运存在问题的客户和造成的损失。
    Q10语句的特点是：带有分组、排序、聚集操作并存的多表连接查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。
    Q10的查询语句如下：
select
c_custkey, c_name, //客户信息
sum(l_extendedprice * (1 - l_discount)) as revenue, //收入损失
c_acctbal,
n_name, c_address, c_phone, c_comment //国家、地址、电话、意见信息等
from
customer, orders, lineitem, nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '[DATE]' // DATE是位于1993年一月到1994年十二月中任一月的一号
and o_orderdate < date '[DATE]' + interval '3' month //3个月内
and l_returnflag = 'R' //货物被回退
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 11.Q11: 库存价值查询
    Q11语句是查询库存中某个国家供应的零件的价值。
    Q11语句的特点是：带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。
    Q11的查询语句如下：
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value //聚集操作，商品的总价值
from
partsupp, supplier, nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = '[NATION]'
group by
ps_partkey having //带有HAVING子句的分组操作
sum(ps_supplycost * ps_availqty) > ( //HAVING子句中包括有子查询
select
sum(ps_supplycost * ps_availqty) * [FRACTION] //子查询中存在聚集操作；FRACTION为0.0001/SF1
from
partsupp, supplier, nation //与父查询的表连接一致
where //与父查询的WHEWR条件一致
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = '[NATION]' //指定国家
)
order by //按商品的价值降序排序
value desc;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 12.Q12: 货运模式和订单优先级查询
    Q12语句查询获得货运模式和订单优先级。可以帮助决策：选择便宜的货运模式是否会导致消费者更多的在合同日期之后收到货物，而对紧急优先命令产生负面影响。
    1 SF，Scale Factor ，数据库的比例因子。TPC-H标准规定，测试数据库的比例因子必须从下列固定值中选择：1，10,30,100,1000,3000,10000 (相当于1GB，10GB,30GB,100GB,1000GB,3000GB,10000GB)。数据库的大小缺省定义为1（例如：SF＝1；近似于1GB）。
    Q12语句的特点是：带有分组、排序、聚集操作并存的两表连接查询操作。
    Q12的查询语句如下：
select
l_shipmode,
sum(case //聚集操作
when o_orderpriority ='1-URGENT' //OR运算，二者满足其一即可，选出URGENT或HIGH的
or o_orderpriority ='2-HIGH'
then 1
else 0
end) as high_line_count,
sum(case
when o_orderpriority <> '1-URGENT' //AND运算，二者都不满足，非URGENT非HIGH的
and o_orderpriority <> '2-HIGH'
then 1
else 0
end) as low_line_count
from
orders,lineitem
where
o_orderkey = l_orderkey
and l_shipmode in ('[SHIPMODE1]', '[SHIPMODE2]') /* 指定货运模式的类型，在TPC-H标准指定的范围内随机选择，SHIPMODE2必须有别于SHIPMODE1 */
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '[DATE]' //从1993年到1997年中任一年的一月一号
and l_receiptdate < date '[DATE]' + interval '1' year //1年之内
group by //分组操作
l_shipmode
order by //排序操作
l_shipmode;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 13.Q13: 消费者订单数量查询
    Q13语句查询获得消费者的订单数量，包括过去和现在都没有订单记录的消费者。
    Q13语句的特点是：带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。
    Q13的查询语句如下：
select
c_count, count(*) as custdist //聚集操作，统计每个组的个数
from //子查询
(select
c_custkey,
count(o_orderkey)
from
customer left outer join orders on //子查询中包括左外连接操作
c_custkey = o_custkey
and o_comment not like ‘%[WORD1]%[WORD2]%’ //LIKE操作
//WORD1 为以下四个可能值中任意一个：special、pending、unusual、express
//WORD2 为以下四个可能值中任意一个：packages、requests、accounts、deposits
group by //子查询中的分组操作
c_custkey
)as c_orders (c_custkey, c_count)
group by //分组操作
c_count
order by //排序操作
custdist desc, //从大到小降序排序
c_count desc;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 14.Q14: 促销效果查询
    Q14语句查询获得某一个月的收入中有多大的百分比是来自促销零件。用以监视促销带来的市场反应。
    Q14语句的特点是：带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。
    Q14的查询语句如下：
select
100.00 * sum(case
when p_type like 'PROMO%' //促销零件
then l_extendedprice*(1-l_discount) //某一特定时间的收入
else 0
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
lineitem, part
where
l_partkey = p_partkey
and l_shipdate >= date '[DATE]' // DATE是从1993年到1997年中任一年的任一月的一号
and l_shipdate < date '[DATE]' + interval '1' month;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 15.Q15: 头等供货商查询
    Q15语句查询获得某段时间内为总收入贡献最多的供货商（排名第一）的信息。可用以决定对哪些头等供货商给予奖励、给予更多订单、给予特别认证、给予鼓舞等激励。
    Q15语句的特点是：带有分排序、聚集、聚集子查询操作并存的普通表与视图的连接操作。
    Q15的查询语句如下：
create view revenue[STREAM_ID](supplier_no, total_revenue) as //创建复杂视图（带有分组操作）
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount)) //获取供货商为公司带来的总利润
from
lineitem
where
l_shipdate >= date '[DATE]' //DATE 是从1993年一月到1997年十月中任一月的一号
and l_shipdate < date '[DATE]' + interval '3' month //3个月内
group by //分组键与查询对象之一相同
l_suppkey;


//查询语句
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,revenue[STREAM_ID] //普通表与复杂视图进行连接操作
where
s_suppkey = supplier_no
and total_revenue = (//聚集子查询
select
max(total_revenue)
from
revenue[STREAM_ID] //聚集子查询从视图获得数据
)
order by
s_suppkey;
//删除视图
drop view revenue[STREAM_ID];

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 16.Q16: 零件/供货商关系查询
    Q16语句查询获得能够以指定的贡献条件供应零件的供货商数量。可用于决定在订单量大，任务紧急时，是否有充足的供货商。
    Q16语句的特点是：带有分组、排序、聚集、去重、NOT IN子查询操作并存的两表连接操作。
    Q16的查询语句如下：
select
    p_brand,
    p_type,
    p_size,
    count(distinct ps_suppkey) as supplier_cnt //聚集、去重操作
from
    partsupp,
    part
where
    p_partkey = ps_partkey
    and p_brand <> '[BRAND]'
// BRAND＝Brand＃MN ，M和N是两个字母，代表两个数值，相互独立，取值在1到5之间
    and p_type not like '[TYPE]%' //消费者不感兴趣的类型和尺寸
and p_size in ([SIZE1], [SIZE2], [SIZE3], [SIZE4], [SIZE5], [SIZE6], [SIZE7], [SIZE8])
//TYPEX是在1到50之间任意选择的一组八个不同的值
    and ps_suppkey not in ( //NOT IN子查询，消费者排除某些供货商
        select
            s_suppkey
        from
            supplier
        where
            s_comment like '%Customer%Complaints%'
    )
group by //分组操作
    p_brand,
    p_type,
    p_size
order by //排序操作
    supplier_cnt desc, //按数量降序排列，按品牌、种类、尺寸升序排列
    p_brand,
    p_type,
    p_size;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 17.Q17: 小订单收入查询
    Q17语句查询获得比平均供货量的百分之二十还低的小批量订单。对于指定品牌和指定包装类型的零件，决定在一个七年数据库的所有订单中这些订单零件的平均项目数量（过去的和未决的）。如果这些零件中少于平均数20％的订单不再被接纳，那平均一年会损失多少呢？所以此查询可用于计算出如果没有没有小量订单，平均年收入将损失多少（因为大量商品的货运，将降低管理费用）。
    Q17语句的特点是：带有聚集、聚集子查询操作并存的两表连接操作。
    Q17的查询语句如下：
select
    sum(l_extendedprice) / 7.0 as avg_yearly //聚集操作
from
    lineitem, part
where
    p_partkey = l_partkey
    and p_brand = '[BRAND]' /*指定品牌。 BRAND＝’Brand#MN’ ，M和N是两个字母，代表两个数值，相互独立，取值在1到5之间 */
    and p_container = '[CONTAINER]' //指定包装类型。在TPC-H标准指定的范围内随机选择
    and l_quantity < ( //聚集子查询
        select
            0.2 * avg(l_quantity)
        from
            lineitem
        where
            l_partkey = p_partkey
    );

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 18.Q18: 大订单顾客查询
    Q18语句查询获得比指定供货量大的供货商信息。可用于决定在订单量大，任务紧急时，验证否有充足的供货商。
    Q18语句的特点是：带有分组、排序、聚集、IN子查询操作并存的三表连接操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。
    Q18的查询语句如下：
select
    c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, //基本信息
    sum(l_quantity) //订货总数
from
    customer, orders, lineitem
where
    o_orderkey in ( //带有分组操作的IN子查询
        select
            l_orderkey
        from
            lineitem
        group by 
            l_orderkey having
            sum(l_quantity) > [QUANTITY] // QUANTITY是位于312到315之间的任意值
    )
    and c_custkey = o_custkey
    and o_orderkey = l_orderkey 
group by
    c_name,
    c_custkey,
    o_orderkey,
    o_orderdate,
    o_totalprice
order by
    o_totalprice desc,
    o_orderdate;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 19.Q19: 折扣收入查询
    Q19语句查询得到对一些空运或人工运输零件三个不同种类的所有订单的总折扣收入。零件的选择考虑特定品牌、包装和尺寸范围。本查询是用数据挖掘工具产生格式化代码的一个例子。
    Q19语句的特点是：带有分组、排序、聚集、IN子查询操作并存的三表连接操作。
    Q19的查询语句如下：
select
    sum(l_extendedprice * (1 - l_discount) ) as revenue
from
    lineitem, part
where
(
    p_partkey = l_partkey
    and p_brand = ‘[BRAND1]’ /*特定品牌。BRAND1、BRAND2、BRAND3＝‘Brand＃MN’，M和N是两个字母，代表两个数值，相互独立，取值在1到5之间 */
    and p_container in ( ‘SM CASE’, ‘SM BOX’, ‘SM PACK’, ‘SM PKG’) //包装范围
    and l_quantity >= [QUANTITY1] and l_quantity <= [QUANTITY1] + 10 /* QUANTITY1 是1到10之间的任意取值 */
    and p_size between 1 and 5 //尺寸范围
    and l_shipmode in (‘AIR’, ‘AIR REG’) //运输模式，如下带有阴影的粗体表示的条件是相同的，存在条件化简的可能
    and l_shipinstruct = ‘DELIVER IN PERSON’
)
or
(
    p_partkey = l_partkey
    and p_brand = ‘[BRAND2]’
    and p_container in (‘MED BAG’, ‘MED BOX’, ‘MED PKG’, ‘MED PACK’)
    and l_quantity >= [QUANTITY2] and l_quantity <= [QUANTITY2] + 10 /* QUANTITY2 是10到20之间的任意取值 */
    and p_size between 1 and 10
    and l_shipmode in (‘AIR’, ‘AIR REG’)
    and l_shipinstruct = ‘DELIVER IN PERSON’
)
or
(
    p_partkey = l_partkey
    and p_brand = ‘[BRAND3]’
    and p_container in ( ‘LG CASE’, ‘LG BOX’, ‘LG PACK’, ‘LG PKG’)
    and l_quantity >= [QUANTITY3] and l_quantity <= [QUANTITY3] + 10 /* QUANTITY3 是20到30之间的任意取值 */
    and p_size between 1 and 15
    and l_shipmode in (‘AIR’, ‘AIR REG’)
    and l_shipinstruct = ‘DELIVER IN PERSON’
);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 20.Q20: 供货商竞争力查询
    Q20语句查询确定在某一年内，找出指定国家的能对某一零件商品提供更有竞争力价格的供货货。所谓更有竞争力的供货商，是指那些零件有过剩的供货商，超过供或商在某一年中货运给定国的某一零件的50％则为过剩。
    Q20语句的特点是：带有排序、聚集、IN子查询、普通子查询操作并存的两表连接操作。
    Q20的查询语句如下：
select
    s_name, s_address
from
    supplier, nation
where
    s_suppkey in ( //第一层的IN子查询
        select
            ps_suppkey
        from
            partsupp
        where
            ps_partkey in ( //第二层嵌套的IN子查询
                select
                    p_partkey
                from
                    part
                where
                    p_name like '[COLOR]%' //COLOR为产生P_NAME的值的列表中的任意值
            )
            and ps_availqty > (//第二层嵌套的子查询
                select
                    0.5 * sum(l_quantity) //聚集子查询
                from
                    lineitem
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date('[DATE]’) //DATE为在1993年至1997年的任一年的一月一号
                    and l_shipdate < date('[DATE]’) + interval ‘1’ year //1年内
            )
    )
    and s_nationkey = n_nationkey
    and n_name = '[NATION]' //TPC-H标准定义的任意值
order by
    s_name;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 21.Q21: 不能按时交货供货商查询
    Q21语句查询获得不能及时交货的供货商。
    Q21语句的特点是：带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。
    Q21的查询语句如下：
select
    s_name, count(*) as numwait
from
    supplier, lineitem l1, orders, nation
where
    s_suppkey = l1.l_suppkey
    and o_orderkey = l1.l_orderkey
    and o_orderstatus = 'F'
    and l1.l_receiptdate > l1.l_commitdate
    and exists ( //EXISTS子查询
        select
            *
        from
            lineitem l2
        where
            l2.l_orderkey = l1.l_orderkey
            and l2.l_suppkey <> l1.l_suppkey
    )
    and not exists ( //NOT EXISTS子查询
        select
            *
        from
            lineitem l3
        where
            l3.l_orderkey = l1.l_orderkey
            and l3.l_suppkey <> l1.l_suppkey
            and l3.l_receiptdate > l3.l_commitdate
    )
    and s_nationkey = n_nationkey
    and n_name = '[NATION]' //TPC-H标准定义的任意值
group by
    s_name
order by
    numwait desc,
    s_name;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 22.Q22: 全球销售机会查询
    Q22语句查询获得消费者可能购买的地理分布。本查询计算在指定的国家，比平均水平更持肯定态度但还没下七年订单的消费者数量。能反应出普通消费者的的态度，即购买意向。
    Q22语句的特点是：带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。
    Q22的查询语句如下：
select
    cntrycode,
    count(*) as numcust,
    sum(c_acctbal) as totacctbal
from ( //第一层子查询
    select
        substring(c_phone from 1 for 2) as cntrycode,
        c_acctbal
    from
        customer
    where
        // I1…I7是在TPC-H中定义国家代码的可能值中不重复的任意值
        substring(c_phone from 1 for 2) in ('[I1]','[I2]’,'[I3]','[I4]','[I5]','[I6]','[I7]')
        and c_acctbal > (//第二层聚集子查询
            select
                avg(c_acctbal)
            from
                customer
            where
                c_acctbal > 0.00
                and substr (c_phone from 1 for 2) 
                                in ('[I1]','[I2]','[I3]','[I4]','[I5]','[I6]','[I7]')
        )
        and not exists (//第二层NOT EXISTS子查询
            select
                *
            from
                orders
            where
                o_custkey = c_custkey
        )
        ) as custsale
group by
    cntrycode
order by
    cntrycode;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

5.案例分析

以各大云厂商的MySQL为例，测试结果如下。根据该表格分析对比得出各个云厂商MySQL的优劣。
在这里插入图片描述

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Li_阴宅/article/detail/935531
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号