MYSQL6查询性能测试TPCH

1、5.6查询性能测试报告网易DBA王洪权MydbalifeTPC-H介绍：TPC-H测量在商业分析中决策支持系统（DSS）的性能。DSS是一种计算机应用程序，它分析商业数据展现出来使用户/管理者可以更容易得进行商业决策，例如供求管理、客户满意度分析、市场份额分析等。TPC-H模拟了商业环境中的分析端，大量的数据被细化，可以帮助企业进行可靠的商业决策，其中包含一整套面向商业的特殊查询和并发数据修改内容。该基准中选择的查询 和数据库中的数据都具有广泛的全行业关联性。这种基准测试所描述的决策支持系统可检查大量的数据，所执行的查询也具有很高的复杂度。并且，TPC-H会基于数据库的大小将结果分类.TPC-

2、H是决策支持数据库的基准测试，它包含了一整套面向商业的ad-hoc查询和并发数据修改，强调测试的是数据库、平台和I/O性能，关注查询能力。Tpc-h数据库由定义好的八个表组成Tpc-H数据模型测试环境DELL PowerEdge 1750内存大小2GMysql版本：5.6.5-m8-log MySQL Community Server (GPL)(最新)5.5.23-55-log Percona Server (GPL), Release rel25.3, Revision 240 ( percona最新)Tpch库生成：1 下载 tpc_h, /tpch/s

3、pec/tpch 2 8 0.zip :2创建MySQL用户、数据库、及授权mysql -u root -pmysql> CREATE USER 'tpch''%' IDENTIFIED BY 'tpch'mysql> CREATE DA TABASE tpch;mysql> GRANT ALL ON tpch.* to 'tpch''%'3然后在tpch文件目录下，把 makefile复制并改名成 makefile，接着修改 makefile文件 shell> cp makefile.su

4、ite makefile shell> vim makefilemakefile中相应项后面填写：CC = gcc # Current values for DA TABASE are: INFORMIX, DB2, TDAT (Teradata)# Current values for MACHINE are:# Current values for WORKLOAD are:DATABASE= SQLSERVERMACHINE = LINUXWORKLOAD = TPCH4修改tpch.h文件修改其中的 SQLSERVER段为：#ifdef SQLSERVER#define GEN_

5、QUERY_PLAN#define START_TRAN#define END_TRAN#define SET_OUTPUT#define SET_ROWCOUNT#define SET_DBASE#endif执行make;5生成需要用的数据shell> ./dbgen -s 1-s数据规模因子，1为1G的数据量；这步骤在dbgen下边生成数据，以tblSQLSERVER, SYBASEATT, DOS, HP, IBM, ICL, MVS,SGI, SUN, U2200, VMS, LINUX, WIN32 TPCH"EXPLAIN;""START TRA

6、NSACTION;n""COMMIT;n”"""limit %d;n""use %s;n"结尾。6创建库中相关表mysql -e "source /home/tpch/dbgen/dss.ddl"载入数据myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINA TED BY '|'"myqsl -e "LO

7、AD DATA INFILE '/home/tpch/dbgen/orders.tbl' INTO TABLE ORDERS FIELDSTERMINATED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINA TED BY '|'"myqsl -e "LOAD DA TA INFILE '/home/tpch/dbgen/nation

8、.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINA TED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/part.tbl' INTO TABLE PART FIELDS TERMINATED B

9、Y '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/region.tbl' INTO TABLE REGION FIELDSTERMINATED BY '|'"myqsl -e "LOAD DATA INFILE '/home/tpch/dbgen/supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINA TED BY '|'"7修改tpch目录下的dss.ri文件1. 删除&

10、quot;CONNECT TO TPCD;"2. 删除所有的"TPCH.”(注意有个点)3. 删除所有的"COMMIT WORK;”(注意分号也要删除)8执行该文件，创建相关约束。myqsl -e "source /home/tpch/dbgen/dss.ri"实际上dss.ri这个文件是为库中的表添加相关约束，先添加主键，后添加外键。Dss.ri文件内容如下：-For table REGIONALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);-For table NATIONALTER TABL

11、E NATION ADD PRIMARY KEY (N_NATIONKEY);-For table PARTALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY);-For table SUPPLIERALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);-For table PARTSUPPALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);-For table CUSTOMERALTER TABLE CUSTOMER ADD PRIMARY K

12、EY (C_CUSTKEY);-For table LINEITEMALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);-For table ORDERSALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY);为各个表添加外键。ALTER TABLE NATION ADD FOREIGN KEY (N_REGIONKEY) references REGION(R_REGIONKEY);ALTER TABLE SUPPLIER ADD FOREIGN KEY (S_NATIONKEY

13、) referencesNATION(N_NATIONKEY);ALTER TABLECUSTOMERADDFOREIGNKEY(C_NATIONKEY)referencesNATION(N_NATIONKEY);-For table PARTSUPPALTER TABLEPARTSUPPADDFOREIGNKEY(PS_SUPPKEY)referencesSUPPLIER(S_SUPPKEY);ALTER TABLEPARTSUPPADDFOREIGNKEY(PS_PARTKEY)referencesPART(P_PARTKEY);-For table ORDERSALTER TABLEOR

14、DERSADDFOREIGNKEY(O_CUSTKEY)referencesCUSTOMER(C_CUSTKEY);-For table LINEITEMALTER TABLELINEITEMADDFOREIGNKEY(L_ORDERKEY)referencesORDERS(O_ORDERKEY);ALTER TABLE LINEITEM ADDFOREIGN KEY(L_PARTKEY,L_SUPPKEY)referencesPARTSUPP(PS_PARTKEY, PS_SUPPKEY);由于查询中使用的是小写表名，而使用dss.ddl生成的表名是大写的，所以转换表名成小写alter ta

15、ble NATION rename nation;alter table SUPPLIER rename supplier;alter table REGION rename region;alter table PARTSUPP rename partsupp;alter table PART rename part;alter table ORDERS rename orders;alter table LINEITEM rename lineitem;alter table CUSTOMER rename customer;cd /home/tpch/dbgen/queries这下已经成

16、了 16个sql文件，为测试性能sql.可以放在数据库内通过source执行，也可以采用以下方式执行。./qgen -c tpch -s 1 1（这步可以这样，也可以直接在mysql那里执行：source /home/asd/1.sql）MRR(Multi Range Read ) 测试测试语句:DBT-3? QUERY 10select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment fromcustom

17、er, orders, lineitem, nation wherec_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date('1993-10-01') and o_orderdate < date('1993-10-01') + '3 month' and l_returnflag = 'R'and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal,

18、c_phone, n_name, c_address, c_comment order by revenue desc执行计划5.6中执行计划idhselect-tyoet加t推衅沌七倬加？1fey.| 听1SIMPLE11洲供尸口倘工郎我七:顷住册如CJ.DERDATE:NULL1颈iLStcm：reqjrF捋河尿办3PF.：WY1SDiPLE低ticP俏PE椭11匚机Eliehm，西L皿配帝l15.5中执行计划idJSElst_typeLjtabletypepossibldceysIkeyLJkeyjen芷f| r邮rH:r-HINPLErH做忏r-"HrangerPF.：Rvj

19、0_CJSTKE7,D.0RDER0ATC 瓦邺汩r-HOJ)R)ERD4TEr 3- - -NLL133215IHP.Eojncmereq_refmw41|：h.3fder5,O_CLSTKEY11SIMPLEnaticieqjefPHI倾FFW4tpch.custmer.CJttTIONkEY11SIMPLElineitEfti音LJORDERKEYIJWER 旧4tF：h,order5,0_0RDERKE1Extrausing where; using Temporary; using filesortusing where测试为了测试的目的，我使用了TPC-H Query #10并且运

20、行他在 TPC-H数据。的配置如下。同时注意在mysqloptimizer_switch=optimizer_switch=optimizer_switch=优化器设置：'index_condition_pushdown=off'mrr=on '' mrrcostbased=off ' read_rnd_buffer_size=4M (only on MySQL 5.6)注意在这里我关闭了 ICP优化这个特性，目的是为了达到测试的目的，因为我想查看 一下单个的MRR对于性能的的影响， 同时也注意我把 mrr_cost_based给关掉了。这是因为 基于成

21、本的算法在选择执行计划的时候计算MRR花费的成本。MRR使用的场景见下图:在内存负载为主的情况下现在让我们看看当所要查询的数据完全装在到内存中时候，MRR工作是怎么样的。测试采用了以内存工作为主的情况，InnoDB缓冲池的大小设置为512M ,缓冲池中所有的数据已经预热，使相关数据页已经完全加载在缓冲池中。注意，在开始看基准测试结果之前，我 们确定了要查询的InnoDB数据集的大小是大约1.2G.完全在内存的时候：可以看到查询时间基本上差不多。统计值查询耗时柱状图如下:Mysql 5.52min 16.90sMysql 5.6 启 用MRR2m14sIO为主的情况下，设置BP大小为512M,数

22、据不能完全装在在BP中的时候：MySQL5.6CounterMySQL5.5MySQL5.6MySQL5.5 read rnd bufeNameCreated_tr_size=4Mread_rnd_bufer_size=4Mmp_disk_t ablesHandler r0000ead_keyHandler r283752340796283752340796ead_nextHandler_r286093286093286093286093ead_rnd_n ext Innodb_bu ffer pool37944379443794437944read_ahea d017776017776Inn

23、odb_buffer_pool_ read_requ ests1551628146196415513841461847Innodb_buffer_pool_77336403717734740370readsInnodb_da ta_read1269256192(1.2G)954863616(910M)1269436416(1.2G)954847232(910M)Innodb_da ta_reads77350581617736158160Innodb_pa ges_read77335581467734658145Innodb_ro ws_read398157455197398157455197S

24、elect_sca n1111Sort_scan1111查询耗时10m57.584s2m18.757s6m31.233s2m43.835s注意这里有两个参数需要理解：Handler_read_key Innodb_rows_read (为什么MRR会更多呢？)因为正常的话没有启用MRR的情况下，会先读2级别索引，找出rowid然后再读以及索引，找到具体记录。这个过程会调用存储引擎，所以调用影响的参数 Handler_read_XXX ,和 Innodb_rows_read这2个参数每调用一次会分别加1, MRR读记录的时候会分开调用，会引起Handler_read_XXX，和Innodb_ro

25、ws_read分别加2，这会看起来 MRR做事情更糟糕， 实时上不是的，查询仍单读同一个索引的表的记录，MRR使得磁盘的读更加顺序。从表格中可以看出 Innodb_buffer_pool_reads(物理读次数)，5.5(77336) VS 5.6 (40370),可 以得出物理读越来越少，因为使用了MRR,使得数据访问变得顺序，避免了对同一个数据页的多次访问，Innodb_buffer_pool_read_ahead (预读的次数)，5.5 (0) VS 5.6 ( 17776)可 以看出，5.6由于使用了 MRR,使得访问格式变得顺序，因次预读次数大大增加。最后再从Innodb_data_

26、read (数据读取总量来看)，5.5 (1.2G) VS 5.6 (910M )，可以看出5.6 大大的减少了数据的读取量。因此可以得出MRR使得大量的随即读取变得顺序，在顺序访问的同时，因为 mysql有预读的功能，所以会看到预读次数会大大增加。统计值查询耗时Mysql 5.510m57.584sMysql 5.62m18.757sMySQL5.5 read_rnd_buf er_size=4M 6m31.233sMySQL5.6 read_rnd_buf er_size=4M 2m43.835s柱状图如下:NysqlS, 5lysdl 5. 6启用眼KMyEQl 5,5 (read_rn

27、OLifer_size (4H)总结:经过测试，发现新版本的 mysql 5.6开启MRR的预热要比 mysql 5.5.23预热时间快接近5倍。但是也可以看出MRR也很依赖于read_rnd_bufer_size的大小，当设置一定大的时候，性能提升很大，MRR使得随即读取转换成顺序读，大大的减少了数据的访问量，节省了 BP的资源，同时采用 MRR也可以使得查询时间大大缩短。MRR测试脚本：#!/bin/bashsed -i 's/innodb_buffer_pool_size = 512M/innodb_buffer_pool_size = 2M/' /etc/f grep

28、-i innodb_buffer_pool_size /etc/f service mysql restartmysql -e "flush status;"|grep -v "Logging"mysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Handl er_read_key|Handler_read_next|Handler_read_rnd_next|Innodb_buffer_p

29、ool_read_ahead|Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innod b_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'sed -i 's/innodb_buffer_pool_size = 2M/innodb_buffer_pool_size = 512M/' /etc/f grep -i innodb_buffer_

30、pool_size /etc/f service mysql restartmysql -e "set global optimizer_switch='index_condition_pushdown=off;"|grep -v "Logging"mysql -e "set global optimizer_switch='mrr=on'"|grep -v "Logging"mysql -e "set global optimizer_switch='mrr_cost_b

31、ased=off;"|grep -v "Logging"#mysql -e "set global read_rnd_buffer_size=4194304;"|grep -v "Logging" sh sar.sh & sh iostat.sh &time mysql -e "use tpch;select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount) as revenue, c_acctbal, n_name, c_addre

32、ss, c_phone, c_commentfromcustomer, orders, lineitem, nationwherec_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= '1993-08-01'and o_orderdate < date_add( '1993-08-01' ,interval '3' month)and l_returnflag = 'R'and c_nationkey = n_nationkeygrou

33、p byc_custkey, c_name, c_acctbal, c_phone, n_name,c_address,c_commentorder byrevenue descLIMIT 20;"|grep -v "Logging"|tail -n3|egrep -w "real|user|sys"Mysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Handl er_read_

34、key|Handler_read_next|Handler_read_rnd_next|Innodb_buffer_pool_read_ahead|Innodb_ buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innod b_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'BKA ( Batched Key Acces® 测试Benchmark :出于测试的目

35、的，我使用了 TPC-H Query #3并且运行它在 TPC-H数据库，innodb dataset 大小317M注意出于测试的目的查询缓存的cache也是禁用。这里分别测试了默认情况下，两种版本查询的不同，还有分别调整了join_buffer_size和read_rnd_bufer_size的情况下，性能的对比。MySQL 5.6配置如下：optimizer_switch='index_condition_pushdown=off optimizer_switch='mrr=on' optimizer_switch='mrr_cost_based=off o

36、ptimizer_switch='batched_key_access=on' join_buffer_size=6M read_rnd_buffer_size=6M注意我这里关闭了ICP由于我想只看 BKA算法对于查询时间的影响。因为 BKA 一来于MRR接口，所以这里我打开了MRR。数据集大小2.5G, BP大小设置为512M.查询使用TPC-H Query #3 selectl_orderkey,sum(l_extendedprice * (1 - l_discount) as revenue, o_orderdate, o_shippriorityfromcustome

37、r, orders, lineitem FORCE INDEX (L_ORDERKEY) wherec_mktsegment = 'AUTOMOBILE' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < '1995-03-09' and l_shipdate > '1995-03-09'group byl_orderkey, o_orderdate, o_shippriority order byrevenue desc, o_orderd

38、ate LIMIT 3;5.6执行计划如下：I id | stlslt/pt | taHe | type | pssibldeys | 好 | ley.len | rr| m Extra十十t+t1SWFLEMLF»'WJLNUL150M1Using 岫 UsingUsing fihsort1SI1FLE0挑refm(4tpci. customer?Using 曲国 Jsiig join bra (latcheil 时顼:优时1HSMFLE1血叫refHHLJfOEEBlweiw4tpcixrdefs.UB®'B t加g柚弓Jsiig ：dih Wfr 3拙

39、恭州工扇.，T一T5.5执行计划如下id | selKtJype | table | type | 哪派怫 | key | 晚加 | ref| 顾 | EM咫邮 kULL hull null1(953? using mponry; jrng;jl昭鞠虹助饵 ojot i圳mtaiteKOJRn1 .ihgtee'SDMHLMfflUt附尚波触炯2 Using toe完全在内存中的情况Mysql 5.6 启Mysql用Mysql5.5(join buf统计值fer_size(128BKA(join_bu ffer_size(15.5(join_buffer_size(6M)K),read

40、 rnd'28K),read_r,read_rnd_bufer_size(6bufersize(nd_bufer_siM)256K)ze(256K)查询耗时734.140s712.655s740.137sMysql 5.6 启用BKA(join_buffer_size(6M),read_rnd_bufer_size(6M)324.877s柱状图如下:数据在cache中的时间时间C/s)不完全在内存中的情况如下Counter NameMySQL5.5 默认 配置(join_buffer_s ize=128K read_rnd_bufer _size=128K)MySQL5.6 默认 jo

41、in_buffer_siz e=128K read_rnd_bufer _size=256KMySQL5.5 join_buffer_siz e=6M read_rnd_bufer _size=6MMySQL5.6 join_buffer_size=6 M read_rnd_bufer_siz e=6MCreated_tmp_0000disk_tablesHandler_read_20394410767462039441076746keyHandler_read_ next Handler_read_ rnd_next Innodb_buffer _pool_read_ah ead Innod

42、b_buffer _pool_read_re quests Innodb_buffer _pool_reads Innodb_data_r ead Innodb_data_r eads Innodb_pages _read Innodb_rows_ read Select_scan Sort_scan 查询耗时872798161500172641414201648882731986944(2.5G)16662716661310227982120m10.271s87279887279887279816423716150016150016631728658263898204414121233605

43、74180491164838135842986594304(2.727312005121303236608(1.2G)G)(2.5G)182167166579794231821531665657940918955961022798189559622211119m55.038s18m39.165s6m16.147s重点关注对象Innodb_buffer_pool_read_ahead ,这个参数说明了访问的顺序变的越来越顺序了。Innodb_buffer_pool_reads 不能从BP中读入数据的时候，从物理磁盘读数据到内存中的次Innodb_data_read至此已经读取的数据数量（字节）从

44、表格中的数据可以看出，Innodb_buffer_pool_read_ahead （预读的页的页数），可以看出，5.6 IO的访问格式上来看变得越来越顺序了，这个也很能理解，因为 BKA使用了 MRR接口，从Innodb_data_read （总计读的数据量）和 Innodb_data_reads （物理读的次数） 这两个参数可以看出，5.6中数据读取越来越少，5.5 （2.5G） VS 5.6（1.2G,从数据读取量上来说5.6是5.5的一半，物理读5.5（166627） vs 5.6（79423）上来看，5.6基本上物理读是 5.5的1/2, 再从buffer大小对于查询时间上的影响可以看

45、出，BKA 很依赖于 join_buffer_size 和read_rnd_buffer_size，如果采用系统默认的值的时候，5.5和5.6的查询时间相差较小，加大这两个变量，可以使得查询性能呈现直线上升。数据不在cache中查询耗时:Mysql 5.6 启Mysql用Mysql5.5(join buf统计值fer_size(128BKA(join_bu ffer_size(15.5(join_buffer_size(6M)K),read rnd'28K),read_r,read_rnd_bufer_size(6bufer size(nd_bufer_siM)256K)ze(256K

46、)查询耗时1210.271s1195.038s1119.165sMysql 5.6 启用BKA(join_buffer_size(6M),read_rnd_bufer_size(6M)376.47s柱状图如下:数据不在cache中的查询时间口数据不在。配诡中的查询时间时间(/s)结论：BKA算法在以IO为主的查询能大大的缩短查询时间，BKA可以大大减少物理读的次数，节省了大量宝贵的内存资源，有利于BP更好的缓冲热点数据，但是以内存为主的查询的性能提高不是很大，BKA 算法取决于join_buffer_size和read_rnd_bufer_size的设置，会随着设置的大小，查询时间呈现明显缩短

47、。BKA测试脚本#!/bin/bashsed -i 's/innodb_buffer_pool_size = 512M/innodb_buffer_pool_size = 2M/' /etc/fgrep -i innodb_buffer_pool_size /etc/fservice mysql restartmysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Handler_read_key|Handler_r

48、ead_next|Handler_read_rnd_next|Innodb_buffer_pool_read_ahead|Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innodb_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'sed -i 's/innodb_buffer_pool_size = 2M/innodb_buffer_pool_si

49、ze = 512M/' /etc/fgrep -i innodb_buffer_pool_size /etc/fservice mysql restartmysql -e "set global join_buffer_size=6291456;"|grep -v "Logging"mysql -e "set global read_rnd_buffer_size=6291456;"|grep -v "Logging"a='mysqladmin ext|grep -i Innodb_buffer_p

50、ool_pages_data|awk '(print $4'、mysql -e "flush status;"|grep -v "Logging"sh sar.sh &sh iostat.sh &time mysql -e "use tpch;selectl_orderkey,sum(l_extendedprice * (1 - l_discount) as revenue, o_orderdate,o_shippriorityfromcustomer,orders,lineitem FORCE INDEX (L

51、_ORDERKEY)wherec_mktsegment = 'AUTOMOBILE'and c_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate < '1995-03-09'and l_shipdate > '1995-03-09'group byl_orderkey,o_orderdate,o_shippriorityorder byrevenue desc,o_orderdateLIMIT 10;"|grep -v "Logging&qu

52、ot;|tail -n3|egrep -w "real|user|sys”b='mysqladmin ext|grep -i Innodb_buffer_pool_pages_data|awk '(print $4'、c='expr $b - $a'mysql -e "select $c*16/1024/1024”mysqladminext|egrep"Created_tmp_disk_tables|Created_tmp_tablesHandler_mrr_init|Handler_mrr_rowid_refills|Ha

53、ndler_read_key|Handler_read_next|Handler_read_rnd_next|Innodb_buffer_pool_read_ahead|Innodb_ buffer_pool_read_requests|Innodb_buffer_pool_reads|Innodb_data_read|Innodb_data_reads|Innodb_pages_read|Innodb_rows_read|Select_scan|Sort_scan"|sed 's/|/g'ICP 测试(index condition push down)Mysql

54、5.6配置如下：mysql -e "set global optimizer_switch='index_condition_pushdown=on'"|grep -v "Logging5.5保持默认配置测试两个版本的 mysql均保持默认设置，采用 TPC-H query 19进行压力测试。TPC-H Query #19 语句如下：selectsum(l_extendedprice* (1 - l_discount) as revenuefromlineitem force index(i_l_partkey),partwhere(p_partk

55、ey = l_partkeyand p_brand = 'Brand#45'and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')and l_quantity >= 1 and l_quantity <= 1+10and p_size between 1 and 5and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER I

56、N PERSON')or(p_partkey = l_partkeyand p_brand = 'Brand#24'and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')and l_quantity >= 14 and l_quantity <= 14+10and p_size between 1 and 10and l_shipmode in ('AIR', 'AIR REG')and l_shipinstruct = 'DELIVER IN PERS