[MySQL] INDEX를 활용한 쿼리 튜닝

▶  MYSQL 실행 순서

FROM : 조회 테이블 확인

ON

JOIN

WHERE : 데이터 추출 조건 확인

GROUP BY : 컬럼 그룹화

HAVING  : 그룹화 조건 확인

SELECT : 데이터 추출

ORDER BY : 데이터 순서 정렬

 

▶ EXPAIN 과 ANALYZE

▷ EXPAIN

Mysql 서버의 실행계획이 무엇인지 알고싶을 때 사용함

optimizer 로 부터 query 실행 계획을 가져와서 보여준다

 

▷ EXPLAIN ANALYZE

실행 계획의 다양한 지점에서 소요된 행과 측정 시간을 계산하면서 쿼리를 계획하고 실행한다

일반 EXPLAIN 이 추출하는 실행계획, 예상비용 이외에 EXPLAIN ANALYZE 는 실행계획에서 개별 iterator 의 비용을 추출한다

 

예를 들어, Mike 와 Jon 이 2005년 8월에 전화를 건 총 금액을 나열하는 쿼리가 있다

SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

+------------+-----------+----------+
| first_name | last_name | total    |
+------------+-----------+----------+
| Mike       | Hillyer   | 11853.65 |
| Jon        | Stephens  | 12218.48 |
+------------+-----------+----------+

 

EXPLAIN ANALYZE 에서 알 수 있는 추가적인 정보는 다음과 같다

• 첫번째 행을 가져오는 실제 시간 (ms)
• 모든 행을 가져오는 실제 시간 (ms)
• 실제 읽은 행 수  
• 실제 루프 수

EXPLAIN ANALYZE
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

 -> Table scan on <temporary>  (actual time=0.001..0.001 rows=2 loops=1)
    -> Aggregate using temporary table  (actual time=58.104..58.104 rows=2 loops=1)
        -> Nested loop inner join  (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1)
            -> Table scan on staff  (cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1)
            -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)
                -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043) (actual time=0.450..19.988 rows=8024 loops=2)

 

 

▷ EXPLAIN FORMAT=TREE

실행계획 및 예상 비용을 보여주지만, 쿼리의 어떤 작업에서 얼만큼의 시간이 소요되었는지는 알 수 없다.

EXPLAIN FORMAT=TREE
SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
     AND
     payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

 -> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Nested loop inner join  (cost=1757.30 rows=1787)
            -> Table scan on staff  (cost=3.20 rows=2)
            -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894)
                -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=

 

 

※ EXPLAIN 명령어를 사용했을 때 조회할 수 있는 컬럼

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

 

▶ id

식별자이며, SELECT 쿼리 내에서의 일련번호이다. 

 

▶ select_type

SIMPLE : 단순 SELECT (UNION 또는 하위 쿼리를 사용하지 않음)

 

▶ table

참조하는 테이블의 이름이다

 

▶ type

조인 유형이다. 아래로 내려갈수록 권장하지 않는 조인 유형이다

• const : 테이블에는 쿼리가 시작할때 읽히는 하나의 일치하는 row 가 있다. 결과는 항상 1행이기 때문에 이 행의 컬럼은 옵티마이저에서 상수로 간주될 수 있으며 상수테이블은 한번만 읽기 때문에 매우 빠르다. const 는 PK 또는 고유 인덱스의 모든 부분을 상수 값과 비교할 때 사용된다. 
• eq_ref : 조인 조건에서 동등한 값을 비교하는 동등조인에서 사용된다. 보통 PK 나 UNIQUE INDEX 와 같은 인덱스를 사용해서 조인한다. eq_ref 는 조인 조건의 인덱스를 사용해 정확히 일치하는 레코드를 찾아낸다. 
• ref : 비동등조인 또는 범위 조건을 포함하는 조인에서 사용된다. 비동등 조인은 조인 조건에서 동등하지 않은 값을 비교하는 조인유형이다. ref 는 인덱스를 사용해 조인조건에 맞는 범위의 레코드를 찾아낸다. 
• range : 인덱스를 범위로 스캔하여 지정된 범위에 있는 행만 검색된다. WHERE 절에 range=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE와 같은 범위조건이 포함된 경우 사용된다. 
• index : 인덱스 전체 또는 부분을 스캔한다. (인덱스 트리를 스캔한다는 점을 제외하면 index 조인유형과 index 는 동일하다) 인덱스 크기에 따라 성능이 달라진다.
• all : 전체 테이블을 스캔하기때문에 가장 비효율적이다. 

 

▶ possible_keys

MySQL 이 이 테이블에서 데이터를 찾기 위해 선택할 수 있는 인덱스들

 

▶ key

MySQL 이 실제로 사용하기로 결정한 인덱스를 나타낸다.

 

▶ key_len

MySQL 이 사용하기로 결정한 키의 길이

 

▶ ref

ref 컬럼은 테이블에서 행을 선택하기 위해 key 컬럼에 지정된 인덱스와 비교되는 컬럼이나 상수를 보여준다

 

▶ rows

rows 컬럼은 MySQL이 쿼리를 실행하기 위해 검사해야 한다고 생각하는 row의 수를 나타낸다.
InnoDB 테이블의 경우 이 숫자는 추정치이며 항상 정확하지는 않을 수 있다.

 

Extra

이 컬럼에는 MySQL이 쿼리를 해결하는 방법에 대한 추가 정보가 포함되어 있다.

 

 

---

 

※ 오브젝트 스캔 유형

 

▶ 테이블 스캔

인덱스를 거치지 않고 바로 디스크에 위치한 테이블 데이터에 접근하는 유형

 

▷ 테이블 풀 스캔

인덱스를 거치지 않고 테이블로 발 직행하여 처음부터 끝까지 데이터를 훑어보는 방식

 

  1. WHERE 절의 조건문을 기준으로 활용할 인덱스가 없거나

  2. 전체 데이터 대비 대량의 데이터가 필요할 때 테이블 풀 스캔을 수행한다

 

 

▶ 인덱스 스캔

인덱스로 테이블 데이터를 찾아가는 유형

 

▷ 인덱스 범위 스캔 (index range scan)

• 인덱스를 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾아가는 방식
• BETWEEN ~ AND, <, >, LIKE 구문 등이 포함될 경우 인덱스 범위 스캔으로 수행한다
• 좁은범위 : 효율적 / 넓은범위 : 비효율적

 

 

 

▷ 인덱스 풀 스캔 (index full scan)

• 인덱스를 처음부터 끝가지 수행하는 방식
• 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구하는 SQL문에서 인덱스 풀 스캔이 수행된다

 

▷ 인덱스 고유 스캔 (index unique scan)

• PK나 고유 인덱스로 테이블에 접근하는 방식
• 인덱스를 사용하는 사용하는 스캔 방식 중 가장 효율적인 스캔방법이다.
• WHERE 절에 = 조건으로 작성하며, 해당 조인 열이 기본 키 또는 고유 인덱스의 선두 열로 설정되었을 때 활용한다. 

 

▷ 인덱스 루스 스캔 (index loose scan)

• 인덱스의 필요한 부분들만 골라 스캔하는 방식
• 인덱스 범위 스캔처럼 넓은 범위에 전부 접근하지 않고 WHERE절 조건문 기준으로 필요한 데이터와 필요하지 않은 데이터를 구분한 뒤 불필요한 인덱스 키는 무시한다
• 보통 GROUP BY 구문이나 MAX(), MIN() 함수가 포함되면 작동한다

 

▷ 인덱스 병합 스캔 (index merge scan)

• 테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식
• WHERE 문 조건절의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식을 취한다. 통합하는 방법으로는 결합(union)과 교차(intersection) 방식이 있으며 이들은 모두 실행계획으로 출력된다
• 인덱스 병합 스캔은 물리적으로 존재하는 개별 인덱스를 각각 수행하므로 인덱스에 접근하는 시간이 몇배로 걸린다. 

 

 

 

---

※ HINT

데이터를 더 빨리 찾을 수 있게 추가 정보를 전달하는 것을 '힌트' 라고 한다. 데이터베이스에 힌트를 전달함으로서 의도대로 작동하도록 도울 수 있다. 

 

아래와 같이 학생 테이블의 이름 열에 대한 학생_IDX01 인덱스와,

전공 코드 열에 대한 학생_IDX02 인덱스가 있다고 가정했을 때,

각 인덱스는 아래 그림과 같은 물리적 구조로 생성되며, 이때 인덱스의 각 행이 테이블의 기본키(PK : 학번) 값을 가리킨다. 

 

아래 쿼리는 이름이 '유재석' 인 학생 정보를 가져오므로, 학생 열로 구성된 학생_IDX01 인덱스를 이용하면 테이블을 처음부터 끝까지 찾지 않아도 워하는 데이터를 빠르게 찾을 수 있다. 

SELECT 학번, 전공코드
FROM 학생
WHERE 이름 = '유재석';

 

따라서 학생_IDX01 인덱스 를 활용하여 길을 찾아 달라는 힌트를 쿼리에 직접 작성할 수 있다.

이때 인덱스를 사용하겠다는 힌트는 USE INDEX 키워드를 사용하며, 크게 두가지 사항을 명시적으로 작성하여 힌트를 사용한다.

 

1. /*! */ 형태의 주석처럼 힌트를 명시

SELECT 학번, 전공코드
FROM 학생 /*! USE INDEX (학생_INDX01)*/
WHERE 이름 = '유재석';

 

2. 주석 표기 없이 쿼리의 일부로 작성

SELECT 학번, 전공코드
FROM 학생 USE INDEX (학생_IDX01)
WHERE 이름 = '유재석';

 

다음은 MySQL 에서 실무에서 자주 쓰이는 힌트이다.

힌트	설명	활용도
STRAIGHT_JOIN	FROM 절에 작성된 테이블 순으로 조인을 유도하는 힌트	높음
USE INDEX	특정 인덱스를 사용하도록 유도하는 힌트	높음
FORCE INDEX	특정 인덱스를 사용하도록 강하게 유도하는 힌트	낮음
IGNORE INDEX	특정 인덱스를 사용하지 못하도록 유도하는 힌트	중간

 

이렇게 명시적으로 힌트를 작성해도 옵티마이저는 무조건 힌트를 참고하지 않는다. 옵티마이저가 비효율적이라고 예측하면 사용자가 작성한 힌트는 무시될 수 있다.

 

▶ 힌트 사용시 고려사항

테이블이나 인덱스, 뷰 등에 변화가 생기면 SQL 문 실행시 오류가 발생할 가능성도 있으니 SQL 문에 힌트를 작성하면 별도로 관리가 필요하다

 

SQL 문에 학생_IDX01 이라는 인덱스를 사용할 것이라는 USE INDEX 힌트를 작성하는 쿼리

SELECT * 
FROM 학생 USE INDEX (학생_IDX01)
WHERE 이름 = ?;

학생_IDX01 인덱스가 불필요하다고 판단되어 삭제 됨

ALTER TABLE 학생 DROP INDEX 학생_IDX01;

이렇게 인덱스가 삭제되었지만 USE INDEX 가 남아있을 경우 SQL 문을 실행하지 못하고 오류메세지가 출력된다. 

ERROR 1176 (42000): Key '학생_IDX01' doesn't exist in table '학생'

 

 

---

※ 조건 유형

SQL 문의 WHERE 절 조건문 기준으로 디스크에 접근하게 된다.

 

1. 데이터에 액세스하는 조건문으로 데이터를 가져옴

2. 가져온 데이터에서 다시한번 출력한 데이터만 추출함

 

이때 맨 처음 디스크에서 데이터를 검색하는 조건을 액세스 조건이라고 하고, 

디스크에서 가져온 데이터에서 추가로 추출하거나 가공 및 연산하는 조건을 필터조건 이라고 한다. 

 

▶ 액세스 조건 (Access Condition)

디스크에 있는 데이터에 어떻게 접근할 것인지를 다룬다

SQL 튜닝에서 가장 중요한 핵심 사항이다

 

옵티마이저는 WHERE 절의 특정 조건문을 이용해서 소량의 데이터를 가져오고,

인덱스를 통해 시간 낭비를 줄이는 조건절을 선택해 스토리지 엔진의 데이터에 접근하고 MySQL 엔진으로 데이터를 가져온다.

 

다음 예제에는 WHERE 절에 ID = 1 과 CODE = 'A' 조건문이 있지만 ID 열로 생성된 인덱스를 활용해서 TABLE2 테이블의 일부 데이터에 접근하고 있다. 만약 CODE = 'A' 조건문을 액세스 조건을 ㅗ삼아 데이터에 접근한다면 인덱스 활용 없이 대량의 데이터에 접근할 것으로 예측할 수 있다. 

액세스 조건 : ID = 1

 

▶ 필터조건 (Filter Condition)

액세스 조건을 이용해 MySQL 엔진으로 가져온 데이터를 기준으로 추가로 불필요한 데이터를 제거하거나 가공하는 조건이다. 

액세스 조건으로 가져온 데이터를 대상으로 필터조건인 CODE = 'A' 를 적용해 필터링 작업을 한다. 

 

 

 

 

 

 

 

참고 : 업무에 바로 쓰는 SQL 튜닝 책 (https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=274173954&start=slayer)