[Real MySQL 정리] , 5장 인덱스

5장, 인덱스

5.1 디스크 읽기 방식

5.1.1 저장 매체

서버에 사용되는 데이터를 저장할 수 있는 매체는 크게 3가지로 나뉜다.

• 내장 디스크(Internal Disk)
• DAS(Direct Attached Storage)
• NAS(Network Attached Storage)
• SAN(Storage Area Nework)

5.2 인덱스란?

DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. 인덱스를 역할로 구분해본다면 PK와 보조 키로 구분할 수 있다.

PK : 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스

• 보조키 : PK를 제외한 나머지 모든 인덱스
  PK를 제외한 나머지 모든인덳는 보조 인덱스라고 분류한다, 유니크 인덱스는 PK와 성격이 비슷하고 PK를 대체해서 사용할 수 있다고
  해서 대체 키라고도 하는데, 별도로 분류하기도 하고 그냥 보조 인덱스로 분류하기도 한다.

데이터 저장 방식(알고리즘)별로 구분하는 것은 사실 상당히 많은 분류가 가능하겠지만 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다. 최근 새롭게 Fractal-Tree 인덱스와 같은 알고리즘도 도입됐다.

• B-Tree : B-Tree 인덱스는 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱싱하는 알고리즘이다.
• Hash : Hash 인덱스 알고리즘은 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.
  하지만 값을 변형해서 인덱싱하므로, 전방(Prefix) 일치와 같이 값의 일부만 검색하고자 할 때는 해시 인덱스를 사용할 수 없다.
  Hash 인덱스는 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
• Fractal-Tree : Fractal-Tree 알고리즘은 B-Tree의 단점을 보완하기 위해 고안된 알고리즘이다. 값을 변형하지 않고
  인덱싱하며 범용적인 목적으로 상요할 수 있다는 측면에서 B-Tree와 거의 비슷하지만 데이터가 저장되거나 삭제될 때 처리 비용을 상당히 줄일 수 있게
  설계된 것이 특징이다.

데이터의 중복 허용 여부로 분류하면 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있다. 유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려 주는 효과를 낸다.

5.2 B-Tree 인덱스란?

B-Tree에는 여러 가지 변형된 형태의 알고리즘이 있는데, 일반적으로 DMBS에서는 주로 B+-Tree 또는 B-Tree가 사용된다.

` B-Tree의 "B"는 Binary의 약자가 아니라 Balanced를 의미한다.`

 

B-Tree는 칼럼의 원래 값을 변형시키지 않고(물론 값의 앞부분만 잘라서 관리하기는 하지만) 인덱스 구조체 내에서는 항상 정렬된 상태로 유지하고 있다.

5.3.1. 구조 및 특성

B-Tree는 트리 구조로 됭있다. 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간의 노드를 "브렌치 노드"라고 한다. 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소 값을 가지고 있다.

인덱스는 테이블의 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다. 이를 위해 인덱스의 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가지게 된다.

InnoDB 테이블에서는 PK에 의해 클러스터링되기 때문에 PK 자체가 주소 역할을 한다.

5.3.2 B-Tree 인덱스 키 추가 및 삭제

인덱스 키 추가

B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 만약 리프 노드가 꽉 차서 더는 저장할 수 없을 떄는 리프 노드가 분리돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어진다. 이러한 탓에 B-Tree는 상대적으로 쓰기 작업(새로운 키를 추가 하는 작업)에 비용이 많이 드는 것으로 알려졌다.

 

인덱스 추가로 인해 INSERT나 UPDATE 문장이 어떤 영향을 받을까? 테이블의 칼럼 수, 칼럼의 크기, 인덱스 칼럼의 특성 등을 확인해야 한다.

 

대략적으로 계산하는 방법은 테이블에 레코드를 추가하는 작업 비용을 1이라고 가정하면 해당 테이블의 인덱스에 키를 추가하는 작업 비용을 1~1.5 정도로 예측하는 것이 일반적이다. 일반적으로 테이블에 인덱스가 3개(테이블의 모든 인덱스가 B-Tree라는 가정하에)가 있다면 이떄 테이블에 인덱스가 하나도 없는 경우 작업 비용이 1이고, 3개인 경우에는 5.5 정도의 비용(1.5 * 3 + 1)정도로 예측해 볼 수 있다. 중요한 것은 이 비용의 대부분이 메모리와 CPU에서 처리하는 시간이 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야 하기 때문에 시간이 오래 걸린다는 것이다.

 

InnoDB 스토리지 엔진은 적절하게 인덱스 키 추가 작업을 지연시켜 나중에 처리할지, 아니면 바로 처리할지 결정한다.

1. 사용자의 쿼리 실행
2. InnoDB 버퍼 풀에 새로운 키 값을 추가해야 할 페이지(B-Tree의 리프 노드)가 존재한다면 즉시 키 추가 작업 처리
3. 버퍼 풀에 B-Tree의 리프 노드가 없다면 인서트 버퍼에 추가할 키 값과 레코드의 주소를 임시로 기록해 두고 작업 완료(사용자의 쿼리는 실행 완료됨)
4. 백그라운드 작업은 인덱스 페이지를 읽을 때마다 인서트 버퍼에 머지해야 할 인덱스 키값이 있는지 확인한 후, 있다면 병합함(B-Tree에 인덱스 키와 주소를 저장)
5. 데이터베이스 서버 자원의 여유가 생기면 MySQL 서버의 인서트 버퍼 머지 스레드가 조금씩 인서트 버퍼에 임시 저장된 인덱스 키와 주소 값을 머지(B-Tree에 인덱스 키와 주소를 저장)시킴

이 기능은 체인지 버퍼링이라고한다. 관련 설정 파라미터로 innodb_chanage_buffering가 있다.

 

버퍼에 의해 인덱스 키 추가 작업이 지연되어 처리된다 하더라도, 이는 사용자에게 아무런 악영향 없이 투명하게 처리되므로 개발자는 이를 신경쓰지 않아도 된다. innodb_chanage_buffering설정 값을 이용해 키 추가 작업과 키 삭제 작업 중 어느 것을 지연처리할지 설정해야 한다.

 

인덱스 키 삭제

해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만하면 작업이 완료된다. 이렇게 삭제 마키된 인덱스 키 공간은 계속 그대로 방치하거나 또는 재활용할 수 있다. 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 이 작업 역시 디스크 I/O가 필요한 작업이다.

 

인덱스 키 변경

인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키값이 변경되는 경우에는 단순히 인덱스상의 키값만 병경하는 것은 불가능하다. B-Tree의 키값 변경 작업은 먼저 키값을 삭제한 후, 다시 새로운 키값을 추가하는 형태로 처리된다.

 

인덱스 키 검색

INSERT, UPDATE, DELETE 작업을 할 떄 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 바로 빠른 검색을 위해서다.

 

검색 작업은 트리 탐색 과정을 거친다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우애에도 인덱스가 있으면 빠른 검색이 가능하다. B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다.

또한 인덱스를 이용한 검색에서 중요한 사실은 인덱스의 키값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다는 것이다. 이미 변형된 값은 B-Tree 인덱스에 존재하는 값이 아니다. 따라서 함수나 연산을 수행한 결과로 정렬한다거나검색하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 한다.

 

InnoDB 스토리지 엔진에서 인덱스는 더 특별한 의미가 있다. InnoDV 테이블에서 지원하는 레코드 잠금이나 넥스트 키 락(갭 락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼있다. 따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 테이블의 모든 레코드를 잠글 수도 있다. InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하고 많은 부분에 영향을 끼친다는 것이다.

5.3.3 B-Tree 인덱스 사용에 영향을 미치는 요소

B-Tree 인덱스는 인덱스를구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키값의 개수 등에의해 검색이나 변경업의 성능이 영향을 받는다.

 

인덱스 키값의 크기

 

일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조다. 그러면 MySQL의 B-Tree는 자식 노드를 몇 개갂지 가질지가 궁금할 것이다. 그것은 바로 인덱스의 페이지 크기와 키 값의 크기의 따라 결정된다. InnoDB의 모든 페이지 크기는 16KM로 고정돼 있다.

만약 인덱스의 키가 16바이트라고 가정하면 아래 그림과 같이 인덱스 페이지가 구성될 것이다.

자식 노드 주소라는 것은 여러가지 복합적인 정보가 담긴 영역이며, 페이지의 종류별로 대략 6바이트에서 12바이트까지 다양한 크기의 값을 가질 수 있다.

여기서는 편의상 자식 노드 주소 영역이 평균적으로 12바이트로 구성된다고 가정하자. 그림의 경우 하나의인덱스 페이지(16KB)에 몇 개의 키를 저장할 수 있을까? 계산해 보면 16*1024/(16+12) = 585개 저장할 수 있다. 최종적으로 이 경우는 자식 노드를 585개를 가질 수 있는 B-Tree가 되는 것이다.

 

그러면인덱스 키값이 커지면 어떤 현상이 발생할까?

 

위의 경우에서 키값의 크기가 두 배인 32바이트로 늘어났다고 가정하면 한 페이지에 인덱스 키를 16*1024(32 + 12) = 372개 저장할 수 있다. 만약 SELECT 쿼리가 레코드 500개를 일겅야 한다면 전자는인덱스 페이지 한 번으로 해결될 수도 있지만, 후자는 최소한 2번 이상 디스크로부터 읽어야 한다. 결국 인덱스를 구성하는 키값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.

 

또한, 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스 크기가 커진다는 것을 의미한다. 하지만 인덱스를 캐시해 두는 InnoDB의 버퍼 풀이나 캐시 영역은 크기가 제한적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리(버퍼 풀이나 키 캐시)에 캐시해 둘 수 있는 레코드 수는 줄어드는 것을 의미한다. 자연히 메모리의 효율이 떨어지게 되는 결과를 가져온다.

 

B-Tree 깊이

 

B-Tree 인덱스의 깊이는 상당히 중요하지만 직접적으로 제어할 방법이 없다. 인덱스 키값의 평균 크기가 늘어나면 어떤 현상이 발생할까?

 

인덱스의 B-Tree의 깊이가 3인 경우 최대 몇 개의 키값을 가질 수 있는지 비교해 보자.

 

키 값이 16바이트인 경우에는 최대 2억개 정도의 키값을 담을 수 있지만 키값이 32바이트로 늘어나면 5천만개로 줄어든다. B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제다.

결론적으로 인덱스스 키값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키값의 개수가 작아지고, 그 때문에 같은 레코드 건수라 하더라도 B-Tree 의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하게 된다.

 

선택도(기수성)

모든 인덱스 키값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스의 키값은 100개인데, 그중에서 유니크한 값의 수는 10개라면 기수성은 10이다. 인덱스 키값 가운데 중복된 값이 많이자면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

참고, 선택도가 좋지 않다고 하더라도 정렬이나 그룹핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다.
인덱스가 항상 검색에만 사용되는 것이 아니므로 여러 가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.

 

읽어야 하는 레코드의 건수

 

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다. 인덱스를 이용한 읽기의 손익 분기점이 얼마인지 판단할 필요가 있는데, 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 더 비용이 많이 드는 작업인 것으로 예측한다.

 

즉, 인덱스를 통해 읽어야할 레코드의 건수(물론 옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드에 20%~25%를 넘어서면 인덱스를 이용하지 않고 직접 테이블을모두 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적이다.

 

전체 100만 건의 레코드 가운데 50만 건을 읽어야 하는 작업은 인덱스의 손익 분기점인 20~25%보다 훨씬 크기 때문에
MySQL 옵티마이저는 인덱스를 이용하지 않고 직접 테이블을 처음부터 끝까지 읽어서 처리할 것이다. 이렇게 많은 레코드를 읽을 때는 강제로 인덱스를 사용하도록 힌트를 추가해도 성능상 얻을 수 있는 이점이 없다.

5.3.4 B-Tree 인덱스를 통한 읽어내기

어떤 경우에 인덱스를 사용하도록 유도할지, 또는 사용하지 못하게 판단하려면 MySQL(더 정확히는 스토리지 엔진)이 어떻게 이용(경유)해서 실제 레코드를 읽어 내는지 알고 있어야 할 것이다. 인덱스를 이용하는 대표적인 방법 3가지를 보자.

 

인덱스 레인지 스캔

 

인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식으로, 밑에서 설명할 나머지 2가지 접근 방식보다는 빠른 방법이다.

인덱스 레인지 스캔은 검색해 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현한다. 그림의 화살표에서도 알 수 있듯이 루트 노드에서부터 비교를 시작해 브렌치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 실제로 원하는 시작 지점을 찾을 수 있다. 일단 시작해야할 위치를 찾으면 그때부터는 리프 노드의 레코코드만 순서대로 읽으면 된다.

 

만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다.
그리고 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 끝낸다.

두꺼운 선은 스캔해야 할 위치 검색을 위한 비교 작업을 의미하며, 바탕색이 있는 리프 노드의 레코드 구간은 실제 스캔하는 범위를 표현한다. 위는 인덱스만을 읽는 경우를 보여준다. 하지만 B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어와야 하는 경우도 많은데, 이 과정을 좀 더 자세히 살펴보자.

위 그림에서는 B-Tree 인덱스에서 루트와 브렌치 노드를 이용해 특점 검색(스캔) 시작 값을 가지고 있는 리프 노드를 검색하고, 그 지점부터 필요한 방향(오름차순 또는 내림차순)으로 인덱스를 읽어 나가는 과정이다. 가장 중요한 것은 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가자온다는 것이다. 이는 별도의 정렬 과정이 수반되는 것이 아니라 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다는 것이다.

 

또 위의 그림에서 중요한 것은 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다는 것이다. 이때 리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번식 실행된다. 그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다.

 

그리고인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통해 읽기보다는 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 되는 것이다.

 

인덱스 풀 스캔

 

인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 대표적으로 쿼리의 조건절에 사용된 칼럼의 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다. 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다. 인덱스 뿐만이 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이방식으로 처리되지 않는다.

먼저 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트(리프 노드 사이를 연결하는 세로로 그려진 두쌍씩의 화살표)를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라고 한다. 이 방식은 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다. 인덱스의 포함된 칼럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없기 때문이다.

 

루스 인덱스 스캔

 

중간마다 필요치 않은 인덱스 키값은 무시하고 다음으로 넘어가는 형태로 처리한다.

일반적으로 GROUP BY 또는 집합 함수 가운데 MAX()또는 MIN() 함수에 대한 최적화를 하는 경우에 사용된다,

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dep_no BETWEEN 'd002' AND 'd004'
GROUP By dept_no;

이 인덱스는 dept_no + emp_no의 값으로 정렬이 돼 있어서 dept_no 값만 읽으면 된다. 즉, 인덱스에서 WHERE조건을
만족하는 범위를 전체 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다. 그림을 보면 인덱스 리프 노드를 스캔하면서 불필요한 부분(dept_no는 바탕 색칠이 되었지만 emp_no는 바탕 색칠이 되지 않은 레코드)은 그냥 무시하고 필요한 부분(레코드 전체가 바탕 색칠된 레코드)만 읽었음을 알 수 있다.

5.3.5 다중 칼럼 인덱스

두 개 이상의 컬럼으로 구성된 인덱스를 다중 칼럼 인덱스라고 하며, 또한 2개 이상의 칼럼이 연결됐다고 해서 "Concatenated Index" 라고도 한다.

데이터 레코드 건수가 작은 경우에는 브렌치 노드가 없는 경우도 있을 수 있다. 하지만 루트 노드와 리프 노드는 항상 존재한다. 그림은 다중 칼럼 인덱스일 때 각 인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다는 것이다.

즉, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다느 것이다. 그림에서 칼럼이 2개 뿐이지만, 만약 칼럼이 4개인 인덱스를 생성한다면 세 번째 칼럼은 두 번째 칼럼에 의존해서 정렬되고 네 번째 칼럼은 다시 세 번째 칼럼에 의존해서 정렬된다는 것이다.

 

위의 그림에서 EMP_NO 값의 정렬 순서가 빠르다 하더라도 DEPT_NO 칼럼의 정렬 순서가 늦다면 인덱스의 뒤쪽에 위치한다.

그래서 EMP_NO 값이 "10003"인 레코드가 인덱스 리프 노드의 제일 마지막(하단)에 위치하는 것이다.

5.3.6 B-Tree 인덱스의 정렬 및 스캔 방향

인덱스 키값은 항상 오름차순으로만 정렬되지만 사실 그 인덱스를 거꾸로 끝에서부터 읽으면 내림차순으로 정렬된 인덱스로도 사용될 수 있다. 인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어 내는 실행 계획에 따라 결정된다.

 

인덱스의 정렬

 

MySQL은 인덱스를 구성하는 칼럼 단위로 정렬 방식(ASC와 DESC)을 혼합해서 생성하는 기능을 아직까지 지원하지 않는다.

CREATE INDEX ix_teamname_userscore ON employees (team_name_ASC, USER_score DESC);

MySQL에서 위와 같은 명령으로 인덱스를 생성하면 아무 문제 없이 인덱스 생성은 가능하지만, 실제로는 ASC나 DESC 키워드를 무시하고 모든 칼럼이 오름차순으로만 정렬된다. MySQL에서 ASC 또는 DESC 키워드는 앞으로 만들어질 버전에 대한 호환성을 위해 문법상으로만 제공하는 것이다.

 

그럼 인덱스를 구성하는 칼럼 가운데 ASC와 DESC를 혼합해서 만들 수 있을까? MySQL에서는 칼럼의 값을 역으로 변환해서 구현하는 것이 유일한 방법이다.

CREATE TABLE ranking (
    team_name VARCHAR (20),
    user_name VARCHAR (20),
    user_socre INT,
    ...
    INDEX ix_teamname_userscore(team_name, user_score)                 
);

ranking 테이블에서 team_name 칼럼은 ASC로 정렬하고 user_score는 높은 점수 순서대로 정렬해서 사용자를 조회하려면 어떻게 해야할까?

SELECT team_name, user_defined_type_name FROM ranking ORDER BY team_name ASC, user_score DESC;

위와 같이 쿼리를 사용하면 원하는 결과를 조회할 수 있지만, 이 쿼리는 실행의 최종 단계에서 레코드를 정렬하는 과정이 필요하므로 절대로 빠르게 처리할 수 없다. 그래서 이럴 떄는 user_score의 값을 역으로 변환해서 저장하는 것이 유일한 방법이다.

 

즉 user_socre 값을 그대로 음수로 만들어서 저장하느 ㄴ것이다. 그러면 다음과 같이 ORDER BY의 정렬 조건을 모두 ASC로 사용할 수 있게 되므로 별도의 정렬 작업 없이 인덱스를 읽기만 해도 정렬되어 출력되는 것이다.

SELECT  team_name, user_defined_type_name FROM ranking ORDER BY team_name, user_score;

인덱스 스캔 방향

SELECT * FREE employtees ORDER BY first_name DESC LIMIT 1;

인덱스는 항상 오름차순으로만 정렬돼 있지만 인덱스를 최소 값부터 읽으면 오름차순으로 값을 가져 올 수 있고, 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 MySQL 옵티마이저는 이미 알고 있다. 그래서 위의 쿼리는 인덱스를 역순으로 접근해 첫 번째 레코드만 읽으면 된다.

즉, 인덱스를 역순으로 정렬되게 할 수는 없지만 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.

SELECT * FROM employees WHERE first_name>='Anneke'
ORDER BY first_name ASC LIMIT 4;

SELECT  * FROM employees ORDER BY first_name DESC LIMIT 5;

첫 번째 쿼리는 first_name 칼럼에 정의된 인덱스를 이용해 "Anneke"라는 레코드를 찾은 후 오름차순으로 해당 인덱스를 읽으면서 4개의 레코드만 가져오면 아무런 비용을 들이지 않고도 원하는 정렬 효과를 얻을 수 있다.

 

두 번째 쿼리는 이와 반대로 employees 테이블의 first_name 칼럼에 정의된 인덱스를 역순으로 읽으면서 처음 다섯 개의 레코드만 가져오면 되는 것이다.

 

쿼리의 ORDER BY 처리나 MIN() 또는 MAX() 함수 드의 최적화가 필요한 경우, MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.

5.3.7 B-Tree 인덱스의 가용성과 효율성

쿼리의 WHERE 조건이나 GROUP BY 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다. 그래야만 쿼리의 조건을 최적화하거나, 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.

 

비교 조건의 종류와 효율성

 

다중 칼럼 인덱스에서 각칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교("=")인지 아니면 크다(">") 또는 작다("<")와 같은 범위 조건인지에 따라 각 인덱스 칼럼의 활용 형태가 달라지며 그 효율 또한 달라진다.

SELECT * FROM dept_emp
WHERE  dept_no = 'd002' AND emp_no >= 10114;

각각의 칼럼의 순서만 다른 2가지 케이스로 인덱스를 생성했다고 가정하자. 위 쿼리가 처리되는 동안 각 인덱스에 어떤 차이가 있을까?

• 케이스 A : dept_no + emp_no
• 케이스 B : emp_no _ dept_no

케이스 A의 경우 "dept_no = d002 AND emp_no>=1004"인 레코드를 찾고, 그 이후에는 dept_no가 'd002'가 아닐 떄까지 인덱스를 그냥 쭉 읽기만 함ㄴ 된다. 이 경우는 읽은 레코드가 모두 사용자가 원하는 결과임을 알 수 있다.
즉, 5건의 레코드를 찾는데 꼭 필요한 5번의 비교 작업만 수행한 것이므로 상당히 효율적으로 인덱스를 이용한 것이다.

케이스 B 우선 "emp_no>=10144" AND dept_no='d002'인 레코드를 찾고, 그 이후 모든 레코드에 대해 dept_no='d002'인지 비교하는과정을 거쳐야 한다. 이처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업을 "필터링"이라고도 한다.

 

최종적으로는 dpt_no='d002' 조건을 만족(필터링)하는 레코드 5건을 가져온다. 즉, 이 경우에는 5건의 레코드를 찾기 위해 7번의 비교 과정을 거친 것이다.

왜 이런 현상이 발생해을까?

 

이유는 다중 칼럼 인덱스의 정렬 방식(인덱스 N번째 키값은 N-1번째 키값에 대해서 다시 정렬됨)떄문이다. 케이스 A 인덱스에서 2번째 칼럼인 emp_no는 비교 작업의 범위를 좁히는데 도움을 준다. 하지만 케이스 B 인덱스에서 2번째 칼럼인 dept_no는 비교 작업의 범이를 좁히는 데 아무런 도움을 주지 못하고, 단지 쿼리의 조건에 맞는지 검사하는 용도로만 사용됐다.

 

케이스 A의 두 조건과 같이 작업의 범위를 결정하는 조건을 작업 범위 결정 조건이라 하고, 케이스 B의 dept_no='d002'" 조건과 같이 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건을 **"필터링 조건 또는 체크 조건 이라고 표현한다.

 

작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성능을 높이지만 체크 조건은 많다고 해서(최종적으로 가져오는 레코드는 작게 만들지 몰라도) 쿼리의 성능을 높이지는 모한다. 오히려 쿼리 실행을 더 느리게 만들 때가 많다.

 

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다. 여기서 왼쪽이라 함은 하나의 칼럼 내에서뿐만 아니라 다중 컬럼 인덱스의 칼럼에 대해서도 함께 적용된다.

 

그림에서 정렬만 표현했지만, 사실은 이 정렬이 빠른 검색의 전제 조건이다. 즉 하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다. 또한 다중 칼럼 인덱스에서도 왼쪽 칼럼의 값을 모르면 레인지 스캔을 사용할 수 없다.

 

 

케이스 A의 인덱스가 지정된 employees 테이블에 대해 다음과 같은 쿼리가 어떻게 실행되는지 한 번 살펴보자.

SELECT * FROM employees WHERE first_name LIKE '%mer'

이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수는 없다. 그 이유는 first_name 칼럼에 저장된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하는데 조건절에 주어진 상수 값("%mer")에는 왼쪽 부분이 고정되지 않았기 때문이다. 따라서 정렬 우선순위가 낮은 뒤부분의 값만으로는 왼쪽 기준 정렬 기반의 인덱스인 B-Tree에서는 인덱스의 효과를 얻을 수 없다.

 

케이스 B의 인덱스가 지정된 dept_emp 테이블에 대해 다음 쿼리가 어떻게 실행되는지 한 번 살펴보자.

SELECT * FROM dept_emp WHERE emp_no>=10144;

인덱스가 칼럼 순서대로 생성돼 있다면 인덱스의 선행 칼럼인 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")
• LIKE '%??' (앞부분 비교가 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
• 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우 (ex. SUBSTRING, DAYOFMONTH)
• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
• 데이터 타입이 서로 다른 비교(인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
• 문자열 데이터 타입의 콜레이션이 다른 경우(UTF8, EUCKR)

그리고 MySQL에서는 다른 DBMS와 달리 NULL 값도 인덱스로 관리되기 때문에 IS NULL 조건도 작업 범위 결정 조건으로 활용될 수 있다. 다중 칼럼으로 만들어진 인덱스는 어떤 조건에서 사용될 수 있고, 어떤 경우에는 절대 사용될 수 없는지 살펴보자.

INDEX idx_test ( col_1, col_2, ... , col_n)

작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우

• column_1 칼럼에 대한 조건이 없는 경우
• column_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우

작업의 범위 결정 조건으로 인덱스를 사용하는경우(i는 2보다 크고 n보다 작은 임의의 값을 의미)

• column_1 ~ column_(i-1) 칼럼까지 Equal 형태("=" 또는 "IN")로 비교
• column_i 칼럼에 대해 다음연산자 중 하나로 비교
  • Equal("=" 또는 "IN")
  • 크다 작다 형태(">" 또는 "<")
  • LIKE로 좌측 일치 패턴(LIKE '승환%')

---

참고 문헌 :

• 개발자외 DBA를 위한 Real MySQL / 이성민
• 뚜벅이 강군