MySQL 쿼리 작성 및 최적화

쿼리 작성에 필요한 기초 지식

• MySQL은 영어 대소문자를 구분한다.
  • 이유는 통상적으로 DB 내용이 디렉토리나 파일로 관리되는데 윈도우OS에선 디렉토리나 파일의 대소문자를 구분하지 않지만, 유닉스 계열에서는 대소문자를 구분하기 때문이다.
  • 설정 파일에서 모두 소문자로만 저장되도록 할 수 있긴하다.
• 리터럴 표기법
  • 문자열은 항상 홑 따옴표(')로 감싼다.
  • 예약어와 충돌이 예상되는 경우 백 틱(`)으로 감싼다. (사실 예약어는 되도록 다른 곳에 사용하지 않는 게 정석이다.)
• 문자열과 상수 비교 시 숫자를 우선으로 하여 문자를 숫자로 변환한다.
  • 숫자 값을 상수로 SQL에서 사용할 때는 DBMS가 자동으로 타입에 맞게 변경해준다.

SELECT * FROM tab_test WHERE number_column='10000'; -- 문제 없음 ('10000'을 숫자 10000으로 변환하여 처리)
SELECT * FROM tab_test WHERE string_column=10000; -- 문제 있음
-- 숫자가 우선이기에 오히려 모든 레코드의 문자열 컬럼의 값(string_column)을 숫자로 변환해서 쿼리를 수행하기 때문에 매 레코드마다 변환 처리해야한다.
-- 만약에라도 string_column 값이 숫자 형태가 아닌 문자열이 들어있으면 에러마저 난다.

• 날짜 타입을 비교할 때는 문자열 값도 날짜 타입으로 변경해서 비교한다.

SELECT * FROM dept_emp WHERE from_date='2021-08-15';
-- 문자열을 날짜로 변경하기 때문에 인덱스 사용에 문제 없음
SELECT * FROM dept_emp WHERE from_date=STR_TO_DATE('2021-08-15', '%Y-%m-%d');
-- 위의 쿼리와 똑같음

• BOOLEAN, BOOL 타입은 TINYINT 타입에 대한 동의어다. 실질적으로 1, 0이 저장되고 조회된다.

CREATE TABLE tb_boolean (bool_value BOOLEAN);
INSERT INTO tbl_boolean VALUES (FALSE);
SELECT * FROM tb_boolean WHERE bool_value=FALSE;
SELECT * FROM tb_boolean WHERE bool_value=TRUE;

연산자

• 동등 연산자 / 부정 비교 연산자
  • =, <=> 연산자는 동등 연산자고 <=> 연산자는 NULL까지 비교하는 Null-safe 비교 연산자다.
  • <> , != 부정 비교 연산자다. 일반적으로 <> 를 많이 사용하므로 하나로 통일해서 쓰도록 한다.
• REGEXP 연산자
  • 정규표현식으로 일일이 조건 검사를 한다. 따라서 인덱스를 전혀 사용하지 못하므로 비효율적이다.
• LIKE 연산자
  • 특정 단어가 포함하는지 검사하는 연산자로 B-TREE 인덱스 구조상 와일드카드가 앞에 붙는 경우(ex. LIKE '%ABC' or LIKE '%ABC%')라면 인덱스 레인지 스캔이 불가능하다.
    • 와일드 카드가 뒤에만 붙는 경우(ex. LIKE 'ABC%')는 인덱스 레인지 스캔이 가능하다.
• BETWEEN 연산자
  • "크거나 같다"와 "작거나 같다"라는 두 개의 연산을 합친 연산자다.
  • WHERE 조건에 사용된 경우, 인덱스를 타더라도 선형적으로 탐색하기 때문에 효율적이지 않을 수 있다. (많은 레코드를 조건 검사해야 할 수 있다.)
• IN 연산자
  • IN (?, ?, ?) : 상수가 사용된 경우
    • IN 연산자 이후에 N개의 상수가 있으면 인덱스를 사용할 수 있으면 동등 비교(=) 연산자를 N번 사용해서 쿼리한 것처럼 최적화 가능하여 효율적으로 동작한다.
    • IN (상수)의 경우에 일반 스칼라 값(단일 컬럼)이 아닌 튜플(여러 컬럼)이 사용된 경우에는 효율적으로 동작하지 않을 수도 있다.
  • IN (SELECT ... FROM ...) : 서브쿼리가 사용된 경우
    • MySQL 8.0 이후 버전에서는 세미 조인으로 최적화되기도 하지만 비효율적인 쿼리 형태다.
• NOT IN 연산자
  • 부정형인 NOT이 붙으면 프라이머리 키가 아닌 이상 인덱스 풀 스캔을 할 수밖에 없어 비효율적이다.
• NOW() 함수
  • 같은 쿼리에서 여러 곳에 사용되어도 모두 같은 값을 가지므로 현재 날짜/시간이 필요할 때 사용하면 된다.
  • SYSDATE()는 쿼리에 쓰인 곳마다도 다르고 레코드 마다도 다르게 계산되기에 쓰면 안 된다. (복제에도 문제 생길 확률이 높다.)
    • 문제의 쿼리 : SELECT * FROM employees WHERE hire_date < SYSDATE(); → SYSDATE()가 상수가 아니므로 레코드마다 WHERE 조건의 값이 다르게 계산된다.
• 기타 내장 함수로 제공하는 것들(ex. DATE_ADD, CONCAT, RPAD, TRIM, ...)은 사용하지 않는 게 좋다. 애플리케이션에서 처리하는 게 더 효율적이다.

IP는 4바이트의 부호 없는 정수(Unsigned integer) 현실은 varchar(15) 문자열을 쓰고 있는 게 대부분.

COUNT() 쿼리의 주의사항

SELECT 쿼리보다 COUNT가 빠를 것이라는 잘 못된 예측과 인덱스를 사용하지 못한 COUNT쿼리는 페이징해서 데이터를 가져오는 쿼리보다 훨씬 느리게 실행될 수 있다는 것이다.

또한 컬럼명이나 표현식이 COUNT함수의 인자로 사용되면 컬럼이나 표현식의 결과가 NULL이 아닌 레코드의 건 수만 반환된다.

SELECT

쿼리 최적화하기 가장 좋은 주제가 조회(SELECT)다.

일반적인 웹 애플리케이션에서 생성, 수정, 삭제보단 조회가 많이 일어나고 복잡하기 때문이다.

살펴보기 앞서 알아둬야 할 것으로 "쿼리 최적화"를 하기 위한 아이디어의 핵심은 두 가지다.

첫 째는 만들어 놓은 인덱스를 잘 사용하는지고, 둘 째는 쿼리가 원하는 순서대로 실행되는 지다.

쿼리는 너무나도 다양하게 작성할 수 있으므로 전부 다 다룰 순 없다.

따라서 주요하게 봐야 할 WHERE 절, GROUP BY절, ORDER BY 절이 있을 때 인덱스를 사용하게 하는 방법을 알아보고 복합적으로 사용되었을 때 인덱스를 사용하게 하는 방법에 대해 알아본다.

WHERE 절에 인덱스 사용

• 복합 인덱스 생성에 사용된 컬럼의 순서와 WHERE 조건에 사용된 컬럼이 같아야 인덱스를 사용할 수 있다.

GROUP BY 절에 인덱스 사용

• WHERE와 다르게 체크 조건, 범위 결정 조건 이런 걸 따지지 않는다.
• GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 개수가 모두 일치해야 한다.
  • (A,B,C,D) 이렇게 인덱스가 생성되어있고 GROUP BY에 (A,C)가 있어도 인덱스를 사용할 수 없다.
  • (A,B,C,D) 이렇게 인덱스가 생성되어있고 GROUP BY에 (A,B,C,D,E)가 있어도 인덱스를 사용할 수 없다.

ORDER BY 절에 인덱스 사용

GROUP BY와 조건이 같지만 하나 더 주의해야 할 것이 있다.

• 오름차순(ASC) 또는 내림차순(DESC) 옵션이 인덱스와 같거나 아예 반대인 경우에만 인덱스를 사용할 수 있다.

---

앞서 WHERE, GROUP BY, ORDER BY가 각각 단독으로 사용되었을 때 인덱스를 사용하는 방법에 대해 학습했다.

그러나 일반적인 애플리케이션의 쿼리에서는 복합(ex. where + order by)으로 사용되는 경우가 많다.

복합적으로 사용되었을 때 주의사항을 알아본다.

기본적으로 하나의 쿼리는 하나의 인덱스만 사용한다고 봐야 한다. 따라서 WHERE 조건에는 A 인덱스, ORDER BY에는 B인덱스 이런 식으로 사용할 수 없다.

WHERE + (ORDER BY/GROUP BY) 절에 인덱스 사용

→ WHERE + ORDER BY가 인덱스를 사용할 수 있는 경우 3가지

1. WHERE 절에 사용된 컬럼 + ORDER BY 절에 사용된 컬럼이 모두 하나의 인덱스의 컬럼에 포함된 경우 (참고로 인덱스를 새로 만들어야 한다면 이렇게 되게 최적화해야 한다.)
2. WHERE 절에 사용된 컬럼만 인덱스를 사용하는 경우
   • ORDER BY 즉, 정렬에는 인덱스를 사용할 수 없으므로 Using Filesort로 임시 테이블에서 정렬되므로 비효율적이지만, WHERE 조건에 의한 결과 레코드가 적은 경우에 한해서는 비효율적이지만은 않다.
3. ORDER BY 절에 사용된 컬럼만 인덱스를 사용하는 경우
   • 반대로 WHERE 조건에 의한 결과 레코드가 아주 많은 레코드의 정렬이 필요한 경우에는 오히려 이렇게 쓰기도 한다.

✔️ 주의사항

-- 인덱스는 (col1, col2, col3)로 생성되었음
SELECT * FROM tb_test WHERE col1=10 ORDER BY col2, col3; -- 인덱스 사용가능
SELECT * FROM tb_test WHERE col1=10 ORDER BY col1, col2, col3; -- 인덱스 사용가능
SELECT * FROM tb_test WHERE col1>10 ORDER BY col2, col3; -- 인덱스 사용 불가 *
SELECT * FROM tb_test WHERE col1>10 ORDER BY col1, col2, col3; -- 인덱스 사용가능
SELECT * FROM tb_test WHERE col1 IN (1,2,3) ORDER BY col1, col2; -- 인덱스 사용 불가 *

동등 비교에서는 WHERE 조건에 사용된 컬럼에 의해 ORDER BY에 동일한 컬럼이 빠져도 인덱스가 사용 가능하지만, 범위 조건 비교에서는 WHERE 조건에서 사용되었더라도 ORDER BY에서 누락되면 인덱스가 사용 불가능하다. (위와 같은 경우 WHERE에서만 사용하게 될 것이다.)

ORDER BY + GROUP BY 절에 인덱스 사용

❌ ORDER BY 와 GROUP BY가 같이 사용된 경우에는 반드시 둘 다 동일하게 컬럼이 명시되어야 하고 순서도 다 똑같아야만 한다. 그렇지 않으면 ORDER BY만 또는 GROUP BY만 인덱스를 이용할 수 있다고 할 지라도 인덱스를 못 탄다.

SELECT * FROM tb_test GROUP BY col1, col2 ORDER BY col1 -- 인덱스 사용 불가능
SELECT * FROM tb_test GROUP BY col1, col2 ORDER BY col1, col3 -- 인덱스 사용 불가능

MySQL에서는 GROUP BY 컬럼에 대한 정렬까지 한 번에 수행하는 것이 기본 동작이므로 GROUP BY에 쓰인 컬럼 모두 ORDER BY에 쓰는 것과 쓰지 않는 것의 결과가 같다.

WHERE + ORDER BY + GROUP BY 절에 인덱스 사용

진행과정은 다음과 같다.

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

이에 따른 인덱스 사용 결정 순서도는 아래 그림과 같다.

DISTINCT

SELECT 절에 DISTINCT를 사용하는 것은 GROUP BY와 거의 같은 방식으로 처리된다.

인덱스 이용도 마찬가지다. 단, 인덱스를 이용하지 못하는 경우에 DISTINCT는 정렬을 보장하지 않는다.

SELECT DISTINCT first_name, last_name FROM employees; -- first_name, last_name이 유일한 레코드
SELECT DISTINCT(first_name), last_name FROM employees; -- first_name, last_name이 유일한 레코드

집합 함수(count(), max(), min() , ...)와 함께 사용된 DISTINCT는 위 규칙과 다르게 사용된 컬럼만 중복을 제거한다.

SELECT COUNT(DISTINCT s.salary) -- salary만 중복 제거한 레코드
FROM employees e, salaries s
WEHERE e.emp_no=s.emp_no
    AND e.emp_no BETWEEN 10001 AND 10010;

LIMIT n

LIMIT는 MySQL에서만 제공되는 키워드다.

LIMIT 로 원하는 레코드 수를 충족시킨다면 중간에 읽기 작업이 멈추는 특징이 있다.

SELECT * FROM employees LIMIT 0, 10; -- (1)
SELECT * FROM employees GROUP BY first_name LIMIT 0, 10; -- (2)
SELECT DISTINCT first_name FROM employees LIMIT 0, 10;-- (3)
SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
ORDER BY first_name LIMIT 0, 10; -- (4)

(1) 쿼리는 풀 테이블 스캔을 시도하지만 LIMIT 조건 덕분에 10개를 읽으면 더 이상의 읽기 작업을 멈춘다.

(2) 쿼리는 GROUP BY가 있기 때문에 GROUP BY가 처리되어야만 LIMIT 처리를 할 수 있으므로 LIMIT 자체가 효율적으로 동작하게 해주진 않는다.

(3) 쿼리는 DISTINCT에서 정렬 필요 없이 유니크한 레코드만 만들면 된다. 풀 테이블 스캔을 함과 동시에 중복 제거 작업(임시 테이블 사용)을 진행하다가 10개를 읽으면 멈춘다.

(4) 쿼리는 employees 테이블로부터 WHERE 조건에 일치하는 레코드를 읽고 first_name을 기준으로 정렬을 하면서 10건이 정렬이 완료되면 읽기를 중지한다. 하지만 이 쿼리도 우선 WHERE 조건이 일치하는 레코드를 전부 읽어야 하므로 효율적이지 않다.

앞선 쿼리들로 하고 싶은 이야기는 이것이다.

쿼리에 GROUP BY나 ORDER BY 같은 전체 범위 작업이 선행되더라도 LIMIT 옵션이 있으면 중간에 읽기를 그만둘 여지가 있으므로 성능 향상에 약간이나마 도움이 된다는 것이다.

LIMIT의 제약 조건은 표현식이나 별도의 서브 쿼리를 쓸 수 없다는 것이 있다.

🐶 LIMIT 5000000, 10 이런 식으로 앞에 offset이 클 때 최적화하는 방법에 대해서는 다른 포스트에서 참고할 수 있다.

COUNT()

쿼리의 결과 레코드 수를 가져오는 COUNT() 함수는 엄청 빨리 처리할 수 있다고 생각하여 튜닝의 대상이 아니라고 오해하는 사람이 많다.

SELECT COUNT(*) FROM posts WHERE board_id = 1; -- (1)
SELECT * FROM posts WHERE board_id = 1 ORDER BY post_id DESC LIMIT 0, 10; -- (2)

(1) 의 경우에 board_id가 1인 게시글(posts)이 100만 건이면 100만 건을 읽어야 하기에 엄청 느리다. 커버링 인덱스가 가능하면 성능이 매우 좋을 수 있으나 그렇지 않은 경우에는 느리다.

(2) 의 경우에 10건의 게시글만 가져오기 때문에 빠르다.

COUNT 함수가 위 쿼리는 그나마 간단한데 복잡해지기 시작하면 무조건 레코드를 읽어야 하기에 주의해야 하는 쿼리다.

보통 페이징처리할 때 많이 count를 쓰는데 커버링 인덱스가 불가능하다면 페이지 번호를 보여주지 않는 방법(이전, 다음만 있는 것...)을 고려해야 한다.

JOIN

조인은 인덱스가 없으면 무조건 비효율적이라고 보면 된다. 웬만하면 조인에 사용되는 컬럼은 다 인덱스를 생성하도록 하자.

조인에 앞서 인덱스 레인지 스캔을 하는 과정을 알아야 하는데 과정은 다음과 같다.

1. 인덱스 탐색 : 인덱스에서 조건을 만족하는 값이 있는 위치를 찾는다.
2. 인덱스 스캔 : 인덱스 탐색에서 찾은 위치부터 읽어야 할 만큼을 읽는다.

이 원칙이 JOIN에서도 적용된다.

만약 A, B라는 두 테이블을 조인한다고 할 때, 옵티마이저는 드라이빙 테이블과 드리븐 테이블을 정해야 하는데 A가 드라이빙 테이블, B가 드리븐 테이블로 정해졌다고 치자.

그러면 동작은 다음과 같다.

1. A에서 인덱스 탐색
2. A에서 인덱스 스캔 (N개 결과 레코드)
3. A에서 생성된 결과 레코드 N개 * (B에서 인덱스 탐색 + B에서의 인덱스 스캔)

3이 핵심인데 JOIN할 때, 드리븐 테이블에서 A의 결과 레코드마다 인덱스 탐색과 인덱스 스캔이 필요하므로 드리븐 테이블이 효율적으로 검색되어야 하는 게 옵티마이저 입장에서 중요하다.

만약 드리븐 테이블인 B에서 조인하는 컬럼에 인덱스가 없었다? 그러면 테이블 풀 스캔을 해야 하므로 매우 성능이 안 좋아진다.

따라서 옵티마이저는 JOIN할 때 웬만하면 드리븐 테이블이 최적화되도록 드리븐 테이블과 드라이빙 테이블을 선택한다.

JOIN 하는 두 테이블의 컬럼 둘 다 인덱스가 없는 경우는 양쪽 다 테이블 풀 스캔을 해야 하므로 그나마 적게 할 수 있도록 레코드 수가 적은 테이블을 드리븐 테이블로 선택한다.

OUTER JOIN 주의사항

• OUTER로 JOIN되는 테이블은 절대 드라이빙 테이블이 될 수 없다.
• OUTER로 JOIN되는 테이블의 컬럼에 대한 조건은 ON절에 명시해야 한다.
  • 조건을 WHERE 절에 명시한 경우, 옵티마이저에 의해 INNER JOIN을 해버린다.
• 기존에 사용하던 SELECT 쿼리를 SELECT 절만 COUNT(*)로 바꿔서 COUNT 쿼리하면 안 된다.
  • JOIN이 불필요하게 들어가지는 않는지 검토를 반드시 해야 한다.
• NOT IN (subquery), NOT EXISTS 는 비효율적으로 동작한다 따라서 가능하다면 OUTER JOIN을 사용한 ANTI JOIN이 이뤄지도록 개선해야 한다.

GROUP BY .. ORDER BY NULL

• MySQL에서는 GROUP BY 절을 쓰면 "정렬"까지 같이 수행한다.
• 비즈니스 요구사항에 정렬이 필요 없다면, 굳이 "정렬"이라는 불필요한 작업을 해서 성능을 저하시킬 이유가 없다. 이 때는 ORDER BY NULL 이라는 키워드를 적어서 정렬을 하지 않도록 처리한다.

ORDER BY

ORDER BY가 없는 경우 정렬 기준

• 인덱스를 사용하면 인덱스의 정렬 기준
• 인덱스를 사용하지 못하고 풀 테이블 스캔을 한다면 스토리지 엔진에 따라 다름
  • MyISAM : 레코드 저장 순서대로 하다가 중간에 삭제되면 그 중간에 다시 저장하기도 하므로 섞여서 가져옴
  • InnoDB : 항상 프라이머리 키로 클러스터링 되기 때문에 프라이머리키 순서대로 가져옴
• 임시 테이블을 거쳐서 처리되는 경우는 레코드 순서를 예측할 수 없음

결론적으로 이런 경우에 의존하면 안 되고 정렬을 보장하기 위해 ORDER BY 절을 반드시 적용하여 쿼리 해야 한다.

서브 쿼리(Subquery)

MySQL은 서브 쿼리, 특히 FROM절의 서브 쿼리와 WHERE절에 IN 서브 쿼리에서 효율적으로 동작하지 않는다.

서브 쿼리는 외부 쿼리에서 정의된 컬럼을 사용하는지 여부에 따라 상관 서브 쿼리와 독립 서브 쿼리로 나눌 수 있다.

• 상관 서브 쿼리(Correlated subquery) - 외부 쿼리에 정의된 컬럼을 참조하기 때문에 독립적이지 않고 항상 외부 쿼리의 결괏값이 전달되어야만 실행 가능하기에 최적화하기 어렵고 비효율적이다.

SELECT *
FROM employees e
WHERE EXISTS
    (SELECT 1
     FROM dept_emp de
     WHERE de.emp_no = e.emp_no -- 외부 쿼리의 컬럼 참조
        AND de.from_date BETWEEN '2020-01-01' AND '2021-08-19')

• 독립 서브 쿼리(Self-Contained subquery) - 서브 쿼리에서 정의된 컬럼만 참조한다 따라서 서브 쿼리는 외부 쿼리의 상수로 사용된다. 단, 독립 서브 쿼리여도 효율적으로 처리되지 못하는 경우가 많다.

SELECT de.dept_no, de.emp_no
FROM dept_emp de
WHERE de.emp_no = (SELECT e.emp_no
                   FROM employees e
                   WHERE e.first_name='Georgi' AND e.last_name='Facello' LIMIT 1)

MySQL 8.0에 와서는 많이 개선되었다고 하지만 웬만하면 서브 쿼리를 개선의 대상이라고 보고 접근하는 게 좋을 것 같다.

SELECT 만해도 양이 많아 INSERT, DDL 같은 부분은 다음 포스트에서 추가로 작성한다.