MySQL(4.1)

오늘 배운 것

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

Read와 달리 insert, delete 의 update연산 실행 시, 인덱스키 추가나 삭제가 일어난다. 이 부분을 알아두면 쿼리 성능을 쉽게 예측할 수 있다.

8.3.2.1 인덱스 키 추가 (add) - 다소 신경쓸게 있음.

새로운 값이 추가되어 인덱스에 새로운 키 값을 추가할 때, 테이블의 스토리지 엔진에 따라 즉시 인덱스에 저장 될 수도, 아닐 수도 있다.

Btree 특성상 인덱스가 추가될때, 단순히 append가 아니라 적절한 위치를 찾고 리프노드에 저장하는데, 이때 split이 발생할 수도 있다.

인덱스 추가로 인해 Btree연산 발생으로, INSERT, UPDATE문장이 어떤 양향을 받을지 궁금해지는데, 명확하게 대답하려면

  1. 테이블의 칼럼 수
  2. 칼럼의 크기
  3. 인덱스 칼럼의 특성 을 확인해야 한다.

대략적으로 계산하려면 테이블에 레코드를 추가하는 작업비용을 1이라고 한다면, 해당 테이블의 인덱스에 키를 추가하는 작업비용을 1.5정도로 예측하는 것이다. 일반적으로 테이블에 인덱스가 3개(테이블의 모든 인덱스가 BTree라는 가정)가 있다면 이때 테이블에 인덱스가 하나도 없는 경우는 작업비용이 1이고, 3개인 경우는 5.5(1.5*3+1)정도로 예측한다.

중요한것은, 이 비용의 대부분이 메모리,CPU에서 처리하는게 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야해서 걸리는 시간이라는 점이다.

MyISAM이나 MEMORY 테이블에서는 INSERT가 실행되면 즉시 새로운 키 값을 BTree인덱스에 변경한다. 그러나 InnoDB에서는 조금 더 지능적으로 처리해서, 필요하다면 인덱스 키 추가를 지연시켜 나중에 처리할 수도 있다.

하지만 PK나 Unique 인덱스의 경우 중복체크가 필요하므로 즉시 Btree에 추가하거나 삭제한다.

InnoDB의 체인지 버퍼에 대해서는 4.2.10을 참고해라

8.3.2.2 인덱스 키 삭제 (delete) - 다소 간단

키값 삭제는 간단하다. 해당 키값이 저장된 인덱스의 리프노드를 찾아서 그냥 삭제마크만 한다. 이렇게 삭제마킹된 인덱스 키 공간은 계속 두고 재활용할수있다.

이 마킹작업또한 Disk Write가 요구된다. 5.5 이상의 MySQL InnoDB에서는 이 작업도 버퍼링되어 지연처리 될 수 있다. 지연된 인덱스 키 삭제 또한 사용자에게는 특별한 영향없이 MySQL서버가 내부적으로 처리하므로 걱정할 것은 없다.

MyISAM, MEMORY 는 체인지 버퍼와 같은 기능이 없어서 인덱스 키 삭제가 완료된 후 쿼리 실행이 완료된다.

8.3.2.3 인덱스 키 변경 (update) - delete 이후 add

인덱스의 키 값은 그 값에따라 저장될 리프노드의 위치가 결정되므로 BTree의 키 값이 변결되는 경우, 단순히 인덱스 상의 키 값만 변경하는 것은 불가능하다 -> 그거만 딸랑 바꾸면 될게 아니라 리프노드간의 순서라던지, Balance고려가 필요

그래서 삭제하고 add하는 형태로 구현된다.

이것도 마찬가지로 InnoDB에서 체인지 버퍼를 활용하여 지연될 수 있다.

인덱스 관리 비용을 감수하는 이유가 이 검색을 위한거다. SELECT, UPDATE, DELETE 를 처리하기위해 항상 해당 레코드를 먼저 검색해야 할때 항상 인덱스 트리 탐색이 일어난다.

100%일치, 혹은 값의 앞부분(LEft-most part)만 일치하는 경우에 사용할 수 있다. 부등호 비교조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.

인덱스를 이용한 검색에서 중요한것은

인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 BTree의 빠른검색을 사용할 수 없다는 것이다.

이미 변형된 값은 BTree인덱스에 존재하는 값이 아니다. 따라서

함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업 은 BTree의 장점을 이용할 수 없다.

InnoDB에서 인덱스는 더 특별한 의미가 있다. 레코드 lock이나, 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어있다.

따라서 UPDATE나 DELETE문장이 실행될때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 테이블의 모든 레코드를 잠글수도 있다. 그래서 InnoDB에서 인덱스가 더 잘 설계되어야 한다.

인덱스가 없으면 풀스캔 하는데, 이 과정에서 레코드들을 잠근다는 의미

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

8.3.3.1 인덱스 키 값의 크기

InnoDB에서 디스크에 데이터를 저장하는 가장 기본 단위를 Page 또는 Block이라고 한다. 디스크의 모든 RW 작업의 최소 작업단위가 된다. 특히 Page는 InnoDB의 버퍼 풀에서 데이터를 버퍼링 하는 기본 단위이기도 하다. 인덱스도 결국은 Page단위로 관리되며, 앞서 Btree 인덱스의 루트, 브랜치, 리프노드를 구분한 기준이 Page단위이다.

B트리인덱스구조

일반적으로 DBMS의 Btree의 자식 노드의 개수는 가변적이다. 보통 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다. innodb_page_size 시스템 변수를 이용해 4KB~64KB사이의 값을 선택할 수 있으나, 기본은 16KB이다.

인덱스페이지

자식노드 주소라는 것은 여러가지 복합적인 정보가 담긴 영역이고 대략 6바이트에서 12바이트까지 다양하다. 여기서는 편의상 12바이트 정도로 했다.

이 경우, 하나의 인덱스 페이지에 몇개의 키를 저장할 수 있을까? 약 161024(KB니까)/(16+12)=585개 저장할 수 있다. 최종적으로 이 경우는 자식노드를 585개를 가질 수 있는 Btree가 되는것이다. 그럼 인덱스 키 값이 커지면? 예를들어 32바이트로 늘어났다고 하면, 161024/(32+12)=372, SELECT쿼리가 레코드 500개를 읽어야 한다면 인덱스 페이지 한번으로 해결될 수 있으나 후자는 2번이상 디스크로부터 읽어야 한다.

또한 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 것을 의미한다. 하지만 인덱스를 캐시해두는 InnoDB의 버퍼풀이나 MyISAM의 키 캐시 영역은 크기가 제한적이므로 인덱스가 커지면 메모리에 캐시해둘수있는 레코드 수는 줄어든다.

8.3.3.2 B-Tree 깊이

사실 직접 제어할 방법은 없고, 깊어지면 어떻게 되는지만 보자. 길이가 3일경우, 최대 2억(585585585) 정도의 키 값을 담을 수 있으나 32바이트로 늘어나면 5천만(372372372) 정도만 담을 수 있다. Btree의 깊이는 값을 검색할때

몇번이나 랜덤하게 디스크를 읽어야 하는지 와 직결되는 문제다.

그러나 일반적으로 깊이가 5 이상 내려가는 경우는 별로 없다.

8.3.3.3 선택도 Selectivity (기수성 Cardinatlity)

거의 같은 의미로 사용된다. 모든 인덱스 키 값 가운데 유니크한 값의 수 를 의미한다.

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

대상: 주민등록번호, 이메일, 전화번호

이유: 값 대부분이 고유해서, 인덱스로 조회 시 결과가 1개 혹은 아주 소수로 좁혀짐.

낮은 카디널리티 (인덱스 👎)

대상: 성별, 주문 완료 여부(Y/N), 회원 등급

이유: 값의 종류가 몇 개 없어, 인덱스로 조회해도 전체 데이터의 상당량을 스캔해야 함. MySQL 옵티마이저가 "이럴 바엔 그냥 테이블 전체를 읽는 게 낫겠다"고 판단하여 인덱스를 안 쓸 확률이 높음.

8.3.3.4 읽어야 하는 레코드의 건수

인덱스를 통해 읽는것은 바로 테이블의 레코드를 읽는것보다 비용이 든다. 테이블에 100만건의 레코드가 있는데, 50만건을 읽어야 하는 쿼리가 있다고 하자. 이 작업은 전체 테이블을 모두 읽어서 필요없는 50만건을 버리는 것이 효율적일지, 인덱스를 통해 필요한 50만건만 읽어오는 것이 효율적인지 판단해야 한다.

인덱스를 통한 손익 분기점을 파악해야 하는데, 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 1건을 읽는게 테이블에서 직접 1건을 읽는것 보다 4~5배 더 비용이 드는 것으로 판단한다.

즉 인덱스를 통해 읽어야할 레코드의 건수(물론 옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지않고 테이블을 모두 직접 읽어서 필터링 하는게 더 효율적이다. 따라서 이 경우, 인덱스를 써서 필요한것만 읽는게 아니라, 전체를 읽고 버리는게 좋다.

8.3.4 B-Tree 인덱스를 통한 데이터 읽기 (스캔)

어떤 경우에 인덱스를 사용하게 유도할지, 또는 사용하게 못할지 판단하려면 스토리지 엔진들이 어떻게 실제 레코드를 읽어내는지 알아야 한다.

8.3.4.1 인덱스 레인지 스캔 (범위 탐색)

가장 대표적이고, 나머지 2가지보다 빠른 방법이다. 보통 인덱스를 통해 레코드를 한 건 읽는 경우 와 한 건 이상 을 읽는 경우를 다른 이름으로 구분하지만, 이번절에선 모두 같은이름으로 부르겠다.

상세한 내용은 10장 실행계획 에 있다.

인덱스레인지스캔

사진에 나온것처럼 시작 위치를 찾으면 리프에서 쭉 내려가고, 없으면 다시 올라가서 찾고 그런다. 위 그림은 실제 인덱스 만을 읽는 경우를 보여준다. 그러나 리프노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어와야 하는 경우도 많은데, 이 과정은 아래와 같다.

인덱스레인지스캔

위아래든 아래위든 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다. 별도의 정렬과정이 수반되는 것이 아니라 인덱스 자체의 정렬특성때문에 그렇게된다.

또, 인덱스의 리프에서 검색조건에 일치하는 것들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다. 이때 리프노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 이때 한건단위로 랜덤IO가 일어난다. 따라서 앞서 20~25% 원칙이 적용된다.

인덱스 레인지 스캔은 다음과 같은 3가지 과정을 거친다.

  1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. -> 인덱스 탐색(index search)
  2. 1번에서 탐색한 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔(index scan)이라고 한다.(1번과 합쳐서 스캔이라고도 한다.)
  3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

쿼리가 필요로 하는 데이터에 따라 3번과정은 안할수도 있는데, 이를 커버링 인덱스 라고 한다.

커버링 인덱스로 처리되는 쿼리는 디스크 레코드를 읽지 않아도 되므로 랜덤읽기가 상당히 줄어들고 성능이 증가한다.

8.3.4.2 인덱스 풀 스캔

인덱스를 이용하긴 하지만, 첨부터 끝까지 읽는것. 대표적으로 쿼리의 조건절에서 사용된 칼럼이 인덱스의 첫번째 칼럼이 아닌경우 인덱스 풀스캔방식이 사용된다. 예를들어, 인덱스는 (A,B,C) 칼럼의 순서대로 만들어져 있지만, 쿼리의 조건절은 B, C로 검색하는 경우이다.

일반적으로 인덱스를 이용한다고 하면 레인지스캔이나 loose 인덱스 스캔을 의미한다. 역으로 테이블 전체를 읽거나 풀스캔 하면 “인덱스를 사용하지 못한다” 혹은 “인덱스를 효율적으로 사용하지 못한다” 를 의미한다.

인덱스풀스캔

인덱스의 크기가 테이블보다는 작으므로 테이블 풀스캔보다는 인덱스 풀스캔이 낫다.

쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 이 방식이 사용된다. 인덱스 뿐만 아니라 데이터까지 읽어야 한다면 절대 이 방식으로 처리되지 않는다.

8.3.4.3 Loose 인덱스 스캔

오라클에서는 “인덱스 스킵스캔” 이라고 하는거랑 기능, 작동방식은 비슷하지만 MySQL에서는 Loose 인덱스 스캔 이라고 한다.

앞서 2가지는 이와 반대로 Tight 인덱스 스캔 이라고 한다.

인덱스루스

레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 SKIP하고 지나간다.

일반적으로 GROUP BY 또는 집합함수중 MAX, Min같은 함수에 대해 최적화 하는 경우에 사용된다.

예를들어,

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dep_no BETWEEN 'doo2' AND 'doo4'
GROUP BY dept_no;

이 쿼리에서 사용된 테이블에서 dept_no와 emp_no 두개의 칼럼으로 인덱스가 생성되어있다. 또한 이 인덱스는(dept_no, emp_no)조합으로 정렬까지 돼있어서 위 그림처럼 읽고있다.

루스 인덱스 스캔을 사용하려면 여러 조건을 만족해야 하는데, 10장 실행계획에서 자세히 언급하겠다.

8.3.4.4 인덱스 스킵 스캔

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬되어 있다 라는 것. 이로인해 인덱스를 구성하는 칼럼의 순서가 매우 중요하다.

앞서 Loose와 다른점은 다음과 같다.

>Loose는 `GROUPBY` 를 처리하끼 위햬 인덱스를 사용하는 경우에만 사용할 수 있다.
>이거는 WHERE 조건절의 검색을 위해 사용 가능하도록 용도가 넓어졌다.

인덱스스킵스캔

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요하다고 강조했다. 예를 들어 employees 테이블에 다음과 같은 인덱스를 생성해보자.

mysql> ALTER TABLE employees
       ADD INDEX ix_gender_birthdate (gender, birth_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';

이제 MySQL 8.0 버전부터 도입된 인덱스 스킵 스캔을 활성화하고, 동일 쿼리의 실행 계획을 다시 확인해보자.

mysql> SET optimizer_switch='skip_scan=on';

mysql> EXPLAIN
       SELECT gender, birth_date
       FROM employees
       WHERE birth_date>='1965-02-01';
+----+-------------+-----------+------------+-------+---------------------+------+---------+------+----------+------------------------------+
| id | select_type | table     | partitions | type  | possible_keys       | key                 | key_len | ref  | rows     | Extra                        |
+----+-------------+-----------+------------+-------+---------------------+------+---------+------+----------+------------------------------+
|  1 | SIMPLE      | employees | NULL       | range | ix_gender_birthdate | ix_gender_birthdate | 5       | NULL |   149955 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+---------------------+------+---------+------+----------+------------------------------+

이 쿼리는 WHERE 절의 실행 계획에서 type 칼럼이 “range”로 표시됐는데, 이는 인덱스에서 꼭 필요한 부분만 읽었다는 것을 의미한다. 그리고 실행 계획의 Extra 칼럼에 “Using index for skip scan”이라는 문구가 포함됐는데, 이는 ix_gender_birthdate 인덱스에 대해 인덱스 스킵 스캔을 활용해 주어진 조건의 레코드만 효율적으로 조회했다는 것을 의미한다. MySQL 옵티마이저는 우선 gender 칼럼의 유니크한 값을 모두 조회해 주어진 birth_date 칼럼의 조건에 추가해서 쿼리를 다시 실행하는 형태로 처리한다. 다음의 그림 8.12는 인덱스 스킵 스캔이 어떻게 처리되는지를 보여준다.

그래서 위의 쿼리 중에서 gender 칼럼과 birth_date 칼럼의 조건을 모두 가진 두 번째 쿼리는 효율적으로 사용할 수 있지만 gender 칼럼에 대한 비교 조건이 없는 첫 번째 쿼리는 인덱스를 사용할 수가 없었다. 주로 이런 경우에는 birth_date 칼럼부터 시작하는 인덱스를 새로 생성해야만 했다.

MySQL 8.0부터는 옵티마이저가 gender 칼럼을 건너뛰어서 birth_date 칼럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔(Index skip scan) 기능이 도입됐다. 물론 MySQL 8.0 이전 버전에서도 인덱스 스킵 스캔과 비슷한 최적화를 수행하는 루스 인덱스 스캔(Loose index scan)이라는 기능이 있었지만 루스 인덱스 스캔은 GROUP BY 작업을 처리하기 위해 인덱스를 사용하는 경우에만 적용할 수 있었다. 하지만 MySQL 8.0에 도입된 인덱스 스킵 스캔은 WHERE 조건절의 검색을 위해 사용하도록 용도가 훨씬 넓어진 것이다.

우선 인덱스 스킵 스캔 기능이 비활성화되고, MySQL 8.0 이전 버전에서 어떤 실행 계획으로 처리됐는지 한번 살펴보자.

mysql> SET optimizer_switch='skip_scan=off';

mysql> EXPLAIN
       SELECT gender, birth_date
       FROM employees
       WHERE birth_date>='1965-02-01';
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+--------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys       | key                 | key_len | ref  | rows   | Extra                    |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | ix_gender_birthdate | ix_gender_birthdate | 5       | NULL | 299910 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------------+---------------------+---------+------+--------+--------------------------+

위의 쿼리는 WHERE 조건절에 gender 칼럼에 대한 조건이 없이 birth_date 칼럼의 비교 조건만 가지고 있기 때문에 ix_gender_birthdate 인덱스를 효율적으로 이용할 수 없다. 여기서 인덱스를 “효율적으로” 이용한다는 표현과 동일한 의미로, 인덱스에서 꼭 필요한 부분만 접근하는 것을 의미한다. 위의 실행 계획에서 type 칼럼이 “index”라고 표시된 것은 인덱스를 처음부터 끝까지 모두 읽었다(인덱스 풀 스캔)는 의미이므로 인덱스를 비효율적으로 사용한 것이다. 이 예제 쿼리는 인덱스에 있는 gender 칼럼과 birth_date 칼럼만으로도 처리를 완료할 수 있기 때문에 ix_gender_birthdate 인덱스를 풀 스캔한 것이다. 만약 예제 쿼리가 employees 테이블의 모든 칼럼을 가져와야 했다면 테이블 풀 스캔을 실행했을 것이다.

이제 MySQL 8.0 버전부터 도입된 인덱스 스킵 스캔을 활성화하고, 동일 쿼리의 실행 계획을 다시 확인해보자.

mysql> SET optimizer_switch='skip_scan=on';

mysql> EXPLAIN
       SELECT gender, birth_date
       FROM employees
       WHERE birth_date>='1965-02-01';
+----+-------------+-----------+------------+-------+---------------------+------+---------+------+----------+------------------------------+
| id | select_type | table     | partitions | type  | possible_keys       | key                 | key_len | ref  | rows     | Extra                        |
+----+-------------+-----------+------------+-------+---------------------+------+---------+------+----------+------------------------------+
|  1 | SIMPLE      | employees | NULL       | range | ix_gender_birthdate | ix_gender_birthdate | 5       | NULL |   149955 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+---------------------+------+---------+------+----------+------------------------------+

이 쿼리는 WHERE 절의 실행 계획에서 type 칼럼이 “range”로 표시됐는데, 이는 인덱스에서 꼭 필요한 부분만 읽었다는 것을 의미한다. 그리고 실행 계획의 Extra 칼럼에 “Using index for skip scan”이라는 문구가 포함됐는데, 이는 ix_gender_birthdate 인덱스에 대해 인덱스 스킵 스캔을 활용해 주어진 조건의 레코드만 효율적으로 조회했다는 것을 의미한다. MySQL 옵티마이저는 우선 gender 칼럼의 유니크한 값을 모두 조회해 주어진 birth_date 칼럼의 조건에 추가해서 쿼리를 다시 실행하는 형태로 처리한다. 다음의 그림 8.12는 인덱스 스킵 스캔이 어떻게 처리되는지를 보여준다.

8.3.5 다중 칼럼 인덱스

지금까지 살펴본 인덱스들은 모두 1개의 칼럼만 포함된 인덱스였다. 하지만 실제 서비스에서는 2개 이상의 칼럼을 포함하는 인덱스가 더 많이 사용된다.

다중칼럼인덱스

중요한 것은, 두번째 칼럼은 첫번째 칼럼에 의존해서 정렬되어 있다는 것.

다중칼럼 인덱스에서는 인덱스 내 각 칼럼의 위치가 상당히 중요하다.

results matching ""

    No results matching ""