mysql> explain select * from article where article_id = 167598086197714944;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | article | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
-> 자동으로 clustered Index 생성 (Primary Key를 이용한 조회)
인덱스의 종류
1. clustered Index (=Primary Key 인덱스, 클러스터형 인덱스)
clustered Index
InnoDB에서 기본으로 제공하는 인덱스 (Table당 1개만 생성)
Primary Key 기준으로 정렬된 인덱스
Leaf Node에 실제 데이터 row가 포함됨 → 빠른 데이터 접근이 가능합니다.
테이블 데이터는 PK 순으로 저장되기 때문에 PRIMARY KEY 값이 변경되면 그 레코드의 물리적인 저장 위치가 바뀌어야 합니다.
테이블의 데이터가 정렬되어 있기에 속도면에서 우수한 성능을 보이지만, 데이터의 추가/수정/삭제 시 레코드를 매번 정렬해야 하기 때문에 추가/수정/삭제의 성능이 저하됩니다.
2. Non-clustered Index (Secondary Index = , 보조 인덱스)
non-clustered Index
Table 당 여러 개 생성이 가능합니다.
Leaf Node에 실제 row가 없습니다.
인덱스의 key 값과 데이터 행을 가리키는 포인터가 존재합니다. (인덱스 Key 값 + Primary Key 컬럼 값)
특정 데이터를 access 하기 위해서는 secondary Index의 key 컬럼에서 Primary Key 값을 access 하고, 해당 값으로 Primary Key를 검색하여 원하는 데이터를 찾아가는 형태로 처리됩니다. (clustered Index와는 다르게 tree를 2번 타야 합니다.)
테이블 데이터가 대용량으로 변경될 경우 secondary Index를 재구성하기 위해 많은 자원이 사용됩니다.
물리적으로 레코드를 정렬하지 않기 때문에 clustered index보다 속도면에서는 성능이 떨어지지만, 추가/수정/삭제의 성능은 뛰어납니다.
unique 제약조건을 설정한 컬럼에 대해 자동으로 Non-clustered Index를 생성합니다.
3. id값을 주로 Primary Key로 두는 이유
각각의 테이블마다 PK로 id값을 가지고 있다 보니 일관성이 생깁니다. (다른 컬럼이 PK라면, 각각의 테이블의 PK를 알아내기 위해 테이블을 확인할 필요가 있습니다.)
MySQL에서 PK를 설정하면 해당 값을 Index로 잡아 데이터를 B-tree 구조로 저장합니다.
정수형 id는 정렬/저장에 최적화되어 있어서 검색, 범위조회, JOIN 모두 빠르게 동작합니다.
id 컬럼이 자동 증가하는 순서로 삽입되면, 트리의 균형을 유지하는데 있어서 추가적인 비용이 줄어들 수 있습니다. (O(logN) 보장)
