摘要:
用mysql/innodb测试Q18慢SQL
硬件环境:
- 内存8GB
- cpu8核
- mysql: 8.0.28
explain分析：
[root@localhost ~]# mysql -D tpch -e "
> explain select
> c_name,
> c_custkey,
> o_orderkey,
> o_orderdate,
> o_totalprice,
> sum(l_quantity)
> from
> customer,
> orders,
> lineitem
> where
> o_orderkey in (
> select
> l_orderkey
> from
> lineitem
> group by
> l_orderkey having
> sum(l_quantity) > 300
> )
> 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
> limit 100\G
> "
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: orders
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 13754753
filtered: 100.00
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: customer
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: tpch.orders.o_custkey
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: lineitem
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: tpch.orders.o_orderkey
rows: 4
filtered: 100.00
Extra: NULL
*************************** 4. row ***************************
id: 2
select_type: SUBQUERY
table: lineitem
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 31751480
filtered: 100.00
Extra: NULL
执行耗时:
mysql> select
-> c_name,
-> c_custkey,
-> o_orderkey,
-> o_orderdate,
-> o_totalprice,
-> sum(l_quantity)
-> from
-> customer,
-> orders,
-> lineitem
-> where
-> o_orderkey in (
-> select
-> l_orderkey
-> from
-> lineitem
-> group by
-> l_orderkey having
-> sum(l_quantity) > 300
-> )
-> 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
-> limit 100;
+--------------------+-----------+------------+-------------+--------------+-----------------+
| c_name | c_custkey | o_orderkey | o_orderdate | o_totalprice | sum(l_quantity) |
+--------------------+-----------+------------+-------------+--------------+-----------------+
| Customer#001287812 | 1287812 | 42290181 | 1997-11-26 | 558289.17 | 318.00 |
| Customer#001172513 | 1172513 | 36667107 | 1997-06-06 | 550142.18 | 322.00 |
| Customer#000399481 | 399481 | 43906817 | 1995-04-06 | 549431.65 | 312.00 |
| Customer#001492954 | 1492954 | 30332516 | 1996-03-10 | 541181.80 | 310.00 |
| Customer#001082018 | 1082018 | 31018979 | 1995-12-06 | 537993.05 | 304.00 |
| Customer#001114039 | 1114039 | 30417318 | 1995-10-31 | 536420.39 | 305.00 |
| Customer#001221100 | 1221100 | 34748294 | 1997-11-22 | 533199.98 | 307.00 |
| Customer#001101754 | 1101754 | 46794464 | 1992-04-28 | 532623.90 | 320.00 |
| Customer#000253643 | 253643 | 58441889 | 1997-08-30 | 532485.39 | 308.00 |
| Customer#000591466 | 591466 | 55799200 | 1996-02-11 | 524209.45 | 327.00 |
| Customer#001296343 | 1296343 | 28346247 | 1992-11-01 | 523740.15 | 310.00 |
| Customer#000406811 | 406811 | 47953029 | 1995-01-14 | 523581.51 | 303.00 |
| Customer#001196317 | 1196317 | 34509573 | 1995-10-15 | 522897.01 | 318.00 |
| Customer#000634048 | 634048 | 36327201 | 1992-04-25 | 519634.30 | 315.00 |
| Customer#001308376 | 1308376 | 28077922 | 1996-10-27 | 518711.99 | 319.00 |
| Customer#000008782 | 8782 | 29294434 | 1993-01-05 | 518140.85 | 322.00 |
| Customer#000061768 | 61768 | 46486080 | 1997-05-02 | 517881.34 | 314.00 |
| Customer#000948784 | 948784 | 35999840 | 1993-09-15 | 517688.62 | 309.00 |
| Customer#000948259 | 948259 | 29089474 | 1994-04-18 | 516193.66 | 302.00 |
| Customer#000158578 | 158578 | 53246051 | 1993-01-24 | 513721.96 | 301.00 |
| Customer#000789988 | 789988 | 49864035 | 1995-11-05 | 513213.66 | 308.00 |
| Customer#001458101 | 1458101 | 30768103 | 1993-04-12 | 512801.73 | 306.00 |
| Customer#001232356 | 1232356 | 52340710 | 1993-01-01 | 512292.83 | 320.00 |
| Customer#000773671 | 773671 | 30567815 | 1996-03-03 | 512201.38 | 303.00 |
| Customer#000299162 | 299162 | 38643041 | 1994-07-21 | 512074.82 | 304.00 |
| Customer#000287270 | 287270 | 37531878 | 1997-03-20 | 512044.58 | 315.00 |
| Customer#000149305 | 149305 | 44769410 | 1996-06-28 | 511963.95 | 311.00 |
| Customer#000024484 | 24484 | 41588929 | 1998-03-28 | 509719.48 | 303.00 |
| Customer#001247930 | 1247930 | 59138305 | 1993-10-31 | 509372.97 | 321.00 |
| Customer#001058674 | 1058674 | 30218567 | 1992-04-09 | 508704.15 | 303.00 |
| Customer#000357649 | 357649 | 51184261 | 1998-07-05 | 508562.74 | 315.00 |
| Customer#000015127 | 15127 | 34551296 | 1998-04-12 | 508442.04 | 312.00 |
| Customer#001080199 | 1080199 | 37282215 | 1992-04-18 | 507778.23 | 312.00 |
| Customer#000634264 | 634264 | 46678469 | 1995-01-27 | 507548.21 | 307.00 |
| Customer#000488812 | 488812 | 33928775 | 1995-10-13 | 507547.09 | 302.00 |
| Customer#001483156 | 1483156 | 52312869 | 1992-06-14 | 507065.74 | 308.00 |
| Customer#000998485 | 998485 | 51532000 | 1992-10-27 | 506996.90 | 319.00 |
| Customer#000350203 | 350203 | 28309383 | 1993-12-31 | 506745.51 | 301.00 |
| Customer#000998174 | 998174 | 40753383 | 1997-01-14 | 506271.45 | 302.00 |
| Customer#000942725 | 942725 | 48881602 | 1993-09-19 | 504197.89 | 327.00 |
| Customer#000602488 | 602488 | 43980355 | 1994-09-22 | 503468.79 | 312.00 |
| Customer#000266747 | 266747 | 41274690 | 1992-08-10 | 503260.49 | 317.00 |
| Customer#001274600 | 1274600 | 30728833 | 1994-01-05 | 502908.12 | 306.00 |
| Customer#000180970 | 180970 | 28378659 | 1997-03-21 | 502856.99 | 303.00 |
| Customer#001493087 | 1493087 | 38422560 | 1994-04-17 | 502119.94 | 305.00 |
| Customer#001143101 | 1143101 | 52415075 | 1996-05-10 | 501221.45 | 312.00 |
| Customer#000954800 | 954800 | 42359809 | 1996-03-09 | 500726.71 | 305.00 |
| Customer#001000024 | 1000024 | 59057792 | 1993-12-30 | 500422.55 | 314.00 |
| Customer#000569749 | 569749 | 33002339 | 1993-10-09 | 499928.12 | 306.00 |
| Customer#000655072 | 655072 | 46777222 | 1993-06-27 | 499565.93 | 314.00 |
| Customer#000138238 | 138238 | 43491654 | 1992-05-20 | 498276.78 | 308.00 |
| Customer#001410662 | 1410662 | 33414662 | 1996-05-07 | 497540.55 | 310.00 |
| Customer#001422388 | 1422388 | 51409728 | 1996-03-12 | 497139.69 | 306.00 |
| Customer#001291849 | 1291849 | 31192034 | 1993-09-05 | 497050.74 | 309.00 |
| Customer#000607090 | 607090 | 34181762 | 1992-03-25 | 496418.69 | 309.00 |
| Customer#000242576 | 242576 | 43438883 | 1992-11-27 | 496298.09 | 314.00 |
| Customer#000776071 | 776071 | 39154146 | 1992-12-03 | 496189.68 | 305.00 |
| Customer#000027808 | 27808 | 32161635 | 1992-08-06 | 496173.99 | 309.00 |
| Customer#000166589 | 166589 | 46685344 | 1993-12-14 | 496075.11 | 327.00 |
| Customer#000385288 | 385288 | 51929955 | 1993-04-19 | 495841.65 | 304.00 |
| Customer#000608233 | 608233 | 28092902 | 1997-03-23 | 494174.42 | 306.00 |
| Customer#000414544 | 414544 | 33162084 | 1994-02-15 | 493935.58 | 304.00 |
| Customer#000149107 | 149107 | 52357185 | 1993-04-06 | 493715.64 | 307.00 |
| Customer#000435364 | 435364 | 44886083 | 1994-05-30 | 493374.78 | 306.00 |
| Customer#001232266 | 1232266 | 47688132 | 1998-05-06 | 492421.01 | 302.00 |
| Customer#000392000 | 392000 | 48862754 | 1996-07-14 | 491950.72 | 305.00 |
| Customer#000237835 | 237835 | 42146816 | 1996-05-24 | 491883.49 | 301.00 |
| Customer#001128433 | 1128433 | 55747171 | 1993-09-06 | 491693.10 | 310.00 |
| Customer#000509131 | 509131 | 43952871 | 1996-10-11 | 491413.92 | 301.00 |
| Customer#001014283 | 1014283 | 53407847 | 1996-09-02 | 490944.06 | 304.00 |
| Customer#001386731 | 1386731 | 50776837 | 1993-10-22 | 490144.86 | 305.00 |
| Customer#000135130 | 135130 | 53397509 | 1995-03-02 | 489735.11 | 305.00 |
| Customer#000395026 | 395026 | 54915267 | 1998-01-23 | 489692.83 | 309.00 |
| Customer#001303807 | 1303807 | 49461377 | 1996-11-08 | 489054.75 | 303.00 |
| Customer#000064450 | 64450 | 37023521 | 1994-07-20 | 489005.79 | 306.00 |
| Customer#000366727 | 366727 | 57974274 | 1996-09-04 | 488477.57 | 302.00 |
| Customer#000084952 | 84952 | 54409761 | 1997-05-09 | 488285.96 | 307.00 |
| Customer#001300402 | 1300402 | 31932481 | 1995-04-14 | 487828.61 | 302.00 |
| Customer#000441086 | 441086 | 40376484 | 1993-08-05 | 487725.38 | 307.00 |
| Customer#000561656 | 561656 | 59237092 | 1992-09-22 | 487714.55 | 303.00 |
| Customer#000333922 | 333922 | 48301091 | 1994-04-26 | 487418.72 | 308.00 |
| Customer#000715606 | 715606 | 49519297 | 1993-09-17 | 487000.50 | 309.00 |
| Customer#000830836 | 830836 | 32766721 | 1995-01-27 | 485962.64 | 306.00 |
| Customer#000648478 | 648478 | 47773829 | 1998-01-20 | 485897.71 | 312.00 |
| Customer#001151815 | 1151815 | 55722822 | 1995-08-24 | 485654.49 | 316.00 |
| Customer#000372146 | 372146 | 48300454 | 1992-11-30 | 484962.56 | 318.00 |
| Customer#000225562 | 225562 | 39492870 | 1995-08-12 | 484782.87 | 317.00 |
| Customer#000485371 | 485371 | 39395047 | 1997-03-20 | 483675.19 | 302.00 |
| Customer#000850192 | 850192 | 29805379 | 1994-06-07 | 483666.61 | 302.00 |
| Customer#000799613 | 799613 | 56089923 | 1993-09-12 | 483608.27 | 312.00 |
| Customer#000356227 | 356227 | 34130272 | 1995-10-03 | 483390.93 | 305.00 |
| Customer#000333106 | 333106 | 33450849 | 1995-07-10 | 482794.35 | 309.00 |
| Customer#000675502 | 675502 | 32991271 | 1992-04-09 | 482761.07 | 307.00 |
| Customer#000508750 | 508750 | 37004583 | 1997-12-11 | 482694.83 | 304.00 |
| Customer#000338563 | 338563 | 56217669 | 1994-10-22 | 482452.83 | 303.00 |
| Customer#001205237 | 1205237 | 56741287 | 1993-03-25 | 481622.72 | 304.00 |
| Customer#000604861 | 604861 | 45897379 | 1994-05-30 | 481442.14 | 318.00 |
| Customer#000778949 | 778949 | 40876003 | 1992-05-10 | 481422.24 | 314.00 |
| Customer#000347839 | 347839 | 53623108 | 1995-05-07 | 481251.56 | 316.00 |
| Customer#001350514 | 1350514 | 43245189 | 1994-11-29 | 480988.72 | 320.00 |
+--------------------+-----------+------------+-------------+--------------+-----------------+
100 rows in set (42.04 sec)
