MySQL 개발 사양에 대한 나의 의견

대부분의 MySQL 사양은 인터넷에서도 찾을 수 있습니다. 여기서 공유하고 싶은 내용은 Lao Ye가 개인적으로 더 중요하다고 생각하거나 쉽게 간과되고 쉽게 혼동되는 사항입니다.

1. 기본적으로 InnoDB 엔진을 사용합니다

[올드예의 관점] 많이 요청했어요 InnoDB는 거의 99%의 MySQL 애플리케이션 시나리오에 적합하며 MySQL 5.7의 시스템 테이블이 InnoDB로 변경되었으므로 MyISAM을 고수할 이유가 없습니다.

또한 자주 읽고 쓰는 InnoDB 테이블은 자동 증가/순차 특성을 갖는 정수를 명시적 기본 키로 사용해야 합니다.

　[참고]: [MySQL FAQ] 시리즈 - InnoDB 테이블의 기본 키로 자동 증가 열을 사용하는 것이 권장되는 이유는 무엇입니까?

　2. 문자셋을 utf-8로 선택

[Old Ye의 관점] 디스크 공간을 절약하고 싶다면 latin1을 선택하는 것이 좋습니다. 일반적으로 소위 "보편성"을 위해 UTF-8을 선택하는 것이 권장되지만 실제로 사용자가 제출한 UTF-8 데이터는 latin1 문자 집합에도 저장될 수 있습니다.

latin1을 사용하여 utf-8 데이터를 저장할 때 발생할 수 있는 문제는 중국어 기반 검색이 있는 경우 100% 정확하지 않을 수 있다는 것입니다(Lao Ye는 개인적으로 일반 중국어 완전 검색을 테스트했으며 전혀 문제가 되지 않았습니다. 즉, 일반적인 중국어 비교는 문제가 되지 않습니다.

latin1 문자 세트를 사용하여 utf-8 데이터를 저장하는 방법은 웹 측(사용자 측)의 문자 세트가 utf-8이고 백엔드 프로그램에서도 utf-8을 사용하여 처리하는 것입니다. 하지만, Character_set_client, Character_set_connection, Character_set_results, Character_set_database, Character_set_server는 모두 latin1이며, 데이터 테이블과 필드의 문자 집합도 latin1입니다. 또는 데이터 테이블이 latin1을 사용합니다. 각 연결 후에 SET NAMES LATIN1을 실행하세요.

　[참고]: MySQL 문자 집합에 대한 간략한 설명입니다.

　3. InnoDB 테이블 행 레코드의 물리적 길이는 8KB를 초과하지 않습니다

[Old Ye의 관점] InnoDB의 기본 데이터 페이지는 16KB를 기준으로 합니다. B+Tree의 특성상 데이터 페이지에는 최소 2개의 레코드가 저장되어야 합니다. 따라서 실제 저장 길이가 8KB를 초과하는 경우(특히 TEXT/BLOB 열) 큰 열(대형 열)은 ORACLE의 "행 마이그레이션"과 유사한 "페이지 오버플로 저장"을 발생시킵니다.

따라서 큰 열(특히 TEXT/BLOB 유형)을 사용하고 자주 읽고 써야 하는 경우 이러한 열을 하위 테이블로 분할하고 기본 테이블과 함께 저장하지 않는 것이 가장 좋습니다. 너무 자주 발생하지 않는 경우 기본 테이블에 보관하는 것이 좋습니다.

물론 innodb_page_size 옵션을 8KB로 수정한다면 행 레코드의 물리적 길이는 4KB를 넘지 않는 것이 좋습니다.

　[참고]: [MySQL 최적화 사례] ​​시리즈 - InnoDB 테이블에서 BLOB 컬럼의 저장 효율성을 최적화합니다.

　4. 파티션 테이블 사용 여부

[올드예의 관점] 파티션 테이블을 사용하면 성능이나 운영 및 유지 관리 편의성이 확실히 향상되는 경우도 있습니다. , 분할된 테이블을 사용하는 것이 좋습니다.

예를 들어 Lao Ye는 zabbix의 데이터베이스에서 TokuDB 엔진을 사용하고 시간 차원을 기반으로 파티션 테이블을 사용합니다. 이것의 장점은 Zabbix의 일상적인 응용 프로그램이 영향을 받지 않도록 하고 관리자가 과거 데이터를 정기적으로 삭제하는 것이 편리하다는 것입니다. 해당 파티션만 삭제하면 되며 영향을 미치는 매우 느린 DELETE를 실행할 필요가 없습니다. 전반적인 성능.

　[참조]: Zabbix 데이터베이스를 TokuDB로 마이그레이션합니다.

　5. 저장 프로시저와 트리거 사용 여부

　[라오예의 관점] 일부 적합한 시나리오에서는 저장 프로시저와 트리거를 사용해도 문제가 되지 않습니다.

우리는 게임 비즈니스 로직 처리를 완료하기 위해 스토리지를 사용했습니다. 성능은 문제가 되지 않으며, 요구 사항이 변경되면 저장 프로시저만 수정하면 되며 변경 비용이 매우 낮습니다. 또한 자주 업데이트되는 테이블을 유지하기 위해 트리거를 사용합니다. 이 테이블에 대한 모든 변경 사항은 일부 필드를 다른 테이블로 동기적으로 업데이트하며(구체화된 뷰의 위장된 구현과 유사) 성능 문제가 없습니다.

MySQL의 저장 프로시저와 트리거를 골칫거리로 여기지 마십시오. 잘 사용하면 문제가 발생하더라도 최적화해도 늦지 않습니다. 또한 MySQL에는 구체화된 뷰가 없으므로 뷰를 가능한 한 적게 사용하십시오.

　6. 올바른 유형 선택

【라오예의 관점】공통적인 제안 외에도 몇 가지 사항이 있습니다:

　6.1 .IPV4 주소를 저장하려면 INT UNSIGNED를 사용하고 변환에는 INET_ATON() 및 INET_NTOA()를 사용하여 저장을 위해 기본적으로 사용할 필요가 없습니다.

6.2. ENUM의 내부 저장 메커니즘은 CHAR/VARCHAR이 아닌 TINYINT 또는 SMALLINT를 사용하여 열거형을 저장하는 것이 좋습니다. 데이터를 제공합니다.

6.3. 앞서 퍼진 '상식적인 잘못된 정보'는 DATETIME 대신 TIMESTAMP를 사용하는 것이 좋습니다. 실제로 5.6부터는 DATETIME에 우선 순위를 두어 날짜와 시간을 저장하는 것이 좋습니다. 왜냐하면 사용 가능한 범위가 TIMESTAMP보다 크고, 물리적 저장 공간도 TIMESTAMP보다 1byte만 더 많아 전체적인 성능 손실은 크지 않기 때문입니다. .

6.4. 모든 필드 정의에는 NULL이어야 하는 경우를 제외하고는 기본적으로 NOT NULL 제약 조건이 추가됩니다(그러나 NULL 값을 데이터베이스에 저장해야 하는 시나리오는 생각할 수 없습니다. 0). 이 필드에 대해 COUNT() 통계를 수행하면 통계 결과가 더 정확해지거나(NULL 값이 있는 경우 COUNT로 계산되지 않음), WHERE 컬럼 IS NULL 검색을 수행하면 결과가 빠르게 반환될 수도 있습니다.

6.5. 특히 테이블에 큰 TEXT/BLOB 열이 있는 경우 모든 필드를 읽기 위해 직접 SELECT *를 사용하지 마십시오. 이러한 열을 읽을 필요가 없을 수도 있지만 게으르고 SELECT *를 썼기 때문에 이러한 "정크" 데이터로 인해 메모리 버퍼 풀이 씻겨 나가고 실제로 버퍼링이 필요한 핫 데이터가 씻겨 나갔습니다.

　8. 지수에 대하여

[올드예의 관점] 일반적인 제안 외에도 몇 가지 핵심 사항이 있습니다.

8.1, 20개 이상 길이의 문자열 열의 경우 전체 열 인덱스(예: ALTER TABLE t1 ADD INDEX(user(20)))보다는 접두사 인덱스를 생성하는 것이 가장 좋지만, 이는 인덱스 활용도를 효과적으로 향상시킬 수 있다는 단점이 있습니다. 이 열을 접두사 인덱스로 정렬할 때 사용할 필요가 없다는 것입니다. 접두사 인덱스의 길이는 필드의 통계를 기반으로 결정될 수 있으며 일반적으로 평균 길이보다 약간 큽니다.

8.2. pt-duplicate-key-checker 도구를 정기적으로 사용하여 중복된 인덱스를 확인하고 삭제하세요. 예를 들어, 인덱스 idx1(a, b)가 이미 인덱스 idx2(a)를 포함하고 있다면 idx2 인덱스를 삭제할 수 있습니다.

8.3. 다중 필드 결합 인덱스가 있는 경우 WHERE의 필터 조건의 필드 순서는 인덱스와 일치할 필요는 없지만 정렬 및 그룹화가 있는 경우에는 일관성이 있어야 합니다.

예를 들어, 결합 인덱스 idx1(a, b, c)이 있는 경우 다음 SQL은 인덱스 를 완전히 사용할 수 있습니다.

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到，WHERE中字段顺序并没有和索引字段顺序一致
SELECT ... WHERE b = ? AND a = ? AND c = ?;
SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;
SELECT ... WHERE a = ? AND b = ? ORDER BY c;
SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;
SELECT ... WHERE a = ? ORDER BY b, c;
SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

그리고 다음은 SQL은 인덱스의 일부만 사용할 수 있습니다:

SELECT ... WHERE b = ? AND a = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a IN (?, ?) AND b = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a = ? AND c = ?;   -- 只能用到 (a) 部分
SELECT ... WHERE a = ? AND b IN (?, ?);    -- 只能用到 (a, b) 部分
SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?;   -- 只能用到 (a) 部分，注意BETWEEN和IN的区别
SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?;    -- 只能用到 (a, b) 部分

다음 SQL은 이 인덱스를 전혀 사용하지 않습니다 :

SELECT ... WHERE b = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... ORDER BY b;
SELECT ... ORDER BY b, a;

위의 예를 보면 과거에 WHERE 조건 필드의 순서가 인덱스를 사용하기 전 인덱스의 순서와 일치해야 한다고 강조했던 "상식오해"가 반드시 일치할 필요는 없음을 알 수 있다. 엄격하게 따라야 합니다.

또한 쿼리 최적화 프로그램에서 지정한 인덱스나 실행 계획이 최적이 아닐 수도 있습니다. 최적의 인덱스를 수동으로 지정하거나 세션 수준의 Optimizer_switch 옵션을 수정하여 악화를 초래할 수 있는 일부 요소를 끌 수도 있습니다. 기능(예를 들어 인덱스 병합은 일반적으로 좋은 일이지만 인덱스 병합을 사용한 후에는 더 나빠지는 경우도 있습니다. 이 경우 인덱스 중 하나를 강제로 지정해야 하거나 인덱스 병합 기능을 사용해야 합니다. 일시적으로 꺼질 수 있습니다.)

　9. 기타

　9.1. 인덱스 기반 조건 필터링이라 하더라도, 스캔해야 할 총 데이터 양이 30개를 초과한다고 옵티마이저가 인식한 경우 %(ORACLE에서는 20%, MySQL은 현재 30%인 것 같은데, 앞으로 조정될 수도 있음) 실행 계획을 전체 테이블 스캔으로 직접 변경하고 더 이상 인덱스를 사용하지 않습니다.

9.2. 여러 테이블을 조인할 때 필터링 가능성이 가장 높은 테이블(데이터의 양이 가장 적을 필요는 없지만 WHERE 조건만 추가한 후 필터링 가능성이 가장 높은 테이블)을 구동 테이블로 선택해야 합니다. 또한 JOIN 이후 정렬이 있는 경우 정렬 필드가 드라이버 테이블에 속해야 드라이버 테이블의 인덱스를 사용하여 정렬을 완료할 수 있습니다.

9.3. 대부분의 경우 정렬 인덱스가 더 높으므로 실행 계획에 Using filesort가 표시되면 먼저 정렬 인덱스를 생성합니다.

9.4. pt-query-digest를 사용하여 느린 쿼리 로그를 정기적으로 분석하고, Box Anemometer와 결합하여 느린 쿼리 로그 분석 및 최적화 시스템을 구축합니다.

　[참고]: [MySQL FAQ] 시리즈 - EXPLAIN 결과의 어떤 정보가 우려를 불러일으킬까요?

참고: 별도로 지정하지 않는 한 위의 사양 권장 사항은 MySQL 5.6 및 이전 버전에 적용됩니다. 버전 5.7 이상에서는 일부 변경 사항이 있을 수 있으며 이에 따라 개별 사양 권장 사항을 조정해야 합니다.

이상은 MySQL 개발 스펙에 대한 저의 의견입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!