혼공 SQL - ch06(인덱스)

목록

1. 포스팅 개요
2. 본론
       2-1. 인덱스 개념을 파악하자
       2-2. 인덱스의 내부 작동
       2-3. 인덱스의 실제 사용
       2-4. 인덱스를 효과적으로 사용하는 방법
3. 요약

1. 포스팅 개요

혼자 공부하는 SQL 챕터 6을 요약 및 정리한 포스팅

2. 본론

2-1. 인덱스 개념을 파악하자

인덱스란?

• 데이터를 빠르게 찾을 수 있도록 도와주는 도구
• 인덱스를 만들면 SELECT 문의 출력 속도가 빨라진다.

인덱스의 개념

• 책의 찾아보기와 비슷한 개념.
  • 하나의 단어를 찾을 때, 책을 처음부터 전부 뒤져보는 것과 책 뒤의 index에서 찾는 것 중 후자가 더 빠른 것처럼 DB에서도 INDEX를 활용하면 보다 더 효율적으로 빠르게 데이터를 추출할 수 있다.
• 인덱스의 사용 여부에 따라 성능 차이가 날 수 있다.
• 인덱스의 사용 여부에 따른 결과 값의 차이는 없다. 동일하다!
• 단지 시간이 오래 걸릴 뿐이다.

인덱스의 문제점

예시)
SQL책을 보면서 SELECT 단어를 찾으려고 한다.
이 때 책 뒤에 찾아보기(INDEX)가 있다.
그런데 SQL책에 SELECT 단어가 얼마나 많을 것인가..
찾아보기를 활용한다면,
책 뒤쪽에서 페이지를 찾고, 앞의 본문을 펼쳐보고..
책 뒤쪽에서 페이지를 찾고, 앞의 본문을 펼쳐보고..
책 뒤쪽에서 페이지를 찾고, 앞의 본문을 펼쳐보고..

차라리 책의 처음부터 단어를 찾아가는 것이 더 빠를 수도 있다.
이처럼 무조건 인덱스가 있다고 좋은 것은 아니다.
오히려 처음부터 데이터를 찾아가는 것보다 더 오래 걸릴 수도 있고,
더군다나 사용하지 않으면 쓸데없이 공간만 더 늘어날 수도 있다.

MySQL은 인덱스를 사용하는 것이 빠른지, 전체 테이블을 검색하는 것이 빠를지 알아서 스스로 판단한다.

인덱스의 장점과 단점

인덱스의 장점

• SELECT에서 즉각적인 효과를 내는 방법 중 한 가지
  • SELECT 문으로 검색하는 속도가 매우 빨라진다.
• 적절한 인덱스를 생성하고, 인덱스를 사용하는 SQL을 만들면 보다 더 빠른 응답 속도를 얻을 수 있다.
  • 적은 처리량으로 요청한 결과를 빨리 얻을 수 있으니, 추가적으로 더 많은 일을 할 수 있고, 결과적으로 성능이 향상되는 효과를 얻을 수 있다.

인덱스의 단점

• 인덱스도 공간을 차지하므로, 데이터베이스 안에 추가적인 공간이 필요하다.
  • 인덱스는 대략 테이블 크기의 10%정도의 공간이 추가로 필요하다.
• 처음 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
  • 찾아보기가 없는 책에 새로 찾아보기를 만드는 것과 마찬가지로 작업 시간이 필요하다.
• SELECT가 아닌 데이터의 변경작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수도 있다.
  • 인덱스는 잘 사용하면 SELECT의 검색 속도가 빨라지지만, 잘 못 사용하면 오히려 성능이 나빠진다.

인덱스의 종류

클러스터형 인덱스(Clustered Index)

• Ex) 영어사전 - 이미 정렬되어있는 것
• 기본 키로 지정하면 자동 생성된다.
• 테이블에 한 개만 만들 수 있다.
• 기본 키로 지정한 열을 기준으로 자동 정렬된다.

보조 인덱스(Secondary Index)

• Ex) 책 뒤의 찾아보기 - 찾아보기에서 찾은 후, 표시된 페이지로 이동.
• 고유 키(UNIQUE)로 지정하면 자동으로 생성된다.
• 여러 개를 만들 수 있다.
• 자동 정렬되진 않는다.

고유 인덱스

• 값이 중복되지 않는 인덱스
• 기본 키나 고유 키로 지정하면 값이 중복되지 않아서 고유 인덱스가 자동 생성된다.

테이블에 적용하는 인덱스

• 인덱스는 테이블의 열(컬럼) 단위에 생성된다.
• 하나의 열에는 하나의 인덱스를 생성할 수 있다.

인덱스 확인 SQL문

SHOW INDEX FROM 테이블_이름;

결과는 다음처럼 나온다.

세부적인 부분은 다음과 같다.

Key_name
위에서 Key_name에 PRIMARY라고 되어있는 부분은 기본 키 설정으로 인해서 자동으로 생성된 인덱스라는 의미이다.
이것이 바로 클러스터형 인덱스이다.
만약, 그냥 열 이름이 써져있다면, 해당 인덱스는 보조 인덱스이다.

Column_name
col1이라고 되어있는데, col1 컬럼에 인덱스가 만들어져있다는 의미이다.

Non_Unique
'고유하지 않다'라는 의미인데, 중복이 허용되냐는 의미다.
0이면 false라는 의미로 중복을 허용하지 않는다는 의미이고,
1이면 true라는 의미로 중복을 허용한다는 의미이다.

기본 키를 설정하지 않고 테이블을 만들어서 데이터를 입력하고, 조회를 하면, 입력한 순서대로 데이터가 나온다.
이 때 하나의 열을 기본 키로 지정하게 되면, 기본 키로 지정한 열에 클러스터형 인덱스가 생성되고, 다시 조회를 하면 기본 키로 설정한 열을 기준으로 자동정렬된다.

2-2. 인덱스의 내부 작동

인덱스는 균형 트리의 자료구조로 구성되어있다.

균형 트리(Balanced tree, B-tree)

• 나무를 거꾸로 표현한 자료 구조
• 트리에서 제일 상단의 뿌리를 루트, 줄기를 중간, 끝에 달린 잎을 리프라고 부른다.

노드(node)

균형 트리 구조에서 데이터가 저장되는 공간

• 루트 노드(root node) : 노드의 가장 상위 노드
• 리프 노드(leaf node) : 제일 마지막에 존재하는 노드
• 중간 노드(internal node) : 중간에 끼인 노드

위의 그림에서 루트노드와 리프노드의 생략된 의미로 ...을 세로로 넣었는데, 중간 노드가 가리키는 건 그 생략된 표시인 ...이다...

노드라는 용어는 개념적인 설명에서 나오는 용어인데, MySQL에서는 페이지(page)라고 부른다.

페이지(page)

• 최소한의 저장 단위 16Kbyte(16384byte) 크기를 가진다.
  • 데이터를 1건만 입력해도 1개 페이지가 필요하다.

균형 트리는 데이터를 조회할 때 아주 뛰어난 성능을 발휘한다.
- 만약 균형 트리가 없으면, 전체 테이블 검색(Full Table Scan)만 하게 될 것이다.

페이지 분할

인덱스를 구성하면 SELECT의 속도는 향상된다.
하지만, 데이터의 변경작업(INSERT, UPDATE, DELETE)시 성능이 나빠진다.
특히 INSERT 작업 시에 더욱 그러하다.
왜냐하면 페이지 분할작업이 발생하기 때문이다.

페이지 분할은 새로운 페이지(=노드)를 준비해서 데이터를 나누는 작업을 의미한다.
즉, 페이지(=노드)에 공간이 없어서 나눠지는 것을 뜻한다.
페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 준다.

인덱스의구조

클러스터형 인덱스로 구성되면 인덱스가 생성된 열을 기준으로 데이터 순서가 자동으로 정렬된다.
마치 흩어진 영어단어들을 영어사전으로 만들기 위해 A, B, C.. 순서대로 맞추는 것처럼
데이터 또한 자동정렬된다.
클러스터형 인덱스가 구성되면 리프 페이지가 곧 데이터 페이지이다.

보조 인덱스로 구성되면 데이터가 자동정렬되진 않는다.
마치 책의 찾아보기를 만든다고 해서 책의 본문이 바뀌지 않는 것과 같다.
그리고 책의 분량이 찾아보기의 양에 따라 늘어난다.

이와 같이

보조 인덱스가 구성되면 페이지가 생성되고(책의 분량 일부 늘어남),
페이지에는 데이터의 위치(찾아보기 페이지 쪽수)가 페이지 번호+#위치형태로 기록된다.

보조 인덱스에서는 클러스터형 인덱스(리프 페이지가 곧 데이터 페이지)처럼 리프 페이지와 데이터 페이지가 동일하지 않다.

리프페이지는 데이터페이지의 위치를 표기하고 있다.
보조 인덱스는 데이터의 중복 여부에 따라 단순 보조 인덱스와 고유 보조 인덱스로 나뉜다.
단순 보조 인덱스는 중복을 허용하는 보조 인덱스이고,
고유 보조 인덱스는 중복을 허용하지 않는 보조 인덱스이다.

2-3. 인덱스의 실제 사용

인덱스 생성

CREATE (UNIQUE) INDEX 인덱스_이름 -- UNIQUE 여부에 따라 중복 허용여부 결정.
    ON 테이블_이름 (열_이름) (ASC || DESC) -- (ASC || DESC) 기본 ASC

※참고로 CREATE INDEX로 생성되는 인덱스는 보조인덱스다.

UNIQUE
중복이 허용되지 않는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.
이 때 기존에 입력된 값들에 중복이 있으면 안 된다.
인덱스를 생성한 후에 입력되는 데이터에도 중복을 허용할 수 없다.

인덱스 확인

SHOW INDEX FROM 테이블;

인덱스의 크기 확인

SHOW TABLE STATUS;

결과는 다음과 같이 나온다.

Data_length 인덱스(또는 데이터)의 크기를 Byte 단위로 표기한 것이다.
참고로 16384Byte는 16KB이다.
MySQL의 1페이지 크기는 기본적으로 16KB이다.
위의 표에선 페이지가 1개 할당되어있다는 의미이다.

※ 1KByte = 1024Byte

Index_length는 보조 인덱스의 크기를 나타낸다.

CREATE INDEX로 생성한 인덱스 적용

ANALYZE TABLE 문으로 테이블을 분석/처리해줘야 한다.

ANALYZE TABLE 테이블_이름;

---

인덱스 이용

인덱스를 사용하려면 인덱스가 생성된 열 이름이 SQL문에 있어야 한다.

member라는 테이블에 다음과 같이 인덱스가 생성되어 있다.

별표(*)를 이용해서 SELECT를 실행하면, 테이블 전체를 스캔한다.

인덱스가 생성된 열 이름을 넣어서 조회해보자.
그래도 결과는 테이블 전체를 스캔하는 것을 확인 할 수 있다.

where절을 통해서 mem_name의 값이 '에이핑크'인 행을 조회해보고 실행계획을 살펴보자.
WHERE 절에 인덱스가 적용된 열 이름이 있을 때, 인덱스가 적용된다는 것을 확인할 수 있다.

---

※ Full Table Scan을 제외하면 나머지는 모두 인덱스를 사용했다는 의미이다.

다른 예시로 숫자로 구성된 인원수에 대한 열을 단순 보조 인덱스(중복 허용)로 만들고, 인원수가 7명인 가수 그룹의 이름과 인원수를 조회해보도록 하겠다.

CREATE INDEX idx_member_mem_number -- 인덱스 생성
on member(mem_number);

ANALYZE TABLE member; -- 인덱스 적용

SELECT mem_name, mem_number -- 조회
from member
where mem_number >= 7;

결과는 다음과 같다.

---

숫자의 범위로 조회할 때 인덱스를 사용한다.

만약 7이 아니라, 1을 줬다면? 역시 숫자의 범위로 조회하기 때문에 인덱스를 활용할 것이다.

SELECT mem_name, mem_number 
from member
where mem_number >= 1;

결과는 다음과 같다.

---

?????????????????????

"인덱스를 활용할 것"이라고 예상했지만, 인덱스를 활용하지 않았다.

왜 인덱스가 사용되지 않았을까?

그것은 MySQL이 인덱스를 사용하는 것보다 전체 테이블을 스캔 하는 것이 더 낫겠다고 판단했기 때문이다.

인덱스가 있더라도 MySQL의 판단 하에 사용하지 않을 수도 있다.

SELECT mem_name, mem_number  -- 전체 테이블 스캔
from member
where mem_number*2 >= 14;

SELECT mem_name, mem_number  -- Index Range 스캔
from member
where mem_number >= 14/2;

(※ 인덱스 결과는 주석으로 표기)
두 SQL문의 결과는 위에서 언급했던 것처럼 그룹 인원이 7명 이상인 것과 결과가 동일하다.

그런데 왜 위의 SQL은 인덱스를 사용하지 않았고, 밑의 SQL은 왜 인덱스를 사용했을까?

결론은 WHERE문에서 열에 연산이 가해졌기 때문이다.
위의 SQL은WHERE문에서 열에 연산(mem_name*2)이 가해졌기 때문에 인덱스를 사용하지 않았고,
밑의 SQL은 WHERE문에서 열에 대한 연산이 없기 때문에 인덱스를 사용한 것이다.

따라서, WHERE 절에서 나오는 열에 대한 연산 하지 않는 것이 좋다!

인덱스를 사용하냐 안 하냐에 따라서 결과가 달라지는 것은 아니다. 검색 순서 차이일뿐, 결과는 똑같다!!

인덱스제거

DROP INDEX 인덱스_이름 ON 테이블_이름

기본 키, 고유 키로 인해서 자동 생성된 인덱스는 제거하지 못 한다
ALTER TABLE문을 통해서 기본 키, 고유 키를 제거해야 삭제할 수 있다.

인덱스에 클러스터형 인덱스와 보조 인덱스가 섞여있을 때에는 보조 인덱스를 먼저 제거하는 것이 좋다.

기본 키로 자동 생성된 클러스터형 인덱스 제거.

기본 키 지정으로 자동 생성된 클러스터형 인덱스는 DROP INDEX문이 아닌 ALTER TABLE문으로만 제거할 수 있다.
즉 기본 키를 제거함으로써 클러스터형 인덱스도 삭제된다.

ALTER TABLE 테이블_이름
    DROP PRIMARY KEY;

만약, 다른 테이블에서 기본 키를 외래키로 참조하고 있다면, 관계를 제거하기 위해서 외래 키의 이름 알아야하는데 다음 SQL문으로 알 수 있다.

SELECT table_name, constraint_name
from information_schema.referential_constraints
where constraint_schema = 스키마_이름;

---

---

※ 참고 ※

information_schema : MySQL의 시스템 데이터베이스
referential_constraints : MySQL의 시스템 테이블. 여기에 MySQL 전체의 외래 키 정보가 들어있다.

다음 사진은 그 일부다.

---

---

다음과 같이 제거 해주면 된다.

ALTER TABLE buy
    DROP FOREIGN KEY 외래키_이름;
ALTER TABLE member
    DROP PRIMARY KEY;

2-4. 인덱스를 효과적으로 사용하는 방법

인덱스는 열 단위에 생성된다.
WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
데이터의 중복이 높은 열은 인덱스를 만들어보 별 효과가 없다.
클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
사용하지 않는 인덱스는 제거한다.

• 사용하지 않는 인덱스를 제거하면 공간을 확보할 수 있고, 데이터 입력(INSERT)시 발생되는 부하도 많이 줄일 수 있다.

3. 요약

인덱스의 개념을 통해서 인덱스가 무엇인지, 문제점은 없는지, 장점은 무엇이고, 단점은 무엇인지 알아보았고,
인덱스의 내부 구조를 통해서 인덱스가 어떤 방식으로 구성되는지, 인덱스에는 어떤 종류가 있는지 알아보았다.
그리고 이러한 내용을 토대로 인덱스를 실제 사용하면서, 인덱스를 어떨 때 사용하며, 어떻게 적용시키는지, 그리고 제거까지 해보았다.