인덱스. SQL SQL 고유 인덱스의 인덱스
그리고 인덱스, 이것은 특수 조회 테이블, 데이터베이스 검색 엔진이 데이터 검색 속도를 높이는 데 사용할 수 있습니다. 간단히 말해서 인덱스는 테이블의 데이터에 대한 포인터입니다. 데이터베이스의 색인은 책 뒷면의 색인과 매우 유사합니다.
예를 들어, 특정 주제에 관한 책의 모든 페이지에 대한 링크를 원하는 경우 먼저 모든 주제를 알파벳 순서로 나열한 다음 하나 이상의 특정 페이지 번호를 참조하는 색인을 참조하십시오.
색인은 쿼리와 문장의 속도를 높이는 데 도움이 되지만 명령문으로 인해 데이터 입력 속도가 느려집니다. 업데이트그리고 끼워 넣다. 데이터에 영향을 주지 않고 인덱스를 생성하거나 삭제할 수 있습니다.
인덱스 생성에는 명령문이 포함됩니다. 인덱스 생성, 인덱스 이름을 지정하여 테이블과 인덱스할 열을 지정하고 인덱스가 오름차순인지 내림차순인지 여부를 나타낼 수 있습니다.
인덱스는 고유할 수도 있습니다. 고유한, 인덱스는 인덱스가 있는 열 또는 열 조합의 중복 항목을 방지합니다.
CREATE INDEX 명령
기본 구문 인덱스 생성다음과 같이:
CREATE INDEX index_name ON 테이블_이름;
단일 열 인덱스
단일 열 인덱스는 테이블의 한 열에만 생성됩니다. 기본 구문은 다음과 같습니다.
CREATE INDEX index_name ON table_name(컬럼_이름);
고유 인덱스
고유 인덱스는 작업뿐만 아니라 데이터 무결성을 보장하는 데에도 사용됩니다. 고유 인덱스는 테이블에 중복된 값을 삽입하는 것을 허용하지 않습니다. 기본 구문은 다음과 같습니다.
table_name(column_name)에 UNIQUE INDEX index_name을 생성합니다.
복합 인덱스
복합 인덱스는 테이블의 두 개 이상의 열에 대한 인덱스입니다. 기본 구문은 다음과 같습니다.
CREATE INDEX index_name on table_name(column1,column2);
단일 열 또는 복합 인덱스에 인덱스를 생성하는지 여부에 관계없이 WHERE 쿼리에서 필터 조건으로 자주 사용할 수 있는 열을 고려하세요.
하나의 컬럼만 사용하는 경우 하나의 컬럼에 대한 인덱스를 선택해야 합니다. WHERE 절에서 필터로 자주 사용되는 열이 2개 이상인 경우에는 복합 인덱스를 선택하는 것이 좋습니다.
암시적 인덱스
암시적 인덱스는 객체가 생성될 때 데이터베이스 서버에 자동으로 생성되는 인덱스입니다. 인덱스는 기본 키 및 고유 제약 조건에 대해 자동으로 생성됩니다.
DROP INDEX 명령
SQL 명령을 사용하여 인덱스를 삭제할 수 있습니다. 떨어지다. 성능이 느려지거나 향상될 수 있으므로 인덱스를 삭제할 때는 주의해야 합니다.
기본 구문은 다음과 같습니다.
DROP INDEX 인덱스_이름;
INDEX 제약조건 예제를 보면 인덱스에 대한 실제 예제를 볼 수 있습니다.
언제 인덱스를 피해야 합니까?
인덱스는 데이터베이스 성능을 향상시키기 위한 것이지만 피해야 하는 경우도 있습니다.
다음 지침은 인덱스 사용을 다시 고려해야 하는 시기를 나타냅니다.
- 작은 테이블에는 인덱스를 사용하면 안 됩니다.
- 대규모 업데이트 또는 삽입 작업이 자주 발생하는 테이블입니다.
- Null 값이 많이 포함된 열에는 인덱스를 사용하면 안 됩니다.
- 자주 조작되는 열은 색인화하면 안 됩니다.
6. SQL Server 시스템에서 인덱스와 키를 생성하고 사용합니다.
인덱스는 테이블을 기반으로 생성되고 인덱스된 열의 값으로 검색이 수행되는 데이터 검색 속도를 높이도록 설계된 데이터베이스의 별도의 물리적 구조입니다. 또한 SQL Server는 인덱스를 사용하여 테이블의 행과 열이 고유한지 확인하고, 정보를 구성하고, 테이블 데이터를 단일 파일이나 파일 그룹에 배포하여 액세스 속도를 향상시킵니다.
SQL Server에서 테이블 데이터와 인덱스는 다음 형식의 페이지로 저장됩니다.
SQL Server에서 테이블과 인덱스를 위한 디스크 메모리는 8페이지 블록으로 나뉩니다. 범위. 한 익스텐트가 가득 차면 객체에 다음 익스텐트(추가 8페이지)가 할당됩니다.
SQL Server는 이진 트리 다이어그램을 사용하여 인덱스를 나타냅니다.
레벨 1
레벨 0
다이어그램의 각 직사각형은 색인 페이지를 나타냅니다. 수준 수가 증가하면 인덱스 처리 성능이 저하됩니다. SQL Server는 클러스터형과 비클러스터형의 두 가지 유형의 인덱스를 지원합니다.
클러스터 인덱스레벨 0(리프 레벨)이 현재 테이블 데이터의 페이지를 포함하고 물리적으로 주어진 인덱스의 논리적 순서에 따라 정보를 저장하는 이진 트리입니다. 클러스터형 인덱스를 생성하려면 데이터베이스에 기존 테이블 데이터 양보다 1.2배 더 많은 여유 디스크 공간이 필요합니다. 테이블당 하나의 클러스터형 인덱스만 있을 수 있습니다.
비클러스터형 인덱스의 경우 리프 수준 페이지에는 실제 테이블 데이터가 포함되지 않지만 데이터 페이지 번호와 페이지 레코드의 시퀀스 번호를 포함하여 데이터 행에 대한 포인터가 포함됩니다. 비클러스터형 인덱스는 테이블 데이터 행의 물리적 재정렬이 필요하지 않으므로 데이터베이스에 큰 여유 디스크 공간이 필요하지 않습니다.
비클러스터형 인덱스는 항상 클러스터형 인덱스보다 한 수준 높으므로 타일 수준에 도달한 후에는 추가 데이터 페이지 읽기가 필요합니다. 테이블에 클러스터형 인덱스가 있는 경우 비클러스터형 인덱스의 행 포인터는 클러스터형 인덱스의 타일 수준을 가리킵니다. 테이블에 클러스터형 인덱스가 없으면 행 인덱스에는 파일 ID, 데이터 페이지 번호 및 페이지의 레코드 번호가 포함됩니다.
단일 테이블에 최대 249개의 비클러스터형 인덱스를 만들 수 있습니다. 인덱스 행의 길이는 900바이트를 초과할 수 없으며 값 열은 16개를 초과할 수 없습니다.
어떤 테이블이든 하나의 클러스터형 인덱스와 2~6개의 비클러스터형 인덱스만 있으면 충분합니다(데이터 웨어하우스 생성 제외).
BIT, TEXT, IMAGE 데이터 유형의 열에는 인덱스를 생성할 수 없습니다. 뷰에는 인덱스를 만들 수 없습니다.
특정 SQL Server 데이터베이스 테이블에 인덱스를 생성하려면 다음 방법 중 하나를 사용할 수 있습니다.
SQL 명령 CREATE INDEX를 사용하여 인덱스를 생성합니다.
SQL Server 엔터프라이즈 관리자 유틸리티의 기능을 활용하십시오.
인덱스를 생성하는 두 번째 방법을 살펴보겠습니다. 인덱스 생성의 초기 단계는 필요한 데이터베이스와 해당 데이터베이스가 정의될 테이블을 선택하는 것입니다.
명령을 실행합니다. 모든 작업/관리하다인덱스메뉴 행동데이터베이스 인덱스 관리 대화 상자가 표시됩니다. 이 대화 상자의 드롭다운 목록에 주의하세요. 데이터 베이스그리고 테이블, 데이터베이스와 해당 테이블 사이를 탐색할 수 있습니다. 게다가 목록에는 기존의인덱스선택한 데이터베이스 테이블에 사용 가능한 인덱스가 표시됩니다.
이 대화 상자 하단에는 다음 작업을 수행하는 제어 버튼이 있습니다.
새로운- 선택한 데이터베이스 테이블에 대한 새 인덱스를 생성합니다.
편집하다- 기존 인덱스의 매개변수 편집
삭제- 미리 선택된 색인을 삭제합니다.
닫다- 대화 상자를 닫습니다.
돕다- 이 문제에 대한 참고 정보를 얻습니다.
새 색인을 생성하려면 버튼을 사용하세요. 새로운이 대화 상자. 이 작업을 수행하면 다른 대화 상자가 열립니다. 만들다새로운~ 안에 덱스, 인덱스 매개변수를 설정하는 데 사용됩니다. 현장에서 색인이름이 대화 상자에서는 생성할 인덱스의 이름을 입력한 후, 인덱스에 참여하는 필드 목록을 결정해야 합니다. 인덱스에 특정 필드를 추가하려면 해당 이름 왼쪽에 있는 확인란을 선택합니다. 여기에서 다음 필드 정보를 볼 수도 있습니다. 열- 분야 명, 데이터유형- 데이터 형식, 길이- 크기, 널 입력 가능- Null 값을 사용하는 기능 정도- 정확성과 규모- 입력된 값의 순서. 제시된 목록의 필드 순서를 변경할 수 있습니다.
옵션그룹 색인옵션생성된 인덱스의 추가 매개변수를 구성할 수 있습니다.
고유한가치– 특정 필드에 고유한 값만 입력해야 하는 경우 이 옵션을 설정해야 합니다. 이렇게 하면 새 레코드가 추가될 때마다 자동으로 고유성을 확인할 수 있습니다. 이 필드에 대한 레코드에 이미 존재하는 값을 입력하려고 하면 오류 메시지가 표시됩니다. 이 경우 해당 필드에 NULL 값이 존재하지 않도록 주의해야 합니다. NULL 값을 사용하고 이 옵션을 설정하면 오류가 발생할 수 있습니다. 따라서 고유 인덱스를 생성하려는 필드에 값을 필수 입력하도록 설정하는 것이 좋습니다.
클러스터링됨색인- SQL 서버 시스템에는 데이터를 물리적으로 인덱싱하는 기능이 있습니다. 즉, 인덱스를 사용하면 테이블에 있는 데이터의 물리적 위치와 연관된 별도의 구조가 생성됩니다. 이 옵션을 사용하면 소위 클러스터 인덱싱을 수행할 수 있으며, 그 결과 테이블 자체의 데이터가 이 인덱스의 순서에 따라 정렬되고 추가된 모든 정보로 인해 물리적 순서가 변경됩니다. 데이터. 테이블에는 하나의 클러스터형 인덱스만 정의할 수 있다는 점을 고려해야 합니다.
무시하다복제하다가치- 이 옵션을 선택하면 색인된 필드의 중복 값 입력이 무시됩니다.
하다~ 아니다다시 계산하다통계- 이 옵션을 설정하면 테이블의 통계를 자동으로 업데이트하는 기능이 결정됩니다.
파일그룹- 이 옵션을 사용하면 생성된 인덱스가 위치할 파일 그룹을 선택할 수 있습니다.
채우다요인- 이 기회는 극히 드물게 사용됩니다. 이 옵션을 사용하면 인덱스의 페이지 매김을 구성할 수 있습니다. 데이터베이스 테이블의 정보를 자주 변경, 삭제, 추가하려는 경우 계수는 다음과 같습니다. 필팩터가능한 한 낮게 설정해야 합니다(예: 20). 일반적으로 읽기 전용으로 액세스되는 대형 테이블을 사용할 때는 계수를 100으로 설정하는 것이 좋습니다.
인주색인- 옵션은 내부 인덱스 공간 채우기를 결정하고 옵션과 함께 사용됩니다. 채우다사실 토르;
떨어지다기존의- 클러스터형 인덱스를 사용할 때 이 옵션을 선택하면 다시 생성이 결정되어 클러스터형 인덱스가 원치 않는 업데이트를 방지하는 데 도움이 됩니다.
버튼 사용 편집하다SQL이 대화 상자는 생성된 SQL 명령을 사용자에게 제공하며 이를 통해 설정을 수행할 수 있습니다. 창에는 제어 버튼이 있습니다 구문 분석그리고 실행하다, 설치된 설정의 정확성을 분석하고(Parse) 결과 SQL 명령을 실행할 수 있습니다(Execute).
이후 생성된 인덱스는 SQL 문에서 사용할 수 있습니다. 선택하다다음과 같은 방법으로:
선택하다...
에서 <имя таблицы> (색인= <имя_индекса>)
정보 무결성을 모니터링하는 데 사용되는 데이터베이스의 기본 개념 중 하나는 다음과 같습니다. 열쇠. 기본 키와 외래 키를 분리합니다. 기본 키데이터베이스 테이블의 레코드를 고유하게 식별하는 고유 필드(또는 여러 필드)입니다. 외래 키일반적으로 다른 테이블의 기본 키에 해당하는 테이블 필드입니다.
인덱스와 키의 주요 차이점을 살펴보겠습니다.
SQL Server를 사용하면 테이블에 대해 하나의 기본 키만 정의할 수 있지만 여러 개의 고유 인덱스를 생성할 수 있습니다.
기본 키를 사용하는 경우 NULL 값을 입력하는 기능이 금지되고, 고유 인덱스를 사용하는 경우 이 금지 사항이 필수는 아니지만 준수하는 것이 좋습니다.
SQL Server 엔터프라이즈 관리자 유틸리티를 사용하여 기본 키를 생성하는 프로세스를 살펴보겠습니다. 이 문제를 해결하는 첫 번째 단계는 데이터베이스 개체 목록에서 테이블을 선택하는 것입니다. 명령 실행 설계테이블메뉴 행동필수 필드를 선택해야 하는 창에서 테이블 디자이너를 로드하고 열에서 확인란을 제거합니다. 허용하다널이 분야에 대해. 기본 키 설정은 버튼을 사용하여 완료됩니다. 세트주요한열쇠.
이 작업이 올바르게 수행되면 필드 이름/필드 왼쪽에 열쇠 아이콘이 나타납니다. 기본 키 제거는 설치와 동일한 방식으로 수행됩니다.
Transact-SQL 명령은 인덱스를 만드는 데 사용됩니다.
CREATE INDEX의 일반 구문은 다음과 같습니다.
인덱스 생성<имя индекса>에<имя таблицы>(컬럼 이름[, 컬럼 이름]…)
[,] FILLFACTOR=x][[,]
IGNORE_DUP_KEY][[,] DROP_EXISTING)[[,]
STATISTICS_NORECOMPUTE]]
이 명령의 매개변수를 살펴보겠습니다.
PAD_INDEX는 각 내부 페이지에 열려 있는 공간의 양입니다. 기본적으로 내부 페이지의 요소 수는 ≥2입니다. 이 매개변수는 FILLFACTOR와 함께 사용되며 이 매개변수의 백분율 값을 사용합니다.
IGNORE_DUP_KEY - 고유 키 필드의 중복 값을 사용하여 테이블에 행을 배치하려고 해도 작업을 계속할 수 있습니다. 메시지가 화면에 표시되고 행이 무시됩니다.
DROP_EXISTING - 이 매개 변수를 사용하면 기존 클러스터형 인덱스가 삭제되고 다시 생성되며, 기존 비클러스터형 인덱스는 새 클러스터형 인덱스가 생성된 후에만 다시 작성됩니다.
STATISTICS_NORECOMPUTE - 인덱스에 대한 통계 정보의 자동 업데이트를 차단합니다.
인덱스에 대한 다른 작업을 살펴보겠습니다.
1) 색인 보기:
a) SQL Server 엔터프라이즈 관리자 창에서 데이터베이스(폴더에 있는 아이콘)를 선택합니다. 데이터베이스);
b) 탭으로 이동 테이블그리고인덱스– 선택한 데이터베이스의 모든 테이블 이름과 관련 인덱스 이름이 여기에 표시됩니다.
2) 인덱스 이름 바꾸기, 삭제.
인덱스를 삭제하려면 Transact-SQL 명령을 사용합니다.
떨어지다색인[소유자.]<имя_ таблицы>. <имя_индекса>[,[소유자.]<имя_таблицы>, <имя_индекса>]
인덱스 이름 바꾸기는 다음 명령을 사용하여 수행됩니다.
sp_ 이름 바꾸기 <имя_объекта>, <новое имя> [,열| 색인]
SQL Server Enterprise Manager 창을 사용할 수도 있습니다. 디자이너에서 테이블을 열고 상황에 맞는 메뉴에서 테이블을 선택합니다. 속성, 대화 상자에서 테이블속성탭 선택 인덱스/ 열쇠- 여기에서 인덱스 이름을 바꾸고 새 인덱스를 생성할 수 있습니다.
인덱스 사용 전략:
1) 색인이 생성되어야 합니다:
테이블을 조인하는 데 사용되는 열;
쿼리를 실행할 때 분석되는 데이터 범위를 제한하는 데 사용되는 열
지시어에 사용되는 열 ORD응급실에 의해그리고
그룹에 의해;
합산 및 요약에 사용되는 열입니다.
2) 색인을 생성해서는 안 됩니다.
행 수가 적은 테이블;
광범위한 값을 갖는 열;
값이 매우 긴 열(>25바이트)
쿼리를 작성할 때 사용되지 않는 열입니다.
3) 열에는 클러스터형 인덱스를 사용하는 것이 좋습니다.
열이 많은 쿼리에 사용되는 경우;
열이 ORDER BY에서 사용되는 경우 또는
열이 테이블 조인에 사용되는 경우.
자동 인덱스 유형 선택 기능 사용:
쿼리 분석기를 엽니다.
DB 목록에서 데이터베이스 이름을 선택하십시오.
SQL 명령의 텍스트를 입력하십시오.
팀 선택 쿼리/인덱스 분석 수행.
SQL Server는 쿼리를 분석하여 쿼리 속도를 높이는 데 도움이 되는 인덱스를 만들 수 있는지 확인합니다. 색인을 찾을 수 있으면 창이 표시됩니다. 질문분석기. 추천 색인을 생성하려면 버튼을 클릭하세요. 수용하다.
6. 인덱스 및 성능 최적화
데이터베이스의 인덱스: 목적, 성능에 미치는 영향, 인덱스 생성 원칙
6.1 인덱스란 무엇입니까?
인덱스는 테이블의 특정 필드 또는 필드 집합을 기준으로 검색 및 정렬 속도를 높일 수 있는 데이터베이스의 특수 구조이며, 데이터의 고유성을 보장하는 데에도 사용됩니다. 색인을 비교하는 가장 쉬운 방법은 책의 색인을 사용하는 것입니다. 색인이 없으면 올바른 위치를 찾기 위해 책 전체를 살펴봐야 하지만 색인을 사용하면 동일한 작업을 훨씬 더 빠르게 수행할 수 있습니다.
일반적으로 인덱스가 많을수록 데이터베이스 쿼리 성능이 향상됩니다. 그러나 인덱스 수가 과도하게 증가하면 데이터 수정 작업(삽입/변경/삭제) 성능이 저하되고 데이터베이스의 크기가 커지므로 인덱스 추가 시 주의가 필요하다.
인덱스 생성과 관련된 몇 가지 일반 원칙은 다음과 같습니다.
· 검색 및 정렬 작업에 자주 사용되는 조인에 사용되는 열에 대해 인덱스를 생성해야 합니다. 기본 키 제약 조건이 적용되는 열에 대해서는 인덱스가 항상 자동으로 생성된다는 점에 유의하세요. 대부분의 경우 외래 키가 있는 열에 대해 생성됩니다(Access에서 자동으로).
· 고유성 제약 조건이 적용되는 열에 대해서는 인덱스가 자동으로 생성되어야 합니다.
· 반복되는 값이 최소화되고 데이터가 고르게 분포된 필드에 대해서는 인덱스를 생성하는 것이 가장 좋습니다. Oracle에는 중복 값이 많은 열에 대한 특수 비트 인덱스가 있습니다. SQL Server 및 Access에서는 이러한 유형의 인덱스를 제공하지 않습니다.
· 특정 열 집합에 대해 검색이 지속적으로(동시에) 수행되는 경우 이 경우 복합 인덱스(SQL Server에만 해당)를 만드는 것이 합리적일 수 있습니다. 즉 열 그룹에 대해 하나의 인덱스입니다.
· 테이블이 변경되면 이 테이블에 겹쳐진 인덱스가 자동으로 변경됩니다. 결과적으로 인덱스가 심하게 조각화되어 성능에 영향을 줄 수 있습니다. 정기적으로 인덱스 조각화 정도를 확인하고 조각 모음을 수행해야 합니다. 많은 양의 데이터를 로드할 때 먼저 모든 인덱스를 삭제하고 작업이 완료된 후 다시 생성하는 것이 합리적일 수 있습니다.
· 테이블뿐만 아니라 뷰(SQL Server에서만)에 대해서도 인덱스를 생성할 수 있습니다. 장점 - 요청 시점이 아닌 테이블에 새로운 값이 나타나는 순간 필드를 계산하는 기능.
1) 지수의 개념
색인하나 이상의 열 값을 기반으로 테이블 행에 대한 빠른 액세스를 제공하는 도구입니다.
표준이 성능 문제를 다루지 않기 때문에 표준화되지 않았기 때문에 이 연산자에는 많은 다양성이 있습니다.
2) 인덱스 생성
인덱스 생성
에()
3) 인덱스 변경 및 삭제
인덱스 활동을 제어하기 위해 연산자가 사용됩니다.
인덱스 변경
인덱스를 제거하려면 다음 연산자를 사용하십시오.
드롭 인덱스
a) 테이블 선택 규칙
1. 행의 5% 이하가 선택되는 테이블을 인덱싱하는 것이 좋습니다.
2. SELECT 문의 WHERE 절에 중복된 테이블이 없는 테이블은 인덱싱해야 한다.
3. 자주 업데이트되는 테이블을 색인화하는 것은 실용적이지 않습니다.
4. 2페이지 이하(Oracle의 경우 300행 미만)를 차지하는 테이블을 인덱스하는 것은 전체 스캔에 더 오랜 시간이 걸리지 않으므로 부적절합니다.
b) 열 선택 규칙
1. 기본 및 외래 키 - 테이블 조인, 데이터 검색 및 검색에 자주 사용됩니다. 이는 항상 최대의 유용성을 갖춘 고유 인덱스입니다.
2. 참조 무결성 옵션을 사용하는 경우 항상 FK에 대한 인덱스가 필요합니다.
3. 데이터가 종종 정렬 및/또는 그룹화되는 열입니다.
4. SELECT 문의 WHERE 절에서 자주 검색되는 컬럼.
5. 긴 설명 열에 인덱스를 생성하면 안 됩니다.
c) 복합지수 생성 원칙
1. 개별 열에 고유한 값이 거의 없으면 복합 인덱스가 좋지만 복합 인덱스가 더 많은 고유성을 제공합니다.
2. SELECT 문으로 선택한 값이 모두 복합 인덱스에 속해 있으면 해당 인덱스에서 해당 값이 선택됩니다.
3. WHERE 절에서 AND 연산자와 결합된 2개 이상의 값을 사용하는 경우에는 복합 인덱스를 생성해야 합니다.
d) 생성하는 것은 권장되지 않습니다.
복합 인덱스를 포함하여 열에 다음과 같은 인덱스를 생성하는 것은 권장되지 않습니다.
1. 쿼리 결과 검색, 병합, 정렬에는 거의 사용되지 않습니다.
2. 자주 변경되는 값을 포함하므로 인덱스를 자주 업데이트해야 하므로 데이터베이스 성능이 저하됩니다.
3. 소수의 고유 값(10% m/f 미만) 또는 하나 또는 두 개의 값을 가진 다수의 라인을 포함합니다(공급업체의 거주 도시는 모스크바입니다).
4. WHERE 절에 함수나 표현식이 적용되어 인덱스가 작동하지 않습니다.
e) 우리는 잊지 말아야 한다
인덱스 수가 많으면 데이터 업데이트 속도가 느려지므로 인덱스 수를 줄이기 위해 노력해야 합니다. 따라서 MS SQL Server에서는 테이블당 16개 이하의 인덱스를 생성할 것을 권장합니다.
일반적으로 인덱스는 쿼리 목적과 참조 무결성 유지를 위해 생성됩니다.
인덱스가 쿼리에 사용되지 않는 경우 삭제해야 하며 트리거를 사용하여 참조 무결성을 보장해야 합니다.
이 문서에서는 쿼리 실행 시간을 최적화하는 데 있어 인덱스와 인덱스의 역할에 대해 설명합니다. 기사의 첫 번째 부분에서는 다양한 형태의 인덱스와 이를 저장하는 방법에 대해 설명합니다. 다음으로 인덱스 작업에 사용되는 세 가지 주요 Transact-SQL 문인 CREATE INDEX, ALTER INDEX 및 DROP INDEX를 살펴보겠습니다. 그런 다음 시스템 성능에 미치는 영향 지수의 단편화가 고려됩니다. 그런 다음 인덱스 생성에 대한 몇 가지 일반적인 지침을 제공하고 몇 가지 특수한 인덱스 유형에 대해 설명합니다.
일반 정보
데이터베이스 시스템은 일반적으로 인덱스를 사용하여 관계형 데이터에 대한 빠른 액세스를 제공합니다. 인덱스는 하나 이상의 데이터 행에 빠르게 액세스할 수 있는 별도의 물리적 데이터 구조입니다. 따라서 인덱스를 적절하게 조정하는 것은 쿼리 성능을 향상시키는 핵심 요소입니다.
데이터베이스 색인은 여러 면에서 책의 색인(알파벳순 색인)과 유사합니다. 책에서 주제를 빨리 찾아야 할 경우 먼저 책의 어느 페이지에서 이 주제가 논의되는지 색인을 살펴본 다음 즉시 원하는 페이지를 엽니다. 마찬가지로, 테이블에서 특정 행을 검색할 때 데이터베이스 엔진은 인덱스에 액세스하여 물리적 위치를 찾습니다.
그러나 도서 색인과 데이터베이스 색인 사이에는 두 가지 중요한 차이점이 있습니다.
책의 독자는 각 특정 사례에 색인을 사용할지 여부를 스스로 결정할 기회가 있습니다. 데이터베이스 사용자에게는 이러한 기회가 없으며 이 결정은 다음과 같은 시스템 구성 요소에 의해 내려집니다. 쿼리 최적화 프로그램. (사용자는 인덱스 힌트를 통해 인덱스 사용을 조작할 수 있지만 이러한 힌트는 제한된 수의 특수한 경우에만 사용하는 것이 좋습니다.)
특정 통합 문서에 대한 색인은 통합 문서와 함께 생성되며 이후에는 더 이상 수정되지 않습니다. 이는 특정 주제에 대한 색인이 항상 동일한 페이지 번호를 가리킨다는 것을 의미합니다. 반면, 데이터베이스 인덱스는 해당 데이터가 변경될 때마다 변경될 수 있습니다.
테이블에 적절한 인덱스가 없으면 시스템은 테이블 스캔 방법을 사용하여 행을 검색합니다. 표현 테이블 스캔이는 시스템이 테이블의 각 행(처음부터 마지막까지)을 순차적으로 검색 및 검사하고 WHERE 절의 검색 조건이 만족되면 결과 집합에 해당 행을 배치한다는 의미입니다. 따라서 모든 행은 메모리의 물리적 위치에 따라 검색됩니다. 이 방법은 아래 설명된 것처럼 인덱스를 사용한 액세스보다 효율성이 떨어집니다.
인덱스는 다음과 같은 추가 데이터베이스 구조에 저장됩니다. 인덱스 페이지. 색인이 생성된 각 행에 대해 다음이 있습니다. 색인 항목, 인덱스 페이지에 저장됩니다. 각 인덱스 요소는 인덱스 키와 인덱스로 구성됩니다. 이것이 인덱스 요소가 가리키는 테이블 행보다 훨씬 짧은 이유입니다. 이러한 이유로 각 인덱스 페이지의 인덱스 요소 수는 데이터 페이지의 행 수보다 훨씬 큽니다.
인덱스 페이지를 순회하는 데 필요한 I/O 작업 수가 해당 데이터 페이지를 순회하는 데 필요한 I/O 작업 수보다 훨씬 적기 때문에 인덱스의 이러한 속성은 매우 중요합니다. 즉, 테이블을 스캔하려면 테이블의 인덱스를 스캔하는 것보다 더 많은 I/O 작업이 필요할 수 있습니다.
데이터베이스 엔진 인덱스는 B+ 트리 데이터 구조를 사용하여 생성됩니다. B+ 트리는 모든 최하위 노드가 트리의 최상위(루트 노드)에서 동일한 레벨 수만큼 떨어져 있는 트리 구조를 갖습니다. 이 속성은 인덱싱된 열에 데이터가 추가되거나 제거되는 경우에도 유지됩니다.
아래 그림은 Employee 테이블의 B+ 트리 구조와 Id 열의 값이 25348인 해당 테이블의 행에 대한 직접 액세스를 보여줍니다. (Employee 테이블은 Id 열로 인덱싱된다고 가정합니다.) 또한 이 그림에서 B+ 트리는 루트 노드, 트리 노드 및 0개 이상의 중간 노드로 구성되어 있음을 볼 수 있습니다.
다음과 같이 이 트리에서 값 25348을 검색할 수 있습니다. 트리의 루트에서 시작하여 원하는 값보다 크거나 같은 가장 작은 키 값을 검색합니다. 따라서 루트 노드에서 이 값은 29346이 되며 이 값과 관련된 중간 노드로 전환이 이루어집니다. 이 노드에서 값 28559는 지정된 요구 사항을 충족하며 그 결과 이 값과 연결된 트리 노드로 전환됩니다. 이 노드에는 원하는 값 25348이 포함되어 있습니다. 필요한 인덱스를 결정한 후 적절한 포인터를 사용하여 데이터 테이블에서 해당 행을 추출할 수 있습니다. (대안으로 동등한 접근 방식은 인덱스보다 작거나 같은 값을 검색하는 것입니다.)
인덱스 검색은 분명한 이점 때문에 행 수가 많은 테이블을 검색할 때 일반적으로 선호되는 방법입니다. 인덱스 검색을 사용하면 몇 번의 I/O 작업만으로 매우 짧은 시간에 테이블의 모든 행을 찾을 수 있습니다. 그리고 순차 검색(즉, 첫 번째 행에서 마지막 행까지 테이블 스캔)에는 더 많은 시간이 걸리며, 필요한 행은 더 멀리 떨어져 있습니다.
다음 섹션에서는 클러스터형과 비클러스터형의 두 가지 기존 인덱스 유형을 살펴보고 인덱스를 만드는 방법을 알아봅니다.
클러스터형 인덱스
클러스터형 인덱스테이블에 있는 데이터의 물리적 순서를 결정합니다. 데이터베이스 엔진에서는 테이블당 하나의 클러스터형 인덱스만 만들 수 있습니다. 테이블의 행은 여러 가지 방법으로 물리적으로 정렬될 수 없습니다. 클러스터형 인덱스를 사용한 검색은 B+ 트리의 루트 노드에서 이중 연결 리스트로 연결된 트리의 노드 방향으로 수행됩니다. 페이지 체인.
클러스터형 인덱스의 중요한 속성은 트리 노드에 데이터 페이지가 포함되어 있다는 것입니다. (클러스터형 인덱스 노드의 다른 모든 수준에는 인덱스 페이지가 포함됩니다.) 클러스터형 인덱스가 명시적으로 또는 암시적으로 정의된 테이블을 클러스터형 테이블이라고 합니다. 클러스터형 인덱스의 B+ 트리 구조는 아래 그림에 나와 있습니다.
클러스터형 인덱스는 기본 키 제약 조건에 의해 정의된 기본 키가 있는 각 테이블에 기본적으로 생성됩니다. 또한 각 클러스터형 인덱스는 기본적으로 고유합니다. 클러스터형 인덱스가 정의된 열에서는 각 데이터 값이 한 번만 나타날 수 있습니다. 중복 값이 포함된 열에 클러스터형 인덱스가 생성되면 데이터베이스 시스템은 중복 값이 포함된 행에 4바이트 식별자를 추가하여 명확성을 강화합니다.
클러스터형 인덱스는 쿼리가 값 범위를 검색할 때 매우 빠른 데이터 액세스를 제공합니다.
비클러스터형 인덱스
비클러스터형 인덱스의 구조는 클러스터형 인덱스와 정확히 동일하지만 두 가지 중요한 차이점이 있습니다.
비클러스터형 인덱스는 테이블 행의 물리적 순서를 변경하지 않습니다.
비클러스터형 인덱스 노드 페이지는 인덱스 키와 책갈피로 구성됩니다.
테이블에 하나 이상의 비클러스터형 인덱스를 정의하는 경우 테이블 행의 물리적 순서는 변경되지 않습니다. 각 비클러스터형 인덱스에 대해 데이터베이스 엔진은 인덱스 페이지에 저장되는 추가 인덱스 구조를 만듭니다. 비클러스터형 인덱스의 B+ 트리 구조는 아래 그림에 나와 있습니다.
비클러스터형 인덱스의 책갈피는 인덱스 키에 해당하는 행이 있는 위치를 나타냅니다. 인덱스 키 책갈피 구성 요소는 테이블이 클러스터링된 테이블인지 힙인지에 따라 두 가지 유형이 될 수 있습니다. (SQL Server 용어로 힙은 클러스터형 인덱스가 없는 테이블입니다.) 클러스터형 인덱스가 있는 경우 비클러스터형 인덱스 탭에는 테이블 클러스터형 인덱스의 B+ 트리가 표시됩니다. 테이블에 클러스터형 인덱스가 없으면 책갈피는 동일합니다. 행 식별자(RID - 행 식별자), 테이블이 저장되는 파일의 주소, 행이 저장되는 물리적 블록(페이지)의 주소, 페이지 내 행의 오프셋의 세 부분으로 구성됩니다.
앞서 언급했듯이 비클러스터형 인덱스를 사용하여 데이터를 검색하는 것은 테이블 유형에 따라 두 가지 방법으로 수행할 수 있습니다.
힙 - 비클러스터형 인덱스의 검색 구조를 탐색한 후 행 식별자를 사용하여 행을 검색합니다.
클러스터형 테이블 - 비클러스터형 인덱스 구조를 검색한 후 해당 클러스터형 인덱스를 검색하는 것입니다.
두 경우 모두 I/O 작업의 양이 상당히 크기 때문에 비클러스터형 인덱스를 주의해서 설계해야 하며, 이를 사용하면 성능이 크게 향상될 것이라고 확신하는 경우에만 사용해야 합니다.
Transact-SQL 언어 및 인덱스
이제 인덱스의 물리적 구조에 대해 익혔으니 이번 섹션에서는 인덱스를 생성, 수정, 삭제하는 방법과 인덱스 조각화 정보를 얻고 인덱스 정보를 편집하는 방법을 살펴보겠습니다. 이 모든 것은 시스템 성능을 향상시키기 위해 인덱스를 사용하는 것에 대한 후속 논의를 준비하는 데 도움이 됩니다.
인덱스 생성
테이블의 인덱스는 다음 명령문을 사용하여 생성됩니다. 인덱스 생성. 이 명령어의 구문은 다음과 같습니다.
CREATE INDEX index_name ON 테이블_이름 (column1 ,...) [ INCLUDE (column_name [ ,... ]) ] [[, ] PAD_INDEX = (ON | OFF)] [[, ] DROP_EXISTING = (ON | OFF)] [[ , ] SORT_IN_TEMPDB = (켜기 | 끄기)] [[, ] IGNORE_DUP_KEY = (켜기 | 끄기)] [[, ] ALLOW_ROW_LOCKS = (켜기 | 끄기)] [[, ] ALLOW_PAGE_LOCKS = (켜기 | 끄기)] [[, ] STATISTICS_NORECOMPUTE = (ON | OFF)] [[, ] ONLINE = (ON | OFF)]] 구문 규칙
index_name 매개변수는 생성할 인덱스의 이름을 지정합니다. table_name 매개변수로 식별되는 단일 테이블의 하나 이상의 열에 인덱스를 생성할 수 있습니다. 인덱스가 생성되는 컬럼은 column1 매개변수에 의해 지정됩니다. 이 매개변수의 숫자 접미사는 인덱스가 테이블의 여러 열에 생성될 수 있음을 나타냅니다. 데이터베이스 엔진은 뷰에 대한 인덱스 생성도 지원합니다.
모든 테이블 열을 인덱싱할 수 있습니다. 즉, VARBINARY(max), BIGINT, SQL_VARIANT 데이터 형식 값을 포함하는 열도 인덱싱할 수 있습니다.
인덱스는 단순하거나 복합적일 수 있습니다. 단순 인덱스는 단일 열에 생성되고, 복합 인덱스는 여러 열에 생성됩니다. 복합 인덱스에는 크기 및 열 수와 관련된 특정 제한이 있습니다. 인덱스는 최대 900바이트, 최대 16개의 열을 포함할 수 있습니다.
고유 매개변수인덱싱된 열이 단일 값(즉, 반복되지 않는) 값만 포함할 수 있음을 지정합니다. 단일값 복합 인덱스에서 고유값은 각 행의 모든 열 값의 조합이어야 합니다. UNIQUE 키워드를 지정하지 않으면 인덱싱된 열의 중복 값이 허용됩니다.
클러스터링된 매개변수클러스터형 인덱스를 지정하고 비클러스터형 매개변수(기본값)은 인덱스가 테이블의 행 순서를 변경하지 않도록 지정합니다. 데이터베이스 엔진은 테이블에 최대 249개의 비클러스터형 인덱스를 허용합니다.
열 값의 내림차순 인덱스를 지원하도록 데이터베이스 엔진이 향상되었습니다. 컬럼 이름 뒤의 ASC 매개변수는 컬럼 값의 오름차순으로 인덱스가 생성됨을 지정하고, DESC 매개변수는 인덱스 컬럼 값의 내림차순을 지정한다. 이는 인덱스 사용에 있어 더 큰 유연성을 제공합니다. 내림차순을 사용하면 값이 반대 방향으로 정렬된 열에 복합 인덱스를 만들어야 합니다.
포함 매개변수비클러스터형 인덱스의 노드 페이지에 추가되는 키가 아닌 열을 지정할 수 있습니다. INCLUDE 목록의 컬럼 이름은 반복되어서는 안 되며, 컬럼은 키 컬럼과 키가 아닌 컬럼으로 동시에 사용될 수 없습니다.
INCLUDE 매개변수의 유용성을 진정으로 이해하려면 그것이 무엇인지 이해해야 합니다. 커버링 인덱스. 모든 쿼리 열이 인덱스에 포함되면 성능이 크게 향상될 수 있습니다. 쿼리 최적화 프로그램은 테이블의 데이터에 액세스하지 않고도 인덱스 페이지 전체에서 모든 열 값을 찾을 수 있습니다. 이 기능을 커버링 인덱스 또는 커버링 쿼리라고 합니다. 따라서 비클러스터형 인덱스 노드 페이지에 키가 아닌 열을 추가로 포함하면 더 많은 적용 범위 쿼리를 얻고 성능을 크게 향상시킬 수 있습니다.
FILLFACTOR 매개변수인덱스 생성 시 채워질 각 인덱스 페이지의 비율을 지정합니다. FILLFACTOR 매개변수의 값은 1부터 100까지 설정할 수 있다. 값 n=100이면 각 인덱스 페이지가 100%로 채워진다. 기존 노드 페이지와 비노드 페이지에는 새 행을 삽입할 수 있는 여유 공간이 없습니다. 따라서 이 값은 정적 테이블에만 사용하는 것이 좋습니다. (기본값 n=0은 인덱스 노드 페이지가 가득 차 있고 각 중간 페이지에 하나의 항목에 대한 여유 공간이 있음을 의미합니다.)
FILLFACTOR 매개변수를 1~99 사이의 값으로 설정하면 생성되는 인덱스 구조의 노드 페이지에 여유 공간이 포함됩니다. n 값이 클수록 인덱스 노드 페이지에 사용 가능한 공간이 줄어듭니다. 예를 들어 n=60인 경우 각 인덱스 노드 페이지에는 향후 인덱스 행 삽입을 위한 40%의 여유 공간이 있습니다. (인덱스 행은 INSERT 또는 UPDATE 문을 사용하여 삽입됩니다.) 따라서 데이터가 자주 변경되는 테이블에는 n=60 값이 적합합니다. 1에서 99 사이의 FILLFACTOR 값의 경우 중간 인덱스 페이지에는 각각 하나의 항목에 대한 여유 공간이 포함됩니다.
인덱스가 생성되면 사용 중에는 FILLFACTOR 값이 지원되지 않습니다. 즉, 여유 공간에 대한 백분율을 설정할 때 사용 가능한 데이터로 예약된 공간의 양만 나타냅니다. FILLFACTOR 매개변수를 원래 값으로 복원하려면 ALTER INDEX 문을 사용합니다.
매개변수 PAD_INDEX FILLFACTOR 매개변수와 밀접한 관련이 있습니다. FILLFACTOR 매개변수는 기본적으로 여유 공간의 양을 인덱스 노드의 전체 페이지 크기에 대한 백분율로 지정합니다. 그리고 PAD_INDEX 매개변수는 FILLFACTOR 매개변수의 값이 인덱스 페이지와 인덱스의 데이터 페이지 모두에 적용되도록 지정합니다.
DROP_EXISTING 매개변수비클러스터형 인덱스도 있는 테이블에서 클러스터형 인덱스를 재생성할 때 성능을 향상시킬 수 있습니다. 자세한 내용은 아래의 "인덱스 다시 작성" 섹션을 참조하세요.
SORT_IN_TEMPDB 매개변수 tempdb 시스템 데이터베이스에 인덱스를 생성할 때 사용되는 중간 정렬 작업의 데이터를 배치하는 데 사용됩니다. tempdb가 데이터와 다른 디스크에 있는 경우 성능이 향상될 수 있습니다.
IGNORE_DUP_KEY 매개변수시스템이 인덱스 열에 중복 값을 삽입하려는 시도를 무시할 수 있습니다. 이 옵션은 INSERT 문이 인덱싱된 열에 중복 데이터를 삽입할 때 장기 실행 트랜잭션이 중단되는 것을 방지하기 위해서만 사용해야 합니다. 이 옵션이 활성화되면 INSERT 문이 인덱스의 고유성을 위반하는 테이블에 행을 삽입하려고 시도할 때 데이터베이스 시스템은 전체 문을 충돌시키는 대신 단순히 경고를 발행합니다. 이 경우 데이터베이스 엔진은 중복된 키 값이 있는 행을 삽입하지 않고 이를 무시하고 올바른 행을 추가합니다. 이 매개변수가 설정되지 않으면 전체 명령의 실행이 비정상적으로 종료됩니다.
언제 매개변수 ALLOW_ROW_LOCKS활성화(on으로 설정)되면 시스템은 행 잠금을 적용합니다. 마찬가지로 활성화되면 매개변수 ALLOW_PAGE_LOCKS, 시스템은 동시 액세스 중에 페이지 잠금을 적용합니다. STATISTICS_NORECOMPUTE 매개변수지정된 인덱스에 대한 통계 자동 재계산 상태를 결정합니다.
활성화됨 온라인 매개변수대화 상자 모드에서 인덱스를 생성, 재생성 및 삭제할 수 있습니다. 이 옵션을 사용하면 인덱스를 변경하는 동시에 기본 테이블이나 클러스터형 인덱스 및 관련 인덱스의 데이터를 동시에 변경할 수 있습니다. 예를 들어 클러스터형 인덱스가 다시 생성되는 동안 해당 데이터를 계속 업데이트하고 해당 데이터에 대한 쿼리를 실행할 수 있습니다.
매개변수 ON기본 파일 그룹(기본값) 또는 지정된 파일 그룹(file_group 값)에 지정된 인덱스를 만듭니다.
아래 예에서는 Employee 테이블의 Id 열에 비클러스터형 인덱스를 만드는 방법을 보여줍니다.
SampleDb를 사용하세요. 직원(ID)에 대한 인덱스 ix_empid 생성;
단일 값 복합 인덱스 생성은 아래 예에 나와 있습니다.
SampleDb를 사용하세요. FILLFACTOR= 80으로 Works_on(EmpId, ProjectNumber)에 고유 인덱스 ix_empid_prnu를 생성합니다.
이 예에서 각 열의 값은 한 자리 숫자여야 합니다. 인덱스가 생성되면 각 인덱스 노드 페이지 공간의 80%가 채워집니다.
열에 중복된 값이 포함되어 있으면 해당 열에 고유 인덱스를 생성할 수 없습니다. 이러한 인덱스는 각 값(NULL 값 포함)이 열에 정확히 한 번 나타나는 경우에만 생성될 수 있습니다. 또한 기존 고유 인덱스에 포함된 열에 기존 데이터 값을 삽입하거나 변경하려는 시도는 값이 중복되면 시스템에서 거부됩니다.
인덱스 조각화에 대한 정보 가져오기
인덱스 수명 동안 인덱스는 조각화되어 인덱스 페이지에 데이터를 저장하는 프로세스가 비효율적일 수 있습니다. 인덱스 조각화에는 내부 조각화와 외부 조각화라는 두 가지 유형이 있습니다. 내부 단편화는 각 페이지에 저장되는 데이터의 양을 결정하는 반면, 외부 단편화는 페이지가 논리적 순서를 벗어나면 발생합니다.
내부 인덱스 조각화에 대한 정보를 얻으려면 다음과 같은 DMV 동적 관리 뷰를 사용하세요. sys.dm_db_index_physical_stats. 이 DMV는 지정된 페이지의 데이터와 인덱스의 볼륨 및 조각화에 대한 정보를 반환합니다. 각 페이지에 대해 B+ 트리의 각 수준에 대해 하나의 행이 반환됩니다. 이 DMV를 사용하면 데이터 페이지의 행 조각화 정도에 대한 정보를 얻을 수 있으며, 이를 기반으로 데이터 재구성 여부를 결정할 수 있습니다.
sys.dm_db_index_physical_stats 뷰의 사용은 아래 예에 나와 있습니다. (배치 예제를 실행하기 전에 Works_on 테이블에 있는 기존 인덱스를 모두 삭제해야 합니다. 인덱스를 삭제하려면 나중에 설명할 DROP INDEX 문을 사용하세요.)
SampleDb를 사용하세요. @dbId INT 선언; @tabId INT를 선언하세요. @indId INT를 선언하세요. SET @dbId = DB_ID("샘플Db"); SET @tabId = OBJECT_ID("직원"); SELECT avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(@dbId, @tabId, NULL, NULL, NULL);
예제에서 볼 수 있듯이 sys.dm_db_index_physical_stats 보기에는 5개의 매개 변수가 있습니다. 처음 세 매개변수는 각각 현재 데이터베이스, 테이블 및 인덱스의 ID를 정의합니다. 네 번째 매개변수는 파티션 ID를 지정하고, 마지막 매개변수는 통계 정보를 얻는 데 사용되는 검색 수준을 지정합니다. (특정 매개변수의 기본값은 NULL 값을 사용하여 지정할 수 있습니다.)
이 보기의 가장 중요한 열은 avg_fragmentation_in_percent 및 avg_page_space_used_in_percent 열입니다. 첫 번째는 평균 조각화 수준을 백분율로 나타내고, 두 번째는 점유된 공간의 양을 백분율로 결정합니다.
지수 정보 편집
이전 섹션에서 설명한 대로 인덱스 조각화 정보에 익숙해지면 다음 시스템 도구를 사용하여 이 정보와 기타 인덱스 정보를 편집할 수 있습니다.
디렉토리 보기 sys.indexes;
카탈로그 뷰 sys.index_columns;
시스템 프로시저 sp_helpindex;
objectproperty 속성 함수;
SQL Server Management Studio 관리 환경;
DMV 동적 관리 보기 sys.dm_db_index_usage_stats;
DMV 동적 관리 보기 sys.dm_db_missing_index_details.
카탈로그 보기 sys.indexes각 인덱스에 대한 행과 클러스터형 인덱스가 없는 각 테이블에 대한 행을 포함합니다. 이 카탈로그 뷰의 가장 중요한 열은 object_id, name 및 index_id 열입니다. object_id 열에는 인덱스를 소유한 데이터베이스 개체의 이름이 포함되고, name 및 index_id 열에는 각각 해당 인덱스의 이름과 ID가 포함됩니다.
카탈로그 보기 sys.index_columns인덱스 또는 힙의 일부인 각 열에 대한 행을 포함합니다. 이 정보는 sys.indexes 카탈로그 뷰를 통해 얻은 정보와 함께 사용되어 지정된 인덱스의 속성에 대한 추가 정보를 얻을 수 있습니다.
시스템 절차 sp_helpindex테이블 인덱스에 대한 정보와 열에 대한 통계 정보를 반환합니다. 이 절차의 구문은 다음과 같습니다.
sp_helpindex [@db_object = ] "이름"
여기서 @db_object 변수는 테이블 이름을 나타냅니다.
인덱스와 관련하여, 객체 속성 함수 IsIndexed와 IsIndexable이라는 두 가지 속성이 있습니다. 첫 번째 속성은 테이블이나 뷰에 인덱스가 있는지 여부에 대한 정보를 제공하고, 두 번째 속성은 테이블이나 뷰가 인덱싱 가능한지 여부를 나타냅니다.
SQL Server Management Studio를 사용하여 기존 인덱스 정보를 편집하려면 데이터베이스 폴더에서 원하는 데이터베이스를 선택하고 테이블 노드를 확장한 다음 해당 노드에서 원하는 테이블과 해당 인덱스 폴더를 확장합니다. 테이블의 Indexes 폴더에는 해당 테이블에 대한 모든 기존 인덱스 목록이 표시됩니다. 인덱스를 두 번 클릭하면 해당 인덱스의 속성이 포함된 인덱스 속성 대화 상자가 열립니다. (Management Studio를 사용하여 새 인덱스를 생성하거나 기존 인덱스를 삭제할 수도 있습니다.)
성능 sys.dm_db_index_usage_stats다양한 유형의 인덱스 작업 수와 각 유형의 작업이 마지막으로 수행된 시간을 반환합니다. 단일 쿼리의 지정된 인덱스에 대한 각각의 별도 검색, 조회 또는 업데이트 작업은 인덱스 사용으로 간주되며 해당 DMV의 해당 카운터를 1씩 증가시킵니다. 이렇게 하면 인덱스가 얼마나 자주 사용되는지에 대한 일반적인 정보를 얻을 수 있으므로 이를 사용하여 어떤 인덱스가 더 많이 사용되고 어떤 인덱스가 덜 사용되는지 확인할 수 있습니다.
성능 sys.dm_db_missing_index_details인덱스가 없는 테이블 컬럼에 대한 자세한 정보를 반환한다. 이 DMV의 가장 중요한 열은 index_handle 및 object_id 열입니다. 첫 번째 열의 값은 누락된 특정 인덱스를 식별하고, 두 번째 열의 값은 인덱스가 누락된 테이블을 식별합니다.
인덱스 변경
데이터베이스 엔진은 문을 지원하는 몇 안되는 데이터베이스 시스템 중 하나입니다. 인덱스 변경. 이 문을 사용하여 인덱스 유지 관리 작업을 수행할 수 있습니다. ALTER INDEX 문의 구문은 CREATE INDEX 문의 구문과 매우 유사합니다. 즉, 이 문을 사용하면 앞서 CREATE INDEX 문에서 설명한 ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE 매개 변수의 값을 변경할 수 있습니다.
위 옵션 외에도 ALTER INDEX 문은 세 가지 다른 옵션을 지원합니다.
REBUILD 매개변수, 인덱스를 다시 만드는 데 사용됩니다.
매개변수 재구성, 인덱스 노드 페이지를 재구성하는 데 사용됩니다.
비활성화 매개변수, 인덱스를 비활성화하는 데 사용됩니다. 이 세 가지 옵션은 다음 하위 섹션에서 설명됩니다.
인덱스 재구축
INSERT, UPDATE 또는 DELETE 문을 사용하여 데이터를 변경하면 데이터 조각화가 발생할 수 있습니다. 이 데이터가 색인화되면 색인 정보가 여러 물리적 페이지에 분산되어 색인 조각화가 가능합니다. 인덱스 데이터 조각화로 인해 데이터베이스 엔진이 추가 데이터 읽기 작업을 수행해야 할 수 있으며 이로 인해 전체 시스템 성능이 저하됩니다. 이 경우 조각난 인덱스를 모두 REBUILD해야 합니다.
이 작업은 두 가지 방법으로 수행할 수 있습니다.
ALTER INDEX 문의 REBUILD 매개변수를 통해;
CREATE INDEX 문의 DROP_EXISTING 매개변수를 통해.
REBUILD 매개변수는 인덱스를 재빌드하는 데 사용됩니다. 이 매개변수에 인덱스 이름 대신 ALL을 지정하면 테이블의 모든 인덱스가 다시 생성됩니다. (인덱스가 동적으로 재생성되도록 허용하면 인덱스를 삭제하고 다시 생성할 필요가 없습니다.)
CREATE INDEX 문의 DROP_EXISTING 옵션은 비클러스터형 인덱스도 있는 테이블에 클러스터형 인덱스를 다시 만들 때 성능을 향상시킬 수 있습니다. 기존 클러스터형 또는 비클러스터형 인덱스를 삭제하고 지정된 인덱스를 다시 생성해야 함을 지정합니다. 앞서 언급했듯이 클러스터형 테이블의 각 비클러스터형 인덱스에는 테이블의 클러스터형 인덱스에 해당하는 값이 트리 노드에 포함되어 있습니다. 이러한 이유로 테이블에 클러스터형 인덱스를 삭제하면 해당 비클러스터형 인덱스를 모두 다시 만들어야 합니다. DROP_EXISTING 매개변수를 사용하면 비클러스터형 인덱스를 다시 생성할 필요가 없습니다.
DROP_EXISTING 옵션은 REBUILD 옵션보다 더 유연하고 인덱스를 구성하는 열을 변경하고 비클러스터형 인덱스를 클러스터형 인덱스로 변경하는 등 여러 옵션을 제공하므로 더 강력합니다.
인덱스 노드 페이지 재구성
ALTER INDEX 문의 REORGANIZE 매개변수는 지정된 인덱스에 있는 노드의 페이지를 재구성하여 페이지의 물리적 순서가 논리적 순서(왼쪽에서 오른쪽)와 일치하도록 합니다. 이렇게 하면 특정 양의 인덱스 조각화가 제거되어 인덱스 성능이 향상됩니다.
인덱스 비활성화
DISABLE 옵션은 지정된 인덱스를 비활성화합니다. 비활성화된 인덱스는 다시 활성화될 때까지 사용할 수 없습니다. 비활성화된 인덱스는 연결된 데이터가 변경되어도 변경되지 않습니다. 따라서 비활성화된 인덱스를 재사용하려면 인덱스를 완전히 다시 생성해야 합니다. 비활성화된 인덱스를 활성화하려면 ALTER TABLE 문의 REBUILD 옵션을 사용하세요.
테이블의 클러스터형 인덱스가 비활성화되면 클러스터형 인덱스가 있는 테이블의 모든 데이터 페이지가 트리 노드에 저장되므로 테이블의 데이터에 액세스할 수 없습니다.
인덱스 제거 및 이름 바꾸기
현재 데이터베이스에서 인덱스를 제거하려면 다음을 사용하십시오. DROP INDEX 명령어. 테이블에 클러스터형 인덱스를 삭제하는 작업은 리소스를 많이 소모할 수 있습니다. 모든 비클러스터형 인덱스를 다시 만들어야 합니다. (모든 비클러스터형 인덱스는 노드 페이지에서 클러스터형 인덱스의 인덱스 키를 포인터로 사용합니다.) DROP INDEX 문을 사용하여 인덱스를 삭제하는 방법은 아래 예에 설명되어 있습니다.
SampleDb를 사용하세요. DROP INDEX ix_empid ON 직원;
DROP INDEX 명령어에는 추가 기능이 있습니다. MOVE TO 매개변수, 그 의미는 CREATE INDEX 문의 ON 매개변수와 동일합니다. 즉, 이 매개 변수를 사용하여 클러스터형 인덱스 노드 페이지에 있는 데이터 행을 이동할 위치를 지정할 수 있습니다. 데이터는 힙 형태로 새 위치로 이동됩니다. 새 데이터 저장소 위치에 대해 기본 파일 그룹이나 명명된 파일 그룹을 지정할 수 있습니다.
DROP INDEX 문은 PRIMARY KEY 및 UNIQUE 인덱스와 같이 무결성 제약 조건을 위해 시스템에서 암시적으로 생성된 인덱스를 삭제하는 데 사용할 수 없습니다. 이러한 인덱스를 제거하려면 해당 제약 조건을 제거해야 합니다.
sp_rename 시스템 프로시저를 사용하여 인덱스 이름을 바꿀 수 있습니다.
데이터베이스 다이어그램이나 개체 탐색기를 사용하여 Management Studio에서 인덱스를 생성, 수정 및 삭제할 수도 있습니다. 그러나 가장 쉬운 방법은 필요한 테이블의 Indexes 폴더를 사용하는 것입니다. Management Studio에서 인덱스를 관리하는 것은 Management Studio에서 테이블을 관리하는 것과 유사합니다.
데이터베이스 엔진에서는 인덱스 수에 실질적인 제한을 두지 않지만 인덱스 수를 제한해야 하는 몇 가지 이유가 있습니다. 첫째, 각 인덱스는 일정량의 디스크 공간을 차지하므로 데이터베이스 인덱스 페이지의 총 개수가 데이터베이스의 데이터 페이지 수를 초과할 가능성이 있습니다. 둘째, 인덱스를 사용하여 데이터를 검색하는 이점과 달리 데이터를 삽입하고 삭제하는 것은 인덱스를 유지해야 하기 때문에 이러한 이점을 제공하지 않습니다. 테이블에 인덱스가 많을수록 인덱스를 재구성하는 데 더 많은 작업이 필요합니다. 일반적으로 자주 쿼리하는 경우 인덱스를 선택한 다음 사용량을 평가하는 것이 좋습니다.
이 섹션에서는 인덱스 생성 및 사용에 대한 몇 가지 지침을 제공합니다. 다음 권장 사항은 일반적인 규칙일 뿐입니다. 궁극적으로 그 효과는 데이터베이스가 실제로 사용되는 방식과 가장 자주 실행되는 쿼리 유형에 따라 달라집니다. 결코 사용되지 않을 열을 인덱싱하는 것은 아무 소용이 없습니다.
인덱스 및 WHERE 절 조건
SELECT 문의 WHERE 절에 하나의 열이 있는 검색 조건이 포함되어 있으면 해당 열에 인덱스를 만들어야 합니다. 이는 특히 선택성이 높은 조건에서 권장됩니다. 조건의 선택성이란 테이블의 전체 행 수에 대한 조건을 만족하는 행 수의 비율을 의미합니다. 높은 선택성은 이 비율의 더 낮은 값에 해당합니다. 인덱싱된 열을 사용한 검색 처리는 조건 선택도가 5% 미만일 때 가장 성공적입니다.
조건의 선택성 수준이 80% 이상으로 일정하면 열을 인덱싱하면 안 됩니다. 이 경우 인덱스 페이지에는 추가 I/O 작업이 필요하므로 인덱스를 사용하여 얻을 수 있는 시간 절약 효과가 줄어듭니다. 이 경우 쿼리 최적화 프로그램이 일반적으로 선택하는 테이블 스캔을 수행하여 인덱스를 쓸모 없게 만드는 검색을 수행하는 것이 더 빠릅니다.
자주 사용되는 쿼리의 검색 조건에 AND 연산자가 포함되어 있는 경우 가장 좋은 방법은 SELECT 문의 WHERE 절에 지정된 모든 테이블 열에 복합 인덱스를 만드는 것입니다. 이러한 복합 인덱스 생성은 아래 예에 나와 있습니다.
이 예에서는 WHERE 절의 모든 열에 복합 인덱스를 생성합니다. 이 쿼리에서는 두 조건이 AND로 연결되어 있으므로 이러한 조건의 두 열 모두에 복합 비클러스터형 인덱스를 만들어야 합니다.
인덱스와 조인 연산자
조인 작업의 경우 조인되는 각 열에 인덱스를 생성하는 것이 좋습니다. 조인된 열은 한 테이블의 기본 키와 다른 테이블의 해당 외래 키를 나타내는 경우가 많습니다. 해당 조인 열에 PRIMARY KEY 및 FOREIGN KEY 무결성 제약 조건을 지정하는 경우 외래 키 열에 비클러스터형 인덱스만 만들어야 합니다. 시스템은 기본 키 열에 클러스터형 인덱스를 암시적으로 생성합니다.
아래 예에서는 조인 작업과 추가 필터가 포함된 쿼리가 있는 경우 사용할 인덱스를 생성하는 방법을 보여줍니다.
피복지수
앞서 언급했듯이 인덱스에 모든 쿼리 열을 포함하면 쿼리 성능이 크게 향상될 수 있습니다. Covering이라고 하는 이러한 인덱스의 생성은 아래 예에 나와 있습니다.
AdventureWorks2012를 사용하세요. GO DROP INDEX Person.Address.IX_Address_StateProvinceID; GO CREATE INDEX ix_address_zip ON Person.Address(PostalCode) INCLUDE(시, StateProvinceID); GO SELECT 시, StateProvinceID FROM Person.Address WHERE PostalCode = 84407;
이 예에서는 먼저 Address 테이블에서 IX_Address_StateProvinceID 인덱스를 제거합니다. 그런 다음 PostalCode 열 외에 두 개의 추가 열을 포함하는 새 인덱스가 생성됩니다. 마지막으로 예제 끝에 있는 SELECT 문은 인덱스에 포함된 쿼리를 보여줍니다. 이 쿼리의 경우 쿼리 최적화 프로그램이 비클러스터형 인덱스 노드 페이지에서 모든 열 값을 찾을 수 있으므로 시스템은 데이터 페이지에서 데이터를 검색할 필요가 없습니다.
인덱스 페이지에는 일반적으로 해당 데이터 페이지보다 더 많은 항목이 포함되므로 포함 인덱스를 사용하는 것이 좋습니다. 또한 이 방법을 사용하려면 필터링되는 열이 인덱스의 첫 번째 키 열이어야 합니다.
계산 열의 인덱스
데이터베이스 엔진을 사용하면 다음과 같은 특별한 유형의 인덱스를 만들 수 있습니다.
인덱싱된 뷰;
필터링 가능한 인덱스;
계산된 열의 인덱스
분할된 인덱스;
열 지속성 인덱스;
XML 인덱스;
전체 텍스트 인덱스.
이 섹션에서는 계산된 열과 관련 인덱스에 대해 설명합니다.
계산된 열테이블 데이터 계산 결과가 저장되는 테이블 컬럼입니다. 이러한 열은 가상이거나 지속적일 수 있습니다. 이 두 가지 유형의 열은 다음 하위 섹션에서 설명됩니다.
가상 계산 열
해당 클러스터형 인덱스가 없는 계산 열은 논리 열입니다. 하드 드라이브에 물리적으로 저장되지 않습니다. 따라서 행에 액세스할 때마다 평가됩니다. 가상 계산 열의 사용은 아래 예에 나와 있습니다.
SampleDb를 사용하세요. CREATE TABLE 주문(OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD(DAY, 7, orderdate));
이 예의 Orders 테이블에는 총계와 배송일이라는 두 개의 가상 계산 열이 있습니다. 총계 열은 가격과 수량이라는 두 개의 다른 열을 사용하여 계산되고, 배송 날짜 열은 DATEADD 함수와 주문 날짜 열을 사용하여 계산됩니다.
상수 계산 열
데이터베이스 엔진을 사용하면 기본 열이 정확한 데이터 형식을 갖는 결정적 계산 열에 인덱스를 만들 수 있습니다. (계산된 열은 동일한 테이블 데이터에 대해 항상 동일한 값을 반환하는 경우 결정적이라고 합니다.)
인덱싱된 계산 열은 SET 문의 다음 매개 변수가 ON으로 설정된 경우에만 생성될 수 있습니다. 이러한 매개 변수는 열이 결정적임을 보장합니다.
QUOTED_IDENTIFIER
CONCAT_NULL_YIELDS_NULL
또한 NUMERIC_ROUNDABORT 매개변수를 off로 설정해야 합니다.
계산 열에 클러스터형 인덱스를 생성하면 클러스터형 인덱스의 노드 페이지에 데이터 행이 포함되므로 해당 열의 값은 해당 테이블 행에 물리적으로 존재하게 됩니다. 다음 예에서는 Orders 테이블의 계산된 열 합계에 대한 클러스터형 인덱스를 만듭니다.
SampleDb를 사용하세요. 주문에 대해 클러스터형 인덱스 ix1 생성(총계);
CREATE INDEX 문을 실행하면 계산된 총계 열이 테이블에 물리적으로 표시됩니다. 즉, 계산된 열의 기본 열에 대한 모든 업데이트로 인해 해당 열이 업데이트됩니다.
열은 다음을 사용하여 다른 방법으로 상수로 만들 수 있습니다. 지속되는 매개변수. 이 옵션을 사용하면 해당 클러스터형 인덱스를 만들지 않고도 계산된 열의 실제 존재 여부를 지정할 수 있습니다. 이 기능은 대략적인 데이터 유형(부동 또는 실수)의 열에 생성되는 물리적 계산 열을 생성하는 데 필요합니다. (앞서 언급한 것처럼 기본 열이 정확한 데이터 형식인 경우에만 계산 열에 인덱스를 만들 수 있습니다.)
