• 2022-08-08 mysql慢SQL-Q18-10GB数据量-mysql/innodb测试

    摘要:

    用mysql/innodb测试Q18慢SQL

    硬件环境:

    1. 内存8GB
    2. cpu8核
    3. 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)

    热力图:

  • 相关阅读:
    JavaScript DOM的使用，让页面跳动动起来
    微机原理实验：字符转换为ASCII码
    数据结构：线性表之-队列
    芯启源加入龙蜥社区，推动集成电路和DPU芯片创新落地
    kepler.gl笔记：添加数据
    yolov5官方开源项目地址github记录一下
    基于C++实现一个支持简单交互绘图小程序
    内网渗透之Socks代理简介
    vue3项目实战的请求接口问题（一）跨域问题+解决方法
    看万山红遍
  • 原文地址：https://blog.csdn.net/adofsauron/article/details/126238173