master 와 slave 의 쿼리실행 계획이 틀려진 이유는 ?

use mysql;
show tables like '%stats%';
--------------------------------
[통합 통계 정보 테이블]
column_stats
index_stats
table_stats
 
 
[스토리지 엔진 통계 정보 테이블]
innodb_index_stats
innodb_table_stats

show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+

1. 각 스토리지 엔진이 제공하는 통계정보는 내용이 상당히 부족함
2. MariaDB 스토리지 엔진 인터페이스로 통계정보를 전달하기에는 제약이 많음 ( 인덱스가 아닌 컬럼의 통계 정보 수집불가)
3. 통계 정보의 제어가 쉽지 않아서 통계정보가 영구적이지 못하고 단기적으로 관리됨.

show variables like 'use_stat_tables';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| use_stat_tables | NEVER |
+-----------------+-------+
 
 
1. NEVER  (Default)
 - analyze table 명령어를 입력해도 [통합 통계 정보 테이블] 에 데이터가 수집되지 않는다.
 - 옵티마이저가 실행계획을 수립할때에도 [스토리지 엔진 통계 정보 테이블] 만을 활용하여 실행계획을 수립한다.
2. COMPLEMENTARY
 - analyze table 명령어를 입력할때에 [통합 통계 정보 테이블] 에 데이터가 수집된다.
 - 옵티마이저가 실행계획을 수립할때 [스토리지 엔진 통계 정보 테이블] 통계정보를 우선적으로 사용하되 정보가 부족하거나 없는경우에는 [통합 통계 정보 테이블] 의 정보가 사용된다.
 - MyISAM 이나 MEMORY 스토리지 엔진의 경우에는 통계 정보 테이블이 존재 하지않으므로 이러한 단점을 보완하기 위해 [통합 통계 정보 테이블] 의 정보가 사용된다.
3. PREFERABLY
 - analyze table 명령어를 입력할때에 [통합 통계 정보 테이블] 에 데이터가 수집된다.
 - 옵티마이저가 실행계획을 수립할때 우선적으로 [통합 통계 정보 테이블] 을 활용한다. 만약 [통합 통계 정보 테이블] 이 존재하지 않는 경우에는 [스토리지 엔진 통계 정보 테이블] 을 활용한다.
 
※ 주의 2,3 번인 상태에서 analyze table 명령어를 수행할때 full table scan 이 일어난다.

show global variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON    |
+--------------------------+-------+

show variables like 'innodb_stats_transient_sample_pages';                                             
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| innodb_stats_transient_sample_pages | 8     |
+-------------------------------------+-------+
자동으로 통계 정보 수집이 실행될때 8개의 페이지만 임의로 샘플링해서 분석하고 그 결과를 통계정보로 활용함.
 
 
show variables like 'innodb_stats_persistent_sample_pages';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_stats_persistent_sample_pages | 20    |
+--------------------------------------+-------+
analyze table 통계 정보 수집이 실행될때 20개의 페이지만 임의로 샘플링해서 분석하고 그 결과를 통계정보로 활용함.

CREATE TABLE `test` (
 `field1` varchar(200)
) ENGINE=InnoDB STATS_AUTO_RECALC = 0, STATS_SAMPLE_PAGES = 2000; 
  
ALTER TABLE `test` STATS_AUTO_RECALC = 0, STATS_SAMPLE_PAGES = 2000;
#(샘플페이지 갯수는 많을수록 정확도가 높아짐)

MariaDB 의 기본설정은 테이블 통계정보를 수집하고 있으며 (Cost 측정을위함) 대량으로 테이블이 변경되었을때에는 테이블 상태값을 업데이트하는데
이때 매우 적은수의 페이지를 가지고 테이블 상태값을 업데이트한다. 따라서 매우 부정확한 정보로 갱신이 되었을때는 옵티마이저가 실행계획을 잘못 수립할수 있다.
 
 
이걸 방지하기 위해서는 테이블단위로는 위의 예제와 같이 만들어주고 ALTER 나 CREATE DDL 을 통해 STATS_AUTO_RECALC = 0 설정을 맞추어주어야한다.  
최초로 테이블을 생성할때에는  `innodb_stats_persistent` 시스템변수를 참고하여 테이블을 생성한다고한다. 
MariaDB > SHOW VARIABLES LIKE 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON    |
+-------------------------+-------+
 
 
하지만 해당 옵션을 꺼버리는 순간 analyze table 옵션으로도 [스토리지 엔진 통계 정보 테이블] 갱신되지 않으므로 설정을 ON 으로 두고
자동 갱신을 사용하지 않을 테이블에 한해서 STATS_AUTO_RECALC  을 변경해서 사용해야 한다.
 
 
 
 
※ 즉 테이블별로 STATS_AUTO_RECALC  에 대한 설정을 0 으로 변경하고 STATS_SAMPLE_PAGES 의 수를 적절하게 조절하여 시스템 점검이나 시스템이 한가한 시간에 주기적으로 상태를 업데이트해주는것이 가장 좋은방법이다.