[mysql] 인덱스 정리

인덱스란

  • 책의 목차와 같이 테이블 컬럼에 대한 인덱스를 생성해서 데이터를 검색속도를 향상 시키는것
  • 인덱스를 타면 풀스캔을 피할 수 있어서 데이터 검색 속도가 빠르다.

풀스캔

  • 인덱스가 없는 테이블에서 데이터를 찾을 때 처음부터 마지막 레코드까지 조회하여 검색 조건과 비교하게 된다.
  • 많은 양의 데이터를 조회 할 경우 풀스캔은 모둔 레코드를 조회하기 떄문에 성능이 느려지게 된다.

언제쓰면 좋을까

  • SELECT 쿼리에서 성능이 잘나오지만 INSERT, UPDATE, DELETE 쿼리에서는 때에 따라 다르다.
  • UPDATE, DELETE는 WHERE절에 잘 설정된 인덱스로 조건을 붙여주면 조회할 때 성능은 크게 저하 되지 않는다 (인덱스로 인해 데이터 조회에 속도가 빨라지는 것이고 데이터 수정 자체가 빨라지는 것이 아님)
  • INSERT의 경우, 새로운 데이터가 추가되면서 기존 인덱스 페이지에 저장되어 있던 탐색 위치가 수정되어야 하므로 효율이 좋지 않다.
  • WHERE 절에서 자주 사용되는 Column
  • 외래키가 사용되는 Column
  • join에 자주 사용되는 Column
  • ORDER BY, ORDER BY
  • 데이터양이 많은 테이블

인덱스 칼럼 기준

  • 카디널리티가 높은 것
  • 전체 행에 대한 특정칼럼의 중복 수치를 나타낸 지표로 복합인덱스인 경우 카디널리티가 높은 순에서 낮은순으로 구성하는게 좋다.

인덱스 사용방법

  • 생성
CREATRE INDEX 인덱스이름 ON 테이블이름(필드1, 필드2, ...)
  • 삭제
ALTER TABLE 테이블이름 DROP INDEX 인덱스이름
  • 실행계획
SHOW INDEX FROM 테이블 이름
구분설명
Table테이블 이름
Non_unique인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함
Key_name인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함
Seq_in_index인덱스에서의 해당 필드의 순서를 표시함
Column_name해당 필드의 이름을 표시함
Collation기본적인 정렬 형태, A오름 차순, NULL: 정렬구분 없음
Cardinality인덱스에서 저장된 유일한 값들의 수를 표시함
Sub_part인덱스 접두어를 표시함
Packed키가 압축되는 방법을 표시함
Null해당 필드가 Null을 저장 할 수 있으면 YES, 그렇지 않으면 ”를 표시함
Index_type인덱스에 사용되는 메소드를 표시함 인덱스 모드(BTREE, FULLTEXT, HASH, RTREE) / FULLTEXT 는 5.7 이상에서 유효
Comment해당 필드를 설명하는 것이 아닌 인덱스에 관한 정보를 표시함
Index_comment인덱스에 관란 모든 기타 정보를 표시함
실행계획 정보

유의사항

  • 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야만 한다.
    • 인덱스 컬럼순서와 조회쿼리 컬럼 순서를 지킬필요는 없다.
  • 인덱스로 사용된 컬럼값 그대로 사용해야 인덱스가 사용된다.
    • 인덱스는 가공된 데이터를 저장하고 있지 않다.
  • AND 연산자는 검색 범위를 좁혀주지만 OR 연산자는 비교할 행이 더 늘기 때문에 풀 스캔이 발생할 확률이 높다.
  • 범위조건일때 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않는다.
  • null 값의 경우 is null 조건으로 인덱스 레인지 스캔 가능
  • LIKE 검색시 %가 앞에 위치하면 풀스캔 발생

참조