역사상 가장 포괄적인 MySQL 사용 사양 공유

[관련 학습 권장 사항: mysql 튜토리얼]

최근에는 데이터베이스 관련 작업이 많이 포함되었으며 회사의 기존 사양이 너무 포괄적이지 않아 직접 사용할 수 있도록 일부 표준 사용법을 정리했습니다. 인터넷에 떠도는 다양한 전문가들의 관련 스펙을 토대로 정정 부탁드립니다.

데이터베이스 환경

dev: 개발 환경

개발은 읽고 쓸 수 있으며 테이블 구조를 수정할 수 있습니다. 개발자는 테이블 구조와 그 안의 데이터를 마음대로 수정할 수 있지만 다른 개발 동료에게 영향을 주지 않도록 해야 합니다.

test: 테스트 환경

은 개발자가 읽고 쓸 수 있으며, 개발자는 도구를 통해 테이블 ​​구조를 수정할 수 있습니다.

온라인 환경

개발자는 온라인 환경에서 데이터베이스 작업을 직접 수행할 수 없습니다. 작업이 필요한 경우 DBA를 찾아 작업을 수행하고 해당 기록을 작성하는 것이 금지됩니다.

핵심 문제는 각 환경에서 MySQL 서버에 해당하는 사용자 권한이 명확하게 구분되고 식별 가능해야 하며 비즈니스 시나리오 등을 구체적으로 구분할 수 있어야 한다는 것입니다.

네이밍 규칙

기본 네이밍 규칙

• 의미 있는 영어 단어를 사용하고 중간에 밑줄로 구분하세요. (병음 사용 금지)
• 영문, 숫자, 밑줄만 사용하고 영문으로 시작하세요.
• 라이브러리, 테이블, 필드에는 모두 소문자를 사용하고 카멜 케이스 이름은 사용하지 마세요.
• desc와 같은 ORACLE 및 MySQL의 예약어와 index와 같은 키워드를 사용하지 마세요.
• 이름은 32자를 초과할 수 없습니다. 이름의 의미가 명확해야 합니다. 동사 대신 명사를 사용하는 것이 좋습니다.
• 모든 데이터베이스 및 데이터 테이블은 접두사를 사용해야 합니다.
• 임시 데이터베이스 및 테이블 이름 앞에는 tmp 및 접미사 날짜
• 백업 라이브러리와 테이블에는 접두사 bak, 접미사 날짜가 와야 합니다

라이브러리, 테이블, 필드가 모두 소문자인 이유는 무엇입니까?

MySQL에서 데이터베이스와 테이블은 해당 디렉터리 아래의 디렉터리와 파일에 해당합니다. 따라서 운영 체제의 민감도에 따라 데이터베이스 및 테이블 이름 지정의 대소문자 구분이 결정됩니다.

• Windows에서는 대소문자를 구분하지 않습니다.
• Linux의 대소문자 규칙
• 데이터베이스 이름과 테이블 이름은 엄격하게 대소문자를 구분합니다.
• 테이블 별칭은 대소문자를 엄격하게 구분합니다.
• 열 이름과 열 별칭은 모든 경우에 무시됩니다.
• 변수 이름도 엄격하게 작성됩니다.
• 카멜 케이스 이름이 설정된 경우 문제를 해결하는 방법은 무엇입니까? MySQL 구성 파일 my.ini에 lower_case_table_names = 1을 추가해야 합니다.
  

테이블 명명

동일 모듈 내 테이블은 최대한 동일한 접두사를 사용해야 하며, 테이블 이름은 최대한 의미를 표현해야 합니다. 모든 로그 테이블은 log_

필드 이름

• 실제 의미를 표현하는 영어 단어나 약어로 시작됩니다. 부울 의미가 있는 필드에는 is_라는 접두사가 붙고 그 뒤에 동사의 과거 분사가 옵니다.
• 테이블 간 의미가 같은 필드는 이름이 동일해야 합니다. 테이블 간에 동일한 의미를 갖는 필드는 테이블 이름_필드 이름에서 모듈 접두사를 뺀 이름으로 지정됩니다.
• 외래 키 필드는 테이블 이름_필드 이름을 사용하여 연관성을 나타냅니다.
• 테이블의 기본 키는 자동 증가형인 id로 일반적으로 합의되며, 다른 테이블의 외래 키는 xxx_id 형식으로 표현됩니다.

인덱스 이름 지정

• 고유하지 않은 인덱스는 "idx_필드 이름_필드 이름[_필드 이름]"에 따라 이름이 지정되어야 합니다.
• 고유 인덱스는 "uniq_필드 이름_필드 이름[_필드 이름]"에 따라 이름이 지정되어야 합니다
  

제약 조건 이름 지정

• 기본 키 제약 조건: pk_table 이름.
• 고유 제약 조건: uk_테이블 이름_필드 이름. (애플리케이션에는 고유성 검사 로직이 동시에 있어야 합니다.)
  

테이블 디자인 사양

테이블 엔진은 실제 애플리케이션 시나리오에 따라 다르며, 로그 및 보고서 테이블, 트랜잭션 관련 테이블에는 myisam을 권장합니다. , 감사 및 금액은 innodb 엔진을 사용하는 것이 좋습니다. 설명이 없으면 테이블 생성 시 innodb 엔진이 사용됩니다

기본 utf8mb4 문자 집합이 사용되며, 데이터베이스 정렬 규칙은 utf8mb4_general_ci입니다. (데이터베이스 정의에서는 기본값을 사용하므로 더 이상 데이터 테이블을 만들 수 없습니다. 정의되어 있지만 보험 목적으로 모두 작성하는 것이 좋습니다

문자 세트로 utf8을 선택하고 정렬 규칙으로 utf8_general_ci를 선택하는 것이 어떻습니까?

utf8 인코딩을 사용하는 MySQL은 Emoji 표현을 a로 저장할 수 없습니다. 백엔드 프로젝트가 클라이언트의 Emoji 입력을 완전히 지원하도록 하려면 JDBC 연결 문자열이 utf8로 설정된 상황에서는 인코딩을 utf8mb4로 업그레이드하는 것이 가장 좋습니다. 위 구성 후에는 이모티콘 데이터를 정상적으로 삽입할 수 없으므로 코드에서 연결 문자 집합을 utf8mb4로 지정해야 합니다.

모든 테이블과 필드는 해당 테이블과 필드의 실제 의미를 설명하기 위해 주석 열 속성을 사용해야 합니다. 열거형 값이므로 해당 필드에 사용되는 모든 내용을 정의하는 것이 좋습니다.

설명이 없으면 테이블의 첫 번째 ID는 기본 키여야 하며 자동 확장이 금지됩니다. 비트랜잭션 내의 컨텍스트로 데이터를 기본 키 명령문 디자인으로 사용하는 것은 금지되어 있습니다.

설명이 없는 경우 테이블에는 create_time 및 수정 시간 필드가 포함되어야 합니다. 즉, 테이블에는 생성 시간과 수정 시간을 기록하는 필드가 포함되어야 합니다.

설명이 없는 경우 테이블에는 is_del이 포함되어야 합니다. 데이터 삭제 여부를 나타내는 데 사용됩니다. 원칙적으로 데이터베이스 데이터의 물리적 제거는 허용되지 않습니다.

• 필드에 데이터를 저장하려면 저장 공간을 최대한 적게 사용하세요
• int를 사용할 수 있다면 char 또는 varchar를 사용하지 마세요
• tinyint를 사용할 수 있다면 int를 사용하지 마세요
• UNSIGNED를 사용하여 저장하세요 음수가 아닌 값.
• ENUM 및 SET 유형을 사용하는 것은 권장되지 않습니다. 대신 TINYINT를 사용하세요.
• 짧은 데이터 유형을 사용하세요. 예를 들어 값 범위가 0-80이면 TINYINT UNSIGNED를 사용하세요.
• DECIMAL은 정확한 부동 소수점 숫자를 저장하는 데 사용해야 합니다. FLOAT 및 DOUBLE
• Time 필드 대신 특별한 상황을 제외하고는 int를 사용하여 unix_timestamp를 기록합니다.
• YEAR 유형을 사용하여 연도를 저장합니다.
• 저장 날짜는 DATE 유형을 사용합니다.
• TIMESTAMP는 4바이트, DATETIME은 8바이트를 사용하므로 시간(초 단위)을 저장하려면 TIMESTAMP 유형을 사용하는 것이 좋습니다.
• IPV4를 저장하려면 INT UNSIGNED를 사용하는 것이 좋습니다.
• TEXT, BLOB 유형은 최대한 사용하지 마세요.
• 데이터베이스에 사진, 파일 등을 저장하기 위해 VARBINARY, BLOB 유형을 사용하는 것은 금지되어 있습니다. 다른 저장 방법(TFS/SFS)을 사용하는 것이 좋습니다. MySQL은 포인터 정보만 저장합니다.
• 단일 레코드의 크기는 8k를 초과할 수 없습니다. (열 길이(중국어)_3(UTF8) + 열 길이(영어)_1)

날짜/시간의 차이점은 무엇인가요?

유사점:

TIMESTAMP 열의 표시 형식은 DATETIME 열과 동일합니다. 표시 너비는 19자로 고정되며 형식은 YYYY-MM-DD HH:MM:SS입니다.

차이:

TIMESTAMP

• 4바이트 저장, 시간 범위: 1970-01-01 08:00:01 ~ 2038-01-19 11:14:07 값은 시간대를 포함하는 UTC 형식으로 저장됩니다. 변환: 저장할 때 현재 시간대로 변환하고, 검색할 때 다시 현재 시간대로 변환합니다.
• Datetime은 8바이트로 저장되며, 시간 범위: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• 시간대에 관계없이 실제 형식으로 저장
  

방법 TIMESTAMP 자동 할당 속성을 사용하려면?

현재 시간을 ts의 기본값(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP)으로 설정합니다. 행이 업데이트되면 ts 값을 업데이트합니다: ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP.

1과 2를 결합할 수 있습니다: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

INT UNSIGNED를 사용하여 IP를 저장하는 방법은 무엇입니까?

ipv4 주소를 저장하려면 char(15) 대신 INT UNSIGNED를 사용하고 변환에는 MySQL 함수 inet_ntoa 및 inet_aton을 사용하세요. IPv6 주소에는 현재 변환 기능이 없으며 DECIMAL 또는 두 개의 bigINT를 사용하여 저장해야 합니다.

• 설명이 없으면 모든 필드가 NOT NULL로 설정되고 기본값이 설정됩니다.
• 데이터베이스에 일반 텍스트 비밀번호를 저장하는 것은 금지됩니다.
• 설명이 없으면 모든 부울 필드는 다음과 같습니다. is_hot 및 is_deleted와 같이 기본값을 설정하고 0으로 설정해야 합니다.
• 설명이 없으면 정렬 필드 order_id는 프로그램에서 기본적으로 내림차순으로 정렬됩니다.
• 정수 정의에 길이가 추가되지 않습니다. 예를 들어 INT[4]
  

INT [M] 대신 INT를 사용하는 경우 M 값은 무엇을 의미하나요?

숫자형 괄호 뒤의 숫자는 너비만 나타내며 저장 범위와는 관련이 없습니다. 많은 사람들이 INT(4)와 INT(10)의 값 범위가 각각 (-9999 ~ 9999) 및 (-9999999999 ~ 9999999999)라고 생각합니다. 이러한 이해는 잘못된 것입니다. 실제로 정수의 M 값을 ZEROFILL 속성과 함께 사용하면 열 값의 너비가 동일해질 수 있습니다. INT[M]의 M 값에 관계없이 해당 값 범위는 여전히 (부호 있는 경우 -2147483648 ~ 2147483647), (부호 없는 경우 0 ~ 4294967295)입니다.

표시 너비는 열 내에 저장할 수 있는 값의 범위를 제한하지 않으며, 열의 지정된 너비를 초과하는 값의 표시를 제한하지도 않습니다. 선택적 확장 속성 ZEROFILL과 함께 사용하면 기본 보충 공백이 0으로 대체됩니다. 예: INT(5) ZEROFILL로 선언된 열의 경우 값 4는 00004로 검색됩니다. 표시 너비를 초과하는 정수 열에 값을 저장하면 MySQL은 복잡한 조인을 위한 임시 테이블을 생성할 때 문제가 발생합니다. 이러한 경우 MySQL은 ZEROFILL을 지정하면 데이터가 원래 열 너비에 맞는다고 믿기 때문입니다. 숫자 열, MySQL UNSIGNED 속성을 열에 자동으로 추가합니다.

VARBINARY를 사용하여 대소문자를 구분하는 가변 길이 문자열을 저장하세요.

CHAR를 언제 사용하고 언제 VARCHAR를 사용해야 할까요?

CHAR 및 VARCHAR 유형은 유사하지만 저장 및 검색 방식이 다릅니다. 또한 최대 길이와 후행 공백이 유지되는지 여부도 다릅니다. CHAR 및 VARCHAR 유형에 대해 선언된 길이는 저장하려는 최대 문자 수를 나타냅니다. 예를 들어 CHAR(30)은 30자를 차지할 수 있습니다.

CHAR 컬럼의 길이는 테이블 생성 시 선언한 길이로 고정됩니다. 길이는 0에서 255 사이의 값일 수 있습니다. CHAR 값을 저장할 때 지정된 길이만큼 공백으로 오른쪽을 채웁니다. CHAR 값이 검색되면 후행 공백이 제거됩니다. 저장 또는 검색 중에는 대소문자 변환이 수행되지 않습니다.

VARCHAR 열의 값은 가변 길이 문자열입니다. 길이는 0에서 65,535 사이의 값으로 지정할 수 있습니다. (VARCHAR의 최대 유효 길이는 최대 행 크기와 사용된 문자 세트에 따라 결정됩니다. 전체 최대 길이는 65,532바이트입니다.) CHAR과 비교하여 VARCHAR 값은 필요한 문자 수에 길이를 기록하기 위해 1바이트만 저장합니다(선언된 열 길이가 255를 초과하는 경우 2바이트가 사용됩니다). VARCHAR 값은 패딩 없이 저장됩니다. 표준 SQL에 따라 값을 저장하고 검색할 때 후행 공백이 유지됩니다.

char는 사용자 비밀번호의 MD5 해시 값을 저장하는 데 적합하며 길이는 항상 동일합니다. 고정 길이 행은 조각화가 덜 발생하기 때문에 자주 변경되는 값의 경우 char은 varchar보다 낫고 매우 짧은 열의 경우 char도 varchar보다 더 효율적입니다. char(1) 문자열은 단일 바이트 문자 집합에 대해 1바이트만 차지하지만 varchar(1) 문자열은 길이 정보를 저장하는 데 1바이트가 사용되므로 2바이트를 차지합니다.

인덱스 디자인 사양

MySQL의 쿼리 속도는 좋은 인덱스 디자인에 달려 있으므로 인덱스는 고성능을 위해 매우 중요합니다. 합리적인 인덱스는 쿼리 속도를 높이는 반면(UPDATE 및 DELETE 속도 포함. MySQL은 행이 포함된 페이지를 메모리에 로드한 다음 UPDATE 또는 DELETE 작업을 수행함), 비합리적인 인덱스는 속도를 저하시킵니다. MySQL 색인 검색은 Xinhua Dictionary의 병음 및 부수 검색과 유사합니다. 병음 및 부수 색인이 존재하지 않는 경우 페이지별로 검색해야 합니다. MySQL 쿼리가 인덱스를 사용할 수 없는 경우 MySQL은 전체 테이블 스캔을 수행하므로 많은 IO가 소모됩니다. 인덱스의 목적: 중복 제거, 위치 지정 속도 향상, 정렬 방지 및 인덱스 포함.

커버링 인덱스란 무엇인가요? InnoDB 스토리지 엔진에서 보조 인덱스(비기본 키 인덱스)는 행 주소를 직접 저장하지 않고 기본 키 값을 저장합니다. 사용자가 보조 인덱스에 포함되지 않은 데이터 열을 쿼리해야 하는 경우 보조 인덱스를 통해 기본 키 값을 먼저 찾은 다음 기본 키를 통해 다른 데이터 열을 쿼리해야 하므로 두 번 쿼리됩니다. 커버링 인덱스의 개념은 쿼리가 인덱스에서 완료될 수 있으며, 기본 키 쿼리는 자연스러운 커버링 인덱스의 효율성이 상대적으로 높다는 것입니다. 인덱스를 합리적으로 생성하고 쿼리 문을 합리적으로 사용하면 포함 인덱스를 사용할 때 성능이 향상될 수 있습니다. 예를 들어, SELECT email, uid FROM user_email WHERE uid=xx, uid가 기본 키가 아닌 경우 성능 향상을 위해 적절할 때 인덱스를 index(uid, email)로 추가할 수 있습니다.

인덱스 기본 사양

인덱스 수를 제어합니다. 단일 테이블의 인덱스 수는 5개를 초과할 수 없으며, 단일 인덱스의 필드 수는 5개를 초과할 수 없습니다.

데이터 밀도 및 분포를 종합적으로 평가
• 쿼리 및 업데이트 비율을 고려하세요
• 
  
테이블에 인덱스가 너무 많으면 안 되나요?

InnoDB의 보조 인덱스는 b+tree를 저장용으로 사용하므로 UPDATE, DELETE, INSERT 시 b+tree를 조정해야 합니다. 인덱스가 너무 많으면 업데이트 속도가 느려집니다.

문자열에는 접두사 색인을 사용하세요. 접두사 색인의 길이는 8자를 초과하지 않아야 합니다. 필요한 경우 의사 열을 추가하고 색인을 생성할 수 있습니다.

BLOB/텍스트 및 기타 필드를 색인화하지 마세요. 큰 필드를 색인화하지 마세요. 이렇게 하면 색인이 너무 많은 저장 공간을 차지하게 됩니다.

접두사 색인이란 무엇인가요?

직접 말하면 접두사 인덱스는 텍스트의 처음 몇 글자(구체적으로 인덱스 생성 시 지정하는 문자 수)를 인덱스로 만들어 생성되는 인덱스가 작아지므로 쿼리 속도가 빨라집니다. 접두사 색인은 색인 파일의 크기를 효과적으로 줄이고 색인 속도를 향상시킬 수 있습니다. 그러나 접두사 인덱스에는 단점도 있습니다. MySQL은 ORDER BY 또는 GROUP BY에서 접두사 인덱스를 사용할 수 없으며 포함 인덱스로 사용할 수도 없습니다.

접두사 인덱스 설정 구문: ALTER TABLE table_name ADD KEY(column_name(prefix_length));

기본 키 지침

테이블에는 기본 키가 있어야 합니다.

자주 업데이트되는 열을 사용하지 마세요
• 시도하지 마세요. 문자열 열을 선택하려면
UUID MD5 HASH를 사용하지 마세요
• 기본적으로 비어 있지 않은 고유 키를 사용하세요
• 자동 증가 또는 발급자를 선택하는 것이 좋습니다
• 
  중요한 SQL은 색인화해야 하며, 핵심 SQL은 우선적으로 처리합니다. indexes

UPDATE, DELETE 문 WHERE 조건 열

ORDER BY, GROUP BY, DISTINCT 필드
• 다중 테이블 JOIN 필드
• 
  
가장 차별화된 필드를 앞쪽에 배치

다음을 선택하세요. 단일 숫자, 사용자 ID 등과 같이 필터링 가능성이 더 나은 필드를 앞에 배치합니다. 유형, 상태 및 기타 필터링 속성은 일반적으로 앞에 배치하지 않는 것이 좋습니다.

색인은 왼쪽 접두사 원칙을 기반으로 합니다. 결합 인덱스(a, b, c)가 설정된 경우 쿼리 조건에는 (a) 또는 (a,b) 또는 (a,b,c)만 포함되며, 해당 인덱스는 (a,c)인 경우에만 사용할 수 있습니다. 조건으로 사용하면 a 열 인덱스만 사용할 수 있으므로 이때 a의 반환 열이 너무 크지 않도록 해야 합니다. 그렇지 않으면 문 디자인이 됩니다. 비합리적, (b,c)는 색인화될 수 없습니다
• 합리적으로 공동 색인을 생성하십시오(중복을 피하기 위해), (a,b,c)는 (a), (a,b), (a,b,c)와 동일합니다.
• 
  
색인 금기

• "gender"와 같이 카디널리티가 낮은 열에는 인덱스를 생성하지 마십시오.
• 인덱스 열에 수학 연산 및 함수 연산을 수행하지 마십시오.
• 일반적으로 사용되는 작은 테이블을 인덱스하지 마십시오.
• 외래 키를 사용하지 마십시오.
• 외래 키 참조 무결성을 보호하는 데 사용되며 비즈니스 목적에서 실현될 수 있습니다.
• 상위 테이블과 하위 테이블의 작업은 서로 영향을 미치므로 가용성이 떨어집니다.
• INNODB 자체에는 온라인 DDL에 제한이 있습니다.
  

인덱스에 대한 제한 in MYSQL

MYISAM 스토리지 엔진의 인덱스 길이의 합은 1000바이트를 초과할 수 없습니다
BLOB 및 TEXT 유형 열에 대해서는 접두사 인덱스만 생성할 수 있습니다.
MYSQL은 현재 함수 인덱스를 지원하지 않습니다
(!=와 같지 않음)을 사용하는 경우 또는 a8093152e673feb7aba1828c43532094), MYSQL은 인덱스를 사용할 수 없습니다.
함수 연산(abs(열) 등)을 사용하여 필드를 필터링한 후에는 MYSQL에서 인덱스를 사용할 수 없습니다.
MYSQL은 조인 문의 조인 조건 필드 유형이 일치하지 않는 경우 인덱스를 사용할 수 없습니다.
LIKE 연산을 사용할 때 조건이 와일드카드(예: '%abc…')로 시작하면 MYSQL은 인덱스를 사용할 수 없습니다.
동등하지 않은 쿼리를 사용하는 경우 MYSQL은 해시 인덱스를 사용할 수 없습니다.

문 디자인 사양

미리 컴파일된 문을 사용하세요

• SQL 문을 전달하는 것보다 더 효율적인 매개변수만 전달하세요
• 한 번 구문 분석하면 여러 번 사용 가능
• SQL 주입 가능성 줄이기
  

피하세요 암시적 변환

은 색인 오류를 발생시킵니다.

접두사 색인을 최대한 활용하세요.

• 은 가장 왼쪽 접두사여야 합니다.
• 두 범위 조건을 동시에 사용할 수 없습니다.
• 행간 %를 사용하지 않는 쿼리 , like "%ab"
  

not in/like

• 인덱스를 사용할 수 없어 전체 테이블 스캔이 발생함
• 전체 테이블 스캔으로 인해 버퍼 감소 풀 활용
  

저장 프로시저 및 트리거 서버, UDF, 이벤트 등을 사용하지 마세요.

• 데이터베이스가 가장 잘하는 일을 하도록 하세요
• 비즈니스 결합을 줄이고 저장 및 샤딩을 위한 공간을 남겨두세요
• 버그 방지
  

큰 테이블의 JOIN 사용을 피하세요

MySQL의 가장 뛰어난 기능은 단일 테이블의 기본 키/보조 인덱스 쿼리입니다.
JOIN은 더 많은 메모리를 소비하고 임시 테이블을 생성합니다.

데이터베이스에서 수학 연산을 방지합니다

• MySQL은 수학적 연산과 논리적 판단에 능숙하지 않습니다.
• Unable 인덱스를 사용하여
  

데이터베이스와의 상호 작용 횟수를 줄입니다.

• INSERT … ON DUPLICATE KEY UPDATE
• REPLACE INTO, INSERT IGNORE, INSERT INTO VALUES(),(),()
• UPDATE … WHERE ID IN( 10,20,50,…)

페이징을 올바르게 사용하세요

페이징으로 표시되는 페이지 수를 제한하세요. 클릭만 가능합니다. 지연된 연관을 사용하여 이전 페이지와 다음 페이지에서

페이징을 올바르게 사용하는 방법은 무엇입니까?

다음과 같은 페이징 문이 있다고 가정하자: SELECT * FROM table ORDER BY id LIMIT 10000, 10 MySQL에서 LIMIT OFFSET을 처리하는 방식은 OFFSET+LIMIT의 데이터를 모두 꺼낸 후 OFFSET과 맨 아래에 LIMIT를 반환합니다. 따라서 OFFSET 값이 크면 MySQL의 쿼리 성능이 매우 저하됩니다. id > n을 사용하면 해결 가능:

id > n을 사용하는 방법에는 제한이 있습니다. 불연속적인 id 문제는 페이지를 넘길 때 마지막 id를 동시에 전달하면 해결됩니다.

http://example.com/page.php?last=100 
select * from table where id<100 order by id desc limit 10 
//上一页 
 http://example.com/page.php?first=110 
select * from table where id>110 order by id desc limit 10

이 방법의 가장 큰 단점은 탐색 중 삽입/삭제 작업이 있는 경우 페이지 넘김이 업데이트되지 않고 여전히 새로운 개수(*)를 기준으로 총 페이지 수가 계산될 수 있다는 것입니다. 결국 뭔가가 생성될 수 있습니다. 일부 기록에 액세스할 수 없습니다. 이 문제를 해결하려면 현재 페이지 번호와 마지막 페이지 전환 이후 총 레코드 수에 영향을 미치는 삽입/삭제 작업이 있는지 여부를 계속해서 도입하고 캐시하면 됩니다

select * from table where id >= (select id from table order by id limit #offset#, 1)

• 대용량 SQL을 거부하고 small SQL
• QUERY CACHE를 최대한 활용
• 멀티 코어 CPU를 최대한 활용
• 또는 대신에 in을 사용하세요. in의 값은 1000을 넘지 않아야 합니다
• rand()로 order를 사용하는 것은 금지되어 있습니다
• EXPLAIN 진단을 사용하여 임시 테이블 생성을 방지하세요
  

EXPLAIN 문(MySQL 클라이언트 최종 실행에서)은 MySQL이 SELECT 문을 실행하는 방법에 대한 정보를 얻을 수 있습니다. SELECT 문에 EXPLAIN을 실행하면 MySQL이 SELECT 문을 실행할 때 인덱스, 전체 테이블 스캔, 임시 테이블, 정렬 및 기타 정보를 사용하는지 여부를 알 수 있습니다. MySQL이 전체 테이블 스캔을 수행하거나 임시 테이블을 사용하거나 정렬 등을 수행하지 않도록 하십시오. 자세한 내용은 공식 문서를 참조하세요.

Union 대신 Union All을 사용하세요

Union All과 Union의 차이점은 무엇인가요?

Union과 Union 모든 키워드는 모두 두 개의 결과 세트를 하나로 병합하지만 사용법과 효율성 측면에서 다릅니다.

Union은 테이블 연결 후 중복 레코드를 필터링하므로 테이블 연결 후 생성된 결과 집합을 정렬하고 중복 레코드를 삭제한 다음 결과를 반환합니다. 예:

select * from test_union1 
union select * from test_union2

이 SQL은 실행 시 먼저 두 테이블의 결과를 가져온 다음 정렬 공간을 사용하여 중복 레코드를 정렬 및 삭제하고 마지막으로 결과 집합을 반환합니다. 테이블 데이터가 큰 경우 오류가 발생할 수 있습니다. 정렬할 디스크입니다.

그리고 Union all은 단순히 두 결과를 결합하여 반환합니다. 이러한 방식으로 반환된 두 결과 집합에 중복 데이터가 있는 경우 반환된 결과 집합에는 중복 데이터가 포함됩니다.

从效率上说，union all要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all，如下：

select * from test_union1 union all select * from test_union2

•  程序应有捕获SQL异常的处理机制
• 禁止单条SQL语句同时更新多个表
• 不使用select * ，SELECT语句只获取需要的字段
• 消耗CPU和IO、消耗网络带宽
• 无法使用覆盖索引
• 减少表结构变更带来的影响
• 因为大，select/join 可能生成临时表
• UPDATE、DELETE语句不使用LIMIT
• INSERT语句必须显式的指明字段名称，不使用INSERT INTO table()
• INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不超过500
• 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 备注：仅针对Myisam
• 数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新
• 禁止使用跨库查询
• 禁止使用子查询，建议将子查询转换成关联查询
• 针对varchar类型字段的程序处理，请验证用户输入，不要超出其预设的长度；
  

分表规范

单表一到两年内数据量超过500w或数据容量超过10G考虑分表，需提前考虑历史数据迁移或应用自行删除历史数据，采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略

• 用HASH进行散表，表名后缀使用十进制数，下标从0开始
• 按日期时间分表需符合YYYY[MM][dd][HH]格式
• 采用合适的分库分表策略。例如千库十表、十库百表等
• 禁止使用分区表，分区表对分区键有严格要，分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。
• 拆分大字段和访问频率低的字段，分离冷热数据
  

行为规范

• 批量导入、导出数据必须提前通知DBA协助观察
• 禁止在线上从库执行后台管理和统计类查询
• 禁止有super权限的应用程序账号存在
• 产品出现非数据库导致的故障时及时通知DBA协助排查
• 推广活动或上线新功能必须提前通知DBA进行流量评估
• 数据库数据丢失，及时联系DBA进行恢复
• 对单表的多次alter操作必须合并为一次操作
• 不在MySQL数据库中存放业务逻辑
• 重大项目的数据库方案选型和设计必须提前通知DBA参与
• 对特别重要的库表，提前与DBA沟通确定维护和备份优先级
• 不在业务高峰期批量更新、查询数据库其他规范
• 提交线上建表改表需求，必须详细注明所有相关SQL语句
  

其他规范

日志类数据不建议存储在MySQL上，优先考虑Hbase或OceanBase，如需要存储请找DBA评估使用压缩表存储。

相关图文教程：mysql数据库图文教程

위 내용은 역사상 가장 포괄적인 MySQL 사용 사양 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!