관련 무료 학습 권장사항: mysql tutorial(동영상)
시간이 지나고 사용자 수가 증가함에 따라 플랫폼이나 시스템이 데이터베이스 작업은 느려지는 경향이 있으며, 데이터베이스는 Java 애플리케이션 개발에서 특히 중요합니다. 대부분의 경우 데이터베이스 성능이 초기 단계에 묻혀 있는 부분이 더 많으면 데이터베이스가 가장 중요한 역할을 하게 됩니다. 따라서 병목 현상이 발생하는 경우 전체 시스템의 핵심이 되기 때문에 개발 시 MySQL의 보다 표준화된 사용이 필수적입니다.
1. 데이터베이스의 모든 테이블 접두사는 프로젝트 이름의 약어를 사용합니다.
2. 데이터베이스의 모든 개체 이름은 밑줄로 구분됩니다. . 데이터베이스 모든 개체 이름은 MySQL 예약어 및 키워드를 사용할 수 없습니다. 키워드가 포함된 SQL 쿼리는 키워드를 작은따옴표로 묶어야 합니다.
4. 데이터베이스의 모든 개체 이름은 32자를 초과할 수 없으며 이름 지정은 규칙을 따라야 합니다.
5. 임시 데이터베이스 테이블은 pro_tmp_로 시작하고 날짜
20190917로 시작해야 하며, 백업 테이블은 pro_bac로 시작해야 하며 pro는 프로젝트 이름의 약어입니다. ) 6 , 동일한 데이터를 저장하는 데이터베이스의 모든 컬럼 이름과 컬럼 유형은 일관되어야 합니다.
2. MySQL 데이터베이스의 기본 설계 사양
InnoDB 및 MyISAM과 같은 올바른 엔진을 선택하면 MySQL을 사용할 때 많은 사람들이 가장 일반적으로 사용하는 두 가지 테이블 유형이 있으며 두 테이블 유형 모두 특정 애플리케이션에 따라 장점과 단점이 있습니다.
기본적인 차이점은 MyISAM 유형은 트랜잭션 처리와 같은 고급 처리를 지원하지 않는 반면, MyISAM 유형 테이블은 성능을 강조하며 실행 시간은 InnoDB 유형보다 빠르지만 트랜잭션을 제공하지 않습니다. InnoDB는 트랜잭션 지원 및 외래 키와 같은 고급 데이터베이스 기능을 지원합니다.
따라서 트랜잭션 처리 지원, 외래 키 지원, 충돌 복구 기능 지원 및 동시성 제어는 테이블 구축 시 선호되는 스토리지 엔진입니다.
2. 데이터베이스 및 테이블의 문자 세트는 UTF8을 사용합니다.
이모지 표현 등을 저장해야 하는 필드가 있는 경우, utf8은 범용 코드로 알려져 있으며 트랜스코딩이 필요하지 않고 코드가 깨질 위험이 없으며 공간을 절약하며 utf8mb4는 utf8과 역호환됩니다.
3. 데이터베이스를 설계할 때 모든 테이블과 필드에 주석을 추가해야 합니다.
Comment 절을 사용하여 테이블과 열에 메모를 추가하거나 데이터베이스 연결 도구의 주석 열에 직접 주석을 추가하고 프로젝트 시작부터 데이터 사전.
Comment 절을 사용하여 다음과 같은 주석을 추가합니다.
-- 1、创建表： CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number); -- 2、添加表注释： Comment on table t1 is '个人信息'; -- 3、添加字段注释： comment on column t1.id is 'id'; comment on column t1.nameis '姓名'; comment on column t1.age is '年龄';
데이터베이스 연결 도구를 사용하여 주석을 추가합니다.
4 단일 테이블의 데이터 크기는 500만 이내로 제어해야 합니다.
단일 테이블의 데이터 크기는 500만 개 이내로 제어하는 것이 좋습니다. 500만 개는 MySQL 데이터베이스의 한계는 아니지만, 너무 많은 데이터는 테이블 구조 수정, 데이터 백업 및 복원에 도움이 되지 않습니다. 단일 테이블의 데이터 크기를 제어하기 위해 하위 데이터베이스, 하위 테이블과 같은 방법을 사용하는 것이 적합합니다.
5. MySQL 파티션 테이블을 사용할 때는 주의하세요
파티셔닝은 테이블의 데이터를 월 단위 등 특정 방식으로 더 작고 관리하기 쉬운 여러 부분으로 나누는 것입니다. 논리적으로 하나의 테이블, 분할된 테이블은 물리적으로 여러 파일로 표시되지만 논리적으로는 여전히 동일한 테이블로 표시됩니다. 파티션 간 쿼리 효율성은 낮을 수 있으므로 물리적 파티션 테이블과 빅데이터를 관리하는 다른 방법.
6. 핫 데이터와 콜드 데이터를 분리하고 테이블 너비를 줄여보세요.
MySQL은 각 테이블에 최대 4096개의 열을 저장할 수 있도록 제한하고, 데이터의 각 행 크기는 65535바이트를 초과하지 않습니다. 디스크 IO 스레드의 오버헤드가 발생하면 테이블 너비를 적절하게 제어해야 합니다. 테이블이 넓을수록 테이블을 메모리 버퍼 풀에 로드할 때 차지하는 메모리가 더 커져 더 많은 IO 스레드가 소모되기 때문입니다. 핫 데이터 효율성의 메모리 캐시 적중을 보장하고, 캐시를 보다 효과적으로 사용하고, 쓸모 없는 콜드 데이터를 읽지 않고, 자주 사용되는 열을 동일한 테이블에 배치하고, 불필요한 상관 관계 작업을 피하십시오.
7. 예약된 필드를 만들 때 주의하세요
데이터베이스 테이블을 디자인할 때 어떤 친구들은 현재 필요한 필드를 디자인할 뿐만 아니라 여러 필드를 백업용으로 따로 남겨두기도 합니다. 예를 들어 이름(Name), 성별(Sex), 생년월일(생년월일) 등 다양한 필수 필드를 추가한 사람 테이블(Person)을 디자인했습니다.
예를 들어, 미래에 Person 테이블에 졸업 학교, 직장, 결혼 상태, 사진 등과 같은 정보가 포함될 수 있으므로 Text1, Text2...Text5라는 5개의 varchar2 필드를 추가했습니다. 단계 작업은 예방 조치인 것처럼 보이지만 실제로는 그렇지 않습니다. 예약된 필드가 너무 많으면 공간이 낭비되고, 예약된 필드를 이름으로 알 수 없고, 예약된 필드의 저장된 데이터 유형을 확인할 수 없기 때문입니다. , 필드 유형을 수정하면 테이블 잠금과 같은 문제가 발생할 수도 있습니다.
이 상황에서는 다음 두 가지 해결 방법을 참고할 수 있습니다.
수량이 적고 정보의 성격이 원본 테이블과 밀접한 관련이 있는 경우 원본 테이블에 필드를 직접 추가하고 업데이트할 수 있습니다. ;
숫자가 크거나 원본 테이블 개체의 중요한 속성이 아닌 경우 새 테이블을 추가하고 키 값을 통해 연결할 수 있습니다. 데이터베이스에 사진과 파일을 저장하는 것이 금지됩니다. 데이터베이스 테이블에 대용량 바이너리 데이터 파일을 저장하고 일반적으로 파일이 매우 큰 경우 데이터베이스가 읽기 작업을 수행할 때 많은 수의 무작위 IO 작업이 수행됩니다. 파일은 IO 작업에 많은 시간과 성능을 소모하므로 시간이 지남에 따라 데이터 양이 급격히 증가하므로 사진과 파일은 일반적으로 파일 서버에 저장되고 데이터베이스는 파일 주소 정보를 저장하는 데만 사용됩니다.
1. 스토리지 요구 사항을 충족하는 가장 작은 데이터 유형을 우선시합니다.
주로 인덱스의 성능을 고려하세요. 컬럼의 필드가 클수록 인덱스를 구축하는 데 필요한 공간이 커지기 때문에 한 페이지에 저장할 수 있는 인덱스 노드의 개수는 적어지고, 인덱스 노드의 개수는 줄어들기 때문입니다. 순회 중에 필요한 IO는 더 작아질수록 인덱스 성능은 저하됩니다.
TEXT 및 BLOB 데이터 유형을 사용하지 마세요. MySQL 메모리 임시 테이블은 쿼리의 경우 64K 데이터 유형을 지원하지 않습니다. 이러한 데이터가 포함되어 있으므로 정렬과 같은 작업을 수행할 때 메모리 임시 테이블을 사용할 수 없으며 작업을 수행하려면 디스크 임시 테이블을 사용해야 합니다.
TEXT 및 BLOB 유형은 접두사 인덱스만 사용할 수 있습니다(인덱스가 매우 긴 문자 시퀀스인 경우, 이 인덱스는 메모리를 많이 차지하고 매우 느립니다. 이때 접두사 인덱스는 인덱스의 처음 몇 글자를 인덱스로 사용하지만 중복을 줄이기 위해 사용됩니다. 인덱스의 비율에 따라 접두사 인덱스의 반복률도 판단해야 합니다.) MySQL은 인덱스 필드 길이가 제한되어 있으므로 TEXT 유형은 접두사 인덱스만 사용할 수 있고 TEXT 열에는 기본값이 있을 수 없습니다. 사용해야 하는 경우에는 BLOB 또는 TEXT 컬럼을 사용하는 것이 좋습니다. 별도의 확장 테이블로 분리하고, 쿼리 시
를 사용하지 말고 필요한 컬럼만 빼내시면 됩니다. 3. ENUM 열거형 사용을 피하세요ENUM 값을 수정하려면 ALTER 문을 사용해야 합니다. ENUM 유형의 ORDER BY 연산은 비효율적입니다.select *숫자 값을 열거형으로 사용하는 것은 금지되어 있습니다. ENUM의 값입니다.
4. 모든 열의 기본값은 NOT NULL로 정의됩니다.
데이터베이스의 모든 NULL 열은 저장하는 데 추가 공간이 필요하므로 더 많은 공간을 차지하게 됩니다.
수행 시 데이터베이스에서 NULL 값을 처리해야 합니다. 비교 및 계산 특별 대우.
5. TIMESTAMP(4바이트) 또는 DATETIME(8바이트) 유형을 사용하여 시간을 저장합니다.
TIMESTAMP 저장되는 시간 범위는 1970-01-01 00:00:01 ~ 2038-01-19-03:14 입니다. 07;
TIMESTAMP는 INT와 동일한 4바이트를 차지하지만 TIMESTAMP 값 범위를 초과하는 경우 DATETIME 유형 저장을 사용합니다.
시간을 저장하기 위해 문자열 유형을 사용할 때의 단점: 사용할 수 없습니다. 날짜 함수는 비교 계산을 수행하며 문자열 저장소는 더 많은 공간을 차지합니다.
6. 금융 관련 금액 데이터는 소수점 형식을 사용해야 합니다.
정확한 부동 소수점: 소수
정확하지 않은 부동 소수점: float, double
십진수 형식은 계산 중에 정밀도를 잃지 않는 정확한 부동 소수점 숫자입니다. 공간을 차지합니다. 크기는 정의된 너비에 따라 결정됩니다. 4바이트마다 9자리를 저장할 수 있으며 소수점도 1바이트를 차지합니다. 또한 Decimal 유형을 사용하여 bigint보다 큰 데이터 유형을 저장할 수 있습니다.
4. MySQL 인덱스 설계 사양
1. 테이블당 인덱스 수는 5개를 초과할 수 없습니다.
인덱스는 쿼리 효율성을 높일 수 있지만 삽입 및 업데이트 효율성도 저하되며 경우에 따라 쿼리 효율성도 저하됩니다. . 더 많다고 좋은 것이 아니므로 양을 조절해야 합니다.
2. 각 Innodb 테이블에는 기본 키가 있어야 합니다.
각 테이블은 여러 개의 인덱스를 가질 수 있지만 table 저장 순서는 하나만 있을 수 있습니다. Innodb는 기본 키 인덱스 순서로 테이블을 구성하므로 자주 업데이트되는 열, UUID, MD5, HASH 및 문자열 열을 기본 키로 사용하지 마십시오. 기본 키 권장 사항 자동 증가 ID 값을 사용하세요.
3. 외래 키 제약 조건을 사용하지 마세요.
외래 키 제약 조건을 사용하는 것은 권장되지 않지만 테이블 간 관련 키에 대한 인덱스를 생성해야 합니다.
외래 키는 데이터 성능의 완전한 참조를 보장합니다. 그러나 외래 키는 상위 테이블과 하위 테이블의 쓰기 작업에도 영향을 미치므로 성능이 저하되고 테이블이 더욱 결합되게 만드는 경우 비즈니스 측면에서 구현하는 것이 좋습니다.
1、建议使用预编译语句进行数据库操作
预编译语句可以重复使用，相同的SQL语句可以一次解析，多次使用，减少SQL编译所需要的时间，提高处理效率；此外，还可以有效解决动态SQL带来的SQL注入问题。
2、避免数据类型的隐式转换
隐式转换如：SELECT 1 + "1"；数值型 + 字符型 的隐式转换有可能会导致索引失效，以及一些意想不到的结果等。
3、充分利用表中存在的索引
1）避免使用双%号的查询条件
如 WHERE first_name like '%James%'，若无前置%，只有后置%，则执行SQL语句时会用到列上的索引，双%号则不会使用列上的索引。
2）一条SQL语句只能使用复合索引中的一列进行范围查询
例如有weight、age、sex三列的联合索引，在查询条件中有weight列的范围查询，则在age和sex列上的索引将不会被使用；因此，在定义联合索引时，若某列需要用到范围查询，则将该列放到联合索引的右侧。
3）使用not exists 代替not in
因为not in 在SQL语句中执行时会导致索引失效。
4、杜绝使用SELECT * ，必须使用SELECT <字段列表> 查询
因为使用SELECT * 查询会消耗更多的CPU、IO和网络宽带资源，并且查询时无法使用覆盖索引。
5、禁止使用不含字段列表的INSERT 语句
如：INSERT into table_name values ('1','2','3'); 改为带字段列表的INSERT 语句：INSERT into table_name('c1','c2','c3') values ('1','2','3');
6、避免使用子查询，可以把子查询优化为join 关联操作
但是，通常子查询在in 子句中，且子查询中为简单SQL(即不包含union、group by、order by、limit从句)时，才可以把子查询转化为join关联查询进行优化；
子查询性能差的原因：
子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。
7、避免使用JOIN 关联太多表
1）在Mysql中，对于同一个SQL关联（join）多个表，每个join 就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大；
2）如果程序中大量的使用了多表关联的操作，同时join_buffer_size(MySQL允许关联缓存的个数)设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响服务器数据库性能的稳定性；
3）此外，对于关联操作来说，会产生临时表影响查询效率，而Mysql最多允许关联61个表，建议不超过5个；
8、对同一列对象进行or 判断时，使用in 替代or
in 的值只要涉及不超过500个，则in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。
9、禁止使用order by rand() 进行随机排序
10、禁止在WHERE 从句中对列进行函数转换和计算
因为在WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。
No推荐：
where date(end_time)='20190101'
推荐：
where end_time >= '20190101' and end_time < '20190102'
11、在明显不会有重复值时使用UNION ALL 而不是UNION
1）UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作；
2）UNION ALL 不会再对结果集进行去重操作；
12、把复杂、较长的SQL 拆分为为多个小SQL 执行
1）大SQL在逻辑上比较复杂，是需要占用大量CPU 进行计算一条SQL语句；
2）在MySQL中，一条SQL 语句只能使用一个CPU 进行计算；
3）SQL拆分后可以通过并行执行来提高处理效率。
1、超过100万行数据的批量操作(update delete insert)，分多次进行
大批量操作可能回造成严重的主从延迟；
binlog日志为row格式时会产生大量的日志；
避免产生大事物操作。
2、对于大表使用pt-online-schema-change 修改表结构
1）避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表；
2) pt-online-schema-change 먼저 원본 테이블과 동일한 구조의 새 테이블을 생성하고, 새 테이블의 테이블 구조를 수정한 다음, 원본 테이블의 데이터를 새 테이블에 복사하고, 원본 테이블에 일부 트리거를 추가한 다음, 원본 테이블에 새로 추가된 데이터를 새 테이블에 복사합니다. 모든 행 데이터가 복사된 후 새 테이블의 이름을 원본 테이블로 지정하고 원본 테이블을 삭제합니다. 원래 DDL 작업을 여러 개의 작은 배치로 나누어 실행합니다.
3. 프로그램에서 사용하는 계정에 슈퍼 권한을 부여하는 것은 금지되어 있습니다
최대 연결 수에 도달하면 슈퍼 권한을 가진 사용자가 계속 연결을 위해 실행됩니다. DBA가 문제를 해결합니다.
4. 프로그램이 데이터베이스 계정에 연결하려면 최소 권한의 원칙을 따르세요
프로그램에서 사용하는 데이터베이스 계정은 하나의 데이터베이스에서만 사용할 수 있으며, 원칙적으로 프로그램에서 사용하는 계정은 권한을 부여하지 않습니다. 권한을 삭제합니다.
위 내용은 프로그래머는 MySQL 사용 사양 매뉴얼을 알아야 합니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
