[데이터베이스] 14. 인덱스 실습

사전 준비

• timeout 방지 : 최대 로딩 시간 늘리기

• 더미데이터 작성
• Table 생성

create table member_tb(
id int primary key auto_increment,
gender char(1),
nickname varchar(20),
age int,
money int
);

desc member_tb;

• 트랜잭션 작성 - 더미데이터 생성

DELIMITER $$

DROP PROCEDURE IF EXISTS insertDummyData$$

CREATE PROCEDURE insertDummyData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000000 DO
IF mod(i,2) = 1 THEN
INSERT INTO member_tb(gender, nickname, age, money)
VALUES('M', concat('닉네임', i), FLOOR(1 + RAND() * 60),FLOOR(10000 + RAND() *
100000));
ELSE
INSERT INTO member_tb(gender, nickname, age, money)
VALUES('F', concat('닉네임', i), FLOOR(1 + RAND() * 60),FLOOR(10000 + RAND() *
100000));
END IF;
SET i = i + 1;
END WHILE;
END$$

DELIMITER $$


CALL insertDummyData;

1. 인덱스 사용

• PRIMARY KEY = 자동으로 Index로 사용됨 (Unique)

select *
from member_tb;

select *
from member_tb
where nickname = '닉네임7'; -- 0.219s

select *
from member_tb
where id = 7; -- 0.0s

2. CREATE INDEX

• CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

-- 인덱스 제작 조건 1. 비즈니스 상 해당 컬럼을 조회하는 일이 자주 발생해야 함
-- 인덱스 제작 조건 2. 중복 데이터가 전체 데이터의 15% 이내일 때
create index nickname_idx on member_tb (nickname);

-- 왜 닉네임7을 조회하면 안되는가? - 캐싱되어있음 (LRU(Least Recently Used) 알고리즘)
select * from member_tb where nickname = '닉네임9'; -- 0.0s

3. INDEX의 특징

1. UK (Unique Key) : Indexing이 빨라 탐색 시간이 대폭 감소

-- 인덱스 삭제 & UK 생성
drop index nickname_idx on member_tb;
alter table member_tb add constraint uk_nickname unique(nickname);

select * from member_tb where nickname = '닉네임10'; -- 0.0s

1. Index 사용 → 찾아야 할 표본을 줄일 수 있음

-- Full Scan
-- 0.0s
select * from member_tb where money between 10000 and 20000;

create index money_idx on member_tb(money);

-- Index Range Scan
-- 0.0s
select * from member_tb where money between 20000 and 30000;

1. Multi Index → 여러 행을 이용한 쿼리 사용이 잦을 때, 탐색 시간 감소

-- 0.468s
-- 인덱스로 행을 걸러내고, 그 결과를 가지고 평균을 구함
select avg(age) from member_tb where money between 20000 and 30000;


create index money_age_idx on member_tb(money, age);

-- 인덱스로 행을 걸러내면서 평균을 구하고, 그 결과를 바로 출력
-- 0.047s
select avg(age) from member_tb where money between 10000 and 20000;