2022-08-01 mysql/stoonedb慢SQL-Q18分析

lewis 5年前 (2020-02-01) 阅读数 5 #技术

摘要:

分析慢SQL-Q18出现的原因

DDL分析:SQL:

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;

innodb的explain分析:

mysql> explain select
    -> c_name,
    -> c_custkey,
    -> o_orderkey,
    -> o_orderdate,
    -> o_totalprice,
    -> sum(l_quantity)
    -> from
    -> customer,
    -> orders,
    -> lineitem
    -> where
    -> o_orderkey in (
    -> select
    -> 
Display all 1017 possibilities? (y or n) 
    -> l_orderkey
    -> from
    -> 
Display all 1017 possibilities? (y or n) 
    -> lineitem
    -> group by
    -> 
Display all 1017 possibilities? (y or n) 
    -> l_orderkey
    -> having
    -> 
Display all 1017 possibilities? (y or n) 
    -> 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
4 rows in set, 1 warning (0.01 sec)

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

PRIMARY

orders

ALL

PRIMARY

13754753

100

Using where; Using temporary; Using filesort

PRIMARY

customer

eq_ref

PRIMARY

tpch.orders.o_custkey

100

PRIMARY

lineitem

ref

PRIMARY

tpch.orders.o_orderkey

100

SUBQUERY

lineitem

index

PRIMARY

31751480

100

stonedb的explain分析:

mysql> 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: lineitem
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 59986052
     filtered: 100.00
        Extra: Using where with pushed condition <in_optimizer>(`tpch`.`lineitem`.`l_orderkey`,`tpch`.`lineitem`.`l_orderkey` in ( <materialize> (/* select#2 */ select `tpch`.`lineitem`.`l_orderkey` from `tpch`.`lineitem` group by `tpch`.`lineitem`.`l_orderkey` having (sum(`tpch`.`lineitem`.`l_quantity`) > 300) ), <primary_index_lookup>(`tpch`.`lineitem`.`l_orderkey` in <temporary table> on <auto_key> where ((`tpch`.`lineitem`.`l_orderkey` = `materialized-subquery`.`l_orderkey`)))))(t0) Pckrows: 916, susp. 916 (0 empty 0 full). Conditions: 1; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: orders
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: tpch.lineitem.l_orderkey
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. 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
*************************** 4. row ***************************
           id: 2
  select_type: SUBQUERY
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 59986052
     filtered: 100.00
        Extra: Using temporary; Using filesort
4 rows in set, 1 warning (54.11 sec)

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

PRIMARY

lineitem

ALL

PRIMARY

59986052

100

Using where with pushed condition <in_optimizer>(`tpch`.`lineitem`.`l_orderkey`,`tpch`.`lineitem`.`l_orderkey` in ( <materialize> (/* select#2 */ select `tpch`.`lineitem`.`l_orderkey` from `tpch`.`lineitem` group by `tpch`.`lineitem`.`l_orderkey` having (sum(`tpch`.`lineitem`.`l_quantity`) > 300) ), <primary_index_lookup>(`tpch`.`lineitem`.`l_orderkey` in <temporary table> on <auto_key> where ((`tpch`.`lineitem`.`l_orderkey` = `materialized-subquery`.`l_orderkey`)))))(t0) Pckrows: 916, susp. 916 (0 empty 0 full). Conditions: 1; Using temporary; Using filesort

PRIMARY

orders

eq_ref

PRIMARY

tpch.lineitem.l_orderkey

100

PRIMARY

customer

eq_ref

PRIMARY

tpch.orders.o_custkey

100

SUBQUERY

lineitem

ALL

PRIMARY

59986052

100

Using temporary; Using filesort

执行分析:热力图:

hash join数据统计:

[2022-08-01 19:27:48.120105] [1369856] End traversed 8380864/8380864 rows.
[2022-08-01 19:28:28.177049] [1369856] Begin match dim of 32986052 rows, spliting into 64 threads with packs type.
[2022-08-01 19:29:07.932297] [1369856] End match dim. Produced tuples:32986052/32986052
[2022-08-01 19:29:07.985311] [1369856] Tuples after inner join 1-2 [hash]: 32986052  [111]
[2022-08-01 19:29:08.286386] [1369856] GroupTable begin, initialized for up to 32986052 groups, 53+31 bytes (3171 MB)
[2022-08-01 19:29:08.529321] [1369856] Aggregating: 32986052 tuples left.
[2022-08-01 19:30:40.828429] [1369856] Group/Aggregate end. Begin generating output.
[2022-08-01 19:30:40.828502] [1369856] Output rows: 8249593, output table row limit: 8249593
[2022-08-01 19:31:06.163156] [1369856] Generating output end. Aggregated (8249593 group). Omitted packrows: 0 + 0 partially, out of 26622 total.
[2022-08-01 19:31:08.930503] [1369856] Heap Sort begin, initialized for 8249593 rows, 16+0+3 bytes each.
[2022-08-01 19:32:21.876613] [1369856] Sorted end, rows retrieved.

版权声明

本文仅代表作者观点，不代表博信信息网立场。

上一篇：mysql的2002错误怎么解决下一篇：MySQL 主主复制

2022-08-01 mysql/stoonedb慢SQL-Q18分析

版权声明

作者文章