2024-05-08) MySQL DB Indexing 고찰

6주간의 팀 프로젝트 작성이 마무리되었다. 급했던 나서 조금이나마 숨을 돌리면서 프로젝트를 되돌아보는 시간을 가졌다. 그러고 보니 놓치고 있었던 부분이 보이게 되었는데, 바로 인덱스가 생성될 때 실제 데이터 수와는 다른 인덱스가 생성되는 것을 확인할 수 있었다.

프로젝트에 JPA annotation을 적용, 인덱스를 생성하여 약 20%의 성능 향상을 얻을 수 있었다. 그러나 사용 중 데이터 수와 인덱스 수의 불일치 현상이 발생하여 의문이 들었다. 50,000개의 데이터가 있는 데이터베이스에서 show index를 실행했을 때, 인덱스 수가 실제 데이터 수보다 20개 적은 49,980개로 나왔다. 이 문제를 해결하기 위해 데이터베이스를 복제하고 인덱스를 다시 생성했지만 결과는 같았다.

이에 ALTER TABLE new_table ENGINE=InnoDB;를 사용해 인덱스를 재생성했으나, 인덱스 수에 변화는 없었다. 중복된 ID가 있는지 확인하기 위해 SQL 쿼리를 실행했으나, 결과는 없었다.

DROP TABLE IF EXISTS index_test_table;
CREATE TABLE index_test_table LIKE flight;
INSERT INTO index_test_table SELECT * FROM flight Where id<6180;
ANALYZE TABLE index_test_table;
SHOW INDEX FROM index_test_table;

더 자세히 조사해보기 위하여 다음과 같은 쿼리문을 반복 실행하여 인덱스에 값이 불일치 하는 지점을 찾았다. 그 결과 6,179개까지는 인덱스 수가 일치했지만, 6,180개부터 인덱스 수가 6,160으로 감소하는 현상을 발견했다.

이 문제는 MySQL의 설정적인 문제나 InnoDB 엔진이 일정 개수 이상에서는 데이터베이스를 분석할 때 정확도가 떨어지는 것으로 보인다. 따라서 InnoDB에서 발생하는 병목 현상을 해결하기 위해 다양한 InnoDB 관련 설정을 조정했지만, 성능 변화는 없었다.

변경한 설정값 예시)

innodb_buffer_pool_size 128M→4G
myisam_max_sort_file_size 2146435072 → 4292870144
key_buffer_size 8 →64
sort_buffer_size 256K→128M
binlog_row_event_max_size=8→16K
open_files_limit=8161 → 16322
innodb_log_buffer_size=16M→256M

이러한 값의 변화에도 인덱스 생성에 있어 차이가 없었다.

 

For InnoDB tables, ANALYZE TABLE determines index cardinality by performing random dives on each of the index trees 
and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE 
could produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it 
does not take all rows into account.

결론적으로, InnoDB는 카디널리티를 랜덤 다이브 기법으로 생성하기 때문에 약간의 오차가 있는 것으로 밝혀졌다. 이를 해결하기 위해 innodb_stats_persistent와 innodb_stats_persistent_sample_pages 값을 조정하여 정밀도를 높일 수 있으며, 20->60으로 증가시킴으로 인덱스 정밀도를 6179에서 19549로 증가시켰다.

MySQL 공식 문서

이러한 분석과 설정 조정을 통해 데이터베이스의 인덱스 생성과 관리에 대한 더 깊은 이해를 얻을 수 있었다.