2장 인덱스 설계와 프로그램적 처리

논리적인 데이터 모델의 설계가 좋다고 해서 효율적으로 SQL을 작성할 수 있는 것은 아니다. 물리적으로도 적절한 방식으로 설계를 구현해야 하며, 그렇지 않으면 SQL로 데이터에서 유의미한 정보를 효율적으로 추출하기 어렵다. 그 중 테이블에 적절한 인덱스를 만들었는지 여부는 SQL 쿼리가 잘 수행되는지 확인하는 핵심 요소 중 하나다.'

 

BETTER WAY 10 인덱스를 만들 때는 널을 고려하자

널은 다른 값, 심지어 또 다른 널과도 동등이나 비동등 연산자로 비교가 불가능하다. 널 값이 있는지 알아내려면 IS NULL 연산자를 사용해야 한다. 인덱스로 생성된 컬럼 값이 대부분 널이라면 항상 NULL이 아닌 값을 조회하지 않는 한 해당 인덱스는 많이 사용하지 않을 것이다. 컬럼에 있는 값을 좀 더 빠르게 검색하고 싶은데 값의 대다수가 NULL이라면, 데이터베이스가 지원하는 한 널 값을 제외하고 인덱스를 만드는 것이 좋다.

데이터베이스 시스템마다 인덱스에 있는 널 값을 처리하는 방식이 다르지만, 주요 데이터베이스 시스템의 공통적인 특성은 기본키에 속한 컬럼에 널 값을 허용하지 않는다는 것이다.

그 중 SQL Server는 인덱스에 널 값 입력이 가능하고 모든 널 값을 동일하다고 여긴다. 기본키 컬럼에는 NULL을 저장하지 못하며, 유일키 컬럼에는 널 값을 하나만 저장할 수 있다. 인덱스에서 널 값을 제외하려면 필터링된 인덱스(Filtered Index)를 만들어야 한다.

CREATE INDEX CustPhone_IDX
  ON Customers (CustPhoneNumber)
  WHERE CustPhoneNumber IS NOT NULL;

만일 CustPhoneNumber 컬럼에 IS NULL 조건을 제시하면 SQL Server는 해당 조건 검색을 수행할 때 이 필터링된 인덱스를 사용하지 않을 것이다.

SQL Server는 빈 문자열(VARCHAR)을 NULL로 변환하지 않기 때문에 CustPhoneNumber 컬럼에 빈 문자열이 포함되어 있어도 인덱스를 만들 수 있다.

 

 

BETTER WAY 11 인덱스와 데이터 스캔을 최소화하도록 인덱스는 신중히 만들자

보통은 쿼리를 튜닝하면 훨씬 적은 비용으로 성능 향상을 기대할 수 있다. 흔히 성능 문제는 인덱스가 부족하거나 올바르지 않은 인덱스를 만들어서 발생한다.

쿼리를 만족하는 레코드를 찾기 위해 인덱스를 사용하는 것을 인덱스 탐색이라고 한다. DB 엔진이 적합한 레코드를 찾으려고 인덱스와 데이터 페이지를 스캔하는 것을 각각 인덱스 스캔(Index Scan), 테이블 스캔(Table Scan)이라고 한다. 데이터가 많을수록 인덱스 스캔을 완료하는 데에 시간이 걸린다.

다음의 Customers테이블을 보면,

CustomerID를 기본키로 선언했으므로 이 컬럼에 인덱스를 자동으로 만들었고, CREATE INDEX 문으로 CustState 컬럼에도 인덱스를 만들었다.

CREATE TABLE Customers (
  CustomerID int PRIMARY KEY NOT NULL,
  CustFirstName varchar(25) NULL,
  CustLastName varchar(25) NULL,
  CustStreetAddress varchar(50) NULL,
  CustCity varchar(30) NULL,
  CustState varchar(2) NULL,
  CustZipCode varchar(10) NULL,
  CustAreaCode smallint NULL,
  CustPhoneNumber varchar(8) NULL
);

CREATE INDEX CustState ON Customers (CustState);

아래 4개의 쿼리문을 살펴보자.

SELECT * FROM Customers WHERE CustomerID = 1;                    -- 1
SELECT CustomerID FROM Customers WHERE CustomerID = 25;            -- 2
SELECT * FROM Customers WHERE CustState = 'TX';                    -- 3
SELECT CustomerID FROM Customers WHERE CustAreaCode = '905';    -- 4

1번: 기본키에서 유일 인덱스 탐색을 한 후, 해당 인덱스로 Customers 테이블에서 CustomerID=1인 모든 데이터를 반환한다.

2번: 필요한 값이 모두 인덱스에 있으므로, 불필요하게 Customers 테이블을 보지 않고 유일 인덱스 탐색만 한다.

3번: CustState 컬럼에 인덱스를 만들었지만 이는 유일 인덱스가 아니기 때문에 WHERE 조건을 만족하는 모든 값을 찾으려면 인덱스 전체를 뒤져야 한다(인덱스 스캔). 또, 인덱스에 없는 컬럼도 SELECT에 있기 때문에 이런 값까지 가져오려면 테이블로 돌아가서 데이터를 가져와야 한다.

4번: CustAreaCode 컬럼에는 인덱스가 없으므로 조건에 맞는 데이터를 찾기 위해 테이블에서 전체 로우를 뒤져야 한다(테이블 스캔).

 

임의의 객체에서 모든 항목을 검색한다는 점에서 테이블 스캔과 인덱스 스캔에 큰 차이가 없어 보이지만, 인덱스는 테이블에 비해 훨씬 작고 스캔용으로 설계된 객체이므로 월등히 빠르다. 그러나 인덱스가 많다고 모든 것이 해결될 것이라 생각하면 안된다.

인덱스 컬럼 하나를 갱신할 때마다 ‘인덱스 테이블’을 하나 이상 갱신하게 되며, 이는 더 많은 디스크 읽기와 쓰기가 발생함을 의미한다. 인덱스는 구조화가 매우 잘 되어 있기 때문에 인덱스를 갱신하는 작업은 종종 테이블을 갱신하는 작업보다 비용이 많이 든다.

 

 

BETTER WAY 12 인덱스를 단순 필터링 이상의 목적으로 사용하자

조인 조건 컬럼의 인덱스 유무

컬럼을 인덱스로 만들었는지 여부는 테이블 간 조인이 얼마나 효율적으로 수행되는지에 영향을 미친다. 조인은 여러 테이블에 분산된 데이터를 결합하므로 여러 페이지에 있는 데이터를 더 많이 읽느라 디스크 탐색 대기 시간에 민감하다. 따라서 적절하게 인덱스를 만드는 것은 조인이 응답하는 시간에 큰 영향을 줄 수 있다.

쿼리를 수행할 때는 일반적으로 세 가지 조인 알고리즘(중첩 루프, 해시 조인, 소트머지 조인)을 사용한다.

 

1. 중첩 루프(Nested Loop) 조인

가장 기본적인 조인 알고리즘으로, 쿼리 두 개가 중첩되어 있다고 생각하면 쉽다. 선행(Outer) 쿼리가 한 테이블에서 결과 집합을 가져오고, 두 번째 쿼리는 선행 쿼리 결과 집합의 각 로우에 대응하는 데이터를 다른 테이블에서 가져온다. 중첩 루프 조인은 조인 조건에 참여하는 컬럼을 인덱스로 만들었을 때 가장 효과가 좋다. 선행 쿼리가 작은 결과 집합을 반환할 때 중첩 루프 조인은 좋은 성능을 보인다.

 

2. 해시(Hash) 조인

조인에 참여하는 한쪽 테이블 데이터를 해시 테이블로 만든 후 다른 쪽 테이블의 각 로우를 매우 빠르게 탐색할 수 있다. 해시 조인은 해시 테이블을 사용하므로 조인되는 컬럼을 인덱스로 만들 필요가 없다. 해시 조인의 성능을 향상할 수 있는 유일 인덱스는 WHERE 조건절 또는 조인할 때 ON 절에 사용되는 컬럼에 대한 인덱스다.

 

3. 소트머지(Sort-merge) 조인

조인 조건에 따라 두 테이블을 각각 정렬한 후 지퍼처럼 정렬된 두 항목을 결합하는 식으로 수행된다. 소트머지 조인은 많은 점에서 해시 조인과 유사하다. 조인 조건 컬럼만 인덱스로 만드는 것은 소용없고, 한 번에 모든 후보 레코드를 읽을 수 있는 독립적인 조건을 구성하는 컬럼에 대한 인덱스가 필요하다. 조인 순서가 의미가 없고 성능에도 영향을 미치지 않는다.

 

ORDER BY와 인덱스

인덱스는 ORDER BY 절의 효율에도 영향을 미친다. 특히 B-tree 인덱스는 인덱스로 만든 컬럼의 데이터를 정렬해 둔다. 이것은 인덱스를 사용하면 ORDER BY 절에 명시된 대로 정렬하는 연산을 피할 수 있다는 의미다.

실제로 정렬된 인덱스는 정렬 작업을 줄일 뿐만 아니라 모든 데이터를 처리하지 않고도 첫 번째 결과 집합을 반환할 수 있는 파이프라인 효과를 볼 수 있다. 이런 효과를 내려면 WHERE 절에서 사용할 인덱스 컬럼을 ORDER BY 절에서도 동일한 순서로 사용해야 한다.

 

 

BETTER WAY 13 트리거를 남발하지 말자

대부분의 DBMS는 테이블에 DELETE, INSERT, UPDATE를 수행할 때 자동으로 트리거를 실행하는 기능을 제공한다. 트리거는 계산 데이터를 갱신하는 데도 사용하지만, 트리거 대신 DRI(선언적 참조 무결성)를 사용하는 것이 더 쉽고 빠르며 효율적이다.

각종 제약 조건을 사용하면 DB 엔진이 자동으로 무결성을 강화하도록 할 수 있다. 또 옵티마이저는 제약 조건으로 더 나은 성능의 쿼리 실행 계획을 만든다.

 

DRI 적용 방법

INSERT : 자식 테이블에 새 로우 입력 시, 키 값이 부모 테이블에 존재할 때만 입력 가능

UPDATE / DELETE : CASCADE(부모 테이블 변경 시 자식 테이블에 전파), NO ACTION(해당 로우 참조 시 키 값 변경 불가), SET NULL / SET DEFAULT(부모 테이블 키 값 변경 또는 삭제 시 자식 테이블 값을 NULL이나 기본 값으로 설정)

-- 자식 테이블에서 길 잃은 레코드를 방지하는 DRI 사용
ALTER TABLE Order_Details
  ADD CONSTRAINT fkOrder FOREIGN KEY (OrderNumber)
    REFERENCES Orders (OrderNumber) ON DELETE CASCADE;

 

 

트리거는 데이터를 관리하는 한 가지 접근 방법일 뿐 최상의 방법은 아니다. 물론 다음과 같이 트리거를 사용하는 것이 적절할 때도 있다.

• 중복 또는 파생 데이터의 관리 : 역정규화된 데이터베이스는 일반적으로 데이터를 중복으로 저장한다. 트리거로 이런 데이터를 동기화할 수 있다.
• 복잡한 컬럼 제약 조건 : 컬럼의 제약 조건이 동일한 테이블의 다른 로우 또는 다른 테이블의 로우에 의존한다면, 트리거가 이 컬럼의 제약 조건을 유지하는 최상의 방법이다.
• 복잡한 기본 값 : 다른 컬럼, 로우, 테이블에 있는 데이터를 기준으로 기본 값을 생성하는 데 트리거를 사용할 수 있다.
• 데이터베이스 간 참조 무결성 : 관련 테이블을 두 데이터베이스에 나누었다면 트리거로 데이터베이스 간 참조 무결성을 확보할 수 있다.

이러한 경우 테이블보다는 뷰에 트리거를 생성하면 좋다. 이렇게 하면 대량의 가져오기(Import)/내보내기(Export) 작업을 할 때는 트리거를 작동하지 않고, 애플리케이션에서 데이터를 처리할 때만 트리거를 작동할 수 있기 때문이다.

 

 

BETTER WAY 14 데이터의 부분 집합을 포함하거나 제외하려면 필터링된 인덱스를 사용하자

필터링된 인덱스(SQL Server)나 부분(Partial) 인덱스(PostgreSQL)는 비클러스터드 인덱스이며, 테이블에 있는 일부 로우의 집합만 포함한다. 필터링된 인덱스는 성능과 저장 용량이라는 두 마리 토끼를 잡을 수 있다. 인덱스에 있는 로우의 개수가 적어서 필요한 I/O 작업도 적기 때문이다. 

 

필터링된 인덱스는 인덱스를 만들 때 WHERE 절을 추가해서 생성(단, 해당 값들이 모두 NULL이거나, 모두 NOT NULL일 때만 가능)한다. 테이블 데이터의 전체 값 중 차지하는 비율이 적고 WHERE 절에서 빈번히 사용되는 값이 있을 때, 필터링된 인덱스를 사용하면 전통적인 인덱스에 비해 그 성능이 월등히 좋아진다. 필터링된 인덱스를 만들 때 사용하는 WHERE 절에서는 결정적 함수만 사용할 수 있고 OR 연산자는 사용할 수 없다.

 

SQL Server에서는 몇 가지 추가 제약이 있다. 필터 조건에서는 계산 컬럼, UDT(User Defined Type)(사용자 정의 타입) 컬럼, 공간 데이터 타입 컬럼, hierarchyID 데이터 타입 컬럼, BETWEEN, NOT IN 연산자, CASE 문을 사용할 수 없다.

 

-- 필터링이 적용된 컬럼은 인덱스에 포함할 필요가 없다.
CREATE NONCLUSTERED INDEX LowProducts
  ON Products (ProductNumber)
  WHERE QuantityOnHand < 10;
  
-- 조건절에서 IN 연산자를 사용하는 필터링된 인덱스 생성
CREATE NONCLUSTED INDEX PendingDocuments
  ON DocumentStatus (DocumentNumber, Status)
  WHERE Status IN ('Pending publication', 'Pending expiration');
  
-- 동일 컬럼에 필터링된 인덱스를 여러 개 생성
CREATE NONCLUSTERED INDEX PendPubDocuments
  ON DocumentStatus (DocumentNumber, Status)
  WHERE Status = ‘Pending publication’;
CREATE NONCLUSTERED INDEX PendExpDocuments
  ON DocumentStatus (DocumentNumber, Status)
  WHERE Status = ‘Pending expiration’;
  
-- 정렬 연산이 필요한 쿼리
SELECT ProductNumber, ProductName
FROM Products
WHERE CategoryID IN (1, 5, 9)
ORDER BY ProductName;
-- 정렬 연산을 위한 필터링된 인덱스 생성
CREATE INDEX SelectProducts
ON Products(ProductName, ProductNumber)
WHERE CategoryID IN (1, 5, 9);

 

 

BETTER WAY 15 프로그래밍으로 검사하는 대신 선언적 제약 조건을 사용하자

DB가 정상 작동하려면 반드시 각 필드에 유효한 값이 있는지 확인하고 이런 필드에 있는 데이터의 무결성을 강화해야 한다. SQL은 이를 위해 여러 제약 조건을 제공한다.

 

SQL은 테이블 데이터 규칙을 명시하는 방식으로 제약 조건을 제공한다. INSERT, DELETE, UPDATE 작업이 일어날 때 모든 제약 조건이 검사된다. 이런 제약 조건을 위반하면 해당 작업은 진행되지 않는다.

 

다음과 같이 6가지 제약 조건이 있다.

제약 조건은 테이블을 생성할 때(CREATE TABLE 문)나 테이블을 생성한 후(ALTER TABLE 문)에 명시가 가능하다.

Keyword	Note
NOT NULL	NULL이 아닌 값만 입력 가능
UNIQUE	중복 값 입력 방지, NULL 허용 한 테이블에 여러 개 정의할 수 있음
PRIMARY KEY	유일한 값만 입력 가능하며 NULL을 허용하지 않음 한 테이블에 한 개만 정의할 수 있음
FOREIGN KEY	다른 테이블의 기본키가 됨
CHECK	한 테이블이나 필드에 정의 가능 - 단일 필드: 특정 값만 입력 가능 - 테이블: 특정 필드 값이 동일한 로우의 다른 필드 값을 기준으로 제한
DEFAULT	한 필드의 기본 값을 정의하는 데 사용

 

 

BETTER WAY 16 자신이 사용하고 작성하는 SQL의 종류를 파악하자

효율적으로 SQL 문을 작성하려면 사용 중인 DBMS가 어떤 종류의 SQL을 사용하는지 이해해야 한다. DBMS별로 SQL 구현 내용이 다르므로 동일한 SQL 문이라도 성능은 다르다. 

 

	SQL Server	MySQL	Oracle	PostgreSQL
정렬에서의 NULL	널이 아닌 값 -> 널	널이 아닌 값 -> 널 (컬럼 이름 앞에 -를 붙이면 ASC는 DESC로, DESC는 ASC로 바뀜)	널 -> 널이 아닌 값 (ORDER BY 절에서 NULLS FIRST나 NULLS LAST 추가하면 변경 가능)	널 -> 널이 아닌 값 (ORDER BY 절에서 NULLS FIRST나 NULLS LAST 추가하면 변경 가능)
반환 로우 개수 제한	ROW_NUMBER(), 표준 기반의 커서	표준 기반의 커서, LIMIT 연산자	ROW_NUMBER(), 표준 기반의 커서, ROWNUM 의사 컬럼	모두 가능
BOOLEAN 타입 지원 여부	지원 안함 BIT 타입(0, 1, NULL) 지원	지원 TINYINY(1)로 사용	지원 안함	지원
UNIQUE 제약 조건	널 허용 (단, 널 값을 하나만 가질 수 있음)	널 허용	널 허용	널 허용

 

 

BETTER WAY 17 인덱스에서 계산 결과를 사용할 시기를 파악하자

BETTER WAY 11. 인덱스와 데이터 스캔을 최소화하도록 인덱스는 신중히 만들자’에서는 계산 컬럼을 저장하기보다는 함수를 사용하는 내용을 다루었다. 함수 기반의 계산 컬럼을 인덱스로 만드는 것이 가능하다.

 

함수 기반 인덱스를 사용하는 공통적인 이유 한 가지는 쿼리에서 대·소문자를 처리하기 위해서다. 액세스 SQL Server, MySQL은 기본적으로 대·소문자를 구분하지 않는다.

-- 대·소문자를 구분하지 않는 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
FROM Employees
WHERE EmpLastName = ‘Viescas’;

실제로 저장된 값이 viescas, VIESCAS, Viescas, 심지어는 ViEsCaS더라도 액세스 SQL Server와 MySQL은 동일한 사람을 결과로 반환한다. 이런 DBMS에서 대·소문자에 상관없이 결과를 얻으려면 다음과 같은 쿼리를 사용해야 한다.

-- 대·소문자를 구분하는 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
FROM Employees
WHERE UPPER(EmpLastName) = ‘VIESCAS’;

이처럼 WHERE 절에서 함수를 사용하는 쿼리는 인덱스로 쿼리 속도를 빠르게 하지 못하고 테이블 스캔이 수행된다. 아래와 같이 인덱스를 만든다면 위의 대소문자를 구분하는 쿼리는 인덱스를 사용할 것이다.

CREATE INDEX EmpLastNameUpper
  ON Employees (UPPER(EmpLastName));

 

SQL Server에서는 단순히 함수를 기반으로 인덱스를 만들 수 없고, 테이블에 계산 컬럼을 추가한 후 이 컬럼에 인덱스를 만들어야 한다. 함수 기반 인덱스는 쿼리 최적화 관점에서 많은 이점을 제공한다. 다만 테이블에 인덱스가 많을수록 UPDATE 작업이 느려지고, 특히 함수 기반 인덱스는 중복 인덱스를 만들기가 매우 쉬우므로 과다하게 만들지 않도록 주의해야 한다.