Skip to content

[ SQL 첫걸음 ] 제 6장 데이터베이스 객체 작성과 삭제

참고

SQL 첫걸음을 참고로 공부한 내용입니다. 따라서 아래 명령어를 통해 나오게 되는 결괏값은 전부 해당 책을 통해 다운로드한 파일의 결과물입니다.

데이터베이스 객체

데이터베이스 객체

데이터베이스 객체란 테이블이나 , 인덱스 등 데이터베이스 내에 정의하는 모든 것을 일컫는 말로 구체적으로 이야기하면 실체를 가지는 어떤 것입니다. 따라서 객체의 종류에 따라 데이터베이스에 저장되는 내용도 달라집니다.

쉽게 테이블은 객체이며 해당 테이블을 조작할 수 있는 명령이었던 SELECT, INSERT 등은 객체가 아닙니다.

주의

C++, 자바, 파이썬 등의 프로그래밍에서 사용하는 객체지향 프로그래밍의 객체와 혼동해서는 안 됩니다.

데이터베이스에서의 레코드, 다시 말해 필드의 집합을 개체(Entity)라고 합니다. 쉽게 말해 개체는 일종의 정보를 표현하는 단위로 한글로는 실체로 번역되기도 합니다.

이러한 맥락에서 사실 해당 책에서 사용되는 객체(Object)는 개체(Entity)로 표현되는 게 더 맞을 수도 있으나 우선은 책에는 객체라 표현되어 있기에 해당 용어가 프로그래밍에서 사용되는 객체와는 다르다는 점에 유의해야 합니다.

객체는 이름을 가집니다. 따라서 데이터베이스 내에서 객체를 작성할 때는 이름이 중복되지 않게 해야 합니다. 테이블의 열이나 SELECT 명령에서의 별명(AS) 또한 이름을 갖지만 이것들은 모두 객체가 아닙니다. 실체가 존재하지 않기 때문입니다. 다만 이름을 붙일 때는 제약 사항, 다시 말해 아래와 같은 명명 규칙(Naming Convention)을 따라야 합니다.

  • 기존 이름이나 예약어와 중복하지 않는다.
  • 숫자로 시작할 수 없다.
  • 언더스코어(_) 이외의 기호는 사용할 수 없다.
  • 한글을 사용할 때는 더블쿼트("") (MySQL에서는 백쿼트('')) 로 둘러싼다.
  • 시스템이 허용하는 길이를 초과하지 않는다.

이름은 어떤 데이터가 저장되어 있는지 파악하는 기준이 되는 경우가 많기 때문에 단순히 a와 같이 무의미한 이름이 아닌 연관된 유의미한 이름으로 짓는 게 중요합니다.

스키마

객체는 스키마(Schema)라는 그릇 안에서 만들어집니다. 따라서 객체의 이름이 같아도 스키마가 서로 다르면 상관없습니다. 이러한 특징 때문에 데이터베이스 객체는 스키마 객체(Schema Object)라 불리기도 합니다. 또한 테이블을 작성해서 구축해나가는 작업을 스키마 설계(Schema Design)라고 부릅니다. 이때 스키마는 SQL 명령의 DDL(Data Definition Language)을 이용하여 정의합니다.

조금 더 구체적인 예시로 MySQL의 경우 CREATE DATABASE 명령으로 작성한 데이터베이스가 곧 스키마가 됩니다. Oracle 등에서는 데이터베이스와 데이터베이스 사용자가 계층적 사용자가 됩니다.

정보

동일한 스키마 또는 테이블 내에서 이름이 중복되어 충돌되지 않게 각각의 이름이 가지는 범위를 네임스페이스(Namespace)라고 합니다.

따라서 스키마나 테이블은 네임스페이스(Namespace)이기도 합니다.

테이블 작성, 삭제, 변경

데이터베이스 객체인 테이블을 작성, 삭제, 변경하는 명령을 DDL(Data Definition Language)이라 합니다.

테이블 작성

CREATE 명령을 사용하여 테이블, 뷰, 인덱스 등의 객체를 작성할 수 있습니다. 그 형태는 간단하게 표현해보면 아래와 같습니다.

CREATE TABLE 테이블명 (
     정의1,
     정의2,
    ...
)

열을 정의할 때는 열명을 붙이고 자료형으로 INTEGER, VARCHAR 등을 지정합니다. 특히 CHAR 또는 VARCHAR의 경우 문자열형이기 때문에 최대길이를 괄호(())를 사용하여 함께 지정해줘야 합니다. 또한 열을 정의할 때 DEFAULT 키워드를 사용하여 기본값을 설정할 수 있습니다. 마지막으로 NULL을 허용할 것인지 지정해야 합니다. 생략했을 때는 NULL을 허용하는 것으로 인지합니다.

최대 길이가 8인 문자열형 name열, 기본값으로 현재 시간을 저장하는 날짜시간형 create_at열, 그리고 no열을 가지는 테이블 sample62를 만드는 방법은 아래와 같습니다.

$ mysql > CREATE TABLE sample62 (
    -> no INTEGER NOT NULL,
    -> name VARCHAR(8) NOT NULL,
    -> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    -> );

Query OK, 0 rows affected (0.02 sec)

$ mysql> DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| name       | varchar(8) | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+------------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)

INSERT 명령을 통해 실제로 데이터를 입력해보고 확인해보면 아래와 같습니다.

$ mysql > INSERT INTO sample62 (no, name) VALUES (1, '테스트');

Query OK, 1 row affected (0.00 sec)

$ mysql > SELECT * FROM sample62;

+----+-----------+---------------------+
| no | name      | created_at          |
+----+-----------+---------------------+
|  1 | 테스트    | 2021-12-08 17:52:55 |
+----+-----------+---------------------+
1 row in set (0.00 sec)

정보

날짜시간형의 경우 현재 시간을 기본값으로 사용하고 싶다면 MySQL에서는 CURRENT_TIMESTAMP, Oracle에서는 SYSDATE를 사용합니다.

테이블 삭제

DROP 명령을 통해 필요없는 객체를 삭제할 수 있씁니다. 이때 유의할 점은 많은 데이터베이스가 삭제 명령에 따로 확인을 요구하지 않는다는 것입니다. 따라서 실수로 삭제하지 않게 조심해야 합니다.

앞서 만든 테이블 sample62를 삭제하는 방법은 아래와 같습니다.

$ mysql > DROP TABLE sample62;

Query OK, 0 rows affected (0.00 sec)

$ mysql > DESC sample62;

ERROR 1146 (42S02): Table 'sample.sample62' doesn't exist

데이터 행 삭제

테이블 정의는 그대로 둔 채 데이터, 다시 말해 행만 삭제할 때는 DROP이 아닌 DELETE 명령을 사용합니다. 이때 WHERE 구를 사용하여 조건을 지정하지 않으면 테이블의 모든 행이 삭제됩니다.

그러나 DELETE 명령의 경우 행 단위로 내부처리가 일어나기 때문에 삭제할 행이 많으면 처리속도가 늦어집니다. 따라서 테이블 내의 모든 행을 삭제해야 할 때 빠른 속도의 작업 처리가 필요하다면 DDL로 분류되는 TRUNCATE TABLE 명령을 사용할 수 있습니다.

$ mysql > TRUNCATE TABLE sample62;

Query OK, 0 rows affected (0.01 sec)

$ mysql > SELECT * FROM sample62;

Empty set (0.00 sec)

테이블 변경

테이블을 작성한 뒤에도 열 구성은 변경 가능합니다. ALTER 명령을 통해 객체를 변경할 수 있습니다.

열 추가

ALTER TABLE 명령을 통해 테이블의 열 구성을 변경하는데 열을 추가하려면 ADD 하부명령을 사용합니다. 이때 열을 정의하는 방법은 CREATE TABLE 때와 동일합니다.

이전에 만들었던 테이블 sample62에 최대길이가 16이고 VARCHAR형인 열 nickname을 추가하는 방법은 아래와 같습니다.

$ mysql > ALTER TABLE sample62 ADD (
    -> nickname VARCHAR(16) NOT NULL
    -> );

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql> DESC sample62;

+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| no         | int         | NO   |     | NULL              |                   |
| name       | varchar(8)  | NO   |     | NULL              |                   |
| created_at | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(16) | NO   |     | NULL              |                   |
+------------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)

열 속성 변경

ALTER TABLE 명령에서 열 속성을 변경하라면 MODIFY 하부명령을 사용합니다. 이때 열을 정의하는 방법은 CREATE TABLE 때와 동일합니다.

테이블 sample62에 존재하는 nickname 열의 제약조건을 NULL 값을 허용하는 걸로 변경하고 최대길이 또한 8로 줄이는 방법은 아래와 같습니다.

$ mysql > ALTER TABLE sample62 MODIFY nickname VARCHAR(8) NULL;

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| name       | varchar(8) | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

열 이름 변경

ALTER TABLE 명령에서 열의 이름을 변경하려면 CHANGE 하부명령을 사용합니다. 첫 번째로 기존 열 이름을 입력하고 뒤이어 신규 열 이름을 입력하면 기존 열 이름이 해당 신규 열 이름으로 변경됩니다.

!!! info "정보'

`CHANGE` 하부명령은  이름 뿐만 아니라  속성도 변경할  있습니다.

Oracle에서는  이름을 변경할 경우 `RENAME TO` 하부명령을 사용합니다.

테이블 sample62에 존재하는 name열의 이름을 real_name으로 변경하고 최대 길이 또한 16으로 변경하는 방법은 아래와 같습니다.

$ mysql > ALTER TABLE sample62 CHANGE name real_name VARCHAR(16);

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql > DESC sample62;

+------------+-------------+------+-----+-------------------+-------------------+
| Field      | Type        | Null | Key | Default           | Extra             |
+------------+-------------+------+-----+-------------------+-------------------+
| no         | int         | NO   |     | NULL              |                   |
| real_name  | varchar(16) | YES  |     | NULL              |                   |
| created_at | datetime    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8)  | YES  |     | NULL              |                   |
+------------+-------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

열 삭제

ALTER TABLE 명령에서 열을 삭제하려면 DROP 하부명령을 사용합니다. 뒤에 삭제하고 싶은 열명을 지정하면 됩니다.

테이블 sample62에 존재하는 real_name열을 삭제하는 방법은 아래와 같습니다.

$ mysql > ALTER TABLE sample62 DROP real_name;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
3 rows in set (0.00 sec)

ALTER TABLE로 테이블 관리

최대길이 연장

대규모 데이터베이스에서는 데이터의 크기가 매우 커질 때가 많습니다. 특히 행 개수가 많은 테이블에서는 데이터 하나의 크기만을 최적화하더라도 저장공간을 효율적으로 관리할 수 있습니다. 이럴 때 ALTER TABLE을 활용하여 해당 열의 자료형만 변경하거나 문자열형의 경우 최대길이를 조절하여 저장공간을 관리할 수 있습니다.

이때 유의할 점은 데이터가 이미 저장되어 있을 때 해당 데이터가 변경하려는 자료형에 알맞지 않은 경우 오류가 발생한다는 것과 또한 최대길이도 마찬가지로 저장되어 있는 데이터의 길이가 만약 변경하려는 최대길이보다 클 경우 오류가 발생한다는 것입니다.

먼저 아래와 같이 데이터가 저장되어 있는 테이블 sample62가 있다고 가정해봅시다.

+----+---------------------+------------------------+
| no | created_at          | nickname               |
+----+---------------------+------------------------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임            |
+----+---------------------+------------------------+

만약 nickname열의 자료형을 INTEGER형으로 변결하려면 아래와 같은 오류가 발생합니다.

$ mysql > ALTER TABLE sample62 MODIFY nickname INTEGER;

ERROR 1366 (HY000): Incorrect integer value: '테스트용 닉네임' for column 'nickname' at row 1

또한 최대길이를 변경했을 때 이미 저장된 데이터의 길이가 변경하려는 길이보다 클 경우에 아래와 같은 오류가 발생합니다.

$ mysql > ALTER TABLE sample62 MODIFY nickname VARCHAR(4);

ERROR 1265 (01000): Data truncated for column 'nickname' at row 1

열 추가

ALTER TABLE ADD ... 명령문을 통해 열을 추가하면 행을 추가하는 INSERT 명령을 꼭 확인해야 합니다. 추가된 열이 NULL 값을 허용하거나 DEFAULT 값이 존재하지 않는 이상 해당 열에 데이터 값을 지정해줘야 하기 때문입니다.

기존에 데이터가 존재하는 테이블 sample62에 아래와 같이 NOT NULL 제약조건을 걸어 name열을 추가한다고 가정해봅시다.

ALTER TABLE sample62 ADD name VARCHAR(4) NOT NULL;

기존 존재하던 행의 새로 추가된 name열 값과 DESC 명령을 통해 테이블 구조를 확인해보면 아래와 같습니다.

$ mysql > SELECT * FROM sample62;

+----+---------------------+------------------------+------+
| no | created_at          | nickname               | name |
+----+---------------------+------------------------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |
+----+---------------------+------------------------+------+
1 row in set (0.00 sec)

$ mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
| name       | varchar(4) | NO   |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)

추가된 name열의 Default 값이 NULL로 되어 있음에도 제약조건이 NOT NULL로 되어 있기 때문에 기본값이 빈 문자열, 다시 말해 ''로 추가된 것을 확인할 수 있습니다.

따라서 WHERE 구와 함께 IS NULL= ''를 조건으로 검색(SELECT)하면 결과는 아래와 같습니다.

$ mysql > SELECT * FROM sample62 WHERE name IS NULL;

Empty set (0.00 sec)

$ mysql > SELECT * FROM sample62 WHERE name = '';

+----+---------------------+------------------------+------+
| no | created_at          | nickname               | name |
+----+---------------------+------------------------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |
+----+---------------------+------------------------+------+
1 row in set (0.00 sec)

INTEGER형의 경우는 아래와 같이 제약조건이 NOT NULL일 때 기본값으로 0이 입력되는 것을 확인할 수 있습니다.

$ mysql > ALTER TABLE sample62 ADD test INTEGER NOT NULL;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql > SELECT * FROM sample62;

+----+---------------------+------------------------+------+------+
| no | created_at          | nickname               | name | test |
+----+---------------------+------------------------+------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임            |      |    0 |
+----+---------------------+------------------------+------+------+
1 row in set (0.01 sec)

정보

제약조건이 NULL값을 허용하고 별다른 DEFAULT 키워드를 통한 기본값 설정이 없을 경우에는 자료형에 상관없이 기본값이 전부 NULL로 입력됩니다.

제약

CREATE TABLE로 테이블을 정의할 때 NOT NULL과 같은 제약 또한 정의할 수 있습니다. 이러한 제약은 저장될 데이터를 단어 의미 그대로 제한하는 역할을 합니다. NOT NULL 외에도 대표적인 제약조건으로는 기본기(Primary Key) 제약이나 외부참조(정합) 제약 등이 있습니다.

테이블 작성시 제약 정의

NOT NULL 제약조건을 가진 name열과 NOT NULL 제약조건 및 UNIQUE 제약조건을 가진 no열로 구성된 테이블 sample531을 만드는 방법은 아래와 같습니다.

$ mysql > CREATE TABLE sample531 (
    -> no INTEGER NOT NULL UNIQUE,
    -> name VARCHAR(8) NOT NULL
    -> );

Query OK, 0 rows affected (0.01 sec)

$ mysql > DESC sample531;

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| no    | int        | NO   | PRI | NULL    |       |
| name  | varchar(8) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

이처럼 열에 대해 정의하는 제약을 열 제약이라 합니다.

NOT NULL 제약조건 및 기본키(PRIMARY KEY) 제약조건을 가진 no열과 sub_no열, 그리고 아무런 제약조건을 가지고 있지 않은 name열로 구성된 테이블 sample632를 만드는 방법은 아래와 같습니다.

$ mysql > CREATE TABLE sample632 (
    -> no INTEGER NOT NULL,
    -> sub_no INTEGER NOT NULL,
    -> name VARCHAR(8),
    -> PRIMARY KEY (no, sub_no)
    -> );

Query OK, 0 rows affected (0.01 sec)

$ mysql > DESC sample632;

+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| no     | int        | NO   | PRI | NULL    |       |
| sub_no | int        | NO   | PRI | NULL    |       |
| name   | varchar(8) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

이처럼 한 개의 제약으로 복수의 열에 제약을 설정하는 경우를 테이블 제약이라 합니다.

제약에는 이름을 붙일 수 있습니다. 이는 추후 관리를 쉽게 하기 위해서입니다. 이때 CONSTRAINT 키워드를 사용하여 제약의 이름을 지정할 수 있습니다. 그 방법은 아래와 같습니다. 키워드 다음에 오는 문자열이 곧 제약의 이름이 됩니다.

$ mysql > CREATE TABLE sample631 (
    -> no INTEGER NOT NULL,
    -> CONSTRAINT pkey_sample631 PRIMARY KEY (no)
    -> );

Query OK, 0 rows affected (0.01 sec)

$ mysql > DESC sample631;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| no    | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

제약 추가

기존에 존재하던 테이블에도 나중에 제약을 따로 추가할 수 있습니다. 이때 열 제약테이블 제약이라는 두 가지 방법이 존재한다는 걸 앞서 알게 되었습니다.

열 제약 추가

ALTER TABLE로 열 정의를 변경할 때 MODIFY 하부명령을 통해 해당 열에 제약조건을 추가할 수 있습니다. 이때 유의할 점은 기존 존재하던 데이터에 해당 제약조건이 영향을 끼치기 때문에 만약 제약을 위반하는 데이터가 있다면 오류가 발생합니다.

테이블 제약 추가

테이블 제약은 ALTER TABLE 명령의 ADD 하부명령으로 추가할 수 있습니다. 이때 유의할 점은 기본키의 경우 테이블에 하나만 설정할 수 있기 때문에 이미 테이블에 기본키가 설정되어 있다면 추가로 기본키를 작성할 수 없다는 것입니다. 또한 열 제약 때와 마찬가지로 기존의 데이터에 제약조건을 위반하는 경우가 있다면 오류가 발생합니다. 예를 들면 아래 명령문과 같은 방식으로 테이블 제약을 추가할 수 있습니다.

ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY (no);

제약 삭제

열 제약은 열 정의를 변경하는 방식으로 변경할 수 있고 테이블 제약은 삭제할 수 있습니다.

먼저 열 제약을 삭제하는 방법은 예를 들어 기존 테이블 smple631NOT NULL 제약조건이 있던 c열의 제약조건을 없애는 방법은 아래 명령문과 같습니다.

ALTER TABLE sample631 MODIFY c VARCHAR(30);

테이블 제약의 경우 ALTER TABLE 명령의 DROP 하부명령으로 삭제할 수 있습니다. 이때 MySQL의 경우 기본키는 테이블 하나당 설정할 수 있기 때문에 PRIMARY KEY라는 키워드를 입력해 삭제할 수 있고 그밖의 데이터베이스 제품은 삭제할 때 지정했던 제약명을 입력하여 삭제합니다.

$ mysql > ALTER TABLE sample631 DROP PRIMARY KEY;

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql > DESC sample631;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| no    | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

다른 데이터베이스 제품의 경우 테이블 제약을 삭제하는 방법은 아래와 같이 해당 제약명을 입력하면 됩니다.

ALTER TABLE sample631 DROP CONSTRAINT pkey_sample631;

기본키

기본키로 설정할 열은 NOT NULLUNIQUE 제약조건이 선행되어야 합니다. 따라서 기본키로 설정된 열이 NULL 값을 가지거나 중복하는 데이터 값을 가지면 제약에 위반됩니다.

아래와 같은 방법으로 만든 테이블 sample634가 존재한다고 가정해봅시다.

$ mysql > CREATE TABLE sample634 (
    -> no INTEGER NOT NULL,
    -> name VARCHAR(8),
    -> CONSTRAINT pkey_sample634 PRIMARY KEY (no)
    -> );

Query OK, 0 rows affected (0.01 sec)

그리고 아래와 같이 테이블에 두 개의 행을 추가했다고 가정해봅시다.

INSERT INTO sample634 VALUES(1, '첫번째 행');
INSERT INTO sample634 VALUES(2, '두번째 행');

이때 기본키로 설정된 no열에 중복된 값을 추가하는 시도를 하면 아래와 같이 오류가 발생합니다.

$ mysql > INSERT INTO sample634 VALUES(2, '중복된 행');

ERROR 1062 (23000): Duplicate entry '2' for key 'sample634.PRIMARY'

이는 UPDATE 명령에도 똑같이 적용되어 제약조건에 위반이 되면 오류가 발생합니다.

$ mysql > UPDATE sample634 SET no = 2 WHERE no = 1;

ERROR 1062 (23000): Duplicate entry '2' for key 'sample634.PRIMARY'

이처럼 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조가 기본키 제약입니다. 행이 유일성을 필요로 한다는 의미에서 유일성 제약이라 부르기도 합니다.

복수의 열로 기본키 구성하기

기본키 제약에는 이를 구성할 열 지정이 필요하며 이때 지정된 열은 NOT NULL 제약이 설정되어 있어야 합니다. 다시 말해 기본키로는 NULL 값이 허용되지 않습니다.

이때 기본키를 구성하는 열은 복수여도 무관합니다. 따라서 아래와 같이 a열과 b열이 모두 기본키인 테이블 sample635의 경우 하나의 열만 봤을 때는 중복되는 값이 존재하기 때문에 기본키 제약에 위반되지만 a열과 b열을 하나의 쌍으로 봤을 때는 (1, 1), (1, 2)와 같이 전부 중복되지 않는 쌍이기 때문에 기본키 제약에 위반되지 않습니다.

+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
+---+---+

인덱스 구조

인덱스

인덱스(Index)색인이라고도 불리며 그 역할은 검색속도를 향상시키는 데 있습니다. 여기서 검색은 SELECT 명령 및 WHERE 구를 통해 조건을 지정하여 그에 알맞는 행을 찾는 과정을 의미합니다. 테이블에 인덱스가 존재하면 효율적인 검색이 가능해져 WHERE 구로 조건이 지정된 SELECT 명령의 처리 속도가 향상됩니다.

예를 들어 목차를 생각하면 편합니다. 백과사전에 목차가 존재하지 않으면 과일이라는 단어를 찾을 때 앞에서부터 하나씩 다 살펴봐야 합니다. 그러나 목차가 존재하면 해당 목차를 통해 가 일치하는 곳으로 바로 가서 훨씬 효율적으로 찾을 수 있습니다.

인덱스 또한 이처럼 검색에 사용되는 키워드와 대응하는 행의 장소가 저장되어 있습니다. 이때 인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성됩니다. 그러나 인덱스는 결국 테이블에 종속되어 있기 때문에 테이블이 없으면 아무런 의미가 없어 테이블이 삭제될 때 함께 삭제됩니다.

검색에 사용하는 알고리즘

데이터베이스의 인덱스에 쓰이는 대표적인 검색 알고리즘은 이진 트리(Binary Tree)를 활용한 이진 검색(Binary Serach) 또는 해시(Hash)가 있습니다.

풀 테이블 스캔(Full Table Scan)

인덱스가 지정되지 않은 테이블을 검색할 때는 풀 테이블 스캔(Full Table Scan)이라 불리는 검색 방법을 사용합니다. 이르 그대로 테이블에 저장된 모든 값을 처음부터 차례로 조사해나갑니다. 매우 단순한 방법으로 만약 행이 100개가 존재한다면 값을 최대 100번 비교합니다.

이진 탐색(Binary Search)은 집합을 반으로 나누어 조사하는 방법으로 차례로 나열된 집합에 대해 유효한 검색 방법입니다.

아래와 같이 나열된 집합에서 30을 찾는다고 가정해봅시다. 이진 탐색은 해당 집합의 가운데 값인 20에 접근하여 검색하고자 하는 숫자(30)가 이보다 작다면 좌측 절반을, 크다면 우측 절반을 조사하며 다시 해당 절반의 중간을 잡아 이 절차를 반복합니다.

+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+
| 1 | 2 | 3 | 5 | 10 | 11 | 19 | 20 | 23 | 30 | 31 | 32 | 38 | 40 | 100 |
+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+

이런 방식을 사용하면 결국 집합의 개수 또는 길이만큼 반복해야 했던 풀 테이블 스캔과 비교하여 이진 탐색은 선형 로그 시간이 걸립니다.

정보

선형 로그 시간은 자료구조의 복잡도(Complexity)에서 등장하는 개념입니다. 어떤 행위를 수행하기 위해 걸리는 시간과 공간을 수치화하는 표현이라 생각하면 편합니다.

이진 트리(Binary Tree)

이진 탐색은 고속으로 검색이 가능하지만 데이터가 미리 정렬되어 있어야 하는 단점이 있습니다. 테이블 내의 모든 행을 언제나 정렬된 상태로 두는 것은 어렵기 때문에 이럴 때 테이블 데이터와 별개로 인덱스용 데이터를 저장장치에 만듭니다. 이때 사용하는 자료구조가 바로 이진 트리(Binary Tree)입니다.

위 이진 탐색의 집합 예시를 이진 트리로 표현하면 아래와 같습니다.

트리는 노드(Node)라는 요소로 구성됩니다. 그리고 각 노드는 두 개의 가지(Branch)로 나뉩니다. 이때 두 가지를 비교했을 때 좌측 가지에는 더 작은 값이, 우측에는 더 큰 값이 놓입니다. 이처럼 두 개의 가지로 분기하는 구조라서 이진 트리라 부릅니다.

검색은 이러한 이진 트리의 가지를 통해서 행해집니다. 맨 위 20이라는 루트 노드(Root Node)를 시작으로 이진 탐색과 비슷한 방식을 사용하여 원하는 수치와 비교해 더 작으면 왼쪽 가지를, 더 크면 오른쪽 가지를 선택해 조사해 나갑니다.

유일성

이진 트리에서는 동일한 값을 가진 노드가 존재할 수 없습니다. 따라서 무조건적으로 대소비교가 가능하여 가지가 두 개 생성됩니다.

이러한 유일성을 위해 결국 데이터베이스 또한 기본키 제약을 통하여 유일한 값을 가지게 해야 합니다.

인덱스 작성과 삭제

인덱스(Index)는 DDL(Data Definition Language)을 사용하여 작성하거나 삭제합니다.

사실 표준 SQL에서는 인덱스 자체가 데이터베이스 제품에 의존하는 선택적인 항목으로 취급되어 CREATE INDEX 명령이 존재하지 않습니다. 하지만 대부분의 유명한 데이터베이스 제품에는 인덱스 구조가 도입되어 있고 비슷한 방법으로 이를 관리할 수 있습니다.

인덱스 작성

CREATE INDEX 명령으로 인덱스를 만들 수 있습니다. 이때 인덱스에 이름을 붙여 관리하는데, 인덱스가 데이터베이스 객체가 될지 아니면 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다릅니다.

Oracle, DB2 등에서 인덱스는 스키마 객체가 됩니다. 따라서 스키마 내에서 이름이 중복되지 않게 관리합니다.

반대로 SQL Server, MySQL에서 인덱스는 테이블 내의 객체가 됩니다. 따라서 테이블 내에서 이름이 중복되지 않게 관리합니다.

MySQL에서 테이블 sample62no열에 isample62라는 인덱스를 지정하는 방법은 아래와 같습니다. 이때 인덱스를 확인하려면 SHOW INDEX FROM 테이블명과 같은 형태의 명령문을 사용합니다.

$ mysql > CREATE INDEX isample62 ON sample62(no);

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql > SHOW INDEX FROM sample62;

+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample62 |          1 | isample62 |            1 | no          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

인덱스를 작성할 때는 저장장치에 색인용 데이터가 만들어집니다. 테이블 크기에 따라 인덱스 작성시간도 달라지기 때문에 행이 대량으로 존재할 경우 시간과 저장공간 모두 많이 소비됩니다.

인덱스 삭제

DROP INDEX 명령에 인덱스 이름을 지정하여 인덱스를 삭제할 수 있습니다. 이때 SQL Server, MySQL처럼 인덱스가 테이블 내의 객체로 존재할 경우 테이블 이름도 지정해야 합니다.

앞서 만든 인덱스 isample65를 삭제하는 방법은 아래와 같습니다.

$ mysql > DROP INDEX isample62 ON sample62;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ mysql > SHOW INDEX FROM sample62;

Empty set (0.00 sec)

앞서 인덱스의 역할에 대해 살펴봤던 것처럼 인덱스를 통해 검색(SELECT) 속도를 향상시킬 수 있습니다. 이때 WHERE 구의 조건으로 인덱스의 열을 사용하면 됩니다. 그러나 INSERT 명령을 사용할 때는 결국 기존 테이블 뿐만 아니라 인덱스 객체에도 데이터를 추가해야 하기 때문에 기존보다 시간이 더 걸립니다.

인덱스 isample62를 테이블 sample62nickname에 지정했다고 가정해봅시다. 인덱스를 통해 검색(SELECT)을 하는 방법은 아래와 같이 단순합니다. 인덱스로 지정한 열을 WHERE 구의 조건으로 지정하면 됩니다.

$ mysql > SELECT * FROM sample62 WHERE nickname = '테스트용 닉네임';

+----+---------------------+------------------------+------+------+-------------+
| no | created_at          | nickname               | name | test | test_number |
+----+---------------------+------------------------+------+------+-------------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |    0 |        NULL |
+----+---------------------+------------------------+------+------+-------------+
1 row in set (0.00 sec)

EXPLAIN

인덱스를 사용하면 검색 속도가 향상되는데 실제로 입력한 명령문이 인덱스를 사용하는지 확인하려면 EXPLAIN 명령을 사용하면 됩니다.

EXPLAIN 명령을 사용하여 인덱스로 지정했던 열인 nickname을 사용하는 경우와 그렇지 않은 no 열을 사용하여 차이를 살펴보겠습니다.

$ mysql > EXPLAIN SELECT * FROM sample62 WHERE nickname = '테스트용 닉네임';

+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sample62 | NULL       | ref  | isample62     | isample62 | 27      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

$ mysql > EXPLAIN SELECT * FROM sample62 WHERE no = 1;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sample62 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

주의

EXPLAIN 명령은 표준 SQL에는 존재하지 않는 데이터베이스 제품 의존형 명령입니다. 하지만 다른 데이터베이스 제품이라도 비슷한 명령을 지원합니다.

예를 들어 PostgreSQL은 똑같은 EXPLAIN 명령을, Oracle에서는 EXPLAIN PLAN 명령을 사용합니다.

최적화

SELECT 명령을 실행할 때 인덱스의 사용 여부를 선택하게 됩니다. 이는 데이터베이스 내부의 최적화에 의해 처리되는 부분입니다. 내부 처리에서는 SELECT 명령을 실행하기에 앞서 실행계획을 세웁니다. EXPLAIN 명령은 이 실행계획을 확인하는 명령입니다.

또한 실행계획에서는 인덱스의 유무는 물론 사용여부도 내부 최적화 처리를 통해 판단합니다. 이때 판단 기준으로 인덱스의 품질을 고려하는데 예를 들어 데이터의 종류가 적을 수록 인덱스의 효율이 떨어지기 때문에 인덱스를 사용하지 않습니다.

이처럼 데이터베이스 내부적으로 인덱스를 지정하더라도 내부 최적화를 거쳐 실행계획을 세워서 인덱스의 유무, 인덱스의 사용여부를 결정해 가장 최선의 검색을 수행합니다.

뷰 작성과 삭제

FROM 구에 서브쿼리를 사용할 수 있다는 걸 서브쿼리 부분에서 알 수 있었습니다. 이때 서브쿼리에 이름을 붙여 데이터베이스 객체화하여 쓰기 쉽게 한 것을 뷰(View)라고 합니다.

본래 객체로 사용할 수 없는 SELECT 명령을 객체로서 이름을 붙여 관리할 수 있도록 한 것이 바로 뷰입니다. 따라서 뷰를 참조하면 정의된 SELECT 명령의 실행결과를 테이블처럼 사용할 수 있습니다.

예를 들어 아래와 같이 FROM 구에 서브쿼리가 들어간 명령문이 존재한다고 가정해봅시다.

SELECT * FROM (SELECT * FROM sample54) AS sq;

이때 FROM 구에 작성된 SELECT * FROM sampl54 서브쿼리 부분을 sample_view_67과 같이 이름을 붙여 뷰로 만들 수 있습니다. 그러면 아래와 같이 기존 SELECT 명령을 더 단순하게 사용할 수 있습니다.

SELECT * FROM sample_view_67;

이처럼 뷰를 사용하면 복잡한 SELECT 명령을 더 단순하게 사용할 수 있게 됩니다.

가상 테이블

뷰는 테이블처럼 취급되지만 결국 실체가 존재하지 않기 때문에 가상 테이블(Virtual Table)이라 합니다. 따라서 뷰는 다른 테이블처럼 쓰거나 지울 수 있는 저장공간을 가지고 있지 않습니다.

물론 INSERT, UPDATE, DELETE 명령도 조건만 맞으면 사용할 수 있지만 SELECT 명령만 사용할 것을 권장하고 있습니다.

뷰 작성과 삭제

뷰의 작성

뷰를 작성할 때는 CREATE VIEW 명령을 사용합니다.

SELECT * FORM sample54 명령문을 대체하는 sample_view_67 뷰를 만드는 방법은 아래와 같습니다. 이때 생성된 뷰를 확인하는 명령은 SHOW CREATE VIEW 뷰이름 명려운과 같은 형태입니다.

$ mysql > CREATE VIEW sample_view_67 AS SELECT * FROM sample54;

Query OK, 0 rows affected (0.00 sec)

$ mysql > SHOW CREATE VIEW sample_view_67;

+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View           | Create View                                                                                                                                                              | character_set_client | collation_connection |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| sample_view_67 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sample_view_67` AS select `sample54`.`no` AS `no`,`sample54`.`a` AS `a` from `sample54` | utf8mb4              | utf8mb4_0900_ai_ci   |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

생성한 뷰를 실제로 사용해보면 아래와 같이 정상적으로 원하는 결괏값을 반환한 걸 확인할 수 있습니다.

$ mysql > SELECT * FROM sample_view_67;

+------+------+
| no   | a    |
+------+------+
|    1 |  900 |
|    2 |  900 |
+------+------+
2 rows in set (0.00 sec)

$ mysql > SELECT * FROM (SELECT * FROM sample54) AS sq;

+------+------+
| no   | a    |
+------+------+
|    1 |  900 |
|    2 |  900 |
+------+------+
2 rows in set (0.00 sec)

뷰를 만들 때 열 지정을 생략하면 SELECT 명령의 SELECT 구에서 지정하는 열 정보가 수집되어 자동적으로 뷰의 열로 지정됩니다. 반대로 열을 지정한 경우에는 SELECT 명령의 SELECT 구에 지정한 열보다 우선됩니다. 이때 유의할 점은 만 지정할 수 있을 뿐 자료형이나 제약을 지정할 수 없습니다.

열을 지정하여 뷰를 작성하는 방법은 아래와 같습니다.

$ mysql > CREATE VIEW sample_view_672(n, v1, v2) AS SELECT no, a, a*2 FROM sample54;

Query OK, 0 rows affected (0.01 sec)

$ mysql > SELECT * FROM sample_view_672;

+------+------+------+
| n    | v1   | v2   |
+------+------+------+
|    1 |  900 | 1800 |
|    2 |  900 | 1800 |
+------+------+------+
2 rows in set (0.00 sec)

SELECT 명령의 SELECT 구와 같은 수의 열을 일일이 지정해야 하기 때문에 SELECT 명령의 모든 열을 사용할 경우에는 열을 지정하지 않는 편이 낫습니다.

뷰 삭제

뷰를 삭제할 때는 DROP VIEW 명령을 사용합니다. 방법은 아래와 같습니다.

$ mysql> DROP VIEW sample_view_672;

Query OK, 0 rows affected (0.01 sec)

$ mysql> SHOW CREATE VIEW sample_view_672;

ERROR 1146 (42S02): Table 'sample.sample_view_672' doesn't exist

뷰의 약점

SELECT 명령은 행을 검색하여 클라이언트로 반환하는 명령입니다. 따라서 단순한 검색 뿐만 아니라 ORDER BY 명령을 통한 정렬은 물론 GROUP BY 명령을 통해 집계하여 결괏값을 반환할 수 있습니다. 이러한 모든 처리는 계산능력을 필요로 하기 때문에 컴퓨터의 CPU를 사용합니다.

뷰의 경우 저장되는 것이 SELECT 명령뿐이기 때문에 다른 테이블과 달리 대용량의 저장공간을 필요로 하지 않습니다. 그러나 앞서 설명한 것처럼 계산능력을 위해 CPU 자원을 사용합니다.

머티리얼라이즈드 뷰(Materialized View)

이처럼 계산능력을 사용하는 뷰이기 때문에 만약 근원이 되는 테이블에 보관되는 데이터양이 많을 경우 처리속도가 떨어집니다. 뷰를 중첩해서 사용해도 마찬가지입니다.

이러한 상황을 회피하기 위해 사용할 수 있는 것이 머티리얼라이즈드 뷰(Materialized View)입니다.

일반적인 뷰는 데이터를 일시적으로 저장했다가 쿼리가 실행 종료될 때 함께 삭제됩니다.

반면에 머티리얼라이즈 뷰의 경우 처음 참조되었을 때 데이터를 저장해둡니다. 이후 다시 참조할 때 이전에 저장한 데이터를 그대로 사용하기 때문에 매번 SELECT 명령을 실행할 필요가 없게 됩니다. 마치 테이블처럼 저장장치에 저장해두고 사용하는 것입니다. 만약에 뷰에 지정된 테이블의 데이터가 변경되면 SELECT 명령을 재실행하여 데이터를 다시 저장합니다. 이는 RDBMS가 자동으로 실행합니다.

뷰에 지정된 테이블의 데이터가 자주 변경되지 않는 경우라면 머티리얼라이즈드 뷰를 사용하여 뷰의 약점을 보완할 수 있습니다. 그러나 이를 지원하지 않는 데이터베이스 제품도 있다는 점에 주의해야 합니다.

정보

머티리얼라이즈드 뷰와 같은 방식을 스냅샷(Snapshot)이라 한다.

함수 테이블

뷰를 구성하는 SELECT 명령의 경우 단독으로 수행 가능해야 합니다. 따라서 상관 서브쿼리처럼 서브쿼리가 부모 쿼리와 연관되어 있는 경우 해당 서브쿼리를 뷰로 사용할 수 없습니다.

이러한 뷰의 약점을 보완하기 위해 함수 테이블(Function Table)을 사용할 수 있습니다. 함수 테이블은 테이블을 결괏값으로 반환해주는 사용자정의 함수입니다. 이때 함수에는 인수를 지정할 수 있기 때문에 인수의 값에 따라 WHERE 구의 조건을 붙여 결괏값을 바꿀 수 있습니다. 이러한 방식으로 함수 테이블은 서브쿼리처럼 동작합니다.

MySQL 기준으로 함수는 CREATE FUNCTION 명령을 통해 만들 수 있습니다. 데이터베이스에 함수를 만드는 방법에 관해서는 추후에 더 자세히 살펴보겠습니다.