집 >데이터 베이스 >MySQL 튜토리얼 >일반적으로 사용되는 SQL Server 사양 모음
일반적으로 사용되는 SQL Server 사양 모음
- 巴扎黑원래의
- 2017-05-01 11:25:151273검색
공통 필드 유형 선택
1. 문자형은 varchar/nvarchar 데이터형 사용을 권장합니다
2. 금액통화는 Money 데이터 타입을 사용하는 것을 권장합니다
3. 과학적 표기법은 숫자 데이터 형식을 사용하는 것이 좋습니다
4. 자체 증가하는 로고에는 bigint 데이터 형식을 사용하는 것이 좋습니다(데이터 양이 많고 int 형식으로는 로드할 수 없으므로 향후 변환이 번거로움)
5. 시간 유형은 날짜/시간 데이터 유형을 권장합니다
6. 이전 데이터 유형인 text, ntext 및 image를 사용하는 것은 금지되어 있습니다
7. xml 데이터 형식인 varchar(max), nvarchar(max) 사용을 금지합니다
제약 조건 및 인덱스
각 테이블에는 기본 키가 있어야 합니다
각 테이블에는 엔터티 무결성을 강화하는 데 사용되는 기본 키가 있어야 합니다.
단일 테이블에는 기본 키가 하나만 있을 수 있습니다(비어 있거나 중복된 데이터는 허용되지 않음)
단일 필드 기본 키를 사용해 보세요
외래 키는 허용되지 않습니다
외래 키는 테이블 구조 변경 및 데이터 마이그레이션의 복잡성을 증가시킵니다
외래 키는 삽입 및 업데이트 성능에 영향을 미칩니다.
데이터 무결성은 프로그램에 의해 제어됩니다
NULL 속성
새로 추가된 테이블은 모든 필드에서 NULL이 금지됩니다
(새 테이블은 왜 NULL을 허용하지 않나요?
NULL 값을 허용하면 애플리케이션이 더 복잡해집니다. 다양한 예상치 못한 버그를 방지하려면 특정 로직 코드를 추가해야 합니다
값이 3개인 논리, 모든 등호("=") 쿼리에는 null인지 판단이 추가되어야 합니다.
Null=Null, Null!=Null, not(Null=Null), not(Null!=Null)은 모두 알 수 없으며 사실이 아닙니다)
예를 들어 설명하겠습니다.
테이블의 데이터가 그림과 같은 경우:
aa와 동일한 이름을 제외한 모든 데이터를 찾으려고 하는데 실수로 SELECT * FROM NULLTEST WHERE NAME<>'aa'
를 사용했습니다. 결과는 예상과 달랐습니다. 실제로 name=bb인 데이터 레코드만 찾았고 name=NULL
인 데이터 레코드는 찾지 못했습니다. 그러면 aa와 동일한 이름을 제외한 모든 데이터를 어떻게 찾을 수 있나요?
ISNULL 함수만 사용할 수 있습니다. SELECT * FROM NULLTEST WHERE ISNULL(NAME,1)<>'aa'
그러나 ISNULL이 심각한 성능 병목 현상을 일으킬 수 있다는 사실을 모를 수도 있으므로 대부분의 경우 사용자가 쿼리하기 전에 유효한 데이터를 입력할 수 있도록 응용 프로그램 수준에서 사용자 입력을 제한하는 것이 가장 좋습니다.
이전 테이블에 추가된 새 필드는 NULL을 허용해야 합니다(전체 테이블의 데이터 업데이트 및 장기 잠금 유지로 인한 차단을 방지하기 위해)(주로 이전 테이블의 변환을 고려하기 위한 것입니다)
인덱스 디자인 가이드라인
WHERE 절에서 자주 사용되는 컬럼에 대해 인덱스를 생성해야 한다
테이블 조인에 자주 사용되는 컬럼에 인덱스를 생성해야 한다
ORDER BY 절에서 자주 사용되는 컬럼에 대해 인덱스를 생성해야 한다
인덱스를 사용하는 쿼리보다 전체 테이블 스캔 작업이 더 빠를 수 있으므로 작은 테이블(몇 페이지만 사용하는 테이블)에는 인덱스를 생성하면 안 됩니다.
-
단일 테이블의 인덱스 개수는 6개를 초과하지 않는다
선택성이 낮은 필드에는 단일 열 인덱스를 작성하지 마십시오
고유한 제약 조건을 최대한 활용하세요
인덱스에는 5개 이하의 필드(포함 열 포함)가 포함됩니다.
선택성이 낮은 필드에 대해서는 단일 열 인덱스를 생성하지 마십시오
SQL SERVER에는 인덱스 필드의 선택성에 대한 요구 사항이 있습니다. 선택성이 너무 낮으면 SQL SERVER는
- 인덱스 생성에 적합하지 않은 필드: 성별, 0/1, TRUE/FALSE
- 인덱스 생성에 적합한 필드: ORDERID, UID 등
- 인덱스는 쿼리 속도를 높이지만 쓰기 성능에 영향을 미칩니다
- 테이블의 인덱스는 해당 테이블과 관련된 모든 SQL을 결합하여 종합적으로 생성하고
- 을 병합해 보아야 합니다. 결합형 인덱스의 원리는 필터링성이 좋은 필드가 상위에 배치되는 것입니다.
- 인덱스가 너무 많으면 컴파일 시간이 늘어날 뿐만 아니라 최상의 실행 계획을 선택하는 데이터베이스의 기능에도 영향을 미칩니다
- 데이터베이스에서 복잡한 작업을 수행하는 것은 금지되어 있습니다
SELECT *
은 금지됩니다. 인덱스 열에는 함수나 계산을 사용하지 마세요.
커서 금지
트리거 금지
인덱스 지정은 금지
변수/매개변수/연관 필드 유형은 필드 유형
과 일치해야 합니다. 매개변수화된 쿼리
JOIN 수 제한
SQL 문의 길이와 IN 절의 개수를 제한하세요
대규모 거래 운영은 피하세요
영향을 받은 행 개수 정보를 끄면
이 반환됩니다. 꼭 필요한 경우가 아니면 SELECT 문에는 NOLOCK
을 추가해야 합니다. UNION ALL을 사용하여 UNION
교체 페이징이나 TOP을 이용하여 대용량 데이터 조회
재귀 쿼리 수준 제한
NOT EXISTS는 NOT IN
을 대체합니다. 임시 테이블 및 테이블 변수
지역 변수를 사용하여 평균 실행 계획 선택
OR 연산자를 사용하지 마세요.
거래 예외 처리 메커니즘 추가
출력 열은 두 부분으로 구성된 명명 형식
을 사용합니다. 데이터베이스에서 복잡한 작업을 수행하는 것은 금지되어 있습니다
XML 구문 분석
문자열 유사성 비교
문자열 검색(Charindex)
복잡한 작업은 프로그램 측면에서 완료
SELECT *
사용이 금지됩니다. 메모리 소비 및 네트워크 대역폭 감소
쿼리 최적화 프로그램에 인덱스
에서 필요한 열을 읽을 수 있는 기회를 제공합니다. 테이블 구조가 변경되면 쿼리 오류가 발생하기 쉽습니다
인덱스 컬럼에 함수나 계산을 사용하는 것은 금지되어 있습니다
인덱스 컬럼에 함수나 계산을 사용하는 것은 금지되어 있습니다
where 절에서 인덱스가 함수의 일부인 경우 최적화 프로그램은 더 이상 인덱스를 사용하지 않고 전체 테이블 스캔을 사용합니다
Col1 필드에 작성된 인덱스가 있다고 가정하면 다음 시나리오에서는 인덱스가 사용되지 않습니다.
ABS[Col1]=1
[Col1]+1>9
또 다른 예를 들어보겠습니다
위와 같은 쿼리는 O_OrderProcess 테이블의 PrintTime 인덱스를 사용할 수 없으므로 다음 쿼리 SQL
을 사용합니다.
인덱스 컬럼에 함수나 계산을 사용하는 것은 금지되어 있습니다
Col1 필드에 인덱스가 구축되어 있다고 가정하면 해당 인덱스는 다음 시나리오에서 사용될 수 있습니다.
[Col1]=3.14
[Col1]>100
[Col1] 0과 99 사이
[Col1] 'abc%' 좋아요
[Col1] IN(2,3,5,7)
LIKE 쿼리의 인덱스 문제
1.[Col1] like “abc%” –indexeek 인덱스 쿼리를 사용합니다
2.[Col1] like “%abc%” –index scan 그리고 이것은 인덱스 쿼리를 사용하지 않습니다
3.[Col1] like “%abc” –index scan 인덱스 쿼리를 사용하지 않습니다
위의 세 가지 예를 통해 LIKE 조건 앞에 퍼지 일치를 사용하지 않는 것이 가장 좋다는 점을 모두가 이해해야 한다고 생각합니다. 그렇지 않으면 인덱스 쿼리가 사용되지 않습니다.
커서 사용금지
관계형 데이터베이스는 집합 연산에 적합합니다. 즉, WHERE 절과 선택 열에 의해 결정된 결과 집합에 대해 집합 연산이 수행됩니다. 커서는 집합이 아닌 연산을 제공하는 방법입니다. 일반적인 상황에서 커서에 의해 구현된 함수는 클라이언트측 루프에 의해 구현된 함수와 동일한 경우가 많습니다.
커서는 서버 메모리에 결과 세트를 배치하고 루프를 통해 레코드를 하나씩 처리하는데, 이는 많은 데이터베이스 자원(특히 메모리 및 잠금 자원)을 소모합니다.
(게다가 커서는 정말 복잡하고 사용하기 어려우니 최대한 적게 사용하세요)
트리거 사용 금지
트리거는 애플리케이션에 불투명합니다(애플리케이션 수준에서는 트리거가 언제 트리거될지, 언제 발생할지 알 수 없습니다. 설명할 수 없는 느낌입니다...)
쿼리에 인덱스를 지정하는 것은 금지되어 있습니다
With(index=XXX) (쿼리에서는 일반적으로 With(index=XXX)를 사용하여 인덱스를 지정합니다.)
데이터가 변경됨에 따라 쿼리문에서 지정한 인덱스 성능이 최적이 아닐 수 있습니다
인덱스는 애플리케이션에 투명해야 합니다. 지정된 인덱스가 삭제되면 쿼리 오류가 발생하여 문제 해결에 도움이 되지 않습니다.
새로 생성된 인덱스는 애플리케이션에서 즉시 사용할 수 없으며 게시해야 적용됩니다.
변수/매개변수/연관 필드 유형은 필드 유형과 일치해야 합니다. (이것은 이전에 크게 신경 쓰지 않았던 부분입니다.)
대규모 테이블 스캔의 경우 특히 심각한 유형 변환의 추가 CPU 소비를 피하세요
위의 두 사진을 보면 굳이 설명할 필요가 없을 것 같은데, 다들 이미 알고 계실 텐데요.
데이터베이스 필드 유형이 VARCHAR인 경우 애플리케이션에서 유형을 AnsiString으로 지정하고 길이를 명확하게 지정하는 것이 가장 좋습니다
데이터베이스 필드 유형이 CHAR인 경우 애플리케이션에서 유형을 AnsiStringFixedLength로 지정하고 길이를 명확하게 지정하는 것이 가장 좋습니다
데이터베이스 필드 유형이 NVARCHAR인 경우 애플리케이션에서 유형을 문자열로 지정하고 길이를 명확하게 지정하는 것이 가장 좋습니다
매개변수화된 쿼리
쿼리 SQL은 다음과 같은 방법으로 매개변수화할 수 있습니다.
sp_executesql
준비된 쿼리
저장 프로시저
사진으로 설명해보죠, 하하.
JOIN 수 제한
단일 SQL 문의 테이블 JOIN 개수는 5개를 초과할 수 없습니다.
JOIN이 너무 많으면 쿼리 분석기가 잘못된 실행 계획을 실행하게 됩니다.
JOIN이 너무 많으면 실행 계획을 세울 때 비용이 많이 소모됩니다
IN 절의 조건 수 제한
IN 절에 매우 많은 수(천 개)의 값을 포함하면 리소스가 소모되고 오류 8623 또는 8632가 반환될 수 있습니다. IN 절의 조건 수는 100개로 제한되어야 합니다
대규모 거래 운영은 피하세요
데이터를 업데이트해야 할 때만 트랜잭션을 시작하여 리소스 잠금 유지 시간을 줄입니다.
트랜잭션 예외 캡처 전처리 메커니즘 추가
데이터베이스에서의 분산 트랜잭션 사용은 금지됩니다
사진을 사용하여 설명하세요
즉, 1,000행의 데이터를 모두 업데이트한 후에 tran을 커밋해서는 안 됩니다. 이 1,000행의 데이터를 업데이트할 때 리소스를 독점하여 다른 트랜잭션을 처리할 수 없게 만드는 것은 아닌지 생각해 보세요.
영향을 받은 행 개수 정보를 닫고
을 반환합니다. Set Nocount On 표시 SQL 문에서 영향을 받은 행 개수 정보 반환을 취소하고 네트워크 트래픽을 줄입니다
꼭 필요한 경우가 아니면 SELECT 문에 NOLOCK
을 추가해야 합니다. 꼭 필요한 경우가 아니면 모든 선택 문에 NOLOCK을 추가하도록 하세요
더티 읽기가 허용되도록 지정합니다. 다른 트랜잭션이 현재 트랜잭션에서 읽은 데이터를 수정하는 것을 방지하기 위해 공유 잠금이 발행되지 않으며, 다른 트랜잭션에서 설정된 배타적 잠금은 현재 트랜잭션이 잠긴 데이터를 읽는 것을 방지하지 않습니다. 더티 읽기를 허용하면 더 많은 동시 작업이 발생할 수 있지만 비용은 읽기 후 다른 트랜잭션에 의해 롤백되는 데이터 수정입니다. 이로 인해 트랜잭션에 오류가 발생하거나, 커밋되지 않은 사용자 데이터가 표시되거나, 사용자에게 기록이 두 번 표시되거나 기록이 전혀 표시되지 않을 수 있습니다.
UNION ALL을 사용하여 UNION
교체 UNION ALL을 사용하여 UNION
교체 UNION은 SQL 결과 세트의 순서를 취소하고 CPU, 메모리 등의 소비를 증가시킵니다.
많은 양의 데이터를 조회하려면 페이징이나 TOP을 이용하세요
IO 및 네트워크 대역폭의 병목 현상을 방지하기 위해 레코드 반환 수를 합리적으로 제한합니다.
재귀 쿼리 수준 제한
불합리한 재귀 CTE가 무한 루프에 들어가는 것을 방지하려면 MAXRECURSION을 사용하세요.
임시 테이블 및 테이블 변수
지역 변수를 사용하여 평균 실행 계획 선택
저장 프로시저나 쿼리에서 데이터 분포가 매우 고르지 않은 테이블에 액세스하면 저장 프로시저나 쿼리가 최적이 아닌 실행 계획을 사용하게 되어 높은 CPU 및 많은 수의 IO 읽기와 같은 문제가 발생하는 경우가 많습니다. 잘못된 실행 계획을 방지합니다.
지역 변수를 사용하면 SQL은 컴파일 시 이 지역 변수의 값을 알지 못합니다. 이때 SQL은 테이블의 일반적인 데이터 분포를 기반으로 반환 값을 "추측"합니다. 사용자가 저장 프로시저나 명령문을 호출할 때 어떤 변수 값을 대체하더라도 생성되는 계획은 동일합니다. 이러한 계획은 일반적으로 좀 더 온건하고 최선의 계획은 아닐 수도 있지만 일반적으로 최악의 계획도 아닙니다
쿼리의 지역 변수가 부등식 연산자를 사용하는 경우 쿼리 분석기는 간단한 30% 계산을 사용하여
예상 행 수 =(총 행 * 30)/100
쿼리의 지역 변수가 항등 연산자를 사용하는 경우 쿼리 분석기는 정밀도 * 추정할 총 테이블 레코드 수
를 사용합니다.
예상 행 수 = 밀도 * 총 행
OR 연산자를 사용하지 마세요
OR 연산자의 경우 일반적으로 전체 테이블 스캔이 사용됩니다. 이를 여러 쿼리로 나누고 UNION/UNION ALL을 구현하는 것을 고려하세요. 여기서 쿼리가 인덱스로 이동하여 더 작은 결과 집합을 반환할 수 있는지 확인해야 합니다
거래 예외 처리 메커니즘 추가
애플리케이션은 사고를 잘 처리하고 적시에 롤백을 수행해야 합니다.
연결 속성 설정 "set xact_abort on"
출력 열은 두 부분으로 구성된 명명 형식
을 사용합니다. 2단계 명명 형식: 테이블 이름.필드 이름
JOIN 관계가 있는 TSQL에서는 필드가 어느 테이블에 속해 있는지를 필드에 나타내야 합니다. 그렇지 않으면 향후 테이블 구조가 변경된 후 모호한 열 이름으로 인한 프로그램 호환성 오류가 발생할 수 있습니다
건축설계
읽고 쓰기 분리
스키마 분리
데이터 수명주기
읽기와 쓰기의 분리
읽기와 쓰기의 분리를 설계 초기부터 고려하여 동일한 라이브러리를 읽고 쓰더라도 빠른 확장에 유리합니다
읽기 특성에 따라 읽기는 실시간 읽기와 지연 가능 읽기로 구분되며 각각 쓰기 라이브러리와 읽기 라이브러리에 해당합니다.
읽기와 쓰기의 분리는 읽기가 불가능할 때 자동으로 쓰기 쪽으로 전환하는 것을 고려해야 합니다
스키마 분리
데이터베이스 간 JOIN은 금지됩니다
데이터 수명주기
데이터 사용 빈도에 따라 대용량 테이블은 일반 서브 데이터베이스에 보관
본관/자료관 물리적 분리
로그형 테이블은 파티셔닝 또는 하위 테이블로 구성되어야 합니다
큰 테이블의 경우 파티셔닝이 필요합니다. 파티셔닝 작업은 테이블과 인덱스를 여러 파티션으로 나누는 작업입니다. 파티션 전환을 통해 기존 파티션과 새 파티션을 빠르게 교체하고 데이터 정리 속도를 높이며 IO 리소스 소비를 크게 줄일 수 있습니다
자주 작성되는 테이블에는 파티셔닝이나 하위 테이블이 필요합니다
자체 성장 및 래치 잠금
래치는 SQL Server에 의해 내부적으로 적용되고 제어됩니다. 이는 메모리 내 데이터 구조의 일관성을 보장하는 데 사용됩니다.
위 내용은 일반적으로 사용되는 SQL Server 사양 모음의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!