[Database] MySQL 실행계획으로 쿼리 최적화를 위한 힌트를 확인하자

이전에 옵티마이저에 대해서 알아보았습니다.

 

https://myallinone.tistory.com/entry/MySQL%EC%9D%98-%EB%91%90%EB%87%8C-%EC%98%B5%ED%8B%B0%EB%A7%88%EC%9D%B4%EC%A0%80optimizer

MySQL의 두뇌, 옵티마이저(optimizer)

 

이 옵티마이저는 사용자의 질의를 받고, 그 질의를 최적의 수단을 통해 데이터를 가져와서 우리에게 돌려줍니다.

 

그 수단이 '쿼리 실행 계획'입니다.

 

MySQL에서 쿼리 실행 계획을 확인하는 방법은 EXPLAIN 명령어을 사용하는 것입니다.

 

이 명령을 사용하면 MySQL 옵티마이저가 쿼리 실행 계획을 생성하는 방법을 보여주는 테이블을 반환합니다.
이 테이블에는 쿼리를 실행할 때 MySQL이 어떤 테이블을 읽고, 어떤 인덱스를 사용하는지 등의 정보가 포함됩니다.

 

그 중에서 우리는 네 가지 컬럼명을 확인해 봅시다.

 

첫 번째,  type

type 열은 MySQL 옵티마이저가 테이블을 읽는 방법을 나타내며, 쿼리의 성능을 결정하는 중요한 열입니다.

다음과 같은 값들이 컬럼으로 들어올 수 있습니다.

 

1) ref

인덱스를 사용하여 참조하는 것을 의미합니다. 즉, WHERE 절이나 JOIN 절에 인덱스가 있는 열을 사용하고, 해당 인덱스를 사용하여 레코드를 참조하는 경우 ref가 표시됩니다.

2) eq_ref

고유 인덱스 또는 기본 키(primary key)를 사용하여 참조하는 것을 의미합니다. 즉, JOIN 작업에서 하나의 테이블에서만 하나의 레코드를 참조하는 경우 사용됩니다.

3) system

const와 같이 상수 값을 사용하여 단일 행을 반환하는 것과 비슷하지만, 상수 대신 서브쿼리 또는 유저 변수를 사용하는 경우에 표시됩니다. 즉, 단일 레코드를 참조하기 위해 파일 또는 테이블 외부에서 가져온 데이터를 사용하는 경우에 발생할 수 있습니다.

4) const

하나의 테이블에서 하나의 레코드를 참조하는 경우에 해당하는 조인 유형을 의미합니다.

5) range

조건절에서 범위 연산자(>, >=, <, <=) 또는 BETWEEN을 사용한 경우에 해당하는 조인 유형입니다. 인덱스의 범위를 참조하여 매칭되는 레코드를 반환합니다.

6) index

WHERE 절에서 IN 또는 OR 같은 조건 연산자를 사용하여 인덱스를 사용한 경우에 해당하는 조인 유형입니다. 인덱스를 스캔하여 매칭되는 레코드를 반환합니다.

7) unique_subquery

서브쿼리에서 단일 결과를 반환하는 경우에 해당하는 조인 유형입니다. 서브쿼리의 결과를 캐시하고 캐시된 값을 사용하여 매칭되는 레코드를 반환합니다.

 

우리가 여기서 신경쓸 것은 ALL과 system 컬럼 입니다.

보편적으로 ALL과 system 조인 유형은 효율적이지 않으므로 최대한 피하는 것이 좋습니다.

ALL은 테이블의 모든 레코드를 검색해야 하는 경우에 사용되며, 대용량의 테이블에서는 매우 느리고 비효율적일 수 있습니다. system은 시스템 테이블에서 정보를 가져올 때 사용되는 조인 유형이므로 일반적으로 사용되지 않습니다.

 

반면에 ref, eq_ref, range, index, unique_subquery, const 조인 유형은 적절한 인덱스가 있는 경우에는 매우 빠르고 효율적인 작업을 수행할 수 있으므로 이러한 유형의 조인을 사용하는 것이 좋습니다.

 

두 번째, key 

key 열은 MySQL 옵티마이저가 사용한 인덱스를 나타내며, key 값이 NULL인 경우 인덱스가 사용되지 않았음을 나타냅니다. key 값이 NULL이라는 것은 적절한 인덱스가 사용되지 않았다는 것을 의미할 뿐만 아니라, 옵티마이저가 전체 테이블 스캔을 수행하고 있다는 것도 나타냅니다. 따라서 key 값이 NULL이면 해당 쿼리의 성능이 저하될 가능성이 있습니다.

 

좋은 소식은 key 값이 NULL인 경우가 항상 나쁜 쿼리라고 할 수는 없다는 겁니다.

예를 들어, 테이블이 작고 인덱스가 필요하지 않은 경우나, 적절한 인덱스를 생성하지 않아서 인덱스를 사용할 수 없는 경우에는 key 값이 NULL일 수 있습니다. 그러나 이러한 경우에는 쿼리 처리 속도가 저하되므로, 가능하면 인덱스를 사용하도록 쿼리를 작성하고, 적절한 인덱스를 생성하여 성능을 최적화하는 것이 좋습니다.

 

 

세 번째, rows

rows 열은 MySQL 옵티마이저가 예상하는 결과 집합의 크기입니다.

쿼리에서 rows 값이 작을수록 더 빠르게 처리되겠죠?

 

네 번째, extra

extra 열은 추가 정보를 제공합니다.

예를 들어, Using index의 경우 인덱스를 사용하고 있다는 것을, Using where의 경우 WHERE 조건을 사용하고 있다는 것을 나타냅니다. 그리고 Using temporary, Using filesort 등은 MySQL에서 정렬을 위해 임시 테이블을 생성하거나 파일 정렬을 수행할 때 발생하는 Extra 값입니다.

 

만약 Using temporary와 Using filesort가 없고, Using where도 없다면, 쿼리가 인덱스를 사용하여 효율적으로 검색하고 있다는 것을 의미합니다. 이 경우, MySQL 서버가 추가적으로 임시 테이블을 만들거나 파일 정렬을 수행할 필요가 없으므로 성능이 더 좋아질 수 있습니다.

이러한 정보를 기반으로 쿼리 실행 계획을 분석하고, 쿼리의 성능을 최적화할 수 있습니다. 이를 통해 적절한 인덱스를 생성하거나, JOIN의 순서를 변경하여 성능을 개선할 수 있습니다.