티스토리 뷰
MySQL은 하드웨어와 최적화, 인덱스를 활용하여 필요한 데이터에 접근할 때 성능을 발휘한다.
최적화는 MySQL 측면에서 하드웨어를 효율적으로 활용하게 해주는 다양한 기술과 알고리즘, 데이터 구조를 의미한다.
즉, 최적화는 하드웨어 성능에 초점을 맞추는 것이며 하드웨어보다 최적화가 MySQL 성능에 더 많은 영향력을 미치게된다.
인덱스가 없는 MySQL 성능은 작은 규모의 데이터만 들어올릴 수 있는 성능으로 제한되지만,
균형 잡힌 지렛대에 인덱스를 추가하면 MySQL은 대량의 데이터도 월등한 성능으로 처리한다.
성능 향상과 관련 없는 딴 짓
MySQL성능을 향상 시키기 위한 방법을 찾을 때 두 가지 방법이 등장하는데 고사양의 하드웨어를 구매하는 것과 MySQL 튜닝이다.
더 좋고 빠른 하드웨어
MySQL 성능을 향상 시키기 위해 스케일 업을 먼저 고려해서는 안된다. 물론 500GB 데이터에 1GB 메모리를 사용하는 경우는 잘못되었고, 애플리케이션 실행에 안정성을 담보하기 위한 임시 방편이라면 시행해도 된다. 하지만 그런 상황이 아니라면 최후의 수단으로 두는 것이 옳다.
MySQL 튜닝
MySQL 튜닝은 성능 향상을 위한 방법으로 아래 세 가지로 나눠진다.
튜닝
MySQL을 성능 향상 시키기 위해서 시스템 변수를 조정하는 행위로, 구체적인 목표와 기준이 있는 실험실 수준의 작업이다. 튜닝은 R&D 이기 때문에, 일반적으로 적용할 수 있는 결과는 아니다.
구성
구성은 시스템 변수를 하드웨어와 환경에 적합한 값으로 설정하는 행위다. MySQl 인스턴스가 프로비저닝 되거나 하드웨어가 변경될 때 수행하는 것으로 MySQL이 일반적으로 실행되는 방식에 영향을 미친다.
최적화
워크로드(쿼리, 데이터, 접근 패턴 조합)를 줄이거나 효율성을 높여 MySQL 성능을 향상 시키는 행위다. 애플리케이션은 사용량이 증가하는 경향이 있으므로 보통 성능 향상을 선택한다. 그래서 목표로 기존 하드웨어를 갖고 더 빠른 응답 시간과 더 많은 용량을 제공하여 애플리케이션 성능에 긍정적인 영향을 가져오는 것으로 둔다.
참고로 MySQL 에서 튜닝에 해당하는 작업은 굉장히 어려운 실험적인 도전 과제다. 더불어, MySQL 8.0에서는 Innodb_dedicated_server 매개 변수를 활성화하여 자동으로 서버를 구성한다. 그렇기에 포스트에서는 최적화에 대해서 더 깊이 알아보고자 한다.
MySQL 인덱스
인덱스는 성능에서 핵심이며 쿼리와 인덱스를 변경하는 방법으로 다양한 성능 문제가 해결된다.
다음 설명에서 사용되는 예시는 InnoDB 테이블 표준 인덱스(PK나 유니크 인덱스)만 적용된다.
InnoDB 테이블은 인덱스다.
elem 테이블을 만들도록 해보자.
CREATE TABLE `elem` (
`id` int unsigned NOT NULL,
`name` char(2) NOT NULL,
`height` char(2) NOT NULL,
PRIMARY KEY (`id`), -- 프라이머리 키를 가진 클러스터 인덱스
KEY `idx_name` (`name`) -- 'name'으로 구성된 비고유 넌클러스터 인덱스
) ENGINE=InnoDB;
위 테이블을 InnoDB 테이블로서 name을 통해 노드를 검색 시, 실제 구조를 살펴보면 아래와 같다.
논클러스터 인덱스로 자식노드를 따라가면 리프노드에서 프라이머리키 값을 얻게 된다.
이후 해당 프라이머리 키를 통해 다시 인덱스를 타서 행 정보를 가져오게 된다.
테이블 접근 방법
인덱스를 사용하여 행을 조회하는 방법은 세가지 테이블 접근 중 하나다.
MySQL에서 테이블은 곧 인덱스이므로 인덱스 조회가 가장 적합하면서 일반적인 테이블 접근 방법이다. 그러나 때로는 쿼리에 따라 인덱스 조회가 불가능할 때도 있으며, 이때는 인덱스 스캔 혹은 테이블 스캔과 같은 방법이 유일하다.
인덱스 스캔과 테이블 스캔은 피애햐하는데 EXPLAIN 쿼리 실행계획에서 어떻게 테이블 접근 방법을 분석해야하는지 알아볼 예정이다.
인덱스 조회
인덱스 조회에서 인덱스의 정렬된 구조(B-트리)와 접근 알고리즘을 활용하여 특정 행이나 행 범위를 찾는다. 이는 가장 빠르고 효과적인 접근 방법이다.
인덱스 스캔
모든 행을 읽고 일치하지 않는 행을 필터링한다. MySQL은 PK로 모든 행을 읽기 전에 논클러스터 인덱스로 행 읽기를 시도하는데 이를 인덱스 스캔이라고 한다.
인덱스 스캔에는 두 가지 유형이있다.
풀 인덱스 스캔
첫 번째는 풀 인덱스 스캔으로 인덱스 순서대로 모든 행을 읽는다. 모든 행을 읽는 것은 성능적으로 불리하지만 인덱스 순서가 ORDER BY와 일치할 때 정렬를 피할 수 있다.
SELECT * FROM elem FORCE INDEX (name) ORDER BY name 에 대한 풀 인덱스 스캔은 어떻게 될까?
세컨더리 인덱스의 첫 번째 값인 Amer을 읽고
PK 에서 해당 행을 찾는다.
이후 세컨더리 인덱스의 두 번째 값인 Eble을 읽고
PK에서 해당 행을 찾는다.
….
인덱스 전용 스캔
두 번째는 인덱스 전용 스캔이다. MySQL 는 인덱스에서 열값을 읽는다. 이를 위해서 커버링 인덱스가 필요하며 이후에 더 자세히 다룰 예정이다. 전체 행을 읽기 위해 PK 조회를 해야하는 상황이 아니므로 풀 인덱스 스캔 보다는 빠르게 된다.
세컨더리 인덱스를 순서대로 스캔하는 것은 순차 읽기 일 수 있지만, PK 조회는 무작위 읽기다. 인덱스 순서대로 행에 접근한다고 해서 순차 읽기가 보장되는 것은 아니며 무작위 읽기가 발생할 가능성이 높다. 그렇기 때문에.. 인덱스 스캔을 피해라..
추가로 커버링 인덱스란
인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어오는 과정이 필요 없는 인덱스다. 디스크를 읽지 않으므로 랜덤 읽기가 줄어들어 성능이 빨라진다.
풀 테이블 스캔
풀 테이블 스캔은 PK 순서대로 모든 행을 읽는다. MySQL이 인덱스 조회나 인덱스 스캔을 수행할 수 없을 떄 테이블 스캔이 유일한 옵션이다. WHERE, GROUP BY, ODER BY 절이 있는 모든 쿼리는 인덱스 스캔일지라도 인덱스를 사용할 수 있다. 그렇기에 테이블 스캔을 당연하게 여기면 안된다. 일반적으로 성능에 굉장히 나쁘기 때문에 조심해야 한다. 더불어 MySQL은 인덱스 조회를 할 수 있을 때에 테이블 스캔을 잘못 선택하기도 하기에 잘 확인해야 한다.
맨 왼쪽 접두사 요구사항
인덱스를 사용하려면 쿼리는 인덱스의 맨 왼쪽 접두사, 즉 맨 왼쪽 인덱스 열로 시작하는 하나 이상의 인덱스 열을 반드시 사용해야한다. 기본적인 인덱스 구조는 인덱스 열 순서에 따라 정렬되므로 맨 왼쪽 접두사가 필요하며 그 순서대로만 탐색할 수 있다.
예시로 pk가 id, 세컨더리 인덱스를 a,b로 설정하고 (a,b) 와 (b,a) WHERE 문 을 가진 쿼리는 서로 다른 인덱스로 받아드려 순서가 달라, 성능 측면에서 인덱스가 동일하다는 의미가 아니다.
추가로, (a,b,id) 를 가진 WHERE문의 쿼리를 위해 해당 인덱스를 추가하는 것은 좋지 않다.
MySQL 에서 문자상으로 추가하지 않더라도 PK는 시컨더리 인덱스에 추가된다.그렇기에 pk값을 마지막에 포함한 세컨더리 인덱스를 생성하는 것은 인덱스의 크기가 커질뿐더러, 중복된다.
EXPLAIN 쿼리 실행계획
EXPLAIN 명령은 MySQL 이 쿼리를 실행하는 방법을 설명하는 쿼리 실행 계획 또는 EXPLAIN 계획을 보여준다. 해당 쿼리 실행 계획에는 테이블 조인 순서, 테이블 접근 방법, 인덱스 사용 등 중요한 정보가 포함되어 있다.
EXPLAIN 출력은 많은 정보가 있고 쿼리에 전적으로 종속되어있다.
먼저 EXPLAIN 명령어 사용 시 출력되는 필드의 의미를 검토해보자.
table 필드
테이블 이름, 참조된 서브 쿼리다. 테이블은 쿼리의 순서가 아니라 MySQL이 결정한 조인 순서로 나열된다.
type 필드
테이블 접근 방법이나, 인덱스 조회의 접근 유형이다. index는 인덱스 스캔 ALL은 풀 테테이블 스캔, const, ref, range등 다른 값은 인덱스 조회의 접근 유형이다.
possible_keys 필드
쿼리가 맨 왼쪽 접두사를 사용하므로 MySQL이 사용할 수 있는 인덱스를 나열한다. 이 필드에 나열되지 않으면 맨 왼쪽 접두사에 대한 요구사항이 충족되지 않음을 의미한다.
key 필드
MySQl이 사용할 인덱스 이름이거나, 인덱스를 사용할 수 없을 때 NULL 이다.
ref 필드
인덱스에서 행을 조회하는데 사용되는 값의 소스를 나열한다. 단일 테이블 쿼리나 조인의 첫 번째 테이블에서 ref는 종종 하나 이상의 인덱스 열에 대한 사수 조건을 나타내는 const다. 상수 조건은 리터럴과 같다(= ≤≥) (예시로 a = ‘Au’는 상수 조건이다.)
여러 테이블을 조인하는 쿼리에서 ref는 조인 순서상 이전 테이블의 열 참조다.
rows 필드
MySQL이 일치하는 행을 찾기 위해 조회할 예상 행의 수다. (참고로 근사값이기에 같지는 않을 것이다)
Extra 필드
쿼리 실행 계획에 대한 부가 정보를 제공한다. (MySQL이 적용할 수 있는 쿼리 최적화를 나타낸다.)
인덱스 사용가능한 개별 쿼리 사례
이후 5개 하위 절에서 MySQL이 인덱스를 사용할 수 있는 개별 쿼리 사례들을 설명하면서 인덱스 사용에 대해 다뤄보고자 한다.
- WHERE
- GROUP BY
- ORDER BY
- COVERING INDEX
- TABLE JOIN
먼저 테이블을 생성한다.
CREATE TABLE `elem` (
`id` int unsigned NOT NULL,
`a` char(2) NOT NULL,
`b` char(2) NOT NULL,
`c` char(2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_a_b` (`a`, `b`)
) ENGINE=InnoDB;
생성하고 임의의 데이터를 삽입한다.
INSERT INTO `elem` (`id`, `a`, `b`, `c`) VALUES
-> (1, 'A1', 'B1', 'C1'),
-> (2, 'A2', 'B2', 'C2'),
-> (3, 'A3', 'B3', 'C3'),
-> (4, 'A4', 'B4', 'C4');
+----+----+----+----+
| id | a | b | c |
+----+----+----+----+
| 1 | A1 | B1 | C1 |
| 2 | A2 | B2 | C2 |
| 3 | A3 | B3 | C3 |
| 4 | A4 | B4 | C4 |
+----+----+----+----+
WHERE
MySQl은 인덱스를 사용하여 WHERE 절의 테이블 조건과 일치하는 행을 찾을 수 있다.
인데스 사용의 경우 주로 테이블 조건, 인덱스, 맨 왼쪽 접두사에 대한 요구사항 같은 여러 요소에 따라 달라지기 때문에 MySQL이 인덱스를 사용할 수 도 있고 사용하지 않을 수도 있다고 하겠다.
열과 해당 열값으로 이루어지며 이 조건과 일치하는 행을 찾거나, WHERE 절에서 테이블 조건은 술어(*predicate)라고도 한다.
PRIMARY KEY(id)
WHERE id = 1
프라이머리 키 조회를 위한 EXPLAIN 계획
EXPLAIN SELECT * FROM elem WHERE id = 1\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: const -> ALL(테이블 스캔) index(인덱스 스캔)이 아닌 PK 조회
possible_keys: PRIMARY -> 세컨더리 키 사용하지 않아 나열되지 않음
key: PRIMARY -> 인덱스 조회로 PK 사용
key_len: 4
ref: const -> PK 혹은 uniue second index 의 모든 인덱스 열에 상수 조건이 있을 때만 발생
rows: 1
filtered: 100.00
Extra: NULL -> 상수 행은 오직 하나의 행과 일치할 수 있기 때문에 일치할 필요 없음
1 row in set, 1 warning (0.00 sec)
프라이머리 키를 사용하여 범위 접근하는 EXPLAIN 계획
EXPLAIN SELECT * FROM elem WHERE id > 3 and id < 6 and c = ‘Cd’\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: range -> 여전히 PK를 사용하지만 범위 스캔으로 바뀐다.
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL -> id 열의 조건이 상수가 아니므로 NULL, 조건 c가 상수지만 인덱스 조회에 사용되지 않음
rows: 1
filtered: 25.00
Extra: Using where -> where 절을 이용해 행을 찾는다.
1 row in set, 1 warning (0.00 sec)
세컨더리 인덱스 조회에 대한 EXPLAIN 계획
EXPLAIN SELECT * FROM elem WHERE a = 'Au'\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b -> 맨 왼쪽 접두사에 대한 요구사항을 충족하므로 세컨더리 인덱스를 사용
key_len: 8
ref: const -> 인덱스 a열만 사용
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT * FROM elem WHERE a = 'Au'AND b = 'Be'\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b -> 맨 왼쪽 접두사에 대한 요구사항을 충족하므로 세컨더리 인덱스를 사용
key_len: 16
ref: const,const -> 인덱스 a열과 b열 사용
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
조건이 상수지만 인덱스가 비고유라 조회가 하나 이상의 행과 일치할 수도 있기 때문에 const 접근 유형은 불가하다. 그리고 where 절이 하나의 행만 조회할 것으로 추정하지만 쿼리가 실행될 떄 변경될 수도 있다. (더 나올 수도 있다)
인덱싱되지 않은 열에 대해서 조건이 없지만 MySQl은 인덱스만 사용하여 일치하는 행을 찾을 수 있으므로 EXtra : NULL이 다시 나온다. 그래서 인덱스를 하나 추가해본다.
인덱스 조회와 인덱싱되지 않은 열에 대한 EXPLAIN 계획
EXPLAIN SELECT * FROM elem WHERE a = 'Al' AND c = 'Co'\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b
key_len: 8
ref: const
rows: 1
filtered: 25.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
인덱스가 c 열을 포함하지 않은 상태다.
세컨더리 인덱스 key : idx_a_b를 사용하지만 c열의 조건( c = 'Co')은 행을 일치 시키는 것을 방지한다. 즉, MySQL은 인덱스를 사용하여 a열의 조건에 대한 행을 조회하고 읽은 다음 c열의 조건과 일치하는 행을 찾는다.
++ 참고로 EXPLAIN에서 rows는 모든 테이블 조건과 일치한ㄴ 행 수가 아니라, 쿼리를 실행할 때 MySQL이 조회할 행 수로 추정한 값이다.
맨 왼쪽 접두사 없는 WHERE에 대한 EXPLAIN 계획
EXPLAIN SELECT * FROM elem WHERE b = 'Be'\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ALL -> 맨 왼쪽 접두사에 대한 요구사항이 충족되지 않아서
possible_keys: NULL -> MySQL이 테이블 조건과 인덱스 열을 사용할 수 없음을 나타낸다.
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 25.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
위 예시는 최악의 EXPLAIN 계획의 예로, type : ALL, possible_keys : NULL 또는 key : NULL이 표시되면 쿼리를 멈추고 분석해야 한다 볼 수 있다.
GROUP BY
MySQL은 값이 인덱스 순서에 따라 암묵적으로 그룹화 되므로 GROUP BY를 최적화 하기 위해 인덱스를 사용할 수 있다.
GROUP BY a에 대한 EXPLAIN 계획
EXPLAIN SELECT a, COUNT(*) FROM elem GROUP BY a\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: index -> 인덱스 스캔, 필터링 WHERE 절 없어서 모두 읽음
possible_keys: idx_a_b -> 인덱스 사용하여 GROUP BY 최적화
key: idx_a_b
key_len: 16
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index -> a열의 값만 읽고 PK 전체 행을 읽지 않음(커버링 인덱스)
1 row in set, 1 warning (0.01 sec)
동일한 인덱스 열의 GROUP BY 와 WEHRE 에 대한 EXPLAIN 계획
EXPLAIN SELECT a, COUNT(a) FROM elem WHERE a != 'Ar' GROUP BY a \\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: range -> 해당 WHERE 문이 a < 'Ar' AND a > 'Ar' 이 되어 버위 접근 유형이 동작한다.
key: idx_a_b
key_len: 16
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
다른 인덱스 열의 GROUP BY와 WHERE 에 대한 EXPLAIN 계획
EXPLAIN SELECT a, b FROM elem WHERE b = 'B' GROUP BY a\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: range
possible_keys: idx_a_b
key: idx_a_b
key_len: 16
ref: NULL
rows: 9
filtered: 100.00
Extra: Using where; Using index for group-by
1 row in set, 1 warning (0.01 sec)
위 쿼리에서는 WHERE 절의 b열에 대한 동일 조건과 SELECT 절에서 a와 b열 선택이라는 두 가지 중요한 세부 정보가 있다. 이러한 세부 정보에 의해, Extra아 표시된 특별한 Using index for group-by가 활성화된다. 만약 같음, 같지 않음으로 변경되면 쿼리 최적화가 손실 된다.
맨 왼쪽 접두사가 없는 GROUP BY에 대한 EXPLAIN 계획
EXPLAIN SELECT b, COUNT(*) FROM elem GROUP BY b\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: index -> a열에 조건이 없음에도 인덱스를 사용했다.
possible_keys: idx_a_b
key: idx_a_b
key_len: 24
ref: NULL
rows: 19
filtered: 100.00
Extra: Using index; Using temporary -> 맨 왼쪽 접두사 조건 지키지 않은 부작용
1 row in set, 1 warning (0.00 sec)
위 계획에 대해서 a열이 없음에도 인덱스를 사용하고 있는데 MySQL이 a열의 인덱스(type:index)를 스캔하고 있으므로 충족된다. a = a 와 같이 항상 참인 a열 조건을 상상해볼 수 있다.
Using temporary는 맨 왼쪽 접두사 조건을 지키지 않은 부작용으로 a값을 읽을 때 임시 테이블에서 b열값을 수집한다. a열의 모든 값을 읽은 후에는 COUNT(*)에 의해 그룹화 되고 집계된 임시테이블에 대해 테이블 스캔을 한다.
ORDER BY
MySQL은 ORDER BY를 최적화하기 위해 정려된 인덱스를 사용할 수 있다. 순서대로 행에 접근하기 때문에 정렬 동작을 피할 수 있게되는 것이다.( 참고로 MySQL은 행을 정렬할 때 EXPLAIN 계획의 Extra 필드에 Using filesort 를 출력한다. 파일 정렬이란 행 정렬을 의미한다. )
인덱스를 사용하여 ORDER BY를 최적화하는 방법에는 세 가지가 있다.
첫 번째는 맨 왼쪽 접두사를 사용하는 것이고,
ex) ORDER BY id,
ORDER BY a
ORDER BY a, b
두 번째는 인덱스 상수로 맨 왼쪽 부분을 유지하고 다음에 인덱스 열을 기준으로 정렬하는 것이다.
SELECT …… WHERE a = ‘Ar’ ORDER BY b
다른 인덱스 열들의 ORDER BY와 WHERE 에 대한 EXPLAIN 계획
EXPLAIN SELECT a, b FROM elem WHERE a = 'Ar' ORDER BY b\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b
key_len: 12
ref: const -> WHERE 조건이 상수 이므로 인덱스를 사용할 수 있다.
rows: 1
filtered: 100.00
Extra: Using index -> b열 값을 순서대로 읽는다.
1 row in set, 1 warning (0.01 sec)
첫 번째 인덱스 부분(a)의 WHERE 조건이 상수이므로 인덱스 (a,b)를 사용할 수 있다.
따라서 MySQL은 인덱스에서 a = ‘Ar’로 이동하고, 거기에서 b열 값을 순서대로 읽는다.
세 번째 방법은 두 번째 방법의 특별한 경우로 쿼리가 파일 정렬을 일으키지 않는 경우다.
EXPLAIN SELECT * FROM elem WHERE a = 'A1' AND b = 'B' ORDER BY id\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b
key_len: 24
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL -> 정렬을 일으키지 않았다.
1 row in set, 1 warning (0.00 sec)
위 쿼리에서 정렬을 일으키지 않은 이유는 세컨더리 인덱스에 추가된 프라이머리 키를 사용했기 때문이다.
맨 왼쪽 접두사 없는 ORDER BY 에 대한 EXPLAIN 계획
EXPLAIN SELECT * FROM elem WHERE a = 'Al' ORDER BY id\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b
key_len: 12
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition; Using filesort -> 왼쪽 접두사가 없어 정렬 발생
1 row in set, 1 warning (0.00 sec)
b열의 조건을 제거하면 MySQL이 ORDER BY 를 최적화하기 위해 숨겨진 프라이머리 키를 사용할 수 있도록 하는세컨더리 인덱스에 더 이상 왼쪽 접두사가 없다.
따라서 이 특별한 쿼리는 Extra 필드에 “using filesort”가 나온다.
새로운 최적화는 인덱스 컨디션 푸시다운이라고 하는 Using index condition 이다.
스토리지 엔진이 인덱스를 사용하여 WHERE 조건과 일치하는 행을 찾는다는 의미다.
커버링 인덱스
커버링 인덱스에는 쿼리가 참조하는 모든 열이 포함된다.
예시로, 인덱스 (a,b) 인 상태에서
SELECT a, b FROM elem WHERE a = ‘Au’ AND b = ‘Be’ 인 모양세다.
a와 b 열의 WHERE 조건은 늘 그렇듯이 인덱스 열을 가리키지만 이러한 인덱스열은 SELECET질의 해당 열을 다시 기리키기도 하여 인덱스에서 필요한 열 값을 읽었음을 나타낸다.
일반적으로 MySQL은 PK에서 전체행을 읽는다. 그러나 커버링 인덱스를 사용하면 MySQL은 인덱스에서 필요한 열값만을 읽을 수 있기 때문에 PK 조회를 피할 수 있게된다.
EXPLAIN SELECT a, b FROM elem WHERE a = 'A1' AND b = 'B1' \\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b
key_len: 24
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
커버링 인덱스는 매력적이지만 실제 쿼리에서 인덱스 하나가 맡기에는 너무 많은 열과 조건 절이 있어서 실용적이지는 않다. 그렇기 떄문에 커버링 인덱스를 만드는데 시간을 쏟지는 말자.
테이블 조인
MySQL은 테이블 조인에 인덱스를 사용하며, 사용법은 다른 것에 인덱스를 사용하는 것과 기본적으로 같다. 주요 차이라면 각 테이블의 조인 조건에 사용되는 소스값이다.
기존에 elem 테이블과 더불어 join을 위해 사용될 elem_names테이블 생성
CREATE TABLE `elem_names` (
`symbol` char(2) NOT NULL,
`name` varchar(16) DEFAULT NULL,
PRIMARY KEY (`symbol`)
) ENGINE=InnoDB;
INSERT INTO `elem_names` (`symbol`, `name`) VALUES
('H', 'Hydrogen'),
('He', 'Helium'),
('Li', 'Lithium'),
('Be', 'Beryllium');
프라이머리 키 조회 시 테이블 조인에 대한 EXPLAIN 계획
EXPLAIN SELECT name FROM elem
JOIN elem_names ON (elem.a = elem_names.symbol)
WHERE a IN ('Ag', 'Au', 'At')\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: range
possible_keys: idx_a_b
key: idx_a_b
key_len: 12
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: elem_names
partitions: NULL
type: eq_ref -> PK, 유니크 세컨더리 인덱스 사용하는 단일 행 조회
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: index_test_db.elem.a -> 참조열 elem.a를 읽는다는 의미
rows: 1
filtered: 100.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
참고로 테이블별로 조인이 인덱스 사용법을 변경하지 않는다. 주요 차이는 조인 조건의 값이 선행 테이블에서 온다는 것이다.
MySQL은 어떤 방법이든 테이블을 조인할 수 있지만, eq_ref접근 유형을 사용하는 인덱스 조회가 한 행만 일치하면 되기 때문에 가장 좋고 빠르다.
세컨더리 인덱스 조회 시 테이블 조인에 대한 EXPLAIN 계획
EXPLAIN SELECT name FROM elem
JOIN elem_names ON (elem.a = elem_names.symbol)
WHERE a IN ('Ag', 'Au')\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem_names
partitions: NULL
type: range
possible_keys: NULL
key: PRIMARY
key_len: 2
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: ref
possible_keys: idx_a_b
key: idx_a_b
key_len: 3
****ref: index_test_db.elem_names.symbol
rows: 2
filtered: 100.00
Extra: Using index
그전에 쿼리랑 매우 비슷하지만 실행 계획은 상당히 다르다.
IN() 목록에서 “At” 이 바뀌었을 뿐인데 말이다.
첫 번째 테이블은 elem_names로 elem JOIN elem_names으로 작성된 쿼리와는 다르다.
MySQL은 쿼리에 나열된 JOIN 순서가 아니라, 내부적으로 테이블 조인 순서를 결정한다. type과 key 필드는 프라이머리 키에 대한 범위 스캔을 나타내지만, 값은 ref필드가 FULL이고 이 테이블에는 WHJERE 조건이 없다. 해당 쿼리를 재작성하기 위해서는 SHOW WARINGS 명령어로 살펴보면
원래 쿼리에 작성된 elem.a대신 elem_names.symbols에 대한 값으로 IN() 목록을 사용하도록 쿼리를 재작성한다.
MySQL이 두 번째 elem 테이블을 조인하는데 사용할 2개의 행만 일치시키면 되기 때문에 PK 를 사용하여 매우 빠른 인덱스 조회를 할 수 있다.
MySQl 은 쿼리에서 테이블이 작성된 순서가 아니라 가능한 최상의 순서로 테이블을 조인한다.
그러나 인덱스 없이 테이블 조인을 할 수 있는데.. 이를 풀 조인이라고하며 쿼리가 수행할 수 있는 최악의 작업이다.
풀 조인을 위한 EXPLAIN 계획
EXPLAIN SELECT name
FROM elem STRAIGHT_JOIN elem_names IGNORE INDEX (PRIMARY)
ON (elem.a = elem_names.symbol)\\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: elem
partitions: NULL
type: index
possible_keys: idx_a_b
key: idx_a_b
key_len: 24
ref: NULL
rows: 19
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: elem_names
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 25.00
Extra: Using where; Using join buffer (hash join)
2 rows in set, 1 warning (0.00 sec)
강제로 STRIGHT_JOIN과 IIGNORE INDEX (PRIMARY)를 실행했는데,
이는 elem 테이블 인덱스 전용스캔으로 행 모두를 가져온다.
그리고 각 행에 대해 일치하는 행을 찾기 위해 풀 테이블 스캔을 사용하여 두 번쨰 테이블 elem_names 를 조인한다.
풀 조인은 선행 테이블의 각 행에 대해 조회가 발생하므로 쿼리가 수행할 수 있는 최악의 단일 작업이다.
더불어 Extra 필드의 Using join buffer (hash join)은 MySQL 8.0.18에 새롭게 도입된 해시 조인 알고리즘으로, 메모리 내 해싯값 테이블을 만들고 반복되는 테이블 스캔을 수행하는 대신 이 해시 ㅔ이블을 사용해 행을 조회한다.
'DataBase System' 카테고리의 다른 글
| 데이터 접근과 관리 (0) | 2024.07.15 |
|---|---|
| 쿼리 응답 시간 (1) | 2024.07.13 |
| Index란? (0) | 2023.05.21 |
| 데이터 베이스 키(Key) (0) | 2023.05.21 |
| DBCP 란? (1) | 2023.05.21 |