SQL
색인

소개

비고

색인 만들기

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

그러면 Cars 테이블의 EmployeeId 열에 대한 색인이 작성됩니다. 이 인덱스는 다음과 같이 EmployeeId 에서 서버가 값을 기준으로 정렬하거나 선택하도록 서버에 요청하는 쿼리의 속도를 향상시킵니다.

SELECT * FROM Cars WHERE EmployeeId = 1

인덱스는 다음과 같이 둘 이상의 열을 포함 할 수 있습니다.

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

이 경우 색인은 조건 세트가 동일한 순서로 정렬 된 경우 모든 포함 된 열을 정렬하거나 선택하도록 요청하는 조회에 유용합니다. 즉, 데이터를 검색 할 때 전체 테이블을 조사하는 대신 인덱스를 사용하여 검색 할 행을 찾을 수 있습니다.

예를 들어 다음과 같은 경우에는 두 번째 색인을 사용합니다.

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

그러나 순서가 다른 경우 색인은 다음과 같이 동일한 이점을 갖지 않습니다.

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

소유자가 OwnerId = 17 인 항목을 찾으려면 데이터베이스가 EmployeeId 및 CarID의 모든 값에서 전체 색인을 검색해야하므로 색인이 도움이되지 않습니다.

인덱스가 계속 사용될 수도 있으며 쿼리 최적화 프로그램에서 인덱스를 검색하고 OwnerId 필터링 한 다음 필요한 테이블 만 검색하면 전체 테이블을 검색하는 것보다 빠를 수 있습니다 (특히 테이블이 큰 경우).

클러스터 된 고유 인덱스 및 정렬 된 인덱스

인덱스에는 생성시 또는 기존 인덱스를 변경하여 설정할 수있는 몇 가지 특성이 있습니다.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

위의 SQL 문은 Employees에 새로운 클러스터형 인덱스를 생성합니다. 클러스터 된 인덱스는 테이블의 실제 구조를 지정하는 인덱스입니다. 테이블 자체는 인덱스의 구조와 일치하도록 정렬됩니다. 즉, 테이블에 최대 하나의 클러스터 된 인덱스가있을 수 있습니다. 클러스터형 인덱스가 이미 테이블에 있으면 위의 명령문은 실패합니다. (클러스터 된 인덱스가없는 테이블은 힙이라고도합니다.)

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

이렇게하면 Customers 테이블의 Email 열에 대한 고유 인덱스가 작성됩니다. 이 인덱스는 일반 인덱스와 같은 쿼리 속도를 높이는 동시에 해당 열의 모든 전자 메일 주소를 고유하게 만듭니다. 고유하지 않은 Email 값으로 행을 삽입하거나 업데이트하면 기본적으로 삽입 또는 업데이트가 실패합니다.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

EmployeeID가 고유해야하는 테이블 제약 조건을 만드는 인덱스를 Customers에 만듭니다. (열이 현재 고유하지 않은 경우 실패합니다.이 경우 ID를 공유하는 직원이있는 경우 실패합니다.)

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

그러면 내림차순으로 정렬 된 인덱스가 만들어집니다. 기본적으로 MSSQL 서버의 인덱스는 오름차순이지만 변경할 수 있습니다.

고유 색인으로 삽입

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Customers 의 Email 열에 고유 색인이 설정된 경우이 작업은 실패합니다. 그러나이 경우 대체 동작을 정의 할 수 있습니다.

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE : 인덱스 삭제

이 명령은 테이블의 색인을 삭제합니다. 그것은 SAP ASE 서버에서 작동합니다.

통사론:

DROP INDEX [table name].[index name]

예:

DROP INDEX Cars.index_1

정렬 된 색인

인덱스를 검색하는 방식으로 정렬 된 인덱스를 사용하는 경우 SELECT 문은 검색시 추가 정렬을 수행하지 않습니다.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

쿼리를 실행할 때

SELECT * FROM scoreboard ORDER BY score DESC;

데이터베이스 시스템은 인덱스 룩업을이 순서로 수행 할 수 있으므로 추가 정렬을 수행하지 않습니다.

인덱스 삭제 또는 비활성화 및 재구성

DROP INDEX ix_cars_employee_id ON Cars;  

DROP 명령을 사용하여 색인을 삭제할 수 있습니다. 이 예에서 우리는 것이다 DROP 테이블 자동차에 ix_cars_employee_id라는 인덱스를.

이렇게하면 인덱스가 완전히 삭제되고 인덱스가 클러스터 된 경우 클러스터링이 제거됩니다. 느리고 계산 비용이 많이 드는 인덱스를 다시 만들지 않고는 다시 작성할 수 없습니다. 또는 인덱스를 비활성화 할 수 있습니다.

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

이렇게하면 테이블이 인덱스에 대한 메타 데이터와 함께 구조를 유지할 수 있습니다.

비판적으로 이는 인덱스 통계를 유지하므로 변경 사항을 쉽게 평가할 수 있습니다. 보증 된 경우 색인은 나중에 완전히 다시 작성되는 대신 다시 작성 될 수 있습니다.

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

NULLS를 허용하는 고유 인덱스

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

이 스키마는 0..1 관계를 허용합니다. 사람들은 0 또는 1 개의 운전 면허를 가질 수 있으며 각 면허는 한 사람에게만 속할 수 있습니다

색인 다시 작성

시간이 지남에 따라 B-Tree 인덱스는 데이터 업데이트 / 삭제 / 삽입으로 인해 조각화 될 수 있습니다. SQLServer 용어에서 우리는 내부 (색인 페이지는 절반 비어 있음) 및 외부 (논리적 페이지 순서는 물리적 순서와 일치하지 않음)를 가질 수 있습니다. 색인 다시 작성은 색인을 삭제하고 다시 작성하는 것과 매우 유사합니다.

우리는 다음과 같이 색인을 다시 작성할 수 있습니다.

ALTER INDEX index_name REBUILD; 

기본적으로 재 구축 인덱스는 테이블을 잠그고 DML을 방지하는 오프라인 작업이지만 많은 RDBMS가 온라인 재구성을 허용합니다. 또한 일부 DB 공급 업체는 REORGANIZE (SQLServer) 또는 COALESCE / SHRINK SPACE (Oracle)와 같은 인덱스 재 구축 대안을 제공합니다.

클러스터 된 인덱스

클러스터 된 인덱스를 사용하는 경우 테이블의 행은 클러스터 된 인덱스가 적용된 열을 기준으로 정렬됩니다. 따라서 두 개의 다른 열로 테이블을 정렬 할 수 없으므로 테이블에는 클러스터 된 인덱스가 하나만있을 수 있습니다.

일반적으로 큰 데이터 테이블에서 읽기를 수행 할 때는 클러스터 된 인덱스를 사용하는 것이 가장 좋습니다. 클러스터 된 인덱스의 단점은 테이블에 쓰고 데이터를 재구성 (의지)해야 할 때입니다.

테이블에 클러스터 된 인덱스를 만드는 예제 Employee_Surname 열에있는 Employees :

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

비 클러스터형 인덱스

비 클러스터형 인덱스는 테이블과 별도로 저장됩니다. 이 구조체의 각 인덱스는 테이블에있는 행의 포인터를 포함합니다.

이 포인터를 행 로케이터라고합니다. 행 위치 지정자의 구조는 데이터 페이지가 힙 또는 클러스터 된 테이블에 저장되는지 여부에 따라 다릅니다. 힙의 경우, 행 위치 지정자는 행에 대한 포인터입니다. 클러스터 된 테이블의 경우 행 위치 지정자가 클러스터 된 인덱스 키입니다.

Employees 및 Column Employee_Surname 테이블에 클러스터되지 않은 인덱스를 만드는 예제입니다.

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

테이블에 비 클러스터형 인덱스가 여러 개있을 수 있습니다. 읽기 작업은 클러스터 된 인덱스보다 클러스터되지 않은 인덱스가 일반적으로 인덱스와 테이블보다 먼저 수행해야하므로 느립니다. 그러나 쓰기 조작에는 제한이 없습니다.

부분 또는 필터링 된 인덱스

SQL Server 및 SQLite를 사용하면 열의 하위 집합뿐만 아니라 행의 하위 집합도 포함하는 인덱스를 만들 수 있습니다.

order_state_id 가 finished (2) 인 order가 일정하게 증가하고 order_state_id equal 가 started (1) 인 주문이 안정적으로 증가 order_state_id equal .

비즈니스에서 다음과 같은 검색어를 사용하는 경우 :

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

부분 인덱싱을 사용하면 미완료 주문을 포함하여 인덱스를 제한 할 수 있습니다.

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

이 인덱스는 필터링되지 않은 인덱스보다 작으므로 공간을 절약하고 인덱스를 업데이트하는 비용을 줄입니다.