[SQL] SQL 인덱스

in Class on SQL

인덱스

인덱스의 개념파악

인덱스는 SELECT를 사용해서 테이블 조회할 때 결과를 빠르게 추출하도록 도와주는 기능이다.

앞 예시들에서 인덱스 없이도 별 문제가 없었던 건 데이터의 양이 적었기 때문 데이터가 커지면 데이터를 빠르게 찾을 수 있게 반드시 필요하며, 현실적으로 인덱스 없이 데이터베이스 운영은 불가능하다.

인덱스에는 클러스터형 인덱스와 보조 인덱스가 있다.

클러스터형 인덱스는 기본키로 지정시 자동 생성되며 테이블에 1개만 만들 수 있다.

기본 키로 지정한 열을 기준으로 자동 정렬된다.

보조 인덱스(Secondary Index)는 고유키로 지정시 자동 생성되며 여러개를 만들 수 있지만 자동 정렬되진 않는다. 인덱스를 만들면 SELECT의 출력속도가 빨라진다.

인덱스의 장단점

인덱스가 좋긴 하지만 남용도 문제가 된다. 필요없는 인덱스를 만들면 데이터베이스가 차지하는 공간이 늘어나고 인덱스를 이용해 데이터를 찾는게 전체 테이블을 찾는거보다 느려진다.

20211214_115609

인덱스는 SELECT에서 즉각적인 효과를 내는 빠른 방법 중 한가지다. 적절한 인덱스를 생성 후 인덱스를 쓰는 SQL을 만들면 기존보다 아주 빠른 응답속도를 얻을 수 있다.

20211214_115726

인덱스의 장점으로는

  • SELECT 문으로 검색하는 속도가 빨라진다.
  • 그 결과 컴퓨터의 부담이 줄어서 결국 전체 시스템의 성능이 향상된다.

인덱스의 단점으로는

  • 인덱스도 공간을 차지해서 데이터베이스 안에 추가 공간이 필요하다.(대략 테이블 크기의 10%정도 공간이 추가로 필요하다.)

  • 처음 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
  • SELECT 가 아닌 데이터 변경작업(CRUD)가 자주 일어나면 성능이 더 나빠질 수 있다. (인덱스를 잘 쓰면 SELECT의 검색속도가 빨라지나, 잘못 사용시 성능이 오히려 저하한다.)

인덱스의 종류

MySQL에서의 인덱스는 위에 말했듯 크게 클러스터형 인덱스와 보조 인덱스(Secondary Index) 로 나뉜다.

클러스터형 인덱스는 영어사전과 같고, 보조 인덱스는 책의 뒤에 찾아보기가 있는 일반사전과 같다.

20211214_120346

자동으로 생성되는 인덱스

앞서 이야기한 인덱스의 개념과 장단점을 이해했다면 테이블에 적용해보자

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

하나의 열에 여러개 인덱스 생성 후 여러개 열 묶어서 하나의 인덱스를 생성할 수도 있따. 하나의 열에 하나의 인덱스를 생성한다 생각해보자.

20211214_120747

열 하나당 인덱스 하나를 생성시 이 테이블엔 우선 8개 서로 다른 인덱스 생성이 가능하다.

위에선 member정의시 회원 아이디(mem_id)를 기본키로 정의했다. 이렇게 기본키로 지정시 자동으로 mem_id열에 클러스터형 인덱스가 생성된다.

그런데 기본 키는 테이블에 하나만 생성할 수 있다. 그렇게 되면 결국 클러스터형 인덱스는 테이블에 한 개만 만들 수 있다.

CREATE TABLE table1  (
    col1  INT  PRIMARY KEY, -- 기본 키로 지정
    col2  INT,
    col3  INT
);

이제 테이블의 인덱스를 확인해보자. SHOW INDEX문을 사용하면

SHOW INDEX FROM table1;

20211214_122012

이렇게 나오게 된다.

먼저 Key_name부분엔 PRIMARY라고 써있다. 이건 기본키로 설정해서 자동으로 생성된 인덱스라는 의미다 . 이게 클러스터형 인덱스이다. (key_name에 primary라 써있다면 클러스터형 인덱스와 같다 생각하면 된다.)

Column_name이 col1로 설정된건 col1열에 인덱스가 만들어져 있다는 말이다. 마지막으로 Non_Uinque는 고유하지 않다 라는 뜻이다.

즉 중복이 허용되냐는 뜻이다. Non-Unique가 0이라는 건 False, 반대로 1은 True라는 소리이다.

결론으로 이 인덱스는 중복이 허용되지 않는 인덱스이다.

고유 인덱스

  • 고유 인덱스(Unique Index)는 인덱스의 값이 중복되지 않는다의 의미고, 단순 인덱스는 인덱스의 값이 중복되어도 된다는 의미다. 기본 키(Primary Key)나, 고유 키(Unique)로 지정하면 값이 중복되지 않으므로 고유 인덱스가 생성된다. 그 외의 인덱스는 단순 인덱스로 생성된다.

기본 키와 더불어 고유키도 인덱스가 자동 생성된다. 고유키로 생성되는 인덱스는 보조 인덱스다.

CREATE TABLE table2  (
    col1  INT  PRIMARY KEY,
    col2  INT  UNIQUE,	-- 고유키로 지정
    col3  INT  UNIQUE	-- 고유키로 지정
);

20211214_122906

자동으로 정렬되는 클러스터형 인덱스

USE market_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
( mem_id      CHAR(8) ,
  mem_name    VARCHAR(10),
  mem_number  INT ,  
  addr        CHAR(2)  
 );

클러스터형 인덱스 적용하면

ALTER TABLE member DROP PRIMARY KEY ; -- 기본 키 제거
ALTER TABLE member
    ADD CONSTRAINT
    PRIMARY KEY(mem_name); -- 클러스터형 인덱스 생성
SELECT * FROM member;

INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울');
SELECT * FROM member;

20211214_123624

기본 키 변경 시 주의할 점
  • 이미 대용량의 데이터가 있는 상태에서 기본 키를 지정하면 시간이 엄청 오래 걸릴 수 있다. 노트에 단어 4개가 있으면 정렬하는데 금방이지만, 노트에 4만개면 엄청 오래걸린다. 또 앞에서 회원 이름열을 기본키로 했는데 이름은 중복 될수 있으므로 이건 위험하다.

정렬되지 않는 보조 인덱스가

고유키로 지정하면 보조 인덱스 생성한다. 또 보조 인덱스는 테이블에 여러개 설정할 수 있다. 고유키를 테이블에 여러개 설정할 수 있는 것과 같다.

보조인덱스는 일반 책으로 비유하면 책의 맨 뒤에 추가되지 본문이 변경 되는 것은 아니다.

20211214_131754

ALTER TABLE member
     ADD CONSTRAINT
     UNIQUE (mem_id);
SELECT * FROM member;

20211214_133824

데이터를 추가로 입력하면 일반 책의 본문의 제일 뒤에 추가 되는것과 유사하다.

INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울');
SELECT * FROM member;

20211214_134104

보조 인덱스는 여러개를 만들 수 있다. 하지만 보조 인덱스를 만들 때 마다 데이터베이스의 공간을 차지하게 되고 , 전반적으로 시스템에 나쁜 영향을 미친다. 그러므로 필요한 열에만 적절히 보조 인덱스를 생성하는 것이 좋다. (보조 인덱스는 여러개 생성할 수 있다. 고유키를 테이블에 여러개 지정하는 것과 마찬가지이다.)

  • 클러스터형 인덱스는 영어사전처럼 내용이 이미 정렬되어있 는 인덱스이다. 기본키로 지정하면 클러스터형 인덱스가 생성되고 해당 열로 자동 정렬이 된다.

  • 보조 인덱스는 일반 책의 찾아보기와 같이 별도 공간에 인덱스가 생성된다. 고유키로 지정하면 보조 인덱스가 생성되고 자동 정렬되진 않는다.

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

 클러스터형 인덱스보조 인덱스
영문클러스터형 인덱스보조 인덱스
관련 제약조건기본키(Primary Key)고유 키(Unique)
테이블당 개수1개여러 개
정렬지정한 열로 정렬됨정렬되지 않음
비유영어사전일반 책의 찾아보기

인덱스 내부 작동

  • 인덱스를 만들고 사용하는 법은 어렵지 않다. 하지만 인덱스의 작동 과정을 제대로 이해하지 못한 상태에서 사용하면 오히려 문제가 생길 수 있다.

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형트리로 들어진다. 균형 트리(Binary Tree), B-Tree는 “자료구조”에 범용적으로 쓰이는 데이터의 구조다.

20211214_141335

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

인덱스의 내부 작동 원리

  • 인덱스의 내부 작동원리를 이해하면, 인덱스를 사용해야 할 경우와 사용하지 말아야 할 경우를 선택시 도움이 된다. 인덱스가 늘 좋은건 아니라 정확한 판단이 중요하다.

균형 트리의 개념

균형 트리 구조에서 데이터가 저장되는 공간을 노드(node)라고 한다

루트 노드는 노드의 가장 상위노드를 말한다. 모든 출발은 루트노드에서 시작된다. 리프 노드는 제일 마지막에 존재하는 노드를 말한다.

루트노드와 리프노드 중간에 끼인 노드들은 중간노드라 말한다.

20211214_153426

노드라는 용어는 개념적 설명에서 주로 나오는 용어이며, MySQL에서는 페이지 페이지는 최소한의 저장단위로, 16kbyte(16384byte) 크기를 지닌다.

예를들어 데이터 1건만 입력해도 1개 페이지(16kb)가 필요하다. (페이지는 비어있는 노트 1장으로 생각해도 된다. 노트에 1개의 단어만 적고 싶어도 1장이 필요한 것과 마찬가지로 1건의 데이터만 입력해도 1페이지가 필요하다.)

균형 트리는 데이터를 검색할 떄(SELECT구문을 사용할 때) 아주 뛰어난 성능을 발휘한다. 만약 다음 그림에서 MMM이라는 데이터를 검색한다고 가정시, 모두 리프 페이지 만 있으므로 MMM을 찾는 법은 처음부터 검색하는 법밖에 없다.

AAA에서 MMM까지 8건 데이터(페이지 3개)를 검색해야 그 결과를 알 수 있다.

20211214_170254

균형트리에서 검색하게 되면 무조건 루트트리에서 검색한다. 모든 데이터는 정렬되어 있고 MMM은 AA,FFF,LLL 3개를 읽은 뒤 나오므로 3번쨰 리프 페이지로 직접 이동하면 된다.

세번쨰 리프 페이지에서 LLL,MMM 2개를 읽어 MMM을 찾았습니다.

결국 루트 페이지에서 AAA,FFF,LLL 3개와 리프 페이지에서 LLL,MMM 2개 합쳐서 5건의 데이터를 검색해서 원하는 결과를 찾았으며 페이지 2개를 읽었따.

균형 트리가 아닌 구조에선 3페이지를 읽었지만, 균형트리는 2페이지만 읽어서 결과를 얻을 수 있다.

20211214_170254

지금은 중간단계 페이지 없이 루트페이지, 리프페이지만 있는 2단계 뿐이라 효용성이 크게 안 느껴질수 있지만, 실무에서 사용되는 훨씬 많은 데이터(많은 단계)는 균형 트리 구성여부에 따라 읽어야 하는 페이지 수가 상당히 크다.

균형 트리의 페이지 분할

앞서 데이터 검색시 균형트리가 효과적인걸 봤다. 인덱스는 균형트리로 구성되어있다. 즉 인덱스를 만들면 select 속도를 향상시킬 수 있다.

2페이지 읽어서 데이터 찾는건 3페이지 읽어서 데이터 찾는거보다 빠르다.

그런데 인덱스 구성하면 데이터 변경작업(Insert, update, delete) 시 성능이 나빠진다.

특히 insert 작업이 일어날 떄 더 느리게 입력될 수 있따.

이유는 페이지 분할 이라는 작업이 발생하기 때문이다.

페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업이다. 페이지 분할이 일어나면 MySQL이 느려지고 너무 자주 일어나면 성능에 큰 영향을 끼친다.

앞 그림에서 III 데이터가 새로 insert 되었다고 가정시 균형트리는 다음과 같이 변경된다.

20211214_184340

두 번쨰 리프 페이지에는 빈 공간이 있어 JJJ가 아래로 한칸 이동하고 III가 그 자리에 삽입된다. 정렬 되어야 하기 때문에 JJJ가 한칸 이동했을 뿐 큰 변화는 일어나지 않는다. 즉 III를 입력하는 작업은 순식간에 처리 된다.

GGG를 입력하려면? 근데 두번쨰 리프 페이지는 더 빈 공간이 없다. 이럴 때 페이지 분할 작업이 일어난다.

데이터를 1개밖에 추가 안했지만 많은 변화가 생긴다. 새 페이지 확보 후 페이지 분할 작업이 1회 일어나고, 루트 페이지에도 새로 등록된 페이지의 제일 위에 있는 데이터 III가 등록되었습니다.

20211214_190940

이번에는 PPP와 QQQ를 연속해서 다음 그림의 1번에서 PPP를 입력하면 네번쨰 리프 페이지에 빈칸이 있으므로 제일 마지막에 추가된다. 별일이 일어나지 않습니다.

이번엔 QQQ를 입력해보자. 그런데 QQQ를 입력해보니 네번쨰 리프페이지에는 빈 칸이 없으므로 2번처럼 페이지 분할 작업이 일어난다.

페이지 분할 후에 추가된 다섯번쨰 리프 페이지를 루트 페이지에 등록하려고 하니 루트 페이지도 이미 꽉 차서 등록할 곳이 없다.

그래서 3번과 같이 루트 페이지도 다시 페이지 분할을 해야한다. 원래 루트페이지가 있던 곳은 2개의 페이지가 되어 더 이상 루트 페이지가 아니라 중간 페이지가 된다.

마지막으로 4의 새 페이지를 준비해서 중간 노드를 가리키는 새로운 루트 페이지로 구성이 된다.

20211214_191746

20211214_191756

결국 QQQ 하나를 입력하기 위해 3개의 새로운 페이지가 할당되고 2회의 페이지가 분할되었다. 데이터 하나를 입력하기 위해 너무 많이 일어난다.

즉 입력작업이 엄청 오래 걸린다.

이를 통해 인덱스를 구성시 데이터 변경(특히, insert)작업이 느려지는 지 확인 할 수 있었다.

인덱스 구조

  • 인덱스 구조를 통해 인덱스를 생성하면 왜 데이터가 정렬되는지, 어떤 인덱스가 더 효율적인지 살펴보자.

클러스터형 인덱스 구성하기

  • 이번엔 클러스터형 인덱스와 보조 인덱스의 구조는 어떻게 다른지 살펴보자. 우선 인덱스 없이 테이블 생성하고 다음과 같이 데이터를 입력해보자. ``` CREATE TABLE cluster – 클러스터형 테이블 ( mem_id CHAR(8) , mem_name VARCHAR(10) ); INSERT INTO cluster VALUES(‘TWC’, ‘트와이스’); INSERT INTO cluster VALUES(‘BLK’, ‘블랙핑크’); INSERT INTO cluster VALUES(‘WMN’, ‘여자친구’); INSERT INTO cluster VALUES(‘OMY’, ‘오마이걸’); INSERT INTO cluster VALUES(‘GRL’, ‘소녀시대’); INSERT INTO cluster VALUES(‘ITZ’, ‘잇지’); INSERT INTO cluster VALUES(‘RED’, ‘레드벨벳’); INSERT INTO cluster VALUES(‘APN’, ‘에이핑크’); INSERT INTO cluster VALUES(‘SPC’, ‘우주소녀’); INSERT INTO cluster VALUES(‘MMU’, ‘마마무’);

SELECT * FROM cluster;


1 페이지에 4개행이 입력된다 가정하면 아래 그림과 같이 나온다.

여기서 표현한 데이터 페이지는 실제 데이터가 들어있는 부분이다. 아직은 인덱스가 없는 상태로, 각 페이지 위에 써진 숫자는 페이지 번호를 임의로 부여했다.


![20211214_192412](/assets/20211214_192412_bkmito2el.png)

![20211214_192739](/assets/20211214_192739.png)

정렬된 순서를 보면 입력된 순서와 동일한 순서로 보이게 된다.


이제 테이블의 mem_id에 클러스터형 인덱스를 구성해보자. 앞서 배웠듯 mem_id를 Primary key로 지정시 클러스터형 인덱스로 구성되게 된다.

ALTER TABLE cluster ADD CONSTRAINT PRIMARY KEY (mem_id);



결과를 보면 mem_id를 기준으로 오름차순 정렬 된다.
mem_id열을 기본키로 지정해서 클러스터형 인덱스가 생성되어서 그렇다.


![20211214_193307](/assets/20211214_193307.png)



실제 다음과 같이 데이터는 데이터 페이지가 정렬되고 균형 트리형태의 인덱스가 형성된다.

먼저 클러스터형 인덱스를 구성하기 위해 행 데이터를 지정한 열로 정렬한다.
그림 아래쪽의 데이터 페이지를 보면 회원 아이디로 정렬한 것이 확인된다.

그리고 각 페이지의 인덱스로 지정된 열의 첫 번쨰 값을 가지고 루트페이지를 만든다.

인덱스 페이지의 리프페이지는 데이터 그 자체다.


![20211214_193727](/assets/20211214_193727.png)



----



#### 보조 인덱스 구성하기

동일한 데이터로 보조 인덱스를 만들어보자

USE market_db; CREATE TABLE second – 보조 인덱스 테이블 ( mem_id CHAR(8) , mem_name VARCHAR(10) ); INSERT INTO second VALUES(‘TWC’, ‘트와이스’); INSERT INTO second VALUES(‘BLK’, ‘블랙핑크’); INSERT INTO second VALUES(‘WMN’, ‘여자친구’); INSERT INTO second VALUES(‘OMY’, ‘오마이걸’); INSERT INTO second VALUES(‘GRL’, ‘소녀시대’); INSERT INTO second VALUES(‘ITZ’, ‘잇지’); INSERT INTO second VALUES(‘RED’, ‘레드벨벳’); INSERT INTO second VALUES(‘APN’, ‘에이핑크’); INSERT INTO second VALUES(‘SPC’, ‘우주소녀’); INSERT INTO second VALUES(‘MMU’, ‘마마무’);


![20211214_194612](/assets/20211214_194612.png)

앞서 고유 키 제약조건은 보조 인덱스를 생성한다는 것을 확인했다.

mem_id 열에 유니크를 지정하고 데이터를 확인해보자.

ALTER TABLE second ADD CONSTRAINT UNIQUE (mem_id);

SELECT * FROM second;


![20211214_195134](/assets/20211214_195134.png)

보조 인덱스가 생성되었는데도 입력한 것과 순서가 동일하다.

내부적으로 보조 인덱스가 데이터 페이지를 건드리지 않았다. 그리고 그림의 위쪽을 보면 별도의 장소에 인덱스 페이지를 생성했다.


보조 인덱스는 일반 책과 같다. 찾아보기가 없던 일반 책에 찾아보기를 만든다고 본문이 바뀌지 않는 것 처럼 책의 본문이 바뀌지 않는 것과 마찬가지로 보조 인덱스를 생성해도 데이터페이지는 변경되지 않는다.

대신 책의 뒷부분 등 별도의 공간에 만들어진거처럼 보조인덱스가 별도 공간에 만들어진다.

#### 인덱스에서 데이터 검색하기


클러스터형 인덱스에서 데이터 찾는법 똑같음.

![20211214_200154](/assets/20211214_200154.png)

![20211214_200310](/assets/20211214_200310.png)


-------

***인덱스 검색(Index Scan)*** 을 통해 클러스터형 인덱스는 2페이지만 읽어서 원하는 결과를 검색했고,
보조 인덱스는 3페이지를 읽어서 원하는 결과를 검색했다. 두 인덱스 모두 검색이 빠르지만 클러스터형 인덱스가 좀 더 빠르다.

----

- 인덱스는 내부적으로 **균형 트리**, 즉 나무를 거꾸로 표현한 자료구조로 구성된다.
- 노드는 트리 구조에서 데이터가 저장되는 공간을 말하는데, MySQL에서 노드를 이지라 부른다.
- ***전체 테이블 검색*** 은 데이터를 처음부터 끝까지 검색 하는 것입니다. 인덱스가 없으면 전체 페이지를 검색하는 법 밖에 없다.

- ***페이지 분할*** 은 데이터를 입력할 떄 입력할 페이지에 공간이 없어서 2개 페이지로 데이터가 나눠지는 것을 말한다.

- ***인덱스 검색*** 은 클러스터형 또는 보조 인덱스를 이용해서 데이터를 검색하는 것이다. 속도는 인덱스 사용하지 않았을 때 보다 빠르다.

----


## 인덱스의 실제 사용

#### 인덱스 생성

CREATE [UNIQUE] INDEX 인덱스_이름 ON 테이블_ 이름(열_이름) [ASC|DESC]


CREATE INDEX로 생성되는 인덱스는 보조 인덱스이다.

**UNIQUE**는 중복이 안되는 고유 인덱스를 만드는 것인데 , 생략하면 중복이 허용된다.
unique로 인덱스 생성하려면 기존에 입력값 중 중복이 있으면 안된다.

기본은 ASC로 오름차순 된다.

#### 인덱스 제거 문법

DROP INDEX로 제거

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

주의할 점은 기본 키 , 고유키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다. ALTER TABLE문으로 기본키나 고유키 제거시 자동으로 생성된 인덱스도 제거한다.

20211214_203357

인덱스를 효과적으로 사용하는 법

1. 인덱스는 열 단위에 생성된다.
2. WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
3. WHERE절에 사용되더라도 자주 사용해야 가치가 있다.
4. 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.
5. 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
6. 사용하지 않는 인덱스는 제거한다.
  • CREATE INDEX 문으로 인덱스를 직접 생성한다.
  • 기본 키 및 고유키로 자동 생성된 인덱스는 ALTER TABLE로 제거하고 CREATE INDEX문으로 생성한 인덱스는 DROP INDEX 문으로 제거한다.
  • 단순 보조 인덱스 는 중복을 허용하는 보조 인덱스이며, CREATE INDEX문을 사용한다.
  • 고유 보조 인덱스 는 중복을 허용하지 않는 보조 인덱스이며, CREATE UNIQUE INDEX문을 사용한다.
  • MySQL워크벤치에서 SQL을 실행 후, 실행계획에서 인덱스의 사용 여부를 알 수 있다.



© 2021.03. by yacho

Powered by github