SQL 성능 최적화 방법과 실용적인 예시

SQL 쿼리 성능 최적화는 데이터베이스를 효과적으로 운영하는 데 매우 중요합니다. 쿼리의 속도가 느리면 전체 시스템 성능에 큰 영향을 미치기 때문에, 성능을 최적화하기 위한 다양한 방법들을 적용할 수 있습니다. 이 글에서는 SQL 성능을 최적화하는 방법을 여러 가지 설명하고, 각 방법에 대한 실제 예시도 제공합니다.

1. 인덱스 최적화

인덱스는 데이터베이스 테이블에서 특정 데이터를 빠르게 조회할 수 있도록 도와주는 중요한 요소입니다. 적절한 인덱스를 추가하면 쿼리 성능을 크게 향상시킬 수 있습니다. 특히, WHERE 절이나 JOIN에서 자주 사용되는 컬럼에 인덱스를 추가하면 성능이 개선됩니다.

예시:

인덱스 추가:

sql
코드 복사
-- `ItemSeq`와 `AmdSeq` 컬럼에 인덱스 추가
CREATE INDEX IX_ItemSeq_AmdSeq ON thegns_TPLMMaterials(ItemSeq, AmdSeq);

위 예시처럼 ItemSeq와 AmdSeq 컬럼을 자주 조회하거나 JOIN에 사용한다면 해당 컬럼에 인덱스를 추가하는 것이 좋습니다.

인덱스 제거:

sql
코드 복사
-- 사용되지 않는 인덱스를 제거하여 불필요한 성능 저하 방지
DROP INDEX IX_ItemSeq_AmdSeq ON thegns_TPLMMaterials;

인덱스는 디스크 공간을 차지하며, 과도한 인덱스는 오히려 성능을 저하시킬 수 있기 때문에 자주 사용되지 않는 인덱스는 주기적으로 제거하는 것이 중요합니다.

2. 쿼리 리팩토링 (Refactoring)

쿼리 리팩토링은 쿼리의 구조를 변경하여 성능을 향상시키는 방법입니다. 복잡한 서브쿼리나 중복된 조건들을 개선하여 쿼리를 간결하고 효율적으로 바꿀 수 있습니다.

예시:

서브쿼리 최적화:

서브쿼리를 사용한 예시:

sql
코드 복사
SELECT
    A.ItemSeq,
    (SELECT MAX(HandAmd) FROM thegns_TPLMMaterialsHandWriteAmd WHERE AmdSeq = X1.AmdSeq) AS LastHandAmd
FROM
    thegns_TPLMMaterials AS A;

서브쿼리를 JOIN으로 변환한 예시:

sql
코드 복사
SELECT
    A.ItemSeq,
    MAX(E.HandAmd) AS LastHandAmd
FROM
    thegns_TPLMMaterials AS A
LEFT JOIN
    thegns_TPLMMaterialsHandWriteAmd AS E ON A.AmdSeq = E.AmdSeq
GROUP BY
    A.ItemSeq;

서브쿼리를 JOIN으로 바꾸면 중복된 데이터 조회를 줄여 성능을 향상시킬 수 있습니다. MAX()나 COUNT()와 같은 집계 함수와 함께 JOIN을 사용하는 방식이 더욱 효율적일 수 있습니다.

3. JOIN 최적화

JOIN을 사용할 때 불필요한 LEFT JOIN이나 비효율적인 JOIN은 성능을 저하시킬 수 있습니다. 필요한 데이터만을 조회하도록 JOIN 조건을 최적화하고, 불필요한 JOIN은 제거하는 것이 좋습니다.

예시:

불필요한 LEFT JOIN 제거 전:

sql
코드 복사
SELECT
    A.ItemSeq,
    B.ItemName
FROM
    thegns_TPLMMaterials AS A
LEFT JOIN
    _TDAItem AS B ON A.ItemSeq = B.ItemSeq
LEFT JOIN
    _TDAEmp AS C ON A.CompanySeq = C.CompanySeq;

불필요한 LEFT JOIN 제거 후:

sql
코드 복사
SELECT
    A.ItemSeq,
    B.ItemName
FROM
    thegns_TPLMMaterials AS A
JOIN
    _TDAItem AS B ON A.ItemSeq = B.ItemSeq;

JOIN에서 불필요한 테이블을 제거하거나 INNER JOIN을 사용하여 성능을 개선할 수 있습니다. 또한, LEFT JOIN을 사용할 경우 실제로 필요한 경우에만 사용하는 것이 좋습니다.

4. WITH(NOLOCK) 사용

WITH(NOLOCK)은 데이터 조회 시 테이블의 잠금을 방지하여 성능을 개선할 수 있습니다. 하지만 이는 데이터 일관성에 영향을 미칠 수 있으므로 사용에 주의가 필요합니다.

예시:

sql
코드 복사
SELECT
    A.ItemSeq,
    B.ItemName
FROM
    thegns_TPLMMaterials AS A
WITH (NOLOCK)
INNER JOIN
    _TDAItem AS B WITH (NOLOCK) ON A.ItemSeq = B.ItemSeq;

WITH(NOLOCK)을 사용하면 데이터 읽기 작업에서 잠금을 방지하여 성능을 개선할 수 있습니다. 그러나 데이터 일관성이 중요한 경우에는 사용을 피해야 합니다. 예를 들어, 실시간 트랜잭션 데이터의 정확성이 중요하다면 WITH(NOLOCK)을 사용하지 않는 것이 좋습니다.

5. 쿼리 계획 분석 (Execution Plan)

SQL Server에서는 쿼리 실행 계획(Execution Plan)을 통해 쿼리가 어떻게 실행되는지 분석할 수 있습니다. 쿼리 성능을 최적화하려면 Execution Plan을 통해 비효율적인 부분을 찾아 개선할 수 있습니다.

예시:

SQL Server Management Studio(SSMS)에서 실행 계획을 분석하려면, 쿼리 실행 전에 "실행 계획 보기"를 활성화하고, 실행 후 나타나는 실행 계획을 분석합니다. Index Scan이나 Table Scan이 발생하는 부분은 인덱스를 추가하거나 JOIN을 최적화하여 개선할 수 있습니다.

6. 데이터 집합 축소

쿼리에서 필요한 데이터만을 조회하는 것이 성능을 개선하는 중요한 방법입니다. 불필요한 데이터를 조회하지 않도록 SELECT *를 피하고, 필요한 컬럼만 조회하도록 쿼리를 작성해야 합니다.

예시:

불필요한 데이터 조회 전:

sql
코드 복사
SELECT *
FROM thegns_TPLMMaterials
WHERE ItemSeq = @ItemSeq;

필요한 데이터만 조회 후:

sql
코드 복사
SELECT ItemSeq, ItemName, Spec
FROM thegns_TPLMMaterials
WHERE ItemSeq = @ItemSeq;

SELECT *를 사용하면 테이블의 모든 컬럼을 조회하게 되는데, 실제로 필요한 컬럼만 조회하는 것이 성능을 크게 향상시킬 수 있습니다.

7. 쿼리 캐시 활용

SQL Server는 동일한 쿼리가 반복 실행되면 결과를 캐시하여 성능을 개선합니다. 자주 변경되지 않는 데이터에 대해 쿼리 캐시를 활용하면 성능이 향상됩니다.

예시:

sql
코드 복사
SELECT ItemSeq, ItemName, Spec
FROM thegns_TPLMMaterials
WHERE ItemSeq = @ItemSeq;

자주 조회되는 데이터에 대해서는 캐시가 성능을 향상시키는데 도움이 됩니다. 데이터가 자주 변경되지 않는 경우, 쿼리 캐시를 적극 활용하면 쿼리 성능이 개선됩니다.

8. 서버 및 데이터베이스 최적화

서버나 데이터베이스 설정을 최적화하는 것도 성능을 개선하는 중요한 방법입니다. max server memory나 max degree of parallelism 등의 설정을 조정하여 데이터베이스 성능을 최적화할 수 있습니다.

예시:

sql
코드 복사
-- 메모리 설정
EXEC sp_configure 'max server memory', 8192;  -- 메모리 8GB로 설정
RECONFIGURE;

-- 병렬 처리 설정
EXEC sp_configure 'max degree of parallelism', 4;  -- 최대 병렬 처리 4로 설정
RECONFIGURE;

max server memory는 SQL Server에서 사용할 수 있는 최대 메모리 양을 설정하는 옵션입니다. max degree of parallelism은 쿼리 실행 시 사용할 수 있는 CPU의 수를 설정하는 옵션입니다. 이 설정을 통해 쿼리 성능을 최적화할 수 있습니다.

9. 분할된 테이블 사용 (Partitioning)

대규모 테이블을 분할하여 성능을 최적화할 수 있습니다. 파티셔닝을 통해 테이블을 특정 기준에 따라 물리적으로 나누면 쿼리 성능을 크게 향상시킬 수 있습니다.

예시:

sql
코드 복사
-- 1년 단위로 파티셔닝 예시
CREATE PARTITION FUNCTION YearlyPartitionFunction (int)
AS RANGE RIGHT FOR VALUES (20210101, 20220101, 20230101);

CREATE PARTITION SCHEME YearlyPartitionScheme
AS PARTITION YearlyPartitionFunction
TO (Primary, Year_2022, Year_2023);

파티셔닝을 활용하면, 대용량 테이블에서 데이터를 나누어 처리할 수 있어 쿼리 성능을 향상시킬 수 있습니다.

10. 쿼리 리팩토링 사례

서브쿼리나 복잡한 쿼리 구문을 리팩토링하여 성능을 최적화할 수 있습니다.

예시:

서브쿼리 사용 전:

sql
코드 복사
SELECT
    A.ItemSeq,
    (SELECT MAX(HandAmd) FROM thegns_TPLMMaterialsHandWriteAmd WHERE AmdSeq = X1.AmdSeq) AS LastHandAmd
FROM
    thegns_TPLMMaterials AS A;

JOIN을 사용한 리팩토링 예시:

sql
코드 복사
SELECT
    A.ItemSeq,
    MAX(E.HandAmd) AS LastHandAmd
FROM
    thegns_TPLMMaterials AS A
LEFT JOIN
    thegns_TPLMMaterialsHandWriteAmd AS E ON A.AmdSeq = E.AmdSeq
GROUP BY
    A.ItemSeq;

---

결론

SQL 성능 최적화는 데이터베이스의 효율적인 운영과 빠른 데이터 처리를 위해 매우 중요합니다. 인덱스 추가, 쿼리 리팩토링, 불필요한 JOIN 제거, 서버 설정 최적화 등 다양한 방법을 통해 쿼리 성능을 개선할 수 있습니다. 이 글에서 소개한 방법들을 상황에 맞게 적용하여 SQL 성능을 최적화하고, 더 효율적인 데이터베이스 운영을 할 수 있습니다.