MySQL에서 primary 인덱스와 보조 인덱스를 생성 후 count(*)을 할 경우 옵티마이저는 어떻게 처리할까?

== 환경정보

Aurora MySQL 3.04.0

db.t3.medium , 2vCPU , 4GB RAM

== 테이블 생성

* primary 인덱스와 다양한 보조 인덱스를 생성

* 의도적으로 primary 인덱스에서 많은 데이터를 갖게 하기 위해 char(255)컬럼을 4개 추가

* col3는 의도적으로 null 허용 컬럼으로 생성

drop table t1;
create table t1 (
    col1 int not null
    , col2 int not null
    , col3 int null
    , col4 int not null
    , col5 int not null
    , col6 int not null
    , col7 int not null
    , col8 int not null
    , col9 int not null
    , col10 int not null
    , col11 char(255) not null
    , col12 char(255) not null
    , col13 char(255) not null
    , col14 char(255) not null
    );

alter table t1 add primary key (col1);
create index idx_t1_col2 on t1 (col2);
create index idx_t1_col3_2 on t1 (col3,col4);
create index idx_t1_col5_3 on t1 (col5,col6,col7);
create index idx_t1_col5_4 on t1 (col5,col6,col7,col8);

== 테스트 데이터 입력

* 데이터를 반복적으로 insert해서 약 180만건 데이터 생성

* 데이터 사이즈 약 2GB, 인덱스 사이즈 약 140MB 확인

insert into t1 select 1,1,1,1,1,1,1,1,1,1,1,1,1,1;

insert into t1
select col1+x, col2+x, col3+x, col4+x, col5+x, col6+x, col7+x, col8+x, col9+x, col10+x, col11, col12, col13, col14
from t1
	cross join (select col1 as x from t1 order by col1 desc limit 1) t2
limit 200000;

select count(*) from t1;
-- 1831072

SELECT table_name AS 'TableName',
                 ROUND(SUM(data_length+index_length)/(1024*1024), 2) AS 'All(MB)',
                 ROUND(data_length/(1024*1024), 2) AS 'Data(MB)',
                 ROUND(index_length/(1024*1024), 2) AS 'Index(MB)'
FROM information_schema.tables
GROUP BY table_name
ORDER BY data_length DESC; 
--	t1	2175.34	2035.00	140.34

== 실행계획 확인

* where조건 없이 count(*) 하는 경우 실행계획을 확인해 보면 가장 사이즈가 작은 인덱스인 idx_t1_col2 인덱스를 사용해서 처리하는것을 확인할 수 있다.

* 인덱스 힌트를 통해 다른 인덱스를 지정할 경우 해당 인덱스를 통해 처리하도록 실행계획을 세우는걸 확인할 수 있다.

explain
select count(*) from t1;
--	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
--	1	SIMPLE	t1		index		idx_t1_col2	4		1701204	100.00	Using index

explain
select count(*) from t1 force index(`primary`);
--	id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
--	1	SIMPLE	t1		index		PRIMARY	4		1701204	100.00	Using index

== 실제 실행시간 측정

* where절을 통해 실제 데이터를 탐색하게 만들지 않는 이상 테이블 스캔을 통해 처리하는 것으로 보임
* 스키마에서 not null 컬럼인 경우 where col2 is not null 하면 조건이 무시되어 테이블 스캔으로 처리하고, 스키마에서 null 컬럼인 경우는 where col3 is not null 하면 실제 데이터를 체크하는 것으로 보임
* 실제 데이터를 확인해야 하는 where col2 > 0 과 같은 조건을 주면 적절한 보조 인덱스를 사용하는 것으로 보임
* count(col3)와 같이 컬럼을 명시하면 null 체크를 해야하기 때문에 where절을 넣은것과 동일하게 동작 함

select count(*) from t1;
-- 19.812 sec
select count(*) from t1 force index(`primary`);
-- 19.735 sec
select count(*) from t1 force index(`idx_t1_col2`);
-- 19.172 sec
select count(*) from t1 force index(`idx_t1_col5_4`);
-- 19.703 sec
select count(*) from t1 where col1 is not null;
-- 19.063 sec
select count(*) from t1 where col2 is not null;
-- 19.156 sec 
select count(*) from t1 where col3 is not null;
-- 0.515 sec
select count(*) from t1 where col1 > 0;
-- 19.359 sec
select count(*) from t1 where col2 > 0;
-- 0.562 sec
select count(*) from t1 where col5 > 0;
-- 1.078 sec
select count(col2) from t1;
-- 19.226 sec 
select count(col3) from t1;
-- 0.532 sec

== 데이터 확인

* DB를 재시작 후 버퍼풀을 확인

* 위의 테스트에서 확인했던것과 같이 where절 조건을 주지 않는 경우 버퍼풀에 primary 인덱스 페이지만 올라와 있는것을 확인할 수 있음. 인덱스 힌트를 줘도 동일 함.

* where col2 > 0 와 같은 조건을 주고 실행하는 경우 primary 인덱스 페이지는 로드하지 않고 idx_t1_col2 인덱스 페이지만 로드 되는것을 확인할 수 있음

select table_name, index_name, count(*)
from information_schema.INNODB_BUFFER_PAGE 
where table_name like '%t1%'
group by table_name, index_name
order by table_name, index_name;

== 결론

1. MySQL에서 where절 없이 count(*)을 하는 경우 무조건 primay 인덱스를 사용한다

2. 적절한 where절 조건을 통해 데이터를 탐색할 수 있도록 해주면 보조 인덱스를 이용한다

3. 실행계획에 표시된 내용과 실제 수행은 다를 수 있다

하만철 / Ha Mancheol