가장 포괄적인 MySQL 디버깅 및 최적화 기술-MySQL 튜토리얼-php.cn

MySQL은 강력한 오픈 소스 데이터베이스입니다. 점점 더 많은 데이터베이스 기반 애플리케이션으로 인해 사람들은 MySQL을 한계까지 밀어붙이고 있습니다. MySQL 설치를 조정하고 최적화하기 위한 101가지 팁은 다음과 같습니다. 일부 팁은 특정 설치 환경에만 적용되지만 아이디어는 일반적인 것입니다. 더 많은 MySQL 튜닝 및 최적화 기술을 익히는 데 도움이 되도록 여러 범주로 나누었습니다.

MySQL 서버 하드웨어 및 운영 체제 조정:

1. 전체 InnoDB 파일을 메모리에 로드할 수 있을 만큼 충분한 물리적 메모리가 있어야 합니다. 메모리에 있는 파일에 액세스하는 것이 하드 디스크에 액세스하는 것보다 훨씬 빠릅니다.
2. 스왑을 사용하지 마십시오. 스왑은 하드 드라이브에서 읽혀지며 속도가 매우 느립니다.
3. 배터리로 구동되는 RAM을 사용하세요(참고: RAM은 랜덤 액세스 메모리입니다).
4. 고급 RAID(참고: 저렴한 디스크의 중복 배열, 즉 디스크 배열)를 사용하세요. RAID10 이상이 좋습니다.
5. RAID5를 피하세요(참고: 스토리지 성능, 데이터 보안 및 스토리지 비용의 균형을 맞추는 스토리지 솔루션). 데이터베이스 무결성을 보장하려면 검증에 대한 비용을 지불해야 합니다.
6. 논리적으로뿐만 아니라 물리적으로도 운영 체제와 데이터 파티션을 분리합니다. 운영 체제의 읽기 및 쓰기 작업은 데이터베이스 성능에 영향을 미칩니다.
7. MySQL 임시 공간과 복제 로그 및 데이터를 다른 파티션에 배치합니다. 데이터베이스 배경이 디스크에서 읽고 쓸 때 데이터베이스 성능에 영향을 미칩니다.
8. 디스크 공간이 많을수록 속도가 빨라집니다.
9. 더 좋고 더 빠른 디스크.
10. SATA(참고: SATA, 직렬 하드 드라이브) 대신 SAS(참고: Serial Attached SCSI, Serial Attached SCSI)를 사용합니다.
11. 특히 RAID 구성에서는 작은 하드 드라이브가 큰 하드 드라이브보다 빠릅니다.
12. 배터리 지원 캐시 RAID 컨트롤러를 사용하세요.
13. 소프트웨어 디스크 어레이를 사용하지 마세요.
14. 데이터 파티션에는 솔리드 스테이트 IO 카드(디스크 드라이브 아님)를 사용하는 것이 좋습니다. 이 카드는 거의 모든 양의 데이터에 대해 2GB/s 쓰기 속도를 지원할 수 있습니다.
15. Linux에서 swappiness 값을 0으로 설정 – 데이터베이스 서버에 파일을 캐시할 이유가 없으며 이는 서버나 데스크탑에 유리합니다.
16. 가능하다면 noatime 및 nodirtime을 사용하여 파일 시스템을 마운트하세요. 액세스된 데이터베이스 파일의 수정 시간을 업데이트할 이유가 없습니다.
17. XFS 파일 시스템을 사용하세요. 이 파일 시스템은 ext3보다 빠르고 작으며 로깅 옵션이 많으며 ext3에는 MySQL에서 이중 버퍼링 문제가 있는 것으로 나타났습니다.
18. 최고의 성능 표준을 위해 XFS 파일 시스템 로그 및 버퍼 변수를 조정합니다.
19. Linux 시스템에서는 NOOP 또는 DEADLINE IO 예약 스케줄러를 사용하세요. – NOOP 및 DEADLINE 예약 스케줄러에 비해 CFQ 및 ANTICIPATORY 예약 스케줄러는 매우 느립니다.
20. 64비트 운영 체제를 사용하세요. MySQL의 경우 메모리 지원 및 사용량이 더 많아집니다.
21. 서버에서 사용하지 않는 설치 패키지와 데몬을 삭제하면 리소스 사용량이 줄어듭니다.
22. MySQL을 사용하는 호스트와 MySQL 호스트를 호스트 파일에 넣습니다. DNS 조회는 없습니다.
23. 절대로 MySQL 프로세스를 강제 종료하지 마세요. 데이터베이스와 백업을 실행하는 프로그램이 손상됩니다.
24. 서버를 MySQL에 기여합니다. 백그라운드 프로세스 및 기타 서비스는 데이터베이스가 차지하는 CPU 시간을 단축할 수 있습니다.

MySQL 구성:

25. 작성 시 innodb_flush_method=O_DIRECT를 사용하여 이중 버퍼링을 방지하세요.
26. O_DIRECT 및 EXT3 파일 시스템을 사용하지 마세요. 작성한 모든 내용을 직렬화하게 됩니다.
27. 전체 InnoDB 파일을 메모리에 로드하기에 충분한 innodb_buffer_pool_size를 할당하세요. 디스크에서 읽는 횟수는 줄어듭니다.
28. innodb_log_file_size 매개변수를 너무 크게 설정하지 마십시오. 그러면 더 빠르고 더 많은 디스크 공간을 확보할 수 있습니다. 일반적으로 더 많은 로그를 버리는 것이 좋으며 데이터베이스 충돌 후 데이터베이스를 복원하는 시간을 줄일 수 있습니다.
29. innodb_thread_concurrency와 thread_concurrency 매개변수를 혼합하지 마십시오. 이 두 값은 호환되지 않습니다.
30. max_connections 매개변수에 아주 작은 숫자를 할당하세요. 연결이 너무 많으면 RAM이 소모되고 MySQL 서비스가 잠깁니다.
31. 연결을 열 때 속도가 느려지는 것을 방지하려면 thread_cache를 상대적으로 높은 숫자인 16 정도로 유지하세요.
32. Skip-name-resolve 매개변수 사용 - DNS 조회를 제거합니다.
33. 쿼리가 반복되고 데이터가 자주 변경되지 않는 경우 쿼리 캐싱을 사용할 수 있습니다. 그러나 데이터가 자주 변경되는 경우 쿼리 캐시를 사용하면 문제가 해결됩니다.
34. 디스크에 쓰는 것을 방지하려면 temp_table_size 값을 늘리세요.
36. sort_buffer_size 값을 너무 높게 설정하지 마세요. 그렇지 않으면 메모리가 빨리 소모됩니다. 37. 크기를 결정하세요. 일반적으로 key_read_requests는 key_reads 값보다 높아야 합니다. 그렇지 않으면 key_buffer를 효율적으로 사용할 수 없습니다
38. innodb_flush_log_at_trx_commit을 0으로 설정하면 성능이 향상되지만 (1)의 경우 기본값을 유지합니다. 그런 다음 데이터의 무결성을 보장해야 하며 복제가 지연되지 않는지도 확인해야 합니다.
39. 구성을 테스트하고 정상적인 프로덕션에 영향을 주지 않고 자주 다시 시작하려면 테스트 환경이 필요합니다.

MySQL 스키마 최적화:

40. 데이터베이스를 체계적으로 유지하세요.

41. 이전 데이터 보관 – 반환 또는 검색 쿼리에 대한 중복 행을 제거합니다.

42. 데이터를 색인화하세요.
44. 텍스트 및 BLOB 데이터 유형을 압축하여 디스크 읽기를 줄입니다.
45. 둘 다 latin1보다 낮습니다. 실행 효율성.
46. 트리거를 드물게 사용하세요.
47. 중복되는 데이터를 최소화하세요. 불필요한 데이터를 반복하지 마세요.
48. 확장 행 대신 연결된 테이블을 사용하세요.
49.
50. 다른 데이터는 쿼리에 자주 사용되지만 BLOB/TEXT 데이터는 그렇지 않은 경우
51. 테이블을 자주 확인하고 최적화하세요. InnoDB 테이블 최적화를 자주 다시 작성하세요.
53. 때로는 열을 추가할 때 인덱스를 다시 추가하세요.
54. 다른 요구 사항을 충족하려면 다른 스토리지 엔진을 사용하세요. 로그 테이블이나 감사 테이블의 경우 - 쓰기에 더 효율적입니다.
56. 세션 데이터는 MySQL 대신 캐시(memcache)에 저장됩니다. 캐시는 자동 자동 채우기 값을 허용하고 읽기 어려운 시공간 데이터를 생성하는 것을 방지합니다.
57. 가변 길이 문자열을 저장할 때 CHAR 대신 VARCHAR을 사용하세요. CHAR은 고정 길이가 아니기 때문에 공간이 절약됩니다(UTF8은 이에 영향을 받지 않습니다).
58. – 작은 변화가 큰 영향을 미칠 수 있습니다.
59. 프로덕션 변경 사항을 반영하는 개발 환경에서 모든 스키마를 테스트하세요.
60. 구성 파일의 값을 임의로 변경하지 마세요. 심각한 영향을 미칠 수 있습니다.
61. MySQL 구성에서는 적은 것이 더 좋습니다.
62. 의심스러운 경우 일반 MySQL 구성 파일을 사용하세요.

쿼리 최적화:

63. 느린 쿼리 로그를 사용하여 느린 쿼리를 검색하세요.
64. 실행 계획을 사용하여 쿼리가 정상적으로 실행되고 있는지 확인하세요.
65. 쿼리가 최적으로 실행되고 있는지 항상 테스트하세요. 성능은 시간이 지나면서 항상 변합니다.
66. 전체 테이블에 count(*)를 사용하지 마세요. 전체 테이블이 잠길 수 있습니다.
67. 후속 유사한 쿼리가 쿼리 캐시를 사용할 수 있도록 쿼리의 일관성을 유지하세요.
68. 적절한 경우 DISTINCT 대신 GROUP BY를 사용하세요.
69. WHERE, GROUP BY 및 ORDER BY 절에 인덱스 열을 사용하세요.
70. 인덱스를 단순하게 유지하고 여러 인덱스에 동일한 열을 포함하지 마세요.
71. 때때로 MySQL은 잘못된 인덱스를 사용합니다. 이 경우 USE INDEX를 사용하세요.
72. SQL_MODE=STRICT 사용 시 문제를 확인하세요.
73. 5개 미만의 레코드가 있는 인덱스 필드의 경우 UNION에서 LIMIT를 사용하는 것은 OR이 아닙니다.
74. 업데이트하기 전에 SELECT를 피하려면 INSERT ON DUPLICATE KEY 또는 INSERT IGNORE를 사용하여 구현하지 마세요.
75. MAX를 사용하지 말고 인덱스 필드와 ORDER BY 절을 사용하세요.
76. ORDER BY RAND()를 사용하지 마세요.
77. LIMIT M, N은 실제로 쿼리 속도를 늦출 수 있으므로 아껴서 사용하세요.
78. WHERE 절에 하위 쿼리 대신 UNION을 사용하세요.
79. 업데이트의 경우 공유 모드를 사용하여 독점 잠금을 방지하세요.
80. MySQL을 다시 시작하기 전에 데이터가 메모리에 있고 쿼리 속도가 빠른지 확인하기 위해 데이터베이스를 워밍업하는 것을 잊지 마세요.
81. 테이블에서 모든 데이터를 삭제하려면 DROP TABLE, CREATE TABLE DELETE FROM을 사용하세요.
82. 최소화된 데이터로 필요한 데이터를 조회할 때 *를 사용하면 시간이 많이 소모됩니다.
83. 오버헤드를 줄이려면 다중 연결 대신 영구 연결을 고려하세요.
84. 서버 부하 사용을 포함한 벤치마크 쿼리는 때때로 하나의 간단한 쿼리가 다른 쿼리에 영향을 미칠 수 있습니다.
85. 서버의 부하가 증가하면 SHOW PROCESSLIST를 사용하여 느리고 문제가 있는 쿼리를 확인하세요.
86. 개발 환경에서 생성된 이미지 데이터에 대한 모든 의심스러운 쿼리를 테스트하세요.

MySQL 백업 프로세스:

87. 보조 복제 서버에서 백업합니다.
88. 데이터 종속성 및 외래 키 제약 조건의 불일치를 방지하려면 백업 중에 복제를 중지하세요.
89. MySQL을 완전히 중지하고 데이터베이스 파일에서 백업을 만듭니다.
90. MySQL 덤프를 백업용으로 사용하는 경우 바이너리 로그 파일도 백업하세요. 복제가 중단되지 않는지 확인하세요.
91. LVM 스냅샷을 신뢰하지 마세요. 그러면 향후 문제를 일으킬 수 있는 데이터 불일치가 발생할 수 있습니다.
92. 단일 테이블 복구를 더 쉽게 하려면 데이터가 다른 테이블과 격리된 경우 테이블 단위로 데이터를 내보내세요.
93. mysqldump를 사용할 때는 –opt를 사용하세요.
94. 백업하기 전에 테이블을 확인하고 최적화하세요.
95. 가져오기 속도를 높이려면 가져오는 동안 외래 키 제약 조건을 일시적으로 비활성화하세요.
96. 더 빠른 가져오기를 위해 가져오는 동안 고유성 감지가 일시적으로 비활성화됩니다.
97. 각 백업 후에 데이터베이스, 테이블 및 인덱스의 크기를 계산하여 데이터 크기 증가를 더 잘 모니터링하세요.
98. 자동화된 예약 스크립트를 통해 복제 인스턴스 오류 및 대기 시간을 모니터링합니다.
99. 정기적인 백업을 수행하세요.
100. 정기적으로 백업을 테스트하세요.
마지막 101: MySQL 모니터링 실행: Monitis가 세계 최초의 무료 온디맨드 MySQL 모니터링을 공개합니다.