MYSQL/성능개선을 위한 인덱스 정의시 알아두면 유용한 정보(쿼리 실행 계획)

흔히 쿼리의 검색결과를 최적화 할때 새로운 인덱스를 정의하거나 명시적으로 인덱스를 정의하곤 한다. 인덱스에 대해서 쓰는 글은 아니므로 자세히 어떻게 정령되는지에 대한 정보는 나중에 따로 포스팅을 하겠습니다.

인덱스 정의 프로세스

문제가 되는 쿼리의 실행계획을 먼저 DB에 질의를 던저봐야합니다.

EXPLAIN SELECT ~~~

EXPLAIN 을 사용해서 mysql 옵티마이져가 쿼리를 어떻게 연산할것인지에 대한 정의가 테이블로 출력시킬 수 있는데 해당 결과를 보고 인덱스가 잘 먹히고는 있는지 검색되는 rows는 얼마나 많은지 비 효율적인 연산이 일어나지는 않는지 먼저 체크를 해봐야합니다.
EX)

[
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "test",
    "partitions": null,
    "type": "ALL",
    "possible_keys": "test_id",
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 411143,
    "filtered": 0.14,
    "Extra": "Using where; Using temporary; Using filesort"
  },
]

id: 검색순서
select_type: "SIMPLE"로 표시되며, 이것은 단순한 SELECT 쿼리임을 나타냅니다.
table: "test" 테이블에서 데이터를 가져옵니다.
possible_keys: 사용가능한 인덱스
type: "ALL" 전체 검색, index: 인덱스를 사용하여 검색
key: 사용하고잇는 인덱스명
ref: 조인조건에 사용된 테이블 간의 연결정보
rows: 해당 테이블에서 검생된 행의 수
filtered: 결과 집합이 필터링 되어 선택된 행의 비율
Extra: "Using where; Using index; Using temporary; Using filesort"는 추가 정보를 나타냅니다. "Using where"는 WHERE 절을 사용하여 행을 필터링한다는 것을 의미하며, "Using index"는 인덱스를 사용하고, "Using temporary; Using filesort"는 정렬을 위해 임시 테이블 및 파일 정렬을 사용한다는 것을 나타냅니다.

 

위 검색쿼리의 실행계획을 보면 인덱스를 사용하지도않고 그렇기때문데 ALL로 테이블에 정의된 모든 행을 검색하는 비효율적인 연산이 일어나는것을 확인할 수 있습니다. 이러한 실행계획을 보고 인덱스를 작성해야 효율적인 검색효율 개선을 기대할 수 있습니다.

 

위 메타 정보를보고 실행계획을 분석한뒤 적절한 인덱스를 정의하여 검색성능을 계선시킬 수 있습니다.

인덱스를 정의할때는 복합 인덱스를 정의할시 컬럼의 나열된 순서도 굉장히 중요한 영향을 끼칩니다.
일단 기본적으로 권장되는 복합 인덱스의 정의 순서는 카디널리티가 높은순에서 낮은순으로 나열돼야 합니다. 생각해보면 청소를 할때 아무렇게 정리를 해도 상관은 없지만서도 무언가를 정리할때 지저분한것부터 정리를하고 비교적 정리가 돼 있는 부분은 나중에 정리를해야 효율적으로 정리정돈이 될 태니까요