Mysql 인덱스 사용법 및 실행 계획 정리

mysql 인덱스에 대한 정확한 이해도 없이 사용을 하다보니 조금 개념적으로 헷갈리는게 많이 있었다. 이 부분에 대해 한번 정리하고 넘어가고자 기록해본다.

 

인덱스

---

인덱스는 빠르게 특별한 컬럼과 함께 값을 찾는데 사용된다. 인덱스가 없으면 Mysql은 처음 행부터 전체 테이블을 읽어 들여서 데이터를 찾는다. 거대한 테이블에서 이런 행동은 비용이 상당히 많이 들어가게 된다. 만약에 테이블이 인덱스를 가지고 있으면 빠르게 접근할 수 있게 된다.

대부분의 Mysql 인덱스 (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT)는 B-tree안에 저장된다. 예외적으로 spatial 데이터 타입은 R-tree를 사용, 메모리 테이블은 또한 hash index를 지원, InnoDB는 FULLTEXT 인덱스를 위해 inverted list를 사용한다.

 

 

인덱스 동작 방식

---

- 행을 찾기 위해서 매칭되는 WHERE 구문을 빠르게 찾는다.

 

- 조건으로 부터 불필요 행을 제거한다. 만약에 여러 인덱스가 있는 경우 Mysql은 가장 적은 수의 행을 사용하는 인덱스를 선택한다. (Mysql은 한번에 하나의 인덱스만 사용할 수 있다.)

 

- 만약 테이블이 multiple column 인덱스를 가지고 있으면 인덱스의 가장 왼쪽에 컬럼을 사용하여 옵티마이저를 통해 행을 찾는다. 예를 들어 만약에 (col1, col2, col3)을 사용하는 인덱스가 있는 경우 인덱스는 이 순서로 검색을 진행한다. (col1), (col1, col2), (col1, col2, col3)

 

- 조인이 있는 경우 다른 테이블에서 행을 찾는다. Mysql은 동일한 유형과 사이즈로 되어 있는 열을 index로 사용할 때 더욱 효과적으로 행을 찾는다. VARCHAR, CHAR는 두개를 같은 사이즈로 명시 하였을 경우에 같은 타입으로 고려되어 사용된다. 예를 들어 VARCHAR(10) = CHAR(10)이지만 VARCHAR(10) ≠ CHAR(15)는 같지 않다.

 

- binary가 아닌 문자열 사이를 비교하기 위해서는 동일한 문자열 집합을 사용해야한다. utf8과 latin1열을 비교할 경우 인덱스를 사용할 수 없다.

 

- 타입이 다른 문자열과 숫자 등을 비교하려고 할때도 마찬가지로 인덱스를 탈 수 없다. (묵시적 형변환)

 

- index로 사용되는 key_col에서 min(), max()의 값을 찾기 위해 인덱스에서 key_col 이전에 발생하는 모든 키 파트에서 WHERE key_part_n = const를 사용하는지 여부를 확인하기 위해서 전처리기를 통해서 최적화가 진행된다. 이 경우에서 Mysql은 각 min() 또는 max() 표현식에 대해 단일키 조회를 수행하여 상수를 대체한다. 모든 표현식이 상수로 바뀌고 나면 쿼리가 한번에 반환된다.

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

 

 

실행계획

---

- 실행계획 필드 정리

• Id
  • Select 구문 구분 ID
• select_type
  • SIMPLE : 단순 SELECT
  • DERIVED : 서브 쿼리 중 가장 안쪽에 있는 쿼리 
  • PRIMARY : 서브 쿼리 바깥쪽에 있는 쿼리
  • DEPENDENT SUBQUERY  : 조건절 내부에서 외부 쿼리와 연결된 SELECT 

// PRIMARY
SELECT * FROM ( 
    // DERIVED : 서브 쿼리 중 가장 안쪽에 있는 쿼리
    SELECT * FROM timeline_item ti
) tt;


// DEPENDENT SUBQUERY
SELECT * FROM timeline_item t1 WHERE EXISTS ( SELECT * FROM timeline_site t2 WHERE t1.id = t2.id)

• table

  • 참조하는 테이블 이름

• type

  • 조인 혹은 조회 타입 (아래로 갈수록 성능 하락)
    1. System : 테이블에 데이터가 하나만 있는 경우
    2. const : SELECT에서 Primary Key 혹은 Unique Key를 상수로 조회하는 경우
    3. eq_ref : 조인할 때 Primary, Unique Key로 매칭하는 경우
    4. ref : 조인할 때 Primary, Unique Key로 매칭하지 않은 경우
    5. ref_or_null : ref와 같지만 NULL이 추가되어 검색된 경우
    6. index_merge : 두개의 인덱스가 병합되어 검색이 된경우
    7. unique_subquery : In절 내부 서브쿼리에서 Primary Key가 있는 경우
    8. index_subquery : In절 내부 서브쿼리에서 Primary Key가 아닌 인덱스가 있는 경우
    9. range : 특정 범위 내에서 인덱스를 사용하여 데이터 추출 하는 경우
    10. index : 인덱스를 처음부터 끝까지 찾아서 검색하는 경우로 일반적인 인덱스 풀스캔
    11. all : 테이블 풀스캔

• possible_keys

  • 데이터 조회 시 DB에서 사용할 수 있는 인덱스 리스트

• key

  • 실제로 사용할 인덱스

• key_len

  • 실제로 사용할 인덱스 길이

• ref

  • key 안의 인덱스와 비교하는 컬럼(상수)

• rows

  • 쿼리 실행 시 조사하는 행수

• extra

  • 추가 정보 (데이터가 많고 Using filesort, Using temporary 상태가 나온다면 무조건 최적화 필요)

    1. Using Index

       커버링 인덱스라고 하며 인덱스 자료구조를 이용하여 데이터를 추출

    2. Using where

       where 조건으로 데이터를 추출 (Type이 All 또는 Index와 같이 표현될 시 성능이 안좋다는 뜻)

    3. Using filesort

       데이터 정렬이 필요한 경우로, 메모리 혹은 디스크 상에서의 정렬을 모두 포함 (데이터 많을 시 성능 하락)

    4. Using Temporary

       쿼리 처리 시 내부적으로 Temporary 테이블이 사용됨

 

 

WHERE 조건문 주의사항

---

• 묵시적 형변환에 조심하라
• 무턱되고 함수를 사용하면 옵티마이저가 데이터 분포도 체크를 하지 못하기에 사용하지 말 것

// 함수 사용
SELECT * FROM timeline_item ti WHERE DATE_FORMAT(modified_at, '%Y%m%d') <= '20200402'

// 대안방안
SELECT * FROM timeline_item ti WHERE modified_at <= '2020-04-02'

• Like 검색은 % 위치에 따라 다르게 수행 된다. %123, %125%의 경우에는 데이터 풀 스캔이 발생된다. 하지만 12312%와 같은 경우에는 인덱스를 사용해서 진행됨. 하지만 1%등과 같이 데이터 분포도에 비해 너무 추상적으로 %를 사용하게 될 경우에는 옵티마이저가 인덱스 사용과 데이터 풀스캔의 효율성 판단 했을 때 풀스캔이 효율적이다고 생각하고 데이터 풀스캔이 실행된다.