개발자 신종민

# MySQL

# 05. 인덱스

# 5.2 인덱스란?

칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-value pair)으로 인덱스를 만들어 두는 것. 가장 중요한 것은 정렬이다. 최대한 빠르게 찾을 수 있게 DBMS의 인덱스도 칼럼의 값을 주어진 순서대로 미리 정렬해서 보관한다. 자료구조를 예를 들자면 sortedList는 DBMS의 인덱스와 같은 자료구조이며, ArrayList는 데이터 파일과 같은 자료구조를 사용한다. sortedList는 저장되는 값을 항상 정렬된 상태로 유지하는 자료구조. ArrayList는 값을 저장되는 순서대로 그대로 유지하는 자료구조.

sortedList 장단점을 통해 인덱스 알아보기 sortedList 자료구조는 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬되어 있어 값을 빨리 찾아올 수 있다. 결론적으로 DBMS에서는 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. 여기서 알 수 있듯이 테이블의 인덱스를 하나 더 추가할지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하는지 여부에 따라 결정되어야함. select 쿼리문에 where 조건절에 사용되는 컬럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스 크기가 비대해져 오히려 역효과.

인덱스를 역할별로 구분하면 프라이머리 키, 보조 키로 구분

  • 프라이머리 키는 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스를 의미한다.이 칼럼은 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 이를 식별자라고도 부른다. 프라이머리 키는 NULL 값을 허용하지 않으며 중복을 허용하지 않는 것이 특징
  • 프라이머리 키를 제외한 나머지 모든 인덱스는 보조 인덱스(secondary index)로 분류한다. 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 PK를 대체할 수 있다고도 해서 대체키라고도 한다. 별도 분류 혹은 보조 인덱스로 분류하기도 한다.

# 5.3 B-Tree 인덱스

B-Tree는 칼럼의 원래 값을 변경시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지하고 있다. 일반적인 용도에 가장 적합한 알고리즘.

# 5.3.1 구조 및 특성

B-Tree 구조의 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어 있는 형태. 트리 구조 가장 하위에 있는 노드를 리프 노드라하고 루트도 리프도 아닌 중간 노드를 브랜치 노드라고 한다.
루트-브랜치-리프-데이터 파일 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터에 레코드를 찾아가기 위한 조소 값을 가지고 있다. 인덱스의 키값은 모두 정렬되어 있지만 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서대로 저장돼 있다.

# 06. 실행 계획

DBMS에서 쿼리를 최적으로 실행하기 위해 각 테이블 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다. DBMS에서 이러한 기능을 옵티마이저가 담당한다.

# 6.1 개요

# 6.1.1 쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 3가지로 나눌 수 있다.

  1. 사용자로부터 요청된 SQL문장을 잘게 쪼개서 MySQL 서버가 이해할 수 잇는 수준으로 분리한다.
  2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
  3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

첫 번째 단계를 "SQL 파싱"이라고 하며 MySQL 서버의 "SQL 파서"라는 모듈로 처리한다. 만약 SQL 문장이 문법적으로 잘못되었다면 이 단계에서 걸러진다. 또한 이 단계에서 "SQL 파스트리"가 만들어지고 MySQL 서버는 SQL문장 그 자체가 아닌 SQL파스트리를 이용해 쿼리를 실행한다.

두번째 단계는 첫 번째 단계에서 만들어진 SQL파스트리를 참조하면서, 다음과 같은 내용을 처리.

  • 불필요한 조건의 제거 및 복잡한 연산의 단순화
  • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지
  • 각 테이블에 사용된 조건과 인덱스를 통계 정보를 이용해 사용할 인덱스 결정
  • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정

즉, 최적화 및 실행 계획 수립의 단계. 옵티마이저가 처리하고 이 단계가 끝나면 실행계획이 수립된다. 1,2단계에서는 거의 MySql 엔진에서 처리. 3번째 단계는 MySql엔진과 스토리지엔진이 동시에 참여하여 처리.

# 6.1.2 옵티마이저의 종류

옵티마이저는 현재 대부분이 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법과 예전 오라이클에서 많이 사용했던 규칙 기반 최적화(Rule-based optimizer, RBO)으로 크게 나눠 볼 수 있다.

  • 규칙 기반 최적화는 기본적으로 대상 테이블의 레코드 건수나 선택도등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립
  • 비용 기반 최적화는 쿼리를 처리하기 위한 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출한다. 이렇게 실행된 방법별로 최소 비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행한다.

# 6.1.3 통계 정보

비용 기반 최적화에서 가장 중요한 것은 통계 정보다. 통계 정보가 정확하지 않다면 전현 엉뚱한 방향으로 쿼리를 실행해 버릴 수 있기 때문이다. 그치만 통계 정보는 그리 다양하진 않은편. 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부. 레코드 건수가 많지 않으면 통계 정보가 상당히 부정확한 경우가 많음으로 "ANALYZE" 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때도 있다. 이런 현상은 주로 레코드 건수가 얼마되지 않는 개발용 Mysql에서 자주 발생한다.

# 6.2 실행 계획 분석

MySQL에서 쿼리의 실행 계획을 확인하려면 EXPLAIN 명령을 사용하면 된다. EXPLAIN을 사용하면 쿼리 문장의 특성에 따라 표 형태로 된 1줄 이상의 결과가 표시된다. 출력된 실행계획에서 위쪽의 출력된 결과일수록 쿼리의 바깥(outer)부분이거나 먼저 접근한 테이블이고, 아래쪽 결과일수록 나중에 접근한 테이블에 해당된다. 쿼리 자쳬가 상당히 복잡하고 무거운 쿼리인 경우에는 실행계획의 조회 또한 느려질 가능성이 있다. 그리고 update나 insert, delete 문장의 실행 계획을 확인할 방법이 없다. update나 insert, delete 문장의 실행 계획을 확인하려면 where 조건절만 같은 select 문장을 만들어 대략적으로 확인해볼 수 있다.

# 6.2.1 id 칼럼

하나의 select 문에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id가 부여된다. 반대로 쿼리 문장이 3개의 단위 select 쿼리로 구성되어 있으면 실행 계획의 각 레코드가 각기 다른 id를 지닌다.

# 6.2.2 select_type 칼럼

각 단위 select 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.

  • SIMPLE: 유니온이나 서브 쿼리를 사용하지 않는 단순한 select 쿼리인 경우. 일반적으로 가장 바깥 select 쿼리의 타입이다.

  • PRIMARY: UNION이나 서브 쿼리가 포함된 select 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리의 타입은 PRIMARY.

  • UNION: 유니온으로 결합하는 단위 select 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 select 쿼리의 타입은 UNION.

  • DEPENDENT: 내부 쿼리가 외부의 값을 참조해서 처리될 때 타입. DEPENDENT 키워드를 포함하는 서브쿼리는 외부쿼리의 의존적이므로 절대 외부 쿼리보다 먼저 실행될 수 없다. 그래서 DEPENDENT 키워드가 포함된 서버 쿼리는 비효율적인 경우가 많다.

  • UNION RESULT: UNION 결과를 담아두는 테이블을 의미한다.

  • SUBQUERY: 여기서의 서브쿼리는 FROM 절 이외에서 사용되는 서브 쿼리만을 의미한다. from절에서 사용된 서브쿼리는 DERIVED라고 표시되고 그 밖에 위치에 사용된 서브쿼리는 전부 SUBQUERY라고 표시된다. "파생 테이블"이라는 단어는 DERIVED와 같은 의미로 이해하면 된다.

  • DEPENDENT SUBQUERY

  • DERIVED: 서브쿼리가 FROM절에 사용된 경우 MySQL은 항상 DERIVED인 실행 계획을 만든다. MySQL은 FROM절에 사용된 서브 쿼리를 제대로 최적화하지 못할 때가 대부분이다. 파생 테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많음.

    다른 방법이 없어서 서브쿼리를 사용하는 것은 피할 수 없지만. 조인으로 해결할 수 있는 경우라면 서브쿼리보단 조인을 강력히 추천. 서브 쿼리를 조인으로 풀어서 고쳐 쓰는 습관을 들이기.

  • UNCACHEABLE SUBQUERY: 캐시가 불가능한 서브 쿼리

  • UNCACHEABLE UNION

# 6.2.4 type 칼럼

# 6.2.10 Extra

const row not found
쿼리 실행 계획에서 const 접근 방식으로 테이블을 읽었지만 실제 해당 데이터가 1건도 존재하지 않으면 이 내용 표시된다.

Distinct
쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인했으며, 꼭 필요한 레코드만 읽었다는 것을 표현하고 있다.

Full scan on Null key
"col1 IN (SELECT col2 FROM ...)"과 같은 조건을 가진 쿼리에서 자주 발생. col1값이 Null이 된다면 결과적으로 "NULL IN (SELECT col2 FROM ...)"와 같이 바뀐다. SQL 표준은 Null값을 "알수없는 값"으로 정의하고 있으며, Null에 대한 연산의 규칙까지 정의하고 있다.

  • 서브 쿼리가 단 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
  • 서브 쿼리가 단 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE MySQL이 쿼리를 실행하는 중 col1이 Null을 만난다면 예비책으로 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 키워드이다.

IN이나 NOT IN 연산자 왼쪽 값이 NULL인 레코드가 있고 서브쿼리에 개별적으로 WHERE 조건이 지정돼 있다면 상당한 성능 문제가 발생할 수도 있다.

Impossible HAVING
쿼리에 사용된 HAVING절에 조건을 만족하는 레코드가 없을 때. 애플리케이션 쿼리 중에서 실행 계획에 extra칼럼에 위 메시지가 뜬다면 쿼리가 제대로 작성되지 못한 경우가 대부분이므로 쿼리의 내용을 다시 점검하는 것이 좋다.

Impossible WHERE
"Impossible HAVING"과 비슷하며 WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우 표시

Impossible WHERE noticed after reading const tables
쿼리를 실행해보기 전엔 WHERE 조건이 항상 false인지는 모르지만 실행해보니까 알게된 경우

No matching min/max row
쿼리의 where 조건절을 만족하는 레코드가 한 건도 없는 경우 일반적으로 "Impossible WHERE"문장이 Extra 칼럼에 표시된다. 만약 MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 단 한 건도 없을 때 "No matching min/max row"가 출력. 그리고 MIN()이나 MAX()의 결과로 NULL 반환

Extra 칼럼에 출력되는 내용 중에서 "no matching ..." 이나 "Impossible WHERE ..."등의 메시지는 잘못 생각하면 쿼리 자체가 오류인 것처럼 오해라기 쉽다. 하지만 단지 쿼리 실행을 산출하기 위한 기초 자료가 없을을 표현하는 것뿐이다. Extra 칼럼에 이러한 메시지가 표시된다고 해서 실제 쿼리 오류가 발생하는 것은 아니다.

No matching min/max row in const table
조인에 사용된 테이블에서 const 방식으로 접근할 때, 일치하는 레코드가 없다면 "no matching row in const table"이라는 메시지를 표시한다.

No tables used
FROM 절이 없는 쿼리 문장이나 "FROM DUAL" 형태의 쿼리 실행 계획에서는 Extra 칼럼에 출력된다.

Not exists
프로그램을 개발하다보면 A테이블에서는 존재하지만 B테이블에서는 없는 값을 조회해야 하는 쿼리가 자주 사용된다. 주로 NOT IN(subquery)형태나 NOT EXIST 연산자를 주로 사용한다. 이러한 형태의 조인을 안티-조인(Anti-join)이라고 한다. 똑같은 처리를 아우터 조인(LEFT OUTER JOIN)을 이용해도 구현할 수 있다. 일반적으로 안티-조인으로 처리해야하지만 레코드 건수가 많을 때는 NOT IN(subquery)이나 NOT EXISTS 연산자보다 아우터 조인이 더 빠른 성능을 낼 수 있다.

EXPLAIN
SELECT *
FROM dept_emp de
	LEFT JOIN department d ON de.dept_no=d.dept_no
WHERE d.dept_no IS NULLL;

이렇게 아우터 조인을 이용해 안티-조인을 수행하는 쿼리에서는 Extra 칼럼에 Not exists 메시지가 표시된다. Not exists 메시지는 이 쿼리를 NOT EXISTS 형태의 쿼리로 변환해서 처리했다는 것이 아니라. MySQL이 내부적으로 어떤 최적화를 했는데 그 최적화 이름이 "Not exists"인 것이다.

Range checked for each record
두 테이블 조인하는 경우 WHERE절 변수가 두개가 사용되어 매 레코드마다 인덱스 레인지 스캔을 체크한다.

Select tables optimized away
MIN()또는 MAX()만 SELECT절에 사용되거나 또는 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 적절한 인덱스를 사용할 수 없을 때 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면 위 메시지가 출력.

unique row not found
두 개의 테이블이 각각 유니크(프라이머리키 포함) 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 표시된다.

# 정렬의 처리 방식

# 인덱스를 이용한 정렬

ORDER BY 절을 넣지 않아도 자동으로 정렬된다고 해서 ORDER BY 절 자체를 쿼리에서 완전히 빼버리고 작성하기도 한다. ORDER BY 절을 포함하면 MySQL서버가 별도로 정렬 작업을 한 번 더 할까 걱정스러워서다. 하지만 MySQL 서버는 정렬을 인덱스로 처리할 수 있다면 부가적으로 불필요한 정렬작업을 수행하지 않는다.

  • 인덱스로 정렬이 처리될 때는 ORDER BY가 쿼리에 명시된다고 해서 작업량이 더 늘지 않는다.
  • 어떤 이유로 쿼리 실행 계획이 조금 변경되어 ORDER BY가 명시되지 않는 쿼리는 결과가 기대했던 순서대로 가져오지 못해서 애플리케이션 버그로 연결될 수 있다.
  • 그래서 ORDER BY 절을 명시해두면 성능상의 손해가 없음은 물론 이런 예외 상황에서도 버그로 연결되지 않을 것 이다.

# 6.3.6 테이블 조인

inner join은 어느 테이블을 먼저 읽어도 결과가 달라지지 않으므로 MySQL 옵티마이저가 조인의 순서를 조절해서 다양한 방법으로 최적화를 수행할 수 있다. 하지만 outer join은 반드시 outer가 되는 테이블을 먼저 읽어야하기 때문에 조인의 순서를 옵티마이저가 선택할 수 없다.

# JOIN (INNER JOIN)

일반적인 조인을 inner join이라 한다. MySQL에서 조인은 네스티드-루프 방식만 지원한다. 일반적으로 프로그램을 작성할 때 두개의 for나 while과 같은 반복 루프 문장을 실행하는 형태로 조인 처리되는 것을 의미한다. 짝을 찾지 못하는 레코드는 조인 결과에 포함되지 않는다.

# OUTER JOIN

inner join에서는 일치하는 레코드를 찾지 못했을 때는 table1의 결과를 모두 버리지만 outer join에서는 table1의 결과를 버리지 않고 그대로 결과에 포함한다.

left outer join

  • 보통 left outer join으로 통일해서 사용하는 것이 일반적.
  • outer join시 주의 점은 연결되는 테이블이 있는 쿼리에서 모든 조건을 ON절에 명시하는 습관 필요. 잘못하면 옵티마이저가 inner조인으로 변형시켜서 처리할 수도 있기 때문에.

# INNER JOIN과 OUTER JOIN의 선택

INNER JOIN과 OUTER JOIN의 성능 비교는 실제 가져와야 하는 레코드가 같다면 쿼리 성능은 거의 차이가 발생하지 않는다. 즉, INNER JOIN과 OUTER JOIN은 성능을 고려한 선택이 아닌 업무 요건에 따라 선택하는 게 바람직. 레코드가 결과에 포함되지 않을까 걱정이라면, 테이블 구조와 데이터의 특성을 분석해 inner join을 사용해야 할 지 outer join을 사용해야 할 지 결정.

# 6.4 실행 계획 분석 시 주의사항

# 6.4.1 select_type 칼럼의 주의 대상

  • DRIVED DRIVED은 FROM 절에 사용된 서브 쿼리로 발생한 임시 테이블을 의미한다. 임시 테이블은 메모리 혹은 디스크에 저장될 수 있다. 일반적으로 메모리 저장은 성능에 영향을 크게 미치지 않지만 데이터의 크기가 커서 임시테이블을 디스크에 저장할 경우 성능이 저하된다.

  • UNCACHEABLE SUBQUERY 쿼리의 FROM 절 이외의 부분에서 사용하는 서브 쿼리는 가능하면 mysql 옵티마이저가 최대한 캐시되어 재사용 될 수 있게 유도한다. 하지만 사용자 변수나 일부 함수가 사용된 경우 이러한 캐시 기능을 사용할 수 없게 만든다. 이런 실행 계획이 사용된다면 혹시 사용자 변수를 제거하거나 다른 함수로 대체해서 사용가능한지 검토해보는 것이 좋다.

  • DEPENDENT SUBQUERY 쿼리의 FROM 절 이외의 부분에서 사용하는 서브 쿼리가 자체적으로 실행되지 못하고, 외부 쿼리에서 값을 전달받아 실행되는 경우 표시된다. 이는 서브쿼리가 외부 쿼리의 결과 값에 의존적이기 때문에 전체 쿼리의 성능을 느리게 만든다. 서브 쿼리가 불필요하게 외부 쿼리의 값을 전달받고 있는지 검토하여, 가능하면 외부 쿼리의 의존도를 제거하는 게 좋다.

# 6.4.2 Type 칼럼의 주의 대상

  • ALL, index index는 인덱스의 풀 스캔을 의미하며, ALL은 풀 테이블 스캔을 의미한다. 둘 다 대상의 차이만 있을 뿐이지 전체 레코드를 대상으로 하는 작업 방식이라 빠르게 결과를 가져오기 어렵다. 새로운 인덱스를 추가하거나 쿼리의 요건을 변경해서 이러한 접근 방법을 제거하는 것이 좋다.

# 6.4.3 key 칼럼의 주의 대상

  • 쿼리가 인덱스를 사용하지 못할 때 실행 계획의 key 칼럼에 아무 값도 표시되지 않는다. 쿼리가 인덱스를 사용할 수 있게 인덱스를 추가하거나, where 조건을 변경하는 것이 좋다.

# 6.4.4 Rows 컬럼의 주의 대상

  • 쿼리가 실제 가져오는 레코드 수보다 훨씬 더 큰 값이 Rows 칼럼에 표시되는 경우 쿼리가 인덱스를 정상적으로 사용하고 있는지, 그리고 그 인덱스가 충분히 작업 범위를 좁혀 줄 수 있는 칼럼으로 구성되었는지 검토해보는 것이 좋다. 인덱스가 효율적이지 않다면 충분히 식별성을 가지고 있는 칼럼을 선정해 인덱스를 다시 생성하거나 쿼리의 요건을 변경해보는 것이 좋다.
  • Rows 칼럼의 수치를 판단할 때 주의해야 할 점은 LIMIT가 포함된 쿼리라 하더라도 LIMIT의 제한은 Rows 칼럼의 고려 대상에서 제외된다는 것이다. 즉, limit 1로 1건만 select하더라도 rows칼럼에서는 훨씬 큰 수치가 표현될 수 있으며, 성능상 아무런 문제가 없고 최적화된 쿼리일 수 있다.

# 6.4.5 Extra 칼럼의 주의 대상

# 쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우

  • full sacn on null key
  • impossible having
  • impossible where
  • impossible where noticed after reading const tables
  • no matching min/max row
  • no matching row in const table
  • unique row not found

위와 같은 코멘트가 Extra 칼럼에 표시된다면 우선 쿼리가 요건을 제대로 반영해서 작성됐거나 버그가 생길 가능성은 없는지 확인해야 한다. 또는 개발용 데이터베이스와 테스트용 레코드가 제대로 준비되었는지 확인. 쿼리가 업무적인 요건을 충족한다면 무시해도 된다.

# 쿼리의 실행 계획이 좋지 않은 경우

  • range checked for each record
  • using filesort
  • using join buffer
  • using temporary
  • using where

위와 같은 코멘트가 Extra 칼럼에 표시된다면 먼저 쿼리를 더 최적화할 수 있는 검토. using where은 대부분의 쿼리에서 표시되는 경향이 있기에 지나치기 쉬우나 만약 실행 계획이 Rows 칼럼의 값이 실제 select 되는 레코드 건수보다 상당히 높은 경우에는 반드시 보완하여 차이를 줄이는 것이 중요하다.

# 쿼리 실행 계획이 좋은 경우

  • Distint
  • Using index
  • Using index for group-by

여기에 표시된 항목은 최적화되어 처리되고 있음을 알려주는 지표정도로 생각. 특히 두번째의 Using index는 쿼리가 커버링 인덱스로 처리되고 있음을 알려주는 것인데, MySQL에서 제공할 수 있는 최고의 성능을 보여줄 것이다. 만약 쿼리를 아무리 최적화해도 성능 요건에 미치지 못한다면 인덱스만으로 쿼리가 처리(커버링 인덱스)되는 형태로 유도해보는 것이 좋다.

← Redis →