SELECT
  t.TABLE_SCHEMA AS `schema`,
  t.TABLE_NAME AS `table`,
  t.AUTO_INCREMENT AS `auto_increment`,
  c.DATA_TYPE AS `pk_type`,
  (t.AUTO_INCREMENT / (CASE DATA_TYPE
                         WHEN 'tinyint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 255, 127)
                         WHEN 'smallint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 65535, 32767)
                         WHEN 'mediumint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 16777215, 8388607)
                         WHEN 'int' THEN IF(COLUMN_TYPE LIKE '%unsigned', 4294967295, 2147483647)
                         WHEN 'bigint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 18446744073709551615, 9223372036854775807)
                         END / 100)) AS `max_value`
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME 
WHERE t.AUTO_INCREMENT IS NOT NULL
AND c.COLUMN_KEY = 'PRI'
AND c.DATA_TYPE LIKE '%int';

MySQL 5.7.20 버전부터 deprecate 되었고 8.0 에서는 사라졌다. 

쿼리를 Query Cache 에서 조회해보고 Query Cache에 있다면 Parsing, Optimizing, Executing 을 거치지 않고 결과를 반환한다. 

좋지 않은가?

근데 왜 없어졌을까?

Query Cache 에서는 아래의 두 쿼리가 다르다고 판단한다. 

SELECT * FROM TABLE

select * from table

Query Cache 에서 쿼리가 동일한 것으로 인식되기 위해서는 대소문자, 바이트 까지 동일해야한다. 

쿼리 스트링이 같다고 해도 데이터베이스, 프로토콜버전, 디폴트 문자셋 등이 다른 경우도 다른 쿼리로 판단한다. 

Query Cache 에 저장되어 있는 쿼리 결과의 대상 테이블의 변경 (INSERT, UPDATE, DELETE, ALTER TABLE, TRUNCATE 등등)이 발생할 경우 캐시에서 제거한다. 이때 다른 쓰레드에서 이 데이터를 참조하지 못하도록 Lock을 건다. 

(Query Cache는 여러 세션이 공유하는 부분으로 동기화를 위해 Query Cache Lock을 건다.)

Query Cache Lock 이 풀릴때까지 Query Cache에 접근하는 쓰레드들은 "Waiting for query cache lock" 대기가 걸린다. 

테이블 변경이 자주 발생하는 경우 Query Cache 를 사용하는 쿼리들의 대기 시간이 많아진다. 

이런 문제 때문에 사라진듯하다. 

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

갑자기 생각이 안날때가 있어서 정리해본다. 

- IGNORE INDEX : 특정 인덱스 사용안함

- USE INDEX

  - 옵티마이저에게 지정한 인덱스 사용 권장 

  - 권장이기 때문에 Table Scan 이더 빠르면 Table Scan 을 할수도 있다. 

- FORCE INDEX 

  - 인덱스 사용 강제

  - 인덱스를 사용하지 못하는 경우에만 해당 인덱스를 사용하지 않고 다른 플랜으로 처리 

  - Table Scan 이 더 성능이 좋아도 인덱스 사용 

참고 : https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

mysql 5.7 부터 performance_schema.metadata_locks 가 추가 되었으며 
이를 조회해서 metadata lock 을 잡고 있는 세션 확인이 가능하다.

auto_commit = false 상태로 쿼리를 하다보면 본인도 모르게 metadata lock 을 잡을 수 도 있다. 

예를 들면 python 에서 pymysql 로 접속시 기본 설정이 auto_commit = false 로 되어 있다. 

metadata lock 조회를 위한 선행 작업 

-- 기본 설정이 YES 지만 확인해본다.
SELECT * FROM performance_schema.setup_consumers WHERE NAME = 'global_instrumentation';

-- ENABLED = YES 가 아닐 경우 업데이트 
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';

-- MySQL 8.0 에서는 기본설정이 ENABLED = YES 지만 
-- 5.7 에서는 NO 로 되어 있어 확인이 필요하다.
SELECT * FROM performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl';

-- ENABLED = YES 가 아닐 경우 업데이트 
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

metadata lock 조회

SELECT 
  OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,
  THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();

1. 컬럼 타입은 text 가 아닌 json 컬럼 타입을 사용한다. 

    text 타입을 사용할 경우 json 문법을 안지켜서 나중에 문제가 되는 경우가 발생한다. 

2. json 내부의 속성중 업데이트가 자주 발생하는 경우 컴럼 전체 update를 하지 않고 

   json 함수를 사용하여 업데이트할 속성만 업데이트한다. 

   json 전체를 업데이트할 경우 binlog 증가 이슈가 발생한다. 

3. json 내부의 속성값을 조회조건으로 사용하는 경우 가상컬럼을 만들어서(인덱스 생성 필수) 사용한다. 

   json 을 그냥 검색할 경우 full scan 이슈가 발생한다. 

MySQL 5.7.8 부터 JSON 을 지원한다. 

JSON 문서 내의 값에서 Generated Column을 생성한 다음 해당 컬럼을 인덱싱하면 실제로 JSON 필드를 인덱싱할 수 있다. 

JSON 테스트 데이터 

{
    "id": 1,  
    "name": "Sally",  
    "games_played":{    
       "Battlefield": {
          "weapon": "sniper rifle",
          "rank": "Sergeant V",
          "level": 20
        },                                                                                                                          
       "Crazy Tennis": {
          "won": 4,
          "lost": 1
        },  
       "Puzzler": {
          "time": 7
        }
     }
 }

테스트 테이블 생성

CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
);

위 DDL문에서 아래 부분이 JSON 타입 컬럼 player_and_game 에서 name 속성 값을 참조하는 Generated Column (names_virtual) 을 지정한 부분이다. 

`names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name')

Generated Column 에 지정할수 있는 제약조건은 아래와 같다. 

[VIRTUAL|STORED] [UNIQUE [KEY]] [[NOT] NULL] [[PRIMARY] KEY]

VIRTUAL, STORED는 컬럼의 값이 실제로 저장되는지 여부를 나타낸다.

- VIRTUAL : 기본값, 컬럼의 값 저장안함, 저장 공간없음, 행을 읽을 때마다 계산해서 처리한다.

                    인덱스를 생성하면 값이 인덱스 자체에 저장된다.

- STORED : 데이터가 테이블에 저장(입력,수정)될때 컬럼의 값도 저장된다. 

                    인덱스는 일반적인 인덱스와 동일하게 처리된다. 

아래에 테스트 소스 깃헙에 있는 mysql-json.sql 파일에 있는 insert 문을 사용한다. 

INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
    "id": 1,  
    "name": "Sally",
    "games_played":{    
       "Battlefield": {
          "weapon": "sniper rifle",
          "rank": "Sergeant V",
          "level": 20
        },                                                                                                                          
       "Crazy Tennis": {
          "won": 4,
          "lost": 1
        },  
       "Puzzler": {
          "time": 7
        }
      }
   }'
);
....

입력된 데이터 확인, names_virtual 컬럼에서 JSON 문서의 name 속성값 확인됨 

mysql> select * from players;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| id | player_and_games                                                                                                                                                                                           | names_virtual |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
|  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
|  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
|  3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}}           | Ali           |
|  4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred        |
|  5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}}          | Phil          |
|  6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}}             | Henry         |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
6 rows in set (0.00 sec)

players 테이블 컬럼 정보 확인 

mysql> show columns from players;
+------------------+--------------+------+-----+---------+-------------------+
| Field            | Type         | Null | Key | Default | Extra             |
+------------------+--------------+------+-----+---------+-------------------+
| id               | int unsigned | NO   | PRI | NULL    |                   |
| player_and_games | json         | NO   |     | NULL    |                   |
| names_virtual    | varchar(20)  | NO   |     | NULL    | VIRTUAL GENERATED |
+------------------+--------------+------+-----+---------+-------------------+

Extra 에 VIRTUAL GENERATED 으로 Generated Column 이  VIRTUAL 인것을 보여준다. 

STORED 로 지정된 컬럼의 경우 STORED GENERATED 로 표시된다. 

Battlefield 레벨, 승리한 테니스 게임, 패배한 테니스 게임 및 퍼즐 게임 시간 컬럼을 추가한다.

ALTER TABLE `players` ADD COLUMN `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL AFTER `names_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL AFTER `battlefield_level_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL AFTER `tennis_won_virtual`;  
ALTER TABLE `players` ADD COLUMN `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL AFTER `tennis_lost_virtual`;

mysql> show columns from players;
+---------------------------+--------------+------+-----+---------+-------------------+
| Field                     | Type         | Null | Key | Default | Extra             |
+---------------------------+--------------+------+-----+---------+-------------------+
| id                        | int unsigned | NO   | PRI | NULL    |                   |
| player_and_games          | json         | NO   |     | NULL    |                   |
| names_virtual             | varchar(20)  | NO   |     | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int          | NO   |     | NULL    | VIRTUAL GENERATED |
| times_virtual             | int          | NO   |     | NULL    | VIRTUAL GENERATED |
+---------------------------+--------------+------+-----+---------+-------------------+

Generated Column을 검색하는 쿼리 실행계획 확인 

mysql> EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

해당 컬럼에 인덱스 생성하고 실행계획 확인, 생성한 인덱스 인덱스 잘탄다. 

mysql> CREATE INDEX `names_idx` ON `players`(`names_virtual`);  
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ref
possible_keys: names_idx
          key: names_idx
      key_len: 82
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

다른 Generated Column 들도 인덱스를 생성하고, 생성된 인덱스를 확인한다. 

mysql> CREATE INDEX `times_idx` ON `players`(`times_virtual`);  
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX `won_idx` ON `players`(`tennis_won_virtual`);  
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX `lost_idx` ON `players`(`tennis_lost_virtual`);  
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX `level_idx` ON `players`(`battlefield_level_virtual`);  
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM `players`;
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| players |          0 | PRIMARY   |            1 | id                        | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | names_idx |            1 | names_virtual             | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | times_idx |            1 | times_virtual             | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | won_idx   |            1 | tennis_won_virtual        | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | lost_idx  |            1 | tennis_lost_virtual       | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| players |          1 | level_idx |            1 | battlefield_level_virtual | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.03 sec)

STORED 로 지정된 Generated Column 을 사용하는 경우 

1. PRIMARY KEY 를 잡아야될경우 
2. 일반 BTREE 대신 FULLTEXT 또는 RTREE 인덱스가 필요한 경우 
3. 인덱스를 사용하지 않는 스캔이 많이 발생하는 컬럼일 경우 

테스트용 테이블 생성 

CREATE TABLE `players_two` (  
    `id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,
    `player_and_games` JSON NOT NULL,
    `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL,
    `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL,
    `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL,
    `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL,
    `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL, 
    PRIMARY KEY (`id`),
    INDEX `times_index` (`times_virtual`),
    INDEX `names_index` (`names_virtual`),
    INDEX `won_index` (`tennis_won_virtual`),
    INDEX `lost_index` (`tennis_lost_virtual`),
    INDEX `level_index` (`battlefield_level_virtual`)
);

PK 컬럼을 STORED 로 지정하였다. 

`id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,

SHOW COLUMNS 로 컬럼 확인 

mysql> SHOW COLUMNS FROM `players_two`;
+---------------------------+-------------+------+-----+---------+-------------------+
| Field                     | Type        | Null | Key | Default | Extra             |
+---------------------------+-------------+------+-----+---------+-------------------+
| id                        | int         | NO   | PRI | NULL    | STORED GENERATED  |
| player_and_games          | json        | NO   |     | NULL    |                   |
| names_virtual             | varchar(20) | NO   | MUL | NULL    | VIRTUAL GENERATED |
| times_virtual             | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int         | NO   | MUL | NULL    | VIRTUAL GENERATED |
+---------------------------+-------------+------+-----+---------+-------------------+

출처 :

https://www.compose.com/articles/mysql-for-json-generated-columns-and-indexing/

https://dev.mysql.com/blog-archive/indexing-json-documents-via-virtual-columns/

테스트 소스 :

https://github.com/compose-ex/mysql-json-indexing-generated-columns/blob/master/mysql-json.sql

열 암호화시에 char, nchar 데이터 타입 컬럼의 공백  검색 이슈가 있다.

테스트 테이블 생성

이름 컬럼 암호화 

이름 검색시 오류가 발생한다. 

피연산자 유형 충돌: varchar은(는) varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColEncKey', column_encryption_key_database_name = 'Test') collation_name = 'Korean_Wansung_CI_AS'과(와) 호환되지 않습니다.

아래와 같은 형식으로 검색해야 에러없이 검색된다. 

그런데 '일호'가 검색되지 않는다.. 

왜?

이름 컬럼은 nchar(20) 이다. char, nchar 는 입력한 값이 데이터 타입의 크기보다 작으면 그만큼 공백이 입력된다. 

'일호                  ' 로 저장되어 있다. 

열 암호화가 적용된 컬럼에 테스트용 데이터 입력 

열 암호화가 적용된 컬럼은 insert 시에도 변수를 선언해서 사용해야 한다.

특이사항은 insert 문 하나인데 (1개 행 적용됨)이 3개가 뜬다..

궁금하면 이것은 프로파일러를 떠보기 바란다. 

문제가 되는 부분

열 암호화 이후에 insert 된 데이터는 공백 없이 조회가 된다. 

빈 테이블을 열 암호화해서 사용할 경우 문제가 없겠지만 데이터가 있는 상태에서 열 암호화 해서 사용할 경우

서비스에서 데이터 조회하는 부분에서 이슈가 된다. (데이터가 입력된 시점에 따라 어떤건 조회되고 어떤건 안되고..)

해결방안

- trim() 해서 업데이트 하면되지 않아? 라고 생각할 수 있다.

  여러가지 방법으로 trim() 해보려고 했지만 쿼리나 프로시져로는 안된다. 

  정리하기 힘들정도로 여러가지 형태의 쿼리, 프로시져를 짜서 삽질을 해봤다.  안된다. 

- set ansi_padding off 를 적용해서 임시 테이블을 만들어서 데이터를 입력하고 열 암호화를 적용하면 되지 않을까?

  char 는 set ansi_padding off 가 먹히지만 nchar 는 안먹힌다. 

  문제되는 대부분의 컬럼이 nchar 다.. 

  컬럼 타입을 char 나 nvarchar 로 바꿀까? 안된데.. 못 바꾼데.. 

- 데이터를 다 읽어서 다시 update 하는 어플리케이션을 만들어서 돌려야된다.

  이거 말고는 답을 못 찾았다. ㅡ_ㅡ);

SQL Server 2016 에 추가된 기능으로 특정 컬럼을 암호화할 수 있는 기능이다.

이때만 해도 내가 이걸 쓰게될줄은 몰랐다. 

시작하기전에 아래의 몇가지 사항을 이해하고 넘어가면 좋다. 

- 열 마스터 키 (CMK) : 열 암호화 키를 보호하는 암호화 키

- 열 암호화 키 (CEK) : 암호화된 열을 보호하는 암호화 키

- 열 마스터 키의 저장소 

   - Azure Key Vault : Microsoft Azure 내에서 중요한 데이터의 암호화 및 암호 해독에 사용되는 암호화 키 및 비밀을 보호하고 관리하는 데 사용되는 키 자격 증명 모음 (주의사항 이걸 사용하다 그만 써야지 하면 뒷감당이 힘들다.)

  - Windows 인증서 저장소 : 인증서를 저장하고 관리하는 Windows에 내장된 인증서 컨테이너

  - HSM : 민감한 데이터를 안전하게 저장하도록 특별히 설계된 하드웨어 장치 

- 열 수준 암호화 유형
  - 결정적 : 항상 동일한 암호문으로 암호화하므로 특정 작업(포인트 조회, 구별, 그룹화 기준)에 사용할 수 있고 인덱싱가능
  - 무작위 : 더 안전하지만 어떤 작업에서도 평가하거나 사용할 수 없으며(쓰기/표시 전용) 인덱싱불가

1. 열 마스터 키 만들기 (column master key)

- 개체 탐색기에서 데이터베이스를 선택
- 보안 -> Always Encrypted 키 -> 열 마스터 키 
  새 열 마스터 키 생성 

- 키 저장소 Windows 인증서 저장소 - 로컬 컴퓨터를 선택
  인증서 생성 

- 인증서 생성 버튼 클릭시 인증서가 생성되며 생성된 정보가 노출된다. 

- Windows 인증서 저장소에 인증서가 생성되어 있는지 확인 

2. 열 암호화 키 생성 (column encryption key)

- 개체 탐색기에서 데이터베이스를 선택
- 보안 -> Always Encrypted 키 -> 열 암호화 키 
  새 열 암호화 키 생성 

- 새 열 암호화키 생성 
  생성한 열 마스터 키를 선택하고 열 암호화 키 이름을 입력한다. 

3. 테스트 데이터 생성

CREATE TABLE [dbo].[tb_test]
(
 [Seq] [int] IDENTITY(1,1), 
 [Name] [nvarchar](20) not null, 
 [Phone] [varchar](20) not null, 
 [Email] [varchar](50) not null,
 PRIMARY KEY CLUSTERED ([Seq] ASC)
)
GO

insert into [dbo].[tb_test] values ('홍길동','010-1234-5678','hhhhh@email.com');
insert into [dbo].[tb_test] values ('김길동','010-1234-1234','kim@email.com');
insert into [dbo].[tb_test] values ('최길동','010-5678-5678','choi@email.com');
insert into [dbo].[tb_test] values ('강길동','010-5678-1234','kang@email.com');
insert into [dbo].[tb_test] values ('한길동','010-5678-5678','han@email.com');
GO

select * from [dbo].[tb_test];

4. 열 암호화 

- 개체 탐색기에서 데이터베이스를 선택
   태스크 -> 열 암호화 

- 암호화할 열 선택 

5. 데이터 확인 (데이터베이스 서버에서)

- 암호화한 컬럼은 데이터가 암호화되어 확인이 안된다. 

- 새 세션으로 접속시 Always Encrypted -> Always Encrypted(열 암호화) 사용 선택후 접속 

- 데이터 조회시 오류가 발생한다. 

6. Windows 인증서 저장소에 저장된 열 마스터 키 만들때 사용한 인증서 내보내기 

- 열 마스터 키를 만들때 사용한 인증서를 내보낸다. 

7. 인증서 파일을 클라이언트로 복사하여 등록

- 인증 파일을 더블클릭하면 인증서 가져오기 마법사가 시작된다. 
  인증서 저장소 위치를 로컬 컴퓨터로 지정한다. 

- 인증서 내보내기에서 지정한 암호를 입력한다.

8. 인증서를 가져온 후 클라이언트에서 데이터 조회