[SQL] 인덱스 작성 & 삭제

인덱스

CREATE INDEX
DROP INDEX

• 인덱스는 데이터베이스 객체의 하나로 DDL을 사용하여 작성하거나 삭제한다.
  👉 표준 SQL 명령은 없지만 대표적인 데이터베이스 제품에는 인덱스 구조가 도입되어 있으며 비슷한 관리 방법으로 인덱스를 다룰 수 있다.

인덱스 작성

CREATE INDEX 인덱스명 ON 테이블명(열명1, 열명2, ...)

# sample62 테이블의 no 열에 isample65 인덱스 지정
mysql> CREATE INDEX isample65 ON sample62(no);
Query OK, 0 rows affected (0.01 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 | isample65 |            1 | no          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

• 인덱스에 이름을 붙여 관리하는데 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 제품에 따라 다르다.
  👉 SQL server나 MySQL에서는 테이블 내의 객체가 되므로 테이블 내 이름이 중복되지 않도록 지정해야 한다.
• 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정해야 한다.
  👉 복수로도 지정할 수 있다.
• 인덱스 작성시 색인용 데이터가 생성되고 테이블 크기에 따라 작성 시간도 달라진다.
  👉 행이 대량으로 존재하면 시간도, 저장공간도 많이 소비된다.

인덱스 삭제

# 스키마 객체의 경우
DROP INDEX 인덱스명

# 테이블 내 객체의 경우
DROP 인덱스명 ON 테이블명

mysql> DROP INDEX isample65 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)

• 인덱스만 삭제하는 경우 DROP INDEX를 사용한다.
  👉 인덱스는 테이블에 의존하는 객체이므로 테이블을 삭제하면 인덱스도 자동으로 삭제된다.
• 인덱스의 열을 WHERE 구로 조건을 지정하여 SELECT 명령으로 검색하면 처리속도가 향상된다.
  !but 모든 SELECT 명령에 적용되는 인덱스는 작성할 수 없다.
• INSERT 명령의 경우 인덱스를 최신 상태로 갱신하는 처리가 늘어나므로 처리속도가 떨어진다.

EXPLAIN

EXPLAIN SQL명령

mysql> EXPLAIN SELECT * FROM sample62 WHERE a = 'a'; 
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sample62 | NULL       | ref  | isample65     | isample65 | 93      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

# 조건식에 a 열이 사용되지 않으면 인덱스를 사용할 수 없다.
mysql> EXPLAIN SELECT * FROM sample62 WHERE c = 'a';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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.01 sec)

• 인덱스를 사용하여 검색하는지를 확인할 때 사용한다.
• SQL 명령은 실제로 실행되지는 않지만 MySQL의 경우 실제로 실행되는 경우도 있다.
• possible_keys에 사용될 수 있는 인덱스, key는 사용된 인덱스가 표시된다.

최적화

• 내부적으로 SELECT 명령을 실행하기 앞서 실행계획을 세운다.
  👉 EXPLAIN 명령은 이 실행계획을 확인하는 명령
• 실행계획에서는 인덱스 사용 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단한다.
  👉 판단 기준으로 인덱스의 품질도 고려하므로 서로 다른 값의 여러 종류의 데이터가 존재하면 그만큼 효율도 좋아진다.