참고
Indexing(인덱싱)
NOTE
인덱스는 Heap(데이터가 페이지로 저장되는 구조)에 있는 데이터를 가리키는 포인터를 포함하는 별도의 데이터 구조입니다. 인덱스를 통해 특정 값을 빠르게 검색할 수 있으며, 주로 B-Tree 구조가 사용됩니다.
인덱싱 안하면 위처럼 전체 검색함.. (Full Scan, 순차접근)
테이블이 사전순으로 정렬되면 더 빠르게 조회 가능 (트리 검색 방식)
Index구조와 Page(Heap) 구조
데이터베이스의 테이블과 인덱스가 디스크에 어떻게 저장되고, 쿼리되는지 발생하는 비용을 알기 위해서 먼저 아래의 기초 개념들을 알아야합니다.
테이블과 ROW_ID
논리적인 데이터 구조로 열과 행으로 이루어집니다. 행을 고유하게 식별하기 위해 시스템에서 관리하는 ROW_ID를 생성하며, PostgreSQL에서는 Tuple ID라고도 합니다.
Page와 IO
고정된 크기의 메모리 위치로, DB는 개별 행을 읽는 대신 한 번의 IO 작업으로 하나 이상의 Page를 읽습니다.
•
Page크기는 DBMS마다 다르며, MySQL에서는 16KB로 설정됩니다.
•
IO 작업은 페이지 단위로 수행되며, 가능한 적은 IO작업으로 필요한 데이터를 얻는것이 중요합니다.
Heap 구조
모든 테이블 데이터가 저장된 Page들의 집합입니다.
•
데이터를 순서 없이 저장하며, 필요한 데이터를 찾기 위해서는 많은 데이터를 읽어야해서 비용이 많이 듭니다.
Index
Heap 구조와 별개로 존재하는 데이터 구조로, 빠른 검색을 위해 사용됩니다.
•
Index는 특정 열에 대해 생성되며, 해당 열의 값을 빠르게 찾을 수 있고 B-Tree를 주로 사용합니다.
인덱스를 사용할 시 주의점
NOTE
Index를 적절히 사용하지 않으면 오히려 성능을 저하시킬 수 있습니다. 인덱스가 너무 과도하게 쓰이거나, 복합 인덱스를 사용하는 경우와 같은 경우에는 사용되는 순서에따라 성능이 크게 달라질 수 있습니다.
DBMS는 Index를 항상 최신의 정렬 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있습니다. 그러므로 Index가 적용된 컬럼에 write가 수행된다면 아래와 같은 연산이 오버헤드로 발생합니다.
또한 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요합니다.
인덱스를 남발하면 오히려 오버헤드가 발생한다. (인덱스 테이블에 추가연산이 들어간다.)
•
INSERT 연산: 새로운 데이터에 대한 인덱스를 추가한다.
•
DELETE 연산: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 수행합니다.
•
UPDATE 연산: 기존의 인덱스를 제거하고, 갱신된 데이터에 대해 인덱스를 추가합니다.
복합 인덱스의 경우 왼쪽에 있는 컬럼부터 사용한다.
•
복합 인덱스 (a,b)가 존재할 때, b 컬럼만으로 WHERE 절 검색을 수행하면 인덱스를 활용하지 못합니다.
•
이는 복합 인덱스가 첫 번째 컬럼(a)을 기준으로 정렬된 후, 그 안에서 두 번째 컬럼(b)이 정렬되기 때문입니다. 따라서 b 컬럼만으로는 효율적인 인덱스 검색이 불가능합니다.
랜덤 액세스(Random Access)
NOTE
랜덤 엑세스란, 데이터를 저장하는 블록을 한 번에 여러 개 엑세스하는 것이 아니라 한 번에 하나의 블록만을 접근하는 방식입니다.
한 번에 여러 개의 블록을 접근한다면 같은 양의 데이터에 대해 적은 횟수의 디스크 I/O가 발생하기 때문에 성능이 향상될 수 있습니다.
순차접근은 연속적인 데이터를 읽고, 랜덤접근은 필요한 데이터를 하나씩 읽습니다.
순차접근 vs 랜덤접근 (데이터가 많아질수록 순차접근이 더 좋을 수 있다.)
랜덤 액세스 발생시점
Index에 접근하여 확인한 ROWID를 이용하여, 테이블을 접근하는 경우 발생하게 됩니다.
ROWID는 해당 데이터를 찾아가는 유일한 주소 값이며 인덱스를 이용한다는 것은 인덱스로부터 조건을 만족하는 인덱스 값에 접근해서 ROWID로 테이블을 조회한다는 의미입니다.
1.
확인 랜덤 엑세스: SQL의 WHERE, HAVING절에 의해 발생합니다.
WHERE '전표번호', '전표일련번호'
-- 인덱스 구성 '전표번호'
SQL
복사
•
전표번호는 Index로 커버되지만, 전표일련번호는 인덱스에 포함되지 않는 경우입니다.
•
이 경우, 전표번호 조건을 만족하는 모든 레코드에 대해 인덱스를 통해 ROWID를 얻고, 테이블에 접근해서 전표일련번호를 추가로 확인합니다.
•
이 과정에서 확인 랜덤 액세스가 발생합니다. 즉 인덱스에서 찾은 결과를 기반으로 테이블에 접근하여 추가적인 조건을 확인하는 접근입니다.
2.
추출 랜덤 액세스: SELECT절에 의해 발생하는 랜덤 엑세스
SELECT "거래일자"
-- 인덱스 구성: "전표번호", "전표일련번호"
SQL
복사
•
Index에 ‘전표번호’와 ‘전표일련번호’가 포함되어 있어 두 컬럼에 대한 조건은 Index로 처리할 수 있습니다.
•
SELECT절에서 거래일자와 같은 인덱스에 없는 컬럼이 필요할 때, Index를 통해 ROWID를 얻은 후, 다시 테이블에 접근하여 컬럼을 가져와야 합니다.
•
이 과정에서 추출 랜덤 액세스가 발생하며, 해당 인덱스를 통해 찾은 후, 필요한 컬럼을 가져오기 위해 테이블에 접근하는 형식입니다.
3.
정렬 랜덤 액세스: ORDER BY, GROUP BY절에 의해 발생하는 랜덤 액세스
•
정렬 작업을 위해 인덱스를 사용하는 경우, 인덱스에서 정렬된 데이터를 가져오는 과정에서 랜덤 액세스가 발생합니다.
•
이때 발생하는 랜덤 엑세스는 정렬된 데이터의 건수와 동일합니다.
Heap-Organized Table, Index-Organized Table(IOT)
NOTE
Heap-Organized Table과 Index-Organized Table은 MySQL을 기준으로 보면, 기본적으로 모든 테이블이 Heap-Organized 방식으로 생성되지만, Clustered Index를 활용하면 특정 열을 기준으로 정렬된 테이블이 생성됩니다.
Heap-Organized Table
MySQL에서 기본적으로 만들어지는 방식이며, 데이터가 삽입된 순서대로 테이블에 저장되며 기본키나 보조 인덱스를 통해 데이터를 조회할 수 있습니다.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
hire_date DATE
);
SQL
복사
+------------+------------+------------+------------+
| Row ID | Employee ID| First Name | Department |
+------------+------------+------------+------------+
| 1 | 105 | Alice | HR |
| 2 | 101 | Bob | Sales |
| 3 | 110 | Charlie | HR |
| 4 | 102 | David | Sales |
| 5 | 107 | Eve | Marketing |
+------------+------------+------------+------------+
SQL
복사
Heap-Organized Table
[Heap-Organized Table - MySQL]
1. 쿼리 실행
|
v
2. 인덱스 검색 (보조 인덱스)
|
v
3. 힙(Heap)에서 데이터 행 위치 찾기
|
v
4. 데이터 행 접근
|
v
5. 결과 반환
SQL
복사
•
데이터는 삽입된 순서대로 저장됩니다.
•
PK가 설정되어 있어 해당 열에 대해 자동으로 Index가 생성됩니다.
•
데이터를 검색할 때 Index를 사용하여 특정 행을 찾은 후, Heap에서 해당 데이터를 조회하게 됩니다.
Index-Organized Table
MySQL에서 별도로 지원되지는 않지만 Clustered Index를 통해 유사하게 만들 수 있습니다.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
hire_date DATE
) ENGINE=InnoDB;
SQL
복사
InnoDB 스토리지 엔진을 통해 테이블을 생성(PK기준 정렬)
+------------+------------+------------+------------+
| Employee ID| First Name | Last Name | Department |
+------------+------------+------------+------------+
| 101 | Bob | Smith | Sales |
| 102 | David | Jones | Sales |
| 105 | Alice | Brown | HR |
| 107 | Eve | Davis | Marketing |
| 110 | Charlie | Wilson | HR |
+------------+------------+------------+------------+
SQL
복사
Index-Organized Table
[Index-Organized Table - MySQL with InnoDB]
1. 쿼리 실행
|
v
2. 클러스터드 인덱스 검색 (Primary Key)
|
v
3. 데이터 행 직접 접근 (정렬된 상태로 저장)
|
v
4. 결과 반환
SQL
복사
•
Employee ID를 기준으로 테이블의 데이터가 물리적으로 정렬되어 저장됩니다.
•
데이터를 검색할 때, 테이블이 인덱스 순서대로 정렬되어 Heap조회 없이 빠르게 접근합니다.
클러스터형 인덱스(Clustered Index) vs 비클러스터형 인덱스(Non-Clustered Index)
NOTE
인덱스는 클러스터형과 비클러스터형으로 나뉩니다. 이 두가지 인덱스는 데이터가 디스크에 저장되는 방식과 검색 효율성에 있어 중요한 차이를 보입니다.
클러스터형 인덱스
클러스터형 인덱스는 테이블의 데이터를 특정 열의 값에 따라 물리적으로 정렬하여 저장합니다.
클러스터형 인덱스가 적용된 테이블에서는 인덱스가 곧 데이터이며, 별도의 힙 구조가 존재하지 않습니다.
B Tree의 구조를 가진다. (Node = Page)
•
테이블 당 하나만 생성할 수 있으며, 특정 컬럼을 PK로 지정하면 클러스터형 인덱스가 생성됩니다.
•
특정 범위의 데이터를 빠르게 검색할 수 있습니다.
비클러스터형 인덱스
비 클러스터형 인덱스는 별도의 인덱스 페이지를 생성하여 테이블의 데이터를 참조합니다.
데이터 자체는 클러스터형 인덱스와 달리 물리적으로 정렬되지 않으며, 인덱스는 테이블 데이터의 위치를 가리키는 포인터 역할을 합니다.
•
인덱스는 데이터를 직접 포함하지 않고 저장된 위치를 가리킵니다.
•
데이터를 검색하려면 먼저 비클러스터형 인덱스를 통해 데이터를 찾은 후, 해당 위치에서 실제 데이터를 조회해야 합니다.
•
실제 데이터 페이지는 정렬되지 않으므로, 클러스터형에 비해 write작업이 비교적 빠르다.
클러스터형, 비클러스터형 혼합
현실적으로 테이블에 클러스터형 인덱스와 비클러스터형 인덱스가 혼합된 경우가 많습니다.
예를 들어, PK는 클러스터형 인덱스로 설정되고, 추가로 자주 조회되는 다른 열에는 비클러스터형 인덱스가 사용됩니다.
•
비클러스터형 인덱스를 먼저 거치고 인덱스형 데이터를 찾습니다. 이때 비클러스터형 인덱스는 데이터 페이지 번호 + #오프셋 대신 클러스터형 인덱스에 대한 컬럼 값을 가집니다.
•
비클러스터형 인덱스가 클러스터형 인덱스 값을 가지는 이유는, 데이터의 변경이 생기는 경우 비클러스터형의 데이터가 직접 값을 참조하면 데이터 변경시마다 인덱스를 갱신해야 한다.
실습
docker run \
--name mysql-test \
-e MYSQL_ROOT_PASSWORD=my-secret-pw \
-e MYSQL_DATABASE=testdb \
-p 3306:3306 \
-d \
mysql:latest
docker exec -it mysql-test mysql -u root -p
Bash
복사
-- 1. 스키마 지정
USE testdb;
-- 2. 테이블 생성
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
-- 3. 데이터 삽입
INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 50000.00),
('Jane', 'Doe', 'HR', 55000.00),
('Jim', 'Beam', 'Engineering', 60000.00),
('Jack', 'Daniels', 'Marketing', 65000.00);
-- 4. 비클러스터형 인덱스 생성
CREATE INDEX idx_department ON employees(department);
-- 5. 인덱스 확인
SHOW INDEX FROM employees;
-- 6. 클러스터형, 비클러스터형 인덱스 확인
EXPLAIN SELECT * FROM employees WHERE employee_id = 1; -- 클러스터
EXPLAIN SELECT * FROM employees WHERE department = 'Sales'; -- 비클러스터
-- 비클러스터 방식은 employee_id(클러스터형 키)를 반환하고 다시 클러스터형에서 찾는다.
-- 7. 혼합 인덱스
-- employee(클러스터), department(비클러스터)
EXPLAIN SELECT *
FROM employees
WHERE employee_id = 1
AND department = 'Sales';
SQL
복사
EXPLAIN
NOTE
지정한 쿼리문에 대해서 실행계획기가 만든 실행 계획을 보여준다!
EXPLAIN SELECT * FROM table_name WHERE condition;
TypeScript
복사
•
id: 쿼리 내에서 SELECT 문의 실행 순서를 나타낸다.
•
select_type: 쿼리의 각 부분이 어떤 유형인지를 나타낸다.
◦
ex) SIMPLE, PRIMARY, UNION, SUBQUERY
•
table: 접근 중인 테이블 이름입니다.
•
type: 조인 유형을 나타내며 쿼리 성능에 영향을 미치는 가장 큰 요소 입니다.
◦
ALL, index, range, ref, ‘eq_ref’, const, system, NULL
•
possible_key: 쿼리에 사용될 수 있는 인덱스 목록
•
key: 실제로 사용되는 인덱스입니다.
•
key_len: 사용된 인덱스의 길이입니다.
•
ref: 인덱스의 어떤 컬럼이 비교에 사용되는지를 나타냅니다.
•
rows: 이 쿼리를 실행하기 위해 탐색해야하는 예상 행(row)가 주어집니다.
•
Extra: 추가 정보
◦
Using index, Using Where, Using temporary, Using filesort
분석 및 최적화
type 컬럼은 쿼리 성능을 최적화하는데 있어 가장 중요한 지표이다. ALL, index를 range, ref, eq_ref, const로 바꾸도록 노력하자.
•
ALL: 테이블의 모든 행을 스캔한다. (성능이 매우 떨어진다.)
•
index: 인덱스 전체를 스캔하지만, 테이블 스캔보다는 좋다.
•
range: 인덱스 범위 스캔으로 특정 범위 내에서만 스캔한다.
•
ref: 인덱스를 사용하여 하나 이상의 행을 검색한다.
•
eq_ref: 기본 키 또는 고유 인덱스를 사용하여 정확히 하나의 행을 검색한다.
•
const/system: 고정된 값으로, 1행만 일치한다.
실습
NOTE
실제로 몇백만건의 데이터를 넣고 일반조회와 인덱스 조회의 시간차이를 확인해보자!
UUID vs Auto Increment 중 PK 선택하기
우리의 경험에 따르면 95%의 경우 PK 선택은 항상 Auto Increment Integer 여야 합니다. 가독성은 단순함을 이끕니다. 숫자는 쓰기 쉽고, 기억하기 쉽고, 의사소통하기 쉽습니다. 사건의 발단 프로젝트 진행 과정에서 Auto Increment로 PK 사용으로 발생될 수 있는 문제점이 있었다 Increment PK는 정수형으로 보통 1, 2, 3, 4 순서로 순차적 채번이 된다는 특징이 있다. 만약 A라는 사용자가 게시물(PK는 1)을 올린다고 가정하고 B라는 악의적인 사용자가 본인의 글을 수정을 할 때 API에 요청 데이터 중 PK ID만 1로 바꾸면 1번 글이 수정된다는 취약점이 존재한다. 그래서 비즈니스 로직에서 해당 게시물을 쓴 소유자와 현재 수정하려는 사용자가 일치한 지를 파악하는..
https://stir.tistory.com/294
강의 중 참고하라던 내용 (PK ID를 참고할수 있어 Auto Increment의 취약점이 나온다는 내용)
근데 읽어보면 걍 Auto Increment가 보편적이고 이상적으로 UUID가 좋다는 내용..
@Test
public void bulkInsert(){
var easyRandom = PostFixtureFactory.get(
1L,
LocalDate.of(1978, 1, 1),
LocalDate.of(2022, 2, 1)
);
var stopWatch = new StopWatch();
stopWatch.start();
var posts = IntStream.range(0, 10000 * 100)
.parallel()
.mapToObj(i -> easyRandom.nextObject(Post.class))
.toList();
stopWatch.stop();
System.out.println("객체 생성 시간 " + stopWatch.getTotalTimeSeconds());
var queryStopWatch = new StopWatch();
queryStopWatch.start();
postRepository.bulkInsert(posts);
queryStopWatch.stop();
System.out.println("객체 저장 시간" + queryStopWatch.getTotalTimeSeconds());
}
Java
복사
100만건의 데이터를 저장!
총 400만의 데이터
데이터 생성시 메모리를 많이 먹으므로 주의하자.
Bulk Insert
NOTE
DB에 INSERT 해야하는 레코드가 많은 경우 사용되는 기법이다!
# 기존의 쿼리
INSERT INTO table VALUES (1, "hello");
INSERT INTO table VALUES (2, "world");
INSERT INTO table VALUES (3, "!");
# 한 쿼리에 묶어서 넣어준다.
INSERT INTO table VALUES (1, "hello"), (2, "world"), (3, "!");
SQL
복사
실제로 사용할 때는, 한번에 수천개씩 묶어서 넣어줌
public void bulkInsert(List<Post> posts) {
var sql = String.format("""
INSERT INTO `%s` (memberId, contents, createdDate, createdAt)
VALUES (:memberId, :contents, :createdDate, :createdAt)
""", TABLE);
SqlParameterSource[] params = posts
.stream()
.map(BeanPropertySqlParameterSource::new)
.toArray(SqlParameterSource[]::new);
namedParameterJdbcTemplate.batchUpdate(sql, params);
}
Java
복사
실제 사용
성능이 향상되는 이유
•
DB의 다양한 요소들에 의해, 쿼리 한번이 이루어지면 그 전후에 이루어지는 작업이 꽤나 있다.
◦
Transaction, Index 등
•
DB에 1,000,000개의 데이터를 INSERT한다고 가정하자
◦
DB에 1개의 데이터를 넣으면 N, 쿼리 전후에 M이 소모된다고 가정
◦
쿼리 하나에 1개의 레코드를 INSERT
▪
1,000,000 * N + 1,000,000 * M
◦
쿼리 하나에 1,000개의 튜플을 INSERT
▪
1,000,000 * N + 1,000 * M → (M이 1000배의 시간을 절약하게됨)