1. 인덱스(Index)란?
- DB에서 인덱스는 "RDBMS에서 검색 속도를 높이기 위한 기술"이다.
- 즉, 테이블 칼럼을 색인화 하는 것을 뜻한다. 칼럼을 색인화하게 되면 DB안에 레코드를 처음부터 Full Scan하지 않고, Index 파일 검색을 통해 검색 속도가 향상된다.
- 'KEY'와 'INDEX'라는 용어는 일반적으로 같은 의미로 사용된다.
2. 인덱스를 사용하는 경우
- 인덱스를 사용하면 INSERT, SELECT, UPDATE 속도는 더 느려지게 된다. → 이유는 index를 사용하면 정렬된 상태를 유지해야 하고, 데이터 테이블 외에 인덱스 테이블에도 INSERT와 UPDATE를 해줘야 하기 때문이다.
- 따라서 인덱스는 변경보다 검색이 많은 경우에 사용하면 더 효율적이다.
3. 인덱스의 종류
- 인덱스에는 여러 종류가 있다.
◦ Primary Key(PK): 기본 키 (고유하고 null이 아님)
◦ Unique Key(UK): 고유 인덱스 (고유하고 null일 수 있음)
◦ Foreign Key(FK): 외래키 (null이거나 부모 테이블의 기본키 값과 동일) - 추가적으로 Plain Index(일반 인덱스; 반드시 고유할 필요는 없음), Full-Text Index(전체 텍스트 인덱스; 전체 텍스트 검색용)가 있다.
(1) Primary Key (PK, 기본 키)
- 고유한 값이며 null이 될 수 없다. 항상 하나의 레코드만 식별하며, 각 레코드는 표현 되어야 한다. 각 테이블은 기본 키를 하나만 가질 수 있다.
- 많은 테이블은 숫자 ID 필드를 기본 키로 사용한다. AUTO_INCREMENT 속성은 새 행에 대한 고유한 ID를 생성하는 데 사용할 수 있으며, 기본 키와 함께 일반적으로 사용된다.
- 기본 키는 일반적으로 CREATE TABLE 명령문으로 테이블을 만들 때 추가된다.
CREATE TABLE `Employees` ( `ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, `First_Name` VARCHAR(25) NOT NULL, `Last_Name` VARCHAR(25) NOT NULL, `Position` VARCHAR(25) NOT NULL, `Home_Address` VARCHAR(50) NOT NULL, `Home_Phone` VARCHAR(12) NOT NULL, PRIMARY KEY (`ID`) );
- 예시는 ID 필드에 기본 키를 만든다. ID 필드는 NOT NULL로 정의되어야 하며, 그렇지 않으면 인덱스를 만들 수 없다.
- CREATE INDEX 명령으로 기본 키를 만들 수 없다.
- 테이블이 이미 생성된 후에 기본 키를 추가하려면 ALTER TABLE을 사용한다.
ALTER TABLE Employees ADD PRIMARY KEY(ID);
(2) Unique Key (UK, 고유 인덱스)
- 고유 인덱스는 고유해야 하지만 NULL일 수 있는 열을 가질 수 있다. 따라서 각 키 값은 단 하나의 레코드만 식별하지만 모든 레코드를 표현할 필요는 없다.
- (MariaDB 10.5 부터) 인덱스 유형이 지정되지 않은 경우 Unique는 일반적으로 BTREE 인덱스이며, 옵티마이저가 행을 찾는 데 사용할 수 있다.
◦ 키가 사용된 스토리지 엔진의 최대 키 길이보다 길고, 스토리지 엔진이 긴 고유 인덱스를 지원하는 경우 → HASH 키가 생성된다.
◦ 이를 통해 MariaDB는 모든 유형 또는 열 수에 대해 고유성을 적용할 수 있다.
- 고유 인덱스 생성 예시는 다음과 같다.
CREATE TABLE `Employees` ( `ID` TINYINT(3) UNSIGNED NOT NULL, `First_Name` VARCHAR(25) NOT NULL, `Last_Name` VARCHAR(25) NOT NULL, `Position` VARCHAR(25) NOT NULL, `Home_Address` VARCHAR(50) NOT NULL, `Home_Phone` VARCHAR(12) NOT NULL, `Employee_Code` VARCHAR(25) NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY (`Employee_Code`) );
- CREATE INDEX 명령이나 ALTER TABLE 명령으로 테이블을 생성한 후에도 고유 키를 추가할 수 있다.
CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone); ALTER TABLE Employees ADD UNIQUE `EmpCode` ( `Employee_Code` );
- 인덱스는 두 개 이상의 열을 포함할 수 있다. MariaDB는 전체 인덱스를 사용할 수 없는 경우 인덱스의 가장 왼쪽에 있는 하나 이상의 열을 사용할 수 있다. (HASH 인덱스 유형 제외)
CREATE TABLE t1 (a INT NOT NULL, b INT, UNIQUE (a,b)); INSERT INTO t1 values (1,1), (2,2); SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | 1 | | 2 | 2 | +---+------+
- 인덱스는 a 와 b 두 열에 대해 고유하게 정의되어 있으므로 다음 행은 유효하다. 왜냐하면 a 와 b는 개별적으로는 고유하지 않지만 조합은 고유하기 때문이다.
INSERT INTO t1 values (2,1); SELECT * FROM t1; +---+------+ | a | b | +---+------+ | 1 | 1 | | 2 | 1 | | 2 | 2 | +---+------+
(3) Foreign Key (FK, 외래 인덱스)
- 외래키는 두 테이블을 서로 연결하는 데 사용되는 키이다.
- 외래키가 포함된 테이블을 자식 테이블이라고 하고, 외래키 값을 제공하는 테이블을 부모 테이블이라 한다.
- 외래키 값은 NULL이거나 부모 테이블의 기본키 값과 동일해야 한다. (참조: 무결성 제약조건)
- 부모 테이블의 기본키, 고유키를 외래키로 지정할 수 있다.
- 외래키로 지정할 두 테이블의 필드는 같은 데이터 타입이어야 한다.
- 외래키 생성 예
create table department( id int auto_increment primary key, name varchar(20) not null, code char(13) not null unique key ); create table employee ( id int auto_increment primary key, name varchar(20) not null, code char(13) not null unique key, dept_id int, foreign key (dept_id) references department(id) ); # CONSTRAINT [CONSTRAINT_NAME]은 생략 가능 - 외래키 생성 시 옵션 지정이 가능하다.
- 1) On Delete <옵션>
◦ Cascade : 부모 데이터 삭제 시 자식 데이터도 삭제
◦ Set null : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Null로 업데이트
◦ Set default : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트
◦ Restrict : 자식 테이블이 참조하고 있을 경우, 데이터 삭제 불가
◦ No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.
2) On Update <옵션>
◦ Cascade : 부모 데이터 업데이트 시 자식 데이터도 업데이트
◦ Set null : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Null로 업데이트
◦ Set default : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트
◦ Restrict : 자식 테이블이 참조하고 있을 경우, 업데이트 불가
◦ No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.
- 테이블 생성 후 외래키를 추가할 수 있다.
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department(id); - 외래키를 삭제할 때에는 CONSTRAINT_NAME을 알아야한다.
# 삭제하고 싶은 Foreign Key의 CONSTRAINT_NAME 확인 select * from information_schema.table_constraints where TABLE_SCHEMA = 'DB명' and TABLE_NAME = '테이블명' # Foreign Key 삭제 ALTER TABLE [Table_Name] DROP CONSTRAINT [CONSTRAINT_NAME]; # or ALTER TABLE [Table_Name] DROP FOREIGN KEY [CONSTRAINT_NAME];
- [참고] 부모 테이블의 기본키, 고유키가 여러 개의 컬럼으로 이루어져 있다면 부모가 가진 기본키, 고유키 컬럼을 원하는 개수만큼 묶어서 외래키로 지정할 수 있다.
CREATE TABLE `parent` ( `id1` INT(11) NOT NULL, `id2` INT(11) NOT NULL, `id3` INT(11) NOT NULL, `uk1` INT(11) NOT NULL, `uk2` INT(11) NOT NULL, `uk3` INT(11) NOT NULL, PRIMARY KEY (`id1`, `id2`, `id3`), UNIQUE KEY (`uk1`, `uk2`, `uk3`) ); CREATE TABLE `child` ( `id` INT(11) NOT NULL, `id1` INT(11) NOT NULL, `id2` INT(11) NOT NULL, `uk1` INT(11) NOT NULL, `uk2` INT(11) NOT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`id1`, `id2`) REFERENCES `parent` (`id1`, `id2`), FOREIGN KEY (`uk1`, `uk2`) REFERENCES `parent` (`uk1`, `uk2`) );
[참고] DBMS(DataBase Management System)는 인덱스를 어떻게 관리하고 있는가?
B+Tree 인덱스 알고리즘
- 일반적으로 사용되는 인덱스 알고리즘은 B+Tree 알고리즘이다. B+Tree 인덱스는 칼럼의 값을 변형하지 않고(사실 값의 앞부분만 잘라서 관리한다.), 원래의 값을 이용해 인덱싱하는 알고리즘이다.
Hash 인덱스 알고리즘
- 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱 하므로, 특정 문자로 시작하는 값으로 검색을 하는 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
왜 index를 생성하는데 b+tree를 사용하는가?
- 데이터에 접근하는 시간복잡도가 O(1)인 hash table이 더 효율적일 것 같지만 SELECT 질의의 조건에는 부등호(<>) 연산도 포함이 된다. hash table을 사용하게 되면 등호(=) 연산이 아닌 부등호 연산의 경우에 문제가 발생한다. 동등 연산(=)에 특화된 hash table은 데이터베이스의 자료구조로 적합하지 않다.
[참고 자료]
- MariaDB 공식 문서 https://mariadb.com/kb/en/getting-started-with-indexes/#
- 개발 Tistory1 - https://skd03052.tistory.com/228
- 개발 Tistory2 - https://bamdule.tistory.com/45
728x90
'Study & Webinar' 카테고리의 다른 글
| AI의 발전에 따른 웹 개발 영역의 변화 및 전망에 대한 고찰 (feat. ChatGPT) (4) | 2024.10.18 |
|---|---|
| 문서 작성에 자주 쓰이는 특수문자 모음 (0) | 2024.09.22 |