MySQL 테이블 및 SQL 설계

MySQL 테이블 및 SQL 설계은(는) 무엇을 하나요?

적용 가능성

• 작업 또는 프로젝트가 MySQL을 대상 데이터베이스로 설정하고 데이터 모델링, 테이블 설계, SQL, 인덱스, 트랜잭션 또는 동시성 제어와 관련된 경우 적용합니다. 명시적 호출은 필요하지 않습니다.
• SDLC 워크플로와 무관하게 사용합니다. 일반 데이터베이스 작업에 MySQL을 선택하거나 다른 데이터베이스만 관련된 작업에는 적용하지 마십시오.

사용법

• 공식 MySQL 8.4 의미 체계를 검증 기준으로 사용하며 배포 버전을 제한하지 않습니다. 차이가 중요할 때 실제 대상 버전의 기능 지원, 제약 조건 적용 및 비권장 여부를 확인합니다.
• 작업과 관련된 비즈니스 의미, 무결성 요구 사항, 주요 접근 패턴, 데이터 규모 및 증가, 동시성, 일관성 요구 사항을 설정합니다. 가능한 경우 기존 설계 및 작업 증거를 사용하고, 주요 정보가 누락된 경우 가정과 그 영향을 명시합니다.
• 로컬 InnoDB 트랜잭션에 트랜잭션 및 동시성 규칙을 적용합니다. 트랜잭션 내 쿼리 금지, CHECK 제약 조건, 외래 키 제약 조건 및 저장 프로시저를 MySQL 제한 사항이 아닌 프로젝트 관례로 명시적으로 식별합니다.
• 레코드는 밀리초당 최대 한 번 수정된다고 가정합니다. 이 가정은 updated_at이 재사용되지 않는 버전 식별자임을 보장하지 않습니다. 동시성 제어에 사용하기 전에 타임스탬프 검사의 조건을 적용하십시오.
• 작업과 관련된 규칙만 적용합니다. 규칙에 대한 설명이 필요한 경우 MySQL 설계 예제에서 해당 주제를 읽으십시오. 기본적으로 모든 예제를 로드하지 마십시오.

데이터 모델링

엔티티, 관계 및 필드 소유권

• 행 세분성: 행이 나타내는 비즈니스 객체, 관계 또는 이벤트를 정의합니다. 테이블 내에서 세분성을 일관되게 유지합니다. 예제를 참조하십시오.

• 관계 및 고유성: 양쪽의 최소 및 최대 카디널리티를 정의합니다. 일대다 참조는 다 쪽에 저장하고, 고유 참조를 사용하여 일대일 관계를 최대 하나의 일치로 제한하며, 다대다 관계에는 접합 테이블을 사용합니다. 동일한 객체의 반복된 연관 관계의 경우 각 연관 관계의 비즈니스 의미, ID 및 고유성 범위를 정의합니다. 예제를 참조하십시오.

• 필드 소유권: 값을 결정하는 객체, 관계 또는 이벤트와 함께 필드를 배치합니다. 현재 사실의 복제를 피합니다. 현재 값 업데이트의 영향을 받지 않는 이벤트 시간 값에 기록 스냅샷을 유지합니다. 예제를 참조하십시오.

• 참조 무결성: 참조 대상이 존재해야 하는지, 그리고 참조를 생성하거나 변경할 때 충족해야 하는 상태를 정의합니다. 대상이 삭제되거나 유효하지 않게 될 때 기존 참조가 처리되는 방식을 정의합니다. 키 및 제약 조건 및 동시성 제어에 따라 비즈니스 쓰기 흐름을 통해 이러한 요구 사항을 적용합니다. 예제를 참조하십시오.

테이블 설계

이름 및 주석

• 소문자, 숫자 및 밑줄을 사용하여 데이터베이스, 테이블 및 열의 이름을 지정합니다. 숫자로 시작하거나, 예약어를 사용하거나, 불명확한 약어를 사용하지 마십시오.
• 열 또는 목적에 따라 인덱스 이름을 지정합니다. uniq_ 및 idx_는 고유 인덱스와 일반 인덱스를 식별하는 데 사용할 수 있습니다. 기본 인덱스 이름은 PRIMARY입니다.
• 의미, 상태 값 또는 제약 조건이 변경되면 열 주석 및 관련 정의를 업데이트합니다.

데이터 타입

• 동일한 값을 참조하거나 비교하는 데 사용되는 열은 데이터 타입과 부호에서 호환되도록 유지합니다. 가능한 경우 일관된 정의를 사용합니다.
• 정확한 금액은 명시적인 정밀도와 스케일의 DECIMAL 또는 정의된 최소 단위의 정수로 나타냅니다. 통화 및 반올림 규칙을 정의합니다. 저장, 매개변수 및 계산을 통해 정확한 표현을 유지합니다. 변환 전에 부동 소수점으로 계산하지 마십시오.
• FLOAT, DOUBLE 또는 DECIMAL에 비권장 UNSIGNED를 설정하지 마십시오.
• 부울에는 is_xxx 및 TINYINT UNSIGNED를 사용하고, 애플리케이션 검증을 통해 값을 0 또는 1로 제한합니다.
• CHAR를 사용할 때 후행 공백 처리가 필드의 비즈니스 의미와 일치하는지 확인합니다.
• TIMESTAMP는 세션 시간대와 UTC 간에 변환됩니다. DATETIME은 변환하지 않습니다. 필요한 경우 원래 시간대 이름이나 오프셋을 별도로 저장합니다.
• 생성에는 created_at을, 마지막 수정에는 updated_at을 사용합니다. 변경 가능한 비즈니스 레코드에는 둘 다 필요합니다. 추가 전용 레코드는 생성 시간만 필요합니다. 감사 요구 사항에 따라 접합 테이블 또는 기타 테이블에 추가합니다.
• 감사 타임스탬프에는 DATETIME(3), 밀리초 정밀도 및 베이징 시간을 사용하고, 연결 시간대는 +08:00입니다.
• 감사 타임스탬프를 NOT NULL DEFAULT CURRENT_TIMESTAMP(3)로 설정하고 updated_at에는 ON UPDATE CURRENT_TIMESTAMP(3)를 추가합니다. 데이터베이스가 유지 관리하도록 합니다. 애플리케이션에서 중복하여 할당하지 마십시오.

키 및 제약 조건

• InnoDB 테이블에 대해 안정적이고 짧은 기본 키를 명시적으로 정의합니다. 보조 인덱스 레코드에는 기본 키 열이 포함되므로, 넓은 기본 키는 보조 인덱스 비용을 증가시킵니다.
• 필수 필드는 NOT NULL로 표시합니다.
• CHECK를 사용하지 마십시오. 모든 애플리케이션 쓰기 진입점에서 범위, 열거형 및 필드 조합의 유효성을 검사하고 유효하지 않은 쓰기를 거부합니다. 예제를 참조하십시오.
• 비즈니스 고유성은 삽입 전 쿼리가 아닌 고유 인덱스로 적용합니다. 고유 인덱스는 여러 NULL 값을 허용합니다.
• 적용 가능한 비즈니스 운영 및 테넌트 범위 내에서 멱등성 식별자에 대한 고유성을 적용하고, 참여하는 모든 열은 NOT NULL입니다.
• 외래 키 제약 조건 또는 캐스케이드를 사용하지 마십시오. 비즈니스 쓰기 흐름을 통해 참조 무결성을 유지합니다.

문자셋 및 데이터 정렬

• 텍스트에는 utf8mb4를 사용합니다.
• 동일한 값을 참조하거나 비교하는 데 사용되는 텍스트 열은 문자셋과 데이터 정렬에서 호환되도록 유지합니다. 가능한 경우 일관된 정의를 사용합니다.
• 데이터 정렬을 변경하기 전에 동등성 변경 및 고유성을 위반하는 새 중복 값을 확인합니다.

SQL 프로그래밍

• 저장 프로시저를 사용하지 마십시오. 애플리케이션 코드가 분기, 커밋 및 롤백을 제어합니다.

쿼리

• 안정적인 비즈니스 쿼리에서 필요한 열을 명시적으로 선택합니다. SELECT *는 피합니다.
• 페이지 매기기 또는 단일 행 선택에 결정적 순서가 필요한 경우 행을 고유하게 정렬하는 정렬 키 조합을 사용합니다.
• 모든 정렬 키를 커서에 포함합니다. 동시 정렬 키 변경은 페이지 간 누락/중복을 유발할 수 있습니다. 커서 페이지 매기기는 일관된 페이지 간 스냅샷을 보장하지 않습니다.

데이터 수정

• INSERT IGNORE 또는 UPDATE IGNORE를 사용하여 실패해야 하는 오류를 숨기지 마십시오.
• 빈 매개변수 또는 누락된 동적 조건으로 인해 UPDATE/DELETE가 전체 테이블 작업으로 저하되는 것을 방지합니다. LIMIT는 비즈니스 범위 조건을 대체하지 않습니다.
• 위의 감사 타임스탬프 정의에서 updated_at은 행 내용이 변경될 때 자동으로 변경됩니다. 모든 업데이트 문의 실행 시간을 기록하지는 않습니다.
• 일반 업데이트를 REPLACE INTO로 대체하지 마십시오. 기본 키/고유 키 충돌 시 해당 대체 의미 체계는 지정되지 않은 필드를 기본값으로 재설정하고 삭제/삽입 동작을 트리거할 수 있습니다.

표현식 및 함수

• 비교 유형을 일치시킵니다. 암시적 변환이 중요한 비즈니스 비교를 결정하도록 하지 마십시오.
• 길이 확인에서 바이트, 문자 및 사용자에게 표시되는 그래핌을 구분합니다. LENGTH는 바이트를 계산하고 CHAR_LENGTH는 문자(표시되는 그래핌이 아님)를 계산합니다.
• NOT IN이 사용하는 집합에 NULL이 포함된 경우, 어떤 멤버와도 일치하지 않는 값이 UNKNOWN을 산출할 수 있습니다. 이를 확인된 비멤버십으로 해석하지 마십시오.
• COUNT(DISTINCT col)은 NULL을 제외합니다. 다중 열 형식은 참여하는 모든 열이 NULL이 아닌 경우에만 고유한 조합을 계산합니다.
• SUM은 빈 입력 또는 모두 NULL 입력에 대해 NULL을 반환합니다. GROUP BY가 그룹을 형성하지 않으면 해당 그룹에 대한 행이 반환되지 않습니다. 비즈니스 규칙이 빈 집계를 0으로 정의하는 경우에만 0으로 대체합니다.
• 반개방 시간 간격을 선호합니다. 시작은 포함하고 끝은 제외합니다. 저장된 값과 동일한 시간 기준으로 비즈니스 시간대에서 경계를 계산합니다.

트랜잭션 및 일관성

트랜잭션 관리

• 트랜잭션 내 명령문: 명시적 트랜잭션 외부에서 쿼리, 비즈니스 검증 및 쓰기 준비를 수행합니다. 트랜잭션 내에서는 필요한 데이터 수정 및 트랜잭션 제어만 허용합니다. 하위 쿼리를 포함한 일반 쿼리, 잠금 쿼리 및 쓰기 내 쿼리 구성 요소를 금지합니다. 예제를 참조하십시오.
• 트랜잭션 연결: 동일한 트랜잭션 내에서 동일한 연결에서 함께 성공해야 하는 쓰기를 실행합니다. 연결 전환, 비동기 실행 및 전파 설정은 해당 경계를 유지해야 합니다. 예제를 참조하십시오.
• 암시적 커밋: START TRANSACTION으로 중첩 트랜잭션을 시뮬레이션하거나 비즈니스 쓰기 사이에서 autocommit을 0에서 1로 전환하지 마십시오. 둘 다 암시적으로 커밋합니다. ALTER TABLE 및 TRUNCATE TABLE과 같은 DDL을 비즈니스 쓰기 트랜잭션 외부에 유지합니다. 예제를 참조하십시오.
• 실패 처리: 필수 쓰기 오류 또는 충족되지 않은 비즈니스 조건 발생 시 이후 쓰기를 중지하고 전체 트랜잭션을 롤백합니다. 실패한 명령문이 이전 쓰기를 취소한다고 가정하지 마십시오. 교착 상태 또는 잠금 시간 초과 후 전체 롤백을 보장하고 안전한 경우에만 유한 재시도 제한으로 전체 트랜잭션을 재시도합니다. 불확실한 커밋의 경우 먼저 멱등성 식별자를 사용하여 확인합니다. 예제를 참조하십시오.
• 외부 작업: 로컬 트랜잭션은 외부 호출 또는 메시지 전송을 원자적으로 커밋/롤백하지 않습니다. 예제를 참조하십시오.

동시성 제어

• 비즈니스 조건부 업데이트: 쓰기 시간 비즈니스 필드 조건을 선호합니다. 트랜잭션 외부에서 읽은 데이터에서 새 값을 계산할 때는 관련 원래 값 또는 유효한 버전 식별자를 확인하는 조건을 포함하여 동시 변경 사항을 덮어쓰지 않도록 합니다. 하나의 조건문으로 충분할 때는 명시적 트랜잭션을 추가하지 마십시오. 예제를 참조하십시오.
• 타임스탬프 검사: 적절한 비즈니스 필드가 없는 경우, 모든 관련 수정이 updated_at을 변경하고 검증 중에 이전 값을 재사용할 수 없는 경우에만 원래 updated_at과 일치시킵니다. 읽기, 쓰기, 직렬화 및 바인딩에서 밀리초를 유지합니다. 그렇지 않으면 재사용되지 않는 버전 식별자 또는 다른 동시성 메커니즘을 사용합니다. 예제를 참조하십시오.
• 참조 무결성: 조건부 업데이트, 고유성 및 필요한 트랜잭션을 사용하여 대상 삭제/무효화에 대한 참조 생성/변경을 조정합니다. 예제를 참조하십시오.
• 업데이트 결과: 드라이버가 일치하거나 변경된 행을 보고하는지 확인한 다음 필요한 쓰기가 성공했는지 평가합니다. 충족되지 않은 조건은 성공이 아닙니다. 0개의 변경된 행만으로는 충족되지 않은 조건을 증명하지 않습니다. 예제를 참조하십시오.
• 쓰기 순서: 경쟁 트랜잭션은 일관된 테이블/레코드 순서를 사용해야 합니다. SQL 텍스트 순서는 실제 잠금 순서를 설정하지 않습니다. 예제를 참조하십시오.

데이터베이스 보안

• 애플리케이션 데이터를 SQL에 연결하는 대신 매개변수로 바인딩합니다.
• 동적 테이블/열 이름 및 정렬 방향을 코드 허용 목록을 통해 SQL 구조에 매핑합니다. 값 자리 표시자는 이를 대체할 수 없습니다.

성능 최적화

• 요청 왕복: 정확성과 전반적인 성능을 유지하면서 비즈니스 작업당 데이터베이스 요청을 줄입니다.

인덱스 최적화

• 인덱스 재사용 및 변경: 기본 쿼리에서 인덱스를 재사용합니다. 기존 경로가 성능 요구 사항을 충족하는 경우 새 SQL/조건에 대해서만 인덱스를 추가하지 마십시오. 병합하거나 교체하기 전에 다른 쿼리 및 고유 제약 조건을 확인합니다. 예제를 참조하십시오.
• 낮은 카디널리티 필드: 고유 값이 적다는 이유만으로 인덱스를 거부하지 마십시오. 대상 분포, 결합된 필터, 정렬, 커버링 이점, 테이블 조회 및 쓰기 비용을 고려합니다. 예제를 참조하십시오.
• 접두사 인덱스: 실제 데이터에서 문자열 접두사 길이를 선택합니다. 고유 접두사 인덱스는 접두사를 공유하는 고유한 전체 값을 거부할 수 있으며 전체 값 고유성을 대체할 수 없습니다. 예제를 참조하십시오.
• 복합 인덱스: 필터링, 왼쪽 접두사 재사용, 정렬 및 그룹화의 균형을 유지합니다. 동등성 열은 일반적으로 범위 열보다 앞에 옵니다. 선택도별로 보편적으로 정렬하지 마십시오. 예제를 참조하십시오.
• 정렬 비용: filesort만으로는 인덱스가 나쁘다는 의미가 아닙니다. 추가된 인덱스의 정렬, 스캔 및 유지 관리 비용을 비교합니다. 예제를 참조하십시오.
• 인덱싱된 열 및 표현식: 일일 필터링에는 직접 시간 범위 조건을 선호합니다. 표현식이 필요한 경우 일치하는 함수 기반 또는 생성된 열 인덱스를 평가합니다. 예제를 참조하십시오.
• 선행 와일드카드: 일반 B-트리 인덱스는 일반적으로 선행 와일드카드 검색을 효과적인 범위 스캔으로 전환할 수 없습니다. 전체 텍스트 또는 전용 검색은 원래 일치 의미 체계를 유지해야 합니다. 예제를 참조하십시오.

쿼리 최적화

• 배치 쿼리: 병합 가능한 행별 쿼리를 조인으로 대체합니다.