MySQL SELECT 캐쉬 옵션 SQL_CACHE, SQL_NO_CACHE

시작

최근 회사에서 MySQL 버전을 5.5 로 업그레이드하면서 문제되는 쿼리가 있다고 해서 알아본 내용을 적어본다.

MySQL 5.5 로 업그레이드후 기존 쿼리중 서브쿼리에 SQL_NO_CACHE 옵션을 사용한 쿼리에서 문제가 발생했는데 원인은 MySQL 5.5 에서 서브쿼리에 대해 SQL_NO_CACHE 옵션을 사용할수 없게 했기 때문이었다. 이와 비슷하게 UNION 지시어에 대해서도 첫번째 SELECT 에만 지시어 사용이 가능하다.

캐시

MySQL 설정의 query_cache_type 의 값에 따라서 쿼리 결과에 대한 캐시 동작이 달라지는데 설정 값은 다음과 같다.(query_cache_type 설정값)

0 -> 쿼리 결과를 캐시하지 않는다.
1 -> SELECT SQL_NO_CACHE 로 시작하는 경우를 제외한 캐시가능한 쿼리 결과를 캐시한다.(기본값)
2 -> SELECT SQL_CACHE 로 시작하는 캐시가능한 쿼리에 대해서만 결과를 캐시한다.

SQL_CACHE, SQL_NO_CACHE 는 쿼리 결과를 다음에 쓸수 있도록 캐쉬할지 여부를 결정한다. SQL_CACHE 는 쿼리의 결과를 캐시에 저장한다. SQL_NO_CACHE 라고 하면 해당 쿼리의 실행결과를 캐쉬에 저장하지 않을뿐 아니라 쿼리의 캐시가 기존에 있었는지도 확인하지 않는다.
(select 쿼리 캐쉬 옵션 문서)

SQL_NO_CACHE 제약사항

SQL_NO_CACHE 의 제약사항은 서브쿼리에 사용할수 없는것과 UNION 조합의 경우 첫번째에만 사용할수 있다는것이다. (select 문서)

예를 들어 아래와 같은 상황에서 마지막 쿼리는 에러를 발생한다.

create table a( b int );
select * from (select b from a) tmp ; => ok
select * from (select sql_no_cache b from a) tmp;  => Last_SQL_Error: Error 'Incorrect usage/placement of 'SQL_NO_CACHE'' on query.

에러가 발생된 이유는 서브쿼리에 SQL_NO_CACHE 를 사용했기 때문인데 이는 MySQL 5.5 이전에는 없던 제약사항이다.

우선 서브쿼리에 SQL_NO_CACHE 를 사용할수 없는것은 애초에 서브쿼리의 결과는 캐시 되지 않기 때문이다.(서브쿼리캐쉬에 대한 답변)

그리고 UNION 조합에서 첫번째에만 사용할수 있는것은 UNION 쿼리에서 캐시는 각각의 SELECT 별로 캐시되는것이 아니라 전체 쿼리를 하나로 판단해서 캐시되기 때문이다.(UNION 옵션 캐시에 대한 답변)

덧붓이기

MySQL 5.7 이전버전에서는 SQL_NO_CACHE 옵션을 사용할때 앞에 공백문자가 있어야 된다. 이는 쿼리 파서의 문제라고 하며 공백이 아닌 줄바꿈 같은거 있으면 안된다.

개인적으로 개발할때는 PostgreSQL 만 사용하고 있기 때문에 요즘 MySQL 에는 별로 흥미가 없다. PostgreSQL 에 대한 글들이 유독 많이 보이고 재미있는 것들도 많은것을 보면서 MySQL 을 대체할것이 나온다면 MariaDB 가 아니라 PostgreSQL 이 아닐까 생각해본다. 벤치마크나 장단점에 의한 판단이 아니라 내가 자주 보는 Rails 나 개발 블로그들의 포스팅 비중이 그렇기 때문인데 이쪽 방향으로 따라가는것같다.