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. 인덱스를 효과적으로 사용하는 방법

  1. 인덱스는 열 단위에 생성
    하나의 열에 하나의 인덱스를 만드는 것이 가장 일반적임
  2. WHERE 절에서 사용되는 열에 인덱스를 만들어야 함
    SELECT 문을 사용할 때, WHERE 절의 조건에 해당 열이 나와야 인덱스를 사용함.
  3. WHERE 절에 사용되더라도 자주 사용해야 가치가 있음
    인덱스를 생성해서 효율이 좋더라도 자주 사용하지 않으면 큰 의미 없음
    어떠한 테이블이 INSERT 작업을 위주로 한다면 인덱스를 생성하지 않는 것이 좋음 (인덱스는 INSERT의 성능을 나쁘게 하기 때문)
  4. 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없음
    열에 들어갈 데이터의 종류가 몇 가지 되지 않으면 인덱스가 큰 효과를 내지 못함
  5. 클러스터형 인덱스는 테이블 당 하나만 생성할 수 있음
    클러스터형 인덱스는 데이터 페이지를 읽는 수가 보조 인덱스보다 적기 때문에 성능이 우수함
    조회할 때 가장 많이 사용되는 열에 지정하는 것이 효율적
  6. 사용하지 않는 인덱스는 제거
    실제로 사용되는 SQL을 분석해 WHERE 조건에서 사용되지 않는 열의 인덱스는 제거
    그러면 공간 확보 및 데이터 입력 시 발생되는 부하를 줄일 수 있음
728x90
반응형