인덱스를 사용하면 데이터를 조회할 때의 성능이 향상된다. 그러면 인덱스를 무조건적으로 많이 추가하는 게 좋다고 착각할 수도 있다. 하지만 인덱스를 추가하면 조회 성능은 올라가지만, 쓰기 작업(삽입, 수정, 삭제)의 성능은 저하된다.
왜 그런 지 직관적으로 먼저 이해해보자.
인덱스를 추가한다는 건 인덱스용 테이블이 추가적으로 생성된다는 뜻이다. 그렇다면 인덱스를 추가하지 않은 상태에서 원래 테이블에만 데이터를 넣는 것보다, 인덱스를 추가한 상태에서 원래 테이블과 인덱스용 테이블 둘 다에 데이터를 넣어야 하는 게 더 느릴 수 밖에 없다. 인덱스의 개수가 많아지면 많아질수록 성능은 느려질 수 밖에 없다. 데이터를 삽입하는 것 이외에도 수정, 삭제 작업에서도 같은 이유로 성능이 느려진다.
실제로도 그런지 테스트해보자.
테이블 생성하기
-- 테이블 A: 인덱스가 없는 테이블
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
);
-- 테이블 B: 인덱스가 많은 테이블
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
);
ㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅestㅅest
인덱스 추가
-- 각 컬럼에 인덱스를 추가
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);
데이터 삽입 성능 테스트
-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
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만개 데이터 삽입 시 걸리는 속도]
약 300ms 정도의 시간이 소요된다.
[인덱스를 많이 생성한 테이블에 10만개 데이터 삽입 시 걸리는 속도]
약 2000ms로 시작해서 25,000ms까지 걸리는 걸 확인할 수 있다. 데이터가 많아지면 많아질수록 점점 삽입 속도가 느려지는 걸 확인할 수 있다.
⭐
[이것만은 꼭 기억해두자!]
- 최소한의 인덱스만 사용하려고 하자.
- 인덱스를 추가하면 조회 속도는 빨라지나, 쓰기(삽입, 수정, 삭제) 속도는 느려짐을 항상 기억하자.
