본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
ORDER BY
ORDER BY 절이 사용되지 않는 SELECT 쿼리의 결과의 정렬순서는 다음과 같다.
- 인덱스를 사용한 SELECT의 경우에는 인덱스의 정렬된 순서대로 레코드를 가져온다.
- 인덱스를 사용하지 못하고 풀 테이블 스캔을 실행하는 SELECT의 경우, MyISAM은 테이블 저장된 순서대로 가져오는데, 순서가 INSERT 순서를 이믜하는 것은 아니다. 레코드가 삭제되면서 빈 공간이 생기면 INSERT 되는 레코드는 항상 테이블의 마지막이 아니라 빈 공간 이있으면 빈 공간에 저장되기 때문이다. InnoDB의 경우 항상 프라이머리 키로 클러스터링 돼어 있기 때문에 풀 테이블 스캔의 경우 기본적으로 프라이머리 키 순서대로 레코드를 가져온다.
- SELECT 쿼리가 임시 테이블을 거쳐서 처리되면 조회되는 레코드의 순서는 예측하기는 어렵다.
ORDER BY 절이 없는 SELECT 쿼리 결과의 순서는 처리 절차에 따라 달라질 수 있다. 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리에 대해서는 어떠한 정렬도 보장하지 않는다.
ORDER BY에서 인덱스를 사용하지 못할 대는 추가적인 정렬 작업을 수행하고, 쿼리 실행 계획이 있는 Extra 컬럼에 Using filesort 라는 코멘트가 표시된다. Filesort라는 단어에 포함된 File은 디스크의 파일을 이용해 정렬을 수행한다는 의미가 아니라 쿼리를 수행하는 도중에 MySQL 서버가 퀵 소트 정렬 알고리즘을 수행했다는 의미 정도로 이해하면 된다. 정렬 대상이 많은 경우 여러 부분으로 나눠서 처리하는데, 정렬된 결과를 임시로 디스크나 메모리에 저장해둔다. 실제로 메모리만 이용해 정렬이 수행됐는지 디스크의 파일을 이용했는지는 알 수 없다.
ORDER BY 사용법 및 주의사항
ORDER BY 절은 1개 또는 그 이상 여러 개의 칼럼으로 정렬할 수 있으며, 정렬 순서는 칼럼마다 다르게 명시할 수 있다. 정렬할 대상은 칼럼명이나 표현식으로 명시하지만 SELECT 되는 칼럼의 순번을 명시할 수도 있다. 즉 "ORDER BY 2"라고 명시하면 SELECT 되는 칼럼들 중에서 2번째 칼럼으로 정렬한다는 의미이다.
SELECT first_name, last_name
FROM employees
ORDER BY "last_name";
ORDER BY 뒤에 숫자 값이 아닌 문자열 상수를 사용하는 경우 옵티마이저가 ORDER BY 절 자체를 무시한다. 상수값으로 정렬을 수행하는 것은 아무런 의미가 없으므로 옵티마이저는 이렇게 문자 리터럴이 ORDER BY 절에 사용되면 모두 무시해버린다.
ORDER BY RAND()
이벤트 성격의 단순 추첨 또는 임의의 사용자 조회 같은 기능을 SQL을 이용해 처리할 때가 있다. 가장 쉽게 사용할 수 있는 방법이 ORDER BY RAND()다. ORDER BY RAND()는 RAND() 함수로 발생되는 임의의 값을 각 레코드별로 부여하고, 그 임의값으로 정렬을 수행한다. 아주 간단하게 랜덤하게 레코드를 가져올 수 있기 때문에 ORDER BY RAND()가 자주 사용되곤 한다.
ORDER BY RAND()를 이용한 임의 정렬이나 조회는 절대 인덱스를 이용할 수 없다. 정렬해야 할 레코드가 적으면 문제가 되지 않는다. 하지만 대량의 레코드를 대상으로 임의 정렬해야 할 때 문제가 될 수 있다. 인덱스는 변수가 아닌 정해진 값을 순서대로 정렬해서 가지고 있기 때문에 인덱스를 이용한 임의 정렬은 구현할 수 없을 것이다. 반대로 임의의 값을 별도의 칼럼으로 생성해 두고 그 칼럼에 인덱스를 생성하면 손쉽게 인덱스를 이용한 임의 정렬을 구현할 수 있다.
여러 방향으로 동시 정렬
여러 개의 칼럼을 조합해서 정렬할 때 각 칼럼의 정렬 순서가 오름차순과 내림차순이 혼용되면 인덱스를 이용할 수 업삳. ASC와 DESC가 혼용된 정렬이 엔덱스를 사용하게 하려면 칼럼의 값 자체를 변형시켜 테이블에 저장하는 것이 유일한 해결책이다. 문자열 타입은 아직까지 별다른 방법이 없지만 숫자 타입이나 날짜 타입은 다음과 같이 변경해서 저장하면 된다.
- 숫자 타입의 값은 반대 부호로 변환해서 칼럼에 저장한다.
- 날짜 타입의 값은 그 자체로 음수값을 가질수 없다. 우선 DATETIME이나 DATE 타입의 값을 타임스탬프 타입으로 변환하면 정수 타입으로 변환할 수 있다. 이 값의 부호를 음수로 만들어서 저장한다.
다음 예제는 나이는 내림차순으로, 지역은 오름차순으로 해서 회원 정보를 저장하기 위해 일부러 테이블의 age 칼럼을 음수로 변화해서 저장하고 있다.
위의 쿼리는 ORDER By 절의 두 칼럼이 모두 오름차순으로 정렬되기 때문에 tb_member 테이블이 ix_region_age 인덱스가 정렬된 순서와 동일하다. MySQL 서버는 별도의 정렬 작업을 수행하지 않고 인덱스만 읽기 때문에 빠르게 처리할 수 있다.
MySQL의 정렬에서 NULL은 항상 최소의 값으로 간주하고 정렬을 수행한다. 오름차순 정렬인 경우 NULL은 항상 제일 먼저 반환되며, 내림차순인 경우에는 제일 마지막에 반환된다. NULL에 대한 정렬 순서를 변경하려면 함수를 사용해서 값을 변형해야 한다. 하지만 인덱스를 이용한 정렬을 사용하지 못하게 할 수도 있으므로 주의해야 한다.
함수나 표현식을 이용한 정렬
하나 또는 여러 칼럼의 연산 결과를 이용해 정렬하는 것도 가능하다. 연산 결과에 의한 정렬은 인덱스를 사용할 수 없기 때문에 가능하다면 피하는 것이 좋다.
SELECT * FROM employees ORDER BY emp_no;
SELECT * FROM employees ORDER BY emp_no+10;
예제 첫 번재 쿼리는 emplyoees 테이블의 프라이머리 키인 emp_no를 이용해 정렬을 수행하기 때문에 프라이머리 키 순서대로 읽기만 하면 되지만, 두번째는 쿼리는 동일한 순서를 만들어내는 쿼리임에도 옵티마이저는 이를 최적화 하지 못하고 별도의 정렬 작업을 수행하게 도니다.
ORDER BY 절에 인덱스에 명시된 칼럼의 값을 조금이다로 변형(연산)시켜서 정렬을 수행하면 인덱스를 이용한 정렬이 불가능해진다는 점에 주의해야한다.
표현식의 결과 순서가 칼럼의 원본 값 순서와 동일할 때
위 예제와 같이 ORDER BY 절의 표현식에 의해ㅣ 변형된 값 자체가 이전의 값과 순서가 동일하다면 변형되지 않은 칼럼을 그대로 사용해 주는 것이 인덱스를 이용한 정렬을 사용하는 유일한 방법이다.
표현식의 정렬 순서가 칼럼의 원본 값과 다른 경우(연산의 결과가 칼럼의 값에만 의존적인 경우)
미리 표현식 연산 결과를 위한 별도의 칼럼을 추가해서 레코드가 INSERT 되거나 UPDATE될 때 해당 칼럼을 계속 업데이트 하는 방식이 최선이다. MySQL은 함수를 이용한 인덱스가 없기 때문에 표현식의 결과를 저장하는 별도의 칼럼을 생성해야 한다. 그리고 그 칼럼에 인덱스를 생성하고 표현식의 정렬이 필요할 때는 이미 연산 결과값이 저장된 칼럼으로 ORDER BY를 사용하는 것이다.
회원 테이블에서 나이가 30살을 기준으로 가까운 나이 순서대로 정렬하는 상황을 생각해보자.
SELECT * FROM tb_member ORDER BY ABS(member_age - 30);
위 쿼리문장에서 member_age 칼럼에 인덱스가 준비돼 있더라도 별도의 정렬(Filesort)를 거쳐야 한다. 하지만 ABS(member_age -30) 표현식의 결과는 meber_age 칼럼값에만 의존적이다. 다음과 같이 연산의 결과를 저장하는 member_age_diff 칼럼에 인덱스를 생성하고, 정렬을 수행하면 된다.
표현식의 정렬 순서가 칼럼의 원본 값과 다른 경우(연산의 결과가 칼럼 이외의 값에 의존적인 경우)
이러한 경우에는 어떠한 방식을 사용해도 인덱스를 이용해 정렬할 수 없다. 이번에는 기준 연령이 30이 아닌 가변적인 경우의 쿼리를 가정해보자. 이럴 때는 해당 레코드에 포함된 칼럼 이외의 변수가 표현식에 사용되어 레코드가 INSERT 되거나 UPDATE 될 때 미리 연산을 해두는 것 자체가 불가능하다.
SELECT * FROM tb_member ORDER BY ABS(member_age - ?);
인덱스를 이용한 정렬이 불가능하기 때문에 ORDER BY 자체를 튜닝하기가 어렵다. 결국 WHERE 절의 조건을 최적화해서 정렬해야 할 레코드의 건수를 최대한 줄이는 형태로 튜닝하는 것이 좋다. 인덱스를 이용해 정렬할 수 없을 때는 쿼리가 가져오는 값의 크기가 크면 클수를 정렬하는 데 더 많은 메모리가 필요하기 때문에 SELECT 되는 칼럼을 최소화 하는 것이 좋다.
