데이터베이스 코드가 데이터베이스를 잠그는 것을 방지하기 위해 SQL 쿼리 속도를 높이는 팁

데이터베이스 분야는 아직 상대적으로 미숙하기 때문에 모든 플랫폼의 SQL 개발자는 어려움을 겪고 있으며 동일한 실수를 반복하고 있습니다. 물론, 데이터베이스 공급업체는 어느 정도 진전을 이루고 있으며 계속해서 더 큰 문제를 해결하기 위해 노력하고 있습니다.

SQL 개발자가 SQL Server, Oracle, DB2, Sybase, MySQL 또는 기타 관계형 데이터베이스 플랫폼에서 코드를 작성하든, 동시성, 리소스 관리, 공간 관리 및 런타임 속도는 여전히 문제입니다.

문제의 일부는 마법의 총알이 없다는 것입니다. 거의 모든 모범 사례에 대해 적어도 한 가지 예외를 지적할 수 있습니다.

우리는 데이터베이스 튜닝이 예술이자 과학이라고 말합니다. 이는 전반적으로 적용되는 엄격하고 빠른 규칙이 거의 없기 때문에 의미가 있습니다. 한 시스템에서 해결한 문제는 다른 시스템에서는 문제가 되지 않으며 그 반대의 경우도 마찬가지입니다.

쿼리 튜닝에는 정답이 없지만 그렇다고 포기할 필요는 없습니다. 다음 원칙 중 일부를 따르면 좋은 결과를 얻을 수 있기를 바랍니다.

CASE 대신 UPDATE를 사용하지 마세요

이 문제는 매우 일반적이지만 감지하기 어렵습니다. UPDATE를 사용하는 것이 자연스럽고 논리적인 것처럼 보이기 때문에 많은 개발자가 이 문제를 무시하는 경우가 많습니다.

이 시나리오를 예로 들어 보겠습니다. 임시 테이블에 데이터를 삽입하고 다른 값이 있는 경우 특정 값을 표시하려면 해당 값이 필요합니다.

Customer 테이블에서 레코드를 추출하고 $100,000가 넘는 주문을 한 고객을 "선호"로 표시하고 싶을 수도 있습니다.

따라서 테이블에 데이터를 삽입하고 UPDATE 문을 실행한 다음 주문 금액이 $100,000를 초과하는 고객에 대해 CustomerRank 열을 "선호"로 설정합니다.

문제는 UPDATE 문이 로그에 기록된다는 점입니다. 즉, 테이블에 기록될 때마다 두 번 기록된다는 의미입니다.

해결책: SQL 쿼리에 인라인 CASE 문을 사용하면 각 행의 주문 금액 조건을 확인하고 테이블에 쓰기 전에 "선호" 태그를 설정합니다. 처리 성능이 놀랍게 향상됩니다.

맹목적으로 코드를 재사용하지 마세요

이 문제도 매우 일반적입니다. 필요한 데이터를 얻을 수 있다는 것을 알기 때문에 다른 사람이 작성한 코드를 복사하는 것이 쉽습니다.

문제는 필요하지 않은 데이터를 너무 많이 가져오는 경우가 많고 개발자가 이를 간소화하는 경우가 거의 없어 결국 많은 데이터가 발생한다는 것입니다.

이는 일반적으로 WHERE 절의 추가 외부 조인 또는 추가 조건으로 나타납니다. 재사용된 코드를 정확한 요구 사항에 맞게 간소화하면 성능을 크게 향상시킬 수 있습니다.

필요한 만큼 많은 열을 추출하세요

이 질문은 질문 2와 유사하지만 열마다 고유합니다. 열을 하나씩 나열하는 대신 SELECT*를 사용하여 모든 쿼리를 쉽게 코딩할 수 있습니다.

필요하지 않은 데이터를 너무 많이 추출하는 것도 문제입니다. 이런 실수를 수없이 봐왔습니다. 개발자는 수백만 개의 행이 있는 120열 테이블에서 SELECT* 쿼리를 수행하지만 3~5개의 열만 사용합니다.

실제로 필요한 것보다 훨씬 더 많은 데이터를 다루고 있는데, 쿼리가 결과를 반환한다는 것은 기적입니다. 필요하지 않은 데이터를 너무 많이 처리하고 있을 뿐만 아니라 다른 프로세스에서 리소스를 빼앗고 있습니다.

두 번 쿼리하지 마세요(더블 딥)

이것은 많은 사람들이 저지르는 또 다른 실수입니다. 수억 개의 행이 있는 테이블에서 데이터를 추출하기 위해 저장 프로시저를 작성하는 것입니다.

개발자는 캘리포니아에 거주하며 연간 $40,000 이상을 버는 고객에 대한 정보를 추출하고 싶었습니다. 그래서 그는 캘리포니아에 거주하는 고객에게 쿼리를 보내고 쿼리 결과를 임시 테이블에 넣습니다.

그런 다음 연 소득이 $40,000 이상인 고객을 쿼리하고 해당 결과를 다른 임시 테이블에 저장하세요. 마지막으로 그는 두 테이블을 결합하여 최종 결과를 얻습니다.

농담하시나요? 이 작업은 단일 쿼리로 수행해야 하며 대신 매우 큰 테이블을 두 번 쿼리합니다. 어리석게 생각하지 마십시오. 큰 테이블을 한 번만 쿼리하면 저장 프로시저가 훨씬 빠르게 실행된다는 것을 알게 될 것입니다.

약간 다른 시나리오는 프로세스의 여러 단계에 큰 테이블의 하위 집합이 필요하여 매번 큰 테이블이 쿼리되는 경우입니다.

이 문제를 방지하려면 이 하위 집합을 쿼리하고 다른 곳에 유지한 다음 후속 단계를 이 더 작은 데이터 집합에 지시하세요.

임시 테이블을 언제 사용해야 하는지 알아두세요

이 문제는 해결하기가 조금 더 까다롭지만 효과는 상당합니다. 임시 테이블은 큰 테이블이 두 번 쿼리되는 것을 방지하는 등 다양한 상황에서 사용할 수 있습니다. 임시 테이블을 사용하면 대형 테이블을 조인하는 데 필요한 처리 능력을 크게 줄일 수도 있습니다.

테이블을 큰 테이블에 연결해야 하는데 큰 테이블에 조건이 있는 경우, 큰 테이블의 데이터 중 필요한 부분을 임시 테이블로 추출한 후 임시 테이블에 연결하면 됩니다. 쿼리 성능을 향상시킵니다.

이 기능은 동일한 테이블에 대해 유사한 조인을 수행해야 하는 저장 프로시저에 여러 쿼리가 있는 경우에도 유용합니다.

전단계 데이터

이것은 종종 간과되는 오래된 방법이기 때문에 제가 가장 이야기하고 싶은 주제 중 하나입니다.

대형 테이블에서 유사한 조인 작업을 수행하는 보고서나 저장 프로시저(또는 그룹)가 있는 경우 미리 테이블을 조인하고 테이블에 유지하여 데이터를 사전 준비하면 많은 도움이 됩니다.

이제 대규모 조인을 방지하면서 이 사전 준비 테이블에 대해 보고서를 실행할 수 있습니다. 이 방법을 항상 사용할 수는 없지만 일단 사용하고 나면 서버 리소스를 절약할 수 있는 좋은 방법이라는 것을 알게 될 것입니다.

참고: 많은 개발자는 조인 기준을 반복해서 입력할 필요가 없도록 쿼리 자체에 초점을 맞추고 조인을 기반으로 읽기 전용 보기를 생성하여 이 조인 문제를 해결합니다.

그러나 이 접근 방식의 문제점은 쿼리가 필요한 모든 보고서에 대해 여전히 쿼리를 실행해야 한다는 것입니다. 데이터를 사전 준비하는 경우 연결을 한 번만 실행하면(예: 보고서 10분 전) 다른 사람들이 대규모 연결을 피할 수 있습니다.

내가 이 트릭을 얼마나 좋아하는지 모르실 겁니다. 대부분의 환경에서 일반적으로 사용되는 일부 테이블은 항상 연결되어 있으므로 먼저 미리 준비하지 못할 이유가 없습니다.

일괄 삭제 및 업데이트

이것은 종종 간과되는 또 다른 팁입니다. 대규모 테이블에서 대량의 데이터를 삭제하거나 업데이트하는 것은 올바르게 수행하지 않으면 악몽이 될 수 있습니다.

문제는 두 문이 모두 단일 트랜잭션으로 실행된다는 것입니다. 이를 종료해야 하거나 실행 중 시스템에 문제가 발생하는 경우 시스템은 전체 트랜잭션을 롤백해야 하므로 시간이 오래 걸립니다.

이러한 작업은 해당 기간 동안 다른 트랜잭션도 차단하므로 실제로 시스템에 병목 현상이 발생합니다. 해결 방법은 작은 배치로 삭제하거나 업데이트하는 것입니다.

이 문제는 여러 가지 방법으로 해결됩니다.

트랜잭션이 종료된 이유에 관계없이 롤백해야 할 행의 수가 적으므로 데이터베이스가 훨씬 빠르게 온라인 상태로 돌아옵니다.

소규모 배치 트랜잭션이 디스크에 커밋되면 다른 트랜잭션이 들어와 일부 작업을 처리할 수 있으므로 동시성이 크게 향상됩니다.

마찬가지로 많은 개발자들은 이러한 삭제 및 업데이트 작업이 같은 날 완료되어야 한다고 고집해 왔습니다. 특히 보관하는 경우 항상 그런 것은 아닙니다.

작업을 확장해야 하는 경우 그렇게 할 수 있습니다. 이러한 집중적인 작업을 수행하는 데 시간이 더 오래 걸리더라도 시스템 속도를 늦추지 마십시오.

임시 테이블을 사용하여 커서 성능 향상

가능하면 커서를 피하는 것이 가장 좋습니다. 커서는 그 자체로 많은 작업에서 큰 문제가 되는 속도 문제를 가질 뿐만 아니라 작업이 오랫동안 다른 작업을 차단하게 하여 시스템의 동시성을 크게 감소시킬 수도 있습니다.

그러나 커서 사용을 피할 수 없는 경우에는 대신 임시 테이블에서 커서 작업을 수행하여 커서로 인한 성능 문제를 제거할 수 있습니다.

예를 들어 테이블을 조회하고 일부 비교 결과에 따라 여러 열의 커서를 업데이트합니다. 해당 데이터를 임시 테이블에 넣고 활성 테이블 대신 임시 테이블과 비교할 수 있습니다.

그런 다음 훨씬 더 작고 짧게 잠긴 활성 테이블에 대해 단일 UPDATE 문을 실행할 수 있습니다.

이러한 데이터 수정을 수행하면 동시성을 크게 향상시킬 수 있습니다. 커서를 전혀 사용할 필요가 없으며 항상 컬렉션 기반 솔루션이 있다는 말로 마무리하겠습니다.

테이블 값 함수 사용

이것은 전문가만이 아는 비밀이기 때문에 제가 가장 좋아하는 기술 중 하나입니다.

쿼리의 SELECT 목록에서 스칼라 함수를 사용하면 결과 집합의 각 행에 대해 함수가 호출되므로 대규모 쿼리의 성능이 크게 저하될 수 있습니다.

그러나 스칼라 함수를 테이블 반환 함수로 변환한 다음 쿼리에서 CROSS APPLY를 사용하면 성능이 크게 향상될 수 있습니다.

동일한 배치에서 많은 테이블에 대해 대규모 작업을 수행하지 마세요.

이것은 명백해 보일 수도 있지만 그렇지 않습니다. 요점을 더 잘 설명하기 때문에 또 다른 생생한 예를 사용하겠습니다.

시스템이 차단되어 많은 작업이 중단되었습니다. 하루에 여러 번 실행된 삭제 루틴이 명시적 트랜잭션에서 14개 테이블의 데이터를 삭제하는 것으로 나타났습니다. 한 번의 트랜잭션으로 14개 테이블을 모두 처리한다는 것은 모든 삭제가 완료될 때까지 각 테이블을 잠그는 것을 의미합니다.

해결책은 각 테이블 삭제를 별도의 트랜잭션으로 나누어 각 삭제 트랜잭션이 하나의 테이블만 잠그도록 하는 것입니다.

이렇게 하면 다른 테이블이 확보되어 막힘이 완화되고 다른 작업이 계속 실행될 수 있습니다. 차단을 방지하려면 항상 이와 같은 대규모 트랜잭션을 별도의 작은 트랜잭션으로 나누어야 합니다.

트리거를 사용하지 마세요

이것은 이전 것과 거의 동일하지만 여전히 언급할 가치가 있습니다. 트리거 문제: ​​트리거에서 수행하려는 모든 작업은 원래 작업과 동일한 트랜잭션에서 수행됩니다.

Orders 테이블의 행을 업데이트하는 동안 다른 테이블에 데이터를 삽입하는 트리거를 작성하면 트리거가 완료될 때까지 두 테이블이 모두 잠깁니다.

업데이트 후 다른 테이블에 데이터를 삽입해야 하는 경우 업데이트를 저장 프로시저에 삽입하고 별도의 트랜잭션으로 실행하세요.

롤백해야 하는 경우 두 테이블을 동시에 잠그지 않고도 쉽게 롤백할 수 있습니다. 항상 그렇듯이 트랜잭션을 짧게 유지하고 한 번에 여러 리소스를 잠그지 마십시오.

GUID를 클러스터링하지 마세요

수년이 지난 지금도 우리가 여전히 이 문제로 어려움을 겪고 있다는 사실이 믿기지 않습니다. 하지만 여전히 1년에 두 번 이상 클러스터형 GUID가 발생합니다.

GUID(Globally Unique Identifier)는 무작위로 생성된 16바이트 숫자입니다. 이 열을 기준으로 테이블의 데이터를 정렬하면 DATE 또는 IDENTITY와 같이 꾸준히 증가하는 값을 사용하는 것보다 테이블 조각화 속도가 훨씬 빨라집니다.

몇 년 전에 클러스터형 GUID가 있는 테이블에 많은 데이터를 삽입하고 IDENTITY 열이 있는 다른 테이블에 동일한 데이터를 삽입하는 벤치마크를 수행했습니다.

GUID 테이블은 극도로 조각화되어 단 15분 만에 성능이 수천 퍼센트 포인트 떨어졌습니다.

5시간 후 IDENTITY 테이블의 성능은 몇 퍼센트 포인트만 떨어졌으며 이는 GUID에만 적용되는 것이 아니라 모든 휘발성 열에 적용됩니다.

데이터가 존재하는지만 확인하고 싶다면 행 수를 세지 마세요

이런 상황은 매우 흔한 상황이므로 테이블에 데이터가 존재하는지 확인해야 하며, 이 확인 결과에 따라 원하는 특정 작업을 수행합니다.

데이터가 존재하는지 확인하기 위해 SELECT COUNT(*) FROMdbo.T1을 실행하는 사람들을 자주 봅니다.

1. SET @CT=(SELECT COUNT(*) FROM

2. dbo.T1)

3. If@CT>0

4. BEGIN

6. END

이는 전혀 필요하지 않습니다. 데이터가 존재하는지 확인하려면 다음을 수행하세요.

1. 존재하는 경우(dbo.T1에서 1 선택)

2. BEGIN

4. END

테이블의 모든 항목을 계산하지 말고 첫 번째 행을 다시 가져오세요. 찾다. SQL Server는 EXISTS를 올바르게 사용할 수 있을 만큼 똑똑하며 두 번째 코드 부분은 결과를 매우 빠르게 반환합니다.

테이블이 클수록 이 부분의 차이는 더욱 확연해집니다. 데이터가 너무 커지기 전에 올바른 일을 하십시오. 데이터베이스를 튜닝하기에 너무 이른 때는 없습니다.

저는 실제로 2억 7천만 행이 있는 테이블을 대상으로 프로덕션 데이터베이스 중 하나에서 이 예제를 실행했습니다.

첫 번째 쿼리는 15초가 걸렸고 456197개의 논리적 읽기가 포함되었습니다. 두 번째 쿼리는 1초 이내에 결과를 반환했으며 5개의 논리적 읽기만 포함했습니다.

그러나 테이블의 행 수를 정말로 계산해야 하고 테이블이 큰 경우 또 다른 방법은 시스템 테이블에서 추출하는 것입니다.

sysindexes에서 행을 선택하면 모든 인덱스의 행 수를 얻을 수 있습니다.

그리고 클러스터형 인덱스는 데이터 자체를 나타내므로 WHERE indid = 1을 추가하여 테이블 행을 가져온 다음 테이블 이름만 포함하면 됩니다.

그래서 최종 쿼리는 다음과 같습니다.

1.object_name(id)='T1'이고 indexid =1인 sysindexes에서 행을 선택합니다.

2억 7천만 행으로 구성된 테이블에서 결과는 1초 이내에 반환됩니다. , 6개의 논리적 읽기만 사용하면 성능이 달라집니다.

역방향 검색을 수행하지 마세요

간단한 쿼리 SELECT * FROMCustomers WHERE RegionID 이 쿼리는 테이블 스캔을 통해 행별 비교가 필요한 역방향 검색이므로 인덱스를 사용할 수 없습니다. 이러한 작업을 수행해야 하는 경우 인덱스를 사용하도록 쿼리를 다시 작성하면 성능이 훨씬 더 좋아질 수 있습니다.

쿼리는 다음과 같이 쉽게 다시 작성할 수 있습니다.

1.SELECT * FROM Customers WHERE RegionID

이 쿼리는 인덱스를 사용하므로 데이터세트가 큰 경우 성능은 테이블 스캔 버전보다 훨씬 뛰어납니다.

물론 그렇게 쉬운 게 없고 성능이 더 안 좋을 수도 있으니 사용하기 전에 꼭 한번 해보세요. 관련된 요소가 너무 많지만 100% 작동합니다.

마침내 저는 이 쿼리가 규칙 4번을 위반했음을 깨달았습니다. 두 번 쿼리하지 마세요. 하지만 이는 또한 엄격하고 빠른 규칙이 없다는 것을 보여줍니다. 여기서는 두 번 쿼리하지만 비용이 많이 드는 테이블 스캔을 피하기 위해 이렇게 합니다.

이 팁을 모두 사용할 수는 없겠지만, 염두에 둔다면 언젠가 큰 문제를 해결하는 데 사용할 수 있을 것입니다.

가장 기억해야 할 중요한 점은 제가 말하는 것을 교리로 받아들이지 마세요. 실제 환경에서 시도해 보십시오. 동일한 솔루션이 모든 경우에 작동하지는 않지만 성능 저하 문제를 해결할 때 항상 사용하는 솔루션이며 항상 작동합니다.