본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
이전 포스팅 ( Real MySQL [2] 실행계획 - table, type 칼럼 ) 에서 계속...
fulltext
fulltext는 MySQL 전문 검색(Fulltext) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다. 전문 검색 인덱스는 통계 정보가 관리되지 않으며, 전혀 다른 SQL을 사용해야 한다. 그래서 MySQL 옵티마이저는 전문 인덱스를 사용할수 있는 SQL에서는 쿼리의 비용과는 관계없이 거의 매번 fulltext 접근 방법을 사용한다.
MySQL 전문 검색 조건은 우선순위가 상당히 높다. 전문 검색은 "MATCH ... AGAINST ..." 구문을 사용해 실행하는데 해당 테이블의 전문 검색용 인덱스가 준비돼 있어야만 한다. 전문 인덱스가 없다면 쿼리는 오류를 발생하고 중지된다.
EXPLAIN
SELECT *
FROM employee_name
WHERE emp_no = 10001
AND emp_no BETWEEN 10001 AND 10005
AND MATCH(first_name, last_name) AGAINST('Facello' IN BOOLEAN MODE);
위 쿼리 문장은 3개의 조건을 가진다.
1) employee_name 테이블의 프라이머리 키를 1건만 조회하는 const 타입의 조건
2) range 타입의 조건
3) 전문 검색(Fulltext) 조건
최종적으로 옵티마이저가 선택한 것은 첫 번째 조건인 const 타입이다. const 타입의 첫 번째 조건이 없으면 fulltext 조건인 세번째 조건을 택한다.
일반적으로 쿼리에 전문 검색 조건(MATCH ... AGAINST ...)를 사용하면 MySQL은 fulltext 접근 방식을 사용하는 경향이 있다. 하지만 전문 검색 인덱스를 이용하는 fulltext 보다 일반 인덱스를 이용하는 range 접근 방법이 더 빨리 처리되는 경우가 더 많다. 전문 검색 쿼리는 각 조건별로 성능을 확인해 보는 것이 좋다.
ref_or_null
ref 접근 방식과 같은데 NULL 비교가 추가된 형태다. 접근 방식의 이름 그대로 ref 방식 또는 NULL 비교(IS NULL) 접근 방식을 의미한다. 실무에서는 존재감이 없으므로 의미만 기억하라.
EXPLAIN
SELECT * FROM titles WHERE to_Date='1985-03-01' OR to_date IS NULL;
unique_subquery
WHERE 조건절에 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방식이다. unique_subquery의 의미 그대로 서브 쿼리에서 중복되지 않은 유니크한 값만 반환할 때 이 접근 방법을 사용한다.
EXPLAIN
SELECT * FROM departments WHERE dept_no IN (
SELECT dept_no FROM dept_emp WHERE emp_no=10001);
쿼리 문장의 iN 부분에서 subquery는 emp_no=10001인 레코드 중에서 부서 번호 중복이 없기 때문에 실행 계획 두번째 라인의 dept_emp 테이블의 접근방식은 unique_subquery로 표시된다.
index_subquery
IN 연산의 특성으로, IN (subquery) 또는 IN (상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 제거되어야 한다. 중복된 값을 인덱스를 이용해 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.
index_subquery와 unique_subquery 접근 방법의 차이를 정리해보자 .
unique_subquery
- IN (subquery) 형태의 조건에서 subquery의 반환 값에는 중복이 없으므로 별도의 중복 제거 작업이 필요하지 않음.
index_subquery
- IN (subquery) 형태의 조건에서 subquery 반환 값에 중복된 값이 있을수 있지만 인덱스를 이용해 중복을 제거할 수 있음.
사실 Index_subquery나 unique_subquery 모두 IN( ) 안에 있는 중복 값을 낮은 비용으로 제거한다.
EXPLAIN
SELECT * FROM departments WHERE dept_no IN (
SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003');
위의 서브 쿼리는 'd001' 부터 'd003' 까지 dept_no 값만 가져오면 되고, 이미 프라이머리 키는 dept_no 칼럼의 값 기준으로 정렬되어 있어서 중복된 dept_no를 제거하기 위한 정렬이 필요하지 않다.
range
인덱스 레인지 스캔 형태의 접근 방법이다. 이 방법은 범위로 검색하는 경우를 의미하며 "<, >, IS NULL, BETWEEN, IN, LIKE"등의 연산자로 인덱스를 검색할 때 사용된다. 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법인데, MySQL이 가지고 있는 접근 방법 중에서 상당히 우선순위가 낮다. 하지만 이 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 어느 정도의 성능은 보장된다고 볼 수 있다.
EXPLAIN
SELECT dept_no FROM dept_emp WHERE dept_no BETWEEN 'd001' AND 'd003';
인덱스 레인지 스캔이라고 하면 const, ref, range 세 가지 접근 방법을 묶어서 지칭하는 것이다. "인덱스를 효율적으로 사용한다" 또는 "범위 제한 조건으로 인덱스를 사용한다"는 표현은 모두 이 세가지 접근 방법을 의미하며 일반적으로 '인덱스 레인지 스캔' 또는 '레인지 스캔'으로 언급할 때가 많다.
index_merge
지금까지 설명된 방식과 달리 index_merge 방식은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만든 후 그 결과를 병합하는 처리 방식이다. 그다지 효율적으로 동작하지는 않는다. index_merge 방식의 특징은 다음과 같다.
- 여러 인덱스를 읽어야 하므로 range 방식보다 효율성이 떨어진다.
- AND와 OR 연산이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 않는다.
- 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
- index_merge 의 결과는 항상 2개 이상의 집합이 되기 때문에 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.
EXPLAIN
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
OR first_name = 'Smith';
BETWEEN 조건에서 11000은 employees 테이블의 프라이머리 키를 조회하고, first_name="Smith' 조건은 ix_firstname 인덱스를 이용해 조회한후 두 결과를 병합하는 형태로 실행계획이 만들어진다.
index
index 접근 방법은 많은 사람들이 자주 오해하는 접근 방법이다. index 라서 효율적으로 인덱스를 사용하는 구나 생각하지만 사실 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. range 접근 방식과 같이 효율적으로 인덱스의 필요한 부분만 읽는 것이 아니라는 점을 잊지 말자.
index 접근 방식은 풀 스캔 방식과 레코드 건수는 같다. 하지만 인덱스는 데이터 전체 파일보다는 크기가 작아 풀 테이블 스캔보다 효율적이고 빠르다. 또한 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 풀 테이블 스캔보다 훨씬 효율적으로 처리될 수 있다. index 접근 방법은 다음의 조건가운데 1+2 조건을 충족하거나 1+3조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.
1) range 나 const 또는 ref 와 같은 접근 방식으로 인덱스를 사용하지 못하는 경우
2) 인덱스에 포함된 칼럼으로만 처리할 수 있는 쿼리인 경우(즉, 데이터 파일을 읽지 않아도 되는 경우)
3) 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우(즉, 별도의 정렬 작업을 피할 수 있는 경우)
EXPLAIN
SELECT * FROM departments ORDER BY dept_name DESC LIMIT 10;
위의 쿼리는 WHERE 조건이 없어 range, const ref 접근 방식을 사용할 수 없다. 정렬하려는 칼럼은 인덱스(ux_department)가 있으므로 별도 정렬 처리를 피하려고 index 접근 방식이 사용된다.
위 예제의 실행계획은 테이블의 인덱스를 처음부터 끝까지 읽는 index 접근 방식이지만 LIMIT 조건이 있어 효율적인 쿼리이다. 하지만 LIMIT 조건이 없거나 가져와야 할 레코드 건수가 많아지면 상당히 느려질 수 있다.
ALL
풀 테이블 스캔을 의미하는 접근 방식이다. 테이블을 전부 읽어 불필요한 레코드를 제거(체크 조건이 존재할 때) 하고 반환한다. 접근 방법중 가장 비효율적인 방법이다.
InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어들이는 기능을 제공한다. InnoDB에서는 이 기능을 "리드 어헤드(Read Ahead)" 라고 하며, 한 번에 여러 페이지를 읽어서 처리할 수 있다.
일반적으로 index 와 ALL 접근 방법은 빠른 응답을 사용자에게 보내줘야하는 웹 서비스 등과 같은 OLTP 환경에 적합하지 않다.
끄읕.
