책너두 (Real MySQL 8.0 1권) 47일차 (~490p)

요약

• Extra 칼럼의 나머지 값들을 이해하게 됨.
  • Using MRR
    • 최소 페이지 접근하여 레코드를 읽을 수 있게 최적화함.
  • Using sort_union(…), Using union(…), Using intersect(…)
    • 인덱스 조건이 AND, OR 일때에 따라 최적화 하는 기법임.
  • Using temporary
    • 내부 임시 테이블을 이용하는 실행 계획임.
    • 메모리, 혹은 디스크 중 어디세 저장됐는지는 시스템 변수로 확인 가능
  • Using where
    • 스토리지, MySQL 엔진에서 작업 범위 조건 & 체크 조건에 따라 쿼리 처리가 각각 나눠져서 실행됨.
  • Zero limit
    • 실행 레코드 데이터 값이 아닌 쿼리 결과값의 메타 데이터만을 반환함.

메모

Using MRR

• MySQL 엔진은 실행 계획을 수립하고 그 실행 계획에 맞춰 스토리지 엔진의 API를 호출해서 쿼리를 처리함.
  • InnoDB를 포함한 스토리지 엔진 레벨에서는 쿼리 실행의 전체적인 부분을 알지 못하므로 최적화에 한계가 있음.
  • 이 이유로 아무리 많은 레코드를 읽는 과정이라더라도 스토리지 엔진은 MySQL 엔진이 넘겨주는 키 값을 기준으로 레코드를 한 건 한 건 읽어서 반환하는 방식으로밖에 작동하지 못하는 한계점이 있음.
    • 그래서 실제로 매번 읽어서 반환하는 레코드가 동일 페이지에 있다고 하더라도 레코드 단위로 API의 호출이 필요한 것임.
• MySQL 서버에서는 이 단점을 보완하기 위해 MRR(Multi Range Read)이라는 최적화를 도입함.
• MySQL 엔진은 여러 개의 키 값을 한 번에 스토리지 엔진으로 전달하고 스토리지 엔진은 넘겨받은 키 값들을 정렬해서 최소한의 페이지 접근만으로 필요한 레코드를 읽을 수 있게 최적화함.
  • MRR이 도입되면서 각 스토리지 엔진은 디스크 접근을 최소화할 수 있게 됨.
  • MRR 최적화와 MRR 최적화를 활용한 조인 방법인 BKA 조인에 대해서 9.3.1.1절 MRR 배치 키 액세스를 참조하자.

Using sort_union(…), Using union(…), Using intersect(…)

• index_merge 접근 방법으로 실행되는 경우 2개 이상의 인덱스가 동시에 사용될 수 있음.
• 실행 계획의 Extra 칼럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 좀 더 상세히 설명하기 위해 다음 3개 중 하나의 메시지를 선택적으로 출력함.
  • Using intersect(…) : 각 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우, 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미임.
  • Using union(…) : 각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우, 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미임.
  • Using sort_union(…) : Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상태적으로 대량의 range 조건들), 이 방식으로 처리됨. Using sort_union과 Using union의 차이점은 Using sort_union은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 이후 비로소 레코드를 읽어서 반환할 수 있다.
• Using union()과 Using sort_union()은 둘 다 충분히 인덱스를 사용할 수 있는 조건이 OR 연산자로 연결된 경우 사용됨.
• Using union()은 대체로 동등 비교(Equal)처럼 일치하는 레코드 건수가 많지 않은 경우 사용됨.
  • 각 조건이 크다 또는 작다와 같이 상대적으로 많은 레코드에 일치하는 조건이 사용되는 경우, Using sort_union()이 사용됨.
  • 하지만 실제로는 레코드 건수에 관계없이 각 WHERE 조건에 사용된 비교 조건이 모두 동등조건이면 Using union() 이 사용되며, 그렇지 않으면 Using sort_union()이 사용됨.

Using temporary

• MySQl 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary table)을 사용함.
• 임시 테이블은 메모리상에 생성될 수도 있고 디스크상에 생성될 수도 있음.
• 쿼리의 실행 계획에서 Extra 칼럼에 “Using temporary” 키워드가 표시되면 임시 테이블을 사용한 것임.
  • 이때 사용된 임시 테이블이 메모리에 생성됐는지 디스크에 생성됐는지는 실행 계획만으로는 판단할 수 없다.

실행 계획의 Extra 칼럼에 “Using temporary”가 표시되지는 않지만 실제 내부적으로 임시 테이블을 사용할 때도 많음. 대표적으로 메모리나 디스크에 임시 테이블을 생성하는 쿼리는 다음과 같음.

• FROM 절에 사용된 서브쿼리는 무조건 임시 테이블을 생성한다. 물론 이 테이블을 파생 테이블이라고 부르지만, 결국 실체는 임시 테이블임.
• “COUNT(DISTINCT column1)”을 포함하는 쿼리도 인덱스를 사용할 수 없는 경우에는 임시 테이블이 만들어짐.
• UNION이나 UNION DISTINCT가 사용된 쿼리도 항상 임시 테이블을 사용해 결과를 병합함. (8.0 버전부터 UNION ALL 이 사용된 경우, 더이상 내부 임시 테이블을 사용하지 않도록 개선됨.)
• 인덱스를 사용하지 못하는 정렬 작업 또한 임시 버퍼 공간을 사용함. 정렬해야 할 레코드가 많아지면 결국 디스크를 사용함. 정렬에 사용되는 버퍼도 결국 실체는 임시 테이블과 같음. 쿼리가 정렬을 수행할 때는 실행 계획의 Extra 칼럼에 “Using filesort”라고 표시됨.

그리고 임시 테이블이나 버퍼가 메모리에 저장됐는지, 디스크에 저장됐는지는 MySQL 서버의 상태 변숫값으로 확인할 수 있음.

p488 을 참고하자. (9.2.6절 내부 임시 테이블 활용을 참조하자)

 

Using where

• 이미 MySQL 서버의 아키텍처 부분에서 언급했듯, MySQL 서버는 내부적으로 크게 MySQL 엔진과 스토리지 엔진이라는 두 개의 레이어로 나눠 볼 수 있음.
• 각 스토리지 엔진은 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 역할을 하며, MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행함.
  • MySQL 엔진 레이어에서 별도의 가공을 해서 필터링(여과) 작업을 처리한 경우에만 Extra 칼럼에 “Using where” 코멘트가 표시됨.

• 위 그림과 같이 각 스토리지 엔진에서 전체 200 레코드를 읽었는데, MySQL 엔진에서 별도의 필터링이나 가공 없이 그 데이터를 그대로 클라이언트로 전달하면 “Using where”가 표시되지 않음.
  • 8.3.7.1절 “비교 조건의 종류와 효율성” 에서 작업 범위 결정 조건과 체크 조건의 구분을 언급했는데, 실제로 작업 범위 결정 조건은 각 스토리지 엔진 레벨에서 처리되지만 체크 조건은 MySQL 엔진 레이어에서 처리됨.

mysql> SELECT * 
             FROM employees
             WHERE emp_no BETWEEN 10001 AND 10100
             AND gender='F';

• 위 쿼리에서 작업 범위 결정 조건은 emp_no BETWEEN 10001 AND 10100 이며 gender=’F’ 는 체크 조건임.
  • emp_no 조건만 만족하는 레코드 건수는 100건임.
  • 두 조건을 만족하는 레코드는 37건밖에 안됨.
  • 즉, 스토리지 엔진은 100개를 읽어서 MySQL 엔진에 넘겨줬지만 MySQL 엔진은 그 중 63건의 레코드를 그냥 필터링해서 버렸다는 의미임.
• 실행 계획에서 Extra 칼럼에서 가장 흔히 표시되는 내용이 “Using where”임.
  • 실제로 왜 이 메시지가 표시됐는지 전혀 이해할 수 없을 때도 많음.
  • ex) 실제로 프라이머리 키 한 건의 레코드만 조회해도 “Using where”로 출력되는 문제점도 있었음.
  • 그래서 실행 계획의 “Using where”가 성능상의 문제를 일으킬지 아닐지를 적절히 선별하는 능력이 필요함.
  • 8.0에서는 실행 계획에 filtered 칼럼이 같이 표시되므로 쉽게 성능상의 이슈가 있는지 없는지를 알아낼 수 있음.
  • ex) filtered 칼럼 값이 50%인 것을 보면 옵티마이저는 100건 중, 50건은 버려지고 최종 남은 50건이 반환될 것으로 예측했다는 것을 알 수 있음.

만약, 위 쿼리에서 최종적으로 쿼리에 일치하는 레코드는 37건밖에 안되지만 스토리지 엔진은 100건의 레코드를 읽은 것임. 즉, 상당히 비효율적인 과정이라고 볼 수 있음. 근데 만약 employees 테이블에 (emp_no, gender)로 인덱스가 준비돼 있었다면 두 조건 모두 작업 범위 제한 조건으로 사용되어 필요한 37개의 레코드만 정확하게 읽을 수 있음.

Zero limit

• 때로 MySQL 서버에서 데이터 값이 아닌 쿼리 결과값의 메타데이터만 필요한 경우도 있음.
• 즉, 쿼리의 결과가 몇 개의 칼럼을 가지고, 각 칼럼의 타입은 무엇인지 등의 정보만 필요한 경우가 있음.
• 이 경우, 쿼리의 마지막에 “LIMIT 0”을 사용하면 됨.
  • 이때 MySQL 옵티마이저는 사용자의 의도(메타 정보만 조회하고자 하는 의도)를 알아채고 실제 테이블의 레코드는 전혀 읽지 않고 결괏값의 메타 정보만 반환함.
  • 이 경우, 실행 계획의 Extra 칼럼에 “Zero limit” 메시지가 출력됨.