꿈꾸는 개발자, DBA 커뮤니티 구루비

4.1 개요

쿼리 실행 절차

구분	단계	실행 주체	결과물
1	SQL 파싱	SQL 파서(MariaDB 엔진)	SQL 파스 트리
2	최적화 및 실행 계획 수립	옵티마이저(MariaDB 엔진)	실행 계획
3	SQL 실행	스토리지 엔진 + 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_ratio	NULL 값의 비율(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_tables	never	MySQL 5.6의 통계 정보 관리 방식과 동일, 통합 통계 테이블에는 통계를 수집하지 않음
use_stat_tables	complementary	각 스토리지 엔진이 제공하는 통계정보를 우선적으로 사용하되 스토리지 엔진이 제공하는 정보가 부족하거나 없는 경우에는 MariaDB의 통합 정보가 사용됨
use_stat_tables	preferably	각 스토리지 엔진별로 관리되는 통계 정보보다 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 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
2	1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
3	2번에서 선정된 실행 계획의 첫 번째 테이블을 "부분 실행 계획"의 첫번째 테이블로 선정
4	전체 N-1개의 테이블 중에서(3번에서 선택된 테이블 제외) optimizer_search_depth 시스템 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
5	4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 "부분 실행 계획"에 대입해서 실행 비용을 계산
6	5번의 비용 계산 결과, 가장 비용이 낮은 실행 계획을 골라서 그 중 두 번째 테이블을 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)