MySQL 쿼리 튜닝 은 어떻게 할까? 기초편 (INDEX)

이번엔 MySQL 쿼리 튜닝에 대해 알아보자.

 

쿼리 튜닝이란 무엇일까?

쿼리 성능을 일정 수준 이상 끌어올리는 일을 말한다.

 

튜닝을 하기전에 인덱스라는 개념에 대해서 알아야 쉽게 정리가 된다

그렇다면 인덱스(Index)란 무엇일까?

 

 

인덱스(index)란?
인덱스(index)는 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 사용합니다.
이러한 인덱스는 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각할 수 있습니다.

MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색합니다.
따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 됩니다.

하지만 인덱스를 사용하면 테이블 전체를 읽지 않아도 되므로, 검색과 질의에 대한 처리가 빠르게 이루어집니다.
이러한 인덱스는 사용자가 직접 접근할 수는 없으며, 검색과 질의에 대한 처리에서만 사용됩니다.

이렇게 인덱스가 설정된 필드 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 합니다.
따라서 인덱스가 설정된 테이블의 처리 속도가 느려질 수 있습니다.
그러므로 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋습니다.

References : http://tcpschool.com/mysql/mysql_index_create

 

 

간단하게 말해, 데이터를 빠르고 쉽게 검색하기 위한, 일종의 책갈피 같은 샘입니다.

 

그렇다면 쿼리튜닝을 하는데, 인덱스에 대해 왜 알아야 할까?

 

보통 쿼리 튜닝 시에 인덱스 여부를 먼저 확인합니다.

• 인덱스가 제대로 설정이 되어있는지를 확인하고,
• 그렇지 않다면 인덱스를 추가하거나, 혹은 불필요한 곳에 들어갔을 경우 인덱스를 제거합니다.

그렇다면 인덱스 시 고려해야 할 점은 무엇일까요?

 

 

 

인덱스 시 고려할 점

    1.  중복 인덱싱 피하기 : 키로 설정된 곳에 인덱싱 추가하지 않기

    2. 인덱스 최소화 : 인덱스 역시 사본 형태로 저장된 데이터라 하드웨어 자원을 소모. 성능 저하 초래

    3. WHERE, ORDER BY, GROUP BY 절에는 인덱스 지정 : 서버 결과를 fetch 하는데 더 빠르게 하는 것을 도와줌 (정렬에도 도움)

    4. 인덱스 중복 데이터 확인 : 카운트 비율을 활용해, Index uniqueness 비율을 확인. 평균 90% 이상으로 유지.

SELECT (COUNT(DISTINCT indexed_column)/COUNT(*)) * 100 FROM some_table;

    5. 문자열은 CRC32로 인덱싱 : 회원 아이디, 회원 이메일 등은 CRC32 칼럼으로 SELECT.

SELECT * FROM tables
WHERE email_crc = crc32('seongman@seongman.com') AND email = 'seongman@seongman.com';

     CRC32(자세한 설명은 WIKI)란 간단하게 말해, Cyclic Redundancy Check (CRC) 방식 중의 하나이다.

 

 

인덱스 확인방법 (다음 포스트에 자세히)

인덱스를 활용한 검색이 잘되는지를 매번 코드를 보며 확인하기란 어렵다.

그래서 MySQL 에는 "EXPLAIN" 절이 있다. "EXPLAIN"은 쿼리가 실행 시 해당 쿼리를 분석해준다.

mysql> EXPLAIN SELECT * FROM user WHERE user_idx = 100;

해당 분석 내용 중 "key" coulmn과 "possible_keys"라는 column에 보면 해당 키로 인덱스가 되어 fetch 되어 있는지 알 수 있습니다.

 

위와 같이 간단한 쿼리의 경우 눈에 띄는 데이터는 보이지 않습니다.

다양한 방법이 많지만, EXPLAIN의 결괏값 분석만으로도 충분히 query 튜닝을 할 수 있습니다.

 

 

다음번에는 EXPLAIN을 활용한 심층 쿼리 분석 및 테이블 정의에 대해 써보고자 한다