소스: sql-src/mid_09_index/02_explain.sql, 03_dialects.sql. EXPLAIN 으로 인덱스 사용 여부를 직접 비교하고, DB 별 인덱스 문법 차이를 확인합니다. 1,000행은 SYSTEM_RANGE(1, 1000) 으로 만들고, 조회 컬럼은 sal·name 처럼 해당 인덱스에 없는 컬럼을 일부러 골라 인덱스만으로 답이 안 끝나게 합니다.
EXPLAIN SELECT sal FROM emp WHERE dept = '개발';PLAN
-------------------------------------------------------------------------------------------------
SELECT
"SAL"
FROM "PUBLIC"."EMP"
/* PUBLIC.EMP.tableScan */
WHERE "DEPT" = U&'\ac1c\bc1c'
(1행)dept 에 아직 인덱스가 없어 계획 주석이 tableScan입니다. U&'\ac1c\bc1c'는 H2 가 유니코드 이스케이프로 표기한 '개발'입니다. 같은 쿼리를 dept 인덱스를 만든 뒤 다시 돌리면 계획이 바뀝니다.
CREATE INDEX ix_emp_dept ON emp (dept);
EXPLAIN SELECT sal FROM emp WHERE dept = '개발';PLAN
----------------------------------------------------------------------------------------------------------------------
SELECT
"SAL"
FROM "PUBLIC"."EMP"
/* PUBLIC.IX_EMP_DEPT: DEPT = U&'\ac1c\bc1c' */
WHERE "DEPT" = U&'\ac1c\bc1c'
(1행)계획 주석이 PUBLIC.IX_EMP_DEPT: DEPT = ... 로 바뀌었습니다. 옵티마이저가 ix_emp_dept 인덱스로 dept = '개발' 인 행만 좁혀 찾겠다는 뜻입니다.
DROP INDEX ix_emp_dept;
CREATE INDEX ix_emp_dept_hired ON emp (dept, hired);
EXPLAIN SELECT sal FROM emp WHERE dept = '개발' AND hired > DATE '2023-01-01';PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"SAL"
FROM "PUBLIC"."EMP"
/* PUBLIC.IX_EMP_DEPT_HIRED: DEPT = U&'\ac1c\bc1c'
AND HIRED > DATE '2023-01-01'
*/
WHERE ("DEPT" = U&'\ac1c\bc1c')
AND ("HIRED" > DATE '2023-01-01')
(1행)선두 컬럼인 dept 조건이 있어 ix_emp_dept_hired 를 그대로 탑니다. 이번엔 dept 조건을 빼고 hired 조건만 남겨 봅니다.
EXPLAIN SELECT sal FROM emp WHERE hired > DATE '2023-01-01';PLAN
-----------------------------------------------------------------------------------------------------
SELECT
"SAL"
FROM "PUBLIC"."EMP"
/* PUBLIC.EMP.tableScan */
WHERE "HIRED" > DATE '2023-01-01'
(1행)같은 ix_emp_dept_hired 인덱스가 있는데도 tableScan 으로 돌아갔습니다. 선두 컬럼 dept 조건이 빠지면 hired 가 두 번째 컬럼이어도 이 인덱스의 정렬 순서를 활용할 수 없기 때문입니다.
EXPLAIN SELECT sal FROM emp WHERE UPPER(dept) = '개발';PLAN
--------------------------------------------------------------------------------------------------------
SELECT
"SAL"
FROM "PUBLIC"."EMP"
/* PUBLIC.EMP.tableScan */
WHERE UPPER("DEPT") = U&'\ac1c\bc1c'
(1행)ix_emp_dept_hired 인덱스가 있어도 UPPER(dept) 로 컬럼을 가공하는 순간 그 인덱스는 못 탑니다. 인덱스는 원본 컬럼 값 그대로를 정렬해 둔 것이라, UPPER()를 씌운 결과값은 인덱스에 없기 때문입니다. sal + 0 = 500 처럼 컬럼에 연산을 씌운 경우도 같은 원리로 tableScan 이 나오며, 02_explain.sql 예제 7 에서 직접 확인할 수 있습니다.
CREATE INDEX ix_emp_name ON emp (name);
EXPLAIN SELECT sal FROM emp WHERE name LIKE '김%';PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
"SAL"
FROM "PUBLIC"."EMP"
/* PUBLIC.IX_EMP_NAME: NAME >= U&'\ae40'
AND NAME < U&'\ae41'
*/
WHERE "NAME" LIKE U&'\ae40%'
(1행)'김%' 처럼 와일드카드가 뒤에만 있으면 옵티마이저가 이 조건을 NAME >= '김' AND NAME < '김' 같은 범위 조건으로 바꿔 ix_emp_name 을 탑니다. 문자열이 '김' 으로 시작하는 구간만 인덱스에서 좁혀 찾을 수 있기 때문입니다.
EXPLAIN SELECT sal FROM emp WHERE name LIKE '%김';PLAN
------------------------------------------------------------------------------------------------
SELECT
"SAL"
FROM "PUBLIC"."EMP"
/* PUBLIC.EMP.tableScan */
WHERE "NAME" LIKE U&'%\ae40'
(1행)같은 ix_emp_name 인덱스가 있는데도 '%김' 은 tableScan 입니다. 앞쪽이 와일드카드면 "어디서 시작하는지" 자체를 알 수 없어 범위로 좁힐 방법이 없기 때문입니다.
주의LIKE 의 앞쪽 와일드카드 문제는 세 DB 모두 같은 원리로 발생합니다. 검색어가 항상 뒤에 % 만 붙는 검색창이라면 인덱스를 그대로 씁니다. 양쪽에 % 를 붙여야 하는 자유 검색은 인덱스 대신 전문 검색(Full-Text Search)을 따로 검토합니다.
emp_code 는 VARCHAR(10) 컬럼이지만 숫자로만 이루어진 문자열을 담고 있습니다. 이 컬럼에 인덱스를 걸고 자료형이 다른 리터럴로 조회해 봅니다.
CREATE INDEX ix_emp_code ON emp (emp_code);
EXPLAIN SELECT name FROM emp WHERE emp_code = 100;PLAN
-------------------------------------------------------------------------------------------
SELECT
"NAME"
FROM "PUBLIC"."EMP"
/* PUBLIC.EMP.tableScan */
WHERE "EMP_CODE" = 100
(1행)숫자 리터럴 100 으로 비교하자 ix_emp_code 가 있는데도 tableScan 이 나왔습니다. emp_code 는 문자열 컬럼인데 비교 값이 숫자라 자료형이 안 맞아, 인덱스를 탈 수 없게 된 것입니다.
EXPLAIN SELECT name FROM emp WHERE emp_code = '100';PLAN
-------------------------------------------------------------------------------------------------------------
SELECT
"NAME"
FROM "PUBLIC"."EMP"
/* PUBLIC.IX_EMP_CODE: EMP_CODE = '100' */
WHERE "EMP_CODE" = '100'
(1행)문자열 리터럴 '100' 으로 자료형을 맞추자 같은 조건인데도 ix_emp_code 를 그대로 탑니다. 컬럼과 비교 값의 자료형을 맞추는 것만으로 결과가 갈리는 대표적인 예입니다.
주의이 형변환 동작은 H2 옵티마이저의 판단이며, 실제 Oracle · MySQL · MSSQL 은 버전과 설정에 따라 형변환을 거는 방향이 다를 수 있습니다. 어느 DB 든 컬럼의 선언 자료형과 비교 값의 자료형을 맞추는 습관이 가장 안전합니다.
CREATE INDEX ix_emp_sal ON emp (sal);
UPDATE emp SET sal = NULL WHERE MOD(id, 200) = 0;
EXPLAIN SELECT name FROM emp WHERE sal IS NULL;PLAN
--------------------------------------------------------------------------------------------------
SELECT
"NAME"
FROM "PUBLIC"."EMP"
/* PUBLIC.IX_EMP_SAL: SAL IS NULL */
WHERE "SAL" IS NULL
(1행)H2 는 IX_EMP_SAL: SAL IS NULL 로 인덱스를 그대로 씁니다. H2 는 NULL 값도 인덱스에 저장하기 때문입니다. 하지만 Oracle 의 단일 컬럼 B-tree 인덱스는 NULL 을 저장하지 않아, 실제 Oracle 이라면 같은 조건에 이 인덱스를 못 쓰고 풀 스캔으로 돕니다. 이 결과는 H2 옵티마이저 판단이며 실제 DB 는 다를 수 있습니다.
함수 기반 인덱스와 포함 컬럼(covering index 의 부가 컬럼)은 DB 마다 문법이 완전히 다릅니다.
| 기능 | Oracle · Tibero | MySQL | MSSQL |
|---|---|---|---|
| 함수 기반 인덱스 | (UPPER(name)) |
((UPPER(name)))(8.0.13+) |
계산 열 + 인덱스 |
| 포함 컬럼 | 복합 인덱스로 대체 | 복합 인덱스로 대체 | INCLUDE (b, c) |
Oracle 은 함수식을 인덱스 정의에 그대로 씁니다. UPPER(name) 처럼 대소문자를 구분하지 않는 검색이 잦다면, 이 함수 기반 인덱스로 변형 3 의 "컬럼 가공은 인덱스를 못 탄다" 문제를 피할 수 있습니다. MySQL 은 8.0.13 부터 괄호를 두 겹으로 감싼 함수 인덱스를 지원합니다. 두 문법 모두 H2 는 지원하지 않습니다.
SET MODE Oracle;
-- @error
CREATE INDEX ix_ora_upper ON t_ora (UPPER(name));
SET MODE MySQL;
-- @error
CREATE INDEX ix_my_upper ON t_my ((UPPER(name)));예상 오류: Syntax error in SQL statement "CREATE INDEX ix_ora_upper ON t_ora (UPPER[*](name))"; expected "ASC, DESC, NULLS, ,, )"
예상 오류: Syntax error in SQL statement "CREATE INDEX ix_my_upper ON t_my ([*](UPPER(name)))"; expected "identifier"두 오류 모두 "H2 미지원, 문법 검토만" 입니다. MSSQL 은 함수식을 바로 인덱스에 쓰지 못하고, 먼저 계산 열(computed column)을 만든 뒤 그 열에 인덱스를 겁니다. INCLUDE 절은 인덱스 키에는 안 넣지만 조회 시 함께 가져올 컬럼을 지정해, 인덱스만 읽고 원본 테이블까지 안 가도 되게(covering) 만드는 MSSQL 전용 문법입니다.
SET MODE MSSQLServer;
-- @error
ALTER TABLE t_ms ADD name_upper AS UPPER(name);
-- @error
CREATE INDEX ix_inc ON t_inc (a) INCLUDE (b, c);예상 오류: Unknown data type: "AS"
예상 오류: Syntax error in SQL statement "CREATE INDEX ix_inc ON t_inc (a) [*]INCLUDE (b, c)"Oracle · MySQL 은 INCLUDE 절이 없어 같은 효과를 내려면 (a, b, c) 처럼 필요한 컬럼을 복합 인덱스에 함께 넣습니다. 네 문장 모두 H2 문법으로는 지원하지 않아 구문 오류로 확인만 하고, 실제 DB 문법은 위 표로 비교합니다.
CREATE INDEX ix ON t (sal DESC, hired ASC) 처럼 컬럼마다 정렬 방향을 다르게 주는 문법은 표준에 가까워 Oracle · MySQL · MSSQL · H2 모두 그대로 실행됩니다(03_dialects.sql 예제 5).