3장 SQL 튜닝의 실행 계획 파헤치기 - (2)

3.2.2.3 table

테이블명이나 테이블 알리아스를 표시하는 항목이다. 서브쿼리나 임시 테이블을 만들어서 별도의 작업을 수행할 때는 <subquery#>, <derived#>으로 표시한다.

 

3.2.2.4 partitions

데이터가 저장된 논리적인 영역을 표시하는 항목이다. 사전에 정의한 전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 SQL 성능 측면에서 유리하다. 만약 너무 많은 영역의 파티션에 접근하는 것으로 출력된다면 파티션 정의를 튜닝하는 것이 좋다.

 

3.2.2.5 type

옵티마이저가 테이블의 레코드를 어떤 방식으로 읽었는지 나타낸다. 12개의 값이 있는데 ALL을 제외한 나머지 값은 인덱스를 사용하는 접근 방법이다. 

 

const

테이블의 레코드 건수와 관계없이 쿼리가 PK나 유니크 인덱스를 이용하는 WHERE 조건절을 가지고 있으면 반드시 1건을 반환하는 쿼리의 처리 방식이다. 

EXPLAIN 
SELECT *
  FROM employees 
 WHERE emp_no = 10001;

 

EXPLAIN 
SELECT *
  FROM dept_emp
 WHERE dept_no = 'd005'
   AND emp_no = 10001;

위 쿼리에서 dept_emp 테이블은 dept_no, emp_no 조합의 복합키를 가지고 있다.

type이 const인 실행 계획은 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화한다. 그래서 상수(const)로 표시되는 것이다. 아래 쿼리를 살펴보자

EXPLAIN
SELECT COUNT(1)
  FROM employees e1
 WHERE first_name = ( SELECT first_name 
                        FROM employees e2
                       WHERE emp_no = 100001 );

e2 테이블의 type은 const인 것을 확인할 수 있다. 서브쿼리 WHERE 절에서 PK를 동등 조건(equal)으로 비교를 하였기 때문에 나올 수 있는 레코드 수의 경우의 수는 0, 1건이며 const가 표시되었다는 것은 1건이 나왔다는 것을 의미한다.
실제 위 쿼리는 옵티마이저에 의해 최적화되는 시점에 다음과 같은 쿼리로 변환된다. 즉, 옵티마이저에 의해 상수화된 다음 쿼리 실행기로 전달되기 때문에 접근 방법이 const인 것이다.

EXPLAIN
SELECT COUNT(1)
  FROM employees e1
 WHERE first_name = 'Jasminko'; -- 'Jasminko'는 emp_no가 100001인 사원의 first_name 값

 

eq_ref

조인이 포함되어 있는 쿼리에서만 표시된다. 드라이빙 테이블과 드리븐 테이블을 연결시켜주는 컬럼의 값이 드리븐 테이블 측에 무조건 레코드 1건씩 연결이 되며 드리븐 테이블의 연결 컬럼의 제약조건이 NOT NULL이라면 드리븐 테이블의 type에 eq_ref가 표시된다. 아래 실행 계획을 살펴보자.

-- eq_ref 실행계획
EXPLAIN 
SELECT e.emp_no, t.title
  FROM employees e
 INNER JOIN titles t 
    ON e.emp_no = t.emp_no
 WHERE e.emp_no BETWEEN 10001 AND 10100;

id값이 t, e 테이블 모두 1이기 때문에 조인이라는 것을 확인할 수 있다. 실행 계획에서 e테이블보다 t테이블이 상단 위치하기 때문에 t테이블이 드라이빙 테이블이고 e테이블이 드리븐 테이블이다. e테이블의 emp_no 컬럼은 PK이기 때문에 제약조건이 NOT NULL이고 연결되는 레코드가 무조건 1건씩만 존재하기 때문에 e테이블의 type은 eq_ref가 표시된다.

 

ref

반드시 동등 조건(equal) 이어야 한다. 조인이 포함된 쿼리문에서 드리븐 테이블의 연결된 컬럼의 레코드 건수가 1개 이상이 가능한 경우에 나타난다. 그리고 조인이 포함되지 않은 쿼리문에서 조회 레코드 건수가 1개 이상이 가능한 경우에 나타난다. 다음 실행계획을 살펴보자.

EXPLAIN 
SELECT STRAIGHT_JOIN e.emp_no, t.title
  FROM employees e
 INNER JOIN titles t 
    ON e.emp_no = t.emp_no
 WHERE e.emp_no BETWEEN 10001 AND 10100;

eq_ref 실행계획 유사하며 유일하게 다른점은 SELECT절에 STRAIGHT_JOIN 키워드가 추가되었다. 그래서 드라이빙 테이블이 t테이블이 아니라 e테이블이 되었다. 드리븐 테이블인 t테이블의 emp_no컬럼은 복합키 중 하나의 컬럼이기 때문에 중복된 값이 있을 수 있다. 그래서 조인시 연결되는 레코드 건수가 1개 이상일 수가 있을 수 있고 테이블간 연결된 컬럼은 동등 조건(equal)이기 때문에 type에 ref가 표시된다.

titles 테이블은 복합키(emp_no, titles, from_date)로 구성되어있다. 아래 실행계획을 살펴보자.

EXPLAIN 
SELECT *
  FROM titles
 WHERE emp_no = 10001;

위 두 이미지는 실행계획의 결과와 쿼리문만 실행했을 때 결과이다. 실행했을 때 결과의 레코드가 1건이기 때문에 실행계획의 type이 const가 될 것으로 보이지만 titles 테이블에 emp_no 값이 10,001 레코드를 추가가 가능하기 때문에 현 상황의 레코드 1건이 중요한 것이 아니라 1건 이상이 나올 가능성이 있으면 type에 ref가 표시된다.

EXPLAIN 
SELECT *
  FROM titles
 WHERE emp_no = 10001
   AND title = 'Senior Engineer';

위 쿼리는 결과 레코드도 1건이지만 레코드가 추가가 된다면 1건 이상이 나올 가능성이 있기 때문에 type에 ref가 표시된다.

EXPLAIN 
SELECT *
  FROM titles
 WHERE emp_no = 10001
   AND title = 'Senior Engineer'
   AND from_date = '1986-06-26';

위 쿼리는 WHERE절에 emp_no, title, from_date 컬럼이 모두 존재하기 때문에 복합키인 PK조회이기 때문에 레코드가 1건 이상이 불가능하다. 그래서 type에 const가 표시된다.

 

ref_or_null

이름 그대로 ref 또는 NULL 비교 접근 방법을 의미한다. ref보다 속도가 떨어지지만 차이가 크지 않기 때문에 ref와 같다고봐도 무방하다. WHERE절에 NULL값을 찾는 조건식이 있다면 ref_or_null이 표시되고 NULL값을 찾는 조건식이 없다면 ref가 표시된다.

-- 만약 titles 테이블의 to_date 컬럼에 인덱스가 없다면 아래 쿼리로 생성
CREATE INDEX idx_titles_todate
    ON titles(to_date);
    
    
EXPLAIN 
SELECT * 
  FROM titles 
 WHERE to_date = '1985-03-01'
    OR to_date IS NULL;

 

EXPLAIN 
SELECT * 
  FROM titles 
 WHERE to_date = '1985-03-01';
 

-- idx_titles_todate 인덱스를 삭제하고 싶다면
ALTER TABLE titles 
 DROP INDEX IDX_TITLES_TODATE;

 

유니크 인덱스 생성된 컬럼에서 쿼리문에 따라 type이 모두 다르게 나올 수 있다. 테이블 생성하고 데이터를 삽입 후 SELECT문으로 데이터의 ROW를 확인하자.

-- NULL 허용 컬럼에 유니크 인덱스를 생성
CREATE TABLE unique_null_test (
    id INT PRIMARY KEY AUTO_INCREMENT 
  , nm VARCHAR(10) UNIQUE NULL
);

-- nm 값이 있는 Row Insert
INSERT INTO unique_null_test
SET nm = 'aaa';

-- nm이 NULL인 Row Insert 2번
INSERT INTO unique_null_test
SET nm = NULL;

INSERT INTO unique_null_test
SET nm = NULL;

SELECT * FROM unique_null_test;

 

/* 
	nm 컬럼에 NULL 레코드가 2건 이상이 될 수 있음
	TYPE: ref
*/
EXPLAIN 
SELECT id
  FROM unique_null_test
 WHERE nm IS NULL;


/* 
	nm 컬럼에 유니크 속성으로 equal 조건으로는 레코드가 무조건 1건
	TYPE: const
*/
EXPLAIN 
SELECT id
  FROM unique_null_test
 WHERE nm = 'aaa';


/* 
	nm 컬럼에 유니크 속성으로 equal 조건으로는 레코드가 무조건 1건이 나올 수 있고
    NULL 레코드가 2건 이상이 될 수 있음.
	TYPE: ref_or_null
*/
EXPLAIN 
SELECT id
  FROM unique_null_test
 WHERE nm = 'aaa' 
    OR nm IS NULL;

 

fulltext

FULL TEXT INDEX를 활용하여 텍스트 검색을 빠르게 처리할 수 있다. n-gram parser를 사용하였고 기본값이 2이기 때문에 문자열에서 2글자씩 잘라 토큰을 만들어 인덱스를 만든다. 예를들어 " abcd"라는 문자열이 있을 때 n-gram의 값이 2라면 "ab", "bc", "cd" 이렇게 토큰을 만든다.

-- 테이블 추가
CREATE TABLE employee_name (
	  emp_no INT NOT NULL
	, first_name VARCHAR(14) NOT NULL
	, last_name VARCHAR(16) NOT NULL 
	, PRIMARY KEY (emp_no)
	, FULLTEXT KEY fx_name(first_name, last_name) WITH PARSER ngram
);


-- 테이블에 레코드 추가
INSERT INTO employee_name
(emp_no, first_name, last_name)
SELECT emp_no, first_name, last_name
FROM employees;

 

-- MATCH - AGAINST 명령어를 이용
-- 조회시간: 36.8ms
EXPLAIN 
 SELECT *
   FROM employee_name
  WHERE MATCH(first_name, last_name) 
AGAINST ('Facello' IN BOOLEAN MODE);

위 실행계획은 MATCH-AGAINST 명령어를 이용한 전문 인덱스를 활용하여 검색하였다.

-- 조회시간: 141ms
EXPLAIN 
SELECT *
  FROM employee_name
 WHERE first_name LIKE '%Facello%' 
    OR last_name LIKE '%Facello%';

위 실행계획은 LIKE문을 활용하여 검색하였다. 

LIKE와 MATCH-AGAINST 명령어의 검색 결과는 조건에 따라 결과가 다를 수 있다.