인덱스
INDEX의 의미
인덱스(Index)는 RDBMS에서 검색 속도를 높이기 위해 사용하는 하나의 기술이다. 인덱스는 색인이라고도 하며, 테이블의 특정 컬럼을 색인화하여 검색 시 해당 테이블의 레코드를 전부 스캔하는 대신, 색인화된 인덱스 파일을 검색함으로써 검색 속도를 빠르게 한다. 인덱스 구조는 주로 Tree 구조로 색인화되며, RDBMS에서 사용하는 인덱스는 Balance Search Tree를 사용한다.
INDEX의 원리
인덱스를 해당 컬럼에 주게 되면 초기 테이블 생성 시, FRM, MYD, MYI 3개의 파일이 만들어진다.
- FRM: 테이블 구조가 저장되어 있는 파일
- MYD: 실제 데이터가 있는 파일
- MYI: 인덱스 정보가 들어있는 파일
인덱스를 사용하지 않는 경우, MYI 파일은 비어져 있다. 그러나 인덱스를 해당 컬럼에 만들게 되면, 해당 컬럼을 따로 인덱싱하여 MYI 파일에 입력한다. 이후 사용자가 SELECT 쿼리로 인덱스를 사용하는 쿼리를 실행하면, 해당 테이블을 검색하는 대신 MYI 파일의 내용을 검색한다. 인덱스를 사용하지 않는 SELECT 쿼리라면 테이블을 Full Scan하여 모두 검색한다.
이는 책의 뒷부분에 있는 '찾아보기'와 같은 개념이다. 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을 수 있는 것과 같다. '찾아보기'가 없다면 처음부터 끝까지 모든 페이지를 보고 찾아야 할 것이다.
INDEX의 장점
- 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
- 인덱스를 사용하면 테이블 행의 고유성을 강화할 수 있다.
- 테이블의 기본키는 자동으로 인덱스된다.
- 여러 필드로 이루어진(다중 필드) 인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있다.
INDEX의 단점
- 인덱스를 만들면 .mdb 파일 크기가 늘어난다.
- 사용자가 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
- 인덱스된 필드에서 데이터를 업데이트하거나 레코드를 추가 또는 삭제할 때 성능이 떨어진다.
- 인덱스가 데이터베이스 공간을 차지하여 추가적인 공간이 필요해진다.
- 인덱스를 생성하는 데 시간이 많이 소요될 수 있다.
- 데이터 변경 작업이 자주 일어날 경우, 인덱스를 재작성해야 할 필요가 있어 성능에 영향을 끼칠 수 있다.
따라서 어느 필드를 인덱스해야 하는지 미리 시험해보고 결정하는 것이 좋다. 인덱스를 추가하면 쿼리 속도가 빨라지지만, 데이터 행을 추가하는 속도는 느려지게 되어 여러 사용자가 사용하는 경우 레코드 잠금 문제가 발생할 수 있다. 특정 필드에 대한 인덱스를 만드는 것이 항상 성능을 향상시키는 것은 아니므로 주의해야 한다.
INDEX의 목적
인덱스의 목적은 RDBMS의 검색 속도를 높이는 데 있다. SELECT 쿼리의 WHERE 절이나 JOIN 예약어를 사용했을 때만 인덱스를 사용하며, SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있다. DELETE, INSERT, UPDATE 쿼리에는 해당 사항이 없으며, 인덱스 사용 시 오히려 성능이 저하될 수 있다.
상황 분석
사용하면 좋은 경우
- WHERE 절에서 자주 사용되는 컬럼
- 외래키가 사용되는 컬럼
- JOIN에 자주 사용되는 컬럼
사용을 피해야 하는 경우
- 데이터 중복도가 높은 컬럼
- DML(Data Manipulation Language)이 자주 일어나는 컬럼
DML에 취약
INSERT
- Index Split: 인덱스의 블록들이 하나에서 두 개로 나누어지는 현상. 인덱스는 데이터가 순서대로 정렬되어야 한다. 기존 블록에 여유 공간이 없는 상황에서 새로운 데이터가 입력되면, 기존 블록의 내용 중 일부를 새 블록에 기록한 후 기존 블록에 빈 공간을 만들어 새로운 데이터를 추가한다. 이는 성능 면에서 매우 불리하다.
DELETE
- 테이블에서 데이터가 삭제될 경우, 다른 데이터가 그 공간을 사용할 수 있다. 그러나 인덱스에서는 데이터가 삭제되지 않고 '사용 안됨' 표시만 해둔다. 즉, 테이블에 데이터가 1만 건 있는 경우, 인덱스에는 2만 건이 있을 수 있다는 뜻이다.
UPDATE
- 인덱스에는 업데이트 개념이 없다. 테이블에 업데이트가 발생하면 인덱스에서는 삭제가 먼저 발생한 후 새로운 작업의 삽입 작업이 발생한다. 이는 성능에 큰 부하를 준다.
예제 코드
인덱스 생성 예제 (MySQL)
CREATE TABLE posts (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT,
author_id INT,
INDEX (author_id)
);
-- 인덱스 생성
CREATE INDEX idx_title ON posts(title);인덱스 사용 예제 (Java JDBC)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IndexExample {
public static void main(String[] args) {
try {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");
Statement stmt = conn.createStatement();
// 인덱스 사용하여 검색
String sql = "SELECT * FROM posts WHERE title = 'Example Title'";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id"));
System.out.println("Title: " + rs.getString("title"));
System.out.println("Content: " + rs.getString("content"));
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
So...
인덱스는 데이터베이스의 검색 속도를 높이는 데 중요한 역할을 한다. 적절한 필드에 인덱스를 생성하면 쿼리 성능이 향상되지만, 잘못된 사용은 오히려 성능을 저하시킬 수 있다. 인덱스의 장단점을 잘 이해하고, 상황에 맞게 인덱스를 사용하는 것이 중요하다. DELETE, INSERT, UPDATE 쿼리에는 인덱스 사용이 적합하지 않을 수 있으며, SELECT 쿼리의 성능을 최적화하는 데 초점을 맞추는 것이 좋다.
