[MySQL] Multi-Range Read(다중범위 읽기) 최적화

보조 인덱스에서 범위 스캔을 사용하여 행을 읽으면 테이블이 크고 스토리지 엔진 캐시에 저장되지 않은 경우 기본 테이블에 대한 임의의 디스크 액세스가 많이 발생할 수 있습니다. 디스크 스윕 다중 범위 읽기 (MRR) 최적화를 통해 MySQL은 먼저 인덱스 만 스캔하고 관련 행에 대한 키를 수집하여 범위 스캔에 대한 임의 디스크 액세스 수를 줄입니다. 그런 다음 키가 정렬되고 기본 키 순서대로 기본 테이블에서 행이 검색됩니다. 디스크 스윕 MRR의 동기는 무작위 디스크 액세스 수를 줄이고 대신 기본 테이블 데이터의 순차적 스캔을 달성하는 것입니다.

 

다중 범위 읽기 최적화는 다음과 같은 이점을 제공합니다.

- MRR을 사용하면 인덱스 튜플을 기반으로 임의의 순서가 아닌 순차적으로 데이터 행에 액세스할 수 있습니다. 서버는 쿼리 조건을 만족하는 인덱스 튜플 세트를 가져 와서 데이터 행 ID 순서에 따라 정렬한 다음 정렬 된 튜플을 사용하여 데이터 행을 순서대로 검색합니다. 이는 데이터 액세스를보다 효율적이고 저렴하게 만듭니다.

- MRR은 범위 인덱스 스캔 및 조인 속성에 인덱스를 사용하는 동일 조인과 같은 인덱스 튜플을 통해 데이터 행에 액세스해야 하는 작업에 대한 키 액세스 요청의 일괄 처리를 지원합니다. MRR은 일련의 인덱스 범위를 반복하여 적합한 인덱스 튜플을 얻습니다. 이러한 결과가 누적되면 해당 데이터 행에 액세스하는 데 사용됩니다. 데이터 행 읽기를 시작하기 전에 모든 인덱스 튜플을 확보 할 필요는 없습니다.

 

가상 생성 컬럼에서 작성된 2차 인덱스에서는 MRR 최적화가 지원되지 않습니다. InnoDB는 가상 생성 컬럼에서 보조 인덱스를 지원합니다.

 

다음 시나리오는 MRR 최적화가 유리할 수 있는 시기를 보여줍니다.

 

시나리오 A : 인덱스 범위 스캔 및 동일 조인 조작을 위해 InnoDB 및 MyISAM 테이블에 MRR을 사용할 수 있습니다.

인덱스 튜플의 일부는 버퍼에 축적됩니다.

2. 버퍼의 튜플은 데이터 행 ID별로 정렬됩니다.

3. 정렬된 인덱스 튜플 순서에 따라 데이터 행에 액세스합니다.

 

시나리오 B : MRR은 다중 범위 인덱스 스캔의 NDB 테이블에 사용되거나 속성으로 동일 조인을 수행 할 때 사용할 수 있습니다.

1. 범위의 일부, 단일키 범위는 쿼리가 제출된 중앙 노드의 버퍼에 누적됩니다.

2. 범위는 데이터 행에 액세스하는 실행 노드로 전송됩니다.

3. 액세스된 행은 패키지로 압축되어 중앙 노드로 다시 전송됩니다.

4. 데이터 행이있는 수신 패키지는 버퍼에 배치됩니다.

5. 버퍼에서 데이터 행을 읽습니다.

 

MRR이 사용되면 EXPLAIN 출력의 Extra 열에 Using MRR이 표시됩니다.

 

쿼리 결과를 생성하기 위해 전체 테이블 행에 액세스 할 필요가 없는 경우 InnoDB 및 MyISAM은 MRR을 사용하지 않습니다. 인덱스 튜플의 정보 (커버링 인덱스를 통해)를 기반으로 결과가 완전히 생성 될 수있는 경우입니다. MRR은 이점이 없습니다.

 

두 개의 optimizer_switch 시스템 변수 플래그는 MRR 최적화 사용에 대한 인터페이스를 제공합니다. mrr 플래그는 MRR의 사용 가능 여부를 제어합니다. mrr이 사용 가능(on)인 경우 mrr_cost_based 플래그는 옵티마이저가 MRR 사용 여부(on) 또는 가능할 때마다 MRR 사용(cost) 사이에서 비용 기반 선택을 시도할지 여부를 제어합니다. 기본적으로 mrr은 켜져 있고 mrr_cost_based는 켜져 있습니다.

 

MRR의 경우 스토리지 엔진은 read_rnd_buffer_size 시스템 변수 값을 버퍼에 할당할 수 있는 메모리 양에 대한 지침으로 사용합니다. 엔진은 최대 read_rnd_buffer_size 바이트를 사용하고 단일 패스에서 처리 할 범위 수를 결정합니다.