Overview
동등 비교 조건을 위한 최적화
동등 비교조건에 만족하는 실행계획을 세울때 index range scan을 위한 최적화시 row를 예측하기위해 직접 index를 이용해서 row수를 예측하도록 하고 있는데, 이 방식을 index dive 방식이라고 한다.
하지만 MySQL5.6부터는 index dive방식이 아니라 인덱스통계정보를 바탕으로 실행계획을 세울 수 있도록 하는데 이 방식이 index statistics방식이다.
eq_range_index_dive_limit
MySQL5.6부터 index statistics를 이용하기 위해서 eq_range_index_dive_limit 시스템 변수를 제공하고 있으며, session/global로 동적 변경이 가능하다.
MySQL5.6부터 지원되고 있으며, 버젼에 따라 디폴트값이 10 또는 200이 된다.
eq_range_index_dive_limit=N, 비교 조건에 참가하는 value 개수가 N개를 넘어서면 index statistics방식을 사용하도록 하는 시스템 변수이다.
동등 비교 조건에 참여하는 Value 개수로 최적화 방법 선택
eq_range_index_dive_limit 시스템 변수에 N값은 비교 조건에 참여하는 value 개수로 index dive 또는 index statistics 중 어떤것을 사용할것인지를 결정해주고,
MySQL5.6 이하 버젼은 비교조건을 실행할 인덱스가 존재하는 경우 직접 인덱스를 접근해서 최적화하는 inde dive방식으로 처리하다보니 explain만 실행해도 해당 인덱스를 조회함으로써 비교적 정확한 최적화를 하고 있으나, 빈번한 경우 매번 최적화 작업을 위해 매번 IO가 발생되었다.
MySQL5.6부터는 영구적 통계 정보를 활용하여 index statistics 방식으로 최적화할때 통계 정보를 이용하여 빠르게 실행된다.
영구적 통계 정보 생성 및 최적화를 위한 조건을 알아보자.
영구적 통계 정보 생성(MySQL5.6 Newfeture)
MySQL 5.6부터는 통계정보를 주기적으로 mysql.innodb_table_stats, mysql.innodb_index_stats 테이블에 영구히 저장한다.
보통 ANALYZE TABLE를 하면 통계 정보가 갱신되나, 아래와 같이 시스템 변수를 설정하여 주기적으로 통계 정보를 갱신하게 된다.
1) innodb_stats_persistent = 1 (ON)
2) innodb_stats_auto_recalc = 1 (ON) 3) innodb_stats_persistent_sample_pages = 20 4) innodb_stats_transient_sample_pages = 8 |
동등 비교에 참가될 value 개수로 row 예측 방식이 달라짐.
1) eq_range_index_dive_limit = 0
: disable되어 무조건 index dive로 실행된다.(MySQL5.6이하와 동일하게 실행. MySQL5.6이하는 이러한 변수 없음.) 2) eq_range_index_dive_limit > 0
: enable되며, 디폴트는 10 또는 200이다.(MySQL5.6이상)
(1) col_name IN(val1, ..., valN) < N
: index dive 방식 사용. (2) col_name IN(val1, ..., valN) >= N : index statistics 방식 사용. |
Test
- 테이블 건수 : eq_range_test : 3145728 (300만건 이상.)
- 사용 가능한 인덱스 : 4개
- id1컬럼을 기준으로 테스트 : (id1컬럼에 3,8,9값은 1개씩만 존재)
SQL> select id1, count(*) from eq_range_test group by id1; +------+----------+ | id1 | count(*) | +------+----------+ | 1 | 943716 | | 2 | 943717 | | 3 | 1 | | 4 | 314573 | | 5 | 314573 | | 6 | 314573 | | 7 | 314573 | | 8 | 1 | | 9 | 1 | +------+----------+
- 스트립트
--eq_range_test 테이블 구조 : id1컬럼에 인덱스가 4개 존재.
CREATE TABLE `eq_range_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id1` int(11) DEFAULT NULL,
`id2` int(11) DEFAULT NULL,
`id3` int(11) DEFAULT NULL,
`regdt` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_id1` (`id1`),
KEY `idx_id1_idx2` (`id1`,`id2`),
KEY `idx_idx1_idx2_idx3` (`id1`,`id2`,`id3`)
);
--테이블 통계
SQL> select * from mysql.innodb_table_stats where table_name='eq_range_test';
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
| test | eq_range_test | 2016-02-16 14:02:25 | 3137625 | 8425 | 16161 |
+---------------+---------------+---------------------+---------+----------------------+--------------------------+
--인덱스 통계
SQL> select * from mysql.innodb_index_stats where table_name='eq_range_test';
+---------------+---------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+---------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | eq_range_test | PRIMARY | 2016-02-16 14:02:25 | n_diff_pfx01 | 3137625 | 20 | id |
| test | eq_range_test | PRIMARY | 2016-02-16 14:02:25 | n_leaf_pages | 8367 | NULL | Number of leaf pages in the index |
| test | eq_range_test | PRIMARY | 2016-02-16 14:02:25 | size | 8425 | NULL | Number of pages in the index |
| test | eq_range_test | idx_id1 | 2016-02-16 14:02:25 | n_diff_pfx01 | 2 | 8 | id1 | <--stat_value가 2종류라고 알고있음.
| test | eq_range_test | idx_id1 | 2016-02-16 14:02:25 | n_diff_pfx02 | 3339020 | 20 | id1,id |
| test | eq_range_test | idx_id1 | 2016-02-16 14:02:25 | n_leaf_pages | 3650 | NULL | Number of leaf pages in the index |
| test | eq_range_test | idx_id1 | 2016-02-16 14:02:25 | size | 4231 | NULL | Number of pages in the index |
| test | eq_range_test | idx_id1_idx2 | 2016-02-16 14:02:25 | n_diff_pfx01 | 7 | 8 | id1 |
| test | eq_range_test | idx_id1_idx2 | 2016-02-16 14:02:25 | n_diff_pfx02 | 17 | 20 | id1,id2 |
| test | eq_range_test | idx_id1_idx2 | 2016-02-16 14:02:25 | n_diff_pfx03 | 3127706 | 20 | id1,id2,id |
| test | eq_range_test | idx_id1_idx2 | 2016-02-16 14:02:25 | n_leaf_pages | 4685 | NULL | Number of leaf pages in the index |
| test | eq_range_test | idx_id1_idx2 | 2016-02-16 14:02:25 | size | 5386 | NULL | Number of pages in the index |
| test | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx01 | 1 | 8 | id1 |
| test | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx02 | 3 | 20 | id1,id2 |
| test | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx03 | 3 | 20 | id1,id2,id3 |
| test | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_diff_pfx04 | 3069035 | 20 | id1,id2,id3,id |
| test | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | n_leaf_pages | 5695 | NULL | Number of leaf pages in the index |
| test | eq_range_test | idx_idx1_idx2_idx3 | 2016-02-16 14:02:25 | size | 6544 | NULL | Number of pages in the index |
+---------------+---------------+--------------------+---------------------+--------------+------------+-------------+-----------------------------------+
SQL> select table_name, index_name, column_name, cardinality from INFORMATION_SCHEMA.STATISTICS where table_name ='eq_range_test'\G
*************************** 1. row ***************************
table_name: eq_range_test
index_name: PRIMARY
column_name: id
cardinality: 3137625
*************************** 2. row ***************************
table_name: eq_range_test
index_name: idx_id1
column_name: id1
cardinality: 2 <------- 역시나 cardinality가 2라고 알고 있음.
*************************** 3. row ***************************
table_name: eq_range_test
index_name: idx_id1_idx2
column_name: id1
cardinality: 18
*************************** 4. row ***************************
table_name: eq_range_test
index_name: idx_id1_idx2
column_name: id2
cardinality: 58
*************************** 5. row ***************************
table_name: eq_range_test
index_name: idx_idx1_idx2_idx3
column_name: id1
cardinality: 18
*************************** 6. row ***************************
table_name: eq_range_test
index_name: idx_idx1_idx2_idx3
column_name: id2
cardinality: 58
*************************** 7. row ***************************
table_name: eq_range_test
index_name: idx_idx1_idx2_idx3
column_name: id3
cardinality: 58
SQL> show table status like 'eq_range_test'\G
*************************** 1. row ***************************
Name: eq_range_test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3137625
Avg_row_length: 43
Data_length: 138035200
Max_data_length: 0
Index_length: 264781824
Data_free: 144703488
Auto_increment: 3211216
Create_time: 2016-02-15 15:49:27
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
|
eq_range_index_dive_limit=3 일때, 값의 분포도에 따른 플랜 확인
: Test1에 분포도가 좋은 경우, index range스캔시 실제 데이터를 바탕으로 rows컬럼에 표시되고 플랜 또한 정확하다.
: Test2는 분포도가 좋은 경우라도, 통계를 기반으로 플랜을 작성되었고, rows컬럼은 예측된 row 를 기준으로 보여준다.
SQL> set session eq_range_index_dive_limit=3; |
TEST1 : N < 3
--분포도가 나쁜 경우(index dive : full scan) SQL> explain select * from eq_range_test where id1 in (1,2); +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | eq_range_test | ALL | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL | NULL | 3137625 | Using where | <--예측된 Row수 +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ --분포도가 좋은 경우,(index dive : idx_id1 index scan) SQL> explain select * from eq_range_test where id1 in (3,9); +----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+ | 1 | SIMPLE | eq_range_test | range | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | idx_id1 | 5 | NULL | 2 | Using index condition | <---실제 읽은 row수 +----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+ |
TEST2 : N>= 3
--분포도가 나쁜 경우, (index statistics 사용 : full scan) SQL> explain select * from eq_range_test where id1 in (1,2,4,5); +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | eq_range_test | ALL | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL | NULL | 3137625 | Using where | <--예측된 Row수 +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ --분포도가 좋은 경우, (index statistics 사용 : full scan) SQL> explain select * from eq_range_test where id1 in (3,8,9,0); +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | eq_range_test | ALL | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL | NULL | 3137625 | Using where | <--예측된 Row수 +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ |
쿼리 실행시 읽은 row수 확인
Test1은 rows에 표시된 2 row는 실제 쿼리 실행시에도 2row를 읽었음을 알 수 있다.
Test2는 rows컬럼에 예측 row수로 표시되었고, 실제 실행시 full scan한 Row수 만큼 읽었다.
Test1 : VALUE값이 2개
-- VALUE값이 2개 SQL> explain select * from eq_range_test where id1 in (3,9); +----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+ | 1 | SIMPLE | eq_range_test | range | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | idx_id1 | 5 | NULL | 2 | Using index condition | <---실제 읽은 row수 +----+-------------+---------------+-------+-----------------------------------------+---------+---------+------+------+-----------------------+ SQL> select * from eq_range_test where id1 in (3,9); +-------+------+------+------+---------------------+ | id | id1 | id2 | id3 | regdt | +-------+------+------+------+---------------------+ | 33333 | 3 | 3 | 3 | 2015-09-15 11:22:48 | | 99999 | 9 | 3 | 3 | 2015-09-15 11:22:48 | +-------+------+------+------+---------------------+ 2 rows in set (0.00 sec) [db-rand-db1][test]> show session status like 'hand%' ; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | <---- 실제 2건 읽음. | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 2 | | Handler_read_last | 0 | | Handler_read_next | 2 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ |
Test2 : VALUE값이 4개
-- VALUE값이 4개 SQL> explain select * from eq_range_test where id1 in (3,8,9,0); +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | eq_range_test | ALL | idx_id1,idx_id1_idx2,idx_idx1_idx2_idx3 | NULL | NULL | NULL | 3137625 | Using where | <--예측된 Row수 +----+-------------+---------------+------+-----------------------------------------+------+---------+------+---------+-------------+ SQL> select * from eq_range_test where id1 in (3,8,9,0); +--------+------+------+------+---------------------+ | id | id1 | id2 | id3 | regdt | +--------+------+------+------+---------------------+ | 33333 | 3 | 3 | 3 | 2015-09-15 11:22:48 | | 99999 | 9 | 3 | 3 | 2015-09-15 11:22:48 | | 888888 | 8 | 3 | 3 | 2015-09-15 11:22:55 | +--------+------+------+------+---------------------+ 3 rows in set (2.02 sec) SQL> show session status like 'hand%' ; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 3145729 | <--- 실제 3145729 읽음. | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+---------+ |
value 개수에 상관없이 인덱스 힌트를 준 경우
use index절 : value 개수가 N를 넘어가면 통계 정보를 이용해서 해당 인덱스를 사용될건지 판단되어 use index절이 무시될 수 있다.
force index절 : value 개수와 상관없이 force index절 인덱스를 강제로 사용한다.
use index절 사용
SQL> explain select * from eq_range_test use index(idx_id1) where id1 in (3,8,9,0); +----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | eq_range_test | ALL | idx_id1 | NULL | NULL | NULL | 3137625 | Using where | <---예측된 Row수 +----+-------------+---------------+------+---------------+------+---------+------+---------+-------------+ SQL> select * from eq_range_test use index(idx_id1) where id1 in (3,8,9,0); +--------+------+------+------+---------------------+ | id | id1 | id2 | id3 | regdt | +--------+------+------+------+---------------------+ | 33333 | 3 | 3 | 3 | 2015-09-15 11:22:48 | | 99999 | 9 | 3 | 3 | 2015-09-15 11:22:48 | | 888888 | 8 | 3 | 3 | 2015-09-15 11:22:55 | +--------+------+------+------+---------------------+ 3 rows in set (2.02 sec) <----2초. SQL> show session status like 'hand%' ; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 3145729 | <-----실제 3145729건 읽음. | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+---------+ |
force index절 사용
SQL> explain select * from eq_range_test force index(idx_id1) where id1 in (3,8,9,0); +----+-------------+---------------+-------+---------------+---------+---------+------+---------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+-------+---------------+---------+---------+------+---------+-----------------------+ | 1 | SIMPLE | eq_range_test | range | idx_id1 | idx_id1 | 5 | NULL | 6275248 | Using index condition | <---예측된 Row수 +----+-------------+---------------+-------+---------------+---------+---------+------+---------+-----------------------+ SQL> select * from eq_range_test use index(idx_id1) where id1 in (3,8,9,0); +--------+------+------+------+---------------------+ | id | id1 | id2 | id3 | regdt | +--------+------+------+------+---------------------+ | 33333 | 3 | 3 | 3 | 2015-09-15 11:22:48 | | 888888 | 8 | 3 | 3 | 2015-09-15 11:22:55 | | 99999 | 9 | 3 | 3 | 2015-09-15 11:22:48 | +--------+------+------+------+---------------------+ 3 rows in set (0.00 sec) <---- 0초. SQL> show session status like 'hand%' ; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 4 | <--- 실제 4건 읽음. | Handler_read_last | 0 | | Handler_read_next | 3 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ |
Conclusion
MySQL5.6부터 영구적 통계 정보를 적극 활용하기 위해 eq_range_index_dive_limit 변수를 활용하도록 하였으나, 정확하지 않는 통계정보로 인하여 최적화(실행계획) 시킬때 시간 및 IO량은 줄 수는 있으나 잘못된 최적화로 인하여 빈도가 높고, 대량의 테이블을 이용한 쿼리인 경우 성능 저하를 일으킬 수 있다.
MySQL5.7.4부터 디폴트 값이 10개에서 200개로 value 개수가 늘어나는 것은 기존 방식인 index dive방식을 기존처럼 사용하도록 변경된게 아닌가 추측된다.
MySQL의 통계정보에는 아직까지 column value에 대한 histogram이 없고, row수 및 cardinality정도만 제공하고 있으므로 통계 정보를 믿고 최적화 하는건 아직 조심스럽다고 볼 수 있겠다.
작성자가 댓글을 삭제했습니다.
답글삭제