[DB, Index] B-Tree 인덱스 사용하여, 쿼리 수행 속도 비교해보자

00. 서론 및 개발 환경

cs공부를 하면서, 인덱스 인덱스 말로만 들어봤지, 실제 프로젝트에 적용시켜본 적도 없고, 인덱스를 적용하면 얼마나 성능이 향상되는지 체감도 별로 못했다.
이번 기회에 한번 직접 테스트를 해보며, 수행 속도를 비교해 보고자 한다.

 

개발 환경

• DB: MySQL
• Tool: HeidiSQL

01. 더미데이터 삽입

테이블 구조 및 생성

테스트를 위한 더미데이터를 생성하기 전에 테이블을 먼저 정의해주자.

CREATE TABLE `my_dummy` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `time` DATETIME NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
    `data` LONGTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `인덱스 2` (`time`) USING BTREE
)

 

그냥 간단하게 PK, 생성일자, 문자열데이터를 가지는 아주 심플한 구조로 테이블을 정의했다.

데이터 삽입 프로시저 정의

CREATE DEFINER=`root`@`localhost` PROCEDURE `ctrlCV`(
    IN `cnt` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '더미데이터를 마구 만들어버릴꺼야~~'
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE j INT DEFAULT 1;
    DECLARE mydata TEXT DEFAULT '';

    WHILE j <= 100 DO
       SET mydata = CONCAT(mydata, 'as1dfas#hgahgs345352gao135hos^dsasdfash54gw345ahgsgaohosds314\n');
       SET j = j + 1;
   END WHILE;

   WHILE i <= cnt DO
      INSERT INTO my_dummy(data)
            VALUES (mydata);
      SET i = i + 1;
    END WHILE;
END

데이터 삽입을 위해서 ctrlCV라는 프로시저를 정의했다.
매개변수로 생성할 열의 갯수를 입력하면 된다.

프로시저를 사용하여, 데이터 INSERT

CALL ctrlCV(40000);

 

대충 40000개의 열을 추가했다.
이 정도면 속도 비교에 유의미하겠지..?

02. 인덱스 정의

CREATE INDEX TIME ON MY_DUMMY(TIME);
ALTER TABLE my_dummy DROP INDEX TIME;
SHOW INDEX FROM my_dummy;

각각 인덱스 생성, 인덱스 제거, 인덱스 조회 명령어이다.

나는 datetime 필드로 쿼리 성능을 비교할 것이기 때문에, time 필드로 인덱스를 생성했다.
이후 속도 비교시, 인덱스를 만들었다가 제거했다가 왔다갔다 할거다.

03. 속도 비교

프로파일링

SET profiling = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 30;

우선 속도 측정을 위해 프로파일링을 설정해 주어야한다.
대략적인 사용법은 아래와 같다.

1. 프로파일링을 사용하도록 ON(1) 설정, (default: 0)
2. 임의의 쿼리 실행
3. SHOW PROFILES 명령어를 사용해서, 앞에서 사용한 쿼리 아이디를 알아낸다.
4. SHOW PROFILE FOR QUERY {쿼리아이디} 위에서 알아낸 쿼리 아이디의 성능을 조회한다.

해당 방법을 가지고 속도를 측정했으니, 참고 바란다.

속도 비교에 사용할 쿼리

SELECT COUNT(*) FROM my_dummy WHERE SECOND(TIME) = 1 OR SECOND(TIME) = 31;\

나는 time 필드를 가지고 데이터를 조회하는 쿼리를 작성했다.
대충 생성된 시간이 1초 또는 31초로 맞아 떨어지는 컬럼을 조회*하는 쿼리다.

SELECT 쿼리 속도 측정

인덱스를 사용하지 않고 속도 측정

대략 0.6초 정도의 시간이 소요되었다.

 

인덱스를 사용하고 속도 측정

대략 0.00006초 정도로 10000배 정도 빨라진 것을 확인 할 수 있다.

INSERT 쿼리 속도 측정

보통 인덱스를 사용하면, INSERT, DELETE, UPDATE 등의 성능은 오히려 저하된다고 하는데 진짜 그런지 측정해보겠다.
위에서 정의한 프로시저를 사용하여 5000개의 열을 삽입하는 속도를 측정할 것이다.

 

인덱스가 없을 때의 INSERT 속도

대략 19초 정도 걸린다.

 

인덱스가 존재할 때의 속도 측정

대략 22초 정도 걸렸다.

현재 삽입한 데이터가 적어서 그런지, 아니면 테이블 구조가 단순해서 그런지 모르겠지만, 막 유의미한 성능 차이는 없는것 같다.

04. 결론

• 인덱스를 사용하면 SELECT 쿼리에서 엄청난 성능 향상을 기대할 수 있다.
• INSERT 쿼리에서는 생각보다 큰 성능 저하를 보이지 않았다.
  • 아마 현재 테스트 환경 때문에 그런 것 같으니 유의하자..
• 앞으로 INDEX를 적극 활용해야겠다.ㅎㅎ