11. 쿼리 작성 및 최적화

• SQL은 어떠한 데이터를 요청하기 위한 언어이지, 어떻게 데이터를 읽을지 표현하는 언어가 아니다
• 따라서 쿼리가 빠르게 수행되게 하기 위해서는 쿼리가 어떻게 요청을 처리할 지 예측할 수 있어야 한다

1. 쿼리 작성과 연관된 시스템 변수

1.1) SQL 모드

• 저장될 데이터에 대한 유효성 검사 범위를 설정하는 시스템 변수

• , 구분자를 이용하여 동시에 설정할 수 있다

• SQL_MODE 옵션

  • [doc] Server SQL Modes

• MYSQL 8.0 버전에서의 sql_mode 기본값 설정

  

  • ONLY_FULL_GROUP_BY → HAVING 조건 또는 ORDER BY, GROUP BY 절에 지정하지 않은 열의 참조를 거부

| ONLY_FULL_GROUP_BY | GROUP BY 절이 사용된 문장의 select 절에는 group by 절에 명시된 컬럼과 집계함수(count, sum)만 사용 가능하도록 강제하는 옵션 (5.7까지는 기본값으로 비활성화되었었는데 8.0부터 활성화) | | --- | --- | | STRICT_ALL_TABLES, STRICT_TRANS_TABLES | (모든 혹은) 트랜잭션 지원 스토리지 엔진 테이블에 대해서 Strict SQL Mode | | NO_ZERO_IN_DATE, NO_ZERO_DATE | 0000-00-00 날짜에 대해 유효한 날짜로 허용하지 않음 2010-00-01 등 유효하지 않은 날짜를 허용하지 않음 | | ERROR_FOR_DIVISION_BY_ZERO | MOD(N,0)을 포함하여 0으로 나누기 처리 규칙 지정 • 비활성화 → NULL , 경고X • 활성화 → NULL , 경고 | | NO_ENGINE_SUBSTITUTION | CREATE TABLE 또는 ALTER TABLE과 같은 명령문이 비활성되거나 컴파일되지 않은 스토리지 엔진을 지정할 때 기본 스토리지 엔진의 자동 대체를 제어 • 비활성화 ◦ CREATE TABLE : 기본 엔진이 사용(사용할 수 없는 경우 경고) ◦ ALTER TABLE : 경고를 발생하고 테이블 변경되지 않음 • 활성화 ◦ 오류가 발생하고 원하는 엔진을 사용할 수 없는 경우 테이블이 생성되거나 변경되지 않음 |

• Combination modes
  • ANSI → REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY
  • TRADITIONAL → STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
• [doc] strict sql mode

1.2) 영문 대소문자 구분

• MySQL 서버는 설치된 운영체제에 따라 테이블명의 대소문자를 구분(마이그레이션 시 주의)
  • DB 테이블이 디렉터리나 파일로 매핑되기 때문
  • WINDOW → 대소문자 구분X
  • UNIX 계열 → 대소문자 구분O
• lower_case_table_names
  • 0(기본값) → 테이블 대소문자를 구분한다
  • 1 → 모두 소문자로 저장, 서버가 대소문자를 구분하지 않음
  • 2 → 저장은 대소문자 구분, 쿼리에서는 구분하지 않음 시스템 변수로 설정 가능

1.3) MySQL 예약어

• MYSQL 예약어를 데이터베이스나, 테이블, 칼럼의 이름으로 사용하기 위해서는 ``` 나 " 로 감싸야 한다
• 단순히 문법이 틀리다 라는 오류를 반환하기 때문에 관리하기 어렵다
• 웬만하면 DB, 테이블 명 등에 예약어를 사용하지 않는 것을 권장