[SQL] 인덱스

채록·2021년 3월 19일

TIL sql

Database

목록 보기

7/11

인덱스!! DB내의 효율향상에 아주~ 관련있어보인다.

I. 인덱스 ?

테이블에 붙여진 색인

1. 검색에 사용되는 알고리즘 종류

1) Full table scan

색인을일렬로 정렬된 상태에서 오름차순으로 값을 찾아나간다.
=> 찾는 값이 전체 150중에 100이라면 1부터 100까지 찾아나간다.

2) Binary search

찾고자 하는 값의 전체 값의 중간부터 찾기 시작한다!

ex) 전체 100중에서 30을 찾자!
=> 50보다 큰가? NO!
=> 25보다 큰가? YES!
=> 이부분 잘 모르겠음 37이 기준인지 38이 기준인지!! 무튼 이것보다 큰가? NO!
.
.
.
찾았다 30!!

3) Binary tree

tree 구조로 좌측은 작은 값 / 우측은 큰 값을 지정해 비교해 나간다. Root값은 가운데 값이다.

이미지 출처

트리에 같은 값 있나?
이것을 유일성 이라고 한다. binary tree 내에서 같은 값이 나오게 하려면 지정해주어야 한다.
=> '같은' 조건 추가해주기

이미지 출처

II. 인덱스 사용하기

1) 인덱스 생성하기

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

복수의 열에 대해서 복수의 인덱스 생성이 가능하다!

주의!! 인덱스의 name space 는 table 마다 다르다!!

내 테이블에서 만들어보기!!

mysql> DESC products;
+----+--------+----------+-------+-------+-------+
| id | name   | quantity | price | color | size  |
+----+--------+----------+-------+-------+-------+
|  1 | book   |        1 | 300   | NULL  | small |
|  2 | pencil |       10 | 500   | NULL  | small |
|  3 | cd     |      100 | 3000  | NULL  | small |
|  4 | book   |        5 | 2000  | NULL  | small |
|  5 | rular  |       10 | 800   | NULL  | small |
+----+--------+----------+-------+-------+-------+
mysql> CREATE INDEX qInd ON products (quantity);
mysql> DESC products;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| quantity | int          | YES  | MUL | 0       |                |
| price    | varchar(30)  | YES  |     | NULL    |                |
| color    | varchar(10)  | YES  |     | NULL    |                |
| size     | varchar(10)  | NO   |     | small   |                |
+----------+--------------+------+-----+---------+----------------+

2) 인덱스 확인하기

SHOW INDEX FROM 테이블명;

내 테이블에서 활용해보기!!


mysql> SHOW INDEX FROM products;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | qInd     |            1 | quantity    | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

오.. 뭔가 설정이 굉장히 많다!!

3) 인덱스 제거하기

i) 스키마에서 삭제하기

DROP INDEX 인덱스명

ii) 테이블에서 삭제하기

DROP INDEX 인덱스명 ON 테이블명

III. 인덱스 활용 확인하기

명령어 EXPLAIN을 통해 인덱스가 어떻게 활용되고 있는지 SQL명령을 입력해 확인할 수 있다!!

주의!!
다만 이 SQL 명령은 실제로 실행되는게 아니다!! 어떤 상태로 실행되는지를 DB가 설명해줄 뿐!!

1) =로 검색

mysql> EXPLAIN SELECT * FROM products WHERE quantity=10;
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | products | NULL       | ref  | qInd          | qInd | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM products WHERE quantity=10;
+----+--------+----------+-------+-------+-------+
| id | name   | quantity | price | color | size  |
+----+--------+----------+-------+-------+-------+
|  2 | pencil |       10 | 500   | NULL  | small |
|  5 | rular  |       10 | 800   | NULL  | small |
+----+--------+----------+-------+-------+-------+

2) 부등호로 검색

mysql> EXPLAIN SELECT * FROM products WHERE quantity>10;
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | products | NULL       | range | qInd          | qInd | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
mysql> SELECT * FROM products WHERE quantity>10;
+----+------+----------+-------+-------+-------+
| id | name | quantity | price | color | size  |
+----+------+----------+-------+-------+-------+
|  3 | cd   |      100 | 3000  | NULL  | small |
+----+------+----------+-------+-------+-------+