Query Optimize를 제대로 못하는 mysql 길들이는 7가지 방법

mysql index관련 검색을 하다 재미있는 내용의 글을 발견 정리하려고 올립니다.

mysql의 경우에도 버젼업이 많이 되고 있어, 최신 버젼의 optimizer에 대해서 아는바가 없지만, 현재 4.x대를 사용 중이라 optimizer의 문제를 너무 많이 느끼고 있어서 공감했던 글입니다.

7 ways to convince MySQL to use the right index

위 글의 내용을 정리하면 대략 다음과 같습니다.

CREATE TABLE t (
  id INT UNSIGNED AUTO_INCREMENT,
  type INT UNSIGNED,
  level TINYINT unsigned,
  ...
  PRIMARY KEY(id),
  KEY `type` (type)
) ENGINE=InnoDB;

위와 같은 테이블이 있고, 해당 테이블에 대해 아래와 같은 쿼리를 사용하려고 합니다.

SELECT id FROM data
WHERE type=12345 AND level > 3
ORDER BY id

정확한 이해를 위해 보다 자세한 내용을 소개하자면

• t라는 테이블에는 약 10,000,000개의 row가 있다.

• 'type'이라는 index의 선택도는 대략 하나의 value에 대해 평균 100row정도 (예를 들어 type 'x'를 검색하면 결과가 평균 100개정도 나온다는 의미)

• 쿼리 수행 시간이 오래 걸린다.

• EXPLAIN 결과에서 보면 key는 primary key를 이용한다고 하고 rows는 10,000,000, extra 정보에는 using where라고 나온다.

• 'type' index를 이용한 EXPLAIN 결과에서는 rows는 110rows, extra정보에는 "using where;using filesort"라고 나온다.

실제 실행 시간은 mysql이 제시한 방법 보다는 'type' index를 이용한 쿼리가 나은 결과를 보이는데, 그럼 쿼리를 어떻게 고치면 'type' index를 사용할 수 있게 할 수 있는지에 대해 7가지 방법에 대해 소개를 하고 있습니다.

몰랐던 방법들도 있네요.

내용이 어렵진 않으니 원문을 참고하세요~