정렬 기준이 인덱스 컬럼이면 인덱스가 이미 정렬되어 있어 따로 정렬하지 않아도 됩니다. 인덱스가 없는 컬럼이면 전부 읽어서 정렬해야 합니다.
EXPLAIN SELECT id, ord_date FROM orders ORDER BY ord_date;PLAN
---------------------------------------------------------------------------------------------------------------------
SELECT
"ID",
"ORD_DATE"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_ORDERS_DATE */
ORDER BY 2
/* index sorted */
(1행)EXPLAIN SELECT id, amt FROM orders ORDER BY amt;PLAN
----------------------------------------------------------------------------------------------
SELECT
"ID",
"AMT"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.ORDERS.tableScan */
ORDER BY 2
(1행)첫 계획 끝의 index sorted 주석이 인덱스 순서를 그대로 쓴다는 표시입니다. 둘째는 전체 읽기이고 정렬 표시가 없지만 실제 DB 에서는 별도 정렬 단계가 생깁니다. Oracle 은 SORT ORDER BY, MySQL 은 Extra 의 Using filesort, MSSQL 은 Sort 연산입니다. H2 출력에 정렬 단계가 안 보인다고 정렬이 없다는 뜻은 아닙니다.
Oracle 은 두 문장으로 예상 계획을 봅니다. 아래 쿼리는 emp_id 인덱스가 있다고 가정합니다.
-- Oracle · Tibero
EXPLAIN PLAN FOR
SELECT
*
FROM orders
WHERE emp_id = 7;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);도식(H2 미지원, 실행 결과 아님)
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 32 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS | 60 | 32 |
|* 2 | INDEX RANGE SCAN | IDX_ORDERS_EMP | 60 | 1 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("EMP_ID"=7)Rows 와 Cost 는 설명용 예시 값입니다. 읽는 순서는 가장 깊은 2 번 INDEX RANGE SCAN 이 먼저이고 결과가 1 번으로 올라가 테이블 행을 읽습니다. * 표시가 붙은 줄의 조건은 아래 Predicate Information 에 나옵니다. access 는 인덱스로 찾는 조건이고 filter 는 읽은 뒤 거르는 조건입니다.
실제 수행 통계는 힌트를 붙여 쿼리를 실행하고 바로 이어서 커서 계획을 꺼냅니다.
-- Oracle · Tibero
SELECT /*+ GATHER_PLAN_STATISTICS */
COUNT(*)
FROM orders
WHERE status = 'CANCEL';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));도식(H2 미지원, 실행 결과 아님)
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | TABLE ACCESS FULL | ORDERS | 1 | 1500 | 300 |E-Rows 는 계획을 세울 때의 예상이고 A-Rows 는 실제로 읽은 행 수입니다. 이 예시에서는 예상 1500, 실제 300 으로 다섯 배 차이가 납니다. 이런 차이가 여러 단계에서 크게 벌어지면 통계가 낡았거나 분포가 치우친 것을 의심합니다.
MySQL 은 문장 앞에 EXPLAIN 만 붙입니다. 아래는 인덱스 조건과 전체 읽기 두 경우의 표입니다.
-- MySQL
EXPLAIN SELECT id FROM orders WHERE emp_id = 7;
EXPLAIN SELECT id, amt FROM orders WHERE cust = 'C7' ORDER BY amt;도식(H2 미지원, 실행 결과 아님)
id | table | type | key | rows | Extra
---+--------+------+----------------+------+------------------------------
1 | orders | ref | idx_orders_emp | 60 | Using index
id | table | type | key | rows | Extra
---+--------+------+------+------+-----------------------------
1 | orders | ALL | NULL | 2990 | Using where; Using filesort첫 표의 Using index 는 인덱스만 읽고 끝났다는 뜻으로 커버링이라고 부릅니다. 선택한 컬럼 id 가 InnoDB 에서 인덱스에 이미 들어 있기 때문입니다. 둘째 표는 type ALL 과 key NULL 로 전체 읽기이고 Extra 의 Using filesort 는 별도 정렬입니다. Using temporary 는 임시 테이블을 만든다는 뜻이고 GROUP BY 에서 자주 나옵니다.
-- MySQL 8.0.18 부터
EXPLAIN ANALYZE SELECT id FROM orders WHERE emp_id = 7;도식(H2 미지원, 실행 결과 아님)
-> Covering index lookup on orders using idx_orders_emp (emp_id=7)
(cost=6.25 rows=60) (actual time=0.05..0.09 rows=60 loops=1)앞 괄호가 예상(cost, rows)이고 뒤 괄호가 실제(시간, rows)입니다. 이 문장은 쿼리를 실제로 실행합니다. EXPLAIN FORMAT=TREE 는 8.0.16 부터 같은 트리에서 앞 괄호만 보여 줍니다.
MSSQL 은 SSMS 에서 예상 실행 계획(Ctrl+L)과 실제 실행 계획 포함(Ctrl+M)을 쓰는 것이 기본입니다. 텍스트로는 아래처럼 켭니다.
-- MSSQL
SET SHOWPLAN_TEXT ON;
GO
SELECT
*
FROM orders
WHERE emp_id = 7;
GO
SET SHOWPLAN_TEXT OFF;
GO도식(H2 미지원, 실행 결과 아님)
StmtText
------------------------------------------------------------------
|--Nested Loops(Inner Join)
|--Index Seek(OBJECT:([orders].[idx_orders_emp]), SEEK:([emp_id]=(7)))
|--Clustered Index Seek(OBJECT:([orders].[PK_orders]), SEEK:([id]=[id]) LOOKUP)인덱스로 emp_id 가 7 인 행의 키를 찾고 PK 로 한 번 더 가서 나머지 컬럼을 읽는 형태입니다. 이 뒤쪽 단계가 Key Lookup 입니다. 텍스트 모양은 버전과 옵션에 따라 다르니 그래픽 계획의 아이콘 이름으로 익혀 둡니다. 논리적 읽기 수는 아래처럼 봅니다.
-- MSSQL
SET STATISTICS IO ON;
SELECT * FROM orders WHERE emp_id = 7;Table 'orders'. Scan count 1, logical reads 63, physical reads 0위 줄은 설명용 예시 값입니다. logical reads 는 읽은 페이지 수라 같은 쿼리를 고치기 전후로 견주기 좋은 숫자입니다.
H2 의 방언 모드를 Oracle, MySQL, MSSQLServer, Regular 로 바꿔 같은 EXPLAIN 을 돌렸습니다. 네 모드 모두 실행되었고 출력이 같았습니다.
SET MODE Oracle;
EXPLAIN SELECT id FROM orders WHERE emp_id = 7;PLAN
-----------------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_ORDERS_EMP: EMP_ID = 7 */
WHERE "EMP_ID" = 7
(1행)MySQL, MSSQLServer, Regular 모드의 출력도 이 블록과 글자까지 같았습니다. SET MODE 는 방언 문법만 흉내 낼 뿐 계획 출력은 늘 H2 형식이라는 뜻입니다. 이 결과를 Oracle 이나 MySQL 의 계획으로 읽으면 안 됩니다.