[데이터베이스] - 최적화 방법 11가지

개요

 

서비스를 개발하다보면 데이터베이스 쿼리 연산으로 인해서 애플리케이션 서버의 성능이 떨어지는것을 경험할 수 있습니다. 여러가지 방법을 사용하여 이러한 성능 문제를 개선할 수 있는데요. 해당 게시글에서는 대표적인 11가지 방법에 대해 설명하겠습니다.

1. 인덱싱

단일 인덱스

조건절에 자주 사용되는 컬럼을 인덱싱하면 보다 빠르게 조건에 만족하는 데이터들을 조회할 수 있습니다. 하지만 인덱스를 적용하면 데이터가 삭제, 추가 될 때마다 인덱스 테이블을 갱신해서 성능 이슈가 발생할 수 있습니다.

CREATE INDEX idx_username ON users(username);

복합 인덱스

복합인덱스는 여러 개의 조건으로 데이터를 탐색할 때 빠른 속도로 탐색할 수 있도록 도와줍니다.

CREATE INDEX idx_name_age ON employees(name, age)

2. 정규화와 반정규화

정규화

정규화는 데이터의 무결성과 일관성을 보장하기 위해 중복된 데이터가 발생하지 않도록 커다란 테이블을 작은 테이블로 변환합니다. 정규화정도가 높으면 높을 수록 데이터의 일관성은 보장되지만, 쿼리가 점점 복잡해지고 데이터베이스 성능이 낮아질 수 있습니다.

ex) 3-정규화 예시 (이행적 종속 제거)

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) REFERENCE customers(customer_id)
)

반정규화

반정규화는 테이블 데이터의 중복성을 어느정도 허용하면서 데이터베이스 조회에서의 성능을 얻는 기법입니다.

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

3. 쿼리 최적화

EXPALIN

쿼리 최적화를 하기 위해서 EXPLAIN 구절을 사용할 수 있습니다. MySQL 에서 EXPLAIN 이란 데이터베이스가 데이터를 찾아가는 일련의 과정을 사람이 알아보기 쉽게 DB 결과 셋으로 보여주는 것입니다. MySQL Explain 실행계획을 활용하여 기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수립 등과 같이 성능 최적화와 관련된 전반적인 업무를 처리할 수 있습니다.

상세

EXPLAIN SELECT * FROM orders WHERE customer_id

* 구문 피하기

서버 비즈니스 로직에서 사용되는 데이터만 로드하여 데이터양과 응답속도를 개선할 수 있습니다.

SELECT order_id, order_date
FROM orders
WHERE customer_id = 123;

4. Partitioning

Partitioning Tables

파티셔닝은 데이터 개수가 많은 테이블을 작은 테이블로 나누어서 쿼리 성능을 향상 시킬수 있게 합니다.

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

Partition Pruning

데이터베이스 엔진이 쿼리 실행시 불필요한 데이터를 탐색하는 것을 방지하려면 아래와 같이 범위를 지정한 쿼리를 작성해야 합니다.

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

5. Caching

Query Caching

자주 사용되는 쿼리에 대한 캐시를 구현할 수 있습니다.

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

Object Caching

웹 애플리케이션 서버에서 자주 참조되는 객체에 대한 캐싱을 할 수 있습니다.

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

6. Regular Maintenance

Update Statistics

특정 테이블에 대한 접근 정보를 주기적으로 최신화하는것은 데이터베이스 엔진에서 실행계획 플로우를 결정하는데에 도움을 줄 수 있습니다.

-- Update statistics for a table
UPDATE STATISTICS table_name;

Data Archiving

더 이상 사용되지 않는 데이터를 삭제하는것으로 데이터베이스 성능에 도움을 줄 수 있습니다.

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

7. Hardware Optimization

Optimize Server Configuration

하드웨어 가용 용량에 기반하여 데이터베이스 서버의 세팅을 변경하는것으로 데이터베이스 성능을 향상 시킬 수 있습니다. (buffer sizes, cache settings, connection limits)

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

USE SSDs

HDD (Hard Disk Drives) 를 사용하는대신 SSD (Solid State Drives) 를 사용하면 데이터베이스 성능을 높일 수 있습니다.

8. Concurrency Control

트랜잭션 격리 수준을 설정하여 데이터베이스 성능을 높일 수 있습니다. 성능과 데이터베이스 일관성 사이의 trade off 를 분석하여 결정합니다. 이는 비즈니스 도메인에 따라 달라질 수 있습니다.

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

9. Connection Pooling

Pooling 을 설정하여 데이터베이스 연결 정보를 재 사용하여 성능을 높일 수 있습니다.

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

10. Database Design

데이터베이스 스키마 설계 단계에서 불필요한 관계를 줄이고 최적의 데이터 타입을 사용하는것으로 성능을 개선할 수 있습니다. (설계의 중요성)

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

11. Monitoring and Profiling

주기적으로 데이터베이스 서버의 시스템 자원을 확인하여 데이터베이스 성능을 측정합니다. (CPU Usage, Memory Usage, query execution times)

SHOW STATUS LIKE 'cpu%';

참고 링크

https://danielfoo.medium.com/11-database-optimization-techniques-97fdbed1b627