.png&blockId=18f00b21-a9bc-4e35-aeb8-ecb07bddb89a)
DBMS 의 인덱스 (MYSQL 8.0 DBMS 기준)
현업에서 사용하는 DBMS는 다양하고, DBMS마다 인덱스 파일 구조 & 자료구조가 조금씩 다르다.
레코드
•
특정 데이터를 식별하기 위한 작은 단위
•
레코드가 모여 하나의 페이지를 형성하고, 페이지가 모여 데이터 파일이 된다.
•
DBMS의 인덱스는 레코드의 키값을 기준으로 항상 정렬되어 있다. (레코드를 추가할 때마다 데이터 파일의 레코드를 정렬한다.)
•
> 데이터가 많아질수록 삽입 성능이 좋지 않지만, 원하는 레코드를 빠르게 탐색할 수 있다는 장점이 있다.
클러스터 인덱스(Clustered Index)
MYSQL에도 MyISAM, NDB, Memory, InnoDB 같은 다양한 스토리지 엔진이 있다. InnoDB 기준으로 설명한다.
•
클러스터 인덱스는 InnoDB 엔진에서만 제공되는 인덱스로 Oracle DBMS에서는 조직된 인덱스 테이블(index-organized table)로 불린다.
•
클러스터 인덱스는 키의 값을 기준으로 레코드의 위치가 결정되는 인덱스이다.
•
실제 물리적인 정렬 순서와 인덱스의 정렬 순서와 같은 B+트리 구조이다.
B+트리 구조 : 책의 목차를 보고 원하는 데이터의 위치가 들어있는 범위를 찾아가서 탐색 (테이블 자체가 인덱스)
B- 트리 구조 : 책 제일 뒤에 Appendix 색인을 보도 필요한 단어를 찾아, 페이지로 이동 후 탐색 (테이블 자체는 정렬되어있지 않고 인덱스 페이지에만 특정한 기준으로 정렬)
•
루트 노드(최 상단 노드)는 자식 페이지에 대한 포인터(위치)만 갖고 있다.
•
리프 노드(최 하단 노드)는 클러스터 키(레코드를 식별할 수 있는 속성)와 데이터를 갖고 있다.
•
위와 같은 구조라고 가정할때, 만약 키값이 24인 데이터를 찾는다면, 먼저 24는 13보다 크고 30보다 작으므로, 13~30 키가 저장되어있는 왼쪽 페이지로 가서, 키 값을 검색하게 된다.
반대로, id가 40인 데이터를 찾는다면, 40이 30보다 크므로, 오른쪽 페이지로 가서 검색을 수행
정렬 기준(기본키가 없을 때)
1.
기본키가 정의되어 있지 않고 NOT NULL 제약조건을 가진 유니크키가 존재하는 경우, 첫 번째 유니크키를 사용한다.
2.
기본키, 유니크키 둘다 없는 경우, Auto0Increment 처럼 내부적으로 자동 증가 필드를 만들어서 사용
예시
기본키가 없는 테이블의 상태, id를 역순으로 데이터 저장
id 에 기본키(Primary Key) 속성을 부여했을 뿐인데, id를 기준으로 정렬이 되었다.
이제부터, 입력되는 데이터들은 기본키인 id 를 기준으로 정렬 순서에 맞게 입력될 것.
40만건의 데이터가 저장되어있는 경우, 특정 데이터를 검색했을 때, 인덱스가 없는 경우 0.219초, 인덱스가 있는 경우 0.000초
보조 인덱스
•
클러스터 인덱스를 제외한 나머지 인덱스
•
보조 인덱스로 지정된 속성의 데이터를 찾는 경우, 보조 인덱스를 통해서 검색하려는 데이터의 위치(클러스터 키)를 알 수 있고, 그 값으로 접근한다.
◦
> 만약 보조 인덱스가 없었다면, 클러스터 인덱스는 회원 번호를 기준으로 정렬되어 구분 되어있기 때문에, 인덱스를 통한 검색 자체가 불가능함
유니크 인덱스 지정
CREATE TABLE MEMBER(
...
UNIQUE INDEX 인덱스_이름 (칼럼1, 칼럼2, 칼럼3)
...
)
Plain Text
복사
CREATE TABLE MEMBER(
...
칼럼1 VARCHAR(32) NOT NULL UNIQUE
...
)
Plain Text
복사
CREATE UNIQUE INDEX 인덱스_이름 ON 테이블_이름 (칼럼1, 칼럼2, ...)
Plain Text
복사
ALTER TABLE 테이블_이름 ADD UNIQUE KEY (칼럼1, 칼럼2, ...)
Plain Text
복사
•
> NOT NULL 조건의 UNIQUE 조건은 UNIQUE KEY 와 동일한 의미
•
유니크 인덱스도 B+트리 구조를 갖는다.
외래키 인덱스 지정
CREATE TABLE MEMBER (
...
FOREIGN KEY (칼럼1) REFERENCES 참조할_테이블(참조할_칼럼)
...
)
Plain Text
복사
ALTER TABLE 테이블 이름 ADD FOREIGN KEY (외래키) REFERENCES 참조할_테이블 (참조할_칼럼)
Plain Text
복사
•
외래키 인덱스도 B+트리 구조를 갖는다.
칼럼 인덱스
•
중복된 속성의 값을 가질 수 있고, 최대 16개의 복수의 칼럼을 인덱스로 지정할 수 있으나, 인덱스 파일 크기가 커지므로 좋은 설계는 아니다.
•
그냥 수식어 없이 INDEX 로 추가해주면 됨.
CREATE TABLE MEMBER (
...
INDEX 인덱스_이름 (칼럼1, 칼럼2, 칼럼3) # 복수의 인덱스
...
)
Plain Text
복사
CREATE INDEX 인덱스_이름 ON (칼럼1, 칼럼2, 칼럼3, 칼럼4, 칼럼5, 칼럼6, 칼럼7, 칼럼8, 칼럼9, ...) # 최대 16개 지정 가능
Plain Text
복사
ALTER TABLE 테이블_이름 ADD INDEX 인덱스_이름 (칼럼1, 칼럼2, ...);
Plain Text
복사
주의점
Case.1
ALTER TABLE MEMBER ADD INDEX `gender_name` (M_GENDER, M_NAME);
Plain Text
복사
Case.2
ALTER TABLE MEMBER ADD INDEX `member_gender`(M_GENDER);
ALTER TABLE MEMBER ADD INDEX `member_name` (M_NAME);
Plain Text
복사
각 결과가 Case.1의 경우 오른쪽, Case.2의 경우 왼쪽으로 다른 결과로 인덱싱하게 된다.
Case.1
ALTER TABLE MEMBER ADD INDEX `gender_name` (M_GENDER, M_NAME);
Plain Text
복사
Case.2
ALTER TABLE MEMBER ADD INDEX `name_gender` (M_NAME, M_GENDER);
Plain Text
복사
복수의 칼럼을 묶어서 인덱싱하는 경우도 순서에 따라서 정렬되는 결과가 달라진다.
인덱스 제거
ALTER TABLE 테이블_이름 DROP INDEX 인덱스_이름;
Plain Text
복사
제거하고 싶은 인덱스 이름을 입력해서 DROP INDEX 하면 된다.
인덱스 조회
SHOW INDEX FROM 테이블_이름;
Plain Text
복사
mysql> SHOW INDEX FROM MEMBER;
+--------+------------+----------+--------------+-------------+-----------+-------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality
+--------+------------+----------+--------------+-------------+-----------+-------------
| MEMBER | 0 | PRIMARY | 1 | M_ID | A | 2352438 |
| MEMBER | 1 | birth | 1 | M_BIRTH | A | 17 |
+--------+------------+----------+--------------+-------------+-----------+-------------
| Sub_part | Packed | Null | Index_type| Comment| Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------
| NULL | NULL | | BTREE | | | YES | NULL |
| NULL | NULL | YES | BTREE | | | YES | NULL |
+--------+------------+----------+--------------+-------------+-----------+-------------
Plain Text
복사
•
> 유니크 여부(Non_unique), 인덱스 이름(Key_name), 인덱싱이 적용된 칼럼 이름(Column_name) 등등 다양한 정보 조회 가능
