전화 = N'1234'를 사용한 쿼리가 전화 = '1234'보다 느린 이유는 무엇입니까?

전화 = N'1234'를 사용한 쿼리가 전화 = '1234'보다 느린 이유는 무엇입니까?

나는 바르샤르인 들판이 있습니다(20).

이 쿼리가 실행되면 속도가 빠릅니다(인덱스 검색 사용).

SELECT * FROM [dbo].[phone] WHERE phone = '5554474477'

하지만 이것은 느립니다(색인 스캔 사용).

SELECT * FROM [dbo].[phone] WHERE phone = N'5554474477'

필드를 nvarchar로 변경하면 인덱스 검색이 사용될 것으로 예상됩니다.

ㅠㅠnvarchar데이터 유형 우선 순위가 보다 높습니다.varchar그래서 그것은 칼럼의 암묵적인 캐스팅을 수행해야 합니다.nvarchar이렇게 하면 색인 검색을 방지할 수 있습니다.

일부 조합에서는 여전히 탐색 기능을 사용할 수 있으며, 다음을 누르기만 하면 됩니다.cast검색을 통해 전체 테이블의 모든 행에 대해 이 작업을 수행할 필요가 없는 대신 검색에 의해 일치하는 행에 대한 잔여 술어로 변환되지만, 이러한 정렬은 사용하지 않을 수 있습니다.

이에 대한 조합의 효과는 아래에 설명되어 있습니다.SQL 컬렉션을 사용하면 검색이 실행되고 Windows 컬렉션은 내부 함수를 호출하여 검색으로 변환할 수 있습니다.

CREATE TABLE [dbo].[phone]
  (
     phone1 VARCHAR(500) COLLATE sql_latin1_general_cp1_ci_as CONSTRAINT uq1 UNIQUE,
     phone2 VARCHAR(500) COLLATE latin1_general_ci_as CONSTRAINT uq2 UNIQUE,
  );

SELECT phone1 FROM [dbo].[phone] WHERE phone1 = N'5554474477';
SELECT phone2 FROM [dbo].[phone] WHERE phone2 = N'5554474477';

그SHOWPLAN_TEXT아래에 있습니다.

쿼리 1

  |--Index Scan(OBJECT:([tempdb].[dbo].[phone].[uq1]),  WHERE:(CONVERT_IMPLICIT(nvarchar(500),[tempdb].[dbo].[phone].[phone1],0)=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))

쿼리 2

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
       |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@1],[@1],(62))))
       |    |--Constant Scan
       |--Index Seek(OBJECT:([tempdb].[dbo].[phone].[uq2]), SEEK:([tempdb].[dbo].[phone].[phone2] > [Expr1005] AND [tempdb].[dbo].[phone].[phone2] < [Expr1006]),  WHERE:(CONVERT_IMPLICIT(nvarchar(500),[tempdb].[dbo].[phone].[phone2],0)=[@1]) ORDERED FORWARD)

두 번째 경우에 계산 스칼라는 다음 값을 방출합니다.

Expr1004 = 62
Expr1005 = '5554474477'
Expr1006 = '5554474478'

▁on▁shownicate▁the에 있습니다.phone2 > Expr1005 and phone2 < Expr1006그래서 표면적으로는 제외할 것입니다.'5554474477' 깃발 지만국기하▁flag기국.62일치한다는 뜻입니다.

NB: 검색 범위는 여기서 동일성을 수행하는 문자열의 길이에 따라 달라집니다.

어의 은 경우.= N'a'를 들어 해서 예를 들검 여은다 히시전는인으로 하는 전체 값 .a그리고 일치하는 것들만 보존하는 잔여 술어를 가지고 있습니다.= 'a' 어= N''더 나쁜 것은.길이가 0인 접두사를 사용하면 전체 인덱스를 읽게 됩니다.

다른 답들은 이미 무슨 일이 일어나는지 설명합니다. 우리는 이미 봤습니다.NVARCHAR는 보높 은유 우순가집다니위를선형다▁preced다가보다 유형 순위가 .VARCHAR데이터베이스가 열에 대한 모든 행을 다음과 같이 캐스팅해야 하는 이유를 설명하고 싶습니다.NVARCHAR공급된 단일 값을 다음과 같이 캐스팅하는 것보다VARCHAR두 번째 옵션이 직관적으로나 경험적으로 훨씬 더 빠르더라도 말입니다.또한 성능에 미치는 영향이 왜 이렇게 클 수 있는지 설명하고 싶습니다.

의 NVARCHARVARCHAR변환의 폭이 좁아지고 있습니다.그것은,NVARCHAR▁a보다 잠재적으로 더 되어 있습니다.VARCHARvalue. 치를 모든 것을 표현하는 것은 가능하지 않습니다.NVARCHAR로 입력합니다.VARCHAR출력, 따라서 전자에서 후자로의 캐스팅은 잠재적으로 정보를 잃게 됩니다.반대쪽 캐스트는 확장된 변환입니다.에서 캐스팅VARCHAR에 가치가 있는.NVARCHAR가치는 정보를 잃지 않습니다. 안전합니다.

두 가지 유형이 일치하지 않을 경우 SQL Server는 항상 안전한 변환을 선택하는 것이 원칙입니다.그것은 똑같은 오래된 "정확성이 성과를 능가한다"라는 구호입니다.또는 벤자민 프랭클린의 말을 빌리자면, "작은 성과와 본질적인 정확성을 교환하는 사람은 정확성이나 성과를 얻을 자격이 없습니다."따라서 유형 우선 순위 규칙은 안전한 변환이 선택되도록 설계됩니다.

이제 여러분과 저는 변환 범위를 좁히는 것이 이 특정 데이터에 대해서도 안전하다는 것을 알고 있습니다. 하지만 SQL Server 쿼리 최적화 프로그램은 이에 대해 신경 쓰지 않습니다.좋든 나쁘든 실행 계획을 작성할 때 데이터 유형 정보를 먼저 보고 유형 우선 순위 규칙을 따릅니다.

---

따라서 SQL Server가 느린 옵션을 선택해야 하는 이유가 설명됩니다.이제 왜 그 차이가 그렇게 큰지에 대해 이야기해 보겠습니다.중요한 것은 저장된 데이터를 캐스트해야 한다고 결정한 후에는 쿼리의 상수 값이 아니라 테이블의 모든 행에 대해 캐스트해야 한다는 것입니다.비교 값을 캐스팅한 후까지 행이 필터와 일치하는지 여부를 모르기 때문에 비교 필터와 일치하지 않는 행의 경우에도 마찬가지입니다.

하지만 상황은 더 악화됩니다.열의 캐스트 값이 정의한 인덱스에 저장된 값과 더 이상 같지 않습니다.그 결과 열의 인덱스는 이제 데이터베이스 성능의 핵심을 잘라내는 이 쿼리에 사용할 수 없습니다.

전체 테이블 검색이 아닌 이 쿼리에 대한 인덱스 검색을 받게 된 것은 매우 행운이라고 생각합니다. 쿼리의 요구 사항을 충족하는 커버링 인덱스가 있기 때문일 수 있습니다(최적화 도구는 테이블의 모든 레코드만큼 쉽게 인덱스의 모든 레코드를 캐스트하도록 선택할 수 있습니다).

---

유형 불일치를 보다 유리한 방법으로 명시적으로 해결하여 이 쿼리에 대한 내용을 수정할 수 있습니다.이를 달성하는 가장 좋은 방법은 물론 평원을 제공하는 것입니다.VARCHAR우선 주조/변형의 필요성을 전혀 배제해야 합니다.

SELECT * FROM [dbo].[phone] WHERE phone = '5554474477'

하지만 저는 우리가 보고 있는 것이 문자 그대로의 그 부분을 반드시 제어할 필요는 없는 애플리케이션에 의해 제공되는 가치라고 생각합니다.이 경우에도 다음 작업을 수행할 수 있습니다.

SELECT * FROM [dbo].[phone] WHERE phone = cast(N'5554474477' as varchar(20))

두 예 모두 원래 코드에서 유형 불일치를 양호하게 해결합니다.후자의 상황에서도, 당신은 당신이 알고 있는 것보다 더 많은 문자를 통제할 수 있습니다. 이 가 .://▁a 에서 된 경우, "http:///된 "에서 생성됩니다. 넷프그문그관있것입다을니련이것과마아도제로램는과 관련이 있을 것입니다.AddWithValue()기능.저는 이 문제에 대해 과거에 작성한 적이 있으며 올바르게 처리하는 방법을 알고 있습니다.

이러한 수정 사항은 또한 상황이 왜 이런 식인지를 입증하는 데 도움이 됩니다.

---

SQL Server 개발자들은 나중에 쿼리 최적화기를 향상시켜 다음과 같은 상황을 살펴볼 수도 있습니다. 유형 우선순위 규칙이 테이블 또는 인덱스 검색을 유발하는 행별 변환을 유발합니다.그러나 그 반대의 변환은 지속적인 데이터를 포함하며 인덱스 탐색에 불과할 수 있습니다. 이 경우 먼저 데이터를 검토하여 더 빠른 축소 변환도 안전할 수 있는지 확인하십시오.

하지만, 저는 이런 일이 일어날 가능성이 낮다고 생각합니다.기존 시스템 내에서 쿼리를 수정하는 것은 개별 쿼리를 평가하는 데 드는 추가 비용과 옵티마이저가 수행하는 작업을 이해하는 복잡성에 비해 너무 쉬워서("서버가 여기에 문서화된 우선 순위 규칙을 따르지 않은 이유는 무엇입니까?") 정당화할 수 있습니다.

 SELECT * FROM [dbo].[phone] WHERE phone = N'5554474477'

로 해석됩니다.

 SELECT * from [dbo].[phone] WHERE CAST(phone as NVARCHAR) = N'5554474477'

인덱스 사용을 방지합니다.

언급URL : https://stackoverflow.com/questions/54540928/why-is-query-with-phone-n1234-slower-than-phone-1234