04 실행계획 분석

4.1 개요

4.1.1 쿼리 실행 절차

MariaDB 서버에서 쿼리가 실행되는 과정은 크게 3가지로 나눌수 있습니다.

- 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MariaDB서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.

- SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.

- 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

 

첫번째 단계를 SQL 파싱이라고 하며 MariaDB서버의 SQL 파서라는 모듈로 처리합니다.

만약 SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러집니다. 또한 이 단계에서 SQL 파스 트리가 만들어집니다. MariaDB서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행합니다.

 

두번째 단계는 첫번째 단계에서 만들어지 SQL 파스 트리를 참조하면서 최적화 및 실행 계획 수립을 합니다. 이 단계는 옵티마이저에서 처리합니다. 두번째 단계가 완료되면 쿼리의 실행계획이 만들어집니다.

 

세번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MariaDB 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행합니다.

 

첫번째와 두번째 단계는거의 MariaDB 엔진에서 처리하며, 세번째 단계는 MariaDB엔진과 스토리지 엔진이 동시에 참여해서 처리합니다.

 

4.1.2 옵티마이저의 종류

대부분의 DBMS가 선택하고 있는 CBO(Cost-based optimizer)와 RBO(Rule-based optimizer)로 나눌 수 있습니다.

현재는 거의 대부분이 CBO를 채택하고 있으며 MariaDB도 마찬가지입니다.

 

4.1.3 통계정보

CBO에서 제일 중요한 것은 통계정보입니다. 하지만 다른 DBMS보다 통계 정보는 그리 다양하지 않습니다. 기본적으로 MariaDB에서 관리되는 통계정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부입니다.

 

4.1.3.1 MySQL 5.6의 통계정보

MariaDB 10.0은 기본적으로 MySQL 5.6의 기능들을 포함하고 있기 때문에 MySQL 5.6의 통계정보가 어떻게 관리되는지를 이해하는 것은 MariaDB의 통계 정보 관리 방법을 이해하는데 도움이 될 것입니다.

MySQL5.6에서는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있게 개선되었습니다. 5.5버전까지는 show index from '테이블' 명령으로만 테이블의 인덱스 칼럼의 분포도를 볼 수 있었지만, mysql데이터베이스의 innodb_index_stats,innodb_table_stats테이블에서도 인덱스를 조회할 수 있게 되었습니다.

 

mysql> show tables like '%_stats';
+---------------------------+
| Tables_in_mysql (%_stats) |
+---------------------------+
| innodb_index_stats        |
| innodb_table_stats        |
+---------------------------+
2 rows in set (0.00 sec)

 

MySQL5.6에서 테이블을 생성할 때는 STATS_PERSISTENT 옵션을 설정할 수 있는데, 이 설정 값에 따라서 테이블 단위로 영구적인 통계 정보를 보관할지 말지를 결정할 수 있습니다.

 

STATS_PERSISTENT=0

테이블의 통계 정보를 MySQL 5.5 이전의 방식대로 관리하며, mysql 데이터베이스의 innodb_index_stats와 innodb_table_stats에 저장하지 않음

STATS_PERSISTENT=1

테이블의 통계정보를 mysql 데이터베이스의 innodb_index_stats와 innodb_table_stats에 저장함

STATS_PERSISTENT=DEFAULT

테이블을 생성할 때 별도로 옵션을 설정하지 않은 것과 동일하며 1과 같은 의미

기본적으로 ON(1)로 설정되어 있습니다.

 

이미 생성된 테이블의 통계정보를 영구적 또는 단기적으로 변경하는 것은 ALTER TABLE 명령으로 실행할 수 있습니다.

 

MySQL 5.5버전까지는 다음 경우마다 테이블의 통계정보가 새로 수집되었습니다.(테이블 생성, 레코드 대량 변경,삽입,ANALYZE TABLE 명령어 실행 SHOW TABLE STATUS,SHOW INDEX FROM 명령어 실행 등)

이렇게 자주 통계정보가 변경되어 버리면 갑자기 성능이 이슈가 생길 수도 있는 가능성이 높아질 것입니다.(Index range scan을 타다가 갑자기 table full scan을 타버린다거나..)

하지만 영구적인 통계정보가 도입되면서 의도되지 않은 통계 정보 변경을 막을 수 있게 되었습니다.

innodb_stats_auto_recalc=OFF로 설정해서 자동으로 통계정보가 수집되는 것을 막을 수 있습니다. 기본적으로 ON이므로 영구적인 통계정보를 이용하고자 한다면 OFF로 변경하면 됩니다.

또한 통계 정보를 자동으로 수집할지 여부도 테이블을 생성할 때 STATS_AUTO_RECALC 옵션을 이용해서 테이블 단위로 조정할 수 있습니다.

 

STATUS_AUTO_RECALC=1

테이블의 통계정보를 MySQL 5.5이전의 방식대로 자동 수집하게 됩니다.

STATUS_AUTO_RECALC=0

테이블의 통계정보는 ANALYZE TABLE명령을 실행할 때에만 수집됩니다.

STATUS_AUTO_RECALC=DEFAULT

옵션을 설정하지 않은것과 동일하며 테이블의 통계 정보 수집을 innodb_stats_auto_recalc 시스템 변수의 값으로 결정합니다.

 

Mysql 5.6버전에서는 innodb_stats_tranient_sample_pages와 innodb_stats_persistent_sample_pages 시스템변수를 사용해서 통계정보를 수집할 때 몇개의 InnoDB 테이블 블록을 샘플링할지 결정할 수 있습니다.

innodb_stats_tranient_sample_pages : 기본값 8, 자동으로 통계 정보 수집이 실행될 때 8개 페이지만 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용함을 의미

innodb_stats_persistent_sample_pages  : 기본값 20, ANALYZE TABLE 명령이 실행되면 임의로 20개 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용함을 의미

 

4.1.3.2 MariaDB 10.0의 통계정보

MariaDB 10.0에서는 스토리지 엔진에 관계없이 사용 가능한 통합된 통계정보를 관리할 수 있는 기능을 제공하고 있습니다. 다른 DBMS와 같이 통계 정보를 별도로 백업해 두거나 다시 복구해서 사용할 수 있게 되었습니다.

MariaDB의 통합된 통계 정보는 mysql 데이터베이스에 table_stats와 column_stats 그리고 index_stats 테이블로 관리되는데, 이 테이블들은 모두 MyISAM스토리지 엔진을 사용합니다.

 

MariaDB [mysql]> show tables like '%_stats';
+---------------------------+
| Tables_in_mysql (%_stats) |
+---------------------------+
| column_stats              |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| table_stats               |
+---------------------------+
5 rows in set (0.002 sec)

실제 쿼리가 실행될 때 어떤 통계 정보를 사용할지는 use_stat_tables 시스템 설정에 의해서 결정됩니다. (DEFAULT는 never)

use_stat_tables ='never'

MySQL 5.6의 통계 정보 관리 방식과 동일하게 작동하며 mysql데이터베이스의 table_stats와 column_stats그리고 index_stats테이블에는 통계정보가 수집되지 않음. MariaDB 10.0에서도 use_stat_tables 시스템 변수를 never로 설정해서 MySQL 5.6버전의 영구적 통계 정보를 그대로 사용할 수 있음

use_stat_tables ='complementary'

각 스토리지 엔진이 제공하는 통계 정보를 우선적으로 사용하되 스토리지 엔진이 제공하는 정보가 부족하거나 없는 경우에는 mariaDB의 통합 통계정보가 사용

use_stat_tables ='preferably'

MariaDB의 통합 통계정보를 우선해서 사용

4.1.4 히스토그램 통계 정보

MariaDB 10.0에서만 지원하고 있습니다. 테이블의 모든 칼럼에 대해 최솟값과 최댓값 그리고 NULL값을 가진 레코드의 비율 그리고 갈럼 값들의 분포를 히스토그램으로 수집해서 mysql.column_stats에서 관리합니다.

4.1.4.2 mariaDB에서 히스토그램 사용

히스토그램은 ANALYZE TABLE 명령으로 다른 통계 정보와 함께 생성됩니다. histogram_size 설정으로 히스토그램 수집여부를 결정할 수 있습니다. 기본값은 0이고 사용하지 않는다는것을 의미합니다. 0보다 크게 설정하고 히스토그램의 정확도를 위해서 histogram_type시스템 설정 변수를 DOUBLE_PREC_HB로 할지, SINGLE_PREC_HB로 할지 설정해야 합니다.

 

4.1.5 조인 옵티마이저 옵션

실행계획 최적와를 위한 옵티마이저 옵션이 2개있습니다.

Exhaustive 검색

MySQL 5.0과 그 이전 버전에서 사용되던 조인 최적화 기법으모 FROM절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법입니다. 만약 테이블이 20개라면 가능한 조인조합은 20!(3628800)개가 됩니다.

 

Greedy 검색

Exhaustive검색의 문제점을 해결하기 위해 MySQL 5.0부터 도입되었습니다. 

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_dept=64가 기본값입니다. 63으로 설정하면 Exhaustive 검색알고리즘을 사용합니다. 0~62로 설정하면 설정된 개수로 한정해서 최적의 실행계획을 산출합니다.

optimizer_prune_level=ON

Heuristic검색이라는 중요한 조인 최적화 기능입니다. 다양한 조인 순서의 비용을 계산하는 도중 언제든지 이미 계산했던 조인 순서의 비용보다 큰 경우 중간에 포기할 수 있다는 것입니다. 이는 성능차이를 낼 수 있으므로 왠만하면 ON으로 설정해서 사용하도록 합니다.

 

출처 : Real MariaDB