mysql 문의 성능 분석 및 최적화

1. explain을 사용하여 쿼리 계획 보기

2. show processlist를 사용하여 쿼리 프로세스 보기(어떤 상태) ), 완료 명령은 다음과 같습니다 mysql -uroot -p -e 'show processlist G' |grep state: |sort|uniq -c|sort -rn 이 방법은 방법 3과 유사합니다. 방법 3은 더 유용합니다.

3. 프로그램 프로필을 사용하세요. 기본적으로 비활성화되어 있으며 프로파일링 = 1로 설정하여 활성화해야 합니다. 일부 쿼리를 실행한 후 show profile을 입력하면 이전에 실행한 문의 쿼리 시간이 매우 정확하게 표시되는 것을 확인할 수 있습니다. 그런 다음 쿼리 n에 대한 show profile을 사용하여 쿼리 문에 해당하는 쿼리 실행의 각 단계와 소요 시간을 확인합니다.

4. 느린 로그를 사용하고 타사 도구인 pt-query-digest를 사용하여 분석 보고서를 생성합니다. 이 분석 방법을 사용하는 경우 구성 파일을 변경해야 할 가능성이 매우 높으며 이는 다음 형식으로 설정할 수 있습니다. log_slow_queries = /var/log/mysql/mysql-slow.log#로그 저장 디렉터리 long_query_time = 0 //모든 쿼리 로그 캡처 -queries-not-using-indexes//인덱스를 사용하지 않아도 기록 가능

프로젝트에서 프로그램 실행 시간의 대부분이 데이터베이스 작업에 소비되는 것으로 나타났습니다. . 느린 쿼리 로그에 대한 분석 보고서를 작성하려면 pt-query-digest를 사용하십시오. (실제 프로덕션에서는 느린 쿼리 로그를 열고 닫는 것이 쉽지 않습니다. 이때 TCP 트래픽을 모니터링하거나 tcpdump를 사용하여 시뮬레이션할 수 있습니다.) 업데이트 및 삽입 작업이 전체 시간의 95%를 차지하는 것으로 나타났습니다.

그런 다음 실행된 명령문을 추가로 분석합니다.

이 업데이트 문의 각 부분에 소요되는 시간은 다음과 같습니다.

시간은 주로 쿼리 종료 상태에 소비됩니다.

Google로 이동하여 mysql 구성 파일 my.conf에 innodb_flush_log_at_trx_commit = 0을 추가하세요. 검증 결과, 문제는 성공적으로 해결되었으며 속도 개선도 매우 뚜렷했습니다(위의 변경 사항은 삽입 작업에도 영향을 미쳤습니다). 동시에 질문이 남습니다: 쿼리 종료 상태는 무엇이며, 왜 그렇게 오래 걸리는지, innodb_flush_log_at_trx_commit = 0을 추가한 후 성능이 왜 그렇게 향상됩니까?

쿼리 종료 상태는 어떤가요? mysql의 공식 문서에는 다음과 같이 설명되어 있습니다. 이 상태는 쿼리를 처리한 후 항목 해제 상태 이전에 발생합니다. 제가 이해한 바로는 명령문이 실행되었지만 아직 완료되지 않은 후속 작업이 있습니다.

그럼 아이템 해방 상태는 어떤가요? 스레드가 명령을 실행했습니다. 이 상태에서 수행된 일부 항목 해제에는 쿼리 캐시가 포함됩니다. 이 상태는 일반적으로 쿼리 캐시의 공간을 해제하는 것입니다(업데이트 작업이므로 해당). 캐시에 있는 레코드가 유효하지 않으므로 이 단계가 필요합니다).

innodb_flush_log_at_trx_commit의 기본값은 1입니다. 이때 동작은 다음과 같습니다. 각 트랜잭션 커밋에서 로그 버퍼가 로그 파일에 기록되고 디스크로 플러시 작업이 로그 파일에서 수행됩니다. 로그 버퍼의 기능: 트랜잭션이 실행이 완료된 후에만 로그(트랜잭션은 로그를 유지해야 함)를 디스크에 쓸 수 있도록 합니다. 이 시간은 주로 디스크 IO에 소비되어야 합니까?

innodb_flush_log_at_trx_commit 값을 0으로 변경한 후 동작은 다음과 같습니다. innodb_flush_log_at_trx_commit 값이 0이면 로그 버퍼가 1초에 한 번씩 로그 파일에 기록되고 해당 로그에서 디스크 플러시 작업이 수행됩니다. 파일이지만 트랜잭션 커밋에서는 아무 작업도 수행되지 않습니다. 0으로 변경한 후에는 매번 수행해야 할 작업이 이제는 1초에 한 번만 수행되어 시간이 많이 절약되는 것을 알 수 있다.

innodb_flush_log_at_trx_commit 값을 0으로 설정하면 부작용이 있습니다. 서버측 mysql 프로그램이 충돌하면 트랜잭션의 마지막 순간이 손실됩니다(로그 파일에 포함될 시간을 갖기도 전에). ). 그러나 이 애플리케이션이 거래에 대해 그렇게 엄격한 요구 사항을 가질 필요는 없다는 점을 고려하면 이는 허용됩니다.

위 내용은 mysql 문의 성능 분석 및 최적화 내용이며, 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!