쿼리의 WHERE, GROUP BY, ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 알아야 쿼리의 조건을 최적화하거나, 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있습니다.
이 글에서는 어떤 조건에서 인덱스를 사용할 수 있고, 사용한다면 100% 활용할 수 있는지 일부만 사용할 수 있는지 살펴봅니다.
1. 비교 조건의 종류와 효율성
mysql> SELECT * FROM dept_emp
WHERE dept_no = 'd002' AND emp_no >= 10114;
위의 쿼리를 위해 dept_emp 테이블에 각각 컬럼의 순서만 다른 2가지 케이스로 인덱스를 생성했다고 가정해봅시다.
1.1. 케이스 A: INDEX (dept_no, emp_no)
dept_no = 'd002' AND emp_no >= 10114인 레코드를 찾고, 그 이후에는 dept_no가 d002가 아닐 때까지 인덱스를 쭉 읽습니다. 이 경우는 읽은 레코드가 모두 클라이언트가 원하는 결과이므로 상당히 효율적으로 인덱스를 이용한 케이스입니다.
dept_no 컬럼과 emp_no 컬럼은 모두 작업의 범위를 결정하는 조건(작업 범위 결정 조건)이므로 이 인덱스는 쿼리 처리 성능을 높이게 됩니다.
1.2. 케이스 B: INDEX (emp_no, dept_no)
emp_no >= 10114 AND dept_no = 'd002'인 레코드를 찾고, 그 이후 모든 레코드에 대해 dept_no가 d002인지 비교하는 과정을 거칩니다.
emp_no 컬럼만 작업 범위 결정 조건이고, dept_no 컬럼은 비교 작업의 범위를 줄이지 못하는 거름종이 역할의 조건(체크 조건)입니다. 작업 범위 결정 조건은 많으면 많을수록 쿼리 처리 성능을 높이지만 체크 조건은 쿼리 처리 성능을 높이지 못하며 오히려 더 느리게 만들 때가 많습니다.
2. 가용성과 효율성 판단
B-Tree 인덱스의 특성상 다음 조건에서는 작업 범위 결정 조건으로 사용할 수 없습니다. 경우에 따라서 필터링 조건으로 사용할 수는 있습니다.
NOT-EQUALS로 비교된 경우("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL)
.. WHERE column <> 'N'
.. WHERE column NOT IN (10, 11, 12)
.. WHERE column IS NOT NULL;
LIKE '%??'(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
.. WHERE SUBSTRING(column, 1, 1) = 'X'
.. WHERE DAYOFMONTH(column) = 1
NOT-DETERMINISTIC 속성의 스토어드 함수가 비교조건에 사용된 경우
- .. WHERE column = deterministic_function()
데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우)
- .. WHERE char_column = 10
문자열 데이터 타입의 콜레이션이 다른 경우
- .. WHERE utf8_bin_char_column = euckr_bin char_column
🎯정리
작업의 범위를 결정하는 조건(작업 범위 결정 조건)은 쿼리 처리 성능을 높입니다.
거름종이 역할의 조건(필터링 조건)은 쿼리 처리 성능을 낮춥니다.
작업 범위 결정 조건으로 사용할 수 없는 경우를 알아두면 쿼리 성능을 높일 수 있습니다.