[관련 학습 권장 사항: mysql 튜토리얼]
최근에는 데이터베이스 관련 작업이 많이 포함되었으며 회사의 기존 사양이 너무 포괄적이지 않아 직접 사용할 수 있도록 일부 표준 사용법을 정리했습니다. 인터넷에 떠도는 다양한 전문가들의 관련 스펙을 토대로 정정 부탁드립니다.
데이터베이스 환경
dev: 개발 환경
개발은 읽고 쓸 수 있으며 테이블 구조를 수정할 수 있습니다. 개발자는 테이블 구조와 그 안의 데이터를 마음대로 수정할 수 있지만 다른 개발 동료에게 영향을 주지 않도록 해야 합니다.
test: 테스트 환경
은 개발자가 읽고 쓸 수 있으며, 개발자는 도구를 통해 테이블 구조를 수정할 수 있습니다.
온라인 환경
개발자는 온라인 환경에서 데이터베이스 작업을 직접 수행할 수 없습니다. 작업이 필요한 경우 DBA를 찾아 작업을 수행하고 해당 기록을 작성하는 것이 금지됩니다.
핵심 문제는 각 환경에서 MySQL 서버에 해당하는 사용자 권한이 명확하게 구분되고 식별 가능해야 하며 비즈니스 시나리오 등을 구체적으로 구분할 수 있어야 한다는 것입니다.
네이밍 규칙
기본 네이밍 규칙
라이브러리, 테이블, 필드가 모두 소문자인 이유는 무엇입니까?
MySQL에서 데이터베이스와 테이블은 해당 디렉터리 아래의 디렉터리와 파일에 해당합니다. 따라서 운영 체제의 민감도에 따라 데이터베이스 및 테이블 이름 지정의 대소문자 구분이 결정됩니다.
테이블 명명
동일 모듈 내 테이블은 최대한 동일한 접두사를 사용해야 하며, 테이블 이름은 최대한 의미를 표현해야 합니다. 모든 로그 테이블은 log_
필드 이름
인덱스 이름 지정
제약 조건 이름 지정
테이블 디자인 사양
테이블 엔진은 실제 애플리케이션 시나리오에 따라 다르며, 로그 및 보고서 테이블, 트랜잭션 관련 테이블에는 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이 포함되어야 합니다. 데이터 삭제 여부를 나타내는 데 사용됩니다. 원칙적으로 데이터베이스 데이터의 물리적 제거는 허용되지 않습니다.
날짜/시간의 차이점은 무엇인가요?
유사점:
TIMESTAMP 열의 표시 형식은 DATETIME 열과 동일합니다. 표시 너비는 19자로 고정되며 형식은 YYYY-MM-DD HH:MM:SS입니다.
차이:
TIMESTAMP
방법 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를 사용하여 저장해야 합니다.
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));기본 키 지침
테이블에는 기본 키가 있어야 합니다.
UPDATE, DELETE 문 WHERE 조건 열
다음을 선택하세요. 단일 숫자, 사용자 ID 등과 같이 필터링 가능성이 더 나은 필드를 앞에 배치합니다. 유형, 상태 및 기타 필터링 속성은 일반적으로 앞에 배치하지 않는 것이 좋습니다.
인덱스에 대한 제한 in MYSQL
MYISAM 스토리지 엔진의 인덱스 길이의 합은 1000바이트를 초과할 수 없습니다
BLOB 및 TEXT 유형 열에 대해서는 접두사 인덱스만 생성할 수 있습니다.
MYSQL은 현재 함수 인덱스를 지원하지 않습니다
(!=와 같지 않음)을 사용하는 경우 또는 <>), MYSQL은 인덱스를 사용할 수 없습니다.
함수 연산(abs(열) 등)을 사용하여 필드를 필터링한 후에는 MYSQL에서 인덱스를 사용할 수 없습니다.
MYSQL은 조인 문의 조인 조건 필드 유형이 일치하지 않는 경우 인덱스를 사용할 수 없습니다.
LIKE 연산을 사용할 때 조건이 와일드카드(예: '%abc…')로 시작하면 MYSQL은 인덱스를 사용할 수 없습니다.
동등하지 않은 쿼리를 사용하는 경우 MYSQL은 해시 인덱스를 사용할 수 없습니다.
문 디자인 사양
미리 컴파일된 문을 사용하세요
피하세요 암시적 변환
은 색인 오류를 발생시킵니다.
접두사 색인을 최대한 활용하세요.
not in/like
저장 프로시저 및 트리거 서버, UDF, 이벤트 등을 사용하지 마세요.
큰 테이블의 JOIN 사용을 피하세요
MySQL의 가장 뛰어난 기능은 단일 테이블의 기본 키/보조 인덱스 쿼리입니다.
JOIN은 더 많은 메모리를 소비하고 임시 테이블을 생성합니다.
데이터베이스에서 수학 연산을 방지합니다
데이터베이스와의 상호 작용 횟수를 줄입니다.
페이징을 올바르게 사용하세요
페이징으로 표시되는 페이지 수를 제한하세요. 클릭만 가능합니다. 지연된 연관을 사용하여 이전 페이지와 다음 페이지에서
페이징을 올바르게 사용하는 방법은 무엇입니까?
다음과 같은 페이징 문이 있다고 가정하자: 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)
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
分表规范
单表一到两年内数据量超过500w或数据容量超过10G考虑分表，需提前考虑历史数据迁移或应用自行删除历史数据，采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略
行为规范
其他规范
日志类数据不建议存储在MySQL上，优先考虑Hbase或OceanBase，如需要存储请找DBA评估使用压缩表存储。
相关图文教程：mysql数据库图文教程
위 내용은 역사상 가장 포괄적인 MySQL 사용 사양 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
