쿼리 작성 및 최적화

1. MySQL 연산자와 내장 함수

Type 비교

MySQL은 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비교를 수행한다.

MySQL에서는 정해진 형태의 날짜 포맷으로 표기하면 MySQL서버가 자동으로 DATE나 DATETIME 값으로 변환한다.

 

BETWEEN, IN, NOT IN

• BETWEEN : BETWEEN이 선형으로 인덱스를 검색해야 하는 것과 달리 IN은 동등 비교를 여러번 수행하는 것과 같은 효과가 있기 때문에 여러 column의 인덱스를 최적으로 사용할 수 있다.
• IN : IN 연산자에 상수값을 입력으로 전달하는 경우는 다른 DBMS만큼 최적화해서 수행할 수 있다. 하지만 IN 연산자의 입력이 상수가 아니라 서브쿼리인 경우에는 상당히 느려질 수 있다.
• NOT IN : NOT IN의 실행계획은 인덱스 풀 스캔으로 표시되는데, 동등이 아닌 부정형 비교라서 인덱스를 이용해 처리 범위를 줄이는 조건으로 사용할 수 없기 때문이다.

 

내장 함수

MySQL의 함수는 MySQL에서 기본적으로 제공하는 내장함수와 사용자가 직접 작성해서 추가할 수 있는 사용자 정의 함수로 구분된다.

• NOW() : 쿼리가 실행되는 시점에 실행되고 값을 할당받아 그 값을 SQL 문장의 모든 부분에서 사용하게 한다.
• CONCAT() : 여러 개의 문자열을 하나의 문자열로 변환하는 함수
• RPAD(), LPAD() : 문자열의 좌측 또는 우측에 문자를 덧붙여 지정된 길이의 문자열로 만듬
• GROUP_CONCAT() : 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정, 중복제거 연결 등을 할 수 있는 함수
• CASE WHEN THEN END : if else 느낌, 조건 설정 가능

 

2. WHERE 절에서 인덱스 태우기

• 컬럼 값 자체를 변환하지 않고 그대로 사용
• 비교 조건에서 양쪽의 데이터 타입이 일치해야 함
• OR 연산이 있으면 주의 → 인덱스 한번 + 풀 테이블 한번 하느니 차라리 풀 테이블 한번 하는 것이 나음
• 범위 조건이 있으면 그 뒤의 column은 인덱스를 쓰지 못함
• MySQL에서는 NULL값이 포함된 레코드도 인덱스로 관리됨

 

3. GROUP BY 인덱스 태우기

• GROUP BY 절에 명시된 column이 인덱스 column의 순서와 위치가 같아야 함
• 인덱스를 구성하는 column 중에서 뒷쪽에 있는 column은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 column이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없음
• GROUP BY 절에 명시된 column이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 사용하지 못함

 

4. ORDER BY 인덱스 태우기

ORDER BY 절의 인덱스 사용 여부는 GROUP BY 요건과 거의 흡사하다. 하지만 정렬되는 각 column의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 또는 정반대인 경우에만 사용할 수 있음

 

5. DISTINCT

집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행계획에서는 DISTINCT가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 있어야 한다. 하지만 실행 계획의 Extra 칼럼에는 ‘Using temporary’메시지가 출력되지 않는다.

SELECT DISTINCT

집합함수와 함께 사용되지 않는 DISTINCT는 GROUP BY와 거의 같은 방식으로 처리된다. 단지 차이는 SELECT DISTINCT의 경우 정렬이 보장되지 않는 것 뿐이다.

집합함수와 함께 사용되는 DISTINCT

집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 column 값들 중에서 중복을 제거하고 남은 값만을 가져온다.

 

6. LIMIT

• MySQL의 LIMIT은 항상 쿼리의 가장 마지막에 실행된다.
• LIMIT의 중요한 특성은 LIMIT에서 필요한 레코드 건수만 준비되면 바로 쿼리를 종료시킨다는 것이다.
• LIMIT 0이 사용되면 MySQL 옵티마이저는 쿼리를 실행하지 않고 최적화만 실행한다.

 

7. JOIN 쿼리 튜닝

7-1. 조인과 인덱스 레인지 스캔

일반적으로 인덱스 풀 스캔이나 테이블 풀 스캔 작업은 인덱스 탐색 과정이 거의 없지만 실제 인덱스나 테이블의 모든 레코드를 읽기 때문에 부하가 높다. 하지만 인덱스 레인지 스캔 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔 과정은 부하가 작지만 특정 인덱스 키를 찾는 인덱스 탐색과정이 상대적으로 부하가 높은 편이다.

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

 

7-2. JOIN 주의 사항

데이터 타입이 같은지 확인을 잘 해야 한다. 같은 CHAR 타입이라도 문자집합이나 콜레이션이 다른 경우 문제가 될 수 있다. 가능하면 DB 모델에 대한 표준화 규칙을 수립하고, 규칙을 기반으로 설계를 진행한다면 이런 문제를 최소화할 수 있을 것이다.

조인이 수행되는 칼럼들끼리는 데이터 타입을 일치시키기 위해 최종 점검을 하는 것이 좋다.

 

7-3. OUTER JOIN 주의 사항

OUTER로 조인되는 테이블의 칼럼에 대한 조건은 모두 ON 절에 명시해야 한다.

LEFT JOIN이 사용된 쿼리는 WHERE절의 조건 때문에 MySQL 옵티마이저가 LEFT JOIN을 INNER JOIN으로 변환해버린다.

 

7-4. 지연된 조인

조인이 실행되기 이전에 GROUP BY, ORDER BY를 처리하는 방식으로 조인을 사용해서 데이터를 조회하는 쿼리에 GROUP BY 또는 ORDER BY를 사용할 때 각 처리 방법에서 인덱스를 사용한다면 이미 최적으로 처리되고 있을 가능성이 높다.

하지만 그러지 못하면 MySQL 서버는 조인이 모든 조인을 실행하고 난 다음 GROUP BY나 ORDER BY를 실행하게 된다.

파생 테이블에 저장돼야 할 레코드의 건수가 적으면 적을수록 지연된 조인의 효과가 커진다. 따라서 쿼리에 GROUP BY나 DISTINCT 등과 LIMIT절이 함께 사용된 쿼리에서 상당히 효과적이다.

 

8. GROUP BY

MySQL의 GROUP BY는 정렬 작업까지 수행한다. 이런 정렬 작업 때문에 GROUP BY가 많이 느려지는데, GROUP BY에서 정렬은 하지 않도록 ‘ORDER BY NULL’을 추가할 수 있다.

GROUP BY가 사용된 쿼리에서는 그룹핑된 그룹별로 소계를 가져올 수 있는 롤업 기능을 사용할 수 있다.

 

9. ORDER BY

ORDER BY 절이 없는 SELECT 쿼리 결과의 순서는 처리 결과에 따라 달라질 수 있다. 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리에 대해서는 어떠한 정렬도 보장하지 않는다.

ORDER BY RAND()를 이용한 임의 정렬이나 조회는 절대 인덱스를 이용할 수 없다.

MySQL 정렬에서 NULL은 항상 최소의 값으로 간주하고 정렬을 수행한다. 오름차순 정렬인 경우 NULL은 항상 제일 먼저 반환되며, 내림차순인 경우에는 제일 마지막에 반환된다.

 

10. 서브쿼리

10-1. 서브쿼리의 제약 사항

• 서브 쿼리를 IN 연산자와 함께 사용할 때에는 효율적으로 처리되지 못한다.
• IN 연산자 안에서 사용되는 서브 쿼리에는 ORDER BY와 LIMIT을 동시에 사용할 수 없다.

 

10-2. SELECT 절에 사용된 서브쿼리

SELECT 절에 사용된 서브쿼리는 내부적으로 임시 테이블을 만든다거나 쿼리를 비효율적으로 실행하도록 만들지는 않기 때문에 서브 쿼리가 적절한 인덱스를 사용할 수 있으면 크게 주의할 사항은 없다.

가끔 조인으로 처리해도 되는 쿼리를 SELECT절의 서브쿼리를 이용해 작성할 때도 있다. 하지만 서브 쿼리로 실행될 때보다 조인으로 처리할 때가 훨씬 빠르기 때문에 가능하다면 조인으로 쿼리를 작성하는 것이 좋다.

 

10-3. WHERE절에 단순 비교를 위해 사용된 서브쿼리

독립 서브 쿼리일 때, 서브쿼리를 먼저 실행한 후 상수로 변환하고, 그 조건을 범위 제한 조건으로 사용하는 것이 일반적인 RDBMS의 처리 방식이다.

 

10-4. FROM절에 사용된 서브 쿼리

FROM절에 사용된 서브 쿼리는 항상 임시 테이블을 사용하므로 제대로 최적화되지 못하고 비효율적일 때가 많으며, 불필요하게 사용된 경우가 많기 때문에 FROM절의 서브 쿼리를 조인 쿼리로 바꾸는 경우가 많다.

11. 집합 연산

여러 테이블의 레코드를 연결하는 방법이다.

• UNION : 두 개의 집합을 하나로 묶는 역할이다. 중복을 제거하면 UNION DISTINCT를, 제거하지 않으면 UNION ALL을 사용하면 된다. UNION 키워드 뒤에 아무것도 명시하지 않으면 DISTINCT가 적용된다. 중복인 레코드를 확인하는 방법은 ‘모든 칼럼’을 비교하는 것이다.

12. AUTO_INCREMENT

• AUTO_INCREMENT 속성을 가진 칼럼은 반드시 PK나 유니크 키의 일부로 정의돼야 한다.
• AUTO_INCREMENT 속성을 가진 칼럼 하나로 PK를 생성할 때는 아무런 제약이 없다.
• 여러 개의 칼럼으로 PK를 만들 때
  • AUTO_INCREMENT 속성의 칼럼이 제일 앞이라면, 아무런 제약이 없다.
  • AUTO_INCREMENT 속성의 칼럼이 제일 앞이 아니라면, InnoDB에서는 불가능하다. 반드시 AUTO_INCREMENT 속성을 가진 칼럼이 제일 앞인 UNIQUE키가 하나 더 있어야 한다.

13. SQL 힌트

13-1. STRAIGHT JOIN

STRAIGHT JOIN 힌트는 옵티마이저가 FROM절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.

 

13-2. USE INDEX / FORCE INDEX / IGNORE INDEX

복잡한 인덱스에 대해 옵티마이저가 적합한 인덱스를 선택하지 못할 때 사용할 수 있다.

• USER INDEX : 특정 인덱스를 사용하도록 권장
• FORCE INDEX : 더 강하게 권장
• IGNORE INDEX : 특정 인덱스를 사용하지 못하게 함

 

13-3. 쿼리 캐싱 날리기

RESET QUERY CACHE 명령은 MySQL 서버에 포함된 모든 캐시 내용을 삭제하며, 삭제 작업이 진행되는 동안 모든 쿼리의 실행이 대기해야 한다. 따라서 서비스 중인 MySQL 서버에서는 이 명령을 실행할 때 주의해야 한다.