인덱스 작성과 삭제
-
인덱스(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명령을 사용합니다.
