트리거, 인덱스, 백업, 프로시저

1. 트리거 (TRIGGER): "자동 실행기"

특정 테이블에 이벤트(INSERT, UPDATE, DELETE)가 발생하면 자동으로 실행되는 개체임.

• 주요 용도:
  • 유효성 검사: 데이터가 삽입/수정될 때 올바른 값인지 확인.
  • 로그/감사(Logging/Audit): 데이터가 언제, 누구에 의해 바뀌었는지 기록 보관.
• 주의사항: 트리거 내에서는 트랜잭션 제어 문장(COMMIT, ROLLBACK)을 사용할 수 없음.
• 기본 문법:

  CREATE TRIGGER 트리거이름
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE} -- 실행 시점과 사건
  ON 테이블이름
  FOR EACH ROW -- 행마다 실행
  BEGIN
      -- 실행할 SQL 문장
  END;
  

---

2. 인덱스 (INDEX): "빠른 검색을 위한 색인"

데이터 조회 속도를 높이기 위해 별도로 생성하는 자료구조임.

• 특징:
  • 장점: 조회(SELECT) 속도가 비약적으로 향상되고 전체 시스템 성능이 좋아짐.
  • 단점: 삽입(INSERT), 삭제(DELETE), 수정(UPDATE) 시 인덱스 트리(B-Tree)를 재구성(분할/합병)해야 하므로 작업이 무거워짐.
• 효율적인 사용: 전체 데이터의 2%~4% 정도를 검색할 때 가장 효율적임.
• 엔진별 특성:
  • MyISAM: 인덱스가 별도 파일로 관리되어 조회에 유리.
  • InnoDB: 삽입/삭제 시 트랜잭션 관리가 강력함.

---

 

3. 백업 및 복원 (Backup & Restore)

데이터를 안전하게 보관하고 사고 시 복구하는 CLI 기반 명령어임.

📂 백업 (mysqldump 사용)

• 전체 DB: mysqldump -u아이디 -p패스워드 --all-databases > 파일명.sql
• 특정 DB: mysqldump -u아이디 -p패스워드 DB명 > 파일명.sql
• 원격 백업: mysqldump -u아이디 -p패스워드 -h IP주소 DB명 > 파일명.sql

📂 복원 (mysql 사용)

• 전체 복원: mysql -u아이디 -p패스워드 < 파일명.sql
• 특정 DB 복원: mysql -u아이디 -p패스워드 DB명 < 파일명.sql

---

4. 스토어드 프로시저 (Stored Procedure)

1. 개요

• 정의: 자주 사용하는 SQL 문장들을 하나로 묶어 이름(함수처럼)을 붙여두고 호출하는 개체임.
• 장점:
  1. 성능 향상: 한 번 컴파일되면 DB 메모리에 캐싱되어 빠르게 실행됨.
  2. 보안: 테이블 구조를 몰라도 프로시저 실행 권한만 있으면 데이터 조작 가능.
  3. 네트워크 부하 감소: 여러 줄의 쿼리를 보낼 필요 없이 CALL 한 줄로 끝남.

2. 프로시저 생성 및 호출 문법

구분자(DELIMITER)를 바꾸는 이유는 프로시저 내부의 ;을 문장의 끝이 아닌 프로시저의 일부로 인식시키기 위함임.

-- 1. 기존에 있으면 삭제
DROP PROCEDURE IF EXISTS myproc;

-- 2. 프로시저 생성 (구분자 변경)
DELIMITER //
CREATE PROCEDURE myproc
    (IN _userid CHAR(15), IN _name VARCHAR(20), _birthyear int, 
     IN _addr CHAR(10), IN _mobile char(11), IN _mdate date)
BEGIN
    -- 실행할 로직 (DML 등)
    INSERT INTO usertbl VALUES(_userid, _name, _birthyear, _addr, _mobile, _mdate);
END //
DELIMITER ;

-- 3. 프로시저 실행 (호출)
CALL myproc('bsz', '배수지', 1994, '광주', '01022223333', '1994-01-01');

---

3. 외부 언어(Python 등)에서의 실행 원리

파이썬 같은 프로그래밍 언어에서 프로시저를 다룰 때의 핵심 규칙임.

• 실행 메서드: cursor.callproc('프로시저명', (매개변수들)) 호출.
• 매개변수 전달: 입력값은 반드시 튜플(Tuple) 형태로 전달해야 함.
• 트랜잭션 처리: 프로시저 내부에서 INSERT, UPDATE 등을 수행한다면, 호출 후 반드시 연결 객체에서 commit() 또는 rollback()을 실행해줘야 실제 DB에 반영됨.

 

---

5. 기타 프로그래밍 개념

• 절차적 프로그래밍: SQL에서도 변수, 조건문, 반복문을 사용하는 절차적 로직 처리가 가능함.
• 오류 코드: 에러 번호(1000~1981 등)를 통해 문제 원인을 파악하고 예외 처리를 수행함.
• 동적 SQL: 상황에 따라 SQL 문장을 문자열로 조립하여 실행하는 방식.