인덱스
- 테이블에서 원하는 데이터를 쉽고 빠르게 찾기 위해 인덱스 설정 필요
인덱스 설정 요건
✅ 인덱스가 설정된 테이블의 처리 속도가 느려질 수 있기 때문에 수정보다는 검색이 자주 사용되는 테이블에서 사용
- 크기가 큰 테이블에서만 인덱스를 적용
- 기본키 제약, 유일성 제약이 부여될 때에는 불필요(자동으로 인덱스 설정이 되기 때문)
- 한 개의 테이블에 복수의 인덱스를 작성한 경우 오히려 성능이 떨어짐
- Cardinality(카디널리티)가 높은 열에 생성 (원하는 데이터를 선택하는 과정에서 최대한 많은 데이터가 걸러져야 성능이 향상)
✅ Cardinality(카디널리티)
- 특정 열의 고유한 값을 나타내는 데 얼마나 서로 다른 값이 있는지를 나타내는 지표
- 중복도가 낮으면 카디널리티가 높음
- 중복도가 높으면 카디널리티가 낮음
예시 테이블
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
username varchar(100) NOT NULL,
email varchar(100) NOT NULL UNIQUE,
registration_date DATE
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
order_date DATE,
order_status varchar(100),
FOREIGN KEY (user_id) REFERENCES users (user_id)
);
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name varchar(100) NOT NULL,
product_price DECIMAL(10, 2) NOT NULL
);
INSERT INTO users (user_id, username, email, registration_date) VALUES
(1, 'user1', 'user1@example.com', '2023-01-15'),
(2, 'user2', 'user2@example.com', '2023-02-20');
INSERT INTO orders (order_id, user_id, order_date, order_status) VALUES
(1, 1, '2023-01-20', 'Shipped'),
(2, 1, '2023-02-10', 'Delivered'),
(3, 2, '2023-02-25', 'Shipped');
INSERT INTO products (product_id, product_name, product_price) VALUES
(1, 'Product A', 100.00),
(2, 'Product B', 75.50),
(3, 'Product C', 50.25);
INDEX 읽기 방식
INDEX Range Scan
- INDEX를 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾아가는 방식
- SQL구문에서
BETWEEN~AND구문, LIKE구문, <,> 등 비교 연산 및 구문에 포함될 경우 인덱스 범위 스캔 진행
- 좁은 범위를 스캔할 때는 효율적이지만 넓은 범위를 스캔할 때는 비효율적
create index date_index on orders(order_date);
explain
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
✅ range
- 테이블 내의 연속된 데이터 범위를 조회하는 유형
- =, <>, <, >, IS NULL, BETWEEN 또는 IN연산을 통해 범위 스캔을 수행
INDEX Full Scan
- 인덱스를 처음부터 끝까지 스캔하는 방식
- 단, 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구한 SQL문에서 INDEX FULL Scan진행
- Table Full Scan보다는 상대적으로 적은 양을 차지하므로 성능상 유리하나 인덱스 오브젝트의 전 영역을 모두 검색하는 방식으로 검색범위를 줄이는 튜닝 필요
explain
select * from orders;
INDEX Unique Scan
- 기본 키나 고유 인덱스로 테이블에 접근하는 방식으로 인덱스 스캔 방식 중 가장 효율적인 방법
- WHERE절에 = 조건으로 작성하여 해당 조인 열이 기본키 또는 고유 인덱스의 선두 열로 설정되었을 때 활용
explain
SELECT * FROM users
WHERE user_id = 1;
✅ const
- 조회하는 데이터가 단 1건일 때 출력되는 유형으로 성능상 매우 유리한 방식
- 고유 인덱스나 기본키를 사용하면 단 1건의 데이터에만 접근
INDEX loose Scan
- 인덱스의 필요한 부분들만 골라 스캔하는 방식으로 INDEX Range Scan처럼 넓은 범위에 스캔하는 것이 아닌 WHERE조건문 기준으로 필요한 데이터와 필요하지 않은 데이터를 구분한 뒤 불필요한 인덱스 키는 무시
- 보통 GROUP BY, MAX(), MIN()함수가 포함 시 작동
INDEX Merge Scan
- 테이블 내의 생성된 인덱스들을 통합해서 스캔하는 방식
- WHERE조건절의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식
- INDEX Merge Scan은 물리적으로 존재하는 개별 인덱스를 각각 수행하므로 인덱스에 접근하는 시간이 몇 배로 걸림
create index status_idx on orders(order_status);
explain
SELECT * FROM orders
WHERE order_status = 'Shipped'
UNION
SELECT * FROM orders
WHERE order_status = 'Delivered';
✅ ref
- 조인을 수행할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우
- 드라이빙 테이블과 드리븐 테이블이 조인을 수행하면 일대다 관계가 되므로, 드라이빙 테이블의 1개 값이 드리븐 테이블에서는 2개 이상의 데이터로 존재
- 드리븐 테이블의 데이터양이 많지 않을 때는 성능 저하를 우려하지 않아도 되나, 데이터양이 많다면 접근해야 할 데이터 범위가 넓어져 성능 저하의 원인이 되는지 확인 필요
참고
