Data Analysis/혼공SQL
[혼공SQL] 6-3 인덱스의 실제 사용
알밤바
2022. 8. 9. 12:12
728x90
반응형
본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.
인덱스 생성 : CREATE INDEX
인덱스 제거 : DROP INDEX
1. 인덱스 생성 문법
- 테이블을 생성할 때 특정 열을 기본 키, 고유 키로 설정하면 인덱스가 자동 생성됨
- Primary Key 문법 사용 : 클러스터형 인덱스 생성
- Unique 문법 사용 : 보조 인덱스 생성
- 그 외 직접 보조 인덱스를 생성하려면 CREATE INDEX문을 사용해야 함
- (CREATE INDEX 로 생성되는 인덱스는 보조인덱스임)
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC | DESC]
- UNIQUE는 중복이 안 되는 고유 인덱스를 만듦 (생략하면 중복 허용)
- CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 됨
- 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없음
2. 인덱스 제거 문법
DROP INDEX 인덱스_이름 ON 테이블_이름
- 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.
- ALTER TABLE문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스 제거 가능
- 하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있는 경우, 보조 인덱스부터 제거하는 것이 좋음
3. 실습
3.1 일반 인덱스
- member 테이블에 어떤 인덱스가 설정되어 있는지 확인 (SHOW INDEX)
SHOW INDEX FROM member;
- key_name = PRIMARY → 클러스터형 인덱스
- 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 테이블은 보조 인덱스가 없기에 표기되지 않음
3.2 보조 인덱스
단순 보조 인덱스 : 중복을 허용
고유 보조 인덱스 : 중복을 허용하지 않음 [+] 클러스터형 인덱스와 보조 인덱스 동시에 사용 가능
📌 단순 보조 인덱스
- addr에 중복을 허용하는 단순 보조 인덱스를 생성 (index 이름 : idx_member_addr)
CREATE INDEX idx_member_addr
ON member (addr);
- 인덱스 설정 확인
- Non_unique = 1 → 단순 보조 인덱스 (중복 허용 ○)
SHOW INDEX FROM member;
- 인덱스 크기 확인
- 보조 인덱스가 추가되었지만 Index_length가 0이 나옴
- 생성된 인덱스를 실제로 적용시키려면 ANALYZE TABLE 문으로 먼저 테이블을 분석/처리해야 함
SHOW TABLE STATUS LIKE 'member';
- 테이블 분석/처리하기 (ANALYZE TABLE)
- 보조 인덱스가 생성된 것을 확인됨 (크기 : 16384Byte = 16KB)
-- 테이블을 분석/처리하기 (ANALYZE TABLE 문 활용)
ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';
📌 고유 보조 인덱스
- 인원수(mem_number)에 고유 보조 인덱스 생성 (CREATE UNIQUE INDEX)
CREATE UNIQUE INDEX idx_member_mem_number
ON member (mem_number);
- 인원수가 4인 중복 값이 있기에 고유 보조 인덱스를 생성할 수 없음
- 인원수 열에 인덱스를 꼭 만들고 싶다면 ‘단순 보조 인덱스’를 생성하면 됨
- 회원 이름(mem_name)에 고유 보조 인덱스 생성
CREATE UNIQUE INDEX idx_member_mem_name
ON member (mem_name);
-- 인덱스 설정 확인
SHOW INDEX FROM member;
- Non_unique = 0 → 고유 보조 인덱스 (중복 허용 x)
- 이름(mem_name)이 '마마무'인 태국 가수 데이터 추가
-- 이름(mem_name)이 '마마무'인 태국 가수 데이터 추가
into member values('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');
▶ Error Code: 1062. Duplicate entry '마마무' for key 'member.idx_member_mem_name'
- 이름이 중복된다고 회원가입이 되지 않는다면 실제로 문제가 발생함
→ 고유 보조 인덱스를 지정할 때는 절대로 중복되지 않는 열에만 해야 함!!!
3.3 인덱스 활용 실습
📌 인덱스 확인
analyze table member; -- 지금까지 만든 인덱스를 모두 적용
show index from member;
📌 인덱스를 사용했는지 여부 확인
- 데이터 전체 조회 (SELECT *)
select * from member;
- 해당 쿼리문은 인덱스와 아무런 상관이 없음
- 인덱스를 사용하려면 인덱스가 생성된 열 이름이 SQL문에 있어야 함!
- [Execution Plan] 창을 통해 인덱스 사용 여부 확인 가능
▶ 위의 상황은 Full Table Scan을 한 것으로 확인 (처음부터 끝까지 모두 확인)
- 인덱스가 있는 열 조회
select mem_id, mem_name, addr from member;
▶ 역시나 열 이름이 SELECT 다음에 나와도 인덱스를 사용하지 않음
- 인덱스가 생성된 특정 열 조회
- WHERE 절에 열 이름이 들어 있어야 인덱스를 사용함
select mem_id, mem_name, addr
from member
where mem_name = '에이핑크';
▶ Single Row (constant) : 인덱스를 사용하여 결과를 얻었다는 의미
Full Table Scan을 제외한 나머지 메시지 모두 인덱스를 사용했다는 의미임
- 숫자의 범위로 조회
- 인원수(mem_number)로 단순 보조 인덱스 생성
create index idx_member_mem_number
on member (mem_number);
analyze table member; -- 인덱스 적용
- 인원수(mem_number)가 7명 이상인 그룹의 이름과 인원수 조회
select mem_name, mem_number
from member
where mem_number >= 7;
▶ 숫자의 범위로 조회하는 것도 인덱스를 사용함
3.4 인덱스를 사용하지 않을 때
- 인덱스가 있고 WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우가 있음
- 인원수가 1명 이상인 회원 조회
- 인덱스가 있더라도 MySQL이 인덱스 검색보다는 전체 테이블 검색이 낫겠다고 판단했기 때문
select mem_name, mem_number
from member
where mem_number >= 1;
- 인원수(mem_number)의 2개를 하면 14배 이상이 되는 회원의 이름과 인원수 검색
- where mem_number >= 7와 동일한 조건임에도 Full Table Scan 진행
- WHERE 문에서 열에 연산이 가해지면 인덱스를 사용하지 않음
select mem_name, mem_number
from member
where mem_number*2 >= 14;
- 연산을 다르게 수정함 (열에 연산을 하지 않음)
select mem_name, mem_number
from member
where mem_number >= 14/2;
3.5 인덱스 제거 실습
- 인덱스 이름 확인
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;
- 기본 키 지정으로 자동 생성된 클러스터형 인덱스 제거
- PK에 설정된 인덱스는 ALTER TABLE 문으로만 제거 가능 (DROP INDEX 문으로 제거 불가)
alter table member
drop primary key;
▶ Error Code: 1553. Cannot drop index 'PRIMARY': needed in a foreign key constraint
- member의 mem_id 열을 구매 테이블(buy)이 참조하고 있기에 오류 발생
- 기본 키(PK)를 제거하기 전 외래 키(FK) 관계를 제거해야 함
- 테이블에는 외래 키(FK)가 여러 개 있을 수 있으므로 외래 키의 이름을 알아내야 함
- information_schema 데이터베이스의 referential_constraints 테이블 조회
- (MySQL 안에 원래 포함되어 있는 시스템 데이터베이스와 테이블임)
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;
4. 인덱스를 효과적으로 사용하는 방법
- 인덱스는 열 단위에 생성
하나의 열에 하나의 인덱스를 만드는 것이 가장 일반적임 - WHERE 절에서 사용되는 열에 인덱스를 만들어야 함
SELECT 문을 사용할 때, WHERE 절의 조건에 해당 열이 나와야 인덱스를 사용함. - WHERE 절에 사용되더라도 자주 사용해야 가치가 있음
인덱스를 생성해서 효율이 좋더라도 자주 사용하지 않으면 큰 의미 없음
어떠한 테이블이 INSERT 작업을 위주로 한다면 인덱스를 생성하지 않는 것이 좋음 (인덱스는 INSERT의 성능을 나쁘게 하기 때문) - 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없음
열에 들어갈 데이터의 종류가 몇 가지 되지 않으면 인덱스가 큰 효과를 내지 못함 - 클러스터형 인덱스는 테이블 당 하나만 생성할 수 있음
클러스터형 인덱스는 데이터 페이지를 읽는 수가 보조 인덱스보다 적기 때문에 성능이 우수함
조회할 때 가장 많이 사용되는 열에 지정하는 것이 효율적 - 사용하지 않는 인덱스는 제거
실제로 사용되는 SQL을 분석해 WHERE 조건에서 사용되지 않는 열의 인덱스는 제거
그러면 공간 확보 및 데이터 입력 시 발생되는 부하를 줄일 수 있음
728x90
반응형