나의 장점과 단점 인덱스의 장점과 단점 페이지 1/2-PHP 튜토리얼-php.cn

인덱스의 장점과 단점
인덱스를 왜 만드는가? 인덱스를 생성하면 시스템 성능이 크게 향상될 수 있기 때문입니다. 첫째, 고유 인덱스를 생성함으로써 데이터베이스 테이블의 각 데이터 행의 고유성을 보장할 수 있습니다. 둘째, 데이터 검색 속도를 크게 높일 수 있는데, 이는 인덱스를 생성하는 주된 이유이기도 합니다. 셋째, 테이블 간의 연결 속도를 높일 수 있는데, 이는 데이터의 참조 무결성을 달성하는 데 특히 의미가 있습니다. 넷째, 데이터 검색을 위해 그룹화 및 정렬 절을 사용하면 쿼리에서 그룹화 및 정렬하는 시간도 크게 줄일 수 있습니다. 다섯째, 인덱스를 사용하면 쿼리 프로세스 중에 최적화 숨기기를 사용하여 시스템 성능을 향상시킬 수 있습니다.
어떤 사람들은 다음과 같이 질문할 수 있습니다. 인덱스를 추가하면 많은 이점이 있는데 테이블의 모든 열에 대해 인덱스를 생성하는 것은 어떨까요? 이 생각은 합리적이기는 하지만 일방적이기도 하다. 인덱스에는 많은 장점이 있지만 테이블의 모든 열에 인덱스를 추가하는 것은 매우 현명하지 않습니다. 인덱스를 추가하면 단점이 많기 때문입니다. 첫째, 인덱스를 생성하고 유지하는 데 시간이 걸리며, 이 시간은 데이터 양이 증가함에 따라 증가합니다. 둘째, 인덱스는 데이터 테이블이 차지하는 데이터 공간 외에 각 인덱스도 일정량의 물리적 공간을 차지해야 합니다. 셋째, 테이블에 데이터를 추가, 삭제, 수정하는 경우 인덱스를 동적으로 유지해야 하므로 데이터 유지 속도가 떨어진다.
인덱스는 데이터베이스 테이블의 특정 열에 구축됩니다. 따라서 인덱스를 생성할 때 어떤 열을 인덱싱할 수 있고 어떤 열을 인덱싱할 수 없는지 신중하게 고려해야 합니다. 일반적으로 인덱스는 이러한 열에 생성되어야 합니다. 예를 들어 자주 검색되는 열에서는 기본 키 역할을 하는 열에 대한 검색 속도를 높이고 열의 고유성을 강화하며 데이터의 배열 구조를 구성합니다. 조인에 자주 사용되는 열에 인덱스를 생성합니다. 이는 조인 속도를 높일 수 있습니다. 인덱스가 정렬되어 있으므로 범위를 기준으로 자주 검색해야 합니다. 지정된 범위는 연속적입니다. 인덱스가 이미 정렬되어 있으므로 자주 정렬해야 하는 열에 인덱스를 생성하므로 쿼리는 인덱스 정렬을 사용하여 WHERE에서 자주 사용되는 열에 인덱스를 생성할 수 있습니다. 조건 판단을 빠르게 하기 위한 조항.
또한 색인을 생성하면 안 되는 열도 있습니다. 일반적으로 이러한 인덱스를 생성해서는 안 되는 컬럼에는 다음과 같은 특징이 있습니다. 첫째, 쿼리에서 거의 사용되거나 참조되지 않는 컬럼에 대해서는 인덱스를 생성해서는 안 됩니다. 이는 이러한 열이 거의 사용되지 않기 때문에 인덱싱 여부에 관계없이 쿼리 속도가 향상되지 않기 때문입니다. 반대로, 인덱스 추가로 인해 시스템 유지 속도가 감소하고 필요한 공간이 증가합니다. 둘째, 데이터 값이 적은 열에는 인덱스를 추가해서는 안 됩니다. 이는 이러한 컬럼은 인사 테이블의 성별 컬럼과 같이 값이 매우 적기 때문에 질의 결과에서는 결과 세트의 데이터 행이 테이블의 데이터 행 중 큰 부분을 차지하기 때문입니다. 테이블에서 검색해야 할 데이터 행의 비율이 엄청납니다. 인덱스를 늘려도 검색 속도는 크게 향상되지 않습니다. 셋째, 텍스트, 이미지, 비트 데이터 형식으로 정의된 열에는 인덱스를 추가하면 안 됩니다. 이는 이러한 열의 데이터 볼륨이 상당히 크거나 값이 거의 없기 때문입니다. 넷째, 수정 성능이 검색 성능보다 훨씬 높을 경우 인덱스를 생성하지 않아야 한다. 수정 성능과 검색 성능이 서로 상반되기 때문이다. 인덱스를 추가하면 검색 성능은 향상되지만 수정 성능은 저하됩니다. 인덱스를 줄이면 수정 성능이 향상되고 검색 성능이 저하됩니다. 따라서 수정 성능이 검색 성능보다 훨씬 높을 경우에는 인덱스를 생성하지 않아야 합니다.
인덱스 생성 방법과 인덱스의 특징
인덱스 생성 방법
인덱스를 생성하는 방법에는 직접 인덱스 생성 방식과 간접 인덱스 생성 방식이 있습니다. CREATE INDEX 문을 사용하거나 인덱스 생성 마법사를 사용하는 등 직접 인덱스를 생성하거나, 테이블에 기본 키 제약 조건이나 고유 키 제약 조건을 정의하는 등 간접적으로 인덱스를 생성하면 인덱스도 동시에 생성됩니다. 시간. 두 방법 모두 인덱스를 생성할 수 있지만 인덱스를 생성하는 구체적인 내용은 다릅니다.
CREATE INDEX 문을 사용하거나 인덱스 생성 마법사를 사용하여 인덱스를 생성합니다. 이는 인덱스를 생성하는 가장 기본적인 방법이며, 필요에 따라 인덱스를 사용자 정의할 수 있는 가장 유연한 방법입니다. 이런 방식으로 인덱스를 생성하면 데이터 페이지의 전체 크기 지정, 정렬, 통계 정렬 등과 같은 다양한 옵션이 있어 인덱스를 최적화할 수 있습니다. 이 방법을 사용하면 인덱스의 유형, 고유성 및 복합성을 지정할 수 있습니다. 즉, 클러스터형 인덱스 또는 비클러스터형 인덱스를 만들 수 있으며 하나 또는 두 개의 열에 인덱스를 만들거나 인덱스를 만들 수 있습니다. 두 개 이상의 열.
기본 키 제약 조건이나 고유 키 제약 조건을 정의하여 인덱스를 간접적으로 생성할 수도 있습니다. 기본 키 제약 조건은 테이블의 레코드가 동일한 기본 키 레코드를 갖도록 제한하여 데이터 무결성을 유지하는 논리입니다.기본 키 제약 조건을 만들 때 시스템은 자동으로 고유한 클러스터형 인덱스를 만듭니다. 논리적으로는 기본키 제약조건이 중요한 구조이지만, 물리적 구조적으로 보면 기본키 제약조건에 해당하는 구조가 클러스터링된 고유 인덱스(Unique Clustered Index)이다. 즉, 물리적 구현에는 기본 키 제약 조건이 없고 고유 클러스터형 인덱스만 있습니다. 마찬가지로 고유 키 제약 조건을 생성할 때 고유 비클러스터형 인덱스인 인덱스도 생성됩니다. 따라서 제약조건을 이용해 인덱스를 생성할 때 인덱스의 종류와 특성은 기본적으로 정해져 있어 사용자가 커스터마이징할 여지가 적다.
테이블에 기본 키 또는 고유 키 제약 조건을 정의할 때 CREATE INDEX 문을 사용하여 생성된 표준 인덱스가 테이블에 이미 있는 경우 기본 키 제약 조건 또는 고유 키 제약 조건에 의해 생성된 인덱스가 이전에 생성된 표준 인덱스를 덮어씁니다. . 즉, 기본 키 제약 조건이나 고유 키 제약 조건에 의해 생성된 인덱스는 CREATE INDEX 문을 사용하여 생성된 인덱스보다 우선 순위가 높습니다.
인덱스의 특성
인덱스에는 고유 인덱스와 복합 인덱스라는 두 가지 특성이 있습니다.
고유 인덱스는 인덱스 열의 모든 데이터가 고유하고 중복된 데이터를 포함하지 않도록 보장합니다. 테이블에 이미 기본 키 제약 조건이나 고유 키 제약 조건이 있는 경우 SQL Server는 테이블이 생성되거나 수정될 때 자동으로 고유 인덱스를 생성합니다. 그러나 고유성을 보장해야 하는 경우에는 고유 인덱스를 생성하는 대신 기본 키 제약 조건이나 고유 키 제약 조건을 생성해야 합니다. 고유 인덱스를 생성할 때 다음 규칙을 주의 깊게 고려해야 합니다. 테이블에 기본 키 제약 조건이나 고유 키 제약 조건을 생성할 때 테이블에 이미 데이터가 포함되어 있으면 SQL Server는 자동으로 고유 인덱스를 생성하고, 인덱스를 생성할 때 SQL을 사용합니다. 서버는 테이블에 이미 있는 데이터의 중복성을 확인합니다. insert 문을 사용하여 데이터를 삽입하거나 수정 문을 사용하여 데이터를 수정할 때마다 SQL Server는 데이터의 중복성을 확인합니다. 중복된 값이 있으면 SQL Server는 해당 문을 취소합니다. 오류 메시지를 실행하고 반환합니다. 테이블의 각 데이터 행에 고유한 값이 있는지 확인하여 각 엔터티를 고유하게 확인할 수 있도록 엔터티의 무결성을 보장할 수 있는 열에만 고유 인덱스를 만들 수 있습니다. 인사 테이블의 이름 열에는 사람들이 동일한 이름을 가질 수 있으므로 고유 인덱스를 생성할 수 없습니다.
복합 인덱스는 두 개 이상의 열에 생성된 인덱스입니다. 검색 시 2개 이상의 컬럼이 키 값으로 작용할 경우 해당 컬럼에 대해 복합 인덱스를 생성하는 것이 가장 좋습니다. 복합 인덱스를 생성할 때 다음 규칙을 고려해야 합니다. 최대 16개의 컬럼을 하나의 복합 인덱스로 결합할 수 있으며, 복합 인덱스를 구성하는 컬럼의 총 길이는 900바이트를 초과할 수 없습니다. 복합 열은 너무 길 수 없습니다. 복합 인덱스에서는 모든 열이 동일한 테이블에서 나와야 하며 복합 인덱스의 테이블 전체에서 복합 열을 만들 수 없으므로 열 순서가 매우 중요합니다. 원칙적으로 가장 고유한 열을 먼저 정의해야 합니다. 예를 들어 (COL1, COL2)의 인덱스는 두 열의 순서가 다르기 때문에 (COL2, COL1)의 인덱스와 동일하지 않습니다. 쿼리 최적화 프로그램이 복합 인덱스를 사용하려면 쿼리의 WHERE 절이 복합 인덱스의 첫 번째 열을 참조해야 합니다. 복합 인덱스는 테이블에 여러 키 열이 있을 때 매우 유용합니다. 복합 인덱스를 사용하면 쿼리 성능이 향상되고 테이블 수량에 생성되는 인덱스 수를 줄일 수 있습니다.
인덱스의 종류
인덱스의 순서가 데이터 테이블의 물리적 순서와 동일한지 여부에 따라 인덱스는 두 가지 유형으로 나눌 수 있습니다. 하나는 데이터 테이블의 물리적 순서가 인덱스 순서와 동일한 클러스터형 인덱스이고, 다른 하나는 데이터 테이블의 물리적 순서가 인덱스 순서와 다른 비클러스터형 인덱스이다.
클러스터형 인덱스의 아키텍처
인덱스의 구조는 트리 구조와 유사합니다. 트리의 최상위 부분을 리프 수준, 트리의 다른 부분을 비리프 수준이라고 합니다. 트리는 리프가 아닌 수준에 있습니다. 마찬가지로 클러스터형 인덱스도 클러스터형 인덱스의 리프 수준과 리프가 아닌 수준이 트리 구조를 이루고, 인덱스의 가장 낮은 수준이 리프 수준이다. 클러스터형 인덱스에서 테이블의 데이터가 위치한 데이터 페이지는 리프 레벨, 리프 레벨 위의 인덱스 페이지는 리프가 아닌 레벨, 인덱스 데이터가 위치한 인덱스 페이지는 리프가 아닌 레벨입니다. 수준. 클러스터형 인덱스에서는 데이터 값의 순서가 항상 오름차순입니다.
클러스터형 인덱스는 테이블에서 자주 검색되거나 순차적으로 액세스되는 열에 생성되어야 합니다.클러스터형 인덱스를 생성할 때 다음 요소를 고려해야 합니다. 테이블에는 하나의 물리적 데이터 순서만 있을 수 있으므로 각 테이블은 하나의 클러스터형 인덱스만 가질 수 있습니다. 테이블 행의 물리적 순서는 물리적 순서와 동일합니다. 비클러스터형 인덱스를 생성하기 전에 클러스터형 인덱스를 생성하세요. 이는 클러스터형 인덱스가 테이블 행의 물리적 순서를 변경하기 때문입니다. 자동으로 키 값의 고유성을 유지하거나 UNIQUE 키 단어를 사용하여 명시적으로 유지하거나 시스템 자체에서 사용하고 사용자가 액세스할 수 없는 내부 고유 식별자를 통해 유지합니다. 클러스터형 인덱스의 평균 크기는 약 5%입니다. 그러나 실제로 클러스터형 인덱스의 크기는 인덱스 생성 과정에서 인덱스 열의 크기에 따라 변경되는 경우가 많습니다. 인덱스를 생성하려면 테이블 공간의 1.2배가 필요하므로 클러스터형 인덱스를 생성할 수 있는 충분한 공간이 필요합니다.
시스템은 테이블의 데이터에 액세스할 때 먼저 해당 열에 인덱스가 있는지, 해당 인덱스가 검색할 데이터에 의미가 있는지 확인합니다. 인덱스가 존재하고 의미가 있으면 시스템은 인덱스를 사용하여 테이블의 레코드에 액세스합니다. 시스템은 인덱스부터 시작하여 데이터를 찾고, 인덱스 검색은 트리 인덱스의 루트부터 시작합니다. 루트부터 시작하여 검색 값을 각 키 값과 비교하여 검색 값이 키 값보다 크거나 같은지 확인합니다. 검색값보다 큰 키값이 발견되거나, 검색값이 인덱스 페이지의 모든 키값보다 크거나 같을 때까지 이 단계를 반복합니다.
비클러스터형 인덱스의 구조
비클러스터형 인덱스의 구조도 트리 구조로 클러스터형 인덱스의 구조와 매우 유사하지만 분명한 차이점도 있습니다.
비클러스터형 인덱스에서 리프 수준에는 키 값만 포함되고 데이터 행은 포함되지 않습니다. 비클러스터형 인덱스는 행의 논리적 순서를 나타냅니다. 비클러스터형 인덱스에는 두 가지 아키텍처가 있습니다. 한 아키텍처는 클러스터형 인덱스가 없는 테이블에 비클러스터형 인덱스를 생성하고, 다른 아키텍처는 클러스터형 인덱스가 있는 테이블에 비클러스터형 인덱스를 생성합니다.
데이터 테이블에 클러스터형 인덱스가 없으면 데이터 테이블을 데이터 힙이라고도 합니다. 비클러스터형 인덱스가 데이터 힙 상단에 생성되면 시스템은 인덱스 페이지의 행 식별자를 사용하여 데이터 페이지의 레코드를 가리킵니다. 행 식별자는 데이터가 있는 위치에 대한 정보를 저장합니다. 데이터 힙은 IAM(Index Allocation Map) 페이지를 사용하여 유지 관리됩니다. IAM 페이지에는 데이터 힙이 위치한 클러스터의 스토리지 정보가 포함되어 있습니다. 시스템 테이블 sysindexes에는 데이터 힙과 관련된 첫 번째 IAM 페이지를 가리키는 포인터가 있습니다. 시스템은 IAM 페이지를 사용하여 데이터 힙을 탐색하고 새 행을 삽입할 수 있는 공간을 찾습니다. 이러한 데이터 페이지와 이러한 데이터 페이지 내의 레코드는 순서가 없으며 서로 연결되어 있지 않습니다. 이러한 데이터 페이지 간의 유일한 연결은 IAM의 레코드 순서입니다. 데이터 힙에 비클러스터형 인덱스가 생성되면 리프 수준에는 데이터 페이지를 가리키는 행 식별자가 포함됩니다. 행 식별자는 레코드 행의 논리적 순서를 지정하며 파일 ID, 페이지 번호 및 행 ID로 구성됩니다. 행 식별자는 고유하게 유지됩니다. 비클러스터형 인덱스의 리프 수준 페이지 순서는 테이블에 있는 데이터의 물리적 순서와 다릅니다. 이러한 키 값은 리프 수준에서 오름차순으로 유지됩니다.
클러스터형 인덱스가 있는 테이블에 비클러스터형 인덱스가 생성되면 시스템은 클러스터형 인덱스를 가리키는 인덱스 페이지의 클러스터형 키를 사용합니다. 클러스터링 키는 데이터의 위치 정보를 저장합니다. 테이블에 클러스터형 인덱스가 있는 경우 비클러스터형 인덱스의 리프 수준에는 물리적 행 식별자에 매핑되는 대신 클러스터형 키에 매핑되는 클러스터형 키 값이 포함됩니다. 시스템이 비클러스터형 인덱스가 있는 테이블의 데이터에 액세스하고 이 비클러스터형 인덱스가 클러스터형 인덱스에 생성되면 먼저 비클러스터형 인덱스에서 클러스터형 인덱스에 대한 포인터를 찾은 다음 클러스터형 인덱스를 사용합니다. 클러스터형 인덱스에 대한 포인터를 찾으려면 인덱스를 사용하여 데이터를 찾으세요.
비클러스터형 인덱스는 데이터를 여러 방법으로 검색해야 할 때 매우 유용합니다. 비클러스터형 인덱스를 생성할 때 다음 상황을 고려하십시오. 기본적으로 생성된 인덱스는 각 테이블에 비클러스터형 인덱스이며 최대 249개의 비클러스터형 인덱스를 생성할 수 있지만 클러스터형 인덱스는 최대 1개만 생성할 수 있습니다. .