My SQL 최적화 가이드 - 1. 최적화

 

https://dev.mysql.com/doc/refman/8.0/en/optimize-overview.html

MySQL :: MySQL 8.0 Reference Manual :: 10.1 Optimization Overview

 

서론

 

쿼리성능 문제와 최적화 같은 성능 이슈는 업무를 하다 보면 자주 언급되는 문제이다. 그렇기 때문에 이를 개선하기 위한 작업을 진행하는것 또한 잦은 일이다.

정해진 자원을 효율적으로 사용하여 비용을 낮추고 사용자들에게 보다 좋은 경험을 제공하기 위해서 이러한 작업들이 필수적인데, 그렇기 때문에 많은 개발자들이 직접 겪고 공유한 다양한 성능개선 가이드와 스토리들은 조금만 검색해 보아도 많이 찾아볼 수 있다.

이러한 글들을 잘 찾아보고 읽어보며 하나씩 적용해 보는 것도 좋지만, 근원적으로 공식 문서를 우선 읽어보는 것 또한 중요하다는 생각이 들었다. Oracle, MS-SQL 등 과 같이 많은 RDBMS가 존재하지만 나는 최근에 My Sql을 이용한 프로젝트를 진행하고 있다. 그래서 My Sql을 정리 대상으로 선택하여 dev.mysql의 공식문서를 찾아서 읽어보고 예시들을 정리해 보기로 하였다.

전체적으로는 'MySQL 8.0 Reference Manual'의 'Optimization' 메뉴를 기준으로 정리를 진행해 보려고 하고,

 

이번글에서는 데이터베이스 수준에서의 최적화와 하드웨어 수준의 최적화를 간략하게 정리하였다.

 

 

 

Optimizing at the Database Level

 

• 테이블 구조 확인 : 테이블의 각 컬럼이 적합한 타입으로 할당되어있는지, 각 테이블이 업무 유형에 적합한 컬럼을 갖고 있는지 확인.
  
  

  -- 비효율적인 설계
  CREATE TABLE orders (
      order_id INT AUTO_INCREMENT PRIMARY KEY,
      customer_id INT,
      status VARCHAR(20),  -- 상태 정보가 자주 변경된다면 해당 컬럼을 별도의 테이블로 분리
      last_updated TIMESTAMP
  );
  
  
  
  -- 개선된 설계: 상태 정보를 별도 테이블로 분리
  CREATE TABLE orders (
      order_id INT AUTO_INCREMENT PRIMARY KEY,
      customer_id INT,
      last_updated TIMESTAMP
  );
  
  CREATE TABLE order_status (
      order_id INT,
      status VARCHAR(20),
      PRIMARY KEY (order_id)
  );

  
  
  
• 인덱스 설계 : 효율적인 쿼리 동작을 위해 인덱스를 적절하게 설계
  
  

  -- 인덱스가 없는 경우: 전체 테이블 스캔이 발생하여 검색 시간이 커짐
  SELECT * FROM employees WHERE department_id = 5;
  
  -- 적절한 인덱스 추가하여
  CREATE INDEX idx_department_id ON employees(department_id);
  
  -- 인덱스 사용으로 빠른 검색이 가능하게 한다
  SELECT * FROM employees WHERE department_id = 5;

  
  
  
• 각 테이블마다 적절한 스토리지 엔진 사용 : InnoDB과 같은 트렌젝션 스토리지 엔진 이나 MyISAM 과 같은 논트렌젝션 스토리지 엔진을 적절히 사용하는 것이 퍼포먼스나 확장성에 중요하다.
  
  

  -- 읽기 중심의 로그 테이블 등에서는 MyISAM을 사용
  CREATE TABLE logs (
      log_id INT AUTO_INCREMENT PRIMARY KEY,
      message TEXT
  ) ENGINE=MyISAM;
  
  
  -- 트렌젝션과 동시성 지원이 필요한 경우 InnoDB를 사용
  CREATE TABLE accounts (
      account_id INT AUTO_INCREMENT PRIMARY KEY,
      balance DECIMAL(10,2)
  ) ENGINE=InnoDB;

  
  
  
• 적절한 행형식 선택 : 특히 압축된 행 형식은 디스크 자원 절약 및 성능에 도움을 줄 수 있다.
  
  

  -- InnoDB 테이블에서 압축 적용
  CREATE TABLE compressed_orders (
      order_id INT AUTO_INCREMENT PRIMARY KEY,
      customer_name VARCHAR(255),
      order_date DATE
  ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; -- ROW_FORMAT=COMPRESSED 사용
  
  
  -- MyISAM 테이블에서 압축 적용
  CREATE TABLE readonly_table (
      id INT AUTO_INCREMENT PRIMARY KEY,
      data TEXT
  ) ENGINE=MyISAM ROW_FORMAT=COMPRESSED; -- ROW_FORMAT=COMPRESSED

  
  
  

 

Optimizing at the Hardware Level

 

• 탐색 최적화를 위해 데이터를 여러 디스크로 분산을 시도할 수 있다.
  

  // 오래된 데이터를 분리하여 특정 범위만 탐색할 수 있게 한다
  CREATE TABLE orders_partitioned (
      order_id INT,
      order_date DATE,
      customer_name VARCHAR(100)
  ) PARTITION BY RANGE (YEAR(order_date)) (
      PARTITION p0 VALUES LESS THAN (2010),
      PARTITION p1 VALUES LESS THAN (2020),
      PARTITION p2 VALUES LESS THAN MAXVALUE
  );

  
  

  # RAID 설정을 사용하여 데이터를 여러 디스크에 분산할 수 있다
  # RAID 0 설정: 병렬로 데이터를 분산하여 읽기/쓰기 성능을 향상시킨다
  mdadm --create /dev/md0 --level=0 --raid-devices=2 /dev/sda /dev/sdb

  
  
  
• 여러 디스크에서 병렬로 데이터를 읽거나 쓰도록 구성하면 성능을 향상시킬 수 있다.
  

  # 병렬 읽기/쓰기 활성화
  # RAID 5를 구성하여 디스크 I/O 병목을 줄임.
  mdadm --create /dev/md0 --level=5 --raid-devices=3 /dev/sda /dev/sdb /dev/sdc

  
  

  -- 쿼리 병렬실행
  SET GLOBAL innodb_parallel_read_threads = 4;

  
  

  -- InnoDB의 플러시 설정을 통해 데이터 쓰기 성능을 위해 동기화 쓰기를 비활성화.
  SET GLOBAL innodb_flush_log_at_trx_commit = 2;

  
  
  
  
• 테이블 크기가 메모리보다 큰 경우 CPU에 병목현상이 발생할 수 있다. 다음과 같은 방법을 고려할 수 있다.
  

  -- 필요한 데이터만 선택하여 CPU 부하를 줄임.
  -- 불필요한 데이터를 제외하고 필요한 열만 선택
  SELECT customer_name, order_date FROM orders WHERE order_date > '2023-01-01';

  
  

  -- 인덱스를 활용하여 테이블 스캔을 줄인다
  CREATE INDEX idx_order_date ON orders(order_date);

  
  

  -- MySQL 스레드 동시설정을 사용한다
  SET GLOBAL innodb_thread_concurrency = 8;

  
  
  
• CPU 캐시에 들어가지 않는 데이터를 요청하면 메인 메모리 대역폭에 병목현상이 발생할 수 있다. 아래 방법들이 고려될 수 있다.
  

  -- 자주 사용되는 데이터를 메모리에서 처리한다
  CREATE TABLE memory_orders (
      order_id INT PRIMARY KEY,
      customer_name VARCHAR(100)
  ) ENGINE=MEMORY;

  
  

  -- 데이터 압축을 통해 메모리 사용량을 줄여 메모리 대역폭에서 병목현상을 방지한다
  CREATE TABLE compressed_table (
      id INT AUTO_INCREMENT PRIMARY KEY,
      data TEXT
  ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;