SQL 쿼리문 최적화

목차

1. 실행계획이란? 실행 계획은 어떤 정보에 의존합니까?
2. SQL 문 작성 방법을 통일하여 구문 분석 오버헤드를 줄입니다
3. SQL 문 중첩을 줄입니다
4. OLTP 시스템 SQL을 임시로 저장합니다. 명령문은 바인딩 고정 변수를 사용해야 합니다
6. 기울어진 필드의 바인딩 변수 스누핑 문제
7. start tran의 트랜잭션은 가능한 작아야 합니다.
8. 일부 SQL 쿼리문에는 Nolock을 추가해야 합니다
9. nolock을 추가한 후 페이지 분할이 자주 발생하는 테이블을 쿼리하면 건너뛰거나 반복되는 읽기가 발생할 수 있습니다
10. 테이블의 순차 필드. , 테이블은 페이지 분할이 발생하기 쉽습니다
11. 복합 인덱스를 사용하여 여러 where 조건의 쿼리 속도를 향상합니다
13. 퍼지 쿼리에 like를 사용하지 않도록 주의하세요. %
14. SQL Server 테이블을 연결하는 세 가지 방법
15. Row_number는 테이블 스캔을 유발하므로 임시 테이블 페이징을 사용하는 것이 좋습니다

실행 계획은 무엇입니까? 실행 계획은 어떤 정보에 의존합니까?

실행 계획은 SQL 문과 관련 테이블의 통계 정보를 기반으로 데이터베이스가 작성하는 쿼리 계획입니다. 예를 들어 SQL 문을 사용하는 경우 이 계획은 쿼리 최적화 프로그램에서 자동으로 분석됩니다. extract 100,000 records 테이블에서 1개의 레코드를 검색하기 위해 쿼리 최적화 프로그램은 "인덱스 검색" 방법을 선택합니다. 테이블이 아카이브되고 5,000개의 레코드만 남아 있으면 쿼리 최적화 프로그램은 계획을 변경하고 "전체 테이블 스캔"을 사용합니다. " "방법.

실행 계획이 정해져 있지 않고 '개인화'되어 있음을 알 수 있습니다. 올바른 "실행 계획"을 생성하는 데는 두 가지 중요한 사항이 있습니다.

SQL 문이 쿼리 최적화 프로그램에 수행하려는 작업을 명확하게 전달합니까?
쿼리 최적화 프로그램으로 얻은 데이터베이스 통계가 최신이고 정확합니까?

파싱 오버헤드를 줄이기 위해 SQL 문 작성 방법을 통일

다음 두 SQL 문에 대해 프로그래머는 동일한 것으로 생각하지만 데이터베이스 쿼리 최적화 프로그램은 서로 다른 것으로 생각할 수 있습니다.

select * from Dual

Select * From Dual

실제로 쿼리 분석기는 이를 두 개의 다른 SQL 문으로 간주하여 두 번 구문 분석해야 합니다. . 2개의 실행 계획을 생성합니다. 따라서 프로그래머로서 동일한 쿼리 문이 모든 곳에서 일관성을 유지하는지 확인해야 합니다. 공백이 하나 더 있어도 작동하지 않습니다!

SQL 문 중첩 줄이기

데이터베이스에서 캡처한 SQL 문을 인쇄하면 A4 용지 2장 길이인 경우가 종종 있습니다. 일반적으로 이렇게 복잡한 진술에는 문제가 있는 경우가 많습니다. 이 2페이지 분량의 SQL 문을 가지고 원저자에게 물어봤더니 시간이 너무 오래 걸려서 한동안 이해하지 못했다고 하더군요. SQL 문으로 인해 원저작자도 혼란을 겪을 수 있고, 데이터베이스도 혼란을 겪을 수 있다고 생각됩니다.

일반적으로 Select 문의 결과를 하위 집합으로 사용하고 그 하위 집합에서 쿼리를 수행하는 방식이 비교적 일반적이지만 경험에 따르면 다음과 같은 경우가 있습니다. 3개 이상의 중첩 수준을 사용하면 쿼리 최적화 프로그램이 잘못된 실행 계획을 쉽게 제공할 수 있습니다. 깜짝 놀랐기 때문이다. 인공지능 같은 것들은 결국 인간의 해상도보다 열등하다. 사람이 어지러우면 데이터베이스도 어지러울 것이라고 장담할 수 있다.

또한, 실행 계획은 재사용이 가능합니다. SQL 문은 간단할수록 재사용 가능성이 높아집니다. 복잡한 SQL 문에서 문자 하나가 변경되는 한 이를 다시 구문 분석해야 하며, 그러면 많은 쓰레기가 메모리에 채워지게 됩니다. 데이터베이스가 얼마나 비효율적인지는 상상할 수 있습니다.

"임시 테이블"을 사용하여 중간 결과 임시 저장

SQL 문을 단순화하는 중요한 방법은 임시 테이블을 사용하여 중간 결과를 임시로 저장하는 것입니다. 그러나 임시 테이블의 이점은 훨씬 더 큽니다. 임시 테이블에 임시 저장되고 후속 쿼리는 tempdb에 있습니다. 이렇게 하면 프로그램에서 기본 테이블을 여러 번 스캔하는 것을 방지할 수 있으며 프로그램 실행 중 "업데이트 잠금"을 차단하는 "공유 잠금"이 크게 줄어들어 차단 및 오류가 줄어듭니다. 동시성 성능 향상.

OLTP 시스템 SQL 문은 바인드 변수를 사용해야 합니다

changetime이 있는 orderheader에서 *를 선택하세요.

changetime이 있는 orderheader에서 *를 선택하세요. ; '2010-09-22 00:00:01'
쿼리 최적화 프로그램은 위의 두 문을 서로 다른 SQL 문으로 간주하여 두 번 구문 분석해야 합니다. 바인드 변수를 사용하는 경우
changetime > @chgtime
@chgtime 변수가 어떤 값이든 전달할 수 있는 orderheader에서 *를 선택하면 많은 수의 유사한 쿼리가 실행 계획을 재사용할 수 있어 데이터베이스를 크게 줄일 수 있습니다. SQL 문을 구문 분석하는 부담. 한 번 구문 분석하고 여러 번 재사용하는 것이 데이터베이스 효율성을 높이는 원칙입니다.

편향된 필드에서 바인딩 변수 스누핑 문제

모든 것에는 양면이 있습니다. 바인드 변수는 대부분의 OLTP 처리에 적용 가능하지만 예외가 있습니다. 예를 들어 where 조건의 필드가 "비뚤어진 필드"인 경우입니다.

'기울어진 필드'는 해당 열의 값이 대부분 동일하다는 의미입니다. 예를 들어 인구 조사표의 '민족' 열에서는 90% 이상이 한족입니다. 따라서 SQL 문에서 30세인 한족 인구를 쿼리하려면 where 조건에 "ethnic" 열을 배치해야 합니다. 이때 바인드 변수 @nation을 사용하면 큰 문제가 발생합니다.

@nation이 전달한 첫 번째 값이 "Han"이라면 전체 실행 계획이 필연적으로 테이블 스캔을 선택한다고 상상해 보세요. 그런 다음 전달된 두 번째 값은 "Buyi"입니다. 이는 "Buyi"의 비율이 1만분의 1에 불과하므로 색인 검색을 사용해야 하는 이유입니다. 그러나 처음으로 파싱된 "Han"의 실행 계획을 재사용하므로 두 번째에도 테이블 스캔 방식을 사용하게 된다. 이 문제는 유명한 "바인드 변수 스누핑"입니다. "비뚤어진 필드"에는 바인드 변수를 사용하지 않는 것이 좋습니다.

트랜 트랜잭션 시작은 가능한 한 작아야 합니다.

SQL Server에서 SQL 문은 기본적으로 트랜잭션이며, 문이 실행된 후 기본적으로 커밋됩니다. 실제로 이것은 start tran이 각 문의 시작 부분에 암시되고 커밋이 끝에 암시되는 것처럼 최소화된 형태의 start tran입니다.
어떤 경우에는 start tran을 명시적으로 선언해야 합니다. 예를 들어 "삽입, 삭제 및 수정" 작업을 수행할 때 여러 테이블을 동시에 수정해야 합니다. 테이블이 성공했거나 수정 사항이 전혀 성공하지 못했습니다. Begin tran은 여러 SQL 문을 함께 실행하고 최종적으로 함께 커밋할 수 있는 역할을 할 수 있습니다. 장점은 데이터 일관성이 보장되지만 완벽한 것은 없다는 것입니다. Begin tran이 지불하는 대가는 제출 전에 SQL 문에 의해 잠긴 모든 리소스가 커밋될 때까지 해제될 수 없다는 것입니다.
Begin tran이 너무 많은 SQL 문을 트랩하면 데이터베이스 성능이 저하된다는 것을 알 수 있습니다. 대규모 트랜잭션이 커밋되기 전에는 필연적으로 다른 문이 차단되어 많은 차단이 발생하게 됩니다.
Begin tran을 사용하는 원칙은 데이터 일관성 보장을 전제로 start tran에 의해 트랩되는 SQL 문이 적을수록 좋다는 것입니다! 어떤 경우에는 트리거를 사용하여 데이터를 동기화할 수 있으며 start tran이 반드시 사용되는 것은 아닙니다.

일부 SQL 쿼리 문에는 nolock을 추가해야 합니다

SQL 문에 nolock을 추가하는 것은 SQL Server의 동시 성능을 향상시키는 중요한 수단입니다. Oracle의 구조상 이는 필요하지 않습니다. 더 복잡합니다. "데이터 선행자"를 저장하는 실행 취소 테이블 공간이 있습니다. 수정 중에 데이터가 커밋되지 않은 경우 읽은 내용은 수정되기 전의 복사본이며 해당 복사본은 다음 위치에 저장됩니다. 실행 취소 테이블 공간. 이런 방식으로 오라클의 읽기와 쓰기는 서로 독립적일 수 있으며, 이것이 오라클이 널리 칭찬받는 이유입니다. SQL Server의 읽기와 쓰기는 서로 차단됩니다. 동시성 성능을 향상시키기 위해 일부 쿼리에 nolock을 추가하여 읽는 동안 쓰기를 허용할 수 있지만 커밋되지 않은 더티 데이터를 읽을 수 있다는 단점이 있습니다. nolock 사용에는 세 가지 원칙이 있습니다.

(1) 쿼리 결과를 "삽입, 삭제, 수정"으로 사용하는 경우 nolock을 추가할 수 없습니다!

(2) 쿼리된 테이블은 페이지 분할이 자주 발생하므로 nolock을 주의해서 사용하세요!

(3) 임시 테이블을 사용하여 Oracle의 실행 취소 테이블 공간과 유사한 기능을 하는 "데이터 예시"를 저장할 수도 있습니다.

임시 테이블을 사용하여 동시성 성능을 향상할 수 있는 경우에는 사용하지 마세요. nolock을 사용하세요.

nolock을 추가한 후 페이지 분할이 자주 발생하여 건너뛰거나 반복 읽기가 발생하는 테이블을 쿼리할 수 있습니다.

nolock을 추가한 후 '삽입, 삭제, 수정' 중에 쿼리할 수 있습니다. 그러나 "삽입, 삭제, 수정"이 동시에 발생하기 때문에 데이터 페이지가 가득 차면 페이지 분할이 불가피한 경우가 있는데, 이때 nolock 쿼리가 발생하는 경우도 있습니다. 예를 들어, 100페이지에서 읽은 내용이 페이지가 101페이지로 나누어져 있기 때문에 이로 인해 nolock 쿼리에서 101페이지를 읽을 때 데이터를 반복적으로 읽어서 "반복 읽기"가 발생할 수 있습니다. 마찬가지로, 100페이지의 데이터를 읽기 전에 99페이지로 나누어 읽는 경우 nolock 쿼리에서 레코드가 누락되어 "건너뛰기"가 발생할 수 있습니다.

위에서 언급한 친구는 nolock을 추가한 후 일부 작업에서 오류를 보고했습니다. nolock 쿼리로 인해 두 개의 동일한 레코드가 다른 테이블에 삽입되면 당연히 기본 키 충돌이 발생할 것으로 추정됩니다.

클러스터드 인덱스가 테이블의 시퀀스 필드에 구축되지 않아 테이블이 페이지 분할되기 쉽습니다

예를 들어 주문 테이블에는 주문 번호 orderid와 고객 번호 contactid, 그러면 클러스터형 인덱스를 어디에 추가해야 합니까? 이 테이블의 경우 주문 번호가 순차적으로 추가됩니다. 클러스터형 인덱스가 orderid에 추가되면 마지막에 새 행이 추가되므로 페이지 분할이 자주 발생하지 않습니다. 그러나 대부분의 쿼리는 고객 ID를 기반으로 하기 때문에 contactid에 클러스터형 인덱스를 추가하는 것이 합리적입니다. 주문 테이블의 경우 contactid는 순차 필드가 아닙니다.

예를 들어 "Zhang San"의 "contactid"가 001인 경우 "Zhang San"이 새로 배치하는 경우 "Zhang San"의 주문 정보가 이 테이블의 첫 번째 데이터 페이지에 배치되어야 합니다. 오늘 주문하세요. 주문한 경우 주문 정보는 테이블 마지막 페이지에 표시되지 않고 첫 페이지에 표시됩니다! 첫 페이지가 가득 차면 어떻게 되나요? 죄송합니다. 이 레코드를 위한 공간을 확보하려면 이 테이블의 모든 데이터를 다시 이동해야 합니다.

SQL Server의 인덱스는 Oracle의 인덱스와 다릅니다. SQL Server의 클러스터형 인덱스는 실제로 Oracle의 인덱스 구성 테이블과 동일한 클러스터형 인덱스 필드 순서로 테이블을 정렬합니다. SQL Server의 클러스터형 인덱스는 테이블 자체를 조직화한 형태이므로 효율성이 매우 높습니다. 그렇기 때문에 레코드를 삽입할 때 그 위치가 무작위로 배치되는 것이 아니라, 순서대로 배치되어야 하는 데이터 페이지에 해당 데이터 페이지에 공간이 없으면 페이지 분할이 발생하게 됩니다. 따라서 클러스터형 인덱스는 테이블의 순차 필드를 기반으로 구축되지 않으며 테이블이 페이지 분할되기 쉽습니다.

한 번은 친구가 특정 테이블을 다시 인덱싱한 후 삽입 효율이 크게 떨어지는 상황을 겪었습니다. 아마도 상황은 이렇을 것으로 추정된다. 테이블의 클러스터형 인덱스는 테이블의 순차 필드에 구축되지 않을 수 있으므로 테이블이 보관되는 경우가 많기 때문에 테이블의 데이터가 희박한 상태로 존재합니다. 예를 들어, Zhang San이 20개의 주문을 했는데 지난 3개월 동안 5개의 주문만 있습니다. 보관 전략은 3개월의 데이터를 유지하는 것입니다. 그러면 Zhang San의 지난 15개 주문이 보관되어 15개의 공석이 남게 됩니다. 발생 시 용도 변경 삽입에 입력되었습니다. 이 경우 사용 가능한 여유 공간이 있으므로 페이지 분할이 발생하지 않습니다. 그러나 쿼리는 데이터 없이 빈 위치를 검색해야 하기 때문에 쿼리 성능이 상대적으로 낮습니다.

클러스터형 인덱스를 재구축한다는 것은 테이블의 데이터를 다시 정렬한다는 의미이기 때문에 상황이 달라졌습니다. 데이터를 삽입할 때 페이지 채우기 비율이 매우 높기 때문입니다. 발생하므로 성능이 크게 저하됩니다.

클러스터형 인덱스가 순차 필드에 구축되지 않은 테이블의 경우 페이지 채우기 비율을 더 낮춰야 합니까? 클러스터형 인덱스 재구축을 방지하시겠습니까? 생각해 볼 만한 질문이에요!

복합 인덱스를 사용하여 여러 where 조건에 대한 쿼리 속도를 향상시킵니다.

복합 인덱스는 일반적으로 단일 인덱스보다 선택성이 더 좋습니다. 게다가 특정 where 조건에 맞게 특별히 설정된 인덱스이므로 단일 인덱스보다 쿼리 속도가 빠릅니다. 복합 인덱스의 선행 필드는 "매우 선택적인" 필드여야 합니다. 예를 들어 날짜, 성별, 나이 등 3개의 필드가 있습니다. 어떤 필드를 가이드 필드로 사용해야 하는지 살펴볼까요? 당연히 "날짜"가 선행 필드로 사용되어야 합니다. 날짜는 3개 필드 중 가장 선택적인 필드입니다.

여기서 예외가 있습니다. 날짜가 클러스터형 인덱스의 선두 필드이기도 한 경우에는 복합 인덱스를 구축할 수 없으며 직접 클러스터형 인덱스로 이동할 수 있어 더욱 효율적입니다.

클러스터형 인덱스를 '복합 인덱스'로 구축하지 마세요. 클러스터형 인덱스가 단순할수록 좋고, 선택성이 높을수록 좋습니다! 클러스터형 인덱스에는 2개의 필드가 포함되어 있으며 허용됩니다. 그러나 필드가 2개 이상인 경우 자동 증가 필드를 기본 키로 구축하는 것을 고려해야 합니다. 클러스터형 인덱스는 기본 키일 필요가 없습니다.

퍼지 쿼리에 좋아요를 사용할 때 첫 번째 %를 사용하지 않도록 노력해야 합니다.

때때로

Select * from contact where와 같은 일부 퍼지 쿼리를 수행해야 하는 경우가 있습니다. 사용자 이름은 '% yue%'

키워드 %yue%입니다. yue 앞에 "%"가 사용되므로 쿼리는 꼭 필요한 경우가 아니면 테이블 전체를 스캔해야 합니다.

SQL Server 테이블을 조인하는 세 가지 방법

(1) 병합 조인

(2) 중첩 루프 조인

(3) 해시 조인

SQL Server 2000에는 중첩 루프 조인(Nested Loop Join)이라는 한 가지 조인 방법만 있습니다. 결과 집합 A가 작으면 기본적으로 A의 각 레코드를 B에서 검색해야 합니다. 스캔됨은 결과 ​​집합 A와 동일합니다. 행 수 x B 결과 집합의 행 수입니다. 따라서 두 결과 집합이 모두 크면 Join 결과가 좋지 않습니다.

SQL Server 2005에는 Merge Join이 추가되었습니다. 테이블 A와 테이블 B의 연결 필드가 클러스터형 인덱스가 있는 필드인 경우 테이블의 순서가 양쪽으로 조인된 것입니다. 이러한 종류의 조인 오버헤드는 테이블 A의 결과 집합 행 수에 테이블 B의 결과 집합 행 수를 더한 것과 동일합니다. 하나는 덧셈이고 다른 하나는 병합 조인의 효과가 다음과 같다는 것을 알 수 있습니다. Nested Loop Join보다 훨씬 낫습니다.

연결된 필드에 인덱스가 없으면 SQL2000의 효율성은 매우 낮습니다. 그러나 SQL2005는 테이블 A와 B의 결과 집합에 인덱스를 임시로 추가하는 것과 동일한 Hash 조인을 제공합니다. SQL2005의 효율성이 SQL2000보다 훨씬 향상되었으며 이것이 중요한 이유라고 생각합니다.

요약하자면, 테이블 연결 시 다음 사항에 주의해야 합니다.

(1) 연결 필드는 클러스터형 인덱스가 있는 필드를 선택해 보세요

(2) where 조건을 신중하게 고려하여 테이블 A와 B의 결과 집합을 줄여보세요.

(3) 많은 조인 연결 필드에 인덱스가 부족하고 여전히 SQL2000을 사용하고 있다면 즉시 업그레이드하세요.

Row_number는 테이블 스캔으로 이어지기 때문에 페이징을 위해 임시 테이블을 사용하는 것이 좋습니다

ROW_Number 페이징 테스트 결과:
페이징에 ROW_Number 사용: CPU 시간 = 317265밀리초, 점유 시간 = 423090 밀리초
페이징에 임시 테이블 사용: CPU 시간 = 1266밀리초, 점유 시간 = 6705밀리초

ROW_Number 구현은 순서 기준을 기반으로 하며 쿼리에 대한 정렬의 영향은 명백합니다.

기타

예를 들어 일부 작성 방법에서는 색인 사용이 제한됩니다

chgdate +7 < sysdate

chgdate < sysdate -7