클라이밍하는 개발자

1. 데이터베이스 스케줄러 개요

데이터베이스 스케줄러는 특정 시간이나 주기적으로 데이터베이스 작업을 자동으로 실행하는 도구입니다. 이는 데이터 관리, 성능 최적화, 보고서 생성 등 다양한 목적으로 사용됩니다.

1.1 스케줄러의 주요 기능

• 정기적인 데이터 정리 및 아카이빙
• 주기적인 통계 및 보고서 생성
• 데이터베이스 유지보수 작업 자동화
• 데이터 백업 및 복구 프로세스 관리

2. MySQL에서의 스케줄러 구현

MySQL에서는 이벤트 스케줄러를 통해 예약된 작업을 실행할 수 있습니다.

2.1 이벤트 스케줄러 활성화

먼저, MySQL 서버에서 이벤트 스케줄러를 활성화해야 합니다:

SET GLOBAL event_scheduler = ON;

2.2 이벤트 생성 예시

다음은 매일 자정에 30일 이상 된 로그를 삭제하는 이벤트 예시입니다:

DELIMITER //
CREATE EVENT daily_log_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-07-25 00:00:00'
DO
BEGIN
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
DELIMITER ;

2.3 이벤트 관리

이벤트 조회 / 삭제 / 수정

//이벤트 조회
SHOW EVENTS;

//이벤트 삭제
DROP EVENT IF EXISTS daily_log_cleanup;

//이벤트 수정
ALTER EVENT daily_log_cleanup
ON SCHEDULE EVERY 2 DAY
ENABLE;

3. 스케줄러 활용 사례

3.1 주기적인 데이터 집계

매주 월요일 새벽 2시에 주간 판매 통계를 생성하는 예시:

DELIMITER //
CREATE EVENT weekly_sales_summary
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-07-29 02:00:00'
DO
BEGIN
    INSERT INTO sales_summary (week, total_sales)
    SELECT 
        YEARWEEK(order_date) as week,
        SUM(total_amount) as total_sales
    FROM orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
    GROUP BY YEARWEEK(order_date);
END //
DELIMITER ;

3.2 데이터베이스 최적화

DELIMITER //
CREATE EVENT monthly_db_optimization
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-08-01 03:00:00'
DO
BEGIN
    -- 모든 테이블 최적화
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @stmt = CONCAT('OPTIMIZE TABLE ', tbl_name);
        PREPARE stmt FROM @stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

매월 1일 새벽 3시에 데이터베이스 최적화를 수행하는 예시:

1. 인덱스(Index)란 무엇인가?

인덱스는 데이터베이스 테이블의 검색 속도를 향상시키기 위해 사용되는 데이터 구조입니다. 책의 색인과 유사하게, 데이터베이스 인덱스는 특정 열(컬럼)의 값과 해당 레코드의 위치 정보를 저장합니다.

예를 들어, 도서관에서 책을 찾을 때 책의 제목이나 저자별로 정리된 목록을 사용하는 것과 비슷합니다.

2. 인덱스의 장점

2.1 검색 속도 향상

인덱스의 주요 장점은 데이터 검색 속도를 크게 향상시킨다는 점입니다. 인덱스가 없으면 데이터베이스는 모든 레코드를 순차적으로 검색해야 하지만, 인덱스를 사용하면 원하는 데이터를 빠르게 찾을 수 있습니다.

예시 코드 (Node.js와 MySQL 사용):

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database'
});

// 인덱스가 없는 경우
connection.query('SELECT * FROM users WHERE username = ?', ['john'], (err, results) => {
  if (err) throw err;
  console.log(results);
});

// 인덱스 생성
connection.query('CREATE INDEX idx_username ON users (username)', (err) => {
  if (err) throw err;
  console.log('Index created successfully');
});

// 인덱스를 사용한 검색
connection.query('SELECT * FROM users WHERE username = ?', ['john'], (err, results) => {
  if (err) throw err;
  console.log(results);
});

2.2 정렬 및 그룹화 성능 개선

인덱스는 ORDER BY나 GROUP BY 연산의 성능도 향상시킵니다. 이미 정렬된 상태로 데이터를 저장하기 때문에, 추가적인 정렬 작업이 필요하지 않습니다.

3. 인덱스의 단점

3.1 추가 저장 공간 필요

인덱스는 별도의 데이터 구조로 저장되기 때문에 추가적인 저장 공간이 필요합니다. 대규모 데이터베이스의 경우 이는 상당한 양의 저장 공간을 차지할 수 있습니다.

3.2 쓰기 작업 성능 저하

INSERT, UPDATE, DELETE와 같은 쓰기 작업 시 인덱스도 함께 업데이트해야 하므로 이러한 작업의 성능이 저하될 수 있습니다.

예시 코드:

// 인덱스가 있는 테이블에 데이터 삽입
connection.query('INSERT INTO users (username, email) VALUES (?, ?)',
['newuser', 'newuser@example.com'],
(err) => {
  if (err) throw err;
  console.log('User inserted');
});

4. 인덱스 사용 시 주의사항

4.1 적절한 컬럼 선택

모든 컬럼에 인덱스를 생성하는 것은 바람직하지 않습니다. 주로 검색 조건으로 자주 사용되는 컬럼이나 JOIN 연산에 사용되는 컬럼에 인덱스를 생성하는 것이 좋습니다.

4.2 인덱스 관리

데이터베이스의 데이터가 크게 변경되면 인덱스의 효율성이 떨어질 수 있습니다. 주기적으로 인덱스를 재구축하거나 최적화하는 것이 중요합니다.

// 인덱스 재구축
connection.query('ALTER TABLE users DROP INDEX idx_username, ADD INDEX idx_username (username)', (err) => {
  if (err) throw err;
  console.log('Index rebuilt');
});

결론

인덱스는 데이터베이스 쿼리의 성능을 크게 향상시킬 수 있는 강력한 도구입니다.

하지만 무분별한 사용은 오히려 성능 저하를 초래할 수 있으므로, 데이터베이스의 특성과 쿼리 패턴을 고려하여 적절히 사용해야 합니다. 인덱스의 장단점을 잘 이해하고, 필요한 곳에 적절히 적용함으로써 데이터베이스의 전반적인 성능을 최적화할 수 있습니다. 정기적인 모니터링과 관리를 통해 인덱스의 효율성을 유지하는 것도 중요합니다. 이를 통해 더 빠르고 효율적인 데이터베이스 시스템을 구축할 수 있습니다.