고성능 SQL을 작성하는 방법

먼저 실행계획이 무엇인지 알아야겠죠?

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

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

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

쿼리 최적화 프로그램에서 얻은 데이터베이스 통계가 최신이고 정확합니까?

추천 강좌: MySQL 튜토리얼.

SQL 문 작성 방식 통일

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

select*from dual 
select*From dual

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

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

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

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

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

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

SQL 문을 단순화하는 중요한 방법은 다음과 같습니다. 임시 테이블을 사용하면 임시 결과가 임시 테이블에 저장되고 후속 쿼리는 tempdb에 저장됩니다. 실행 중에 "공유 잠금"이 "업데이트 잠금"을 차단하여 차단을 줄이고 동시성 성능을 향상시킵니다.

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

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

쿼리 최적화 프로그램은 위의 두 문을 서로 다른 SQL 문으로 간주하므로 구문 분석이 필요합니다. 두 배. 바인드 변수

select*from orderheader where changetime >@chgtime

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

바인드 변수 peek

바인드 변수는 대부분의 OLTP 프로세스에 적용되는 두 가지 측면이 있습니다. 예외. 예를 들어 where 조건의 필드가 "비뚤어진 필드"인 경우입니다.

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

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

필요할 때만 Begin 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이 반드시 사용되는 것은 아닙니다.

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

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

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

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

(3) 임시 테이블을 사용하면 "이전 데이터"도 저장할 수 있는데, 이는 Oracle의 Undo 테이블 공간과 유사한 기능을 가지고 있습니다.

임시 테이블을 사용하여 동시성 성능을 향상시킬 수 있다면 nolock을 사용하지 마세요.

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

예를 들어 주문 테이블에는 주문 번호 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개의 공석이 남게 됩니다. 발생 시 용도 변경 삽입에 입력되었습니다. 이 경우 사용 가능한 여유 공간이 있으므로 페이지 분할이 발생하지 않습니다. 그러나 쿼리는 데이터 없이 빈 위치를 검색해야 하기 때문에 쿼리 성능이 상대적으로 낮습니다.

클러스터형 인덱스를 재구축한다는 것은 테이블의 데이터를 다시 정렬한다는 의미이기 때문에, 데이터 삽입 시 페이지 채우기율이 매우 높아서 페이지 분할이 자주 발생합니다. 성능이 크게 향상되었습니다.

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

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

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

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

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

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

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

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

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

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

위 내용은 고성능 SQL을 작성하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!