티스토리 뷰

개요

애플리케이션의 코드를 튜닝해서 성능을 2배 개선한다는 것은 쉽지 않은 일이지만, DBMS에서는 몇 십배, 몇 백배의 성능 향상이 흔한 일이다. 그만큼 SQL 쿼리를 튜닝하는 법을 아는 것이 중요하다.
쿼리의 결과를 예측하는 것은 물론, 옵티마이저의 내부적인 처리 방식도 어느 정도 숙지할 필요가 있다.

쿼리와 연관된 시스템 설정

sql_mode

sql_mode라는 시스템 설정에는 여러 개의 값이 동시에 설정될 수 있는데, 몇 개만 보면 다음과 같다.

  • STRICT_ALL_TABLES : 일반적인 MySQL에서는 저장하려는 값의 길이가 칼럼 길이를 넘어가더라도 경고만 발생시킨 후 초과한 부분을 자르고 저장한다. 이 옵션을 주면 경고가 아닌 에러를 발생시켜 칼럼 길이를 넘는 데이터를 저장하는 것을 막을 수 있다.
  • STRICT_TRANS_TABLES : MySQL 서버는 칼럼 타입과 호환되지 않는 값을 저장하려고 할 때 최대한 변환해서 저장하려고 하나, 이 옵션을 주면 강제 변환하지 않고 에러를 발생시킨다.
  • TRADITIONAL : 위 두 방법보다 조금 더 엄격한 방법으로 ANSI 표준 모드로 동작하도록 한다.
  • ONLY_FULL_GROUP_BY : MySQL 쿼리에서는 GROUP BY절에 포함되지 않은 칼럼이라도 집합 함수 없이 SELECT나 HAVING절에 이용할 수 있도록 되어 있다. 사실 SQL 표준과는 거리가 있는 방식인데, 이 옵션은 이를 방지하도록 해준다.
  • IGNORE_SPACE : 프로시저나 함수명과 괄호 사이에 공백이 들어가 있어도 무시해준다.
  • ANSI : 위의 여러가지 옵션을 조합해서 MySQL 서버가 최대한 SQL 표준에 맞게 동작하도록 한다.

연산자와 내장 함수

연산자

  • <=> 연산자는 Null-Safe 비교 연산자로 = 연산자와 같으며, NULL을 하나의 값으로 보고 비교할 수 있게 해준다. 즉, NULL <=> NULL은 true이다.
  • LIKE 연산자는 와일드카드 문자(%, _)가 검색어 뒤쪽에 있다면 인덱스 레인지 스캔을 사용할 수 있고, 반대로 앞에 있다면 인덱스 레인지 스캔을 사용할 수 없다.
  • BETWEEN과 IN은 비슷한 듯 보이지만 다르다. IN 연산자는 여러 개의 동등 비교(=)를 묶은 연산자라서 인덱스를 사용할 수 있다. 다만 IN 연산자에 사용할 상수들을 가져오기 위해 IN(subquery) 를 사용하는 것은 성능에 악영향을 줄 수 있다. 기대하는 대로 서브 쿼리가 먼저 실행되는 것이 아니라, 서브 쿼리의 외부가 먼저 실행되고 IN(subquery)가 체크 조건으로 사용되기 때문이다.
  • NOT IN은 부정형 비교라서 인덱스를 사용할 수 없다.

내장 함수

  • 하나의 SQL에서 NOW()는 항상 같은 값이지만, SYSDATE()는 같은 SQL 안에서도 다른 값을 가질 수 있다.
    • SYSDATE()는 슬레이브 DB에서 안정적으로 복제되지 못하고, 해당 함수와 비교되는 칼럼은 인덱스를 효율적으로 사용할 수 없다. (값이 매번 바뀌므로)
  • BENCHMARK(반복횟수, 스칼라값을 반환하는 표현식) 함수는 쿼리의 성능을 알아볼 때 유용하게 쓰일 수 있는데, 주의할 점은 실제 반복횟수만큼 애플리케이션에서 실행하는 것과 해당 함수로 반복횟수만큼 실행하는 것에는 차이가 있다는 것이다. 벤치마크 함수는 딱 1번의 네트워크, 쿼리 파싱, 최적화가 일어나기 때문이다. 해당 함수는 같은 결과값을 가지는 두 개의 기능을 비교하는 데에 쓰면 좋다.
  • COUNT()는 보통 COUNT(*)로 많이 쓰는데, *이라는 기호 때문에 굳이 특정 칼럼을 넣거나 COUNT(1) 같이 사용할 필요는 없다. 성능은 같다.
    • 보통 페이징 처리를 위해 많이 사용하는데, 대신 COUNT 쿼리에서 ORDER BY나 불필요한 LEFT JOIN 등이 걸려있는 것은 제거해야 한다. 인덱스를 제대로 사용하지 못하는 COUNT 쿼리는 많은 부하를 일으킬 수 있기 때문에 주의해야 한다.
      • 페이징 쿼리를 개발한 후 count를 위해 해당 쿼리를 그대로 복사해서 사용하다보니 자주 일어나는 실수이다.

SELECT

SELECT 각 절의 처리 순서

SELECT 쿼리의 결과를 예측하기 위해서는 처리 순서에 대한 이해가 필수적이다. 일반적으로는 다음 순서를 따른다.

  1. 드라이빙 테이블과 드리븐 테이블들에 대해 WHERE 적용 및 조인 실행
  2. GROUP BY
  3. DISTINCT
  4. HAVING
  5. ORDER BY
  6. LIMIT

그리고 GROUP BY절이 없이 ORDER BY만 사용된 쿼리에서는 다음 순서를 따르기도 한다.

  1. 드라이빙 테이블 읽어서 ORDER BY 적용
  2. 드리븐 테이블 WHERE 적용 및 조인 실행
  3. LIMIT 적용

WHERE 절의 인덱스 사용

인덱스를 사용하기 위한 기본적인 규칙은 인덱스된 칼럼의 값 자체를 변경하지 않고 그대로 사용해야 한다는 것이다.

WHERE 조건이 인덱스를 사용할 수 있는 기준은 5장에서 보았듯이 범위 제한 조건과 체크 조건으로 나뉘는데, 범위 제한 조건으로 동등 비교 조건이나 IN으로 구성된 조건이 인덱스를 구성하는 칼럼과 순서대로 얼마나 일치하는가에 따라 달라진다.
결국 WHERE 절에서는 각 조건이 명시된 순서는 중요하지 않고, 해당 칼럼에 대한 조건이 있는지 없는지가 중요하다.

그리고 각 조건이 OR로 연결되면 비교해야 할 레코드가 더 늘어나기 때문에 풀 테이블 스캔을 사용하거나, 인덱스를 각각의 칼럼에 맞추어 사용하더라도 index_merge 방식으로 접근해야 해서 효율이 떨어질 수 있다. 인덱스를 효율적으로 이용할 수 있도록 OR 대신 AND로 조건을 주는 것이 좋다.

GROUP BY 절의 인덱스 사용

GROUP BY 절의 각 칼럼은 비교 연산자를 가지지 않으므로 WHERE 절처럼 범위 제한 조건이나 체크 조건 등을 고려할 필요는 없다.
사용 조건은 다음과 같다.

  • GROUP BY 절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야 한다.
  • 인덱스의 앞쪽부터 빠지는 칼럼 없이 사용해야 하며, 인덱스에 존재하지 않는 칼럼으로 GROUP BY를 사용해서도 안된다.

여기서 조금 더 주의할 점은, WHERE 조건 절에 앞쪽, 예를 들어 COL1, COL2 가 동등 비교 조건으로 사용된다면, GROUP BY 절에는 COL1, COL2 가 빠지고 COL3 부터 사용해도 인덱스를 사용할 수 있다.
즉 다음 두 쿼리는 같은 결과를 가져온다.

... WHERE COL1 = '상수' ... GROUP BY COL2, COL3
... WHERE COL1 = '상수' ... GROUP BY COL1, COL2, COL3

ORDER BY 절의 인덱스 사용

기본적으로 ORDER BY 는 GROUP BY 절의 사용 조건과 거의 비슷하다. 여기에 조건이 더 하나 추가되는데, 정렬되는 각 칼럼의 오름차순 혹은 내림차순 옵션이 인덱스와 같거나 정반대의 경우에만 사용할 수 있다는 것이다.
즉 MySQL의 인덱스는 오름차순으로 정렬되어 있기 때문에, 모든 ORDER BY의 조건이 오름차순 혹은 내림차순 한 방향으로만 걸려있어야 한다.

WHERE 조건과 ORDER BY(또는 GROUP BY) 절의 인덱스 사용

다음 3가지 중 하나를 사용한다.

  • WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 사용
    • WHERE, ORDER BY 절의 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함되어 있을 때 사용 가능하다.
    • 가장 빠른 성능을 보인다.
  • WHERE 절만 인덱스 이용
    • 인덱스를 통해 검색 후 Filesort를 통해 정렬을 수행한다.
    • WHERE 절의 조건에 일치하는 레코드 건수가 많지 않을 때 사용한다.
  • ORDER BY 절만 인덱스 이용
    • ORDER BY 절의 순서대로 레코드를 읽으면서 WHERE 절을 하나씩 적용하는 방식이다.
    • 아주 많은 레코드를 조회해서 정렬해야 할 경우 이 방법을 사용하기도 한다.

GROUP BY 절과 ORDER BY 절의 인덱스 사용

GROUP BY 절의 칼럼과 ORDER BY 절의 칼럼이 순서와 내용이 모두 같아야 한다.
둘 중 하나라도 인덱스를 이용할 수 없을 때에는 둘다 인덱스를 사용하지 못한다.

WHERE, ORDER BY, GROUP BY 절의 인덱스 사용

종합적으로 다음 3개의 질문으로 정리할 수 있다.

1. WHERE 절이 인덱스를 사용할 수 있는가?
2. GROUP BY 절이 인덱스를 사용할 수 있는가?
3. GROUP BY 절과 ORDER BY 절이 동시에 인덱스를 사용할 수 있는가?

DISTINCT

DISTINCT에서 주의할 점은 DISTINCT는 SELECT되는 레코드를 유니크하게 가져오는 것이지 칼럼 하나를 유니크하게 가져오는 것이 아니라는 것이다.
즉 다음 두 쿼리는 똑같이 (COL1 + COL2) 의 유니크 값을 가져온다. 괄호는 적용되지 않는다.

SELECT DISTINCT COL1, COL2 FROM test_table;
SELECT DISTINCT(COL1), COL2 FROM test_table;

하지만 집합 함수 내에서 사용된 DISTINCT 는 조금 다른데, 집합 함수의 인자 칼럼들 중에서 중복을 제거하고 남은 값들을 가져오게 된다.

JOIN

보통의 인덱스 레인지 스캔으로 레코드를 읽는 작업은 다음 순서를 가진다.

  1. 인덱스 탐색 : 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다.
  2. 인덱스 스캔 : 1번에서 찾은 위치에서 필요한 만큼 인덱스를 읽는다.
  3. 2번에서 읽는 인덱스 키와 레코드 주소로 최종 레코드를 읽어온다.

조인 시 드라이빙 테이블을 읽을 때는 인덱스 탐색을 한 번만 수행하고, 그 이후로는 스캔만 수행하면 되지만, 드리븐 테이블에서는 탐색과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다.
그래서 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.

따라서 조인하는 테이블 칼럼 중 한 쪽 테이블 칼럼에만 인덱스가 있을 경우, 옵티마이저는 인덱스가 있는 테이블을 드리븐 테이블로 선택할 확률이 높다. 드라이빙 테이블을 풀 테이블 스캔 하더라도 말이다.
그리고 몇 가지 조인에 대해 알아두어야 하는 사실은 다음과 같다.

  • OUTER JOIN 에서 OUTER 로 조인되는 테이블 칼럼에 대한 조건은 WHERE 절이 아니라 모두 ON 절에 명시해야 한다.
    • 그렇지 않으면 옵티마이저는 INNER JOIN 과 같은 방법으로 처리한다.
  • 페이징 처리 시 COUNT 쿼리를 날릴 때 데이터를 가져오던 쿼리문에서 SELECT 절만 바꿔서 가져오는 실수를 주의해야 한다.
    • OUTER JOIN 시 조인 전과 후의 레코드 건수의 차이가 없다면 당연히 조인을 제거하고 COUNT 쿼리를 날리는 것이 좋다.
  • NOT EXISTS나 NOT IN(subquery) 는 상당히 비효율적이므로 가능하다면 OUTER JOIN을 이용한 ANTI JOIN으로 처리하자.
    • OUTER JOIN 을 하면서 필요 없는 쪽의 테이블 칼럼을 IS NULL 로 걸러서 가져오는 방법이다.
  • INNER JOIN과 OUTER JOIN이 가져와야 하는 레코드 건수가 같다면 둘의 성능 차이는 거의 없다.
  • FOREIGN KEY는 조인과 아무런 연관이 없다.
    • FOREIGN KEY를 생성하는 주 목적은 데이터의 무결성을 보장하기 위함이다.
    • 아무런 상관이 없고 값만 같은 칼럼으로도 조인이 가능하다.
  • 보통 드라이빙 테이블부터 읽은 순으로 조인의 결과 순서가 보장되지만, 조인 버퍼를 사용한 조인은 정렬이 보장되지 않는다.

GROUP BY

  • 쿼리에 GROUP BY가 사용되면 GROUP BY 절에 사용되지 않은 칼럼은 반드시 집합 함수로 감싸서 사용해야 한다.
  • 다른 DBMS와 달리 MySQL의 GROUP BY는 정렬을 기본으로 수행한다.
  • MySQL의 GROUP BY가 불필요한 정렬을 수행하지 않게 하려면 ORDER BY NULL 키워드를 사용해야 한다.

ORDER BY

  • 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리는 어떠한 정렬도 보장하지 않는다.
    • 인덱스를 사용한 SELECT 절이라고 ORDER BY 절을 사용하지 않아도 되는 것은 아니다!
    • 정렬이 필요한 곳에는 꼭 ORDER BY를 명시하자.
  • 인덱스를 사용하지 못하는 정렬 작업은 실행 계획에 "Using filesort" 코멘트가 표시되는데, 이는 디스크 파일을 이용했다는 뜻은 아니고 그냥 퀵 소트 정렬 알고리즘을 사용했다 정도의 의미로만 이해하면 된다. 실제로 메모리만 이용했는지 디스크의 파일까지 이용했는지는 알 수 없다.
  • 여러 개의 칼럼을 조합해서 정렬할 때 각 칼럼의 정렬 순서가 오름차순, 내림차순이 혼용되면 인덱스를 이용할 수 없다.
    • 전부 다 오름차순이거나, 전부 다 내림차순이어야 인덱스를 사용해서 정렬할 수 있다.

서브 쿼리

MySQL 서버는 서브 쿼리를 최적으로 실행하지 못할 때가 많다.
서브 쿼리를 지양하고, JOIN 으로 해결하거나 두 번의 쿼리로 나눠서 실행하는 것이 훨씬 좋다.

IN (subquery) 같은 경우는 옵티마이저가 EXISTS (subquery) 형태로 변환하여 실행한다.

집합 연산

집합 연산자에는 보통 UNION, INTERSECT, 그리고 MINUS 가 있는데, MySQL은 UNION 기능만 제공한다.
INTERSECT 와 MINUS 는 JOIN 을 활용하면 충분히 같은 결과를 가져올 수 있다.

  • UNION 은 기본적으로 두 집합을 합치는 것이기 때문에 DISTINCT 조건이 기본적으로 붙는다.
    • 두 집합 간에 중복된 결과과 나오지 않는다는 것이 보장된다면, 중복제거 작업을 하지 않는 UNION ALL을 쓰는 것이 좋다.

SELECT INTO OUTFILE

SELECT 쿼리의 결과를 화면으로 출력하는 것이 아니라 파일로 저장할 수 있다.
테이블 단위로 데이터를 덤프받아서 적재하거나, 엑셀 혹은 다른 DBMS로 옮길 때 csv 형태로 파일을 뽑아서 유용하게 사용할 수 있다.

주의할 점이 3가지 있다.

  • SELECT 결과는 MySQL 클라이언트가 아니라 MySQL 서버가 기동 중인 장비의 디스크로 저장된다.
  • 파일, 그리고 파일이 저장되는 디렉터리는 MySQL 서버를 기동 중인 OS의 계정이 쓰기 권한을 가지고 있어야 한다.
  • 이미 동일 디렉터리, 동일 이름의 파일이 있다면 덮어쓰는 것이 아니라 에러가 발생한다.

다음 쿼리와 같이 OUTFILE 옵션 뒤에는 결과를 저장할 파일 경로와 이름을 적고, FIELDS 옵션에는 각 칼럼의 구분자를, LINES 옵션에는 각 레코드의 구분자를 명시한다.

SELECT col1, col2, col3  
INTO OUTFILE '/tmp/result.csv'  
FIELDS TERMINATED BY ','  
LINES TERMINATED BY ' \\n'  
FROM my\_table  
WHERE col1 BETWEEN 100 AND 200;

참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.

'Database > Real MySQL' 카테고리의 다른 글

[Real MySQL] 3. 아키텍처  (0) 2020.08.10
[Real MySQL] 2. 설치와 설정  (0) 2020.08.05
[Real MySQL] 7. 쿼리 작성 및 최적화 (2)  (0) 2020.05.23
[Real MySQL] 6. 실행 계획  (0) 2020.03.25
[Real MySQL] 5. 인덱스  (0) 2020.03.08
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday