Real MySQL 8.0 4 - 쿼리 작성 및 최적화

MySQL 연산자와 내장 함수

---

숫자

 숫자 값을 상수로 SQL에 사용할 때는 다른 DBMS와 마찬가지로 따옴표(' 또는 ") 없이 숫자 값을 입력하면 된다. 또한 문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자 값이거나 숫자 타입의 컬럼이면 MySQL 서버가 문자열 값을 숫자 값으로 자동 변환한다. 하지만 이처럼 숫자 값과 문자열 값을 비교할 때는 한 가지 주의할 사항이 있다. 서로 다른 타입으로 WHERE 조건 비교가 수행되는 다음 쿼리를 잠깐 살펴보자.

SELECT * FROM tab_test WHERE number_cloumn='10001';
SELECT * FROM tab_test WHERE string_cloumn=10001;

 위 쿼리와 같이 두 비교 대상이 문자열과 숫자 타입으로 다를 때는 자동으로 타입의 변환이 발생한다. MySQL은 숫자 탙입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비교를 수행한다.

 첫 번째 쿼리는 주어진 상숫값을 숫자로 변환하는데, 이때는 상숫값 하나만 변환하므로 선능과 관련된 문제가 발생하지 않는다. 두 번째 쿼리는 주어진 상숫값이 숫자 값인데, 비교되는 컬럼은 문자열 컬럼이다. 이때 MySQL은 문자열 컬럼을 숫자로 변환해서 비교한다. 즉, string_column 컬럼의 모든 문자열 값을 숫자로 변환해서 비교를 수행해야 하므로 string_column에 인덱스가 있더라도 이를 이용하지 못한다. string_column에 알파벳과 같은 문자가 포함된 경우에는 숫자 값으로 변환할 수 없으므로 쿼리 자체가 실패할 수도 있다.

 원천적으로 이러한 문제점을 제거하려면 숫자 값은 숫자 타입의 컬럼에만 저장해야 한다. 아주 간단한 것 같지만 처음 데이터 모델이 생성된 이후 이전저런 변경을 거치다 보면 이처럼 간단한 규칙도 검사하지 못할 때가 허다하다. 주로 코드나 타입과 같은 값을 저장하는 컬럼에서 이 같은 현상이 자주 발생하므로 주의하자.

날짜

 다른 DBMS에서 날짜 타입을 비교하거나 INSERT하려면 문자열을 DATE 타입으로 변환하는 코드가 필요하다. 하지만 MySQL에서는 정해진 형태의 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIME 값으로 변환하기 때문에 복잡하게 STR_TO_DATE() 같은 함수를 사용하지 않아도 된다.

SELECT * FROM dept_emp WHERE from_date='2011-04-29';
SELECT * FROM dept_emp WHERE from_date=STR_TO_DATE('2011-04-29','%Y-%m-%d);

 첫 번째 쿼리와 같이 날짜 타입의 컬럼과 문자열 값을 비교하는 경우 MySQL 서버는 문자열 값을 DATE 타입으로 변환해서 비교한다. 두 번째 쿼리는 SQL에서 문자열을 DATE 타입으로 강제 변화해서 비교하는 예제인데, 이 두 쿼리의 차이점은 없다. 첫 번째 쿼리와 같이 비교한다고 해서 from_date 컬럼의 값을 문자열로 변환해서 비교하지 않기 때문에 from_date 컬럼으로 생성된 인덱스를 이용하는 데 문제가 되지 않는다.

불리언

 BOOL이나 BOOLEAN이라는 타입이 있지만 사실 이것은 TINYINT 타입에 대한 동의어일 뿐이다. 테이블의 컬럼을 BOOL로 생성한 뒤에 조회해보면 컬럼의 타입이 BOOL이 아니라 TINYINT라는 점을 알 수 있다. MySQL에서는 다음 예제 쿼리와 같이 TRUE 또는 FALSE 형태로 비교하거나 값을 저장할 수 있다. 하지만 이는 BOOL 타입뿐만 아니라 숫자 타입의 컬럼에도 모두 적용되는 비교 방법이다.

CREATE TABLE tb_boolean (bool_value BOOLEAN);

INSERT INTO tb_boolean VALUES (FALSE);
SELECT * FROM tb_boolean WHERE bool_value=FALSE;
SELECT * FROM tb_boolean WHERE boo_value=TRUE;

 위의 쿼리에서 TRUE나 FALSE로 비교했지만 실제로 값을 조회해 보면 0 또는 1 값이 조회된다. 즉, MySQL은 C/C++ 언어에서처럼 TRUE 또는 FALSE 같은 불리언 값을 정수로 매핑해서 사용하는 것이다. 이때 MySQL에서는 FALSE가 C/C++ 언어에서처럼 정수값 0이 되지만 TRUE는 C/C++ 언어와 달리 1만을 의미한다는 점에 주의해야 한다. 그래서 숫자 값이 저장된 컬럼을 TRUE나 FALSE로 조회하면 0이나 1 이외의 숫자 값은 조회되지 않는다.

MySQL 연산자

동등(Euqal) 비교(=, <=>)

 동등 비교는 다른 DBMS에서와 마찬가지로 "=" 기호를 사용해 비교를 수행하면 된다. 하지만 MySQL에서는 동등 비교를 위해 "<=>" 연산자도 제공한다. "<=>" 연산자는 "=" 연산자와 같으며, 부가적으로 NULL 값에 대한 비교까지 수행한다. MySQL에서는 이 연산자를 NULL-Safe 비교 연산자라고 하는데, "="연산자와 "<=>"의 차이를 예제로 살펴보자.

=로 NULL 비교

<=>로 NULL 비교

위 예제 결과에서도 알 수 있듯이 NULL은 "IS NULL" 연산자 이외에는 비교할 방법이 없다. 그래서 첫 번째 쿼리에서 한쪽이 NULL이면 비교 결과도 NULL로 반환한다. 하지만 Null-Safe 비교 연산자를 이용해 비교한 결과를 보면 양쪽 비교 대상 모두 NULL이라면 TRUE를 반환하고, 한쪽만 NULL이라면 FALSE를 반환한다. 즉, "<=>" 연산자는 NULL을 하나의 값으로 인식하고 비교하는 방법이라고 볼 수 있다.

REGEXP 연산자

 REGEXP 연산자를 문자열 컬럼 비교에 사용할 때 REGEXP 조건의 비교는 인덱스 레인지 스캔을 사용할 수 없다. 따라서 WHERE 조건절에 REGEXP 연산자를 사용한 조건을 단독으로 사용하는 것은 성능상 좋지 않다. 가능하다면 데이터 조회 범위를 줄일 수 있는 조건과 함께 REGEXP 연산자를 사용하길 권장한다.

LIKE 연산자

 REGEXP 연산자보다는 훨씬 단순한 문자열 패턴 비교 연산자지만 DBMS에서는 LIKE 연산자를 더 많이 사용한다. REGEXP 연산자는 인덱스를 전혀 사용하지 못한다는 단점이 있지만 LIKE 연산자는 인덱스를 이용해 처리할 수도 있다. LIKE 연산자는 정규 표현식을 검사하는 것이 아니라 어떤 상수 문자열이 있는지 없는지 정도를 판단하는 연산자다. 다음 예제를 통해 LIKE 연산자의 사용법을 한번 살펴보자.

SELECT 'abcdef' LIKE 'abc%';
-- 결과 : 1

SELECT 'abcdef' LIKE '%abc';
-- 결과 : 0

SELECT 'abcdef' LIKE '%ef';
-- 결과 : 1

 LIKE에서 사용할 수 있는 와일드카드 문자는 "%"와 "_"가 전부다. REGEXP는 비교 대상 문자열의 일부에 대해서만 일치해도 TRUE를 반환하는 반면, LIKE는 항상 비교 대상 문자열의 처음부터 끝까지 일치하는 경우에는 TRUE를 반환한다.

• %: 0 또는 1개 이상의 모든 문자에 일치(문자의 내용과 관계없이)
• _: 정확히 1개의 문자에 일치(문자의 내용과 관계없이)

와일드카드 문자일 '%'나 '_' 문자 자체를 비교한다면 ESCAPE 절을 LIKE 조건 뒤에 추가해 이스케이프 문자(Escape sequence)를 설정할 수 있다.

SELECT 'abc' LIKE 'a%';
-- 결과 : 1

SELECT 'a%' LIKE 'a%';
-- 결과 : 1

SELECT 'abc' LIKE 'a/%' ESCAPE '/';
-- 결과 : 0

SELECT 'a%' LIKE 'a/%' ESCAPE '/';
-- 결과 : 1

 LIKE 연산자는 와일드카드 문자인 (%, _)가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔으로 사용할 수 있지만 와일드카드가 검색어의 앞쪽에 있다면 인덱스 레인지 스캔을 사용할 수 없으므로 주의해서 사용해야 한다. 이 경우 인덱스의 Left-most 특성으로 인해 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식으로 쿼리가 처리된다.

BETWEEN 연산자

 "크거나 같다"와 "작거나 같다"라는 두 개의 연산자를 하나로 합친 연산자다. BETWEEN 연산자는 다른 비교 조건과 결합해 하나의 인덱스를 사용할 때 주의해야 할 점 이 있다. 동등 비교 연산자와 BETWEEN 연산자를 이용해 부서 번호와 사원 번호로 dept_emp 테이블을 조회하는 다음 쿼리를 한번 생각해보자.

SELECT * FROM dept_emp
WHERE dept_no = 'd003' AND emp_no = 10001;

SELECT * FROM dept_emp
WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no = 10001;

 dept_emp 테이블에는 (dept_no, emp_no) 컬럼으로 구성된 프라이머리 키가 존재한다. 그래서 첫 번째 쿼리는 dept_no와 emp_no 조건 모두 인덱스를 이용해 범위를 줄여주는 방법으로 사용할 수 있다. 하지만 두 번째 쿼리에서 사용한 BETWEEN은 크다(>) 또는 작다(<) 연산자와 같이 범위를 읽어야 하는 연산자라서 dept_no가 'd003'보다 크거나 같고 'd005'보다 작거나 같은 모든 인덱스의 범위를 검색해야만 한다. 결국 BETWEEN이 사용된 두 번째 쿼리에서 emp_no=10001 비교 범위를 줄이는 역할을 하지 못한다.

 BETWEEN과 IN을 동일한 비교 연산자로 생각하는 사람도 있는데, 사실 BETWEEN은 크다와 작다 비교를 하나로 묶어 둔 것에 가깝다. 그리고 IN 연산자의 처리 방법은 동등 비교(=) 연산자와 비슷하다. 그림은 이 IN과 BETWEEN 처리 과정의 차이를 보여주는데, IN 연산자는 여러 개의 동등 비교(=)를 하나로 묶은 것과 같은 연산자라서 IN과 동등 비교 연산자는 같은 형태로 인덱스를 사용한다.

BETWEEN(왼쪽)과 IN(오른쪽)의 인덱스 사용 방법의 차이

 BETWEEN 조건을 사용하는 위의 쿼리는 dept_emp 테이블의 (dept_no, emp_no) 인덱스의 상당히 많은 레코드(전체 데이터의 1/3)를 읽는다. 하지만 실제로 가져오는 데이터는 1건밖에 안 된다. 결국 이 쿼리는 10만 건을 읽어서 1건 반환하는 것이다. 그런데 이 쿼리를 다음과 같은 형태로 바꾸면 emp_no=10001 조건도 작업 범위를 줄이는 용도로 인덱스를 이용할 수 있게 된다.

SELECT * FROM dept_emp
WHERE dept_no IN ('d003', 'd004', 'd005')
AND emp_no=10001;

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

 이 예제처럼 여러 컬럼으로 인덱스가 만들어져 있는데, 인덱스 앞쪽에 있는 컬럼의 선택도가 떨어질 때는 IN으로 변경하는 방법으로 쿼리의 성능을 개선할 수도 있다. 

IN 연산자

 여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자다. 여러 개의 값이 비교되지만 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리된다. IN 연산자는 다음과 같이 두 형태를 구문해서 생각해볼 필요가 있다.

• 상수가 사용된 경우 - IN (?, ? , ?)
• 서브쿼리가 사용된 경우 - IN (SELECT .. FROM ..)

 IN 연산자에 상수가 사용된 경우는 동등 비교와 동일하게 작동하기 때문에 매우 빠르게 쿼리가 처리될 것이다. MySQL 8.0 이전 버전까지는 IN 절에 튜플(레코드)을 사용하면 항상 풀 테이블 스캔을 했었다. 다음 예제 쿼리를 한번 살펴보자.

SELECT * 
FROM dept_emp
WHERE (dept_no, emp_no) IN (('d001',10017), ('d002',10144), ('d003',10054));

 위의 예제 쿼리는 IN 절의 상숫값이 단순 스칼라값이 아니라 튜플이 사용됐다. MySQL 8.0 이전 버전까지는 이런 쿼리를 실행하면 성능에 문제가 생겨서 일부러 쿼리를 쪼개어 여러 번 실행했다. 하지만 MySQL 8.0 버전부터는 위의 쿼리와 같이 IN 절에 튜플을 그대로 나열해도 인덱스를 최적으로 사용할 수 있게 개선됐다. 다음은 MySQL 8.0 버전에서 위 쿼리의 실행 계획을 확인해본 결과다.

id| table    | type  | key     | key_len | rows | Extra |
--+----------+-------+---------+---------+------+-------+
 1| dept_emp | range | PRIMARY | 20      | 3    |       |

 실행 계획을 살펴보면 dept_emp 테이블의 프라이머리 키를 이용했는데, key_len 컬럼의 값이 20인 것으로 보아 demp_emp 컬럼(4글자x4바이트)과 emp_no 컬럼(4바이트)을 모두 이용해 인덱스 레인지 스캔을 실행한다는 것을 확인할 수 있다.

 IN (subquery) 형태의 조건이 사용된 쿼리는 최적화가 매우 까다로운데, MySQL 8.0 이전 버전까지만 해도 최적화가 상당히 불안했다. 하지만 MySQL 8.0 버전부터는 IN (subquery) 같은 세미 조인의 최적화가 많이 안정화됐다. 

 NOT IN의 실행 계획은 인덱스 풀 스캔으로 표시되는데, 동등이 아닌 부정형 비교여서 인덱스를 이용해 처리 범위를 줄이는 조건으로는 사용할 수 없기 때문이다. NOT IN 연산자가 프라이머리 키와 비교될 때 가끔 쿼리의 실행계획에 인덱스 레인지 스캔이 표시되는 경우가 있다. 하지만 이는 InnoDB 테이블에서 프라이머리 키가 클러스터링 키이기 때문일 뿐 실제 IN과 같이 효율적으로 실행한다는 것을 의미하지 않는다.

MySQL 내장 함수

NULL 값 비교 및 대체(IFNULL, ISNULL)

 IFNULL()은 컬럼이나 표현식의 값이 NULL인지 비교하고, NULL이면 다른 값으로 대쳏는 용도로 사용할 수 있는 함수다. IFNULL() 함수에는 두 개의 인자를 전달하는데, 첫 번째 인자는 NULL인지 아닌지 비교하려는 컬럼이나 표현식을, 두 번째 인자로는 첫 번째 인자의 값이 NULL일 경우 대체할 값이나 컬럼을 설정한다. IFNULL() 함수의 반환 값은 첫 번째 인자가 NULL이 아니면 첫 번째 인자의 값을, 첫 번째 인자의 값이 NULL이면 두 번째 인자의 값을 반환한다.

 ISNULL() 함수는 이름 그대로 인자로 전달한 표현식이나 컬럼의 값이 NULL인지 아닌지 비교하는 함수다. 반환되는 값은 인자의 표현식이 NULL이면 TRUE(1), NULL이 아니면 FALSE(0)를 반환한다. 두 함수의 사용법을 예제로 살펴보자.

SELECT IFNULL(NULL, 1);
-- 결과 : 1

SELECT IFNULL(0, 1);
-- 결과 : 0

SELECT ISNULL(0);
-- 결과 : 0

SELECT ISNULL(1/0);
-- 결과 : 1

벤치마크(BENCHMARK)

 BENCHMARK() 함수는 SLEEP() 함수와 같이 디버깅이나 간단한 함수의 성능 테스트용으로 아주 유용한 함수다. BENCHMARK() 한수는 2개의 인자를 필요로 한다. 첫 번째 인자는 반복해서 수행할 횟수이며, 두 번째 인자로는 반복해서 실행할 표현식을 입력하면 된다. 두 번째 인자의 표현식은 반드시 스칼라값을 반환하는 표현식이어야 한다. 즉 SELECT 쿼리를 BENCHMARK() 함수에 사용하는 것도 가능하지만, 반드시 스칼라값(하나의 컬럼을 가진 하나의 레코드)을 반환하는 SELECT 쿼리만 사용할 수 있다.

 BENCHMARK() 함수의 반환 값은 중요하지 않으며, 단지 지정한 횟수만큼 반복 실행하는 데 얼마나 시간이 소요됐는지가 중요할 뿐이다. 다음 예제를 보면 MD5() 함수를 100만 번 실행하는 데 1.945초의 시간이 소요된다는 것을 알 수 있다. 그리고 두 번째 예제는 salaries 테이블에서 건수만 세는 SQL 문장의 성능을 확인해 볼 수 있다.

MD5 100만번 실행 결과

SELECT BENCHMARK(10000000, (SELECT COUNT(*) FROM salaries));

BENCHMARK(10000000, MD5(SELECT COUNT(*) FROM salaries))|
+---------------------------------------+
|                                      0|
+---------------------------------------+
1 row in set (0.83 sec)

 하지만 이렇게 SQL 문장이나 표현식의 성능을 BENCHMARK() 함수로 확인할 때는 주의할 사항이 있다. 그것은 "SELECT BENCHMARK(10, expr)"와 "SELECT expr"을 10번 직접 실행하는 것과는 차이가 있다는 것이다. SQL 클라이언트와 같은 도구로 "SELECT expr"을 10번 실행하는 경우에는 매번 쿼리의 파싱이나 최적화, 테이블 잠금이나 네트워크 비용 등이 소요된다. 하지만 "SELECT BENCHMARK(10, expr)"로 실행하는 경우에는 벤치마크 횟수에 관계없이 단 1번의 네트워크, 쿼리 파싱 및  최적화 비용이 소요된다는 점을 고려해야 한다.

 또한 "SELECT BENCHMARK(10, expr)"을 사용하면 한 번의 요청으로 expr 표현식이 10번 실행되는 것이므로 이미 할당받은 메모리 자원까지 공유되고, 메모리 할당도 "SELECT expr" 쿼리로 직접 10번 실행하는 것보다는 1/10밖에 일어나지 않는다. 그래서 위의 예제에서는 생각보다 짧은 시간에 완료된 것이다. BENCHMARK() 함수로 얻은 쿼리나 함수의 성능은 그 자체로는 큰 의미가 없으며, 두 개의 동일 기능을 상대적으로 비교 분석하는 용도로 사용할 것을 권장한다.

 

SELECT

---

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

 WHERE 절아ㅣ나 ORDER BY 또는 GROUP BY가 인덱스를 사용하려면 기본적으로 인덱스된 컬럼의 값 자체를 변환하지 않고 그대로 사용한다는 조건을 만족해야 한다. 인덱스는 컬럼의 값을 아무런 변환 없이 B-Tree에 정렬해서 저장한다. WHERE 조건이나 GROUP BY에서도 원본값을 검색하거나 정렬할 때만 B-Tree에 정렬된 인덱스를 이용한다. 즉, 인덱스는 salary 컬럼으로 만들어져 있는데, 다음 예제의 WHERE 절과 같이 salary 컬럼을 가공한 후 다른 상숫값과 비교한다면 이 쿼리는 인덱스를 적절히 이용하지 못하게 된다.

SELECT & FROM salaries WHERE salary*10 > 150000;

 사실 이 쿼리는 간단히 다음과 같이 변경해서 salary 컬럼의 값을 변경하지 않고 검색하도록 유도할 수 있지만, MySQL 옵티마이저에서는 인덱스를 최적으로 이용할 수 있게 표현식을 변환하지는 못한다.

SELECT * FROM salaries WHERE salary > 150000/10;

 이러한 형태는 아주 단순한 예제이며, 복잡한 연산을 수행한다거나 MD5() 함수와 같이 해시 값을 만들어서 비교해야 하는 경우라면 미리 게산된 값을 저장하도록 MySQL의 가상 컬럼(Virtual Column)을 추가하고 그 컬럼에 인덱스를 생성하거나 함수 기반의 인덱스를 사용하면 된다. 결론적으로 인덱스 컬럼을 변형해서 비교하는 경우(그 변형이 아무리 간단한 연산이라고 하더라도)에는 인덱스를 이용할 수 없게 된다는 점에 주의하자.

WHERE 절의 인덱스 사용

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

WHERE 조건의 인덱스 사용 규칙

 그림의 위쪽은 4개의 컬럼이 순서대로 결합 인덱스로 생성돼 있는 것을 의미하며, 아래쪽은 SQL의 WHERE 절에 존재하는 조건을 의미한다. 그림에서 "WHERE 조건절의 순서"에 나열된 조건들의 순서는 실제 인덱스의 사용 여부와 무관하다. 즉, 그림과 같이 WHERE 조건절에 나열된 순서가 인덱스와 다르더라도 MySQL 서버 옵티마이저는 인덱스를 사용할 수 있는 조건들을 뽑아서 최적화를 수행할 수 있다. 그림에서 COL_1과 COL_2는 동등 비교 조건이며 COL_3의 조건은 동등 비교 조건이 아닌 크다 또는 작다와 같은 범위 비교 조건이므로 뒤 컬럼인 COL_4의 조건은 작업 범위 결정 조건으로 사용되지 못하고 체크 조건(점선 표기)으로 사용된다. 이는 WHERE 조건절과 인덱스의 컬럼 순서가 일치하지 않기 때문이 아니라 인덱스 순서상 COL_4의 직전 컬럼인 COL_3가 동등 비교 조건이 아니라 범위 비교 조건으로 사용됐기 때문이다.

 MySQL 8.0 이전 버전까지는 하나의 인덱스를 구성하는 각 컬럼의 정렬 순서가 혼합되어 사용할 수 없었다. 하지만 MySQL 8.0 버전부터는 다음 예제와 같이 인덱스를 구성하는 컬럼별로 정순(오름차순)과 역순(내림차순) 정렬을 혼합해서 생성할 수 있게 개선됐다.

ALTER TABLE ... ADD INDEX ix_col1234(col_1 ASC, col_2 DESC, col_3 ASC, col_4 ASC);

GROUP BY나 ORDER BY와는 달리 WHERE 절의 조건절은 순서를 변경해도 결과의 차이가 없기 때문에 WHERE 절에서의 각 조건이 명시된 순서는 중요치 않고 인덱스를 구성하는 컬럼에 대한 조건이 있는지 없는지가 중요하다.

다음과 같이 OR 연산자가 있으면 처리 방법이 완전히 바뀐다.

SELECT *
FROM employees
WHERE first_name='Kebin' OR last_name='Poly';

 위의 쿼리에서 first_name='Kebin' 조건은 인덱스를 이용할 수 있지만 last_name='Poly'는 인덱스를 사용할 수 없다. 이 두 조건이 AND 연산자로 연결됐다면 first_name의 인덱스를 이용하겠지만 OR 연산자로 연결됐기 때문에 옵티마이저는 풀 테이블 스캔을 선택할 수밖에 없다. (풀 테이블 스캔) + (인덱스 레인지 스캔)의 작업량보다는 (풀 테이블 스캔) 한 번이 더 빠르기 때문이다. 이 경우 first_name과 last_name 컬럼에 각각 인덱스가 있다면 index_merge 접근 방법으로 실행할 수 있다. 물론 이 방법은 풀 테이블 스캔보다는 빠르지만 여전히 제대로 된 인덱스 하나를 레인지 스캔하는 것보다는 느리다. WHERE 절에서 각 조건이 AND로 연결되면 읽어와야 할 레코드의 건수를 줄이는 역할을 하지만 각 조건이 OR로 연결되면 읽어서 비교해야 할 레코드가 더 늘어나기 때문에 WHERE 조건에 OR 연산자가 있다면 주의해야 한다.

GROUP BY 절의 인덱스 사용

 SQL에 GROUP BY가 사용되면 인덱스의 사용 여부는 어떻게 결정될까? GROUP BY 절의 각 컬럼은 비교 연산자를 가지지 않으므로 작업 범위 결정 조건이나 체크 조건과 같이 구분해서 생각할 필요는 없다. GROUP BY 절에 명시된 컬럼의 순서가 이덱스를 구성하는 컬럼의 순서와 같으면 GROUP BY 절은 일단 인덱스를 이용할 수 있다. 조금 풀어서 사용 조건을 정리해보면 다음과 같다. 여기서 설명하는 내용은 여러 개의 컬럼으로 구성된 다중 컬럼 인덱스를 기준으로 한다. 하지만 컬럼이 하나인 단일 컬럼 인덱스도 똑같이 적용된다.

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

GROUP BY 절의 인덱스 사용 규칙

 그림은 GROUP BY 절이 인덱스를 사용하기 위한 조건을 간단하게 보여준다. 그림의 위쪽은 (COL_1, COL_2, COL_3, COL_4)로 만들어진 인덱스를 의미하며, 아래쪽은 COL_1부터 COL_3을 순서대로 GROUP BY 절에 명시된 컬럼을 의미한다. 이 그림에서 GROUP BY 절과 인덱스를 구성하는 컬럼의 순서가 중요하므로 굵은 화살표로 방향 표시를 넣어둔 것이다. 다음에 예시된 GROUP BY 절은 모두 그림의 인덱스를 이용하지 못하는 경우다.

... 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 컬럼이 인덱스를 구성하는 컬럼의 순서와 일치하지 않기 때문에 사용하지 못하는 것이다.
• 세 번째 예제는 GROUP BY 절에 COL_3가 명시됐지만 COL_2가 그 앞에 명시되지 않았기 때문이다.
• 네 번째 예제에서는 GROUP BY 절의 마지막에 있는 COL_5가 인덱스에는 없어서 인덱스를 사용하지 못하는 것이다.

다음 예제는 GROUP BY 절이 인덱스를 사용할 수 있는 패턴이다. 다음 예제는 WHERE 조건 없이 단순히 GROUP BY만 사용된 형태의 쿼리다.

... 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이나 COL_2가 동등 비교 조건으로 사용된다면 GROUP BY 절에 COL_1이나 COL_2가 빠져도 인덱스를 이용한 GROUP BY가 가능할 때도 있다. 다음 예제는 인덱스의 인덱스의 앞쪽에 있는 컬럼을 WHERE 절에서 상수로 비교하기 때문에 GROUP BY 절에 해당 컬럼이 명시되지 않아도 인덱스를 이용한 그루핑이 가능한 예제다.

... WHERE COL_1='상수' ... GROUP BY COL_2, COL_3
... WHERE COL_1='상수' AND COL_2='상수' ... GROUP BY COL_3, COL_4
... WHERE COL_1='상수' AND COL_2='상수' AND COL_3='상수' ... GROUP BY COL_4

 위 예제와 같이 WHERE 절과 GROUP BY 절이 혼용된 쿼리가 인덱스를 이용해 WHERE 절과 GROUP BY 절이 모두 처리도리 수 있는지는 다음 예제와 같이 WHERE 조건절에서 동등 비교 조건으로 사용된 컬럼을 GROUP BY 절로 옮겨보면 된다.

-- 원본 쿼리
... WHERE COL_1 = '상수' ... GROUP BY COL_2, COL_3

-- WHERE 조건절의 COL_1 컬럼을 GROUP BY 절의 앞쪽으로 포함시켜 본 쿼리
... WHERE COL_1 = '상수' ... GROUP BY COL_1, COL_2, COL_3

 위의 예제에서 COL_1은 상숫값과 비교되므로 "GROUP BY COL_2, COL_3"는 "GROUP BY COL_1, COL_2, COL_3"와 똑같은 결과를 만들어 낸다. 이처럼 GROUP BY 절을 고쳐도 똑같은 결과가 조회된다면 WHERE 절과 GROUP BY 절이 모두 인덱스를 사용할 수 있는 쿼리로 판단하면 된다.

ORDER BY 절의 인덱스 사용

 MySQL에서 GROUP BY와 ORDER BY는 처리 방법이 상당히 비슷하다. 그래서 ORDER BY 절의 인덱스 사용 여부는 GROUP BY의 요건과 거의 흡사하다. 하지만 ORDER BY는 조건이 하나 더 있는데, 정렬되는 각 컬럼의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 정반대인 경우에만 사용할 수 있다는 것이다. 여기서 MySQL의 인덱스는 모든 컬럼이 오름차순으로만 정렬돼 있기 때문에 ORDER BY 절의 모든 컬럼이 오름차순이거나 내림차순일 때만 인덱스를 사용할 수 있다. 인덱스의 모든 컬럼이 ORDER BY 절에 사용돼야 하는 것은 아니지만, ORDER BY 절의 컬럼들이 인덱스에 정의된 컬럼의 왼쪽부터 일치해야하는 것에는 변함이 없다. 그림은 ORDER BY 절이 인덱스를 이용하기 위한 요건을 보여준다.

ORDER BY 절의 인덱스 사용 규칙

그림과 같은 인덱스에서 다음 예제의 ORDER BY 절은 인덱스를 이용할 수 없다. 참고로 ORDER BY 절에 ASC나 DESC와 같이 정렬 순서가 생략되면 오름차순(ASC)으로 해석한다.

... ORDER BY COL_2, COL_3
... ORDER BY COL_1, COL_3, COL_2
... ORDER BY COL_1, COL_2 DESC, COL_3
... ORDER BY COL_1, COL_3
... ORDER BY COL_1, COL_2, COL_3, COL_4, COL_5

위의 각 예제가 인덱스를 사용하지 못하는 원인을 살펴보자.

• 첫 번째 예제는 인덱스의 제일 앞쪽 컬럼인 COL_1이 ORDER BY 절에 명시되지 않았기 때문에 인덱스를 사용할 수 없다.
• 두 번째 예제는 인덱스와 ORDER BY 절의 컬럼 순서가 일치하지 않기 때문에 인덱스를 사용할 수 없다.
• 세 번째 예제는 ORDER BY 절의 다른 컬럼은 모두 오름차순인데, 두 번째 컬럼인 COL_2의 정렬 순서가 내림차순이라서 인덱스를 사용할 수 없다. 인덱스가 "(COL_1 ASC, COL_2 DESC, COL_3 ASC, COL_4 ASC)"와 같이 정의됐다면 이 정렬은 인덱스를 사용할 수 있게 된다.
• 네 번째 예제는 인덱스에는 COL_1과 COL_3 사이에 COL_2 컬럼이 있지만 ORDER BY 절에는 COL_2 컬럼이 명시되지 않았기 때문에 인덱스를 사용할 수 없다.
• 다섯 번째 예제는 인덱스에 존재하지 않는 COL_5가 ORDER BY 절에 명시됐기 때문에 인덱스를 사용하지 못한다.

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

GROUP BY와 ORDER BY 절이 동시에 사용된 쿼리에서 두 절이 모두 하나의 인덱스를 사용해서 처리되려면 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, col3

MySQL 5.7 버전까지는 GROUP BY는 GROUP BY 컬럼에 대한 정렬까지 함께 수행하는 것이 기본 작동 방식이었다. 하지만 MySQL 8.0 버전부터는 GROUP BY 절이 컬럼의 정렬까지는 보장하지 않는 형태로 바뀌었다. 그래서 MySQL 8.0 버전부터는 GROUP BY 컬럼으로 그루핑과 정렬을 모두 수행하기 위해서는 GROUP BY 절과 ORDER BY 절을 명시해야 한다.

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

문자열이나 숫자 비교

 문자열 컬럼이나 숫자 컬럼을 비교할 때는 반드시 그 타입에 맞는 상숫값을 사용할 것을 권장한다. 즉 비교 대상 컬럼이 문자열 컬럼이라면 문자열 리터럴을 사용하고, 숫자 타입이라면 숫자 리터럴을 이요하는 규칙만 지켜주면 된다.

SELECT * FROM employees WHERE emp_no=10001;
SELECT * FROM employees WHERE first_name='Smith';
SELECT * FROM employees WHERE emp_no='10001';
SELECT * FROM employees WHERE first_name=10001;

 첫 번째와 두 번째 쿼리는 적절히 타입을 맞춰서 비교를 수행했지만, 세 번째와 네 번째 쿼리는 컬럼의 타입과 비교 상수의 타입이 일치하지 않는 WHERE 조건이 포함돼 있다. 위 예제의 쿼리가 어떻게 실행되는지 쿼리별로 한번 살펴보자.

• 첫 번째와 두 번째 쿼리는 컬럼의 타입과 비교하는 상숫값이 동일한 타입으로 사용됐기 때문에 인덱스를 적절히 이용할 수 있다.
• 세 번째의 쿼리는 emp_no 컬럼이 숫자 타입이기 떄문에 문자열 상숫값을 숫자로 타입 변환해서 비교를 수행하므로 특별히 성능 저하는 발생하지 않는다.
• 네 번째 쿼리는 first_name이 문자열 컬럼이지만 비교되는 상숫값이 숫자 타입이므로 옵티마이저는 우선순위를 가지는 숫자 타입으로 비교를 수행하려고 실행 계획을 수립한다. 그래서 first_name 컬럼의 문자열을 숫자로 변환해서 비교를 수행한다. 하지만 first_name 컬럼의 타입 변환이 필요하기 때문에 ix_firstname 인덱스를 사용하지 못한다.

Short-Circuit Evaluation

 프로그램 개발 경험이 있다면 "Short-circuit Evaluation"이라는 말을 들어본 적이 있을 것이다. 간단히 다음 의사 코드를 이용해 "Short-circuit Evaluation"의 작동 방식을 살펴보자.

boolean in_transaction;

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

 위의 예제 코드에서는 in_transaction 불리언 변수의 값이 TRUE이면 has_midified() 함수를 호출하고, 그 결괏값이 TURE라면 commit() 함수가 실행될 것이다. 그런데 in_transaction 불리언 변수의 값이 FALSE라면 has_modified() 결괏값에 관계없이 commit() 함수는 호출되지 않는다. 그래서 많은 프로그래밍 언어에서는 빠른 성능을 위해 in_transaction 불리언 변수값이 FALSE라면 has_modified() 함수를 호출도 하지 않고 다음 코드를 실행한다. 이처럼 여러 개의 표현식이 AND 또는 OR 논리 연산자로 연결된 경우 선행 표현식의 결과에 따라 후행 표현식을 평가할지 말지 결정하는 최적화를 "Short-circuit Evaluation"이라고 한다.

 그럼 이제 MySQL 서버에서 "Short-circuit Evaluation"이 어떻게 쿼리의 성능에 영향을 미치는지 한번 살펴보자. 다음 쿼리는 salaries 테이블에서 2개의 조건을 모두 만족하는 레코드를 조회하는 쿼리다. 이해를 돕기 위해서 2개의 조건이 각각 별도로 사용됐을 때 일치하는 레코드의 건수도 함께 확인한다.

-- salaries 테이블의 전체 레코드 건수
SELECT COUNT(*) FROM salaries;
+----------+
| COUNT(*) |
| 2844047  |
+----------+

-- 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번 조건 결합
-- 1번 조건을 만족하는 레코드 건수
SELECT COUNT(*) FROM salaries
WHERE CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01' /* 1번 조건 */
	AND to_date < '1985-01-01';                            /* 2번 조건 */

 위의 예제 쿼리에서 사용된 두 개의 조건은 모두 인덱스를 사용하지 못하기 때문에 이 쿼리는 풀 테이블 스캔을 하게 된다. 그리고 1번과 2번 조건을 AND로 연결한 3번째 쿼리의 결과는 0건이 될 것이다. 이런 형태의 쿼리에서 WHERE 절에 나열되는 조건의 순서가 쿼리의 성능에 영향을 미칠 것이라는 해본 사용자는 많지 않을 것이다. 이제 WHERE 절에서 1번 조건과 2번 조건의 순서만 바꿔가면서 쿼리 성능을 한번 확인해보자.

SELECT COUNT(*) FROM salaries
WHERE CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01' /* 1번 조건 */
	AND to_date < '1985-01-01';                            /* 2번 조건 */
-- 결과 : 0.73 sec

SELECT COUNT(*) FROM salaries
WHERE to_date < '1985-01-01'                                          /* 1번 조건 */
	AND CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01'; /* 2번 조건 */                           /* 2번 조건 */
-- 결과 : 0.52 sec

 WHERE 절에 1번 조건을 먼저 사용한 쿼리의 응답 시간은 0.73초인 반면, 2번 조건을 먼저 사용한 쿼리의 응답 시간은 0.52초다. 사실 별 차이가 아니라고 생각할 수도 있지만, 1번 조건이 더 많은 CPU를 사용하거나 더 많은 자우너을 소모하는 조건이었다면 두 쿼리의 시간 차이는 훨씬 더 커졌을 것이다.

 WHERE 절에 1번 조건이 먼저 사용된 쿼리의 경우 MySQL 서버는 salaries 테이블 전체 레코드에 대해 CONVERT_TZ(from_date, ...) 함수를 실행하고 그 결과에 대해 to_date 컬럼의 비교 작업을 한다. 즉, CONVERT_TZ() 함수가 2844047번 실행되고 to_date 컬럼 비교 작업이 2442943번 실행돼야 한다. 하지만 WHERE 절에 2번 조건이 먼저 사용된 쿼리의 경우 salaries 테이블에서 "to_date<'1985-01-01'" 조건을 만족하는 레코드가 한 건도 없기 때문에 to_date 컬럼의 비교 작업만 284407번 실행하면 되고, CONVERT_TZ() 함수는 한 번도 호출되지 않는다. 이로 인해 두 번째 쿼리의 성능이 30% 정도 빨라진 것이다.

 MySQL 서버는 쿼리의 WHERE 절에 나열된 조건을 순서대로 "Short-circuit Evaluation" 방식으로 평가해서 해당 레코드를 반환해야 할지 말지를 결정한다. 그런데 WHERE 절의 조건 중에서 인덱스를 사용 할 수 있는 조건이 있다면 "Short-circuit Evaluation"과는 무관하게 MySQL 서버는 그 조건을 가장 최우선으로 사용한다. 그래서 WHERE 조건절에 나열된 조건의 순서가 인덱스의 사용 여부를 결정하지는 않는다. 예를 들어, 다음 예제를 한번 살펴보자.

SELECT * FROM employees
WHERE last_name = 'Aamodt'
AND first_name = 'Matt';

 위의 쿼리에서 last_name 컬럼 조건은 인덱스를 사용할 수 없지만 first_name 컬럼 조건은 인덱스를 효율적으로 사용할 수 있다. 이러한 경우 WHERE 절에 나열된 조건의 순서와 무관하게 MySQL 서버는 인덱스를 사용할 수 있는 조건을 먼저 평가한다. 그래야만 MySQL 서버는 employees 테이블의 ix_firstname (first_name) 인덱스를 이용해서 꼭 필요한 레코드만 빠르게 가져올 수 있다. 그러고 나서 WHERE 절에 first_name 조건을 제외한 나머지 조건을 순서대로 평가한다. WHERE 조건절에 다음과 같이 인덱스를 사용하지 못하는 또 다른 조건이 있었다면 MySQL 서버는 first_name 조건을 평가하고 그다음 last_namer 조건, 그리고 마지막으로 birth_date 조건을 평가하는 순서를 사용한다.

OUTER JOIN의 성능과 주의사항

 이너 조인(INNER JOIN)은 조인 대상 테이블에 모두 존재하는 레코드만 결과 집합으로 반환한다. 이너 조인의 이 같은 특성 떄문에 아우터 조인(OUTER JOIN)으로만 조인을 실행하는 쿼리들도 자주 보인다. 다음 예제 쿼리는 3개 테이블을 조인하면서 LEFT JOIN만 사용하고 있다.

SELECT *
FROM employees e
LEFT JOIN dept_emp de ON de.emp_no = e.emp_no
LEFT JOIN departments d On d.dept_no = de.dept_no AND d.dept_name='Development';

 이 쿼리의 실행 계획을 보면 다음과 같이 제일 먼저 employees 테이블을 풀 스캔하면서 dept_emp 테이블과 departments 테이블을 드리븐 테이블로 사용한다는 것을 알 수 있다.

+---+-------+--------+--------------------+--------+-------------+
|id | table | type   | key                | rows   | Extra       |
| 1 | e     | ALL    | NULL               | 299920 | NULL        |
| 1 | de    | ref    | ix_empno_fromdate  |     1  | NULL        |
| 1 | d     | eq_ref | PRIMARY            |     1  | Using where |
+---+-------+--------+--------------------+--------+-------------+

 employees 테이블에 존재하는 사원 중에서 dept_emp 테이블에 레코드를 갖지 않는 경우가 있다면 아우터 조인이 필요하지만, 대부분 그런 경우는 없으므로 굳이 아우터 조인을 사용할 필요가 없다. 즉 테이블의 데이터가 일관되지 않은 경우에만 아우터 조인이 필요한 경우인 것이다. MySQL 옵티마이저는 절대 아우터로 조인되는 테이블을 드라이빙 테이블로 선택하지 못하기 때문에 풀 스캔이 필요한 employees 테이블을 드라이빙 테이블로 선택한다. 그 결과 쿼리의 성능이 떨어지는 실행 계획을 수립한 것이다.

 이 쿼리에 이너 조인을 이용했다면 다음과 같이 departments 테이블에서 부서명이 "Development"인 레코드 1건만 찾아서 조인을 실행하는 실행 계획을 선택했을 것이다.

+---+-------+--------+--------------------+--------+-------------+
|id | table | type   | key                | rows   | Extra       |
| 1 | d     | ref    | ux_deparname       |     1  | Using index |
| 1 | de    | ref    | PRIMARY            | 41392  | NULL        |
| 1 | e     | eq_ref | PRIMARY            |     1  | NULL        |
+---+-------+--------+--------------------+--------+-------------+

 이너 조인으로 사용해도 되는 쿼리를 아우터 조인으로 작성하면 MySQL 옵티마이저가 조인 순서를 변경하면서 수행할 수 있는 최적화의 기회를 빼앗아버리는 결과가 된다. 필요한 데이터와 조인되는 테이블 간의 관계를 정확히 파악해서 꼭 필요한 경우가 아니라면 이너 조인을 사용하는 것이 업무 요건을 정확히 구현함과 동시에 쿼리의 성능도 향상시킬 수 있다.

 아우터 조인(OUTER JOIN) 쿼리를 작성하면서 많이 하는 또 다른 실수는 다음 예제와 같이 아우터(OUTER)로 조인되는 테이블에 대한 조건을 WHERE 절에 함께 명시하는 것이다.

SELECT *
FROM employees e
LEFT JOIN dept_manager mgr ON mgr.emp_no = e.emp_no
WHERE mgr.dept_no = 'd001';

 ON 절에 조인 조건은 명시했지만 아우터로 조인되는 테이블인 dept_manager의 dept_no='d001' 조건을 WHERE 절에 명시한 것은 잘못된 조인 방법이다. 위의 LEFT JOIN이 사용된 쿼리는 WHERE 절의 조건 때문에 MySQL 옵티마이저가 LEFT JOIN을 다음 쿼리와 같이 INNER JOIN으로 변환해서 실행해버린다.

SELECT *
FROM employees e
INNER JOIN dept_manager mgr ON mgr.emp_no = e.emp_no
WHERE mgr.dept_no = 'd001';

 정상적인 아우터 조인이 되게 만들려면 다음 쿼리와 같이 WHERE 절의 "mgr.dept_no='d001'" 조건을 LEFT JOIN의 ON 절로 옮겨야 한다.

SELECT *
FROM employees e
LEFT JOIN dept_manager mgr ON mgr.emp_no = e.emp_no AND mgr.dept_no = 'd001';

 예외적으로 OUTER JOIN으로 연결되는 테이블의 컬럼에 대한 조건을 WHERE 절에 사용해야 하는 경우가 있는데, 다음과 같이 안티 조인(ANTI-JOIN) 효과를 기대하는 경우가 그렇다.

SELECT *
FROM employees e
LEFT JOIN dept_manager dm ON dm.emp_no = e.emp_no
WHERE dm.emp_no IS NULL
LIMIT 10;

 위 쿼리는 사원 중에서 매니저가 아닌 사용자들만 조회하는 쿼리인데, WHERE 절에 아우터로 조인된 dept_manager 테이블의 emp_no 컬럼이 NULL인 레코드들만 조회한다. 이런 형태의 요건이 아우터 테이블의 컬럼이 WHERE 절에 사용될 수 있는 유일한 경우다. 그 외의 경우 MySQL 서버는 LEFT JOIN을 INNER JOIN으로 자동 변환한다는 것을 꼭 기억하.

ORDER BY

ORDER BY는 검색된 레코드를 어떤 순서로 정렬할지 결정한다. ORDER BY 절이 사용되지 않으면 SELECT 쿼리의 결과는 어떤 순서로 정렬될까?

• 인덱스를 사용한 SELECT의 경우에는 인덱스에 정렬된 순서대로 레코드를 가져온다.
• 인덱스를 사용하지 못하고 풀 테이블 스캔을 실행하는 SELECT를 가정해보자. MyISAM 테이블은 테이블에 저장된 순서대로 가져오는데, 이 순서가 정확히 INSERT된 순서는 아닐 수도 있다. 일반적으로 테이블의 레코드가 삭제되면서 빈 공간이 생기고, INSERT되는 레코드는 항상 테이블의 마지막이 아니라 빈 공간이 있으면 그 빈 공간에 저장되기 때문이다. InnoDB의 경우에는 항상 프라이머리 키로 클러스터링돼 있기 때문에 풀 테이블 스캔의 경우에는 기본적으로 프라이머리 키 순서대로 레코드를 가져온다.
• SELECT 쿼리가 임시 테이블을 거쳐 처리되면 조회되는 레코드의 순서를 예측하기 어렵다.

ORDER BY 절이 없는 SELECT 쿼리 결과의 순서는 처리 절차에 따라 달라질 수 있다. 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리에 대해서는 어떠한 정렬도 보장하지 않는다. 예를 들어, 인덱스를 사용한 SELECT 쿼리이기 때문에 ORDER BY 절을 사용하지 않아도 된다는 것은 잘못된 생각이. 항상 정렬이 필요한 곳에서는 ORDER BY 절을 사용해야 한다.

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