도서 :
- 인덱스는 쿼리의 성능을 언급하면서 뺴놓을 수 없는 부분이다.
- MySQL 8.0 으로 업그레이드 되면서 전문 검색이나 위치 기반 검색 기능도 모두 InnoDB 스토리지 엔진에서 사용할 수 있게 개선되었다.
- 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O 를 줄이느냐가 관건일 때가 상당히 많다.
- SSD 가 기계식 하드디스크보다 훨씬 대중화된 상태이며 요즘은 DBMS 용으로 사용할 서버에는 대부분 SSD 를 채택하고 있다. SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)을 제거하고 그 대신 플래시 메모리를 장착하고 있다. 그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다. 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다. 그리고 컴퓨터 메모리(D-RAM)보다는 느리지만 기계식 하드 디스크 드라이브보다는 훨씬 빠르다.
- '랜덤 I/O' 와 '순차 I/O' 같은 디스크 읽기 방식을 예로 들면 디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다. 하지만 SSD 의 장점은 랜덤I/O 가 훨씬 빠르다는 것이다. 데이터베이스 서버에서 순차 I/O 작엄은 그다지 비중이 크지 않고 랜덤 I/O 를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS 용 스토리지에 최적이라고 볼 수 있다. 때문에 일반적인 웹 서비스 환경의 데이터베이스에서는 SSD 가 하드 디스크 드라이브보다는 훨씬빠르다.
- '랜덤 I/O' 와 '순차 I/O' 둘다 하드 디스크 드라이브의 플래터를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미한다.
그러나 순차 I/O 는 3개의 페이지를 디스크에 기록하기 위해 1번 시스템 콜을 요청하지만, 랜덤 I/O 는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청한다. 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차I/O는 디스크의 헤드를 1번 움직였고, 랜덤 I/O 는 디스크 헤드를 3번 움직였다.
디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다.
그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 크다. 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다. -> log buffer 관련 블로그 1 , log buffer 관련 블로그 2
디스크 원판을 가지지 않는 SSD는 랜덤 I/O 와 순차 I/O 의 차이가 없을 것으로 예측하지만, 실제로는 그렇지 않다.
SSD 드라이브에서도 랜덤 I/O 는 여전히 순차 I/O 의 보다 전체 스루풋이 떨어진다. (SSD 관련 블로그1)
때문에 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이라고 할 수 있다. 여기서 랜덤I/O를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.
- 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤I/O 를 사용하며, 풀 테이블 스캔은 순차I/O를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 이는 순차 I/O 가 랜덤 I/O 보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문인데, 이런 형태는 OLTP(On-Line-Transaction Processing) 성격의 웹 서비스 보다는 데이터 웨어하우스나 통계 작업에서 자주 사용된다. -> OLTP/Oracle 관련 글
- 인덱스는 책의 '찾아보기(또는 색인)' 이라고 할 수 있다. 색인이 'ㄱ', 'ㄴ', 'ㄷ', ... 과 같은 순서로 정렬되어 있는 것 처럼, DBMS 의 인덱스 또한 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다. DBMS 의 인덱스는 SortedList 에 비유할 수 있다. SortedList 자료 구조는 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾아올 수 있다. DBMS 의 인덱스도 인덱스가 많은 테이블은 당연히 INSERT 나 UPDATE, DELETE 문장의 처리가 느려진다. 하지만 이미 정렬된 "찾아보기"용 표(인덱스)를 가지고 있기 때문에 SELECT 문장은 매우 빠르게 처리할 수 있다.
- 결론적으로 DBMS 에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. 때문에 SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.
- B-Tree 인덱스 는 데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘이다. 하지만 아직도 가장 범용적인 목적으로 사용되는 인덱스 알고리즘이기도하다. B-Tree 의 'B' 는 'Binary'의 약자가 아니라 'Balanced'를 의미한다는 점에 주의하자. B-Tree 는 칼럼의 원래 값을 변형시키지 않고(물론 값의 앞부분만 잘라서 관리하기는 하지만) 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다. 전문 검색과 같은 특수한 요건이 아닌 경우, 대부분 인덱스는 거의 B-Tree 를 사용할 정도로 일반적인 용도에 적합한 알고리즘이다.
- B-Tree 는 트리 구조의 최상위에 하나의 '루트 노드'가 존재하고 그 하위에 자식 노드가 붙어있는 형태다. 트리 구조의 가장 하위에 있는 노드를 '리프 노드' 라고 하고, 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간의 노드를 '브랜치 노드' 라고 한다. 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다. 인덱스는 테이블의 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야한다. 이를 위해 인덱스의 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가진다.
InnoDB 테이블은 프라이머리 키를 주소처럼 사용한다. 그래서 InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 데이터 파일을 바로 찾지는 못하고, 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다. 즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 한다. 이러한 InnoDB 인덱스 구조는 장단점을 가지고 있다.
| 장점 | - 프라이머리 키(클러스터링 키) 로 검색할 때 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름) - 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(이를 커버링 인덱스라고한다.) |
| 단점 | - 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐 - 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림 - INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림 - 프라이머리 키를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 처리 성능이 느림 |
- INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 바로 빠른 검색을 위해서다. 인덱스를 검색하는 작업은 B-Tree 의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 '트리 탐색'이라고 한다. 인덱스 트리 탐색은 SELECT 에서만 사용하는 것이 아니라 UPDATE 나 DELETE 를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다. B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다. 부등호('>','<') 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다. 또한 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree 의 빠른 검색 기능을 사용할 수 없다. 따라서 함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업은 B-Tree 의 장점을 이용할 수 없으므로 주의해야 한다.
- InnoDB 스토리지 엔진에서 인덱스는 더 특별한 의미가 있다. InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다. 따라서 UPDATE 나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 테이블의 모든 레코드를 잠글 수도 있다. InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다.
- 참조 블로그1 : 넥스트락 , 참조 블로그2 : 넥스트락
- B-Tree 인덱스는 인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스의 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.
- InnoDB 스토리 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block) 이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위이기도 하다. 인덱스는 결국 페이지 단위로 관리된다. B-Tree 는 인덱스의 페이지 크기와 키 값의 크기에 따라 자식 노드를 몇개까지 가질지 결정된다. MySQL 5.7 부터는 InnoDB 스토리지 엔진의 페이지 크기를 innodb_page_size 시스템 변수를 이용해 4KB~64KB 사이의 값을 선택할 수 있지만 기본값은 16KB 이다.
- 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다. 또한 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 것을 의미한다. 인덱스를 캐시해 두는 InnoDB 의 버퍼 풀의 키 캐시 영역은 크기가 제한적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어든다. 그러면 자연히 메모리의 효율이 떨어지는 결과를 가져온다.
- 결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라 하더라도 B-Tree 의 깊이(Depth) 가 깊어져서 디스크 읽기가 더 많이 필요하게 된다는 것을 의미한다. 따라서 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다는 것을 가조할 수 있고, 실제로 아무리 대용량 데이터베이스라도 B-Tree 의 깊이가 5단계 이상까지 깊어지는 경우는 흔치 않다.
- 인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality) 은 거의 같은 의미로 사용되며, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값은 100개인데, 그 중에서 유니크한 값의 수는 10개라면 기수성은 10이다. 인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
- 선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.
- 인덱스 컬럼이 가진 값들중 유니크한 값이 많을수록 불필요하게 읽는 레코드의 수는 적어진다.
예를 들어 country 칼럼으로만 인덱스가 생성된 상태에서 다음의 두 케이스를 살펴보자.
케이스 A : country 칼럼의 유니크한 값의 개수가 10개
케이스 B : country 칼럼의 유니크한 값의 개수가 1000개
mysql> SELECT * FROM tb_test WHERE country='KOREA' AND city='SEOUL';
- country 컬럼의 유니크한 값들이 총 10개 일때 :A 케이스는 10000건의 데이터가 있다고 가정하에 country='KOREA' 는 10000/10 이 일치한다고 예상할 수 있고, 인덱스를 통해 검색된 1000건 가운데 city='SEOUL' 인 레코드는 1건 이므로 999건은 불필요하게 읽은 것으로 볼 수 있다.
- country 컬럼의 유니크한 값들이 총 1000개일 때 : B 케이스 country='KOREA' 는 10000/1000 이 일치할 것이며 , 그 10 건 중 city='SEOUL' 인 레코드는 1건이므로 9건만 불필요하게 읽은 것이다.
때문에 인덱스(유니크한 컬럼인 인덱스)에서 유니크한 값(컬럼이 가진 값들)의 개수는 인덱스나 쿼리의 효율성에 큰영향을 미친다.
- 인덱스를 통해 테이블의 레코드를 읽는 것은 그렇지 않은 경우에 비해 높은 비용이 드는 작업이다. 테이블에 레코드가 100만 건이 있을때, 그중 50만 건을 읽어야 하는 쿼리가 있다고 가정해 보자. 이 작업은 전체 테이블을 모두 읽어서 필요 없는 50만 건을 버리는 것이 효율적일지, 인덱스를 통해 필요한 50만 건만 읽어 오는 것이 효율적일지 판단해야 한다. 인덱스를 이용한 익기의 손익 분기점이 얼마인지 판단할 필요가 있는데, 일반적인 DBMS 의 옵티마이저에서는 인덱스를 통해 레코드 1건을 익는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업으로 예측한다. 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 방식으로 처리하는 것이 효율적이다.
- 인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식으로 빠르다. 인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.(단건의 경우 다른 이름으로 구분하지만 동작방식은 인덱스 레인지 스캔이라 할 수 있다.)
mysql> SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';
일단 검색하려는 값의 시작해야 할 위치를 찾으면 그때부터 리프 노드의 레코드만 순서대로 읽으면 된다. 이처럼 차례대로 쭉 읽는 것을 스캔이라고 표현한다. 만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다. 그리고 최종적으로 스캔을 멈춰야 할 위치까지 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다.
- 여기서 중요한 것은 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다는 것이다. 이는 별도의 정렬 과정이 수반되는 것이 아니라 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다.
- 또 한가지 중요한 것은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다는 것이다. 이때 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O 가 한 번씩 일어난다. 예를 들어 3건의 레코드가 검색 조건에 일치(범위안에 든 인덱스 레코드값이)한다고 가정하면, 데이터 레코드를 읽기 위해 랜덤 I/O 가 최대 3번 필요한 것이다. 그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다. 그리고 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 효율적인 처리 방식이 된다.
1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index Seek)이라 한다.
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이과정을 인덱스 스캔(Index Scan) 이라 한다.( 1번과 2번을 합쳐서 인덱스 스캔이라고 하기도한다.)
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어 온다.
쿼리에 따라 3번 과정은 필요하지 않을 수 있는데, 이를 커버링 인덱스 라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다. MySQL 서버에서는 1번과 2번 단계의 작업이 얼마나 수행됐는지를 확인할 수 있게 다음을 제공한다.
mysql> SHOW STATUS LIKE 'Handler_%';
-----------------------------------------------------------
Variable_name | Value
-----------------------------------------------------------
Handler_read_first | 71
Handler_read_last | 1
Handler_read_key | 567
Handler_read_next | 3447233
Handler_read_prev | 19
Handler_read_key 상태 값은 1번 단계가 실행된 횟수, 그리고 Handler_read_next 와 Handler_read_prev 는 2번 단계로 읽은 레코드 건수를 의미한다. Handler_read_next 는 인덱스 정순으로 읽은 레코드 건수이며, Handler_read_prev 는 인덱스 역순으로 읽은 레코드 건수다. 그리고 Handler_read_first 와 Handler_read_last 는 인덱스의 첫 번째 레코드와 마지막 레코드를 읽은 횟수를 의미하는데, 이 둘은 MIN() 또는 MAX() 와 같이 제일 큰 값 또는 제일 작은 값만 읽는 경우 증가하는 상태 값이다. 이 상태 값들은 읽은 레코드 건수를 의미하는데, 실제 인덱스만 읽었는지 인덱스를 통해 테이블의 레코드를 읽었는지(3단계)는 구분하지 않는다.
- 인덱스 풀 스캔은 인덱스를 사용하지만 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 대표적으로 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 예시로, 인덱스는 (A,B,C) 칼럼의 순서로 만들어져 있지만 쿼리의 조건절은 B 칼럼이나 C 칼럼으로 검색하는 경우다.
- 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로, 이 방식이 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다. 하지만 인덱스 풀 스캔 방식으로 인덱스를 사용하는 경우는 '인덱스를 효율적으로 사용하지 못한다'라고 표현할 수 있다.
- 루스 인덱스 스캔은 '인덱스 스킵 스캔' 이라고도 하며, MySQL8.0 버전부터 최적화를 지원하기 시작했다. 앞서 설명한 두가지 ( 인덱스 레인지 스캔, 인덱스 풀 스캔 ) 은 루스 인덱스 스캔과는 상반된 의미에서 타이트 인덱스 스캔으로 분류한다. 루스(Loose) 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.
- 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(Skip)하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.
mysql> SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;
이 쿼리에서 사용된 dept_emp 테이블은 dept_no 와 emp_no 라는 두 개의 칼럼으로 인덱스가 생성돼 있다. 또한 이 인덱스는 (dept_no, emp_no) 조합으로 정렬까지 돼 있어서 dept_no 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다. 즉 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.
- 인덱스 스킵 스캔은 인덱스의 핵심인 '데이터베이스는 인덱스의 값이 정렬돼 있다' 를 이용한다. 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요하다. 예를 들어, employees 테이블에 다음과 같은 인덱스를 생성해보자.
mysql> ALTER TABLE employees
ADD INDEX ix_gender_birthdate (gender, bitrh_date);
이 인덱스를 사용하려면 WHERE 조건절에 gender 칼럼에 대한 비교 조건이 필수다.
-- // 인덱스를 사용하지 못하는 쿼리
mysql> SELECT * FROM employees WHERE birth_date >= '1965-02-01';
-- // 인덱스를 사용할 수 있는 쿼리
mysql> SELECT * FROM employees WHERE gender='M' AND birth_date >= '1965-02-01';
위의 두 쿼리 중 gender 칼럼과 birth_date 칼럼의 조건을 모두 가진 두 번째 쿼리는 인덱스를 효율적으로 사용할 수 있지만 gender 칼럼에 대한 비교 조건이 없는 첫 번째 쿼리는 인덱스를 사용할 수가 없었다. 주로 이런 경우에는 birth_day 칼럼부터 시작하는 인덱스를 새로 생성해야만 했다. 하지만
- MySQL8.0 부터는 옵티마이저가 gender 칼럼을 건너 뛰어서 birth_date 칼럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔(Index Skip Scan) 최적화 기능이 도입됐다. 물론 이전에도 인덱스 스킨 스캡과 비슷한 루스 인덱스 스캔 기능이 있었지만 이전에는 GROUP BY 작업에서만 적용가능했다면, MySQL 8.0 부터는 WHERE 조건절의 검색에서도 사용가능하도록 용도가 훨씬 넒어진 것이다.
- 인덱스 스킵 스캔 기능을 비활성화하고, 활성화 하고의 차이를 살펴보자
-- // 인덱스 스킵 스캔 비활성화
mysql> SET optimizer_switch='skip_scan=off';
mysql> EXPLAIN
SELECT gender, birth_date
FROM employees
WHERE birth_date>='1965-02-01';
--------------------------------------------------------------------------------------------------------------
id | table | type | key | Extra
--------------------------------------------------------------------------------------------------------------
1 | employees | index | ix_gender_birthdate | Using where; Using index
type 칼럼이 'index' 라고 표시된 것은 인덱스를 처음부터 끝까지 모두 읽었다( 풀 인덱스 스캔 ) 는 의미이므로 인덱스를 비효율적으로 사용한 것이다. gender 칼럼과 birth_date 칼럼만 있으면 처리를 완료할 수 있기 때문에 ix_gender_birthdate 인덱스를 풀 스캔한 것이다. 만약 예제 쿼리가 employees 테이블의 모든 칼럼을 가져와야 했다면 테이블 풀 스캔을 실행했을 것이다.
- MySQL8.0 버전부터 도입된 인덱스 스킵 스캔을 활성화하고 동일 쿼리 실행계획을 다시보자.
-- // 인덱스 스킵 스캔 활성화
mysql> SET optimizer_switch='skip_scan=on';
mysql> EXPLAIN
SELECT gender, birth_date
FROM employees
WHERE birth_date>='1965-02-01';
--------------------------------------------------------------------------------------------------------------
id | table | type | key | Extra
--------------------------------------------------------------------------------------------------------------
1 | employees | range | ix_gender_birthdate | Using where; Using index for skip scan
type 칼럼의 값이 'range' 로 표시되는데, 인덱스에서 꼭 필요한 부분만 읽었다는 것을 의미한다. 그리고 Extra 칼럼에 'Using index for skip scan' 이라는 문구가 표시됐는데, ix_gender_birthdate 인덱스에 대해 인덱스 스킵 스캔을 활요해 데이터를 조회했다는 것을 의미한다. MySQL 옵티마이저는 우선 gender 칼럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gender 칼럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다. gender 가 만약 'M' 과 'F' 두가지를 가진 유니크 칼럼이라면 MySQL 옵티마이저는 내부적으로 아래의 두가지 쿼리를 실행한 것과 같은 형태로 실행한다.
mysql> SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date>='1965-02-01';
mysql> SELECT gender, birth_date FROM employees WHERE gender='F' AND birth_date>='1965-02-01';
여기서 gender 칼럼이 'M' 또는 'F' 값만을 가지기에 이렇게 처리하는 것은 아니다. 칼럼이 어떤 타입이더라도 MySQL 서버는 인덱스를 루스 인덱스 스캔과 동일한 방식으로 읽으면서 인덱스에 존재하는 모든 값을 먼저 추출하고 그 결과를 이용해 인덱스 스킵 스캔을 실행한다.
- 인덱스 스킵 스캔은 MySQL 8.0 버전에 새로이 도입된 기능이어서 아직 다음과 같은 단점이 있다.
- WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
- 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함 (커버링 인덱스)
첫 번째 조건은 쿼리 실행 계획의 비용과 관련된 부분인데, 만약 유니크한 값의 개수가 매우 많다면 MySQL 옵티마이저는 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해진다. 그래서 쿼리의 처리 성능이 오히려 더 느려질 수 있다. 그래서 인덱스 스킵 스캔은 인덱스의 선행 칼럼이 가진 유니크한 값의 개수가 소량일 때만 적용 가능한 최적화라는 것을 기억하자.
두 번쨰 제약 조건은 아래 예제 쿼리를 통해 확인해보자.
mysql> EXPLAIN
SELECT *
FROM employees
WHERE birth_date>='1965-02-01';
---------------------------------------------------------------------------------------
id | title | type | key | rows | Extra
---------------------------------------------------------------------------------------
1 | employees | ALL | NULL | 300363 | Using where
WHERE 조건절은 동일하지만 SELECT 절에서 employees 테이블의 모든 칼럼을 조회하도록 변경했다. 이 쿼리는 ix_gender_birthdate 인덱스에 포함된 gender 칼럼과 birth_date 칼럼 이외의 나머지 칼럼도 필요로 하기 때문에 인덱스 스킵 스캔을 사용하지 못하고 풀 테이블 스캔으로 실행 계획을 수립한 것을 확인할 수 있다. 하지만 이 제약 사항은 MySQL 서버의 옵티마이저가 개선되면 충분히 해결될 수 있는 부분으로 보인다.
- 다중 칼럼(Multi-column) 인덱스 는 두 개 이상의 칼럼으로 구성된 인덱스이며, 2개 이상의 칼럼이 연결됐다고 해서 'Concatenated Index' 라고도 한다. 다중칼럼인덱스에서 인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다. 즉 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다는 것이다. 만약 세 번째 인덱스 칼럼도 있다면 세 번째는 두 번째 칼럼에 의존해서 정렬되고 네 번째, 다섯 번째도 마찬가지다. 때문에 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치( 순서 ) 가 상당히 중요하며, 그것을 아주 신중히 정해야 하는 이유가 바로 그것이다.
- B-Tree 인덱스의 정렬은 인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다. 일반적인 사용 DBMS 에서는 인덱스를 생성하는 시점에 인덱스를 구성하는 각 칼럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다. MySQL 5.7 버전까지는 칼럼 단위로 정렬 순서를 혼합(ASC 와 DESC 혼합)해서 인덱스를 생성할 수 없었다. 하지만 MySQL 8.0 버전부터는 다음과 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다. ( 여기서 주의할 것은 인덱스 생성시 정렬방향을 결정하는것과, 데이터를 가져올때 쿼리에 쓰는 접근방향은 별개라는 것이다. )
mysql> CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);
그렇다면 인덱스 내림차순 정렬은 왜 필요할까? 인덱스 tid 칼럼이 오름차순으로 정렬되어있다고 가정하고 다음을 보자.
mysql> SELECT * FROM t1 ORDER BY tid ASC LIMIT 12619775,1;
-> 1 row in set (4.15 sec )
mysql> SELECT * FROM t1 ORDER BY tid DESC LIMIT 12619775,1;
-> 1 row in set (5.35 sec )
위 예시 처럼 실제 속도에서 차이가 나기때문에 대량의 레코드를 빈번하게 내림차순으로 조회하는 경우 내림차순 인덱스를 고려해볼만 하다. 이는 많은 쿼리가 인덱스의 앞쪽만 또는 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는 데 도움이 될 것이다.
실제 내부적으로 InnoDB 에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 다음의 두 가지 이유가 있다.
- 페이지 잠금이 인덱스 정순 스캔(Forward Index Scan) 에 적합한 구조이다.
- 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조 (InnoDB 페이지 내부에서 레코드들이 단방향으로만 링크를 가진 구조이다.)이다.
일반적으로 인덱스를 ORDER BY ... DESC 하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면 내림차순 인덱스를 굳이 고려할 필요는 없다.
- 쿼리의 WHERE 조건이나 GROUP BY , 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 쿼리의 조건을 최적화하거나, 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.
다중 컬럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교('=') 인지 아니면 크다('>') 또는 작다('<') 같은 범위 조건인지에 따라 각 인덱스 칼럼의 활용 형태가 달라지며, 그 효율 또한 달라진다.
mysql> SELECT * FROM dept_emp WHERE dept_no = 'd002' AND emp_no >= 10114;
위 쿼리를 위해 dept_emp 테이블에 각각 칼럼의 순서만 다른 두 가지 케이스로 인덱스를 생성했다고 가정하자.
케이스 A : INDEX (dept_no, emp_no)
케이스 B : INDEX (emp_no, dept_no)
케이스 A 인덱스는 "dept_no='d002' AND emp_no>=10144" 인 레코드를 찾고, 그 이후에는 dept_no가 'd002' 가 아닐 땍까지 인덱스를 그냥 쭉 읽기만 하면된다. 이 경우 읽은 레코드가 모두 사용자가 원하는 레코드임을 알 수 있다. 즉, 조건을 만족하는 레코드가 5건이라고 할때 5건의 레코드를 찾는 데 꼭 필요한 5번의 비교 작업만 수행한 것이므로 상당히 효율적으로 인덱스를 이용한 것이다.
하지만 케이스 B 인덱스는 우선 "emp_no>=10144 AND dept_no='d002'" 인 레코드를 찾고, 그 이후 모든 레코드에 대해 dept_no 가 'd002'인지 비교하는 과정을 거쳐야 한다.
케이스 A 인덱스에서 2번째 칼럼인 emp_no 는 비교 작업의 범위를 좁히는 데 도움을 준다. 하지만 케이스 B 인덱스에서 2번째 칼럼인 dept_no 는 비교 작업의 범위를 좁히는 데 아무런 도움을 주지 못하고, 단지 쿼리의 조건에 맞는지 검사하는 용도로만 사용됐다. 즉, 케이스 A 인덱스에서 dept_no 와 emp_no 칼럼은 모두 '작업 범위 결정 조건' 에 해당하지만, 케이스 B 인덱스에서는 emp_no 칼럼만 '작업 범위 결정 조건' 이고, dept_no 칼럼은 '필터링 조건(체크 조건)' 으로 사용된 것이다. 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 필터링 조건(체크 조건)은 많다고 해서(최종적으로 가져오는 레코드는 작게 만들지 몰라도) 쿼리의 처리 성능을 높이지는 못한다. 오히려 쿼리 실행을 더 느리게 만들 때가 많다.
- 인덱스의 가용성은 인덱스의 특징이 왼쪽 값에 기준해서(Left-most) 오른쪽 값이 정렬돼 있다는 것이다. 여기서 왼쪽이란 '케이스 A : INDEX (first_name)' 같은 하나의 칼럼 내에서 뿐만 아니라 '케이스 B : INDEX (dept_no, emp_no)' 와 같은 다중 칼럼 인덱스의 칼럼에 대해서도 함께 적용된다.
이런 정렬 특성은 빠른 검색의 전제 조건이다. 즉 하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다. 또한 다중 칼럼 인덱스에서도 왼쪽 칼람의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없다.
케이스 A 에서 하나의 칼럼 내에서의 인덱스 활용을 살펴보자. (인덱스를 효율적으로 사용하지 못한 예)
mysql> SELECT * FROM employees WHERE first_name LIKE '%mer';
이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수는 없다. 그 이유는 first_name 칼럼에 저장된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하는데, 조건절에 주어진 상숫값('%mer') 에는 왼쪽 부분이 고정되지 않았기 때문이다. 따라서 정렬 우선순위가 낮은 뒷부분의 값만으로는 왼쪽 기준( Left-most) 정렬 기반의 인덱스인 B-tree 에서는 인덱스의 효과를 얻을 수 없다.
케이스 B 의 다중칼럼으로 지정된 인덱스 활용을 살펴보자. (인덱스를 효율적으로 사용하지 못한 예)
mysql> SELECT * FROM dept_emp WHERE emp_no >= 10114;
인덱스(dept_no, emp_no) 칼럼 순서대로 생성돼 있다면 인덱스의 선행 칼럼인 dept_no 조건 없이 emp_no 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다. 케이스 B 의 인덱스는 다중 칼럼으로 구성된 인덱스이므로 dept_no 칼럼에 대해 먼저 정렬한 후, 다시 emp_no 칼럼값으로 정렬돼 있기 때문이다.
간단히 WHERE 조건절에 대한 내용만 언급했지만 인덱스의 왼쪽 값 기준 규칙은 GROUP BY 절이나 ORDER BY 절에도 똑같이 적용된다.
- 가용성과 효율성 판단 중 기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없다. 여기서 사용할 수 없다는 것은 작업 범위 결정 조건으로 사용할 수 없다는 것을 의미한다. 경우에 따라서는 체크 조건으로 인덱스를 사용할 수는 있다.
- NOT-EQUAL로 비교된 경우( "<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL" )
"... WHERE column <> 'N';"
"... WHERE column NOT IN (10, 11, 12);"
"... WHERE column IS NOT NULL;"
- LIKE "%??"( 앞 부분이 아닌 뒷 부분 일치 ) 형태로 문자열 패턴이 비교된 경우
"... WHERE column LIKE '%승환';"
"... WHERE column LIKE '_승환';"
"... WHERE column LIKE '%승%';"
- 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
"... WHERE SUBSTRING(column, 1, 1) = 'X';"
"... WHERE DAYOFMONTH(column) = 1;"
- NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우(NOT-DETERMINISTIC : 참고 블로그1)
"... WHERE column = deterministic_function();"
- 데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
"... WHERE char_column = 10;"
- 문자열 데이터 타입의 콜레이션이 다른 경우
"... WHERE utf8_bin_char_column = euckr_bin_char_column;"
- 다른 일반적은 DBMS 에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL 에서는 NULL 값도 인덱스에 저장된다. 다음과 같은 WHERE 조건도 '작업 범위 결정 조건'으로 인덱스를 사용한다.
mysql> ... WHERE column IS NULL .. ;
- 다음과 같은 다중 칼럼 인덱스가 있다고 가정해보자.
INDEX ix_test ( column_1, column_2, column_3, ..., column_n )
- 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
- column_1 칼럼에 대한 조건이 없는 경우
- column_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
- 작업 범위 결정 조건으로 인덱스를 사용하는 경우 ( i 는 2보다 크고 n보다 작은 임의의 값을 의미 )
- column_1 ~ column_( i - 1) 칼럼까지 동등 비교 형태("=" 또는 "IN") 로 비교
- column_i 칼럼에 대해 다음 연산자 중 하나로 비교
-> 동등 비교 ( "=" 또는 "IN" )
-> 크다 작다 형태 ( ">" 또는 "<" )
-> LIKE 로 좌측 일치 패턴 ( LIKE '승환%' )
위의 두 가지 조건을 모두 만족하는 쿼리는 column_1 부터 column_i 까지는 작업 범위 결정 조건으로 사용되고, column_( i + 1 ) 부터 column_n 까지의 조건은 체크 조건으로 사용된다. 인덱스를 사용하는 경우와 그렇지 않은 상황에 해당하는 쿼리의 조건 몇가지를 예제로 살펴보자.
-- // 다음 쿼리는 인덱스를 사용할 수 없음
mysql> ... WHERE column_1 <> 2
-- // 다음 쿼리는 column_1 과 column_2 까지 범위 결정 조건으로 사용됨
mysql> ... WHERE column_1 = 1 AND column_2 > 10
-- // 다음 쿼리는 column_1 , column_2, column_3 까지 범위 결정 조건으로 사용됨
mysql> ... WHERE column_1 IN (1, 2) AND column_2 = 2 AND column_3 <= 10
-- // 다음 쿼리는 column_1 , column_2 , column_3 까지 범위 결정 조건으로 그리고 column_4 는 체크 조건으로 사용됨
mysql> ... WHERE column_1 = 1 AND column_2 = 2 AND column_3 IN (10, 20, 30) AND column_4 <> 100
-- // 다음 쿼리는 column_1 , column_2 , column_3 , column_4 까지 범위 결정 조건으로 사용됨
-- // 좌측 패턴 일치 LIKE 비교는 크다 또는 작다 비교와 동급으로 생각하면 됨
mysql> ... WHERE column_1 = 1 AND column_2 IN (2, 4) AND column_3 = 30 AND column_4 LIKE '김승%'
-- // 다음 쿼리는 column_1 , column_2 , column_3 , column_4 , column_5 칼럼까지 모두 범위 결정 조건으로 사용됨
mysql> ... WHERE column_1 = 1 AND column_2 = 2 AND column_3 = 30 AND column_4 = '김승환' AND column_5 = '서울'
이러한 인덱스의 특징은 B-Tree 의 특징이므로 MySQL 뿐 아니라 대부분의 RDBMS 에도 동일하게 적용된다.
- 유니크 인덱스는 사실 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. 말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, MySQL 에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. 유니크 인덱스에서 NULL 도 저장될 수 있는데, NULL 은 특정 값이 아니므로 2개 이상 저장될 수 있다. MySQL 에서 프라이머리 키는 기본적으로 NULL 을 허용하지 않는 유니크 속성이 자동으로 부여된다. 그러나 InnoDB 테이블의 프라이머리 키는 클러스터링 키의 역할도 하므로 유니크 인덱스와는 근본적으로 다르다.
유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 사실 인덱스의 구조상 아무런 차이점이 없다. 유니크 인덱스와 일반 세컨더리 인덱스의 일기와 쓰기를 성능 관점에서 살펴보자.
- 많은 사람이 유니크 인덱스가 빠르다고 생각한다. 하지만 이것은 사실이 아니다. 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아니라는 것이다.
비록 하나의 값을 검색하는 경우, 유니크 인덱스와 일반 세컨더리 인덱스는 사용되는 실행 계획이 다르다. 하지만 이는 인덱스의 성격이 유니크한지 아닌지에 따른 차이일 뿐 큰 차이는 없다. 즉, 1개의 레코드를 읽느냐 2개의 레코드를 읽느냐의 차이만 있다는 것일 뿐, 읽어야 할 레코드 건수가 같다면 성능상의 차이는 미미하다.
- 유니크 인덱스는 인덱스 쓰기에서 세컨더리 인덱스의 쓰기보다 느리다.
새로운 레코드가 INSERT 되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다. 그런데 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다. 또한 MySQL 에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 떄는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다. 그리고 InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼(Change Buffer) 가 사용되어 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 안타깝게도 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.
- 결론적으로 유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는것이 좋다.