mysql explain

explain을 사용하면 옵티마이저의 실행계획과 얼마나 많은 데이터를 접근하고 필터했는지 확인할 수 있다.

인덱스 없이 실행

explain select * from study;

실행 결과

• 실행 시간 -> 3초이상
• 중요한 항목 : type , rows, filter
  • type(데이터 스캔 항목) : ALL

인덱스 생성

create index POST__index_member_id on POST (memberId);

create index POST__index_created_date on POST (createDate);

create index POST__index_member_id_created_date 
on POST (memberId,createDate);

※Grouping -> memberId == 5개종류 , createdDate == 19만개 종류

memberId index로 실행

select createDate, memberId, count(id) 
from post
use index (POST__index_member_id)
where memberId = 4 and createDate between '1900-01-01' and '2020-01-01'
group by memberId , createDate;

• 실행시간 -> 20초이상
  • type : ref
  • index를 주지않았을 때 보다 느린 이유
    • 인덱스가 없다면 테이블 전체 스캔으로 3초의 시간만 걸림
    • 인덱스를 효율적으로 사용하지 않는다면 인덱스 테이블과 데이터 테이블을 왕복해 가며 스캔
    • memberId는 엄청 빠르게 찾지만 memberId가 4인 데이터가 많다면 다음 조건을 더 비효율적으로 찾게 됨

createDate index로 실행

select createDate, memberId, count(id) from post
use index (POST__index_created_date)
where memberId = 4 and createDate between '1900-01-01' and '2020-01-01'
group by memberId , createDate;

• 실행시간 -> 10초이상
  • type : index

memberId_index_createDate_index로 실행

select createDate, memberId, count(id) from post
use index (POST__index_member_id_created_date)
where memberId = 4 and createDate between '1900-01-01' and '2020-01-01'
group by memberId , createDate

• 실행시간 -> 10초이상
  • type : range

type

system : 테이블이 하나의 레코드만 가지는 경우
const  : 테이블에 조건을 만족하는 레코드가 하나일 때, 상수 취급
eq_ref : 인덱스가 UNIQUE이거나 PRIMARY KEY인 경우의 조인으로 const를 제외한 조인 중 가장 좋은 형태
ref    : eq_ref와 다른 점은 UNIQUE나 PRIMARY KEY가 아닐 경우 사용한다는 것.
range  : 조건에 레코드의 범위가 주어진 조인.
index  : all 형태와 비슷하며, 인덱스를 사용한다.
all    : 모든 레코드를 스캔한다.

주의사항

옵티마이저가 잘못된 인덱스로 설정을 할 때가 있다.
그렇게되면 1초 걸리는 쿼리가 10초 걸릴 수 도 있으니 꼭 인덱스와 데이터베이스 구조를 확인해야한다!