MySQL Index 설정을 통한 DB 성능 개선기

해당 글은 운영 중인 어플리케이션의 사용자단에서 자주 사용되는 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;