[Data Base] 12. Index_3.Index 실습

김미숙

Mar 20, 2025

Contents

1. Dummy data setting Index 생성 UK 생성 복합 Index

1. Dummy data setting

프로시저 호출

Dummy data setting


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;


select *
from member_tb;

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

-- 0초
select *
from member_tb
where id =

❗

index full scan

인덱스 테이블을 모두 스캔

Index 생성

‼️

비즈니스 상 해당 칼럼을 조회하는 일이 빈번하게 있어야함

데이터 중복이 15% 이내


-- Index 생성
create index nickname_idx on member_tb (nickname);

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

❗

LRU 알고리즘(Least Recently Used Algorithm)

가장 오랫동안 참조되지 않은 페이지를 교체하는 기법

LRU를 구현하기 위해서는 캐시가 가득 찼을때, 가장 오랫동안 참조되지 않은 페이지를 찾아서 없애는 과정이 필요

LRU의 원리는 캐시의 크기가 3인데 이미 3개의 페이지가 캐시에 들어있다면 맨 뒤에 있는 페이지번호 node 를 지우고 새로운 페이지번호 node 를 앞에 연결해주는 방식


create index money_idx on member_tb (money);

-- index Range scan
select *
from member_tb
where money between 20000 and 30000;

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

❗

Index Range scan

index 값이 unique가 아니라 중복을 허용할 때, 2번 이상 범위를 탐색하는 방법

ndex를 수직 탐색 후 필요한 범위까지만 탐색

특징

비교조건(>,<) 사용 시 --> Unique Index

Non Unique Index 사용시 사용될 수 있음

수직 탐색 후 필요한 범위까지만 탐색

Single Block Read

UK 생성


-- index 삭제 그리고 UK 생성 (데이터 유일할 경우 unique / 자주찾을거고 데이터 중복이 15% 이내는 index)
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;

❗

Index unique scan

값이 하나만 존재하는 것을 보장하기 때문에, 1번만 조회

결과로 하나의 건이 반환될때 사용되고 칼럼이 유일한 값으로 구성

Primary Key에 기본적으로 생성되는 인덱스

조건 검색이 단일(=)인 경우 사용하게 된다

특징

칼럼의 값이 유일한 Primary Key에 사용된 Index

동등 조건 "="

Single Block

복합 Index


-- money, age 복합 index
create index money_age_idx on member_tb (money, age);

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

❗

복합 Index (Composite Index)

데이터베이스에서 여러 개의 컬럼(열)들을 조합하여 인덱스를 생성하는 것

단일 인덱스(Single Index)가 한 개의 컬럼에 대해 생성되는 것과는 달리, 복합 인덱스는 여러 개의 컬럼을 함께 사용하여 인덱스를 생성

Contents

1. Dummy data setting Index 생성 UK 생성 복합 Index

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 $$

-- Index 생성 create index nickname_idx on member_tb (nickname); -- 왜 닉네임7을 조회하면 안되는가? -> 캐싱되어있음(LRU 알고리즘) select * from member_tb where nickname = '닉네임8';

create index money_idx on member_tb (money); -- index Range scan select * from member_tb where money between 20000 and 30000; -- 평균 / 0.422초 -- 인덱스로 행을 걸러내고, 그 결과를 가지고 평균을 구한다 select avg(age) from member_tb where money between 20000 and 30000;

-- index 삭제 그리고 UK 생성 (데이터 유일할 경우 unique / 자주찾을거고 데이터 중복이 15% 이내는 index) 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;

-- money, age 복합 index create index money_age_idx on member_tb (money, age); -- 0.031초 -- 인덱스로 행 걸러내면서 평균을 구하고 그 결과를 출력 select avg(age) from member_tb where money between 10000 and 20000;