[DB] 15. 인덱스 실습

Feb 27, 2025

Contents

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. 실습

index의 있고 없고의 차이

닉네임 7이라는 데이터를 찾고 싶을 때, index가 없는 nickname으로 조회하면 full scan 방식으로0.234s 가 걸린다. 하지만 PK인 id로 검색을 하였을 경우 0.0s 초가 걸렸다. 압도적으로 빠른 검색이 가능하다.


select * from member_tb;

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

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

비지니스상 어떠한 column을 많이 조회하는 경우에는 index를 설정해주는게 유리하다. 하지만 조건이 있다. index설정을 해주고 싶은 column의 중ㅜ복된 데이터가 15% 이내에 있어야한다.



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

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

그리고 또한 이미 한 번 캐싱된 데이터를 조회 할 때에는 바로 불러오기 때문에 테스트를 할 때에는 이미 캐싱된 데이터 말고 다른 데이터로 테스트를 해야한다. ex) 위 소스 코드에서 닉네임 7말고 닉네임 8로 조회

index 삭제와 UK(unique) 생성

UK를 생성하면 중복된 값이 없기 때문에 툴에서 자동으로 index설정을 해준다. 그래서 속도가 매우 빠르다.



-- 인덱스 삭제 그리고 UK 생성
drop index nickname_idx on member_tb;

alter table member_tb add constraint uk_nickname unique (nickname);

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

상황에 따라서는 full scan이 유리하다.



-- 0.016s(full scan)
select * from member_tb where money between 100000 and 200000;

create index money_idx on member_tb (money);

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

복합index

하나의 column에 index설정을하면 인덱스가 먼저 실행 후에 그 결과를 가지고 연산을 한다. 하지만 복합index를 사용하면 index가 실행되며 연산이 되고, 결과를 출력해 주기 때문에 2가지 이상의 column을 자주 조회한다면 복합index를 걸어주는게 유리하다.



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

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

-- 0.031s
-- 인덱스로 행 걸러내면서 평균을 구한다. 그리고 그 결과를 그냥 출력
-- (where에서 and로 자주 검색하는 조건이면 복합index를 걸면 유리함)
select avg(age) from member_tb where money between 10000 and 20000;

Index Range Scan

Index Range Scan은 특정 범위에 해당하는 데이터를 검색 할 때 사용되는 스캔방식이다.

See more posts

[DB] 15. 인덱스 실습

1. 더미 데이터 세팅

2. 실습

index의 있고 없고의 차이

index 삭제와 UK(unique) 생성

상황에 따라서는 full scan이 유리하다.

복합index

Index Range Scan

More articles

[코딩테스트] 1. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

[환경세팅] 7. Cursor

[DB] 14. 인덱스 (B-Tree 인덱스)

[DB] 13. 인덱스 (트리 탐색 기법)