MySQL 서브 쿼리 성능 저하에 관하여

MySQL 서브 쿼리 성능 저하에 관하여 라고 썼지만 사실 아래 내용은 Join 연산에도 적용되는 문제다

 

https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html

MySQL :: MySQL 8.0 Reference Manual :: 10.2.2.2 Optimizing Subqueries with Materialization

를 보면 다음과 같은 문단이 있다

 

 

The optimizer uses materialization to enable more efficient subquery processing. Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory. The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table. The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index contains unique values to eliminate duplicates and make the table smaller.

Subquery materialization uses an in-memory temporary table when possible, falling back to on-disk storage if the table becomes too large.

 

결과적으로 연산에 있어 temporary table를 만들게 되고 이는 어지간하면 인 메모리에 있지만 사이즈가 커지면 디스크에 저장된다는 것이다. 

 

디스크에 데이터가 적재 되면 Disk IO로 인해 성능이 급격하게 떨어질 수 밖에 없다.

디스크는 메인 메모리에 비해 훨씬 느리다

결국 성능이란 관점에서 볼때, 서브 쿼리와 같은 연산을 사용한다면 

temporary table 이 메모리에 위치하는 것이 중요하다.

이를 위해 두 가지로 고민해볼 수 있다.

1. temporary table 의 크기를 줄인다. 서브 쿼리 연산 시 필요한 컬럼만 뽑아서 쓰는 것이다. A 테이블에 100개의 필드가 있고 B 에도 100개의 필드가 있지만 쿼리에 필요한 필드는 정작 A 테이블에 3개 B 테이블에 2개라면 해당 필드만 가지고 작업하는 것이다. 

 

2. 메모리 크기를 키우는 것이다. 결국 버퍼 풀에 적재되는 것이기 때문에 버퍼 풀 사이즈를 키우면 그 만큼 temporary table 가 들어갈 공간도 늘어난다. 

 

innodb_buffer_pool_size  // 해당 수치를 조절하면 된다.