SQL 쿼리 최적화에 대한 심층적인 이해 - 고성능 SQL 문을 작성하는 방법에 대한 구체적인 분석

SQL 쿼리 최적화에 대한 심층적인 이해 - 고성능 SQL 문 작성 방법에 대한 구체적인 분석:

1. 먼저 실행계획이 무엇인지부터 이해해야 겠죠?

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

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

(1) SQL 문은 쿼리 최적화 프로그램이 수행하려는 작업을 명확하게 전달합니까?

(2) 쿼리 최적화 프로그램을 통해 얻은 데이터베이스 통계 정보는 최신이고 정확한가?

2. SQL문 작성방식 통일

다음 두 SQL 문에 대해 프로그래머는 동일하다고 생각하지만 데이터베이스 쿼리 최적화 프로그램은 서로 다르다고 생각합니다.

아아아아

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

3. SQL문을 너무 복잡하게 작성하지 마세요

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

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

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

4. "임시 테이블"을 사용하여 중간 결과를 임시로 저장하세요

SQL 문을 단순화하는 중요한 방법은 임시 테이블을 사용하여 중간 결과를 임시로 저장하는 것입니다. 그러나 임시 테이블의 이점은 이보다 훨씬 더 많습니다. 임시 결과는 임시 테이블에 저장되고 후속 쿼리는 tempdb에 있으므로 여러 번 발생하는 것을 피할 수 있습니다. 또한 메인 테이블을 스캔하면 프로그램 실행 중 "공유 잠금" 차단 및 "업데이트 잠금"이 크게 줄어들어 차단이 줄어들고 동시성 성능이 향상됩니다.

5. OLTP 시스템 SQL 문은 바인드 변수

select*from dual 

select*From dual

를 사용해야 합니다. 위의 두 문은 쿼리 최적화 프로그램에서 서로 다른 SQL 문으로 간주되므로 두 번 구문 분석해야 합니다. 바인드 변수를 사용하는 경우

select*from orderheader where changetime >'2010-10-20 00:00:01' 
select*from orderheader where changetime >'2010-09-22 00:00:01'

@chgtime 변수는 임의의 값을 전달할 수 있으므로 다수의 유사한 쿼리에서 실행 계획을 재사용할 수 있으므로 데이터베이스에서 SQL 문을 구문 분석하는 부담을 크게 줄일 수 있습니다. 한 번 구문 분석하고 여러 번 재사용하는 것이 데이터베이스 효율성을 높이는 원칙입니다.

6. 바인딩 변수 엿보기

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

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

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

7. 필요한 경우에만 start tran

을 사용하세요. 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이 반드시 사용되는 것은 아닙니다.

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

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

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

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

(3) 임시 테이블을 이용하면 오라클의 언두 테이블스페이스

와 유사한 기능을 갖는 "데이터 포섀도우"도 저장할 수 있다. 동시성 성능을 향상시키기 위해 임시 테이블을 사용할 수 있다면 nolock을 사용하지 마십시오.

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

예를 들어, 주문 테이블에는 주문 번호 orderid와 고객 번호 contactid가 있습니다. 그렇다면 어떤 필드에 클러스터형 인덱스를 추가해야 할까요? 이 테이블의 경우 주문 번호가 순차적으로 추가됩니다. 클러스터형 인덱스가 orderid에 추가되면 마지막에 새 행이 추가되므로 페이지 분할이 자주 발생하지 않습니다. 그러나 대부분의 쿼리는 고객 번호를 기반으로 하기 때문에 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개의 공석이 남게 됩니다. 발생 시 용도 변경 삽입에 입력되었습니다. 이 경우 사용 가능한 여유 공간이 있으므로 페이지 분할이 발생하지 않습니다. 그러나 쿼리는 데이터 없이 빈 위치를 검색해야 하기 때문에 쿼리 성능이 상대적으로 낮습니다.

클러스터형 인덱스를 다시 작성하면 테이블의 데이터가 다시 정렬되고, 데이터 삽입 시 페이지 채우기 비율이 매우 높아 성능이 크게 저하되므로 클러스터형 인덱스를 다시 작성하면 상황이 달라집니다. .

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

10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读

加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。

11、使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

12、数据类型的隐式转换对查询效率的影响

sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql server 2000自动转换数据类型，会导致传入的参数与主键字段类型不一致，这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题，但是还是应该注意一下。

13、SQL Server 表连接的三种方式

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000只有一种join方式——Nested Loop Join，如果A结果集较小，那就默认作为外表，A中每条记录都要去B中扫描一遍，实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大，那Join的结果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的连接字段正好是聚集索引所在字段，那么表的顺序已经排好，只要两边拼上去就行了，这种join的开销相当于A表的结果集行数加上B表的结果集行数，一个是加，一个是乘，可见merge join 的效果要比Nested Loop Join好多了。

如果连接的字段上没有索引，那SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于临时给A，B表的结果集加上索引，因此SQL2005的效率比SQL2000有很大提高，我认为，这是一个重要的原因。

总结一下，在表连接时要注意以下几点：

(1)    连接字段尽量选择聚集索引所在的字段

(2)    仔细考虑where条件，尽量减小A、B表的结果集

(3)    如果很多join的连接字段都缺少索引，而你还在用SQL Server 2000，赶紧升级吧。

위 내용은 SQL 쿼리 최적화에 대한 심층적인 이해 - 고성능 SQL 문을 작성하는 방법에 대한 구체적인 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!