본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
쿼리 실행 절차 (263p)
1) 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
2) SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 통해 테이블을 읽을지 선택한다.
3) 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
첫 번째 단계를 SQL 파싱 이라고 하며, MySQL 서버의 "SQL 파서" 라는 모듈로 처리한다. SQL 문장이 문법적으로 잘못된 경우 이 단계에서 걸러진다. 또한 이 단계에서 "SQL 파스트리"가 만들어진다. MySQL 서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행한다.
두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서, 다음과 같은 내용을 처리한다.
- 불필요한 조건의 제거 및 복잡한 연산의 단순화
- 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
두 번째 단계는 "최적화 및 실행계획 수립" 단계이며 MySQL 서버의 "옵티마이저"에서 처리한다. 두 번째 단계가 완료되면 쿼리의 "실행 계획"이 만들어진다.
세번째 단계는 수립된 실행계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.
첫 번째 단계와 두번째 단계는 MySQL 엔진에서 처리하며, 세 번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.
옵티마이저 종류 (265p)
옵티마이저는 크게 2가지 종류로 나눠진다.
1) 비용 기반 최적화(Cost-Based Optimizer, CBO)
- 쿼리를 처리하기 위한 여러가지 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계정보를 이용해 실행 계획별 비용을 산출한다. 산출된 각 실행 방법별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행한다.
2) 규칙 기반 최적화(Rule-Based Optimizer, RBO)
- 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이다. 통계정보(레코드 건수, 컬럼 분포도)를 조사하지 않고, 실행계획이 수립되기 때문에 쿼리가 거의 항상 같은 실행 방법을 만들어낸다. 오래전부터 DBMS 에서 거의 지원되지 않거나 업데이트 되지 않은 상태로 남아 있는 것이 현실이다.
규칙기반 최적화는 인덱스의 통계정보가 거의 없고 상대적으로 느린 CPU 연산탓에 비용 계산 과정이 부담스러웠기 때문에 사용되던 최적화 방법이다. 현재는 거의 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있으며, MySQL 역시 마찬가지다.
통계 정보 (265p)
비용 기반 최적화에서 가장 중요한 것은 통계 정보이다. 통계 정보가 정확하지 않으면 엉뚱한 방향으로 쿼리가 실행될 수 있다. MySQL 에서 기본적으로 관리되는 통계 정보는 레코드 건수와 인덱스의 유티크한 값의 개수 정도가 전부다. MySQL의 통계 정보는 사용자가 알아채지 못하는 순간순간 자동으로 변경되기 때문에 상당히 동적인 편이다.
MyISAM과 InnoDB의 테이블과 인덱스 통계 정보는 다음과 같이 확인할 수 있다.
SHOW TABLE STATUS LIKE 'tb_test'\G
SHOW INDEX FROM tb_test;
레코드 건수가 많지 않으면 통계 정보가 부정확한 경우가 많아 ANALYZE 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때도 있다. ANALYZE 명령은 인덱스 키값의 분포도만 업데이트 하며, 전체 테이블의 건수는 테이블의 전체 페이지 수를 이용해 예측한다.
// 파티션을 사용하지 않는 테이블의 통계 정보 수집
ANALYZE TABLE tb_test;
// 파티션을 사용하는 테이블에서 특정 파티션의 통계 정보 수집
ANALYZE TABLE tb_test ANALYZE PARTITION p3;
ANALYZE를 실행하는 동안 MyISAM 테이블은 읽기는 가능하지만 쓰기는 안된다. InnoDB 테이블은 읽기와 쓰기 모두 불가능하므로 서비스 도중에 ANALYZE를 실행하지 않는 것이 좋다. (5.1.38 미만 버전) InnoDB 테이블은 인덱스 페이지 중에서 8개 정도만 랜덤하게 선택해서 분석하고 그 결과를 인덱스의 통계 정보로 갱신한다.
실행 계획 분석 (266p)
MySQL 실행 계획의 확인은 EXPLAIN 명령을 사용한다. EXPLAIN 명령만 사용하면 기본적인 쿼리 실행 계획만 보인다. EXPLAIN EXTENDED나 EXPLAIN PARTITIONS 명령을 이용하여 더 상세한 실행 계획을 확인할 수도 있다. 추가 옵션을 사용하는 경우 기본적인 실행 계획에 추가로 정보가 1개씩 더 표시된다.
EXPLAIN 명령은 EXPLAIN 키워드 뒤에 확인하고 싶은 SELECT 쿼리 문장을 적으면 된다.
EXPLAIN
SELECT e.emp_no, e.first_name, s.from_date, s.salary
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
LIMIT 10;
EXPLAIN 을 실행하면 쿼리 문장의 특성에 따라 표 형태로 된 1줄 이상의 결과가 표시된다. 표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블의 개수만큼 출력된다(임시 테이블까지 포함). 실행 순서는 위에서 아래로 순서대로 표시된다. 출력된 실행 계획에서 위쪽에 출력된 결과일수록(id 칼럼의 값이 작을수록) 쿼리의 바깥(Outer)부분이거나 먼저 접근한 테이블이고, 아래쪽의 결과일수록 쿼리 안쪽 부분 또는 나중에 접근한 테이블에 해당된다.
ID 컬럼 (268p)
SELECT 문장은 1개 이상의 하위(SUB) SELECT 문장을 포함할 수 있다.
SELECT ...
FROM (SELECT ... FROM tb_test1) tb1,
tb_test2 tb2
WHERE tb1.id = tb2.id;
위의 쿼리 문장의 SELECT는 다음과 같이 분리할 수 있다. SELECT로 구분한 것을 "단위 쿼리"라고 표현하겠다.
SELECT ... FROM tb_test1;
SLECCT ... FROM tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;
실행 계획에서 왼쪽에 표시되는 id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다. 이 예제는 최소 2개의 id 가 표시된다.
만약 하나의 SELECT 문장 안에 여러 테이블을 조인하게 되면 조인되는 테이블 개수만큼 실행 계획 레코드가 출력되지만 같은 ID가 부여된다.
EXPLAIN
SELECT e.emp_no, e.first_name, s.from_date, s.salary
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
LIMIT 10;
아래의 쿼리의 실행계획에서는 쿼리 문장이 3개의 단위 SELECT 로 구성되어 실행 계획의 각 레코드가 각기 다른 id를 지닌 것을 확인할 수 있다.
EXPLAIN
SELECT
( ( SELECT COUNT(*) FROM employees) + ( SELECT COUNT(*) FROM departments ) ) AS total_count;
select_type 컬럼 (269p)
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다. select_type 컬럼에 표시될수 있는 값은 다음과 같다.
SIMPLE
UNION이나 서브 쿼리를 사용하지 않는 단순 SELECT쿼리인 경우에 표시된다. 쿼리가 아무리 복잡해도 SIMPLE인 단위 쿼리는 단 하나만 존재한다. 일반적으로 제일 바깥 SELECT 쿼리의 select_type이 SIMPLE 로 표시된다.
PRIMARY
UNION이나 서브 쿼리가 포함된 SELECT 쿼리의 실행 계획에서 가장 바깥쪽 쿼리는 select_type이 PRIMARY로 표시된다. SIMPLE 과 마찬가지로 select_type 이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재하며, 쿼리 제일 바깥의 SELECT 단위 쿼리가 PRIMARY로 표시된다.
UNION
UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째이후 SELECT 쿼리의 select_type은 UNION으로 표시된다. UNION의 첫 번째 단위 SELECT는 select_type이 UNION이 아니라 UNION으로 결합된 전체 집합의 select_type이 표시된다.
EXPLAIN
SELECT * FROM (
( SELECT emp_no FROM employees e1 LIMIT 10)
UNION ALL
(SELECT emp_no FROM employees e2 LIMIT 10)
UNION ALL
(SELECT emp_no FROM employees e3 LIMIT 10)
) tb;
UNION이 되는 단위 SELECT 쿼리 3개 중 첫번째만 UNION이 아니고 나머지는 모두 UNION으로 표시된다. 세 개의 서브 쿼리로 조회된 결과를 UNION ALL로 결합해 임시 테이블을 만들어 사용하므로 UNION ALL의 첫 번째 쿼리는 DERIVED라는 select_type을 갖는다.
DEPENDENT UNION
쿼리에 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다. DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부의 영향에 받는 것을 의미한다. 내부 쿼리가 외부의 값을 참조해서 처리될때 DEPENDENT 키워드가 select_type에 표시된다.
EXPLAIN
SELECT
e.first_name,
( SELECT CONCAT('Salary change count : ', COUNT(*)) AS message
FROM salaries s WHERE s.emp_no=e.emp_no
UNION
SELECT CONCAT('Department change count : ', COUNT(*)) AS message
FROM dept_emp de WHERE de.emp_no=e.emp_no
) AS message
FROM employees e
WHERE e.emp_no=10001;
위 예제에서 UNION에 사용된 SELECT 쿼리에 아우터가 정의된 employees 테이블의 emp_no 컬럼이 사용되었기 때문에 DEPENDENT UNION이라 select_type에 표시된다.
서브 쿼리의 경우 외부 쿼리보다 먼저 실행되는 것이 일반적이며 빠르게 처리된다. 하지만 DEPENDENT 의 경우 외부 쿼리에 의존적이므로 절대 외부 쿼리보다 먼저 실행될 수 없다. 그래서 DEPENDENT 서브 쿼리는 비효율적인 경우가 많다.
UNION RESULT
UNION의 결과를 담아두는 테이블을 의미한다. UNION이나 UNION ALL 쿼리는 모두 UNION의 결과를 임시 테이블로 생성하게 되는데, 실행 계획상에서 이 임시 테이블을 가리키는 라인의 select_type이 UNION RESULT다. UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도로 id 값은 부여되지 않는다.
EXPLAIN
SELECT emp_no FROM salaries WHERE salary > 100000
UNION ALL
SELECT emp_no FROM dept_emp WHERE from_date > '2001-01-01';
UNION RESULT 라인의 table 컬럼은 "<union1, 2>"로 표시돼 있는데, 이것은 id 1,2 번 단위 쿼리의 조회 결과를 UNION했다는 의미이다.
SUBQUERY
일반적으로 서브 쿼리라고 하면 여러 가지를 통틀어 이야기할 때가 많은데, 여기서 SUBQUERY라고 하는 것은 FROM 절 이외에서 사용되는 서브 쿼리만을 의미한다.
EXPLAIN
SELECT
e.first_name,
(SELECT COUNT(*) FROM dept_emp de, dept_manager dm WHERE dm.dept_no=de.dept_no) AS cnt
FROM employees e
WHERE e.emp_no-10001
실행 계획에서 FROM 절에 사용된 서브 쿼리는 select_type이 DERIVED라고 표시되고 그 밖의 위치에서 사용된 서브 쿼리는 전부 SUBQUERY라고 표시된다.
서브 쿼리는 사용되는 위치에 따라 각각 다른 이름을 지니고 있다.
- 중첩된 쿼리(Nested Query)
SELECT 되는 칼럼에 사용된 서브 쿼리를 네스티드 쿼리라고 한다.
- 서브 쿼리(Sub Query)
WHERE 절에 사용된 경우에는 일반적으로 서브쿼리라고 한다.
- 파생 테이블(Derived)
FROM 절에 사용된 서브 쿼리를 MySQL에서는 파생 테이블 이라고 하며, 일반적으로 RDBMS 전체적으로 인라인 뷰(inline view) 또는 서브 셀렉트(Sub Select)라고 부르기도 한다.
또한 서브 쿼리가 반환하는 값의 특성에 따라 다음과 같이 구분하기도 한다.
- 스칼라 서브 쿼리(Scalar SubQuery)
하나의 값만 (칼럼이 단 하나인 레코드 1건만) 반환하는 쿼리
- 로우 서브 쿼리
칼럼의 개수에 관계없이 하나의 레코드만 반환하는 쿼리
DEPENDENT SUBQUERY
서브 쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우를 DEPENDENT SUBQUERY 라고 표현한다.
EXPLAIN
SELECT e.first_name,
(SELECT COUNT(*)
FROM dept_emp de, dept_manager dm
WHERE dm.dept_no=de.dept_no AND de.emp_no = e.emp_no) AS cnt
FROM employees e
WHERE e.emp_no = 10001;
안쪽(Inner)의 서브 쿼리 결과가 바깥쪽(Outer) SELECT 쿼리의 칼럼에 의존적이라서 DEPENDENT라는 키워드가 붙는다. 또한 DEPENDENT UNION 과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브 쿼리보다 처리 속도가 느릴 때가 많다.
DERIVED
서브 쿼리가 FROM 절에서 사용된 경우 select_type은 DERIVED이다. DERIVED는 단위 SELECT 쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다. DERIVED로 생성되는 임시 테이블을 파생 테이블이라고도 한다. FROM 절에 사용된 서브 쿼리는 제대로 최적화되지 못할 때가 대부분이다. 파생 테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다.
EXPLAIN
SELECT *
FROM
(SELECT de.emp_no FROM dept_emp de) tb,
employees e
WHERE e.emp_no=tb.emp_no;
사실 위의 쿼리는 FROM 절의 서브 퀴리를 제거하고 조인으로 처리할 수 있는 형태이다. 실행 계획에서 보면 알수 있듯이 FROM 절의 서브 쿼리를 임시 테이블로 만들어서 처리한다. MySQL 6.0 이상 버전부터 FROM 절의 서브 쿼리 최적화가 많이 개선될 것으로 알려졌다. 그 전까지는 FROM 절의 서브 쿼리는 상당히 신경 써서 개발하고 튜닝해야 한다. 가능하다면 DERIVED 형태의 실행 계획을 조인으로 해결할 수 있도록 바꾸는 것이 좋다.
UNCHACHEABLE SUBQUERY (276p)
하나의 쿼리 문장에서 서브 쿼리가 하나만 있더라도 실제 그 서브 쿼리가 한번만 실행되는 것은 아니다. 조건이 동일한 서브 쿼리의 결과를 재사용하기 위해 내부적인 캐시 공간에 담아둔다. 서브 쿼리 캐시는 쿼리 캐시나 파생 테이블(DERIVED) 과는 무관한 기능이다. SUBQUERY와 DEPENDENT SUBQUERY가 캐시를 사용하는 방법을 비교하자
- SUBQUERY는 바깥쪽(Outer)의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.
- DEPENDENT SUBQUERY는 의존하는 바깥쪽(Outer) 쿼리의 칼럼의 값 단위로 캐시해두고 사용한다.
select_type이 SUBQUERY인 경우 캐시를 사용하는 과정이다. 위 그림에서는 캐시가 처음 한번만 생성된다. 하지만 DEPENDENT SUBQUERY는 서브 쿼리 결과가 한 번만 캐시되지 않고 외부 쿼리의 값 단위로 캐시가 만들어진다.
서브 쿼리에 포함된 요소에 의해 캐시 자체가 불가능한 경우 UNCACHEABLE SUBQUERY로 표시되며 캐시를 사용하지 못하는 요소는 다음과 같다.
- 사용자 변수가 서브 쿼리에 사용된 경우
- NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브 쿼리에 사용된 경우
- UUID나 RAND 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우
다음은 사용자변수(@status)가 사용된 쿼리 예제이다. 이 경우 WHERE 절에 사용된 단위 쿼리의 select_type은 UNCACHEABLE SUBQUERY로 표시된다.
EXPLAIN
SELECT *
FROM employees e
WHERE e.emp_no = (
SELECT @status FROM dept_emp de WHERE de.dept_no = 'd005';
);
다음 포스팅에서 계속.
