1) 인덱스 개념을 파악하자
인덱스의 개념
인덱스는 책의 "찾아보기"와 같다
수 만개의 데이터 중 내가 원하는 데이터를 찾는 것은 쉽지 않을 것이다. 이를 위해 "찾아보기" 기능이 데이터베이스의 인덱스이다
인덱스의 문제점
찾아보기는 언뜻 장점만 있어 보이지만 단점이 있다.
직관적인 비유를 위해 명부에 김을 찾아본다고 생각해보자
매번 찾아보기와 본문을 번갈아 보며 김이 있는 모든 곳을 찾아봐야 하는데 찾아보기의 양과 번갈아보는 일 모두 비효율적이게 된다.
이게 데이터베이스의 인덱스에도 똑같이 적용된다. 즉, 불필요한 인덱스는 오히려 성능 저하를 일으킨다.
인덱스의 장점과 단점
장점
- SELECT 문으로 검색하는 속도 빨라짐
- 컴퓨터 부담 감소 ➡️ 전체 시스템 성능 향상
단점
- 처음 인덱스 사용 시 시간이 소요됨
- 인덱스만의 메모리가 필요해 DB 안에 추가 공간 필요
인덱스의 종류
크게 클러스터형 인덱스, 보조 인덱스을 나뉨
클러스터형 인덱스
- 사전과 유사
- 책 자체가 찾아보기
- 이미 알파벳 순서로 정렬되어 있음
보조 인덱스
- 일반적인 책의 찾아보기
자동으로 생성되는 인덱스
인덱스는 테이블의 열(컬럼) 단위에 생성, 하나의 열에는 하나의 인덱스 생성 가능
- 클러스터형 인덱스는 기본 키로 지정하면 자동으로 생성되고 테이블당 1개만 만들 수 있음
- 고유 키도 인덱스가 자동으로 생성됨 ➡️ 보조 인덱스
- 생성된 인덱스는
SHOW INDEX FROM ~~으로 확인 가능
SHOW INDEX FROM table1;
- Table : 테이블명
- Non_unique : 고유하지 않음 = 중복 허용되나요?
- Key_name : 인덱스의 이름을 표시, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표기
- Seq_in_index : 멀티 컬럼이 인덱스인 경우 해당 필드의 순서 표시
- Column_name : 해당 필드의 이름을 표시
- Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시
- Sub_part : 인덱스 접두어를 표시
- Packed : 키가 압축되는 방법을 표시
- Null : 해당 필드가 NULL을 저장할 수 있으면 YES, 저장할 수 없으면 '' 표시
- Index_type : 인덱스가 어떤 형태로 구성되어 있는지 나타내며 MySQL은 대부분 B-tree 사용
- Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보 표시
- Index_comment : 인덱스에 관한 모든 기타 정보 표시
자동으로 정렬되는 클러스터형 인덱스
어느 속성을 기준으로 정렬할래?
클러스터형 인덱스는 기본 키로 지정하면 자동 생성된다.
특정 열을 기본 키로 지정하면 클러스터형 인덱스가 생성되고 그 열을 기준으로 정렬 된다
<기본키 지정 X = 클러스터형 인덱스 생성 X>
<기본키 지정 O = 클러스터형 인덱스 생성 O, mem_id 기준 자동 정렬>
정렬되지 않는 보조 인덱스
고유키를 지정하면 보조 인덱스가 생성된다.
기본키를 지정하면 자동 생성되는 클러스터형 인덱스와 다르게 보조 인덱스는 여러 개 지정할 수 있고 자동정렬 되지 않는다.
<고유키 설정으로 보조 인덱스 생성>
- 고유키를 늘려 보조 인덱스가 늘어나도 테이블에 변화는 없다
찾아보기가 추가된다고 책의 본문이 변경되지 않는 것과 같음- 보조 인덱스는 이와 같이 여러 개 만들 수 있지만, 만들 때 마다 DB의 공간을 차지해 성능 저하의 우려가 있어 꼭 필요한 열에만 생성한다.
2) 인덱스의 내부 작동
인덱스의 내부 작동 원리
인덱스를 위해 다양한 자료구조가 사용되는데 주로 해시 테이블과 B+Tree, B-Tree 구조 등이 많이 사용된다.
MySQL의 데이터베이스 엔진인 InnoDB는 그 중 B-tree를 개선한 B+Tree를 사용한다.
균형 트리의 개념
[B-Tree 자료구조]
- 데이터가 저장되는 공간을 노드 라고 한다.
- 루트 노드는 가장 상위 노드
- 브랜치 노드는 중간에 위치한 노드
- 리프 노드는 가장 하위 노드
- 자식 수가 2만 가능한 이진 트리의 확장판 (자식 N개 이상 = B+tree 구조)
- B = Balanced : 좌우 균형이 맞지 않으면 매우 비효율적이라 항상 균형을 맞추기 때문에 B-tree라 부른다
[페이지?]
쉽게 말하면 B+tree 구조의 노드가 페이지이다.
즉, 인덱스를 구현하기 위해 사용한 B-tree 구조의 각 노드를 MySQL에서는 페이지라고 부르는 것이다.
하지만 좀 더 자세히 배우면 디스크와 메모리(버퍼풀)에 데이터를 읽고 쓰는 최소 작업 단위를 페이지라 부른다.
인덱스, 테이블, PK(클러스터 인덱스) 등은 모두 페이지 단위로 관리된다. ➡️ 만약 쿼리를 통해 1개의 레코드를 읽고 싶더라고 하나의 블록을 읽어야한다.
균형 트리의 페이지 분할
인덱스 사용을 조심해야 하는 이유가 뭘까?
인덱스가 있으면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠진다. 그 이유는 페이지 분할이라는 작업이 발생하기 때문이다.
- 새로운 페이지를 준비해서 데이터를 나누어야하는 작업
- INSERT 작업 시 특히 성능 저하를 일으킨다
따라서 인덱스를 사용하는 것이 효율적인지 알아보는 것이 중요하다.
인덱스의 구조
클러스터형 인덱스 구성하기
- PRIMARY KEY를 지정하면 자동으로 클로스터형 인덱스 구성
- 자동 정렬
보조 인덱스 구성하기
- 보조 인덱스는 정렬 x
- 보조 인덱스는 데이터 페이지에 영향을 미치지 않음
인덱스에서 데이터 검색하기
- 클러스터형 인덱스가 보조 인덱스보다 검색이 좀 더 빠름
- 클러스터형 인덱스
- 루트 페이지 읽음
- 리프 페이지(데이터 페이지)를 찾아 검색
- 총 2페이지를 읽음
- 보조 인덱스
- 루트 페이지 읽음
- 리프 페이지를 읽음
- 데이터 페이지에서 최종 검색
- 총 3페이지를 읽음
3) 인덱스의 실제 사용
인덱스 생성과 제거 문법
인덱스 생성 문법
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC || DESC]- CREATE INDEX 사용
- UNIQUE는 고유 인덱스 생성 (중복 X)
- UNIQUE 인덱스 생성 시 기존 입력값에 중복이 없어야 함
- 인덱스 생성 후 입력되는 데이터도 중복 X
- 회원 이름을 UNIQUE로 하면 대참사 (동명이인)
- UNIQUE는 전화번호, 이메일 등 고유값으로 정해야함
인덱스 제거 문법
DROP INDEX 인덱스_이름 ON 테이블_이름- DROP INDEX 사용
- 기본키, 고유키로 자동 생성된 인덱스는 DROP INDEX로 제거 불가능
- ALTER TABLE 문으로 기본키나 고유키를 제거하면 자동으로 생성된 인덱스도 제거 가능
인덱스 생성과 제거 실습
인덱스 생성 실습
-
기본키(클러스터형 인덱스)
-
고유키(보조 인덱스)
create index idx_member_addr
on member (addr)
- non_unique == 1 : 고유 보조 인덱스가 아니라는 것. 고유키가 아니라는 것이 절대절대절대 아님
- 중복 데이터를 허용 (주소(여기서는 지역명)가 겹칠 수 있음)
- 보조 인덱스 적용
- 보조 인덱스 생성 후에 table status를 확인하면 Index_length가 0인 것을 확인할 수 있다
- 아니 왜 적용 안되나요 ㅠㅠ ➡️ ANALYZE TABLE 사용
ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';
Index_length를 통해 보조 인덱스가 생성된 것을 확인할 수 있다.
인덱스의 활용 실습
위에서 한 내용 그대로인데 mem_name에 보조 인덱스 하나 더 추가한 모습
- 인덱스 사용하고 있나요?
SELECT * FROM member;이 쿼리를 실행하는데 인덱스를 사용하고 있는지 확인하는 방법
결과창 우측의 EXECUTION PLAN 누르면 확인 가능
지금은 책 전부를 살펴본 것과 같은 결과
- 인덱스가 있는 열 조회
SELECT mem_id, mem_name, addr FROM member;
왜 아직도 책 전부 보고 있나요 ㅠㅠ
- 인덱스가 생성된 mem_name의 특정값 행을 조회
SELECT mem_id, mem_name, addr
FROM member
WHERE mem_name = '에이핑크';
O M G Single Row = 인덱스를 사용해 결과를 얻었다는 뜻
즉, WHERE 절에 열 이름이 들어있어야 인덱스를 사용
- 숫자 범위로 조회 (인원수로 조회)
인원 수 보조 인덱스 생성
CREATE INDEX idx_member_mem_number
ON member (mem_number);
ANALYZE TABLE member;인원 수 7명 이상인 그룹의 이름과 인원수 조회
SELECT mem_name, mem_number
FROM member
WHERE mem_number >= 7;
아름답게 인덱스가 사용된 모습....
인덱스를 사용하지 않을 때
MySQL은 인덱스 검색할지 테이블 전체 검색할지 구분 가능
- WHERE 절의 인덱스보다 전체 테이블 조회가 더 효율적일 거 같으면 INDEX 사용 안함
- WHERE 절에 연산이 가해지면 INDEX 사용 안함
💡 인덱스가 사용되지 않으면 비효율적이므로 유지보수에 신경써야함
인덱스 제거 실습
SHOW INDEX FROM member;
- 클러스터형 + 보조 인덱스 섞여 있다면, 보조 인덱스 먼저 삭제하는 것이 좋다
DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;
4) 미션
기본 미션
-
member 확인
-
INDEX 확인
엥 왜 갑자기 Cardinality가 10이나 나오지?? ➡️ Cardinality는 고유값의 개수를 알려줘 교재와 다르게 10개가 나오는게 정상인 듯..?
카디널리티는 특정 컬럼을 기준으로
중복도가 높으면 카디널리티가 낮고
중복도가 낮으면 카디널리티가 높다.
테이블 전체 row에서 얼마나 중복되는가에 대한 지표로 활용 가능 -> 상대적인 수치, 실제로 카디널리티에 기반한 인덱싱 전략을 세우기도 함
💡 지금까지 배운대로 뭐가 효율적일지 생각해보자
- 카디널리티가 높은 순으로 정렬할까 낮은 순으로 정렬할까..?
정답은 카디널리티가 높은 것부터 낮은 순으로 구성하는 것이다.
이유는 B-Tree에 있는데 이 자료구조는 데이터를 일정하게 정렬하고, 나누는 특징이 있다. 데이터가 나뉘면서 Depth가 얕은데 (대부분 3이하) 이 특징이 데이터 조회 성능이 좋은 이유다.
그렇기 때문에 범위를 크게 줄일 수 있는(카디널리티가 높은) 것부터 탐색하는 것이 효율적이다.
선택 미션
인덱스 생성 기본 문법
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC || DESC]인덱스 제거 기본 문법
DROP INDEX 인덱스_이름 ON 테이블_이름