MySQL 쿼리 캐시

쿼리 캐시( Query Cache ) 는 타 DBMS에는 없는 MySQL의 독특한 기능 중 하나로서 적절히 설정만 해두면 상당한 성능 향상 효과를 얻을 수 있다. 

여러 가지 복잡한 처리 절차왜 꽤 큰 비용을 들여 실행된 결과를 쿼리 캐시에 담아 두고, 동일한 쿼리 요청이 왔을 때 간단하게 쿼리 캐시에서 찾아서 바로 결과를 내려 줄 수 있기 때문에 기대 이상의 효과를 거둘 수 있다.

하지만 항상 그렇 듯이 이 쿼리 캐시에도 장단점이 있으므로 적절한 조율이 중요하다. 쿼리 캐시는 단어의 이미와는 달리 SQL 문장을 캐시하는 것이 아니라 쿼리의 결과를 메모리애 캐시해두는 기능이다. 쿼리 캐시의 구조는 간단한 키와 값의 쌍으로 관리되는 맵( map )과 같은 데이터 구조로 구현돼 있다. 여기서 키를 구성하는 요소 가운데 가장 중요한 것은 쿼리 문장 자체일 것이며, 값은 해당 쿼리의 실행 결과가 될 것이다.

하지만 데이터베이스에서 쿼리를 처리할 때는 상당히 많은 부분의 처리 절차가 있다. 이를 전부 무시하고 동일한 쿼리 문장이 요청됐다고 그냥 캐시된 결과를 보내서는 안 된다. 쿼리 캐시의 결과를 내려보내주기 전에 반드시 다음과 같은 확인 절차를 거쳐야 한다.

1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?

2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?

3. 트랜잭션 내에서 실행된 쿼리인 경우, 그 결과가 가시 범위 내의 트랜잭션에서 만들어진 결과인가?

4. 쿼리에 사용된 기능( 내장 함수나 저장 함수 등) 이 캐시돼도 동일한 결과를 보장할 수 있는가?

4.1 CURRENT_DATE, SYSDATE, RAND 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?

4.2 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?

5. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?

6. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?

7. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?

1. 요청된 쿼리 문장이 쿼리 캐시에 존재하는가?

쿼리 캐시는 MySQL의 어떠한 처리보다 앞 단에 위치하며, 캐시된 결과를 찾기 위해, 쿼리 문장을 분석해서 복잡한 비교 과정을 거치는 것이 아니기 때문에 아주 간단하고 빠르게 진행된다. 비교 방식은 그냥 요청된 쿼리 문장 자체가 동일한지 여부를 비교하는 것이다. 여기서 비교하는 대상으로는 공백이나 탭과 같은 문자까지 모두 포함되며, 대소문자까지 완전히 동일해야 같은 쿼리로 인식한다. 결론적으로 애플리케이션의 전체 쿼리 가운데 동일하거나 비슷한 작업을 하는 쿼리는 하나의 쿼리로 통일해 문자열로 관리하는 것이 좋다. 그렇다고 무리하게 쿼리를 통합하라는 것은 아니며, 적절히 추가 비용 없이 변경이 가능한 것들은 통합하라는 것이다. 동일한 쿼리가 여러곳에서 정의되어 사용되면 어느 순간에 각 쿼리가 달라지고( 공백이나 개행 문자 하나라도 ) 그렇게 되면 쿼리 캐시를 공유하지 못하게 된다.

2. 해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?

어떤 사용자가 요청한 쿼리에 대해 동일한 쿼리 결과가 쿼리 캐시에 저장돼 있더라도 이 사용자가 해당 테이블의 읽기 권한이 없다면 쿼리 캐시의 결과를 보여줘서는 안 되기 때문에 이런 확인 작업은 당연한 것이다.

3. 트랜잭션 내에서 실행된 쿼리인 경우 가시 범위 내에 있는 결과인가?

InnoDB의 모든 트랜잭션은 각 트랜잭션 ID를 갖게 된다. 트래잭션 ID는 트랜잭션이 시작된 시점을 기준으로 순차적으로 증가하는 6바이트 숫자 값이어서 트랜잭션 ID값을 비교해 보면 어느쪽이 먼저 시작된 트랜잭션인지 구분할 수 있다. InnoDB에서는 트랜잭션 격리 수준을 준수하기 위해 각 트랜잭션은 자신의 ID보다 ID값이 큰 트랜잭션에서 변경한 작업 내역이나 쿼리 결과가 참조할 수 없다. 이를 트랜잭션의 가시 범위라고 한다. 쿼리 캐시도 그 결과를 만들어 낸 트랜잭션의 ID가 가시 범위 내에 있을 때만 사용할 수 있는 것이다.

4-1. CURRENT_DATE(), SYSDATE(). RAND() 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?

SYSDATE()나 RAND() 같은 함수는 동일 사용자가 동일 쿼리를 실행하더라도 호출하는 시간에 따라 결과가 달라진다. 또한 이런 내장 함수뿐 아니라 NOT DETERMINISTIC 옵션으로 정의된 스토어드 루틴이 사용된 쿼리도 시점에 따라 결과가 달라질 가능성이 있다. 그래서 호출될 때마다 결과 값이 달라지는 CURRENT_DATE()나 SYSDATE() 그리고 RAND()와 같은 내장 함수뿐 아니라 NOT DETERMINISTIC으로 정의된 스토어드 함수 등은 사용하지 않는 편이 쿼리 캐시의 효율을 높이는 데 도움이 된다.

4-2. 프리페어 스테이트먼트의 경우 변수가 결과에 영향을 미치지 않는가?

프리페어 스테이트먼트( 바인드 변수가 사용된 쿼리 ) 의 경우에는 쿼리 문장 자체에 변수 ("?") 가 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 찾을 수가 없다. 여기서 한가지 주의해야 할 사항은 프로그램 코드에서는 프리페어 스테이트먼트를 사용했다 하더라도 실제 MySQL 서버에서는 프리페어 스테이트먼트 형태로 실행되지 않는다는 점이다. 진정한 프리페어 스테이트먼트를 사용하려면 프로그램 소스코드에서 데이터베이스 커넥션을 생성할 때 특별한 옵션을 사용해야만 한다 이를 서버 사이드 프리페어 스테이트먼트 라고 하는데 MySQL 5.0까지는 프리페어 스테이트먼트로 실행된 쿼리는 쿼리 캐시를 사용할 수 없었지만 MySQL 5.1부터는 이러한 제약이 없어졌다. 

5. 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?

쿼리 결과가 쿼리 캐시에 저장된 이후 데이터가 변경되면 어떻게 될까? 당연히 이미 변경된 데이터를 캐시하는 것은 의미가 없기 때문에 데이터를 제거( 무효화, Invalidation) 해야한다. 위에서도 잠깐 언급한 것처럼 쿼리 캐시는 빠른 처리를 위해 아주 단순하게 작동하도록 셜계돼 있다. 따라서 쿼리 캐시에 있는 데이터를 무효화 하는 작업은 레코드 단위가 아닌 데이블 단위로 처리된다. 만약 쿼리 캐시를 1GB로 아주 크게 설정하고, 하나의 테이블로부터 조회된 데이터로 쿼리 캐시를 꽉 채웠다고 해보자. 그런데 해당 테이블에 새로운 레코드를 한 건 INSERT 하면 MySQL 서버는 쿼리 캐시에 채워져 있는 1GB의 내용을 모두 제거해야 할 것이다. 이 작업은 아무리 메모리 작업이라 해도 상당히 시간이 소모되는 작업이다.

더욱이 쿼리 캐시는 절대 여러 스레드에서 동시에 변경할 수 없기 때문에 다른 스레드는 쿼리 캐시 작업이 완료될 때까지 기다려야 한다. 많은 사용자가 쿼리 캐시를 위한 메모리 공간은 무조건 크게 설정하면 좋다고 생각하지만 이러한 이유로 적절한 크기 이상으로 설정할 경우캐시 자체가 부하의 원인이 될 가능성도 있다. 여기서 적절한 크기라 함은 일반적으로 32M ~ 64MB 정도이다.

주의 

그리고 이와 관련해더 한 가지 더 주의해야 할 사항이 있다. 사용자들이 조회한 횟수를 보여주는 칼럼을 가지고 있는 게시판 테이블이 있다고 해보자. 애플리케이션에서는 항상 이 테이블로부터 SELECT를 실행하기 전에 UPDATE로 조회 수를 증가시켜야 할 것이다. 그러면 ㄴ어떤 현상이 발생할까? 이 테이블로부터 SELECT한 결과를 쿼리 캐시에 저장했는데, 그 뒤에 실행된 UPDATE 쿼리에 의해 바로 쿼리 캐시에서 삭제돼 버리는 현상이 발생하는 것이다.

즉, 이 테이블은 절대 쿼리 캐시를 사용할 수 없고, 쿼리 캐시에 저장하고 삭제하는 오버헤드만 추가하는 꼴이 돼 버리는 것이다. UPDATE와 SELECT의 쿼리 순서를 변경해도 마찬가지다. 이런 경우에는 조회수 칼럼을 다른 테이블로 분리하거나 ㅈ또는 조회수를 질정한 횟수만큼 누적한 후 한꺼번에 업데이트하는 편이 좋다.( 일반적으로 후자가 좋다 ) 테이블 3개를 조인해서 하나의 쿼리로 적상한 경우와, 각 테이블별로 쿼리를 하나씩 쪼개서 작성한 경우를 가정해 보자.

전자의 \경우에는 3개의 테이블 가운데 하나만 변경돼도 쿼리 캐시를 사용할 수 없지만 후자의 경우에는 3개의 쿼리 가운데 여전히 2개의 쿼리는 쿼리 캐시를 사용할 수 있다. 그래서 가끔은 복ㅈ바하게 하나의 쿼리로 필요한 모든 데이터를 가져오도록 쿼리를 작성하는 것보다 잘게 쪼개는 것이 더 효율 적일 수도 있다.

6. 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?

쿼리 캐시의 전체 크기를 64MB로 설정했는데 만약 어떤 쿼리 하나가 60MB정도의 쿼리 결과를 만들어 내면 하나의 쿼리 때문에 쿼리 캐시를 다 소모해 버릴 수 있다. 이러한 현상을 예방하고자 특정한 크기 미만의 쿼리 결과만 캐시 하도록 설정하는 시스템 파라미터가 있다. 이 설정 파라미터의 이름은 query_cache_limit 이며 값은 1~2M 미만으로 설정하는 것이 알반적읻. MySQL은 이 파라미터로 설정된 크기 미만의 쿼리 결과만 캐시한다. 결론적으로 쿼리가 결과를 만들어내는 데 많은 시간과 자원이 필요하지만 만들어진 결과의 크기가 작을수록 쿼리 캐시를 더 효울적으로 사용할 수 있기 때문에 GROUP BY나 DISTINCT 그리고 COUNT()와 같은 집합 함수의 결과가 쿼리 캐시를 사용하기에 아주 적합하다.

7. 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?

• 임시 테이블에 대한 쿼리
• 사용자 변수의 사용 : 쿼리에 사용자 변수를 사용하면 프리페어 스테이트먼트와 동일한 효과가 발생하므려 MySLQ이 쿠리 캐시를 사용하지 못하게 한다.
• 컬럼 기반의 권한 설정
• LOCK IN SHARE MODE 힌트 : SELECT 문장의 끝에 붙여서 조회하는 레코드에 공유 잠금( 읽기 락 )을 설정하는 쿼리
• FOR UPDATE 힌트 : SELECT 문장의 끝에 붙여서 조회하는 레코드에 배타적 자금( 쓰기 락 )을 설정하는 쿼리
• UDF( User Defined Function ) 의 사용
• 독립적인 SELECT 문장이 아닌 일부분의 서브 쿼리
• 스토어트 루틴 ( Procedure, Function, Trigger ) 에서 사용된 쿼리 ( 독립적인 쿼리라 하더라도 )
• SQL_NO_CACHE : SELECT 문장에서 SELECT 키워드 뒤에 붙이는 힌트로서, 이 힌트가 사용되면 쿼리 캐시를 사용하지 않는다. 애플리케이션에서 사용되는 쿼리에 의도적으로 이 힌트를 사용하는 경우는 거의 없으며, 대신 ㅜ커리의 성능을 시험할 때 자주 사용한다.

쿼리 캐시를 사용하지 못하게 하는 수많은 제약사항이 있음에도 여전히 쿼리 캐시는 그만큼의 효과를 충분히 얻을 수 있는 훌륭한 기능이다.  MySQL 서버에서 실행되는 작업은 대부분 MySQL 서버의 상태 변수에 누적되어 기록되기 때문에 SHOW GLOBAL STATUS 명령을 이용해 쿼리 캐시가 얼마나 사용됐고  MySQL 서버에서 SELECT 쿼리가 얼마나 실행 됐는지 등에 대한 정보를 확인해 볼 수 있다.

Qcache_hits와 Com_select 상태 값을 이용해 쿼리 캐시가 얼마나효율적으로 사용되고 있는지 조사해 볼 수 있다. 

Qcache_hits는 쿼리 캐시로 처리된 SELECT 쿼리의 수를 의미하며 Com_select 는 쿼리 캐시에서 결과를 차지 못해서 MySQL 서버가 쿼리를 실행한 횟수를 의미한다. 즉 Com_select 값과 Qcache_hists 값을 더하면 MySQL 서버로 요청된 모든 SELECT 문장의 총 합이 되는 것이다.

쿼리 캐시의 히트율이 20%이상이면 일반적으로 쿼리 캐시를 사용하는 것이 좋다고 이야기하기도 하는데 이보다 낮은 수치가 나온 경우도 사용 하는 것이 좋을 때가 있다. 쿼리 캐시 히트율은 쿼리 캐시가 얼마나 실행 시간을 줄이고 컴퓨팅 자원을 절약해줬는지를 나타내는 수치가 아니기 때문이다. 1%의 쿼리 캐시 히트율이라 하더라도 해당 쿼리가 사용하는 자원이나 ㅈ시간이 아주 크다면 쿼리 캐시는 그만큼 가치 있는 일을 했다고 볼 수 있기 때문이다. MySQL 서버에서 쿼리 캐시를 사용하지 않기로 했다면 컨피규레이션 파일의 설정 파라미터를 다음과 같이 변경하면된다.( 이 두 파라미터를 동시에 설정해야 메모리 낭비도 없고, MySQL 서버가 쿼리 캐시를 검색하거나 제거하는데 필요한 오버헤드도 줄일 수 있다. )

query_cache_size = 0

query_cache_type= 0