테이블은 사용자의 데이터를 가지는 주체로서, 서버의 많은 옵션과 인덱스 등의 기능이 테이블에 종속되어 사용된다.
테이블 생성
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_test (
member_id BIGINT [UNSIGNED] [AUTO_INCREMENT],
nickname CHAR(20) [CHARACTER SET 'utf-8'] [COLLATE 'utf8_general_ci'] [NOT NULL],
home_url VARCHAR(200) [COLLATE 'latin1_general_cs'],
birth_year SMALLINT [(4)] [UNSIGNED] [ZEROFILL],
member_point INT [NOT NULL] [DEFAULT 0],
registerd_dttm DATETIME [NOT NULL],
modified_ts TIMESTAMP [NOT NULL] [DEFAULT CURRENT_TIMESTAMP],
gender ENUM('Female', 'Male') [NOT NULL],
hobby SET('Reading', 'Game', 'Sports'),
profile TEXT [NOT NULL],
session_data BLOB,
PRIMARY KEY (member_id),
UNIQUE INDEX ux_nickname (nickname),
INDEX ix_registereddttm (registered_dttm)
) ENGINE=INNODB;
|
TEMPORARY 키워드를 사용하면 해당 커넥션에서만 사용 가능한 임시 테이블을 생성한다.- 이미 같은 테이블이 있으면 에러가 발생하며 옵션을 사용하여 무시할 수 있다.
- 테이블을 정의한 스크립트 마지막에 스토리지 엔진을 결정하기 위한
ENGINE 키워드를 사용할 수 있다.- 별도로 정의되지 않으면 MySQL 8.0에서는 InnoDB 스토리지 엔진이 기본으로 사용된다.
각 컬럼은 컬럼명 + 컬럼타입 + [타입 별 옵션] + [NULL 여부] + [기본값] 순서로 명시하고, 타입별로 여러 옵션을 추가로 사용할 수 있다.
테이블 구조 조회
테이블 구조를 확인하는 방법은 SHOW CREATE TABLE, DESC 명령이 있다.
SHOW CREATE TABLE
CREATE TABLE 문장을 표시해준다. 최초로 생성할 때 사용자가 실행한 내용이 아닌 테이블의 메타 정보를 읽어 CREATE TABLE 명령으로 재작성하여 보여준다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| SHOW CREATE TABLE employees;
/*
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`last_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`gender` enum('M','F') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
KEY `ix_hiredate` (`hire_date`),
KEY `ix_gender_birthdate` (`gender`,`birth_date`),
KEY `ix_firstname` (`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci STATS_PERSISTENT=0 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
|
컬럼의 목록, 인덱스, 외래키 정보를 동시에 보여주기 때문에 SQL을 튜닝하거나 테이블의 구조를 확인할 때 주로 사용한다.
DESC
DESCRIBE의 약어 형태의 명령으로 같은 결과를 보여준다. 테이블이 컬럼 정보를 보기 편한 표 형태로 표시해준다.
인덱스 컬럼의 순서나 외래키, 테이블 자체의 속성을 보여주지는 않으므로 테이블의 전체적인 구조를 한 번에 확인하기는 어렵다.
1
2
3
4
5
6
7
8
9
10
11
12
13
| DESC employees;
/*
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | MUL | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | MUL | NULL | |
| hire_date | date | NO | MUL | NULL | |
+------------+---------------+------+-----+---------+-------+
*/
|
테이블의 구조 변경
ALTER TABLE 명령을 사용한다. 테이블의 자체의 속성을 변경할 수 있을뿐만 아니라 인덱스의 추가 삭제나 컬럼을 추가/삭제하는 용도로도 사용된다.
테이블 자체에 대한 속성 변경은 주로 테이블의 문자 집합이나 스토리지 엔진, 파티션 구조 등의 변경이다.
1
2
3
4
5
6
| ALTER TABLE employees
CONVERT TO CHARACTER SET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI,
ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE employees ENGINE=InnoDB,
ALGORITHM=INPLACE, LOCK=NONE;
|
스토리지 엔진을 변경하는 명령은 내부적인 테이블의 저장소를 변경하는 것이라 항상 테이블의 모든 레코드를 복사하는 작업이 필요하다.
ALTER TABLE 문장에 명시된 ENGINE이 기존과 동일하더라도 테이블의 데이터를 복사하는 작업은 실행되기 때문에 주의해야 한다.- 테이블 데이터를 리블드 하는 목적으로도 사용된다.
- 주로 레코드의 삭제가 자주 발생하는 테이블에서 데이터가 저장되지 않은 빈 공간을 제거해 디스크 사용 공간을 줄이는 역할을 한다.
테이블 명 변경
RENAME TABLE 명령으로 처리할 수 있으며, 당순히 테이블의 이름 변경뿐만 아니라 다른 데이터베이스로 테이블을 이동할 때도 사용할 수 있다.
1
2
| RENAME TABLE table1 TO table 2;
RENAME TABLE db1.table1 TO db2.table 2;
|
- 이름만 변경하는 경우
- 첫 번째 명령 처럼 동일 데이터베이스 내에서 이름만 변경하는 작업은 메타 정보만 병경하기 때문에 매우 빨리 처리된다.
- 테이블 복사
- 두 번째 명령과 같이 데이터베이스를 변경하는 경우는 메타 정보뿐만 아니라 테이블이 저장된 파일까지 다른 디렉터리로 이동해야 한다.
- 운영체제에서 서로 다른 파티션으로 파일을 이동할 때는 데이터 파일을 먼저 복사하고 복사를 완료하면 원본 파티션의 파일을 삭제하는 형태로 처리하는데 동일하게 처리된다.
- 서로 다른 운영체제의 파일 스스템을 사용하고 있었다면 데이터 파일의 크기에 비례해서 시간이 소요된다.
- 테이블을 교체하는 경우 일시적으로 테이블이 없어지는 시점이 발생하기 때문에 에러를 발생 시킬 수 있다.
- 여러 테이블의
RENAME명령을 하나의 문장으로 묶어 실행하여 예방할 수 있다.
테이블 상태 조회
모든 테이블은 만들어진 시간, 대략적인 레코드 건수, 데이터 파일의 크기 등 정보를 가지고 있다. 또한 데이터 파일의 버전이나 레코드 포맷 등과 같이 자주 사용되지는 않지만 중요한 정보도 가지고 있는데, 이러한 정보를 조회할 수 있는 명령이 SHOW TABLE STATUS ...다.
LIKE '패턴' 과 같은 조건을 사용해 특정 테이블의 상태만 조회하는 것도 가능하다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| SHOW TABLE STATUS LIKE 'employees' \G;
/*
*************************** 1. row ***************************
Name: employees
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 297022
Avg_row_length: 51
Data_length: 15220736
Max_data_length: 0
Index_length: 22593536
Data_free: 5242880
Auto_increment: NULL
Create_time: 2023-07-25 10:31:33
Update_time: 2023-08-17 14:39:48
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options: stats_persistent=0
Comment:
*/
|
SELECT 쿼리를 이용해서 조회할 수도 있다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| SELECT * FROM information_schema.TABLES
WHERE TABLE_SCHEMA='employees' AND TABLE_NAME='employees' \G
/*
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: employees
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 297022
AVG_ROW_LENGTH: 51
DATA_LENGTH: 15220736
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 22593536
DATA_FREE: 5242880
AUTO_INCREMENT: NULL
CREATE_TIME: 2023-07-25 10:31:33
UPDATE_TIME: 2023-08-17 14:39:48
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_general_ci
CHECKSUM: NULL
CREATE_OPTIONS: stats_persistent=0
TABLE_COMMENT:
*/
|
테이블 구조 복사
테이블 삭제
MySQL 8.0 에서는 특정 테이블을 삭제하는 작업이 다른 테이블의 DML이나 쿼리를 직접 방해하지는 않는다.
레코드가 많지 않은 테이블을 삭제하는 작업은 서비스 도중이라고 하더라도 문제가 되지는 않지만 용량이 매우 큰 테이블을 삭제하는 작업은 상당히 부하가 크다.
- 서버는 해당 테이블이 사용하던 데이터 파일을 삭제해야 한다.
- 파일의 크기가 매우 크고 디스크에서 파일 조각들이 너무 분산되어 저장돼 있다면 많은 디스크 읽기/쓰기 작업이 필요하다.
- 이로인해 다른 커넥션의 쿼리 처리 성능이 떨어질 수도 있다.
InnoDB 스토리지 엔진의 어댑티브 해시 인덱스는 버퍼불의 각 페이지가 가진 레코드에 대한 해시 인덱스 기능을 제공하는데, 어댑티브 해시 인덱스가 활성화돼있는 경우 테이블이 삭제되면 관련 정보들을 모두 삭제해야 한다.
- 많이 가지고 있다면 삭제 작업으로 인해 서버의 부하가 높아지고 간접적으로 다른 쿼리 처리에 영향을 미칠 수도 있다.
- 테이블의 스키마 변경에도 영향을 미칠 수 있다.
