[필기노트] MySQL Index

Index : 자료 내에서 특정 항목을 찾기 위한 메커니즘
 - 인덱스는 일반적인 데이터 테이블과 달리 특정 순서로 유지되는 특수 테이블
 - 모든 데이터를 포함하는 대신, 행이 실제로 존재하는 위치에 대한 정보와 함께 
     데이터 테이블에서 행을 찾을 때 필요한 열만 포함
 - 인덱스의 역할 : 테이블의 모든 행을 확인할 필요 없이 테이블의 행과 열의 서브셋을 쉽게 검색하는 것 
 - 특정 테이블에서 행을 빨리 찾기 위해 사용된다. ☆
 - 디스크 저장소에 얼마나 덜 접근하게 만드는지, 인덱스 Root에서 Leaf 까지 오고가는 횟수를 줄이는 것이 핵심
 - 인덱스의 검색은 Root > Branch > Leaf > 디스크 저장소를 기준으로 검색한다.

고유 인덱스 : UNIQUE 속성

다중 열 인덱스 : 두 열을 함께 사용해서 인덱스를 만들 수 있다.

B-트리 인덱스 (균형 트리 인덱스) : MySQL, Oracle, SQL server는 모두 기본적으로 B- 트리 인덱스
 - 잎 노드 (Leaf Node)와 하나 이상의 가지 노드(Branch Node) 레벨이 있는 트리로 구성
 - 가지노드 (Branch Node) : 트리 탐색에 사용
 - 잎 노드 (Leaf Node) : 실제 값과 위치 정보를 가진다.  
 - 고객의 이름이나 성 등 다양한 값이 포함된 열을 처리하는 데 탁월
 - 소수의 값만 허용하는 열에 대해 생성하면 통제하기 어렵다.

비트맵 인덱스 : 다수의 행에 걸쳐 적은 수의 값만 포함하는 열 (기수가 낮은 데이터)의 경우 다른 인덱스가 필요
 - 열에 저장된 각 값에 대한 비트맵을 생성하는 비트맵 인덱스(Bitmap Index)가 포함되어 있다.
 - 인덱스는 두 개의 비트맵을 유지한다.
 - 하나는 0에 대한 비트맵, 하나는 1에 대한 비트맵
 - 열에 저장된 값의 수가 너무 많아지면 서버가 지나치게 많은 비트맵을 유지해야 하므로 사용할 수 없다.
 - 많은 양의 데이터가 상대적으로 적은 값을 포함하는 열에 인덱스를 생성
 ex) CREATE BITMAP INDEX idx_confirm ON Channels (confirm);

텍스트 인덱스 : MySQL, SQL서버, oracle DB는 문서에 대한 특수 인덱싱과 검색 메커니즘을 제공
 - DB에 문서가 저장 된 경우 문서에서 단어나 구문을 검색하도록 지원해야 할 수 있다.
 - 풀텍스트 인덱스 (full-text index) : SQL, MySQL에서 포함
 - 오라클 텍스트(Oracle Text) : 오라클 DB에서 알려진 강력한 도구를 포함

인덱스의 단점
 - 테이블에서 행을 추가하거나 삭제할 때마다 해당 테이블의 모든 인덱스를 수정해야한다.
 - 필요할 때만 인덱스를 추가하는게 중요하다.

인덱스 관리 전략
 - 모든 Primary key에 인덱스에 만들어져 있는지 확인한다
 - 다중 열 Primary key의 경우 기본 키 열의 서브셋 또는 모든 기본 키 열에서 제약 조건 정의와 다른 순서로 생성할 수 있음
 - 외래 키 제약조건에서 참조되는 모든 열에 대해서 인덱스를 작성해야한다.
  - 서버는 부모 행이 삭제될 때 자식 행이 없는지 확인해야한다.
  - 열에 인덱스가 없다면 전체 테이블을 스캔해야 한다.
 - 데이터 검색에 자주 사용되는 열을 인덱싱한다
  - 대부분의 날짜 열은 2~50자의 짧은 문자열 열과 함께 인덱스로 사용하기 좋은 후보




-- 제약 조건 : 테이블의 하나 이상의 열에 적용되는 제한사항
기본키 제약조건 
 - 테이블 내에서 고유성을 보장하는 열을 식별

외래키 제약조건
 - 다른 테이블의 기본 키 열에 있는 값만 포함하도록 하나 이상의 열을 제한한다
 - update cascade 또는 delete cascade 규칙이 설정되면 다른 테이블에서 허용하는 값도 제한할 수 있다.

고유 제약조건
 - 테이블 내에서 고유한 값을 포함하도록 하나 이상의 열을 제약한다
 - 기본 키 제약 조건은 특별한 유형의 고유 제약조건

체크 제약조건
 - 열에 허용되는 값을 제한한다.

 

 

-------- Code

 

SHOW INDEX FROM Indexs
 - Indexs 테이블의 모든 인덱스를 조회한다.

ALTER TABLE Indexs ADD INDEX idx_data (data);
 - Indexs 테이블의 idx_data 인덱스를 생성한다.

ALTER TABLE Indexs DROP INDEX idx_data;
 - Indexs 테이블의 idx_data 인덱스를 삭제한다.

EXPLAIN 
    SELECT customer_id, first_name, last_name
    FROM customer
    WHERE first_name LIKE 'S%' AND last_name LIKE 'P%';
 - EXPLAIN을 이용해 쿼리 옵티마이저가 쿼리 실행을 결정하는 방법을 확인,
 - 쿼리에 대한 실행 계획을 확인한다.




CREATE TABLE Indexs(
indexId INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(255)
);
ALTER TABLE Indexs ADD INDEX idx_data (data);

= 같음

CREATE TABLE Indexs(
indexId INT(11) NOT NULL AUTO_INCREMENT,
data VARCHAR(255),
PRIMARY KEY (indexId),
KEY idx_data (data)
);



CREATE TABLE Users(
userId INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
nickname VARCHAR(255),
tag VARCHAR(255),
PRIMARY KEY (userId),
KEY idx_name (name)
);
ALTER TABLE Users ADD INDEX idx_full_name (name, nickname);



13-1 실습
ALTER TABLE customer
ADD INDEX idx_rental_customer_id (customer_id);