MySQL 8.0 버전에서는 대부분의 인덱스 변경 작업이 온라인 DDL로 처리 가능하도록 개선됐다.
인덱스 추가
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| ALTER TABLE employees ADD PRIMARY KEY (emp_no),
ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE employees ADD UNIQUE INDEX ux_empno (emp_no),
ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE employees ADD INDEX ix_lastname (last_name),
ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE employees ADD FULLTEXT INDEX fx_firstname_lastname (first_name, last_name),
ALGORITHM=INPLACE, LOCK=SHARED;
ALTER TABLE employees ADD SPATIAL INDEX fx_loc (last_location),
ALGORITHM=INPLACE, LOCK=SHARED;
|
- 전문 검색 인덱스와 공간 검색 인덱스는
INPLACE 알고리즘으로 인덱스 생성이 가능하지만 SHARED 잠금이 필요하다. - B-Tree 자료 구조를 사용하는 인덱스의 추가는 프라이머리 키라고 하더라도
INPLACE 알고리즘에 잠금 없이 온라인으로 생성이 가능하다.
인덱스 조회
인덱스의 목록을 조회할 때는 SHOW INDEXES 명령을 사용하거나 SHOW CREATE TABLE 명령으로 표시되는 테이블의 생성 명령을 참조한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| SHOW INDEX FROM employees;
/*
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 297022 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 0 | ux_empno | 1 | emp_no | A | 280034 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_hiredate | 1 | hire_date | A | 5332 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_gender_birthdate | 1 | gender | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_gender_birthdate | 2 | birth_date | A | 10970 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_firstname | 1 | first_name | A | 1619 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_lastname | 1 | last_name | A | 1681 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | fx_firstname_lastname | 1 | first_name | NULL | 280034 | NULL | NULL | | FULLTEXT | | | YES | NULL |
| employees | 1 | fx_firstname_lastname | 2 | last_name | NULL | 280034 | NULL | NULL | | FULLTEXT | | | YES | NULL |
+-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
*/
|
SHOW INDEXES 명령은 테이블의 인덱스만 인덱스 컬럼별로 한 줄씩 표시한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| SHOW CREATE TABLE employees;
/*
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`last_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`gender` enum('M','F') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`),
UNIQUE KEY `ux_empno` (`emp_no`),
KEY `ix_hiredate` (`hire_date`),
KEY `ix_gender_birthdate` (`gender`,`birth_date`),
KEY `ix_firstname` (`first_name`),
KEY `ix_lastname` (`last_name`),
FULLTEXT KEY `fx_firstname_lastname` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci STATS_PERSISTENT=0 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
|
인덱스 이름 변경
쿼리에서 인덱스의 이름을 힌트로 사용하면 MySQL 서버에서 해당 인덱스를 삭제하거나 다른 인덱스로 대체하는 경우 응용프로그램의 코드를 변경해야 했다.
인덱스를 삭제하고 동일 이름으로 새로운 인덱스를 생성하면 되지만 새로운 인덱스를 생성하는 동안 필요한 인덱스가 없어지므로 손쉽게 작업하기가 어려웠다.
이는 인덱스의 이름만 변경할 수 있다면 새로운 인덱스로 기존 인덱스를 대체할 수 있는데, MySQL 5.7 버전부터는 인덱스의 이름을 변경할 수 있게 되었다.
1
2
| ALTER TABLE salaries RENAME INDEX ix_salary TO ix_salary2,
ALGORITHM=INPLACE, LOCK=NONE;
|
- 인덱스의 이름을 변경하는 작업은
INPLACE 알고리즘을 사용하지만 실제 테이블 리빌드를 필요로 하지 않는다.- 응용 프로그램에서 힌트로 해당 인덱스의 이름을 사용 중이라고 하더라도 짧은 시간 안에 교체가 가능하다.
1
2
3
4
5
6
7
8
9
10
| /* 새로운 인덱스 생성 */
ALTER TABLE employees
ADD INDEX index_new (first_name, last_name),
ALGORITHM=INPLACE, LOCK=NONE;
/* 기존 인덱스 삭제 및 새로운 인덱스의 이름을 변경 */
ALTER TABLE employees
DROP INDEX ix_firstname,
RENAME INDEX index_new TO ix_firstname,
ALGORITHM=INPLACE, LOCK=NONE;
|
인덱스 가시성 변경
MySQL 서버에서 인덱스를 삭제하는 작업은 ALTER TABLE DROP INDEX 명령으로 즉시 완료된다.
하지만 한 번 삭제된 인덱스를 새로 생성하는 것은 매우 많은 시간이 걸릴 수도 있다.
- 최악의 경우 응용 프로그램의 서비스를 멈추고, 인덱스를 다시 생성하고 응용 프로그램을 다시 시작해야 한다.
- 인덱스나 테이블을 삭제하는 작업은 부담스러운 작업이므로 한 번 생성된 인덱스는 삭제하지 못하는 경우가 많다.
MySQL 8.0 버전부터는 인덱스의 가시성을 제어할 수 있는 기능이 도입되었으며, 이로 인해 쿼리를 실행할 때 해당 인덱스를 사용할 수 있는지를 결정할 수 있게 되었다.
1
| ALTER TABLE employees ALTER INDEX ix_firstname [INVISIBLE | VISIBLE];
|
인덱스가 INVISIBLE 상태로 변경되는 옵티마이저는 해당 인덱스가 없는 것으로 간주하고 실행 계획을 수립한다.
- 해당 명령은 메타데이터만 변경하기 때문에 온라인 DDL로 실행되는지 여부를 고려하지 않아도 된다.
- 인덱스를 삭제하기 전 먼저 해당 인덱스를 비활성하여 상황을 모니터링한 후 안전하게 인덱스를 삭제할 수 있게 됐다.
최초 인덱스를 생성할 때도 가시성을 설정할 수 있다.
1
2
3
4
5
6
7
8
| SHOW CREATE TABLE employees \G
/*
CREAET TABLE employees \G (
...
KEY 'ix_firstname_lastname' ('first_name', 'last_name') /* !80000 INVISIBLE */
) ENGINE=InnoDB
*/
|
비슷한 컬럼으로 구성된 인덱스가 많아지면 MySQL 옵티마이저가 다른 인덱스를 선택하여 실행계획이 바뀌고, 이로인해 성능이 악화될 수도 있다.
이러한 문제가 우려되는 경우 처음 INVISIBLE로 생성 후 부하가 낮을 때 VISIBLE로 변경하여 모니터링하고 적용 여부를 결정 및 성능이 떨어지는 원인을 분석 할 수 있다.
인덱스 삭제
1
2
3
| ALTER TABLE employees DROP PRIMARY, ALGORITHM=COPY, LOCK=SHARED;
ALTER TABLE employees DROP INDEX ux_empno, ALGORITHM=COPY, LOCK=NONE;
ALTER TABLE employees DROP fx_loc, ALGORITHM=INPLACE, LOCK=NONE;
|
MySQL 서버의 인덱스 삭제는 일반적으로 매우 빨리 처리된다.
- 세컨더리 인덱스 삭제 작업은
INPLACE 알고리즘을 사용하지만 테이블 리빌딩은 발생하지 않는다. - 프라이머리 키의 삭제 작업은 모든 세컨더리 인덱스의 리프 노드에 저장된 프라이머리 키 값을 삭제해야 한다.
- 임시 테이블로 레코드를 복사해서 테이블을 재구성 해야한다.
