[DATABASE] VIEW•INDEX•TRIGGER

성장일기·2024년 7월 2일

[SWCAMP] DB

목록 보기

7/14

VIEW

VIEW 생성

VIEW 생성 후 조회

SELECT * FROM tbl_menu;

-- VIEW 생성
CREATE VIEW hansik AS
SELECT 
       menu_code 
     , menu_name
     , menu_price
     , category_code
     , orderable_status
  FROM tbl_menu 
 WHERE category_code = 4;

-- 생성된 VIEW 조회
SELECT * FROM hansik;

베이스 테이블의 정보가 변경되면 VIEW의 결과도 같이 변경된다.

INSERT 
  INTO tbl_menu 
VALUES (null, '식혜맛국밥', 5500, 4, 'Y');
SELECT * FROM hansik;

VIEW를 통한 DML

VIEW를 통한 DML 작업은 베이스 테이블에도 영향을 주게 된다.

VIEW를 통한 INSERT

VIEW는 AUTO_INCREMENT가 없으므로 pk 컬럼의 값을 지정해 주어야 한다.

VIEW를 통한 INSERT 이후 VIEW 조회 및 베이스 테이블 조회

-- INSERT INTO hansik VALUES (null, '식혜맛국밥', 5500, 4, 'Y');    -- 에러 발생
INSERT 
  INTO hansik
VALUES (99, '수정과맛국밥', 5500, 4, 'Y');   
SELECT * FROM hansik;
SELECT * FROM tbl_menu;

VIEW를 통한 UPDATE

VIEW를 통한 UPDATE 이후 VIEW 조회 및 베이스 테이블 조회

UPDATE hansik
   SET menu_name = '버터맛국밥', menu_price = 5700 
 WHERE menu_code = 99;
SELECT * FROM hansik;
SELECT * FROM tbl_menu;

VIEW를 통한 DELETE

VIEW를 통한 DELETE 이후 VIEW 조회 및 베이스 테이블 조회

DELETE FROM hansik WHERE menu_code = 99;
SELECT * FROM hansik;
SELECT * FROM tbl_menu;

VIEW로 DML 명령어로 조작이 불가능한 경우

사용된 SUBQUERY에 따라 DMB 명령어로 조작이 불가능할 수 있다.
1. 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
2. 뷰에 포함되지 않은 컬럼 중에 베이스가 되는 테이블 컬럼이 NOT NULL 제약조건이 지정된 경우t
3. 산술 표현식이 정의된 경우
4. JOIN을 이용해 여러 테이블을 연결한 경우
5. DISTINCT를 포함한 경우
6. 그룹함수나 GROUP BY 절을 포함한 경우

VIEW 삭제

VIEW에 쓰인 SUBQUERY 안에 연산 결과 컬럼도 사용 가능하다.
```
DROP VIEW hansik;
```
실행결과

OR REPLACE 옵션

테이블을 DROP하지 않고 기존의 VIEW를 새로운 VIEW로 쉽게 다룰 수 있다.

CREATE OR REPLACE VIEW hansik AS
SELECT 
       menu_code AS '메뉴코드'
     , menu_name '메뉴명'
     , category_name '카테고리명'
  FROM tbl_menu a
  JOIN tbl_category b ON a.category_code = b.category_code
 WHERE b.category_name = '한식';

SELECT * FROM hansik;

INDEX

인덱스 생성

PRIMARY KEY 제약조건을 지닌 기본 테이블 생성 후 기본 데이터 INSERT 후 조회

CREATE TABLE phone (
    phone_code INT PRIMARY KEY,
    phone_name VARCHAR(100),
    phone_price DECIMAL(10, 2)
);

INSERT 
  INTO phone (phone_code , phone_name , phone_price )
VALUES 
(1, 'galaxyS23', 1200000),
(2, 'iPhone14pro', 1433000),
(3, 'galaxyZfold3', 1730000);

SELECT * FROM phone;

인덱스가 없는 컬럼을 WHERE절의 조건으로 활용한 조회 진행 시 EXPLAIN으로 쿼리 실행 계획 확인
```
EXPLAIN SELECT * FROM phone WHERE phone_name = 'galaxyS23';
```
실행결과
phone_name 컬럼에 idx_name이라는 이름의 인덱스 생성 후 확인
```
CREATE INDEX idx_name ON phone (phone_name);
```
실행결과
2개 이상의 컬럼을 한번에 하나의 인덱스로 설정해서 생성할 수 있다.

복합 인덱스 생성

CREATE INDEX idx_name_price ON phone (phone_name, phone_price);

SHOW INDEX FROM phone;

인덱스 활용

테이블에 인덱스가 설정 된 컬럼을 활용해 조회를 진행
```
SELECT * FROM phone WHERE phone_name = 'iPhone14pro';
```
실행결과
EXPLAIN 명령어로 쿼리 실행 계획 확인(인덱스 사용 여부 및 사용 컬럼 확인)
```
EXPLAIN SELECT * FROM phone WHERE phone_name = 'iPhone14pro';
```
실행결과
인덱스 최적화(재구성)
- 인덱스 최적화(재구성)은 인덱스가 파편화되었거나, 데이터의 대부분이 변경된 경우에 유용하다.
- 이는 인덱스의 성능을 개선하고, 디스크 공간을 더 효율적으로 사용하게 해준다.
- 단, 인덱스를 재구성하는 동안 해당 테이블은 잠길 수 있으므로, 이 작업은 주의해서 수행해야 한다.
- 'ALTER TABLE' 명령어를 사용해서 재구성한다.
```
ALTER TABLE phone DROP INDEX idx_name;
ALTER TABLE phone ADD INDEX idx_name(phone_name);
```
  실행결과
- 또한, MySQL의 InnoDB 엔진을 사용하는 경우에는 OPTIMIZE TABLE 명령을 사용하여 테이블과 인덱스를 최적화할 수도 있다.
```
OPTIMIZE TABLE phone;
```
  실행결과

인덱스 삭제

인덱스 삭제 후 테이블에 존재하는 인덱스 확인
```
DROP INDEX idx_name ON phone;
SHOW INDEX FROM phone;
```

TRIGGER

트리거의 종류
- BEFORE 트리거
  - 이벤트가 발생하기 전에 실행되며 데이터의 유효성 검사나 변형에 주로 사용된다.
- AFTER 트리거
  - 이벤트가 발생한 후에 실행되며 로깅, 알림 전송 등의 작업에 적합하다.
트리거 작성법

DELIMITER // 

CREATE OR REPLACE TRIGGER [트리거명]
    BEFORE|AFTER [이벤트 타입]
    ON [테이블명]
    FOR EACH ROW
BEGIN
END//

DELIMITER ;

트리거 생성

주문 메뉴(tbl_order_menu) 테이블에 INSERT가 되고 나서 주문(tbl_order) 테이블의 총 합계가 UPDATE 될 수 있는 트리거 생성

DELIMITER //

CREATE OR REPLACE TRIGGER after_order_menu_insert
    AFTER INSERT
    ON tbl_order_menu
    FOR EACH ROW
BEGIN
    UPDATE tbl_order
    SET total_order_price = total_order_price + NEW.order_amount * (SELECT menu_price FROM tbl_menu WHERE menu_code = NEW.menu_code)
    WHERE order_code = NEW.order_code;
END//

DELIMITER ;

트리거 활용

주문 테이블과 주문 메뉴 테이블에 INSERT 작업 이후 주문 테이블의 총 합계가 UPDATE 되는 것 확인

-- 주문 테이블 INSERT
INSERT
  INTO tbl_order
(
  order_code
, order_date
, order_time
, total_order_price
)
VALUES
(
  NULL
, CONCAT(CAST(YEAR(NOW()) AS VARCHAR(4))
	    , CAST(LPAD(MONTH(NOW()), 2, 0) AS VARCHAR(2))
	    , CAST(LPAD(DAY(NOW()), 2, 0) AS VARCHAR(2)))
, CONCAT(CAST(LPAD(HOUR(NOW()), 2, 0) AS VARCHAR(2))
       , CAST(LPAD(MINUTE(NOW()), 2, 0) AS VARCHAR(2))
       , CAST(LPAD(SECOND(NOW()), 2, 0) AS VARCHAR(2)))
, 0			
);

-- 주문 메뉴 테이블 INSERT 1
INSERT
  INTO tbl_order_menu
(
  order_code
, menu_code
, order_amount
)
VALUES
(
  1
, 3
, 2
);

-- 주문 메뉴 테이블 INSERT 2
INSERT
  INTO tbl_order_menu
(
  order_code
, menu_code
, order_amount
)
VALUES
(
  1
, 6
, 3
);

SELECT * FROM tbl_order;
SELECT * FROM tbl_order_menu;

-- 다시 되돌려 테스트 해보고자 할 경우
-- 1) rollback하기
ROLLBACK;
-- 2) 기존 데이터 지우기
DELETE FROM tbl_order WHERE 1 = 1;
DELETE FROM tbl_order_menu WHERE 1 = 1;

-- 이후 AUTO_INCREMENT도 다시 초기화 해 준다.
ALTER TABLE tbl_order AUTO_INCREMENT = 1;