인덱스란?
인덱스는 데이터를 효과적으로 찾기 위한 자료구조다.
- 인덱스는 하나의 컬럼 혹은 여러 개의 컬럼에 대해 정의된다.
- 인덱스는 <컬럼의 값, 해당 레코드에 대한 주소>들로 구성된다.
- 인덱스는 컬럼값에 따라 정렬되어 있다.
- 인덱스는 실제 데이터보다 훨씬 작다.
인덱스를 왜 사용할까?
간단하다. 데이터를 빠르고 효율적으로 찾기 위해!
정렬되지 않은 데이터의 탐색
이러한 테이블이 있을 때 age=37인 데이터를 찾으려면 n개의 데이터를 하나하나 조회해야 한다.
수 억개의 데이터가 있다면? 완전탐색으로 인한 지연시간이 너무 커질 것이다.
정렬된 데이터 탐색
이진탐색을 사용하면 O(logN) 시간복잡도로, 1억 개의 데이터를 30번의 탐색해 원하는 데이터를 조회할 수 있다!
인덱스 요소는 <컬럼의 값, 해당 레코드에 대한 주소>로, 원하는 컬럼값을 찾으면 해당 레코드로 이동해 정보를 제공한다.
인덱스는 하나의 컬럼 혹은 복수의 컬럼에 생성할 수 있다.
PK에는 기본적으로 인덱스가 생성된다.
장단점
장점
- 탐색 시간을 줄인다.
- WHERE뿐만 아니라 UPDATE, DELETE 성능도 개선된다.
단점
- 용량 차지
- 인덱스는 컬럼을 따로 저장하는 것이기에 데이터베이스 용량을 차지한다.
- 인덱스 관리
- 데이터의 삽입/수정/삭제마다 인덱스도 수정해야 한다.
단점이라고 하지만 장점의 메리트가 훨씬 크기 때문에 적절하게 인덱스를 걸어 사용한다.
인덱스 컬럼 선정 기준
- 분포도가 좋은 컬럼인가?
- 분포도: 전체 레코드에서 식별 가능한 수에 대한 백분율
- 분포도가 낮을수록 분포도가 좋다.
- 갱신이 자주 발생하지 않는 컬럼인가?
- 조건절에서 자주 사용되는 컬럼인가?
- 조인의 연결고리에 사용되는 컬럼인가?
- sort 발생을 제거하는 컬럼인가?
- ORDER BY의 대상이 되는 컬럼인가?
인덱스의 구현
B+트리
- 이진탐색보다 빠른 탐색
- 범위탐색에 효과적
인덱스 사용해보기
MySQL
인덱스 생성
create index {indexName} on {tableName}({ColumnName});인덱스 조회
show index from {tableName};인덱스 삭제
alter table {tableName} drop index {indexName};더미 데이터
데이터가 많은 편이 테스트 결과를 보기 좋다.
더미 데이터를 삽입 하는 방법은 여러 가지가 있다.
- 프로시저
- 서비스 이용
프로시저
예시
DELIMITER $$
DROP PROCEDURE IF EXISTS insertLoop$$
CREATE PROCEDURE insertLoop()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 50000 DO
INSERT INTO coupang.PRODUCT(category_id, name, price, main_img, detail_img, stock, score, delivery_fee, is_rocket, is_rocket_fresh, is_rocket_global)
VALUES (1, concat('product_name',i), 10000+i, concat('product_path',i), concat('product_detail_path',i), 100+i, 7, 2000+i, 1, 1, 0);
SET i = i + 1;
END WHILE;
END$$
DELIMITER $$
CALL insertLoop;
$$서비스
사용 예시
테이블 스키마
Product 테이블/Row: 2000개
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| ProductID | int | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | YES | | NULL | |
| userID | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+User 테이블/Row: 1998개
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| userID | varchar(100) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+Index
product 테이블의 userid 컬럼에 인덱스를 걸었다.
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| product | 0 | PRIMARY | 1 | ProductID | A | 2000 | NULL | NULL | | BTREE | | | YES | NULL |
| product | 1 | productindex | 1 | userID | A | 931 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+Join
select user.userid, product.name, user.age
from user join product on user.userid=product.userid;user, product 테이블에서 같은 userid를 가지는 행을 찾는 join문을 테스트 해보자
쿼리 동작 확인
explain 명령어를 사용해 쿼리의 작동 방식을 미리 볼 수 있다.
- type: 행 접근 방식
- ALL: 인덱스를 사용하지 않음
- possible_keys: 이용가능한 인덱스 목록
- key: 실제로 선택된 인덱스
explain select user.userid, product.name, user.age from user join product on user.userid=product.userid;
+----+-------------+---------+------------+------+---------------+--------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1998 | 100 | Using where |
| 1 | SIMPLE | product | NULL | ref | productindex | productindex | 403 | studysql.user.userID | 2 | 100 | NULL |
+----+-------------+---------+------------+------+---------------+--------------+---------+----------------------+------+----------+-------------+product 테이블에 대해 productindex를 사용했음을 볼 수 있다.
부하 확인
explain format=tree 명령어를 이용해 쿼리의 cost를 확인할 수 있다.
------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=1703.81 rows=4292)
-> Filter: (`user`.userID is not null) (cost=201.55 rows=1998)
-> Table scan on user (cost=201.55 rows=1998)
-> Index lookup on product using productindex (userID=`user`.userID) (cost=0.54 rows=2)
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+cost는 1703.81이다.
아래는 인덱스를 사용하지 않은 경우다.
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (product.userID = `user`.userID) (cost=399810.78 rows=399600)
-> Inner hash join (<hash>(product.userID)=<hash>(`user`.userID)) (cost=399810.78 rows=399600)
-> Table scan on product (cost=0.01 rows=2000)
-> Hash
-> Table scan on user (cost=201.55 rows=1998)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+cost 399810.78로 큰 차이가 난다.
explain select user.userid, product.name, user.age from user join product on user.userid=product.userid where user.age=44;
+----+-------------+---------+------------+------+---------------+--------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | ref | userindex | userindex | 5 | const | 2 | 100 | Using where |
| 1 | SIMPLE | product | NULL | ref | productindex | productindex | 403 | studysql.user.userID | 2 | 100 | NULL |
+----+-------------+---------+------------+------+---------------+--------------+---------+----------------------+------+----------+-------------+Join
select user.userid, product.name, user.age from user join product on user.userid=product.userid where user.age=44;user, product 테이블에서 같은 userid를 가지며 age가 44인 데이터를 찾는 join문도 테스트 해보자
부하 확인
인덱스를 걸지 않은 경우
explain format=tree select user.userid, product.name, user.age from user join product on user.userid=product.userid where user.age=44;
+---------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------+
| -> Filter: (product.userID = `user`.userID) (cost=40164.50 rows=39960)
-> Inner hash join (<hash>(product.userID)=<hash>(`user`.userID)) (cost=40164.50 rows=39960)
-> Table scan on product (cost=0.11 rows=2000)
-> Hash
-> Filter: (`user`.age = 44) (cost=201.55 rows=200)
-> Table scan on user (cost=201.55 rows=1998)
|
+---------------------------------------------------------------------+코스트 40164.50
인덱스 건 경우
explain format=tree select user.userid, product.name, user.age from user join product on user.userid=product.userid where user.age=44;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=2.20 rows=4)
-> Filter: (`user`.userID is not null) (cost=0.70 rows=2)
-> Index lookup on user using userindex (age=44) (cost=0.70 rows=2)
-> Index lookup on product using productindex (userID=`user`.userID) (cost=0.64 rows=2)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+코스트 2.20로 크게 차이난다.
