쿼리 최적화 방법 Query Optimization

쿼리 최적화

Nested Query를 지양

SELECT *
FROM Orders
WHERE customer_id IN (SELECT customer_id
                      FROM Customers
                      WHERE region = 'Asia');

인덱싱

• 자주 액세스되는 필드에 인덱스를 생성하여 데이터 검색 속도를 향상시킨다
• 인덱스 고르는 가이드라인
  • 검색, 정렬, JOIN에 자주 이용되는 필드
  • UPDATE가 빈번하게 일어나지 않는 필드
  • INSERT, UPDATE,DELETE가 동시에 수행되는 FK (Weak Entity에 대한 Shared Locking ) 없이 마스터 테이블에서의 업데이트를 허용함
  • WHERE와 AND 연산을 자주 사용하는 필드
  대부분  CREATE INDEX  ON키워드를 포함한다

  CREATE INDEX customers_by_phone
  ON customers (phone_number)
  
  

Select 시 필요한 데이터만 조회

SELECT id FROM users

→

SELECT * FROM users

SELECT DISTINCT, UNION DISTINCT 사용하지 않기

• Group으로 묶어서 duplication을 제거하는데 Group by 연산은 고비용이다

SET STATISTICS TIME ON
SELECT DISTINCT Name, Color, StandardCost, Weight FROM SalesLT.Product

→ 그냥 중복된 필드 그대로 사용하기

SET STATISTICS TIME ON
SELECT  Name, Color, StandardCost, Weight, SellEndDate, SellEndDate FROM SalesLT.Product

혹은 EXISTS를 사용하기

SET STATISTICS TIME ON
SELECT p.Name, p.Color, p.StandardCost, p.Weight
FROM SalesLT.Product p
WHERE EXISTS (
    SELECT 1
    FROM SalesLT.Product p2
    WHERE p2.Name = p.Name
    AND p2.Color = p.Color
    AND p2.StandardCost = p.StandardCost
    AND p2.Weight = p.Weight
    HAVING COUNT(*) > 1
);

INNER JOIN 을 WHERE 절 대신 쓰자

WHERE절 사용보다 INNER JOIN을 더 사용하기

WHERE절은 CROSS join/ CARTESIAN product 로 동작되는데 두 테이블의 CARTESIAN product 는 시간이 굉장히 많이 걸린다

SET STATISTICS IO ON
SELECT p.Name, Color, ListPrice 
FROM SalesLT.Product p, SalesLT.ProductCategory pc
WHERE P.ProductCategoryID = pc.ProductCategoryID

→

SET STATISTICS TIME ON
SELECT p.Name, Color, ListPrice FROM SalesLT.Product p
INNER JOIN  SalesLT.ProductCategory pc
ON P.ProductCategoryID = pc.ProductCategoryID

복잡한 함수 사용 지양

SELECT p.id, ANY_VALUE(p.title) AS title, COUNT(l.id) AS like_count
FROM post p
LEFT JOIN post_like l ON p.id = l.post_id
GROUP BY p.id
HAVING COUNT(l.id) > (SELECT AVG(cnt) FROM (SELECT COUNT(id) AS cnt FROM post_like 
GROUP BY post_id) AS subquery);

→

SELECT p.id, ANY_VALUE(p.title) AS title, COUNT(l.id) AS like_count
FROM post p
LEFT JOIN post_like l ON p.id = l.post_id
GROUP BY p.id
HAVING COUNT(l.id) BETWEEN 10 AND 20; -- 좋아요 수가 10에서 20 사이인 게시물 선택

LIMIT command 사용하기

SET STATISTICS IO ON
SELECT Name, Color, ListPrice 
FROM SalesLT.Product
LIMIT 10

EXISTS를 IN 대신 쓰자

IN 연산은 EXISTS 연산보다 고비용이다, 특히 서브쿼리가 클 때 그렇다

서브쿼리로 결과를 조회할때 EXISTS를 IN 대신 사용하자

SET STATISTICS TIME ON
SELECT ProductNumber,Name,Color FROM SalesLT.Product
WHERE ProductID IN
(SELECT ProductID FROM SalesLT.ProductDescription)

→

SET STATISTICS TIME ON
SELECT ProductNumber,Name,Color FROM SalesLT.Product
WHERE  EXISTS
(SELECT ProductID FROM SalesLT.ProductDescription)

Bulk insert/update를 Loop 대신 쓰자

Loop는 똑같은 쿼리를 계속 실행시키므로 사용을 지양하고 bulk inserts and updates를 사용해야 한다

SET STATISTICS TIME ON

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
    PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter)
INSERT INTO [SalesLT].[ProductDescription]
           ([Description]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           ('This is great'
           ,NEWID()
           ,'12/01/2010')
    SET @Counter  = @Counter  + 1

END

→

USE [AdventureWorksLT2019]
GO
SET STATISTICS TIME ON
INSERT INTO [SalesLT].[ProductDescription]
           ([Description]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES
           ('This is great'
           ,NEWID()
           ,'12/01/2010'),
		   ('New news'
           ,NEWID()
           ,'12/01/2010'),
		   ('Awesome product.'
           ,NEWID()
           ,'12/01/2010'),
..........,
		    ('Awesome product.'
           ,NEWID()
           ,'12/01/2010')
GO

LIKE 사용 시 와일드카드 문자열(%)을 String 앞부분에는 배치하지 않기

• 앞부분에 와일드카드를 배치하면 인덱스 활용이 불가능하다
• 전체 테이블을 스캔해야 일치하는 결과를 찾을 수 있으므로 성능이 저하된다
• value IN (...), value = "...", value LIKE "...%" 등으로 쓰면 인덱스를 활용할 수 있다

SELECT *
FROM books
WHERE category LIKE '%Fantasy';

→

SELECT *
FROM books
WHERE category = 'Fantasy' OR category = 'Romantic Fantasy';

GROUP BY 연산 시 WHERE 절을 HAVING 대신 사용하기

• WHERE절을 사용하여 조건을 필터링하는 것이 HAVING절을 사용하는 것보다 효율적이다
• WHERE 절을 사용하면 데이터의 크기를 먼저 줄이고 그 후에 GROUP BY 연산을 수행해서 성능적으로 더 효율적이다
• WHERE GROUP BY가 GROUP BY HAVING 보다 나음

HAVING 사용

SELECT genre, SUM(sales_count) AS total_sales
FROM books
GROUP BY genre
HAVING genre = 'Adventure' AND SUM(sales_count) >= 100;

→

WHERE 사용

SELECT genre, SUM(sales_count) AS total_sales
FROM books
WHERE genre = 'Adventure'
GROUP BY genre
HAVING SUM(sales_count) >= 100;

HAVING 사용

SELECT b.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM books b
INNER JOIN ratings r ON b.id = r.book_id
GROUP BY b.id
HAVING b.id > 1000;

→

WHERE 사용

SELECT b.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM books b
INNER JOIN ratings r ON b.id = r.book_id
WHERE b.id > 1000
GROUP BY b.id;

3개 이상의 테이블을 INNER JOIN 할 때 크기가 가장 큰 테이블을 FROM 절에 , 작은 순서대로 INNER JOIN 절에 놓기

-- 큰 테이블부터 작은 테이블 순으로 INNER JOIN
SELECT b.id, b.title, a.author_name
FROM authors a
INNER JOIN books b ON a.id = b.author_id;

→

-- 작은 테이블부터 큰 테이블 순으로 INNER JOIN
SELECT b.id, b.title, a.author_name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

데이터베이스가 먼저 큰 테이블을 기준으로 작업을 수행하고, 그 후에 작은 테이블을 조인하면서 필요한 데이터를 가져오게 되어 효율성을 높일 수 있다

큰 테이블을 먼저 배치하면, INNER JOIN 시에 작은 테이블의 행을 필터링하여 더 적은 계산을 수행할 수 있기 때문에 성능이 개선될 수 있다.

 

레퍼런스
https://www.analyticsvidhya.com/blog/2021/10/a-detailed-guide-on-sql-query-optimization/ 
https://medium.com/watcha/쿼리-최적화-첫걸음-보다-빠른-쿼리를-위한-7가지-체크-리스트-bafec9d2c073