Engine Atlas

MySQL 내부 임시 테이블 - RAM, mmap 오버플로, InnoDB 디스크 전환

GROUP BY · TempTable · filesort · LIMIT
MySQL 8.4.10captured evidence
English
01 · SOURCE

Exact SQL

1 active fragmentsMySQLGROUP BY · ORDER BY · LIMIT 100
SELECT account_id, status, bucket_id, COUNT(*) AS order_count, SUM(amount_cents) AS total_cents, MAX(payload) AS sample_payload
FROM work_orders
GROUP BY account_id, status, bucket_id
ORDER BY total_cents DESC, account_id, status, bucket_id
LIMIT 100;
  1. source-scan
  2. temporary-work
  3. temporary-scan
  4. filesort
  5. limit
02 · FLOW

Native plan과 actual iterator

01 / 12
Active native operators5
limitlimit
TREE: -> Limit: 100 row(s) ANALYZE: -> Limit: 100 row(s) (actual time=222..222 rows=100 loops=1)TREE line 1; ANALYZE line 1
filesortsort
TREE: -> Sort: total_cents DESC, work_orders.account_id, work_orders.`status`, work_orders.bucket_id, limit input to 100 row(s) per chunk ANALYZE: -> Sort: total_cents DESC, work_orders.account_id, work_orders.`status`, work_orders.bucket_id, limit input to 100 row(s) per chunk (actual time=222..222 rows=100 loops=1)TREE line 2; ANALYZE line 2
temporary-scantable-scan
TREE: -> Table scan on <temporary> ANALYZE: -> Table scan on <temporary> (actual time=196..209 rows=65536 loops=1)TREE line 3; ANALYZE line 3
temporary-worktemp-table
TREE: -> Aggregate using temporary table ANALYZE: -> Aggregate using temporary table (actual time=196..196 rows=65536 loops=1)TREE line 4; ANALYZE line 4
source-scantable-scan
TREE: -> Table scan on work_orders (cost=15156 rows=128478) ANALYZE: -> Table scan on work_orders (cost=13513 rows=128478) (actual time=0.037..40.3 rows=131072 loops=1)TREE line 5; ANALYZE line 5
Exact execution detail

131072개 source row가 100개 result row로 줄어듭니다

이 SQL은 131072개 source row를 어떻게 100개 result row로 줄이는가?

GROUP BY output

Logical temporary rows

Derived · fixture
65536 rows65536 이 화면에서 생략
Active metrics4
source scan actual rows
131072 row
Captured
limit actual rows
100 row
Captured
execution rows examined
131172 row
Captured
execution rows sent
100 row
Captured
01 / 12