Let's have a cup of coffee.

EXPLAIN(실행계획)

EXPLAIN 키워드를 사용하여 실행계획 정보 출력

mysql> explain select * from employees where emp_no = '10001';
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

id

id는 SELECT에 붙은 번호이며 실행 순서

id가 작을수록 먼저 수행, id가 같은 값이라면 두 개 테이블이 조인 되었다고 볼 수 있음

MySQL은 조인을 하나의 단위로 실행하기 때문에 id로 쿼리의 실행 단위를 식별함

SQL에 서브쿼리나 UNION이 없다면 SELECT는 하나 이므로 모든 행에 대해 1이란 값이 부여되지만 이외의 경우에는 원 구문에서 순서에 따라 각 SELECT 구문들에 순차적으로 번호가 부여됨

select_type

SQL문을 구성하는 SELECT 문의 유형을 출력하는 항목

FROM에 위치하는지, 서브쿼리인지, UNION절로 묶인 SELECT 인지를 나타냄

table

행이 어떤 테이블에 접근하는 지를 나타냄

테이블을 사용하지 않는 경우 table이 null로 표시되고 서브쿼리인 경우에는 <subquery#> 이 출력

table 컬럼에 <derived>, <union> 과 같이 "<>" 가 표시되는 경우 임시테이블을 의미함.

 "<>"안에 표시되는 숫자는 SELECT문의 id를 나타냄

type

데이터 접근 방식을 표시하는 필드

상위에 위치한 type일 수록 빠른 방식

• ALL, index 두 가지는 테이블 또는 특정 인덱스가 전체 행에 접근하기 때문에 테이블 크기가 크면 효율이 떨어짐
• ref_or_null 의 경우 NULL이 들어 있는 행은 인덱스의 맨 앞에 모아서 저장하지만 그 건수가 많으면 MySQL 서버의 작업량이 커짐
• 즉 ALL 이외의 접근 방식은 모두 인덱스를 사용하는데 해당 쿼리로 사용할 수 있는 적절한 인덱스가 없다는 의미 일 수도 있음

possible_keys

사용 가능한 인덱스 목록

쿼리에서 사용된 컬럼과 비교 연산자를 토대로 어떤 인덱스를 사용할 수 있는지를 표시

실제 사용한 인덱스가 아닌 사용할 수 있는 후보 인덱스 목록

key

사용된 PK 또는 Index

key가 NULL이면 인덱스를 사용할 수 없는 쿼리

key_len

key_len 필드는 선택된 인덱스의 길이를 의미

ref

테이블 조인시 어떤 조건으로 해당 테이블에 엑세스 되었는지 나타냄

키 컬럼에 나와있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼이 사용되었는지 나타냄

rows

원하는 행을 찾기 위해 접근하는 데이터의 모든 행의 예측값

EXPLAIN ANALYZE를 제외하곤 EXPLAIN은 MySQL 통계 정보를 토대로 예측

SQL문의 최종 결과 건 수와 비교해 rows 차이가 많이 나는 경우 튜닝 대상이 됨

Filtered

필터 조건에 따라  제거된 비율(%)

필터가 제대로 동작하지 않으면 테이블 등을 ANALYZE 해서 분석을 해야 함

Extra

옵티마이저가 SQL문을 어떻게 해석하여 수행할 것인지에 대한 추가정보

EXPLAIN ANALYZE

실제 실행된 소요 시간, 비용을 측정하여 실행 계획 정보를 출력하고 싶다면 ANALYZE 키워드를 사용해서 분석

(EXPLAIN 키워드만 사용되는 경우 통계정보를 활용한 예측된 실행계획)

[사용방법]

1) EXPLAIN ANALYZE 키워드를 사용

2) ANALYZE "테이블명" => EXPLAIN

EXPLAIN ANALYZE
select * 
  from employees 
 where emp_no >=10002;
 
 -> Filter: (employees.emp_no >= 10002)  (cost=30011 rows=149601) (actual time=1.15..270 rows=300023 loops=1)
    -> Index range scan on employees using PRIMARY over (10002 <= emp_no)  (cost=30011 rows=149601) (actual time=1.11..251 rows=300023 loops=1)

EXPLAIN의 format Options

MySQL 8.0부터 EXPLAIN명령에 FORMAT 옵션을 사용해 실행 계획을 결과를 TABLE, JSON, TREE로 지정

Default EXPLAIN (Table view)

EXPLAIN 
select * 
  from employees 
 where emp_no between '1' and '10000000'
 ;
 
 id|select_type|table    |partitions|type |possible_keys|key    |key_len|ref|rows  |filtered|Extra      |
--+-----------+---------+----------+-----+-------------+-------+-------+---+------+--------+-----------+
 1|SIMPLE     |employees|          |range|PRIMARY      |PRIMARY|4      |   |149601|   100.0|Using where|

EXPLAIN vertical view (쿼리 끝에 '\G' )

mysql> EXPLAIN 
    -> select * 
    ->   from employees 
    ->  where emp_no between 1 and 10000000\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 149601
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

EXPLAIN FROMAT = JSON

attached_condition은 어떤 조건이 사용되었는지 나오는 항목

EXPLAIN FORMAT = JSON
select * 
  from employees 
 where emp_no between '1' and '10000000';


{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "29957.56"
    },
    "table": {
      "table_name": "employees",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "emp_no"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 149601,
      "rows_produced_per_join": 149601,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "14997.46",
        "eval_cost": "14960.10",
        "prefix_cost": "29957.56",
        "data_read_per_join": "19M"
      },
      "used_columns": [
        "emp_no",
        "birth_date",
        "first_name",
        "last_name",
        "gender",
        "hire_date"
      ],
      "attached_condition": "(`employees`.`employees`.`emp_no` between '1' and '10000000')"
    }
  }
}

EXPLAIN FROMAT = TREE

EXPLAIN FORMAT = TREE
select * 
  from employees 
 where emp_no between '1' and '10000000';
 
-> Filter: (employees.emp_no between '1' and '10000000')  (cost=29958 rows=149601)
    -> Index range scan on employees using PRIMARY over (1 <= emp_no <= 10000000)  (cost=29958 rows=149601)

실행계획 개선 방향

자동 증가(AUTO_INCREMENT) 옵션 사용

​

테이블의 PK를 구성하는 컬럼의 크기가 너무 크거나 PK로 사용할 만한 컬럼이 없을때는 숫자 타입의 컬럼에 자동 증가 옵션을 사용해 인조 키를 생성할 수 있다. MySQL 서버의 auto_increment_increment와 auto_increment_offset 시스템 설정을 이용해 AUTO_INCREMENT 컬럼의 자동 증가 값이 얼마씩 증가될지 변경할 수 있다. 일반적으로 이 두 설정 값은 모두 1로 사용되지만, auto_increment_offset을 100000으로 auto_increment_increment를 2로 변경하면 자동 생성되는 값은 100000, 100002, 100004, 100006, ... 과 같이 증가한다.

mysql> set auto_increment_increment=2;
Query OK, 0 rows affected (0.00 sec)

mysql> set auto_increment_offset=10;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 10    |
+--------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> drop table tb_test_autoinc;
Query OK, 0 rows affected (0.02 sec)

mysql> create table tb_test_autoinc
      (col1 int not null auto_increment,
       col2 int,
       primary key(col1));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_test_autoinc values (null,1),(null,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tb_test_autoinc;
+------+------+
| col1 | col2 |
+------+------+
|    2 |    1 |
|    4 |    2 |
+------+------+
2 rows in set (0.00 sec)

mysql> set auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

mysql> set auto_increment_offset=10;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 10    |
+--------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> drop table tb_test_autoinc;
Query OK, 0 rows affected (0.01 sec)

mysql> create table tb_test_autoinc
      (col1 int not null auto_increment,
       col2 int, 
       primary key(col1));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb_test_autoinc values (null,1),(null,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tb_test_autoinc;
+------+------+
| col1 | col2 |
+------+------+
|   10 |    1 |
|   20 |    2 |
+------+------+
2 rows in set (0.00 sec)

auto_increment_offset 값이 auto_increment_increment 값보다 크다면 auto_increment_offset 값은 무시된다.

​

AUTO_INCREMENT 옵션을 사용한 컬럼은 반드시 그 테이블에서 프라이머리 키나 유니크 키의 일부로 정의해야 한다. MyISAM 스토리지 엔진을 사용하는 테이블에서는 자동 증가 옵션이 사용된 컬럼이 PK or UK의 아무 위치나 사용될 수 있다. InnoDB 스토리지 엔진을 사용하는 테이블에서는 반드시 AUTO_INCREMENT 컬럼이 PK or UK 중 적어도 하나의 인덱스에서는 제일 앞에 위치해야 한다.

mysql> create table tb_autoinc_innodb(
    -> col1 int,
    -> col2 int not null auto_increment,
    -> primary key(col1,col2)
    -> )ENGINE=INNODB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and 
it must be defined as a key

InnoDB 스토리지 엔진과 MyISAM 스토리지 엔진에서 AUTO_INCREMENT 컬럼 하나만으로 Primary Key 나 Unique key로 사용하면 작동 방식의 차이는 없다. 하지만 AUTO_INCREMENT 컬럼과 다른 일반 컬럼을 조합해서 Primary key나 Unique key를 구성하면 InnoDB와 MySQL 테이블에서 증가하는 값의 패턴이 달라진다.

mysql> set auto_increment_offset=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set auto_increment_increment=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> create table tb_autoinc_innodb(
    -> col1 int,
    -> col2 int auto_increment,
    -> primary key(col1,col2),
    -> unique key ux_col2(col2)
    -> )engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> create table tb_autoinc_myisam(
    -> col1 int,
    -> col2 int auto_increment,
    -> primary key(col1,col2)
    -> )engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_autoinc_innodb values (1,null), (1,null), (2,null), (2,null);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into tb_autoinc_myisam values (1,null), (1,null), (2,null), (2,null);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tb_autoinc_innodb;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    3 |
|    2 |    4 |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from tb_autoinc_myisam;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    2 |
+------+------+
4 rows in set (0.00 sec)

InnoDB 테이블에서는 프라이머리 키의 앞쪽에 위치한 컬럼(col1)의 값에 관계없이 항상 1씩 증가된 값이 col2(AUTO_INCREMENT 컬럼)에 저장됐다. 하지만 MyISAM 테이블에서는 pk의 선두 컬럼(col1)의 값에 의존해 AUTO_INCREMENT 컬럼(col2)의 값이 1부터 다시 시작됐다.

AUTO_INCREMENT 컬럼은 테이블당 하나만 사용할 수 있다. AUTO_INCREMENT 컬럼의 현재 증가 값은 테이블의 메타 정보에 저장돼어 있다.

show table status where name = 'tb_autoinc_innodb';
show table status like 'tb_autoinc_innodb';

show table status where name = 'tb_autoinc_myisam';
show table status like 'tb_autoinc_myisam';

AUTO_INCREMENT를 초기화 할때는 변경할 AUTO_INCREMENT 값이 해당 컬럼에서 가장 큰 값보다 커야 한다.

alter table tb_autoinc_innodb auto_increment = 1;
show table status like 'tb_autoinc_innodb'; -- auto_increment 값이 변경되지 않는다.

mysql> insert into tb_autoinc_innodb values (3,null);
mysql> select * from tb_autoinc_innodb;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    3 |
|    2 |    4 |
|    3 |    5 |
+------+------+
5 rows in set (0.00 sec)

AUTO_INCREMENT 초기화시 변경할 AUTO_INCREMENT 컬럼의 값의 최대값보다 큰 값으로 설정한다

mysql> delete from tb_autoinc_innodb where col1=3;
mysql> alter table tb_autoinc_innodb auto_increment = 10;  -- col2의 최대값보다 큰값으로 설정
mysql> insert into tb_autoinc_innodb values (3,null);
mysql> select * from tb_autoinc_innodb;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    3 |
|    2 |    4 |
|    3 |   10 |
+------+------+
5 rows in set (0.00 sec)

MySQL에서 1보다 큰값으로 시작하려면 dummy값을 넣어주고 그보다 큰 값으로 시작값을 설정해야 한다. 예를 들어 더미로 1000을 넣어주고 1001로 테이블의 auto_increment를 셋팅해야 한다.

mysql> create table tb_float(fd1 float);
mysql> insert into tb_float values(0.1);

mysql> select * from tb_float where fd1=0.1;
Empty set (0.00 sec)

복제에 참여하는 MySQL 서버에서 부동 소수점을 사용할 때는 주의해야 한다. 부동 소수점 타입의 데이터는 MySQL의 텍스트 기반(바이너리 로그 파일의 쿼리가 텍스트 기반이므로) 복제에서는 마스터와 슬레이브 간의 데이터가 달라질 수 있다. 물론 유효 정수부나 소수부는 달라지지 않겠지만 위에서도 언급했듯이 유효 정수부나 소수부를 눈으로 판별하기는 쉽지 않다.

​

만약 부동 소수점 값을 저장해야 한다면 유효 소수점의 자리수만큼 10을 곱해서 정수로 만들어 그 값을 정수 타입의 컬럼에 저장하는 방법도 생각해볼 수 있다. 예를 들어 소수점 4자리까지 유효한 GPS 정보를 저장한다고 했을 때 소수점으로 된 좌표 값에 10000을 곱해서 저장하고 조회할 때는 10000으로 나눈 결과를 사용하면 된다.

create table tb_location(
latitude int unsigned,
longitude int unsigned);

insert into tb_location(latitude, longitude) values (37.1422*10000, 131.5208*10000);

select latitude/10000 as latitude, longitude/10000 as longitude
  from tb_location 
 where latitude = 37.1422*10000 and longitude=131.5208*10000;

+----------+-----------+
| latitude | longitude |
+----------+-----------+
|  37.1422 |  131.5208 |
+----------+-----------+
1 row in set (0.00 sec)

-- zerofill : 지정한 자릿수에서 숫자값을 제외한 나머지를 숫자값 앞에 0을 패딩해서 표시한다.
mysql> create table tb_bigint(col1 bigint(10) zerofill); 
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> insert into tb_bigint values (123), (12345), (123456789), (1234567890), (12345678901);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from tb_bigint;
+-------------+
| col1        |
+-------------+
|  0000000123 |   -- BIGINT 뒤에 명시된 10자리중 숫자값을 제외하고 0이 왼쪽에 패딩됀다.
|  0000012345 |
|  0123456789 |
|  1234567890 |
| 12345678901 |
+-------------+
5 rows in set (0.00 sec)

정수 타입 뒤의 길이 지정은 ZEROFILL 옵션이 없으면 아무런 의미가 없다. ZEROFILL 옵션이 사용되면 자동으로 그 컬럼의 타입은 양의 숫자만 저장할 수 있는 UNSIGNED 타입이 되어 버리기 때문에 주의해야 한다.

​

MySQL에서는 다른 DBMS에서와 같이 홑따옴표와 쌍따옴표의 경우에는 홑따옴표나 쌍따옴표를 두번 연속으로 표기해서 이스케이프 처리를 할 수도 있다. MySQL에서는 문자열을 표시하기 위해 홑따옴표와 쌍따옴표를 모두 사용할 수 있는데, 홑따옴표로 문자열을 표현할 때는 홑따옴표를 두번 연속으로 표기해서 이스케이프 처리할 수 있다. 그리고 홑따옴표로 문자열을 감쌀 때는 쌍따옴표는 두 번 연속으로 표기해도 이스케이프 용도로 해석되지 않는다. 그 반대로도 똑같이 적용된다. 간단하게 따옴표를 두번 연속해서 이스케이프 처리하는 예제를 한번 살펴보자.

CREATE TABLE tb_char_escape(fd1 varchar(100));
insert into tb_char_escape values ('ab''ba') -- escape ' 
                                 ,("ab""ba") -- escape "
                                 ,("ab\'ba") -- escape \
                                 ,('ab\"ba') -- escape \
                                 ,('ab""ba') -- 문자열 : "" 
                                 ,("ab''ba");-- 문자열 : ''
select * from tb_char_escape;

위의 예제에서 '' 안에 문자열에 쓰인 ""는 이스케이프로 인식되지 않고 문자열로 인식한다. 반대로 ""안에 쓰인 ''도 문자열로 인식한다.

​

1. 콜레이션(Collation)

​

collation은 문자열 컬럼의 값에 대한 비교나 정렬 순서를 위한 규칙을 의미한다. 즉 비교나 정렬 작업에서 영문 대소문자를 같은 것으로 처리할지 아니면 더 크거나 작은 것으로 판단할지에 대한 규칙을 정의하는 것이다.

​

MySQL의 모든 문자열 타입의 컬럼은 독립적인 문자집합과 collation을 가진다. 각 컬럼에 대해 독립적으로 문자집합이나 collation을 지정하든 그렇지 않든 독립적인 문자집합과 collation을 가지는 것이다. 각 컬럼에 대해 독립적으로 지정하지 않으면 MySQL 서버나 DB의 기본 문자집합과 collation을 자동으로 설정된다. collation이란 문자열 컬럼의 값을 비교하거나 정렬하는 기준이 된다. 그래서 각 문자열 컬럼의 값을 비교하거나 정렬할 때는 항상 문자집합뿐 아니라 collation의 일치 여부에 따라 결과가 달라지며, 쿼리의 성능 또한 상당한 영향을 받는다.

​

문자집합은 2개 이상의 collation을 가지고 있는데, 하나의 문자집합에 속한 콜레이션은 다른 문자집합과 공유해서 사용할 수 없다. 또한 테이블이나 컬럼에 문자집합만 지정하면 해당 문자집합의 default collation이 해당 컬럼의 collation으로 지정된다. 반대로 컬럼의 문자집합은 지정하지 않고 collation만 지정하면 그 collation이 소속된 문자집합이 묵시적으로 그 컬럼의 문자집합으로 사용된다. MySQL서버에서 사용 가능한 collation의 목록은 "SHOW CHARACTER SET" 명령을 이용해 다음과 같이 확인해 볼수 있다.

show character set;

show collation;

Collation의 이름은 2개 또는 3개의 파트로 구분돼 있으면, 각 파트는 다음과 같은 의미로 사용된다.

​

3개의 파트로 구성된 Collation 이름

- 첫 번째 파트는 문자집합의 이름이다.

- 두 번째 파트는 해당 문자집합의 하위 분류를 나타낸다.

- 세 번째 파트는 대문자나 소문자의 구분 여부를 나타낸다. 즉, 세 번째 파트가 "ci"이면 대소문자를 구분하지 않는 Collation(Case Insensitive)을 의미하며, "cs"이면 대소문자를 별도의 문자로 구분하는 Collation(Case Sensitive)이다.

​

2개의 파트로 구성된 Collation 이름

- 첫 번째 파트는 문자집합의 이름이다.

- 두 번째 파트는 항상 "bin"이라는 키워드가 사용된다. 여기서 "bin"은 이진 데이터(binary)를 의미하며, 이진 데이터로 관리되는 문자열 컬럼은 별도의 collation을 가지지 않는다. collation이 "xxx_bin"이라면 비교 및 정렬은 실제 문자 데이터의 바이트 값을 기준으로 수행된다.

​

Collation이 대소문자를 구분하지 않는다고 해서 실제 컬럼에 저장되는 값이 모두 소문자나 대문자로 변환되어 저장되는 것은 아니며, collation과 관계없이 입력된 데이터의 대소문자는 별도의 변환없이 그대로 저장된다.

​

자주 사용하게 되는 Latin1이나 euckr, 그리고 utf8 문자집합의 default collation은 각각 latin1_swedish_ci, eurkr_korean_ci, 그리고 uft8_general_ci이다. 이들은 모두 대소문자를 구분하지 않는 콜레이션이라서 대소문자를 구분해서 비교나 정렬해야 하는 컬럼에서는 "_cs" 계열의 콜레이션을 명시적으로 지정해야 한다. 하지만 utf8 문자집합이나 euckr과 같이 별도로 "_cs"계열의 콜레이션을 가지지 않는 문자 집합도 있는데, 이때는 utf8_bin 또는 euckr_bin과 같이 "_bin" 계열의 콜레이션을 사용하면 된다. 일반적으로 각 국가의 언어는 그 나라 국민에게 익숙한 순서대로 문자 코드값이 부여돼 있으므로 대소문자를 구분해야 할 때는 "_bin" 계열의 콜레이션을 적용해도 특별히 문제되지는 않는다.

​

MySQL의 문자열 컬럼은 콜레이션 없이 문자집합만 가질 수는 없다. 콜레이션을 명시적으로 지정하지 않았다면 지정된 문자집합의 기본 콜레이션이 묵시적으로 적용된다. 그리고 문자열 컬럼의 정렬이나 비교는 항상 해당 문자열 컬럼의 콜레이션에 의해 판단하므로 문자열 컬럼에서는 CHAR나 VARCHAR와 같은 타입의 이름과 길이만 같다고 해서 똑같은 타입이라고 판단해서는 안 된다. 타입의 이름과 문자열의 길이, 그리고 문자집합과 콜레이션까지 일치해야 똑같은 타입이라고 할 수 있다. 문자열 컬럼에서는 문자집합과 콜레이션이 모두 일치해야 조인이나 WHERE 조건이 인덱스를 효율적으로 사용할 수 있다. 조인을 수행하는 양쪽 테이블의 컬럼이 문자집합이나 콜레이션이 다르다면 비교작업에서 콜레이션의 변환이 필요하기 때문에 인덱스를 효율적으로 이용하지 못할 때가 많으므로 주의해야 한다.

​

테이블을 생성할 때 문자집합이나 콜레이션을 적용하는 방법을 한 번 살펴보자.

CREATE DATABASE db_test CHARACTER SET=utf8;

CREATE TABLE tb_member(
member_id VARCHAR(20) NOT NULL collate latin1_general_cs,
member_name VARCHAR(20) NOT NULL collate utf8_bin,
member_email VARCHAR(100) NOT NULL,
...
);

문자집합이나 콜레이션은 DB 수준에서 설정할 수도 있으며, 테이블 수준으로 설정할 수도 있다. 그리고 컬럼 수준에서도 개별적으로 설정할 수 있다.

show character set like 'utf8%'

"CREATE DATABASE" 명령으로 기본 문자집합이 utf8인 DB를 생성한다. 이 명령에서 콜레이션은 명시적으로 정의하지 않았지만 utf8의 기본 콜레이션인 utf8_general_ci가 기본 콜레이션이 된다. db_test DB내에서 생성되는 테이블이나 컬럼 중에서 별도로 문자집합이나 콜레이션을 정의하지 않으면 모두 utf8 문자집합과 utf8_general_ci 콜레이션을 사용하도록 자동 설정 된다.

​

"CREATE TABLE" 명령에서는 각 컬럼이 서로 다른 문자집합이나 콜레이션을 사용하도록 정의했다. 각 컬럼의 비교나 정렬 특성을 살펴보자.

- tb_member 테이블을 생성하면서 member_id 컬럼의 콜레이션을 latin1_general_cs로 설정했다. 그래서 memeber_id 컬럼은 숫자나 영문 알파벳, 그리고 키보드의 특수 문자 윌주로만 저장할 수 있고, "_cs" 계열의 콜레이션이므로 대소문자 구분을 하는 정렬이나 비교를 수행한다.

- member_name 컬럼은 콜레이션이 utf8_bin으로 설정됐으므로 한들이나 다른 나라의 언어를 사용할 수 있지만 "_bin" 계열의 콜레이션이 사용됐으므로 대소문자를 구분하는 정렬과 비교를 수행한다.

- member_email 컬럼은 아무런 문자집합이나 콜레이션을 정의하지 않았으므로 DB의 기본 문자집합과 콜레이션을 그대로 사용한다. 그래서 email 컬럼은 utf8_general_ci 콜레이션을 사용하고, 비교나 정렬 시 대소문자를 구분하지 않는다.

​

​

Latin 계열의 문자집합에 대해 _ci, _cs, _bin 콜레이션의 정렬 규칙을 테스트해보자.

create table tb_collate(
fd_latin1_general_ci varchar(10) collate latin1_general_ci,
fd_latin1_general_cs varchar(10) collate latin1_general_cs,
fd_latin1_bin varchar(10) collate latin1_bin,
fd_latin7_general_ci varchar(10) collate latin7_general_ci
);

insert into tb_collate values ('a','a','a','a'), ('A','A','A','A')
                            , ('b','b','b','b'), ('B','B','B','B')
                            , ('_','_','_','_'), ('-','-','-','-')
                            , ('.','.','.','.'), ('~','~','~','~');

Latin_general_ci 콜레이션을 사용하는 컬럼을 기준으로 정렬했다. 출력된 정렬 순서로 보면 'a'와 'A' 중에서 소문자가 먼저인 것처럼 보이지만 사실 대소문자 구분이 없이 정렬된 것이다.

select fd_latin1_general_ci, fd_latin1_general_ci = 'a'
from tb_collate 
order by fd_latin1_general_ci;

Latin_general_cs 콜레이션으로 정렬하여 같은 알파벳에서 대문자가 소문자보다 먼저 정렬됐다.

select fd_latin1_general_cs, fd_latin1_general_cs = 'a' 
from tb_collate
order by fd_latin1_general_cs;

Latin_bin 콜레이션으로 정렬한 예제로 대문자만 먼저 정렬되고 그 다음으로 소문자가 정렬됐다.

select fd_latin1_bin, fd_latin1_bin = 'a'
from tb_collate 
order by fd_latin1_bin;

특수문자만 먼저 정렬하고 알파벳이 다름으로 정렬하기 원할 때 Latin1이 아니라 Latin7문자셋을 사용하여 특수문자가 알파벳보다 먼저 정렬됐다.

select fd_latin7_general_ci, fd_latin7_general_ci = 'a' 
from tb_collate 
order by fd_latin7_general_ci;

검색은 대소문자를 구분하지 않고 정렬은 대소문자를 구분해서 해야 할 때는 검색과 정렬작업 중에서 하나는 인덱스를 이용하는 것을 포기할 수밖에 없다. 이때 일반적으로 콜레이션을 _ci로 만들어 검색은 인덱스를 이용하게 해주고 정렬작업은 인덱스를 사용하지 않는 (Using filesort) 형태로 처리한다. 검색과 정렬 모두 인덱스를 이용하려면 정렬을 위한 콜레이션을 사용하는 컬럼을 하나 더 추가하고 검색은 원본 컬럼을 그리고 정렬은 복사된 추출 컬럼을 이용하는 방법도 생각은 해볼 수 있다.

​

테이블의 구조는 "SHOW CREATE TABLE 명령으로 확인할 수 있다. 각 컬럼의 문자집합이나 콜레이션을 확인하려면 INFORMATION_SCHEMA DB의 COLUMNS 테이블을 확인하면 된다.

select table_name, column_name, column_type, character_set_name, collation_name
  from information_schema.columns 
 where table_schema='employees' and table_name ='tb_collate';

2. 비교 방식

​

테스트 환경 : MySQL 8.0

​

1. NO PAD 방식

​

1-1. CHAR 공백 UNIQUE 테스트

공백을 저장 안하므로 'data '를 'data'로 저장한다.(공백제거)

unique 비교시 'data' 와 'data ' 를 다른데이터로 보지만

UNIQUE 값 저장시에 unique 컬럼의 값에 'data' 가 이미 존재한다면

CHAR는 공백을 제거하고 저장하므로 'data ' 가 'data' 가 되어 같은데이터로 인식하여 저장되지 않는다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요하다.

​

1-2. VARCHAR 공백 UNIQUE 테스트

공백을 저장하여 'data ' 를 'data ' 로 저장한다.(공백유지)

unique 비교시 NOPAD 방식이므로 'data'와 'data '를 다른데이터로 인식한다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요없다.

(하지만, 데이터 자체로 보면 중복이므로 정제가 필요하다.)

​

​

2. PAD SPACE 방식

​

2-1. CHAR 공백 UNIQUE 테스트

공백을 저장 안하므로 'data '를 'data'로 저장한다.(공백제거)

unique 비교시 'data' 와 'data ' 를 같은데이터로 본다. (공백PAD를 채워서 비교)

UNIQUE 값 저장시에 unique 컬럼의 값에 'data' 가 이미 존재한다면

'data '(공백포함) 가 같은데이터로 인식되어 저장되지 않는다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요하다.

​

2-2. VARCHAR 공백 UNIQUE 테스트

공백을 저장하여 'data ' 를 'data ' 로 저장한다.(공백유지)

unique 비교시 PADSPACE 방식이므로 'data'와 'data '를 같은데이터로 인식한다.

공백이 포함된 데이터를 이관시 공백제거 후 같은 데이터가 있는지 점검이 필요하다.

​

-- PADSPACE라는 것은 공백PAD를 길이가 큰쪽에 맞춰서 붙여서 공백의 영향없이 비교될 수 있는 것을 의미한다.

​

​

3. 결론

​

COLLATION(정렬) 방식에 따라 비교 방식이 다르다.

NO PAD는 공백을 채우지 않고 비교하여 'data' 와 'data ' 를 다른 데이터로 인식한다.

PAD SPACE 는 공백을 채우고 비교하여 'data' 와 'data ' 를 같은 데이터로 인식한다.

​

CHAR 타입은 공백을 제거하고 저장하고 VARCHAR 타입은 공백을 저장한다.

​

* COLLATION 조회

Pad_attribute 속성 값 => NO PAD or PAD SPACE

SHOW COLLATION WHERE Charset = 'utf8mb4';

SELECT COLLATION_NAME, PAD_ATTRIBUTE
FROM INFORMATION_SCHEMA.COLLATIONS
WHERE CHARACTER_SET_NAME = 'utf8mb4';

[TEST]

-- ---------------------------------------------------------------------------------------------------------------------

-- NO PAD 방식

-- ---------------------------------------------------------------------------------------------------------------------

use employees;

SHOW COLLATION WHERE Charset = 'utf8mb4';

-- NO PAD 방식
SHOW COLLATION WHERE collation = 'utf8mb4_0900_ai_ci';

​-- 공백이 다르다고 인식한다.
select case when 'data' = 'data ' then 1 else 2 end from dual;

​-- varchar 공백 테스트
drop table blink_test1;
create table blink_test1(
col1 varchar(14) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

insert into blink_test1(col1) values('data');
insert into blink_test1(col1) values('data '); -- 공백도 저장한다.
insert into blink_test1(col1) values('data '); -- 공백도 저장한다.
commit;

-- 결과 3건이 나온다.
select * from blink_test1;

-- 결과 1건이 나온다.
select * from blink_test1 where col1 = 'data';

-- 결과 3건이 나온다.
select * from blink_test1 where col1 like 'data%';

​
-- varchar 공백 pk 테스트(unique 비교시 공백도 저장하므로 'data'와 'data '를 다른데이터로 인식한다)
drop table blink_test2;
create table blink_test2(
col1 varchar(14) NOT NULL
,PRIMARY KEY (col1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

insert into blink_test2(col1) values('data');
insert into blink_test2(col1) values('data '); -- 공백도 저장한다.
insert into blink_test2(col1) values('data '); -- 공백도 저장한다.
commit;

-- 결과 3건이 나온다.
select * from blink_test2;

-- 결과 1건이 나온다.
select * from blink_test2 where col1 = 'data';

-- 결과 3건이 나온다.
select * from blink_test2 where col1 like 'data%';


-- varchar 공백 pk 테스트(unique 비교시 공백도 저장하므로 'data'와 'data '를 다른데이터로 인식한다)
drop table blink_test3;
create table blink_test3(
col1 varchar(14) NOT NULL
,col2 varchar(14) NOT NULL
,PRIMARY KEY (col1, col2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

​insert into blink_test3(col1,col2) values('data','data2');
insert into blink_test3(col1,col2) values('data','data2 ');
insert into blink_test3(col1,col2) values('data','data2 ');
commit;

-- 결과 3건이 나온다.
select * from blink_test3;

-- 결과 1건이 나온다.
select * from blink_test3 where col1 = 'data' and col2 = 'data2';

-- 결과 3건이 나온다.
select * from blink_test3 where col1 = 'data' and col2 like 'data%';
​
-- char 공백 pk 테스트(unique 비교시 공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)
drop table blink_test4;
create table blink_test4(
col1 char(14) NOT NULL
,col2 char(14) NOT NULL
,PRIMARY KEY (col1, col2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

​insert into blink_test4(col1,col2) values('data','data2 '); -- 공백을 제거하고 저장한다.
insert into blink_test4(col1,col2) values('data','data2'); -- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'
insert into blink_test4(col1,col2) values('data','data2 ');-- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'
commit;

-- 결과 1건이 나온다.
select * from blink_test4;

-- 결과 1건이 나온다.
select * from blink_test4 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다.
select * from blink_test4 where col1 = 'data ' and col2 = 'data2 ';


-- char 공백 테스트(공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)
drop table blink_test5;
create table blink_test5(
col1 char(14) NOT NULL
,col2 char(14) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
​
insert into blink_test5(col1,col2) values('data','data2');
insert into blink_test5(col1,col2) values('data','data2 '); -- 공백을 제거하고 저장한다.
insert into blink_test5(col1,col2) values('data','data2 ');-- 공백을 제거하고 저장한다.
commit;

-- 결과 3건이 나온다.
select * from blink_test5;

-- 결과 3건이 나온다.
select * from blink_test5 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다.
select * from blink_test5 where col1 = 'data' and col2 = 'data2 ';

-- 결과 3건이 나온다.
select * from blink_test5 where col1 = 'data' and col2 like 'data2%';​

select cast('data' as char) from dual;​

select cast('data' as char) = cast('data ' as char) ; -- 0

select cast('data' as varchar) = cast('data ' as varchar);

-- PADSPACE라는 것은 공백PAD를 길이가 큰쪽에 맞춰서 붙여서 
-- 공백의 영향없이 비교될 수 있는 것을 의미한다.

SHOW COLLATION WHERE collation = 'utf8mb4_bin';
show COLLATION where charset = 'utf8mb4';
​
-- varchar 공백 테스트
drop table blink_test1;
create table blink_test1(
col1 varchar(14) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

insert into blink_test1(col1) values('data');
insert into blink_test1(col1) values('data '); -- 공백도 저장한다.
insert into blink_test1(col1) values('data '); -- 공백도 저장한다.
commit;​

-- 결과 3건이 나온다.
select * from blink_test1;

-- 결과 1건이 나온다. --> 결과 3건이 나온다.
select * from blink_test1 where col1 = 'data';

-- [추가]
-- 결과 3건이 나온다. PADSPACE를 큰것에 맞춰서 비교한다.
select * from blink_test1 where col1 = 'data ';

-- 결과 3건이 나온다.
select * from blink_test1 where col1 like 'data%';​

-- varchar 공백 pk 테스트(unique 비교시 공백을 채워서 비교하므로 'data'와 'data '를 같은 데이터로 인식한다)
drop table blink_test2;
create table blink_test2(
col1 varchar(14) NOT NULL
,PRIMARY KEY (col1)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;
​
insert into blink_test2(col1) values('data');

insert into blink_test2(col1) values('data '); 
-- UNIQUE 체크를 위해서 공백 채워서 비교하여 저장하지 못한다.
-- 18:33:00 insert into blink_test2(col1) values('data ')
-- Error Code: 1062. Duplicate entry 'data ' for key 'blink_test2.PRIMARY' 0.000 sec

insert into blink_test2(col1) values('data '); 
-- UNIQUE 체크를 위해서 공백 채워서 비교하여 저장하지 못한다.
-- 18:33:31 insert into blink_test2(col1) values('data ') 
-- Error Code: 1062. Duplicate entry 'data ' for key 'blink_test2.PRIMARY' 0.000 sec
commit;

-- 결과 3건이 나온다. --> 결과 1건이 나온다.
select * from blink_test2;

-- 결과 1건이 나온다.
select * from blink_test2 where col1 = 'data';

-- 결과 3건이 나온다. --> 결과 1건이 나온다.
select * from blink_test2 where col1 like 'data%';​

-- varchar 공백 pk 테스트(unique 비교시 공백도 저장하므로 'data'와 'data '를 같은 데이터로 인식한다)
drop table blink_test3;
create table blink_test3(
col1 varchar(14) NOT NULL
,col2 varchar(14) NOT NULL
,PRIMARY KEY (col1, col2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;
​
 -- 공백을 저장한다.
insert into blink_test3(col1,col2) values('data','data2 ');
-- Error Code: 1062. Duplicate entry 'data-data2 ' for key 'blink_test3.PRIMARY'
insert into blink_test3(col1,col2) values('data','data2'); 
-- Error Code: 1062. Duplicate entry 'data-data2 ' for key 'blink_test3.PRIMARY'
insert into blink_test3(col1,col2) values('data','data2 ');
commit;

-- 결과 3건이 나온다. --> 결과 1건이 나온다.
select * from blink_test3;

-- 결과 1건이 나온다.
select * from blink_test3 where col1 = 'data' and col2 = 'data2';

-- [추가]
-- 결과 1건이 나온다. (공백을 추가해도 같은데이터로 본다)
select * from blink_test3 where col1 = 'data ' and col2 = 'data2 ';

-- 결과 3건이 나온다. --> 결과 1건이 나온다.
select * from blink_test3 where col1 = 'data' and col2 like 'data%';​

-- char 공백 pk 테스트(unique 비교시 공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)
drop table blink_test4;
create table blink_test4(
col1 char(14) NOT NULL
,col2 char(14) NOT NULL
,PRIMARY KEY (col1, col2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;
​
-- 공백을 제거하고 저장한다.
insert into blink_test4(col1,col2) values('data','data2 '); 
-- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'
insert into blink_test4(col1,col2) values('data','data2'); 
-- Duplicate entry 'data-data2' for key 'blink_test4.PRIMARY'
insert into blink_test4(col1,col2) values('data','data2 ');
commit;

-- 결과 1건이 나온다.
select * from blink_test4;

-- 결과 1건이 나온다.
select * from blink_test4 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다. --> 결과 1건이 나온다.
select * from blink_test4 where col1 = 'data ' and col2 = 'data2 ';

-- char 공백 테스트(공백을 저장 안하므로 'data'와 'data '를 같은데이터로 인식한다)
drop table blink_test5;
create table blink_test5(
col1 char(14) NOT NULL
,col2 char(14) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

insert into blink_test5(col1,col2) values('data','data2');
insert into blink_test5(col1,col2) values('data','data2 '); -- 공백을 제거하고 저장한다.
insert into blink_test5(col1,col2) values('data','data2 ');-- 공백을 제거하고 저장한다.
commit;

-- 결과 3건이 나온다.
select * from blink_test5;

-- 결과 3건이 나온다.
select * from blink_test5 where col1 = 'data' and col2 = 'data2';

-- 결과 0건이 나온다. --> 결과 3건이 나온다. 공백을 채워서 비교하여 
-- 'data2' 와 'data2 '를 같은 데이터로 인식한다.
select * from blink_test5 where col1 = 'data' and col2 = 'data2 ';

-- 결과 3건이 나온다.
select * from blink_test5 where col1 = 'data' and col2 like 'data2%';

-----------------------------------------------------------------------------------------------------------------------

mysql> select 'ABC'='ABC' as is_equal;
+----------+
| is_equal |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select 'ABC'='ABC ' as is_equal;
+----------+
| is_equal |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select 'ABC'=' ABC' as is_equal;
+----------+
| is_equal |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select 'ABC '='ABC ' as is_equal;
+----------+
| is_equal |
+----------+
|        1 | -- 공백이 둘다 하나인 경우 같다.
+----------+
1 row in set (0.00 sec)

mysql> select 'ABC '='ABC  ' as is_equal;
+----------+
| is_equal |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

공백도 비교한다.

mysql> select 'ABC   ' like 'ABC' as is_same_pattern;
+-----------------+
| is_same_pattern |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> select '  ABC' like 'ABC' as is_same_pattern;
+-----------------+
| is_same_pattern |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> select 'ABC  ' like 'ABC%' as is_same_pattern;
+-----------------+
| is_same_pattern |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

문자열(CAHR와 VARCHAR)

​

​

1. 저장공간

​

CHAR와 VARCHAR은 문자열을 저장할 수 있는 데이터 타입으로 고정 길이(CHAR)와 가변 길이(VARCHAR) 라는 차이가 있다.

​

- 고정 길이는 실제 입력되는 컬럼 값의 길이에 따라 사용하는 저장 공간의 크기가 변하지 않는다. CHAR 타입은 이미 저장 공간의 크기가 고정적이다.

- 가변 길이는 최대로 저장할 수 있는 값의 길이는 제한돼 있지만, 그 이하 크기의 값이 저장되면 그만큼 저장공간이 줄어든다. 하지만 VARCHAR 타입은 저장된 값의 유효 크기가 얼마인지를 별도로 저장해 둬야 하므로 1~2바이트의 저장 공간이 추가로 더 필요하다.

​

하나의 글자를 저장하기 위해 CAHR(1)과 VARCHAR(1) 타입을 사용할 때 실제 사용되는 저장 공간의 크기를 살펴보자. 우선 두 문자열 타입 모두 한 글자를 저장할 때 사용하는 문자집합에 따라 실제 저장 공간은 1~3바이트까지 사용하게 된다. 여기서 하나의 글자가 CHAR 타입에 저장될 때는 추가적인 공간이 더 필요하지 않지만 VARCHAR 타입에 저장할 때는 문자열의 길이를 관리하기 위한 1~2 바이트의 공간을 추가적으로 더 사용한다.

VARCHAR 타입의 길이가 255bytes 이하이면 1byte 만 사용하고 타입의 길이가 256bytes 이상으로 설정되면 2bytes를 길이를 저장하는 데 사용한다. VARCHAR 타입의 최대 길이는 2bytes 표현할 수 있는 이상은 사용할 수 없다. 즉 VARCHAR 타입의 최대 길이는 65,536 이상으로 설정할 수 없다.

​

[주의]

MySQL에서는 하나의 레코드에서 TEXT와 BLOB 타입을 제외한 컬럼의 전체 크기가 64KB를 초과할 수 없다. 만약 테이블에 VARCHAR 타입의 컬럼 하나만 있다면 이 VARCHAR 타입은 최대 64KB 크기의 데이터를 저장할 수 있다. 하지만 이미 다른 컬럼에서 40KB의 크기를 사용하고 있다면 VARCHAR 타입은 24KB만 사용할 수 있다. 이때 만약 24KB를 초과하는 크기의 VARCHAR 타입을 생성하려고 하면 에러가 발생하거나 자동으로 VARCHAR 타입이 TEXT 타입으로 대체된다. 그래서 컬럼을 새로 추가할 때는 VARCHAR 타입이 TEXT 타입으로 자동적으로 변환되지 않았는지 확인해 보는 것이 좋다.

​

문자열 타입의 저장 공간을 언글할때는 1문자와 1byte를 구분해서 사용하고 있다. 1문자는 실제 저장되는 값의 문자 집합에 따라 1~3bytes 공간을 사용할 수 있기 때문이다. 위의 VARCHAR 타입의 컬럼 하나만 가지는 테이블의 예에서 VARCHAR 타입은 최대 64KB 크기의 데이터를 저장할 수 있다고 했는데, 이 수치는 바이트 수를 의미하므로 실제 65,536 글자까지 저장할 수 있다는 것은 아니다. 실제 저장되는 문자가 아시아권의 언어라면 저장 가능한 글자수는 반으로 줄고, UTF-8 문자를 저장한다면 실제 저장 가능한 글자 수는 1/3로 줄어들 것이다.

​

문자열의 값의 길이가 항상 일정하다면 CHAR를 사용하고 가변적이라면 VARCHAR를 사용하는 것이 일반적이다. CHAR 타입과 VARCHAR 타입을 결정할 때 중요한 판단 기준은 다음과 같다.

​

- 저장되는 문자열의 길이가 대개 비슷한가?

- 컬럼의 값이 자주 변경되는가?

​

CHAR 타입과 VARCHAR 타입의 선택 기준은 값의 길이도 중요하지만, 해당 컬럼의 값이 얼마나 자주 변경되는지가 타입 선택의 기준이 되어야 한다. 컬럼의 값이 얼마나 자주 변경되는지가 왜 중요한지 그림으로 살펴보자

CREATE TABLE tb_test(
  fd1 INT,
  fd2 CHAR(10),
  fd3 DATETIME
);

INSERT INTO tb_test(fd1,fd2,fd3) VALUES(1,'ABCD','2011-06-07 11:02:11');

fd2 컬럼의 CHAR(10) 대신 VARCHAR(10)으로 변경해서 똑같이 데이터를 저장해 보자

CREATE TABLE tb_test1(
  fd1 INT,
  fd2 VARCHAR(10),
  fd3 DATETIME
);

INSERT INTO tb_test1(fd1,fd2,fd3) VALUES(1,'ABCD','2011-06-07 11:02:11');

[주의]

UTF-8은 저장하는 문자에 따라 최소 1바이트부터 최대 4바이트까지 사용할 수 다. MySQL 5.1 버전 까지는 UTF-8의 3바이트 문자까지만 저장 한다. 4바이트를 사용하는 UTF-8문자를 저장하려고 하면 "알 수 없는 문자"라는 에러가 출력 된다. 4바이트를 사용하는 UTF-8문자는 MySQL 5.5 이상의 버전에서부터 사용할 수 있다.

​

​

​

2. 문자집합(캐릭터 셋)

​

MySQL 서버에서 각 테이블의 칼럼은 모두 서로 다른 문자집합을 사용해 문자열 값을 저장할 수 있다.

문자집합은 문자열을 저장하는 CHAR와 VARCHAR, 그리고 TEXT 타입의 컬럼에만 설정할 수 있다.

MySQL에서 최종적으로는 컬럼 단위로 문자집합을 관리하지만 관리의 편의를 위해 MySQL 서버와 DB, 그리고 테이블 단위로 기본 문자집합을 설정할 수 있다. 즉, 테이블의 문자집합을 UTF-8로 설정하면 컬럼의 문자집합을 별도로 지정하지 않아도 UTF-8 문자집합을 사용한다. 테이블의 기본 문자집합이 UTF-8이라 하더라도 각 컬럼에 대해 문자집합을 EUC-KR이나 ASCII등을 별도로 지정할 수 있다.

한글 기반의 서비스에서는 euckr 또는 utf8 문자집합을 사용하며, 일본어의 경우에는 cp932 또는 utf8을 적용하는 것이 일반적이다. 한글 윈도우에서 기본적으로 사용되는 MS949(MSWIN949) 문자집합은 EUC-KR보다는 조금 확장된 형태의 문자집합으로 유닉스 계열의 운영체제에서 사용하는 CP949와 같은 문자집합이다. MySQL 서버에서는 별도로 CP949라는 이름의 문자집합은 지원하지 않고, EUC-KR만 지원한다. 실제로 CP949는 EUC-KR보다 더 많은 문자를 표현할 수 있는 문자집합이다. 하지만 MySQL 5.1.38 버전과 그 이후 버전에서는 euckr 문자집합이 보완되어 CP949가 표현하는 모든 문자 집합을 지원하므로 CP949 대신 euckr을 사용해도 아무런 문제없이 사용할 수 있다.

​

웹 서비스에서 여러 나라의 언어를 동시에 지원하기 위해 UTF-8을 많이 사용한다. ANSI 표준에서는 하나의 문자집합만을 기본으로 사용할 수 있는 DB에서 다국어를 지원할 수 있게끔 NCHAR 또는 NATIONAL CHAR와 같은 컬럼 타입을 정의하고 있다. MySQL에서도 NCHAR 타입을 지원하지만 기본적으로 MySQL에서는 컬럼 단위로 문자집합을 선택할 수 있기 때문에 NCHAR 타입을 사용할 필요는 없다. MySQL에서 NCHAR 타입을 사용하면 UTF-8 문자집합을 사용하는 CHAR 타입으로 생성된다.

​

MySQL 서버에서 사용 가능한 문자집합은 다음과 같이 "SHOW CHARACTER SET" 명령으로 확인해 볼 수 있다.

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

select emp_no, first_name from employees where first_name='Georgi';
select emp_no, first_name from employees where first_name=_latin1'Georgi';

첫 번째 쿼리는 문자열을 character_set_connection으로 문자집합이 변환된 이후 처리된다. 두번째 쿼리의 문자열은 인트로듀서(_latin1)의 문자집합으로 first_name 값과 비교가 실행된다. 일반적으로 인트로듀서는 "_문자셋이름" 과 같이, 문자열 리터럴 앞에 언드스코어 기호("_")와 문자집합의 이름을 붙여서 표현한다.

​

​

처리 결과를 클라이언트로 전송할 때의 문자집합 변환

character_set_connection에 정의된 문자집합으로 변환해 SQL을 실행한 다음, MySQL 서버는 쿼리의 결과(결과 셋이나 에러 메시지)를 character_set_results 변수에 설정된 문자집합으로 변환해 클라이언트로 전송한다. 이때 결과 셋에 포함된 컬럼의 값이나 컬럼명과 같은 메타 데이터도 모두 character_set_results로 인코딩되어 클라이언트로 전송된다.

[그림-1] 전체 과정에서 변환 전의 문자집합과 변환해야 할 문자집합이 똑같다면 별도의 문자집합 변환 작업은 모두 생략한다. 예를 들어, 쿼리를 MySQL 서버로 전송할 때 character_set_client와 character_set_connection의 문자집합이 똑같이 utf8이라면 MySQL 서버가 클라이언트로부터 쿼리 요청을 받아도 문자집합은 변환하지 않는다. 결과를 클라이언트로 전송할 때도 character_set_results와 컬럼의 문자집합이 똑같다면 별도의 변환이 필요하지 않게 된다. 여기서 문자집합은 물론이고 콜레이션까지 포함해서 같은지 다른지를 비교한다.

​

character_set_client와 character_set_results, 그리고 character_set_connection이라는 3개의 시스템 설정 변수에 대해서는 클라이언트 프로그램이나 클라이언트 GUI 도구에서 마음대로 변경할 수 있다. 즉 이 시스템 설정 변수는 모두 세션 변수이면서 동적 변수다.

SET character_set_client = 'utf8';
SET character_set_results = 'utf8';
SET character_set_connection = 'utf8';