더 빠른 SQL 쿼리 작성하기 (SQL Optimization)

 

 
SQL은 대량의 데이터를 처리하는 데 사용되는 가장 널리 사용되는 데이터베이스 언어 중 하나입니다. 
SQL 쿼리를 최적화하면 데이터 처리 속도를 높일 수 있으며, 이는 큰 데이터베이스에서 중요한 문제입니다. 
SQL 쿼리를 최적화하고 처리속도를 높이는 방법에는 여러 가지가 있는데요. 이 글에서는 일반적으로 사용되는 몇 가지 최적화 방법과 예시에 대해서 살펴 보도록 하겠습니다.
 
 
 
 
1. Use `regexp_like` instead of `LIKE` clauses
'regexp_like'가 'LIKE' 절보다 빠른 이유는 'regexp_like'가 정규 표현식을 사용하기 때문입니다. 정규 표현식은 'LIKE'보다 더 강력하고 유연한 패턴 매칭 도구입니다.
'LIKE'는 %와 _와 같은 와일드카드를 사용할 수 있는 간단한 패턴 매칭 구문을 사용하지만, 정규 표현식만큼 유연하지는 않습니다. 반면 'regexp_like'는 정규 표현식을 사용하여 복잡한 패턴을 정의할 수 있으며 더 복잡한 패턴을 매칭할 수 있습니다.
또한, 정규 표현식은 패턴 매칭에 최적화되어 있으며 'LIKE' 절은 전체 테이블 스캔이나 인덱스 스캔이 필요할 수 있습니다. 정규 표현식은 더 구체적이기 때문에 효율적인 매칭을 위해 최적화될 수 있으며, 이는 쿼리의 성능 향상에 이어 더 빠른 쿼리 실행 속도를 낼 수 있습니다.
요약하자면, 'LIKE' 절은 간단한 패턴 매칭 작업에 유용하지만, 'regexp_like'는 더 강력한 패턴 매칭 능력, 더 나은 쿼리 최적화 및 더 빠른 쿼리 실행 성능을 제공합니다.
 
 
2. Use ‘regexp_extract’ rather than ‘Case-when Like’
'regexp_extract'가 'CASE-WHEN LIKE'보다 빠른 이유는 'regexp_extract'가 정규 표현식을 사용하여 문자열에서 부분 문자열을 추출하기 때문입니다. 이는 보통 'CASE-WHEN LIKE'보다 더 빠릅니다.
'CASE-WHEN LIKE'는 간단한 패턴 매칭 구문을 사용하여 문자열에서 부분 문자열을 추출할 수 있습니다. 그러나 이는 매우 제한적이며, 정규 표현식과 비교하면 매우 느릴 수 있습니다.
반면, 'regexp_extract'는 더 복잡한 패턴 매칭 작업을 수행할 수 있으며, 문자열에서 부분 문자열을 추출하기 위한 정규 표현식을 사용하여 이 작업을 보다 효율적으로 수행할 수 있습니다. 또한, 정규 표현식은 패턴 매칭에 최적화되어 있기 때문에, 일반적으로 'CASE-WHEN LIKE'보다 더 빠릅니다.
요약하면, 'regexp_extract'는 문자열에서 부분 문자열을 추출하는 데 더 효율적이며, 정규 표현식은 패턴 매칭에 최적화되어 있어 더 빠른 속도를 제공합니다.
예를 들어, 다음과 같은 테이블이 있다고 가정해보겠습니다.

my_table
+----+-------+
| id | value |
+----+-------+
| 1  | abc   |
| 2  | def   |
| 3  | ghi   |
+----+-------+

이 테이블에서 'abc'라는 문자열을 포함한 모든 행을 선택하려면 다음과 같이 'CASE-WHEN LIKE' 구문을 사용할 수 있습니다.

SELECT *
FROM my_table
WHERE value LIKE '%abc%';

이 쿼리는 문자열을 비교하여 '%abc%' 패턴이 포함된 모든 행을 선택합니다. 그러나 이 쿼리는 처리 속도가 느릴 수 있습니다.

이를 개선하기 위해서는 'REGEXP_EXTRACT' 함수를 사용할 수 있습니다. 예를 들어, 다음과 같이 쿼리를 수정할 수 있습니다.

SELECT *
FROM my_table
WHERE REGEXP_EXTRACT(value, 'abc') IS NOT NULL;

이 쿼리는 'REGEXP_EXTRACT' 함수를 사용하여 문자열에서 'abc' 패턴을 추출합니다. 이는 데이터베이스가 처리 방식이 간단해지므로 더욱 빠르게 처리할 수 있습니다.
 
 
 
3. Convert long list of the elements in IN clause into a temporary table
IN 절의 긴 목록을 일시적인 테이블로 변환하는 것이 IN 절을 직접 사용하는 것보다 더 빠른 이유는 쿼리 처리 방식 때문입니다.
IN 절을 사용할 때, 데이터베이스 엔진은 매번 질의되는 테이블의 각 행에 대해 일치하는 값을 찾기 위해 값을 반복적으로 스캔해야 합니다. 이는 특히 목록에 많은 수의 값이 포함된 경우 느리고 비효율적일 수 있습니다.
반면에, 일시적인 테이블을 사용할 때, 데이터베이스 엔진은 값을 한 번 읽고 메모리에 저장합니다. 그리고 일시적인 테이블을 참조하므로 질의할 때 더 효율적으로 처리할 수 있습니다.
일시적인 테이블을 사용하면 IN 절을 사용하는 것보다 더 효율적이고 빠르게 처리할 수 있으며, 목록에 많은 수의 값이 포함된 경우 더욱 그 차이가 두드러집니다.

SELECT *
FROM my_table
WHERE my_column IN ('a', 'b', 'c', 'd', 'e');

이 쿼리에서는 my_column의 값이 'a', 'b', 'c', 'd', 'e' 중 하나인 행을 선택합니다. 그러나 IN 절에 많은 요소가 포함된 경우, 이 쿼리는 처리 속도가 느려질 수 있습니다.
이를 개선하기 위해서는 IN 절에 있는 요소들을 임시 테이블로 변환하면 됩니다. 예를 들어, 다음과 같이 쿼리를 수정할 수 있습니다.

CREATE TEMPORARY TABLE temp_table (value VARCHAR(10));
INSERT INTO temp_table VALUES ('a'), ('b'), ('c'), ('d'), ('e');

SELECT *
FROM my_table
WHERE my_column IN (SELECT value FROM temp_table);

 
 
4. Order your JOINS from largest tables to smallest tables
가장 큰 테이블부터 가장 작은 테이블 순으로 JOIN을 수행하는 것이 더 빠른 이유는 데이터베이스 엔진이 테이블을 JOIN 할 때 처리해야 할 행 수를 최소화할 수 있기 때문입니다.
데이터베이스에서 JOIN을 수행할 때, 먼저 큰 테이블과 작은 테이블의 조인을 수행하면 더 적은 수의 조인이 필요하기 때문에 처리 시간이 단축됩니다. 반면, 작은 테이블과 큰 테이블의 조인을 먼저 수행하면 작은 테이블의 모든 행이 큰 테이블의 모든 행과 일치하는지 확인해야 합니다. 이는 더 많은 작업과 처리 시간이 소요됩니다.
따라서, JOIN을 처리할 때 가장 큰 테이블부터 시작하면 더 빠르고 효율적인 처리가 가능합니다.
 
 
 
5. Use simple equi-joins
간단한 equi-join을 사용하는 것이 더 빠른 이유는 데이터베이스 엔진이 처리해야 할 데이터 양이 적기 때문입니다.
간단한 equi-join은 등호(=)를 사용하여 두 개의 테이블 간에 조인을 수행하는 것을 의미합니다. 즉, 두 개의 테이블에서 동일한 값을 가진 열을 조인하는 것입니다. 이러한 조인은 쿼리를 더 빠르게 처리할 수 있으며, 인덱스를 사용하여 처리 속도를 더욱 향상시킬 수 있습니다.
반면에, 복잡한 join을 사용하면 처리할 데이터 양이 더 많아지기 때문에 처리 시간이 더 오래 걸릴 수 있습니다. 또한, 여러 조건을 사용하여 조인을 수행하면 데이터베이스 엔진이 모든 조건을 처리해야 하므로 더 많은 처리 시간이 소요될 수 있습니다.
따라서, 가능한 경우 간단한 equi-join을 사용하여 쿼리를 빠르고 효율적으로 처리할 수 있습니다.

table1
+----+-------+
| id | value |
+----+-------+
| 1  | abc   |
| 2  | def   |
| 3  | ghi   |
+----+-------+

table2
+----+-------+
| id | value |
+----+-------+
| 3  | jkl   |
| 4  | mno   |
| 5  | pqr   |
+----+-------+

SELECT *
FROM table1
JOIN table2 ON table1.id = table2.id;

-- output
+----+-------+----+-------+
| id | value | id | value |
+----+-------+----+-------+
| 3  | ghi   | 3  | jkl   |
+----+-------+----+-------+

이 결과에서는 table1과 table2에서 id가 3인 행만을 선택합니다. 이처럼 equi-join은 간단하게 두 개 이상의 테이블을 조인할 수 있으며, 데이터베이스가 처리 방식이 간단하므로 더욱 빠르게 처리할 수 있습니다.
 
 
 
6. Always "GROUP BY" by the attribute/column with the largest number of unique entities/values.
가장 많은 고유 엔티티/값을 갖는 속성/열을 기준으로 "GROUP BY"를 수행하는 것이 더 빠른 이유는 데이터베이스 엔진이 처리해야 할 데이터 양을 최소화할 수 있기 때문입니다.
GROUP BY 절은 특정 열을 기준으로 데이터를 그룹화합니다. 이를 통해 특정 기준에 따라 데이터를 집계하고 분류할 수 있습니다. 따라서, GROUP BY를 수행할 때 데이터 양이 증가할 수 있습니다.
그러나, 가장 많은 고유 엔티티/값을 갖는 속성/열을 기준으로 GROUP BY를 수행하면 데이터 양을 최소화할 수 있습니다. 이는 데이터베이스 엔진이 처리해야 할 데이터가 적어지기 때문입니다. 즉, 더 적은 데이터만을 처리해도 결과를 얻을 수 있기 때문에 처리 속도가 더 빨라집니다.
반면, 적은 수의 고유 엔티티/값을 가지는 속성/열을 기준으로 GROUP BY를 수행하면 데이터 양이 증가하여 처리 속도가 느려질 수 있습니다.
따라서, 가능한 경우 가장 많은 고유 엔티티/값을 갖는 속성/열을 기준으로 GROUP BY를 수행하여 쿼리를 더 빠르고 효율적으로 처리할 수 있습니다.
 
 
7. Avoid subqueries in WHERE clause
WHERE 절에서 하위 쿼리를 사용하지 않는 것이 더 빠른 이유는 하위 쿼리를 사용하면 처리해야 할 데이터 양이 증가하기 때문입니다.
하위 쿼리를 사용하면 데이터베이스 엔진은 WHERE 절의 조건에 따라 하위 쿼리를 처리하고 그 결과를 메모리나 디스크에 저장해야 합니다. 그리고 나서 그 결과를 다시 원래의 쿼리에 사용합니다. 이는 데이터 양이 증가하므로 처리 시간이 느려질 수 있습니다.
반면에, 하위 쿼리 없이 WHERE 절에 단순한 조건식을 사용하면 데이터베이스 엔진은 더 적은 양의 데이터를 처리할 수 있습니다. 또한, 적절한 인덱스가 있다면 인덱스를 사용하여 쿼리의 처리 속도를 더욱 향상시킬 수 있습니다.
따라서, 가능한 경우 WHERE 절에서 하위 쿼리를 사용하지 않도록 하여 쿼리를 더 빠르고 효율적으로 처리할 수 있습니다.
 
 
8. Use MAX() instead of RANK()
MAX()를 사용하는 것이 RANK()를 사용하는 것보다 더 빠른 이유는 데이터베이스 엔진이 처리해야 할 데이터 양이 적기 때문입니다.
MAX()는 열에서 가장 큰 값을 반환하는 집계 함수입니다. 따라서, MAX()를 사용하면 데이터베이스 엔진은 전체 데이터를 검색하지 않고 열에서 가장 큰 값을 찾아 반환합니다.
반면, RANK()는 데이터를 분류하고 각 데이터의 순위를 결정하는 데 사용됩니다. 이를 위해 데이터베이스 엔진은 모든 데이터를 검색하고 순위를 결정해야 합니다. 따라서, 데이터 양이 많은 경우 처리 시간이 더 오래 걸릴 수 있습니다.
따라서, 가능한 경우 MAX()를 사용하여 열에서 가장 큰 값을 반환하는 것이 더 효율적입니다. RANK() 함수는 정확한 순위를 결정해야 할 때 사용할 수 있지만, 대부분의 경우 MAX()를 사용하여 처리 속도를 향상시킬 수 있습니다.
 
 
 
9. Use approx_distinct() instead of COUNT(DISTINCT) for very large datasets
approx_distinct()를 사용하는 것이 매우 큰 데이터 집합에서 count(distinct)를 사용하는 것보다 더 빠른 이유는 근사치를 사용하기 때문입니다.
count(distinct)를 사용하면 중복되지 않은 값을 찾기 위해 전체 데이터를 검색해야 합니다. 이는 매우 큰 데이터 집합에서는 처리 시간이 오래 걸릴 수 있습니다.
반면에, approx_distinct()는 HyperLogLog 알고리즘을 사용하여 중복되지 않은 값을 근사치로 계산합니다. 이를 통해 전체 데이터를 검색하지 않아도 중복되지 않은 값의 개수를 빠르게 계산할 수 있습니다.
다음은 예시입니다. 고객 데이터베이스에서 수백만 개의 고객 ID가 있을 때, count(distinct)를 사용하면 모든 ID를 검색하고 중복되지 않은 ID의 개수를 찾아야 합니다. 이는 처리 시간이 매우 오래 걸릴 수 있습니다. 반면에, approx_distinct()를 사용하면 HyperLogLog 알고리즘을 사용하여 중복되지 않은 ID의 개수를 근사치로 계산할 수 있습니다. 이를 통해 전체 데이터를 검색하지 않아도 빠르게 계산할 수 있습니다.
따라서, 매우 큰 데이터 집합에서는 approx_distinct()를 사용하여 중복되지 않은 값의 개수를 빠르게 계산할 수 있습니다.
 
 
10. Use approx_percentile(metric, 0.5) for median
median 값을 구할 때 approx_percentile(metric, 0.5)를 사용하는 것이 다른 방법보다 더 빠른 이유는 근사치를 사용하기 때문입니다.
median 값은 데이터 세트의 중간 값이며, 중간 값을 구하기 위해서는 데이터를 정렬하고 중간 값(데이터 집합의 가운데 값)을 찾아야 합니다. 이는 매우 시간이 오래 걸리며, 특히 매우 큰 데이터 집합의 경우 매우 느립니다.
반면, approx_percentile(metric, 0.5)는 HyperLogLog 알고리즘을 사용하여 근사치로 median 값을 계산합니다. 이는 전체 데이터를 정렬하지 않고 중간 값을 계산하는 것보다 빠르게 처리할 수 있습니다.
HyperLogLog 알고리즘은 데이터의 고유 값의 비율을 계산하는 데 사용되는 확률적 알고리즘입니다. 이를 사용하여 근사치 중앙값을 계산할 수 있습니다. 예를 들어, 데이터 세트의 크기가 1억 개인 경우, approx_percentile(metric, 0.5) 함수를 사용하여 median 값을 계산할 때, 모든 데이터를 정렬하지 않고 중간 값을 찾아내므로 매우 빠른 처리 속도를 보장할 수 있습니다.
따라서, 가능한 경우 median 값을 계산할 때 approx_percentile(metric, 0.5)를 사용하여 더 빠르고 효율적으로 처리할 수 있습니다.
 
 
11. Avoid UNIONs

UNION을 가능한한 피하는 것이 빠른 이유는 UNION을 사용하면 중복을 제거하고 결과를 결합하기 위해 추가적인 처리가 필요하기 때문입니다.
UNION은 두 개 이상의 SELECT 문의 결과를 하나의 결과 집합으로 결합하기 위해 사용됩니다. 그러나 UNION을 사용하면 성능이 저하될 수 있습니다. 이는 UNION을 사용하면 중복을 제거하고 결과를 결합하는 데 추가적인 처리가 필요하기 때문입니다. 또한, UNION을 사용하면 데이터 양이 증가할 수 있으므로 처리 시간이 느려질 수 있습니다.
또한, UNION을 사용하면 조인과 같은 다른 방법으로도 결과를 결합할 수 있으므로 가능하면 UNION을 피하는 것이 좋습니다.
 
 
 
12. Use WITH statements vs. nested subqueries
WITH 문을 사용하는 것이 중첩된 서브쿼리보다 더 빠른 이유는 데이터베이스가 쿼리를 최적화하는 방식 때문입니다.
WITH 문을 사용하면 중첩된 서브쿼리와 달리 더욱 간결한 쿼리를 작성할 수 있습니다. 또한 WITH 문은 쿼리에서 임시 테이블을 만들어 두고 이를 이용해 다른 쿼리를 작성하는 방식으로 처리됩니다.
이러한 WITH 문은 데이터베이스가 쿼리를 최적화할 때 임시 테이블에 대한 메모리 공간을 할당하는 등의 처리를 미리 수행하므로 쿼리 실행 시간을 줄일 수 있습니다. 또한 WITH 문을 사용하면 중복되는 서브쿼리를 제거할 수 있어 쿼리의 가독성을 높일 수 있습니다.
예를 들어, 다음과 같은 두 개의 쿼리가 있다고 가정해보겠습니다.

-- 중첩된 서브쿼리를 사용한 쿼리
SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE country = 'USA'
);

-- WITH 문을 사용한 쿼리
WITH usa_customers AS (
    SELECT customer_id
    FROM customers
    WHERE country = 'USA'
)
SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM usa_customers
);

 
이 두 쿼리는 동일한 결과를 반환하지만, WITH 문을 사용하는 쿼리는 중첩된 서브쿼리를 사용하는 쿼리보다 더욱 간결하고 가독성이 높습니다. 또한, WITH 문을 사용하는 쿼리는 데이터베이스에서 임시 테이블을 만들어 두고 이를 사용하여 쿼리를 최적화하기 때문에 더 빠르게 실행될 가능성이 높습니다.
 
 
이 글에서 설명한 SQL 최적화 방법과 예시들은 일부일 뿐이며, 다른 최적화 방법들도 많이 존재합니다. 그러나 이러한 최적화 방법들은 SQL 쿼리를 작성할 때 유용하게 사용하실 수 있으며, 특히 큰 규모의 데이터베이스에서 데이터 처리 속도를 개선하는 데 도움이 될 수 있습니다. SQL 쿼리를 최적화하는 것은 시간이 걸리고 노력이 필요하지만, 성능을 개선함으로써 더욱 효율적으로 데이터를 처리할 수 있습니다.
 
 
 

이제 직접 미국 대학원과 현지 취업을 경험해 본 멘토들과 함께 대학원 진학과 미국 취업을 준비해 보세요.

https://www.datakorlab.com/

Data KorLab

 
 
#영어이력서 #영문이력서 #이력서 #Cover #coverletter #resume #레주메 #데이터분석 #데이터애널리스트 #미국데이터분석석사 #미국석사 #데이터과학자 #네트워킹이벤트 #GMAT #링크드인 #미국데이터분석 #데이터사이언스 #미국 #GRE #해외취업 #데이터사이언티스트 #미국현지취업 #데이터분석석사 #글래빈 #미국데이터사이언티스트 #글래빈미국 #글래빈미국데이터사이언티스트 #브라이언 #브라이언미국데이터사이언티스트 #스테이시미국데이터사이언티스트 #미국대학원 #해외취업마스터 #미국생활 #데이터사이언스석사 #미국유학생 #유학생 #미국데이터 #애널리틱스석사 #데이터석사 #미국데이터석사유학 #뉴욕직딩 #미국유학 #미국직장인 #미국취업 #미국현지취업 #prerequisites #선수과목 #미국대학원선수과목 #리트코드 #테크니컬인터뷰 #코딩인터뷰 #leetcode #커피챗 #대학원진학컨설팅 #데이터분석대학원 #데이터사이언스인터뷰 #데이터분석가인터뷰 #datascientistinterview #datascientisttechnicalinterview