테이블에 대량의 레코드가 저장되어 있다면, int 타입 컬럼에 인덱스를 추가하는 것은 쿼리 실행 속도를 크게 향상시키는 효과적인 방법입니다. 반대로 레코드 수가 적은 소규모 테이블이라면 인덱스가 오히려 불필요한 오버헤드만 발생시킬 수 있으므로 사용을 권장하지 않습니다.
인덱스가 필요한 이유
인덱스는 마치 책의 목차와 같은 역할을 합니다. 인덱스가 없으면 MySQL은 조건에 맞는 데이터를 찾기 위해 테이블의 모든 행을 처음부터 끝까지 스캔해야 합니다(풀 테이블 스캔). 하지만 인덱스가 있으면 B-Tree 구조를 통해 원하는 데이터에 훨씬 빠르게 접근할 수 있습니다.
예제 테이블 생성하기
개념을 이해하기 위해 실습용 테이블을 먼저 만들어 보겠습니다. 아래 쿼리처럼 테이블 생성 시 INDEX(UserId) 구문을 함께 작성하면 UserId 컬럼에 인덱스가 자동으로 생성됩니다.
mysql> create table indexOnIntColumnDemo
-> (
-> UserId int,
-> UserName varchar(20),
-> UserAge int,
-> INDEX(UserId)
-> );
Query OK, 0 rows affected (0.85 sec)테이블 구조 확인
생성된 테이블의 구조를 확인해 보겠습니다.
mysql> desc indexOnIntColumnDemo;
실행 결과입니다. UserId 컬럼의 Key 항목이 MUL로 표시되어 있는데, 이는 해당 컬럼에 인덱스가 설정되었다는 의미입니다.
+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | UserId | int(11) | YES | MUL | NULL | | | UserName | varchar(20) | YES | | NULL | | | UserAge | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.04 sec)
샘플 데이터 삽입하기
이제 INSERT 명령어를 사용해 테이블에 몇 개의 레코드를 삽입합니다.
mysql> insert into indexOnIntColumnDemo values(1001,'John',23); Query OK, 1 row affected (0.22 sec) mysql> insert into indexOnIntColumnDemo values(1002,'Sam',25); Query OK, 1 row affected (0.14 sec) mysql> insert into indexOnIntColumnDemo values(1003,'Carol',22); Query OK, 1 row affected (0.24 sec) mysql> insert into indexOnIntColumnDemo values(1004,'Mike',26); Query OK, 1 row affected (0.14 sec)
전체 데이터 조회
SELECT 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select *from indexOnIntColumnDemo;
+--------+----------+---------+ | UserId | UserName | UserAge | +--------+----------+---------+ | 1001 | John | 23 | | 1002 | Sam | 25 | | 1003 | Carol | 22 | | 1004 | Mike | 26 | +--------+----------+---------+ 4 rows in set (0.00 sec)
인덱스 컬럼을 활용한 빠른 조회
조회 속도를 높이려면 WHERE 절에서 인덱스가 걸린 int 타입 컬럼(UserId)을 조건으로 사용하면 됩니다.
mysql> select *from indexOnIntColumnDemo where UserId=1004;
실행 결과는 다음과 같습니다.
+--------+----------+---------+ | UserId | UserName | UserAge | +--------+----------+---------+ | 1004 | Mike | 26 | +--------+----------+---------+ 1 row in set (0.00 sec)
추가 팁: EXPLAIN으로 인덱스 동작 확인하기
쿼리가 실제로 인덱스를 활용하고 있는지 확인하려면 SELECT 앞에 EXPLAIN 키워드를 붙여 실행 계획을 살펴볼 수 있습니다.
mysql> explain select *from indexOnIntColumnDemo where UserId=1004;
결과에서 key 항목에 UserId 인덱스 이름이 표시되고 type이 ref로 나타난다면, 인덱스가 정상적으로 사용되고 있는 것입니다. 만약 type이 ALL(풀 테이블 스캔)로 표시된다면 인덱스가 활용되지 않으므로 쿼리나 인덱스 설계를 다시 검토할 필요가 있습니다.
정리
- 레코드가 많은 테이블의 int 컬럼에는 인덱스를 추가하면 조회 성능이 크게 향상됩니다.
- 레코드가 매우 적은 테이블에는 인덱스가 오히려 비효율적일 수 있습니다.
- WHERE 절에서 인덱스가 설정된 컬럼을 조건으로 사용해야 인덱스의 효과를 얻을 수 있습니다.
- EXPLAIN 명령으로 인덱스 사용 여부를 항상 검증하는 습관을 들이면 좋습니다.