Real MariaDB (2017년)
개요 0 0 43,534

by 구루비스터디 MariaDB 실행계획 MariaDB 옵티마이저 MariaDB 히스토그램 [2019.08.11]


4.1 개요

쿼리 실행 절차

구분단계실행 주체결과물
1SQL 파싱SQL 파서(MariaDB 엔진)SQL 파스 트리
2최적화 및 실행 계획 수립옵티마이저(MariaDB 엔진)실행 계획
3SQL 실행스토리지 엔진 + MariaDB 엔진쿼리 결과값


옵티마이저의 종류

규칙 기반 최적화
(RBO:Rule-based optimizer)
옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립
비용 기반 최적화
(CBO:Cost-based optimizer)
부하 정보와 통계 정보를 이용해 각 실행 계획별 비용을 산출하여 최소 비용의 실행 계획을 선택


MariaDB 10.0의 통계 정보

  • MariaDB 10.0부터 통합 통계 정보 관리 기능을 제공
  • 인덱스 컬럼 외 컬럼의 통계 정보 관리 가능
  • 통계 정보를 영구적으로 사용 가능: 통계 정보를 별도로 백업해 복구해서 사용할 수 있음
  • 관리자가 직접 통계 정보 변경 가능


통합 통계 테이블
table_stat컬럼의미
db_name대상 테이블이 속한 데이터베이스 명
table_name대상 테이블의 이름
cardianlity테이블의 레코드 건수
column_stat컬럼의미
db_name대상 테이블이 속한 데이터베이스 명
table_name대상 테이블의 이름
column_name대상 컬럼 이름
min_value해당 컬럼의 최소값(정수 타입도 문자열 포맷으로 저장)
max_value해당 컬럼의 최대값(정수 타입도 문자열 포맷으로 저장)
nulls_ratioNULL 값의 비율(0:NULL 없음, 0.5:NULL값을 가진 레코드가 50%, 1: 모든 레코드가 NULL)
avg_length컬럼 값의 평균 바이트 수
avg_frequency중복된 값을 가진 평균 레코드의 수(1:중복된 값 없음)
index_stat컬럼의미
db_name대상 테이블이 속한 데이터베이스 명
table_name대상 테이블의 이름
index_name대상 인덱스의 이름
prefix_arity인덱스 컬럼 순번
avg_frequency중복된 값을 가진 평균 레코드의 수(1:중복된 값 없음)


통계 파라미터
파라미터내용
use_stat_tablesneverMySQL 5.6의 통계 정보 관리 방식과 동일, 통합 통계 테이블에는 통계를 수집하지 않음
use_stat_tablescomplementary각 스토리지 엔진이 제공하는 통계정보를 우선적으로 사용하되 스토리지 엔진이 제공하는 정보가 부족하거나 없는 경우에는 MariaDB의 통합 정보가 사용됨
use_stat_tablespreferably각 스토리지 엔진별로 관리되는 통계 정보보다 MariaDB의 통합 통계 정보를 우선해서 사용



create table tabl_recalc as select * from employees;

insert into tabl_recalc select * from employees;

MariaDB [(none)]> show global variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

MariaDB [employees]> select * from mysql.table_stats where table_name='tabl_recalc';
Empty set (0.00 sec)

MariaDB [employees]> select * from mysql.innodb_table_stats where table_name='tabl_recalc';
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name  | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
| employees     | tabl_recalc | 2017-04-14 21:33:55 | 299439 |                 1057 |                        0 |
+---------------+-------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

MariaDB [employees]> show global variables like 'use_stat_tables';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| use_stat_tables | NEVER |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [employees]> analyze table tabl_recalc;
+-----------------------+---------+----------+----------+
| Table                 | Op      | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| employees.tabl_recalc | analyze | status   | OK       |
+-----------------------+---------+----------+----------+
1 row in set (0.01 sec)

MariaDB [employees]> select * from mysql.table_stats where table_name='tabl_recalc';
Empty set (0.00 sec)

MariaDB [employees]> SET SESSION use_stat_tables='COMPLEMENTARY';
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> analyze table tabl_recalc;
+-----------------------+---------+----------+-----------------------------------------+
| Table                 | Op      | Msg_type | Msg_text                                |
+-----------------------+---------+----------+-----------------------------------------+
| employees.tabl_recalc | analyze | status   | Engine-independent statistics collected |
| employees.tabl_recalc | analyze | status   | OK                                      |
+-----------------------+---------+----------+-----------------------------------------+
2 rows in set (0.54 sec)

MariaDB [employees]> select * from mysql.table_stats where table_name='tabl_recalc';
+-----------+-------------+-------------+
| db_name   | table_name  | cardinality |
+-----------+-------------+-------------+
| employees | tabl_recalc |      300024 |
+-----------+-------------+-------------+
1 row in set (0.00 sec)


통계 수집 시 주의 사항

ANALYZE TABLE 명령이 실행되면 테이블을 풀 스캔하거나 인덱스 풀 스캔하여 통계 수집
마스터와 슬레이브 구조라면 슬레이브에서 통계 정보를 수집하여 마스터에 복사


통계 생성 구문
tbl 테이블, col1과 col2 컬럼, idx1과 idx2 인덱스에 대해서만 통계 정보 수집
analyze table tbl persistent for columns (col1, col2) indexes (idx1, idx2);
tbl 테이블, col1과 col2 컬럼에 대해서만 통계 수집
analyze table tbl persistent for columns (col1, col2) indexes ();
tbl 테이블, idx1, idx2 인덱스에 대해서만 통계 수집
analyze table tbl persistent for columns () indexes (idx1, idx2);
tbl 테이블에 대해서만 통계 수집
analyze table tbl persistent for columns () indexes ();
테이블, 모든 컬럼, 모든 인덱스의 통계 수집(=analyze table tbl; )
analyze table tbl persistent for all;


히스토그램 통계 정보

  • 히스토그램: 컬럼 값의 분포도를 분석할 수 있는 통계 정보
  • Height-Balanced Histogram 알고리즘 사용

MariaDB [employees]> desc mysql.column_stats;
+---------------+-----------------------------------------+------+-----+---------+-------+
| Field         | Type                                    | Null | Key | Default | Extra |
+---------------+-----------------------------------------+------+-----+---------+-------+
| db_name       | varchar(64)                             | NO   | PRI | NULL    |       |
| table_name    | varchar(64)                             | NO   | PRI | NULL    |       |
| column_name   | varchar(64)                             | NO   | PRI | NULL    |       |
| min_value     | varbinary(255)                          | YES  |     | NULL    |       |
| max_value     | varbinary(255)                          | YES  |     | NULL    |       |
| nulls_ratio   | decimal(12,4)                           | YES  |     | NULL    |       |
| avg_length    | decimal(12,4)                           | YES  |     | NULL    |       |
| avg_frequency | decimal(12,4)                           | YES  |     | NULL    |       |
| hist_size     | tinyint(3) unsigned                     | YES  |     | NULL    |       |
| hist_type     | enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') | YES  |     | NULL    |       |
| histogram     | varbinary(255)                          | YES  |     | NULL    |       |
+---------------+-----------------------------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

--MariaDB에서 히스토그램 사용
MariaDB [employees]> set histogram_size=20;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> set use_stat_tables='preferably';
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> set histogram_type='double_prec_hb';
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> analyze table salaries;
+--------------------+---------+----------+-----------------------------------------+
| Table              | Op      | Msg_type | Msg_text                                |
+--------------------+---------+----------+-----------------------------------------+
| employees.salaries | analyze | status   | Engine-independent statistics collected |
| employees.salaries | analyze | status   | OK                                      |
+--------------------+---------+----------+-----------------------------------------+
2 rows in set (8.28 sec)

MariaDB [employees]> select table_name, min_value, max_value, hist_size, hist_type,
    -> decode_histogram(hist_type, histogram) as histogram
    -> from mysql.column_stats
    -> where table_name='salaries' and column_name='salary'\G
*************************** 1. row ***************************
table_name: salaries
 min_value: 38623
 max_value: 158220
 hist_size: 20
 hist_type: DOUBLE_PREC_HB
 histogram: 0.04030,0.03436,0.03273,0.03198,0.03232,0.03375,0.03688,0.04285,0.05443,0.07974,0.58065    <<< 버킷최대값 / (최대값-최소값) * 216 (single_prec_hb:28)
1 row in set (0.00 sec)

MariaDB [employees]> set histogram_size=100;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> set use_stat_tables='preferably';
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> set histogram_type='double_prec_hb';
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> analyze table salaries;
+--------------------+---------+----------+-----------------------------------------+
| Table              | Op      | Msg_type | Msg_text                                |
+--------------------+---------+----------+-----------------------------------------+
| employees.salaries | analyze | status   | Engine-independent statistics collected |
| employees.salaries | analyze | status   | OK                                      |
+--------------------+---------+----------+-----------------------------------------+
2 rows in set (7.94 sec)

MariaDB [employees]> set optimizer_use_condition_selectivity=4;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> explain extended select * from salaries where to_date <= '1989-03-01';
+------+-------------+----------+------+---------------+------+---------+------+---------+----------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+---------+----------+-------------+
|    1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844047 |     5.88 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [employees]> select count(*), (2844047*0.0588) as expected_value from salaries where to_date <= '1989-03-01';
+----------+----------------+
| count(*) | expected_value |
+----------+----------------+
|   125536 |    167229.9636 |
+----------+----------------+
1 row in set (2.40 sec)

MariaDB [employees]> set histogram_size=200;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> analyze table salaries;
+--------------------+---------+----------+-----------------------------------------+
| Table              | Op      | Msg_type | Msg_text                                |
+--------------------+---------+----------+-----------------------------------------+
| employees.salaries | analyze | status   | Engine-independent statistics collected |
| employees.salaries | analyze | status   | OK                                      |
+--------------------+---------+----------+-----------------------------------------+
2 rows in set (9.70 sec)

MariaDB [employees]> explain extended
    -> select * from salaries where to_date <= '1989-03-01';
+------+-------------+----------+------+---------------+------+---------+------+---------+----------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+---------+----------+-------------+
|    1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844047 |     4.95 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MariaDB [employees]> select count(*), (2844047*0.0495) as expected_value from salaries where to_date <= '1989-03-01';
+----------+----------------+
| count(*) | expected_value |
+----------+----------------+
|   125536 |    140780.3265 |
+----------+----------------+
1 row in set (2.83 sec)


조인 옵티마이저 옵션

  • Exhaustive 검색: from절에 명시된 모든 테이블 조합에 대해서 실행 계획의 비용을 계산하여 최적의 조합 선택
  • Heuristic 검색(Greedy 검색)
구분내용
1전체 N개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
21번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
32번에서 선정된 실행 계획의 첫 번째 테이블을 "부분 실행 계획"의 첫번째 테이블로 선정
4전체 N-1개의 테이블 중에서(3번에서 선택된 테이블 제외) optimizer_search_depth 시스템 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
54번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 "부분 실행 계획"에 대입해서 실행 비용을 계산
65번의 비용 계산 결과, 가장 비용이 낮은 실행 계획을 골라서 그 중 두 번째 테이블을 3번에서 생성된 "부분 실행 계획"의 두 번째 테이블로 선정
7남은 테이블이 모두 없어질 때까지 4~6번까지의 과정을 반복 실행하면서 "부분 실행 계획"에 테이블의 조인 순서를 기록
8최종적으로 "부분 실행 계획"이 테이블의 조인 순서로 결정


  • optimizer_search_depth의 default 값: 64



MariaDB [employees]> set session optimizer_prune_level=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> set session optimizer_search_depth=5;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> select * from tab01,tab02,tab03,tab04,tab05,tab06,tab07,tab08,tab09,tab10, tab11,tab12,tab13,tab14,tab15,tab16,tab17,tab18,tab19,tab20, tab21,tab22,tab23,tab24,tab25,tab26,tab27,tab28,tab29,tab30 where tab01.fd1=tab02.fd1 and tab02.fd1=tab03.fd2 and tab03.fd1=tab04.fd1 and tab04.fd2=tab05.fd2 and tab05.fd2=tab06.fd1 and tab06.fd2=tab07.fd2 and tab07.fd1=tab08.fd1 and tab08.fd1=tab09.fd2 and tab09.fd1=tab10.fd1 and tab10.fd2=tab11.fd2 and tab11.fd2=tab12.fd1 and tab12.fd2=tab13.fd2 and tab13.fd1=tab14.fd1 and tab14.fd1=tab15.fd2 and tab15.fd1=tab16.fd1 and tab16.fd2=tab17.fd2 and tab17.fd2=tab18.fd1 and tab18.fd2=tab19.fd2 and tab19.fd1=tab20.fd1 and tab20.fd1=tab21.fd2 and tab21.fd1=tab22.fd1 and tab22.fd2=tab23.fd2 and tab23.fd2=tab24.fd1 and tab24.fd2=tab25.fd2 and tab25.fd1=tab26.fd1 and tab26.fd1=tab27.fd2 and tab27.fd1=tab28.fd1 and tab28.fd2=tab29.fd2 and tab29.fd2=tab30.fd1;
(((결과 생략)))
2000 rows in set (0.09 sec)

MariaDB [employees]> set session optimizer_search_depth=64;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

MariaDB [employees]> reset query cache;
Query OK, 0 rows affected (0.00 sec)

MariaDB [employees]> select * from tab01,tab02,tab03,tab04,tab05,tab06,tab07,tab08,tab09,tab10, tab11,tab12,tab13,tab14,tab15,tab16,tab17,tab18,tab19,tab20, tab21,tab22,tab23,tab24,tab25,tab26,tab27,tab28,tab29,tab30 where tab01.fd1=tab02.fd1 and tab02.fd1=tab03.fd2 and tab03.fd1=tab04.fd1 and tab04.fd2=tab05.fd2 and tab05.fd2=tab06.fd1 and tab06.fd2=tab07.fd2 and tab07.fd1=tab08.fd1 and tab08.fd1=tab09.fd2 and tab09.fd1=tab10.fd1 and tab10.fd2=tab11.fd2 and tab11.fd2=tab12.fd1 and tab12.fd2=tab13.fd2 and tab13.fd1=tab14.fd1 and tab14.fd1=tab15.fd2 and tab15.fd1=tab16.fd1 and tab16.fd2=tab17.fd2 and tab17.fd2=tab18.fd1 and tab18.fd2=tab19.fd2 and tab19.fd1=tab20.fd1 and tab20.fd1=tab21.fd2 and tab21.fd1=tab22.fd1 and tab22.fd2=tab23.fd2 and tab23.fd2=tab24.fd1 and tab24.fd2=tab25.fd2 and tab25.fd1=tab26.fd1 and tab26.fd1=tab27.fd2 and tab27.fd1=tab28.fd1 and tab28.fd2=tab29.fd2 and tab29.fd2=tab30.fd1;
(((결과 생략)))
2000 rows in set (0.10 sec)


"구루비 데이터베이스 스터디모임" 에서 2017년에 "Real MariaDB" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4186

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입