데이터베이스를 최적화하는 방법들

데이터베이스를 어떻게 설계하고 쿼리를 어떻게 작성하느냐에 따라

병목현상이 발생하여 소프트웨어의 성능이 저하될 수 있다.

데이터베이스를 최적화하는 방법을 자세히 소개하는 글이 있어 이 글을 보며 정리해 봤다.

 

해당 포스트 링크

11 Database Optimization Techniques

 

 

인덱싱 (Indexing)

1. 인덱스 만들기

인덱스는 빠른 조회 메커니즘을 제공하는 데이터 구조로, 쿼리 성능을 크게 향상시킨다.

인덱스는 DB엔진이 WHERE절을 충족하는 행을 빠르게 찾을 수 있도록 하는 정렬된 데이터 구조를 만들어서 작동한다.

인덱스를 사용하면 SELECT 쿼리 속도가 빨라지지만 쓰기 작업 속도는 느려질 수 있으므로 읽기와 쓰기 성능 간의

균형을 맞추는 것이 중요하다.

CREATE INDEX idx_username ON users(username);

 

2. 복합 인덱스 사용

복합 인덱스는 여러 열을 포함하며 여러 조건에 따라 필터링하거나 정렬하는 쿼리에 유용하다.

이렇게 하면 각 열에 대한 별도의 인덱스가 필요 없게 되고 쿼리 플래너의 효율성이 향상된다.

CREATE INDEX idx_name_age ON employees(name, age);

정규화와 비정규화

1. 정규화

정규화란 중복성과 의존성을 최소화하여 데이터 이상이 발생할 가능성을 줄이는 기술이다.

큰 테이블을 더 작고 관련된 테이블로 분할함으로써 정규화는 데이터 일관성을 보장한다.

하지만 여러 개로 나누어진 테이블로 인해 서로 다른 테이블에서 데이터 조회가 필요할 경우

쿼리 복잡도가 증가한다.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    address VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

 

2. 비정규화

정규화와 반대로 중복성을 어느 정도 허용하여 성능을 향상시키는 기술이다. 

중복된 열이나 테이블을 전략적으로 추가하는 것이 포함될 수 있다.

CREATE TABLE denormalized_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    order_date DATE
);

쿼리 최적화

1. 쿼리를 최적화시키기

자주 사용하는 쿼리를 분석하고 최적화하는 기술이다.

EXPLAIN과 같은 도구를 사용하여 쿼리 실행 계획을 이해하고 개선 영역을

식별해 쿼리의 개선 방향을 모색할 수 있다. (인덱스 사용 여부, 조인 방식, 데이터 스캔 범위 등을 분석할 수 있다.)

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

 

2. SELECT * 사용하지 않기

select 쿼리를 수행할 때 *를 통해 모든 열을 가져오지 않고 필요한 열만 검색한다.

이렇게 하면 처리되는 데이터 양이 줄어들어 쿼리 성능이 향상될 수 있다.

SELECT order_id, order_date FROM orders WHERE customer_id =  123 ;

 

파티셔닝

1. 테이블 파티셔닝

큰 테이블을 여러 개의 작은 논리적 단위(파티션)로 나누어 관리하는 기술이다.

즉, 하나의 테이블을 여러 개의 부분(파티션)으로 분할하여 데이터 검색 및 관리 성능을 최적화하는 방법이다.

CREATE TABLE orders (
    id INT NOT NULL,
    created_at DATE NOT NULL,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE(YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

파티션 p2023에서만 검색하므로 성능이 향상된다.

캐싱

1. 쿼리 캐싱

자주 실행되는 SQL 쿼리의 결과를 저장(캐싱)하여, 동일한 요청이 들어왔을 때 데이터베이스에서

다시 실행하지 않고 저장된 결과를 반환하는 방식이다.

-- Pseudocode
DECLARE @cacheKey NVARCHAR(255) = 'query_cache_key';
DECLARE @cachedResult NVARCHAR(MAX);

SET @cachedResult = REDIS.GET(@cacheKey);

IF @cachedResult IS NULL
BEGIN
    -- Execute the query and store the result in the cache
    SET @cachedResult = EXECUTE_QUERY('SELECT * FROM large_table');
    REDIS.SET(@cacheKey, @cachedResult, EXPIRY_TIME);
END

-- Use @cachedResult for further processing

(쿼리  캐시는 테이블의 데이터가 변경되면 캐시에 저장될 결과 중에서 변경된 테이블과

관련된 것들은 모두 삭제해야 했다. 이는 심각한 동시 성능 저하를 유발했다.

데이터 변경은 거의 없고 읽기만 하는 서비스에서는 매우 좋은 기능이었지만 

이런 요건을 가진 서비스는 흔치 않았기 때문에 MySQL8.0에서 해당 쿼리 캐시 기능은 제거되었다. 참고)

 

2. 객체 캐싱

자주 액세스하는 객체나 데이터를 애플리케이션 계층에 캐싱하여 데이터베이스 쿼리를 최소화한다.

메모리 내 캐싱 라이브러리나 프레임워크를 사용할 수 있다.

@Service
@RequiredArgsConstructor
public class PerformanceService {
    private final RedisTemplate<String, Performance> redisTemplate;
    private final PerformanceRepository performanceRepository;

    private static final String CACHE_KEY = "performance:";

    public Performance getPerformance(Long id) {
        // 캐시에서 먼저 조회
        Performance performance = redisTemplate.opsForValue().get(CACHE_KEY + id);

        if (performance != null) {
            return performance; // 캐시된 객체 반환
        }

        // 캐시에 없으면 DB에서 조회 후 저장
        performance = performanceRepository.findById(id).orElseThrow();
        redisTemplate.opsForValue().set(CACHE_KEY + id, performance, Duration.ofMinutes(10));

        return performance;
    }
}

 

정기 유지 관리

1. 통계 업데이트

데이터베이스는 테이블과 인덱스에 대한 각종 통계를 자동으로 수집하여, 이를 바탕으로 어떤 실행 계획이 가장

효율적인지 결정한다.

- 어떤 인덱스를 사용할지, 어떤 조인 방식을 선택할지 결정

- 통계 정보가 오래되면 잘못된 실행 계획이 선택될 가능성이 높아져 성능이 저하될 수 있다.

따라서 통계를 주기적으로 업데이트하여 실행계획을 최적화하는 것이 좋다.

-- Update statistics for a table
UPDATE STATISTICS table_name;

 

2. 데이터 아카이빙

더 이상 필요하지 않은 오래된 데이터를 저장하거나 삭제하는 방법이다.

이를 통해 쿼리 성능을 개선하고 스토리지 요구사항을 줄일 수 있다.

특히 대용량의 과거 데이터 세트가 있는 시스템에서 더 좋은 효과를 낸다.

-- Archive data older than a certain date
DELETE FROM historical_data WHERE date < '2020-01-01';

 

하드웨어 최적화

1. 서버 구성 최적화

워크 로드(작업 부하)와 하드웨어 기능에 따라 데이터베이스 서버 설정 및 구성을 조정한다.

예를 들어 버퍼 크기, 캐시 설정 연결 제한 같은 것들이 있다.

-- Example: Increase the size of the query cache
SET GLOBAL query_cache_size = 256M;

 

2. SSD 사용하기

전형적인 HDD보다 더 빠른 데이터 접근 속도를 제공하는 SSD를 사용할 수 있다.

 

동시성 제어

격리 수준

애플리케이션 요구사항에 따라 격리 수준을 조정해야 한다.

적절한 격리 수준을 선택하는 것이 일관성과 성능의 균형을 맞추는데 중요하다.

-- Set isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

 

커넥션 풀링

커넥션 풀링 사용

각 요청에 대해 매번 새 연결을 설정하지 않고 미리 생성해 둔 연결을 재사용하는 것이 좋다.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/database");
config.setUsername("username");
config.setPassword("password");
config.setMaximumPoolSize(10);

HikariDataSource dataSource = new HikariDataSource(config);

 

데이터 베이스 설계

효율적인 스키마 설계

스키마를 설계하는 단계에서부터 성능을 염두에 두고 적절히 설계해야 한다.

데이터 유형 최적화, 적절한 제약 조건 사용, 불필요한 관계등을 최소화하면

쿼리 효율성이 상당히 좋아질 수 있다.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    price DECIMAL(10, 2),
    -- Additional columns as needed
);

 

모니터링 및 프로파일링

1. 정기적 모니터링

CPU 사용률, 메모리 사용율, 쿼리 실행 시간과 같은 주요 지표들을

정기적으로 모니터링하여 잠재적인 문제를 파악해야 한다.

SHOW STATUS LIKE 'cpu%';

 

2. 쿼리 프로파일링

개별 쿼리 성능을 프로파일링하고 분석하여 병목 현상을 찾아낼 수 있다.

MySQL Performance Schema와 같은 도구를 사용하여 더 자세하게 쿼리 실행에 대해 분석할 수 있다.

-- Enable Performance Schema
SET GLOBAL performance_schema = ON;

-- Profile a specific query
SELECT * FROM orders WHERE customer_id = 123;