[데이터 베이스] 12-4. 인덱스_실습

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;

-- 0.344 초
select * from member_tb where nickname='닉네임7';

-- 0초
select * from member_tb where id = 7;

-- 비지니스상 해당 칼럼을 조회하는 일이 자주 있어야함. 중복된 데이터가 15프로 이내여야 한다.
create index nickname_idx on member_tb (nickname);

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

-- 인덱스 삭제 그리고 UK 생성alter
drop index nickname_idx on member_tb;
ALTER TABLE member_tb ADD CONSTRAINT uk_nickname UNIQUE (nickname);

-- 0초
select * from member_tb where nickname='닉네임9';

select * from member_tb where money between 10000 and 20000;

create index money_idx on member_tb (money);

select * from member_tb where money between 20000 and 30000;

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

drop index money_idx on member_tb;
CREATE INDEX money_age_idx ON member_tb (money, age);

-- 0.031
-- 인덱스로 행 걸러내면서 평균을 구한다. 그리고 그 결과를 그냥 출력
select avg(age) from member_tb where money between 10000 and 20000;