/
/

[Real MySQL] 11. 쿼리 작성 및 최적화 (1)

avatar
chae
2025.03.26
·
13 min read
43004301

읽어보고 싶던 책이여서 읽고 블로그에 정리해보려합니다 😀😀

애플리케이션에서 입력된 데이터를 DB에 저장하거나 dB로부터 필요한 데이터를 가져오려면 SQL이라는 정형화된 문장을 사용해야 한다. DB나 테이블의 구조를 변경하기 위한 문장을 DDL(Data Definition Language)라 하며, 테이블의 데이터를 조작하기 위한 문장을 DML(Data Manipulation Language)라고 한다.

애플리케이션에서 데이터를 저장 또는 조회하기 위해 DB와 통신할 때 DB 서버로 전달되는 것은 SQL뿐이다. SQL은 어떠한(What) 데이터를 요청하기 위한 언어이지, 어떻게(How) 데이터를 읽을지를 표현하는 언어는 아니므로 C나 자바 같은 언어와 비교했을 때 상당히 제한적으로 느껴질 수 있다.

그래서 쿼리가 빠르게 수행되게 하려면 DB 서버에서 쿼리가 어떻게 요청을 처리할지 예측할 수 있어야 한다. 그래서 SQL을 작성하는 방법이나 규칙은 물론, 내부적인 처리 방식(옵티마이저)에 대해 어느 정도 지식이 필요하다.

애플리케이션 코드를 튜닝해서 성능을 2배 개선한다는 것은 쉽지 않은 일이다. 하지만 DBMS에서 몇십 배에서 몇백 배의 성능 향상이 이뤄지는 것은 상당히 흔한 일이다. SQL에서 어떻게(How)를 이해하고, 쿼리를 작성하는 것은 그만큼 중요하다. 11장에서는 쿼리의 패턴별로 "어떻게 처리되는가?" 를 살펴보자.

🔍11.1 쿼리 작성과 연관된 시스템 변수

대소문자 구분, 문자열 표기 방법 등과 같은 SQL 작성 규칙은 MySQL 서버의 시스템 설정에 따라 달라진다. 이번 절에서는 MySQL 서버의 시스템 설정이 쿼리에 어떤 영향을 주는 지 살펴본다. MySQL 예약어에서는 어떤 것이 있고, 이러한 예약어를 사용할 때 주의해야할 사항이 무엇인지도 함께 살펴본다.

🖇11.1.1 SQL 모드

MySQL 서버의 sql_mode라는 시스템 설정에는 여러 개의 값이 동시에 설정될 수 있다. MySQL 서버의 설정파일에서 sql_mode를 설정할 때는 구분자(,)를 이용해 다음에 설명되는 키워드를 동시에 설정한다.

  • STRICT_ALL_TABLES & STRICT_TRANS_TABLES

    • MySQL 서버에서 INSERT나 UPDATE 문장으로 데이터를 변경하는 경우 칼럼의 타입과 저장되는 값의 타입이 다를 때 자동으로 타입 변경을 수행

    • STRICT_TRANS_TABLES 옵션은 InnoDB 같은 트랜잭션을 지원하는 스토리지 엔진에만 엄격한 모드를 적용, STRICT_ALL_TABLES는 트랜잭션 지원 여부와 무관하게 모든 스토리지 엔진에 대해 엄격한 모드 적용

    • 사용자가 원하지 않는 방향으로 값의 자동변환이 유발될 수 있기에 MySQL 서버를 서비스 적용 전 반드시 활성화

    • 서버 도중에 변경하면 INSERT, DELETE 문장 검토

  • ANSI_QUOTES: MySQL은 문자열(리터럴)을 표현하기 위해 홑따옴표, 쌍따옴표 둘다 동시 사용 가능, 하지만 오라클은 홑따옴표가 리터럴을 표기, 쌍따옴표는 칼럼명이나 테이블 같은 식별자를 구분하는 용도로만 사용

    • 이 모드를 설정하면 홑따옴표만 문자열 값 표기로 사용할 수 있음

  • ONLY_FULL_GROUP_BY

    • MySQL 쿼리에서는 GROUP BY절에 포함되지 않는 칼럼이여도 집계 함수의 사용 없이 그대로 SELECT절이나 HAVING절에 사용 가능 -> SQL 표준과는 다름

    • 이 옵션으로 좀 더 엄격한 규칙 적용

    • 활성화되면 GROUP BY에 사용된 문장의 SELECT 절에는 GROUP BY절에 명시된 칼럼과 집계 함수만 사용 가능

  • PIPE_AS_CONCAT

    • ||은 OR 연산자로 사용되는데 이 값을 설정하면 오라클과 같이 문자열 연결 연산자(CONCAT)으로 사용 가능

  • PAD_CHAR_TO_FULL_LENGTH

    • CHAR 타입이어도 VARCHAR와 같이 유효 문자열 뒤의 공백문자는 제거되어 반환됨

    • 뒤쪽 공백이 제거되지 않고 반환되어야하면 이 설정을 추가

  • NO_BACKLATSH_ESCAPES

    • 역슬래시를 문자의 이스케이프 용도로 사용 불가

    • 역슬래시 문자를 다른 문자와 동일하게 취급

  • IGRNORE_SPACE

    • 프로시저나 함수명과 괄호 사이의 공백은 무시

    • MySQL 서버 내장 함수에만 적용되며 옵션이 활성화되면 내장 함수는 모두 예약어로 간주되어 테이블이나 칼럼이름으로 사용 불가

    • backtick(`)를 이용하면 예약어를 테이블이나 칼럼의 이름으로 사용 가능

  • REAL_AS_FLOAT

    • REAL 타입은 DOUBLE 타입의 동의어 이지만 이 모드가 활성화되면 REAL 타입이 FLOAT 타입의 동의어로 변경

  • NO_ZERO_IN_DATE & NO_ZERO_DATE

    • 이 두 옵션이 활성화되면 2020-00-00 or 0000-00-00과 같은 잘못된 날짜를 저장하는 것이 불가능

    • 실제 존재하지 않는 날짜를 저장하지 못하게 함

  • ANSI

    • 위에서 설명한 여러 옵션을 조합해서 서버가 최대한 SQL 표준에 맞게 동작하게 해줌

    • real_as_float, pipes_as_concat, ansi_quotes, ignore_space, only_full_group_by 조합으로 구성된 모드

  • TRADITONAL

    • strict_trans_tables, strict_all_tables, no_zero_in_date, no_zero_date, error_for_division_by_zero, no_engine_substitution의 조합으로 구성된 모드

    • 활성화되면 이 모드가 아닐 때 경고로 처리되던 상황이 모두 에러로 바뀌고 SQL 문장은 실패

🖇11.1.2 영문 대소문자 구분

  • 설치된 운영체제에 따른다 !! (왜냐? DB나 테이블이 디스크의 디렉터리나 파일로 매핑되서)

  • 윈도우는 대소문자 구분 x, 유닉스는 구분

  • 윈도우에서 운영되던 MySQL 데이터를 리눅스로 가져오거나 반대로할 때 영향을 받지 않게 하려면 lower_case_table_names 시스템 변수를 설정

  • 가능하면 통일해서 사용하는 편이 좋음

🖇11.1.3 MySQL 예약어

  • 생성하는 DB나 테이블, 칼럼의 이름을 예약어와 같은 키워드로 생성하면 해당 칼럼이나 테이블을 SQL에서 사용하려면 항상 역따옴표나 쌍따옴표로 감싸야함

    • 성가신 일이기도하지만 "문법이 틀림" 에러만 출력해서 찾아내기 어려운 버그

  • 가장 좋은 방법은 테이블을 직접 생성해 보는 것

    • 역따옴표로 테이블이나 칼럼명을 둘러싸지 않고 생성하면 MySQL 서버가 에러로 알려준다 !!

🔍11.2 매뉴얼의 SQL 문법 표기를 읽는 방법

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [AS alias]
    [ON DUPLICATE KEY UPDATE assignment_list]

value: {expr | DEFAULT}

value_list: value, [, value] ...

assignment: col_name = value

assignment_list: assignment, [, assignment] ...

  • 키워드나 표현식이 표기된 순서로만 사용 가능

  • 대문자로 표현된 단어는 모두 키워드 , 키워드는 대소문자 구분 안하고 사용 가능

  • 이탤릭체로 표현한 단어는 사용자가 선택해서 작성하는 토큰, 대부분 테이블명이나 칼럼명 또는 표현식

  • [] 는 해당 키워드나 표현식 자체가 선택 사항임을 의미

  • | 는 앞뒤의 키워드나 표현식 중에서 단 하나만을 선택해서 사용할 수 있음을 의미

    • INSERT는 LOW_PRIORITY, DELAYED, HIGH_PRIORTIY 중 하나만 선택하거나 아무것도 선택 x

  • {} 는 괄호 내의 아이템 중에 반드시 하나를 사용해야하는 경우

  • ... 표기는 앞에 명시된 키워드나 표현식의 조합이 반복될 수 있음을 의미

🔍11.3 MySQL 연산자와 내장함수

MySQL에서만 사용되는 연산자나 표기법이 있다. ANSI 표준 형태가 아닌 연산자가 많이 있어서 혼란을 주기도 한다.

🖇11.3.1 리터럴 표기법 문자열

11.3.1.1 문자열

  • SQL에서 문자열은 항상 '를 사용해 표시, 하지만 "도 가능

    • select * from departments where dept_no='d001';

  • 예약어에서 충돌을 피할 때 오라클이나 PostgreSQL은 쌍따옴표나 대괄호로 감싸고 MySQL은 역따옴표로 감싼다.

  • ANSI_QUOTES를 설정하면 쌍따옴표는 문자열 리터럴 표기에 사용 불가능 이 때는 충돌 피하려면 쌍따옴표 사용

11.3.1.2 숫자

  • 숫자값을 상수로 사용할 대는 따옴표 없이 숫자값 입력 '

  • WHERE 쿼리를 사용할 때 주의

    • select * from tab_test where number_column='10001';

      • 주어진 상숫값을 숫자로 변환

    • select * from tab_test where string_column=10001;

      • 비교되는 칼럼이 문자열이라 이걸 다 숫자로 바꿔서 비교를 수행해야하므로 string_column에 인덱스가 있더라도 이용 x, 쿼리자체 실패 가능

11.3.1.3 날짜

  • 정해진 형태의 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIME값으로 변환

11.3.1.4 불리언

  • BOOL이나 BOOLEARN이라는 타입이 있는데, TINYINT 타입의 동의어

  • TRUE or FALSE 를 0 or 1에 매핑해서 사용, 다른 숫자말고 1만 TRUE를 의미

- 컬렉션 아티클