23 Nov 2020

[MYSQL] Where,Group By,Order By 절의 인덱스 사용

참조: Real MySQL(도서)

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

where절이나 order by또는 group by가 인덱스를 사용하려면 기본적으로 인덱스된 컬럼의 값 자체를

변환하지 않고 그대로 사용한다는 조건을 만족해야 한다. 인덱스는 칼럼의 값을 아무런 변환 없이

B-Tree에 정렬해서 저장한다. where,group by, order by에서도 원본값을 검색하거나 정렬할 때만

B-Tree에 정렬된 인덱스를 이용한다.

다음 예제의 where절과 같이 salary칼럼을 가공한후 다른 상수 값과 비교한다면 다음의 쿼리는 인덱스를 적절히 이용하지 못하게 된다.

SELECT * FROM salaries WHERE salary*10>15000;

where절에 사용되는 비교 조건에서 연산자 양쪽의 두 비교 대상 값은 데이터 타입이 일치해야 한다.

create table tb_test (age VARCHAR(10),~~)
select * from tb_test where age=2

위와 같은 경우 인덱스 레인지 스캔이 아니라 인덱스 풀 스캔을 하게 된다. 그 이유는 age칼럼의 데이터 타입(VARCHAR)과

비교되는 값 2(INTEGER 타입)의 데이터 타입이 다르기 때문이다.

여러 컬럼으로 인덱스 구성시 기준

카디널리티(중복도)가 낮은->높은순으로 구성하는게 좋을까?

카디널리티(중복도)가 높은->낮은순으로 구성하는게 좋을까?

CREATE TABLE `salaries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `is_bonus` tinyint(1) unsigned zerofill DEFAULT NULL,
  `group_no` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

카디널리티 순서 group_no > from_date > to_date > is_bonus

CREATE INDEX IDX_SALARIES_INCREASE ON salaries 
(is_bonus, from_date, group_no);

CREATE INDEX IDX_SALARIES_DECREASE ON salaries 
(group_no, from_date, is_bonus);

첫 번째 인덱스는 is_bonus, from_date, group_no순으로 카디널리티가 낮은순에서 높은 순으로

두 번째 인덱스는 group_no, from_date, is_bonus순으로 카디널리티가 높은순에서 낮은 순으로

여러 컬럼으로 인덱스를 잡는 경우 카디널리티가 높은순에서 낮은순으로(group_no, from_date, is_bonus)

구성하는게 성능이 더 뛰어나다.

여러 컬럼으로 인덱스시 조건 누락

인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아니다.

그렇다면 어떤것이 누락되도 되고, 누락되면 안되는것은 무엇일까?

CREATE INDEX IDX_SALARIES_DECREASE ON salaries 
(group_no, from_date, is_bonus);

여기서 중간에 있는 from_date를 제외한 조회 쿼리와 가장 앞에 있는 group_no를 제외한 조회쿼리를 사용해보자

select * from salaries where group_no='abcefe' and is_bonus=true;

인덱스를 정상적으로 사용한다.

select * from salaries where from_date='2020-11-11' and is_bonus=true;

인덱스 사용 못함

중요!!

조회 쿼리 사용시 인덱스를 태우려면 최소한 첫번째 조건은 조회조건에 포함되어야 한다.

첫번째 인덱스 컬럼이 조회쿼리에 없으면 인덱스를 타지 않는다!!!!

인덱스 조회시 주의 사항

between,like,<,> 등 범위 조건은 해당 컬럼은 인덱스를 사용하지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않는다.
- 예시로 group_no, from_date, is_bonus으로 인덱스가 잡혀있는데 쿼리를 다음과 같이 쓸 경우 is_bonus는 인덱스가 사용되지 않는다.
```
  where group_no='123' and is_bounus=Y and from_date > 2020
```
=,in은 다음 컬럼도 인덱스를 사용한다.
- in은 =를 여러번 실행시킨것이다.
- in은 인자값으로 상수가 포함되면 상관없지만, 서브쿼리를 넣게되면 성능상 이슈가 발생한다.
- in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고, in은 체크 조건으로 실행되기 때문이다.
and연산자는 각 조건들이 읽어와야할 row수를 줄이는 역할을 하지만 or연산자는 비교해야할 row가 더 늘어나기 때문에 Full table scan이 발생할 확률이 더 높다.
인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용된다.
- 인덱스는 가공된 데이터를 저장하고 있지 않다.
- where salary*10>15000;은 인덱스를 사용못하지만 where salary>15000는 인덱스 사용가능
null값은 is null조건으로 인덱스 레인지 스캔이 가능하다.

where 절의 인덱스 사용

where 조건이 인덱스를 사용하는 방법은 크게 범위 제한 조건과 체크 조건 두 가지 방식으로 구분한다.

둘 중에서 범위 제한 조건은 동등 비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 칼럼과 얼마나 좌측부터

일치하는가에 따라 달라진다.

create index ~~ (col1,col2,col3,col4);
select * from tb_text where col3>?,col2=?,col1=?,col4=? 

여기서 col3의 조건이 동등 조건이 아닌 크다 작다 비교이므로 col4의 조건은 범위 제한 조건으로 사용되지 못하고 체크 조건으로 사용된 것이다.

where 절에서의 각 조건이 명시된 순서는 중요치 않고, 그 컬럼에 대한 조건이 있는지 없는지가 중요하다.

지금까지 보여준 모든 where 조건은 and 연산자로 연결되는 경우를 가정한 것이며, 다음과 같이 OR연산자가 있으면 처리방법이 완전히 바뀐다.

select * from employees where first_name='홍' or last_name='길동';

위의 쿼리에서 where first_name=’홍’ 조건은 인덱스를 이용할 수 있지만 last_name=’길동’ 은 인덱스를 사용할 수 없다. 만약 이 두 조건이 and연산자로 연결됐다면 first_name의 인덱스를 이용하겠지만, or연산자로 연결됐기 때문에 옵티마이저는 풀 테이블 스캔을 선택할 수밖에 없다. (인덱스 레인지 스캔)+(풀 테이블 스캔)의 작업량보다 (풀 테이블 스캔) 한 번이 더 빠르기 때문이다.

이러한 이유들로 인해 where 조건에서 or연산자가 있다면 주의해야 한다.

Group By 절의 인덱스 사용

group by 절의 각 컬럼은 비교 연산자를 가지지 않으므로 범위 제한 조건이나 체크 조건과 같이 구분해서 생각할 필요가 없다.

group by 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 group by 절은 일단 인덱스를 이용할 수 있다.

사용조건 정리

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

인덱스    Group By절
col1  <-   col1
col2  <-   col2
col3  <-   col3
col4

다음에 예시된 Group By 절은 인덱스를 사용하지 못하는 경우다.

... GROUP BY COL2, COL1
... GROUP BY COL1, COL3, COL2
... GROUP BY COL1, COL3
... GROUP BY COL1, COL2, COL3, COL4, COL5

첫 번째와 두 번째 예제는 GROUP BY 컬럼이 인덱스를 구성하는 컬럼의 순서와 일치하지 않기 때문에 사용하지 못하는 것이다.
세 번째 예제는 GROUP BY 절에 COL3가 명시됐지만 COL2가 그 앞에 명시되지 않았기 때문이다.
네 번째 예제에서는 GROUP BY 절의 마지막에 있는 COL5가 인덱스에는 없어서 인덱스를 사용하지 못하는 것이다.

다음 예제는 GROUP BY 절이 인덱스를 사용할 수 있는 패턴이다.

... GROUP BY col1,
... GROUP BY col1, col2
... GROUP BY col1, col2, col3
... GROUP BY col1, col2, col3, col4

WHERE 조건절에 COL1이나 COL2가 동등 비교 조건으로 사용된다면, GROUP BY 절에 COL1이나 COL2가 빠져도 인덱스를 이용한 GROUP BY가 가능할 때도 있다. 다음 예제는 인덱스의 앞쪽에 있는 컬럼을 WHERE 절에서 상수로 비교하기 때문에 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 절의 인덱스 사용

MYSQL에서 GROUP BY와 ORDER BY는 처리 방법이 상당히 비슷하다. 단, ORDER BY는 조건이 더 있다.

정렬되는 각 컬럼의 오름차순 및 내림차순 옵션이 인덱스와 같거나 또는 정반대의 경우에만 사용할 수 있다.
ORDER BY 절의 모든 컬럼이 오름차순이거나 내림차순일 때만 인덱스를 사용할 수 있다.

인덱스의 모든 컬럼이 ORDER BY 절에 사용돼야 하는 것은 아니지만 인덱스에 정의된 컬럼의 왼쪽부터 일치해야 하는 것에는 변함이 없다.

ORDER BY 절	인덱스	ORDER BY절
COL1(ASC) ->	COL1	<- COL1(DESC)
COL2(ASC) ->	COL2	<- COL2(DESC)
COL3(ASC) ->	COL3	<- COL3(DESC)
전부 오름차순	COL4	전부 내림차순

다음예제는 인덱스가 사용되지 못하는 예시이다.

... ORDER BY COL2, COL3
... ORDER BY COL1, COL3, COL2
... ORDER BY COL1, COL2 DESC, COL3
... ORDER BY COL1, COL3
... ORDER BY COL1, COL2, COL3, COL4, COL5

위의 각 예제가 인덱스를 사용하지 못하는 이유

첫 번째 예제는 인덱스의 제일 앞쪽 컬럼인 COL1이 ORDER BY 절에 명시되지 않았기 때문이다.
두 번째 예제는 인덱스와 ORDER BY 절의 컬럼 순서가 일치하지 않기 때문이다.
세 번째 예제는 ORDER BY 절의 다른 컬럼은 모두 오름차순인데, 두 번째 컬럼인 COL2의 정렬 순서가 내림차순이라서 인덱스를 사용할 수 없다.
네 번째 예제는 인덱스에는 COL1과 COL3사이에 COL2컬럼이 있지만 ORDER BY 절에는 COL2컬럼이 명시되지 않았기 때문에 인덱스를 사용할 수 없다.
다섯 번째 예제는 인덱스에 존재하지 않는 COL5가 ORDER BY 절에 명시됐기 때문에 인덱스를 사용하지 못한다.

WHERE 조건,ORDER BY 절, GROUP BY 절의 인덱스 사용

1. WHERE절이 인덱스를 사용할 수 있는가?

2. GROUP BY 절이 인덱스를 사용할 수 있는가?

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

시작
 ↓
where ------------------------>(아니오)
 ↓(예)                             ↓
GROUP BY -> (아니오)             GROUP BY -> (아니오)
 ↓(예)         ↓                   ↓(예)        ↓
ORDER BY -> (아니오)             ORDER BY -> (아니오)
 ↓(예)         ↓                   ↓(예)        ↓
WHERE절       WHERE절만          GROUP BY절  인덱스 전혀 사용못함
GROUP BY절    인덱스 사용        ORDER BY절
ORDER BY절                      인덱스 사용
모두 인덱스 사용

Tags:

0 comments

[MYSQL] Where,Group By,Order By 절의 인덱스 사용

참조: Real MySQL(도서)

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

where절이나 order by또는 group by가 인덱스를 사용하려면 기본적으로 인덱스된 컬럼의 값 자체를

변환하지 않고 그대로 사용한다는 조건을 만족해야 한다. 인덱스는 칼럼의 값을 아무런 변환 없이

B-Tree에 정렬해서 저장한다. where,group by, order by에서도 원본값을 검색하거나 정렬할 때만

B-Tree에 정렬된 인덱스를 이용한다.

다음 예제의 where절과 같이 salary칼럼을 가공한후 다른 상수 값과 비교한다면 다음의 쿼리는 인덱스를 적절히 이용하지 못하게 된다.

where절에 사용되는 비교 조건에서 연산자 양쪽의 두 비교 대상 값은 데이터 타입이 일치해야 한다.

위와 같은 경우 인덱스 레인지 스캔이 아니라 인덱스 풀 스캔을 하게 된다. 그 이유는 age칼럼의 데이터 타입(VARCHAR)과

비교되는 값 2(INTEGER 타입)의 데이터 타입이 다르기 때문이다.

여러 컬럼으로 인덱스 구성시 기준

카디널리티(중복도)가 낮은->높은순으로 구성하는게 좋을까?

카디널리티(중복도)가 높은->낮은순으로 구성하는게 좋을까?

첫 번째 인덱스는 is_bonus, from_date, group_no순으로 카디널리티가 낮은순에서 높은 순으로

두 번째 인덱스는 group_no, from_date, is_bonus순으로 카디널리티가 높은순에서 낮은 순으로

여러 컬럼으로 인덱스를 잡는 경우 카디널리티가 높은순에서 낮은순으로(group_no, from_date, is_bonus)

구성하는게 성능이 더 뛰어나다.

여러 컬럼으로 인덱스시 조건 누락

인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아니다.

그렇다면 어떤것이 누락되도 되고, 누락되면 안되는것은 무엇일까?

여기서 중간에 있는 from_date를 제외한 조회 쿼리와 가장 앞에 있는 group_no를 제외한 조회쿼리를 사용해보자

중요!!

조회 쿼리 사용시 인덱스를 태우려면 최소한 첫번째 조건은 조회조건에 포함되어야 한다.

첫번째 인덱스 컬럼이 조회쿼리에 없으면 인덱스를 타지 않는다!!!!

인덱스 조회시 주의 사항

between,like,<,> 등 범위 조건은 해당 컬럼은 인덱스를 사용하지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않는다.

예시로 group_no, from_date, is_bonus으로 인덱스가 잡혀있는데 쿼리를 다음과 같이 쓸 경우 is_bonus는 인덱스가 사용되지 않는다.

=,in은 다음 컬럼도 인덱스를 사용한다.

in은 =를 여러번 실행시킨것이다.

in은 인자값으로 상수가 포함되면 상관없지만, 서브쿼리를 넣게되면 성능상 이슈가 발생한다.

in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고, in은 체크 조건으로 실행되기 때문이다.

and연산자는 각 조건들이 읽어와야할 row수를 줄이는 역할을 하지만 or연산자는 비교해야할 row가 더 늘어나기 때문에 Full table scan이 발생할 확률이 더 높다.

인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용된다.

인덱스는 가공된 데이터를 저장하고 있지 않다.

where salary*10>15000;은 인덱스를 사용못하지만 where salary>15000는 인덱스 사용가능

null값은 is null조건으로 인덱스 레인지 스캔이 가능하다.

where 절의 인덱스 사용

where 조건이 인덱스를 사용하는 방법은 크게 범위 제한 조건과 체크 조건 두 가지 방식으로 구분한다.

둘 중에서 범위 제한 조건은 동등 비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 칼럼과 얼마나 좌측부터

일치하는가에 따라 달라진다.

여기서 col3의 조건이 동등 조건이 아닌 크다 작다 비교이므로 col4의 조건은 범위 제한 조건으로 사용되지 못하고 체크 조건으로 사용된 것이다.

where 절에서의 각 조건이 명시된 순서는 중요치 않고, 그 컬럼에 대한 조건이 있는지 없는지가 중요하다.

지금까지 보여준 모든 where 조건은 and 연산자로 연결되는 경우를 가정한 것이며, 다음과 같이 OR연산자가 있으면 처리방법이 완전히 바뀐다.

이러한 이유들로 인해 where 조건에서 or연산자가 있다면 주의해야 한다.

Group By 절의 인덱스 사용

group by 절의 각 컬럼은 비교 연산자를 가지지 않으므로 범위 제한 조건이나 체크 조건과 같이 구분해서 생각할 필요가 없다.

group by 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 group by 절은 일단 인덱스를 이용할 수 있다.

사용조건 정리

Group By 절에 명시된 컬럼이 인덱스 컬럼의 순서와 위치가 같아야 한다.

Where 조건절과는 달리, Group By 절에 명시된 컬럼이 하나라도 인덱스에 없으면 Group By절은 전혀 인덱스를 사용하지 못한다.

인덱스를 구성하는 칼럼 중에서 뒤쪽에 있는 칼럼은 Group By 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 칼럼이 Group By 절에 명시되지 않으면 인덱스를 사용할 수 없다.

다음에 예시된 Group By 절은 인덱스를 사용하지 못하는 경우다.

첫 번째와 두 번째 예제는 GROUP BY 컬럼이 인덱스를 구성하는 컬럼의 순서와 일치하지 않기 때문에 사용하지 못하는 것이다.

세 번째 예제는 GROUP BY 절에 COL3가 명시됐지만 COL2가 그 앞에 명시되지 않았기 때문이다.

네 번째 예제에서는 GROUP BY 절의 마지막에 있는 COL5가 인덱스에는 없어서 인덱스를 사용하지 못하는 것이다.

다음 예제는 GROUP BY 절이 인덱스를 사용할 수 있는 패턴이다.

ORDER BY 절의 인덱스 사용

MYSQL에서 GROUP BY와 ORDER BY는 처리 방법이 상당히 비슷하다. 단, ORDER BY는 조건이 더 있다.

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

ORDER BY 절의 모든 컬럼이 오름차순이거나 내림차순일 때만 인덱스를 사용할 수 있다.

인덱스의 모든 컬럼이 ORDER BY 절에 사용돼야 하는 것은 아니지만 인덱스에 정의된 컬럼의 왼쪽부터 일치해야 하는 것에는 변함이 없다.

다음예제는 인덱스가 사용되지 못하는 예시이다.

위의 각 예제가 인덱스를 사용하지 못하는 이유

첫 번째 예제는 인덱스의 제일 앞쪽 컬럼인 COL1이 ORDER BY 절에 명시되지 않았기 때문이다.

두 번째 예제는 인덱스와 ORDER BY 절의 컬럼 순서가 일치하지 않기 때문이다.

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

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

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

WHERE 조건,ORDER BY 절, GROUP BY 절의 인덱스 사용

1. WHERE절이 인덱스를 사용할 수 있는가?

2. GROUP BY 절이 인덱스를 사용할 수 있는가?

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

Tags: