MySQL 테이블에 새 열과 인덱스를 동시에 추가하는 방법
MySQL에서 기존 테이블에 새로운 열(column)을 추가하면서 동시에 해당 열에 대한 인덱스까지 생성해야 하는 경우가 종종 있습니다. 이럴 때는 ALTER TABLE 명령어 하나만으로 두 작업을 한 번에 처리할 수 있습니다.
기본 문법
ALTER TABLE yourTableName
ADD COLUMN yourColumnName dataType,
ADD INDEX(yourColumnName);
테이블 이름 뒤에 ADD COLUMN 절로 새 열을 정의하고, 이어서 ADD INDEX 절을 붙이면 해당 열에 인덱스가 자동으로 생성됩니다.
예제 테이블 생성
문법을 실제로 이해하기 위해 간단한 예제 테이블을 먼저 만들어 보겠습니다.
mysql> create table AddColumnAndIndexDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name varchar(100),
-> Address varchar(200)
-> );
Query OK, 0 rows affected (0.81 sec)
현재 테이블 구조 확인
DESC 명령어로 현재 테이블의 구조를 확인합니다.
mysql> desc AddColumnAndIndexDemo;
실행 결과는 다음과 같습니다.
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | YES | | NULL | |
| Address | varchar(200) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)
현재는 Id, Name, Address 세 개의 열만 존재하며, 인덱스는 기본 키(PRI)인 Id뿐입니다.
새 열 추가 및 인덱스 생성
이제 Age라는 int 타입의 새 열을 추가하면서 동시에 인덱스도 생성해 보겠습니다.
mysql> alter table AddColumnAndIndexDemo add column Age int, add index(Age);
Query OK, 0 rows affected (1.81 sec)
Records: 0 Duplicates: 0 Warnings: 0
변경 결과 확인
테이블 구조를 다시 조회하면 Age 열이 추가되고 Key 항목에 MUL이 표시된 것을 볼 수 있습니다.
mysql> desc AddColumnAndIndexDemo;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | YES | | NULL | |
| Address | varchar(200) | YES | | NULL | |
| Age | int(11) | YES | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MUL은 해당 열이 일반(비고유) 인덱스의 첫 번째 열임을 의미합니다. 즉, Age 열에 인덱스가 성공적으로 생성된 것입니다.
생성된 인덱스 목록 확인
SHOW INDEX 명령어로 테이블에 생성된 모든 인덱스를 확인할 수 있습니다.
mysql> show index from AddColumnAndIndexDemo;
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| addcolumnandindexdemo | 0 | PRIMARY | 1 | Id | A | 0 | NULL | NULL | | BTREE | | | YES |
| addcolumnandindexdemo | 1 | Age | 1 | Age | A | 0 | NULL | NULL | YES | BTREE | | | YES |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.16 sec)
결과를 보면 기본 키(PRIMARY) 외에 Key_name이 Age인 인덱스가 추가로 생성된 것을 확인할 수 있습니다. Non_unique 값이 1이므로 중복 값을 허용하는 일반 인덱스입니다.
참고 사항
- 인덱스 이름을 직접 지정하고 싶다면 ADD INDEX idx_age(Age)처럼 인덱스명을 명시하면 됩니다.
- 인덱스 이름을 생략하면 대상 열의 이름이 그대로 인덱스 이름으로 사용됩니다.
- 대용량 테이블에서는 ALTER TABLE 작업 중 잠금이 발생할 수 있으므로 트래픽이 적은 시간대에 실행하는 것이 좋습니다.