전에 쓴 말: 재해 복구의 중요성은 항상 재난이 발생한 후에야 생각난다. 누군가의 기억은 손실을 겪은 후에야 기억된다

이 글은 주로 mysql 데이터베이스에 관한 것입니다

디자인 원칙

1. 데이터베이스에서 계산하지 마세요: CPU 계산을 비즈니스 계층으로 옮겨야 합니다
2. 단일 테이블의 데이터 양을 제어합니다. 단일 테이블의 레코드는 1000w로 제어됩니다
3. 열 수 제어: 필드 수는 20개 이내로 제어됩니다.
4. 패러다임 및 중복성 균형: 효율성 향상을 위해 패러다임 설계 및 중복 데이터 희생
5. 대규모 SQL 거부 , 대규모 배치
6. 테이블 문자 집합에 UTF8 사용
7. INNODB 스토리지 엔진 사용

데이터 테이블 디자인

1. 가장 효과적인(가장 작은) 데이터 유형을 최대한 사용하세요

2. 숫자를 문자열로 저장하지 말고, 문자를 숫자로 변환하고, char(15) 대신 int를 사용하여 ip를 저장하세요.
3. enum이나 set을 먼저 사용하세요. sex enum('F', ' M')
4, NULL 필드 사용을 피하세요

5. varchar의 성능은 텍스트보다 훨씬 높습니다. blob을 피할 수 없는 경우에는 테이블을 분할하세요.
6. 데이터베이스에 이미지를 저장하지 마세요. MyISAM 테이블은 가변 길이 열(VARCHAR, TEXT 또는 BLOB 열)이 없는 경우 고정 크기 레코드 형식을 사용합니다. 이는 더 빠르지만 불행하게도 일부 공간을 낭비할 수 있습니다. CREATE 옵션을 사용하여 VARCHAR 열을 ROW_FORMAT=fixed로 설정한 경우에도 고정 길이 행을 사용하라는 메시지가 계속 표시될 수 있습니다
8. latin1과 같은 샘플 문자 집합을 사용하세요. utf-8은 latin1보다 3배의 공간을 차지하므로 utf-8을 가능한 한 적게 사용하십시오. 메일, URL 등 utf-8을 사용할 필요가 없는 필드에는 latin1을 사용할 수 있습니다.
9. 정확도와 공간 간의 변환. 부동 소수점 유형은 일반적으로 동일한 숫자 범위의 데이터를 저장할 때 DECIMAL 유형보다 적은 공간을 사용합니다. FLOAT 필드는 4바이트를 사용하여 데이터를 저장합니다. DOUBLE 유형은 8바이트가 필요하며 정확도가 더 높고 숫자 범위가 더 큽니다. DECIMAL 유형 데이터는 DOUBLE 유형
10으로 변환됩니다. 라이브러리 이름, 테이블 이름 및 필드 이름은 12 이내의 고정된 이름 지정 길이를 가져야 합니다. 문자, 라이브러리 이름, 테이블 이름 및 필드 이름은 32자를 초과할 수 없습니다. 이름의 의미를 이해해야 합니다. 라이브러리 이름, 테이블 이름 및 필드 이름에는 MySQL 예약어가 금지됩니다. 임시 라이브러리 및 테이블 이름은 접두사 tmp, 접미사 뒤에 날짜가 있어야 합니다. 11. InnoDB 테이블 행 레코드의 물리적 길이는 8KB를 초과하지 않습니다. InnoDB의 기본 데이터 페이지는 16KB입니다. B+Tree의 특성에 따라 데이터 페이지는 최소 2개의 레코드를 저장해야 합니다. . 따라서 큰 열의 실제 저장 길이가 8KB를 초과하는 경우(특히 TEXT/BLOB 열) ORACLE의 "행 마이그레이션"과 유사한 "페이지 오버플로 저장"이 발생합니다. 따라서 큰 열을 사용해야 하는 경우(특히. TEXT/BLOB 유형) 자주 읽고 쓰는 경우 이러한 열을 하위 테이블로 분할하고 너무 자주 사용하지 않는 경우 기본 테이블에 저장하지 않는 것이 가장 좋습니다. innodb
page
size 옵션을 8KB로 변경하는 경우 행 레코드의 물리적 길이는 4KB를 초과하지 않는 것이 좋습니다 인덱스 클래스

1. 인덱스를 신중하고 합리적으로 사용하세요

2. 문자 필드에 대해 접두사 인덱스를 구축해야 합니다
3. 인덱스에서 열 작업을 수행하지 마세요. 나쁜 경우: age +1 = 10;
4을 사용하는 것이 좋습니다. innodb 기본 키에 대한 자동 증가 열

5. 외래 키를 사용하지 마세요. 프로그램의 제약 조건을 확인하세요.
6. 이미 인덱스가 있는 접두사에는 인덱스를 생성하지 마세요. 예: 인덱스(a, b)가 존재하면 인덱스(a)를 제거합니다.
7. 단일 인덱스의 길이를 제어합니다. 키(이름 (8))을 사용하여 데이터의 처음 몇 문자에 대한 인덱스를 생성합니다
8. 인덱스를 선택적으로 사용합니다. 성별 열과 같이 거의 변경되지 않는 열에 인덱스를 사용하는 것은 그리 좋지 않습니다
9. 최적화 테이블은 인덱스를 압축하고 정렬할 수 있으므로 자주 실행하지 않도록 주의하세요
10. 테이블을 분석하면 데이터가 업데이트될 수 있습니다
11. 인덱스 선택성은 테이블의 데이터 행 수에 대한 고유 인덱스 값(카디널리티라고도 함)의 비율입니다. 인덱스 선택성 = 카디널리티/데이터 행, count(distinct(username))/count(*)는 인덱스 선택성입니다. , 높은 인덱스 선택성의 장점은 일치 항목을 검색할 때 MySQL이 더 많은 행을 필터링할 수 있다는 것입니다. 고유 인덱스는 1
12의 값으로 최고의 선택성을 갖습니다. INNODB 엔진은 매번 데이터를 수정하려면 기본 키 인덱스와 보조 인덱스의 해당 인덱스 값을 수정해야 하며 이로 인해 대량의 데이터 마이그레이션, 페이징 및 조각화가 발생할 수 있습니다.
13. 문자열 열의 경우 길이가 20을 초과하는 경우 접두사 인덱스를 만드는 것이 가장 좋습니다. 정수가 아닌 열 인덱스(예: ALTER TABLE t1 ADD INDEX(user(20)))는 인덱스 활용도를 효과적으로 향상시킬 수 있지만 접두사가 있다는 단점이 있습니다. 이 열을 정렬할 때는 인덱스가 사용되지 않습니다. 프리픽스 인덱스의 길이는 필드의 통계를 기반으로 결정할 수 있습니다. 일반적으로 평균 길이보다 약간 큽니다.
14. 정기적으로 pt-duplicate-key-checker 도구를 사용하여 중복 인덱스를 확인하고 삭제합니다. 예를 들어, 인덱스 idx1(a, b)가 이미 인덱스 idx2(a)를 포함하고 있는 경우 idx2 인덱스

SQL 문 디자인 수업

1. sql 문은 최대한 간단합니다. 하나의 SQL은 하나의 CPU에서만 작동할 수 있습니다. 큰 문은 작은 문으로 나누어서 전체 라이브러리를 차단할 수 있습니다. CACHE 및 멀티코어 CPU 활용)
2. 단순 트랜잭션, 트랜잭션 시간은 최대한 짧아야 하며, 나쁜 경우: trig/func 사용을 피하세요. 트리거 및 함수는 다음과 같습니다. 사용하지 않는 경우 클라이언트 프로그램으로 교체하세요
4. 선택*이 필요하지 않으며 CPU, IO, 메모리 및 대역폭을 소비합니다
5. 또는 IN()으로 다시 작성됩니다. > 으아악
6. OR을 UNION으로 다시 작성합니다

MySQL 인덱스 병합은 정말 바보같습니다

7. not in/like와 같은 음수 %를 피하세요

9. 효율적인 페이징을 제한하세요

한계가 클수록 효율은 낮아집니다

10. 유니온 대신 유니온을 사용하세요.

12. 그룹화, 자동 정렬을 사용하세요
>14. 데이터를 가져오려면 데이터 로드가 삽입보다 약 20배 빠릅니다.
15. 한 번에 너무 많은 데이터를 나누어 업데이트하지 마세요.
16. 성능 분석 도구 사용
으아악
17. 느린 쿼리 문을 보려면 --log-slow-queries --long-query-time=2를 사용하세요. 그런 다음 explain을 사용하여 쿼리를 분석하고 최적화합니다
으아아아

최적화 데이터가 삽입, 업데이트 및 삭제되면 일부 데이터 마이그레이션 및 페이징이 불가피하며 시간이 지남에 따라 일부 조각이 나타나게 됩니다. 이를 위해서는 DBA가 정기적으로 데이터베이스를 최적화하여 조각을 줄여야 합니다. 이는 최적화 명령을 통해 수행됩니다. 예를 들어 MyISAM 테이블에서 작업하는 경우: 테이블 이름 최적화

18. 데이터베이스에서 대규모 쿼리를 실행하는 것은 금지되어 있습니다
19. 미리 컴파일된 문을 사용하고 매개변수만 전달하면 SQL 문을 한 번 구문 분석하고 여러 번 사용하는 것보다 효율적입니다.
20. rand()에 의한 순서 사용은 금지됩니다
21. 단일 SQL 문으로 동시에 여러 테이블을 업데이트하는 것은 금지됩니다
22. 수학적 연산과 논리적 판단을 잘 못함)
23 , SQL 문은 모두 R&D가 필요하고, SQL 키워드는 모두 대문자이며, 각 단어에는 공백이 하나만 허용됩니다
24. NOT IN을 사용할 수 있으면 NOTIN을 사용할 수 있습니다. 함정이 너무 많습니다. . 비어 있음과 NULL을 감지합니다

주목

1. 인덱스 기반의 조건 필터링이라 하더라도, 스캔해야 하는 전체 데이터량이 30%를 초과한다는 것을 옵티마이저가 인지한다면(ORACLE에서는 20%, 현재 MySQL에서는 30%인 것 같습니다) 향후 조정될 수 있음), 실행 계획을 전체 테이블 스캔으로 직접 변경하고 더 이상 인덱스를 사용하지 않습니다
2. 여러 테이블을 조인할 때 필터링은 가장 커야 합니다(반드시 가장 작은 양은 아닙니다). 하지만 WHERE 조건) )을 추가한 후 필터링이 가장 큰 데이터가 드라이버 테이블로 선택됩니다. 또한 JOIN 이후에 정렬이 있는 경우 정렬 필드는 드라이버 테이블에 속해야 하기 때문에 드라이버 테이블의 인덱스를 사용하여 정렬을 완료할 수 있습니다
3. 대부분의 경우 정렬 비용은 일반적으로 높으므로 실행 계획에 Using filesort가 보이면 먼저 정렬 인덱스를 생성하세요
4. pt-query-digest를 사용하여 느린 쿼리 로그를 정기적으로 분석하고 Box Anemometer와 결합하여 느린 쿼리를 구축합니다. 로그 분석 및 최적화 시스템

오늘 흥미로운 것을 봤습니다.
어떤 알고리즘/데이터 구조를 이름으로 '인식'하고 알아야 할까요?