데이터베이스 최적화 방법

1. 인덱스 최적화

 

- 적절한 인덱스를 생성하되 과도한 인덱스는 피해야 한다. INSERT, UPDATE, DELETE 성능이 저하될 수 있기 때문

 

2. 쿼리 최적화

 

- 불필요한 SELECT * 대신 필요한 컬럼만 조최

- 서브쿼리 대신 JOIN 활용

- WHERE절 최적화 (LIKE '% keyword %  대신  LIKE 'keyword%'  사용)

- GROUP BY, ORDER BY 최소화 : 정렬, 그룹핑은 비용이 크므로 필요할 때만 사용

 

3. 테이블 설계 최적화

 

- 정규화(Normalization): 데이터 중복을 최소화하고 데이터 무결성을 유지

- 비정규화(Denormalization): 자주 사용되는 데이터를 중복 저장하여 JOIN 연산을 줄임

- 파티셔닝(Partitioning): 대량의 데이터를 여러 개의 테이블로 나누어 조회 성능 향상

- 샤딩(Sharding): 데이터를 여러 개의 서버에 분산하여 처리

 

4.  캐시(Cache) 활용

 

- DB Query Cache: 자주 사용하는 쿼리 결과를 캐싱하여 성능 향상

 

5.  트랜잭션(Transaction) 및 동시성 최적화

 

- 트랜잭션 크기 최소화: 너무 긴 트랜잭션은 Lock을 증가시켜 성능 저하 유발

- 격리 수준(Isolation Level) 조정: 필요에 따라 READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE 등을 조정

- 락(Lock) 최소화: 필요 없는 ROW LOCK, TABLE LOCK 사용을 피하고 OPTIMISTIC LOCKING 사용

 

6. 데이터베이스 설정 튜닝

 

- 연결(Connection) 풀링: DB 연결을 재사용하여 성능 향상

- 버퍼 풀(Buffer Pool) 및 캐시 크기 조정: InnoDB Buffer Pool, Shared Buffer 등 조정

- 쿼리 실행 계획(Execution Plan) 분석: EXPLAIN 명령어를 활용하여 쿼리 성능 분석

 

 

---

※ 버퍼 (Buffer)와 빌더(Builder)

 

 : 메모리를 효율적으로 사용하고 속도를 최적화하기 위한 기법. 주로 문자열 처리 및 데이터 처리 성능 최적화와 관련

 

1. 버퍼

 : 데이터를 임시로 저장하는 공간으로, 연산 속도를 높이고 자원을 효율적으로 사용하기 위해 사용

 

ex) StringBuffer (Java)

자바에서 String은 불변(immutable)이기 때문에 문자열을 변경할 때마다 새로운 객체를 생성해야 한다.

하지만 StringBuffer는 버퍼 공간에서 문자열을 수정할 수 있어 성능이 향상

// String (불변 객체: 문자열 변경 시 새 객체 생성)
String str = "Hello";
str += " World";  // 새로운 문자열 객체 생성 (비효율적)

// StringBuffer (가변 객체: 기존 객체에서 문자열 변경)
StringBuffer sb = new StringBuffer("Hello");
sb.append(" World");  // 기존 객체에서 문자열 추가 (효율적)

 

2. 빌더

 : 데이터 객체를 점진적으로 구성하는 방법으로, 보통 객체 생성 최적화 및 성능 향상을 위해 사용

 : 체이닝 방식을 사용하여 가독성이 좋음

 : Buffer보다 더 빠른 성능을 제공 (싱글 스레드 환경에서)

 

ex) StringBuilder (Java)

StringBuffer와 비슷하지만 멀티스레드 동기화(synchronization)를 지원하지 않기 때문에 더 빠르다.

StringBuilder sb = new StringBuilder("Hello");
sb.append(" World");  // 기존 객체에서 문자열 추가 (빠름)

 

 

 

• 버퍼(Buffer): 데이터를 임시 저장 후 한꺼번에 처리 → I/O 성능 향상
• 빌더(Builder): 가변 객체를 활용하여 기존 데이터 수정 → 문자열 처리 속도 향상
• 멀티스레드 환경: StringBuffer 사용 (동기화 지원)
• 싱글스레드 환경: StringBuilder 사용 (속도 빠름)

---

※ 인덱스 처리

 : 데이터베이스에서 데이터를 빠르게 검색하고 성능을 최적화하기 위한 기술

 

-- 1. 원본 테이블 생성
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    Amount DECIMAL(10,2)
);

-- 2. 인덱싱된 뷰 생성
CREATE VIEW SalesView WITH SCHEMABINDING AS
SELECT ProductID, SUM(Amount) AS TotalSales
FROM dbo.Sales
GROUP BY ProductID;

-- 3. 인덱스 추가
CREATE UNIQUE CLUSTERED INDEX IDX_SalesView ON SalesView(ProductID);

 

CREATE UNIQUE CLUSTERED INDEX를 추가하여 뷰의 결과를 미리 저장하고 빠르게 조회 가능

일반적인 뷰는 실행될 때마다 쿼리가 실행되지만, 인덱싱된 뷰는 결과를 저장하므로 성능 향상 효과가 있다.

 

 

- 인덱스가 없는 경우

 : 데이터를 찾기 위해 테이블의 모든 행을 처음부터 끝까지 검색해야 하기 때문에 데이터가 많아질수록 속도가 느려지고 성능이 저하

 

- 인덱스가 있는 경우

 : 인덱스를 생성하면 데이터가 정렬된 상태로 저장되므로 원하는 데이터를 빠르게 찾을 수 있다.

   데이터베이스는 B-Tree(Balanced Tree) , Hash Table 같은 자료구조를 사용하여 검색을 최적화 한다.

 

-- 인덱스 생성
CREATE INDEX idx_emp_name ON employees(emp_name);

-- 인덱스를 활용한 검색 (더 빠름)
SELECT * FROM employees WHERE emp_name = 'Kim';

 

 : 인덱스(idx_emp_name)를 먼저 검색하여 "Kim"이 저장된 위치를 찾음

 

 

※ 인덱스 종류

 

① B-Tree

 : 가장 일반적인 인덱스 구조. 데이터를 균형 트리 구조로 저장하여 검색 성능을 최적화

CREATE INDEX idx_customer ON customers(customer_name);

 

② 해시 인덱스(Hash Index)

 : 해시 테이블을 기반으로 특정 키 값을 빠르게 찾을 때 사용 (하지만 범위 검색이 불가능하고, 특정 값 검색에만 적합)

 : 검색 시 트리 탐색 (Index Seek) 을 사용하여 빠르게 조회

 : WHERE, ORDER BY, JOIN 등에서 성능 향상

CREATE INDEX idx_orders ON orders(order_id) USING HASH;

 

③ 클러스터형 인덱스 (Clustered Index)

 : 테이블 데이터 자체를 정렬하여 저장하는 방식으로 테이블 데이터가 인덱스 순서대로 저장되므로 검색 속도가 더 빠름

CREATE TABLE products (
    product_id INT PRIMARY KEY,  -- 기본 키에 클러스터형 인덱스 자동 생성
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

 

④ 비클러스터형 인덱스 (Non-Clustered Index)

 : 클러스터형 인덱스와 다르게 데이터가 따로 저장되며, 인덱스가 데이터 위치를 가리키는 방식

 : 검색 후 해당 데이터의 위치를 찾아서 테이블에서 조회

CREATE INDEX idx_product_name ON products(product_name);

 

 

※ 인덱스가 효과적인 경우

✔ WHERE 절에서 자주 조회하는 컬럼
✔ JOIN 연산 시 사용되는 컬럼
✔ ORDER BY, GROUP BY 연산을 많이 수행하는 컬럼

 

데이터가 적거나, 자주 insert 등 업데이트 되거나 where조건이 인덱스 사용을 방해할 때는 인덱스가 비효율적이다.

SELECT * FROM employees WHERE CAST(salary AS VARCHAR) = '5000';  
-- 타입 변환 시 인덱스 미사용