DB

펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [7~12] With GPT

99duuk 2025. 2. 9. 18:43

https://blog.lael.be/post/370

 

MySQL 쓰면서 하지 말아야 할 것 17가지

*MySQL 쓰면서 하지 말아야 할 것 17가지* 권장사항이다. 이것을 이해하면 당신의 어플리케이션이 더 나은 성능을 발휘할 것이다. 다만 이것이 사람의 실력을 판단하는 척도로 사용되서는 안 될 것

blog.lael.be

 

7. 인덱스 레이아웃 이해 못 하는 것

- 제대로 인덱스랑 스토리지 엔진 선택하려면 공부 좀 해

인덱스란? 

     - DB에서 데이터 빠르게 찾기 위한 자료구조

     - 어떤 인덱스를 사용하느냐에 따라 성능 차이가 큼

     - 스토리지 엔진에 따라 인덱스 구현 방식이 다름 ( MyISAM vs InnoDB 등)

 

스토리지 엔진별 인덱스 방식 비교

엔진 기본 인덱스 구조 특징
InnoDB  Clustered Index (B+Tree 기반) PK 기반 정렬, 데이터와 인덱스가 함께 저장
MyISAM Non-Clustered Index (B+Tree 기반) 인덱스만 저장, 데이터는 별도로 관리됨

 

 

왜 인덱스 이해해야 하는겨?

     - 잘못된 인덱스 사용 시 쿼리 성능 크게 저하

     - 읽기 최적화 vs. 쓰기 최적화를 구분해야함

     - InnoDB는 PK 기반으로 정렬되므로, PK를 신중하게 선택해야 함

단순 인덱스 추가보다 "어떻게 저장되는지"를 이해가 중요.

 

 


- 엔진은 데이터와 인덱스 레코드를 메모리나 디스크에 레이아웃하는 걸 구현한 것

"레코드를 메모리나 디스크에 레이아웃" 

    - 레이아웃 ->  데이터와 인덱스를 물리적으로 어떻게 배치하는지 결정하는 방식

          - 메모리 레이아웃 : 데이터를 RAM에 배치해 빠르게 접근할 수 있도록 함 (ex: MEMORY 엔진)
          - 디스크 레이아웃 : 데이터를 디스크에 배치하여 영구 저장하지만, 접근 속도는 느림

 

스토리지 엔진에 따른 인덱스 저장 방식

     - MySQL은 스토리지 엔진별로 데이터를 저장하는 방식이 다름

     - InnDB와 MyISAM은 동일한 B+Tree 인덱스를 사용하지만, 저장 방식이 다름

엔진 저장 방식 특징
InnoDB (Clustered Index) 데이터와 인덱스가 함께 저장 빠른 조회 (PK 기반 정렬)
MyISAM (Non-Clustered Index) 인덱스와 데이터가 별도로 저장 빠른 INSERT(인덱스가 가벼움)



     - 인덱스는 단순한 목록이 아니라, "데이터 저장 구조"를 결정하는 요소임

     - 인덱스를 이해하지 못하면 성능 최적화가 불가능

차이를 알고 적절한 스토리지 엔진을 선택해야 함


- clustered 구성은 데이터를 PK 순서에 따라 저장함.

Clustered Index란?

     - PK를 기준으로 데이터를 정렬하여 저장

     - PK가 곧 인덱스이며, 데이터 자체도 PK 순서로 정렬됨

     - InnoDB는 기본적으로 Clustered Index를 사용 

 

Clustered Index의 특징

     - PK를 이용한 검색이 빠름 (데이터가 PK 순서대로 정렬됨)

     - PK를 수정하면 데이터 재배열이 필요하여 성능 저하 가능 

 

CREATE TABLE users (
    id INT PRIMARY KEY,   -- PK가 자동으로 Clustered Index로 설정됨
    name VARCHAR(100)
) ENGINE=InnoDB;

# PK 조회 성능 굿, PK 변경 자주 발생하면 성능 저하될지도

 

- non-clustered 구성은 인덱스만 순서대로 저장하고 데이터는 순서 가정하지 않음.

Non-Clustered Index란?

     - 인덱스와 데이터가 분리되어 저장

     - MyISAM 엔진이 기본적으로 사용

 

Non-Clustered Index의 특징

     - 인덱스와 실제 데이터가 별도로 저장

     - PK가 아닌 컬럼에 대해 여러 개의 인덱스를 만들 수 있음

     - 조회 시 인덱스를 조회한 후, 실제 데이터를 다시 읽어야 하는 "Bookmark Lookup" 발생 가능

 

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    INDEX (name) -- Non-Clustered Index 생성
) ENGINE=MyISAM;

# 다양한 조회에 유리 ! (Clustered는 pk기반 조회에 강하지만)


- clustered에서는 인덱스만 타면 추가적인 조회 없이 바로 데이터 가져오는 것임.

Clustered Index는 왜 빠른가?

     - PK가 곧 인덱스이므로, PK를 조회하면 추가적인 데이터 검색이 필요 없음

 

 하지만 Secondary Index를 사용할 경우 PK를 통해 다시 데이터 찾아야 하는 추가 비용 발생하므로 주의 필요

EXPLAIN SELECT * FROM users WHERE id = 10;
id select_type table type possible_keys key rows Extra
1 SIMPLE users const PRIMARY PRIMARY 1 Using Index

 

- 그래서 clustered PK는 작은 놈으로 할 필요가 있다는거

PK가 클수록 문제되는 이유?

     - PK 순서대로 데이터를 정렬하므로, PK 크기가 클수록 정렬 및 조회 속도가 느려짐

     - PK 크기가 크면, 모든 Secondary Index도 불필요하게 커짐

 

# PK를 UUID로 사용 - 비효율적
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,  -- UUID를 사용하면 인덱스 크기가 커짐
    name VARCHAR(100)
) ENGINE=InnoDB;


# PK 크기가 작을수록 좋음 (INT, BIGINT 등 사용 권장)

* 다른 인덱스는 각 레코드마다 PK를 앞에 더 붙이게 되니까.

Secondary Index(보조 인덱스)는 PK를 포함함

     - InnoDB에서 보조인덱스 사용하면 PK이 자동으로 포함됨

     - 즉, 인덱스 크기가 커지므로 PK 크기를 신중히 선택해야..

 

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    INDEX (email) -- Secondary Index
) ENGINE=InnoDB;

# Secondary Index(email)에는 PK(id)도 포함됨 -> 인덱스 크기 증가

* PK 지정 안 하면 아무렇게나 해버림

PK 지정 안하면? (InnoDB 경우 PK 명시하지 않으면 내부적으로 자동 생성된 ROW ID를 이용해 Clustered Index 구성함)

CREATE TABLE users (
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE
) ENGINE=InnoDB;

# 문제 없이 실행됨 
# PK 없으므로 ROW ID 생성 (삽입 순서대로) -> 데이터 정렬 순서 예측 불가

     - PK를 이용한 검색이 빠름 (데이터가 PK 순서대로 정렬됨)

     - PK를 수정하면 데이터 재배열이 필요하여 성능 저하 가능 

8. 쿼리 캐시 이해 못 하는 것

- 어플리케이션 read/write 비율은 알고 있어야지

쿼리 캐시가 효과적인 경우와 비효율적인 경우

     - 쿼리캐시는 주로 READ 성능을 향상시키는 역할

     - WRITE 작업이 많으면 캐시 무효화가 자주 발생하여 오히려 비효율적

          *우리 시스템의 읽/쓰 비율이 어떤지? 분석이 선행되어야...

 

서비스 유형 Read 비율  Write 비율 캐시 활용 가능성
뉴스 사이트 95 %  5 % 가능
전자상거래 80 %  20 % 부분 가능
SNS 60 % 40 % 제한적 캐싱
실시간 게임 서버  10 % 90 % 비효율적

 

- 쿼리 캐시 설계는 CPU 사용과 읽기 성능 간의 타협

쿼리 캐시가 CPU에 미치는 영향

     - 캐시를 사용하면 읽기 성능 향상되지만, 캐시 관리하는 CPU 오버헤드가 발생

     - 캐시를 무효화할 때 CPU 사용량이 증가할 수 있음

 

CPU 사용량 vs. 캐시 효율성

캐시 크기 CPU 사용량 성능 영향
작음 (10MB 이하) 낮음 자주 삭제됨 (효율 낮음)
중간 (100MB ~ 500MB) 적절함 적절한 성능
크게 설정 (1GB 이상) 높음 캐시 무효화 시 CPU 부하 증가

 *무조건 크게 설정한다고 좋은 게 아님. cpu오버헤드 고려해야..

 

캐시 사용시 CPU 문제를 피하려면 ?

     - 짧은 TTL 설정 -> 불필요한 캐시 유지 방지

     - LRU 사용 -> 오래된 캐시 자동 삭제

     - 읽기 집중적인 데이터만 캐싱 

- 쿼리 캐시 크기를 늘린다고 읽기 성능이 좋아지는게 아님. heavy read라도 마찬가지.

캐시 크기가 커지면 왜 성능이 향상 안되는데 ?

     - 캐시 크기가 커질수록, MySQL이 캐시에서 데이터 찾는 비용(검색 오버헤드)이 증가

     - 캐시를 무효화할 때 더 많은 데이터를 삭제 필요 -> CPU 부하 증가

 

query_cache_size=1024M  # ❌ 쿼리 캐시 크기를 무조건 크게 설정하면 성능 저하 가능
query_cache_size=128M  # ✅ 적절한 캐시 크기 유지
query_cache_limit=1M  # ✅ 너무 큰 결과는 캐싱하지 않도록 제한


- 과도한 CPU 사용을 막기 위해 무효화 할 때는 캐시 항목들을 뭉텅이로 날려버림

쿼리 캐시는 어떻게 무효화함 ?

     - 데이터가 변경될 때 무효화 됨 (INSERT, UPDATE, DELETE 실행 시)

     - 개별 항목이 아닌 "관련된 모든 캐시를 한꺼번에 삭제"하는 방식

SELECT * FROM products WHERE category = 'electronics';  -- 캐싱됨

UPDATE products SET price = 100 WHERE id = 10;  -- ⚠️ 해당 테이블의 모든 캐시 삭제됨

products 테이블의 단일 행만 업데이트해도, 전체 products 테이블 캐시가 삭제됨 -> 캐시 활용성 낮아질 수도 있음

  *간단하지만 캐시 활용도를 낮추는 단점..


- 한마디로 SELECT가 참조하는 테이블 데이터 하나라도 변경되면 그 테이블 캐시는 다 날라간다는 얘기임

MySQL 쿼리 캐시는 비효율적인 무효화 방식을 사용

     - 테이블 내 일부 데이터만 변경되어도, 해당 테이블과 관련된 모든 캐시가 삭제

     - 변경이 잦은 테이블에는 쿼리 캐시를 적용하기 어려움

 

SELECT * FROM users WHERE age > 20;  -- 캐싱됨

INSERT INTO users (name, age) VALUES ('Alice', 25);  -- ⚠️ 모든 users 관련 캐시 삭제됨

  *읽기 성능 향상을 위해 캐싱했지만, INSERT/UPDATE가 발생하면 전체 삭제됨.

    -> 변경이 거의 없는 정적인 데이터를 대상으로 해야 효과적

- 수직 테이블 파티셔닝으로 처방

테이블 파티셔닝이란?

     - 데이터를 여러 개의 테이블로 나누어 관리

     - 자주 변경되는 데이터와 변경이 적은 데이터를 분리하면 캐시 효율 증가


* Product와 ProductCount를 쪼갠다든지..

products : 변경 적음 -> 캐싱 가능

products_counts : 변경 잦음 -> 캐싱 비효율적, 별도로 관리

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE product_counts (
    product_id INT PRIMARY KEY,
    view_count INT,
    stock INT
) ENGINE=InnoDB;


* 자주 변하는 것과 변하지 않는 것을 쪼개는게 중요하다 이 말임.

- 제품 정포(products)는 변경이 적으므로 캐시 가능
- 재고(stock)나 조회수(view_count)는 자주 변경

 

    => 쿼리 캐시 무효화 영향 최소화 

9. Stored Procedure를 쓰는 것

- 무조건 쓰면 안 된다는게 아니고..

Stored Procedure(저장 프로시저, SP)는 언제 유용할까?

     - 자주 실행되는 로직을 데이터베이스에 저장하여, 어플리케이션이 SQL을 직접 실행하지 않고 DB에서 실행할 수 있도록 함

     - 복잡한 연산을 한 번에 실행할 때 유용

     - 하지만, 잘못 사용하면 성능 저하 및 유지 보수 문제 발생 가능

 

저장 프로시저 사용 시 주의점

     1. 모든 로직을 SP로 만들면 코드 유지보수가 어려워진다.

     2. DB 부하 증가 -> 어플리케이션에서 처리할 수 있는 것은 어플리케이션에서 처리하는 것이 더 유리함

     3. SP는 DB 종속성이 높아져, DBMS를 변경할 때 큰 문제 발생

 

*무조건 사용하기보다는, 필요할 때만 전략적으로 활용해야..

- 컴파일 할 때 무슨 일이 일어나는지 이해 못 하고 쓰면 재앙이 된다 이 말.

컴파일 시에 무슨 일이 ?

     - SP는 한 번 생성되면 MySQL 내부적으로 "컴파일된 형태"로 저장됨 (한 번 컴파일 후 캐싱 ㄴㄴ)

     - 하지만, MySQL은 다른 DBMS와 다르게 SP를 실행할 때마다 실행 계획을 다시 세움

     - 즉, 캐싱된 실행 계획을 재사용하지 않으므로, 매번 실행 계획을 다시 생성하는 오버헤드 발생

 

이로 인해 발생하는 문제

     1. 자주 실행되는 SP는 실행 계획을 계속 다시 계산해야 하므로 성능 저하

      2. MySQL의 옵티마이저가 매번 실행 계획을 새로 잡아야 하므로 CPU 낭비 증가

 

 

- 다른 RDBMS랑 다르게 connection thread에서 실행 계획이 세워짐.

MySQL의 SP 실행 방식

     - Oracle, PostgreSQL은 SP 실행 계획을 한 번 세우고 캐싱할 수 있음

     - 그러나 MySQL은 각 Connection Thread에서 실행 계획을 개별적으로 세우므로, 같은 SP라도 매번 실행 계획을 다시 만들게 됨

     - 즉, 동일한 SP라도 실행할 때마다 실행 계획을 새로 만들고, 이를 최적화하지 않으면 성능 저하 발생 ! 

 

# 실행 계획이 매번 새롭게 세워지는 경우
CREATE PROCEDURE get_user_by_email(IN user_email VARCHAR(255))
BEGIN
    SELECT * FROM users WHERE email = user_email;
END;

# 실행할 때마다 실행 계획이 새롭게 세워짐 (재사용 X)


- 이게 뭔 얘기냐 하면 데이터 한 번 가져오고 연결 끊으면 그냥 CPU 낭비 (7~8% 정도)하는 꼴이라는 것.

MySQL SP는 Connection Thread에서 실행됨

     - SP 실행 중에는 Connection Thread가 유지되며, 실행 계획을 다시 세움

     - 즉, 한 번 실행한 후 바로 연결을 끊으면, 다음 실행 때 실행 계획을 잡아야 하므로 CPU 사용량이 증가

     - 특히 짧은 트랜잭션이 많을 경우, SP를 남용하면 불필요한 CPU 오버헤드 발생할 수 있음

 

 CPU 낭비를 줄이려면?

     1. 짧고 자주 실행되는 쿼리는 Prepared Statement를 사용하는 것이 유리

     2. SP를 실행할 때 여러 개의 로직을 한 번에 처리하여, 반복 실행을 줄이는 것이 중요

 

// SP 대신 Prepared Statement 사용 예

String sql = "SELECT * FROM users WHERE email = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "test@example.com");
ResultSet rs = stmt.executeQuery();

* Prepared Statement:  SQL 쿼리를 미리 컴파일하고 실행 계획을 저장하여, 동일한 쿼리를 여러번 실행할 때마다 성능을 최적화하는 방법 

     - SQL실행 계획을 한 번만 생성하고 재사용 가능

     - SQL Injection 방지, 

     - 매번 SQL 파싱하지 않고, 변수만 바꿔서 실행 가능 -> 성능 최적화 

 

- 웬만하면 Prepared 구문과 Dynamic SQL을 써라.. 아래 경우를 제외하고

Dynamic SQL

       일반적인 (Static) SQL : SQL문이 컴파일(Prepare)될 때 고정됨

       다이나믹 SQL : 실행될 떄 SQL을 동적으로 생성하여 실행

 *<if>, <choose>, <where> 같은 거 써서 동적 쿼리 만드는 거랑 같은 개념

 

* ETL 타입 프로시저

ETL 타입 프로시저란?

     - 대량의 데이터를 추출(Extract), 변환(Transform), 로드(Load)하는 작업을 수행하는 프로시저

     - 주로 배치 작업에서 사용

     - 애플리케이션보다 데이터베이스에서 실행하는 것이 더 빠름 (네트워크 오버헤드 감소)

 

ETL 작업에 SP가 적합한 이유

     - 애플리케이션에서 데이터를 여러 번 가져와 처리하는 것보다, DB 내부에서 직접 변환하면 속도가 훨씬 빠름

    

대량 데이터 변환 최적화

     - 트랜잭션 최소화하여 성능 향상시킬 수 있음

     - 애플리케이션에서 한 줄씩 처리하는 것보다

     - DB에서 한 번에 처리하는 것이 성능적으로 훨씬 유리

 

ex: 대량 데이터 처리

DELIMITER //
CREATE PROCEDURE update_user_status()
BEGIN
    UPDATE users 
    SET status = 'inactive'
    WHERE last_login < NOW() - INTERVAL 1 YEAR;
END //
DELIMITER ;

# 1년 이상 로그인하지 않은 사용자의 상태를 한 번에 업데이트
# 애플리케이션에서 수천 번의 UPDATE를 실행하는 것보다 DB에서 한 번에 처리하는 것이 효율적


* 아주아주 복잡하지만 자주 실행되지는 않는 것

     - 쿼리가 너무 복잡해서 애플리케이션에서 관리하기 어렵다면 SP로 관리하는 것이 좋음

     - 하지만, 자주 실행되는 경우에는 실행 계획 문제로 성능 저하 가능

     - 즉, 실행 빈도 낮으면서도 매우 복잡한 로직이라면 SP가 유리할 수 있음.

 

ex: 복잡한 데이터 분석 쿼리

DELIMITER //
CREATE PROCEDURE get_top_customers()
BEGIN
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING total_spent > 10000
    ORDER BY total_spent DESC;
END //
DELIMITER ;

# 고객별 총 구매 금액을 계산하고, 일정 금액 이상 구매한 고객만 필터링
# 이런 쿼리는 실행 빈도가 낮고, 한번 실행할 때 여러 개의 테이블을 조인해야 하므로 SP가 유리

*자주 실행되지 않는, 매우 복잡한 로직을 SP로 만들면 유지보수가 쉬워지고 성능도 최적화 ㄱㄴ

* 벗(친구 아니고 but), 자주 실행되는 복잡한 로직이라면 SP 보다 Prepared Statement + 캐싱 고려하는 것이 더 나을 수도 있따고 함...

 

* 한 번 요청할 때마다 여러번 실행되는 간단한 것 (연결한 상태로 여러번 써야 된다니까)

     - 하나의 트랜잭션 내에서 여러 번 실행해야 하는 경우

DELIMITER //
CREATE PROCEDURE update_multiple_orders(IN user_id INT, IN new_status VARCHAR(50))
BEGIN
    UPDATE orders SET status = new_status WHERE customer_id = user_id;
END //
DELIMITER ;

 

     - 애플리케이션에서 여러 번 SQL 날리는 것보다, SP로 한 번에 묶어서 실행하는 것이 효율적

# 애플리케이션에서 여러 번 실행 

String sql = "UPDATE orders SET status = ? WHERE customer_id = ?";
PreparedStatement stmt = conn.prepareStatement(sql);

stmt.setString(1, "shipped");
stmt.setInt(2, 1001);
stmt.executeUpdate();

stmt.setString(1, "delivered");
stmt.setInt(2, 1001);
stmt.executeUpdate();

* SQL 여러 번 실행해야 하므로 네트워크 왕복(Round Trip)이 많아지고 비효율적

 

# SP 사용 - 한 번 호출 시 여러 개의 SQL이 한 번에 실행되므로 성능 최적화 가능

CallableStatement stmt = conn.prepareCall("{CALL update_multiple_orders(?, ?)}");
stmt.setInt(1, 1001);
stmt.setString(2, "shipped");
stmt.execute();

* 트랜잭션 관리가 쉬워지고, 네트워크 왕복 횟수(Round Trip)가 줄어들어 성능 향상 가능 

 

10. 인덱스 컬럼에 함수 쓰는 것

인덱스 컬럼에 함수 사용하면 인덱스를 탈 수 없으며,

함수의 결과를 상수로 만든 후 비교해야 인덱스를 탈 수 있다.

LIKE 검색도 특정 조건에서는 Range Type Index Scan을 사용할 수 있음

- 함수에 인덱스 컬럼 넣어 호출하면 당연히 인덱스 못 탄다

MySQL은 인덱스를 활용할 때 컬럼 값 그대로 비교해야 인덱스를 탈 수 있음

하지만 함수를 사용하면 MySQL이 인덱스를 사용할 수 없고, 모든 데이터를 읽은 후 필터링해야 함 -> Full Table Scan

 

ex: 함수 사용으로 인덱스를 못타는 경우

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    created_at DATETIME,
    INDEX idx_created_at (created_at) -- created_at 컬럼에 인덱스 생성
);

SELECT * FROM users WHERE YEAR(created_at) = 2024;

- 'YEAR(created_at) = 2024' 는 created_at 컬럼에 함수를 적용했기 때문에 인덱스를 사용할 수 없다. 
- MySQL은 created_at 전체 데이터를 스캔한 후 YEAR() 함수를 적용해서 필터링해야 한다. (Full Table Scan 발생)

     => 모든 행을 다 읽고 YEAR() 적용


- 함수를 먼저 계산해서 상수로 만든 다음에 = 로 연결해야 인덱스 탈 수 있다.

YEAR(created_at) = 2024 처럼 컬럼을 함수의 입력값으로 넣으면 인덱스를 탈 수 없음. 

그러나 함수를 먼저 계산하여 상수 값으로 변환하면 인덱스를 활용할 수 있음

SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';

created_at의 인덱스를 먼저 사용하여 범위 검색을 수행 

즉, 2024-01-01 ~ 2024-12-31 범위 안의 레코드만 인덱스를 활용하여 빠르게 조회하고, 이후 필터링 없이 바로 결과를 반환!! 

--> index range scan 가능! (인덱스 효율적 활용)

     => 인덱스 활용해 필요한 범위만 조회

 

* 여기 실행 계획 보면 LIKE도 range type 인덱스 타는 것 보임

ex: LIKE를 범위 검색으로 대체 

SELECT * FROM employees WHERE name >= 'John' AND name < 'Joni';

 

LIKE 'John%' 대신 BETWEEN 또는 >= , < 범위 조건을 사용하면 인덱스를 확실히 탈 수 있음

     -> idx_name 인덱스 활용해 빠른 검색! 

 

LIKE에서 인덱스를 활용하려면 앞부분이 고정된 패턴을 사용하거나, 범위 검색으로 변환하는 것이 좋음!

 

11. 인덱스 빼먹거나 쓸모없는 인덱스 만들어 놓는 것

- 인덱스 분포도(selectivity)가 허접하면 안 쓴다.

인덱스 분포도(Selectivity)는 테이블 내에서 중복되지 않은 값의 비율을 의미함.

분포도가 낮은(중복이 많은) 컬럼에 인덱스를 걸면 비효율적이라 MySQL이 인덱스를 사용하지 않을 수도 있음

일반적으로 고유한 값이 많을 수 록 인덱스 효율이 높음.

 

ex: 분포도 낮아 인덱스 못타는 경우

CREATE TABLE employees (
    id INT PRIMARY KEY,
    department VARCHAR(50),
    INDEX idx_department (department) -- 부서 컬럼에 인덱스 생성
);

SELECT * FROM employees WHERE department = 'HR';

department에는 HR, Sales, IT 등 소수의 값만 존재함.

즉, department 값이 너무 중복되면 인덱스를 활용하는 것보다 풀 스캔이 더 빠를 수도 있음

MySQL은 분포도가 낮은 컬럼의 인덱스를 무시하고 풀스캔을 수행할 가능성이 있음

 

해결책 ==> 중복이 적고 고유한 값이 많은 컬럼에 인덱스 설정해야 함!!

 

- S = d/n

* d = 서로 다른 값의 수 (# of distinct values)
* n = 테이블의 전체 레코드 수

일반적으로 S 값이 0.1 이상(10% 이상 다른 값이 존재)이면 인덱스가 유용하다.

하지만 S 값이 너무 낮으면 중복이 많아 인덱스를 사용해도 이점이 적음 -> 풀스캔이 더 빠를 수도 있음.

 

위의 예를 이어 사용하면 (ex: 분포도 너무 낮아 인덱스 못타는 경우)

 

department에는 HR, Sales, IT 등 소수의 값만 존재함.

  d(서로 다른 값 수) = 3 
  n(전체 레코드 수) = 1,000,000 (100만 개)

  S = 3 / 1000000 = 0.000003 -> 너무 낮음

S 값이 낮아 MySQLdl 인덱스를 사용하지 않고 풀스캔 수행할 가능성이 높음  

 

ex: 분포도가 높은 경우 (좋은 인덱스 활용)

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    INDEX idx_email (email) -- 이메일 컬럼에 인덱스 생성
);

SELECT * FROM customers WHERE email = 'user@example.com';

email 값은 거이 모두 다르다. (S ≈ 1.0), 즉, 고유한 값이 많으므로 인덱스를 활용하기 적합함! 

     => 분포도가 낮은 (중복이 많은) 컬럼에는 인덱스를 걸지 않는 것이 좋음!


- 쓸모없는 인덱스는 INSERT/UPDATE/DELETE를 느리게 할 뿐..

인덱스가 많아지면 INSERT, UPDATE, DELETE 연산 시 모든 인덱스를 갱신해야 하므로 성능이 저하됨.

자주 변경되는 컬럼에는 불필요한 인덱스를 만들지 않는 것이 좋음.

 

ex: 불필요한 인덱스가 많아 성능이 저하되는 경우

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_status VARCHAR(20),
    created_at DATETIME,
    INDEX idx_user (user_id),
    INDEX idx_status (order_status),
    INDEX idx_created (created_at)
);

user_id, order_status, created_at에 인덱스를 모두 걸어놓음.

- order_status는 "pending:, "shipped", "delivered" 등 소수의 값만 존재하여 인덱스가 필요함.

- created_at은 자주 조회될 경우 유용하지만, INSERT 마다 인덱스를 갱신해야 하므로 성능이 저하될 수 있음.

 

- FK는 무조건 인덱스 걸어라. (물론 FK 제약 걸면 인덱스 자동으로 생긴다.)

외래키는 부모 테이블의 기본 키를 참조하므로, 해당 컬럼에 인덱스가 없으면 조인이 매우 비효율적임.

MySQL 기본적으로 외래 키를 설정하면 자동으로 인덱스를 생성하지만,

 

일부 경우 (특히 InnoDB가 아닐 때)는 수동으로 인덱스를 걸어줘야 할 수도 있음. 

일반적으로 자동 생성되지만, 확인하는 것이 좋음.

 

- WHERE나 GROUP BY 표현식에서 쓰이는 컬럼은 인덱스 추가를 고려할 것

WHERE, GROUP BY 절에서 자주 사용되는 컬럼은 인덱스를 걸면 성능이 향상됨

 

ex: WHERE 조건을 빠르게 처리하기 위한 인덱스 추가 

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10,2),
    INDEX idx_sale_date (sale_date)
);

SELECT * FROM sales WHERE sale_date >= '2024-01-01';

sale_date 가 WHERE 조건에 자주 사용되므로 인덱스를 걸어주면 검색 속도가 향상됨.

 

ex: GROUP BY 최적화를 위한 인덱스 추가 

SELECT product_id, SUM(amount) 
FROM sales 
GROUP BY product_id;

GROUP BY product_id가 자주 사용된다면 product_id에 인덱스 추가하면 성능 향상됨


- covering index 사용을 고려할 것

커버링 인덱스는 SELECT 대상이 되는 컬럼이 모두 인덱스에 포함된 경우를 의미함.

쿼리 실행 시 테이블을 조회할 필요 없이 인덱스에서 바로 데이터를 가져올 수 있음 -> 속도 향상

 

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    INDEX idx_dept_salary (department, salary)
);

SELECT department, salary FROM employees WHERE department = 'IT';

department, salary 가 인덱스에 포함되어 있으므로 Covering Index가 적용됨. 

즉, MySQL이 테이블을 읽지 않고 인덱스에서 바로 데이터를 가져옴 

 

* 일반 인덱스 vs. 커버링 인덱스


일반적으로 인덱스를 타도 테이블 데이터를 가져오려면 추가적인 디스크 IO가 발생함.

(커버링 인덱스 사용하면 디스크 IO 줄고 쿼리 속도 극적 향상 ㄱㄴ)

 

ex: 일반 인덱스 사용 Extra: Using Index; Using Where)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    INDEX idx_department (department) -- 단일 인덱스
);

SELECT department, salary FROM employees WHERE department = 'IT';

 

WHERE department = 'IT' -> idx_department 인덱스를 탐색하여 해당 Row 찾음

BUT ! salary는 인덱스에 없으므로 테이블을 추가 조회 (디스크 IO발생)

Extra: Using index condition; Using where

Using index condition : 인덱스는 사용하지만, 데이터를 읽으려면 테이블 조회가 필요함.

 

 

CREATE INDEX idx_dept_salary ON employees(department, salary);

SELECT department, salary FROM employees WHERE department = 'IT';

department 와 salary 모두 인덱스(idx_dept_salary)에 포함

즉, 테이블을 조회할 필요 없이 인덱스에서 바로 결과 가져옴.

Extra: Using index

Using idex 만 표시되면 테이블을 조회하지 않고, 인덱스에서 직접 데이터를 가져왔다는 의미!!

∴ Covering Index를 사용하면 인덱스 자체를 테이블처럼 활용할 수 있음!


- 인덱스 컬럼 순서에 유의할 것!

복합 인덱스(Composite Index)의 컬럼 순서에 따라 성능이 크게 달라질 수 있음

일반적으로 WHERE 절에서 가장 자주 필터링되는 컬럼을 앞에 둠.

CREATE INDEX idx_dept_salary ON employees(department, salary);

where department = 'IT' AND salary > 50000 -> 인덱스 활용 가능

where salary > 50000 AND department = 'IT' -> department가 앞이라 인덱스 활용 어려움

 

=> WHERE 에서 가장 자주 필터링되는 컬럼을 앞에 배치해야 함.

 

12. join 안 쓰는 짓

- 서브쿼리는 join으로 재작성해라

서브쿼리는 각 행마다 실행되므로 성능이 매우 떨어질 수 있음

반면, JOIN을 사용하면 MySQL이 최적화하여 한번에 데이터를 가져올 수 있음

즉, 가능하면 서브쿼리 대신 JOIN을 사용하는 것이 성능 면에서 유리함.

 

ex: 서브쿼리 사용

SELECT 
    id, 
    (SELECT name FROM users WHERE users.id = orders.user_id) AS user_name
FROM orders;

orders 테이블의 각 행마다 users 테이블을 조회 --> N번 실행됨 (비효율적)

orders 테이블에 10,000개 행이 있다면, users 조회도 10,000번 실행됨.

 

ex: JOIN 사용 (효율적)

SELECT orders.id, users.name 
FROM orders 
JOIN users ON orders.user_id = users.id;

JOIN을 사용하면 한 번의 조회로 데이터 가져옴

users 테이블과 orders 테이블을 조인하면 MySQL이 내부적으로 최적화하여 빠르게 처리 

 


- 커서 제거해라

Cursor는 한 줄씩 데이터를 처리하는 방식, 대량의 데이터를 다룰 때 매우 비효율적임.

MySQL은 기본적으로 "집합(SET) 기반 처리"를 수행하므로, 커서보다 JOIN이나 GROUP BY를 활용하는 것이 좋음

 

ex: 커서 사용 경우

DECLARE done INT DEFAULT FALSE;
DECLARE user_id INT;
DECLARE cur CURSOR FOR SELECT id FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
REPEAT
    FETCH cur INTO user_id;
    IF NOT done THEN
        INSERT INTO logs (user_id, log_message) VALUES (user_id, 'Log entry');
    END IF;
UNTIL done END REPEAT;
CLOSE cur;

각 user_id에 대해 반복적으로 INSERT 실행 -> 성능 저하

users 테이블의 데이터가 많아질수록 비효율적

 

ex: 커서 대신 INSERT + SELECT 사용 (효율적)

INSERT INTO logs (user_id, log_message)
SELECT id, 'Log entry' FROM users;

한 번의 실행으로 모든 데이터를 삽입 -> 훨씬 빠름 

커서 없이 SQL의 기본적인 집합 연산을 활용하여 성능 개선


- 좋은 Mysql 성능을 내려면 기본

성능 최적화의 기본은 "한 번에 많은 데이터를 처리하는 방식"을 고민하는 것

한 행씩 가져와서 처리하는 방식(루프, 커서)은 성능이 나쁨.

가능하면 JOIN, GROUP BY, WHERE IN 같은 SQL 기능을 활용하여 한 번에 처리해야 함.

 

ex: 나쁜 방식 (비효율적 반복 처리)

SELECT * FROM users;
FOR EACH user IN users:
    SELECT COUNT(*) FROM orders WHERE user_id = user.id;

각 사용자의 주문 수를 활용하기 위해 반복적으로 orders 테이블을 조회함 -> 느림

 

ex: 좋은 방식 (JOIN + GROUP BY 활용)

SELECT users.id, users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

한 번의 실행으로 모든 데이터를 조회 -> 훨씬 빠름

 


- 집합 기반으로 생각해야지 루프 돌리는거 생각하면 안 된다.

프로그래밍에서는 일반적으로 반복문 (Loop)을 사용하여 데이터를 처리하지만, 

SQL에서는 집합(SET)연산을 활용하여 한 번에 데이터를 처리하는 것이 더 효율적!!

 

ex: 반복문 사용 

SELECT * FROM users;
FOR EACH user IN users:
    UPDATE users SET last_login = NOW() WHERE id = user.id;

각 사용자마다 UPDATE 문 날아감 -> N번 실행되므로 느림

 

ex: 집합 연산 사용 (효율적)

UPDATE users SET last_login = NOW();

한 번의 실행으로 모든 데이터를 업데이트 -> 훨씬 빠름

 

루프 기반 사고방식 대신, SQL의 집합 연산을 활용할 것...

 

 

 

 

'DB' 카테고리의 다른 글

펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [13~17] With GPT  (0) 2025.02.09
펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [1~6] With Claude  (1) 2025.02.09
Mysql PK 생성전략 (+Clusted Index, PostgreSQL Serial)  (0) 2025.02.07
mariadb 컨테이너 exec -it 로 접근 안될때  (0) 2025.02.07
MySQL root 비밀번호 재설정  (2) 2024.10.23

'DB'의 다른글

  • 현재글펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [7~12] With GPT

관련글

티스토리툴바