개발자를 위한 SQL 데이터베이스 성능 조정

SQL 성능 튜닝은 특히 가장 사소한 변경이라도 성능에 극적인(긍정적 또는 부정적) 영향을 미칠 수 있는 대규모 데이터로 작업할 때 매우 어려운 작업이 될 수 있습니다.

중간 규모 및 대기업에서 대부분의 SQL 성능 조정은 데이터베이스 관리자(DBA)가 처리합니다. 하지만 DBA와 같은 작업을 수행해야 하는 개발자가 많이 있습니다. 또한 DBA 가 있는 많은 회사에서 종종 개발자와 잘 협력하기 위해 고군분투합니다. 직책에는 단순히 다른 방식의 문제 해결이 필요하기 때문에 동료 간에 의견 불일치가 발생할 수 있습니다.

또한 기업 구조도 역할을 할 수 있습니다. DBA 팀은 모든 데이터베이스가 있는 10층에 있고 개발자는 15층에 있거나 완전히 별도의 보고 구조 아래 다른 건물에 있다고 가정해 보겠습니다. 이러한 조건에서 원활하게 협력하기는 확실히 어렵습니다.

이 글에서 저는 두 가지를 달성하고자 합니다.

1. 개발자에게 개발자 측 SQL 성능 조정 기술을 제공합니다.
2. 개발자와 DBA가 효과적으로 협력할 수 있는 방법을 설명합니다.

SQL 성능 튜닝(코드베이스에서): 인덱스

데이터베이스를 처음 접하고 스스로에게 "SQL 성능 조정이란 무엇입니까?"라고 묻는 경우에도 인덱싱은 개발 중에 종종 무시되는 SQL 데이터베이스를 조정하는 효과적인 방법이라는 것을 알아야 합니다. 기본적으로 인덱스는 빠른 랜덤 조회와 정렬된 레코드에 대한 효율적인 액세스를 제공하여 데이터베이스 테이블에 대한 데이터 검색 작업의 속도를 향상시키는 데이터 구조입니다. 즉, 인덱스를 만든 후에는 이전보다 더 빠르게 행을 선택하거나 정렬할 수 있습니다.

인덱스는 또한 다른 열에 동일한 값이 없음을 보장하는 기본 키 또는 고유 인덱스를 정의하는 데 사용됩니다. 물론 데이터베이스 인덱싱은 이 간단한 설명으로 정의할 수 없는 광대하고 흥미로운 주제입니다(여기에 더 자세한 설명이 있습니다).

인덱스를 처음 사용하는 경우 쿼리를 구성할 때 다음 다이어그램을 사용하는 것이 좋습니다.

기본적으로 목표는 주요 검색 및 정렬 열을 인덱싱하는 것입니다.

테이블이 INSERT , UPDATE 및 DELETE 에 의해 지속적으로 손상되면 인덱싱할 때 주의해야 합니다. 이러한 작업 후에 모든 인덱스를 수정해야 하므로 결국 성능이 저하될 수 있습니다.

또한 DBA는 삽입 프로세스의 속도를 높이기 위해 백만 개 이상의 행을 일괄 삽입하기 전에 SQL 인덱스를 삭제하는 경우가 많습니다. 배치가 삽입된 후 인덱스를 다시 생성합니다. 그러나 인덱스 삭제는 해당 테이블에서 실행 중인 모든 쿼리에 영향을 미칩니다. 따라서 이 접근 방식은 단일 대규모 삽입으로 작업할 때만 권장됩니다.

SQL 튜닝: SQL Server의 실행 계획

그건 그렇고: SQL Server의 실행 계획 도구는 인덱스를 만드는 데 유용할 수 있습니다.

주요 기능은 SQL Server 쿼리 최적화 프로그램에서 선택한 데이터 검색 방법을 그래픽으로 표시하는 것입니다. 이전에 본 적이 없다면 자세한 설명이 있습니다.

SQL Server Management Studio에서 실행 계획을 검색하려면 쿼리를 실행하기 전에 "실제 실행 계획 포함"(CTRL + M)을 클릭하기만 하면 됩니다.

그런 다음 "실행 계획"이라는 세 번째 탭이 나타납니다. 발견된 누락된 색인을 볼 수 있습니다. 생성하려면 실행 계획을 마우스 오른쪽 버튼으로 클릭하고 "Missing Index Details..."를 선택하십시오. 그것만큼 간단합니다!

( 확대하려면 클릭 )

SQL 튜닝: 코딩 루프 방지

1000개의 쿼리가 데이터베이스를 순서대로 망치는 시나리오를 상상해 보십시오. 다음과 같은 것:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

코드에서 이러한 루프를 피해야 합니다. 예를 들어, 여러 행과 값이 있는 고유한 INSERT 또는 UPDATE 문을 사용하여 위의 스니펫을 변환할 수 있습니다.

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

WHERE 절이 기존 값과 일치하는 경우 저장된 값을 업데이트하지 않도록 하십시오. 이러한 사소한 최적화는 수천 개의 행이 아닌 수백 개의 행만 업데이트함으로써 SQL 쿼리 성능을 극적으로 향상시킬 수 있습니다. 예를 들어:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

SQL 튜닝: 상관 관계가 있는 SQL 하위 쿼리 피하기

상관 하위 쿼리는 상위 쿼리의 값을 사용하는 하위 쿼리입니다. 이러한 종류의 SQL 쿼리는 외부 쿼리에서 반환된 각 행에 대해 한 번씩 행별로 실행되는 경향이 있으므로 SQL 쿼리 성능이 저하됩니다. 새로운 SQL 개발자는 이러한 방식으로 쿼리를 구성하는 경우가 종종 있습니다. 왜냐하면 일반적으로 쉬운 경로이기 때문입니다.

다음은 상관 하위 쿼리의 예입니다.

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

특히 문제는 외부 쿼리( SELECT c.Name… )에서 반환된 각 행에 대해 내부 쿼리( SELECT CompanyName… )가 실행된다는 것입니다. 그러나 외부 쿼리에서 처리하는 모든 행에 대해 Company 를 계속해서 살펴보는 이유는 무엇입니까?

보다 효율적인 SQL 성능 튜닝 기술은 상관 하위 쿼리를 조인으로 리팩토링하는 것입니다.

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

이 경우 처음에 Company 테이블을 한 번만 살펴보고 Customer 테이블과 JOIN합니다. 그때부터 필요한 값( co.CompanyName )을 보다 효율적으로 선택할 수 있습니다.

SQL 튜닝: 신중하게 선택

내가 가장 좋아하는 SQL 최적화 팁 중 하나는 SELECT * 를 피하는 것입니다! 대신 필요한 특정 열을 개별적으로 포함해야 합니다. 다시 말하지만 이것은 간단하게 들리지만 여기저기서 이 오류를 볼 수 있습니다. 수백 개의 열과 수백만 개의 행이 있는 테이블을 생각해 보십시오. 애플리케이션에 실제로 몇 개의 열만 필요한 경우 모든 데이터를 쿼리하는 것은 의미가 없습니다. 엄청난 자원 낭비입니다. ( 더 많은 문제는 여기를 참조하세요. )

예를 들어:

 SELECT * FROM Employees

대

 SELECT FirstName, City, Country FROM Employees

모든 열이 정말로 필요한 경우 모든 열을 명시적으로 나열하십시오. 이것은 많은 규칙이 아니라 미래의 시스템 오류 및 추가 SQL 성능 튜닝을 방지하기 위한 수단입니다. 예를 들어 INSERT... SELECT... 를 사용하고 있고 새 열을 추가하여 원본 테이블이 변경된 경우 대상 테이블에서 해당 열이 필요하지 않더라도 문제가 발생할 수 있습니다. 예:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

SQL Server에서 이러한 종류의 오류를 방지하려면 각 열을 개별적으로 선언해야 합니다.

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

그러나 SELECT * 를 사용하는 것이 적절할 수 있는 몇 가지 상황이 있습니다. 예를 들어 임시 테이블의 경우 다음 주제로 이어집니다.

SQL 튜닝: 임시 테이블의 현명한 사용(#Temp)

임시 테이블은 일반적으로 쿼리의 복잡성을 증가시킵니다. 코드를 간단하고 직관적인 방식으로 작성할 수 있다면 임시 테이블을 사용하지 않는 것이 좋습니다.

그러나 단일 쿼리로 처리 할 수 없는 일부 데이터 조작이 포함된 저장 프로시저가 있는 경우 임시 테이블을 중개자로 사용하여 최종 결과를 생성하는 데 도움을 줄 수 있습니다.

큰 테이블을 조인해야 하고 해당 테이블에 조건이 있는 경우 임시 테이블에 데이터를 전송한 다음 해당 테이블에 조인하여 데이터베이스 성능을 높일 수 있습니다. 임시 테이블에는 원본(큰) 테이블보다 행 수가 적으므로 조인이 더 빨리 완료됩니다!

결정이 항상 간단하지는 않지만 이 예는 임시 테이블을 사용하려는 상황에 대한 이해를 제공합니다.

수백만 개의 레코드가 있는 고객 테이블을 상상해 보십시오. 특정 지역에 가입해야 합니다. SELECT INTO 문을 사용한 다음 임시 테이블과 조인하여 이를 달성할 수 있습니다.

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( 참고: 일부 SQL 개발자는 SELECT INTO 를 사용하여 임시 테이블을 생성하는 것을 피합니다. 이 명령은 tempdb 데이터베이스를 잠그고 다른 사용자가 임시 테이블을 생성할 수 없도록 합니다. 다행히도 이것은 7.0 이상에서 수정되었습니다 .)

임시 테이블의 대안으로 하위 쿼리를 테이블로 사용하는 것을 고려할 수 있습니다.

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

하지만 기다려! 이 두 번째 쿼리에 문제가 있습니다. 위에서 설명한 것처럼 하위 쿼리에 필요한 열만 포함해야 합니다(즉, SELECT * 를 사용하지 않음). 이를 고려하면:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

이러한 모든 SQL 조각은 동일한 데이터를 반환합니다. 그러나 임시 테이블을 사용하면 예를 들어 임시 테이블에 인덱스를 만들어 성능을 향상시킬 수 있습니다. 여기에 임시 테이블과 하위 쿼리의 차이점에 대한 좋은 토론이 있습니다.

마지막으로 임시 테이블 작업이 끝나면 자동으로 삭제되기를 기다리지 말고(데이터베이스에 대한 연결이 종료될 때처럼) 삭제하여 tempdb 리소스를 지우십시오.

 DROP TABLE #temp

SQL 튜닝: "내 레코드가 존재합니까?"

이 SQL 최적화 기술은 EXISTS() 사용에 관한 것입니다. 레코드가 존재하는지 확인하려면 COUNT() EXISTS() 를 사용하십시오. COUNT() 가 전체 테이블을 스캔하여 조건과 일치하는 모든 항목을 세는 동안 EXISTS() 는 필요한 결과를 보는 즉시 종료됩니다. 이렇게 하면 성능이 향상되고 코드가 더 명확해집니다.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

대

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

SQL Server 2016을 사용한 SQL 성능 튜닝

SQL Server 2016으로 작업하는 DBA는 알고 있을 가능성이 높기 때문에 이 버전은 기본값 및 호환성 관리에서 중요한 변화를 표시했습니다. 물론 주요 버전으로서 새로운 쿼리 최적화와 함께 제공되지만 사용 여부에 대한 제어는 이제 sys.databases.compatibility_level 을 통해 간소화되었습니다.

SQL 성능 튜닝(사무실에서)

SQL 데이터베이스 관리자(DBA)와 개발자는 종종 데이터 및 비데이터 관련 문제로 충돌합니다. 내 경험을 바탕으로 효과적으로 협력하고 협력하는 방법에 대한 몇 가지 팁(양 당사자를 위한)이 있습니다.

트위터

개발자를 위한 데이터베이스 최적화:

1. 애플리케이션이 갑자기 작동을 멈추면 데이터베이스 문제가 아닐 수 있습니다. 예를 들어 네트워크 문제가 있을 수 있습니다. DBA를 비난하기 전에 조금 조사하십시오!

2. 당신이 닌자 SQL 데이터 모델러라도 관계형 다이어그램에 대해 DBA에게 도움을 요청하십시오. 그들은 공유하고 제안할 것이 많습니다.

3. DBA는 급격한 변화를 좋아하지 않습니다. 이는 자연스러운 일입니다. 데이터베이스를 전체적으로 분석하고 모든 각도에서 변경 사항의 영향을 조사해야 합니다. 열의 간단한 변경은 구현하는 데 일주일이 걸릴 수 있지만 오류가 회사에 큰 손실로 구체화될 수 있기 때문입니다. 인내심을 가지세요!

4. SQL DBA에게 프로덕션 환경에서 데이터 변경을 요청하지 마십시오. 프로덕션 데이터베이스에 액세스하려면 모든 변경 사항에 대한 책임을 져야 합니다.

SQL Server DBA를 위한 데이터베이스 최적화:

1. 데이터베이스에 대해 묻는 사람들이 마음에 들지 않으면 실시간 상태 패널을 제공하십시오. 개발자는 항상 데이터베이스의 상태를 의심하며 이러한 패널은 모든 사람의 시간과 에너지를 절약할 수 있습니다.

2. 테스트/품질 보증 환경에서 개발자를 돕습니다. 실제 데이터에 대한 간단한 테스트를 통해 프로덕션 서버를 쉽게 시뮬레이션할 수 있습니다. 이것은 다른 사람들과 당신 자신을 위해 상당한 시간 절약이 될 것입니다.

3. 개발자는 비즈니스 로직이 자주 변경되는 시스템에서 하루 종일 시간을 보냅니다. 이 세상이 더 유연해지고 결정적인 순간에 몇 가지 규칙을 어길 수 있다는 것을 이해하려고 노력하십시오.

4. SQL 데이터베이스는 진화합니다. 데이터를 새 버전으로 마이그레이션해야 하는 날이 올 것입니다. 개발자는 각각의 새 버전에서 중요한 새 기능을 기대합니다. 변경 사항 수락을 거부하는 대신 미리 계획하고 마이그레이션을 준비하십시오.