Skip to content

Latest commit

 

History

History
198 lines (159 loc) · 9.24 KB

7-2.md

File metadata and controls

198 lines (159 loc) · 9.24 KB

7장. 쿼리 작성 및 최적화 - INSERT, UPDATE, DELETE

데이터 베이스에는 다양한 Statement 문법이 존재하는데 이를 미리 익혀두면 개발시 더 적합한 방법을 찾아 사용함으로써 성능 개선 효과를 얻을 수 있다.

INSERT

INSERT와 AUTO_INCREMENT

  • MySQL에서는 숫자 값을 순차적으로 증가시키는 AUTO_INCREMENT라는 기능을 제공한다.
  • 테이블 칼럼에 속성을 부여하는 형태로 제공하며 여러 테이블간의 공유는 불가능하다.
AUTO_INCREMENT 제약 및 특성

AUTO_INCREMENT 값의 변화

  • 강제 저장한 값이 AUTO_INCREMENT의 현재 값보다 작을 때는 AUTO_INCREMENT의 값을 변경하지 않는다.
  • 강제 저장한 값이 AUTO_INCREMENT의 현재 값보다 클 때는 AUTO_INCREMENT의 현재 값에 상관없이 강제 저장한 값에 1을 더하여 변경한다.
  • AUTO_INCREMENT값에 0은 들어갈 수 없다. 만약 넣고 싶다면 sql_mode에 NO_AUTO_VALUE_ON_ZERO를 추가한다.

AUTO_INCREMENT의 제약

  • AUTO_INCREMENT값은 반드시 PK 혹은 Unique로 지정되어야 한다.
  • AUTO_INCREMENT 속성을 가진 칼럼을 단일 PK로 지정할때는 별도의 제약이 존재하지 않는다.
  • 만약 AUTO_INCREMENT 칼럼을 포함한 여러개의 칼럼으로 PK를 생성할때는 아래와 같은 제약이 존재한다.
    • AUTO_INCREMENT 칼럼이 PK의 가장 앞에 위치한다면 아무런 제약이 존재하지 않는다.
    • AUTO_INCREMENT 칼럼이 PK의 제일 앞이 아니라면 AUTO_INCREMENT 칼럼에 Unique를 걸어줘야한다. (InnoDB한정, MyISAM의 경우 unique없이 사용가능)

AUTO_INCREMENT관련 설정

  • auto_increment_increment - AUTO_INCREMENT 증가 값
  • auto_increment_offset - AUTO_INCREMENT 칼럼의 초기 값
AUTO_INCREMENT 잠금
  • 여러 커넥션에서 동시에 AUTO_INCREMENT를 사용할 때는 AutoIncrement 잠금이라는 테이블 단위의 잠금을 사용한다.
  • InnoDB의 레코드 잠금과 달리 새로운 값을 가져올때만 잠시 잠금을 얻었다가 바로 풀기때문에 AutoIncrement 잠금으로 인한 성능상 문제는 거의 없다.
  • INSERT 구문이 Rollback되더라고 AUTO_INCREMENT 값은 Rollback되지 않는다.
AUTO_INCREMENT 증가 값 가져오기
  • AUTO_INCREMENT에는 값을 순차적으로 늘리는 기능 외에도 가장 최근에 저장된 값을 조회하는 기능도 있다.
  • 가장 최근에 저장한 값은 커넥션 별로 독립적이다.
INSERT INTO `tb_autoincrement` VALUES (NULL, 'JungleKim');
SELECT LAST_INSERT_ID(); // return 1

INSERT IGNORE

로그성 데이터를 쌓을때 PK나 Unique Key가 중복되면 데이터를 버려도 무방한 경우가 있다. 이때 사용할 수 있는 구문이다.

  • INSERT IGNORE 문장이 실패하고 에러가 발생할 때
    • 칼럼과 값의 수가 일치하지 않는 경우 (Syntax Error)
  • INSERT IGNORE 문장이 에러 없이 완료되지만 저장되지 않거나 값이 변형되어 저장되는 경우
    • 값이 저장되지 않는 경우
      • 이미 테이블에 PK나 Unique Key가 존재하는 경우
      • Partition을 나눠놨는데 Partition 키가 적절하지 않는 경우
    • 값이 변형되어 저장 되는 경우
      • NOT NULL인 컬럼에 NULL을 저장하는 경우 (0 혹은 “” 혹은 비슷한 타입으로 값을 변형한 후 저장)

REPLACE

MySQL 고유의 기능이며 INSERT와 UPDATE의 기능을 묶은 기능이다.

REPLACE INTO employees VALUES (10001, 'Brandon', 'Lee');
REPLACE INTO employees SET emp_no=10001, first_name='Brandon', last_name='Lee';

REPLACE의 구문은 INSERT와 UPDATE 구문과 특별히 다르지 않다.

REPLACE 구문의 중복 레코드 판정

  • 테이블에 PK와 Unique Key가 동시에 존재한다면 모든 값에 중복값이 존재하는지 확인한다.
CREATE TABLE `tb_replace` (
	fb1 INT NOT NULL,
	fb2 INT NOT NULL,
	PRIMARY KEY (fd1),
	UNIQUE INDEX ux_fd2 (fd2)
)

INSERT `tb_replace` (fd1, fd2) VALUES (1,1), (2,2), (3,3), (4,4), (5,5);

# Replace 구문 실행
REPLACE INTO `tb_replace` (fb1, fb2) VALUES (5, 3);

# 결과
(1, 1)
(2, 2)
(4, 4)
(5, 3)

INSERT INTO … ON DUPLICATE KEY UPDATE …

REPLACE 구문과 흡사하게 동작하는 INSERT 구문이다. 중복된 레코드가 존재할때 제거하지 않고 값을 업데이트 한다는 것이 REPLACE와의 차이다. 중복된 레코드를 판정하는 기준은 REPLACE와 같다.

한 가지 특징이 있는데 중복시 INSERT하려던 값을 가져와서 UPDATE 구문에 사용할 수 있다는 점이다.

INSERT INTO tb_emp_stat (hire_year, emp_count)
SELECT YEAR(hire_date), COUNT(*) FROM employees GROUP BY YEAR(hire_date)
ON DUPLICATE KEY UPDATE
	emp_count=VALUES(emp_count);

VALUES(칼럼명) 구문을 통해 입력 하려던 값을 가져와서 사용할 수 있다.

INSERT … SELECT …

  • 타 테이블의 데이터를 읽어서 입력할 수 있다.
  • 같은 테이블의 대해서는 SELECT가 불가능하다.
INSERT INTO temp_employees
SELECT * FROM employees LIMIT 10;

LOAD DATA(LOCAL) INFILE …

LOAD DATA(LOCAL) INFILE 구문은 SELECT INTO OUTFILE구문에 대응되는 적재 기능의 쿼리다. 해당 구문은 일정한 규칙을 지닌 구분자로 구분된 데이터를 읽어 테이블에 입력한다. 일반적으로 INSERT구문을 통한 입력보다 20배정도 빠르다.

읽어 들일 파일은 항상 MySQL이 동작하는 서버 내부에 있어야한다.
만약, 클라이언트 컴퓨터에 존재하는 파일을 읽고 싶으면 `LOAD DATA LOCAL INFILE` 구문을 사용해야한다.

mysqlimport 라는 프로그램은 내부적으로 `LOAD DATA INFILE` 명령을 이용한다.
--use-threads 옵션을 통해 병렬로 데이터를 적재할 수도 있다.

다양한 케이스에 LOAD DATA INFILE 구문을 사용할 수 있다 (P. 506 ~ P.510)

  • 데이터 파일의 값과 칼럼의 개수가 동일한 경우
  • 데이터 파일의 값의 개수가 테이블의 칼럼 수보다 적은 경우
  • 데이터 파일의 값의 개수가 테이블의 칼럼수보다 많은 경우
  • 데이터 파일의 값을 연산해서 테이블의 칼럼에 저장하려는 경우
  • 데이터 파일이 MySQL 서버가 아닌 다른 컴퓨터에 있는 경우

LOAD DATA INFILE의 성능 향상

  • AUTO-COMMIT
    • AUTO-COMMIT이 켜져있으면 데이터를 쓸때마다 flush해서 디스크 I/O가 발생한다.
    • AUTO-COMMIT을 끈 후 LOAD DATA INFILE구문을 실행하고 수동으로 Commit하면 디스크 I/O를 줄여 성능 개선을 할 수 있다.
  • UNIQUE INDEX
    • Unique Key가 있을 때, 매번 중복 체크를 진행하기 때문에 성능의 하락이 있을 수 있다.
    • unique_checks 옵션을 임시로 꺼서 유니크 체크 없이 데이터를 입력하면 성능 개선을 할 수 있다.
      • 이 옵션을 끌 경우 insert buffer를 사용할 수 있다.
      • 데이터의 유니크함이 보장되야 한다.
  • FOREIGN KEY
    • 외래키가 걸려 있으면 매번 외래키가 존재하는지 확인하기 떄문에 성능의 하락이 올 수 있다.
    • foreign_key_checks 옵션을 임시로 꺼서 확인을 안하게 할 수 있다.
      • 외래키가 걸린 테이블에 데이터가 존재하는지 확인이 필수다.

LOAD DATA 명령의 문자열처리 문자열의 Charset이 다른 경우

  • 클라이언트와 커넥션의 옵션을 변경하여 맞춰 파일과 같은 인코딩으로 맞춰주거나
  • LOAD DATA INFILE 구문중 Charset 변경 구문을 사용한다.

UPDATE

UPDATE … ORDER BY … LIMIT n

MySQL에서는 UPDATE에 ORDER BY와 LIMIT를 통해 일부 레코드들의 값만 갱신하는 기능을 제공한다. 해당 쿼리를 사용할때 ORDER BY없이 LIMIT만 사용하면 Master와 Replication의 결과가 높은 확률로 다르기 때문에 문제가 생길 수 있다.

이 쿼리 사용시 Statement based replication 일때 Warning 메세지가 발생한다.

UPDATE salaries
SET salary=salary * 1.1
ORDER BY salary DESC LIMIT 10;

JOIN UPDATE

JOIN UPDATE에 사용되는 테이블은 읽기 참조만 되는 테이블은 읽기 잠금, 칼럼이 변경되는 테이블은 쓰기 잠금이 걸린다. 따라서 일반적인 웹서비스에서 데드락을 유발할 가능성이 높아서 사용하지 않는 것이 좋다.

UPDATE department 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;

DELETE

DELETE … ORDER BY … LIMIT n

UPDATE구문과 같다.

JOIN DELETE

JOIN DELETE의 경우 일반적으로 사용하는 DELETE의 구문과 다른 문법으로 작성한다.

# 일반적으로 사용하는 구문
DELETE FROM employees WHERE id=1;

# JOIN DELETE 구문
# DELETE와 FROM 사이에 삭제 대상이 되는 테이블들을 나열해준다.
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';

코멘트

  • django orm 에서 쿼리들 제공하는지 INSERT IGNORE, ON DUPLICATE KEY UPDATE 등
  • unique_checks와 insert buffer의 관계
  • 유니크 무결성을 검사할 수 있는 커맨드가 있는지