INDEX - 인덱스
06-1 인덱스 개념
인덱스 index 는 데이터를 빠르게 찾을 수 있도록 도와주는 도구이다.
인덱스에는 클러스터형 인덱스와 보조 인덱스가 있다.
클러스터형 인덱스 Clustered Index는 기본 키로 지정하면 자동 생성되며 테이블에 1개만 만들 수 있다.
기본 키로 지정한 열을 기준으로 자동 정렬된다.
보조 인덱스 Secondary Index는 고유 키로 지정하면 자동 생성되며 여러 개를 만들 수도 있지만 자동 정렬되지는 않는다.
인덱스의 개념
인덱스의 문제점
필요 없는 인덱스를 만들면 데이터베이스가 차지하는 공간만 더 늘어가고, 인덱스를 이용해서 데이터를 찾는 것이 전체 테이블을 찾는것 보다 느려진다.
💡 데이터베이스에 인덱스를 생성해 놓아도, 인덱스를 사용해서 검색하는 것이 빠를지 아니면 전체 테이블을 검색하는 것이 빠를지 MySQL이 알아서 판단한다.인덱스의 장점과 단점
인덱스는 SELECT에서 즉각적인 효과를 내는 빠른 방법 중 한 가지이다.
즉, 적절한 인덱스를 생성하고 인덱스를 사용하는 SQL을 만든다면 기존보다 아주 빠른 응답 속도를 얻을 수 있다.
컴퓨터 입장에서는 적은 처리량으로 요청한 결과를 빨리 얻을 수 있으니 여유가 생기고 추가로 더 많은 일을 할 수 있다.
결과적으로 전체 시스템의 성능이 향상되는 효과도 얻게 된다.
인덱스의 장점은 다음과 같다.
- SELECT 문으로 검색하는 속도가 매우 빨라진다.
- 그 결과 컴퓨터의 부담이 줄어들어서 결국 전체 시스템의 성능이 향상된다.
인덱스의 단점은 다음과 같다.
- 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요하다.
- 인덱스는 대략 테이블 크기의 10% 정도의 공간이 추가로 필요하다.
- 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
- 찾아보기가 없는 책에 새로 찾아보기를 만드는 것과 마찬가지로 작업 시간이 필요하다.
- SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수도 있다.
인덱스의 종류
MySQL에서 사용되는 인덱스의 종류는 크게 두 가지로 나뉘는데, 클러스터형 인덱스와 **보조 인덱스**이다.
이 두 개를 쉽게 비교하면 클러스터형 인덱스는 영어사전과 같고, 보조 인덱스는 책의 뒤에 찾아보기가 있는 일반적인 책과 같다.
보조 인덱스는 SQL 책과 같이 찾아보기가 별도로 있고, 찾아보기에서 해당 단어를 찾은 후에 옆에 표시된 페이지를 펼쳐야 실제 찾는 내용이 있는 것을 말한다.
클러스터형 인덱스는 영어사전처럼 책의 내용이 이미 알파벳 순서대로 정렬되어 있는 것이다. 그래서 별도의 찾아보기가 없다. 책 자체가 찾아보기이다.
자동으로 생성되는 인덱스
인덱스는 테이블의 열 (컬럼) 단위에 생성되며, 하나의 열에는 하나의 인덱스를 생성할 수 있다.
💡 하나의 열에 여러 개의 인덱스를 생성할 수도 있고, 여러 개의 열을 묶어서 하나의 인덱스를 생성할 수도 있다. 하지만 상당히 드문 경우이므로 하나의 열에 하나의 인덱스를 생성한다고 생각하자.자동으로 생성되는 인덱스
클러스터형 인덱스는 기본 키로 지정하면 자동으로 생성되고 테이블당 1개만 만들 수 있다.
create table table1 (
col1 int primary key, -- 기본 키로 지정
col2 int,
col3 int
);
show index from table1;| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
|---|---|---|---|---|---|---|---|
| table1 | 0 | PRIMARY | 1 | col1 | A | 0 | NULL |
Key_name 부븐을 보면 PRIMARY라고 써 있다. 이것은 기본 키로 설정해서 ‘자동으로 생성된 인덱스’라는 의미이다.
이것이 바로 클러스터형 인덱스이다.
Key_name에 PRIMARY라고 써 있다면 클러스터형 인덱스와 같다고 생각하자.
Column_name 이 col1로 설정되어 있따는 것은 col1 열에 인덱스가 만들어져 있다는 말이다.
마지막으로 Non_unique는 ‘고유하지 않다’라는 뜻이다. 즉, 중복이 허용되냐는 뜻이다. Non-Unique가 0이라는 것은 False, 반대로 1은 True의 의미이다.
결론적으로 이 인덱스는 중복이 허용되지 않는 인덱스이다.
💡 고유 인덱스(Unique Index)는 인덱스의 값이 중복되지 않는다는 의미고, 단순 인덱스(Non-Unique Index)는 인덱스의 값이 중복되어도 된다는 의미이다. 기본 키(Primary Key)나 고유 키(Unique)로 지정하면 값이 중복되지 않으므로 고유 인덱스가 생성된다. 그 외의 인덱스는 단순 인덱스로 생성된다.기본 키와 더불어 고유 키도 인덱스가 자동으로 생성된다. 고유 키로 생성되는 인덱스는 보조 인덱스이다.
create table table2 (
col1 int primary key, -- 기본 키로 지정
col2 int UNIQUE, -- 고유 키로 지정
col3 int UNIQUE -- 고유 키로 지정
);
show index from table2;| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
|---|---|---|---|---|---|---|---|
| table2 | 0 | PRIMARY | 1 | col1 | A | 0 | NULL |
| table2 | 0 | col2 | 1 | col2 | A | 0 | NULL |
| table2 | 0 | col3 | 1 | col3 | A | 0 | NULL |
Key_name에 col2, col3이라고 열 이름이 써있다. 이렇게 Key_name에 열 이름이 써 있는 것은 보조 인덱스라고 보면 된다.
고유 키 역시 중복값을 허용하지 않기 때문에 Non_unique가 0으로 되어 있다. 또 고유 키를 여러 개 지정할 수 있듯이 보조 인덱스도 여러 개 만들 수 있다.
자동으로 정렬되는 클러스터형 인덱스
클러스터형 인덱스는 기본 키로 지정하면 자동 생성된다. 그리고 테이블에 1개만 생성 된다.
어떤 열을 기본 키로 지정하면(클러스터형 인덱스가 생성되면) 그 열을 기준으로 자동 정렬된다.
예를 들어, 영어 단어와 뜻을 필기한 노트를 영어사전으로 만든다고 가정하면 단어의 알파벳 순서로 정렬되어야 한다.
CREATE TABLE member
(
mem_id CHAR(8),
mem_name VARCHAR(10),
mem_number INT,
addr CHAR(2)
);| mem_id | mem_name | mem_number | addr |
|---|---|---|---|
| TWC | 트와이스 | 9 | 서울 |
| BLK | 블랙핑크 | 4 | 강남 |
| WMW | 여자친구 | 6 | 경기 |
| OMY | 오마이걸 | 7 | 서울 |
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMW', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');
SELECT * FROM member;mem_id 열을 기본 키로 설정하고 내용을 확인해보자.
ALTER TABLE member ADD CONSTRAINT PRIMARY KEY (mem_id);
SELECT * FROM member;| mem_id | mem_name | mem_number | addr |
|---|---|---|---|
| BLK | 블랙핑크 | 4 | 강남 |
| OMY | 오마이걸 | 7 | 서울 |
| TWC | 트와이스 | 9 | 서울 |
| WMW | 여자친구 | 6 | 경기 |
아이디를 기준으로 정렬 순서가 바뀐 것을 확인할 수 있다. mem_id 열을 기본 키로 지정했으므로 mem_id 열에 클러스터형 인덱스가 생성되어 mem_id 열을 기준으로 정렬되었다.
테이블이 영어사전이 되었다면 mem_id가 영어 단어가 되어서 알파벳 순서를 정렬한 것이다.
ALTER TABLE member DROP PRIMARY_KEY; -- 기본 키 제거
ALTER TABLE member ADD CONSTRAINT PRIMARY KEY (mem_name); -- 클러스터형 인덱스 생성
SELECT * FROM member;| mem_id | mem_name | mem_number | addr |
|---|---|---|---|
| BLK | 블랙핑크 | 4 | 강남 |
| WMW | 여자친구 | 6 | 경기 |
| OMY | 오마이걸 | 7 | 서울 |
| TWC | 트와이스 | 9 | 서울 |
이후 부터는 추가로 데이터를 입력하면 알아서 기준에 맞춰서 정렬된다.
💡 **기본 키 변경 시 주의할 점** 이미 대용량의 데이터가 있는 상태에서 기본 키를 지정하면 시간이 엄청 오래 걸릴 수 있다. 또, 앞에서 회원 이름 열을 기본 키로 변경했는데, 실제라면 논리적으로 위험하다. 기본 키는 중복되지 않아야 하는데, 회원 이름은 당연히 중복될 수 있기 때문이다.정렬되지 않는 보조 인덱스
고유 키로 지정하면 보조 인덱스가 생성된다고 했다. 그리고 보조 인덱스는 테이블에 여러 개 설정할 수 있다.
고유 키를 테이블에 여러 개 지정할 수 있는 것과 마찬가지이다.
drop table if exists member;
CREATE TABLE member
(
**mem_id CHAR(8),**
mem_name VARCHAR(10),
mem_number INT,
addr CHAR(2)
);INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMW', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');
SELECT * FROM member;| mem_id | mem_name | mem_number | addr |
|---|---|---|---|
| TWC | 트와이스 | 9 | 서울 |
| BLK | 블랙핑크 | 4 | 강남 |
| WMW | 여자친구 | 6 | 경기 |
| OMY | 오마이걸 | 7 | 서울 |
ALTER TABLE member ADD CONSTRAINT UNIQUE (mem_id);
SELECT * FROM member;mem_id 열을 고유 키로 설정하고 내용을 확인할 시 데이터의 순서에는 변화가 없다.
즉, 보조 인덱스를 생성해도 데이터의 순서는 변경되지 않고 별도로 인덱스를 만드는 것이다.
이번에는 mem_name 열에 추가로 고유 키를 지정할 경우 고유 키는 여러 개 설정해도 되므로 기존 고유 키를 제거할 필요는 없다.
ALTER TABLE member ADD CONSTRAINT UNIQUE (mem_name);
SELECT * FROM member;| mem_id | mem_name | mem_number | addr |
|---|---|---|---|
| TWC | 트와이스 | 9 | 서울 |
| BLK | 블랙핑크 | 4 | 강남 |
| WMW | 여자친구 | 6 | 경기 |
| OMY | 오마이걸 | 7 | 서울 |
보조 인덱스는 여러 개를 만들 수 있다.
하지만 보조 인덱스를 만들 때마다 데이터베이스의 공간을 차지하게 되고, 전반적으로 시스템에 오히려 나쁜 영향을 미치게 된다.
그러므로 꼭 필요한 열에만 적절히 보조 인덱스를 생성하는 것이 좋다.
정리
- 클러스터형 인덱스는 영어사전처럼 내용이 이미 정렬되어 있는 인덱스이다. 기본 키로 지정하면 클러스터형 인덱스가 생성되고 해당 열로 자동 정렬된다.
- 보조 인덱스는 일반 책의 찾아보기와 같이 별도의 공간에 인덱스가 생성된다. 고유 키로 지정하면 보조 인덱스가 생성되고 자동 정렬되지 않는다.
- 고유 인덱스는 값이 중복되지 않는 인덱스이다. 기본 키나 고유 키로 지정하면 값이 중복되지 않아서 고유 인덱스가 자동 생성된다.
| 클러스터형 인덱스 | 보조 인덱스 | |
|---|---|---|
| 영문 | Clustered Index | Secondary Index |
| 관련 제약조건 | 기본 키(Primary Key) | 고유 키(Unique) |
| 테이블당 개수 | 1개 | 여러 개 |
| 정렬 | 지정한 열로 정렬됨 | 정렬되지 않음 |
| 비유 | 영어사전 | 일반 책의 찾아보기 |
06-2 인덱스의 내부 작동
클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다.
균형 트리 Balanced tree, B-tree는 ‘자료 구조’에 나오는 범용적으로 사용되는 데이터의 구조이다.
나무를 거꾸로 표현한 자료 구조로, 트리에서 제일 상단의 뿌리를 루트, 줄기를 중간, 끝에 달린 잎을 리프라고 한다.
인덱스의 내부 작동 원리
균형 트리의 개념
균형 트리 구조에서 데이터가 저장되는 공간을 노드 node 라고 한다.
루트 노트 root node는 노드의 가장 상위 노드를 말한다.
모든 출발은 루트 노드에서 시작된다. 리프 노드 leaf node는 제일 마지막에 존재하는 노드를 말한다.
루트 노드와 리프 노드의 중간에 끼인 노드들은 중간 노드 internal node라 부른다.
노드라는 용어는 개념적인 설명에서 주로 나오는 용어이며, MySQL에서는 페이지page 라고 부른다.
페이지는 최소한의 저장 단위로, 16Kbyte(16384byte) 크기를 가진다.
예를 들어 데이터를 1건만 입력해도 1개 페이지(16Kbyte)가 필요하다.
균형 트리는 데이터를 검색할 때 (SELECT 구문을 사용할 때) 아주 뛰어난 성능을 발휘한다.
균형 트리로 구성하지 않으면(인덱스가 없으면) 전체 페이지를 검색하는 방법밖에 없다.
데이터를 처음부터 끝까지 검색하는 것을 전체 테이블 검색(Full Table Scan) 이라고 부른다.
균형트리는 무조건 루트 페이지부터 검색한다.
균형 트리의 페이지 분할
앞서 데이터를 검색하는 데 균형 트리가 더 효율적이라고 살펴보았다.
인덱스는 균형 트리로 구성되어 있다. 즉, 인덱스를 만들면 SELECT의 속도를 향상시킬 수 있다. 2페이지를 읽어서 데이터를 찾는 것은 3페이지를 읽어서 데이터를 찾는 것보다 빠른 방법이다.
그런데 인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠진다.
특히 INSERT 작업이 일어날 때 더 느리게 입력될 수 있다. 이유는 페이지 분할이라는 작업이 발생하기 때문이다.
페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다.
페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 준다.
인덱스의 구조
클러스터형 인덱스 구성하기
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;정렬된 순서를 확인해보면, 입력된 순서와 동일한 순서로 보인다.
| TWC | 트와이스 |
|---|---|
| BLK | 블랙핑크 |
| WMN | 여자친구 |
| OMY | 오마이걸 |
| GRL | 소녀시대 |
| ITZ | 잇지 |
| RED | 레드벨벳 |
| APN | 에이핑크 |
| SPC | 우주소녀 |
| MMU | 마마무 |
이제 테이블의 mem_id에 클러스터형 인덱스를 구성해보겠다. 앞서 했듯이 mem_id를 Primary Key로 지정하면 클러스터형 인덱스로 구성된다.
ALTER TABLE member ADD CONSTRAINT PRIMARY KEY (mem_id);| APN | 에이핑크 |
|---|---|
| BLK | 블랙핑크 |
| GRL | 소녀시대 |
| ITZ | 잇지 |
| MMU | 마마무 |
| OMY | 오마이걸 |
| RED | 레드밸벳 |
| SPC | 우주소녀 |
| TWC | 트와이스 |
| WMN | 여자친구 |
결과를 보면 mem_id를 기준으로 오름차순 정렬되었다. mem_id 열을 Primary Key로 지정했으니 클러스터형 인덱스가 생성되어서 그렇다.
보조 인덱스 구성하기
CREATE TABLE second -- 보조 인덱스를 테스트하기 위한 테이블
( mem_id CHAR(8),
mem_nmae 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', '마마무');앞서 고유 키 제약조건은 보조 인덱스를 생성한다는 것을 확인했다.
mem_id 열에 UNIQUE를 지정하고 데이터를 확인.
ALTER TABLE second ADD CONSTRAINT UNIQUE (mem_id);| TWC | 트와이스 |
|---|---|
| BLK | 블랙핑크 |
| WMN | 여자친구 |
| OMY | 오마이걸 |
| GRL | 소녀시대 |
| ITZ | 잇지 |
| RED | 레드벨벳 |
| APN | 에이핑크 |
| SPC | 우주소녀 |
| MMU | 마마무 |
보조 인덱스가 생성되었는데도 입력한 것과 순서가 동일하다.
정리
- 인덱스는 내부적으로 균형 트리, 즉 나무를 거꾸로 표현한 자료 구조로 구성된다.
- 노드는 트리 구조에서 데이터가 저장되는 공간을 말하는데, MySQL에서는 노드를 페이지라고 부른다.
- 전체 테이블 검색은 데이터를 처음부터 끝까지 검색하는 것이다. 인덱스가 없으면 전체 페이지를 검색하는 방법밖에 없다.
- 페이지 분할은 데이터를 입력할 때, 입력할 페이지에 공간이 없어서 2개 페이지로 데이터가 나눠지는 것을 말한다.
- 인덱스 검색은 클러스터형 또는 보조 인덱스를 이용해서 데이터를 검색하는 것이다. 속도는 인덱스를 사용하지 않았을 때보다 빠르다.
06-3 인덱스의 실제 사용
인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용하고, 제거하기 위해서는 DROP INDEX 문을 사용한다.
기본 형식은 다음과 같다.
인덱스 생성
CREATE [UNIQUE] INDEX 인덱스_이름 ON 테이블_이름 (열_이름) [ASC | DESC]인덱스 제거
DROP INDEX 인덱스_이름 ON 테이블_이름인덱스 생성과 제거 문법
인덱스 생성 문법
테이블을 생성할 때 특정 열을 기본 키, 고유 키로 설정하면 인덱스가 자동 생성된다는 것은 이미 확인했다.
Primary Key 문법을 사용하면 클러스터형 인덱스가, Unique 문법을 사용하면 보조 인덱스가 자동으로 생성되었다.
그 그외에 직접 인덱스를 생성하려면 CREATE INDEX 문을 사용해야 한다.
- MySQL 도움말 인덱스 생성 문법
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_mem_name
[index_type]
ON tbl_mem_name (key_part, ...)
[index_option]
[algorithm_option | lock_option] ...
key_part: {col_mem_name [(length) | (expr)} [ASC | DESC]
index_option :
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_mem_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}실제 사용하는 것은 다음과 같다.
CREATE [UNIQUE] INDEX 인ㄷ게스_이름 ON 테이블_이름 (열_이름) [ASC | DESC]CREATE INDEX로 생성되는 인덱스는 보조 인덱스이다.
UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.
CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 된다. 그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.
예를 들어, 회원 이름을 UNIQUE로 지정하면 향후에는 같은 이름의 회원은 입력할 수 없게 된다.
회원 이름은 같을 수도 있으므로 이름과 같은 성격을 가진 열에는 UNIQUE로 지정하면 안 된다.
이와 달리 휴대폰 번호, 이메일 등은 사람마다 모두 다르기 때문에 (중복되지 않기 때문에) UNIQUE로 지정해도 별 문제가 없다.
ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어 준다. 기본은 ASC로 만들어지며, DESC로 만드는 경우는 거의 없다.
인덱스 제거 문법
CREATE INDEX로 생성한 인덱스는 DROP INDEX로 제거한다.
DROP INDEX 인덱스_이름 ON 테이블_이름주의할 점은 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다는 것이다.
ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.
💡 **인덱스 제거** 하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있는 경우, 인덱스를 제거할 때는 보조 인덱스부터 제거하는 것이 더 좋다. 클러스터형 인덱스부터 제거하면 내부적으로 데이터가 재구성되기 때문이다. 또한, 인덱스가 많이 생성되어 있는 테이블의 경우 사용하지 않는 인덱스는 과감히 제거해주는 것이 좋다.인덱스 생성과 제거 실습
인덱스 생성 실습
USE market_db;
SELECT * FROM member;
SHOW INDEX FROM member;
현재 member에는 mem_id 열에 클러스터형 인덱스 1개만 설정되어 있다.
SHOW TABLE STATUS **LIKE 'member'**;이번에는 인덱스의 크기를 확인해보자. SHOW TABLE STATUS 문을 사용한다.
결과 중에 Data_length는 클러스터형 인덱스(또는 데이터)의 크기는 Byte 단위로 표기한 것이다. 그런데 MySQL의 1페이지 크기는 기본적으로 16KB이므로 클러스터형 인덱스는 16384/(16*1024) = 1 페이지가 할당되어 있다.
실제로는 데이터의 내용이 많지 않아서 16KB까지 필요 없지만, 최소 단위가 1페이지이므로 1페이지에 해당하는 16KB가 할당되어 있는 것이다.
Index_length는 보조 인덱스의 크기인데 member는 보조 인덱스가 없기 때문에 표기 되지 않는다.
이미 클러스터형 인덱스가 있으므로 이 테이블에는 더 이상 클러스터형 인덱스를 생성할 수 없다. 주소(addr)에 중복을 허용하는 단순 보조 인덱스를 생성해보자.
인덱스 이름을 idx_member_addr로 지정했다. inx는 Index를 의미하는 약자로 사용했는데 이름만 보고도 ‘member 테이블의 addr 열에 지정된 인덱스’라는 것을 알 수 있다.
CREATE INDEX idx_member_addr ON **member (addr);**보조 인덱스는 단순 보조 인덱스(Simple Secondary Index)와 고유 보조 인덱스(Unique Secondary Index)로 나뉜다.
단순 보조 인덱스는 중복을 허용한다는 의미로, 중복이 안 되는 고유 보조 인덱스와 반대라고 생각하면 된다.
SHOW INDEX FROM member;
Key_name에 지금 생성한 단순 보조 인덱스의 이름이 확인된다.
Column_name에서는 어느 열에 지정되었는지 확인된다.
주의할 점은 Non_unique가 1로 설정되어 있으므로 고유 보조 인덱스가 아니라는 것이다. 즉, 중복된 데이터를 허용한다.
💡 **클러스터형 인덱스와 보조 인덱스의 동시 사용** 클러스터형 인덱스와 보조 인덱스가 동시에 있다는 것은 영어사전이면서 동시에 찾아보기도 존재한다는 것이다. 영어사전에 추가로 한글 동물, 한글 식물 단어를 찾아보기로 정리해 놓은 것으로 생각하면 된다.이번에는 보조 인덱스가 추가되었으므로 전체 인덱스의 크기를 다시 확인해보자.
SHOW TABLES STATUS LIKE 'member';
Index_length부분이 보조 인덱스의 크기인데, 이상하게도 크기가 0으로 나왔다.
바로 앞에서 보조 인덱스 idx_member_addr이 생성된 것을 확인했는데, 생성한 인덱스를 실제로 적용시키려면 ANALYZE TABLE 문으로 먼저 테이블을 분석/처리해줘야 한다.
ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';
Index_length(보조 인덱스 크기)는 16KB(16384Byte)이다. 실제로는 이것보다 훨씬 작지만, 보조 인덱스가 1건이면 최소 1페이지가 필요하기 때문에 MySQL의 1페이지 크기인 16KB가 표시되었다.
이번에는 인원수(mem_number)에 중복을 허용하지 않는 고유 보조 인덱스를 생성해보자.
블랙핑크, 마마무, 레드벨벳의 인원수가 4이기에 이미 중복된 값이 있다. 그래서 인원수 열에는 고유 보조 인덱스를 생성할 수 없다.
CREATE UNIQUE INDEX idx_member_mem_number ON member (mem_number);
이번에는 회원 이름(mem_name)에 고유 보조 인덱스를 생성해보겠다.
CREATE UNIQUE INDEX idx_member_mem_name ON member (mem_name);
Non_Unique가 0이라는 것은 중복을 허용하지 않는다는 의미이므로, 고유 보조 인덱스가 잘 생성된 것이다.
이번에는 우연히 마마무와 이름이 같은 태국의 가수 그룹이 회원가입을 항려 한다. 회원 아이디인 기본 키만 다르게 지정해보자.
INSERT INTO member VALUES('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');
이것은 조금 전에 생성한 고유 보조 인덱스로 인해서 중복된 값을 입력할 수 없기 때문이다.
이렇게 이름이 중복된다고 회원가입이 안 되면, 실제 사이트에서는 심각한 문제가 발생할 수 있다.
그러므로 고유 보조 인덱스를 지정할 때 현재 중복된 값이 없다고 무조건 설정하면 안 되며, 업무상 절대로 중복되지 않는 열(주민등록번호, 학번, 이메일 주소 등)에만 UINIQUE 옵션을 사용해서 인덱스를 생성해야 한다.
인덱스의 활용 실습
먼저 지금까지 만든 인덱스가 어느 열에 있는지 확인해보자.
ANALYZE TABLE member;
SHOW INDEX FROM member;
이번에는 전체를 조회해보자. 10건의 회원이 조회되었을 것이다. 하지만 이 SQL은 인덱스와 아무런 상관이 없다.
인덱스를 사용하려면 인덱스가 생성된 열 이름이 SQL 문에 있어야 한다.
SELECT * FROM member;
인덱스를 사용했는지 여부는 결과 중 [Execution Plan] 창을 확인하면 된다.
전체 테이블 검색 (Full Table Scan)을 한 것이 된다. 책과 비교하면 첫 페이지부터 끝 페이지까지 넘겨본 것이다.
이번에는 인덱스가 있는 열을 조회해보자. 조회는 잘 된다.
SELECT mem_id, mem_name, addr FROM member;
다시 [Execution Plan] 창을 확인해보면 역시 전체 테이블 검색을 했다.
열 이름이 SELECT 다음에 나와도 인덱스를 사용하지 않는다.
이번에는 인덱스가 생성된 mem_name 값이 ‘에이핑크’인 행을 조회해보자.
SELECT mem_id, mem_name, addr FROM member WHERE mem_name = '에이핑크';
다시 [Execution Plan] 창을 확인해보면 Single Row(constant) 라고 되어 있다. 이 용어는 인덱스를 사용해서 결과를 얻었다는 의미이다.
이번에는 숫자의 범위로 조회해보겠다. 먼저 숫자로 구성된 인원수 (mem_number)로 단순 보조 인덱스를 만들어보겠다.
CREATE INDEX idx_member_mem_number ON member (mem_number);
ANALYZE TABLE member; -- 인덱스 적용인원수 7명 이상의 그룹의 이름과 인원수를 조회해보겠다. 결과는 4건이 나왔다.
SELECT mem_name, mem_number FROM member WHERE mem_number >= 7;
[Execution Plan] 창에서 인덱스를 사용한 것을 확인 할 수 있다.
mem_number ≥ 7 과 같이 숫자의 범위로 조회하는 것도 인덱스를 사용한다.
인덱스를 사용하지 않을 때
인덱스가 있고 WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우도 있다.
SELECT mem_name, mem_number FROM member WHERE mem_number >= 1;
전체 테이블 검색을 진행했다. 앞에서 7명 이상일 때는 틀림없이 인덱스를 사용했는데, 1명 이상으로 설정하니 전체 테이블 검색을 했다.
인덱스가 있더라도 MySQL이 인덱스 검색보다는 전체 테이블 검색이 낫겠다고 판단했기 때문이다.
이 경우에는 대부분의 행을 가져와야 하므로 인덱스를 왔다 갔다 하는 것보다는 차라리 테이블을 차례대로 읽는 것이 효율적이다.
또 다른 경우를 살펴보자.
인원수(mem_number)의 2배를 하면 14명 이상이 되는 회원의 이름과 인원수를 검색해보자.
SELECT mem_name, mem_number FROM member WHERE mem_number * 2 >= 14;[Execution plan]을 확인해보자. 인덱스 검색을 기대했는데, 전체 테이블 검색을 했다.
WHERE mem_number ≥ 7로 조회했을 때는 인덱스 검색을 했었다. 결론을 말하면 WHERE 문에서 열에 연산이 가해지면 인덱스를 사용하지 않는다.
이런 경우에는 다음과 같이 수정하면 된다. 결과는 동일하게 나올 것이다.
SELECT mem_name, mem_number FROM member WHERE mem_number >= 14/2;
WHERE mem_number * 2 ≥ 14 와 WHERE mem_number ≥ 14/ 2로 실행한 결과의 순서가 다르다.
전체 테이블 검색과 인덱스 검색의 차이로 순서가 다를 뿐이고 결과 데이터는 동일하다.
[Execution plan]을 살펴보면 인덱스를 사용했다. 그러므로 WHERE 절에 나온 열에는 아무런 연산을 하지 않는 것이 좋다.
인덱스 제거 실습
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;클러스터형 인덱스를 먼저 제거해도 되지만, 데이터를 쓸데없이 재구성해서 시간이 더 오래 걸린다.
마지막으로 기본 키 지정으로 자동 생성된 클러스터형 인덱스를 제거하면 된다.
Primary Key에 설정된 인덱스는 DROP INDEX 문으로 제거되지 않고 ALTER TABLE 문으로만 제거할 수 있다.
ALTER TABLE member DROP PRIMARY KEY;
member의 mem_id 열을 buy가 참조하고 있기 때문이다. 그러므로 기본 키를 제거하기 저에 외래 키를 제거해야 한다.
테이블에는 여러 개의 외래 키가 있을 수 있다. 그래서 먼저 외래 키의 이름을 알아내야 한다.
information_schema 데이터베이스의 referential_constraints 테이블을 조회하면 외래 키의 이름을 알 수 있다ㅣ.
SELECT table_name, constraint_name FROM information_schema.referential_constraints WHERE constraint_schema = 'market_db';
이제 외래 키 이름을 알았으니 외래 키를 먼저 제거하고 기본 키를 제거하면 된다.
이제 모든 인덱스가 제거 되었다.
ALTER TABLE by DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member DROP PRIMARY KEY;❕인덱스를 효과적으로 사용하는 방법
인덱스는 열 단위에 생성된다.
하나의 열에 하나의 인덱스를 생성할 수 있다고 기억하자.
사실 하나의 열에 2개 이상의 인덱스를 만들 수도 있고, 2개 이상의 열을 묶어서 하나의 인덱스로 만들 수도 있다.
하지만 이런 경우는 드물기 때문에 하나의 열에 하나의 인덱스를 만드는 것이 가장 일반적이다.
WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
SELECT 문을 사용할 때, WHERE 절의 조건에 해당 열이 나와야 인덱스를 사용한다.
market_db의 member를 사용하는 SQL은 다음과 같다.
SELECT mem_id, mem_name, mem_number, addr FROM member WHERE mem_name = '에이핑크';만약 member 테이블에는 단지 이 SQL만 사용한다고 가정한다면, 이 SQL에서 mem_id, mem_number, addr 열에는 인덱스를 전혀 사용하지 않는다.
WHERE 절에 있는 mem_nmae 열의 인덱스만 사용한다. 그러므로 mem_name 열 외에는 다른 인덱스를 만드는 것은 낭비가 된다.
WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.
열에 들어갈 데이터의 종류가 몇 가지 되지 않으면 인덱스가 큰 효과를 내지 못한다.
클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
클러스터형 인덱스는 데이터 페이지를 읽는 수가 보조 인덱스보다 적기 때문에 성능이 더 우수하다.
그러므로 하나밖에 지정하지 못하는 클러스터형 인덱스(기본 키)는 조회할 때 가장 많이 사용되는 열에 지정하는 것이 효과적이다.
사용하지 않는 인덱스는 제거한다.
실제로 사용되는 SQL을 분석해서 WHERE 조건에서 사용되지 않는 열의 인덱스는 제거할 필요가 있다.
그러면 공간을 확보할 뿐 아니라 데이터 입력 시 발생되는 부하도 많이 줄일 수 있다.
정리
- CREATE INDEX 문으로 인덱스를 직접 생성한다.
- 기본 키 및 고유 키로 자동 생성된 인덱스는 ALTER TABLE로 제거하고, CREATE INDEX 문으로 생성한 인덱스는 DROP INDEX 문으로 제거한다.
- 단순 보조 인덱스는 중복을 허용하는 보조 인덱스이며, CRAETE INDEX 문을 사용한다.
- 고유 보조 인덱스는 중복을 허용하지 않는 보조 인덱스이며, CREATE UNIQUE INDEX 문을 사용한다.
