[MySQL] 인덱스(Index) 정리 및 효율화 방법

ㅁ 들어가며

ㅇ Mysql 인덱스를 튜닝하기 위해 정리하는 중 이동욱님의 글, [mysql] 인덱스 정리 및 팁을 보고 요약해서 정리해 보았습니다.

 

ㅁ 인덱스란?

인덱스 == 정렬

 

 인덱스란 데이터의 저장(INSERT, UPDATE, DELETE) 의 성능을 희생하고 그 대신에 데이터의 읽기 속도를 높이는 테이블의 동작속도(조회)를 높여주는 자료구조입니다.

예를 들어, 책 뒷편의 '찾아보기'에서 알고리즘을 찾아봅시다.  ㄱ~ㅎ 정렬에서 ㅇ을 찾고, 아~이 정렬에서 알을 찾습니다. 이후 알고리즘을 색인하고 페이지 정보를 얻을 수 있습니다.  

  사전도 마찬가지입니다. 영단어를 검색하려면, 일단 알파벳 순에서 해당 알파벳 페이지로 이동하여 단어를 찾아가는 것이 책 전체를 뒤지는 것보다 빠르게 찾을 수 있습니다. 

 

ㅁ B-Tree 인덱스 알고리즘

 mysql에서는 B-Tree 인덱스를 사용하여 컬럼의 값을 변형하지 않고, 원래의 값을 그대로 이용하는 알고리즘입니다. 

 

B-Tree는 트리 구조를 가집니다.

• 루트 노드(Root node): 최상위 노드, ㄱ~ㅎ
  • 브랜치 노드(Branch node): 중위 노드, 가~기
    • 리프 노드(Leaf node): 하위 노드, 단어 색인

ㅇ 데이터를 검색할 때에 루트 -> 브랜치 -> 리프를 색인 후 실제 데이터 조회

     ㄴ 조회 과정의 길이 및 비용이 균등

ㅇ 데이터베이스에서 인덱스는 전체 크기의 약 10% 정도 별도의 데이터로 관리되는데, 리프노드는 실제 데이터의 주소를 가짐.

ㅇ 인덱스는 정렬 VS 데이터는 공간활용

  - 인덱스 키 값은 정렬돼 있지만, 데이터 레코드는 임의 순서로 저장됨

  - 대량의 insert 시에는 순서가 보장될 수도 있음

  - DBMS 설계상 삭제된 공간을 재활용하기 위하여 삭제나 빈공간이 생긴면 그 곳에 순서 상관없이 insert

    (Real Mysql 8.0 v1, p220~221)

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%9D%B8%EB%8D%B1%EC%8A%A4index-%ED%95%B5%EC%8B%AC-%EC%84%A4%EA%B3%84-%EC%82%AC%EC%9A%A9-%EB%AC%B8%EB%B2%95-%F0%9F%92%AF-%EC%B4%9D%EC%A0%95%EB%A6%AC

 

 

 ㅇ 인덱스 페이지 분할은 [2020개정판]이것이 MySQL이다(8.0)09장-03교시 인덱스 - 인덱스의 내부작동 1에 자세히 다루고 있음.

     ㄴ 쉽게 설명하면, 책 색인을 검색 할 때에 한 페이지씩 검색하듯, 인덱스도 페이지 단위로 정렬 생성됨.(3분 26초) 

 

ㅁ 인덱스의 특징

ㅇ 인덱스의 특징을 정리해보자.

 

 - 인덱스 생성 시 DB 크기의 약 10% 정도 추가 공간 필요
 - select 검색 속도를 크게 향상

 - insert, update, delete가 빈번하면 paging으로 인해 성능 저하

 

 

 

ㅁ 인덱스 튜닝 포인트는 무엇이 있을까?

ㅇ 인덱스의 갯수는 3~4개가 적당

 - 데이터 변경 시 인덱스 재정렬 작업 발생

  select 시에는 색인의 속도가 빨라지겠지만, insert, update, delete 시에는 느릴 수 있습니다. 왜냐하면, insert, update,delete를 위해서 우선 select가 선행되어야 하는데, 이후 데이터 변경 및 인덱스 변경으로 후속작업이 발생하기 때문입니다. 더욱이 인덱스의 갯수가 많아지면 그 배수로 지연이 발생하기 때문에 3~4개가 적당합니다.

 - 옵티마이저 혼란: 덱스가 많아지면, 옵티마이져가 인덱스를 잘못 선택할 가능성이 커집니다.

 

ㅇ 인덱스 키 값의 크기는 작게

 인덱스는 페이지 혹은 블록  단위로 관리됩니다. 루트와 브랜치, 리프 노드도 페이지 기준으로 관리된다. 인덱스 키가 커지면, 단위 페이지당 인덱스 row가 작아지고, 더 많은 페이지를 로딩하여서 성능 저하가 발생합니다. 

 

인덱스 페이지 로딩 오버플로워

인덱스 페이지
인덱스 키 (16바이트)	자식노드 주소(12바이트)
인덱스 키 (16바이트)	자식노드 주소(12바이트)
인덱스 키 (16바이트)	자식노드 주소(12바이트)

 

인덱스 키 16 바이트 + 자식노드 주소 12 바이트로 가정해 보자. 

하나의 인덱스 페이지(16KB)에 몇 개의 키를 저장할 수 있을까?

계산해 보면 16*1024/(16+12)= 585개이다. 

키 값이 두배인 32바이트면, 372개로 줄어든다.

만약 Select 쿼리가 레코드 500개를 읽는다면 전자는 인덱스 한번으로 해결되지만, 후자는 2번 이상 디스크를 읽어야 한다. 

디스크를 읽는 횟수가 늘어나면, 그만큼 시간은 느려진다.

 

InnoDB 버퍼 캐시 효율 저하 

 인덱스 크기가 커진다면 InnoDB의 버퍼 캐시에 둘 수 있는 레코드 수는 줄어면서 메모리 효율이 떨어진다. 

 

참조: Real Mysql 8.0 v1, p226~227

 

ㅇ 인덱스 컬럼 기준 시 식별력이 높은 순서로 

식별력이 높다는 것은 인덱스가 세분화되어 더 적은 Row를 식별할 수 있다. 

예를 들어, 인덱스 컬럼이 tinyint(1)이면, 0 혹은 1인데,

  이러면 50% 식별률이다. 그래서 10만 Row의 테이블은 5만개를 색인할 수 있다.

하지만 인덱스 컬럼이 distinct 시 20000개라고 가정하면, 10만 데이터 중에 인덱스가 2만개로 인덱스당 5 Row를 식별할 수 있다.

그래서 인덱스를 생성할 때에 식별력이 더 높은 인덱스를 우선 기준으로 정하는 것이 검색 속도를 향상시키는 방법이 될 수 있다.

 

ㅇ 인덱스 컬럼은 반드시 검색 조건에 포함되어야

 때에 따라서 인덱스의 모든 컬럼을 사용할 수는 없다. 하지만 첫번째 컬럼은 반드시 포함되어야 한다.

인덱스: group_no, from_date, is_bonus

 중간에 포함된 from_date가 누락되어도 필터가 10% 뿐이라도 일부 인덱스가 잡혔지만, group_no가 누락되면 아예 인덱스를 타지 않았다. 실행계획을 확인하여 반드시 첫번재 인덱스 조건은 조회조건에 포함되어 있어야 한다.

 

ㅁ 인덱스 조회 시 주의 사항

ㅇ B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없다. 

 Not-Equal

  - WHERE column <> 'N'

  - WHERE column NOT IN (1,2,3)

  - WHERE column is NOT NULL

 

like '%??' 뒷부분 일치

  - WHERE column LIKE '%피터'

  - WHERE column LIKE '_피터'

  - WHERE column LIKE '% 피%'

 

함수를 사용 등 가공한 경우

  - WHERE SUBSTRING(column,1,) = 'X'

 -  WHERE column*10 > 50000

      ㄴ WHERE column > 50000 /10 <== 이건 가능

 

데이터 타입 불일치

 - WHERE char_cloumn = 10

 

ㅇ 인덱스 사용이 가능한 경우

 - between, <, >, like 앞부분 일치 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼은 작동하지 않는다.

 - =, in 조건은 다음 컬럼에서도 인덱스를 사용할 수 있다.

    ㄴ 다만, in에 서브쿼리면 되지 않는다. 서브쿼리가 먼저 실해오디고, in은 체크 조건으로 실행되기 때문이다.

 - null 값의 경우 in null 조건은 인덱스 색인 가능

    ㄴ 다른 일반적인 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL에서는 NULL 값도 인덱스에 저장된다. 

 

참조: Real Mysql 8.0 v1, p251~253

          이동욱님 블로그

 

ㅁ 함께 보면 좋은 사이트

ㅇ 이동욱님의 글, [mysql] 인덱스 정리 및 팁

ㅇ https://inpa.tistory.com/entry/MYSQL-📚-인덱스index-핵심-설계-사용-문법-💯-총정리