해당 글은 운영 중인 어플리케이션의 사용자단에서 자주 사용되는 product(상품) 테이블에 index 설정을 적용하여 관련 성능을 개선한 내용을 포함하고 있습니다.
1. show variables like '%profiling%'; 명령어를 통해 MySQL Profiling 설정 확인하기
# MySQL Profiling 설정 확인하기
show variables like '%profiling%';
2. Profiling 관련 설정 활성화 및 조회 개수 설정
- Profiling 기능을 활용하여 Query 성능 변화를 확인하기 위해 profiling 기능을 활성화하고 한번에 조회할 수 있는 profiling_history_size를 늘려줍니다.
# profiling 기능 활성화
set profiling = 1;
# 한번에 볼 수 있는 profiling history의 개수를 100개로 설정
set profiling_history_size = 100;
3. Index 설정할 컬럼 Index 등록하기
- 아래와 같은 product 데이터를 100개 조회하는 SQL을 실행하려고 합니다.
- 현재 테스트 DB product 테이블에는 약 1만개의 테스트 상품 데이터가 저장되어있습니다.
# 실제 운영 시에는 조회해야하는 컬럼만 SELECT 되도록 설정하며, 빠른 테스트를 위해 *로 조회했습니다.
SELECT
p.*,
b.*
FROM
product p
JOIN brand b ON p.brand_id = b.id
WHERE
p.status NOT IN ('REMOVE', 'PRIVATE')
AND p.display = 'ON'
AND p.approval = 'Y'
ORDER BY
p.created_at DESC
LIMIT 100;
- SQL에서 정렬 시 사용되는 created_at 컬럼에 대해 Index 설정을 진행했습니다.
# Index 설정 등록
CREATE INDEX idx_created_at ON product (created_at);
# 등록된 Index 설정 확인
SHOW INDEX FROM product;
# Index 설정 삭제
CREATE INDEX idx_created_at ON product (created_at);
idx_created_at 설정이 정상적으로 반영된 것을 확인할 수 있습니다.
4. profiling 기능을 통해 Duration, CPU 사용량 등을 확인하고 싶은 Query 실행하기
- 저는 created_at 컬럼의 Index 설정 전후에 대해 각각 profiling을 통해 비교했습니다.
SELECT
p.*,
b.*
FROM
product p
JOIN brand b ON p.brand_id = b.id
WHERE
p.status NOT IN ('REMOVE', 'PRIVATE')
AND p.display = 'ON'
AND p.approval = 'Y'
ORDER BY
p.created_at DESC
LIMIT 100;
5. show profiles; 명령어를 실행하여 실행한 Query의 Query_ID 찾기
- 4번의 SQL을 실행하고 show profiles; 명령어를 실행할 경우 Query_ID를 확인할 수 있습니다. created_at 컬럼 Index 설정 전후에 대한 Query_ID를 각각 확인합니다. 가장 왼쪽의 숫자로 구성되어 있는 셀이 Query_ID 입니다. 순서대로 Duration, 실행한 Query가 표시됩니다.
6. show profile cpu for query (Query_ID 값); 명령어를 통해 Query 성능 확인하기
created_at 컬럼의 Index 설정 전
- Duration 총합 : 0.026525
- CPU_user 총합 : 0.026304
- CPU_system 총합 : 0.000327
created_at 컬럼의 Index 설정 후
- Duration 총합 : 0.002575
- CPU_user 총합 : 0
- CPU_system 총합 : 0.002571
7. 결과
- Duration : 0.026525 → 0.002575 (약 10.3배 감소)
- CPU_user : 0.026304 → 0
- CPU_system : 0.000327 → 0.002571
확인 결과, Duration은 대폭 감소했고 CPU_user는 0으로 감소하였습니다. 다만 CPU_system이 설정 전에 비해 약간 증가된 것을 확인하였습니다.
8. 상세 분석
Duration의 감소
- Duration 총합이 0.026525에서 0.002575로 대폭 감소했습니다. 이는 쿼리 실행 시간이 크게 단축되었음을 의미하며, 인덱스를 통해 created_at 컬럼을 기준으로 데이터를 훨씬 효율적으로 검색할 수 있게 되었기 때문입니다. 쿼리 성능에 있어서 실행 시간의 감소는 매우 중요한 개선이며, 사용자 경험과 시스템의 처리량 측면에서도 긍정적인 영향을 미칩니다.
CPU 사용량의 변화
- CPU_user가 0.026304에서 0으로 감소하였습니다. 이는 사용자 모드에서의 CPU 처리 시간이 사실상 없음을 의미하며, 인덱스로 인해 데이터베이스 엔진이 데이터를 찾는 데 드는 계산 비용이 크게 줄어들었음을 나타냅니다.
- CPU_system이 0.000327에서 0.002571로 증가했습니다. 시스템 모드에서의 CPU 사용량이 약간 증가한 것은 인덱스를 관리하고 검색하는 과정에서 발생하는 추가적인 시스템 호출이나 커널 레벨에서의 처리가 늘어났기 때문일 수 있습니다. 이는 인덱스를 통해 데이터를 효율적으로 접근하는 과정에서 발생하는 자연스러운 현상입니다.
인덱스 설정의 유의미함
위 결과를 종합해 볼 때, 인덱스 설정은 매우 유의미한 성능 개선을 가져왔다고 볼 수 있습니다. 실행 시간(Duration)의 대폭적인 감소는 인덱스의 효과를 명확하게 보여주며, 이는 데이터베이스의 응답성과 전체적인 성능에 긍정적인 영향을 미칩니다.
9. 기타
- DB Index의 경우 일반적으로 Cardinality가 높은 컬럼을 기준으로 설정하게 됩니다. Index를 설정할 DB 테이블의 컬럼들의 Cardinality를 확인하고 싶을 경우 아래의 SQL을 통해 확인할 수 있습니다.
SELECT
CONCAT(ROUND(COUNT(DISTINCT id) / COUNT(*) * 100, 2), '%') AS id_cardinality,
CONCAT(ROUND(COUNT(DISTINCT title) / COUNT(*) * 100, 2), '%') AS title_cardinality,
CONCAT(ROUND(COUNT(DISTINCT content) / COUNT(*) * 100, 2), '%') AS content_cardinality,
CONCAT(ROUND(COUNT(DISTINCT who) / COUNT(*) * 100, 2), '%') AS who_cardinality,
CONCAT(ROUND(COUNT(DISTINCT createDate) / COUNT(*) * 100, 2), '%') AS createDate_cardinality,
CONCAT(ROUND(COUNT(DISTINCT updateDate) / COUNT(*) * 100, 2), '%') AS updateDate_cardinality
FROM notice;
- SQL을 실행할 때, 내가 설정한 Index 설정이 실제 사용되고 있는지 EXPLAIN 명령어를 통해 확인할 수 있습니다.
EXPLAIN
SELECT
p.*,
b.*
FROM
product p
JOIN brand b ON p.brand_id = b.id
WHERE
p.status NOT IN ('REMOVE', 'PRIVATE')
AND p.display = 'ON'
AND p.approval = 'Y'
ORDER BY
p.created_at DESC
LIMIT 100;
- EXPLAIN 명령어를 format = json 실행할 경우 보다 상세한 결과를 json 형태로 조회할 수 있습니다.
EXPLAIN format = json
SELECT
p.*,
b.*
FROM
product p
JOIN brand b ON p.brand_id = b.id
WHERE
p.status NOT IN ('REMOVE', 'PRIVATE')
AND p.display = 'ON'
AND p.approval = 'Y'
ORDER BY
p.created_at DESC
LIMIT 100;
'Performance' 카테고리의 다른 글
[AWS] CloudFront + Lambda@Edge 이미지 리사이징 (0) | 2023.01.07 |
---|