[DB] 12. 인덱스 Ⅳ. 인덱스 실습

1. 더미 데이터 세팅

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

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;

2. 더미 데이터 확인

SELECT *
FROM MEMBER_TB;

3. 풀스캔

SELECT *
FROM member_tb
WHERE NICKNAME = '닉네임7'; -- 0.235초

4. 인덱스 스캔 - primary key

SELECT *
FROM member_tb
WHERE ID = 7; -- 0.000초

5. 인덱스 스캔 - unique key

alter table member_tb add constraint uk_nickname unique (nickname); -- 1.7초
select *
from member_tb
where nickname = '닉네임9'; -- 0.000초, single row

6. 인덱스 스캔

-- 비지니스상 해당 컬럼을 조회할 일이 '자주' 있어야 함
-- 중복된 데이터가 15% 이하일 경우, 중복 비중이 적을 경우
create index nickname_idx on member_tb (nickname);

-- 왜 닉네임7을 조회하면 안되는가? - 캐싱되어있음 (LRU 알고리즘)
select *
from member_tb
where nickname = '닉네임8'; -- 0.000초0

7. 인덱스 스캔 - range scan

create index money_idx on member_tb (money); -- 0.860초
select *
from member_tb
where money between 30000 and 40000; -- 0.000초, index range scan

8. 자투리

-- 인덱스 삭제 & unique 생성
drop index nickname_idx on member_tb;

select *
from member_tb
where money between 10000 and 20000; -- 0.000초, full scan

-- 인덱스로 행을 걸러내고 그 결과를 가지고 평균을 구한다.
select avg(age)
from member_tb
where money between 30000 and 40000; -- 0.438초

drop index money_idx on member_tb;
drop index money_age_idx on member_tb;
drop index uk_nickname on member_tb;

-- 연관된 2가지의 컬럼을 인덱싱 한다.
create index money_age_idx on member_tb (money, age); -- 1초

-- 인덱스로 행을 걸러니면서 평균을 구한다. 그리고 그 결과를 바로 보여줌
select avg(age)
from member_tb
where money between 10000 and 20000; -- 0.031초