MySQL
Real MySQL [7-22] 쿼리 작성 및 최적화 - AUTO_INCREMENT
weicome
2017. 4. 6. 19:51
본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
- 포스팅에 포함된 Query 결과는 AWS EC2 FreeTier(CentOS7.2) 환경에 MySQL v5.7.17 에서 실행한 결과를 포함하였습니다. 실행환경에 따라 Query 실행시간이나 RDBMS 실행계획이 다를 수 있습니다.
INSERT
INSERT와 AUTO_INCREMENT
MySQL 에서는 순차적으로 증가하는 숫자값을 가져오기 위해 AUTO_INCREMENT라는 기능을 제공한다. AUTO_INCREMENT는 테이블의 칼럼을 부여하는 옵션 형태로 사용하므로 자동 증가 기능은 하나의 테이블에서만 순차적으로 증가하게 된다. AUTO_INCREMENT는 테이블 생성 스크립트에 포함되므로 관리하거나 사용하기가 편하지만 여러 테이블에 동시에 사용할 수는 없다.
AUTO_INCREMENT 제약 및 특성
우선 테이블의 칼럼에 AUTO_INCREMENT 속성을 부여하는 방법을 살펴보자
위의 첫번째 INSERT 문장처럼 AUTO_INCREMENT 속성으로 정의된 칼럼은 별도의 값을 할당하지 않고 사용하는 것이 일반적이다.
두 번째 INSERT 문장에서는 AUTO_INCREMENT 속서응로 정의된 member_id 칼럼에 강제로 값을 할당했다. 이때는 AUTO_INCREMENT가 현재 가진 값과는 과는 관계없이 INSERT 문장에서 지정된 값을 칼럼에 저장한다. 만약 두 번째 형태의 INSERT 문장을 사용할 때 AUTO_INCREMENT의 현재 값을 저장하려면 AUTO_INCREMENT 속성의 칼럼에 NULL이나 0을 저장하면 된다.
두번째 쿼리와 같이 임의의 값을 강제로 AUTO_INCREMENT 칼럼에 저장하면 다음의 2가지 방법으로 AUTO_INCREMENT 의 현재 값이 갱신된다.
- 강제 지정한 값이 AUTO_INCREMENT의 현재 값보다 작을 때는 AUTO_INCREMENT의 현재 값이 변하지 않는다.
- 강제 지정한 값이 AUTO_INCREMENT의 현재 값보다 클 때는 AUTO_INCREMENT의 현재 값이 얼마였든지 관계없이 강제로 저장된 값에 1을 더한 값이 AUTO_INCREMENT 의 다음 값으로 변경된다.
테이블에서 AUTO_INCREMENT 칼럼을 사용할 때는 반드시 다음 규칙을 지켜야 한다.
- AUTO_INCREMENT 속성을 가진 칼럼은 반드시 프라이머리 키나 유니크 키의 일부로 정의돼야 한다.
- AUTO_INCREMENT 속성을 가진 칼럼 하나로 프라이머리 키를 생성할 때는 아무런 제약이 없다.
AUTO_INCREMENT 값은 항상 1씩 증가하는 것은 아니다. MySQL 서버에는 auto_increment_increment와 auto_increment_offset 시스템 변수가 있는데, auto_increment_offset은 AUTO_INCREMENT 속성의 칼럼 초기 값을 정의하며 auto_increment_increment는 AUTO_INCREMENT 값이 증가할 때마다 얼마씩 증가시킬 것인지를 결정한다.
AUTO_INCREMENT 잠금
여러 커넥션에서 AUTO_INCREMENT를 동시에 사용할 때는 동기화 처리가 필요한데, 이를 위해 MySQL 에서는 AutoIncrement 잠금이라는 테이블 단위의 잠금을 사용한다. 테이블 단위의 잠금이란 수백 개으 키너게션이 동시에 하나의 테이블에 INSERT 문장을 실행하고 있다 하더라도 특정 시점에서는 한 테이블의 AutoIncrement 잠금은 반드시 하나의 커넥션만 가질 수 있다는 것을 의미한다.
AutoIncrement 잠금은 AUTO_INCREMENT의 현재 값을 가져올 때만 잠금이 걸렸다가 즉시 해제된다. 그래서 AutoIncrement 잠금은 테이블 단위의 잠금이긴 하지만 성능상 문제가 될 때는 거의 없다. 그리고 AUTO_INCREMENT 값이 INSERT 문장으로 한번 증가하면 해당 INSERT 문장을 포함하는 트랜잭션이 ROLLBACK 되더라도 원래의 값으로 되돌아가지 않는다.
AUTO_INCREMENT 증가 값 가져오기
AUTO_INCREMENT의 최근값을 가져오기 위해 MAX() 함수를 이용하는 방법은 상당히 잘못된 결괄르 반환할 수 있다. 그러므로 아래의 예시에서 사용된 방법을 사용하는 것이 좋다.
MySQL 에서는 현재 커넥션에서 가장 마지막에 증가된 AUTO_INCREMENT 값을 조회하는 LAST_INSERT_ID() 라는 함수를 제공한다. 위의 예제는 INSERT 쿼리를 실행하고 그 이후에 LAST_INSERT_ID()라는 함수를 실행하면 가장 마지막에 사용된 AUTO_INCREMENT 값을 반환하는 것을 보여준다. "SELECT MAX()" 와 같은 쿼리를 사용하면 현재 켜넥션 뿐 아니라 다른 커넥션에서 증가된 AUTO_INCREMENT 값까지 가져올 수 있으므로 사용하지 않는 것이 좋다.