학원 day35. 무결성제약조건, 인덱스, 트랜잭션

- MODEL

P는 PRIMARY KEY 제약조건

F는 FOREIGN KEY 제약조건

U는 UNIQUE 제약조건

빨간점 NOT NULL

PF, PF는 두 컬럼 합쳐서 PRIMARY KEY 제약조건이라는 의미이다.

 

테이블을 생성할때는 부모테이블을 먼저 만들고 자식테이블을 정의해야 한다.

테이블을 삭제할때는 자식레코드를 먼저 삭제하고 부모테이블을 삭제해야 한다.

 

외래키가 있는 쪽이 자식테이블, 외래키가 참조하고 있는 쪽이 부모테이블이다. 

테이블끼리 조인할 때 외래키와 참조키를 조인한다.

참조할 수 있는 키는 PRIMARY KEY나 UNIQUE만 가능하다. 

첫번째칸은 PRIMARY KEY나 UNIQUE에 대한 정보가

두번쨰칸은 FOREIGN KEY에 대한 정보가

세번째칸은 INDEX에 대한 정보가 들어있다.

 

- 무결성 제약조건 실습

--------------------------------------------------------------------------------
-- 무결성 제약조건과 INSERT, UPDATE, DELETE
-- * INSERT, UPDATE, DELETE 작업을 수행할 때마다 무결성 제약조건 위배여부를 반드시 검증한다.
--------------------------------------------------------------------------------

-- NOT NULL 무결성 제약조건 위배 : NOT NULL 제약조건이 정의된 USER_PASSWORD에 NULL이 삽입되는 경우
-- ORA-01400: NULL을 ("HR"."SAMPLE_USERS"."USER_PASSWORD") 안에 삽입할 수 없습니다 (사용자계정(스키마). 테이블명. 컬럼명)
INSERT INTO SAMPLE_USERS (USER_ID, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES ('HONG', '홍길동', 'HONG@GMAIL.COM', '남');

-- UNIQUE 무결성 제약조건 위배 : UNIQUE 제약조건이 정의된 USER_EMAIL에 동일한 이메일 주소가 삽입되는 경우
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES ('hong1', 'zxcv1234', '홍길동1', 'hong1@gmail.com', '남');
-- ORA-00001: 무결성 제약 조건(HR.USERS_EMAIL_UK)에 위배됩니다 (사용자계정.제약조건별칭)
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES ('hong2', 'zxcv1234', '홍길동1', 'hong1@gmail.com', '남');

-- ORA-00001: 무결성 제약 조건(HR.SYS_C008381)에 위배됩니다 (무결성 제약조건 별칭을 정해주지 않았을 때)
-- 제약조건 별칭을 적지 않으면 어느 테이블의 제약조건을 위배했는지 찾기 어렵다. 별칭을 적어주면 디버깅할 때 유리하다.
INSERT INTO SALARY_GRADES (SALARY_GRADE, MIN_SALARY, MAX_SALARY) VALUES ('A', 0, 1000);

-- PRIMARY KEY 무결성 제약 조건 위배 : PRIMARY KEY 제약조건이 정의된 USER_ID에 동일한 아이디값이 삽입되는 경우
-- ORA-00001: 무결성 제약 조건(HR.USERS_ID_PK)에 위배됩니다
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES ('hong1', 'zxcv1234', '홍길동1', 'hong1@gmail.com', '남');

-- CHECK 무결성 제약조건 위배 : CHECK 무결성 제약조건이 정의된 USER_GENDER에 '남', '여' 외의 값이 삽입되는 경우
-- ORA-02290: 체크 제약조건(HR.USERS_GENDER_CK)이 위배되었습니다
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES ('hong2', 'zxcv1234', '홍길동2', 'hong2@gmail.com', '곰');

 

-- 제약조건이 포함된 장바구니 테이블 생성하기
-- 제약조건
--      사용자 아이디는 SAMPLE_USERS 테이블의 USER_ID컬럼의 값만 허용된다.
--      상품번호는 SAMPLE_PRODUCTS 테이블의 PRODUCT_NO컬럼의 값만 허용된다.
--      사용자아이디와 상품번호를 조합한 값이 이 테이블의 행을 대표하는 값이다.
CREATE TABLE SAMPLE_CART_ITEMS (
    USER_ID VARCHAR2(20) CONSTRAINT CART_USER_ID_FK REFERENCES SAMPLE_USERS (USER_ID),
    PRODUCT_NO NUMBER(8),
    ITEM_AMOUNT NUMBER(3),
    ITEM_CREATED_DATE DATE DEFAULT SYSDATE,
    ITEM_UPDATED_DATE DATE DEFAULT SYSDATE,
    
    CONSTRAINT CART_PRODUCT_NO_FK FOREIGN KEY (PRODUCT_NO) REFERENCES SAMPLE_PRODUCTS (PRODUCT_NO),
    CONSTRAINT CART_ITEM_PK PRIMARY KEY (USER_ID, PRODUCT_NO)
);

-- 외래키 제약조건 테스트를 위한 샘플 데이터 등록
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES('kim', 'zxcv1234', '김유신', 'kim@naver.com', '남');
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES('kang', 'zxcv1234', '강감찬', 'kang@naver.com', '남');
INSERT INTO SAMPLE_USERS (USER_ID, USER_PASSWORD, USER_NAME, USER_EMAIL, USER_GENDER)
VALUES('ryu', 'zxcv1234', '류관순', 'ryu@naver.com', '여');

-- 외래키 제약조건 테스트를 위한 샘플 데이터 등록
INSERT INTO SAMPLE_PRODUCTS (PRODUCT_NO, PRODUCT_NAME, PRODUCT_MAKER, PRODUCT_PRICE, PRODUCT_DISCOUNT_RATE, PRODUCT_STOCK)
VALUES (SAMPLE_PRODUCTS_SEQ.NEXTVAL, '아이폰 14', '애플', 1000000, 0, 10);
INSERT INTO SAMPLE_PRODUCTS (PRODUCT_NO, PRODUCT_NAME, PRODUCT_MAKER, PRODUCT_PRICE, PRODUCT_DISCOUNT_RATE, PRODUCT_STOCK)
VALUES(SAMPLE_PRODUCTS_SEQ.NEXTVAL, '아이폰 14 프로 맥스', '애플', 1700000, 0, 10);
INSERT INTO SAMPLE_PRODUCTS (PRODUCT_NO, PRODUCT_NAME, PRODUCT_MAKER, PRODUCT_PRICE, PRODUCT_DISCOUNT_RATE, PRODUCT_STOCK)
VALUES(SAMPLE_PRODUCTS_SEQ.NEXTVAL, '맥북 프로', '애플', 2700000, 0, 5);

-- FOREIGN KEY 제약조건 위배 : 외래키 제약조건이 정의된 PRODUCT_NO에는 SAMPLE_PRODUCTS의 PRODUCT_NO 컬럼에 있는 값만 삽입가능하다.
-- ORA-02291: 무결성 제약조건(HR.CART_PRODUCT_NO_FK)이 위배되었습니다- 부모 키가 없습니다
INSERT INTO SAMPLE_CART_ITEMS(USER_ID, PRODUCT_NO, ITEM_AMOUNT)
VALUES('kim', 100, 1);
-- ORA-02291: 무결성 제약조건(HR.CART_PRODUCT_NO_FK)이 위배되었습니다- 부모 키가 없습니다
INSERT INTO SAMPLE_CART_ITEMS(USER_ID, PRODUCT_NO, ITEM_AMOUNT)
VALUES('kim123', 328, 1);

INSERT INTO SAMPLE_CART_ITEMS(USER_ID, PRODUCT_NO, ITEM_AMOUNT)
VALUES('hong1', 318, 1);
INSERT INTO SAMPLE_CART_ITEMS(USER_ID, PRODUCT_NO, ITEM_AMOUNT)
VALUES('kim', 328, 1);
INSERT INTO SAMPLE_CART_ITEMS(USER_ID, PRODUCT_NO, ITEM_AMOUNT)
VALUES('kang', 338, 5);

-- SAMPLE_PRODUCTS의 상품 정보 삭제하기
-- SAMPLE_CART_ITEMS 테이블이 PRODUCT_NO는 SAMPLE_PRODUCTS의 PRODUCT_NO 컬럼의 값을 참조한다.
-- SAMPLE_CART_ITEMS의 PRODUCT_NO 컬럼에 상품번호가 존재하는 상품정보는 SAMPLE_PRODUCTS에서 그 상품정보를 삭제할 수 없다.
-- ORA-02292: 무결성 제약조건(HR.CART_PRODUCT_NO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
DELETE FROM SAMPLE_PRODUCTS
WHERE PRODUCT_NO = 318;

-- SAMPLE_USERS의 사용자정보 삭제하기
-- ORA-02292: 무결성 제약조건(HR.CART_USER_ID_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
DELETE FROM SAMPLE_USERS
WHERE USER_ID = 'kang';

-- SAMPLE_USERS의 사용자정보 변경하기
-- ORA-02292: 무결성 제약조건(HR.CART_USER_ID_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
UPDATE SAMPLE_USERS
SET
    USER_ID = 'hong'
WHERE USER_ID = 'hong1';

---

인덱스 (색인) 

 

B-tree 인덱스

B-tree인덱스는 관계형 데이터베이스에서 가장 일반적으로 사용되는 인덱스이다.

루트 블록과 브랜치 블록, 리프 블록으로 구성되어 있다.

밸런스드 트리 형태이다.

데이터베이스에서 데이터를 빨리 찾기 위해서 이러한 형태로 되어 있다.

ROWID
- 테이블에서 행의 고유주소를 나타내는 64진수 숫자다. (영어대소문자+숫자+(+,/) 총 64개)
- 구성
  AAAstG + AAH + AAAAG1 + AAA = 오브젝트번호 + 파일번호 + 블록번호 + 데이터번호
  AAAstB + AAH + AAAAGF + AAA
      오브젝트번호 : 객체(테이블)마다 고유한 번호다.
      파일번호 : 해당 데이터가 위치하고 있는 테이블 스페이스의 파일번호(.DBF)다.
      블록번호 : 파일내부의 블록번호다.
      데이터번호 : 데이터가 저장되어 있는 데이터 디렉토리의 슬롯번호다.

-> ROWID를 알면 더 쉽고 빠르게 정보를 얻을 수 있다.

 

인덱스(색인) : 데이터 검색 속도를 향상시키기 위해서 사용되는 데이터베이스 객체다.

 

인덱스 생성
  - 자동 생성
         테이블을 정의할 때 PRIMARY KEY, UNIQUE 제약조건이 정의된 컬럼의 값들은 자동으로 인덱스가 생성된다.
  - 수동 생성
         사용자의 행 조회 성능을 향상시키기 위해서 특정 컬럼을 대상으로 직접 인덱스(색인)을 생성하는 것
         CREATE INDEX 명령어를 사용한다.

-- EMPLOYEES 테이블의 FIRST_NAME컬럼에 대해서 인덱스를 생성한다.
CREATE INDEX EMP_FIRST_NAME_IDX
ON EMPLOYEES (FIRST_NAME);

 

인덱스 생성이 필요한 경우
    * WHERE절에 조회 조건으로 자주 사용되는 컬럼
    * 컬럼의 값이 매우 다양한 값을 포함하고 있는 컬럼 
    * 데이터가 매우 많은 테이블을 대상으로 조회작업을 수행했을 때, 대부분의 조회작업에서 전체 데이터의 2%~4% 미만의 데이터가 조회되는 컬럼

 

인덱스 생성이 필요하지 않는 경우
     * 테이블에 저장된 데이터가 적은 경우
     * 테이블의 데이터가 자주 갱신되는 경우 (오라클의 경우, 인덱스가 BalancedTree방식임. 데이터가 변경되면 트리도 갱신된다. 데이터가 트리에 고루 분포되도록 관리되고 있음.)
     * 인덱스가 생성된 컬럼이 표현식의 일부로 사용되는 경우 (인덱스는 무조건 같은값만 찾기 때문에 인덱스가 있어도 활용 못한다.)
     * WHERE절의 조회 조건으로 자주 사용되지 않는 경우
     * 대부분의 조회작업에서 전체 데이터의 2%~4%이상이 조회되는 경우

 

+) 인덱스는 테이블이 삭제되면 같이 삭제된다.

    테이블과는 별도의 저장소에 따로 관리되고 있다. 

 

실행 계획(Execute Plan) : 사용자가 SQL을 실행해서 데이터를 조회할 때 옵티마이저가 수립하는 작업 절차를 말한다.
     * 옵티마이저(Optimizer)
       : 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 경로를 생성하는 DBMS의 핵심 엔진이다.

3번째 버튼 클릭시 계획 설명이라는 창이 뜬다.

인덱스가 설정된 컬럼 - cost가 0

인덱스가 설정되지 않은 컬럼, full로 찾음 - cost가 3 (cost가 높을수록 성능이 떨어짐)

+) SQL을 전달할 때 ?에 직접 값을 넣어서 전달하지 않는 이유도 옵티마이저와 관련이 있다.

값을 넣으면 값이 바뀔때마다 실행계획을 계속 세워야해서 성능이 떨어진다.

따라서 자주 값이 바뀌는 건 실행계획이 달라지지 않도록 ?을 작성한다.

?가 적혀있는 쿼리로 실행계획을 작성하고 실행하는 순간에 ?에다가 값을 넣어서 실행한다.

PreparedStatement는 쿼리랑 값이랑 따로 전달한다.

실행계획을 다시 짤 필요가 없으니까 실행성능이 좋다.

 

+) 대용량의 데이터를 조회할 때는 오라클 힌트를 사용할 수 있다. 

옵티마이저에게 sql문 실행을 위한 데이터를 스캐닝하는 경로, 조인하는 방법 등을 알려주기 위해 작성하는 것.

 

---

트랜잭션

여러번의 데이터베이스 액세스 작업을 하나의 논리적인 작업 단위로 구성하는 것 

트랜잭션 처리 대상 SQL 명령어 : INSERT, UPDATE, DELETE
트랜잭션의 논리적인 작업단위
예시) 주문작업
  1. 주문정보 저장                      - INSERT
  2. 주문상품 정보 저장                  - INSERT
  3. 결재정보 저장                      - INSERT
  4. 배송정보 저장                      - INSERT
  5. 상품재고 현황 변경                  - UPDATE
  6. 고객의 적립포인트 변경               - UPDATE
  7. 고객의 적립포인트 변경 이력정보 저장  - INSERT
 * 주문작업은 7번의 데이터베이스 변경(INSERT, UPDATE, DELETE) 작업으로 구성되어 있다.
 * 주문작업의 논리적인 작업단위는 5번의 INSERT작업과 2번의 UPDATE 작업으로 구성되어 있다.
 * 주문작업은 위의 7번의 데이터베이스 액세스 작업 중 하나라도 오류가 발생하면 주문작업이 완료되지 못한 것이다.
 * 논리적인 작업단위로 묶여진 7번의 데이터베이스 엑세스 작업은 항상 ALL OR NOTHING의 2가지 처리상태가 존재한다.
 * 논리적인 작업단위로 묶여진 데이터베이스 엑세스 작업에 대한 부분적인 성공을 허용하지 않는다. (데이터의 일관성이 깨지기 때문)

트랜잭션 처리 명령어
- COMMIT
      트랜잭션(논리적인 작업단위) 내의 모든 DML(INSERT, UPDATE, DELETE) 실행결과를 데이터베이스에 영구적으로 반영시킨다.
- ROLLBACK
      트랜잭션(논리적인 작업단위) 내의 모든 DML(INSERT, UPDATE, DELETE) 실행결과의 데이터베이스 반영을 전부 취소시킨다.

트랜잭션(논리적인 작업 단위)의 시작과 종료
-  트랜잭션의 시작
      첫번째 DML 명령이 실행될 때 새 트랜잭션이 자동으로 시작된다.
-  트랜잭션의 종료
      COMMIT/ROLLBACK 명령이 실행될 때 기존 트랜잭션이 종료된다.
      COMMIT/ROLLBACK 명령은 새로운 트랜잭션을 시작시킨다.

커밋하기 전까지는 데이터베이스에 반영 안되고 메모리에만 INSERT, UPDATE되는 것임.

커밋, 롤백 명령을 통해 트랜잭션을 명시적으로 시작하고 종료함.

스프링에서는 선언적 트랜잭션을 처리해주기 때문에 커밋, 롤백을 써주지 않아도 트랜잭션을 처리해준다.

트랜잭션의 성질
-  원자성(Atomicity) : 트랜잭션과 관련된 작업들은 부분적인 성공을 허용하지 않는다.
-  일관성(Consistency) : 트랜잭션이 종료되면 데이터는 언제나 일관성을 유지하는 상태가 된다. (다시 커밋할 수 없음)
-  고립성(Isolation) : 트랜잭션 수행 시 다른 트랜잭션이 끼어들지 못한다.
-  지속성(Durability) : 성공적으로 수행된 트랜잭션은 데이터베이스에 영구적으로 반영된다.

---

자바에서의 트랜잭션 처리

 자바에서의 프로그래밍적 트랜잭션 처리 (스프링에서 선언적 트랜잭션 처리를 하기 때문에 이 코드를 작성하지 않음)
 Connection connection = null;
 PreparedStatement pstmt = null;
 try {
       Connection connection = DriverManager.getConnection(url, username, password);
       connection.setAutoCommit(false);  // 자동 커밋 기능을 정지시킨다.

       pstmt = connection.prepareStatement(SQL1);
       pstmt.executeUpdate(); // SQL1 실행

       pstmt = connection.prepareStatement(SQL2);
       pstmt.executeUpdate(); // SQL2 실행

       pstmt = connection.prepareStatement(SQL3);
       pstmt.executeUpdate(); // SQL3 실행

       connection.commit();  // SQL1,SQL2,SQL3 실행과정이 모두 성공, 데이터베이스에 영구적으로 반영,
  } catch (SQLException ex) {
       connection.rollbank();  // 데이터베이스 반영을 취소
  }