MySQL을 최적화하는 방법 : 인덱스, 느린 쿼리, 구성

MySQL은 세계에서 가장 인기있는 관계형 데이터베이스로 남아 있지만 비효율적으로 사용하는 것이 가장 쉬운 데이터베이스이기도합니다. 많은 사람들이 더 이상의 연구없이 기본 설정을 사용합니다. 이 기사는 이전에 도입 된 MySQL 최적화 기술 중 일부를 검토하고 최신 개선 사항과 결합합니다.

코어 포인트

서버 리소스를 더 잘 활용하고 데이터베이스 성능을 향상시키기 위해 , 및 와 같은 주요 매개 변수를 조정하여 MySQL 구성을 최적화하십시오.

인덱스를 효율적으로 사용하여 쿼리 프로세싱 속도를 높이십시오.
• MySQL에 대한 Percona Toolkit과 같은 도구를 사용하여 데이터베이스 효율성을 최적화하기 위해 중복 또는 사용되지 않은 인덱스와 같은 문제를 자동으로 식별하고 해결합니다. innodb_buffer_pool_size MySQL의 Slow Query Logs 및 innodb_log_file_size와 같은 도구를 사용하여 느린 쿼리를 모니터링하고 분석하여 병목 현상을 감지하고 쿼리 성능을 최적화합니다. innodb_flush_method MySQL 튜너 및 기타 성능 모니터링 도구를 정기적으로 실행하여 데이터베이스 작업에 대한 통찰력을 수집하고 실제 사용 패턴을 기반으로 구성을 미세 조정하십시오.
• 구성 최적화
MySQL에 대한 최초이자 가장 간과 된 성능 향상은 구성을 조정하는 것입니다. 버전 5.7 (현재 버전)은 이전 버전보다 더 나은 기본값을 가지지 만 개선을 계속할 수 있습니다.
• 우리는 당신이 개선 된 홈스테드와 같은 Linux 기반 호스트 또는 Vagrant Virtual Machine을 사용하고 있다고 가정하므로 구성 파일은
에 있습니다. 설치 프로그램이 보조 구성 파일을로드 할 수 있으므로 파일이 많지 않으면
• 파일 일 수 있습니다. pt-query-digest 구성 편집 명령 줄 사용에 익숙해야합니다. 전에 노출되지 않았더라도 좋은 시간입니다.
• Vagrant Virtual 시스템에서 로컬로 편집하면 명령을 사용하여 기본 파일 시스템의 공유 폴더에 파일을 복사 한 다음 일반 텍스트 편집기를 사용하여 편집 한 다음 다시 복사 할 수 있습니다. 완료 후 원래 장소. 그렇지 않으면 와 같은 간단한 텍스트 편집기를 사용하고 명령을 실행하십시오.
참고 : 구성 파일의 실제 위치와 일치하도록 위의 경로를 수정 - 실제로

에있을 수 있습니다. 수동 조정 다음 수동 조정은 즉시 이루어져야합니다. 이 팁을 기반으로

섹션의 구성 파일에 다음을 추가하십시오.

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

• - 버퍼 풀은 메모리의 데이터와 인덱스를 캐시하는 데 사용되는 저장 영역입니다. 메모리에서 자주 액세스하는 데이터를 유지하는 데 사용되며 전용 서버 또는 가상 서버를 실행할 때 응용 프로그램 의이 부분에 가장 많은 RAM을 할당하는 것이 합리적이며 데이터베이스는 종종 병목 현상이됩니다. 그래서 우리는 모든 RAM의 50-70%를 할당합니다. 버퍼 풀 크기 조정에 대한 안내서는 MySQL 문서에 제공됩니다. innodb_buffer_pool_size 로그 파일 크기는 여기에 잘 설명되어 있지만 간단히 말해서 지우기 전에 로그에 저장해야 할 데이터의 양입니다. 이 경우 로그는 사용 할 수있는 오류 로그 또는 로그가 아니라 MySQL의 경우 쓰기가 백그라운드에서 발생하지만 여전히 전경 성능에 영향을 미치기 때문에 체크 포인트 시간을 나타냅니다. 더 큰 로그 파일은 더 적은 작은 체크 포인트가 생성되지만 충돌의 경우 복구 시간이 길어지면서 성능이 향상됩니다 (데이터베이스에 더 많이 다시 작성해야 함).
• 여기에는 로그 파일에 어떤 일이 발생하는지를 나타냅니다. 1의 경우 각 트랜잭션 후 로그가 디스크로 플러시되므로 가장 안전한 설정이 있습니다. 0 또는 2를 사용하면 산성 성능이 적지 만 성능이 향상됩니다. 이 경우 차이는 세트 1의 안정성 이점을 초과하기에 충분하지 않습니다.
• - 새로 고침 작업을 완료하려면 이중 버퍼링을 피하려면 innodb_flush_log_at_trx_commit로 설정하십시오. I/O 시스템이 매우 제대로 작동하지 않는 한 항상 수행해야합니다. DigitalOcean 방울과 같은 대부분의 관리 서버에서는 SSD가 있으므로 I/O 시스템의 성능이 높아집니다.
• Percona의 또 다른 도구가있어 나머지 문제를 자동으로 찾는 데 도움이됩니다. 위의 수동 조정없이 실행하면 다른 3 개는 사용자 기본 설정과 응용 프로그램 환경에 따라 4 개의 수정 사항 중 1 개만 수동으로 식별 할 수 있습니다. innodb_flush_method O_DIRECT
변수 검사관

우분투에 변수 검사관을 설치하려면 :

다른 시스템의 경우 지침을 따르십시오.

그런 다음 다음 명령으로 툴킷을 실행하십시오.

당신은 다음과 비슷한 출력을 볼 수 있습니다 : 이들 중 어느 것도 주요 문제가 아니며 수정할 필요가 없습니다. 우리가 추가 할 수있는 유일한 것은 복제 및 스냅 샷을위한 이진 로깅입니다.

참고 : 최신 버전에서는 Binlog 크기가 1G로 기본값을 받고 PT는 알 수 없습니다.

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

이진 로그의 크기를 결정하도록 설정하십시오. 이 로그는 트랜잭션 및 쿼리를 기록하고 체크 포인트를 만듭니다. 트랜잭션이 최대 값보다 크면 디스크에 저장 될 때 로그가 최대보다 클 수 있습니다.
• max_binlog_size 옵션을 사용하면 바이너리 로깅이 완전히 가능합니다. 그것 없이는 스냅 샷이나 복사가 없습니다. 이것은 디스크 공간에 많은 압력을 가할 수 있습니다. 서버 ID는 바이너리 로깅에 활성화 될 때 필요한 옵션이므로 로그는 (복제를 위해) 출신의 서버를 알고 있으며 형식은 로그에 쓰는 방법 일뿐입니다.
• 보시다시피, 새로운 MySQL은 합리적인 기본값을 가지고있어 거의 즉시 생산에 들어가게합니다. 물론 각 앱은 다르고 추가 사용자 정의 조정이 있습니다. log_bin mysql 튜너 튜너는 더 긴 간격으로 데이터베이스를 모니터링하고 (라이브 응용 프로그램에서 일주일에 한 번 주변을 실행) 로그에서 보는 내용에 따라 변경 사항을 제안합니다.
간단히 다운로드하여 설치하십시오

로 실행하면 데이터베이스의 관리자 사용자 이름과 비밀번호를 요청하고 빠른 스캔을 출력합니다. 예를 들어, 여기에 내 innodb 섹션이 있습니다 :

다시,이 도구는 서버가 실행 된 후 일주일에 한 번 실행해야한다는 점에 유의해야합니다. 구성 값을 변경하고 서버를 다시 시작한 후에는 일주일 동안 실행해야합니다. 이 작업을 수행하기 위해 Cron 작업을 설정하고 정기적으로 결과를 보내는 것이 가장 좋습니다.

구성을 변경할 때마다 MySQL 서버를 다시 시작하십시오 : .

인덱스

다음, 많은 아마추어 데이터베이스 관리자의 주요 진통 점인 인덱싱에 중점을 봅시다! 특히 ORM에 뛰어 들었던 사람들은 실제로 원래 SQL을 만지지 않았습니다.

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

참고 : 키와 인덱스라는 용어는 상호 교환 적으로 사용할 수 있습니다. ./mysqltuner.pl 당신은 MySQL 색인을 책의 색인과 비교할 수 있습니다.이 책은 원하는 주제가 포함 된 올바른 페이지를 쉽게 찾을 수 있습니다. 색인이 없으면 주제가 포함 된 페이지를 검색하려면 전체 책을 읽어야합니다.

상상할 수 있듯이 인덱스로 검색하는 것이 각 페이지를 가로 지르는 것보다 훨씬 빠릅니다. 따라서 일반적으로 데이터베이스에 인덱스를 추가하면 선택 쿼리 속도를 높일 수 있습니다. 그러나 인덱스도 생성 및 저장해야합니다. 따라서 업데이트 및 삽입 쿼리는 느려지고 더 많은 디스크 공간을 차지합니다. 일반적으로 테이블을 올바르게 색인하면 업데이트 및 인서트의 차이가 표시되지 않으므로 올바른 위치에 인덱스를 추가하는 것이 좋습니다.

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

단지 몇 줄만 포함 된 테이블은 실제로 인덱싱의 이점이 없습니다. 상상할 수 있듯이 5 페이지를 검색하는 것은 먼저 색인을 작성하고 페이지 번호를 얻은 다음 특정 페이지를 열는 것보다 느리지 않습니다.

그렇다면 추가 할 인덱스와 어떤 유형의 인덱스가 존재하는지 어떻게 알 수 있습니까?

고유/기본 키 인덱스 기본 키 인덱스는 데이터 색인이며 데이터를 다루는 기본 방법입니다. 사용자 계정의 경우 사용자 ID 또는 사용자 이름 또는 기본 이메일 일 수 있습니다. 기본 키 인덱스는 고유합니다. 유일한 인덱스는 데이터 세트에서 반복 할 수없는 색인입니다. 예를 들어 사용자가 특정 사용자 이름을 선택하면 다른 사람이 사용할 수 없습니다. 사용자 이름 열에 "고유 한"색인을 추가하면이 문제가 해결됩니다. 다른 사람이 기존 사용자 이름으로 행을 삽입하려고하면 MySQL이 오류를보고합니다.

> 기본 키/인덱스는 일반적으로 테이블 생성에서 정의되며 유일한 인덱스는 테이블을 변경하여 정의됩니다.

기본 키와 고유 키는 모두 하나 이상의 열에서 만들 수 있습니다. 예를 들어, 정의 할 국가 당 하나의 사용자 이름 만 있는지 확인하려면 다음과 같은 두 열에서 고유 한 인덱스를 만들 수 있습니다.

고유 인덱스는 자주 액세스하는 열에 추가됩니다. 따라서 사용자 계정을 자주 요청하고 데이터베이스에 많은 사용자 계정이있는 경우 사용 사례가 좋습니다.

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

일반 색인 일반 색인은 검색을 단순화합니다. 특정 열이나 열 조합에 대한 데이터를 빠르게 찾아야하지만 해당 데이터가 고유 할 필요는 없습니다.

위의 작업은 국가 별 사용자 이름을 검색하는 속도를 높입니다.

인덱스는 또한 분류 및 그룹화 속도를 향상시키는 데 도움이됩니다.

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

전체 텍스트 색인 전체 텍스트 색인은 전체 텍스트 검색에 사용됩니다. InnoDB 및 MyISAM 스토리지 엔진 만 전체 텍스트 인덱싱을 지원하며 Char, Varchar 및 텍스트 열만 지원됩니다.

이 인덱스는 수행해야 할 모든 텍스트 검색에 매우 유용합니다. 전체 텍스트 인덱싱은 텍스트 본문에서 단어를 찾는 데 능숙합니다. 앱에서 게시물, 댓글, 설명, 주석 등을 검색하는 경우가 종종 있으면 이러한 컨텐츠에서 이러한 색인을 사용하십시오.

하향 색인 는 특별한 유형이 아니라 변화입니다. 버전 8.0부터 MySQL은 하강하는 인덱싱을 지원하므로 인덱스를 내림차순으로 저장할 수 있습니다. 이것은 마지막 추가 데이터를 먼저 가져와야하는 큰 테이블이나 우선 순위 항목이있을 때 편리합니다. 항상 내림차순으로 정렬 할 수 있지만 이로 인해 성능이 약간 떨어집니다. 이것은 더 빠른 속도입니다.

데이터베이스에 로그를 처리 할 때 DESC를 인덱스에 적용하는 것을 고려하고, 백로 주문으로로드 된 게시물 및 댓글 및 유사한 컨텐츠.

<:> 보조 도구 : 설명

최적화 쿼리를 볼 때 설명 도구가 귀중합니다. 간단한 쿼리 전에 설명을 추가하면 매우 깊은 방식으로 처리하고 사용중인 인덱스를 분석하고 히트 및 미스의 비율을 보여줍니다. 원하는 결과를 얻기 위해 처리 해야하는 행 수를 알 수 있습니다.

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

확장 된 다음을 사용하여 더 확장 할 수 있습니다

이 훌륭한 세부 기사를 읽어 사용하는 방법을보고 발견을 적용하십시오.

<:> 보조 도구 : Percona는 중복 인덱스를 감지하는 데 사용됩니다 이전에 설치된 Percona Toolkit은 또한 타사 CMS를 사용할 때 유용한 중복 인덱스를 감지하거나 필요한 것보다 예기치 않게 더 많은 인덱스가 추가되는지 확인하는 도구를 제공합니다. 예를 들어, 테이블에 설치된 기본 WordPress에는 중복 인덱스가 있습니다.

마지막 줄에 표시된 것처럼 는 중복 인덱스를 제거하는 방법에 대한 제안을 제공합니다.

<:> 보조 도구 : Percona는 사용하지 않은 인덱스를 감지하는 데 사용됩니다 Percona는 사용하지 않은 인덱스를 감지 할 수도 있습니다. 느린 쿼리를 기록하는 경우 (아래 병목 현상 섹션 참조) 도구를 실행할 수 있으며 해당 레코드의 쿼리가 쿼리와 관련된 테이블에서 인덱스를 사용하고 있는지 확인합니다.

wp_posts 이 도구의 자세한 사용은 여기를 참조하십시오.

병목 현상

<code>innodb_buffer_pool_size = 1G # (在此处调整值，总 RAM 的 50%-70%)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # 可以更改为 2 或 0
innodb_flush_method = O_DIRECT</code>

이 섹션에서는 데이터베이스에서 병목 현상을 감지하고 모니터링하는 방법을 설명합니다.

위의 구성은 구성에 추가되어야합니다. 실행 시간이 1 초 이상이고 인덱스를 사용하지 않은 쿼리를 모니터링합니다.

이 로그가 일부 데이터가 있으면, 위에서 언급 한 도구 또는

도구를 사용하여 인덱스 사용을 분석 할 수 있습니다.이 결과는 다음과 같습니다.

이 로그를 수동으로 분석하려면이 작업도 수행 할 수 있지만 먼저 로그를보다 "분석 된"형식으로 내보내야합니다. 이것은 다음과 같이 할 수 있습니다 :

다른 매개 변수는 데이터를 추가로 필터링하고 중요한 컨텐츠 만 내보내도록 할 수 있습니다. 예 : 평균 실행 시간으로 정렬 된 상위 10 개의 쿼리.

<code class="language-bash">wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit</code>

다른 매개 변수에 대한 문서를 참조하십시오.

결론

이 포괄적 인 MySQL 최적화 기사에서는 MySQL을 더 빨리 실행하는 다양한 방법을 조사합니다. 우리는 구성 최적화를 처리하고 인덱스를 완료했으며 병목 현상을 제거했습니다. 그러나 이것은 대부분의 이론적입니다. 실제 사용 사례가 실제 응용 프로그램에 이러한 기술을 적용하려면 다가오는 Performance Boost Project에주의를 기울이십시오!

우리는 기술과 속임수를 놓쳤습니까? 제발 알려주세요!

MySQL 인덱싱 및 느린 쿼리 최적화 FAQ (FAQ)

쿼리 최적화에서 MySQL 인덱싱의 중요성은 무엇입니까?

<code class="language-bash">pt-variable-advisor h=localhost,u=homestead,p=secret</code>

MySQL 인덱스는 데이터 검색 속도를 크게 높일 수 있기 때문에 쿼리 최적화에 중요합니다. 이 책의 인덱스와 유사하게 작동하여 데이터베이스가 테이블의 모든 행을 스캔하지 않고 데이터를 찾고 검색 할 수 있습니다. 이로 인해 특히 대형 데이터베이스에서 쿼리 실행이 더 빠릅니다. 그러나 인덱스는 읽기 속도를 높이지만 데이터를 삽입하거나 업데이트 할 때 인덱스를 업데이트해야하므로 쓰기 속도를 늦출 수 있습니다.

MySQL에서 느린 쿼리를 식별하는 방법은 무엇입니까?

MySQL은 Slow Query Log라는 유용한 도구를 제공합니다. 이 도구는 지정된 시간 이상으로 실행 된 모든 SQL 쿼리에 대한 정보를 기록합니다. MySQL 구성 파일에서 활성화하고 쿼리가 느린 쿼리로 간주되기 전에 쿼리가 취해야 할 초로

를 설정할 수 있습니다.

다른 유형의 MySQL 인덱스는 무엇입니까? long_query_time MySQL은 B-Tree, HASH, R-Tree 및 Full-Text Index를 포함한 여러 유형의 인덱스를 지원합니다. B- 트리는 기본 인덱스 유형이며 다양한 쿼리에 적합합니다. 해시 인덱스는 동일한 비교에 사용되며 이러한 쿼리의 경우 B- 트리보다 빠릅니다. R-Tree Index는 공간 데이터 유형에 사용되며 전체 텍스트 인덱스는 전체 텍스트 검색에 사용됩니다.

MySQL 구성을 최적화하는 방법은 무엇입니까?

MySQL 구성 최적화에는 성능을 위해 다양한 서버 변수를 조정하는 것이 포함됩니다. 여기에는 버퍼 풀 크기 조정, 로그 파일 크기, 쿼리 캐시 크기 등이 포함됩니다. 서버의 성능을 정기적으로 모니터링하고 필요에 따라 이러한 변수를 조정하는 것이 중요합니다.

MySQL 쿼리 및 인덱스를 최적화하는 데 도움이 될 수있는 도구는 무엇입니까?

MySQL 쿼리 및 인덱스 최적화에 사용할 수있는 몇 가지 도구가 있습니다. 이러한 도구에는 MySQL의 내장 설명 설명이 포함되어 있으며 MySQL이 쿼리를 수행하는 방법에 대한 정보와 Percona Toolkit 및 MySQL Workbench와 같은 타사 도구가 포함됩니다.

설명 설명은 쿼리 최적화에 어떻게 도움이됩니까?

MySQL의 설명 설명은 MySQL이 쿼리를 수행하는 방법에 대한 정보를 제공합니다. 여기에는 액세스 된 테이블에 대한 정보, 테이블에 액세스하는 순서, 사용 된 특정 인덱스 및 읽는 행 수에 대한 정보가 포함됩니다. 이 정보는 잠재적 성능 문제를 식별하고 인덱스 최적화를 안내하는 데 도움이 될 수 있습니다.

MySQL의 쓰기 작업에 인덱스가 어떤 영향을 미칩니 까?

인덱스는 데이터 검색 속도를 높이면 읽기 작업을 크게 향상 시키지만 쓰기 작업 속도를 늦출 수 있습니다. 데이터가 삽입되거나 업데이트 될 때마다 해당 인덱스를 업데이트해야하기 때문입니다. 따라서 인덱스를 만들 때 읽기와 쓰기 작업 간의 균형을 맞추는 것이 중요합니다.

MySQL에서 조인 작업에 인덱스 최적화를 사용하는 방법은 무엇입니까?

인덱스는 MySQL에서 조인 작업의 성능을 크게 향상시킬 수 있습니다. 결합 조건에 사용 된 열에 인덱스를 생성함으로써 MySQL은 연결된 테이블에서 일치하는 행을 빠르게 찾을 수 있습니다. 이로 인해 전체 텍스트 스캔의 필요성이 줄어들고 쿼리 실행이 더 빠릅니다.

MySQL 성능 최적화에서 쿼리 캐시의 역할은 무엇입니까?

MySQL의 쿼리 캐시는 쿼리 자체뿐만 아니라 선택 쿼리의 결과를 저장합니다. 동일한 쿼리가 수신되면 MySQL은 쿼리를 다시 실행하는 대신 캐시에서 결과를 검색 할 수 있습니다. 이는 특히 복잡한 쿼리 또는 자주 실행되는 쿼리의 경우 성능을 크게 향상시킬 수 있습니다.

MySQL 서버의 성능을 모니터링하는 방법은 무엇입니까?

MySQL은 서버 성능을 모니터링하기위한 몇 가지 도구를 제공합니다. 이러한 도구에는 성능 패턴 (세부 성능 메트릭 제공) 및 정보 패턴 (데이터베이스 메타 데이터 제공)이 포함됩니다. 또한 Show Status 명령을 사용하여 서버의 실행 상태에 대한 정보를 얻을 수 있습니다.

위 내용은 MySQL을 최적화하는 방법 : 인덱스, 느린 쿼리, 구성의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!