[SQL 첫걸음] 29강. 인덱스 작성과 삭제

인덱스 작성과 삭제

• 인덱스(Index)는 DDL(Data Definition Language)을 사용하여 작성하거나 삭제합니다.
• 사실 표준 SQL에서는 인덱스 자체가 데이터베이스 제품에 의존하는 선택적인 항목으로 취급되어 CREATE INDEX 명령이 존재하지 않습니다.
• 하지만 대부분의 유명한 데이터베이스 제품에는 인덱스 구조가 도입되어 있고 비슷한 방법으로 이를 관리할 수 있습니다.

---

인덱스 작성

CREATE INDEX 명령으로 인덱스를 만들 수 있습니다.

• 이때 인덱스에 이름을 붙여 관리하는데, 인덱스가 데이터베이스 객체가 될지 아니면 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다릅니다.
• Oracle, DB2 등에서 인덱스는 스키마 객체가 됩니다. 따라서 스키마 내에서 이름이 중복되지 않게 관리합니다.
• 반대로 SQL Server, MySQL에서 인덱스는 테이블 내의 객체가 됩니다. 따라서 테이블 내에서 이름이 중복되지 않게 관리합니다.
• MySQL에서 테이블 sample62의 no열에 isample62라는 인덱스를 지정하는 방법은 아래와 같습니다. 이때 인덱스를 확인하려면 SHOW INDEX FROM 테이블명과 같은 형태의 명령문을 사용합니다.

mysql > SELECT * FROM sample62;

+----+---------------------+------------------------+------+
| no | created_at          | nickname               | name |
+----+---------------------+------------------------+------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |
+----+---------------------+------------------------+------+
1 row in set (0.00 sec)

mysql > DESC sample62;

+------------+------------+------+-----+-------------------+-------------------+
| Field      | Type       | Null | Key | Default           | Extra             |
+------------+------------+------+-----+-------------------+-------------------+
| no         | int        | NO   |     | NULL              |                   |
| created_at | datetime   | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nickname   | varchar(8) | YES  |     | NULL              |                   |
| name       | varchar(4) | NO   |     | NULL              |                   |
+------------+------------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)

mysql > CREATE INDEX isample62 ON sample62(no);

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > SHOW INDEX FROM sample62;

+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sample62 |          1 | isample62 |            1 | no          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

• 인덱스를 작성할 때는 저장장치에 색인용 데이터가 만들어집니다.
• 테이블 크기에 따라 인덱스 작성시간도 달라지기 때문에 행이 대량으로 존재할 경우 시간과 저장공간 모두 많이 소비됩니다.

---

인덱스 삭제

• DROP INDEX 명령에 인덱스 이름을 지정하여 인덱스를 삭제할 수 있습니다.
• 이때 SQL Server, MySQL처럼 인덱스가 테이블 내의 객체로 존재할 경우 테이블 이름도 지정해야 합니다.
• 앞서 만든 인덱스 isample65를 삭제하는 방법은 아래와 같습니다.

mysql > DROP INDEX isample62 ON sample62;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql > SHOW INDEX FROM sample62;

Empty set (0.00 sec)

• 앞서 인덱스의 역할에 대해 살펴봤던 것처럼 인덱스를 통해 검색(SELECT) 속도를 향상시킬 수 있습니다.
• 이때 WHERE 구의 조건으로 인덱스의 열을 사용하면 됩니다.
• 그러나 INSERT 명령을 사용할 때는 결국 기존 테이블 뿐만 아니라 인덱스 객체에도 데이터를 추가해야 하기 때문에 기존보다 시간이 더 걸립니다.
• 인덱스 isample62를 테이블 sample62의 nickname에 지정했다고 가정해봅시다.
• 인덱스를 통해 검색(SELECT)을 하는 방법은 아래와 같이 단순합니다.
• 인덱스로 지정한 열을 WHERE 구의 조건으로 지정하면 됩니다.

mysql > SELECT * FROM sample62 WHERE nickname = '테스트용 닉네임';

+----+---------------------+------------------------+------+------+-------------+
| no | created_at          | nickname               | name | test | test_number |
+----+---------------------+------------------------+------+------+-------------+
|  1 | 2021-12-08 19:42:07 | 테스트용 닉네임        |      |    0 |        NULL |
+----+---------------------+------------------------+------+------+-------------+
1 row in set (0.00 sec)

---

EXPLAIN

• 인덱스를 사용하면 검색 속도가 향상되는데 실제로 입력한 명령문이 인덱스를 사용하는지 확인하려면 EXPLAIN 명령을 사용하면 됩니다.
• EXPLAIN 명령을 사용하여 인덱스로 지정했던 열인 nickname을 사용하는 경우와 그렇지 않은 no 열을 사용하여 차이를 살펴보겠습니다.

mysql > EXPLAIN SELECT * FROM sample62 WHERE nickname = '테스트용 닉네임';

+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sample62 | NULL       | ref  | isample62     | isample62 | 27      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql > EXPLAIN SELECT * FROM sample62 WHERE no = 1;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sample62 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN 명령은 표준 SQL에는 존재하지 않는 데이터베이스 제품 의존형 명령입니다.
하지만 다른 데이터베이스 제품이라도 비슷한 명령을 지원합니다.

• 예를 들어 PostgreSQL은 똑같은 EXPLAIN 명령을, Oracle에서는 EXPLAIN PLAN 명령을 사용합니다.