[Real MySQL 8.0] 쿼리 작성 및 최적화 - SELECT

일반적으로 웹 서비스와 같이 일반적인 온라인 트랜잭션 처리 환경의 DB에서는 INSERT나 UPDATE 작업의 경우 거의 레코드 단위로 발생하므로 성능상 문제가 되는 경우가 거의 없다.
하지만, SELECT의 경우 여러 개의 테이블로부터 데이터를 조합해서 빠르게 가져와야 하기 때문에 주의를 기울여야 한다.

따라서, SELECT 쿼리의 각 부분에 사용될 수 있는 기능을 성능 위주로 살펴보고자 한다.

1. SELECT 절의 처리 순서

아래와 같은 다양한 키워드를 이용한 SELECT 문이 존재한다. 이런 경우 어떠한 절이 먼저 실행되는지 예측해야 최적화를 할 수 있다.
SELECT, FROM, WHERE. GROUP BY, HAVING, ORDER BY, LIMIT

SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt
FROM salaries s
	INNER JOIN employees e ON e.emp_no=emp_no
WHERE s.emp_no IN (10001, 10002)
GROUP BY s.emp_no
HAVING AVG(s.salary) > 1000
ORDER BY AVG(s.salary)
LIMIT 10;

 

아래 그림과 같이 각 요소가 빠질 수는 있어도 순서가 바뀌어서 실행되는 형태의 쿼리는 거의 없다.

 

하지만, 예외는 존재한다.
1. GROUP BY 절 없이 ORDER BY만 사용된 쿼리
2. ORDER BY나 GROUP BY 절이 있더라도 인덱스를 이용해 처리할 때는 그 단계 자체가 불필요하므로 생략된다.

1. GROUP BY절 없이 ORDER BY만 사용된 경우 예외적으로 첫 번째 테이블만 읽어서 정렬한 뒤에 나머지 테이블을 읽는 구조이다.
아래와 같은 순서로 동작한다.

드라이빙 테이블(WHERE 적용)  -> ORDER BY -> 드리븐 테이블(조인 적용) -> LIMIT

 

인라인 뷰를 이용한 쿼리 순서 변경

위와 다르게 실행 순서를 벗어나는 쿼리가 필요하다면 서브쿼리로 작성된 인라인 뷰를 사용하면 된다.

Q) 인라인 뷰란?
하나의 질의문에서만 생성되어 사용되어 수행이 끝난 후에는 사라지는 뷰
보통 FROM 절에서 서브쿼리를 하나의 테이블로 사용하는 형태를 말함. 

위의 쿼리에서 LIMIT와 ORDER. BY의 동작 순서를 바꾸려면 FROM 절에서 서브 쿼리를 이용하여 인라인 뷰를 만들어서 LIMIT를 먼저 실행한 후에 나중에 바깥(아우터) 쿼리에서 ORDER BY를 실행하면 순서가 뒤바뀌게 된다.

하지만, 인라인 뷰를 사용하는 방식은 임시 테이블이 생성되기 때문에 주의 해야 한다.

 

2. 인덱스를 사용하기 위한 기본 규칙

WHERE절 조건뿐만 아니라 GROUP BY, ORDER BY 절도 인덱스를 이용할 수 있다.

1. 인덱스 컬럼 변형 없이 사용

WHERE 절이나 ORDER BY 또는 GROUP BY가 인덱스를 사용하려면 기본적으로 인덱스 된 컬럼의 값 자체를 변환하지 않고 그대로 사용한다는 조건을 만족해야 한다. 인덱스는 컬럼의 값을 아무런 변환 없이 B-Tree에 정렬해서 저장한다.
WHERE, GROUP BY, ORDER BY에서도 원본값을 검색하거나 정렬할 때만 B-Tree에 정렬된 인덱스를 사용한다.

예외의 케이스는 아래와 같이 salary 컬럼으로 인덱스가 만들어져 있는데, 해당 컬럼을 가공한 후에 다른 상숫값과 비교하는 쿼리는 인덱스를 적절히 사용하지 못한다.

SELECT * FROM salaries WHERE salary*10 > 2000;

해당 쿼리를 "옵티마이저에서 *10을 오른쪽으로 넘겨서 최적화 할 수 있지 않나?"라고 생각할 수도 있지만 그렇지 않다.

2. 비교 대상끼리 데이터 타입 일치

WHERE 절에 사용되는 비교 조건에서 연산자 양쪽의 비교 대상 값은 데이터 타입이 일치해야 한다.

아래 쿼리를 실행했을 때 착각할 수 있는 점이 있다.
age라는 컬럼에 인덱싱을 했으니 실행 계획의 type 컬럼에 "ref" 혹은 "range"가 표시됐을 거라 기대했지만, 사실은 "index"라고 표시된다. "index"는 인덱스 풀 스캔을 의미한다. 인덱스 레인지 스캔을 이용하지 못하고 인덱스 풀 스캔을 한 이유는 age는 VARCHAR타입이지만 비교되는 값 2는 INTEGER 타입으로 데이터 타입이 일치하지 않기 때문이다.

CREATE TABLE test (age VARCHAR(10), INDEX ix_age (age));
SELECT * FROM test WHERE age=2;

위와 같이 데이터 타입이 불일치할 때 MySQL 옵티마이저가 내부적으로 문자열 타입을 숫자 타입으로 변환한 후 비교 작업을 처리한다.
결국 age컬럼이 숫자 타입으로 변환된 후 비교되므로 인덱스 레인지 스캔이 불가능한 것이다.
쿼리를 " WHERE age = '2'; " 로 변경하면 인덱스 레인지 스캔을 사용하도록 유도할 수 있다.

---

3. WHERE 절의 인덱스 사용

 WHERE 조건이 인덱스를 사용하는 방법은 크게 작업 범위 결정 조건과 체크 조건 두 가지 방식으로 구분한다.

두 방식 중에 작업 범위 결정 조건은, WHERE 절에서 동등 비교 조건이나 IN으로 구성된 조건에 사용된 컬럼들이 인덱스의 컬럼 구성과 좌측에서부터 비교했을 때 얼마나 일치하는가에 따라 달라진다.

위 그림에서 위쪽은 4개의 컬럼이 순서대로 결합 인덱스를 생성한 것을 의미한다.
"WHERE 조건절의 순서"에 나열된 순서가 인덱스와 다르더라도 MySQL 서버 옵티마이저는 인덱스를 사용할 수 있는 조건들을 뽑아서 최적화를 수행할 수 있다.
즉, MySQL 옵티마이저는 인덱스를 사용할 수 있는 조건을 뽑아서 최적화를 수행할 수 있기 때문에 WHERE 조건절의 순서는 실제 인덱스와 상관 없다는 것이다. 

한가지 더 중요한 부분이 있다.
위 그림의 WHERE 조건절에서 COL_1과 COL_2는 동등 비교 조건이고, COL_3는 동등 비교 조건이 아닌 작다와 같은 범위 비교조건이므로 COL_3의 뒤의 컬럼인 COL_4의 조건은 작업 범위 결정 조건으로 사용되지 못하고 체크 조건으로 사용된다.
이는 WHERE 조건절과 인덱스 컬럼 순서가 일치하지 않아서가 아니라, 인덱스 순서상 COL_4 직전 컬럼인 COL_3가 범위 비교 조건으로 사용됐기 때문인 것임을 알아야 한다.

 

✅ OR 연산자의 경우

위 예시는 모두 AND 연산자로 연결되는 경우를 가정한 것이고, OR 연산자가 있으면 처리 방법이 완전히 바뀐다.
아래와 예시에서 first_name은 인덱스를 이용할 수 있고, last_name은 인덱스를 사용할 수 없다고 가정한다.

SELECT *
FROM employees
WHERE first_name="kim" OR last_name="hs";

이 두 조건이 AND로 연결되었다면 first_name의 인덱스를 이용할 수 잇지만 OR 연산자로 연결된 경우 테이블 풀 스캔을 선택할 수밖에 없다.
왜냐하면, (인덱스 레인지 스캔) + (풀 테이블 스캔) 을 하는것보다 (풀 테이블 스캔) 작업 한 번이 더 빠르기 때문이다.
만약, first_name과 last_name 둘 다 각각 인덱스가 있따면 index_merge 접근 방법으로 실행할 수 있다.
하지만, 이 방법이 물론 풀 테이블 스캔보다는 빠르지만 여전히 제대로 된 인덱스 하나를 레인지 스캔하는 것보다는 느리다.

WHERE절에서 각 조건이 AND로 연결되면 읽어와야 할 레코드의 건수를 줄이는 역할을 하지만 각 조건이 OR로 연결되면 읽어서 비교해야 할 레코드가 더 늘어나기 때문에 WHERE 조건에 OR 연산자가 있다면 주의해야 한다.

 

4. GROUP BY 절 인덱스 사용

GROUP BY 절의 각 컬럼은 비교연산자를 가지지 않으므로 작업 범위 결정 조건이나 체크 조건과 같이 구분해서 생각할 필요가 없다.
GROUP BY 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 GROUP BY 절은 일단 인덱스를 이용할 수 있다.

GROUP BY절은 아래와 같은 인덱스 사용 조건이 존재한다. (다중 컬럼 인덱스를 기준, but 단일 컬럼 인덱스도 동일하게 적용됨)

• GROUP BY절에 명시된 컬럼이 인덱스 컬럼의 순서와 위치가 같아야 한다.
• 인덱스를 구성하는 컬럼 중에서 뒤쪽에 있는 컬럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 컬럼이 GROUP BY절에 명시되지 않으면 인덱스를 사용할 수 없다.
• WHERE 조건절과는 달리 GROUP BY절에 명시된 컬럼이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 이용하지 못한다.

GROUP BY절은 WHERE과 다르게 인덱스 컬럼의 순서가 중요하게 작용하는것 같다.
아래는 인덱스를 사용하지 못하는 경우를 나열한 것이다.

.. GROUP BY COL_2, COL_1;
.. GROUP BY COL_1, COL_3, COL_2;
.. GROUP BY COL_1, COL_3;
.. GROUP BY COL_1, COL_2, COL_3, COL_4, COL_5;

이번에는 인덱스를 사용할 수 있는 경우를 나열했다.

.. GROUP BY COL_1;
.. GROUP BY COL_1, COL_2;
.. GROUP BY COL_1, COL_2, COL_3;
.. GROUP BY COL_1, COL_2, COL_3, COL_4;

여기서도, 예외는 존재한다.
만약 WHERE 조건절에서 COL_1이 동등 비교 조건으로 사용된다면 GROUP BY 절에 COL_1이 빠져도 인덳르르 이용한 GROUP BY가 가능할 때도 있다.

5. ORDER BY 절의 인덱스 사용

MySQL에서 GROUP BY와 ORDER B는 처리 방법이 상당히 비슷하다.
하지만 다른점이 있다면, 정렬되는 각 컬럼의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 정반대인 경우에만 사용할 수 있다는 것이다.

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

일반적으로 우리가 사용하는 쿼리는 WHERE 절을 가지고 있으며, 선택적으로 ORDER BY나 GROUP BY절을 포함할 것이다.
쿼리에 WHERE절만 또는 GROUP BY나 ORDER BY절만 포함돼 있다면 사용된 절 하나에만 초점을 맞춰서 인덱스를 사용할 수 있게 튜닝하면 된다.

하지만, WHERE절 + ORDER BY절을 가지고 있다고 가정했을 때 WHERE절과 ORDER BY절이 서로 다른 인덱스를 사용할 수는 없다.
이는 WHERE+GROUP BY 또는 GROUP BY+ORDER BY의 경우에도 마찬가지다.

WHERE 절과 ORDER BY(또는 GROUP BY) 절이 같이 사용된 쿼리 문장은 다음 3가지 중 한가지 방법으로만 인덱스를 이용한다.

• WHERE절과 ORDER BY절이 동시에 같은 인덱스를 이용
  • WHERE절의 비교 조건에서 사용하는 컬럼과 ORDER BY 절의 정렬 대상 컬럼이 모두 하나의 인덱스에 연속해서 포함돼 있을 때 이 방식으로 인덱스를 사용할 수 있다.
  • 나머지 방법보다 훨씬 빠른 성능을 보이므로 가능하다면 이 방식으로 처리되도록 쿼리를 튜닝하거나 인덱스를 생성하는게 좋다,
• WHERE 절만 인덱스를 이용
  • ORDER BY절은 인덱스를 이용한 정렬이 불가능하며, 인덱스를 통해 검색된 결과 레코드를 별도의 처리과정(Using Filesort)을 거쳐 정렬 수행한다.
  • 주로 WHERE절의 조건에 일치하는 레코드의 건수가 많지 않을 때 효율적인 방식이다.
• ORDER BY 절만 인덱스를 이용
  • ORDER BY 절은 인덱스를 이용하지만, WHERE절은 인덱스를 이용하지 못한다.
  • 이 방식은 ORDER BY절의 순서대로 인덱스를 읽으면서 레코드 한 건씩 WHERE절의 조건에 일치하는지 비교하고, 일치하지 않을 때는 버리는 형태로 처리한다.
  • 주로 아주 많은 레코드를 정렬해야 할 때는 이런 형태로 튜닝하기도 한다.

 또한,
1. WHERE 절에서 동등 비교 조건으로 비교된 컬럼과 ORDER BY 절에 명시된 컬럼이 순서대로 빠짐없이 인덱스 컬럼의 왼쪽부터 일치해야 한다.
2. WHERE 절에 동등 비교 조건으로 사용된 컬럼과 ORDER BY절의 컬럼이 중첩되는 부분은 인덱스를 사용할 때 문제가 되지 않는다.
이경우 중간에 빠지는 컬럼이 있으면 WHERE절이나 ORDER BY절 모두 인덱스를 사용할 수 없다. 이때는 주로 WHERE절만 인덱스 사용 가능하다.

✅ 위 그림에서 오른쪽 그림과 같이 ORDER BY절에 해당 컬럼이 사용되고 있다면 WHERE 절에 동등 비교 이외의 연산자(ex.범위 비교 연산자)로 비교돼도 WHERE절과 ORDER BY절이 모두 인덱스를 사용할 수 있다.

 

일반적으로 WHERE 절에서 동등 비교로 사용된 컬럼과 ORDER BY 절의 컬럼이 인덱스를 구성하는 컬럼과 같은 순서로 연속해서 사용됐는지를 확인해야 한다.

 

(1) 아래는 위 그림에서 왼쪽 그림에 해당하는 예제이다. 해당 쿼리는 ORDER BY COL_1, COL_2, COL_3와 동일하게 동작한다.

SELECT * FROM test WHERE COL_1=10 ORDER BY COL_2, COL_3;

이 쿼리에서 WHERE 조건이 상수로 동등비교를 하고 있기 때문에, ORDER BY절에 COL_1 컬럼을 추가해도 정렬 순서에 변화가 없다.
하지만, 이렇게 쿼리를 작성하는것이 인덱스 사용여부를 더 쉽게 판단할 수 있다.

 

(2) 이제 범위 조건 비교가 사용되는 쿼리 예제에 대해 살펴보자.

SELECT * FROM test WHERE COL_1 > 10 ORDER BY COL_1, COL_2, COL_3;
SELECT * FROM test WHERE COL_1 > 10 ORDER BY COL_2, COL_3;

결론적으로, 첫 번째 쿼리는 인덱스를 사용가능하고 두 번째 쿼리는 인덱스를 사용 불가능하다.
왜냐하면, 1️⃣ 첫 번째 쿼리는 COL_1 > 10 조건을 만족하는 COL_1값은 여러 개일 수 있지만 ORDER BY절에서 COL_1부터 COL_3까지 순서대로 모두 명시됐기 때문에 인덱스를 사용해 WHERE절과 ORDER BY절을 처리할 수 있다.

 

하지만, 2️⃣ 두 번째 쿼리에서는 WHERE절에서 COL_1이 범위 조건으로 사용됐는데, ORDER BY에는 COL_1이 명시되지 않았기 때문에 ORDER BY에서는 인덱스를 이용할 수 없다.

 

위에서 계속 WHERE + ORDER BY를 기준으로 말했지만 WHERE + GROUP BY 도 똑같다.

 

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

GROUP BY와 ORDER BY 절이 동시에 사용된 쿼리에서 두 절이 모두 하나의 인덱스를 사용해서 처리되려면 GROUP BY절에 명시된 컬럼과 ORDER BY절에 명시된 컬럼의 순서와 내용이 모두 동일해야 한다.

GROUP BY나 ORDER BY 중 둘 중 하나라도 인덱스를 이용할 수 없을 때는 둘 다 인덱스를 사용하지 못한다.

아래와 같은 경우에 둘 다 인덱스 사용 불가하다.

... GROUP BY col_1, col_2 ORDER BY col_2;
... GROUP BY col_1, col_2 ORDER BY col_1, col_3;

 

8. WHERE 조건과 ORDER BY 절, GROUP BY 절의 인덱스 사용

WHERE절과 GROUP BY절, ORDER BY절이 모두 포함된 쿼리가 인덱스를 사용하는지 판단하는 방법은 아래와 같이 3가지 질문을 기본으로 해서 아래의 그림의 흐름을 적용해보면 된다.

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

 

 

9. WHERE 절의 비교 조건 사용 시 주의사항

쿼리가 최적으로 실행되려면 적합한 인덱스와 함께 WHERE 절에 사용되는 비교 조건의 표현식을 적절하게 사용해야 한다.

9-1. NULL 비교

✅ 다른 DBMS와는 조금 다르게 MySQL에서는 NULL 값이 포함된 레코드도 인덱스로 관리된다. 이는 인덱스에서는 NULL을 하나의 값으로 인정해서 관리한다는 것을 의미한다.
SQL 표준에서 NULL의 정의는 비교할 수 없는 값이다. 따라서, 두 값이 모두 NULL일 때 이 두 값이 동등한지 비교하는 것은 불가능하다.
쿼리에서 NULL인지 비교하러면 "IS NULL" (또는 "<=>") 연산자를 사용해야 한다.

SELECT * FROM titles WHERE to_date IS NULL;

+----+--------+------+-----------+-------------------------+
| id | table  | type | key       | Extra                   |
+----+--------+------+-----------+-------------------------+
| 1  | titles | ref  | ix_todate | Using where; Using index|
+----+--------+------+-----------+-------------------------+

위와 같이 SELECT 쿼리를 통해 to_date가 NULL인 레코드를 조회할 때, to_date 컬럼에 생성된 ix_todate 인덱스를 ref 라는 type으로 적절히 이용하고 있음을 알 수 있다.

 

ISNULL() 함수를 이용하여 NULL인지 확인할 수도 있는데 그때는 주의해야 하는 점이 있다.

1. WHERE to_date IS NULL;
2. WHERE ISNULL(to_date);
3. WHERE ISNULL(to_date)=1;
4. WHERE ISNULL(to_date)=true;

1번과 2번의 경우 인덱스 레인지 스캔을 사용하지만, 3번과 4번의 경우 인덱스나 테이블 풀 스캔을 사용한다.
따라서 NULL 비교시에는 IS NULL 연산을 이용하는 것을 권장한다.

 

9-2. 문자열이나 숫자 비교

✅ 문자열 컬럼이나 숫자 컬럼을 비교할 때는 반드시 그 타입에 맞는 상숫값을 사용할 것을 권장한다.

동일한 타입으로 비교할 경우 인덱스를 적절히 사용할 수 있지만,
컬럼의 타입이 문자열이고 비교 상숫값이 숫자인경우에는 옵티마이저가 숫자타입으로 비교를 수행하려고 실행 계획을 수립하기 때문에
해당 컬럼의 타입 변환이 필요하여 인덱스를 사용하지 못하게 된다.

 

9-3. 날짜 비교

MySQL에는 날짜만 저장하는 DATE 타입, 날짜와 시간을 함꼐 저장하는 DATETIME과 TIMESTAMP 타입, 그리고 시간만 저장하는 TIME 타입이 존재한다. 

 

(1) DATE 또는 DATETIME과 문자열 비교

DATE 또는 DATETIME 타입의 값과 문자열을 비교할 때는 문자열 값을 자동으로 DATETIME 타입의 값으로 변환해서 비교를 수행한다.

 

아래와 같은 쿼리처럼 STR_TO_DATE() 함수를 이용해 문자열 "2023-08-15"을 DATE 타입으로 변환할 수도 있지만, 이렇게 컬럼의 타입이 DATE나 DATETIME 이면 별도로 문자열을 DATE나 DATETIME으로 명시적으로 변환하지 않아도 MySQL에서 내부적으로 변환을 수행한다.

mysql> SELECT COUNT(*) FROM employees WHERE hire_date > STR_TO_DATE('2023-08-15', '%Y-%m-%d');

mysql> SELECT COUNT(*) FROM employees WHERE hire_date > '2011-07-23';

  결론적으로 위의 두 쿼리는 모두 인덱스를 효율적으로 이용하며 동일하게 처리된다.

 

하지만 아래와 같이 DATE나 DATETIME 타입 컬럼을 문자열로 변경해버리는 경우가 있는데, 이 경우는 인덱스를 효율적으로 사용할 수 없다.

SELECT COUNT(*) FROM employees WHERE DATE_FORMAT(hire_date, '%Y-%m-%d') > '2023-08-15';

 

(2) DATE와 DATETIME의 비교

DATETIME 값에서 시간 부분만 떼어 버리고 비교하려면 다음 쿼리와 같이 DATE()함수를 이용하면 된다.

SELECT COUNT(*) FROM employees WHERE hire_date > DATE(NOW());

다른 방법으로, DATETIME 값을 DATE 타입으로 만들지 않고 비교하면 MySQL 서버가 DATE 타입의 값을 DATETIME으로 변환해서 같은 타입을 만든 다음 비교를 수행한다.

DATETIME과 DATE 타입의 비교에서 타입 변환은 인덱스의 사용 여부에 영향을 미치지 않기 때문에 성능보다는 쿼리의 결과에 주의해서 사용하면 된다.

 

 

(3) DATETIME과 TIMESTAMP의 비교

DATE나 DATETIME 타입 값과 TIMESTAMP의 값을 별도의 타입 변환 없이 비교하면 문제가 발생한다.
인덱스 레인지 스캔으로 동작하지 않고 실제 결과 값이 잘못 출력될 수 있다.

• FROM_UNIXTIME() 함수
  • 컬럼이 DATETIME 타입일 때, TIMESTAMP -> DATETIME 값으로 변환
• UNIX_TIMESTAMP() 함수
  • 컬럼이 TIMESTAMP 타입일 때, DATETIME -> TIMESTAMP 값으로 변환 

 

 

9-4. ⭐️ Short-Circuit Evaluation

Short-Circuit Evaluation이란 여러 개의 표현식이 AND 또는 OR 논리 연산자로 연결된 경우 선행 표현식의 결과에 따라 후행 표현식을 평가할지 말지 결정하는 최적화를 의미한다.

 

다음과 같은 표현식의 작동 방식을 살펴보자.

boolean is_valid;

if (is_valid && has_modified()) {
	commit();
}

isValid  불리언 변수의 값이 TRUE이면 has_modified() 함수를 호출하고, 그 결과값까지 TRUE라면 commit() 함수가 실행될 것이다.
하지만, 둘 중 하나라도 FALSE이면 commit()은 실행되지 않는다.
따라서, 선행 표현식의 결과에 따라 후행 표현식을 평가할지 말지 결정하여 최적화 해야 한다.

 

MySQL에서 "short-circuit evaluation"이 어떻게 쿼리의 성능에 영향을 미치는지 보자.
아래 쿼리에서 모두 인덱스를 사용하지 못하고 풀 테이블 스캔을 사용한다고 가정한다.

-- // 1번 조건을 만족하는 레코드 건수
SELECT COUNT(*) FROM salaries
WHERE CONVERT_TZ(from_date,'+00:00', '+09:00') > '1991-01-01';
+----------+
| COUNT(*) |  
+----------+
|  2442943 |
+----------+

-- // 2번 조건을 만족하는 레코드 건수
SELECT COUNT(*) FROM salaries
WHERE to_date < '1985-01-01' ;
+----------+
| COUNT(*) |  
+----------+
|        0 |
+----------+

1번 조건과 2번 조건을 AND로 연결한 쿼리의 결과는 0건이 될 것이다.
여기서 조건의 순서가 쿼리의 성능에 영향을 미칠 수 있다는 것을 생각해야 한다.

-- // (1번 조건, 2번 조건) 순으로 조합
SELECT * FROM salaries
WHERE CONVERT_TZ(from_date,'+00:00', '+09:00') > '1991-01-01'
	AND to_date < '1985-01-01';

==> (0.73 sec)

-- // (2번 조건, 1번 조건) 순으로 조합
SELECT * FROM salaries
WHERE to_date < '1985-01-01'
	AND CONVERT_TZ(from_date,'+00:00', '+09:00') > '1991-01-01';

==> (0.52 sec)

2번 조건을 먼저 사용했을 때, 쿼리 응답시간이 0.21초 더 빠른것을 볼 수 있다.
별 차이 없어 보일 수 있지만 1번 조건이 더 많은 CPU를 사용하거나 더 많은 자원을 사용하는 조건이었다면 시간 차이는 더 커졌을 것이다.

 

(1) 1번 조건이 먼저 사용된 경우에서 MySQL 서버는 salaries 테이블에 전체 레코드에 대해 CONVERT_TZ() 함수를 실행하고 그 결과에 대해 to_date 비교 작업을 앞 조건에서 필터링 된 레코드의 수만큼 실행한다.
(2) 2번 조건이 먼저 사용된 경우에서는 해당 조건 비교 작업만 전체 레코드의 수 만큼만 진행하면 되고 CONVERT_TZ() 함수는 한 번도 실행되지 않으므로 성능이 30% 정도 빨라진 것이다.

 

MySQL 서버는 쿼리의 WHERE 절에 나열된 조건을 순서대로 "Short-circuit evaluation"방식으로 평가해서 해당 레코드를 반환해야 할지 말지를 결정한다.
⭐️ ⭐️ ⭐️ 그런데 WHERE 조건 중에서 인덱스를 사용할 수 있는 조건이 있다면 "Short-circuit evaluation"과는 무관하게 해당 조건을 가장 최우선으로 사용한다.
결론적으로 WHERE 조건절에 나열된 조건의 순서가 인덱스의 사용 여부를 결정하지는 않는다.

 

아래와 같은 쿼리에서 first_name 컬럼만 인덱스를 효율적으로 사용할 수 있다고 가정하면,
first_name 비교 조건 부터 우선 실행되고 그 다음에 last_name, birth_date 순으로 비교 조건이 실행된다.

SELECT * FROM employees
WHERE last_name='hyeon'
	AND first_name='kim'
	AND MONTH(birth_date)=1;

 

조금 더 복잡한 예제를 살펴보자. 참고로 아래 쿼리의 EXISTS (subquery) 부분은 GROUP BY.. HAVING .. 절을 가지고 있기 때문에 MySQL의 세미 조인 최적화를 활용할 수 없는 형태이다. 또한, first_name 컬럼만 인덱스를 사용할 수 있다고 가정한다.

SELCET * FROM employees e
WHERE e.first_name='Kim'
	AND EXISTS (SELECT 1 FROM salaries s
    			WHERE s.emp_no=e.emp_no AND s.to_date > '1999-01-01'
                	GROUP BY s.salary HAVING COUNT(*)>1)
        AND e.last_name='hyeon';

위 쿼리에서 우선 1️⃣ 첫 번째로 인덱스를 사용할 수 있는 first_name 컬럼 조건이 최우선으로 실행되어 데이터의 범위를 최소화한다.
그리고 WHERE절에서 서브쿼리 조건이 last_name 조건보다 먼저 나열됐기 때문에 MySQL 서버는 2️⃣ 두 번째로 EXIST(subquery) 조건을 먼저 평가한다. 그리고 그 후에 3️⃣ 세 번째로 last_name 조건이 평가되는 순으로 실행된다.
이 경우에 last_name의 비교 조건이 먼저 실행되는 게 유리하지만 순서상 그렇지 못하다는 문제가 있다.

 

이번에는 아래와 같이 EXIST(subquery) 조건과 last_name 조건 순서를 변경해서 최대한 앞에서 데이터를 많이 필터링하는게 유리하다. 이 경우에는 first_name -> last_name 비교로 인해 1개의 레코드가 검색되었고 이 1개의 레코드에 대해서만 EXIST (subquery)조건 만족 여부를 평가함으로써 성능을 최적화할 수 있다. 

SELCET * FROM employees e
WHERE e.first_name='Kim'
        AND e.last_name='hyeon'
        AND EXISTS (SELECT 1 FROM salaries s
            		WHERE s.emp_no=e.emp_no AND s.to_date > '1999-01-01'
            		GROUP BY s.salary HAVING COUNT(*)>1);

 

⭐️⭐️⭐️ 결론적으로, MySQL 서버에서 쿼리를 작성할 때 가능하면 복잡한 연산 또는 다른 테이블의 레코드를 읽어야 하는 서브쿼리 조건 등은 WHERE 절의 뒤쪽으로 배치하는 것이 성능상 도움이 된다.
물론, WHERE 조건 중 인덱스를 사용할 수 있는 조건은 어느 위치에 있던지 먼저 실행되므로 순서를 고려하지 않아도 된다.

 

9-5. DISTINCT

특정 컬럼의 유니크한 값을 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.
많은 사용자들이 조인을 하는 경우 레코드가 중복해서 출력되는 것을 막기 위해 DISTINCT를 남용하는 경향이 있다.

DISTINCT를 남용하는 것은 성능상의 문제도 있지만 쿼리의 결과도 의도와 달라질 수 있으므로 주의해야 한다.

 

9-6. LIMIT n

LIMIT은 쿼리 결과에서 지정된 순서에 위치한 레코드만 가져오고자 할 때 사용한다.

SELECT * FROM employees
WHERE emp_no BETWEEN 10001 AND 10010
ORDER BY first_name
LIMIT 0, 5;

위의 쿼리는 아래와 같은 순서로 실행된다.

1. employees 테이블에서 WHERE 절의 검색 조건에 일치하는 레코드를 전부 읽어 온다.
2. 1번에서 읽어온 레코드를 first_name 컬럼값에 따라 정렬한다.
3. 정렬된 결과에서 상위 5건만 사용자에게 반환한다.

MySQL의 LIMIT은 WHERE 조건이 아니기 때문에 항상 쿼리의 가장 마지막에 실행된다.
✅ LIMIT의 중요한 특성은 LIMIT에서 필요한 레코드 건수만 준비되면 즉시 쿼리를 종료한다는 것이다.
✅ 즉, 위의 쿼리에서 모든 레코드의 정렬이 완료되지 않았다 하더라도 상위 5건까지만 정렬되면 작업을 멈춘다는 것이다!

 

아래 예제는 모두 인덱스를 적절히 사용하지 못하는 경우를 가정한다.

• 1️⃣ SELECT * FROM employees LIMIT 0, 10;
  • 풀 테이블 스캔을 실행하면서 MySQL이 스토리지 엔진으로부터 10개의 레코드를 읽어 들이는 순간 스토리지 엔진으로부터 읽기 작업을 멈춘다.
  • 이러한 형태의 쿼리는 상당히 빨리 끝날 수 있다.
• 2️⃣ SELECT first_name FROM employees GROUP BY first_name LIMIT 0, 10;
  • GROUP BY가 있기 때문에 GROUP BY 처리가 완료되고 나서야 LIMIT 처리를 수행할 수 있다.
  • 이 경우 실질적인 서버 작업 내용을 크게 줄여주지는 못한다.
• 3️⃣ SELECT DISTINCT first_name FROM employees LIMIT 0, 10;
  • DISTINCT는 정렬에 대한 요건이 없이 유니크한 그룹만 만들어 내면 된다.
  • MySQL은 스토리지 엔진을 통해 풀 테이블 스캔 방식을 이용하여 employees 테이블 레코드를 읽어 들임과 동시에 DISTINCT를 위한 중복 제거 작업(임시 테이블 사용)을 진행한다.
  • 이 작업을 반복적으로 처리하다가 유니크한 레코드가 LIMIT 건수만큼 채워지면 그 순간 쿼리를 멈춘다.
  • DISTINCT와 함께 사용된 LIMIT는 실질적인 중복 제거 작업의 범위를 줄이는 역할을 한다.
  • 작업량을 상당히 줄여줄 수 있는 쿼리이다.
• 4️⃣ SELECT * FROM employees WHERE emp_no BETWEEN 10001 AND 11000 ORDER BY first_name LIMIT 0, 10;
  • employees 테이블로부터 where 조건절에 일치하는 레코드를 읽은 후 first_name 컬럼의 값으로 정렬을 수행한다.
  • 정렬을 수행하면서 필요한 10건이 완성되는 순간, 나머지 작업을 멈추고 사용자에게 값을 반환한다.
  • 정렬을 수행하기 전에 WHERE 조건에 일치하는 모든 레코드를 읽어 와야 하지만 읽어온 결과가 전부 정렬돼야 쿼리가 완료되는 것이 아니라 필요한 만큼만 정렬하면 된다.
  • 하지만, 크게 작업량을 줄여주지는 못한다.

✅ 쿼리 문장에서 GROUP BY나 ORDER BY 같은 전체 범위 작업이 선행되더라도 LIMIT 절이 있다면 크지 않지만 나름의 성능 향상은 발생한다.

 

 

❗️LIMIT 주의사항

• 1. 1개 또는 2개의 인자를 사용할 수 있다.
  • 인자가 1개인 경우: 상위 N개의 레코드를 가져옴
  • 인자가 2개인 경우: 첫 번째 인자에 지정된 위치부터 두 번째 인자에 명시된 개수만큼의 레코드를 가져온다.
    • 첫번째 인자(시작위치, 오프셋) 은 0부터 시작한다는 것에 주의하자.
    • LIMIT 10, 10; 은 상위 11번째 부터 10개의 레코드를 가져온다.
• 2. LIMIT의 인자로 표현식이나 별도의 서브쿼리를 사용할 수 없다.
  • ex) LIMIT (100-10) -> SQL syntax ERROR 발생
• 3. 페이징 처리
  • 실제 쿼리의 성능은 사용자의 화면에 출력된 레코드의 수가 아니라 MySQL 서버가 그 결과를 만들어 내기 위해 어떠한 작업들을 했는지가 중요하다.
  • 많은 응용  프로그램에서 데이터를 SELECT 할 때 조금씩 잘라서(페이징) 가져가게 되는데, 이때 LIMIT를 많이 사용한다.
    • ex) SELECT * FROM salaries ORDER BY salary LIMIT n, m;
  • 문제가 되는 예시가 존재한다. ex) SELECT * FROM salaries ORDER BY salary LIMIT 2000000, 10;
    • 해당 쿼리는 먼저 salaries 테이블을 처음부터 2000010건의 레코드를 읽은 후에, 2000000건은 버리고 마지막 10건만 사용자에게 반환한다.
    • 사용자에게는 10건만 반환되지만 실제로는 2000010건의 레코드를 읽으므로 쿼리가 느려진다.
  • ✅ LIMIT 조건의 페이징이 처음 몇 개 페이지 조회로 끝나지 않을 가능성이 높다면 다음과 같이 WHERE 조건절로 읽어야 할 위치를 찾고 그 위치에서 10개만 읽는 형태의 쿼리를 사용하는 것이 좋다.
    • 1. 첫페이지 조회용 쿼리
      • SELECT * FROM salaries ORDER BY salary LIMIT 0, 10;
    • ✅ 2. 두 번째 페이지 조회용 쿼리 (첫 페이지의 마지막 salary 값과 emp_no값을 사용)
      • SELECT * FROM salaries
        WHERE salary>=38864 AND NOT (salary=38864 AND emp_no<=274049)
        ORDER BY salary LIMIT 0, 10;
      • salaries 테이블에는 salary 컬럼의 인덱스가 있는데, 이 인덱스는 중복이 허용되는 인덱스이므로 단순히 이전 페이지의 마지막 salary 값 보다 큰 것을 조회하거나 크거나 같은 경우를 조회하면 중복이나 누락이 발생할 수 있으므로 "NOT (salary=38864 AND emp_no<=274049)" 조건을 추가하여 이전 페이지에서 이미 조회됐던 건을 제외한 것이다.
  • 중복이나 누락을 제외하기 위한 방법은 사용하는 인덱스가 몇 개의 컬럼으로 구성됐는지, 유니크한지에 따라 달라질 수 있으므로 주의해야 한다.

 

9-7. COUNT()

COUNT() 함수는 결과 레코드의 건수를 반환하는 함수다.
COUNT() 함수는 컬럼이나 표현식을 인자로 받으며, 특별한 형태로 "*"을 사용할수도 있다. 해당 경우에 "*"은 SELECT 에서 사용할 때 처럼 모든 컬럼을 가져오라는 의미가 아니라 그냥 레코드 자체를 의미하는 것이다.
따라서, COUNT(PK컬럼), COUNT(1)과 같이 사용하지 않고 COUNT(*)로 표현해도 동일한 처리 성능을 보인다.

 

1️⃣ MyISAM 스토리지 엔진을 사용하는 경우는 항상 테이블의 메타 정보에 레코드 건수를 관리하기 때문에 WHERE절이 없는 COUNT(*)쿼리에 대해서 빠르게 처리된다. 하지만 WHERE 조건이 있으면 일반적인 DBMS와 같이 세어보아야 한다.

 

2️⃣ InnoDB 스토리지 엔진을 사용하는 경우는 WHERE 조건이 없는 COUNT(*) 쿼리라 하더라도 직접 데이터나 인덱스를 읽어야만 레코드 건수를 가져올 수 있으므로 주의해서 사용해야 한다.

 

COUNT 쿼리 주의 사항

• ORDER BY나 LEFT JOIN과 같은 레코드 건수를 가져오는 것과 무관한 작업을 포함하는 경우
  • COUNT(*) 쿼리에 ORDER BY 절은 어떤 경우에도 필요하지 않다.
  • LEFT JOIN 또한 레코드 건수의 변화가 없거나 아우터 테이블에서 별도 체크를 하지 않아도 된다면 제거하는 것이 성능상 좋다.
  • MySQL 8.0 부터는 옵티마이저가 COUNT(*) 쿼리에 사용된 ORDER BY는 무시하도록 개선됐다.
• 인덱스를 사용하지 못하는 경우 
  • 페이징해서 데이터를 가져오는 것보다 몇 배 또는 몇십 배 느리게 실행될 수 있다.
• COUNT() 함수에 컬럼명이나 표현식이 인자로 사용되는 경우
  • 레코드의 결과가 NULL이 아닌 레코드 건수만 반환
  • NULL이 될 수 있는 컬럼은 의도대로 쿼리가 동작하는지 확인하는게 좋다.

게시물 목록을 보여줄 때 일반적으로 아래와 같은 쿼리를 사용할 수 있다.

-- // 게시물 건수 확인
SELECT COUNT(*) FROM articles WHER board_id=1;

-- // 특정 페이지의 게시물 조회
SELECT * FROM articles WHERE board_id=? ORDER BY article_id DESC LIMIT 0, 10;

board_id가 1인 레코드의 수가 백만 건이라고 가정하면 첫번째 쿼리는 articles 테이블에서 100만 건을 읽어야 한다.
물론 인덱스만 읽어서 처리(커버링 인덱스)가 가능하다면 쿼리 성능이 빨라지겠지만, 
실제 서비스에서는 삭제된 게시물인지 등 여러 체크해야 되는 부분이 발생하기 때문에 테이블의 데이터를 읽어야만 하는 경우가 대부분이다.
그렇다면 건수를 확인하는 첫 번째 쿼리는 두 번째 쿼리보다 10만 배 느리게 처리될 것이다.
두 번째 쿼리도 결국 다음 페이지로 넘어갈수록 성능이 더 느려질 가능성이 높다.

 

따라서, 해당 경우에는 모든 페이지 번호를 보여주는 것보다는 "이전", "다음" 버튼만 표시하는 방식을 검토해볼 수 있다.