[SQL] 쿼리 작성 3편 - INSERT, UPDATE, DELETE

5. INSERT

• 많은 INSERT 문장이 동시에 실행되는 경우, INSERT 문장 자체보다는 테이블의 구조가 성능에 더 큰 영향을 미친다.
• 하지만 INSERT의 성능과 SELECT의 성능을 동시에 빠르게 만들 수 있는 테이블 구조는 없다.

5.1) 고급옵션

(1) INSERT IGNORE

• INSERT IGNORE 옵션은 INSERT 도중 에러가 발생하면, 처리하던 내용을 포기하고 트랜잭션을 롤백하여 에러 대신 경고 메시지로 에러 수준을 낮춘다.
• INSERT IGNORE 옵션은 프라이머리 키나 유니크 인덱스와 중복되는 경우, 해당 레코드를 무시하고 다음 레코드를 처리할 수 있게 해준다.
• 데이터 타입이 일치하지 않아서 INSERT를 할 수 없는 경우, 기본값으로 INSERT 한다.
• 하지만, 제대로 검증되지 않은 INSERT IGNORE 문장은 의도하지 않은 에러까지 모두 무시할 수 있으므로 주의하자.

-- // 프라이머리 키 (emp_no, from_date)
INSERT IGNORE INTO salaries (emp_no, salary, from_date, to_date) VAlUES
(10001, 60117, '1986-06-26', '1987-06-26'),
(10001, 62102, '1987-06-26', '1988-06-25'),
(10001, 66074, '1988-06-25', '1989-06-25'),
(10001, 66596, '1989-06-25', '1990-06-25'),
(10001, 66961, '1990-06-25', '1991-06-25');
-- Query OK, 0 rows affected, 5 warnings (0.02 sec)
-- Records: 5  Duplicates: 5  Warnings: 5

-- // IGNORE 키워드가 없으면 INSERT 실패
INSERT INTO salaries VALUES(NULL, NULL, NULL, NULL);
-- ERROR 1048 (23000): Column 'emp_no' cannot be null

-- // INGORE 키워드가 있으면, NOT NULL 칼럼인 emp_no, from_date에 기본 값을 저장
INSERT IGNORE INTO salaries VALUES(NULL, NULL, NULL, NULL);
-- Query OK, 1 row affected, 4 warnings (0.02 sec)

SHOW WARNINGS\G
/*
*************************** 1. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'emp_no' cannot be null
*************************** 2. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'salary' cannot be null
*************************** 3. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'from_date' cannot be null
*************************** 4. row ***************************
  Level: Warning
   Code: 1048
Message: Column 'to_date' cannot be null
*************************** 5. row ***************************
  Level: Warning
   Code: 1062
*/

(2) INSERT ... ON DUPLICATE KEY UPDATE

• INSERT ... ON DUPLICATE KEY UPDATE 옵션은 프라이머리 키나 유니크 인덱스와 중복되는 경우, 기존 레코드를 UPDATE 하는 방식으로 작동한다.
• REPLACE 문장도 비슷한 역할을 하지만 REPLACE는 내부적으로 DELETE 와 INSERT의 조합으로 작동한다. ➔ DELETE와INSERT 조합은 성능상 이점이 없기 때문에 권장하지 않는다.

CREATE TABLE daily_statistic(
    target_date DATE NOT NULL,
    stat_name VARCHAR(10) NOT NULL,
    stat_value BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY(target_date, stat_name)
);

-- 해당 날짜의 레코드가 존재하면 stat_value를 1씩 증가
INSERT INTO daily_statistic (target_date, stat_name, stat_value)
VALUES (DATE(NOW()), 'VISIT', 1) ON DUPLICATE KEY UPDATE stat_value = (stat_value + 1);

• INSERT ... ON DUPLICATE KEY UPDATE 절에서는 GROUP BY 결과인 COUNT(*)를 참조할 수 없다.
  • COUNT() 함수 대신 VALUES() 함수를 사용할 수 있다.
  • 하지만, VALUES() 함수는 MySQL 8.0.20 이후 버전에서는 지원되지 않을(Deprecated) 예정이다.
  • MySQL 8.0.20 이후 버전에서는 INSERT ... SELECT ... 에 별칭을 주거나 INSERT 되는 레코드에 별칭을 부여해서 사용할 수 있다.

--// ON DUPLICATE KEY UPDATE 절에서 COUNT(*)을 참조할 수 없음
INSERT INTO daily_statistic
  SELECT DATE('2023-01-27'), 'VISIT', COUNT(*)
  GROUP BY DATE('2023-01-27')
  ON DUPLICATE KEY UPDATE stat_value = stat_value + COUNT(*);
-- ERROR 1111 (HY000): Invalid use of group function

--// COUNT() 함수 대신 VALUES() 함수를 사용할 수 있음
INSERT INTO daily_statistic
  SELECT DATE('2023-01-27'), 'VISIT', COUNT(*)
  GROUP BY DATE('2023-01-27')
  ON DUPLICATE KEY UPDATE stat_value = stat_value + VALUES(stat_value);
  
SHOW WARNINGS\G
/*
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: 'VALUES function' is deprecated and will be removed in a future release. 
Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
*/


--// MySQL 8.0.20 이후 버전
-- (1) SELECT 절에 별칭을 부여하는 방식
INSERT INTO daily_statistic
  SELECT target_date, stat_name, stat_value
  FROM (
      SELECT DATE('2023-01-27') target_date, 'VISIT' stat_name, COUNT(*) stat_value
      GROUP BY (target_date)
  ) new
ON DUPLICATE KEY UPDATE 
  daily_statistic.stat_value = daily_statistic.stat_value + new.stat_value;
  
-- (2) INSERT 되는 레코드에 별칭을 부여하는 방식
INSERT INTO daily_statistic
VALUES ('2023-01-27', 'VISIT', 1),
       ('2023-01-28', 'VISIT', 1) AS new
ON DUPLICATE KEY UPDATE 
  daily_statistic.stat_value = daily_statistic.stat_value + new.stat_value;
  
-- (3) INSERT 되는 레코드와 칼럼에 별칭을 부여하는 방식
INSERT INTO daily_statistic 
  SET target_date = '2023-01-28', 
      stat_name = 'VISIT', 
      stat_value = 1 
  AS new(f1, f2, f3)
ON DUPLICATE KEY UPDATE
  daily_statistic.stat_value = daily_statistic.stat_value + new.f3;

5.2) LOAD DATA 명령 주의 사항

• LOAD DATA 명령은 일반적인 INSERT 명령과 비교했을 때 매우 빠르다.

• 하지만 단점도 있다.

  • 단일 스레드로 실행되므로 시간이 지날수록 INSERT 속도가 저하된다.
  • 단일 트랜잭션으로 실행되므로 언두 로그가 많이 쌓여 오버헤드를 만든다.
    ➔ 데이터가 매우 커서 실행 시간이 길어진다면 다른 온라인 트랜잭션 쿼리들의 성능이 영향을 받을 수 있다.

• LOAD DATA 문장으로 적재할 데이터 파일을 하나보다는 여러 개의 파일로 준비해서 여러 트랜잭션으로 나누어 실행하는 것이 좋다.

• 테이블 간 데이터 복사에는 LOAD DATA 보다는 INSERT ... SELECT ... 문장에서 WHERE 조건으로 잘라서 처리하는 것이 좋다.

5.3) 성능을 위한 테이블 구조

• INSERT 문장의 성능은 쿼리 문장 자체보다는 테이블의 구조에 의해 많이 결정된다.

(1) 대량 INSERT 성능

• 수백 건, 수천 건의 레코드를 INSERT 한다면 프라이머리 키 값을 기준으로 미리 정렬해서 INSERT 문장을 구성하는 것이 성능에 도움이 될 수 있다.

--// secure_file_priv 값이 "" 이어야 함
SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
|                           |
+---------------------------+

--// PK 값인 (emp_no, from_date) 정렬
SELECT * 
FROM salaries
ORDER BY emp_no, from_date
INTO OUTFILE '/tmp/sorted_by_primary.csv'
CHARACTER SET euckr
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

--// (salary, to_date) 칼럼으로 정렬
SELECT * 
FROM salaries
ORDER BY salary, to_date
INTO OUTFILE '/tmp/sorted_by_random.csv'
CHARACTER SET euckr
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

--// PK로 정렬된 csv 파일 LOAD
LOAD DATA INFILE '/tmp/sorted_by_primary.csv'
    INTO TABLE salaries_temp_primary
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
-- Query OK, 2844047 rows affected (2 min 8.09 sec)
-- Records: 2844047  Deleted: 0  Skipped: 0  Warnings: 0 
    
--// PK로 정렬되지 않은 csv 파일 LOAD
LOAD DATA INFILE '/tmp/sorted_by_random.csv'
    INTO TABLE salaries_temp_random
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
-- Query OK, 2844047 rows affected (3 min 16.37 sec)
-- Records: 2844047  Deleted: 0  Skipped: 0  Warnings: 0

(2) 프라이머리 키 선정

• 테이블의 프라이머리 키는 INSERT 성능을 결정하는 가장 중요한 부분이다.
• 프라이머리 키의 선정은 INSERT와 SELECT 성능의 대립되는 두 가지 요소 중에서 하나를 선택해야 함을 의미한다.
• 프라이머리 키 선정 방식
  • INSERT가 빈번한 테이블
    • 단조 증가 또는 단조 감소하는 패턴의 값을 프라이머리 키로 선택
    • 인덱스의 개수를 최소화
  • SELECT가 빈번한 테이블
    • SELECT 쿼리의 조건을 기준으로 프라이머리 키로 선택
    • 쿼리에 맞게 필요한 인덱스들을 추가

(3) Auto-Increment 칼럼

• 자동 증가(Auto Increment) 칼럼을 이용하면 클러스터링되지 않는 테이블의 효과를 얻을 수 있다.

• 자동 증가 값의 채번을 위해서는 잠금이 필요하다.

  • innodb_autoinc_lock_mode

    • 0: 항상 AUTO_INC 잠금을 사용 ➔ 서비스용에서는 이 방식을 사용할 필요 없음
    • 1: 한 건씩 처리할 때는 AUTO_INC 잠금을 사용하지 않고, 여러 레코드를 처리할 때는 AUTO_INC 잠금을 사용 ➔ INSERT 순서대로 채번된 자동 증가 값은 일관되고, 연속된 번호를 갖게 됨
    • 2: AUTO_INC 잠금을 사용하지 않음 ➔ INSERT 순서와 채번된 번호의 연속성을 보장하지 않음

  • 쿼리 기반의 복제를 사용하는 경우, 소스 서버와 레플리카 서버의 자동 증가 값이 동기화되지 못할 수도 있으므로 주의해야 한다.

  • MySQL 5.7 버전의 기본 값은 1, MySQL 8.0 버전의 기본 값은 2이다.
    ➔ 복제의 바이너리 로그 포맷이 MySQL 5.7 버전에서는 STATEMENT였지만, MySQL 8.0 버전에서 ROW로 변경되었기 때문에, 복제를 STATEMENT 바이너리 로그 포맷으로 사용 중이라면 innodb_autoinc_lock_mode 또한 1로 사용해야 한다.
    

• LAST_INSERT_ID() 함수를 통해 가장 마지막에 증가된 AUTO_INCREMENT 값을 조회할 수 있다.

  • SELECT MAX() ...를 사용하는 방법은 잘못된 결과를 반환할 수 있으므로 사용하지 않는 것이 좋다.

CREATE TABLE tb_autoincrement (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(200),
    PRIMARY KEY(id)
);

INSERT INTO tb_autoincrement VALUES (NULL, 'Georgi Fellona');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

6. UPDATE와 DELETE

6.1) UPDATE ... ORDER BY ... LIMIT n

• UPDATE나 DELETE 문장에 ORDER BY 절과 LIMIT 절을 사용하면 정렬된 칼럼을 차례대로 잘라서 처리할 수 있다.
• 하지만 이 방식을 사용할 경우, 복제 소스 서버에서 경고 메시지가 출력될 수 있다.
  • 바이너리 로그 포맷이 ROW일 때는 문제가 되지 않지만, STATEMENT일 때는 ORDER BY에 의해 정렬되더라도 중복된 값의 순서가 복제 소스 서버와 레플리카 서버에서 달라질 수도 있기 때문이다.

SET binlog_format=STATEMENT;

DELETE FROM employees ORDER BY last_name LIMIT 10;

SHOW WARNINGS \G
/*
*************************** 1. row ***************************
Level: Note
 Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. 
The statement is unsafe because it uses a LIMIT clause.
This is unsafe because the set of rows included cannot be predicted.
*/

SET binlog_format=ROW;

6.2) JOIN UPDATE

• 조인된 결과 레코드를 변경 및 삭제하는 쿼리를 JOIN UPDATE라고 한다.
• JOIN UPDATE는 조인되는 모든 테이블에 대해 읽기 참조만 되는 테이블은 읽기 잠금이 걸리고, 칼럼이 변경되는 테이블은 쓰기 잠금이 걸린다.
  ➔ 배치 프로그램이나 통계용 UPDATE 문장에서는 유용하게 사용할 수 있지만, 웹 서비스 같은 OLTP 환경에서는 데드락을 유발할 가능성이 높으므로 빈번하게 사용하는 것은 피하는 것이 좋다.

CREATE TABLE tb_join_update (
    emp_no INT,
    first_name VARCHAR(14),
    PRIMARY KEY(emp_no)
);

INSERT INTO tb_join_update 
VALUES (10001, NULL), (10002, NULL), (10003, NULL), (10004, NULL);
+--------+------------+
| emp_no | first_name |
+--------+------------+
|  10001 | NULL       |
|  10002 | NULL       |
|  10003 | NULL       |
|  10004 | NULL       |
+--------+------------+

UPDATE tb_join_update t1, employees e
SET t1.first_name = e.first_name
WHERE e.emp_no = t1.emp_no;
+--------+------------+
| emp_no | first_name |
+--------+------------+
|  10001 | Georgi     |
|  10002 | Bezalel    |
|  10003 | Parto      |
|  10004 | Chirstian  |
+--------+------------+

• JOIN UPDATE 문장에서는 GROUP BY나 ORDER BY 절을 사용할 수 없다.
  ➔ 문법적으로 지원하지 않는 SQL에 대해 서브쿼리를 이용하여 작성할 수 있다.

ALTER TABLE departments ADD emp_count INT;

--// 부서별로 사원의 수를 emp_count 칼럼에 업데이트
-- JOIN UPDATE 문장에서는 GROUP BY나 ORDER BY 절을 사용할 수 없음
UPDATE departments d, dept_emp de
  SET d.emp_count = COUNT(*)
WHERE de.dept_no = d.dept_no
GROUP BY de.dept_no;
-- check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY de.dept_no' at line 4

-- JOIN UPDATE 문장에서 GROUP BY 절을 처리하기 위해 서브쿼리로 변경
UPDATE departments d, 
    (SELECT de.dept_no, COUNT(*) AS emp_count
     FROM dept_emp de
     GROUP BY de.dept_no
    ) dc
  SET d.emp_count = dc.emp_count
WHERE dc.dept_no = d.dept_no;

• 옵티마이저가 적절한 조인 순서를 결정하지 못한다면, STRAIGHT_JOIN을 사용하거나 JOIN_ORDER 힌트를 사용할 수 있다.
  • STRAIGHT_JOIN 키워드 왼쪽에 명시된 테이블이 드라이빙 테이블이 되고, 오른쪽 테이블이 드리븐 테이블이 된다.

6.3) 여러 레코드 UPDATE

• 하나의 UPDATE 문장으로 여러 개의 레코드를 업데이트하는 경우, 모든 레코드를 동일한 값으로만 업데이트할 수 있었다.
• MySQL 8.0 버전부터는 레코드 생성 문법을 이용해 레코드별로 서로 다른 값을 업데이트 할 수 있게 되었다.
  • VALUES ROW(...), ... 문법을 사용하면 SQL 내에서 임시 테이블을 생성하는 효과를 낼 수 있다.

CREATE TABLE user_level (
    user_id BIGINT NOT NULL,
    user_lv INT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY(user_id)
);

INSERT INTO user_level 
VALUES (1, 2, NOW()), (2, 4, NOW()), (3, 6, NOW()), (4, 8, NOW());
+---------+---------+---------------------+
| user_id | user_lv | created_at          |
+---------+---------+---------------------+
|       1 |       2 | 2023-01-28 23:05:17 |
|       2 |       4 | 2023-01-28 23:05:17 |
|       3 |       6 | 2023-01-28 23:05:17 |
|       4 |       8 | 2023-01-28 23:05:17 |
+---------+---------+---------------------+

UPDATE user_level ul
INNER JOIN (VALUES ROW(1, 1),
                   ROW(2, 4)
           ) new_user_level (user_id, user_lv)
ON new_user_level.user_id = ul.user_id
SET ul.user_lv = ul.user_lv + new_user_level.user_lv;
+---------+---------+---------------------+
| user_id | user_lv | created_at          |
+---------+---------+---------------------+
|       1 |       3 | 2023-01-28 23:05:17 |
|       2 |       8 | 2023-01-28 23:05:17 |
|       3 |       6 | 2023-01-28 23:05:17 |
|       4 |       8 | 2023-01-28 23:05:17 |
+---------+---------+---------------------+

6.4) JOIN DELETE

• JOIN DELETE 문장은 단일 테이블의 DELETE 문장과는 조금 다른 문법으로 쿼리를 작성해야 한다.
  • 하나의 테이블에서 삭제 : DELETE FROM table ...
  • 여러 테이블에서 삭제 : DELETE와 FROM절 사이에 삭제할 테이블명 명시

-- 조인한 결과를 통해 employees 테이블의 레코드만 삭제
DELETE e
FROM employees e, dept_emp de, departments d
WHERE e.emp_no = de.emp_no 
  AND de.dept_no = d.dept_no 
  AND d.dept_no = 'd001';
  
-- 조인한 결과를 통해 여러 테이블의 레코드를 삭제
DELETE e, de
FROM employees e, dept_emp de, departments d
WHERE e.emp_no = de.emp_no 
  AND de.dept_no = d.dept_no 
  AND d.dept_no = 'd001';

• 옵티마이저가 적절한 조인 순서를 결정하지 못한다면, STRAIGHT_JOIN을 사용하거나 JOIN_ORDER 힌트를 사용할 수 있다.