[SQL튜닝] 인덱스(Index)를 이용한 SQL 성능 개선

 

✅ 목적

 

특정 키워드를 검색하기 위해 DB에 데이터를 조회하게 되면  LIKE %keyword% 이런식으로 접근하는데....

 

게시물 본문과 같은 경우 글이 너무 많은 경우 그 안에서 키워드를 찾기란 쉽지 않다.

 

이런 경우에 검색 성능을 높이기 위해 고려할 수 있는것들이 존재한다.

쿼리 최적화와 더불어 인덱싱을 사용함으로써 성능개선에 도전해보게 되었다.

 

✅  어떤 상황이었는가?   (이전 설계의 상태 :  ERD와 MySQL)

 

게시글(BulletinPost)와 장소(Amenity)는 뗄레야 뗄 수 없는 관계이다. 

 

⏺ ERD

BulletinPost와 Amenity는 다음과 같은 관계이다.

게시글 1개는 장소 1개를 가질수 있다.

장소 1개는 게시글 N개에 태그될 수 있다.

 

 

⏺ 요청 

해당 API가 사용되기까지 과정은 아래와 같다.

 

1. 게시글에 특정 장소를 태그를 한다.

2. 게시글과 장소 정보가 DB에 저장된다.

 

3. 추천장소 Tab에서 장소를 필터링한다.

 

4. 필터링된 지역에 해당하는 장소들 중, 게시물이 많이 태그된 순서로 <장소 정보>를 반환해야한다.

5. 응답(Response) 데이터엔 몇개의 게시글이 태그되었는지도 보내줘야한다.

 

 

 

✅ 왜 인덱스(INDEX)를 사용하려고 했는가?

 

 

👩‍💻 실행계획 (FULL SCAN)

실행계획을 간단하게 살펴보자.

 

EXPLAIN 
SELECT 
    a.amenity_id AS amenityId,
    a.address_id AS addressId,
    CONCAT(a.address_start, ' ', a.address_last) AS address,
    a.amenity_name AS amenityName,
    a.longitude AS longitude,
    a.latitude AS latitude,
    bp.amenity_id AS amenityId,
    COUNT(bp.bulletin_post_id) AS bulletinPostCount
FROM 
    amenity a
    JOIN bulletin_post bp ON a.amenity_id = bp.amenity_id
WHERE 
    a.address LIKE ('서울 노원구')
GROUP BY 
    bp.amenity_id
ORDER BY 
    bulletinPostCount DESC;

 

 

1. Amenity 테이블에서 address 컬럼이 <서울 노원구> 로 시작하는지 체크한다.

2. 해당하면 BulletinPost 테이블과 Join하여, 몇개의 게시글이 태그되었는지 확인한다

3. 임시(tmp) 테이블에 해당 데이터를 저장하고 Grouping하여, 게시글 태그가 많은 순서로 정렬해 데이터를 반환한다.

 

 

이런 상황에서 SQL쿼리문만을 사용하면 문제가 발생한다.

현재 단순 LIKE문을 이용하여 <서울 노원구>로 시작하는지 검증부터 진행해야하기 떄문에 무조건 모든 row(행)을 다 봐야한다.

단순히 FULL SCAN은 데이터가 많아지면 많아질수록 효율적이지 못하다.

 

 

테이블에 100만개 1000만개, 1억개의 데이터가 들어갔음에도 전체를 봐야만 한다면.... 응답 시간이 얼마나 늦어질지😵😵 

 

여기에서 DB ERD를 개선하고, 인덱스를 사용해야겠다는 생각을 하게 되었다.

 

 

✏️ 인덱스 생성 조건

 

인덱스를 잘못 사용하면 성능이 나빠질 수 있음을 생각해서 인덱스를 사용할지 말지를 결정해야한다.

 

 

아래는 내가 이전에 학교에서 MYSQL 인덱스를 공부하면서 적어둔 인덱스 생성 조건이다.

 

1. 인덱스는 열 단위에 생성
2. 인덱스는 WHERE 절에서 사용되는 열에 생성
3. WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음
4. 데이터 중복도가 높은 열에는 인덱스를 만들어도 효과가 없음 (선택도가 높은 경우는 효과 없음)
5. 외래키를 설정한 열에는 자동으로 외래키 인덱스가 생성됨
6. 조인에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋음
7. 데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려해야 함
8. 단일 테이블에 인덱스가 많으면 속도가 느려질 수있다. (테이블당 4~5개 권장)
9. 클러스터형 인덱스는 테이블당 하나만 생성할 수 있음
10. 테이블에 클러스터형 인덱스가 아예 없는 것이 좋은 경우도 있음
11. 사용하지 않는 인덱스는 제거

 

📣 생성조건을 기반으로 변경한 설계

 

where절에 사용되는 열 단위에 생성되어야한다

 

->  검색 대상 컬럼은 <서울 광진구>와 같이 정해져 있는 정보이므로 검색 대상 컬럼과 나머지 컬럼으로 분리해서 WHERE절에 address_start만 사용하도록 한다.

-> address_start에 인덱스를 생성한다.

 

 

 

 

 

⏺ MySQL 인덱스

Innodb는 MySQL에서 기본적으로 사용되는 저장 엔진 중 하나이다.

 

InnoDB는 B+TREE 자료구조를 사용한다. 

• B+TREE : 내부 노드에 자식 노드의 포인터만 저장하고, 리프 노드에만 실제 데이터를 저장하는 자료구조
• 범위검색, 정렬된 결과를 효과적으로 처리하는데 효과적이다.
• B-TREE보다 데이터 접근에 뛰어나다!  

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-physical-structure.html

MySQL :: MySQL 8.0 Reference Manual :: 15.6.2.2 The Physical Structure of an InnoDB Index

 

 

DB에서 인덱스가 사용되는걸 이미지화 하면 다음과 같다.

 

 

 

👩‍💻 인덱스 생성후 적용하여 개선하기!

 

#인덱스 생성 
create index idx_address on amenity (address_start);

#인덱스 생성 확인
show index from amenity;

 

EXPLAIN 
SELECT 
    a.amenity_id AS amenityId,
    a.address_id AS addressId,
    CONCAT(a.address_start, ' ', a.address_last) AS address,
    a.amenity_name AS amenityName,
    a.longitude AS longitude,
    a.latitude AS latitude,
    bp.amenity_id AS amenityId,
    COUNT(bp.bulletin_post_id) AS bulletinPostCount
FROM 
    amenity a
    JOIN bulletin_post bp ON a.amenity_id = bp.amenity_id
WHERE 
    a.address_start LIKE ('서울 노원구')
GROUP BY 
    bp.amenity_id
ORDER BY 
    bulletinPostCount DESC;

 

 

 

확인하는 row의 수가 확 줄었다.

 

 

👩‍💻 실행계획 (INDEX RANGE SCAN)

 

 

 

 

 

# 인덱스 삭제
ALTER TABLE amenity DROP INDEX idx_address;

 

 

 

---

 

 

✅ 데이터 조회 시간의 비교

 

아래는 쿼리를 날린 후 걸린 시간을 측정하여 캡쳐한 사진이다.

1 : 인덱스 생성

2 : 인덱스가 적용된 상태로 SELECT문을 실행 

5 : 인덱스 삭제
6 : 인덱스가 적용되지 않은 FULL SCAN 상태로 SELECT문 실행

 

 

인덱스 생성 후 INDEX RANGE SCAN 데이터 조회 시간   >>   0.090 

인덱스가 없는 FULL SCAN 데이터 조회 시간                    >>   0.170  

 

인덱스를 사용함으로써 데이터 조회시간이 약 50%정도 감소했음을 볼 수 있다!

 

 

 

 

 

 

✅ 인덱싱 전후 전체 비교

 

	FULL SCAN	INDEX RANGE SCAN
같은 데이터 조회시 걸린시간 (sec)	0.170sec	0.090 sec
Query Cost	44763.06	6351.45
조회시 보게되는 행(row)의 개수	84000	1477

 

Query Cost : 쿼리문을 작동시키는데 발생하는 비용/시간 을 의미

 

 

---

 

🥲🥲🥲

인덱스 처리를 통해 이전의 풀스캔보다 성능이 훨씬 개선된 것은 맞지만

다양한 가능성과 경우의수를 따지면 조금 더 효율적으로 개선할 수 있을것 같다고 생각한다.

인덱스를 간단하게 공부하고 적용한지라 인덱스의 내부 동작과정과 분석방법에 대해 추가적인 공부가 조금 더 필요함도 느낀다.

  

 

 

✍️추가공부한 내용들 (2023.04.29)

https://jie0025.tistory.com/513

 

 

 

추가적으로  개선할 수 있는지 공부하면서 고민해봐야겠다...

https://jeong-pro.tistory.com/246

 

 

그래도 학교에서 배울 당시에는 이해를 완벽하게 하지 못했었는데 다시 읽고 나니까 좀더 받아들여지는게 다르다.

역시 사람은 반복학습을 통해 점점 성장하는것 같다.

 

이전에 이해하지 못했던것들도 꾸준히 다시 두들겨서 내것으로 만들어내야지!