1.쿼리 실행 절차 3단계
1.1 SQL 파싱 (MariaDB 엔진)
- 요청된 SQL 문을 MariaDB 가 이해 할 수 있도록 잘게 쪼개어 분리 작업 및 문법 체크
1.2 최적화 및 실행계획 수립 (MariaDB 엔진)
- 불필요한 조건의 제거 및 연산의 단순화
- 조인 순서 결정
- 인덱스 선정
- 레코드 데이터 가공 여부 결정
- 실행계획 수립
1.3 데이터 추출 (스토리지 엔진 + MariaDB 엔진)
- 실행계획을 토대로 스토리지 엔진에 레코드 요청 (스토리지 엔진)
- 받은 레코드에 대한 조인 및 정렬 작업 (MariaDB 엔진)
2. 옵티마이저의 종류
- 옵티마이저란 ?
사용자가 요청한 SQL 을 가장 효율적이고 빠르게 수행할수 있는 최적 ( 최저비용 ) 의 처리경로를 선택해주는 DBMS 핵심 엔진
쿼리 실행 절차 2단계인 최적화 및 실행계획 수립을 옵티마이저에서 수행 한다.
2.1 규칙기반 최적화
- 데이터 건수 및 선택도를 고려하지않고 옵티마이저에 저장되어있는 우선순위에 의해서 실행계획을 생성한다
2.2 비용기반 최적화
- 통계정보들을 활용하여 최소 비용이 소요되는 방식을 선택한다.
3. 통계 정보
3.1 MySQL 5.6 통계 정보 (MariaDB 10.0)
- InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계정보 관리
- mysql 데이터베이스 innodb_table_stats / innodb_index_stats 테이블에서 통계정보 조회가능
3.1.1 통계 정보 저장 옵션
STATS_PERSISTENT : 테이블 생성 시 영구적인 통계정보를 생성할지 말지 결정한다.
( 0 : 저장안함 / 1 : 저장 / DEFAULT : innodb_stas_persistent 의 시스템 설정 값을 따름)
STATS_AUTO_RECALC : 통계정보를 자동으로 수집할지 여부를 결정
(0 : ANALYZE TABLE 명령어를 실행 할 때만 수집 / 1 : MySQL 5.5 이전의 방식대로 자동 수집 / DEFAULT : innodb_stats_auto_recalc 의 시스템 설정 값을 따름)
3.1.2 MySQL 5.5 이전 통계 정보 (MariaDB 5.5)
- MySQL 5.5 버전이나 MariaDB 5.5 버전까지는 다음 경우마다 통계정보를 수집했다.
- 새로운 테이블 생성
- 대량의 레코드 변경
- ANALYZE TABLE 명령어 실행
- SHOW TABLE STATUS / SHOW INDEX FROM 명령어 실행
- InnoDB 모니터 활성화
3.1.3 통계정보 생성 시 테이터 블록 샘플링 결정 변수
- innodb_stats_transient_sample_pages : 자동으로 통계 정보 수집이 될 때 설정된 페이지만 임의로 샘플링 해서 분석 (default값:8)
- innodb_stats_persistent_sample_pages : ANALYZE TABLE 명령이 실행되면 설정된 페이지만 임의로 샘플링 해서 분석 (default값:20)
3.2 MariaDB 10.0 통계 정보
- 스토리지 엔진에 관계없이 통합된 통계정보 기능 제공
- 인덱스 되지않은 컬럼에 대한 통계정보 관리
- 통계정보 백업 복구 및 관리자에 의한 변경 가능
3.2.1 통합 통계 정보 테이블
- table_stats
- column_stats
- index_stats
3.2.2 통합 통계 정보 파라미터
- use_stat_tables=nerver : 통합 통계 정보테이블을 사용하지 않음
- use_stat_tables=complementary : 스토리지 엔진 통계 정보를 우선적으로 사용. 정보 부족 시 통합 통계 정보 사용
- use_stat_tables=preferably: 통합 통계 정보를 우선적으로 사용. 정보 부족 시 스토리지 엔진 통계 정보 사용
4. MariaDB 통합 통계 정보 생성 테스트
4.1 설정 변수 확인
- 테스트 환경을 위해 다음과 같은 변수 값을 설정한다.
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_auto_recalc | ON | +--------------------------+-------+ MariaDB [(none)]> SHOW VARIABLES LIKE 'use_stat_tables'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | use_stat_tables | PREFERABLY | +-----------------+------------+
4.2 데이터 적재
- 자동으로 통계 정보가 수집되도록 10만건의 데이터를 입력한다.
MariaDB [(none)]> USE test;
MariaDB [test]> CREATE TABLE TEST1 (ID INT(11) , NAME VARCHAR(20) , PRIMARY KEY (ID));
Query OK, 0 rows affected (0.03 sec)MariaDB [test]> INSERT INTO TEST1SELECT @A:=@A+1 , 'TEST1' FROM (SELECT @A:=20000) A INNER JOIN information_schema.COLUMNS B INNER JOIN information_schema.COLUMNS C LIMIT 100000;
Query OK, 100000 rows affected (0.53 sec)
Records: 100000 Duplicates: 0 Warnings: 0
4.3 통계 정보 테이블 확인
- MariaDB 통합 통계 정보와는 무관하게 스토리지 엔진 통계 정보에만 수집이 되는것을 확인 할 수 있다.
/* MariaDB 통합 통계 정보 확인*/ SELECT * FROM mysql.table_stats WHERE TABLE_NAME = 'TEST1'; Empty set (0.00 sec) /* 스토리지 엔진 통계 정보 확인*/ MariaDB [test]> SELECT * FROM mysql.innodb_table_stats WHERE TABLE_NAME = 'TEST1'; +-----------------+-------------+-----------------------+---------+----------------------+-----------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +-----------------+-------------+-----------------------+---------+----------------------+-----------------------------+ | test | TEST1 | 2018-09-27 01:38:24 | 100204 | 225 | 0 | +-----------------+-------------+-----------------------+---------+----------------------+-----------------------------+ 1 row in set (0.00 sec)
4.4 ANALYZE TABLE 명령어 실행 후 확인
- MariaDB 통합 통계 정보가 수집되는 것을 확인한다.
MariaDB [test]> ANALYZE TABLE TEST1; MariaDB [test]> SELECT * FROM mysql.table_stats WHERE TABLE_NAME = 'TEST1'; +----------+--------------+-------------+ | db_name | table_name | cardinality | +----------+--------------+-------------+ | test | TEST1 | 100000 | +----------+--------------+-------------+ 1 row in set (0.00 sec)
4.5 결론
- innodb_stats_auto_recalc=ON 으로 설정 되면 대량의 데이터 변경이 발생 시 자동으로 통계 정보를 수집한다.
이때 MariaDB 통합 통계 정보와는 무관하게 스토리지 엔진 통계 정보에만 수집이 되는것을 확인 할 수 있다.
MariaDB 통합 통계 정보를 수집 하기 위해서는 ANALYZE TABLE 명령어를 실행해야 수집이 된다.