[SQL] 인덱스(index)

인덱스(index)는 데이터를 빠르게 찾게 도와준다.

 

인덱스의 장점

• SELECT 문으로 검색하는 속도 향상.
• 그 결과로 전체 시스템의 성능이 향상.

인덱스의 단점

• 인덱스도 공간을 차지하기 때문에 추가적인 공간이 필요하다.
• 처음에 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
• SELECT가 아닌 데이터의 변경(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능 저하.

 

 

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

기본키로 지정하면 자동 생성되며 테이블에 1개만 만들 수 있고 기본키의 열을 기준으로 자동 정렬 됨.

영어 사전처럼 책의 내용이 이미 알파벳 순서대로 정렬되어 있다. 그래서 별도의 찾아보기(책의 맨뒤)가 없다.

책 자체가 찾아보기이다.

2. 보조 인덱스(Secondary Index)

고유 키로 지정하면 자동 생성되며 여러 개를 만들 수 있고 자동 정렬되지 않음.

찾아보기가 별도로 있고, 찾아보기에서 해당 단어를 찾은 후에 옆에 표시된 페이지를 펼쳐 내용을 찾는다.

 

 

자동으로 생성되는 인덱스

인덱스는 테이블의 열(Column) 단위에서 생성되며, 하나의 열에는 하나의 인덱스를 생성.
기본키는 테이블에 하나만 지정할 수 있으므로 클러스터형 인덱스는 테이블에 한 개만 만들 수 있다.

USE market_db;
CREATE TABLE table1 (
    col1 INT PRIMARY KEY,
    col2 INT,
    col3 INT
);

 

SHOW INDEX문을 통해 인덱스 정보를 확인할 수 있다.

SHOW INDEX FROM table1;

Key_name 부분을 보면 PRIMARY라고 되어 있는데

이것이 기본키로 설정해서 '자동으로 생성된 인덱스'라는 의미. 이게 클러스터형 인덱스이다.

Column_name 부분은 어떤 열에 인덱스가 만들어졌는지 나타낸다.

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;

 

 

클러스터형 인덱스

클러스터형 인덱스는 영어사전과 비슷하다고 했다. 이는 알파벳 순서로 정렬되어 있다는 것이다.

마찬가지로 어떤 열을 기본 키로 지정하면 그 열을 기준으로 정렬된다.

 

먼저 기본 키가 없는 상태로 테이블을 보자.

CREATE TABLE member
(
    mem_id CHAR(8),
    mem_name VARCHAR(10),
    mme_number INT,
    addr CHAR(2)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');
SELECT * FROM member;

 

기본키로 지정하고 테이블의 정보를 보자.

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

기본 키로 지정되어 클러스터 형 인덱스가 생성되었고 mem_id 열을 기준으로 정렬되었다.

 

기본 키 제거

ALTER TABLE member DROP PRIMARY KEY;

 

보조 인덱스

보조 인덱스는 테이블에 여러 개 설정할 수 있다. 고유 키를 여러 열에 지정하는 것과 마찬가지이다.

보조 인덱스는 책의 찾아보기와 비교를 했는데, 찾아보기를 만들면 책의 뒤에 만들어지는 것이지 책의 내용이 변경되는 것이 아니다.

 

고유 키 없이 테이블을 만들어 보겠다.

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('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');
SELECT * FROM member;

 

이제 mem_id에 고유 키를 추가해보겠다.

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

내용이 정렬되지 않고 그대로이다.

여기서 mem_name에도 고유 키를 추가해보자.

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

그래도 정렬이 되지 않고 그대로이다.

여기에 데이터를 추가해보면?

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

 

그대로~ 맨 뒤에 데이터가 붙었다.

 

모든 내용은 '혼자 공부하는 SQL' 도서를 기반으로 학습 후 정리한 내용입니다.