MySQL 인덱스 사용규칙 (2)

 

GROUP BY 절의 인덱스 사용

GROUP BY 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 GROUP BY 절은 이용할 수 있다.
( 다중 컬럼 인덱스 기준 , 단일 컬럼 인덱스도 동일)

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

 

인덱스 사용규칙 

 (COL1 + COL2 + COL3 + COL4) 로 만들어진 인덱스

 

인덱스를 사용하는 경우 

GROUP BY COL1
GROUP BY COL1,COL2 
GROUP BY COL1,COL2,COL3
GROUP BY COL1,COL2,COL3,COL4

 

 

 

인덱스를 사용하지 못하는 경우 

GROUP BY COL2, COL1 

GROUP BY COL1,COL3,COL2 

--> 
GROUP BY 컬럼이 인덱스를 구성하는 컬럼의 순서와 일치하지 않기 때문에 사용하지 못한다.

------------------------------------------------------------------------------
GROUP BY COL1, COL3 

-->
GROUP BY 절에 COL3 가 명시됐지만 COL2가 그 앞에 명시되지 않았기 때문에 사용 X 
------------------------------------------------------------------------------

GROUP BY COL1,COL2,COL3,COL4,COL5

--> 
GROUP BY 절의 마지막에 있는 COL5가 인덱스에는 없어서 인덱스를 사용하지 못한다.

 

ORDER BY 절의 인덱스 사용

GROUP BY 처리 방법과 비슷하지만 ORDER BY 는 조건이 하나 더 있는데 , 정렬되는 각 컬럼의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 또는 정반대의 경우에만 사용가능하다.

MySQL 의 인덱스는 모든 컬럼이 오름차순으로만 정렬돼 있기 때문에 ORDER BY 절의 모든 컬럼이 오름차순이거나 내림차순일 때만 인덱스를 이용할 수 있다.

 

 

ORDER BY COL2, COL3 

--> 
인덱스의 제일 앞쪽 컬럼이 COL1이 ORDER BY 절에 명시되지 않았기 대문에 인덱스를 사용할 수 없다.
------------------------------------------------------------------------------
ORDER BY COL1 , COL3, COL2

--> 
인덱스와 ORDER BY 절의 컬럼 순서가 일치하지 않기 때문에 인덱스를 사용할 수 없다.
------------------------------------------------------------------------------

ORDER BY COL1, COL2 DESC, COL3 

--> 
ORDER BY 절의 다른 컬럼은 모두 오름차순인데,
두번째 컬럼인 COL2의 정렬 순서가 내림차순이라서 인덱스를 사용할 수 없다.

------------------------------------------------------------------------------

ORDER BY COL1, COL3 

--> 
인덱스에는 COL1 과 COL3 사이에 COL2 컬럼이 있지만 
ORDER BY 절에는 COL2 컬럼이 명시되지 않았기 때문에 인덱스를 사용할 수 없다.

------------------------------------------------------------------------------
ORDER BY COL1,COL2,COL3,COL4,COL5

--> 
인덱스에 존재하지 않는 COL5가 ORDER BY 절에 명시됐기 때문에 인덱스를 사용하지 못한다.

 

WHERE 조건과 ORDER BY (또는 GROUP BY) 절의 인덱스 사용 

 

인덱스의 앞쪽에 있는 컬럼을 WHERE 절에서 상수로 비교하기 때문에 GROUP BY 절에 해당 컬럼이 명시되지 않아도 인덱스를 이용한 경우 

 

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

 

SELECT * FROM tb_test WHERE COL1 > 10 ORDER BY COL1,COL2,COL3;


--> 
COL1>10 조건을 만족하는 COL1 값은 여러 개일 수 있고
COL1부터 COL3 까지 순서대로 모두 명시됐기 때문에 인덱스를 이용해 WHERE,ORDER BY 절을 처리 가능

------------------------------------------------------------------------------

SELECT * FROM tb_test WHERE COL1 > 10 ORDER BY COL2,COL3 ;


--> 
WHERE 절에서 COL1 범위 조건으로 ORDER BY 절에는 COL1 이 명시되지 않았기 때문에 정렬할 때는 
인덱스를 이용할 수 없게 된다.

 

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

GROUP BY 와 ORDER BY 절이 동시에 사용된 쿼리에서 두 절이 모두 하나의 인덱스를 사용해서 처리되려면

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

 

 

Reference

Real MySQL