status = 'SHIP' 등호 조건과 ord_date 범위 조건을 함께 쓰는 쿼리를 두 순서의 인덱스로 봅니다.
CREATE INDEX idx_status_date ON orders (status, ord_date);
EXPLAIN
SELECT
id
FROM orders
WHERE status = 'SHIP'
AND ord_date BETWEEN DATE '2023-03-01' AND DATE '2023-03-31';PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_STATUS_DATE: STATUS = 'SHIP'
AND ORD_DATE >= DATE '2023-03-01'
AND ORD_DATE <= DATE '2023-03-31'
*/
WHERE ("STATUS" = 'SHIP')
AND ("ORD_DATE" BETWEEN DATE '2023-03-01' AND DATE '2023-03-31')
(1행)DROP INDEX idx_status_date;
CREATE INDEX idx_date_status ON orders (ord_date, status);
EXPLAIN
SELECT
id
FROM orders
WHERE status = 'SHIP'
AND ord_date BETWEEN DATE '2023-03-01' AND DATE '2023-03-31';PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_DATE_STATUS: STATUS = 'SHIP'
AND ORD_DATE >= DATE '2023-03-01'
AND ORD_DATE <= DATE '2023-03-31'
*/
WHERE ("STATUS" = 'SHIP')
AND ("ORD_DATE" BETWEEN DATE '2023-03-01' AND DATE '2023-03-31')
(1행)H2 는 두 순서에서 같은 조건 문자열을 보여 주므로 좁혀지는 정도의 차이는 계획에서 보이지 않습니다. 결과는 어느 순서든 44건으로 같고 달라지는 것은 인덱스에서 읽는 항목 수입니다.
(status, ord_date) 는 SHIP 의 3월 구간만 골라 읽어 44개 항목을 봅니다. (ord_date, status) 는 3월 전체(하루 평균 약 6.8행에 31일이라 약 210개)를 읽으며 status 를 하나씩 확인합니다. 이 숫자는 계산한 추정이며 H2 로 측정한 값이 아닙니다.
인덱스는 이미 정렬되어 있으므로 정렬 기준이 인덱스 순서와 같으면 정렬을 따로 하지 않아도 됩니다. (status, ord_date) 인덱스에서 status 를 등호로 고정하면 나머지는 ord_date 순서로 나옵니다.
DROP INDEX idx_date_status;
CREATE INDEX idx_status_date ON orders (status, ord_date);
EXPLAIN
SELECT
id
, ord_date
FROM orders
WHERE status = 'SHIP'
ORDER BY ord_date;PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID",
"ORD_DATE"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_STATUS_DATE: STATUS = 'SHIP' */
WHERE "STATUS" = 'SHIP'
ORDER BY 2
(1행)H2 는 이 계획에 index sorted 를 붙이지 않았습니다. 조건 안에서 이미 정렬된다는 것을 H2 가 표시하지 않을 뿐입니다.
실제 DB 에서는 Oracle 계획의 SORT ORDER BY, MySQL Extra 의 Using filesort, MSSQL 의 Sort 연산이 생기지 않는 형태로 확인합니다.
인덱스 컬럼 순서 전체로 정렬하면 H2 도 index sorted 를 표시합니다. status 조건 없이 ord_date 만 정렬하면 이 인덱스로는 순서가 맞지 않습니다.
EXPLAIN
SELECT
id
, status
, ord_date
FROM orders
ORDER BY status, ord_date;PLAN
--------------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID",
"STATUS",
"ORD_DATE"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_STATUS_DATE */
ORDER BY 2, 3
/* index sorted */
(1행)emp_id 로 찾아 amt 를 조회하는 쿼리의 커버링 인덱스를 DB별로 만듭니다.
-- Oracle · Tibero
CREATE INDEX idx_emp_amt ON orders (emp_id, amt);
-- MySQL
CREATE INDEX idx_emp_amt ON orders (emp_id, amt);
-- MSSQL
CREATE INDEX idx_emp_amt ON orders (emp_id) INCLUDE (amt);Oracle 계획은 아래처럼 바뀝니다. 인덱스가 (emp_id) 뿐일 때는 인덱스를 읽은 뒤 테이블로 가고, (emp_id, amt) 가 있으면 테이블 단계가 사라집니다.
도식(H2 미지원, 실행 결과 아님)
-- 인덱스 (emp_id) 만 있을 때
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | 100 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS | 100 |
|* 2 | INDEX RANGE SCAN | IDX_ORDERS_EMP | 100 |
-- 인덱스 (emp_id, amt) 가 있을 때
| Id | Operation | Name | Rows |
| 0 | SELECT STATEMENT | | 100 |
|* 1 | INDEX RANGE SCAN | IDX_EMP_AMT | 100 |MySQL 은 EXPLAIN 표의 Extra 열을 봅니다. MSSQL 은 Key Lookup 이 사라지는지 봅니다.
도식(H2 미지원, 실행 결과 아님)
-- MySQL EXPLAIN (idx_emp_amt 가 있을 때)
| id | table | type | key | rows | Extra |
| 1 | orders | ref | idx_emp_amt | 100 | Using index |
-- MSSQL 텍스트 계획, 인덱스 (emp_id) 만 있을 때
|--Nested Loops(Inner Join)
|--Index Seek(OBJECT:([idx_emp]), SEEK:([emp_id]=(7)))
|--Key Lookup(OBJECT:([PK_orders]))
-- MSSQL, INCLUDE (amt) 가 있을 때
|--Index Seek(OBJECT:([idx_emp_amt]), SEEK:([emp_id]=(7)))Rows 와 이름은 설명용 예시 값입니다. MySQL 에서 Extra 가 Using index 이면 커버링이고, 값이 없으면 테이블 행을 읽는다는 뜻입니다. MSSQL 텍스트 모양은 버전과 옵션에 따라 다르므로 그래픽 계획에서 Key Lookup 아이콘이 사라졌는지로 확인하면 쉽습니다.
인덱스가 도움이 될지는 조건에 걸리는 행이 전체의 몇 %인지로 가늠합니다. 조건별 건수를 한 번에 셉니다.
SELECT
COUNT(*) AS total
, SUM(CASE WHEN emp_id = 7 THEN 1 ELSE 0 END) AS emp7
, SUM(CASE WHEN status = 'CANCEL' THEN 1 ELSE 0 END) AS cancel
, SUM(CASE WHEN status = 'DONE' THEN 1 ELSE 0 END) AS done
, SUM(CASE WHEN ord_date >= DATE '2023-01-01' AND ord_date < DATE '2023-07-01' THEN 1 ELSE 0 END) AS half_year
, SUM(CASE WHEN ord_date >= DATE '2023-01-01' AND ord_date < DATE '2024-01-01' THEN 1 ELSE 0 END) AS one_year
FROM orders;TOTAL | EMP7 | CANCEL | DONE | HALF_YEAR | ONE_YEAR
------+------+--------+------+-----------+---------
5000 | 100 | 250 | 3250 | 1266 | 2554
(1행)같은 값을 비율로 바꾸면 판단이 쉽습니다. 각 건수에 100 을 곱해 전체 행 수로 나누고 반올림합니다. 전체 SQL 은 예제 파일 03 에 있습니다.
PCT_EMP7 | PCT_CANCEL | PCT_DONE | PCT_HALF_YEAR | PCT_ONE_YEAR
---------+------------+----------+---------------+-------------
2.0 | 5.0 | 65.0 | 25.3 | 51.1
(1행)| 조건 | 해당 건수 | 비율 | 경험적 판단 |
|---|---|---|---|
| emp_id = 7 | 100 | 2.0% | 유리 |
| status = 'CANCEL' | 250 | 5.0% | 유리 |
| 2023 상반기 | 1,266 | 25.3% | 경계 |
| 2023 한 해 | 2,554 | 51.1% | 전체 읽기가 나을 수 있음 |
| status = 'DONE' | 3,250 | 65.0% | 전체 읽기가 나을 수 있음 |
ord_date 에 인덱스를 만들고 한 해 전체(51%)를 읽는 조건의 계획을 봅니다.
CREATE INDEX idx_date ON orders (ord_date);
EXPLAIN
SELECT
id
FROM orders
WHERE ord_date >= DATE '2023-01-01'
AND ord_date < DATE '2024-01-01';PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"ID"
FROM "PUBLIC"."ORDERS"
/* PUBLIC.IDX_DATE: ORD_DATE >= DATE '2023-01-01'
AND ORD_DATE < DATE '2024-01-01'
*/
WHERE ("ORD_DATE" >= DATE '2023-01-01')
AND ("ORD_DATE" < DATE '2024-01-01')
(1행)H2 는 51% 를 읽는데도 인덱스를 고릅니다. 이 결과는 실제 DB 가 같은 선택을 한다는 근거가 아닙니다. 실제 Oracle 이나 MySQL 은 이 정도 범위에서 전체 읽기를 고르는 일이 흔합니다. H2 로 확인할 수 있는 것은 인덱스가 사용 가능한 조건이라는 사실뿐입니다.
인덱스 사용 기록을 보고, 지우기 전에 숨겨서 영향을 확인합니다. 아래는 문법 검토만이며 H2 에서 실행하지 않았습니다.
-- Oracle · Tibero
ALTER INDEX idx_status MONITORING USAGE;
ALTER INDEX idx_status INVISIBLE;
ALTER INDEX idx_status VISIBLE;
-- MySQL (8.0)
SELECT * FROM sys.schema_unused_indexes;
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;
-- MSSQL
SELECT * FROM sys.dm_db_index_usage_stats;
ALTER INDEX idx_status ON orders DISABLE;
ALTER INDEX idx_status ON orders REBUILD;Oracle 12cR2 부터는 DBA_INDEX_USAGE 가 사용 기록을 자동으로 모아서 MONITORING USAGE 를 켜지 않아도 됩니다. 사용 기록은 서버가 켜진 뒤부터의 것이라 월말·분기 배치까지 지나간 기간에 봅니다. 숨긴 뒤 문제가 없으면 DROP INDEX 로 지웁니다.