MYSQL 쿼리 작성 및 최적화 관련 (REAL MY SQL 정리)

 

07. 쿼리작성 및 최적화 

애플리케이션 코드와달리 sql은 작동방식이나 원리에 따라 2배이상의 성능개선을 할 수 잇으므로 sql의 작동원리와 유용한 쿼리패턴을 체크해보자 

 

1. 쿼리와 연관된 시스템설정

1.1 sql모드

- STRICT_ALL_TABLES: 일반적으로 MYSQL에서는 컬럼값이 지정된 길이보다 길어도 에러가 발생하지 않지만 SQL_MODE 시스템변수에 "STRICT_ALL_TABLES"를 설정하면 에러를 발생시키고 쿼리가 중지된다.

- STRICT_TRANS_TABLES: 컬럼 비호환값의 저장시 MYSQL 서버는 비슷한값을 바꿔서 저장하려고하는데 "STRICT_TRANS_TABLES"설정하면 MYSQL 서버의 강제전환을 막는다.

- TRADITIONAL: 앞의 두예약어보다 빡센 방법으로 SQL작동을 제어한다. (결국위의 세가지 제약어는 쿼리가 좀 더 ANSI표준에 맞게 작동하도록 유도하는데 목적이있다)

-ANSI QUOTES: MYSQL은 STRING값을 표현하기위해 ""(DOBULE QUOTES), ''(SINGLE QUOTE)를 모두 사용가능하게한다. BUT 오라클에서는 문자열만 SINGLE QUOTES, 테이블이나 컬럼명은 DOUBLE QUOTES만을 사용하게 제약하는데 이 대, ANSI QUOTES 제약어를 통해서 오라클처럼 설정할 수 있다. 

-ONLY_FULL_GROUP_BY

MYSQL쿼리는 GROUP BY절에 포함되지않은 컬럼이더라도 집합함수없이 그대로 SELECT, HAVING절에 쓸 수 있다. SQL MODE시스템설정에서 ONLY_FULL_GROUP_BY 설정해서 SQL에 더 엄격한 문법규칙을 적용할 수 있다. 

- PIPE_AS_CONCAT

MYSQL ||는 OR연산자인데 ORACLE에서처럼 연결연산자로 사용하려면 PIPE_AS_CONCAT으로 SQL_MODE설정을 통해 바꿀 수 있다.

- PAD_CHAR_TO_FULL_LENGTH

MYSQL은 CHAR타입이어도 유효문자열 뒤 공백은 제거해서 들어오기때문에, CHAR의 경우 뒤의 공백까지도 살려서 자릿수를 맞춰야한다면  SQL_MODE설정에 PAD_CHAR_TO_FULL_LENGTH를 추가하면 된다.

- NO_BACKSLASH_ESCAPES

MYSQL의 SQL_MODE에서는 NO_BACKSLASH_ESCAPES를 사용하면 역슬래시(\)문자를 이스케이프 용도로 사용하지 못하게 설정할 수있다.

- IGNORE_SPACE

MYSQL은 스토어드 프로시져나 함수명고 괄호사이의 공백까지도 동일 오브젝트로 간주해서 에러가 발생할 수 있으므로, 프로시저나 함수명과 괄호사이 공백을 무시하기 위해서는 IGNORE_SPACE를 추가하면 된다.

 

 

 

1.2 영문대소문자 구분

MYSQL은 테이블명의 대소문자를 구분한다. ( MYSQL의 DB나 테이블이 디스크의 디렉토리나 파일로 매핑되기 때문)

따라서 대소문자 영향을 안박데하려면 MYSQL서버의 설정파일에 "LOWER_CASE_TABLE_NAMES"시스템 변수를 1로 설정하면 모두 소문자로만 저장되고 대소문자구분을 하지않는다. DEFAULT값은 0이므로 DB나 테이블명에 대소문자를 구분한다. 

 

 

 

1.3 예약어

- 예약어인지 아닌지 구분하는 방법 -> MYSQL에서 테이블을 생성해본다. 

테이블명/컬럼명을 ``(역따옴표)로 둘러싸지않고 테이블을 생성해봐서 에러가뜨면 예약어이고 아니면 예약어가아니다.