SQL 튜닝 - 2 쿼리 최적화
🐬 최적화할 쿼리문 찾기
Explain 이란?
EXPLAIN 구문은 MySQL이 쿼리를 실행하는 방식을 제공하는 명령문입니다.
우리는 이 구문을 사용하여 최적화할 쿼리문을 찾을 수 있습니다.
이 구문을 사용하면 MySQL 옵티마이저가 테이블에 액세스하고 쿼리 작업을 수행하는 방법에 대한 정보를 보여주는데, 이를 쿼리 실행 계획이라고 합니다.
Explain 사용해보기
이렇게 SELECT 앞에 EXPLIAN 을 적어주면, MySQL이 테이블에 액세스한 방법을 보여주는 출력(실행 계획)을 반환합니다.
1
EXPLAIN SELECT * FROM people;
반환값의 열을 하나하나 살펴보겠습니다.
- ID: 실행 중인 쿼리의 고유 식별자입니다.
- Select Type: 실행되는 select 문의 유형을 알려줍니다. (예 - simple, primary, union 등)
- Table: 접근되는 테이블의 이름입니다.
- Partitions: 쿼리에 대해 접근되는 파티션을 표시합니다(이 과정에서는 범위를 넘어섭니다).
- Type: MySQL이 데이터를 검색하는 데 사용한 접근 방식입니다. 이것은 가장 중요한 열 값 중 하나이며, 나중에 자세히 논의하겠습니다.
- Possible Keys: MySQL이 사용할 수 있는 index 들입니다.
- Key: MySQL이 실제로 사용하는 index 입니다.
- Key Length: MySQL이 사용하는 index 의 길이를 표시합니다.
- Ref: index와 비교되는 값입니다.
- Rows: MySQL이 결과를 반환하기 위해 확인해야 할 추정 행 수입니다.
- Filtered: 쿼리 기준에 부합하는 추정 행의 비율입니다.
Explain 의 TYPE 칼럼 이해하기
EXPLAIN 구문을 사용하면 우리는 쿼리 실행 계획이라는 값을 반환받습니다.
이 글에서는 반환값에서 가장 중요한 열 중 하나인 type에 대해 자세히 알아보겠습니다.
const나 ref 접근 방식을 본다면, 잘 구조화된 데이터베이스를 가지고 있으며 성능이 좋다는 것을 의미할 수 있습니다.
index, all 접근 방식을 본다면, 빠르게 최적화를 시작하는 것이 좋습니다.
Const
시간복잡도 : O(1)
const 접근 방식은 가장 효율적인 방법 중 하나입니다.
const 접근은 기본 키(Primary Key)나 고유 인덱스(Unique Index)가 있을 때만 사용되며, MySQL이 한번의 작업 만으로 필요한 행을 찾을 수 있게 합니다.
type 열에서 const를 볼 때, MySQL은 이 쿼리에 대해 단 하나의 일치 항목만 있다는 것을 알고 있으며, 이는 작업이 가장 효율적이라는 뜻입니다.
Ref
시간복잡도 : O(log N)
ref 접근 방식은 const보다는 약간 덜 효율적이지만, 적절한 인덱스가 있는 경우 여전히 훌륭한 선택입니다.
ref 접근은 쿼리에 인덱스가 있는 열이 동등 연산자에 의해 일치될 때 사용됩니다.
MySQL이 인덱스를 바탕으로 필요한 행을 찾을 수 있다면, 하는 것을 피하고 쿼리 속도를 크게 높일 수 있습니다.
Fulltext
시간복잡도 : O(log N)
MySQL은 텍스트 기반 검색 쿼리를 위한 열에 Full-Text Index를 만들 수 있는 옵션을 제공합니다.
MATCH() ... AGAINST()같은 특별한 구문에 사용됩니다.
쿼리에 Full-Text Index를 가진 칼럼이 포함되고 Full-Text Index가 있는 경우 fulltext 접근 방식이 사용됩니다.
fulltext 접근을 통해 MySQL은 인덱스를 검색하고 결과를 빠르게 반환할 수 있습니다.
Range
시간복잡도 : O(log N)
where 절에서 범위 연산자를 사용할 때, MySQL은 올바른 데이터를 찾기 위해 값의 범위를 통해 검색해야 한다는 것을 알고 있습니다. (BETWEEN, >, <, >=, <=)
MySQL은 B-Tree 인덱스를 사용하여 트리 상단에서 범위의 첫 번째 값까지 내려갑니다.
거기서부터 MySQL은 트리 하단의 연결 리스트를 참조하여 원하는 범위 내의 값이 있는 행을 찾습니다.
MySQL은 불일치가 발견될 때까지 범위 내의 모든 요소를 검토한다는 점을 주목한다면, 이 방법은 const, ref 등 다른 방식들보단 느릴 수 있습니다.
- const, range 는 하나만 찾으면 됩니다.
- range 는 시작점을 찾은 후 해당 범위에 속하는 모든 데이터를 순차적으로 검색해서 반환해야 합니다. 가져와야 하는 값이 많으니까 당연하죠 뭐.🤷
Index
시간복잡도 : O(N) 최적화 필요
인덱스 접근 방식은 MySQL이 필요한 데이터를 찾기 위해 모든 인덱스를 스캔한다는 것을 나타냅니다.
인덱스 접근은 지금까지 나열된 접근 방식 중 가장 느리지만, Full Table Scan하는 것보다는 여전히 빠릅니다.
- MySQL이 기본(primary) 또는 고유(unique) 인덱스를 사용할 수 없을 때, 인덱스가 있다면 인덱스 접근을 사용합니다.
- MySQL 옵티마이저가 “이 쿼리는 인덱스 전체 스캔을 실행하는 것이 성능에 낫겠는데?”라고 판단하면 인덱스 접근을 사용합니다. (쿼리가 인덱스에 포함된 모든 컬럼을 사용하거나, 인덱스가 쿼리 조건에 잘 맞을 때)
All
시간복잡도 : O(N) 최적화 필요
마지막으로, all 접근 방식은 MySQL이 필요한 데이터를 찾기 위해 테이블의 모든 데이터를 다 뒤져본다는 것을 의미합니다.
all은 가장 느리고 비효율적인 접근 방식이므로, 가능한 한 피하고자 하는 방식입니다.
적절한 인덱스가 없을 때 MySQL은 전체 테이블을 스캔할 수도 있으므로, 이는 인덱싱 전략을 검사할 좋은 기회입니다.
Explain의 출력 형식 제어하기
FORMAT = TREE
트리 형식은 실행 계획을 중첩된 트리 구조로 자세히 제공하는 데 유용합니다.
1
explain format=tree select * from people where first_name = "Aaron"
FORMAT = JSON
JSON 형식을 사용하면 쿼리의 인덱스 사용, key parts, 쿼리 비용에 대한 정보를 기계가 읽을 수 있는 JSON 형식으로 제공합니다.
1
explain format=json select * from people where first_name = "Aaron"
EXPLAIN ANALYZE
1
EXPLAIN ANALYZE format=json select * from people where first_name = "Aaron"
EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 쿼리 실행 계획에 대한 자세한 통계를 제공합니다. 이 형식은 실제로 쿼리를 실행하므로 주의해서 사용해야 한다는 점을 유의해야 합니다.
EXPLAIN ANALYZE 형식을 사용하면, MySQL은 실제 실행 시간과 읽은 레코드 수를 포함한 자세한 통계를 제공합니다.
조인이나 서브쿼리가 포함된 쿼리를 작업하는 경우, 이 통계는 쿼리가 시간을 어디에 쓰고 있는지 이해하는 데 매우 가치가 있습니다.
🐬 실전 쿼리 튜닝
인덱스 난독(Index obfuscation) 방지
인덱스 난독(Index obfuscation)을 방지하려면 가능한 열(column)을 그대로 두어야 합니다.
효율적으로 데이터를 질의하려면, 쿼리문을 짤 때 인덱스를 사용하게끔 짜는게 좋습니다. 그러나
예를 들면, Movie 테이블에서 2시간 미만의 영화를 필터링하고 싶다고 해봅시다.
Movie 테이블의 상영시간 칼럼은 분 단위로 되어 있으므로 상영시간을 60으로 나누어 시간으로 변환해야 합니다.
그러나 이렇게 칼럼을 직접 가공하는 연산을 하면 열을 혼란하게 만들고 MySQL이 인덱스를 사용하기 어렵게 만듭니다. 인덱스를 활용하지 못하고 Full Table Scan을 해야 되는거죠.
인덱스 열을 직접 가공하는 대신, 최대한 다른 항으로 요소들을 옮기세요.
1
SELECT * FROM film WHERE length / 60 < 2;
이 식에서는
length열의 각 값에 대해 60으로 나누는 계산을 합니다. 그런 다음 그 결과가 2보다 작은지 확인합니다.이 방식의 문제는, 데이터베이스가 이미 만들어둔 인덱스를 활용할 수 없게 된다는 것입니다.
왜냐하면 데이터베이스는 원래의
length값이 아닌 계산된 결과를 기반으로 비교를 해야 하기 때문입니다.이로 인해, 데이터베이스는 테이블의 모든 행을 하나하나 검사해야 하며, 이는 매우 비효율적입니다.
1
SELECT * FROM film WHERE length < 2 * 60;
반면에 이 식에서는
length열을 그대로 사용하고, 비교하는 값(2시간)을 분 단위로 변환하여 (2 * 60, 즉 120분) 비교합니다.이 방법은 데이터베이스가
length열에 대해 미리 만들어진 인덱스를 활용할 수 있게 해줍니다.데이터베이스는 인덱스를 사용하여 필요한 행들을 훨씬 빠르고 효율적으로 찾을 수 있습니다.
중복 조건(rebundant condition)
중복 조건이란, 쿼리에 적어도 논리적으로 결과를 변경하지 않는 두 번째 조건을 말합니다.
결과에 아무런 변화가 없지만 쿼리를 최적화하는 데 도움이 될 수 있기 때문에 여전히 가치가 있습니다.
현실적으로 우리는 DB 테이블 구조를 바꿀순 없습니다.
하지만 중복조건을 사용하면 테이블 구조를 변경하지 않고 인덱스를 효과적으로 사용할 수 있다는 장점이 있습니다.
우리의 Todo-list 테이블에서 due_time이 24시간 이내로 남은 레코드를 가져오고 싶다고 상상해봅시다.
due_date 에는 인덱스가 있지만, ADDTIME(due_date + due_time) 연산을 해버리면 인덱스가 obfuscated되어버려서 full-table-scan 을 해야합니다.
| id | task | due_time | due_date | completed |
|---|---|---|---|---|
| 1 | 공부하기 | 18:00:00 | 2023-12-29 | false |
| 2 | 뿌링클먹기 | 15:30:00 | 2023-12-30 | false |
| 3 | 놀기 | 23:59:59 | 2023-12-28 | false |
Before Tunning
1
2
3
SELECT * FROM todos
WHERE
ADDTIME(due_date, due_time) BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
이런 경우에 중복조건을 사용하면 유리합니다.
인덱스를 사용한 중복 조건으로 탐색 범위를 먼저 줄여놓는 겁니다.
전체 테이블에서 결과보다 살짝 큰 범위까지 대충 잘라줍니다. (멋있는 말로 근사치approximate 라고 부릅니다.)
그 안에서 조건에 부합하지 않는 것을 필터링하면 됩니다.
인덱스가 없는 필터링은 굉장히 비싼데, 이렇게 탐색범위를 확 줄여서 비용을 아낄 수 있죠.
After Tunning
1
2
3
4
5
SELECT * FROM todos
WHERE
ADDTIME(due_date, due_time) BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
AND
due_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 1 DAY
필요한 만큼만 반환받기(select only what you need)
데이터베이스 테이블에서 SELECT *문을 실행하면 테이블의 모든 데이터, 모든 행과 열을 검색합니다.
JSON이나 TEXT와 같은 용량이 큰 열을 다룰 때 테이블 내 모든 데이터를 검색하는 것은 상당한 성능 문제를 야기할 수 있습니다.
실제로 사용할 계획이 있는 열만 검색한다면 네트워크 사용률, 디스크 액세스 및 메모리 사용량이 크게 줄어듭니다.
특히 쿼리문 작성 시 인덱스가 있는 열을 기준으로 한다면 빠르게 필요한 행만 가져오게끔 범위를 좁힐 수 있습니다.
테이블의 행 수 세기
1
SELECT COUNT(*) FROM table_name;
테이블의 행 수를 세고 싶다면 모든 데이터를 선택하여 애플리케이션으로 보내지 말아야 합니다. 대신 이 작업을 데이터베이스에 위임하여 데이터베이스에서 계산하게 해야 합니다.
연산하기
1 2 3 4 5
SELECT MIN(column_name) FROM table_name; SELECT MAX(column_name) FROM table_name; SELECT AVG(column_name) FROM table_name;
최소값, 최대값 및 평균값과 같은 계산은 애플리케이션에서가 아닌 데이터베이스에서 수행해야 합니다.
이러한 계산을 데이터베이스에서 수행함으로써 네트워크로 보내는 데이터 비용을 크게 줄일 수 있습니다.
또 애플리케이션이 모든 행을 살펴보고 사용자에게 다시 보내는 불필요한 과정을 없앨 수 있습니다.
중복된 값은 받지 않기
1
SELECT DISTINCT column_name FROM table_name;
DB에서 DISTINCT 로 중복제거 작업을 수행한다면 애플리케이션에게 불필요한 행을 보내지 않아도 됩니다.
행 제한하기(Limiting rows)
쿼리에서 반환되는 행의 수를 제한하세요.
주의! 애플리케이션이 받아서 데이터를 필터링하는게 아니라, DB 단계에서 반환할 행의 갯수를 계산해서 보내게 해야합니다.
페이지네이션 쿼리 사용하기
1
LIMIT 10 OFFSET 20
LIMIT에 ORDER BY 절을 항상 넣어야 하며, 그것이 단순히 ORDER BY id라 하더라도 마찬가지입니다.
그렇지 않으면 MySQL이 행을 정렬하는 방법을 결정하게 되며, 이는 일관성에 문제를 일으킬 수 있습니다. (예 -사용자는 똑같이 게시판의 2번 페이지를 클릭했는데, 새로고침할때마다 완전 다른 게시물들이 2번 페이지에 있음.)
페이지네이션이란?
페이지네이션은 사용자가 관심 있어 할 특정 페이지의 결과만 반환하는 기술을 말합니다.
예를 들어, 사용자가 get 요청했을때 앱은 500,000개의 인스타그램 게시물을 반환한다면 매우 많은 처리를 낭비하게 됩니다.
왜냐면 성능과 사용성을 위해 사용자의 화면에는 게시물을 10개씩만 보여주는 경우가 대부분이거든요.
따라서
LIMIT,OFFSET등을 이용해 반환하는 행의 수를 제한하고 사용자에게 유용할 만큼의 행만 반환하는 것이 중요합니다.
📎 Related articles
| 이슈명 | 링크 |
|---|---|
| Introduction to queries | https://planetscale.com/learn/courses/mysql-for-developers/queries/introduction-to-queries |
| 성능 향상을 위한 SQL 작성법 | https://d2.naver.com/helloworld/1155 |