데이터베이스

MySQL 인덱스 동작 확인하기

wwns 2023. 7. 23. 20:29
반응형

이전 글에서 조회 성능 최적화를 위한 인덱스를 작성하면서 인덱스를 적용시키는 여러 방법에 대해서 지식이 부족한 것 같아 추가로 인덱스 동작을 확인하는 글을 정리하게 되었다

 

MySQL8.0 버전 MySQL WorkBench의 Visual Explain을 사용하면서 정리해보려고 한다

https://engineering.linecorp.com/ko/blog/mysql-workbench-visual-explain-index#visualExplainIndex

인덱스는 B-tree구조로 되어있으며 리프 노드는 인덱스로 지정한 컬럼의 오름차순으로 정렬되어 있어 where절에 `=`, `>, <`연산을 빠른 속도로 처리할 수 있다

 

https://engineering.linecorp.com/ko/blog/mysql-workbench-visual-explain-index#visualExplainIndex

복합인덱스는 인덱스를 지정한 순서 (col1, col1)의 순서로 정렬되어있다

 

따라서 where 절에 col2 <= 2에 대한 조건에서는 인덱스를 사용할 수 없게 된다

-> col2는 col1 내에서 정렬된 상태이기 때문에 col1이 정해져야 col2를 범위 탐색할 수 있게 되는 것

 

마찬가지로 where col1 <= 2 and col2 > 1의 경우에도 col1은 트리를 탐색할 수 있지만 col2는 트리를 사용해서 찾을 수 없기 때문에 

col1이 결정되었을 때 col2의 데이터를 탐색할 수 있게 된다

-> 데이터 분포에 따라 성능이 크게 달라질 것으로 보임

 

따라서 where col1 = 1 and col2 > 1에 사용하게 되면 col1이 정해지면 col2이 정렬된 상태이니 빠르게 범위 탐색이 가능하다

ex:) 사용자가 2023년 이후 작성한 글을 불러오자 -> memberId=1 and createdDate > 2023.07

 

테스트에 앞서 사용한 DB테이블과 인덱스 설정 DDL이다

CREATE TABLE `POST` (
  `id` int NOT NULL AUTO_INCREMENT,
  `memberId` int NOT NULL,
  `contents` varchar(100) NOT NULL,
  `createdDate` date NOT NULL,
  `createdAt` datetime NOT NULL,
  `likeCount` int DEFAULT NULL,
  `version` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `POST__index_member_id` (`memberId`),
  KEY `POST__index_created_date` (`createdDate`),
  KEY `POST__index_member_id_created_date` (`memberId`,`createdDate`)
) ENGINE=InnoDB AUTO_INCREMENT=4000018 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  • 테이블에 적재시킨 데이터는
    • memberId = 1 -> 100만 건
    • memberId = 2 -> 200만 건
    • memberId = 3 -> 100만 건 
  • createdDate의 데이터 분포
    • memberId = 1 -> 22년 1월을 30일 종류로 분포 -> 1일 3만 개
    • memberId = 2 -> 22년 1월을 30일 종류로 분포 -> 1일 6만 개
    • memberId = 3 -> 1970년을 365일 종류로 분포 -> 1일 50개

설정된 인덱스 없음

 

SELECT *
FROM post ignore index(POST__index_member_id_created_date, POST__index_member_id, POST__index_created_date)
WHERE memberId = 1 and createdDate between '1900-01-01' and '2023-01-01'
ORDER BY createdDate;

Post 테이블 전체를 스캔해서 얻은 약 400만 건의 행을 WHERE 조건에 부합하는 행을 찾아 createdDate로 정렬하여 반환한다

이때 Order과 post 테이블 모두 빨간색인 것은 둘 다 인덱스를 사용하지 않았음을 나타내며 400만 건의 행을 스캔해서 WHERE 조건에 맞는 행을 찾고 전체를 정렬해서 반환하여 부하가 크다는 것을 의미

약 1.3초가 걸렸으며 memberId를 다르게 설정해도 모두 비슷한 결과로 나타났다 ( Index 사용 x, Full Table Scan(동일 데이터 개수))

WHERE 조건에 인덱스 설정하기

 

memberId 인덱스를 태워서 결과를 확인해 본다

 

예상되는 결과는 memberId로 탐색 범위를 줄이고 -> 400만 개를 100만 개 -> createdDate 조건에 부합하는지 full scan이 이뤄질 것 같다

(여러 인덱스와 데이터가 적재된 상태에서 특정 인덱스를 활용했을 때 결과를 보기 위해 인덱스 사용을 강제하였다)

SELECT *
FROM post use index(POST__index_member_id)
WHERE memberId = 3
ORDER BY createdDate
limit 100;

memberId를 인덱스로 태우고 나니 스캔한 데이터가 3.97M에서 1.97M으로 변했다

여기서 1.85M은 MySQL의 예측치이고, 실제로 memberId = 1인 데이터는 100만 개다. 

 

위의 결과를 보면 테이블 탐색은 초록색으로 인덱스를 타서 처리 속도가 빨라졌으나 Order 절에서는 인덱스를 사용하지 않아 빨간색을 유지하고 있다

 

맨 처음 인덱스를 사용하지 않았을 때 보다 테이블을 스캔하는 것은 인덱스를 활용해서 빨라졌으니 최종 속도가 더 빠르지 않을까?라는 생각을 할 수 있는데, 결과를 보면 다음과 같다

약 1.5배가 느려졌다 이유가 무엇인가?

  • memberId에 인덱스를 태웠으나, memberId의 데이터 분포는 400만 개 데이터 중 3개
  • memberId=1로 탐색한 후 나온 결과 100만 건에 대해 createdDate를 정렬 (+인덱스 테이블에서 실제 데이터 테이블에 접근 I/O)
    • 데이터를 적은양으로 필터링한 것이 아니고, 인덱스 테이블에서 본 테이블로 I/O가 발생하여 오히려 성능이 떨어진 것

ORDER BY 절에 인덱스 설정

그렇다면 ORDER BY 절에만 인덱스를 태워보도록 하자

SELECT *
FROM post use index(POST__index_created_date)
WHERE memberId = 3
ORDER BY createdDate
limit 100;

WHERE절에 MemberId로 부합하는 행을 100만 건 찾아 createdDate로 정렬한 뒤 100개만 추출하도록 했다.

WHERE절에 부합하는 행을 찾는 것은 Full Index Scan이 발생하였다

이 말은 즉, createdDate 인덱스에서 순서대로 레코드를 추출하여 memberId가 1인지 확인하고 해당하는 레코드들을 100개 추출했다는 것이다

  • 최악의 경우는 모든 데이터를 확인하고 limit 개수를 채우게 되는 상황
  • 현재 데이터 분포는 같은 creadtedDate가 3만 개씩 있으므로 한 번 찾으면 연속적으로 찾기 때문에 응답속도가 매우 빠름

Where절에 인덱스를 태울 것인지, Order절에 인덱스를 태울 것인지는 데이터의 분포에 따라 달라질 수 있다

  • ORDER절에 인덱스를 태워 memberId를 빨리 발견하면 -> 응답이 빠를 것
  • ORDER절에 인덱스를 태워 memberId를 맨 마지막에 발견하면 -> 응답이 느릴 것

그렇다면 빨리 발견할 수 있도록 확률을 높여주면 좋은 거 아닌가?

  • WHERE절에 인덱스를 태워 memberId로 얻는 레코드 수를 줄이면 정렬 비용이 적을 것?
    • 걸러진 레코드 수를 정렬한 후 limit 만큼 뽑는다

따라서 걸러지는 데이터의 수(분포)가 어떻냐에 따라 WHERE, ORDER 인덱스 선택을 결정하는데 도움이 된다

GROUP BY절에 인덱스 사용

 

SELECT createdDate, count(*) as cnt
FROM post
WHERE memberId = 1
GROUP BY createdDate
order by createdDate;

 

여기에서 createdDate를 인덱스로 사용하면 group절에 인덱스를 적용할 수 있다

그 결과 `tmp table, filesort`가 사라졌다

tmp table은 GROUP BY를 사용할 때 인덱스를 사용하지 않으면 테이블 전체를 스캔한 후 각 그룹의 모든 행이 연속적인 상태인 임시 테이블(tmp table)을 생성하며 order 시 file sort가 추가되어 이 임시 테이블들이 정렬된 상태로 처리된다

 

이 임시테이블에서 그룹을 찾아내 데이터 처리를 하게 되며 이 임시 테이블의 역할을 인덱스가 하기 때문에 이와 같은 처리가 불필요해진다

 

쿼리 결과

인덱스를 사용했을 때 10배가 넘게 느려졌는데 이는 위에서 설명했듯 Full Index Scan이 원인이 된다

  • GROUP BY절에서 createdDate로 묶은 데이터 총 400만 건에 대해서 memberId가 1인지 확인하고 개수를 세는 방식

따라서 GROUP BY 처리는 빨라지나 테이블 접근은 Full Index Scan이어서 부하가 커져 WHERE 조건에도 인덱스를 지정하는 방법을 고려해야 한다

 

복합 인덱스 설정

복합 인덱스를 설정하여 memberId로 먼저 테이블 스캔이 이루어지고, 남은 결과로 GROUP 절을 통해 데이터를 추출한다

속도도 가장 빠르며 바람직한 설계가 되었다고 생각한다

 

Join에서 인덱스

 

SELECT p.memberId, count(p.id) as post_cnt
FROM 
	Post as p
	INNER JOIN Follow as f ON p.memberId =  f.toMemberId
WHERE createdDate between '1900-01-01' and '2023-01-01'
GROUP BY p.memberId
ORDER BY post_cnt
limit 100;

쿼리는 요약하면 다음과 같다

팔로워가 있는 Post 작성자 중에서 Post를 특정 기간 내에 작성했으며 이 작성자의 id와 작성한 Post의 개수를 조회하는 쿼리이다

결과를 확인해 보면 다음과 같다

  • Join은 nested Loop 처리
    • nested Loop : 하나의 테이블에서 레코드를 가져오고 다른 테이블과 비교하여 조건에 맞는 경우 해당 레코드 반환 -> O(n*m)
      • driving table 행 하나당 inner table 1행씩 스캔해서 결합 조건에 부합하는 것을 추출
  • GROUP BY에서 임시 테이블을 생성하여 데이터를 그룹화함
  • ORDER BY에서 임시 테이블의 데이터를 file sort 

문제점? 분석?

 

VISUAL EXPLAIN을 보면 Full Scan, tmp table, file sort가 발생하기 때문에 이를 없애주기 위해 Follow 테이블에 toMemberId를 인덱스로 설정하고 테스트하였다 또한 Join 시 여러 memberId에 대해서 탐색하는 것이 아니라 현재 사용자로 설정하는 게 바람직하다고 생각하여 쿼리를 수정했다

SELECT p.memberId, count(p.id) as post_cnt
FROM 
	Post as p 
	INNER JOIN Follow as f ON p.memberId =  f.toMemberId 
WHERE p.memberId = 1 and createdDate between '2022-01-25' and '2023-01-01'
GROUP BY p.memberId
ORDER BY post_cnt
limit 100;

인덱스를 잘 탔지만 데이터 분포 때문인지 시간이 10배가량 느려졌다

 

POST에서 걸러지는 데이터의 분포도 100만 개로 부적합하고, Follow에 데이터가 10개로 인덱스를 활용하는 의미가 부적합하다 판단하여 Follow데이터 100만개 (분포는 각 100개씩), createdDate의 범위를 줄여 100만 개의 데이터를 불러오는 것이 아닌 20만 개의 범위로 줄여서 테스트해보았다

그 결과 0.4초 만에 탐색을 성공했다

 

결과를 정리해 보자

  • Follow에 인덱스를 생성하자 GROUP의 임시테이블, ORDER의 file sort가 사라졌다
    • GROUP BY 조건이 memberId이자 toMemberId이기 때문에 이미 Follow에서 인덱스로 정렬된 상태로 오기 때문에 임시 테이블이 불필요한 것으로 보임
  • 인덱스를 타서 필터링을 해도 전체 데이터의 대부분을 차지하게 되면 오히려 속도가 느려졌다
  • 쿼리와 데이터 분포를 생각하여 인덱스를 결정해야 성능 향상의 결과를 기대할 수 있다

MySQL WorkBench의 Visual Explain을 통해 인덱스의 동작을 확인해 볼 수 있었으며 성능 향상을 고민할 때 인덱스를 고려한다면

제공하고 싶은 데이터의 형태나 분포를 잘 고려해야 하며 이런 도구를 활용해서 동작 방식을 확인한 후 적용시켜야겠다

 

참고:

https://engineering.linecorp.com/ko/blog/mysql-workbench-visual-explain-index#indexIntro

 

MySQL Workbench의 VISUAL EXPLAIN으로 인덱스 동작 확인하기

안녕하세요. 저는 LINE 포인트 서버쪽 개발을 담당하고 있는 Ohara(@kory1202)입니다. 얼마 전 특정 테이블에서 데이터를 추출하는 코드를 작성했는데요. 함께 일하는 동료가 그 코드를 보더니 '이런

engineering.linecorp.com

http://www.gurubee.net/article/58242

 

테이블 조인시 index 안타는 문제..(분포도 때문으로 예상)

안녕하세요.  한참 눈팅만 하다가 처음 질문 올려봅니다.. ^^;A와 B 두 테이블 조인하는데 자꾸 두 테이블 다 풀스캔이 뜹니다.. 구조는 아래와..

www.gurubee.net

 

반응형