INDEX 테스트 해보기

오늘은 INDEX TEST를 조금 진행해보았어요.

간단한 테스트들만 측정을 해보았어요 엄청 재밌더라구요.

 

제가 경험했던 스타트업에서는 RDB에서 많은 데이터를 적재하지 못해, 튜닝 경험을 하지 못했었고 

기능의 확장과 서버의 depoly 위주로 진행을 하다보니. 인덱스에 대한 이론만 알고있고 경험해보지 못한상태였어서 늘 고민 지점이 많았었어요. 대체, 언제, 왜, 어떤 지점에서 index를 걸어야 하는지. index를 언제 병합키로 걸어야하는지 머리속으로만 있는 상태였어요.

 

 

 

카더널리티가 높아야한다.

page단위로 저장이 되고 column의 크기가 크면 page를 더 많이 쪼개어야 하기에 index의 성능에도 영향이 간다. ( depth ) 

병합키로 사용시 index를 거는 순번에도 영향이 간다.

where 비교시 형변환을 하게 된다면 full scan을 해야한다. 등등이요.

 

그게 이론으로만 있는게 답답하고, 싫어서 이제서라도 테스트 데이터를 넣고 진행해봤어요.

점차 table을 늘려가고 효율적인 테스트를 진행해보고 싶네요.

 

아래 향로님의 말처럼 미리준비하는 마음가짐으로 해보았어요.

https://jojoldu.tistory.com/727

 

 

 

 

 

 --- 요약---

- 데이터 백만건 진행

 

- table 한개로 진행

- index 컬럼과 아닌 컬럼 ( 문자열 기준 ) 속도 차이: 인덱스-6ms / 비 인덱스 -147ms

-실행계획 rows - 인덱스 - 1 / 비 인덱스- 733493 

 

- 인덱스에 like '%%' 는 index를 활용하지 못함

- 인덱스 like '검색어%'는 동작함

 

-----

 

 

얼마나 효율적인지 100만건의 데이터를 토대로 테스트를 해보았습니다.

이제서야 필요성이 엄청 느껴지네요.

 

docker에서 mysql 8version의 image를 다운받고 

initsql과 함께, docker-compose로 구상했어요. 

volume 옵션을 통해 project directory 내부에 data를 공유해서 docker container가 닫히더라도 데이터가 남아있도록 했어요.

 

DB의 TABLE 설계는 아래와 같아요.

id - pk

nickname - index 

 

proccedure를 통해 데이터를 삽입했어요.

CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    nickname VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL
)


CREATE INDEX idx_nickname ON teachers (nickname)

DELIMITER $$

CREATE PROCEDURE PopulateTeachers()
BEGIN
  DECLARE counter INT DEFAULT 0;

  WHILE counter < 1000000 DO
    INSERT INTO teachers (name, nickname)
    VALUES (CONCAT('Teacher ', counter), CONCAT('Nickname ', counter));

    SET counter = counter + 1;
  END WHILE;
END$$

DELIMITER ;

CALL PopulateTeachers();

---

가상 데이터를 다 넣었어요.

 

여기에서 name이 Teacher 66667 인 사람을 찾아볼게요.

해당 컬럼에는 인덱스가 걸려있지 않아요.

 

실행 결과 - 147ms

SELECT name, nickname
FROM teachers t 
WHERE name = 'Teacher 66667'

실행 계획 확인  - rows 733493 

대략 733493 의 record를 확인해야 제가 원하는 컬럼을 찾을 수 있어요.

 

 

---

 

반면에 index를 걸어둔 nickname을 측정해볼게요.

SELECT name, nickname
FROM teachers t 
WHERE nickname = 'Nickname 66667'

실행 결과 - 6ms 

 

실행 계획 확인

EXPLAIN 
SELECT name, nickname
FROM teachers t 
WHERE nickname = 'Nickname 66667'

실행 계획 확인  - rows 1

---- 

 

추가적으로 like에 관해서도 테스트를 진행해보았습니다.

'%{word}%' 를 활용한 like절 측정

- 결과: 190ms

- 이외 결과물로 출력됨

- 인덱스 힌트를 사용해도 같은 결과 ( USE 

쿼리 explain 사용시

검색해야 하는 rows: 733493

SELECT name, nickname
FROM teachers t 
WHERE nickname like '%Nickname 66667%'

 

 

 

'{word}%' 를 활용한 like절 측정

- 결과: 3ms 

- 이외 결과물로 출력됨

쿼리 explain 사용시

검색해야 하는 rows: 11  *중간 글자가 겹치는 항목

 

 

 

 

'%{word}' 를 활용한 like절 측정

- 결과: 186ms 

쿼리 explain 사용시

검색해야 하는 rows: 733493

 

 

이를 통해, like 절에서 '{word}%' 이외에는 인덱스가 사용이 안되는 것을 확인했어요.

속도 차이도 어마무시하네요.

 

---

IN에 대해서도 테스트를 진행해보았어요.

4개 컬럼 테스트시 - 19ms

재 시도 - 5ms

 

시간이 줄어든 이유는 아마 inmemory buffer가 index page 정보를 캐싱해두지 않았을까 하는 추측이 들어요.

SELECT name, nickname
FROM teachers t  
WHERE nickname IN (
'Nickname 6667',
'Nickname 213',
'Nickname 12',
'Nickname 12212'
)

---

 

전반적으로 RDBMS의 인덱스 개념을 테스트 해보았어요.

 

1백만건의 가상 데이터를 토대로 어떻게 동작하는지, 어떤 성능에 영향을 주는지 측정해보고 알게되었네요.

 

첫번째로, 인덱스가 걸려있는 컬럼과 그렇지 않은 컬럼의 조회시 속도차이.

- 인덱스가 걸려있는 nickname 컬럼의 조회 시간은 6ms

- 인덱스가 걸려 있지 않은 name 컬럼의 조회 시간 147ms

 

두번째로, like절이 인덱스가 어떻게 동작하는지에 대해

- like '{word}%'를 제외하고는 인덱스를 타지 않습니다. 조회시간이 3ms

- like '%{word}%' 조회시간 190ms

- like '%{word}' 조회시간 186ms

 

세번째로 in 절에서의 인덱스 동작 확인

- IN ( 'KEY1', 'KEY2', 'KEY3', 'KEY4' ) - 인덱스 사용 / 조회시간 19ms

- 두번째 조회시 5ms

 ( 인 메모리 버퍼 풀이 인덱스 페이지 정보를 캐싱한 것으로 추측 ) 

 

조금 테스트 하니까 명확해지는 느낌이 드네요!

조금씩 테스트 과정을 늘려가보며, 이론과 맞닿은 인덱스를 체험해봐야겠어요

공부하면 할수록 재미있는게 많네요!