13.1 인덱스
id열의 숫자 순서대로 행을 저장하거나, 알파벳 숫자 순서대로 행을 저장하지 않는다.
테이블을 조회할 때 서버는 쿼리에 응답하기 위해 테이블의 모든 행을 검사해야 한다.
SELECT first_name, last_name
FROM customer
WHERE last_name LIKE 'Y%';성이 Y로 시작하는 모든 고객을 찾으려면 서버는 각 행을 확인해서 last_name 열의 내용을 확인해야 한다. 이 때, 성이 Y로 시작하면 해당 행을 결과에 추가함. 이러한 유형을 테이블 스캔 이라고 함.
만약 행이 3백만개가 넘어간다고 생각해보자. 서버의 추가 기능을 이용하지 않고서는 적절한 시간 내에 쿼리에 응답할 수 없다.
이경우 customer 테이블에 하나 이상의 인덱스 를 사용해야 한다.
간단히 말하자면, 자료내에서 특정 항목을 찾기 위한 메커니즘.
사람이 책에서 인덱스를 사용해서 책에서 단어를 찾는 것과 같은 방식으로 DB서버도 인덱스를 사용해서 테이블에서 행을 찾는다.
인덱스는 일반적인 데이터 테이블과 달리 특정 순서로 유지되는 특수 테이블.
다만 인덱스는 모든 데이터를 포함하는 대신, 행이 실제로 존재하는 위치에 대한 정보와 함께 데이터 테이블에서 행을 찾을 때 필요한 열만 포함한다.
따라서 인덱스의 역할은 테이블의 모든 행을 확인할 필요없이 테이블의 행과 열의 서브셋을 쉽게 검색하는 것.
테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
13.1.1 인덱스 생성
ALTER TABLE customer
ADD INDEX idx_email (email);email열에 인덱스를 추가하면 해당 열의 값을 사용하는 쿼리뿐만 아니라, 고객의 이메일 주소를 사용하는 모든 update 또는 delete 연산의 처리 속도를 높일 수 있다.
idx_email이라는 이름의 인덱스가 정의된다.
쿼리 옵티마이저가 유용하다고 판단될 경우, 인덱스를 사용하도록 선택할 수 있다.
테이블에 둘 이상의 옵티마이저는 특정 SQL문에 가장 유용한 인덱스를 결정해야 한다.
참고
MySQL은 인덱스를 테이블의 선택적 구성요소로 취급하므로, ALTER TABLE명령어로 인덱스 추가 및 제거했음. SQL서버와, 오라클은 인덱스를 독립적인 스키마 개체로 취급함.
따라서
CREATE INDEX idx_email ON customer (email);과 같은 명령어로 인덱스를 생성한다.
SHOW INDEX FROM customer;를 사용해서 특정테이블의 모든 인덱스를 확인할 수 있다.
테이블이 생성될 때 MySQL 서버는 기본 키열(customer_id)에 인덱스를 자동으로 생성하고, 인덱스에 PRIMARY라는 이름을 부여한다.
이것은 기본 키 제약 조건과 함께 사용되는 특수한 유형의 인덱스.
#MySQL
ALTER TABLE customer
DROP INDEX idx_email;
# SQL Server, Oracle
DROP INDEX idx_email ON customer;
DROP INDEX idx_email;다음 명령어를 사용해 인덱스를 제거할 수 있다.
고유 인덱스 (Unique)
만약 고객들의 이메일 주소가 동일하기를 원하지 않는다면, unique index를 만들어서 중복값에 대한 규칙을 적용할 수 있다.
일반 인덱스의 모든 이점을 제공함과 동시에 인덱스 열에 중복값을 허용하지 않음.
행이 삽입되거나, 인덱스 열이 수정될 때 마다 데이터베이스 서버는 고유 인덱스를 확인하여, 테이블의 다른 행에 값이 이미 있는지 확인함.
ALTER TABLE customer
ADD UNIQUE idx_email (email);기본 키 값의 고유성을 확인하므로, 기본 키 열에 고유 인덱스를 생성해서는 안된다.
그러나, 꼭 필요한 경우엔 동일 테이블에 하나 이상의 고유 인덱스를 만들 수 있다.
다중 열 인덱스
이름과 성으로 고객을 검색할 경우, 다음과 같이 두 열을 함께 사용해 인덱스로 만들 수 있다.
ALTER TABLE customer
ADD INDEX idx_full_name (last_name, first_name);이 인덱스는 성과 이름 또는 성만 검색하는 쿼리에는 유용하다.
그러나, 고객의 이름만 검색하는 쿼리에는 유용하지 않다.
따라서 다중 열 인덱스를 생성할 때 인덱스를 최대한 유용하게 만들려면, 두 열을 신중하게 고려해야 한다. 다만 적절한 응답시간을 보장하기 위해 필요한 동일한 열 집합을 사용해서 다른 순서로 인덱스를 만드는 것까지는 막을 수 없다.
13.1.2 인덱스 유형
B-트리 인덱스
balanced-tree index (균형트리 인덱스)
MySQL, 오라클, SQL 서버 모두 디폴트로 B트리 인덱스를 사용한다.
명시적으로 다른 유형을 요청하지 않는 한, B트리 인덱스를 사용함.
B-트리 인덱스는 Leaf node와 하나 이상의 branch node 레벨이 있는 트리로 구성된다.
branch노드는 트리 탐색에 사용되는 반면, Leaf 노드는 실제 값과 위치 정보를 가진다.
성이 G로 시작하는 모든 고객을 검색하는 쿼리를 실행하면, 서버는 최상위 가지 노드(Root Node) 를 살펴보고 A부터 M까지로 시작하는 성을 처리하는 가지 노드의 링크를 따라 감.
이 가지 노드는 G부터 I 까지 시작하는 성을 포함하는 Leaf노드로 감.
그 후 G로 시작하지 않는 값을 만날 때까지 Leaf노드를 읽는다.
customer 테이블에서 행의 삽입, 업데이트, 삭제에 따라 루트 노드의 한 쪽에 있는 가지/잎 노드가 다른 쪽보다 훨씬 더 많아지지 않도록 트리의 균형을 유지하려 한다.
서버는 트리의 균형을 유지함으로써 여러 레벨의 가지 노드를 탐색할 필요 없이 필요한 값을 찾기 위해 Leaf 노드로 빠르게 이동할 수 있다.
비트맵 인덱스
소수의 값만 허용하는 열에 대해 생성하면 통제하기 어려울 수 있다.
예를 들어 활성 or 비활성 계좌를 빠르게 검색할 수 있도록 customer.active열에 인덱스를 생성하기로 함.
활성 1, 비활성 0만 있고, 활성 고객의 수가 훨씬 많은 만큼, 고객 수가 증가함에 따라 균형잡힌 B트리 유지가 어려울 수 있다.
다수의 행에 걸쳐 적은 수의 값만 포함하는 열의 경우 다른 인덱스가 필요하다.
이러한 상황을 더 효율적으로 처리하기 위해서 오라클에서는 비트맵 인덱스 가 포함되어 있다.
#ORACLE
CREATE BITMAP INDEX idx_active ON customer (active);인덱스는 0과 1에 대한 각두 개의 비트맵을 유지한다.
모든 비활성 고객을 검색하는 쿼리를 작성할때 DB는 0비트맵을 통해 원하는 행을 빠르게 검색.
비트맵 인덱스는 기수가 낮은 데이터에 적합한 데이터이지만, 행 수와 비교해서 열에 저장된 값의 수가 너무 많아지면, 서버가 지나치게 많은 비트맵을 유지해야하므로 사용할 수 없다.
예를 들어, 가장 높은 기수(모든 행에 대한 다른 값)을 나타내는 기본 키열에 대해서는 비트맵 인덱스를 생성하지 말아야 한다.
일반적으로 데이터 웨어하우징 환경에서 사용되며, 영업분기, 지리적 지역, 제품, 영업사원 등에 대해 생성함.
텍스트 인덱스
DB에 문서가 저장된 경우 사용자가 해당 문서에서 단어나 구문을 검색하도록 지원해야 할 수도 있다.
서버가 검색 요청이 있을 때마다 각 문서를 읽고, 원하는 텍스트를 스캔하기를 바라지는 않겠지만, 기존의 인덱스들은 이 요건에 적합하지 않다.
SQL서버와 MySQL은 풀텍스트 인덱스 를 포함하며, 오라클은 오라클 텍스트로 알려진 도구를 포함.
문서 검색은 특별한 경우에만 사용되는 만큼, 예제보다는 상황에 따라 어떤 인덱스를 사용할 수 있는 지 아는 것이 중요하다.
13.1.3 인덱스 사용 방법
인덱스는 일반적으로 특정 테이블에서 행을 빨리 찾기위해 사용됨.
그 후 서버는 사용자가 요청한 추가정보를 추출하기 위해 관련 테이블을 읽어야 함.
위와 같은 쿼리에서 서버는 다음 전략 중 하나를 사용할 수 있다.
- customer 테이블의
모든 행을 스캔 last_name 열의 인덱스를 사용해서 성이 P로 시작하는 모든 고객 찾기. 그 후 customer테이블의 각 행을 읽어 이름이 S로 시작하는 행만 찾기.last_name과 first_name열의 인덱스를 사용해서 성이 P로 시작하고, 이름이 S로 시작하는 모든 고객을 찾기
테이블을 다시 읽을 필요 없이 인덱스가 결과셋에 필요한 모든 행을 생성하므로 세 번째가 가장 좋은 옵션인 것 같다.
Explain 문을 사용해 쿼리 실행계획을 확인해보자.
EXPLAIN
SELECT customer_id, first_name, last_name
FROM customer
WHERE first_name LIKE 'S%' AND last_name LIKE 'P%';쿼리 결과를 보면
key: idx_full_name을 실제 실행에 선택되었음을 알 수 있다.
type열에서는 버무이 스캔이 사용될 것임을 알려준다.
즉, DB는 하나의 행을 검색하는 대신 인덱스에서 값의 범위를 찾는다.
13.1.4 인덱스의 단점
더 많은 인덱스가 반드시 좋은 것만은 아님.
모든 인덱스는 결국 (특수한 유형의) 테이블이기 때문.
따라서 테이블에서 행을 추가하거나 삭제할 때마다 해당 테이블의 모든 인덱스를 수정해야함.
행이 업데이트되면, 영향을 받는 모든 열의 인덱스도 수정되어야함.
인덱스가 많을수록 서버가 모든 스키마를 최신 상태로 유지하기 위해 더 많은 작업을 수행해야 하므로 속도가 느려짐.
또한 인덱스는 디스크 공간뿐만 아니라 관리자의 주의가 필요하므로, 가장 좋은 전략은 꼭 필요할 때만 인덱스를 추가하는 것.
월별 관리 루틴과 같은 특별한 목적으로 인덱스가 필요하다면, 우선 인덱스를 추가한 다음, 루틴을 실행한 뒤에 인덱스를 삭제하는 방법도 있음.
잘 모르겠다면, 다음 전략을 기본으로 사용할 것을 권장함.
- 모든 기본키 열에 인덱스가 만들어져 있는지 확인. 다중열 기본키의 경우 기본 키 서브셋 또는 모든 기본키 열에서 기본 키 제약조건 정의와 다른 순서로 추가 인덱스를 생성할 것을 고려.
- 외래 키 제약조건에서는 참조되는 모든 열에 대해 인덱스를 작성함. 서버는 부모 행이 삭제될 때 자식 행이 없는지 확인해야함. 열에 인덱스가 없다면 전체 테이블을 스캔해야 함.
- 데이터 검색에 자주 사용되는 열을 인덱싱함. 대부분의 날짜 열은 2~50자의 짧은 문자열 열과 함께 인덱스로 사용하기 좋은 후보임.
추가
인덱스의 장점은 빠른 검색 속도 향상입니다.
인덱스의 단점은
추가 저장공간이 필요하다는 점. (약 10%)
insert, update, delete 등의 변동 사항이 있는 경우 성능이 저하됨.(데이터 변경 시 인덱스도 수정되어 추가 비용이 발생하므로)
인덱스를 사용하면 좋은 경우
- 규모가 작지 않은 테이블
- INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
- JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
- 데이터의 중복도가 낮은 컬럼
13.2 제약조건
-
기본키 제약조건
- 테이블 내에서 고유성을 보장하는 열을 선택
-
외래키 제약조건
- 다른 테이블의 기본 키 열에 있는 값만 포함하도록 하나 이상의 열을 제한함
- update cascade, delete cascade 규칙이 설정되면 다른 테이블에서 허용하는 값도 제한 가능.
-
고유 제약조건
- 테이블 내에서 고유한 값을 포함하도록 하나 이상의 열을 제한함.
- 기본 키 제약조건은 특별한 유형의 고유 제약조건임. -
체크 제약조건
- 열에 허용되는 값을 제한함.
제약 조건이 없으면, 데이터베이스의 일관성이 의심스러울 수 있다.
기본키와 외래키 제약조건이 있다면, 다른 테이블에서 참조하는 데이터를 수정, 삭제를 다른 테이블에 전파하려 할 때 서버는 오류를 발생시킴.
13.2.1 제약조건 생성
제약 조건은 보통 create table 문을 통해 관련 테이블과 동시에 생성됨.
또는 외래키 제약조건 없이 테이블을 생성하고, 나중에 alter table문으로 외래 키 제약조건을 추가할 수 있음.
ALTER TABLE customer
ADD CONSTRAINT fk_customer_address FOREIGN KEY (address_id)
REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE customer
ADD CONSTRAINT fk_customer_store FOREIGN KEY (store_id)
REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE;on delete restrict : 자식 테이블에서 참조되는 부모 테이블에서 행을 삭제하면 서버에서 오류 발생.
on update cascade: 서버가 부모 테이블의 기본 키 값에 대한 변경사항을 자식 테이블로 전파.
on delete restrict 절은 부모테이블에서 행이 삭제될 때 고아 레코드가 발생하는 것을 방지.
123이 포함된 한 명의 customer 행이 있음을 보여줌.
부모 테이블에서 이 행을 삭제하면 다음과 같은 오류가 발생.
자식 테이블의 행 중 하나 이상이 address_id 열에 값 123을 포함하므로, 외래 키 제약 조건인 on delete restrict 절 때문에 오류 발생.
on update cascade 절은 또 다른 전략을 사용해서 부모 테이블에서 기본 키 값이 수정될 때 고아 레코드가 발생하는 것을 방지함.
address.address_id 열의 값을 수정하면 다음과 같은 결과가 나타남.
값 9999가 customer 테이블에 자동으로 업데이트 되었음. 이를 cascade라고 하며, 고아 행이 생기는 걸 방지하는 두번째 메커니즘임.
restrict 와 cascade와 함께 set null을 선택할 수도 있음.
행이 삭제되거나 업데이트 될때 자식테이블의 외래키 값을 null로 설정함.
다음은 외래키 제약조건을 정의할 때 선택할 수 있는 6가지 옵션임.
- on delete restrict
- on delete cascade
- on delete set null
- on update restrict
- on update cascade
- on update set null
이는 선택사항이며, 외래키 제약조건을 정의 할 때, 0,1,2개를 선택할 수 있다.
마지막으로, 기본 키 또는 외래 키 제약 조건을 삭제하려면 add 대신 drop을 지정하는 경우를 제외, 동일한 alter table 문을 다시 사용하면 됨.
학습점검
ALTER TABLE rental
ADD CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id)
REFERENCES customer (customer_id) ON DELETE RESTRICT;
CREATE INDEX idx_payment
ON payment (payment_date, amount);