데이터베이스 최적화하는 방법 11가지

기술문서 : https://danielfoo.medium.com/11-database-optimization-techniques-97fdbed1b627

11 Database Optimization Techniques

 

---

 

사용자 수가 적지 않은 서비스를 운영하다보면 데이터베이스로 인한 어플리케이션 성능 이슈가 발생하는 경우가 많습니다.

저또한 설계의 미스로 인해 매우 복잡한 쿼리가 탄생하거나 슬로우 쿼리가 발생한 경험이 많았었는데

이 글을 통해 데이터베이스 최적화 방법이 어떤 것들이 있고 어떻게 방지할 수 있을지를 정리할 수 있었습니다.

 

다음은 이 글에서 소개하는 11가지 데이터베이스 최적화 방법입니다.

 

인덱싱 (Indexing)

1. 인덱스 생성

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

인덱스는 where 조건에 만족하는 데이터를 빠르게 찾을 수 있도록 항상 정렬된 데이터 구조를 생성하여 작동합니다

하지만 인덱스는 읽기 성능을 크게 향상시켜주는 대신 쓰기 성능은 감소되기 때문에

읽기와 쓰기 성능 사이에서 적절한 균형을 찾아 생성하는 것이 중요합니다.

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 sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2020),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

 

2. 파티션 정리

쿼리 플래너가 쿼리 실행 중에 불필요한 파티션을 정리하는지 확인하세요.

이렇게 하면 전체 데이터 세트를 스캔하는 것이 방지되고 성능이 향상됩니다.

SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2023-01-01';

---

캐싱

1. 쿼리 캐싱

자주 실행되는 쿼리 결과에 대해 저장소에 캐싱 매커니즘을 구현하는 방법입니다.

이렇게 하면 캐시된 결과를 제공하여 데이터베이스를 로드하는 경우가 줄어들고 응답 시간이 향상됩니다.

-- 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

 

2. 객체 캐싱

어플리케이션 계층에서 자주 접근하는 객체 또는 데이터를 캐싱하여 데이터베이스 쿼리를 최소화하는 방법입니다.

인 메모리 캐싱 라이브러리나 프레임워크를 사용하여 이를 구현할 수 있습니다.

from django.core.cache import cache

def get_user_data(user_id):
    # Try to fetch user data from cache
    user_data = cache.get(f'user_{user_id}')

    if user_data is None:
        # If not in cache, fetch from the database
        user_data = User.objects.get(id=user_id)

        # Store the data in cache for future requests
        cache.set(f'user_{user_id}', user_data, TIMEOUT)

    return user_data

---

정기 유지 관리

1. 통계 업데이트

쿼리 플래너가 싱행 계획에 대해 정보를 바탕으로 결정을 내리려면 통계를 최신 상태로 유지하는 것이 중요합니다.

정기적으로 통계를 업데이트 함으로써 정확하고 효율적인 쿼리 실행 계획을 수립하는 것을 보장할 수 있습니다.

-- Update statistics for a table
UPDATE STATISTICS table_name;

 

2. 데이터 아카이빙

더 이상 사용하지 않는 오래된 데이터를 아카이브하거나 삭제하는 방법입니다.

이를 통해 검색에 수행되는 row 수를 줄여 쿼리 성능을 향상시킬 수 있고 스토리지 요구 사항을 줄일 수 있습니다.

특히 대규모 기록 세트가 있는 시스템에서 더욱 큰 효과를 발휘합니다.

-- 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 사용하기

스토리지로 SSD 사용을 고려할 수 있습니다.

SSD는 전형적인 HDD보다 더 빠른 데이터 접근 속도를 제공하기 때문에

데이터베이스의 성능 향상을 기대할 수 있습니다.

---

동시성 제어

1. 격리 레벨 조정

만들고 있는 어플리케이션 요구사항에 적합한 격리 수준을 찾아 조정해야 합니다.

격리 수준은 한 트랜잭션에서 다른 트랜잭션으로 변경된 내용의 가시성을 제어합니다.

적합한 격리 수준을 선택하는 것은 지속성과 성능 향상을 위한 중요한 사항입니다.

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

---

연결 풀링

1. 데이터베이스 연결 지속하기

각 요청에 대해 새 연결을 설정하는 오버헤드를 피하기 위해 데이터베이스 연결을 재사용 할 수 있습니다.

연결 풀링은 데이터베이스 연결을 효율적으로 관리하고 재사용이 가능하도록 도와줍니다.

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);

---

데이터베이스 설계

1. 효율적인 스키마 설계

성능을 염두해 두고 데이터베이스 스키마 설계를 수행해야 합니다.

데이터 타입을 최적화하거나 적절한 constraints를 사용하거나 불필요 relation을 최소하하는 등의 작업을 수행할 수 있습니다.

잘 설계된 스키마는 쿼리 효율성에 큰 영향을 미칠 수 있습니다.

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 성능 스키마와 같은 도구는 쿼리 실행에 대한 자세한 통찰력을 제공할 수 있습니다.

-- Enable Performance Schema
SET GLOBAL performance_schema = ON;

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