기억 파편

환경: mysql8+

복합 인덱스(Composite Index)는 하나의 인덱스 내에 두 개 이상의 컬럼(column)을 결합하여 생성한 인덱스로 단일 컬럼 인덱스와 달리, 여러 컬럼을 함께 고려하여 검색 성능을 향상시키기 위한 목적으로 사용된다.

• 복합 인덱스는 인덱스에 포함된 컬럼의 순서가 매우 중요
• 인덱스는 왼쪽에서부터 순차적으로 컬럼을 사용하여 쿼리를 최적화함
• 예를들어 두번째 컬럼의 인덱스를 타기 위해서는 첫 번째 컬럼의 인덱스를 반드시 탐색해야 함

explain 실행 시

• type : 각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식
• key : 최종 선택된 실행 계획에서 사용되는 인덱스(NULL: 인덱스가 사용되지 않았음을 의미)
• extra : 옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값

type

system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL

• ALL은 제외한 나머지는 모두 인덱스를 사용하는 접근 방식
• system -> ALL로 갈수록 성능이 느려짐

• system: 테이블에 한 행만 존재하는 경우 (최고의 경우)
• const: 인덱스를 통해 한 행만 검색 가능한 경우 (매우 빠름)
• eq_ref: 각 조인에서 인덱스 키를 사용하여 단일 행을 찾는 경우 (우수)
• ref: 인덱스를 통해 여러 행을 찾는 경우 (좋음)
• range: 인덱스의 범위 스캔(RANGE scan)이 사용된 경우
• index: 인덱스 전용 스캔(Index scan)이 사용된 경우 (테이블 데이터 대신 인덱스만 읽음)
• ALL: 전체 테이블 스캔(Full Table scan)이 발생한 경우 (최악의 경우)

ref

• 참조 조건으로 특정 값을 기준으로 검색
• 주로 복합 인덱스의 일부를 활용하거나 WHERE 조건의 =, 또는 연산자 IN을 활용
• 인덱스를 활용하여 특정 값에 대한 행을 검색할 때 주로 출력됩니다.

index

• 인덱스 전체를 순차적으로 스캔하는 Index Full Scan을 뜻합니다.
• Table Full Scan은 테이블 전체를 읽는 것을 뜻하지만, Index Full Scan은 설정된 인덱스 전체를 스캔하는 것을 뜻합니다.
• 즉, 복합 인덱스의에서는 복합 인덱스에 선언된 모든 컬럼을 순서대로 풀 스캔을 하는 것을 뜻합니다.

extra

(제일 빠름) Using index -> Using where; Using index -> Using index condition -> Null (제일 느림)

• Using Index: covering index 사용하여 테이블 데이터를 읽지 않고 인덱스만으로 쿼리 결과를 만족하는 경우
• Using where: 인덱스를 사용하여 데이터 접근은 하지만, WHERE 절의 조건 중 일부가 인덱스에 포함되지 않아서 서버에서 추가로 조건 검사를 수행해야 하는 경우. 즉, 인덱스 스캔 후에 반환된 결과에 대해 WHERE 조건을 한 번 더 필터링
• Using index condition: 인덱스 컨디션 푸쉬다운 (ICP) 과 관련 있음. 인덱스 스캔 시 인덱스에 포함된 컬럼에 대해 일부 조건을 미리 평가하는데 인덱스만으로 조건을 완전히 만족하지 못하는 경우(예: 인덱스에 포함되지 않은 컬럼의 조건이 있을 때) 실제 테이블 데이터에 접근하여 조건을 최종 확인하게 됨
• NULL: 인덱스는 사용하지만, 추가적인 extra 메시지가 표시되지 않는 경우로, 일반적으로 인덱스 스캔 후에도 테이블 데이터에 접근하는 경우, 인덱스 스캔 후 추가로 테이블 조회가 발생하기 때문에 디스크 I/O가 늘어나고, 전체 쿼리 실행 속도가 저하됨
• Using temporary: 쿼리 실행 중 임시 테이블이 생성되었음 (ORDER BY나 GROUP BY 등에서 사용)
• Using filesort: 인덱스가 아니라 별도의 정렬 알고리즘을 사용하여 정렬했음
• Using join buffer: 조인 시 버퍼를 사용한 조인 전략이 사용됨

Using where; Using index 순서?

직관적으로는 인덱스를 먼저 타고 그 후에 WHERE 조건을 적용하므로 "Using index; Using where" 순서가 자연스러워 보이지만, 실제 EXPLAIN의 extra 컬럼에 나타나는 메시지 순서는 반드시 실행 순서를 그대로 반영하지 않는다. 즉, 출력되는 순서는 내부 구현이나 옵티마이저가 수집한 여러 플래그를 특정 순서로 나열한 결과일 뿐, 실제 동작은 인덱스를 사용한 후 WHERE 조건으로 최종 결과를 필터링하는 방식이다. 표시되는 순서는 내부 구현에 따른 것이며, 성능이나 처리 순서를 해석하는 데 큰 영향을 주지 않는다.

인덱스 조건 푸시다운 (ICP)

• MySQL 5.6이상 버전부터 도입됐으며, 복합 인덱스의 일부 조건이 충족되면 나머지 조건을 인덱스 스캔 중에 필터링 할 수 있다.
• 즉, WHERE 조건의 일부 또는 전부를 인덱스에서 먼저 처리한 다음, 필요할 경우 데이터 테이블에 접근한다.
• 이를 통해 불필요한 디스크 I/O를 줄일 수 있다.

참고

• 인덱스를 사용하더라도 인덱스의 카디널리티가 낮은 경우, 옵티마이저는 인덱스를 타지 않고 Full Table Scan을 선택할 수 있음
• MySQL 옵티마이저는 쿼리의 WHERE 조건을 분석하여 복합 인덱스의 순서에 맞게 조건을 재배열 할 수 있음
• 복합 인덱스의 첫 번째 컬럼을 타지 않는 상황이더라도, 옵티마이저는 커버링 인덱스를 통해 테이블 풀 스캔이 아닌 인덱스 풀 스캔을 채택해 테이블까지는 접근하지 않고 인덱스의 필터링을 통해서 데이터를 가져올 수 있음

ORDER BY NULL은 쿼리의 결과를 정렬하지 않도록 지정하는 구문

언제 쓸까?

데이터베이스에서 정렬 작업은 비용이 많이 드는 작업이다. 결과를 정렬할 필요가 없는 경우, ORDER BY NULL을 사용하여 불필요한 정렬 작업을 피할 수 있다.

정렬이 필요 없다면 성능 향상 가능!

GROUP BY와 함께 사용할 때: MySQL은 GROUP BY를 실행할 때 암묵적으로 정렬을 수행한다. 하지만 특정 상황에서는 이 정렬이 불필요할 수 있다. ORDER BY NULL을 사용하면 MySQL에 정렬을 생략하도록 지시하여 성능을 향상할 수 있다.

환경: MySql

예전에 거래내역의 데이터를 파티셔닝 해서 조회한 적이 있는데 직접 설정했던 게 아니라서 구체적으로 알아본다.

파티셔닝 (Partitioning)

파티셔닝은 하나의 데이터베이스 내에서 데이터를 논리적으로 나누는 방법. 특정 컬럼을 기준으로 데이터를 여러 파티션으로 분할하여 성능을 향상시킬 수 있다.

파티셔닝의 특징:

• 단일 데이터베이스 인스턴스 내에서 분할
• 쿼리 성능 향상: 데이터를 작은 블록으로 나누어 특정 파티션만 조회할 수 있어 성능이 향상
• 트랜잭션 처리: 트랜잭션의 일관성을 유지할 수 있음
• 관리 용이성: 데이터를 분할해도 동일한 데이터베이스 인스턴스를 사용하므로 관리가 상대적으로 단순

파티셔닝을 선택할 때:

• 단일 서버에서 성능을 향상시키고 싶을 때
• 데이터가 일정한 기준으로 나누어지고, 쿼리가 특정 범위 (예: 날짜, 지역 등)로 자주 조회될 때
• 트랜잭션 일관성 및 데이터 무결성을 유지해야 할 때

예시:

• 날짜 기반 파티셔닝: order_date가 날짜 범위에 따라 파티셔닝되어, 특정 날짜 범위만 조회하면 해당 파티션만 스캔하여 빠른 성능을 제공

파티션 생성

CREATE TABLE orders (
    id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION pMax VALUES LESS THAN MAXVALUE
);

PARTITION BY RANGE는 각 파티션의 VALUES LESS THAN 조건에 따라 데이터를 할당

MySQL에서는 파티션 조건이 겹치지 않도록 설계되어 있으므로, 데이터가 특정 파티션에만 할당됨

• YEAR(order_date) * 100 + MONTH(order_date) 값이 202302 미만인 데이터가 이 파티션에 들어감
• 예: 2023년 1월의 데이터(202301), 2022년 12월 이전의 데이터

파티션 확인 가능?

MySQL에서는 내부적으로 파티션이 관리되지만, 사용자 관점에서는 단일 테이블로만 동작하며 클라이언트에서 개별 파티션을 노출하지 않는다. 파티션 정보를 조회하거나 특정 파티션만 쿼리 하는 기능은 있지만, 파티션에 직접 들어가서 작업하는 방식은 지원되지 않는다.

하지만 오라클에서는 파티션이 클라이언트나 관리 도구에서 명시적으로 노출되며, 개별 파티션에 대해 직접 접근하고 작업할 수 있다.

내부 동작 방식

물리적 파일 분리

• MySQL은 각 파티션을 내부적으로 별도의 파일 형태로 저장
• 데이터 파일(.ibd 파일, InnoDB 기준)이 각 파티션별로 생성
• 이로 인해 파티셔닝 된 테이블은 디스크 I/O 및 데이터 관리를 효율적으로 수행

논리적 테이블

• 사용자는 하나의 테이블로 모든 데이터를 다룸
• SQL 문장에서 특정 파티션을 명시적으로 참조할 필요가 없음
• MySQL은 파티션 조건에 따라 자동으로 적절한 파티션에 데이터를 삽입하거나 조회

플로우

1. 삽입 시 MySQL은 파티션 조건을 평가하여 해당 파티션에 데이터를 저장

2. 조회 시에도 사용자는 특정 파티션을 신경 쓰지 않아도 됨. MySQL이 자동으로 필요한 파티션만 읽음(파티션 프루닝).

3. 필요하다면 특정 파티션만 직접 조회할 수도 있음

INSERT INTO orders (id, order_date, amount) VALUES (1, '2023-01-15', 100.00);
-- 이 데이터는 p202301 파티션에 저장됨

SELECT * FROM orders WHERE order_date = '2023-01-15';
-- MySQL은 p202301 파티션만 스캔

SELECT * FROM orders PARTITION (p202301);
-- 명시적인 조회도 가능..

조회 시 파티션 프루닝

MySQL의 파티션 프루닝(Partition Pruning)은 쿼리 실행 시 WHERE 절의 조건에 따라 필요한 파티션만 읽도록 최적화하는 기법

파티션 프루닝이 작동하는 조건

1. WHERE 절에 파티션 키가 포함될 것
   • MySQL은 WHERE 절에 파티션 키(파티셔닝 기준이 되는 컬럼)가 있을 때만 프루닝을 수행
2. 상수 또는 단순 연산 사용
   • MySQL은 조건이 상수 값이거나 단순 연산으로 평가될 수 있을 때만 프루닝을 적용
   • 예를 들어, order_date = '2023-01-15'는 가능하지만 order_date = NOW()는 모든 파티션을 탐색
3. 범위 조건
   • BETWEEN, <, >, = 등의 조건도 프루닝이 가능
4. IN 조건
   • IN 조건도 가능한 경우 특정 파티션만 선택

그럼 아래와 같이 여러 월에 걸쳐진 데이터도 프루닝이 적용될까?

SELECT * FROM orders WHERE order_date >= '2023-02-01' AND order_date < '2023-03-01';

mysql의 경우 가능, oracle의 경우 불가능..

1. mysql

• 위 쿼리는 order_date 컬럼이 파티션 키이므로 프루닝이 가능
• >=, <, BETWEEN과 같은 범위 조건은 프루닝이 지원
  • -> p202301과 p202302 파티션만 검색

• MySQL에서 프루닝 여부를 확인하려면 EXPLAIN PARTITIONS를 사용
• 여기서 partitions 열에 p202301, p202302와 같은 값이 표시되면 프루닝이 성공적으로 적용된 것

EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date >= '2023-01-15' AND order_date < '2023-02-21';

id | partitions    | type  | possible_keys | key  | key_len | ref  | rows  | Extra
---|---------------|-------|---------------|------|---------|------|-------|------
 1 | p202301,p202302 | ALL  | NULL          | NULL | NULL    | NULL | 1000  |

단, 아래와 같은 쿼리는 프루닝 불가

WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 1; -- 프루닝 불가

2. Oracle

• 조건이 파티션의 경계값과 비교 가능해야 한다.
  • order_date= TO_DATE('2024-01-15', 'YYYY-MM-DD')
    • 파티션 p202401만 접근
  • order_date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-01-31', 'YYYY-MM-DD')
    • 파티션 p202401만 접근
  • order_date >= TO_DATE('2023-01-15', 'YYYY-MM-DD') AND order_date < TO_DATE('2023-02-10', 'YYYY-MM-DD');
    
    • 조건이 파티션 경계와 명확히 매칭되므로 Oracle은 p202301과 p202302 두 개의 파티션만 읽습니다.

• 파티션 키에 함수나 연산을 적용하면 Oracle이 프루닝을 할 수 없다.

SELECT * FROM sales WHERE TRUNC(order_date) = TO_DATE('2024-01-15', 'YYYY-MM-DD');
-- 파티션 프루닝 작동하지 않음

• 파티션 키에 연산을 하는 것은 위험하다. 모든 파티션을 스캔할 수 있으니 확인해야한다.

SELECT * FROM orders 
WHERE TRUNC(order_date) >= TO_DATE('2023-01-15', 'YYYY-MM-DD')
  AND TRUNC(order_date) < TO_DATE('2023-02-21', 'YYYY-MM-DD');

• 프루닝 여부 확인

EXPLAIN PLAN FOR
SELECT * FROM orders 
WHERE order_date >= TO_DATE('2023-01-15', 'YYYY-MM-DD') 
  AND order_date < TO_DATE('2023-02-21', 'YYYY-MM-DD');
  
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--------------
PARTITION START  | PARTITION STOP
-----------------|---------------
202301           | 202302

비관락 <-> 낙관락

• 비관락: 데이터가 수정될 것이라고 가정하고, 데이터를 조회하거나 수정할 때 다른 트랜잭션이 접근하지 못하도록 미리 락을 거는 방식입니다.
• 낙관락: 데이터 충돌이 적을 것으로 보고, 충돌이 발생했을 때만 문제를 해결하는 방식입니다. 버전 번호나 타임스탬프를 이용해 데이터가 변경되었는지 확인합니다.
• 2023.01.12 - [개발/spring] - [jpa] lock종류와 사용 시 주의사항

베타락 <-> 공유락

• 베타락: 데이터에 대해 하나의 트랜잭션만 읽기와 쓰기를 모두 할 수 있게 하는 락으로, 다른 트랜잭션은 해당 데이터를 읽거나 쓸 수 없습니다.
• 공유락: 데이터 읽기 작업에서는 여러 트랜잭션이 동시에 접근할 수 있도록 허용하는 락으로, 읽기는 가능하지만 쓰기는 불가능합니다.

쓰기락 <-> 읽기락

• 쓰기락: 데이터에 대해 쓰기 작업을 독점으로 수행할 수 있게 해 주는 락입니다. 락이 걸려 있는 동안에는 다른 트랜잭션에서 읽기와 쓰기 작업이 모두 불가능합니다.(쓰기를 위한 충돌관리; 충돌 시 쓰기 작업 보장)
• 읽기락: 읽기 작업을 수행하는 동안 다른 트랜잭션이 쓰기 작업을 할 수 없도록 하지만, 여러 트랜잭션이 동시에 읽기 작업을 할 수 있게 합니다.

베타락 = 쓰기락 / 공유락 = 읽기락

비관락/낙관락  쓰기락/읽기락 은 관점의 차이 비관락/낙관락 안에도 쓰기락/읽기락 가능

비관적 락과 쓰기 락의 관계

• 비관적 락의 구현 방식 중 하나가 쓰기 락: 비관적 락은 데이터의 변경을 안전하게 처리하기 위해 데이터베이스에서 락을 거는 개념이며, 이를 구현하는 방법 중 하나가 쓰기 락입니다. 비관적 락을 사용하여 충돌을 방지하려면 데이터 접근 시 다른 트랜잭션의 접근을 차단해야 하므로, 쓰기 락을 통해 데이터의 독점적인 접근 권한을 설정하는 경우가 많습니다.
• 공통점: 비관적 락과 쓰기 락 모두 다른 트랜잭션이 데이터에 접근하지 못하도록 제어하는 역할을 합니다.
• 차이점: 비관적 락은 충돌 방지를 위한 전체적인 전략이고, 쓰기 락은 비관적 락을 구현하는 구체적인 방법 중 하나입니다. 쓰기 락은 단순히 쓰기 작업이 수행되는 동안의 락이지만, 비관적 락은 읽기 또는 쓰기 작업 중에 락을 걸어 잠재적인 충돌을 방지할 수 있습니다.

MySQL에서 SELECT ... FOR UPDATE는 행 수준의 쓰기 락을 걸지만, 실제로는 동일한 트랜잭션 내에서만 데이터의 읽기와 수정에 대한 배타적 접근을 보장합니다. 다른 트랜잭션에서는 쓰기 작업은 막히지만 읽기 작업은 허용하는 것이 MySQL의 기본 동작입니다. 이 특징은 특히 InnoDB 엔진을 사용하는 경우에 적용됩니다.

왜 읽기 락이 걸리지 않는가?

MySQL의 InnoDB 엔진에서는 SELECT ... FOR UPDATE가 특정 행에 대해 **공유 락이 아닌 배타적 락(exclusive lock)**을 설정하여 다른 트랜잭션이 해당 행을 수정하지 못하도록 합니다. 그러나 읽기 작업은 차단하지 않습니다. 이는 SELECT ... FOR UPDATE가 쓰기 충돌을 방지하는 데 중점을 두는 방식 때문입니다.

이 방식은 동시성(concurrency)을 최대한 유지하기 위한 MySQL InnoDB의 최적화된 동작 방식으로 볼 수 있습니다. 다시 말해, 읽기 락까지 걸어 데이터 접근을 완전히 차단할 필요가 없는 경우라면, 다른 트랜잭션에서 해당 데이터를 읽는 작업은 허용합니다.

만약 읽기 작업까지 완전히 차단하고 싶다면?

MySQL에서 데이터의 읽기와 쓰기 모두를 차단하려면 명시적 테이블 락을 걸어야 합니다.

• 테이블 락으로 읽기/쓰기 모두 차단

LOCK TABLES MY_TABLE_NAME WRITE;

• 이 명령은 테이블 전체에 대해 락을 설정하여 다른 트랜잭션의 읽기와 쓰기를 모두 막습니다.
• 또는 트랜잭션 격리 수준 조정: 트랜잭션 격리 수준을 SERIALIZABLE로 설정하여, 현재 트랜잭션이 완료되기 전까지 다른 트랜잭션이 해당 행에 접근하는 것을 완전히 막을 수도 있습니다.

InnoDB에서 SELECT ... FOR UPDATE의 읽기 허용 특성은 데이터베이스 성능과 동시성 유지를 위한 트레이드오프이며, MySQL의 특징 중 하나로 이해할 수 있습니다.

아래와 같은 로직은 serializable하지 않아 실행 순서에 따라 결과가 다르다(x=100; y=200에서 시작)

Serializable은 트랜잭션 격리 수준(iso-level) 중 가장 높은 수준으로, 실행 순서에 상관없이 동일한 결과를 보장합니다.

Serializable은 트랜잭션들이 서로 겹치지 않도록 순차적으로 실행되는 것처럼 보이도록 보장합니다. 즉, 동시에 실행되는 여러 트랜잭션이 서로 간섭하지 않도록 하여, 트랜잭션이 직렬화된 것처럼 처리됩니다.

1. 결과의 일관성 보장: 여러 트랜잭션이 동시에 실행되더라도, 실행 순서에 관계없이 동일한 결과를 보장합니다. 즉, 트랜잭션 간에 발생할 수 있는 경쟁 조건이나 읽기-쓰기에 의한 문제(예: 더티 리드, 비반영 읽기, 팬텀 리드 등)를 방지합니다.
2. 트랜잭션 순차성: 데이터베이스는 트랜잭션들이 마치 순차적으로 실행된 것처럼 처리되도록 합니다. 이는 데이터베이스가 내부적으로 잠금 또는 스케줄링을 관리하여 발생할 수 있는 충돌을 막습니다.
3. 동시성 감소: 여러 트랜잭션이 동시에 실행되면, 그들이 서로 잠금을 요구하거나 기다리는 상태가 발생할 수 있습니다. 이로 인해 성능 저하가 있을 수 있습니다.

이를 보장하기 위해선? 

2단계 잠금(2-Phase Locking, 2PL)은 데이터베이스에서 트랜잭션의 일관성과 동시성을 유지하기 위한 잠금 프로토콜입니다. 2단계 잠금 규칙을 따르면 데이터베이스의 ACID 특성을 유지하면서 다중 트랜잭션이 동시에 실행될 때도 무결성을 보장할 수 있습니다.

2단계 잠금(2-Phase Locking)의 원리

: 모든 잠금 작업이 첫 번째 잠금 해제 작업보다 반드시 먼저 이루어지는 것입니다.

1. 확장 단계(Growing Phase):
   • 트랜잭션은 필요한 모든 잠금을 획득하는 단계입니다.
   • 잠금 해제는 허용되지 않으며 오로지 잠금 획득만 할 수 있습니다.
   • 트랜잭션이 접근하는 데이터에 대해 읽기 잠금 또는 쓰기 잠금을 설정합니다.
2. 축소 단계(Shrinking Phase):
   • 트랜잭션이 모든 잠금을 해제하는 단계입니다.
   • 이 단계에서는 더 이상 잠금을 획득할 수 없습니다.
   • 트랜잭션이 모든 작업을 완료하고 나면, 잠금을 해제하여 다른 트랜잭션이 접근할 수 있도록 합니다.

2단계 잠금이 일관성을 보장하는 이유

2PL을 따르는 경우, 트랜잭션 간의 교착 상태(Deadlock)나 무결성 문제를 예방할 수 있습니다. 트랜잭션이 모든 잠금을 획득할 때까지 축소를 시작하지 않기 때문에, 중간에 변경되는 데이터를 읽어 일관성이 깨지는 상황을 방지할 수 있습니다.

2단계 잠금의 단점

• 교착 상태 발생 가능성: 여러 트랜잭션이 서로의 잠금을 기다리다가 교착 상태가 발생할 수 있습니다.
• 성능 저하: 트랜잭션이 길어질수록 잠금을 오랫동안 유지해야 하므로 다른 트랜잭션의 병렬 실행을 방해할 수 있습니다.

종류

•  2PL
  • 모든 잠금 작업이 첫 번째 잠금 해제 작업보다 반드시 먼저 이루어지는 것
  • 락은 트랜잭션이 끝날 때까지 지속되는 것이 아니라, 쓰기 작업이 완료되는 시점에 해제

• C2PL
  • 트랜잭션이 시작되기 전에 필요한 모든 잠금을 미리 획득
  • 모든 리소스를 잠근 후, 트랜잭션을 시작하고 잠금 해제는 트랜젝션 처리하자마자

• S2PL
  • 트랜잭션 시작 시점에 모든 잠금을 미리 걸지는 않음. 대신, 잠금을 필요로 할 때마다 걸고, 획득한 모든 쓰기 잠금을 트랜잭션이 완료될 때까지 유지
  • write lock의 unlock이 커밋 이후

• SS2PL
  • 각 데이터에 접근할 때 해당 데이터에 대한 잠금을 획득하며, 획득한 모든 잠금은 트랜잭션이 완료될 때까지 유지
  • read/write lock의 unlock이 커밋 이후

MySQL의 2PL 사용 방식

MySQL은 InnoDB 스토리지 엔진을 사용할 때, 트랜잭션 격리 수준에 따라 락킹을 관리합니다. InnoDB는 기본적으로 Strict 2-Phase Locking (S2PL)을 사용하며, 이는 일반적인 2PL과 달리 트랜잭션이 종료될 때까지 쓰기 잠금을 유지합니다. 이 방식은 Repeatable Read 및 Serializable 격리 수준에서 특히 활용됩니다.

• Repeatable Read 격리 수준: InnoDB의 기본 격리 수준이며, InnoDB는 기본적으로 S2PL을 사용하여 트랜잭션 중에 읽은 데이터가 변경되지 않도록 보장합니다. 추가로 MySQL에서는 멀티버전 동시성 제어(MVCC)를 사용해 읽기 작업에 대한 잠금 경합을 줄입니다.
• Serializable 격리 수준: 이 수준에서는 MySQL이 트랜잭션 충돌을 방지하기 위해 더 강한 잠금을 사용합니다. 결과적으로 트랜잭션을 직렬화된 순서로 수행하려는 경향이 있으며, SS2PL과 유사한 동작을 제공합니다.

JPA와 데이터베이스 락킹의 관계

JPA에서 특정 락킹을 요청할 때(예: @Lock(LockModeType.PESSIMISTIC_WRITE)), 이 요청은 데이터베이스로 전달되어 MySQL의 2PL 방식에 따라 적용됩니다. 결국, JPA를 통해 락 모드를 설정해도 최종적인 락킹 동작은 데이터베이스의 격리 수준과 락킹 방식에 의해 결정됩니다.

요약

1. MySQL + JPA 조합에서는 InnoDB의 2PL 구현(S2PL)에 의해 락킹이 수행됩니다.
2. 트랜잭션 격리 수준 설정에 따라 2PL의 엄격성이나 일관성 수준이 달라집니다.
3. JPA는 데이터베이스에 락 모드를 요청할 수 있지만, 락킹 방식은 데이터베이스의 구현에 의존합니다.

2PL의 문제점

2PL은 데이터에 공유 락(읽기용) 또는 배타적 락(쓰기용)을 걸어서 트랜잭션이 안전하게 처리되도록 합니다. 하지만 몇 가지 문제점이 있습니다:

1. 데드락 발생 가능성:
   • 여러 트랜잭션이 서로의 락을 기다리는 상태에 빠져서 교착 상태가 발생할 수 있습니다.
2. 높은 락 대기 시간:
   • 트랜잭션 간의 상호 락으로 인해 읽기와 쓰기가 겹칠 때마다 대기 시간이 길어질 수 있으며, 대기 상태에서 성능 저하가 발생합니다.
3. 성능 저하:
   • 특히 읽기 작업이 많은 시스템에서 성능이 크게 저하됩니다. 모든 트랜잭션이 락을 걸어야 하므로, 높은 동시성 요구를 충족하기 어렵습니다.

MVCC (Multi-Version Concurrency Control)는 다중 버전 동시성 제어라고 불리는 방식으로, 데이터베이스에서 동시성 제어를 위해 여러 데이터 버전을 관리하여 성능을 향상시키는 기법입니다. 특히 읽기 작업이 많은 환경에서 락을 걸지 않고도 동시성을 보장할 수 있도록 설계되었습니다.

MVCC의 주요 개념

• 데이터 버전 관리: MVCC에서는 데이터베이스의 각 행(row)에 대해 여러 버전을 저장합니다. 새로운 트랜잭션이 변경을 가할 때마다, 기존 버전을 덮어쓰지 않고 새로운 버전을 생성합니다. 과거의 데이터는 그대로 유지됩니다.
• 트랜잭션 격리: MVCC는 트랜잭션이 시작될 때의 스냅샷(시점) 기준으로 데이터를 읽도록 하여, 다른 트랜잭션이 데이터를 변경하는 중에도 해당 스냅샷 기준 데이터를 읽게 됩니다. 이를 통해 락 없이도 일관된 읽기 작업을 제공합니다.
• 삭제 지연: 과거의 버전 데이터는 특정 조건에서 삭제되며, 데이터베이스가 자동으로 불필요한 버전을 제거하는 가비지 컬렉션 작업을 수행합니다.

MVCC의 작동 방식

1. 트랜잭션 시작 시점의 스냅샷 사용: 트랜잭션이 시작되면 해당 시점의 데이터 스냅샷을 이용하여 데이터 조회를 수행합니다. 이렇게 하면 다른 트랜잭션에서 데이터가 변경되더라도 현재 트랜잭션은 일관성 있는 데이터를 확인할 수 있습니다.
2. 데이터 버전 관리: 데이터에 대한 수정이 발생할 때 기존 데이터는 그대로 유지하고 새로운 버전을 생성합니다. 예를 들어, A 트랜잭션이 테이블의 특정 행을 수정하면, 원래 데이터를 덮어쓰지 않고 새로운 데이터 버전을 추가하는 방식입니다.
3. 커밋 후 가시성: 트랜잭션이 완료되면 변경된 데이터 버전이 다른 트랜잭션에서도 보이게 됩니다. 아직 완료되지 않은 트랜잭션에서의 변경은 다른 트랜잭션에 영향을 미치지 않습니다.
4. 가비지 컬렉션: 시간이 지나면서 더 이상 참조되지 않는 오래된 데이터 버전은 데이터베이스에서 주기적으로 삭제하여 공간을 확보합니다.

MVCC의 장점

• 락을 사용하지 않고도 일관된 읽기를 보장하므로, 읽기 성능이 우수합니다.
• 트랜잭션이 많아도 충돌이 적어, 데드락 발생 가능성이 줄어듭니다.
• 동시 읽기 및 쓰기 작업을 효율적으로 처리하여 높은 동시성을 제공합니다.

MVCC의 단점

• 모든 데이터의 버전을 유지해야 하므로, 저장 공간이 더 많이 필요할 수 있습니다.
• 가비지 컬렉션 작업이 필요하여, 오래된 버전을 제거하는 데 추가적인 관리 비용이 발생할 수 있습니다.

적용 사례

MVCC는 PostgreSQL, MySQL의 InnoDB 엔진, Oracle 등 여러 데이터베이스 시스템에서 사용되며, 특히 트랜잭션 격리 수준을 높이면서도 성능을 유지해야 하는 환경에서 널리 활용됩니다.

출처: https://www.youtube.com/watch?v=0PScmeO3Fig

MySQL의 네임드 락(named lock)은 이름을 가진 사용자 정의 락으로, 특정 자원이나 작업의 동시 접근을 제어하기 위해 사용됩니다. 일반적인 테이블 락이나 행 락과는 달리, 네임드 락은 이름을 기준으로 동기화를 제어하며, 트랜잭션 단위 외부에서 유연하게 락을 걸고 해제할 수 있는 특징이 있습니다.

-- 'my_lock'이라는 이름으로 락을 요청하고, 10초 동안 기다립니다.
SELECT GET_LOCK('my_lock', 10);

-- 락을 얻으면 1을 반환, 대기 시간 초과로 실패 시 0을 반환합니다.

-- 락 해제
SELECT RELEASE_LOCK('my_lock');

• GET_LOCK(name, timeout): 락을 요청합니다. name은 락 이름을 나타내고 timeout은 락을 얻기 위해 대기할 시간을 초 단위로 지정합니다.
  • 1: 락을 성공적으로 획득한 경우
  • 0: 락을 획득하지 못한 경우 (예: 이미 다른 세션에서 해당 락을 보유 중인 경우)
  • NULL: 오류가 발생한 경우 (예: 권한 문제나 기타 오류)
• RELEASE_LOCK(name): 해당 이름의 락을 해제합니다. 락이 성공적으로 해제되면 1, 요청자가 해당 락을 보유하고 있지 않으면 0, 오류가 발생하면 NULL을 반환합니다.

네임드 락의 주요 기능

• 임의의 이름 사용: 락 이름을 문자열로 지정하여 GET_LOCK() 함수로 락을 설정하고 RELEASE_LOCK() 함수로 해제할 수 있습니다. 이 락은 테이블이나 특정 레코드와는 관련이 없고, 단순히 이름만으로 관리됩니다.
• 세션 기반 락: 락은 설정한 세션에서만 해제할 수 있습니다. 세션이 종료되면 해당 세션에서 설정한 모든 네임드 락도 자동으로 해제됩니다.
• 트랜잭션 외부에서의 사용: 트랜잭션과 별도로 작동하므로, 트랜잭션 경계 외부에서도 락을 걸어 작업 동기화가 가능합니다. 네임드 락은 세션 기반으로 작동하므로 트랜잭션의 시작과 종료에 영향을 받지 않으며, 트랜잭션이 커밋되거나 롤백되더라도 유지됩니다. 이를 통해, 트랜잭션 경계에 관계없이 특정 자원에 대한 락을 설정하고 해제할 수 있습니다.

사용 예시

네임드 락_분산락은 주로 다음과 같은 상황에서 사용됩니다.

• 단일 자원에 대한 순차적 접근: 예를 들어, 특정 자원에 동시에 접근하면 안 되는 상황에서 충돌 방지를 위해 네임드 락을 사용합니다.
• 스케줄링 작업: 하나의 프로세스에서만 수행되어야 하는 주기적인 배치 작업에 적용하여 다른 프로세스에서 동일 작업을 수행하지 못하도록 할 때 유용합니다.

주의사항

• 네임드 락은 세션 단위로 적용되므로, 세션이 종료되면 자동으로 해제됩니다.
• 데이터베이스 락이므로, 자주 사용하게 되면 데이터베이스 부하가 높아질 수 있습니다.
• 락의 TTL(Time to Live)을 별도로 지정할 수 없으므로, 락을 영구적으로 점유하지 않도록 주의가 필요합니다.

트랜잭션 내에서의 네임드 락 동작 방식

1. 트랜잭션 내부에서 락 획득 및 해제 가능: 트랜잭션 내부에서도 GET_LOCK()을 호출해 네임드 락을 설정할 수 있습니다. 이후 작업을 진행한 뒤 트랜잭션을 커밋하거나 롤백하더라도 네임드 락은 해제되지 않습니다.
2. 락 유지: 트랜잭션이 종료되어도 네임드 락은 세션이 유지되는 한 계속 걸려 있습니다. 따라서 트랜잭션이 커밋이나 롤백으로 끝나도 락이 해제되지 않으며, 명시적으로 RELEASE_LOCK()을 호출하거나 세션이 종료되어야 해제됩니다.
3. 트랜잭션 종료 후 작업 가능: 네임드 락은 트랜잭션과 독립적이기 때문에, 트랜잭션 종료 후에도 세션 내에서 계속 유효한 상태로 유지됩니다. 따라서 트랜잭션이 완료된 후 다른 작업을 수행할 때도 락을 보유할 수 있습니다.

-- 트랜잭션 시작
START TRANSACTION;

-- 'resource_lock'이라는 이름의 네임드 락 설정
SELECT GET_LOCK('resource_lock', 10);

-- 트랜잭션 내 작업 수행
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 트랜잭션 커밋 또는 롤백
COMMIT;

-- 네임드 락은 여전히 유지됨
-- 락 해제
SELECT RELEASE_LOCK('resource_lock');

API 요청과 MySQL 세션 종료

1. API 요청의 생명주기: 일반적으로 각 API 요청은 DB와 연결을 생성하고 작업을 수행한 후 요청이 종료되면 세션도 함께 닫히는 방식입니다. 따라서, API 호출이 완료되면 MySQL 세션이 종료되고, 세션에 종속된 리소스(네임드 락 포함)도 자동으로 해제됩니다.
2. 네임드 락의 해제: 만약 API 요청 중 네임드 락을 설정했다면, 요청이 끝나면서 세션이 종료될 때 자동으로 락도 해제됩니다. 즉, API 요청이 완료될 때마다 새 세션이 시작되고, 기존 세션이 닫히면서 네임드 락은 해제됩니다. 이를 통해 불필요한 락이 시스템에 남아 동시성 문제를 야기하지 않도록 합니다.
3. 커넥션 풀링의 영향: 커넥션 풀링을 사용하는 경우, API 요청이 끝나도 세션이 종료되지 않고 풀에 반환되어 재사용될 수 있습니다. 이 경우에도, MySQL 네임드 락은 요청마다 명시적으로 해제하는 것이 좋습니다. 그렇지 않으면 동일한 커넥션을 재사용하는 다른 API 요청에서 락 충돌이 발생할 수 있습니다.

public ResponseEntity<String> processTask() {
    try (Connection connection = dataSource.getConnection()) {
        // 네임드 락 획득
        Statement statement = connection.createStatement();
        statement.execute("SELECT GET_LOCK('task_lock', 10)");
        
        // 특정 작업 수행
        performTask();
        
        // 작업 완료 후 락 해제
        statement.execute("SELECT RELEASE_LOCK('task_lock')");
        
        return ResponseEntity.ok("Task completed");
    } catch (Exception e) {
        e.printStackTrace();
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("Task failed");
    }
}

하지만 분산 락의 경우 Redis를 추천

복잡한 분산 락이 필요한 경우 Redis와 같은 외부 시스템이 더 적합한 이유는 성능, 확장성, 안정성 측면에서 MySQL보다 우수하기 때문입니다.

1. 성능과 응답 속도:
   • Redis는 메모리 기반의 데이터 저장소로, 읽기/쓰기 속도가 매우 빠릅니다. 락 획득과 해제 시나 락 상태 확인 등의 작업이 신속하게 처리됩니다.
   • MySQL은 트랜잭션과 관계형 데이터 관리에 최적화되어 있고 디스크 기반이기 때문에, 락을 빈번하게 사용하는 경우 MySQL에는 부하가 발생할 수 있으며 성능이 저하될 가능성이 큽니다.
2. TTL(시간 초과 설정) 지원:
   • Redis는 락에 TTL(Time To Live)을 설정할 수 있습니다. TTL은 락을 획득한 클라이언트가 비정상 종료되거나 네트워크 문제가 발생했을 때 락이 자동으로 해제될 수 있게 합니다. 이를 통해 잠금 상태가 영구적으로 유지되는 문제를 방지할 수 있습니다.
   • MySQL의 네임드 락은 TTL을 기본 제공하지 않기 때문에, 락을 획득한 세션이 종료될 때까지 락이 지속될 수 있어 영구 락 문제를 수동으로 관리해야 합니다.
3. 분산 환경에서의 락 관리:
   • Redis는 여러 서버나 인스턴스가 동시에 락에 접근하는 분산 환경에서도 쉽게 락을 관리할 수 있습니다. Redis는 분산 락을 위한 Redlock 알고리즘을 통해 안정적인 락 제공을 지원합니다.
   • 반면 MySQL 네임드 락은 기본적으로 단일 데이터베이스 인스턴스 내에서 작동하도록 설계되어 있어 다중 인스턴스나 분산 환경에서 락을 구현하는 데는 적합하지 않습니다.
4. 락 상태의 유연한 관리:
   • Redis는 여러 클라이언트나 시스템이 락의 상태를 쉽게 확인하고 제어할 수 있는 기능을 제공합니다. 예를 들어, Redis의 SETNX 명령어와 EXPIRE 옵션을 활용해 락의 생성과 동시에 시간 제한을 줄 수 있습니다.
   • MySQL 네임드 락은 네임드 락의 상태를 쉽게 확인하거나 관리하기 어렵고, SQL 쿼리를 통해 제한적으로 확인하는 방법만 제공됩니다.
5. 분산 트랜잭션 요구사항:
   • Redis는 분산 트랜잭션을 지원하는 라이브러리와 결합하여 다양한 마이크로서비스 아키텍처에서 활용될 수 있으며, 락의 가용성과 일관성을 유지하는 데 최적화되어 있습니다.
   • MySQL은 주로 단일 DB 내에서 트랜잭션을 관리하도록 설계되어 있어 분산 트랜잭션을 다루기 위한 락 시스템으로는 적합하지 않습니다.

Redlock 알고리즘의 동작 원리 for redis

Redlock은 일반적으로 5개의 Redis 노드(인스턴스)를 사용하여 구성됩니다. 락을 획득하기 위해 다음 절차를 수행합니다.

1. 동시에 모든 Redis 인스턴스에 락을 요청:
   • 클라이언트는 각 Redis 노드에 동일한 락(예: 고유한 UUID)을 설정하려고 시도합니다. 이때 SET resource_name lock_value NX PX 명령어를 사용해 락을 생성합니다.
   • NX는 락이 존재하지 않을 때만 생성하도록 하고, PX는 TTL(타임아웃)을 설정해 락이 일정 시간 후에 자동으로 만료되도록 합니다.
2. 과반수 이상의 인스턴스에서 락을 획득해야 함:
   • 클라이언트는 5개의 노드 중 최소한 3개 이상의 노드에서 락을 획득해야 합니다.
   • 또한 모든 락이 획득될 때까지 걸린 시간이 전체 TTL의 절반 이하(보통 설정한 TTL의 2/3 이하)여야 합니다. 이는 네트워크 지연으로 인해 TTL이 끝나기 전에 락을 잃어버릴 위험을 줄이기 위함입니다.
3. 락 획득 성공 여부 판단:
   • 과반수 이상의 노드에서 락을 지정된 TTL 내에 획득한 경우에만 락 획득에 성공한 것으로 간주하고 작업을 수행합니다.
   • 그렇지 않으면 모든 노드에서 락을 해제하고 락 획득을 다시 시도합니다.
4. 작업 완료 후 락 해제:
   • 클라이언트는 작업을 완료한 후 모든 Redis 노드에서 락을 해제합니다.
   • 락 해제는 각 Redis 노드에서 DEL 명령을 통해 수행되며, 락 획득 시 사용한 고유 ID를 사용해 해당 락을 정확히 해제합니다.

Redlock 알고리즘의 장점

• 안정성: 과반수 노드에서만 락을 유지하면 되므로, 일부 노드가 다운되거나 네트워크 지연이 발생해도 락을 유지할 수 있습니다.
• 고성능: TTL을 통해 락이 자동으로 해제되므로, 클라이언트가 비정상 종료되어도 시스템에 락이 영구적으로 걸리지 않습니다.
• 재진입 허용: 클라이언트가 동일한 락을 여러 번 획득할 수 있는 재진입을 허용하지 않음으로써 락의 일관성을 유지합니다.

Redlock 알고리즘의 사용 사례

Redlock은 특히 분산 시스템에서 여러 인스턴스나 프로세스가 동시에 접근하는 자원을 동기화할 때 사용됩니다. 예를 들어, 다중 서버 환경에서 하나의 자원을 동시에 수정하거나 처리하면 안 되는 경우나, 여러 노드에서 특정 자원에 대한 순차적 접근이 필요한 상황에서 유용하게 활용될 수 있습니다.

Redlock 알고리즘의 단점 및 주의점

일부 분산 시스템 전문가들은 Redlock이 네트워크 지연이나 Redis 서버의 TTL 동기화 문제 때문에 완벽한 일관성을 보장하지 못할 수 있다고 지적합니다. 따라서 중요한 트랜잭션 시스템에서는 Redlock을 단독으로 사용하기보다, 데이터의 일관성 요구 사항과 시스템의 특성을 고려하여 추가적인 안전 장치를 마련하는 것이 좋습니다.

과반수 락 획득?

Redlock 알고리즘에서 클라이언트가 5개의 노드 중 최소한 3개 이상의 노드에서 락을 획득해야 하는 이유는 분산 시스템의 일관성과 내결함성을 유지하기 위해서입니다. 구체적인 이유는 다음과 같습니다.

1. 과반수 규칙 (Majority Rule)

• 5개의 노드 중 3개의 노드에서 락을 획득하면 과반수를 확보하게 되므로, 일관성을 보장할 수 있습니다.
• 만약 클라이언트가 3개 이상의 노드에서 락을 획득하면 다른 클라이언트가 동시에 같은 이름의 락을 획득하는 상황을 피할 수 있습니다. 이로 인해, 시스템에서 같은 자원에 대해 두 개의 락이 생성되는 스플릿 브레인(split-brain) 문제를 예방할 수 있습니다.

2. 내결함성 (Fault Tolerance)

• Redlock에서는 노드 일부가 일시적으로 다운되더라도 시스템 전체가 정상적으로 동작할 수 있도록 설계되었습니다. 5개의 노드 중 2개가 다운되더라도 여전히 3개 이상의 노드에서 락을 획득할 수 있기 때문에 락의 가용성이 보장됩니다.
• 이로써 노드 장애나 네트워크 지연이 발생해도 여전히 락을 획득할 수 있는 가능성을 높이고, 잠금 상태의 지속성을 보장할 수 있습니다.

3. 데이터 일관성

• 락을 획득할 때 시간이 지남에 따라 노드별 TTL(락의 만료 시간)이 다르게 적용될 수 있습니다. 하지만 과반수에서 락을 획득함으로써 여러 클라이언트가 동일 자원을 동시에 접근하는 데이터 불일치 문제가 발생할 가능성을 줄일 수 있습니다.
• 만약 절반 미만의 노드에서만 락을 획득할 수 있다면, 이 락이 실제로 유효한지에 대한 신뢰성이 낮아져 다른 클라이언트가 중복으로 락을 획득할 가능성이 커지게 됩니다.

4. 분산 시스템에서의 합의 기반 접근

• 분산 환경에서 대부분의 분산 알고리즘은 과반수 합의를 통해 신뢰할 수 있는 결정(일관된 상태 유지)을 도출합니다. Redlock에서도 마찬가지로, 과반수 노드에서 락을 획득해야만 해당 락을 실제로 "획득했다"는 합의를 기반으로 동작하는 방식입니다.
• 이 접근 방식은 분산 시스템의 리더 선출이나 동기화 등의 문제에서 과반수 합의가 안전성과 일관성을 유지하는 기본 방법론임을 고려해 채택된 것입니다.

무슨 문제를 위해서 과반수락?

1. 중복 락 획득 (Duplicate Lock Acquisition)

• 정의: 중복 락 획득은 여러 클라이언트가 동시에 동일한 자원에 대해 락을 획득하려고 시도하는 상황을 의미합니다. 즉, 두 개 이상의 클라이언트가 동일한 락을 획득하게 되어 자원에 대한 무결성이 깨질 수 있는 상황입니다.
• 문제점:
  • 자원에 대한 경쟁이 발생할 수 있으며, 두 클라이언트가 서로 다른 작업을 수행할 경우 데이터의 일관성이 무너질 수 있습니다.
  • 예를 들어, 두 클라이언트가 동시에 동일한 파일에 데이터를 추가하거나 수정할 경우, 결과적으로 데이터가 손상되거나 원하지 않는 상태가 발생할 수 있습니다.
• 해결책: Redlock 알고리즘에서 과반수의 노드에서 락을 획득해야만 락을 실제로 사용할 수 있도록 함으로써, 중복 락 획득을 방지합니다. 이렇게 하면, 동시에 여러 클라이언트가 락을 획득하려 할 때, 과반수의 노드에서 락을 획득하지 못하면 다른 클라이언트가 해당 자원에 접근하지 못하도록 차단할 수 있습니다.

2. 스플릿 브레인 문제 (Split-Brain Problem)

• 정의: 스플릿 브레인 문제는 분산 시스템에서 네트워크 파티션이 발생하여 서로 다른 노드 그룹이 독립적으로 동작하게 되는 상황을 말합니다. 이로 인해 두 그룹이 각기 다른 결정이나 상태를 유지하게 되어 일관성이 무너지는 문제가 발생할 수 있습니다.
• 문제점:
  • 예를 들어, 노드 A와 B가 서로 연결되어 있다가 네트워크 파티션으로 인해 C, D, E 노드와 연결이 끊어졌다고 가정해봅시다. C, D, E는 서로 독립적으로 동작하면서 락을 획득할 수 있으며, 이 과정에서 A와 B는 C, D, E의 상태를 알지 못합니다. 이로 인해 C와 D는 같은 자원에 대해 락을 획득하고 작업을 수행하게 됩니다.
  • 결과적으로, A와 B가 네트워크 연결이 복구된 후, C와 D가 작업한 내용이 서로 충돌하거나 불일치하게 될 수 있습니다.
• 해결책: Redlock 알고리즘에서는 최소 3개 이상의 노드에서 락을 획득해야 한다고 요구합니다. 이렇게 함으로써 과반수의 노드에서 락을 획득해야만 자원을 사용할 수 있게 하여, 스플릿 브레인 상태에서도 하나의 리더를 정의하고 그에 따라 락을 적절히 관리할 수 있습니다. 네트워크 파티션이 발생하더라도, 과반수 노드의 결정에 따라 자원에 대한 접근을 조정할 수 있습니다.

근데 노드를 5개로 못하면?

1. 노드 수 감소에 따른 과반수 규칙

• 과반수 필요: Redlock에서 과반수의 노드에서 락을 획득해야 한다는 규칙은 일관성과 가용성을 보장하기 위해서입니다. 노드 수가 줄어들면 과반수를 정의하는 방법도 바뀌게 됩니다.
• 예를 들어, 5개의 노드 대신 3개의 노드를 사용할 경우, 과반수는 2개가 되므로 2개의 노드에서 락을 획득하면 락을 사용할 수 있습니다. 이 경우, 락을 획득하는 것이 더 용이해지지만, 노드 장애가 발생했을 때 시스템의 안정성이 떨어질 수 있습니다.

2. 내결함성 저하

• 장애 상황: 노드 수가 적어지면, 일부 노드가 다운되거나 네트워크 파티션이 발생했을 때 남은 노드들이 과반수를 형성하기 어려워질 수 있습니다. 예를 들어, 3개 노드 중 1개가 다운되면, 남은 2개에서만 락을 획득할 수 있으므로, 이 경우 락이 획득되지 않을 수 있습니다.
• 신뢰성: 더 적은 노드 수는 네트워크 오류 또는 노드 장애에 더 취약하게 만들어 전체 시스템의 신뢰성을 저하시킬 수 있습니다.

3. 데이터 일관성 문제

• 스플릿 브레인: 노드 수가 적으면 스플릿 브레인 문제를 더욱 쉽게 겪을 수 있습니다. 예를 들어, 2개의 노드만 있을 경우, 네트워크 파티션이 발생하면 두 노드가 서로 다른 상태를 유지하게 되어, 데이터 일관성 문제가 발생할 수 있습니다.

4. 3개 이상의 노드 사용 추천

• 3개 이상의 노드를 사용하는 것이 일반적으로 권장되며, 이를 통해 기본적인 가용성과 일관성을 확보할 수 있습니다. 3개 노드에서는 2개 이상에서 락을 획득해야 하며, 이로 인해 최소한의 내결함성과 데이터 일관성을 유지할 수 있습니다.

결론

Redlock 알고리즘은 원칙적으로 5개 노드를 사용하는 것을 권장하지만, 최소 3개 노드를 사용할 경우에도 기본적인 분산 락 기능을 수행할 수 있습니다. 그러나 노드 수가 적을수록 장애에 대한 취약성이 증가하므로, 시스템 설계 시 노드 수를 신중히 결정하는 것이 중요합니다.

P6Spy: Java 기반 애플리케이션에서 SQL 쿼리의 로깅 및 모니터링을 위한 프레임워크

P6Spy 주요 기능:

• SQL 로깅: 애플리케이션에서 실행되는 모든 SQL 쿼리, 매개변수, 실행 시간을 기록합니다.
• 성능 모니터링: 각 쿼리의 실행 시간을 보여주어 느린 쿼리를 식별하는 데 도움을 줍니다.
• 동적 필터링: SELECT, UPDATE 등의 특정 쿼리 유형을 필터링하여 로그에서 제외할 수 있습니다.
• 간편한 사용: 표준 JDBC 드라이버 대신 P6Spy 드라이버를 사용하여 SQL 쿼리를 감시하고 로깅합니다.

동작 방식:

1. 스파이 드라이버: P6SpyDriver가 실제 JDBC 드라이버를 감싸고 모든 SQL 쿼리를 가로챕니다.
2. 로그 기록: 쿼리를 콘솔 또는 파일로 기록하며, 로그 포맷은 커스터마이징 가능합니다.
3. 분석: 로그를 통해 느린 쿼리를 찾아 성능을 최적화하거나, SQL 관련 문제를 디버깅할 수 있습니다.

implementation("p6spy:p6spy:3.9.1")

spring.datasource.url=jdbc:p6spy:mysql://10.162.5.x:3306/your_database_name
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver

with springboot 3.2.5

################################################################################################
# P6Spy Options File                                                                           #
# See documentation for detailed instructions                                                  #
# https://p6spy.readthedocs.io/en/latest/configandusage.html#configuration-and-usage           #
################################################################################################
appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=| %(executionTime) ms | %(sql)
databaseDialectDateFormat=yyyy-MM-dd'T'HH:mm:ss
databaseDialectTimestampFormat=yyyy-MM-dd'T'HH:mm:ss

resources/spy.properties 에 위와 같은 내용 작성

import com.p6spy.engine.logging.Category;
import com.p6spy.engine.spy.P6SpyOptions;
import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import jakarta.annotation.PostConstruct;
import java.util.Locale;
import org.hibernate.engine.jdbc.internal.FormatStyle;
import org.springframework.context.annotation.Configuration;

@Configuration
public class P6SpyConfig implements MessageFormattingStrategy {

  @PostConstruct
  public void setLogMessageFormat() {
    P6SpyOptions.getActiveInstance().setLogMessageFormat(this.getClass().getName());
  }

  @Override
  public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
    sql = formatSql(category, sql);
    return String.format("[%s] | %d ms | %s", category, elapsed, formatSql(category, sql));
  }

  private String formatSql(String category, String sql) {
    if (sql != null && !sql.trim().isEmpty() && Category.STATEMENT.getName().equals(category)) {
      String trimmedSQL = sql.trim().toLowerCase(Locale.ROOT);
      if (trimmedSQL.startsWith("create") || trimmedSQL.startsWith("alter") || trimmedSQL.startsWith("comment")) {
        sql = FormatStyle.DDL.getFormatter().format(sql);
      } else {
        sql = FormatStyle.BASIC.getFormatter().format(sql);
      }
      return sql;
    }
    return sql;
  }

}

자바로도 설정 가능

1. P6SpyConfig 클래스

• 동적 설정: P6Spy의 동작을 코드 기반으로 설정하고, Spring의 컨텍스트에서 동작하는 방식입니다.
• 장점:
  • 코드에서 직접 포맷을 제어할 수 있어 더 유연한 설정이 가능합니다.
  • Spring Boot의 DI(의존성 주입) 및 기타 설정들과 쉽게 연동됩니다.
  • 복잡한 포맷이나 특정 로깅 로직을 구현하기 더 편리합니다.
• 단점: P6Spy의 설정을 코드에서 관리해야 하므로 설정을 변경할 때 코드 수정이 필요합니다.

2. spy.properties 파일

• 정적 설정: P6Spy의 설정을 별도의 설정 파일로 관리하는 방식입니다.
• 장점:
  • 코드 수정 없이 spy.properties 파일에서 간단하게 설정을 변경할 수 있습니다.
  • 개발 환경이나 운영 환경에서 설정을 쉽게 바꿀 수 있습니다.
• 단점:
  • 포맷이 비교적 제한적입니다. 커스텀 포맷이 필요한 경우 코드보다 덜 유연합니다.
  • 복잡한 로직을 설정 파일에 구현하기 어렵습니다.

둘 다 설정해야 할까?

• 필요에 따라 둘 다 사용할 수 있지만, 일반적으로는 둘 중 하나만 사용합니다.
• P6SpyConfig 클래스 사용: 코드 기반 설정을 선호하거나, Spring 환경에서 동적으로 설정을 관리하고 싶다면 이 클래스를 설정합니다.
• spy.properties 파일 사용: 간단한 설정 변경을 원하거나, 설정을 코드와 분리하여 운영 환경에서 쉽게 관리하고 싶다면 spy.properties 파일만 설정합니다.

mysql 8 기준, 데이터 삭제와 관련된 명령어들 정리한다.

1. DROP

• 설명: DROP은 데이터베이스 객체 자체를 삭제하는 명령어이다. 주로 테이블, 데이터베이스, 뷰, 인덱스 등과 같은 객체를 완전히 제거하는 데 사용된다.
• 특징:
  • 테이블 자체가 삭제
  • 테이블의 구조(Schema)와 데이터가 모두 삭제되며, 복구불가
  • 삭제된 테이블에 대한 모든 참조(인덱스, 트리거, 외래 키 등)가 함께 삭제됨
  • 롤백 불가: DROP은 트랜잭션 관리와 상관없이 즉시 반영되며, 복구불가
  • 테이블 삭제 후에 해당 테이블에 대한 쿼리를 실행할 수 없음
  • DROP권한 필요

DROP TABLE employees;
DROP DATABASE my_database;

2. TRUNCATE

• 설명: TRUNCATE는 테이블의 모든 데이터를 삭제하지만, 테이블의 구조는 유지하는 명령어. 즉, 테이블은 그대로 남아있고 데이터만 삭제됨.
• 특징:
  • 테이블의 모든 데이터가 삭제되지만, 테이블의 구조와 관련된 인덱스 및 제약 조건은 유지됨.
  • 롤백 불가: 기본적으로 TRUNCATE는 트랜잭션으로 처리되지 않으며, 실행 후 롤백할 수 없음.
  • 성능이 매우 빠름. DELETE보다 빠르게 데이터 삭제가 가능하며, 일반적으로 전체 데이터를 삭제하는 상황에서 더 효율적.
  • 테이블에서 자동 증가(AUTO_INCREMENT) 값이 초기화됨
  • 테이블에서 데이터만 삭제하므로 테이블을 계속 사용할 계획이 있을 때 사용.
  • MySQL에서 TRUNCATE는 사실상 DROP + CREATE와 유사하게 동작하므로, DROP 권한이 있어야 한다!

TRUNCATE TABLE employees;

3. DELETE

• 설명: DELETE는 테이블에서 특정 조건에 맞는 데이터를 삭제하는 명령어. 특정 조건을 사용하여 테이블의 일부 또는 전체 데이터를 삭제.
• 특징:
  • WHERE 절을 통해 특정 행을 선택적으로 삭제.
  • 트랜잭션이 가능하며, 롤백가능
  • 데이터가 삭제되더라도 테이블의 구조는 그대로 유지
  • 인덱스가 많은 테이블에서는 느릴 수 있음
  • 조건 없이 DELETE 명령어를 사용하면 테이블의 모든 데이터가 삭제되지만, 이는 TRUNCATE보다 느릴 수 있음.
  • DELETE 권한 필요

DELETE FROM employees WHERE employee_id = 1;
DELETE FROM employees; -- 모든 데이터 삭제

사용할 때 실행하고자하는 계정의 권한도 함께 확인해야한다!

basics

1. String functions

SELECT CONCAT('Hello', ' ', 'World');
-- Output: Hello World

SELECT LENGTH('MySQL');
-- Output: 5

SELECT LOWER('HELLO');
-- Output: hello

SELECT SUBSTRING('MySQL Functions', 1, 5); //1부터 시작
-- Output: MySQL

SELECT TRIM('  Hello  ');
-- Output: Hello

SELECT ABS(-5);
-- Output: 5

SELECT CEIL(4.3), FLOOR(4.3);
-- Output: 5, 4

SELECT ROUND(5.567, 2);
-- Output: 5.57

SELECT POWER(2, 3);
-- Output: 8

SELECT MOD(10, 3);
-- Output: 1

SELECT NOW();
-- Output: 2024-09-09 12:34:56

SELECT CURDATE();
-- Output: 2024-09-09

SELECT CURTIME();
-- Output: 12:34:56

SELECT DATE_ADD('2024-09-09', INTERVAL 5 DAY);
-- Output: 2024-09-14

SELECT DATE_SUB('2024-09-09', INTERVAL 1 MONTH);
-- Output: 2024-08-09

SELECT ADDDATE('2024-09-09', INTERVAL 10 DAY);
-- Output: 2024-09-19

SELECT TIMESTAMPADD(DAY, 5, '2024-09-09 12:00:00');
-- Output: 2024-09-14 12:00:00

SELECT DATEDIFF('2024-09-15', '2024-09-09');
-- Output: 6

SELECT TIMESTAMPDIFF(HOUR, '2024-09-09 10:00:00', '2024-09-09 15:00:00');
-- Output: 5

select timestampdiff(day, last_login, last_logout) as timestampdifff
, datediff(last_logout, last_login) as datedifff
, last_login , last_logout 
from user
having timestampdifff !=  datedifff
;


timestampdifff|datedifff|last_login         |last_logout        |
--------------+---------+-------------------+-------------------+
           -10|      -11|2024-06-10 16:52:12|2024-05-30 21:04:17|
             3|        4|2024-07-25 17:49:34|2024-07-29 08:16:30|
             0|        1|2024-03-15 17:49:32|2024-03-16 02:01:10|
             0|        1|2024-07-31 08:34:44|2024-08-01 07:01:18|

SELECT YEAR('2024-09-09');
-- Output: 2024

SELECT MONTH('2024-09-09');
-- Output: 9

SELECT DAY('2024-09-09');
-- Output: 9

SELECT HOUR('12:34:56'), MINUTE('12:34:56'), SECOND('12:34:56');
-- Output: 12, 34, 56

SELECT DAYOFWEEK('2024-09-09');
-- Output: 2 (Monday)

SELECT DAYOFYEAR('2024-09-09');
-- Output: 253

SELECT WEEK('2024-09-09');
-- Output: 36

SELECT DATE_FORMAT('2024-09-09', '%W, %M %d, %Y');
-- Output: Monday, September 09, 2024

SELECT STR_TO_DATE('09-09-2024', '%d-%m-%Y');
-- Output: 2024-09-09

SELECT LAST_DAY('2024-09-09');
-- Output: 2024-09-30

SELECT EXTRACT(YEAR FROM '2024-09-09');
-- Output: 2024

SELECT COUNT(*) FROM employees;

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT IF(salary > 5000, 'High', 'Low') FROM employees;

SELECT 
  CASE 
    WHEN salary > 5000 THEN 'High'
    WHEN salary BETWEEN 3000 AND 5000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_range
FROM employees;

window functions

1. ROW_NUMBER()

• Description: Assigns a unique, sequential integer to rows within a result set, starting at 1. Rows with the same values in the ORDER BY clause receive different ranks.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Example Output:

2. RANK()

• Description: Assigns a rank to rows within a result set based on the ORDER BY clause. Rows with equal values in the ORDER BY clause receive the same rank, and the next rank will be skipped (i.e., if two rows share rank 1, the next rank will be 3).

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Example Output:

3. DENSE_RANK()

• Description: Similar to RANK(), but it doesn’t skip any ranks. Rows with equal values in the ORDER BY clause receive the same rank, but the next rank is consecutive (i.e., no ranks are skipped).

SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Example Output:

Syntax Breakdown:

ROW_NUMBER() OVER (ORDER BY column_name)
RANK() OVER (ORDER BY column_name)
DENSE_RANK() OVER (ORDER BY column_name)

• ORDER BY: Specifies the column or columns to determine the order of the ranking.
• You can also partition the data using the PARTITION BY clause to restart the ranking for each partition.

4. Using PARTITION BY:

• You can use the PARTITION BY clause to restart the ranking for each subset of data, such as departments.

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_rank
FROM employees;

• This query ranks employees by salary within each department.

Example Output:

Summary of Ranking Functions:

• ROW_NUMBER(): Assigns a unique rank to each row.
• RANK(): Assigns the same rank to ties, but skips the next rank(s).
• DENSE_RANK(): Assigns the same rank to ties, but does not skip ranks.

ALIAS

SQL에서는 WHERE 절에서 select 절에 선언한 컬럼 별칭(alias)를 바로 사용할 수 없음. 왜냐면 SQL 쿼리에서 데이터 필터링을 위한 조건이 먼저 실행되기 때문에, 이 시점에서는 아직 SELECT 절에서 생성된 별칭이 존재하지 않기 때문

해결책: 서브쿼리 또는 HAVING 절을 사용

1. 서브쿼리를 사용한 해결 방법:

SELECT timestampdiff(day, last_login, last_logout) AS timestampdifff, 
       datediff(last_logout, last_login) AS datedifff, 
       last_login, 
       last_logout 
FROM (
    SELECT last_login, 
           last_logout, 
           timestampdiff(day, last_login, last_logout) AS timestampdifff,   ---
           datediff(last_logout, last_login) AS datedifff   ---
    FROM user
) AS subquery
WHERE timestampdifff != datedifff;

여기서 subquery는 먼저 timestampdifff와 datedifff 값을 계산한 후, 외부 쿼리에서 이 값을 필터링함

2. HAVING 절을 사용하는 방법:

만약 GROUP BY가 사용된다면 HAVING 절을 이용할 수 있으나, 이 경우에는 GROUP BY가 필요 없을 때도 활용할 수 있음. HAVING 절은 SELECT 절이 실행된 후에 필터링을 적용하기 때문에 별칭을 사용할 수 있음

SELECT timestampdiff(day, last_login, last_logout) AS timestampdifff, 
       datediff(last_logout, last_login) AS datedifff, 
       last_login, 
       last_logout 
FROM user
HAVING timestampdifff != datedifff;

union vs union all

• union : 결과에서 중복 삭제(더 느림)
• union all: 중복 포함

left join, right join, inner join

• INNER JOIN: Returns only matching rows from both tables. 교집합
• LEFT JOIN(left outer join): Returns all rows from the left table, and matching rows from the right table. If there’s no match, NULL is returned for columns from the right table. (from A left join B A에서 모두 보여주고 B에서 없으면 null)
• RIGHT JOIN(right outer join): Returns all rows from the right table, along with matching rows from the left table. If there is no match, NULL is returned for the left table's columns.
• JOIN (without a prefix) defaults to INNER JOIN.

collation 이란

데이터베이스의 문자열 Datatype(CHAR, VARCHAR, TEXT 등)에는 캐릭터 셋(Character set)과 콜래이션(Collation)이라는 속성이 있다.

캐릭터 셋(Character set)은 각 문자가 컴퓨터에 저장될 때 어떻게 저장될지(encoding)에 대한 규칙의 집합이고,

콜래이션(Collation)은 특정 캐릭터 셋(Character set)에 의해

• 데이터베이스에 저장된 값들을 비교 검색(where clause)하거나
• 문자들을 서로 정렬(order by) 등의 작업을 위해 비교할 때
• 그리고 인덱싱을 할 때

사용하는 규칙들의 집합을 의미한다.

예를 들어 

• int형은 123 < 345 으로 명확히 비교할 수 있고
• date형은 2013-01-01 < 2022-01-01로 명백하나

문자열의 경우

• '가'와 '나' 중 어느 것이 큰지
• 'a'와 'A' 중 어느 것이 큰지 
• '가'와 'ㄱㅏ'는 어떻게 비교해야 하는지

혼란스럽다. 이와 관련하여 정리된 방식이 collation이라고 생각하면 된다.

대표적인 collation 타입

• utf8mb4_bin
  • binary 저장 값으로 정렬; 각 문자를 byte 취급하여 byte 값을 비교(언어적인 규칙이 고려되지 않음)
  • A는 41, a는 61이기 때문에 오름차순 정렬 시 A~Z 다음 a~z가 정렬된다.
    • SELECT HEX(WEIGHT_STRING('A' collate utf8mb4_bin)) as 'A',  HEX(WEIGHT_STRING('a' collate utf8mb4_bin)) as 'a'  FROM dual;
• utf8mb4_general_ci
  • 간단하고 빠르게 사용할 수 있는 타입
  • 모든 유니코드가 고려된 건 아니지만 일반적으로 많이 사용됨
    • 유니코드 중 Basic Multilingual Plane (BMP)를 벗어나면 정렬이 틀리게 될 수 있음
    • 하지만 중국어(C), 일본어(J), 한국어(K) 통칭 CJK는 BMP에 포함되어 있어 국내에서도 잘 쓰이는 타입
  • case insensitive로 A는 41, a도 41로 같기 때문에 A와 a가 혼용되어 정렬된다.
    • SELECT HEX(WEIGHT_STRING('A' collate utf8mb4_general_ci)) as 'A',  HEX(WEIGHT_STRING('a' collate utf8mb4_general_ci)) as 'a'  FROM dual;
• utf8mb4_unicode_ci
  •  모든 유니코드를 고려한 정렬 규칙으로 고려하는 규칙 자체가 많아 utf8mb4_general_ci 방식보다 느림
    • 한국어, 영어, 중국어, 일본어 사용 환경에서는 utf8mb4_general_ci와 동일한 결과를 냄
    • 더 특수한 문자의 정렬 순서는 달라질 수 있음
  • case insensitive로 A는 0E33, a도 0E33으로 같기 때문에 A와 a가 혼용되어 정렬된다.
    • SELECT HEX(WEIGHT_STRING('A' collate utf8mb4_unicode_ci)) as 'A', HEX(WEIGHT_STRING('a' collate utf8mb4_unicode_ci)) as 'a'  FROM dual;

간단하게 collation 타입 읽는 법

ex. utf8mb4_0900_ai_ci

• utf8mb4 : 문자 하나당 1~4byte 할당(mb4 : 4byte 지원), 바로 이어서 지역 및 언어를 나타내는 단어로 세분화되기도 함
  • utf8mb3는 3byte가 할당되는 방식으로 mysql8에서 deprecated
• 0900 : Unicode Collation Algorithm (UCA) version 9.0 표준을 따른다는 뜻
  • mysql8 추가; 더 세분화된 정렬법 적용
• ai : accent insensitive (이전버전에서는 악센트 구분이 안되었으며 MySQL 8.0부터 추가됨)
• ci : case insensitive (대소문자 구분하지 않음)

그 외 아래와 같은 표기법이 사용될 수 있음

mysql8부터 추가된 collation type의 pad attribute

• 이전 버전에서는 PAD SPACE를 사용하던 것과 다르게 NO PAD 속성이 생김
  • 기본 값은 PAD SPACE
• NO PAD 속성은 문자열 끝에 빈 문자열이 있는 경우에 문자열 비교 시 공백까지 포함하여 비교함(공백도 의미를 가진다는 전제)
• 따라서 정렬 시 의도한 대로 정렬이 되지 않을 수 있으니 사용하고 있는 collation type이 어떤 pad attribute를 갖는지 확인해야 함

그냥 접속 시 매번 collation이 다르게 접속될 수 있음

• jdbc 커넥터 버전에 따라 커넥션 별 collation이 달라질 수 있음
• 워크벤치에서도 지정하지 않으면 외부 영향을 받아 달라질 수 있음
• 따라서 커넥터 레벨에서 collation을 지정하거나 접속 시 명령어에 아래 내용 추가하여 항상 고정된 collation으로 붙을 수 있도록 하는 게 좋음

-- Set the character set and collation for the session
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;

collation이 다르게 접속이 될 경우 데이터 저장 시 에러 발생할 가능성 있음

문자열을 기준으로 다음 작업을 할 때 아래 에러가 발생할 수 있음:

• 테이블을 join 하거나
• 값을 비교, 필터링하거나
• string 연산(concat 등)을 할 때

오류 코드: 1267Illegal mix
of collations (utf8_general_ci, IMPLICIT) and (utf8_unicode_ci, IMPLICIT) for
operation '='

이 경우 내 로컬(커넥션) 설정과 서버의 collation 설정이 다르거나 테이블, 콜롬 등에 설정된 collation이 상이하기 때문으로 collation에 대해서 확인 필요

그럴리는 없겠지만 혹시 테이블, 콜롬별 collation 설정이 다르다면 아래와 같이 join 조건에 collate 타입을 명시해야 한다.

SELECT *
FROM table1
JOIN table2 ON table1.name = table2.description COLLATE utf8_general_ci;

지금 내가 사용하는 디비에서 collation 값 조회하는 방법

• mysql8

show variables where variable_name like '%collation%';

Variable_name                |Value             |
-----------------------------+------------------+
collation_connection         |utf8mb4_0900_ai_ci|
collation_database           |utf8mb4_general_ci|
collation_server             |utf8mb4_general_ci|
default_collation_for_utf8mb4|utf8mb4_0900_ai_ci|


#전체 값 확인
SHOW COLLATION WHERE Charset = 'utf8mb4';


Collation                 |Charset|Id |Default|Compiled|Sortlen|Pad_attribute|
--------------------------+-------+---+-------+--------+-------+-------------+
utf8mb4_0900_ai_ci        |utf8mb4|255|Yes    |Yes     |      0|NO PAD       |
utf8mb4_0900_as_ci        |utf8mb4|305|       |Yes     |      0|NO PAD       |
utf8mb4_0900_as_cs        |utf8mb4|278|       |Yes     |      0|NO PAD       |
utf8mb4_0900_bin          |utf8mb4|309|       |Yes     |      1|NO PAD       |
utf8mb4_bg_0900_ai_ci     |utf8mb4|318|       |Yes     |      0|NO PAD       |
...생략

• mysql5

show variables where variable_name like '%collation%';

Variable_name       |Value             |
--------------------+------------------+
collation_connection|utf8mb4_general_ci|
collation_database  |utf8mb4_general_ci|
collation_server    |utf8mb4_general_ci|


#전체 값 확인
SHOW COLLATION WHERE Charset = 'utf8mb4';

Collation             |Charset|Id |Default|Compiled|Sortlen|
----------------------+-------+---+-------+--------+-------+
utf8mb4_general_ci    |utf8mb4| 45|Yes    |Yes     |      1|
utf8mb4_bin           |utf8mb4| 46|       |Yes     |      1|
utf8mb4_unicode_ci    |utf8mb4|224|       |Yes     |      8|
utf8mb4_icelandic_ci  |utf8mb4|225|       |Yes     |      8|

collation 변경 방법

1) 데이터베이스 레벨

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2) 테이블 레벨

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;

3) 콜롬 레벨

ALTER TABLE mytable MODIFY name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

4) 세션 레벨

SET collation_connection = 'utf8mb4_general_ci' ;

5) 쿼리 레벨(insert문 동일)

SELECT 
    @_now := now(),
    @ver := '1.17.0' collate utf8mb4_general_ci , 
    @domain := 'my domain' collate utf8mb4_general_ci , 
    @port_ := '1234'
;

6) 서버 레벨

• mysql서버 띄울 때  --character-set-server 혹은 --collation-server 옵션을 주던가
• server.cnf파일에 옵션값 설정

[mysqld]
character-set-server=latin1
collation-server=latin1_swedish_ci

결론

• 같은 문자셋이라도 콜레이션에 따라 영어의 경우 대소문자의 구분, 일본어의 경우 히라가나와 가타카나의 구분, 한글 자음과 결합문자를 구분하는 방법 등이 달라짐
• 관련해서 정렬 시 정확도와 검색 속도에 영향이 있음
• MySql 5-> 8로 올릴 때 collation 설정 값이 정렬 등에 영향을 줄 수 있다는 것을 인지할 필요 있음
• MySQL 8.0의 기본 collation 인 utf8 mb4_0900_ai_ci는 utf8이며 글자당 4byte까지 저장하고, 0900 버전의 UCA 규칙을 따르며 accent, 대소문자, 히라가나와 가타카나, 한글 자음과 결합문자를 구분하지 않음

관련 상세 내용은 버전별 공식 문서를 확인하자

https://dev.mysql.com/doc/refman/8.4/en/charset.html

https://dev.mysql.com/doc/refman/5.7/en/charset.html

관련 내용 테스트 한 블로그

https://blog.naver.com/sory1008/223071678680