클라이밍하는 개발자

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. 트리거 (Trigger)

1.1 트리거란?

트리거는 데이터베이스에서 특정 이벤트(삽입, 수정, 삭제 등)가 발생했을 때 자동으로 실행되는 프로그래밍 로직입니다. 이는 데이터의 무결성을 유지하고, 복잡한 비즈니스 로직을 구현하는 데 도움을 줍니다.

1.2 트리거의 주요 용도

• 데이터 유효성 검사
• 자동 값 생성 또는 수정
• 관련 테이블 동기화
• 감사(Audit) 로그 생성

1.3 MySQL에서의 트리거 예시

다음은 새 주문이 추가될 때마다 재고를 자동으로 감소시키는 트리거 예시입니다:

DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END //
DELIMITER ;

이 트리거는 orders 테이블에 새 주문이 삽입된 후 실행되며, products 테이블의 해당 상품 재고를 감소시킵니다.

2. 튜닝 (Tuning)

2.1 튜닝이란?

튜닝은 시스템이나 애플리케이션의 성능을 최적화하는 과정입니다. 데이터베이스 튜닝, 쿼리 튜닝, 서버 튜닝 등 다양한 영역에서 이루어질 수 있습니다.

2.2 튜닝의 주요 영역

• 데이터베이스 튜닝
• 쿼리 최적화
• 서버 구성 튜닝
• 애플리케이션 코드 최적화

2.3 MySQL 쿼리 튜닝 예시

최적화 전 쿼리:

SELECT * FROM users 
WHERE created_at > '2024-01-01' 
AND status = 'active';

최적화 후 쿼리:

SELECT id, name, email FROM users 
WHERE created_at > '2024-01-01' 
AND status = 'active'
LIMIT 1000;

이 예시에서는 다음과 같은 최적화를 수행했습니다:

1. 필요한 컬럼만 선택 (SELECT * 대신)
2. 결과 제한 (LIMIT 사용)
3. 인덱스 사용 가능성 향상 (created_at과 status 컬럼에 복합 인덱스 생성 고려)

3. 트리거와 튜닝의 연관성

트리거와 튜닝은 밀접한 관련이 있습니다. 트리거는 데이터베이스 작업을 자동화하고 일관성을 유지하는 데 도움이 되지만, 과도한 사용은 성능 저하를 초래할 수 있습니다. 따라서 트리거 설계 시 성능을 고려한 튜닝이 필요합니다.

3.1 트리거 튜닝 팁

1. 필요한 경우에만 트리거 사용
2. 트리거 내 로직을 최대한 간단하게 유지
3. 트리거 실행 시점(BEFORE/AFTER) 적절히 선택
4. 트리거 내에서 무거운 쿼리 피하기

4. 결론

트리거와 튜닝은 개발자가 반드시 이해하고 적절히 활용해야 할 중요한 개념입니다. 트리거를 통해 데이터의 일관성과 무결성을 유지하면서도, 적절한 튜닝을 통해 시스템의 전반적인 성능을 최적화할 수 있습니다.

트리거 사용 시에는 그 필요성을 신중히 검토하고, 성능에 미치는 영향을 고려해야 합니다. 튜닝은 지속적인 과정으로, 시스템의 변화와 사용 패턴에 따라 계속해서 수행되어야 합니다.

효과적인 트리거 설계와 지속적인 성능 튜닝을 통해, 개발자는 더 안정적이고 효율적인 시스템을 구축할 수 있습니다. 이는 결과적으로 사용자 경험 향상과 비즈니스 목표 달성에 크게 기여할 것입니다.

1. 프로시저란 무엇인가?

프로시저(Procedure)는 데이터베이스에서 실행할 수 있는 저장된 프로그램 단위입니다. 이는 SQL 문들의 집합으로, 복잡한 처리를 모듈화하여 재사용성을 높이고 편리하게 사용할 수 있게 해줍니다.

1.1 프로시저의 특징

• 여러 SQL 문을 하나의 단위로 실행
• 매개변수를 받아 동적으로 동작 가능
• 데이터베이스 내에 저장되어 필요할 때 호출하여 사용
• 보안성 향상 및 네트워크 트래픽 감소

2. 프로시저의 장점

2.1 성능 향상

프로시저는 미리 컴파일되어 저장되므로, 실행 시 빠른 처리가 가능합니다.

2.2 재사용성

한 번 작성된 프로시저는 여러 곳에서 반복적으로 사용할 수 있습니다.

2.3 보안 강화

데이터베이스 객체에 대한 직접적인 접근을 제한하고, 프로시저를 통해서만 데이터 조작을 허용할 수 있습니다.

3. 프로시저 작성 및 사용 예시

3.1 MySQL에서 프로시저 생성 예시

DELIMITER //

CREATE PROCEDURE GetEmployeeCount(IN departmentId INT, OUT employeeCount INT)
BEGIN
    SELECT COUNT(*) INTO employeeCount 
    FROM employees 
    WHERE department_id = departmentId;
END //

DELIMITER ;

이 프로시저는 특정 부서의 직원 수를 계산합니다.

3.2 프로시저 호출

CALL GetEmployeeCount(1, @count);
SELECT @count AS 'Employee Count';

3.3 Node.js에서 프로시저 사용 예시

const mysql = require('mysql2/promise');

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

  try {
    const [results] = await connection.execute('CALL GetEmployeeCount(?, @count)', [1]);
    const [countResult] = await connection.execute('SELECT @count AS count');
    console.log('Employee Count:', countResult[0].count);
  } catch (error) {
    console.error('Error calling procedure:', error);
  } finally {
    await connection.end();
  }
}

callProcedure();

4. 프로시저의 고급 기능

4.1 조건문 사용

프로시저 내에서 IF-ELSE 문을 사용하여 조건에 따른 로직 실행이 가능합니다.

DELIMITER //

CREATE PROCEDURE CheckBalance(IN accountId INT)
BEGIN
    DECLARE balance DECIMAL(10,2);
    
    SELECT account_balance INTO balance
    FROM accounts
    WHERE id = accountId;
    
    IF balance < 0 THEN
        SELECT 'Account is overdrawn' AS message;
    ELSE
        SELECT 'Account balance is positive' AS message;
    END IF;
END //

DELIMITER ;

4.2 루프 사용

WHILE, REPEAT, LOOP 등을 사용하여 반복 작업을 수행할 수 있습니다.

DELIMITER //

CREATE PROCEDURE GenerateNumbers(IN max INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= max DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

5. 프로시저 사용 시 주의사항

5.1 과도한 사용 지양

모든 로직을 프로시저로 구현하면 애플리케이션의 유연성이 떨어질 수 있습니다.

5.2 버전 관리의 어려움

데이터베이스에 저장되는 프로시저는 일반 코드에 비해 버전 관리가 어려울 수 있습니다.

5.3 디버깅의 복잡성

프로시저 내부의 로직을 디버깅하는 것은 일반 애플리케이션 코드보다 복잡할 수 있습니다.

결론

프로시저는 데이터베이스 프로그래밍에서 강력하고 유용한 도구입니다. 복잡한 비즈니스 로직을 데이터베이스 레벨에서 처리할 수 있게 해주며, 성능 향상과 보안 강화에 기여합니다. 그러나 과도한 사용은 피하고, 적절한 상황에서 균형있게 사용하는 것이 중요합니다.

프로시저를 효과적으로 활용하면 데이터 중심의 작업을 효율적으로 처리할 수 있으며, 애플리케이션과 데이터베이스 간의 결합도를 낮출 수 있습니다. 하지만 항상 유지보수성과 확장성을 고려하여 설계해야 합니다. 프로시저는 데이터베이스 개발자의 강력한 도구이지만, 그 사용에는 신중한 접근이 필요합니다.