SQLServer 인덱스 구조 및 사용 (1)

실제로 색인을 특수 카탈로그로 간주할 수 있습니다. Microsoft 의 SQL 서버는 클러스터된 인덱스와 비클러스터된 인덱스의 두 가지 인덱스를 제공합니다. 다음은 클러스터된 인덱스와 클러스터되지 않은 인덱스의 차이점을 설명합니다.

사실, 우리의 중국어 사전의 텍스트 자체는 바로 집합색인이다. 예를 들어,' 안' 이라는 단어를 찾아보면 당연히 사전의 처음 몇 페이지를 펼친다.' 안' 의 병음은' an' 이기 때문이다. 병음별로 한자를 정렬하는 사전은 영문자' a' 로 시작하고' z' 로 끝나므로' 안' 자는 자연스럽게 사전에 배열된다 만약 당신이' A' 로 시작하는 모든 부분을 찾은 후에 그 단어를 찾을 수 없다면, 그것은 그 단어가 당신의 사전에 없다는 것을 의미합니다. 마찬가지로, "장" 이라는 단어를 찾아보면 사전의 마지막 부분까지 넘어갑니다. "장" 의 병음은 "장" 이기 때문입니다. 즉, 사전 자체의 주체는 디렉토리이므로 찾아야 할 내용을 찾기 위해 다른 디렉토리를 찾을 필요가 없습니다. 우리는 이런 텍스트 내용 자체를 일정한 규칙에 따라 배열된 목차' 클러스터된 색인' 이라고 부른다.

만약 네가 한 단어를 안다면, 너는 아주 빨리 자동사전에서 찾을 수 있다. 그러나 당신은 당신이 모르는 단어를 만날 수 있습니다, 당신은 그 발음을 모릅니다. 이때 방금 찾으려던 단어를 찾을 수 없고, 옆쪽에 따라 원하는 단어를 찾은 다음, 바로 한 페이지로 넘어가서 단어 뒤의 페이지 번호에 따라 원하는 단어를 찾아야 한다. (윌리엄 셰익스피어, 햄릿, 독서명언) 그러나 부수 카탈로그와 사전에서 찾은 단어의 정렬은 텍스트의 실제 정렬 방법이 아닙니다. 예를 들어, "장" 이라는 단어를 찾으면 "장" 이라는 단어가 어근 검색 후 사전의 페이지 번호는 672 이고, "풀" 이라는 단어는 사전의 "장" 단어 위에 있지만 페이지 번호는 63 이고 그 아래에 있는 것을 볼 수 있습니다. 분명히, 이 단어들은 실제로' 장' 자 위와 아래에 있는 것이 아니다. 지금 보시는 연속어' 지연, 장, 석궁' 은 실제로는 비집수 인덱스에서의 순서이며, 사전체의 단어가 비집수 인덱스에서의 매핑입니다. 이런 식으로 필요한 단어를 찾을 수 있지만 목차에서 결과를 찾은 다음 원하는 페이지 번호로 전환하는 두 가지 절차가 필요합니다. 우리는 이 디렉토리를 순수 디렉토리이고, 텍스트가 일반 텍스트인 정렬 방법을' 비집합 색인' 이라고 부른다.

위의 예를 통해 클러스터된 인덱스와 비클러스터된 인덱스가 무엇인지 알 수 있습니다. 더 확장함으로써 카탈로그는 한 가지 방식으로만 정렬할 수 있기 때문에 테이블당 하나의 클러스터된 인덱스만 있을 수 있다는 것을 쉽게 이해할 수 있습니다.

둘째, 클러스터된 인덱스나 클러스터되지 않은 인덱스를 사용할 때입니다.

다음 표는 클러스터된 인덱스 또는 클러스터되지 않은 인덱스 사용 시기 (중요) 를 요약합니다.

작업 설명 클러스터되지 않은 인덱스 열을 사용하는 클러스터된 인덱스는 일반적으로 그룹화되고 정렬됩니다. 일정 범위의 데이터를 반환해야 합니다. 하나 이상의 다른 값을 가질 수 없습니다. 소량의 다른 값이 있어서는 안 된다. 많은 다른 값이 있어서는 안 된다. 자주 업데이트되는 열이 있어서는 안 된다. 외래 키 열이 없어야 합니다. 색인 열을 자주 수정해야 합니다.

실제로 앞의 클러스터된 인덱스와 클러스터되지 않은 인덱스의 정의 예제를 통해 위 테이블을 이해할 수 있습니다. 예를 들어, 데이터 범위를 반환합니다. 예를 들어, 테이블에 시간 열이 있는 경우 해당 열에 합산 인덱스를 하나만 설정하면 됩니다. 이 시점에서 2004 년 6 월 65438+ 10 월 1 부터 6 월 65438+ 10 월 1 까지의 모든 데이터를 조회할 때 클러스터되지 않은 인덱스와 달리 목차의 각 데이터에 해당하는 페이지 번호를 먼저 찾은 다음 페이지 번호를 기준으로 특정 내용을 찾아야 합니다.

셋째, 실제 상황과 결합하여 지표 사용의 오해에 대해 이야기한다.

이론의 목적은 응용이다. 클러스터된 인덱스 또는 클러스터되지 않은 인덱스를 사용해야 하는 시기를 방금 나열했지만 실제로는 이러한 규칙을 쉽게 무시하거나 실제 상황에 따라 전체 분석을 수행할 수 없습니다. 우리는 실천에서 마주친 실제 문제를 근거로 지표 사용의 오해에 대해 이야기하고, 모두가 지표 건립 방법을 파악할 수 있도록 하겠습니다.

1, 기본 키는 클러스터된 인덱스입니다.

저자는 이런 생각이 매우 잘못된 것이며, 인덱스를 모으는 낭비라고 생각한다. 기본적으로 SQL 서버는 기본 키에 클러스터된 인덱스를 설정합니다.

일반적으로 각 데이터를 구분하기 위해 각 테이블에 ID 열을 만듭니다. 이 ID 열은 자동으로 증가합니다. 단계는 일반적으로 1 입니다. Dell 의 사무 자동화 예제에서 열 Gid 는 이러한 상황입니다. 이 시점에서 이 열을 기본 키로 설정하면 SQL SERVER 는 기본적으로 이 열을 클러스터된 인덱스로 설정합니다. 이렇게 하면 데이터가 데이터베이스에서 ID 별로 물리적으로 정렬될 수 있다는 장점이 있지만 의미가 크지 않다고 생각합니다.

클러스터된 인덱스의 장점은 분명하며, 테이블당 하나의 클러스터된 인덱스 규칙만 있을 수 있으므로 클러스터된 인덱스를 더욱 소중하게 만들 수 있습니다.

앞서 언급한 클러스터된 인덱스의 정의에서 알 수 있듯이 클러스터된 인덱스를 사용하면 전체 테이블을 스캔하지 않고도 쿼리 요구 사항에 따라 쿼리 범위를 빠르게 좁힐 수 있다는 장점이 있습니다. 실제 앱에서는 주민등록번호가 자동으로 생성되기 때문에 각 기록의 주민등록번호를 모르기 때문에 실제로 주민등록번호로 조회하기가 어렵다. 이로 인해 ID 번호의 기본 키를 클러스터된 인덱스로 사용하는 것은 자원 낭비입니다. 둘째, ID 번호가 다른 필드를 클러스터된 인덱스로 사용하는 것은 "많은 다른 값이 있을 때 클러스터된 인덱스를 만들어서는 안 된다" 는 규칙을 따르지 않습니다. 물론 이 상황은 사용자가 레코드 내용, 특히 인덱스 항목을 자주 수정할 때만 부정적인 영향을 미치지만 쿼리 속도에는 영향을 주지 않습니다.

사무 자동화 시스템에서 사용자 서명이 필요한 문서, 모임 또는 사용자 쿼리가 필요한 시스템 홈 페이지에 표시되는 문서에 관계없이 데이터 쿼리는 "날짜" 필드와 사용자 자신의 "사용자 이름" 필드를 빼놓을 수 없습니다.

일반적으로 사무 자동화 홈페이지에는 각 사용자가 아직 서명하지 않은 문서나 모임이 표시됩니다. Where 문은 현재 사용자가 로그인하지 않은 상황만 제한할 수 있지만, 시스템이 오랫동안 구축되어 있고 대량의 데이터가 있는 경우 사용자가 홈페이지를 열 때마다 전체 테이블을 스캔하는 것은 의미가 없습니다. 대부분의 사용자는 이미 1 개월 전에 이 파일들을 찾아보았으며, 이는 데이터베이스 비용만 증가시킬 뿐이다. 실제로 사용자가 시스템 홈 페이지를 열면 데이터베이스는 사용자가 지난 3 개월 동안 읽지 않은 파일만 쿼리할 수 있으며, 날짜 필드를 통해 양식 스캔을 제한하여 질의 속도를 높입니다. 만약 당신의 사무 자동화 시스템이 이미 2 년 동안 건립되었다면, 당신의 홈페이지는 속도 이론이 8 배 혹은 더 빠를 것입니다.

이론상' 이라는 단어를 언급하는 이유는 클러스터된 인덱스가 여전히 맹목적으로 기본 키 ID 에 구축되어 있다면 필드 "날짜" 에 인덱스 (클러스터되지 않은 인덱스) 를 작성하더라도 쿼리 속도가 그리 높지 않기 때문입니다. 654.38+백만 개의 데이터 (3 개월 25 만 개의 데이터) 에서 다양한 쿼리의 속도를 살펴보겠습니다.

(1) 기본 키에만 클러스터된 인덱스를 생성합니다. 기간을 분할하지 않습니다.

Tgongwen 에서 GID, fariqi, neibuyonghu, title 을 선택합니다

시간: 128470 밀리초 (즉 128 초)

(2) 기본 키에 클러스터된 인덱스를 생성하고 fariq 에 클러스터되지 않은 인덱스를 생성합니다.

Tgongwen 에서 GID, fariqi, neibuyonghu, title 을 선택합니다

여기서 fariqi & gtdateadd(day, -90, getdate ())

시간: 53,763ms (54 초)

(3) 날짜 행에 합산 인덱스 생성 (fariqi):

Tgongwen 에서 GID, fariqi, neibuyonghu, title 을 선택합니다

여기서 fariqi & gtdateadd(day, -90, getdate ())

시간: 2423 밀리초 (2 초)

각 명령문은 250,000 개의 데이터를 추출하지만, 특히 클러스터된 인덱스가 날짜 열을 기반으로 하는 경우 다양한 경우의 차이는 엄청납니다. 실제로 데이터베이스에 654.38+00 만 용량이 있다면 ID 열에 기본 키를 설정합니다. 위의 654.38+0 과 2 와 마찬가지로 웹 페이지의 성능이 시간 초과되어 전혀 표시되지 않습니다. 이것은 내가 ID 열을 클러스터된 인덱스로 포기하는 가장 중요한 요소이기도 하다. 위의 속도를 얻는 방법은 다음을 추가하는 것입니다.

선언 @d 날짜 시간

Set @d=getdate ()

Select 문 뒤에 다음을 추가합니다.

Select [문 실행 시간 (밀리초)] =datediff (밀리초, @d, getdate ()

2. 색인이 만들어지면 질의 속도가 크게 향상될 수 있습니다.

사실, 위의 예에서 두 번째와 세 번째 문은 정확히 동일하며 색인 작성에 사용된 필드도 동일하다는 것을 알 수 있습니다. 유일한 차이점은 fariqi 필드에 비합산 인덱스를 만드는 반면, fariqi 필드에 합산 인덱스를 만드는 반면 쿼리 속도는 매우 다르다는 것입니다. 따라서 단순히 조회 속도를 높이기 위해 필드를 색인화하는 것은 아닙니다.

테이블을 구성하는 문에서 테이블의 fariqi 필드에 10 만 개의 서로 다른 레코드가 있는 것을 볼 수 있습니다. 이 분야에서 종합지수를 세우는 것이 적절하다. 실제로 우리는 매일 몇 개의 파일을 보내는데, 이 파일들의 날짜는 모두 같다. 이는' 절대다수도 소수도 아니다' 라는 규칙에 완전히 부합한다. 이러한 관점에서 볼 때, "적절한" 클러스터된 인덱스를 만드는 것은 질의 속도를 높이는 데 매우 중요합니다.

3. 질의 속도를 높여야 하는 모든 필드를 클러스터된 인덱스에 추가하여 질의 속도를 높입니다.

앞서 설명한 바와 같이, 데이터 조회 시 필수 필드는 "날짜" 와 사용자 자신의 "사용자 이름" 입니다. 이 두 필드가 매우 중요하기 때문에 이들을 결합하여 복합 색인을 만들 수 있습니다.

클러스터된 인덱스에 필드를 추가하면 질의 속도가 향상될 수 있다고 생각하는 사람들이 많은데, 복합 클러스터된 인덱스 필드를 개별적으로 질의하면 질의 속도가 느려지는 경우가 많습니다. 이 질문으로 다음 질의 속도를 살펴 보겠습니다 (결과 세트는 모두 250,000 개 데이터). (날짜 열 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) 지드, 파리키, 네부후용, 제목 Tgongwen 중 네부후용 =' 사무실' 을 선택한다

조회 속도: 60280 밀리초

위의 실험에서 알 수 있듯이, 클러스터된 인덱스의 초기 열만 질의 조건으로 사용하는 경우 질의 속도는 복합 클러스터된 인덱스를 동시에 사용하는 모든 열과 거의 동일하며 질의 결과 집합 수가 동일한 경우 모든 복합 인덱스 열보다 약간 빠릅니다. 그러나 복합 합계 인덱스의 시작되지 않은 열만 질의 조건으로 사용하면 인덱스가 아무런 영향을 주지 않습니다. 물론 1 및 2 문의 쿼리 속도는 동일합니다. 쿼리의 항목 수가 동일하기 때문입니다. 복합 인덱스의 모든 열을 사용하고 질의 결과가 거의 없는 경우 "인덱스 재정의" 가 이루어지므로 성능을 얻을 수 있습니다. 또한 합산 인덱스의 다른 열을 자주 사용하는지 여부에 관계없이 선행 열은 가장 자주 사용되는 열이어야 합니다.

넷째, 다른 책에 없는 색인을 사용한 경험을 요약한다

1. 합산 인덱스를 사용하는 것이 비합산 인덱스를 사용하는 기본 키보다 빠릅니다.

다음은 예제 문입니다. (모두 250,000 개의 데이터를 추출했습니다.)

선택 GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi ='' 2004-9-16

사용 시간: 3326 밀리초

선택 GID, fariqi, neibuyonghu, reader, title from Tgongwen 여기서 GID & lt=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'' 입니다

시간: 3170ms (500,000 개 조각 추출)

선택 GID, fariqi, neibuyonghu, reader, title from tgongwen where fari qi ='' 2004-9-16

시간: 3326 밀리초 (이전 문장과 정확히 동일). 세트의 수가 동일하면 보다 큼 및 등호는 같습니다.)

Tgongwen 에서 GID, fariqi, neibuyonghu, reader, title 을 선택합니다

여기서 fariqi & gt'' 2004-1-1'와 fariqi & lt''2004-6-6'' 는

시간: 3280 밀리초

4. 일자 열은 분 및 초를 입력해도 질의 속도가 느려지지 않습니다.

다음 예에서 * * 는 1 만 개의 데이터가 있고 2004 년 1 이후 50 만 개의 데이터가 있지만, 두 개의 다른 날짜만 있고 날짜는 일 단위로 정확합니다. 50 만 개의 데이터와 5000 개의 다른 날짜가 있는데, 날짜는 초까지 정확하다.

Tgongwen 에서 GID, fariqi, neibuyonghu, reader, title 을 선택합니다

여기서 fariqi & gt' 2004-1-1'은 파리키가 주문한다

시간: 6390 밀리초

Tgongwen 에서 GID, fariqi, neibuyonghu, reader, title 을 선택합니다

판파리키 & lt' 2004-1-1'은 파리키가 주문한다

시간: 6453ms

동사 (verb 의 약어) 기타 예방 조치

"물은 배를 싣고 배를 뒤집을 수 있다." 지수도 마찬가지다. 색인은 검색 성능을 향상시키는 데 도움이 되지만 너무 많거나 부적절한 색인으로 인해 시스템 효율성이 떨어질 수 있습니다. 사용자가 테이블에 인덱스를 추가할 때마다 데이터베이스는 더 많은 작업을 수행해야 하기 때문입니다. 인덱스가 너무 많으면 인덱스 조각이 발생할 수도 있습니다.

따라서 "적절한" 인덱스 시스템을 구축해야 합니다. 특히 합산 인덱스를 만들고 완벽을 추구해야 데이터베이스가 높은 성능을 얻을 수 있습니다.

물론 실제로 심각한 데이터베이스 관리자로서 더 많은 시나리오를 테스트하여 어떤 시나리오가 효율적이고 효과적인지 확인해야 합니다.