[MySQL] 인덱스(Index)

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

---

인덱스(index)는 데이터를 빠르게 찾을 수 있도록 도와주는 도구로, 실무에서는 현실적으로 인덱스 없이 데이터베이스 운영이 불가능하다.

인덱스에는 두 가지 종류가 있다.

• 클러스터형 인덱스(Clustered Index)
• 보조 인덱스(Secondary Index)

 

인덱스의 개념

책을 예로 들어보면 책의 내용 중 'UNIQUE'에 대해서 찾아보고 싶다면 제일 뒤에 수록되어 있는 찾아보기를 찾아보는 것이다.

찾아보기는 ABC 또는 가나다 순으로 이미 정렬되어 있어 'U' 부분을 살펴보면 쉽게 'UNIQUE' 단어를 찾을 수 있고, 단어 옆에 본문의 페이지 번호가 적혀 있어서 원하는 내용으로 빨리 이동할 수 있다.

책 뒤의 찾아보기는 색인, 인덱스라고도 부른다.

실무에서 운영하는 테이블에서는 인덱스의 사용 여부에 따라 성능 차이가 날 수 있다.

대용량의 테이블일 경우에는 더욱 그러하다.

이것이 인덱스를 사용하는 이유이다.

 

인덱스의 문제점

인덱스는 적절히 사용해야 효과가 극대화된다.

필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간만 늘어나고, 오히려 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블을 찾는 것보다 느려진다.

 

똑똑한 MySQL
데이터베이스에 인덱스를 생성해 놓아도, 인덱스를 사용해서 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를지 MySQL이 알아서 판단한다.
만약 인덱스를 사용하지 않는다면 사용하지도 않는 인덱스를 만든 것이므로 공간 낭비를 한 셈이다.

 

인덱스의 장점과 단점

장점

• SELECT 문으로 검색하는 속도가 매우 빨라진다.
• 적은 처리량으로 요청한 결과를 얻게 되어 여유가 생기고 추가로 더 많은 처리를 할 수 있다.
  즉, 결과적으로 전체 시스템의 성능이 향상된다.

단점

• 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인(테이블 크기의 약 10%) 공간이 필요하다.
• 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
• SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수 있다.

 

인덱스의 종류

인덱스에는 두 가지 종류가 있다.

• 클러스터형 인덱스(Clustered Index)
• 보조 인덱스(Secondary Index)

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

보조 인덱스는 찾아보기에서 해당 단어를 찾은 후에 옆에 표시된 페이지를 펼쳐야 실제 찾는 내용이 있는 것을 말한다.

클러스터형 인덱스는 영어사전처럼 책의 내용이 이미 알파벳 순서대로 정렬되어 있는 것이다.

 

자동으로 생성되는 인덱스

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

하나의 컬럼에 여러 개의 인덱스를 생성할 수도 있고, 여러 개의 컬럼을 묶어서 하나의 인덱스를 생성할 수도 있지만 그런 경우는 드물다.

컬럼이 기본키(PK)로 지정되어 있다면 해당 컬럼은 자동적으로 클러스터형 인덱스가 된다.

즉, 해당 열을 기준으로 데이터가 자동으로 정렬된다.

CREATE TABLE table1  (
    col1  INT  PRIMARY KEY,
    col2  INT,
    col3  INT
);
SHOW INDEX FROM table1;

SHOW INDEX 문을 사용하면 인덱스 정보를 알 수 있다.

Key_name 부분을 보면 PRIMARY라고 써져 있다. 이는 기본 키로 설정해서 클러스터형 인덱스라는 뜻이다.

Column_name이 col1로 설정되어 있다는 것은 col1 열에 인덱스가 만들어져 있다는 것이다.

Non_Unique는 '고유하지 않다'라는 의미이다. 즉, 중복이 허용되냐는 뜻이다.

Non_Unique가 0이라는 것은 false, 반대로 1은 true이다. 결론적으로 이 인덱스는 중복이 허용되지 않는 인덱스이다.

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

 

기본키와 더불어 고유키(Unique Key)도 인덱스가 자동으로 생성된다.

컬럼이 고유 키로 지정되어 있다면 해당 컬럼은 자동적으로 보조 인덱스가 된다.

클러스터형 인덱스와 다르게 보조 인덱스는 자동으로 정렬되지 않는다. 그냥 순서대로 삽입될 뿐이다.

CREATE TABLE table2  (
    col1  INT  PRIMARY KEY,
    col2  INT  UNIQUE,
    col3  INT  UNIQUE
);
SHOW INDEX FROM table2;

Key_name에 컬럼 이름(col2, col3 등)이 쓰여 있는 것은 보조 인덱스라고 보면 된다.

고유 키 역시 중복값을 허용하지 않기 때문에 Non_Unique가 0으로 되어 있다.

고유 키를 여러 개 지정할 수 있듯이 보조 인덱스도 여러 개 만들 수 있다.

 

인덱스의 내부 작동원리

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다.

 

균형 트리의 개념

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

균형 트리 구조에서 데이터가 저장되는 공간을 노드(Node)라고 한다.
루트 노드(Root Node)는 노드의 가장 상위 노드를 말한다. 모든 출발은 루트 노드에서 시작된다. 
리프 노드(Leaf Node)는 제일 마지막에 존재하는 노드를 말한다. 
루트 노드와 리프 노드 사이에 있는 노드를 중간 노드(Internal Node)라고 한다.

그림에선 각 노드에 데이터가 최대 4개 들어가는 것으로 표현했지만 실제로는 훨씬 많은 데이터가 들어간다.

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

페이지는 최소한의 저장 단위로, 16 KByte 크기를 가진다.

 

인덱스를 사용하지 않고 전체 테이블 검색을 사용한 것을 그림으로 나타내면 아래와 같다.

MMM을 찾는다고 가정

전체 테이블 검색에서는 3페이지 만에 원하는 것을 찾았다.

 

인덱스를 사용하여 균형 트리에서 검색을 한다고 가정하면, 균형 트리는 무조건 루트 페이지부터 검색한다.

인덱스를 사용한 균형트리 검색에서는 2페이지 만에 원하는 것을 찾았다.

AAA -> FFF -> LLL -> LLL -> MMM 이렇게 5건의 데이터를 읽어서 원하는 것을 찾았는데, 몇 건의 데이터를 읽었느냐는 중요하지 않다. 몇 개의 페이지를 읽었느냐가 효율성을 판단한다.

 

균형 트리의 페이지 분할

인덱스는 균형 트리로 이루어져 있기 때문에 SELECT의 속도를 향상할 수 있다. 
다만 인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠진다. 
특히 INSERT 작업이 일어날 때 더 느리게 입력될 수 있다.
이유는 페이지 분할이라는 작업이 발생하기 때문인데, 페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다.
페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나게 되면 성능에 큰 영향을 미친다.

이전 그림에서 III 데이터가 새로 삽입되었다고 가정하면 균형 트리는 아래와 같이 변경된다.

이 상태에서 GGG가 추가적으로 삽입된다고 하면, 두 번째 리프 페이지에서 더 이상 빈 공간이 없기 때문에 페이지 분할이 일어난다.

이 상태에서 PPP와 QQQ가 들어온다고 가정하자.

PPP가 들어올 때는 별 일이 일어나지 않지만, QQQ가 들어올 때 네 번째 리프 페이지에 빈칸이 없어서 또다시 페이지 분할 작업이 일어난다.

페이지 분할 후에 추가된 다섯 번째 리프 페이지를 루트 페이지에 등록을 하려고 하니, 루트 페이지도 이미 꽉 차서 루트 페이지도 페이지 분할을 해야 한다.

여기서 새로 생긴 페이지는 루트 페이지가 되고, 기존의 루트 페이지는 중간 페이지가 된다.

결국 QQQ 하나를 입력하기 위해서 3개의 새로운 페이지가 할당되고 2회의 페이지 분할이 되었다.

데이터 하나를 입력하기 위해 많은 일이 일어난 것이다.

어떤 INSERT는 빠르게 실행되고, 다른 INSERT는 느리게 실행되는 이유가 위와 같은 과정에 있다.

 

인덱스의 구조

클러스터형 인덱스 구조

 

보조 인덱스의 구조

 

인덱스의 실제 사용

인덱스 생성, 제거와 사용

인덱스 생성

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용해야 한다.
UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.
CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 된다.
그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.
ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어준다.
기본은 ASC로 만들어지며, 일반적으로 DESC로 만드는 경우는 거의 없다.

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

 

CREATE INDEX idx_member_addr ON member (addr); --주소로 단순 보조 인덱스 생성
    
SHOW INDEX FROM member;--테이블에 생성된 인덱스를 보여줌

 

CREATE UNIQUE INDEX idx_member_name ON member (mem_name); --멤버 이름으로 고유 보조 인덱스 생성

위 쿼리는 문제점이 있다.

멤버 이름으로 고유 보조 인덱스를 생성하기 때문에 동명이인은 멤버 테이블에 입력이 될 수 없기 때문이다.

따라서 현재 중복된 값이 없다고 무조건 설정하면 안 되며, 절대로 중복되지 않는 컬럼(주민등록번호, 학번, 이메일 주소 등)에만 UNIQUE 옵션을 사용해서 인덱스를 생성해야 한다.

 

ANALYZE TABLE member; -- 지금까지 생성한 인덱스를 실제로 적용
SHOW INDEX FROM member; --테이블에 생성된 인덱스를 보여줌

인덱스를 생성한 후에 ANALYZE TABLE문을 실행해 줘야 실제로 적용된다.

 

인덱스 실제 사용

CREATE INDEX idx_member_mem_number ON member (mem_number); --멤버의 수로 단순 보조 인덱스 생성
ANALYZE TABLE member; -- 인덱스 적용

인덱스를 실질적으로 활용하기 위해서는 WHERE 문을 사용해야 한다.

MySQL의 경우 WHERE 문에 작성된 조건에 따라 그 효율성을 먼저 따져, 만약 인덱스 검색의 효율이 더 좋다면 인덱스 검색을, 그렇지 않다면 테이블 전체 검색을 수행한다.

SELECT mem_id, mem_name, addr FROM member WHERE mem_name = '에이핑크';

 

--멤버 수가 7 이상인 그룹의 이름, 수를 출력
SELECT mem_name, mem_number FROM member WHERE mem_number >= 7;

 

WHERE 문을 사용하지 않으면 Full Table Scan을 하기 때문에 인덱스를 만든 이유가 없어진다.

SELECT * FROM member;

 

또한 되도록 WHERE 절에 작성된 열에는 연산과 같은 가공을 하지 않는 것이 바람직하다.

SELECT mem_name, mem_number FROM member 
 WHERE mem_number*2 >= 14; --전체 테이블 스캔, 이렇게 사용 X
    
SELECT mem_name, mem_number FROM member 
 WHERE mem_number >= 14/2; --인덱스 사용해서 스캔, 이렇게 사용 O

 

WHERE 절에 작성된 열 이름에 연산과 같은 가공을 하면 Full Table Scan이 된다.

 

인덱스 제거

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

보조 인덱스는 어떤 것을 먼저 제거해도 상관없다.

 

• 보조 인덱스 삭제

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;

 

• 클러스터형 인덱스 삭제(기본키 제약조건 삭제)

ALTER TABLE member DROP PRIMARY KEY;

위 쿼리는 오류가 발생한다. 해당 테이블의 기본키가 buy 테이블의 외래키이기 때문에 제약 조건에 위배된다.

그러므로 기본 키를 제거하기 전에 외래 키 관계를 제거해야 한다.

테이블에는 여러 개의 외래 키가 있을 수 있다.

그래서 먼저 외래 키의 이름을 알아내야 한다.

information_schema 데이터베이스의 referential_constraints 테이블을 조회하면 외래 키의 이름을 알 수 있다.

SELECT table_name, constraint_name
 FROM information_schema.referential_constraints
 WHERE constraint_schema = 'market_db';

외래 키의 이름을 알았으니 외래 키를 먼저 제거하고 기본키를 제거하면 된다.

ALTER TABLE buy DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member  DROP PRIMARY KEY;

 

인덱스를 제거한다고 데이터의 내용이 바뀌는 것은 아니다.

지금 인덱스를 제거한 것은 찾아보기를 제거하고 영어사전을 순서가 섞인 단어장으로 변경한 것이며, 내용은 그대로이다.