Experiment Detail
MySQL Performance Monitoring
2026. 11. 18.
● System ReadyExperiment Detail Information
고객번호 32563번 고객의 주문 내역 최신순으로 한 조회 성능 비교
고객별 주문 내역을 JOIN으로 조회하고, 고객 ID와 주문 날짜를 조합한 복합 인덱스 적용 전후 성능을 비교한다.
SQL Query
Query 3
SELECT orders.order_id, orders.customer_id, order_date, option_id, quantity, unit_price FROM orders
INNER JOIN order_detail ON orders.order_id = order_detail.order_id
WHERE customer_id = 32563 ORDER BY orders.order_date;
Index Condition
Registered Index Conditions
Query 3 - Condition 5
인덱스 미적용
Query 3 - Condition 6
인덱스 적용
테이블명: orders
인덱스명: idx_orders_customer_date
인덱스 컬럼: customer_id, order_date
Query 3 - Condition 7
인덱스 적용
테이블명: orders
인덱스명: idx_orders_customer
인덱스 컬럼: customer_id
Query 3 - Condition 8
인덱스 적용
테이블명: orders
인덱스명: idx_orders_customer_date
인덱스 컬럼: customer_id, order_date
테이블명: orders
인덱스명: idx_orders_customer
인덱스 컬럼: customer_id
Query 3
| 구분 | 평균 실행 시간 | 중앙값 |
|---|---|---|
| Before | 0.88 ms | 0.85 ms |
| After (Condition 6) | 0.80 ms | 0.80 ms |
| After (Condition 7) | 0.89 ms | 0.89 ms |
| After (Condition 8) | 0.83 ms | 0.82 ms |
Query 3
| 구분 | 1회차 | 2회차 | 3회차 | 4회차 | 5회차 |
|---|---|---|---|---|---|
| Before | 1.02 ms | 0.87 ms | 0.82 ms | 0.85 ms | 0.85 ms |
| After (Condition 6) | 0.82 ms | 0.80 ms | 0.86 ms | 0.77 ms | 0.75 ms |
| After (Condition 7) | 0.84 ms | 0.89 ms | 0.92 ms | 0.80 ms | 1.01 ms |
| After (Condition 8) | 0.88 ms | 0.88 ms | 0.82 ms | 0.80 ms | 0.76 ms |
Query 3
Before
EXPLAIN
orders
ref
fk_orders_customer_idx
11
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
const
PRIMARY,fk_orders_customer_idx
Using filesort
order_detail
ref
PRIMARY
2
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.order_id
PRIMARY
없음
EXPLAIN ANALYZE
-> Nested loop inner join (cost=36.9 rows=27.9) (actual time=4.39..4.44 rows=29 loops=1)
-> Sort: orders.order_date (cost=12.1 rows=11) (actual time=0.819..0.821 rows=11 loops=1)
-> Index lookup on orders using fk_orders_customer_idx (customer_id=32563) (cost=12.1 rows=11) (actual time=0.8..0.804 rows=11 loops=1)
-> Index lookup on order_detail using PRIMARY (order_id=orders.order_id) (cost=2.02 rows=2.53) (actual time=0.328..0.329 rows=2.64 loops=11)
After (Condition 6)
EXPLAIN
orders
ref
idx_orders_customer_date
11
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
const
PRIMARY,fk_orders_customer_idx,idx_orders_customer_date
Using index
order_detail
ref
PRIMARY
2
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.order_id
PRIMARY
없음
EXPLAIN ANALYZE
-> Nested loop inner join (cost=26.9 rows=27.9) (actual time=0.0456..0.0817 rows=29 loops=1)
-> Covering index lookup on orders using idx_orders_customer_date (customer_id=32563) (cost=2.12 rows=11) (actual time=0.0336..0.0355 rows=11 loops=1)
-> Index lookup on order_detail using PRIMARY (order_id=orders.order_id) (cost=2.02 rows=2.53) (actual time=0.00299..0.00369 rows=2.64 loops=11)
After (Condition 7)
EXPLAIN
orders
ref
fk_orders_customer_idx
11
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
const
PRIMARY,fk_orders_customer_idx,idx_orders_customer
Using filesort
order_detail
ref
PRIMARY
2
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.order_id
PRIMARY
없음
EXPLAIN ANALYZE
-> Nested loop inner join (cost=28.6 rows=27.9) (actual time=0.0495..0.0758 rows=29 loops=1)
-> Sort: orders.order_date (cost=3.85 rows=11) (actual time=0.0405..0.0413 rows=11 loops=1)
-> Index lookup on orders using fk_orders_customer_idx (customer_id=32563) (cost=3.85 rows=11) (actual time=0.0268..0.0293 rows=11 loops=1)
-> Index lookup on order_detail using PRIMARY (order_id=orders.order_id) (cost=2.02 rows=2.53) (actual time=0.00209..0.00269 rows=2.64 loops=11)
After (Condition 8)
EXPLAIN
orders
ref
idx_orders_customer_date
11
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
const
PRIMARY,fk_orders_customer_idx,idx_orders_customer_date,idx_orders_customer
Using index
order_detail
ref
PRIMARY
2
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.order_id
PRIMARY
없음
EXPLAIN ANALYZE
-> Nested loop inner join (cost=26.9 rows=27.9) (actual time=0.0442..0.0736 rows=29 loops=1)
-> Covering index lookup on orders using idx_orders_customer_date (customer_id=32563) (cost=2.12 rows=11) (actual time=0.0327..0.0345 rows=11 loops=1)
-> Index lookup on order_detail using PRIMARY (order_id=orders.order_id) (cost=2.02 rows=2.53) (actual time=0.00234..0.00304 rows=2.64 loops=11)