2022/12/29 ~ 2023/01/05

[danbi5228]

다음 스터디

2022-01-05 pm 10:30

학습 범위

• 11.4 ~ 11.4.2.7

정리 범위

• 11.4 ~ 11.4.2.3 단
• 11.4.2.4 ~ 11.4.2.7 모

[mojh7]

11.4 SELECT

11.4.2.4 ORDER BY 절의 인덱스 사용

GROUP BY와 처리 방법이 비슷하여 인덱스 사용 여부가 GROUP BY의 요건과 거의 흡사

인덱스를 이용할 수 있는 요건

1. 정렬되는 각 칼럼의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 정반대인 경우
2. ORDER BY 절의 칼럼들이 인덱스에 정의된 칼럼의 왼쪽부터 일치해야 한다
   • 인덱스의 모든 칼럼이 ORDER BY 절에 사용돼야 하는 것은 아님, 왼쪽 부터 일치하면 된다

인덱스를 사용하지 못하는 예제

인덱스가 (COL_1 ASC, COL_2 ASC, COL_3 ASC, COL_4 ASC)와 같이 정의됐을 때

1. 인덱스의 제일 앞쪽 칼럼인 COL_1이 ORDER BY절에 명시되지 않음

... ORDER BY COL_2, COL_3

2. 인덱스와 ORDER BY 절의 칼럼 순서가 일치하지 않음

... ORDER BY COL_1, COL_3, COL_2

3. ORDER BY절의 다른 칼럼은 오름차순 인데, 두 번째 칼럼인 COL_2의 정렬 순서가 내림차순이라서 사용 할 수 없음
   • 인덱스가 만약 (COL_1 ASC, COL_2 DESC, COL_3 ASC, COL_4 ASC) 와 같이 정의됐다면 인덱스를 사용할 수 있었다

... ORDER BY COL_1, COL_2 DESC, COL_3

4. COL_1 와 3 사이에 COL_2 칼럼이 있지만 ORDER BY 절에는 COL_2 칼럼이 명시되지 않음

... ORDER BY COL_1, COL_3

5. 인덱스에 존재하지 않은 COL_5가 ORDER BY 절에 명시됐음

... ORDER BY COL_1, COL_2, COL_3, COL_4, COL_5

참고로 ORDER BY 절에서 ASC나 DESC가 생략되면 기본은 오름차순으로 해석

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

쿼리에 WHERE, GROUP BY, ORDER BY 절만 포함돼 있다면 사용된 절 하나에만 초점을 맞춰서 인덱스를 사용할 수 있게 튜닝하면 되지만 실제 애플리케이션에서 사용하는 쿼리는 그렇게 단순하지 않다

그리고 SQL 문장이 WHERE 절과 ORDER BY 절을 가지고 있을 때 WHERE 조건은 A 인덱스를 사용하고 ORDER BY 는 B 인덱스를 사용하도록 쿼리가 실행될 수는 없다

WHERE 절과 ORDER BY 절이 같이 사용된 하나의 쿼리 문장에서 인덱스 사용 요건

1. WHERE 절과 ORDER BY절이 동시에 같은 인덱스를 이용

   • WHRER 절의 비교 조건에서 사용하는 칼럼과 ORDER BY 절의 정렬 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함돼 있을 때

   • 다른 두 방법보다 훨씬 빠른 성능을 보이기에 가능한 이 방식으로 처리하도록 튜닝하는 거나 인덱스를 생성하는 것이 좋다

2. WHERE 절만 인덱스 이용

   • ORDER BY절은 인덱스 이용이 불가능하며, 인덱스를 통해 검색된 결과 레코드를 별도의 정렬 처리 과정(Using Filesort)을 거쳐 정렬을 수행한다
   • 주로 WHERE 절의 조건에 일치하는 레코드의 건수가 많지 않을 때 효율적인 방식

3. ORDER BY 절만 인덱스를 이용

   • ORDER BY 절은 인덱스를 이용해 처리하지만, WHERE 절은 인덱스를 이용하지 못함

   • ORDER BY 절의 순서대로 인덱스를 읽으면서 레코드 한 건씩 WHERE 절의 조건에 일치하는지 비교하고, 일치하지 않을 때는 버리는 형태로 처리함

   • 아주 많은 레코드를 조회해서 정렬해야 할 때는 이런 형태로 튜닝하기도 함

WHERE 절에서 동등 비교 조건으로 비교된 칼럼과 ORDER BY 절에 명시된 칼럼이 순서대로 빠짐없이 인덱스 칼럼의 왼쪽부터 일치해야 한다

WHERE 절에 동등 비교 조건으로 사용된 칼럼과 ORDER BY절의 칼럼이 중첩되는 부분은 인덱스를 사용할 때 문제 되지 않음

하지만 중간에 빠지는 칼럼이 있으면 둘 다 인덱스를 사용할 수 없다

• 이때는 주로 WHERE 절만 인덱스를 이용할 수 있다

인덱스가 (COL_1 ASC, COL_2 ASC, COL_3 ASC, COL_4 ASC) 로 정의됐다고 가정할 때

WHERE 절과 ORDER BY 절이 결합된 두 가지 패턴의 쿼리를 보면

# 1번
...
WHERE COL_1 = ?
ORDER BY COL_2 ASC, COL_3 ASC

# 2번
...
WHERE COL_1 = ?, COL_2 = ?, COL_3 > ?
ORDER BY COL_3 DESC, COL_4 ASC

• 2번 처럼 ORDER BY 절에 해당 칼럼이 사용되고 있다면 WHERE 절에 동등 비교 이외의 연산자로 비교돼도 WHERE과 ORDER BY 조건이 모두 인덱스를 이용할 수 있다

• 일반적으로 WHERE 절에서 동등 비교로 사용된 칼럼과 ORDER BY절의 칼럼이 인덱스를 구성하는 칼럼과 같은 순서로 연속해서 사용됐는지를 확인해야 한다

SELECT *
FROM tb_Test
WHERE col_1=10
ORDER BY COL_2, COL_3;

ORDER BY 절의 칼럼 순서가 인덱스의 칼럼 순서와 달라서 정렬할 때 인덱스를 이용하지 못할 것 처럼 보이는데

SELECT *
FROM tb_Test
WHERE col_1=10
ORDER BY COL_1, COL_2, COL_3;

WHERE 조건이 상수로 동등 비교를 하고 있기 때문에 ORDER BY 절에 COL_1 칼럼을 추가해도 정렬 순서에 변화가 없다

• 즉, 이 쿼리는 변경되기 이전의 쿼리와 같다

하지만 이렇게 변경된 쿼리는 WHERE 절과 ORDER BY 절이 동시에 인덱스를 이용할 수 있는지 더 쉽게 판단할 수 있다

GROUP BY 와 ORDER BY가 인덱스를 사용할 수 있을지 없을지 모호할 때는 이처럼 쿼리를 조금 변경해서 원본 쿼리가 같은 순서나 결과를 보장하는지 확인해 보면 된다

WHERE 조건 절에서 범위 조건의 비교가 사용될 때는?

# 1번
SELECT *
FROM tb_Test
WHERE COL_1 > 10
ORDER BY COL_1, COL_2, COL_3;

# 2번
SELECT *
FROM tb_Test
WHERE COL_1 > 10
ORDER BY COL_2, COL_3;

1번 예제는 col_1 > 10 조건을 만족하는 값이 여러 개 일 수 있지만 ORDER BY절에 COL_1 부터 3까지 순서대로 모두 명시됐기 때문에 인덱스를 사용해 처리할 수 있다

하지만 2번 예제는 WHERE절에서 범위 조건으로 검색됐는데, ORDER BY절에서 COL_1이 명시되지 않았기 때문에 정렬할 때 인덱스를 이용할 수 없게 된다

다음과 같이 WHERE 절과 ORDER BY 절에 명시된 칼럼의 순서가 일치하지 않거나 중간에 빠지는 칼럼이 있으면 인덱스를 이용해 WHERE 절과 ORDER BY 절을 모두 처리하기란 불가능

...WHERE COL_1=10 ORDER BY COL_3, COL_4
...WHERE COL_1>10 ORDER BY COL_2, COL_3
...WHERE COL_1 IN (1,2,3,4) ORDER BY COL_2

WHERE 절과 GROUP BY절의 조합도 모두 똑같은 기준이 적용된다

WHERE 절과 ORDER BY나 GROUP BY 절의 조합에서 인덱스의 사용 여부를 판단하는 능력은 상당히 중요함

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

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

• 둘 중 하나라도 인덱스를 이용할 수 없을 때는 둘 다 인덱스를 사용하지 못한다

인덱스를 이용하지 못하는 예시

... GROUP BY col_1, col_2 ORDER_BY col_2
... GROUP BY col_1, col_2 ORDER_BY col_1, col_3

MySQL 5.7 버전 까지는 GROUP BY가 칼럼에 대한 정렬까지 수행하는 것이 기본 작동 방식

8.0 버전 부터는 칼럼의 정렬까지는 보장하지 않는 형태로 바뀌었기에 그루핑과 정렬을 모두 수행하기 위해서는

GROUP BY와 ORDER BY 절 모두 명시해야 한다

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

WHERE 절, ORDER BY 절, GROUP BY 절이 모두 포함된 쿼리가 인덱스를 사용하는지 판단하는 방법

다음과 같은 3개의 질문을 기본으로 65p 그림에 나온 흐름으로 적용해보면 된다

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

각 절이 인덱스를 사용할 수 있는지에 따른 인덱스 사용 여부 판단

WHERE	GROUP BY	ORDER BY	결과
T	T	T	WHERE 절, GROUP BY 절, ORDER BY 절 모두 인덱스 사용
T	T	F	WHERE 절만 인덱스 사용
T	F		WHERE 절만 인덱스 사용
F	T	T	GROUP BY 절, ORDER BY 절 인덱스 사용
F	T	F	인덱스 전혀 사용 못함
F	F		인덱스 전혀 사용 못함

[danbi5228]

11.4 SELECT

11.4.1 SELECT 절의 처리 순서

• 쿼리에서 '절' 구분
  • SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY / LIMIT
  • WHERE 절 내 INNER JOIN 절 (=ON 절)까지 구분해서 표현하기도 함
• 쿼리 절의 실행 순서
  • GROUP BY 절이 있는 경우의 일반적인 실행 순서
  • 드라이빙 / 드리븐 테이블의 WHERE 적용 및 조인 실행
  • GROUP BY
  • DISTINCT
  • HAVING 조건 적용
  • ORDER BY
  • LIMIT
  • GROUP BY 절이 없이 ORDER BY만 사용된 경우의 실행 순서
  • 드라이빙 테이블에 WHERE 적용
  • ORDER BY
  • 드리븐 테이블과의 조인 실행
  • LIMIT
  • 위 같은 실행 순서를 벗어나는 쿼리가 필요한 경우, 서브쿼리로 작성된 인라인 뷰를 사용해야함 (p. 54)
  • (참고) FROM 절에 위치한 서브쿼리를 외부 쿼리와 병합하며 쿼리를 최적화 할 수도 있지만, 결국 조인으로 실행되므로 위 실행 순서 중 하나로 진행됨
• MySQL의 LIMIT은 오라클의 ROWNUM과 조금 성격이 달라서, WHERE 조건으로 사용하지 않고 항상 모든 처리의 결과에 대해 레코드 건수를 제한하는 형태로 사용
• 8.0 버전에 새로 도입된 WITH절은 항상 제일 먼저 실행되어 임시 테이블로 저장됨. 이후 단독으로 조회되거나 조인되는 테이블로 활용됨
• 윈도우 함수가 사용된 쿼리의 경우는 11.4.12.1 절 참고

11.4.2 WHERE 절과 GROUP BY 절, ORDER BY 절의 인덱스 사용

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

• 인덱스된 칼럼의 값 자체를 변환하지 않고 그대로 사용한다는 조건을 만족해야 함
  • ex. ... WHERE salay*10 > 15000; (인덱스 사용 불가) -> ... WHERE salay> 15000/10; (인덱스 사용 가능)
• 해시값을 만들어서 비교해야 하는 등의 복잡한 연산을 수행한 뒤 비교해야 하는 경우
  • 가상칼럼을 추가해서 인덱스를 사용
  • 함수 기반의 인덱스를 사용
• 연산자 양쪽의 비교대상 값의 데이터 타입을 일치시켜야 함
  • ex. 데이터는 string 타입인데, 쿼리에서 비교시에 integer로 비교하는 경우, 변환 후 비교작업이 필요하므로 인덱스 레인지 스캔 불가

11.4.2.2 WHERE 절의 인덱스 사용

• WHERE 조건이 인덱스를 사용하는 방법 - 작업 범위 결정 조건, 체크 조건
• WHERE 조건의 인덱스 사용 규칙 (예시)
  • 인덱스 컬럼 순서 (인덱스 컬럼 지정시): col1, col2, col3
  • WHERE 조건절 칼럼 나열 순서: 상관 없음. 쿼리에서 조건 생성시에 인덱스 컬럼 순서와 동일하게 맞출 필요는 없음
• 8.0 버전 부터는 각 컬럼의 정렬 순서가 혼합되어도 무관
  • ex. col1 - DESC , col2 - ASC
• OR 연산자로 연결된 경우, WHERE 절에 인덱스가 없는 컬럼에 대한 조건이 포함되면 풀테이블 스캔 (p. 58)

11.4.2.3 GROUP BY 절의 인덱스 사용

• GROUP BY 절의 인덱스 사용 규칙
  • 인덱스 컬럼 순서: col1, col2, col3
  • GROUP BY 절 칼럼 나열 순서: col1, col2, col3
  • GROUP BY 절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야 함
  • 인덱스를 구성하는 칼럼 중에서 뒤쪽에 있는 칼럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만, 인덱스의 앞쪽에 있는 칼럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없음
  • WHERE 조건절과는 달리 GROUP BY 절에 명시된 칼럼이 하나라도 인덱스에 없으면 GROUP BY 절에서 전혀 인덱스를 사용할 수 없음
• GROUP BY 절에 앞쪽 인덱스 컬럼이 명시되어있지 않아도, WHERE 절에서 해당 인덱스들이 동등비교조건으로 사용된다면 인덱스를 사용할 수 있다. (p. 60)