쿼리 작성 및 최적화 (1)

인덱스를 사용하기 위한 기본 원칙

• 인덱스 컬럼 가공 X 예시) salary * 10
• 인덱스 컬럼의 데이터 타입과 where 절의 데이터 타입이 같아야함
  • select * from tb_test where age=2 ( age(varchar(10), index ix_age(age) )
  ⇒ 이런 경우 데이터 타입이 달라서 인덱스 풀스캔 하게됨

where 절의 index 사용

범위 제한 조건

• 동등 비교, in으로 구성된 조건이 인덱스를 구성하는 컬럼과 얼마나 좌측부터 일치하는가에 따라 다름

SELECT *
FROM employees
WHERE first_name='Kebin' OR last_name='Poly';

• 위 쿼리에서 first_name='Kebin' 조건은 인덱스를 이용할 수 있지만 last_name='Poly'는 인덱스를 사용할 수 없다. 두조건이 AND 연산자로 연결되었다면 first_name의 인덱스를 이요할수 있지만 OR 연산자로 연결됐기 때문에 옵티마이저는 풀 테이블 스캔을 선택할 수 밖에 없다. ( 풀테이블 스캔+인덱스 레인지 스캔) 작업량보다 (풀 테이블 스캔) 한번이 빠르기 때문이다.
• WHERE의 절에서 각 조건이 AND 로 연결되면 읽어와야 할 레코드의 건수를 줄이는 역할을 하지만 각 조건이 OR로 연결되면 읽어서 비교해야 할 레코드가 더 늘어나기 때문에 WHERE 조건에 OR 연산자가 있다면 주의해야 한다.

GROUP BY 절의 인덱스 사용

• GROUP BY 절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야 한다.
• 인덱스를 구성하는 칼럼 중에서 뒷쪽에 있는 칼럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 칼럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없다.
• WHERE 조건절과는 달리, GROUP BY 절에 명시된 칼럼이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 이용하지 못한다.
• 아래같은 경우는 인덱스를 사용 가능

. WHERE COL1='상수' ... GROUP BY COL2, COL3

... WHERE COL1='상수' AND COL2='상수' ... GROUP BY COL3, COL4

... WHERE COL1='상수' AND COL2='상수' AND COL3='상수' ... GROUP BY COL4

ORDER BY 절의 인덱스 사용

• 정렬되는 각 칼럼의 오름차순 혹은 내림차순 옵션이 인덱스와 같거나 정반대의 경우에만 사용할 수 있다는 것이다.

ORDER BY col2, col3 # col1이 누락
ORDER BY col1, col3, col2 # 순서 불일치
ORDER BY col1, col2 DESC, col3 # 중간에 DESC가 껴서 불가
ORDER BY col1, col3 # col2 누락
ORDER BY col1, col2, col3, col4, col5 # col5는 인덱스에 없어서 불가

where 조건과 order by(또는 group by) 절의 인덱스 사용

• SQL 문장이 WHERE 절과 ORDER BY 절을 가진다면, WHERE 조건은 A 인덱스를 사용하고 ORDER BY는 B인덱스를 사용하는 것은 불가능하다.

WHERE 절과 ORDER BY 절이 같이 사용된 쿼리 문장은 다음 3가지중 한가지 방법으로만 인덱스를 이용한다.

1. WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 이용
   1. WHERE 절의 비교 조건에 사용하는 칼럼과 ORDER BY 절의 정렬 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함되어 있을 때 이 방식으로 인덱스를 사용할 수 있다. 나머지 2가지 방법보다 빠른 성능을 보이기 때문에 이 방식으로 처리할 수 있도록 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
2. WHERE 절만 인덱스를 이용
   1. ORDER BY 절은 인덱스를 이용한 정렬이 불가능하며, 인덱스를 통해 검색된 결과 레코드를 정렬 처리 과정(Filesort) 를 거쳐 정렬을 수행한다. 보통 WHERE 절의 조건에 일치하는 레코드 건수가 많지 않을 때 효율적인 방식이다.
3. ORDER BY 절만 인덱스를 이용
   1. ORDER BY 절은 인덱스를 이용해 처리하지만 WHERE 절은 인덱스를 이용하지 못한다. 이 방식은  ORDER BY 절의 순서대로 인덱스를 읽으면서, 레코드 한 건씩을 WHERE 절의 조건에 일치하는지 비교해 일치하지 않을 때는 버리는 형태로 처리한다.

위 그림은 WHERE 과 ORDER BY 절이 결합된 두 가지 패턴의 쿼리를 표현한 것이다. 그림 오른쪽과 같이 ORDER BY  절에 해당 칼럼이 사용되고 있다면 WHERE 절에 동등 비교 이외의 연산자로 비교돼도 WHERE 조건과 ORDER BY 조건이 모두 인덱스를 이용할 수 있다.

SELECT *
FROM tb_test
WHERE col1=10 ORDER BY col2, col3;

얼핏 보면 ORDER BY 절의 칼럼 순서가 인덱스 칼럼 순서와 달라 정렬할 때 인덱스를 이용하지 못할 것 같아 보인다. 이럴 때는 ORDER BY에 인덱스의 첫번째 칼럼인 COL1 칼럼을 포함해서 쿼리를 작성해보자.

SELECT *
FROM tb_test
WHERE col1=10 ORDER BY col1, col2, col3;

위 쿼리는 WHERE 조건이 상수로 동등 비교를 하고 있어서 ORDER BY 절에 col1 칼럼이 추가되어도 정렬 순서에는 변화가 없다. 즉 변경되기 이전의 쿼리와 같지만 WHERE 절과 ORDER BY 절이 동시에 인덱스를 이용할 수 있는지 여부를 더 쉽게 판단할 수 있다.

GROUP BY 절과 ORDER BY 절의 인덱스 사용

• GROUP BY 절에 명시된 칼럼과 ORDER BY에 명시된 칼럼이 순서와 내용이 모두 같아야 한다.

WHERE 조건과 ORDER BY 절, 그리고 GROUP BY 절의 인덱스 사용

• WHERE 절이 인덱스를 사용할 수 있는가?
• GROUP BY 절이 인덱스를 사용할 수 있는가?
• GROUP BY 절과 ORDER BY 절이 동시에 인덱스를 사용할 수 있는가?