인덱스 실제로 사용해보기
인덱스를 생성하기 위해서는 CREATE INDEX (인덱스명) ON 테이블명 (컬럼명) 문을 사용하고, 인덱스를 제거하기 위해서는 DROP INDEX (인덱스명) ON (테이블명) 문을 사용하면 된다.
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명 (컬럼명) [ASC | DESC]
DROP INDEX 인덱스명 ON 테이블명기본 키는 클러스터형 인덱스, 고유 키는 보조 인덱스가 자동 생성되고 CREATE INDEX를 통해 직접 인덱스를 생성할 수 있다. 참고로 CREATE INDEX로 생성되는 인덱스는 보조 인덱스이다.
보조 인덱스는 데이터 중복 여부(UNIQUE)에 따라 단순 보조 인덱스와 고유 보조 인덱스로 나뉜다. UNIQUE는 중복이 안 되는 고유 인덱스를 생성하는 것으로 생략 시 중복을 허용하는 것이고, 생략하지 않을시 UNIQUE로 인덱스를 생성되어 중복이 허용되지 않는다. 따라서 UNIQUE를 붙이려면 기존의 입력 값들과 향후 입력 값들에 중복이 존재해서는 안된다.
인덱스 생성에서 ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 생성하는 것인데, 기본은 ASC이다.
기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거할 수 없다. 이는 ALTER TABLE 문으로 제약 조건 제거 시 자동으로 인덱스가 삭제된다. 만약 하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있다면 인덱스 제거 시 보조 인덱스부터 삭제를 권장한다. 왜냐하면 클러스터형 인덱스부터 제거하면 내부적으로 데이터가 재구성도기 때문이다. 또한 사용하지 않는 인덱스는 과감히 제거하는 것이 필요하다.
인덱스 생성
<실행>
USE market_db;
SHOW INDEX FROM member;<결과>
SHOW INDEX FROM (테이블명)을 통해 해당 테이블에 생성된 인덱스를 확인할 수 있다. 확인 결과 member 테이블에는 mem_id에 클러스터형 인덱스가 1개 설정되어 있다.
<실행>
SHOW TABLE STATUS LIKE 'member';<결과>
SHOW TABLE STATUS 문을 사용하여 테이블에 생성된 인덱스 정보를 추출할 수 있다. Data_length는 클러스터형 인덱스(또는 데이터)의 크기를 Byte 단위로 표기한 것으로, MySQL의 1페이지 크기는 기본적으로 16KB이므로 클러스터형 인덱스는 16384/(16*1024) = 1페이지가 할당된다.
Index_length는 보조 인덱스의 크기로 현재 보조 인덱스가 없기에 표시되지 않는 것을 확인할 수 있다.
이번에는 실제로 보조 인덱스를 생성해보도록 하자.
<실행>
CREATE INDEX idx_member_addr ON member (addr);
SHOW INDEX FROM member;<결과>
CREATE INDEX를 통해 주소를 저장하는 열에 중복을 허용하는 단순 보조 인덱스(중복 허용 인덱스)를 생성했다. 그리고 SHOW INDEX를 통해 Key_name에서 인덱스명을, Column_name에서 인덱스가 생성된 열을 확인할 수 있다.
그렇다면 보조 인덱스를 생성했으니 테이블의 상태를 확인하면 Index_length에 그만큼의 크기가 반영되어 있을까? 확인해보도록 하자.
<실행>
SHOW TABLE STATUS LIKE 'member';
<결과>
보조 인덱스를 생성했음에도 Index_length 크기가 0으로 표기된다. 이는 생성한 인덱스를 실제로 적용시키려면 ANALYZE TABLE 문으로 먼저 테이블 분석/처리가 필요하다.
<실행>
ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';<결과>
ANALYZE TABLE 문을 사용한 결과 Index_length가 16KB로 보조 인덱스가 적용된 것을 확인할 수 있다.
이번에는 고유 보조 인덱스를 생성해보도록 하자.
<실행>
CREATE UNIQUE INDEX idx_member_mem_name ON member (mem_name);
SHOW INDEX FROM member;<결과>
mem_name 열에는 중복된 값이 없으므로 고유 보조 인덱스가 생성 가능하다. 따라서 CREATE UNIQUE INDEX를 통해 고유 보조 인덱스를 생성하였다. Non_unique가 0이라는 것, 즉 1이라는 것(True)은 중복을 허용하지 않겠다는 것이다.
참고로 중복된 데이터가 많은 열에 인덱스를 생성하는 것은 의미가 없고, 오히려 성능이 저하된다.
💡왜 중복된 데이터가 많은 열에 인덱스를 생성하면 안될까?
인덱스는 기본적으로 데이터의 고유한 값을 기반으로 데이터를 정렬하고 해당 데이터의 위치를 빠르게 찾기 위해 사용한다. 따라서 검색의 효율성은 데이터의 분포도에 따라 달라지는데 중복된 데이터가 많으면 인덱스가 좁혀줄 수 있는 범위가 제한적이기 때문이다.
그렇다면 이번에는 인덱스를 사용한 결과가 어떻게 나오는지 실행 계획을 통해 확인해보도록 하자.
<실행>
ANALYZE TABLE member;
SHOW INDEX FROM member;
SELECT * FROM member;<결과>
현재까지 생성된 모든 인덱스를 적용시키고 위해 ANALYZE TABLE 사용하고 SELECT 문을 실행시킬 경우 모든 열이 조회된다. 그러나 이는 인덱스가 사용되지 않았는데, 인덱스를 사용하려면 인덱스가 생성된 열 이름이 SQL 문에 존재해야 한다.
Execution Plan, 즉 실행 계획을 확인하면(DBeaver에서는 ctrl + shift + e를 통해 확인할 수 있다.) 전체 테이블 검색(All, Full Table Scan)으로 확인되는데, 즉 처음부터 끝까지 모든 열을 확인했다는 뜻이다.
SELECT에 인덱스를 적용시킨 열을 넣어도 전체 테이블 검색이 적용된다. 그렇다면 인덱스를 사용하려면 어떻게 해야할까? 인덱스를 사용하려면 WHERE 문에 인덱스가 생성된 열을 포함시켜야 한다.
<실행>
SELECT mem_id, mem_name, addr FROM member WHERE mem_name = '에이핑크'<결과>
WHERE 문에 인덱스가 적용된 열을 조건에 넣고 검색 시 Execution Plan에서 constant를 확인할 수 있다. 이는 인덱스를 사용해서 결과를 추출했다는 것을 의미한다. 결국 WHERE 문에 인덱스가 생성된 열이 들어 있어야 인덱스를 사용하는 것으로 알 수 있다.
참고로 Execution Plan 창에 Full Table Scan(all)을 제외하고 나머지는 모두 인덱스를 사용했다는 것을 의미한다.
<실행>
CREATE INDEX idx_member_mem_number ON member (mem_number);
ANALYZE TABLE member;
SELECT mem_name, mem_number FROM member WHERE mem_number >= 7;<결과>
숫자의 범위로 데이터를 조회하는 것도 마찬가지로 전체 테이블 검색이 ragne로 범위 검색이 이루어진다. 따라서 인덱스가 사용된 것을 확인할 수 있다.
그런데 WHERE 절에 인덱스를 적용한 열을 명시했다고 해서 반드시 사용되는 것은 아니다.
SELECT mem_name, mem_number FROM member WHERE mem_number >= 1;WHERE 문에 인덱스가 생성된 컬럼을 사용했으나 전체 테이블 검색이 될때도 있다. 이는 인덱스가 존재하더라도 MySQL이 인덱스 검색보다 전체 테이블 검색이 낫겠다고 판단 시 전체 테이블 검색을 사용하게 되는 경우이다. 위 쿼리에서 모든 mem_number는 1보다 크므로 인덱스를 사용하는 것보다 전체 테이블 검색이 낫다고 판단했기에 전체 테이블 검색이 일어났다.
<실행>
SELECT mem_name, mem_number FROM member WHERE mem_number*2 >= 14;<결과>
또한 WHERE 문에서 인덱스를 건 컬럼에 연산이 가해지면 검색 시 인덱스가 사용되지 않는다. 따라서 위 쿼리는 WHERE mem_number ≥ 7로 수정하는 것이 권장된다.
인덱스 제거
클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것을 권장한다. 왜냐하면 클러스터형 인덱스를 먼저 제거 시, 데이터를 쓸데없이 재구성하는 시간이 소요되기 때문이다.
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;또한 기본 키 지정으로 자동 생성된 클러스터형 인덱스는 DROP INDEX 문이 아닌 ALTER TABLE 문으로만 제거 가능하고, 위 경우에 기본 키를 삭제할 경우 외래 키 제약 조건에 의해 삭제가 불가하므로 기본 키 제거 전 외래 키 관계를 먼저 제거해야 한다.
SELECT table_name, constraint_name
FROM information_schema.referential_constraints
WHERE constraint_schema = 'market_db'information_schema 데이터베이스의 referential_constraints 테이블을 조회 시 외래 키 제약 조건의 이름을 확인할 수 있다. information_schema 데이터베이스의 referential_constraints 테이블은 MySQL 안에 원래 포함되어 있는 시스템 데이터베이스 테이블로 MySQL 전체의 외래 키 정보를 포함하고 있다.
ALTER TABLE buy DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member DROP PRIMARY KEY;따라서 앞선 외래 키 정보를 확인한 후 외래 키를 먼저 제거 후 기본 키를 제거할 수 있다.
인덱스의 효과적 사용 방법
인덱스는 하나의 열 단위로 생성하자
하나의 열에 2개 이상의 인덱스를 만들거나 2개 이상의 열을 묶어서 하나의 인덱스로 생성 가능하나 드물기에 하나의 열에 하나의 인덱스를 만드는 것이 가장 일반적이다.
WHERE 절에서 사용되는 열에 인덱스 생성이 필요하다
SELECT문을 사용할 때, WHERE 절의 조건에 해당 열이 나와야 인덱스가 사용되기 때문에 WHERE 절에 자주 사용되는 열에 인덱스를 생성하자.
WHERE 절에 사용되더라도 자주 사용해야 가치가 있다
특정 열에 인덱스를 생성해서 효율이 아주 좋아진다 해도 해당 SELECT 문이 1년에 1번 정도만 사용되고 주로 INSERT 문만 발생한다면 궁극적으로 성능은 저하된다. 따라서 정말 자주 사용되는 열인지 생각해보고 인덱스를 생성하자.
데이터 중복이 높은 열에 인덱스를 생성하는 것은 효과가 없다
열에 들어갈 데이터의 종류가 몇 가지 되지 않으면 인덱스는 큰 효과를 발휘하지 못한다.
클러스터형 인덱스는 테이블당 하나만 생성 가능하다
클러스터형 인덱스(기본 키)는 데이터 페이지를 읽는 수가 보조 인덱스보다 적기에 성능이 우수하다. 따라서 클러스터형 인덱스는 조회할 때 가장 많이 사용되는 열에 지정하는 것이 효과적이다.
사용하지 않는 인덱스는 제거하자
WHERE 조건에서 사용되지 않는 열의 인덱스는 제거가 필요하다. 왜냐하면 인덱스는 공간을 차지하고 데이터 입력 시 발생하는 부하를 유발하기 때문이다.
출처
- 혼자 공부하는 SQL(우재남 저, 한빛미디어)
