[CS/DB] 스터디 week6

2rlokr·2025년 5월 5일

cs-knowledge

목록 보기

6/12

Velog에게 뒷통수 2번째... 열심히 작성한 내용... 날리지마라.. 저장 좀 잘 해라... DB 스터디 하는데 내용 두 번이나 날라가니까 기분 묘함. 근데 이번엔 그래도 다 날라간 거 아니고,,, 정말.. 한 주제에 일부라.. 봐준다. 진짜 화나게 흐즈므르...😡

👀 MySQL Scan

✏️ MySQL Scan의 종류에 대해 설명해주세요

MySQL에서 "Scan"이란 테이블에서 데이터를 읽어오는 방식 중 하나이며, 쿼리를 실행할 때 어떻게 데이터를 탐색(검색)하는지를 설명한다. 일반적으로 MySQL 옵티마이저는 가장 효율적인 접근 방식을 선택하려고 하며, 이때 다양한 Scan 방법이 사용된다.

🔍 1. Full Table Scan (전역 테이블 스캔)

테이블 전체를 순차적으로 스캔하는 방식이다.

✅ 설명
테이블의 모든 행을 처음부터 끝까지 훑는 방식이다. 인덱스가 없거나, 인덱스를 무시하는 조건이 있는 경우 사용된다.

✅ 특징
레코드가 많은 테이블의 경우, Full Table Scan은 성능 저하의 주범이 되는 경우가 많다.

✅ 언제 쓰이나?

인덱스가 없을 때
테이블의 row 수가 적어서 인덱스 사용하는 것보다 빠를 때
WHERE 절이 인덱스를 사용할 수 없을 때

✅ 예시

SELECT * FROM users WHERE age + 5 > 30;

위 쿼리는 age + 5처럼 컬럼에 연산이 들어가므로 인덱스를 사용하지 못하고 Full Table Scan 방식을 사용한다.

📂 2. Index Scan (인덱스 스캔)

테이블이 아니라 인덱스를 통해 데이터를 탐색하는 방식이다.

2-1. Index Full Scan (전체 인덱스 스캔)

인덱스를 전체 순회하지만 정렬된 순서로 데이터를 가져온다.
주로 ORDER BY와 함께 사용됨

✅ 예시:

SELECT age FROM users ORDER BY age;
인덱스를 통해 정렬된 데이터를 가져오므로 테이블을 정렬할 필요가 없어 성능이 좋습니다.

2-2. Index Range Scan (범위 인덱스 스캔)

2-3. Index Unique Scan

2-4. Loose Index Scan

2-5. Skip Index Scan

3. 커버링 인덱스

Lock, Deadlock

✏️ DB에서 사용되는 두 가지 Lock과 Deadlock에 대해 설명해주세요

🔒 Lock (잠금)

여러 트랜잭션이 동시에 동일 데이터를 액세스할 때 데이터 무결성을 유지하고 충동을 방지하는 매커니즘

Lock은 여러 트랜잭션이 동시에 같은 데이터를 사용할 때 충돌을 막기 위해 데이터베이스가 특정 자원(행, 페이지, 테이블 등)에 걸어두는 보호 장치이다.

📌 Lock의 목적

데이터 무결성 유지
동시에 데이터가 깨지지 않도록 보장
다중 트랜잭션 간 충돌 방지

Lock의 종류

1️⃣ 공유 락 (Shared Lock, S-Lock)

💡 개념

읽기 전용 잠금 (read lock)
다른 트랜잭션들도 같은 행을 함께 읽을 수 있다. (쓰기는 불가하다.)
데이터 일관성을 유지하기 위해 주로 사용하며, SELECT 쿼리에서 사용한다.
하지만 누군가 쓰기(UPDATE, DELETE 등)를 하려고 하면 대기하거나 충돌이 발생한다.

예제

-- 트랜잭션 A
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- 트랜잭션 B
SELECT * FROM products WHERE id = 3 LOCK IN SHARE MODE;

트랜잭션 A는 products.id = 1에 공유 락을 건다.
다른 트랜잭션도 이 행을 읽는 건 가능 !
다만, 쓰기(X-lock)을 걸려고 하면 대기하거나 Deadlock이 발생한다.

참고

MySQL 8.0 이후엔 FOR SHARE

SELECT * FROM 테이블명 WHERE 조건 FOR SHARE;

2️⃣ 배타 락 (Exclusive Lock, X-Lock)

💡 개념

쓰기 전용 잠금 (write lock)
해당 데이터에 대해 다른 트랜잭션이 어떤 접근도 못 하게 막는다.
즉, 배타 락이 걸린 데이터는 다른 트랜잭션이 읽거나 쓸 수 없다.
데이터 수정 시 무결성을 보장하기 위해 사용하며 주로 UPDATE, DELETE 쿼리에서 사용된다.

예시

FOR UPDATE

-- 트랜잭션 A
SELECT * FROM products WHERE id = 1 FOR UPDATE;

트랜잭션 A는 products.id = 1에 배타 락을 건다.
다른 트랜잭션은 S-lock도 못 걸고, X-lock도 못 건다.
해당 리소스에 접근하길 원하는 트랜잭션은 A가 커밋하거나 롤백할 때까지 대기해야 한다.

UPDATE

-- Transaction 1: 배타 락 설정
START TRANSACTION;
UPDATE member SET first_name = 'Ga Eun'
WHERE first_name = 'gagle';

-- Transaction 2: 동일 리소스에 대해 배타 락 시도
(대기 상태)
START TRANSACTION;
UPDATE member SET first_name = '가은' WHERE
first_name = 'gagle';

MySQL에서는 UPDATE, DELETE 구문 사용 시 내부적으로 X-lock이 걸린다.
즉, Transaction 1에서 X-lock을 걸었기 때문에, Transaction 2는 대기하게 된다.

참고

MySQL의 Lock은 자동이다.
- SELECT → 기본적으로 락 ❌
- SELECT ... FOR SHARE, FOR UPDATE → 명시적 Lock
- UPDATE, DELETE → 내부적으로 X-lock

😵 DeadLock (교착상태)

서로 다른 트랜잭션이 서로의 Lock을 기다리다가 무한 대기 상태에 빠지는 상황으로, 두 트랜잭션 모두가 블로킹 상태에 진입하여 서로의 블로킹을 해결할 수 없는 상태이다.

Deadlock 예시 상황

설명

트랜잭션 A가 Coupon 테이블의 데이터에 대해 쓰기 잠금(X-lock)을 건다.

트랜잭션 B는 Coupon 테이블에 쓰기, 읽기가 불가하다.

트랜잭션 B가 Member 테이블의 데이터에 대해 쓰기 잠금(X-lock)을 건다.
트랜잭션 A가 Member 데이터에 읽기 시도를 한다. (S-lock 시도)

하지만, 트랜잭션 B가 커밋 혹은 롤백을 하지 않은 상태로 쓰기 잠금 상태에 있기 때문에 트랜잭션 A는 블로킹 상태에 진입한다.

트랜잭션 B가 Coupon 데이터에 읽기 시도를 한다. (S-lock 시도)

트랜잭션 A에 대해서 블로킹 상태에 진입한다.

🚨데드락 발생!
트랜잭션 A, B의 블로킹 상태는 상대 트랜잭션이 종료되어야 해결되는데 서로의 트랜잭션이 블로킹 상태이기 때문에 종료되지 않으므로 데드락 상태가 된다.

🔦 Deadlock 탐지

대부분의 데이터베이스 시스템은 주기적으로 데드락을 감지하기 위한 메커니즘을 가지고 있다.

1. Wait-For 그래프(Wait-For Graph)

데이터베이스의 트랜잭션과 자원 간의 의존성을 그래프로 표현

구성

노드 : 트랜잭션
간선 : T1 -> T2 (자원을 기다리는 상태)

조건
그래프에 사이클(순환)이 존재하면 데드락이 발생한 것

T1 → T2 → T3 → T1
# T1이 T2가 점유한 자원을 기다리고 있고, T2가 T3이 점유한 자원을 기다리고 있고,,,

→ 순환 구조 (사이클) → Deadlock 발생 !

대부분의 RDBMS (MySQL, Oracle, PostgreSQL 등)에서는 이 방식을 자동으로 사용한다.
발생 즉시, 시스템이 사이클을 감지하고 트랜잭션 하나를 강제 종료(rollback) 시킴

2. 타임아웃 기반 탐지

트랜잭션이 일정 시간 동안 잠금 대기 상태에 머무르면 데드락이 발생했다고 판단하고 자동으로 롤백한다.

innodb_lock_wait_timeout = 5  # MySQL에서 설정 가능

위 설정이면, 트랜잭션이 5초 이상 잠금 대기하면 강제 종료

❗ 단점

진짜 데드락이 아닌 경우에도 단순히 느린 쿼리가 실패할 수 있다.
적절하지 않은 타임아웃 설정은 정상 트랜잭션의 실패 가능성을 증가시킨다.

3. MySQL 동작 방법

MySQL InnoDB는 기본적으로 Wait-For Graph 기반 Deadlock 탐지 알고리즘을 사용한다.
즉, 사이클이 생기면 즉시 감지하고, 그 중 하나의 트랜잭션을 자동으로 ROLLBACK 한다.

🔒 MySQL에서의 Lock

✏️ MySQL에서 사용되는 스토리지 엔진 수준의 락에 대해 설명해주세요

🔑 레코드 락 (Record Lock)

테이블 레코드 즉, 특정 행에 잠금을 거는 것을 의미한다.

설명

SELECT ... FOR UPDATE, UPDATE, DELETE 등 트랜잭션이 DML 구문을 실행할 때 자동으로 거는 락이다.
인덱스를 사용하는 경우, 테이블의 레코드가 아닌 인덱스의 레코드를 잠근다.
다른 트랜잭션은 그 행에 대해 수정이 불가하다. (쓰기 불가)
적당한 인덱스가 없다면 모든 테이블의 레코드에 락을 걸고, 테이블을 풀스캔하면서 작업을 처리하게 된다.
- 그러면 동시성이 떨어지게 되므로, MySQL에서의 인덱스 설계는 중요하다.
레코드락 덕분에 여러 트랜잭션이 동시에 서로 다른 레코드에 접근할 수 있다.

예시

# member 테이블에서 last_name이'J'로 시작하는 구성원은 300명
SELECT COUNT(*) FROM member WHERE last_name LIKE 'J%';

# 그 중에서 first_name이 MangKyu인 사원은 1명
SELECT COUNT(*) FROM member WHERE last_name LIKE 'J%' AND first_name = 'MangKyu';

# member 테이블에는 last_name 컬럼만으로 구성된 인덱스 KEY idx_last_name(last_name)가 존재한다.
# 해당 구성원의 등록일을 오늘로 변경하는 쿼리를 실행해보자.
UPDATE member SET register_date = NOW() WHERE last_name LIKE 'J%' AND first_name = 'MangKyu';

MySQL은 테이블 레코드가 아닌 인덱스에 잠금을 걸기 때문에, 1개의 레코드에만 잠금을 거는 것이 아닌, 인덱스가 설정된 300개의 레코드에 잠금이 걸린다.
- 인덱스는 성(last_name)으로만 구성되어 있기 때문에, 해당 레코드를 갱신하기 위해서는 인덱스를 통해 검색되는 모든 레코드에 잠금을 걸게 된다.

🔑 갭 락 (Gap Lock)

레코드가 아닌 레코드와 레코드 사이의 간격을 잠금으로써 레코드의 생성, 수정 및 삭제를 제어한다.

설명

인덱스 범위 조건 중에서 실제 레코드를 제외하고, 데이터가 추가될 수 있는 범위에 걸리게 된다.
인덱스는 정렬된 순서로 존재하므로, 현존하는 레코드의 앞 뒤에 갭 락이 걸린다.

예시

num 테이블에 2,3이라는 2개의 인덱스 레코드가 존재한다.
1이상 5이하의 조건으로 데이터를 검색한다면, 현존하는 레코드인 2와 3에 걸리는 락 -> 레코드 락
아직 실존하지 않는 1, 4, 5가 추가될 수 있는 공간에 걸리는 락 -> 갭 락

📌 즉, 아직 존재하지는 않지만 지정된 범위에 해당하는 인덱스 테이블 공간을 대상으로 거는 잠금이다.

❌ 갭 락이 사용되지 않는 경우

데이터의 유일성이 보장되는 프라이머리 키(PK) 또는 유니크 인덱스에 의한 작업에서는 갭 락이 사용되지 않는다.
Phantom Read를 방지하기 위해 사용된다.

예시

SELECT * FROM users WHERE id = 3 FOR UPDATE;

id=3 라는 유일하고 정확한 인덱스 인덱스 엔트리만 잠근다.
id=3.1 이나 id=2.9 같은 값이 들어올 수 없다.

SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;
SELECT * FROM users WHERE age = 30 FOR UPDATE;

위와 같은 경우는 현재는 존재하지 않지만, 다른 트랜잭션이 age=25 라는 새로운 데이터를 추가할 수도 있다.
그렇게 되면 Phantom Read가 발생할 수 있기 때문에 갭 락을 거는 것이다.

🔑 넥스트 키 락 (Next Key Lock)

레코드 락과 갭 락을 합친 잠금으로, 갭 락은 단독으로 사용되기 보다는 넥스트 키 락의 일부로 함께 사용된다.

해당 행과 그 앞 갭까지 모두 잠근다.
REPEATABLE READ에서 기본적으로 사용된다.

예시

-- users 테이블, age 인덱스 있음
SELECT * FROM users WHERE age = 25 FOR UPDATE;

레코드 락으로 age=25의 행뿐만 아니라, 갭 락으로 그 앞뒤 인덱스 사이의 갭도 함께 잠긴다.

🔑 자동 증가 락 (Auto Increment Lock)

AUTO_INCREMENT 열을 갖는 테이블에 INSERT할 때 다음 번호 생성을 위한 내부 락

AUTO_INCREMENT 컬럼은 여러 레코드가 동시에 INSERT 되더라도 중복되지 않고 순차적으로 증가하는 일련번호를 제공하기 위해 내부적으로 테이블 수준의 잠금인 자동 증가 락(Auto Increment Lock)을 사용한다.
테이블 단위의 특별한 잠금으로, InnoDB에서는 보통 자동 증가 값 생성 시에만 짧게 락이 걸린다.
해당 락은 INSERT와 REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서만 사용된다.
자동 증가 락은 잠금을 최소화하기 위해 한 번 증가하면 절대 자동으로 줄어들지 않는다.
- 만약 AUTO_INCREMENT 값을 초기화하고 싶다면 아래의 쿼리를 사용해야 한다.

ALTER TABLE tablename AUTO_INCREMENT = 1

💉 SQL Injection

✏️ SQL Injection에 대해 설명해주세요

💉 SQL Injection이란?

SQL 인젝션은 웹 애플리케이션의 보안 취약점을 이용하는 공격 방식 중 하나이다. 이 공격은 애플리케이션의 데이터베이스 쿼리에 악의적인 SQL 코드를 "주입"하여 데이터베이스를 조작하거나 민감한 정보를 탈취하는 것을 목표로 한다.

SQL Injection은 입력값 검증을 제대로 하지 않거나, 동적 SQL 쿼리에서 사용자가 제공한 입력값을 바로 포함시키는 경우에 발생할 수 있다.

🔎 SQL Injection 상황

SELECT USER FROM USER_TABLE WHERE USERNAME= '[ID 입력값]' AND PASSWORD = '[PWD 입력값]';

👨‍💻 해커가 관리자의 아이디가 Admin인 것을 확인한 후, 다음과 같은 입력값을 보낸다면?

username = Admin`; --
password : random

그럼 다음과 같은 쿼리문이 완성된다.

SELECT USER FROM USER_TABLE WHERE USERNAME = 'Admin'; --' AND PASSWORD = 'random';

주석 처리 --에 의해 USERNAME만 일치하더라도 로그인이 가능하도록 변경되었고, 해당 방식의 공격을 제대로 예방하지 않으면 해커는 관리자 권한을 가진 계정으로 로그인할 수 있게 된다.

🗡️ SQL Injection 공격의 종류

1️⃣ 인라인 쿼리 인젝션

가장 기본적인 형태의 SQL 인젝션으로, 사용자 입력이 검증없이 직접 SQL 쿼리에 포함될 때 발생한다.

이처럼 입력한 문자 그대로를 그대로 쿼리문에 넣어줄 경우, 테이블에 관련된 모든 데이터를 탈취 당할수도 있고, 테이블에 있는 모든 데이터가 날라가는 등의 위험이 존재한다.

2️⃣ Union 기반 SQL 인젝션 (Union-based SQL Injection)

UNION SQL 연산자를 사용하여 여러 쿼리의 결과를 결합하는 방식으로, 추가적인 데이터를 조회할 때 사용한다.

Union Injection 성공 조건

Union하는 두 테이블의 컬럼 수가 같아야 한다.
데이터 형이 같아야 한다.

Board 테이블을 조회하는 쿼리문을 이용하여 사용자의 식별 정보를 가져오는 예시이다. Board 테이블 조회 쿼리문이 성공하면서, UNION 키워드로 USER 테이블의 데이터도 가져올 수 있게 된다.

공격자가 데이터베이스 구조를 모르는 상태에서 데이터베이스의 응답을 통해 정보를 추측하며 공격을 수행하는 방식이다. 블라인드 SQL 인젝션은 데이터베이스의 응답이 직접적으로 보이지 않을 때 사용한다.

데이터베이스로부터 특정한 값이나 데이터를 전달받지 않고, 단순히 참과 거짓의 정보를 알 수 있을 때 사용한다.

3️⃣-1️⃣ Boolean-based SQL Injection

위의 그림에서는 information_schema.tables에서 DB에 존재하는 모든 테이블의 정보를 가져오고, 여기서 첫 번째 테이블 이름을 조회하게 된다. 그리고, SUBSTR로 테이블 이름의 substring으로 첫 번째 글자만 추출하여 그 문자의 ASCII 값을 구한다.

결과적으로, 해당 조건이 참이될 땐, 쿼리는 성공적인 결과를 반환하고, 조건이 거짓이면 결과가 빈 결과가 되거나 페이지 상태가 다르게 나타난다.

이런식으로 반복적으로 조건이 맞는지 하나씩 물어보는 공격 방식이며, 공격자는 이 프로세스를 자동화 스크립트를 통해 단기간 내에 테이블 명을 알아낼 수 있다.

이 공격에 주로 사용되는 함수는 SLEEP 혹은 BENCHMARK이다. (MySQL 기준)
위의 그림은 현재 사용되고 있는 데이터베이스 이름의 길이를 추측하는 것이다.
현재 사용 중인 데이터베이스 이름의 길이가 1자인지 확인하고, 조건이 참이라면 2초 동안 대기하고, 조건이 거짓이면 SLEEP(2)도 실행되지 않는다.
이렇게 응답 시간을 통해 조건이 참인지 거짓인지 판단할 수 있다.

4️⃣ 에러 기반 SQL 인젝션

공격자가 의도적으로 에러를 발생시키는 SQL 쿼리를 전송하여, 데이터베이스의 에러 메시지를 통해 구조나 데이터를 파악하는 방식이다.

SQL의 잘못된 문법이나 자료형 불일치 등에 의해 DB가 알려주는 데이터베이스 오류 메시지에 의존하여 수행된다.
문법적으로 오류가 있는 쿼리를 DB에 요청했을 때 반환되는 오류 메세지를 HTTP 응답 내에 출력하도록 뒀다면, 이 오류는 일반 사용자도 알 수 있게 된다.
- 그래서 오류 메시지는 되도록이면 추상적으로 처리해야 한다. 불친절한 정도가 적당하다고 한다.

🛡 SQL Injection 방어 방법

1️⃣ Prepared Statements 사용

SQL 쿼리를 작성할 때 PreparedStatements를 사용하여 사용자 입력과 쿼리 구조를 분리한다.

PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?");

위와 같은 방법을 통해 공격자의 입력값이 SQL 코드로 취급되지 않기 때문에 인라인 쿼리 인젝션에서 사용되는 시도도 단순한 문자열로 처리된다.

2️⃣ ORM (Object-Relational Mapping) 사용

ORM 도구를 활용하여 쿼리 생성을 추상화하고 보안을 강화한다.

JPA, Hibernate, MyBatis 등의 ORM을 사용하면 직접 SQL을 작성하는 빈도를 줄인다.
다만, 네이티브 쿼리를 직접 쓰는 경우에는 여전히 주의해야 한다.

@Query("SELECT u from USER u WHERE u.name = ?1") // 안전

@Query("SELECT * FROM users WHERE name = " + name) // 취약

3️⃣ 입력값 검증 및 필터링

사용자의 입력을 철저하게 검증하여 예상치 못한 SQL 구문이 포함되지 않도록 한다.

입력값의 허용 범위, 형식, 길이을 제한하는 방식이다.
정규 표현식으로 유효성 검사를 하는 방법이 있다.

4️⃣ 최소 권한 원칙 적용

데이터베이스 사용자에게 최소한의 권한만 부여하여, 침해 시 피해를 최소화한다.

5️⃣ 에러 메시지 숨기기

데이터베이스 에러 메시지를 사용자에게 노출하지 않도록 설정하여 내부 구조가 드러나지 않게 한다.

SQL 에러, 테이블 이름, 스택 트레이스 등을 사용자에게 보여주지 않아야 한다.
Spring Boot: server.error.include-stacktrace=never 설정하기
커스텀 예외 핸들링 구현하기

2rlokr

이전 포스트

[CS/DB] 스터디 week5

다음 포스트