K-DEVCON 대전 개발자 스터디에서 Real Mysql 책으로 스터디를 진행해보기로 했다.

발표하면서 준비한 내용을 블로그로도 옮겨보려고 한다.

이 글의 내용은 Mysql 8.0 에서 InnoDB 를 기준으로 정리되었다. 이 글은 정리글이기에 생략이 있으며, 책에서는 이전 버전이나 다른 스토리지 엔진에 대해서도 다루기도 하고 더 자세한 내용들을 다루고 있다. 책의 구성이 이미 안다는것을 전제하에 진행된 부분들도 있어 해당 부분에 대해서 보충설명을 넣기도 하였다.

인덱스

인덱스는 책의 맨 끝에 있는 찾아보기(또는 “색인”)으로 설명할 수 있다.

책의 마지막에 있는 “찾아보기” 를 인덱스에 비유했을 때, 책의 내용은 데이터 파일 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 해당한다고 볼 수 있다.

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분이다. MySQL에서 사용 가능한 인덱스의 종류 및 특성에 대해서 알아본다.

디스크 읽기 방식

데이터베이스의 성능 튜닝은 어떻게 디스크 I/O 를 줄이느냐가 관건일 때가 상당히 많다.

디스크 I/O는 2가지로 분류할 수 있다.

• 랜덤 I/O (Random I/O)
• 순차 I/O (Sequence I/O)

이미지 출처

랜덤 I/O (Random I/O)

랜덤 I/O 는 하드디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미한다.

이미지 출처

disk는 회전하고 arm은 앞뒤로 움직여서 원하는 데이터를 읽어온다.

쿼리 튜닝 방향

사실 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O 로 바꿔서 실행할 방법은 그다지 많지 않다. 일반적으로 쿼리를 튜닝하는것은 랜덤 I/O 자체를 줄여주는 것이 목적이라고 할 수 있다.

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀테이블 스캔은 순차 I/O를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀테이블 스캔을 사용하도록 유도할때도 있다. 이는 순차 I/O가 랜덤 I/O보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문이다.

HDD 와 SSD

순차 I/O 에서는 SSD가 하드디스크보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다. 하지만 랜덤 I/O 에서는 SSD가 하드디스크 에 비해 훨씬 빠르다.

인덱스의 특징

DBMS의 인덱스는 저장되는 칼럼의 값을 이용해 항상 정렬된 상태를 유지한다.

데이터 파일은 저장된 순서대로 별도의 정렬없이 그대로 저장해둔다. 데이터가 저장될 때 마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬돼있어서 아주 빨리 원하는 값을 찾아올 수 있다.

데이터의 저장 (INSERT,UPDATE,DELETE) 성능을 희생하고, 그 대신 데이터의 읽기 (SELECT) 속도를 높힌다.

SELECT 쿼리 문의 WHERE 조건절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다. 따라서 테이블의 인덱스를 하나 더 추가할지 말지는 아래 항목을 고려하여 결정해야 한다.

• 데이터의 저장 속도를 어디까지 희생할 수 있는지
• 읽기 속도를 얼마나 더 빠르게 만들어야 하는지

인덱스의 종류 (역할에 따른 분류)

• 프라이머리 키
• 세컨더리 키 (보조 키)

프라이머리 키 (primary key)

• 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스
• 해당 레코드를 식별할 수 있는 기준값 (식별자)
• Unique, Not Null

세컨더리 키 (secondary key) (보조 키)

• 프라이머리 키를 제외한 모든 인덱스

B-Tree 알고리즘

• 데이터베이스의 인덱싱 알고리즘 중 가장 근본 알고리즘
• 실제 데이터베이스에서는 B-Tree 기반의 변형 알고리즘을 사용 (B+-Tree, B*-Tree …)

주요 특징

• 항상 정렬된 상태를 유지한다.
• 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱한다.

여기서 B는 Balanced 의 앞글자다. (Binary 가 아님에 주의)

비교 - Hash Index 알고리즘

• 해시값을 계산해서 인덱싱
• 매우 빠른 검색을 지원 (O(1))

매우 빠른데 왜 안쓸까?

값을 변형해서 인덱싱하므로 값의 일부만 검색하거나 범위를 검색할 때에는 사용할 수 없다. (RDBMS에는 부적합)

B-Tree 구조 및 특성

• 루트 노드: 최상위에 있는 하나의 노드
• 리프 노드: 가장 하위에 있는 노드들
• 브랜치 노드: 루트 노드와 리프 노드가 아닌 나머지 노드

인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다.

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있다.

B-Tree 인덱스 키 추가

저장될 키 값을 이용해 B-Tree 상 의 적절한 위치를 검색한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 리프 노드에 저장한다.

리프노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드를 분리 (Split)한다. 이러한 작업 탓에 B-Tree는 상대적으로 쓰기 작업에 비용이 많이 든다.

새로운 키 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있다. (백그라운드에서 지능적으로 처리) 프라이머리 키 나 유니크 키의 경우 중복 체크가 필요하기 때문에 즉시 처리한다.

B-Tree 인덱스 키 삭제

B-Tree의 키 값이 삭제되는 경우는 상당히 간단하다. 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다. 이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용 할 수 있다. 바로바로 리밸런싱을 하지 않고, 모아서 진행한다 (체인지 버퍼 사용)

B-Tree 인덱스 키 변경

키 값에 따라 위치가 결정되기 때문에 단순히 인덱스 상의 키 값만 변경하는 것은 불가능하다. 따라서 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리한다.

B-Tree 탐색

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

인덱스 키 검색은 루트 노드부터 시작해 브랜치 노드를 거쳐 리프 노드까지 이동하면서 비교 작업을 수행한다. 이 과정을 “트리탐색” 이라고 한다.

인덱스 비교 작업

• 가능한 비교 작업
  • =, >, >=, <, <=, BETWEEN
  • 앞 부분 비교 (like ‘XXXX%’)

뒷 부분 검색은 인덱스를 이용해 비교할 수 없다.

Lock 과 인덱스

InnoDB의 경우 레코드 잠금이나 넥스트 키락 (갭락) 이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠 그는 방식으로 구현돼있다.

UPDATE 나 DELETE 문이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 테이블의 모든 레코드를 잠글 수도 있다.

따라서 적절히 Index를 설정해 주는것이 중요하다.

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

• 인덱스 키 값의 크기
• B-Tree 깊이
• 선택도(기수성)
• 읽어야 하는 레코드의 건수

B-Tree 인덱스 키 값의 크기에 따른 변화

B-Tree는 자식 노드의 개수가 가변적인 구조다. MySQL의 자식 노드 수는 페이지 크기와 키 값의 크기에 따라 결정된다. 키 값의 길이가 길어질수록 한 페이지에 들어가는 노드의 수는 줄어든다.

예시

MySQL 에서 페이지의 기본 사이즈는 16KB다

인덱스의 키가 16B, 자식 노드 주소가 12B 라고 가정했을 때 하나의 페이지에는 585 개의 노드를 가질 수 있다. (16 * 1024 / (16 + 12) = 약 585)

데이터가 585개 미만 이라면 인덱스 페이지 1개로 해결되지만, 그렇지 않다면 최소한 2번 디스크를 읽어야 한다.

B-Tree 의 높이 (깊이)

B-Tree 의 높이는 데이터를 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결된다. 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다

키의 크기에 따라 노드의 개수가 결정되고, 노드의 갯수가 결정되면 높이에 따른 Node의 수가 정해지게 된다.

위에서 든 예시 그대로 페이지의 사이즈는 16KB, 자식 노드 주소는 12B 라고 가정하면 다음과 같은 계산 결과가 나온다.

• 키 값이 16바이트, 깊이가 3일 경우
  • 약 2억(585 * 585 * 585) 개의 노드로 트리 구성
• 키 값이 32바이트, 깊이가 3일 경우
  • 약 5천만(372 * 372 * 372) 개의 노드로 트리 구성

기수성 (Cardinality), 선택도 (Selectivity)

모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아진다. 인덱스는 기수성이 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

선택도는 다음과 같은 공식에 따른다

Selectivity = Cardinality / Total Number Of Records

예시

tb_test 테이블에 country 라는 칼럼과 city 라는 칼럼이 있다고 해보자. 이 테이블의 전체 레코드 건 수는 1만 건이며, country 칼럼에 인덱스가 생성된 상태이다.

이러한 세팅에서 다음과 같이 두가지 케이스를 고려해본다.

• 케이스A : country 칼럼의 유니크한 값의 개수가 10개
• 케이스B : country 칼럼의 유니크한 값의 개수가 1,000개

그리고 두가지 케이스 에서 아래의 쿼리를 하였을 때 1개의 결과 값을 얻었다고 가정한다.

SELECT *
FROM tb_test
WHERE country='KOREA' AND city='SEOUL'

이 때 어떤 케이스가 더 인덱스를 잘 활용한 것일까?

정답은 B이다. 그 이유는 다음과 같다.

• 케이스A : country 칼럼의 유니크한 값의 개수가 10개이기 때문에 쓸모없는 값을 평균적으로 999개 더 읽음. • 케이스B : country 칼럼의 유니크한 값의 개수가 1,000개이기 때문에 쓸모없는 값을 평균적으로 9개 더 읽음.

한 스터디 회원분께서 들어준 예시가 이해하기 재밌어서 공유를 해본다. 온 세상에 10 종류의 옷이 있다고 가정했을 때, 만 명 중에서 나랑 같은 옷을 입은 사람을 마주칠 가능성과 온 세상에 1000 종류의 옷이 있다고 가정했을 때, 만 명 중에서 나랑 같은 옷을 입은 사람을 마주칠 가능성 으로 생각해보시라 했더니 쉽게 이해해주셨다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것 보다 높은 비용이 드는 작업이다. → 인덱스를 이용한 읽기의 손의분기점이 얼마인지 판단 필요

전체 테이블 레코드의 20~25% 를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 (필터링) 방식으로 처리하는 것이 효율적 (테이블 풀 스캔)

B-Tree 인덱스를 통한 데이터 읽기 방법

인덱스 레인지 스캔

인덱스를 통해 레코드를 한 건 이상을 읽는 방식. 검색해야할 인덱스의 범위가 결정됐을 때 사용된다.

아래의 쿼리를 한다고 하였을 때 다음과 같이 동작하게 된다. (* first_name 컬럼이 인덱스이다.)

SELECT *
FROM employees
WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

인덱스 레인지 스캔은 크게 세 가지 과정을 거친다.

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

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

커버링 인덱스

커버링 인덱스란 인덱스만으로도 쿼리에서 필요한 모든 데이터를 충족할 수 있는 인덱스를 말한다. 이 경우 추가적인 I/O가 필요하지 않다.

인덱스 풀 스캔

인덱스를 사용하지만 인덱스의 처음부터 끝까지 모두 읽는 방식이다.

대표적으로 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.

예를들어, 인덱스는(A, B, C) 칼럼의 순서로 만들어졌지만 쿼리의 조건은 B칼럼이나 C칼럼으로 검색하는 경우다.

루즈 인덱스 스캔

루즈 (Loose) 인덱스 스캔은 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.

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

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

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_no BETWEEN 'd0002' AND 'd0004'
GROUP BY dept_no

각 그룹별로 MIN 값만 찾으면 되기 떄문에 나머지 값은 스캔할 필요가 없어서 건너뛴다고 생각하면 이해하기 쉬울 것 같다.

인덱스 스킵 스캔

MySQL 8.0버전부터 도입된 기능이다. 옵티마이저가 사용되지 않는 인덱스 컬럼을 건너뛰고 인덱스 검색이 가능하게 해준다.

사용 조건은 다음과 같다. (옵티마이저가 결정한다.)

• WHERE 조건 절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
• 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함 (커버링 인덱스)

INDEX ix_gender_birthdate (gender, birth_date);

-- // 인덱스를 사용할 수 있는 쿼리
SELECT * FROM employees WHERE gender='M' AND birth_date>='1965-02-01';
-- // 인덱스를 못 사용했던 쿼리
SELECT * FROM employees WHERE birth_date>='1965-02-01';

* 인덱스 스킵 스캔을 타지 못하는 상황이였다면 birth-date 칼럼부터 시작하는 인덱스를 새로 생성해야 했다.

다중 컬럼 인덱스

실제 서비스용 데이터 베이스에서는 2개 이상의 칼럼을 포함하는 인덱스가 더 많이 사용된다.

여러가지 이름으로 불린다.

• 다중 칼럼 (Multi-column) 인덱스
• 복합 (Composite) 인덱스
• 결합 (Concatenated) 인덱스

인덱스 컬럼이 여러개 일 경우, 인덱스의 칼럼이 이전 인덱스 칼럼에 의존해서 정렬된다. 따라서 인덱스 내에서 각 칼럼의 위치(순서)가 중요하다.

인덱스 정렬

인덱스 생성시 정렬 방향도 정할 수 있다.

CREATE INDEX ix_teamname_userscore
ON employees (team_name ASC, user_score DESC);

인덱스 스캔 방향

인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 결정한다.

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

SELECT *
FROM employees
ORDER BY first_name DESC
LIMIT 5;

인덱스 스캔 방향에 따른 성능 비교

인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느리다.

페이지 간의 연결은 양방향으로 되어있지만 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조이기 때문이다.

인덱스를 사용할 수 없는 경우

• 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 속성의 스토어드 함수가 비교 조건에 사용된 경우
  • .. WHERE column = deterministic_function()
• 데이터 타입이 서로 다른 비교 (인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
  • .. WHERE char_column = 10
• 문자열 데이터 타입의 콜레이션이 다른 경우
  • .. WHERE utf8bin_char_column = euckr_bin_char_column

작업범위 결정 조건 과 필터링 조건

• 작업범위 결정 조건
  • 꼭 필요한 비교작업만 수행하도록 한다.
• 필터링 조건 (또는 체크 조건)
  • 단지 쿼리의 조건에 맞는지 검사하는 용도
  • 쿼리의 성능을 높히지 못함. 방해되기도 함.

예시

두가지 케이스의 인덱스가 생상되어있을 때

• 케이스A: INDEX(dept_no, emp_no)
• 케이스B: INDEX(emp_no, dept_no)

쿼리를 실행하는 것을 생각해보자.

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

반환되는 결과는 동일하지만 케이스 B의 경우 데이터 2개가 사용되지 못하는 것을 알 수 있다.

여기서 각 인덱스를 평가하면 다음과 같다.

• 작업범위 결정 조건
  • 케이스 A : dept_no, emp_no
  • 케이스 B : dept_no
• 필터링 조건 (또는 체크 조건)
  • 케이스 B : emp_no

작업범위 결정조건 으로 인덱스를 사용할 수 있는 경우

• 예시 인덱스
  • 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_1 칼럼에 대해 다음 연산자 중 하나로 비교
    • 동등 비교 ( “=” 또 는 “IN” )
    • 크다 작다 형태 ( “>” 또는” <” )
    • LIKE로 좌측 일치 패턴 ( LIKE ‘승환%’)

인덱스 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 (Left-most) 오른쪽 값을 정렬한다. 따라서 정렬 우선 순위가 낮은 뒷부분의 값 만으로는 인덱스의 효과를 얻을 수 없다.

함수 기반 인덱스

두 가지 방식이 있다.

가상 컬럼 이용

ALTER TABLE user
ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ' ,last_name)) VIRTUAL,
ADD INDEX ix_fullname (full_name);

함수 이용

ALTER TABLE user
ADD INDEX ix_fullname ((CONCAT(first_name,' ' ,last_name)))

함수 이용 방식의 경우 index 생성에 사용된 함수를 그대로 사용해야 index를 통해 쿼리를 수행한다.

멀티 밸류 인덱스 (JSON)

하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스이다.

정규화 규칙에 위배되기 때문에 기존의 방식으로는 불가능한 구조이나 JSON 타입을 지원하기 위해 생겼다.

멀티밸류 인덱스를 활용하기 위해서는 일반적인 조건 방식을 사용하면 안되고, 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

• MEMBER_OF ()
• JSON_CONTAINS ()
• JSON_OVERLAPS ()

클러스터링 인덱스

• 테이블의 프라이머리 키에 대해서 적용되는 내용
• 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장되는 것을 클러스터링 인덱스라고 표현
• 프라이머리 키 값에 의해 레코드의 저장 위치가 결정
  • 프라이머리 키 값이 변경되면 레코드의 물리적인 저장 위치도 바뀜

InnoDB에서 프라이머리 키를 명시적으로 설정하지 않을 경우

InnoDB 는 프라이머리 키가 없을 수 없다. 프라이머리 키가 없는 경우에는 스토리지 엔진이 다음 우선 순위대로 대체할 컬럼을 선택한다.

• NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번째 인덱스를 클러스터링 키로 선택
• 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택한다. (이 경우 해당 컬럼을 사용자는 사용할 수 없다.)

가능하면 직접 명시해 주는 것이 좋다.

클러스터링 인덱스의 장점과 단점

장점

• 프라이머리 키 (클러스터링 키) 로 검색할 때 처리 성능이 매우 빠름 (특히, 프라이머리 키를 범위 검색 하는 경우 매우 빠름)
• 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음 (이를 커버링 인덱스라고 한다)

단점

• 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한 번 검색해야 하므로 처리 성능이 느림
• INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
• 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT 하는 작업이 필요하기 때문에 처리 성능이 느림

프라이머리 키 관련 팁

• 프라이머리 키는 반드시 명시할 것
• 프라이머리 키는 AUTO-INCREMENT 보다는 업무적인 칼럼으로 생성 (가능한 경우)
• 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 좋음
• 프라이머리 키 의 사이즈가 커지면 세컨더리 인덱스의 사이즈도 커지므로 주의

where 절에서 조건문의 순서가 쿼리 성능에 영향을 미칠까?

옵티마이저가 index 구성과 통계정보에 따라 조건문의 순서를 적절히 조정한다. 따라서 일반적으로는 크게 영향을 미치지 않는다. 다만 복잡한 쿼리의 경우 달라질 수 있다.

유니크키

유니크 인덱스와 일반 세컨더리 인덱스는 구조상 차이점이 없다. 유니크 키는 값은 값이 2개 이상 저장될 수 없도록 보장한다.

프라이머리키와는 다르게 유니크는 NULL을 허용한다.

유니크키의 읽기 성능

일반 인덱스와 거의 차이가 없다. 다만 1개만 반환하기 때문에 조금 더 빠르게 응답을 할 수 있지만 이는 레코드 수의 차이라고 보면 된다.

유니크키의 쓰기 성능

유니크 인덱스는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다. 또한 중복 체크는 바로 진행되어야 하므로 작업을 버퍼링 하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.

주의 사항

PRIMARY는 이미 UNIQUE 함을 보장한다. UNIQUE 키를 추가할 수 있지만 이는 불필요한 중복이다.

유니크 인덱스가 있다면 일반 인덱스를 만들어 줄 필요는 없다. 유니크 인덱스에서는 추가적으로 unique를 고장해줄 뿐이다. 추가적으로 인덱스를 생성할 필요는 없다. 불필요한 중복이다.

외래 키 (foreign key)

외래키는 제약에 가깝다. 테이블 간의 관계를 정의하고, 데이터 무결성을 보장하는데 사용된다. 다른 인덱스들과 다르게 성능을 위한 인덱스는 아니다.

외래 키의 특징

테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다. 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

외래 키의 동작방식

자식 테이블의 변경이 대기하는 경우

부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될 때까지 기다린다.

부모 테이블의 변경 작업이 대기하는 경우

자식 테이블의 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될 때까지 기다린다.