[SQL] 06-3 인덱스의 실제 사용

인덱스

인덱스의 실제 사용

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용하고, 제거하기 위해서는 DROP INDEX 문을 사용한다.

인덱스 생성과 제거 문법

인덱스 생성 문법

Primary Key 문법을 사용하면 클러스터형 인덱스가, Unique 문법을 사용하면 보조 인덱스가 자동으로 생성된다.

그 외에 직접 인덱스를 생성하려면 CREATE INDEX 문을 사용해야 한다.

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

UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.

ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어 준다.
기본은 ASC로 만들어지며, DESC로 만드는 경우는 거의 없다.

인덱스 제거 문법

CREATE INDEX로 생성한 인덱스는 DROP INDEX로 제거한다.

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

기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.
ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.

인덱스 제거

하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있는 경우, 인덱스를 제거할 때는 보조 인덱스부터 제거하는 것이 더 좋다. 
클러스터형 인덱스부터 제거하면 니부적으로 데이터가 재구성되기 때문이다.  
또한, 인덱스가 많이 생성되어 있는 테이블의 경우 사용하지 않는 인덱스는 과감히 제거해주는 것이 좋다.

인덱스 생성과 제거 실습

인덱스 생성 실습

인덱스 실습을 위해 ‘인터넷 마켓’ 데이터베이스를 생성한 후 진행하겠다.

DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE market_db;

USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);
CREATE TABLE buy -- 구매 테이블
(  num 		    INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
   mem_id   	CHAR(8) NOT NULL, -- 아이디(FK)
   prod_name 	CHAR(6) NOT NULL, --  제품이름
   group_name 	CHAR(4)  , -- 분류
   price     	INT  NOT NULL, -- 가격
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');

INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);

먼저 데이터의 내용을 확인해보겠다.

USE market_db;
SELECT * FROM member;

SHOW INDEX 문으로 member에 어떤 인덱스가 설정되어 있는지 확인해보겠다.

SHOW INDEX FROM member;

-> mem_id 열에 클러스터형 인덱스 1개만 설정되어 있다.

이번에는 SHOW TABLE STATUS 문으로 인덱스의 크기를 확인해보겠다.

SHOW TABLE STATUS LIKE 'member';

결과 중에 Data_length는 클러스터형 인덱스(또는 데이터)의 크기를 Byte 단위로 표기한 것이다.
그런데 MySQL의 1페이지 크기는 기본적으로 16KB이므로 클러스터형 인덱스는 16384(16*1024) = 1페이지가 할당되어 있는 것이다.
실제로는 데이터의 내용이 많지 않아서 16KB까지 필요 없지만, 최소 단위가 1페이지이므로 1페이지에 해당하는 16KB가 할당되어 있는 것이다.
Index_length는 보조 인덱스의 크기인데 member는 보조 인덱스가 없기 때문에 표기되지 않았다.

주소(addr)에 중복을 허용하는 단순 보조 인덱스를 생성하겠다.

CREATE INDEX idx_member_addr
    ON member (addr);

새로 생성된 인덱스를 확인해보겠다.

SHOW INDEX FROM member;

Key_name에서 생성한 단순 보조 인덱스의 이름이 확인된다.
Column_name에서는 어느 열에 지정되었는지 확인된다.

Non_unique가 1로 설정되어 있으므로 고유 보조 인덱스가 아니다.

이번에는 전체 인덱스의 크기를 다시 확인해보겠다.

SHOW TABLE STATUS LIKE 'member';

Index_length 부분이 보조 인덱스의 크기인데, 이상하게도 크기가 0으로 나왔다.

생성한 인덱스를 실제로 적용시키려면 ANALYZE TABLE 문으로 먼저 테이블을 분석/처리해줘야 한다.

ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

-> 이제 보조 인덱스가 생성된 것이 확인된다.

이번에는 인원수(mem_number)에 중복을 허용하지 않는 고유 보조 인덱스를 생성해보겠다.

CREATE UNIQUE INDEX idx_member_mem_number
    ON member (mem_number);

-> 인원수에 이미 중복된 값이 있으므로 인원수 열에는 고유 보조 인덱스를 생성할 수 없다.

이번에는 회원 이름(mem_name)에 고유 보조 인덱스를 생성해보겠다.

CREATE UNIQUE INDEX idx_member_mem_name
    ON member (mem_name);

고유 보조 인덱스가 잘 만들어졌는지 확인해보겠다.

SHOW INDEX FROM member;

-> Non_unique가 0이라는 것은 중복을 허용하지 않는다는 의미이므로, 고유 보조 인덱스가 잘 생성된 것이다.

이번에는 우연히도 마마무와 이름이 같은 가수 그룹이 회원가입을 하려고 한다.
회원 아이디인 기본 키만 다르면 되므로 MOO로 지정하겠다.

INSERT INTO member VALUES('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');

-> 고유 보조 인덱스로 인해서 중복된 값을 입력할 수 없기 때문에 오류가 발생한다.

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

인덱스의 활용 실습

생성한 인덱스를 활용해보겠다.

먼저 지금까지 만든 인덱스가 어느 열에 있는지 확인해보겠다.

ANALYZE TABLE member;
SHOW INDEX FROM member;

-> 회원 아이디(mem_id), 회원 이름(mem_name), 주소(addr) 열에 인덱스가 생성되어 있다.

이번에는 전체를 조회해보겠다.

SELECT * FROM member;

10건의 회원이 조회되었을 것이다.
그런데 이 SQL은 인덱스와 아무런 상관이 없다.
인덱스를 사용하려면 인덱스가 생성된 열 이름이 SQL 문에 있어야 한다.

인덱스를 사용했는지 여부는 결과 중 [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명 이상인 그룹의 이름과 인원수를 조회해보겠다.

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number >= 7;

[Execution Plan] 창에서 인덱스를 사용한 것을 확인할 수 있다.
mem_number >= 7과 같이 숫자의 범위로 조회하는 것도 인덱스를 사용한다.

인덱스를 사용하지 않을 때

인덱스가 있고 WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우가 있다.
인원 수가 1명 이상인 회원을 조회해보겠다.

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number >= 1;

회원은 1명 이상이므로 10건 모두 조회된다.

[Execution Plan] 창을 살펴보면 전체 테이블 검색을 한 것을 확인할 수 있다.
인덱스가 있더라도 MySQL이 인덱스 검색보다는 전체 테이블 검색이 낫겠다고 판단했기 때문이다.
이 경우에는 대부분의 행을 가져와야 하므로 인덱스를 왔다 갔다 하는 것보다 차라리 테이블을 차례대로 읽는 것이 효율적이다.

또 다른 경우를 살펴보겠다.
인원수(mem_number)의 2배를 하면 14명 이상이 되는 회원의 이름과 인원수를 검색해보겠다.

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number*2 >= 14;

4명의 회원이 나왔다.

[Execution plan]을 살펴보면 전체 테이블 검색을 하였다.
결론을 말하면 WHERE 문에서 열에 연산이 가해지면 인덱스를 사용하지 않는다.

이런 경우에는 다음과 같이 수정하면 된다.

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number >= 14/2;

결과는 동일하게 나온다.

[Execution plan]을 살펴보면 인덱스를 사용했다.
따라서 WHERE 절에 나온 열에는 아무런 연산을 하지 않는 것이 좋다.

인덱스 제거 실습

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

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 buy
    DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member
    DROP PRIMARY KEY;

이제 모든 인덱스가 제거되었다.

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

• 인덱스는 열 단위에 생성된다.

• WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.

• WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.

• 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.

• 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.

• 사용하지 않는 인덱스는 제거한다.