본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
쿼리 작성 및 최적화
애플리케이션에서 데이터를 저장 또는 조회하기 위해 데이터베이스와 통신할 때 데이터베이스 서버로 전달되는 것은 SQL 뿐이다. SQL은 어떠한 데이터를 요청하기 위한 언어이지, 어떻게 데이터를 읽을지를 표현하는 언어는 아니므로 C나 자바와 같은 언어와 비교했을 때 상당히 제한적으로 느껴질 수 있다. 하지만 쿼리가 빠르게 수행되게 하려면 쿼리가 어떻게 데이터를 가져올지 예측할 수 있어야 한다. 그래서 SQL을 작성하는 방법이나 규칙은 물론, 내부적인 처리 방식(옵티마이저)에 대해 어느 정도의 지식이 필요하다.
SQL 모드
MySQL 서버의 sql_mode 라는 시스템 설정에는 여러 개의 값이 동시 설정될 수 있다. 대표적으로 SQL 의 작성과 결과에 영향을 미치는 값은 어떤 것들이 있는지 살펴보자. MySQL 서버의 설정 파일에서 sql_mode를 설정할 때는 구분자(,)를 이용해 아래에 나온 키워드를 동시에 설정할 수 있다.
STRICT_ALL_TABLES
저장하려는 값의 길이가 칼럼의 길이보다 더 긴 경우 에러가 발생하지 않는다. 대신 칼럼의 길이를 초과하는 부분은 버리고 저장 가능한 만큼만 칼럼에 저장한다. sql_mode 시스템 변수에 STRICT_ALL_TABLES 값이 설정되면 칼럼의 정해진 길이보다 큰 값을 저장할 때 경고가 아닌 오류를 발생하고 쿼리 실행이 중지된다.
STRICT_TRANS_TABLES
칼럼의 타입과 호환되지 않는 값을 저장할 때, MySQL 서버는 비슷한 값으로 최대한 바꿔서 저장하려고 한다. STRICT_TRANS_TABLES 를 설정하면 원하지 않는 데이터 타입의 변환이 필요할 때 강제 변환하지 않고 에러를 발생시킨다.
TRADITIONAL
STRICT_ALL_TABLES, STRICT_TRANS_TABLES와 비슷하지만 조금 더 엄격한 방법으로 SQL의 동작을 제어한다.
ANSI_QUOTES
ANSI_QUOTES를 설정하면 홑따옴표만 문자열 값 표기로 사용할 수 있고, 쌍따옴표는 칼럼명이나 테이블명과 같은 식별자(Identifier)를 표기하는 데만 사용할 수 있다.
ONLY_FULL_GROUP_BY
MySQL의 쿼리에서는 GROUP BY 절에 포함되지 않은 칼럼이더라도 집합 함수의 사용 없이 그대로 SELECT 절이나 HAVING 절에 사용할 수 있다. ONLY_FULL_GROUP_BY를 설정하면 SQL 문법에 조금 더 엄격한 규칙을 적용하게 된다.
PIPE_AS_CONCAT
MySQL에서 "||"는 OR 연산자와 같은 의미로 사용된다. 하지만 sql_mode 시스템 설정에 PIPE_AS_CONCAT 값을 설정하면 오라클과 같이 문자열 연결(CONCAT) 연산자로 사용할 수 있다.
PAD_CHAR_TO_FULL_LENGTH
MySQL에서는 CHAR 타입이라 하더라도 VARCHAR 와 같이 유효 문자열 뒤의 공백 문자는 제거되어 반환된다. CHAR 타입의 컬럼값을 가져오는 경우, 뒤쪽의 공백이 제거되지 않고 반환돼야 한다면 PAD_CHAR_TO_FULL_LENGTH를 추가하면 된다.
NO_BACKSLASH_ESCAPES
역슬래시 문자를 이스케이프 문자로 사용할 수 있다. 이 옵션을 추가하면 역 슬래시를 문자의 이스케이프 용도로 사용하지 못한다. 이 설정을 활성화하면 백 슬래시 문자도 다른 문자와 동일하게 취급한다.
IGNORE_SPACE
스토어드 프로시저나 함수의 이름 뒤에 공백이 있으면 '스토어드 프로시저나 함수가 없습니다' 애러가 출력될 수 있다. 이 옵션을 추가하면 프로시저나 함수명과 괄호 사이에 공백은 무시한다.
ANSI
이 값은 위에 설명한 여러 가지 옵션을 조합해 MySQL 서버가 최대한 SQL 표준에 맞게 동작하게 만들어준다.
영문 대소문자 구분
MySQL 서버는 설치된 운영체제에 따라 테이블명의 대소문자를 구분한다. 이는 MySQL의 DB나 테이블이 디스크의 디렉터리나 파일로 맵핑되기 때문이다. 즉 윈도우에 설치된 MySQL에서는 대소문자를 구분하지 않지만 유닉스 계열의 운영체제에서는 대소문자를 구분한다. DB나 테이블명의 대소문자 구분은 운영체제를 변경하여 DB 를 옮기는 경우 문제가 되기도 한다. MySQL 서버가 운영체제에 관계없이 대소문자 구분의 영향을 받지 않게 하려면 MySQL 설정 파일에 lower_case_table_names 시스템 변수를 설정하면 된다. 이 변수를 1로 설정하면 모두 소문자로만 저장되고, MySQL 서버가 대소문자를 구분하지 않게 해준다. 기본값은 0으로 테이블명에 대해 대소문자를 구분한다.
MySQL 예약어
예약어와 같은 키워드로 데이터베이스나 테이블, 칼럼 이름을 생성하려면 항상 역따옴표(`)나 쌍따옴표로 감싸줘야 한다. 예약어 여부를 확인하는 좋은 방법은 테이블을 생성해 보는 것이다. 단, 역따옴표(`)로 테이블이나 칼럼명을 둘러싸지 않고 테이블을 생성해야 한다. 항상 테이블을 생성할 때 역따옴표로 테이블이나 칼럼명을 둘러싸지 않은 상태로 생성하는 것을 권장한다.
