인덱스를 다량 생성 및 성능 측정

목표

인덱스 생성시 데이터 삽입 성능을 비교하기 위해 인덱스를 생성하지 않은 테이블과 인덱스를 다량 생성한 테이블에 각각 10만개의 데이터를 삽입 해 본다.

성능 측정 수치

소요시간(ms)

성능 개선 시 성능이 개선 됐는지 정확한 판단을 하기 위해 개선 이전의 수치와 개선 이후의 수치를 정확히 측정해서 비교할 것

과정

1. 인덱스가 없는 테이블 test_table_no_index과 인덱스가 많은 테이블 test_table_many_indexes 생성

   -- 인덱스가 없는 테이블 생성
   CREATE TABLE test_table_no_index (
       id INT AUTO_INCREMENT PRIMARY KEY,
       column1 INT,
       column2 INT,
       column3 INT,
       column4 INT,
       column5 INT,
       column6 INT,
       column7 INT,
       column8 INT,
       column9 INT,
       column10 INT
   );
   	
   -- 인덱스가 많은 테이블 생성
   CREATE TABLE test_table_many_indexes (
       id INT AUTO_INCREMENT PRIMARY KEY,
       column1 INT,
       column2 INT,
       column3 INT,
       column4 INT,
       column5 INT,
       column6 INT,
       column7 INT,
       column8 INT,
       column9 INT,
       column10 INT
   	);

2. 인덱스가 많은 테이블 test_table_many_indexes에 인덱스 추가

   CREATE INDEX idx_column1 ON test_table_many_indexes (column1);
   CREATE INDEX idx_column2 ON test_table_many_indexes (column2);
   CREATE INDEX idx_column3 ON test_table_many_indexes (column3);
   CREATE INDEX idx_column4 ON test_table_many_indexes (column4);
   CREATE INDEX idx_column5 ON test_table_many_indexes (column5);
   CREATE INDEX idx_column6 ON test_table_many_indexes (column6);
   CREATE INDEX idx_column7 ON test_table_many_indexes (column7);
   CREATE INDEX idx_column8 ON test_table_many_indexes (column8);
   CREATE INDEX idx_column9 ON test_table_many_indexes (column9);
   CREATE INDEX idx_column10 ON test_table_many_indexes (column10);

3. 각각의 테이블에 10만개의 데이터 삽입하여 소요시간 측정

   SET SESSION cte_max_recursion_depth = 100000; 
   
   -- 인덱스가 없는 테이블에 데이터 10만개 삽입
   INSERT INTO test_table_no_index (column1, column2, column3, column4, column5, column6, column7, column8, column9, column10)
   WITH RECURSIVE cte AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 FROM cte WHERE n < 100000
   )
   SELECT
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000)
   FROM cte;
   
   -- 인덱스가 많은 테이블에 데이터 10만개 삽입
   INSERT INTO test_table_many_indexes (column1, column2, column3, column4, column5, column6, column7, column8, column9, column10)
   WITH RECURSIVE cte AS (
       SELECT 1 AS n
       UNION ALL
       SELECT n + 1 FROM cte WHERE n < 100000
   )
   SELECT
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000),
       FLOOR(RAND() * 1000)
   FROM cte;

결과

인덱스가 없는 테이블
10만개씩 데이터가 증가할수록 소요 시간이 늘어나는데 보통 2000ms대에 삽입

인덱스가 많은 테이블
9000ms대였다가 데이터가 가장 많은 상태인 마지막 삽입 시 16,174ms까지 소요시간 증가

결론

성능 비교를 위해 극단적으로 인덱스가 하나도 없는 테이블과 모든 컬럼에 인덱스를 생성한 테이블에 데이터를 삽입해 봤다.

인덱스를 추가한다는 것은 특정 컬럼을 기준으로 하는 테이블을 하나 더 생성하는 것과 같고, 데이터 삽입 시 인덱스로 인해 생성된 테이블에도 데이터를 삽입해줘야 하므로 데이터 삽입 횟수가 증가하며 데이터를 삽입하는데 소요되는 시간도 증가된다.

즉, 인덱스를 추가하면 조회 성능은 개선이 되지만 쓰기(삽입, 수정, 삭제)성능은 저하되는 점을 확인할 수 있었다. 그렇기 때문에 꼭 필요한 부분에만 인덱스를 추가하여 효율적인 작업을 할 수 있도록 해야겠다.

참고

https://www.inflearn.com/course/%EB%B9%84%EC%A0%84%EA%B3%B5%EC%9E%90-mysql-%EC%84%B1%EB%8A%A5%EC%B5%9C%EC%A0%95%ED%99%95-sql%ED%8A%9C%EB%8B%9D
<비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전 (SQL 튜닝편)> 강의를 토대로 작성한 내용입니다.