개요
쿼리 튜닝에서 인덱스가 사용되지 않는 경우가 여러가지가 있다. 아래와 같은 케이스를 피해 SQL을 작성하자
1. 인덱스 컬럼의 변형
select * from table where LOWER(name) ='word';
select * from table where idx - 1 = 5;인덱스의 본래 값을 변형하게 되면 DBMS가 인덱스를 이용하지 않는다.
2. 데이터 타입 변환
select * from table where age = '30'숫자형 컬럼에 문자열 값을 비교하려고 하면 인덱스가 무시될 수 있다.
3. NOT 또는 IN 연산자 사용
NOT일 경우에도 인덱스를 타긴 하지만, 일반적으로 NOT에 사용된 값이 아닌 데이터의 비율이 높은 경우가 많기 때문에 인덱스를 타지 않는 경우가 많다.
마찬가지로 IN일 경우에도, IN에 포함된 데이터들의 비율이 매우 높다면 FULL SCAN을 하는 것이 낫다고 DBMS가 판단하면 인덱스를 타지 않는다.
예시 : 만약 employees라는 테이블에 10,000명의 직원이 있는데 그중 9,000명의 이름이 'John' 이고, 나머지 1,000명의 이름이 'John'이 아닌 상황일 경우
SELECT * FROM employees WHERE name != 'John';위의 쿼리는 1,000건의 결과를 반환하지만, 인덱스를 사용하여 9,000건의 'John'을 제외하는 과정이 포함된다. 이 때문에 DBMS는 테이블 풀 스캔을 선택할 가능성이 높다.
- 인덱스 사용이 적절한 경우: 특정 값을 정확히 조회하거나, 결과 데이터가 소수일 경우.
- 전체 테이블 스캔이 적절한 경우: NOT 조건이 많은 데이터를 제외해야 하는 경우, 즉 제외된 데이터를 제외한 나머지 데이터가 대부분인 경우.
4. 와일드 카드 LIKE문장에서 범위를 전체를 지정할 경우
select * from table where name like '%word';문자열로 이루어진 값을 인덱스로 잡았을 때, %가 앞쪽에 사용되면 정렬 순서를 사용할 수 없으므로 테이블 풀 스캔이 이루어진다.
select * from table where name like 'word%';이런 경우는 문자열 정렬 순서를 그대로 이용할 수 있기 때문에 인덱스를 탈 수 있다.
5. 복합 컬럼 index에서 순서가 잘못된 경우
CREATE INDEX idx_employee_name_age ON employees (name, age);
select * from table where age조건절에서 name을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않는 경우가 대부분이다. 그렇기 때문에 많은 쿼리에서 공통적으로 사용된 조건절의 컬럼을 인덱스 선행 컬럼에 주로 사용한다.
6. OR 조건 일 경우
select * from table where name = 'word' or idx = 5name과 idx가 둘다 인덱스가 걸려있는 경우라해도, DBMS가 최적의 OR 조건을 뽑기 힘들어, 풀 스캔 하는 경우가 많다.
7. 옵티마이저의 선택
select * from table where name = 'word' and id = 'elky';인덱스가 name과 id로 2개가 있을 경우 id나 name 인덱스 중 하나가 선택될 수도 있고, 둘 다 선택될 수도 있다.
어떤 방식으로 선택하느냐가 속도에 중요할 수 있다. 즉 실행 계획을 추적해서 원하는 결과가 나오도록 쿼리 튜닝이 필요하다.
