[신입 SQL 교육 자료] KEY, SEQUENCE, INDEX

1. KEY

1.1. PK

- 각 행의 정보들을 식별

- NULL이거나 중복되면 안됨

- 고유 인덱스를 자동 생성

- 가능한 컬럼이 여러 개인 경우 가장 많이 사용되는 것, 간단하고 짧은 것을 선택

- 함수적 종속관계

- 테이블 당 하나만 정의 가능함

 

*문법

CREATE TABLE 테이블명 ( … 컬럼명 데이터타입 CONSTRAINT 제약조건이름 PRIMARY KEY … )

CREATE TABLE 테이블명 ( … CONSTRANT 제약조건이름 PRIMARY KEY (컬럼명) … )

 

1.2. FK

- 참조하는 테이블과 참조되는 테이블간의 관계(1:1, 1:N, N:M)

- 한 테이블에 존재하는 다른 테이블에 정보 - 외래키

- 두 테이블간 종속이 필요한 관계일 때 참조관계를 표현

- FK로 정의된 테이블이 자식테이블, 컬럼이 FK로 참조되는 테이블을 부모테이블 이라고 함

- 부모 테이블은 자식 테이블보다 먼저 생성되어 있어야 함

- 참조하는 컬럼과 참조되는 컬럼은 데이터 타입이 일치해야 함

- 자식 테이블에 INSERT, UPDATE할 때 FK으로 부모 테이블에 없는 값을 저장하면 오류

           - ON DELETE CASCADE : 부모 행을 삭제하면 자동으로 참조하는 자식 행을 삭제

           - ON DELETE SET NULL : 부모 행을 삭제하면 자동으로 참조하는 자식 행은 NULL

- 참조하고자 하는 컬럼은 PK 또는 UK 이어야 함

 

*문법

CREATE TABLE 테이블명 (

           CONSTRAINT 제약조건이름 FOREIGN KEY (컬럼명)

           REFERENCES 참조 테이블명 (참조 컬럼명)

           [ON DELETE CASCADE | ON DELETE SET NULL] … )

 

CREATE TABLE 테이블명 (

           컬럼명 데이터타입 CONSTRAINT 제약조건이름 FOREIGN KEY

           REFERENCES 참조 테이블명 (참조 컬럼명)

           [ON DELETE CASCADE | ON DELETE SET NULL] … )

 

1.3. 식별자

1.3.1. 개념

- ENTITY 내에서 INSTANCE 들을 구분하는 구분자

 

1.3.2. 특징

1.3.2.1. 유일성

- 주식별자에 의해 ENTITY 내 모든 INSTANCE 들을 유일하게 구분한다.

1.3.2.2. 최소성

- 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다.

1.3.2.3. 불변성

- 주식별자가 한번 특정 ENTITY에 지정되면 그 값은 변하면 안된다.

1.3.2.4. 존재성

- 주식별자가 지정되면 반드시 NULL이 아니라 데이터 값이 존재해야 한다.

 

1.3.3. 식별자 분류 기준

1.3.3.1. 대표성 여부             

- 주식별자 : 엔티티 내에서 각 어커런스를 구분하고 타 엔티티와 참조관계를 연결할 수 있다.

- 보조식별자 : 엔티티 내에서 각 어커런스를 구분하나 대표성이 없어 참조관계 연결이 불가하다..

1.3.3.2. 스스로 생성 여부

- 내부식별자 : 엔티티 내부에서 스스로 만들어지는 식별자이다.

- 외부식별자 : 타 엔티티와의 관계를 통해 타 엔티티로부터 받아오는 식별자이다.

1.3.3.3. 속성의 수

- 단일식별자 : 하나의 속성으로 구성된 식별자이다.

- 복합식별자 : 둘 이상의 속성으로 구성된 식별자이다.

1.3.3.4. 대체성 여부

- 본질식별자 : 업무에 의해 만들어지는 식별자이다.

- 인조식별자 : 업무적으로 만들어지지는 않지만 원조식별자가 복잡한 구성을 갖고 있기 때문에 인위적으로 만드는 식별자이다.

 

1.3.4. 주식별자 도출 시 주의사항

- 해당 업무에서 자주 사용되는 속성을 지정한다.

- 명칭, 내역 등과 같이 이름으로 기술되는 것은 피한다.

- 속성의 수가 많아지지 않도록 한다.

 

1.3.5. 식별자관계와 비식별자 관계에 따른 식별자

1.3.5.1. 개요

- 부모 자식 간에 관계에서 외부 식별자가 생성되었을 때, 부모로부터 받은 외부 식별자를 자식이 주식별자로 사용하면 식별자 관계라고 하고 부모와 연결이 되는 속성으로만 사용하면 비식별자 관계라고 한다.

1.3.5.2. 식별자 관계로만 설정할 경우의 문제점

- 부모에서 자식으로 식별자 관계를 연결하면 주식별자의 속성 수가 많아질 수 있다.

1.3.5.3. 비식별자 관계로만 설정할 경우의 문제점

- 자주 조회하는 PK 속성을 차단해 데이터 조회에 어려움이 있을 수 있다.

1.3.5.4. 식별자 관계와 비식별자 관계의 비교

항목	식별자관계	비식별자관계
목적	강한 연결관계 표현	약한 연결관계 표현
자식 주식별자 영향	자식 주식별자의 구성에 포함	자식 일반속성에 포함
표기법	실선 표현	점선 표현
연결 고려사항	\- 반드시 부모엔터티 종속 - 자식 주식별자 구성에 부모 주식별자 포함 필요 - 상속받은 주식별자속성을 타 엔터티에 이전 필요	\- 약한 종속관계 - 자식 주식별자 구성을 독립적으로 구성 - 자식 주식별자 구성에 부모 주식별자 부분 필요 - 상속받은 주식별자속성을 타 엔터티에 차단 필요 - 부모쪽의 관계참여가 선택관계

 

 

2. SEQUENCE

- 유일한 값을 생성해주는 오라클 객체

- 순차적으로 증가하는 컬럼

- PK으로 사용 가능, 자동으로 간편하게 생성

- PK 중복값 방지 – ROW를 추가할 때 증감된 값을 추가할 수 있음

- 메모리에 캐시하면 시퀀스값의 액세스 효율이 증가함

- 서버가 갑자기 다운되면 캐시되었던 시퀀스값들이 날아가 다음 체번 시 갭이 발생할 수 있음

- 테이블과 독립적으로 생성됨

- 여러 테이블에 의해 공유될 수 있음

- 다른 번호로 다시 시작하려면 시퀀스를 삭제한 후 다시 생성해야 함

- ROLLBACK 적용 안됨

- ROLLBACK은 테이블과 관련된 DML을 취소하는 것이고 시퀀스를 취소하는 것이 아니기 때문에 GAP이 발생 가능함

 

*문법

CREATE SEQUENCE 시퀀스명

           [START WITH n]

           [INCREMENT BY n]

           [MAXVALUE n | NOMAXVALUE]

           [MINVALUE n | NOMINVALUE]

           [CYCLE | NOCYCLE]

           [CACHE | NOCACHE] -- 원하는 수만큼 만들어 shared pool 의 library cache에 상주시킴

 

SELECT 시퀀스명.CURRVAL FROM DUAL

INSERT INTO 테이블명(기본키컬럼명, ... ) VALUES(시퀀스명.NEXTVAL, ...);

DROP SEQUENCE 시퀀스명

 

*NEXTVAL, CURRVAL을 사용할 수 있는 경우

- SUBQUERY가 아닌 SELECT문

- INSERT 문의 DML SELECT문

- NSERT 문의 DML VALUES 절

- UPDATE 문의 SET절

 

*NEXTVAL, CURRVAL을 사용할 수 없는 경우

- VIEW문의 SELECT문

- DISTINCT 키워드를 사용한 SELECT문

- GROUP BY, HAVING, ORDER BY를 사용한 SELECT문

- SELECT, DELETE, UPDATE 문에서의 SUBQUERY

- CREATE TABLE, ALTER TABLE 의 DEFAULT 절

 

 

3. INDEX

3.1. 개념

- 테이블에 대한 검색 속도를 향상시켜주는 자료구조

- 해당 컬럼의 데이터를 오름차순 또는 내림차순 등 옵션값에 따라 정렬한 후 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장됨

- Optimizer 에서 인덱스 컬럼을 판단해 더 효율적인 방식으로 쿼리 수행

- 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기(색인)과 같은 개념이다.

- 인덱스는 테이블을 기반으로 선택적으로 생성할 수 있는 구조이다.

- 인덱스의 기본적인 목적은 검색 성능 최적화이다.

- 검색 조건을 만족하는 데이터를 인덱스를 통해 효과적으로 찾을 수 있도록 돕는다.

- DML 작업은 테이블과 인덱스를 함께 변경하므로 느려지는 단점이 존재한다.

 

3.2. 장점

- 잘 사용하면 FULL TABLE SCAN 방식보다 INDEX TABLE SCAN으로 검색 성능이 향상될 수 있음

- ORDER BY, MIN, MAX 를 더 빠르게 수행할 수 있음

           - ORDER BY의 경우 메모리에서 정렬하고 공간이 부족하면 디스크 I/O가 발생함, 인덱스를 사용하면 이 자원을 절약할 수 있음

           - MIN, MAX - 정렬된 인덱스의 시작 값과 끝 값만 가져오면 되므로 효율적임

- 아래에서 사용하는 경우 성능 향상할 수 있음

           - 규모가 큰 테이블

           - INSERT, UPDATE, DELETE 작업이 드문 컬럼

           - WHERE, ORDER BY, JOIN 등에 자주 사용되는 컬럼

           - 데이터의 중복도가 낮은 컬럼

           * 위 조건을 만족하기 위해서 PK를 인덱스로 거는 것이 추천됨

 

3.3. 단점

- 추가 메모리 공간이 필요

           - 관리하기 위해 DBMS의 약 10% 저장공간이 추가로 필요함

- 잘못 사용하면 검색 성능 저하

           - 나이나 성별과 같이 값의 RANGE가 적은 컬럼을 조회하는 경우

- 아래 추가 작업 필요

           - INSERT : 새로운 데이터의 인덱스 추가

           - DELETE : 삭제한 데이터의 인덱스를 사용 안함 처리

           - UPDATE : 기존 인덱스 사용 안함 처리하고 갱신된 데이터의 인덱스 추가

           * 인덱스가 과도하게 쌓이면 위 작업에서 부하가 발생해 DBMS 성능 저하됨

 

3.4. 자료구조

3.4.1. 해시테이블 - KEY와 VALUE를 한쌍으로 데이터를 저장, 등호(=) 연산에 최적화됨

 

3.4.2. B+TREE - 항상 정렬된 상태를 유지하며 왼쪽 자식은 자신보다 항상 작고 오른쪽 자식은 자신보다 항상 큼

           - LEAF NODE에만 데이터를 저장하고 그 외에는 자식 포인터만 저장

           - LEAF NODE 끼리는 LINKED LIST으로 연결됨

           - B-TREE보다 메모리를 더 확보 가능함. 하나의 노드에 더 많은 포인터를 가지고 트리의 높이가 낮아져 검색속도 향상

           - FULL SCAN을 하는 경우 B-TREE는 모든 NODE를 확인해야 하는 반면 B+TREE는 LEAF NODE 끼리 LINKED LIST으로 연결되므로 선형시간 소모됨

           - 부등호(<, >)를 이용한 순차검색 연산에서 효율적

 

3.4.3. B*TREE - ROOT(기준), BRANCH(중간), LEAF(말단) NODE으로 구성되고 계층적 구조임

           - 인덱스의 정렬된 순서 중간쯤 데이터를 ROOT 블록으로 지정하고 ROOT 블록을 기준으로 BRANCH 블록을 정의하며 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리주소 정보인 ROWID를 저장함

 

3.4.4. B-TREE 인덱스

3.4.4.1. 개요

- DBMS에서 가장 일반적인 인덱스이다.

- 루트 블록은 가장 상단의 블록이다.

- 브랜치 블록은 분기를 목적으로 하는 블록이다.

- 리프 블록은 가장 아래 단계에 있는 블록이다. 인덱스를 구성하는 컬럼의 데이터와 행의 위치를 가리키는 레코드 식별자로 구성된다.

- 인덱스 데이터의 값이 동일하면 레코드 식별자 순서로 저장된다.

- 리프 블록은 양방향 링크를 가지고 있다.

- B트리 인덱스는 ‘=’, ‘BETWEEN’, ‘>’ 등과 같은 연산자로 검색하기에 적합한 구조이다.

 

3.4.4.2. 특징

- 인덱스를 생성할 때 동일 컬럼으로 구성된 인덱스를 생성할 수 없다.

- 동일 컬럼으로 구성되지만 순서가 다르면 다른 인덱스로 생성할 수 있다.

- 인덱스 컬럼의 순서는 성능에 중요한 영향을 미친다.

- ORACLE에서는 B-TREE, 비트맵, 리버스 키, 함수기반 인덱스 등이 존재한다.

 

3.4.4.3. 검색 과정

3.5. 전체 테이블 스캔과 인덱스 스캔

3.5.1. 전체 테이블 스캔

3.5.1.1. 개요

- 테이블에 존재하는 모든 데이터를 읽어가면서 조건이 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식이다.

- 오라클의 경우 FULL TABLE SCAN을 하면 데이터를 찾기 위해 테이블의 고수위 마크 아래의 모든 블록을 읽는다. 이 방식으로 읽은 블록들은 메모리에서 곧 제거될 수 있도록 관리된다.

 

3.5.1.2. 옵티마이저의 선택기준

3.5.1.2.1. SQL문에 조건이 존재하지 않는 경우

- 테이블에 존재하는 모든 데이터가 답이 되는 경우이므로 모드 블록을 읽고 반환한다.

 

3.5.1.2.2. SQL문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우

- 사용 가능한 인덱스가 존재하지 않는다면 데이터를 액세스할 수 있는 방법은 테이블의 모든 데이터를 읽으면서 주어진 조건을 만족하는지 검사하는 것 뿐이다. 인덱스가 존재하더라도 함수를 사용해 인덱스 컬럼을 변형한 경우에는 인덱스를 사용할 수 없다.

 

3.5.1.2.3. 옵티마이저의 취사 선택

- 조건을 만족하는 데이터가 많은 경우, 결과를 추출하기 위해서 테이블의 대부분의 블록을 액세스해야 한다고 옵티마이저가 판단하면 조건에 사용 가능한 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.

 

3.5.1.2.4. 그 밖의 경우

- 병렬처리 방식으로 처리하는 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우에 전체 테이블 스캔 방식으로 읽을 수 있다.

 

3.5.2. 인덱스 스캔

3.5.2.1. 개요

- 데이터베이스에서 주로 사용되는 트리 기반 인덱스를 중심으로 설명한다.

- 인덱스 스캔은 인덱스를 구성하는 컬럼의 값을 기반으로 데이터를 추출하는 방식이다.

- 인덱스의 리프 블록은 인덱스를 구성하는 컬럼과 레코드 식별자로 구성된다.

- SQL문에 필요로 하는 모든 컬럼의 값이 인덱스 구성 컬럼에 포함된 경우에 테이블 액세스가 발생하지 않을 수 있다.

- 인덱스는 인덱스 구성 컬럼의 순서로 정렬된다.

 

3.5.2.2. 종류

3.5.2.2.1. 인덱스 유일 스캔(INDEX UNIQUE SCAN)

- 유일 인덱스는 중복을 허락하지 않는다. 유일 인덱스 구성 컬럼에는 모두 ‘=’ 구성이다.

3.5.2.2.2. 인덱스 범위 스캔(INDEX RANGE SCAN)

- 인덱스를 이용해 한 건 이상의 데이터를 추출한다.

- 컬럼 모두에 대해 ‘=’으로 값이 주어지지 않은 경우와 비유일 인덱스를 이용한 스캔방식으로 데이터를 액세스한다.

- 리프 블록의 양방향 링크를 이용해 내림차순으로 데이터를 읽는 것은 인덱스 역순 범위 스캔이라고 한다.

 

3.5.3. 전체 테이블 스캔 방식과 인덱스 스캔 방식의 비교

- 데이터를 액세스하는 방법은 테이블의 전체 데이터를 모두 읽으면서 데이터를 추출하는 전체 테이블 스캔 방식과 인덱스를 경유해서 읽는 인덱스 스캔 방식이 있다.

- 전체 테이블 스캔 방식은 인덱스의 존재유무와 상관없이 항상 사용가능한 스캔 방식이다.

- 인덱스 스캔 방식은 사용가능한 적절한 인덱스가 존재할 때만 이용할 수 있는 방식이다.

- 대용량 데이터 중 극히 일부의 데이터를 찾을 때는 인덱스 스캔방식을 이용해 몇번의 I/O 만으로 데이터를 찾는 것이 유리할 수 있다. 그러나 어차피 한번에 여러 블록씩 읽는 것이라면 테이블 스캔 방식이 유리할 수 있다.

 

 

 

참고 출처

“[DATABASE] 기본키(PK), 외래키(FK)”, velog.io/@jch9537, 2020년 7월 12일 수정, 2023년 5월 16일 접속, https://velog.io/@jch9537/DATABASE-PK-FK.

“[Database] 데이터베이스 테이블 제약조건 PK, FK, UK 개념 “, lovefor-you.tistory.com, 2020년 3월 7일 수정, 2023년 5월 16일 접속, https://lovefor-you.tistory.com/261.

“[DB] 데이터베이스 제약조건 PK, FK, UK 개념설명 “, itworldyo.tistory.com, 2021년 7월 31일 수정, 2023년 5월 16일 접속, https://itworldyo.tistory.com/144.

“데이터베이스란 뭐지?? 관계형 ?? 비관계형??? , PK FK 는 뭥미??”, zibu-story.tistory.com, 2021년 9월 18일 수정, 2023년 5월 16일 접속, https://zibu-story.tistory.com/159.

“MSSQL PK FK UK CK 란? 개념 이해“, jaegeun.tistory.com, 2021년 10월 14일 수정, 2023년 5월 16일 접속, https://jaegeun.tistory.com/53.

“[SQL 24] 제약조건 - PK(Primary Key), FK(Foreign Key)”, keep-cool.tistory.com, 2017년 12월 17일 수정, 2023년 5월 16일 접속, https://keep-cool.tistory.com/51.

“[Database] 기본키(Primary Key), 외래키(Foreign Key)란?”, http://itnovice1.blogspot.com/, 2019년 1월 13일 수정, 2023년 5월 16일 접속, http://itnovice1.blogspot.com/2019/01/database-primary-key.html.

“[데이터베이스] 데이터베이스 PK FK UK CK 의 이해”, silverkim.org, 2022년 8월 10일 수정, 2023년 5월 16일 접속, https://silverkim.org/server-database/db-데이터베이스-pk-fk-uk-ck-의-이해/.

“시퀀스(Sequence)의 이해 및 활용 – 구루비”, http://www.gurubee.net/, 2002년 1월 20일 수정, 2023년 5월 16일 접속, http://www.gurubee.net/lecture/1037.

“[SQL] 시퀀스의 개념과 시퀀스 생성하기”, lifejusik1004.tistory.com, 2020년 10월 5일 수정, 2023년 5월 16일 접속, https://lifejusik1004.tistory.com/entry/SQL-시퀀스의-개념과-시퀀스-생성하기.

“[Oracle] 오라클 시퀀스(Sequence) 사용법 총정리 (생성, 조회, 수정, 삭제)등”, coding-factory.tistory.com/, 2019년 11월 7일 수정, 2023년 5월 16일 접속, https://coding-factory.tistory.com/420.

“오라클 시퀀스(sequence)”, gojoo.tistory.com, 2019년 4월 3일 수정, 2023년 5월 16일 접속, https://gojoo.tistory.com/86.

“Oracle 시퀀스 개념과 활용”, tantangerine.tistory.com, 2020년 2월 26일 수정, 2023년 5월 16일 접속, https://tantangerine.tistory.com/23.

“SQL 시퀀스(sequence)”, coffee-mark.tumblr.com, 2013년 9월 17일 수정, 2023년 5월 16일 접속, https://coffee-mark.tumblr.com/post/61475546764/sql-시퀀스-sequence.

“[day_03] DB / 06. SQL 기본 4 - Oracle SEQUENCE 시퀀스”, creamilk88.tistory.com, 2020년 8월 10일 수정, 2023년 5월 16일 접속, https://creamilk88.tistory.com/62.

“시퀀스 작성 및 사용”, www.ibm.com, 2021년 3월 8일 수정, 2023년 5월 16일 접속, https://www.ibm.com/docs/ko/i/7.1?topic=language-creating-using-sequences.

“[SQL]SEQUENCE(시퀀스)의 기초”, seungsami.tistory.com, 2019년 2월 14일 수정, 2023년 5월 16일 접속, https://seungsami.tistory.com/87.

“[DB] 11. 인덱스(Index) - (1) 개념, 장단점, B+Tree 등”, rebro.kr, 2021년 8월 31일 수정, 2023년 5월 16일 접속, https://rebro.kr/167

“[DB] 데이터베이스(DB) 인덱스(Index) 란 무엇인가?”, choicode.tistory.com, 2021년 9월 13일 수정, 2023년 5월 16일 접속, https://choicode.tistory.com/27.

“[Database] 인덱스(index)란?”, mangkyu.tistory.com, 2020년 10월 13일 수정, 2023년 5월 16일 접속, https://mangkyu.tistory.com/96.

“[DB] 데이터베이스 인덱스(Index) 란 무엇인가?”, coding-factory.tistory.com, 2021년 7월 4일 수정, 2023년 5월 16일 접속, https://coding-factory.tistory.com/746.

“DB 데이터베이스 인덱스(Index) 기본 개념과 설명”, wildeveloperetrain.tistory.com, 2022년 2월 12일 수정, 2023년 5월 16일 접속, https://wildeveloperetrain.tistory.com/131.

“[DB] 인덱스란? - (1) 개념, 장단점, 쓰는 이유”, siahn95.tistory.com, 2021년 5월 21일 수정, 2023년 5월 16일 접속, https://siahn95.tistory.com/77.

“[DB] DB의 INDEX 개념정리”, azderica.github.io, 2021년 10월 27일 수정, 2023년 5월 16일 접속, https://azderica.github.io/00-db-index/.

“DB 인덱스(Index)란?”, velog.io/@alicesykim95, 2022년 3월 7일 수정, 2023년 5월 16일 접속, https://velog.io/@alicesykim95/DB-인덱스index란.

“인덱스 간단 정리(개념, 인덱스컬럼 결정)”, khdscor.tistory.com, 2022년 4월 9일 수정, 2023년 5월 16일 접속, https://khdscor.tistory.com/50.

“SQL 전문가 가이드 (2013년) – 과목2. 3장. 제2절. 인덱스 기본”, www.gurubee.net, 2013년 4월 6일 수정, 2023년 5월 31일 접속, http://www.gurubee.net/lecture/2387,

“SQL 전문가 가이드 (2013년) – 과목1. 1장. 제5절. 식별자 ”, www.gurubee.net, 2013년 3월 9일 수정, 2023년 5월 31일 접속, http://www.gurubee.net/lecture/2387,