내맘대로 정리하는 Real MySQL #7장) 쿼리 작성 및 최적화

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

이번 장에서는 쿼리의 각 패턴별로 "어떻게 처리되는가?"를 살펴보겠다. 또한 많이 알려져 있지 않지만 프로그램 코드를 상당히 줄일 수 있는 유용한 쿼리 패턴도 함께 살펴보겠다.

내맘대로 정리하고 싶었으나, 페이지 수가 무려 180p인 크고 아름다운 Chapter라서 필요하다면 세부 주제별로 잘개 쪼개 정리해보았다. (앗 생각해보니 어차피 내맘대로구나..!)

쿼리와 연관된 시스템 설정

lannstark.tistory.com/38

MySQL sql_mode 설정과 옵션

 

윈도우에 설치된 MySQL에서는 대소문자를 구분하지 않지만 유닉스 계열의 운영체제에서는 대소문자를 구분한다.

MySQL 서버가 OS와 관계없이 대소문자 구분의 영향을 받지 않게 하려면 lower_case_table_names를 활용할 수 있다.

• 0 (default) : DB나 테이블명에 대한 대소문자 구분
• 1 : 모두 소문자로만 저장된다
• 2 : 저장은 대소문자를 구분해 하지만 MySQL 쿼리에서는 대소문자를 구분하지 않게 한다

MySQL 예약어는 " " 를 붙여야 DB, table, column이름으로 쓸 수 있다 (물론 피하는 것이 좋다)

MySQL 연산자와 내장 함수

Type 비교

MySQl은 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비교를 수행한다.

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

LIKE

LIKE 연산자에서 사용할 수 있는 와일드카드는 두 종류이다.

• % : 0개 이상의 모든 문자에 일치
• _ : 정확히 1개의 문자에 일치

BETWEEN, IN, NOT IN

BETWEEN이 선형으로 인덱스를 검색해야 하는 것과는 달리 IN은 동등 비교를 여러번 수행하는 것과 같은 효과가 있기 때문에 여러 column의 인덱스를 최적으로 사용할 수 있다.

IN 연산자에 상수값을 입력으로 전달하는 경우는 다른 DBMS만큼 최적화해서 수행할 수 있다. 하지만 IN 연산자의 입력이 상수가 아니라 서브쿼리인 경우에는 상당히 느려질 수 있다.

NOT IN의 실행계획은 인덱스 풀 스캔으로 표시되는데, 동등이 아닌 부정형 비교라서 인덱스를 이용해 처리 범위를 줄이는 조건으로는 사용할 수 없기 때문이다.

내장 함수

MySQL의 함수는 MySQL에서 기본적으로 제공하는 내장함수와 사용자가 직접 작성해서 추가할 수 있는 사용자 정의 함수로 구분된다.

• IFNULL( ) : 칼럼이나 표현식 값이 NULL인지 비교하고, NULL이면 다른 값으로 대체하는 용도로 사용된다. NULL(null, 1) #1
• ISNULL( ) : 인자를 전달한 표현식이나 칼럼의 값이 NULL이면 1, 아니면 0을 반환한다
• SYSDATE( ) : 함수가 호출될 때마다 그 시각을 반환한다 → 그때그때 다른 값 반환
• NOW( ) : 쿼리가 실행되는 시점에 실행되고 값을 할당받아 그 값을 SQL 문장의 모든 부분에서 사용하게 한다
• DATE_FORMAT( ) : DATETIME 타입의 칼럼이나 값을 원하는 형태의 문자열로 변환
• DATE_ADD(NOW( ), INTERVAL 1 DAY) : 지금 시간에서 하루를 더한다. 1대신 -1을 넣으면 하루를 뺀다.
• UNIX_TIMESTAMP( ) : '1970-01-01 00:00:00'으로부터 경과된 초의 수를 반환
• FROM_UNIXTIME( ) : 인자로 전달한 타임스탬프 값을 DATETIME으로 변환
• RPAD( ) LPAD( ) : 문자열의 좌측 또는 우측에 문자를 덧붙여 지정된 길이의 문자열로 만든다
• RTRIM( ) LTRIM( ) : 문자열의 우측 또는 좌측에 연속된 공백 문자를 제거하는 함수
• TRIM( ) : RTRIM( )과 LTRIM( )을 동시에 수행
• CONCAT( ) : 여러 개의 문자열을 하나의 문자열로 변환하는 함수
• GROUP_CONCAT( ) : 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정, 중복제거 연결 등을 할 수 있는 함수
• CASE WHEN THEN END : if else 느낌, WHEN column < value 처럼 조건도 넣을 수 있다

SELECT first_name,
    CASE gender WHEN 'M' THEN 'Man'
              WHEN 'W' THEN 'Woman'
              ELSE 'Unknown'
  END AS gender
FROM employees;

• CAST( ) : 타입을 변환할 수 있는 함수

CAST('2020-01-01' AS DATE)

• CONVERT( ) : CAST( )를 포함하며, 문자열의 문자집합(utf8)을 변환할 수 있다
• HEX( ) : 이진값을 사람이 읽을 수 있는 16 진수 문자열로 변환한다
• UNHEX( ) : 16진수의 문자열을 이진값으로 변환한다
• SHA( ) : SHA-1 암호화 알고리즘을 사용하여 160비트의 해시 값을 반환한다
• MD5( ) : Message Digest 알고리즘을 사용해 128비트의 값을 반환한다
• SLEEP( ) : 말 그대로 sleep ^^;
• BENCHMARK( ) : 간단한 함수 성능 테스트로 유용한 함수
• INET_ATON( ) : 문자열로 구성된 IP 주소를 정수형으로 변환
• INET_NTOA( ) : 정수를 문자열 IP 주소로 변환
• PASSWORD( ), OLD_PASSWORD( ) : 쓰지마라
• COUNT( ) : count(*) 표기나 count(1)이나 다를게 없다. 혹시 column을 count안에 넣었는데 NULL이면 그 값을 제외하고 counting하니 주의해야 한다

SELECT

SELECT은 여러 개의 테이블로부터 데이터를 조합해서 빠르게 가져와야 하기 때문에 여러 개의 테이블을 어떻게 읽을 것인가에 많은 주의를 기울여야 한다.

일반적인 SELECT 쿼리의 실행 순서 - 인덱스를 적절히 사용하면 쿼리에는 ORDER BY나 GROUP BY가 있다 하더라도 그 실행 순서를 skip할 수 있다.

예외적으로 ORDER BY가 join보다 먼저 실행되는 경우 - 주로 GROUP BY 절이 없이 ORDER BY만 사용된 쿼리에서 사용될 수 있는 순서이다.

WHERE 절에서 인덱스 태우기

• 칼럼 값 자체를 변환하지 않고 그대로 사용해야 한다.
• 비교 조건에서 양쪽의 데이터 타입이 일치해야 한다.
• OR 연산이 있으면 주의해야 한다 : 인덱스 한번 + 풀 테이블 한번 하느니 차라리 풀 테이블 한 번 하는 수 있다.
• 범위 조건이 있으면 그 뒤의 column은 인덱스를 쓰지 못한다.
• MySQL에서는 NULL 값이 포함된 레코드도 인덱스로 관리된다.

WHERE, ORDER BY, GROUP BY 인덱스 태우기

lannstark.tistory.com/40

GROUP BY, ORDER BY 인덱스 튜닝

DISTINCT

집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행계획에서는 DISTINCT가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 있어야 한다. 하지만 실행 계획의 Extra 칼럼에는 'Using temporary' 메시지가 출력되지 않는다.

SELECT DISTINCT

집합함수와 함께 사용되지 않는 distinct는 GROUP BY와 거의 같은 방식으로 처리된다. 단지 차이는 SELECT DISTINCT의 경우 정렬이 보장되지 않는다는 것뿐이다.

집합 함수와 함께 사용된 DISTINCT

집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼 값들 중에서 중복을 제거하고 남은 값만을 가져온다.

LIMIT

• MySQL의 LIMIT은 항상 쿼리의 가장 마지막에 실행된다.
• LIMIT의 중요한 특성은 LIMIT에서 필요한 레코드 건수만 준비되면 바로 쿼리를 종료시킨다는 것이다.
• LIMIT 0이 사용되면 MySQL 옵티마이저는 쿼리를 실행하지 않고 최적화만 실행한 후 즉시

JOIN

lannstark.tistory.com/41

JOIN 쿼리 튜닝

 

GROUP BY

MySQL의 GROUP BY는 정렬 작업까지 수행한다. 이런 정렬 작업 때문에 GROUP BY가 많이 느려지는데, GROUP BY에서 정렬은 하지 않도록 ORDER BY NULL 을 추가할 수 있다.

GROUP BY가 사용된 쿼리에서는 그룹핑된 그룹별로 소계를 가져올 수 있는 롤업 기능을 사용할 수 있다.

ORDER BY

ORDER BY 절이 없는 SELECT 쿼리 결과의 순서는 처리 절차에 따라 달라질 수 있다. 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리에 대해서는 어떠한 정렬도 보장하지 않는다.

ORDER BY에서 인덱스를 사용하지 못할 때는 추가적인 정렬 작업을 수행하고, 쿼리 실행계획에 있는 Extra 칼럼에 'Using filesort'라는 코멘트가 표시된다. 'Filesort'라는 단어에 포함되는 "File"은 디스크의 파일을 이용해 정렬을 수행한다는 의미가 아니라 쿼리를 수행하는 도중에 MySQL 서버가 퀵 소트 정렬 알고리즘을 수행했다는 의미 정도로 이해하면 된다.

ORDER BY RAND()를 이용한 임의 정렬이나 조회는 절대 인덱스를 이용할 수 없다.

MySQL 정렬에서 NULL은 항상 최소의 값으로 간주하고 정렬을 수행한다. 오름차순 정렬인 경우 NULL은 항상 제일 먼저 반환되며, 내림차순인 경우에는 제일 마지막에 반환된다.

서브쿼리

lannstark.tistory.com/44

서브쿼리 튜닝

집합 연산

집합 연산 : 여러 테이블의 레코드를 연결하는 방법

• UNION : 두 개의 집합을 하나로 묶는 역할. 중복을 제거하면 UNION DISTINCT를, 제거하지 않으면 UNION ALL을 사용하면 된다.
• INTERSECT : MySQL에서 바로 제공되지는 않는다.
• MINUS : MySQL에서 바로 제공되지는 않는다.

집합 연산은 모두 임시 테이블이 필요한 작업이다.

UNION 키워드 뒤에 아무것도 명시하지 않으면 DISTINCT 가 적용된다. 중복인 레코드를 확인하는 방법은 '모든 칼럼'을 비교하는 것이다.

lannstark.tistory.com/46

MySQL 교집합과 차집합

SELECT 추가 내용

InnoDB 테이블에 대해서는 레코드를 SELECT 할 때 레코드에 아무런 잠금도 걸지 않는다. 하지만 SELECT 쿼리를 이용해 읽은 칼럼의 값을 애플리케이션에서 가공해서 다시 업데이트 하고자 할때는 다른 트랜잭션이 그 칼럼의 값을 변경하지 못하게 할 때도 있다. 이럴 때는 레코드를 읽으면서 강제로 잠금을 걸어둘 필요가 있는데, 이때 사용하는 명령이 LOCK IN SHARE MODE와 FOR UPDATE이다.

두 명령은 auto-commit이 비활성화(OFF)된 상태 또는 트랜잭션이 시작된 상태에서만 잠금이 유지된다.

• LOCK IN SHARE MODE : select된 레코드에 대해 읽기 잠금을 설정하고 다른 세션에서 해당 레코드를 변경하지 못하게 한다. (읽는 것은 가능하다)
• FOR UPDATE : 쓰기 잠금을 설정하고 다른 트랜잭션에서는 그 레코드를 변경하는 것도, 읽는 것도 못하게 한다.

지금까지의 경험으로 보면 두 명령을 사용한 쿼리는 잠금 경합을 꽤 많이 유발하고, 때로는 데드락을 일으키는 경우도 많았다.

SELECT INTO ... OUTFILE 명령은 SELECT 쿼리의 결과를 화면으로 출력하는 것이 아니라 파일로 저장할 수 있다. SELECT INTO ... OUTLINE 명령은 테이블 단위로 데이터를 덤프받아서 적재하거나, 엑셀 파일이나 다른 DBMS로 옮길 때 유용하게 사용될 수 있다. 여러 포맷의 데이터를 쉽게 만들 수도 있다.

주의해야 할 점

• SELECT 결과는 MySQL 클라이언트가 아니라 MySQL 서버가 기동중인 장비의 디스크로 저장된다
• SELECT INTO ... OUTFILE 명령의 결과를 저장할 파일, 그리고 파일이 저장되는 디렉토리는 MySQL 서버를 기동중인 OS의 계정이 쓰기 권한을 가지고 있어야 한다.
• 이미 동일 디렉토리에 동일 이름의 파일이 있을 때 SELECT INTO ... OUTFILE 명령은 기존 파일을 덮어쓰지 않고 에러를 발생시키며 종료한다.

INSERT, UPDATE, DELETE

AUTO_INCREMENT

• AUTO_INCREMENT 속성을 가진 칼럼은 반드시 PK나 유니크 키의 일부로 정의돼야 한다.
• AUTO_INCREMENT 속성을 가진 칼럼 하나로 PK를 생성할 때는 아무런 제약이 없다.
• 여러 개의 칼럼으로 PK를 만들때
  • AUTO_INCREMENT 속성의 칼럼이 제일 앞이라면, 아무런 제약이 없다.
  • AUTO_INCREMENT 속성의 칼럼이 제일 앞이 아니라면, InnoDB에서는 불가능하다. 반드시 AUTO_INCREMENT 속성을 가진 칼럼이 제일 앞인 UNIQUE 키가 하나 더 있어야 한다.

auto_increment_offset은 AUTO_INCREMENT 속성의 칼럼 초기 값을 정의하며, auto_increment_increment는 AUTO_INCREMENT 값이 증가할 때마다 얼마씩 증가시킬 것인지를 결정한다.

최근에 저장된 AUTO_INCREMENT 값을 가져오는 방법 : LAST_INSERT_ID()

JDBC 3.0 이상에서는 추가적인 네트워크 통신 없이 저장된 AUTO_INCREMENT 값을 가져올 수 있다

여러 INSERT 방법들

• INSERT IGNORE
• REPLACE
• INSERT INTO ... ON DUPLICATE KEY UPDATE ...
• INSERT ... SELECT ...
• LOAD DATA INFILE ...

LOAD DATA INFILE 성능 향상

• Auto-Commit 해제 : auto-commit이 활성화 되어 있다면, 레코드 단위로 insert 될때마다 commit이 실행되어, 매번 레코드 단위 로그 파일의 디스크 동기화 작업을 발생시킨다.
• UNIQUE INDEX : 잠시 체크를 중단하는 것이 좋다 unique_checks = 0
• FK : 잠시 체크를 중단하는 것이 좋다 foreign_key_checks = 0

여러 UPDATE 방법들

• UPDATE ... ORDER BY ... LIMIT n

  • 복제가 구축된 MySQL 서버에서 ORDER BY가 포함된 UPDATE 문장을 사용할 때는 주의가 필요하다

    SET @ranking:=0;
    
    UPDATE salaries
      SET ranking=( @ranking := @ranking + 1)
    ORDER BY salary DESC;

  • 급여순으로 랭킹을 부여하는 쿼리

• JOIN UPDATE

  • 조인되는 모든 테이블에 대해, 읽기 참조가 되는 테이블은 읽기 잠금이, 칼럼이 변경되는 테이블은 쓰기 잠금이 걸린다. 데드락을 유발할 가능성이 높아 많이 사용하지 않는 것이 좋다.

여러 DELETE 방법들

• DELETE ... ORDER BY ... LIMIT n
• JOIN DELETE

DDL

lannstark.tistory.com/48

MySQL DDL 총 모음

SQL 힌트

STRAIGHT JOIN

STRAIGHT JOIN 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.

USE INDEX / FORCE INDEX / IGNORE INDEX

복잡한 인덱스에 대해 옵티마이저가 적합한 인덱스를 선택하지 못할 때 사용할 수 있다.

• USER INDEX : 특정 인덱스를 사용하도록 권장
• FORCE INDEX : 더 강하게 권장
• IGNORE INDEX : 특정 인덱스를 사용하지 못하게함

어떤 용도로 인덱스를 이용할지 명시할 수 있다. (ex. USER INDEX FOR JOIN → WHERE 절도 JOIN 항목에 포함된다)

SELECT * FROM table_name USER INDEX(index_name) WHERE ...;

옵티마이저도 인덱스나 조인을 처리할 때 상당히 신뢰할만한 수준의 최적화를 수행하고, 최적의 실행계획이란 데이터의 성격이나 양에 따라 시시각각 변하기 때문에 지금 PK를 사용하는 것이 좋은 계획이었다 하더라도 내일은 아닐 수 있다. 가능하다면 옵티마이저가 실행 시점의 통계 정보를 가지고 실행 계획을 선택하게 해주는 것이 좋다.

쿼리 캐싱 날리기

RESET QUERY CACHE 명령은 MySQL 서버에 포함된 모든 캐시 내용을 삭제하며, 삭제 작업이 진행되는 동안 (짧은 작업이겠지만) 모든 쿼리의 실행이 대기해야 한다. 따라서 서비스 중인 MySQL 서버에서는 이 명령을 실행할 때 주의해야 한다.