MariaDB/SQL Tuning

[MariaDB][성능] 멀티 레인지 리드(multi range read) 최적화

DBA_JSH 2016. 9. 25. 22:43
[테스트 환경] 
OS : CentOS7 
DB : MariaDB 10.1.12 

멀티 레인지 리드란 ?
Multi Range Read(MRR)는 Random I/O를 Sequential I/O로 처리할 수 있도록 도와주는 기능으로, Non-Clustered Index를 통해 Range Scan을 하는 경우, 바로 데이터를 조회하지 않고 어느 정도 rowid(primary key) 값들을 Random 버퍼(mrr_buffer_size)에 채운 다음, 버퍼 내용을 정렬하여 최대한 rowid(primary key) 순서대로 데이터를 접근할 수 있도록 해주는 것이다.



1. rowid 기준 정렬 (Rowid-odered scan) 
/*파라미터 설정*/
--멀티 레인지 스캔 최적화를 하기 위해서 아래와 같은 파라미터를 활성화(on) 한다.
SET optimizer_switch='mrr=on';
SET optimizer_switch='mrrsort_keys=on';

/*인덱스 생성*/
create index ix_firstname on employees(first_name);
create index salary_idx01 on salaries(salary);

/*first_name이 A로 시작하면서 연봉이 30000 ~ 100000인직원을 조회 하시오*/
-- 건수 : 201417
 analyze format=json
-- EXPLAIN
SELECT   A.emp_no,
         A.first_name, 
         A.last_name,
         B.salary,
         A.gender, 
         A.hire_date 
FROM     employees A 
         INNER JOIN salaries B ON(A.emp_no = B.emp_no)
WHERE    A.first_name LIKE 'A%'
AND      B.salary BETWEEN 30000 AND 100000;
;

/*mrr 사용*/

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 343.66,
    "table": {
      "table_name": "A",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "ix_firstname"],
      "key": "ix_firstname",
      "key_length": "44",
      "used_key_parts": ["first_name"],
      "r_loops": 1,
      "rows": 43234,
      "r_rows": 22039,
      "r_total_time_ms": 45.616,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "(A.first_name like 'A%')",
      "mrr_type": "Rowid-ordered scan"
    },
    "table": {
      "table_name": "B",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "emp_no_salary_idx01", "salary_idx01"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["emp_no"],
      "ref": ["employees.A.emp_no"],
      "r_loops": 22039,
      "rows": 4,
      "r_rows": 9.4711,
      "r_total_time_ms": 178.23,
      "filtered": 100,
      "r_filtered": 96.495,
      "attached_condition": "(B.salary between 30000 and 100000)"
    }
  }
}

/*mrr 미사용*/
/*수행시간 1.6 ~ 1.8초*/
/*파라미터 설정*/
SET optimizer_switch='mrr=off';

SET optimizer_switch='mrr_sort_keys=off';
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 333.9,
    "table": {
      "table_name": "A",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "ix_firstname"],
      "key": "ix_firstname",
      "key_length": "44",
      "used_key_parts": ["first_name"],
      "r_loops": 1,
      "rows": 43234,
      "r_rows": 22039,
      "r_total_time_ms": 71.33,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "(A.first_name like 'A%')"
    },
    "table": {
      "table_name": "B",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "emp_no_salary_idx01", "salary_idx01"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["emp_no"],
      "ref": ["employees.A.emp_no"],
      "r_loops": 22039,
      "rows": 4,
      "r_rows": 9.4711,
      "r_total_time_ms": 191.01,
      "filtered": 100,
      "r_filtered": 96.495,
      "attached_condition": "(B.salary between 30000 and 100000)"
    }
  }
}

1-1 조회 되는 데이터의 양이 적다면 옵티마이저는 멀티 레인지 스캔을 하지 않는가? 
/*데이터 건수가 적어도 멀티 레이니지 리드 최적화를 사용 한다.*/
 -- 데이터 건수와 상관 없이 range 조건일 경우 mrr을 상요하고, 등치조건 일때는 사용하지 않는다.
-- 데이터 건수 : 1979건
analyze format=json
-- EXPLAIN
SELECT   A.emp_no,
         A.first_name, 
         A.last_name,
         B.salary,
         A.gender, 
         A.hire_date 
FROM     employees A 
         INNER JOIN salaries B ON(A.emp_no = B.emp_no)
WHERE    A.first_name LIKE 'AE%'
AND      B.salary BETWEEN 30000 AND 100000;
;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 5.0293,
    "table": {
      "table_name": "A",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "ix_firstname"],
      "key": "ix_firstname",
      "key_length": "44",
      "used_key_parts": ["first_name"],
      "r_loops": 1,
      "rows": 207,
      "r_rows": 207,
      "r_total_time_ms": 0.5652,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "(A.first_name like 'AE%')",
      "mrr_type": "Rowid-ordered scan"
    },
    "table": {
      "table_name": "B",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "emp_no_salary_idx01", "salary_idx01"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["emp_no"],
      "ref": ["employees.A.emp_no"],
      "r_loops": 207,
      "rows": 4,
      "r_rows": 10.01,
      "r_total_time_ms": 3.1933,
      "filtered": 100,
      "r_filtered": 95.512,
      "attached_condition": "(B.salary between 30000 and 100000)"
    }
  }
}
2.key 기준 정렬 (Key-odered scan) 
/*파라미터 설정*/
-- key기준 정렬 최적화를 하기 위해서 아래와 같은 파라미터를 활성화(on)  한다
SET optimizer_switch='join_cache_hashed=on';
SET optimizer_switch='join_cache_bka=on';
/*레벨의 상세 설명은 리얼 마리아DB 309쪽 참조*/
SET join_cache_level =8;

analyze format=json
-- EXPLAIN
SELECT   A.emp_no,
         A.first_name, 
         A.last_name,
         B.salary,
         A.gender, 
         A.hire_date 
FROM     employees A 
         INNER JOIN salaries B ON(A.emp_no = B.emp_no)
WHERE    A.first_name LIKE 'A%'
AND      B.salary BETWEEN 30000 AND 100000;


{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 349.44,
    "table": {
      "table_name": "A",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "ix_firstname"],
      "key": "ix_firstname",
      "key_length": "44",
      "used_key_parts": ["first_name"],
      "r_loops": 1,
      "rows": 43234,
      "r_rows": 22039,
      "r_total_time_ms": 35.722,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "(A.first_name like 'A%')",
      "mrr_type": "Rowid-ordered scan"
    },
    "block-nl-join": {
      "table": {
        "table_name": "B",
        "access_type": "ref",
        "possible_keys": ["PRIMARY", "emp_no_salary_idx01", "salary_idx01"],
        "key": "PRIMARY",
        "key_length": "4",
        "used_key_parts": ["emp_no"],
        "ref": ["employees.A.emp_no"],
        "r_loops": 0,
        "rows": 4,
        "r_rows": null,
        "r_total_time_ms": 154.09,
        "filtered": 100,
        "r_filtered": null
      },
      "buffer_type": "flat",
      "buffer_size": "2048Kb",
      "join_type": "BKAH",
      "mrr_type": "Key-ordered scan",
      "attached_condition": "(B.salary between 30000 and 100000)",
      "r_filtered": 96.495
    }
  }
}

/*mrr 미사용*/
SET optimizer_switch='join_cache_hashed=off';

SET optimizer_switch='join_cache_bka=off';

/*레벨의 상세 설명은 리얼 마리아DB 309쪽 참조*/
SET join_cache_level =2;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 334.7,
    "table": {
      "table_name": "A",
      "access_type": "range",
      "possible_keys": ["PRIMARY", "ix_firstname"],
      "key": "ix_firstname",
      "key_length": "44",
      "used_key_parts": ["first_name"],
      "r_loops": 1,
      "rows": 43234,
      "r_rows": 22039,
      "r_total_time_ms": 44.15,
      "filtered": 100,
      "r_filtered": 100,
      "index_condition": "(A.first_name like 'A%')",
      "mrr_type": "Rowid-ordered scan"
    },
    "table": {
      "table_name": "B",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "emp_no_salary_idx01", "salary_idx01"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["emp_no"],
      "ref": ["employees.A.emp_no"],
      "r_loops": 22039,
      "rows": 4,
      "r_rows": 9.4711,
      "r_total_time_ms": 170.41,
      "filtered": 100,
      "r_filtered": 96.495,
      "attached_condition": "(B.salary between 30000 and 100000)"
    }
  }
}
3.멀티 레인지 상태 변수 확인 (Key-odered scan) 
/*멀티 레인지 리드 최적화 사용 횟수*/
show global status like '%Handler_mrr_init%';

/*멀티 레인지 리드 최적화에서 key_ordered scan이 몇번이나 mrr 버퍼가 다시 채워 졌는지 확인*/
show global status like '%Handler_mrr_key_refills%';

/*멀티 레인지 리드 최적화에서 rowid_ordered scan이 몇번이나 mrr 버퍼가 다시 채워 졌는지 확인*/
show global status like '%Handler_mrr_rowid_refills%';

MariaDB [mysql]> show global status like '%Handler_mrr%' \G;
*************************** 1. row ***************************
Variable_name: Handler_mrr_init
        Value: 144
*************************** 2. row ***************************
Variable_name: Handler_mrr_key_refills
        Value: 0
*************************** 3. row ***************************
Variable_name: Handler_mrr_rowid_refills
        Value: 12
3 rows in set (0.00 sec)


'MariaDB > SQL Tuning' 카테고리의 다른 글

[MariaDB][성능] 서브쿼리 최적화  (0) 2016.09.25
[MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교2  (0) 2016.08.28
[MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교1  (0) 2016.08.24
[MariaDB][성능] 스칼라 서브쿼리 실행계획2  (0) 2016.08.17
[MariaDB][성능]인라인뷰 처리 방식 분석  (0) 2016.08.17

'MariaDB/SQL Tuning'의 다른글

  • 현재글[MariaDB][성능] 멀티 레인지 리드(multi range read) 최적화

관련글

티스토리툴바