MySQL을 최적화하는 방법은 무엇입니까? mysql 관련 최적화-MySQL 튜토리얼-php.cn

1 하드웨어 계층 관련 최적화

1.1, CPU 관련

서버의 BIOS 설정에서 CPU 성능을 극대화하기 위해 다음 구성을 조정할 수 있습니다. , 또는 전형적인 NUMA 문제 방지:

1. 일반적으로 필요한 DB와 같은 서비스를 실행할 때 CPU 성능을 최대화하려면 DAPC(성능 최적화) 모드를 선택합니다. 많은 양의 계산을 끕니다.

2. CPU 효율성을 높이기 위해 C1E 및 C 상태와 같은 옵션을 끕니다. > 3. 메모리 주파수 주파수) 최대 성능을 선택합니다.

4. 메모리 설정 메뉴에서 NUMA 문제를 방지하려면 노드 인터리빙을 활성화합니다.

1.2. 디스크 I/O 관련
IOPS 성능 향상을 위해 디스크 I/O를 최적화할 수 있는 조치는 다음과 같습니다.

1. SSD 또는 PCIe SSD 장치를 사용하여 최소 수백 배 또는 수천 배의 IOPS 향상을 얻습니다.

2. CACHE 및 BBU 모듈이 장착된 어레이 카드를 구입합니다. IOPS를 크게 향상시킬 수 있습니다(주로 SSD 또는 PCIe SSD를 제외한 기계식 디스크를 의미합니다. 동시에 사고 시 데이터가 손실되지 않도록 CACHE 및 BBU 모듈의 상태를 정기적으로 확인해야 합니다). 🎜>

3, 어레이 카드가 있는 경우 어레이 쓰기 전략을 WB 또는 FORCE WB로 설정합니다(이중 전원 보호가 있거나 데이터 보안 요구 사항이 특별히 높지 않은 경우). , WT 전략을 사용하는 것은 엄격히 금지됩니다. 그리고 폐쇄형 배열 미리 읽기 전략은 기본적으로 쓸모가 없으며 거의 사용되지 않습니다. 　4. 가능한 한 RAID-5 대신 RAID-10을 사용하세요.

5. 기계식 디스크를 사용한다면 고속 디스크를 선택해보세요. 예를 들어 몇 달러도 안되는 7.2KRPM 디스크 대신 15KRPM 디스크를 선택하세요.

2 시스템 계층 관련 최적화

2.1. 파일 시스템 계층 최적화

파일 시스템 계층에서는 다음 조치를 통해 IOPS 성능을 크게 향상시킬 수 있습니다.

1. 두 개의 I/O 스케줄러를 사용합니다. cfq를 사용하지 마세요(DB 서비스 실행에는 적합하지 않음).

2 .xfs 파일 시스템을 사용하는 경우 ext3을 사용하지 마십시오. ext4는 거의 사용 가능하지 않지만 비즈니스 볼륨이 큰 경우에는 xfs를 사용해야 합니다.

매개변수 추가됨: noatime, nodiratime, nobarrier 옵션(nobarrier는 xfs 파일 시스템에 고유함);

　2.2, 기타 커널 매개변수 최적화

주요 커널 매개변수에 적절한 값을 설정하는 것은 스왑 경향을 줄이고 메모리 및 디스크 I/O의 큰 변동을 방지하여 순간적인 피크 로드를 방지하는 것입니다.

1. SWAP 사용 가능성을 줄이려면 vm.swappiness를 약 5-10 또는 심지어 0으로 설정하십시오(RHEL 7 이상에서는 0으로 설정하십시오). 🎜>　2. 더티 데이터가 디스크에 지속적으로 플러시되고 즉각적인 I/O 쓰기가 발생하지 않도록 vm.dirty_ground_ratio를 5~10으로 설정하고 vm.dirty_ratio를 약 2배로 설정합니다(비슷함).

3. net.ipv4.tcp_tw_recycle과 net.ipv4.tcp_tw_reuse를 모두 1로 설정하여 TIME_WAIT를 줄이고 TCP 효율성을 향상시킵니다.

4. 인터넷으로 전송되는 read_ahead_kb와 nr_requests의 두 매개변수는 테스트 결과 읽기와 쓰기가 주로 혼합되어 있는 OLTP 환경에는 거의 영향을 미치지 않는 것으로 나타났습니다. 쓰기) 민감한 장면이 더 효과적입니다. 하지만 제 테스트 방법에 문제가 있을 수도 있으니 임의로 조정하시면 됩니다.

3 MySQL 레이어 관련 최적화

3.1. 버전 선택에 대하여

정식 버전은 ORACLE MySQL이라고 할 수는 없습니다. 대부분의 사람들이 그것을 선택할 것입니다.

개인적으로 Percona 브랜치 버전을 선택하는 것이 좋습니다. 성능, 안정성 및 관리 측면에서 많은 개선이 이루어진 비교적 성숙하고 우수한 MySQL 브랜치 버전입니다. 기본적으로 ORACLE MySQL 공식 버전과 완벽하게 호환되며 성능도 약 20% 이상 향상되었기 때문에 먼저 추천해드리고 2008년부터 사용하고 있습니다.

또 다른 중요한 브랜치 버전은 MariaDB입니다. MariaDB의 목적은 ORACLE MySQL을 대체하는 것이기 때문에 실제로 브랜치 버전이라고 말하는 것은 부적절합니다. 주로 원본 MySQL Server 계층에서 소스 코드 수준을 많이 개선했으며 매우 안정적이고 우수한 분기 버전이기도 합니다. 그러나 이로 인해 GTID로 대표되는 새로운 기능이 공식 버전과 호환되지 않게 되었습니다(MySQL 5.7부터 GTID 모드도 온라인에서 동적으로 켜거나 끌 수 있도록 지원됨). 공식 버전이므로 MariaDB를 먼저 권장하지 않습니다.

3.2. 가장 중요한 매개변수 옵션 조정에 대한 제안

더 나은 성능을 얻으려면 다음 주요 매개변수를 조정하는 것이 좋습니다(my.cnf 생성기를 사용할 수 있음). 구성 파일 템플릿 생성):

1. Percona 또는 MariaDB 버전을 선택하는 경우 높은 동시성에서 성능을 향상시킬 수 있는 스레드 풀 기능을 활성화하는 것이 좋습니다. 조건상 큰 하락은 없을 것입니다. 또한 매우 실용적이며 중요한 순간에 생명을 구할 수 있는 extra_port 기능도 있습니다. 또 다른 중요한 기능은 QUERY_RESPONSE_TIME 함수로, 이를 통해 전체 SQL 응답 시간 분포를 직관적으로 느낄 수 있습니다.

　2. default-storage-engine=InnoDB를 설정합니다. InnoDB 엔진은 기본적으로 사용되지 않습니다. InnoDB 엔진은 확실히 99% 이상의 비즈니스 시나리오를 충족할 수 있습니다.

3. innodb_buffer_pool_size를 조정합니다. size. 단일 인스턴스이고 절대적으로 대부분이 InnoDB 엔진 테이블인 경우 물리적 메모리의 50%~70% 정도로 설정하는 것을 고려해 볼 수 있습니다.

4. 실제 필요에 따라 innodb_flush_log_at_trx_commit 및 sync_binlog 값을 설정합니다. 데이터가 손실될 수 없는 경우 둘 다 1로 설정합니다. 약간의 데이터 손실이 허용된다면 각각 2와 10으로 설정할 수 있습니다. 그리고 데이터가 손실되었는지 여부를 걱정할 필요가 없는 경우(예를 들어 슬레이브에서는 어쨌든 다시 실행됨) 모두 0으로 설정할 수 있습니다. 이 세 가지 설정 값이 데이터베이스 성능에 영향을 미치는 정도는 높음, 중간, 낮음입니다. 즉, 첫 번째 값은 데이터베이스를 가장 느리게 만들고 마지막 값은 그 반대가 됩니다. 🎜>

　5 , innodb_file_per_table = 1로 설정하고 독립 테이블 공간을 사용합니다. 공유 테이블 공간을 사용하면 어떤 이점도 생각할 수 없습니다. 기본 10M을 사용하지 마세요. 동시 트랜잭션이 많을 때 영향을 받습니다.

7. innodb_log_file_size=256M을 설정하고, 기본적으로 90% 이상의 시나리오를 충족할 수 있는 innodb_log_files_in_group=2를 설정합니다. > 8. long_query_time = 1로 설정. 5.5 이상 버전에서는 1 미만으로 설정할 수 있습니다. 해당 실행을 기록하려면 0.05(50밀리초)로 설정하는 것이 좋습니다. 느린 SQL, 후속 분석에 사용되며

9. 실제 비즈니스 요구에 따라 max_connection(최대 연결 수) 및 max_connection_error(최대 오류 수)를 10 이상으로 설정하는 것이 좋습니다. 1000개, open_files_limit, innodb_open_files, table_open_cache 및 table_definition_cache 매개변수는 max_connection 크기의 약 10배로 설정할 수 있습니다.

10. 일반적인 오해는 tmp_table_size를 설정하는 것이며 max_heap_table_size는 상대적으로 이 두 옵션은 각 연결 세션에 할당되므로 너무 크게 설정하지 마십시오. 그렇지 않으면 다음과 같은 다른 연결 세션 수준 옵션이 쉽게 발생합니다. read_buffer_size, read_rnd_buffer_size 등을 너무 크게 설정하지 않도록 주의해야 합니다.

11. MyISAM 엔진을 더 이상 사용하지 않는 것이 권장되므로 key_buffer_size를 약으로 설정할 수 있습니다. 32M, 쿼리 캐시 기능을 닫는 것이 좋습니다.

　3.3. 스키마 설계 사양 및 SQL 사용 제안

다음은 MySQL 효율성을 향상시키는 데 도움이 될 수 있는 몇 가지 일반적인 스키마 설계 사양 및 SQL 사용 제안 목록입니다.

1. 모든 InnoDB 테이블은 기본 키로 비즈니스 목적이 없는 자동 증가 열로 설계되었습니다. 이는 대부분의 시나리오에서 실제로 순수하게 읽기 전용인 경우입니다. . 그렇다면 TokuDB를 사용하는 것이 좋습니다.

2. 필드 길이가 요구 사항을 충족하는 한 가능한 가장 작은 길이를 선택합니다. 또한 필드 속성에 NOT NULL 제약 조건을 추가하면 성능이 어느 정도 향상될 수 있습니다.

3. 필요한 경우 TEXT/BLOB 유형을 사용하지 마십시오. 하위 유형 테이블로 분할하는 것이 좋습니다. SELECT * 시 읽기 성능이 저하되는 것을 방지하려면 기본 테이블과 함께 두지 마십시오.

4. 데이터를 읽을 때, 특히 일부 TEXT/BLOB 열을 읽을 때 심각한 무작위 읽기 문제를 피하기 위해 매번 SELECT *를 선택하지 마십시오. >

5. VARCHAR(N) 열에 인덱스를 생성할 때 일반적으로 길이의 약 50%(또는 그 이하)를 사용하여 접두사 인덱스를 생성하면 80% 이상을 충족하기에 충분합니다. 6. 일반적인 상황에서는 하위 쿼리의 성능이 상대적으로 낮습니다. JOIN 작성으로 변경하는 것이 좋습니다.

7. 다중 테이블 조인으로 쿼리할 때는 관련 필드의 유형이 최대한 일관되어야 하며 모두 인덱스가 있어야 합니다. >
(이것은 필터링된 결과 집합을 의미하며 반드시 전체 테이블의 적은 양의 데이터는 아님)

9. 여러 테이블을 조인하고 정렬하는 경우 정렬 필드가 구동 테이블에 있어야 합니다. 그렇지 않으면 정렬 열에서 인덱스를 사용할 수 없습니다.

10. 복합 인덱스를 더 많이 사용하세요. 특히 일부 카디널리티가 너무 작은 경우(예: 열의 총 고유 값 수가 255개 미만인 경우) 독립 인덱스를 생성하지 않으면 효율성이 훨씬 높아집니다.

　3.4. 기타 제안

MySQL 관리 및 유지 관리에 대한 기타 제안은 다음과 같습니다.

1. 일반적으로 단일 테이블은 물리적 크기는 10GB를 초과하지 않고, 단일 테이블의 행 수는 1억개를 초과하지 않으며, 평균 행 길이는 8KB를 초과하지 않습니다. 머신 성능이 충분하다면 MySQL은 이 정도의 데이터를 완전히 처리할 수 없습니다. 성능 문제에 대해 걱정할 필요가 있습니다. 이 제안은 주로 온라인 DDL이 더 비싸다는 점을 고려하기 위한 것입니다.
2. mysqld 프로세스가 너무 많은 메모리를 차지하는 것에 대해 너무 걱정하지 마십시오. OOM Kill이 발생하지 않고 SWAP을 많이 사용하면 괜찮습니다.

3. 예전에는 단일 머신에서 여러 인스턴스를 실행하는 목적이 있었습니다. 컴퓨팅 리소스 사용을 극대화합니다. 단일 인스턴스가 이미 대부분의 컴퓨팅 리소스를 소비하고 있다면 여러 인스턴스를 실행할 필요가 없습니다.

　4. 정기적으로 pt-duplicate-key를 사용하세요. -중복된 인덱스를 확인하고 삭제하는 검사기입니다. 정기적으로 pt-index-usage 도구를 사용하여 사용 빈도가 매우 낮은 인덱스를 확인하고 삭제합니다.

5. 느린 쿼리 로그를 정기적으로 수집하고 pt-query-digest 도구를 사용하여 분석합니다. , Anemometer 시스템과 결합하여 느린 쿼리 관리를 수행하여 느린 쿼리를 분석하고 후속 최적화 작업을 수행합니다.

6. pt-kill을 사용하여 장기 SQL 요청을 종료할 수 있습니다. . Percona 버전에는 옵션이 있습니다. innodb_kill_idle_transaction도 이 기능을 구현할 수 있습니다.

　7. 대형 테이블의 온라인 DDL 요구 사항을 완료하려면 pt-online-schema-change를 사용하세요. 🎜>
8. 정기적으로 pt-table-checksum 및 pt-table-sync를 사용하여 mysql 마스터-슬레이브 복제의 데이터 차이를 확인하고 복구합니다.

마지막에 작성: 이 최적화 참조, 큰 경우에 따라 적용 가능한 시나리오를 소개했습니다. 귀하의 응용 시나리오가 이 기사에 설명된 것과 다를 경우 복사하기보다는 실제 상황에 맞게 조정하는 것이 좋습니다. 기계적으로 말이죠. 질문과 제안은 환영하지만, 뇌를 통하지 않는 습관적인 저항은 거부됩니다.