첫째, 상황에 따라 "적절한" 지수를 설정하십시오.
"적절한" 색인을 만드는 것이 질의 최적화를 위한 첫 번째 전제 조건입니다.
테이블 외에도 인덱스는 물리적 미디어에 저장되는 또 다른 중요한 사용자 정의 데이터 구조입니다. 색인은 색인 코드의 값을 기준으로 데이터를 검색할 때 데이터에 빠르게 액세스할 수 있도록 합니다. 실제로 인덱스가 없으면 데이터베이스는 SELECT 문에 따라 결과를 성공적으로 검색할 수 있지만 테이블이 커질수록 "적합한" 인덱스를 사용하는 효과가 점점 더 두드러집니다. 이 문장에서 우리는' 적절하다' 라는 단어를 사용했다. 색인을 사용할 때 구현 과정을 진지하게 고려하지 않으면 색인이 향상되거나 데이터베이스의 작업 성능을 손상시킬 수 있기 때문이다.
(a) 지수 구조에 대한 간단한 이해
실제로 색인을 특수 카탈로그로 간주할 수 있습니다. Microsoft 의 SQL 서버는 클러스터된 인덱스와 비클러스터된 인덱스의 두 가지 인덱스를 제공합니다. 다음은 클러스터된 인덱스와 클러스터되지 않은 인덱스의 차이점을 설명합니다.
사실 우리의 중국어 사전의 본문 자체는 바로 클러스터된 색인이다. 예를 들어, "An" 이라는 단어를 찾아보면 사전의 처음 몇 페이지를 펼칩니다. "An" 의 병음은 "An" 이고 병음별로 한자를 정렬하는 사전은 영문자 "a" 로 시작하고 "z" 로 끝나므로 "an" 입니다 "A" 로 시작하는 모든 부분을 다 찾아도 이 단어를 찾을 수 없다면, 이 단어는 너의 사전에 없는 것이다. 마찬가지로, "장" 이라는 단어를 찾아보면, "장" 의 병음이 "장" 이기 때문에 사전의 마지막 부분으로도 넘어갑니다. 다른 말로 하자면, 사전의 주체는 그 자체로 하나의 목록이므로, 당신이 찾아야 할 것을 찾기 위해 다른 카탈로그를 찾아보지 않아도 된다. (존 F. 케네디, 공부명언)
우리는 이런 텍스트 내용 자체가 일정한 규칙에 따라 배열된 디렉토리를' 클러스터된 색인' 이라고 부른다.
만약 당신이 단어를 알고 있다면, 당신은 신속하게 자동조회에서 조회할 수 있습니다. 그러나 당신은 당신이 모르는 단어를 만날 수 있습니다, 당신은 그 발음을 모릅니다. 이때 네가 방금 찾고자 했던 글자를 찾을 수 없다. 너는 옆을 근거로 네가 원하는 글자를 찾은 다음, 바로 한 페이지를 넘기고, 글자 뒤의 페이지 번호에 따라 네가 원하는 글자를 찾아야 한다. 그러나 부수 목록과 사전에서 찾은 단어 정렬을 결합하는 것은 실제 텍스트 정렬 방법이 아닙니다. 예를 들어, "장" 이라는 단어를 찾아보면, 부수가 조사한 후 사전에서 "장" 이라는 단어의 페이지 번호는 672 이고, "풀" 이라는 단어는 사전에서 "장" 이라는 단어 위에 있지만 페이지 번호는 63 입니다. 그 아래에 있습니다. 분명히, 이 단어들은 실제로' 장' 자 위와 아래에 있는 것이 아니다. 지금 보시는 연속어' 지연, 장, 석궁' 은 사실 비집수 인덱스에서의 순서이며, 사전체의 단어가 비집수 인덱스에서의 매핑입니다. 이렇게 하면 우리는 당신이 필요로 하는 단어를 찾을 수 있지만, 두 가지 과정이 필요합니다. 먼저 카탈로그에서 결과를 찾은 다음, 당신이 필요로 하는 페이지 번호로 넘어갑니다.
우리는 이 카탈로그를 카탈로그로만, 텍스트만 텍스트로 정렬하는 방식을' 비집수 색인' 이라고 부른다.
위의 예를 통해 클러스터된 인덱스와 비클러스터된 인덱스가 무엇인지 이해할 수 있습니다.
더 확장함으로써 카탈로그는 한 가지 방식으로만 정렬할 수 있기 때문에 테이블당 하나의 클러스터된 인덱스만 있을 수 있다는 것을 쉽게 이해할 수 있습니다.
(b) 클러스터된 인덱스 또는 클러스터되지 않은 인덱스 사용 시기
다음 표에서는 클러스터된 인덱스나 클러스터되지 않은 인덱스를 사용하는 시기를 요약합니다 (중요).
동작 설명
클러스터된 인덱스 사용
클러스터되지 않은 인덱스 사용
열은 일반적으로 그룹별로 정렬됩니다.
해야 할 일
해야 할 일
일련의 데이터를 반환합니다
해야 할 일
하지만 나는 항상 그렇지 않다.
하나 이상의 다른 값
하지만 나는 항상 그렇지 않다.
하지만 나는 항상 그렇지 않다.
십진수의 다른 값
해야 할 일
하지만 나는 항상 그렇지 않다.
수많은 다른 가치관
하지만 나는 항상 그렇지 않다.
해야 할 일
자주 업데이트되는 열
하지만 나는 항상 그렇지 않다.
해야 할 일
외래 키 열
해야 할 일
해야 할 일
기본 키 열
해야 할 일
해야 할 일
인덱스 열 자주 수정
하지만 나는 항상 그렇지 않다.
해야 할 일
실제로 앞의 클러스터된 인덱스와 비클러스터된 인덱스 정의의 예를 통해 위 표를 이해할 수 있습니다. 예를 들어, 데이터 범위를 반환합니다. 예를 들어, 테이블에 시간 열이 있는 경우 해당 열에 클러스터된 인덱스를 설정하기만 하면 됩니다. 이 시점에서 2004 년 6 월 65438+ 10 월 1 6 월 65438+ 10 월 1 에 대한 모든 데이터를 조회할 때 클러스터되지 않은 인덱스와 달리 목차의 각 데이터에 해당하는 페이지 번호를 먼저 찾은 다음 페이지 번호를 기준으로 특정 내용을 찾아야 합니다.
(c) 실제 상황과 결합하여 지표 사용의 오해에 대해 이야기하십시오.
이론의 목적은 응용이다. 클러스터된 인덱스 또는 클러스터되지 않은 인덱스를 사용해야 하는 시기를 방금 나열했지만 실제 작업에서는 이러한 규칙을 쉽게 무시하거나 실제 상황에 따라 전체 분석을 수행할 수 없습니다. 이제 실제로 마주친 실제 문제를 근거로 지수 사용에 대한 오해에 대해 이야기하고, 기하급수적 건립 방법을 숙지할 수 있도록 하겠습니다. (데이비드 아셀, Northern Exposure (미국 TV 드라마), 성공명언)
1, 기본 키는 클러스터된 인덱스입니다.
저자는 이런 생각이 매우 잘못된 것이며, 인덱스를 모으는 낭비라고 생각한다. 기본적으로 SQL 서버는 기본 키에 클러스터된 인덱스를 설정합니다.
일반적으로 각 데이터를 구분하기 위해 각 테이블에 ID 열을 만듭니다. 이 ID 열은 자동으로 증분됩니다. 단계는 일반적으로 1 입니다. Dell 의 사무 자동화 예제에서 열 Gid 는 이러한 상황입니다. 이 시점에서 이 열을 기본 키로 설정하면 SQL SERVER 는 기본적으로 이 열을 클러스터된 인덱스로 설정합니다. 이렇게 하면 데이터가 데이터베이스에서 ID 별로 물리적으로 정렬될 수 있다는 장점이 있지만, 나는 별로 의미가 없다고 생각한다.
클러스터된 인덱스의 장점은 분명합니다. 테이블당 하나의 클러스터된 인덱스 규칙만 있을 수 있으므로 클러스터된 인덱스가 더 소중합니다.
앞에서 설명한 클러스터된 인덱스의 정의에서 알 수 있듯이 클러스터된 인덱스를 사용하는 가장 큰 장점은 쿼리 요구 사항에 따라 전체 테이블을 스캔하지 않고도 쿼리 범위를 빠르게 좁힐 수 있다는 것입니다. 실제 응용에서는 주민등록번호가 자동으로 생성되기 때문에 각 레코드의 주민등록번호를 모르기 때문에 실제 운영에서는 주민등록번호로 조회하기가 어렵습니다. 이로 인해 ID 번호의 기본 키를 클러스터된 인덱스로 사용하는 것은 자원 낭비입니다. 둘째, ID 번호가 다른 필드를 클러스터된 인덱스로 사용하는 것은 "많은 다른 값이 있을 때 클러스터된 인덱스를 설정해서는 안 된다" 는 규칙을 따르지 않습니다. 물론 이 상황은 사용자가 레코드의 내용, 특히 인덱스 항목을 자주 수정할 때만 부정적인 영향을 미치지만 쿼리 속도에는 영향을 주지 않습니다.
사무 자동화 시스템에서는 시스템 홈 페이지에 사용자 서명이 필요한 문서, 모임, 사용자 쿼리가 필요한 문서 등 데이터 쿼리는 "날짜" 필드와 사용자 자신의 "사용자 이름" 필드를 빼놓을 수 없습니다.
일반적으로 사무 자동화 홈페이지에는 각 사용자가 아직 서명하지 않은 문서나 모임이 표시됩니다. Where 문은 현재 사용자가 서명하지 않은 상황만 제한할 수 있지만, 시스템 구축 시간이 길고 데이터 양이 많은 경우 각 사용자가 홈페이지를 열 때마다 전체 테이블을 스캔하는 것은 의미가 없습니다. 대부분의 사용자는 1 개월 전에 이미 파일을 찾아보았기 때문에 데이터베이스 비용만 증가할 수 있습니다. 실제로 사용자가 시스템 홈 페이지를 열면 데이터베이스는 사용자가 거의 3 개월 동안 읽지 않은 파일만 쿼리할 수 있으며 날짜 필드를 통해 양식 스캔을 제한하여 쿼리 속도를 높입니다. 만약 당신의 사무 자동화 시스템이 이미 2 년 동안 구축되었다면, 당신의 홈페이지는 이론적으로 8 배 이상 빨라질 것입니다. (데이비드 아셀, Northern Exposure (미국 TV 드라마), 사무실명언)
여기에' 이론상' 이라는 단어가 언급되는 이유는 클러스터된 색인이 맹목적으로 기본 키 ID 에 구축되어 있다면, 필드' Date' 에 인덱스 (비클러스터된 인덱스) 를 만들어도 쿼리 속도가 그렇게 높지 않기 때문이다. 654.38+00 만 개의 데이터 (3 개월 25 만 개의 데이터) 에서 다양한 쿼리의 속도 성능을 살펴보겠습니다.
(1) 기본 키에만 클러스터된 인덱스를 생성합니다. 기간을 분할하지 않습니다.
원문에서 GID, fariqi, neibuyonghu, title 을 선택하다
시간: 128470 밀리초 (즉 128 초)
(2) 기본 키에 클러스터된 인덱스를 생성하고 fariq 에 클러스터되지 않은 인덱스를 생성합니다.
원문에서 GID, fariqi, neibuyonghu, title 을 선택하다
여기서 fariqi & gtdateadd(day, -90, getdate ())
시간: 53,763ms (54 초)
(3) 날짜 행에 집계 인덱스 생성 (fariqi):
원문에서 GID, fariqi, neibuyonghu, title 을 선택하다
여기서 fariqi & gtdateadd(day, -90, getdate ())
시간: 2423 밀리초 (2 초)
각 명령문은 250,000 개의 데이터를 추출하지만, 특히 클러스터된 인덱스가 날짜 열을 기반으로 하는 경우 모든 경우에 큰 차이가 있을 수 있습니다. 실제로 데이터베이스에 654.38+00 만 용량이 있다면 ID 열에 기본 키를 설정합니다. 위의 654.38+0 과 2 와 마찬가지로 웹 페이지의 성능이 시간 초과되어 전혀 표시되지 않습니다. 이것은 내가 ID 열을 클러스터된 인덱스로 포기하는 가장 중요한 요소이기도 하다.
위의 속도를 얻는 방법은 각 select 문 앞에: declare @d datetime 을 추가하는 것입니다.
Set @d=getdate ()
Select 문 뒤에 다음을 추가합니다.
Select[ 문 실행 시간 (밀리초) ]=datediff (밀리초, @d, getdate ())
2. 색인이 만들어지면 질의 속도가 크게 향상될 수 있습니다.
사실, 위의 예에서 두 번째와 세 번째 명령문은 정확히 동일하며 인덱싱된 필드도 동일하다는 것을 알 수 있습니다. 유일한 차이점은 fariqi 필드에 클러스터되지 않은 인덱스를 만들고, 이 필드에 클러스터된 인덱스를 만들지만 쿼리 속도가 매우 다르다는 것입니다. 따라서 단순히 모든 필드를 색인화하여 쿼리 속도를 높이는 것은 아닙니다.
테이블을 만든 문에서 볼 수 있듯이 10 만 데이터가 있는 이 테이블의 fariqi 필드에는 5,003 개의 다른 레코드가 있습니다. 이 분야에서 종합지수를 세우는 것이 적절하다. 실제로 우리는 매일 몇 개의 서류를 보내는데, 이 서류들의 날짜는 모두 동일하며,' 절대다수도 아니고 소수도 아니다' 는 법칙에 완전히 부합한다. 이러한 관점에서 볼 때, 쿼리 속도를 높이기 위해 "적절한" 클러스터 인덱스를 만드는 것이 중요합니다.
3. 질의 속도를 높여야 하는 모든 필드를 클러스터된 인덱스에 추가하여 질의 속도를 높입니다.
앞서 설명한 바와 같이, 데이터 조회 시 필수 필드는 "날짜" 와 사용자 자신의 "사용자 이름" 입니다. 이 두 필드는 매우 중요하기 때문에 이들을 결합하여 복합 색인을 만들 수 있습니다.
클러스터된 인덱스에 필드 중 하나를 추가하면 질의 속도가 향상될 수 있다고 생각하는 사람들도 많고, 복합 클러스터화된 인덱스 필드를 개별적으로 질의하면 질의 속도가 느려질 수 있다는 의혹도 제기되고 있다. 이 문제를 가지고 다음 질의 속도를 살펴 보겠습니다 (결과 세트는 모두 25 만 개의 데이터): (날짜 열 fariqi 가 복합 클러스터 인덱스 앞에 있고 사용자 이름 neibuyonghu 가 뒤에 있습니다.)
(1)select GID, fariqi, neibuyonghu, title from tgongwen where fari qi > 2004-5-5'
조회 속도: 2513ms
(2) Tgongwen 에서 GID, fariqi, neibuyonghu, title 을 선택합니다. 여기서 fariqi & gt“2004 년 5 월 5 일' 과 내보영합 =' 사무실' 입니다
조회 속도: 2516ms
(3) Select GID, Fariqi, Neibu 후용, Title From Tgongwen Where Neibu 후용 = "office"
조회 속도: 60280ms
위의 실험에서 알 수 있듯이 클러스터된 인덱스의 초기 열만 질의 조건으로 사용하면 복합 클러스터된 인덱스의 모든 열과 동시에 질의되는 속도가 거의 동일하며, 쿼리 결과 세트 수가 동일한 경우 모든 복합 인덱스 열보다 약간 빠르다는 것을 알 수 있습니다. 그러나 복합 합계 인덱스의 시작되지 않은 열만 질의 조건으로 사용하면 인덱스가 아무런 영향을 주지 않습니다. 물론 문 1 과 2 의 쿼리 속도는 동일합니다. 쿼리 항목 수가 동일하기 때문입니다. 복합 인덱스의 모든 열을 사용하는 경우 질의 결과가 적으면 "인덱스 오버레이" 가 형성되어 성능이 최적화됩니다. 또한 합산 인덱스의 다른 열을 자주 사용하는지 여부에 관계없이 선행 열은 가장 자주 사용되는 열이어야 합니다.
(d) 다른 책에 없는 색인을 사용한 경험을 요약한다.
1. 합산 인덱스를 사용하는 것이 비합산 인덱스를 사용하는 기본 키보다 빠릅니다.
다음은 샘플 문입니다. (25 만 개의 데이터 모두 추출)
선택 GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi =' 2004-9-16'
사용 시간: 3326 밀리초
Select GID, fariqi, neibuyonghu, reader, title from tgongwen where GID< = 250000
사용 시간: 4470 밀리초
여기서 클러스터된 인덱스를 사용하는 것은 클러스터되지 않은 인덱스를 사용하는 기본 키보다 거의 1/4 빠릅니다.
2. 특히 데이터 양이 적은 경우 일반 기본 키를 order by 로 사용하는 것보다 합산 인덱스를 사용하는 것이 더 빠릅니다.
파리키의 순서에 따라 Tgongwen 에서 GID, 파리키, 내보영호, 독자, 제목을 선택합니다
시간: 12936
Gid 순서대로 Tgongwen 에서 GID, fariqi, neibuyonghu, reader, title 을 선택합니다
시간: 18843
여기서 클러스터된 인덱스를 사용하는 것이 일반 키를 order by 로 사용하는 것보다 3/ 10 빠릅니다. 실제로 데이터 양이 적은 경우 클러스터된 인덱스를 정렬 열로 사용하는 것이 클러스터되지 않은 인덱스를 사용하는 것보다 훨씬 빠릅니다. 그러나 654.38+ 만 이상과 같이 데이터 양이 많으면 속도 차이가 뚜렷하지 않습니다.
3. 클러스터된 인덱스의 기간을 사용하면 사용 중인 클러스터된 인덱스 수에 관계없이 검색 시간이 전체 데이터 테이블의 데이터 비율에 비례하여 줄어듭니다.
Select GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi > 2004- 1- 1'
시간: 6343ms (1 백만 세그먼트 추출)
Select GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi > 2004-6-6'
시간: 3 170 밀리초 (50 만 개 추출)
선택 GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi =' 2004-9-16'
시간: 3326 밀리초 (이전 문장과 정확히 동일). 세트의 수가 동일하면 보다 큼 및 등호는 같습니다.)
Select GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi > 2004- 1- 1' 및 fariqi & lt'2004-6-6'
시간: 3280 밀리초
4. 일자 열은 분 및 초 입력으로 인해 질의 속도가 느려지지 않습니다.
다음 예에서 * * * 에는 1 만 개의 데이터가 있고, 2004 년 1 뒤에는 50 만 개의 데이터가 있지만, 날짜가 정확히 2 개의 다른 날짜만 있습니다. 50 만 개의 데이터와 5000 개의 다른 날짜가 있는데, 날짜는 초까지 정확하다.
Select GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi > Fariqi 의 2004- 1- 1' 주문
시간: 6390 밀리초
Select GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi < Fariqi 의 2004- 1- 1' 주문
시간: 6453ms
(5) 기타 고려 사항
"물은 배를 실을 수 있고, 배를 뒤집을 수도 있다." 지수도 마찬가지다. 색인은 검색 성능을 향상시키는 데 도움이 되지만 너무 많거나 부적절한 색인으로 인해 시스템 효율성이 떨어질 수 있습니다. 사용자가 테이블에 인덱스를 추가할 때마다 데이터베이스는 더 많은 작업을 수행해야 하기 때문입니다. 인덱스가 너무 많으면 인덱스 조각이 발생할 수도 있습니다.
따라서 "적절한" 인덱스 시스템, 특히 합산 인덱스 생성을 구축하여 데이터베이스가 높은 성능을 얻을 수 있도록 완벽을 추구해야 합니다.
물론 실제로 심각한 데이터베이스 관리자로서 더 많은 시나리오를 테스트하여 어느 것이 가장 효율적이고 효과적인지 확인해야 합니다.
둘째, SQL 문을 개선합니다
많은 사람들은 SQL server 에서 SQL 문이 어떻게 실행되는지 모르고 자신의 SQL 문이 SQL SERVER 에 의해 오해될까 봐 걱정합니다. 예를 들면 다음과 같습니다.
Select * from table 1 여기서 name =' zhangsan' and tid>10000
및 구현:
Select * from table 1 여기서 tID> 10000, name=' 장 3'
어떤 사람들은 위의 두 명령문의 실행 효율성이 같은지 알지 못한다. 왜냐하면 단순히 명령문의 순서를 보면 이 두 명령문은 확실히 다르기 때문이다. TID 가 합산 인덱스인 경우 마지막 문장은 테이블에서 10000 이후의 레코드에서만 찾을 수 있습니다. 이전 문장은 먼저 전체 테이블에서 몇 가지 name=' 장 3' 을 찾은 다음 제한 조건에 따라 tid >；; 10000 을 사용하여 질의 결과를 표시합니다.
사실 이런 걱정은 불필요하다. SQL SERVER 에는 where 절의 검색 조건을 계산하고 테이블 스캔의 검색 공간을 줄일 수 있는 인덱스를 결정하는 질의 분석 최적기가 있습니다. 자동 최적화가 가능합니다.
질의 최적기는 where 절에 따라 질의를 자동으로 최적화할 수 있지만 질의 최적기의 작동 방식을 이해해야 합니다. 그렇지 않으면 쿼리 최적기가 원하는 대로 빠른 쿼리를 수행하지 않는 경우가 있습니다.
질의 분석 단계에서 질의 최적기는 질의의 각 단계를 보고 스캔해야 하는 데이터의 양을 제한하는 것이 유용한지 여부를 결정합니다. 단계를 스캔 매개 변수 (SARG) 로 사용할 수 있는 경우 최적화 가능하다고 하며 이 인덱스를 사용하여 필요한 데이터를 신속하게 얻을 수 있습니다.
SARG 정의: 일반적으로 특정 일치, 한 값 범위 내의 일치 또는 두 개 이상의 조건에 대한 AND 연결을 참조하므로 검색을 제한하는 데 사용되는 작업입니다. 형식은 다음과 같습니다.
열 이름 연산자
또는
& lt 상수 또는 변수 >; 연산자 열 이름
열 이름은 연산자 한 쪽에 나타날 수 있고 상수나 변수는 연산자 다른 쪽에 나타날 수 있습니다. 예를 들면 다음과 같습니다.
Name=' 장삼' 입니다
가격 > 5000
5000 & lt 가격
Name=' 장삼' 가격 > 5000
표현식이 SARG 형식을 충족하지 못하면 검색 범위를 제한할 수 없습니다. 즉, SQL 서버는 각 행에 대해 WHERE 절의 모든 조건을 충족하는지 확인해야 합니다. 따라서 SARG 형식에 맞지 않는 표현식에는 색인이 쓸모가 없습니다.
SARG 를 소개한 후 SARG 사용 경험과 실제로 일부 자료에서 얻은 다양한 결론을 요약해 보겠습니다.
1, Like 문이 SARG 에 속하는지 여부는 사용하는 와일드카드 유형에 따라 달라집니다.
예: 같은 이름의' 장%' 는
그리고: 같은 이름을 가진'% 장' 은 속하지 않습니다.
그 이유는 문자열의 와일드 카드 문자% 로 인해 색인을 사용할 수 없기 때문입니다.
2. 그렇지 않으면 전체 테이블 스캔이 발생합니다.
Name=' 장삼' 과 가격 > 5000 기호 SARG, Name=' 장삼' 또는 가격 >; 5000 은 SARG 와 일치하지 않습니다. 전체 테이블 스캔을 사용하거나 발생시킵니다.
3. 비연산자 및 함수로 인해 SARG 형식을 따르지 않는 명령문입니다.
SARG 와 일치하지 않는 명령문의 가장 일반적인 경우는 NOT,! =,<& gt,! & lt,! & gt, 존재하지 않고, 존재하지 않고, 좋아하지 않습니다. , 기능을 제외하고. SARG 와 일치하지 않는 몇 가지 예가 있습니다.
ABS (가격) < 5000
비슷한 이름의'% 3' 입니다
다음과 같은 표현:
여기서 가격 * 2 & gt5000
SQL SERVER 도 SARG 로 간주됩니다. SQL SERVER 는 이 공식을 다음과 같이 변환합니다.
여기서 가격은 2500/2 보다 큽니다
그러나 SQL SERVER 는 이러한 변환이 원래 표현식과 정확히 동일하다고 보장할 수 없는 경우가 있기 때문에 권장되지 않습니다.
4, IN 의 역할은 OR 와 같습니다.
선언:
Select * from table1where tid in (2,3)
그리고
Select * from table 1, 여기서 tid=2 또는 tid=3 입니다
마찬가지로 전체 테이블 스캔이 발생할 수 있으며 tid 에 색인이 있는 경우 색인이 유효하지 않습니다.
5. 가급적 사용하지 마세요. 적을수록 좋습니다
6.exists 와 in 의 실행 효율성은 동일합니다.
많은 양의 데이터에 따르면 exists 는 in 보다 효율적이며 가능한 한 not in 대신 not exists 를 사용해야 합니다. 하지만 사실 제가 해봤는데, 앞에 not 가 있든 없든 간에 둘 사이의 실행 효율성이 동일하다는 것을 알게 되었습니다. (윌리엄 셰익스피어, Not, Northern Exposure (미국 TV 드라마), 성공명언) 하위 쿼리를 포함하기 때문에 이번에는 SQL 서버와 함께 제공되는 pubs 데이터베이스를 사용하려고 합니다. 실행하기 전에 SQL SERVER 의 통계 입출력 상태를 열 수 있습니다.
(1)select title, price from title _ id where (select title _ id from sales where qty > (30)
이 문장의 실행 결과는 다음과 같습니다.
양식' 판매'. 스캔 수는18,56 개의 논리적 판독값, 0 개의 물리적 판독값 및 0 개의 사전 판독값입니다.
표 "제목". 스캔 수는 1, 논리적 두 번 읽기, 물리적 읽기 0, 미리 읽기 0 입니다.
(2)select title, price from titles where exists (select * from sales where sales.title _ id = titles.title) (30)
두 번째 문장의 실행 결과는 다음과 같습니다.
양식' 판매'. 스캔 수는18,56 개의 논리적 판독값, 0 개의 물리적 판독값 및 0 개의 사전 판독값입니다.
표 "제목". 스캔 수는 1, 논리적 두 번 읽기, 물리적 읽기 0, 미리 읽기 0 입니다.
지금부터 exists 를 사용한 실행 효율은 in 을 사용하는 것과 동일하다는 것을 알 수 있습니다.
7. charindex () 함수를 사용하는 것은 앞에 와일드 카드 문자% 를 추가하는 것과 같습니다.
앞서 말씀드렸듯이, LIKE 앞에 와일드 카드 문자% 를 추가하면 전체 테이블 스캔이 가능하므로 비효율적입니다. 그러나 일부 자료에 따르면 LIKE 를 함수 charindex () 로 바꾸면 속도가 크게 빨라진다. 제 실험을 통해, 저는 이 설명도 틀렸다는 것을 알게 되었습니다.
Select GID, title, fariqi, reader from tgongwen where char index ('형사팀', reader)& gt；; 0 과 fariqi & gt'2004-5-5'
시간: 7 초, 기타: 스캔 수 4, 논리 읽기 7 155 회, 물리적 읽기 0 회, 미리 읽기 0 회.
Select GID, title, fariqi, reader from tgongwen 여기서 reader like'%'+' 형사분대'+'%'와 fariqi & gt' 2004'
시간: 7 초, 기타: 스캔 수 4, 논리 읽기 7 155 회, 물리적 읽기 0 회, 미리 읽기 0 회.
8. 연합은 절대 또는 보다 효과적이지 않습니다.
Where 절에 or 을 사용하면 전체 테이블이 스캔된다고 이미 언급했습니다. 전반적으로, 내가 본 모든 자료는 이곳의 or 대신 union 을 추천한다. 사실은 이 말이 그들 대다수에게 적용된다는 것을 증명했다.
Tgongwen 에서 GID, fariqi, neibuyonghu, reader, title 을 선택합니다. 여기서 fariqi='2004-9- 16' 또는 GID 입니다
시간: 68 초. 스캔 수 1, 논리 읽기 404008 회, 물리적 읽기 283 회, 사전 읽기 392 163 회.
선택 GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi =' 2004-9-16'
리그
Select GID, fariqi, neibuyonghu, reader, title from tgongwen where GID> 9990000
시간: 9 초. 스캔 수는 8 개, 논리적 읽기는 67489 회, 물리적 읽기는 2 16 회, 사전 읽기는 7499 회입니다.
정상적인 상황에서는 or 보다 union 을 사용하는 것이 훨씬 효율적일 수 있습니다.
그러나 필자는 실험을 통해 or 양쪽의 쿼리 열이 같으면 union 을 사용하는 속도가 or 을 사용하는 것보다 훨씬 나쁘다는 것을 발견했다. 비록 union 이 이곳의 색인을 스캔하지만 or 은 전체 테이블을 스캔한다. (알버트 아인슈타인, Northern Exposure (미국 TV 드라마), Northern Exposure
Tgongwen 에서 GID, fariqi, neibuyonghu, reader, title 을 선택합니다. 여기서 fariqi='2004-9- 16' 또는 fariqi 입니다
시간: 6423 밀리초. 스캔 수 2, 논리적 읽기 14726 회, 물리적 읽기 1 회, 미리 읽기 7 176 회.
선택 GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi =' 2004-9-16'
리그
선택 GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi =' 2004-2-5'
시간: 1 1640ms. 검사 수는 8, 논리적 읽기 14806 회, 물리적 읽기 108 회, 사전 읽기 1 144 회입니다.
9. 현장 추출은' 필요한 만큼, 얼마를 추출한다' 는 원칙을 따라야' 선택 *' 을 피할 수 있다
한 가지 실험을 해보죠.
Gid desc 순서로 tgongwen 에서 앞 10000 GID, fariqi, reader, title 을 선택합니다
시간: 4673ms
Gid desc 순서로 tgongwen 에서 top 10000 GID, fariqi, title 을 선택합니다
시간:1376ms
Gid desc 순서로 tgongwen 에서 top 10000 GID, fariqi 를 선택합니다
시간: 80ms
이러한 관점에서 볼 때, 필드를 하나 적게 추출할 때마다 데이터 추출 속도가 그에 따라 빨라집니다. 승천의 속도는 네가 버린 필드의 크기에 달려 있다.
10 과 count(*) 는 count (field) 보다 느리지 않습니다.
* 를 사용하면 모든 행을 집계할 수 있다는 자료가 있는데, 한 세계를 나열하는 것보다 훨씬 효율적이지 않다. 이런 견해는 사실 근거가 없다. 자, 이제 ：
Tgongwen 에서 count(*) 를 선택합니다
시간: 1500 밀리초
Tgongwen 에서 개수 (GID) 를 선택합니다
시간:1483ms
Tgongwen 에서 count(fariqi) 를 선택합니다
시간: 3 140 밀리초
Tgongwen 에서 개수 (제목) 를 선택합니다
시간: 52050 밀리초
위에서 알 수 있듯이 count(*) 와 count (기본 키) 를 사용하는 속도는 같지만 count(*) 가 기본 키를 제외한 어떤 필드보다 빠르며 필드가 길수록 요약 속도가 느려집니다. Count(*) 를 사용하면 SQL SERVER 에서 자동으로 가장 작은 필드를 찾아 요약할 수 있다고 생각합니다. 물론, count 를 직접 쓰면 더 직접적일 것이다.
1 1, order by 는 클러스터된 인덱스 열별로 가장 효율적인 정렬입니다.
자, (GID 는 기본 키이고 fariqi 는 합산 인덱스 열입니다.)
Tgongwen 에서 top 10000 GID, fariqi, reader, title 을 선택합니다
시간: 196 밀리초. 스캔 수 1, 논리적 읽기 289 회, 물리적 읽기 1 회, 사전 읽기 1527 회.
Tgongwen 에서 앞 10000 GID, fariqi, reader, title 을 선택하여 GID ASC 별로 정렬합니다
시간: 4720ms. 스캔 수는 1, 논리적 읽기는 4 1956 회, 물리적 읽기는 0 회, 사전 읽기는 1287 회입니다.
Gid desc 순서로 tgongwen 에서 앞 10000 GID, fariqi, reader, title 을 선택합니다
시간: 4736 밀리초. 스캔 수 1, 논리적 읽기 55350 회, 물리적 읽기 10 회, 미리 읽기 775 회.
전면 10000 GID, fariqi, reader, title from tgongwen order by fari qi ASC 를 선택합니다
시간:173ms. 스캔 수 1, 논리적 읽기 290 회, 물리적 읽기 0 회, 사전 읽기 0 회.
Desc farrich 의 문장 순서에서 10000 의 GID, farrich, 독자, 제목을 선택합니다
시간: 156 밀리 초. 스캔 수 1, 논리적 읽기 289 회, 물리적 읽기 0 회, 사전 읽기 0 회.
위에서 볼 수 있듯이 비정렬 속도 및 논리적 읽기 횟수는 클러스터된 인덱스 열별 정렬 속도와 비슷하지만 비클러스터된 인덱스 열별 정렬 질의보다 훨씬 빠릅니다.
또한 필드를 기준으로 정렬할 때 양의 순서든 역순이든 속도는 기본적으로 동일합니다.
12, 고효율 탑
실제로 대용량 데이터 세트를 쿼리하고 추출할 때 데이터베이스 응답 시간에 영향을 미치는 가장 큰 요소는 데이터 검색이 아니라 물리적 I/O 작업입니다. 예를 들면 다음과 같습니다.
Top 10 * from (
Tgongwen 에서 top 10000 GID, fariqi, title 을 선택합니다
여기서 내보영화는' 사무실' 이다
(기드 ·desc 가 주문)
Gid ASC 별 정렬
이론적으로 이 문은 절의 실행 시간보다 더 오래 실행되어야 하지만 사실은 정반대이다. 절이 실행된 후 10000 개의 레코드가 반환되고 전체 명령문은 10 개의 문만 반환되기 때문에 데이터베이스 응답 시간에 가장 큰 영향을 미치는 요소는 물리적 I/O 작업입니다. 여기서 물리적 I/O 작업을 제한하는 가장 효과적인 방법 중 하나는 TOP 키워드를 사용하는 것입니다. TOP 키워드는 SQL SERVER 에서 시스템에 의해 최적화된 단어로, 상위 또는 상위 백분율 데이터를 추출하는 데 사용됩니다. 저자의 실천에 응용한 결과, TOP 이 정말 유용하고 효율도 높다는 것을 알게 되었다. 하지만 이 단어는 다른 대형 데이터베이스 ORACLE 에 없어 아쉬울 수는 없지만, ORACLE 에서는 rownumber 와 같은 다른 방법으로 해결할 수 있습니다. 앞으로 "천만 레벨 데이터 구현을 위한 페이지 표시 및 저장 프로시저" 에 대한 논의에서 키워드 TOP 을 사용합니다.
지금까지 대용량 데이터베이스에서 필요한 데이터를 신속하게 찾는 방법에 대해 논의했습니다. 물론, 우리가 소개하는 이 방법들은 모두' 소프트' 방법이다. 실제로 네트워크 성능, 서버 성능, 운영 체제 성능, 네트워크 카드 및 스위치와 같은 다양한 "하드" 요소를 고려해야 합니다.