서론
데이터베이스에서 원하는 정보를 빠르고 효율적으로 찾기 위해 인덱스를 사용한다. 일반적으로 기본 키를 통해 원하는 데이터를 인덱싱하기도 하고 Unique 인덱스, 혹은 일반적인 인덱스를 사용하기도 한다.
그리고 생성한 인덱스를 기준으로 데이터베이스에 쿼리를 날려 원하는 데이터를 가져온다.
하지만 실제로 우리가 원하는 인덱스를 잘 타고 데이터베이스가 데이터를 가져올까? 정답은 꼭 그렇지 만은 않다.
데이터베이스에는 옵티마이저라는 각 DBMS 만의 Parser의 결과에 따라 쿼리의 실행 계획을 최적화하는 엔진이 존재한다. 이 옵티마이저가 실제로 어떤 전략에 따라 데이터를 가져오는지는 직접 확인해봐야 한다.
MySQL 기준 EXPLAIN 이라는 명령어를 통해 어떤 쿼리의 실행 계획을 볼 수 있다.
실무에서 필요에 따라 옵티마이저를 사용하곤 했다. 사용할 때마다 간단히 검색해서 얕게 사용해 왔는데, 최근 SQL 튜닝 관련책에서 다루는 내용을 읽고 정리해 볼까 한다.
EXPLAIN
그러면 실제로 EXPLAIN 명령어를 사용해보자.
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 20080 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+쿼리 앞에 EXPLAIN을 붙여서 실행하면 위 처럼 실행 계획을 SQL 쿼리의 결과로 알려준다. id, select_type, table, partitions 등.. 이해할 수 없는 컬럼에 결과가 실행 계획을 나타낸다.
지금부터 하나하나 자세하게 알아보자.
컬럼의 의미
id
id는 실행 순서를 나타낸다. 조인시에는 똑같은 ID가 표시된다. ID의 숫자가 작을 수록 먼저 실행되었고 숫자가 같으면 조인이 일어난 것이다.
select_type
SELECT 문의 유형을 나타낸다. SELECT 문이 FROM 절에 위치하는지, 서브쿼리인지, UNION 절로 묶인 SELECT 문인지 알려준다.
SIMPLE은 UNION이나 내부 서브 쿼리가 없는 간단한 SELECT 문을 나타낸다.
PRIMARY는 서브쿼리가 포함된 SQL 문에서 첫 번째 SELECT 문에 해당하는 구문에 표시된다. 또 UNION 절에서 위에 오는 쿼리에 표시된다. 서브 쿼리가 있는 경우 먼저 접근하는 쿼리라는 뜻으로 이해하면 된다.
SUBQUERY는 말 그대로 서브쿼리로 실행됨을 의미한다. 옵티마이저가 이를 서브쿼리로 인식했다는 의미이다.
DERIVED는 FROM 절에 작성된 서브쿼리라는 의미이다. FROM 절에 별도로 임시 테이블인 인라인 뷰에 사용된 쿼리를 뜻한다.
UNION은 UNION ALL 구문으로 합쳐진 쿼리중 PRIMARY로 검출되지 않은 나머지 SELECT 문을 의미한다.
UNION RESULT는 UNION 구문으로 SELECT 절을 결합했을 때 출력된다.
DEPENDENT SUBQUERY는 UNION 혹은 UNION을 사용하는 서브쿼리가 메인테이블에 영향을 받는 경우를 뜻한다. UNION으로 연결된 쿼리 중 가장 처음으로 작성한 단위 쿼리에 해당된다.
DEPENDENT UNION은 DEPENDENT SUBQUERY와 같은 환경에서 첫 번째 단위쿼리를 제외하고 두 번째 단위 쿼리에 해당하는 경우이다.
DEPENDENT SUBQUERY와DEPENDENT UNION은 둘 다 쿼리가 독립적으로 수행되지 못하고 메인 테이블로부터 하나씩 값을 받는 구조로 튜닝의 대상이 된다.
UNCACHEABLE SUBQUERY는 메모리에 상주하여 재활용되어야 할 서브쿼리가 재사용되지 못할 때 출력된다.
RAND 함수를 조건절에 붙이는 경우 발생할 수 있다.
MATERIALIZED는 IN 절 구문에 연결된 서브쿼리가 임시 테이블을 생성한 뒤 조인이나 가공작업을 수행할 때 나타난다. 즉 IN 조건절에 서브쿼리로 임시테이블을 만드는 경우이다.
table
table은 말 그대로 테이블명을 표시한다. 서브 쿼리나 인라인 뷰 임시테이블을 만들 때는 subquery 혹은 derived라고 표현된다.
partitions
실행계획의 부가 정보로, 데이터가 저장된 논리적인 영역을 표시한다. 전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 성능 측면에서 유리하다. 즉 너무 많은 파티션에 접근하는 것으로 출력되면 파티션 정의를 튜닝해야 한다.
type
테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공한다. 풀 스캔을 할지, 인덱스 스캔을 할지 등을 보여준다.
system은 테이블에 데이터가 없거나 한개만 있는 경우로 성능상으로 가장 좋은 type이다.
const는 조회되는 데이터가 단 1건일 때 출력되는 유형으로 성능상에 매우 유리하다. 고유 인덱스나 기본 키를 사용해 접근하는 경우이다.
eq_ref는 조인이 수행될 때 드리븐 테이블의 데이터가 접근하여 고유 인덱스 또는 기본키로 단 1건의 데이터를 조회하는 방식이다. 조인할 때 가장 유리한 방법이다.
ref는 조인을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우이다. 드라이빙 테이블의 1개 값이 드리븐 테이블의 2개 이상의 데이터가 검색되는 경우이다. 드리븐 테이블의 데이터 양이 많으면 성능 저하를 확인해야 한다. 또는 =, <, > 등의 연산자를 사용해 인덱스로 생성된 열을 비교할 때도 출력된다.
ref_or_null은 ref와 유사하지만 IS NULL 구문이에 인덱스를 활용하도록 최적화된 방식이다. MySQL은 NULL에 대해서도 인덱스를 활용하여 검색할 수 있다.
range은 테이블 내의 연속된 데이터 범위를 조회하는 유형으로 =, <>. >. >=, <, <=, IS NULL, BETWEEN, IN을 통해 범위 스캔을 수행하는 방식이다. 이 역시 성능 저하의 요인이 될 수 있다.
fulltext는 텍스트 검색을 빠르게 처리하기 위해 전문 인덱스(fulltext index)를 사용하여 데이터에 접근하는 방식이다.
index_merge는 결합 인덱스가 동시에 사용되는 유형을 의미한다.
index는 인덱스 풀 스캔을 의미한다. 물리적인 인덱스 블록의 처음부터 끝까지 전부 탐색한다.
all은 테이블 풀 스캔에 해당된다. 인덱스 활용이 오히려 비효율적이라고 옵티마이저가 판단할 때에도 all 이 출력된다.
튜닝을 위해 인덱스를 사용할 수도 있지만 전체 테이블 중 10~20% 이상 분량의 데이터를 조회할 때는 ALL 유형이 오히려 성능상 유리할 수 있다.
possible_keys
옵티마이저가 SQL문을 최적화하고자 사용할 수 있는 인덱스 목록을 출력한다. 하지만 실제 사용한 인덱스가 아닌, 사용할 수 있는 후보군의 기본 키와 인덱스 목록만 보여주므로 SQL 튜닝의 효용성은 없다.
key
옵티마이저가 SQL문을 최적화하기 위해 사용한 키나 인덱스 명을 의미한다. 키나 인덱스를 사용하지 않으면 NULL이 출력된다.
key_len
인덱스를 사용할 때는 인덱스 전체를 사용하거나 일부 인덱스만 사용한다. key_len은 이렇게 사용한 인덱스의 바이트 수를 의미한다. UTF-8 기준으로 INT는 단위당 4바이트 VARCHAR는 단위당 3바이트이다.
PK가 int이고 Index가 VARCHAR(50)이면 int는 4바이트, VARCHAR(50)은 (50+1) * 3 = 155 바이트이므로 총 159 바이트의 key_len을 갖게 된다.ref
테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 액세스 되었는지 알려준다.
rows
쿼리 실행을 위해 접근하는 데이트의 모든 행 수를 나타낸다. 쿼리의 최종 결과 건수에 비해 rows가 너무 많으면 너무 많은 데이터를 가져왔다는 뜻으로 튜닝이 필요하다.
filtered
SQL문을 통해 DB 엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는지 의미한다. 100 건의 데이터 중 10건만 필터링하여 가져온다면 10%가 출력된다.
extra
SQL 문을 어떻게 수행할 것인지 알려준다
distinct 는 중복이 제거 되어 유일한 값을 찾을 때 출력된다.
Using where는 Where 절으로 필터하여 가져올 때 출력된다.
Using temporary는 데이터의 중간 결과를 저장하고자 임시 테이블을 생성하겠다는 의미이다. 보통 DISTINCT, GROUP BY, ORDER BY를 사용하면 출력된다. 임시 테이블을 메모리에 생성하므로 성능 저하의 원인이 될 수 있다. 이 항목이 출력되면 튜닝의 대상이 될 수 있다.
Using Index는 물리적인 데이터 파일을 읽지 않고 인덱스만을 읽어서 처리하는 경우 이다.
Using filesort는 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미이다. 인덱스를 사용하지 못 할때 메모리에 올려서 filesort로 추가적인 정렬작업을 한다는 의미이므로 튜닝의 대상이 된다.
Usinbg join buffer는 조인을 위해 중간 데이터를 저장하는 조인 버퍼를 사용한다는 의미이다.
Using union/Using intersect/Using sort_union은 위 type 항목에서 index_merge를 통해 두 개 이상의 인덱스가 병합되어 사용되는 경우 인덱스를 어떻게 병합했는지에 대한 내용이다. union은 인덱스를 합집합 처럼 결합한다는 뜻으로 OR 구문에 해당되면 intersect는 AND, sort_union은 WHERE 절의 OR 구문이 동등조건이 아닐 때 확인할 수 있다.
Using index condition은 Using where 방식과 달리 필터조건을 스토리지 엔진으로 전달하여 필터링 작업에 대한 MySQL 엔진의 부하를 줄이는 방식이다. 스토리지 엔진으로부터의 결과를 줄여서 MySQL 엔진에 전달하므로 성능 효율을 높인다.
Using index for group-by는 Group by나 Distinct가 포함될 때 인덱스로 정렬작업을 수행하는 인덱스 루스 스캔(필요한 인덱스만 사용하여 검색)일 때 출력된다.
Not exists 하나 일치하는 행을 찾았을 때 추가로 더 검색하지 않아도 될 때 출력된다.
이해하며 쓰다보니 책 내용을 거의 그대로 옮긴 것 같다.
그래서
어떤 실행 계획이 튜닝이 필요할까?
위 내용으로 부터 간단히 정리하면 select_type의 경우엔 SIMPLE, PRIMARY, DERIVED는 효율적이고 DEPENDENT , UNCACHEABLE 은 튜닝의 대상이 된다.
type의 경우엔 system, const, eq_ref는 좋고, index, all은 튜닝의 대상이 된다.
extra의 경우 Using index는 효율적이고, Using filesort, Using temporary는 튜닝의 대상이 된다.
출처: 업무에 바로 쓰는 sql 튜닝
