MySQL 인덱스로 향상되고 설명합니다-PHP 튜토리얼-php.cn

MySQL Performance Boosting with Indexes and Explain 키 포인트

MySQL의 명령을 사용하여 쿼리 실행 계획을 분석하고 최적화하여 연결 유형 및 인덱스 사용과 같은 주요 정보를 공개하여보다 효율적인 데이터베이스 작업을 보장합니다.

쿼리 분석을 구현하여 쿼리의 실제 실행 시간을 측정하여 타겟팅 된 최적화를 수행하여 실행 시간을 줄이고 전반적인 성능을 향상시킵니다.

명령의 피드백을 기반으로 적절한 인덱스를 추가하여 EXPLAIN 조항에 사용 된 열에 중점을두고 데이터 검색 속도를 높이고 쿼리 성능을 향상시킵니다.

검색 작업과 관련된 열의 경우, 특히

연산자를 쿼리에서 사용할 때 성능을 최적화하기 위해 전체 텍스트 인덱싱을 사용하는 것을 고려하십시오.

는 와 함께 의 사용에 주목하십시오. 특히 인덱스가 효과적으로 사용되지 않는 경우 제한 결과의 성능 장점을 상쇄 할 수 있습니다.

EXPLAIN 데이터베이스 최적화는 일반적으로 애플리케이션 성능을 향상시키는 데 주요 초점과 가장 일반적인 병목 현상을 향상시킵니다. 개선이 필요한 것을 측정하고 이해하는 방법은 무엇입니까? WHERE 간단하고 효과적인 도구는 쿼리 분석입니다. 분석을 활성화하면 쿼리 실행 시간을보다 정확하게 추정 할 수 있습니다. 이것은 2 단계 프로세스입니다. 먼저 분석을 활성화하십시오.

다음 삽입 작업이 데이터베이스에 존재한다고 가정합니다 (사용자 1과 갤러리 1이 생성되었다고 가정) :

소량의 데이터는 문제를 일으키지 않지만 간단한 분석에 사용할 수 있습니다. 다음 쿼리를 고려하십시오 LIKE 많은 사진 항목이 있다면이 쿼리는 향후 문제가 될 수 있습니다.

이 쿼리의 정확한 실행 시간을 얻으려면 다음 SQL을 사용할 수 있습니다.

결과는 다음과 같습니다 ORDER BY

명령은 원래 쿼리의 시간뿐만 아니라 다른 모든 쿼리의 시간을 표시하므로 쿼리를 정확하게 분석 할 수 있습니다. show profiles; 쿼리를 개선하는 방법?

당신은 SQL 지식에 의존하여 MySQL의 명령을 향상 시키거나 의존하고 실제 정보를 기반으로 쿼리 성능을 향상시킬 수 있습니다.

> 쿼리 실행 계획, 즉 MySQL이 쿼리를 실행하는 방법을 얻는 데 사용됩니다. ,

, EXPLAIN,

및 명령문에 적합하며 Optimizer의 명령문 실행 계획에 대한 정보를 표시합니다. 공식 문서는 어떻게

EXPLAIN가 우리를 어떻게 도울 수 있는지 잘 설명합니다. SELECT DELETE 를 사용하면 인덱스를 사용하여 인덱스를 사용하여 문을 더 빨리 실행할 수 있도록 인덱스를 추가 해야하는 테이블을 확인할 수 있습니다. 를 사용하여 Optimizer가 테이블에 최상의 순서로 결합하는지 확인할 수도 있습니다. INSERT REPLACE 의 사용을 설명하기 위해 예제를 제공하려면 쿼리를 사용하여 UPDATE : 에서 사용자 이메일을 찾습니다. EXPLAIN 명령을 사용하려면 유형 쿼리 전에 추가하십시오.

결과는 다음과 같습니다 (모든 것을보기 위해 오른쪽 스크롤) :

이 결과는 처음에는 이해하기 쉽지 않습니다. 각각을 자세히 살펴 보겠습니다.
: 이것은
의 각 쿼리에 대한 순차 식별자입니다. id SELECT :
쿼리 유형. 이 필드는 여러 가지 다른 값을 취할 수 있으므로 가장 중요한 값에 중점을 둘 것입니다.
: 하위 쿼리 또는 노조가없는 간단한 쿼리 select_type SELECT :
연결의 가장 바깥 쪽 쿼리에 위치한
: SIMPLE는 중성자 쿼리의 일부입니다
: 서브 쿼리에서 첫 번째
PRIMARY select :
는 노조의 두 번째 또는 후속 진술입니다. 필드 값의 전체 목록은 여기에서 찾을 수 있습니다.
DERIVED select : 행이 참조하는 테이블. from :이 필드는 MySQL 연결이 사용하는 테이블 유형을 나타냅니다. 이것은 아마도
출력에서 가장 중요한 필드 일 것입니다. 누락 된 인덱스를 표시하거나 쿼리가 어떻게 재정의되는지 보여줄 수 있습니다. 이 필드의 가능한 값은 다음과 같습니다 (최고에서 최악의 유형으로 정렬) :
: 테이블에는 행이 0 또는 행이 있습니다. SUBQUERY select : 테이블에는 행과 일치하는 행이 하나 뿐이며 행이 색인화되었습니다. 이것은 가장 빠른 연결 유형입니다.
: 인덱스의 모든 부분은 결합에 의해 사용되며 인덱스는 또는
입니다. UNION select : 이전 테이블의 각 행 조합에 대해 인덱스 열의 모든 일치하는 행을 읽습니다. 이 유형의 조인은 일반적으로 select_type 또는 연산자를 사용하여 인덱스 된 열에서 발생합니다.

: 테이블의 전체 텍스트 색인에 가입하십시오.
: 와 동일하지만 열의 : 연결은 인덱스 목록을 사용하여 결과 세트를 생성합니다. table의

열에는 사용 된 키가 포함됩니다.

: type 서브 쿼리는 테이블에서 하나의 결과 만 반환하고 기본 키를 사용합니다. EXPLAIN

system : 전체 인덱스 트리를 스캔하여 일치하는 행을 찾으십시오.
: 전체 테이블을 스캔하여 조인의 일치 행을 찾으십시오. 이것은 최악의 결합 유형이며, 일반적으로 테이블에 적절한 인덱스가 누락되어 있습니다. const
eq_ref : MySQL이 테이블에서 행을 찾는 데 사용할 수있는 키를 보여줍니다. 이 키는 실제로 사용되거나 사용되지 않을 수 있습니다. PRIMARY_KEY UNIQUE NOT NULL : MySQL이 실제로 사용하는 인덱스를 나타냅니다. MySQL은 항상 쿼리에 사용할 수있는 최고의 키를 찾습니다. 여러 테이블에 합류 할 때 에 나열되지는 않지만 더 나은 키를 찾을 수 있습니다.
: 쿼리 최적화기에서 사용할 인덱스의 길이를 나타냅니다. ref = : 열 또는 상수를 표시하는 열 또는 상수를 표시합니다.
: 출력을 생성하기 위해 확인 된 레코드 수를 나열합니다. 이것은 매우 중요한 지표입니다. 기록이 적을수록 좋습니다. rows
에 해당하는 것은 해당 쿼리를 나타낼 수 있습니다. Extra Using filesort 출력 형식의 전체 문서는 공식 MySQL 페이지에서 찾을 수 있습니다. Using temporary 간단한 쿼리로 돌아 가기 : 유형의 연결이있는

응용 프로그램 모드로 돌아가서 모든 갤러리 이미지를 얻을 수 있습니다. 설명에 "고양이"라는 단어가 포함 된 사진 만 포함 할 수도 있습니다. 이것은 분명히 프로젝트 요구 사항에서 찾을 수있는 상황입니다. 쿼리를 봅시다 :

EXPLAIN 이 더 복잡한 상황에서는 분석을 위해 더 많은 정보를 얻어야합니다.

이것은 다음 결과를 제공합니다 (모든 셀을 보려면 오른쪽 스크롤) :

자세히 살펴보고 쿼리에서 우리가 개선 할 수있는 것을 보자.

앞에서 언급 한 바와 같이, 먼저 볼 수있는 기본 열은

열과 열에서 더 나은 값을 얻고 열의 값을 최소화하는 것입니다. type 첫 번째 쿼리의 결과는 rows이며, 이는 전혀 좋은 결과가 아닙니다. 이것은 우리가 그것을 개선 할 수 있음을 의미합니다. type 쿼리를보고,이를 해결하는 두 가지 방법이 있습니다. 첫째, rows 테이블은 사용되지 않습니다. 쿼리를 확장하여 사용자를 대상으로하거나 쿼리의 사용자 부분을 완전히 삭제해야합니다. 전반적인 성능의 복잡성과 시간 만 증가시킵니다.

그래서 이제 우리는 똑같은 결과를 얻습니다. index :

를 보자

우리가 남긴 것은

유형입니다. ALL는 아마도 최악의 연결 유형이지만 유일한 옵션 인 경우가 있습니다. 요구 사항에 따르면 모든 갤러리 이미지를 원하므로 전체 ALL 테이블을 검색해야합니다. 테이블의 모든 정보가 필요할 때 표에서 특정 정보를 찾으려고 할 때 인덱스가 훌륭하지만 도움이되지 않습니다. 이 상황에 직면 할 때 캐싱과 같은 다른 방법에 의지해야합니다. galleries 우리가

에서 작업하고 있기 때문에, 우리가 할 수있는 마지막 개선은 필드에 전체 텍스트 색인을 추가하는 것입니다. 이런 식으로

를 로 변경하고 성능을 향상시킬 수 있습니다. 전체 텍스트 인덱싱에 대한 자세한 내용은 여기를 참조하십시오. LIKE 우리는 또한 두 가지 매우 흥미로운 상황, 즉 응용 프로그램의 최신 및 관련 기능을 확인해야합니다. 이것들은 갤러리에 적용되며 우리가주의를 기울여야 할 몇 가지 극단적 인 상황을 포함합니다. description 위는 관련 갤러리입니다. LIKE match() 위의 것은 최신 갤러리입니다.

언뜻보기에,이 쿼리는

를 사용하기 때문에 매우 빠릅니다. 이것은

를 사용하는 대부분의 쿼리에서 발생합니다. 불행히도 우리와 응용 프로그램의 경우 이러한 쿼리는

및, LIMIT 우리는 두 쿼리 모두 최악의 연결 유형이 있음을 알 수 있습니다.

역사적으로, MySQL의 구현, 특히 ALL와 함께 사용될 때, 종종 MySQL 성능 문제의 원인이었습니다. 이 조합은 대규모 데이터 세트가있는 대부분의 대화식 응용 프로그램에서도 사용됩니다. 새로운 등록 된 사용자 및 인기있는 태그와 같은 기능은 종종이 조합을 사용합니다.

이것은 일반적인 문제이므로 성능 문제를 해결하기 위해 몇 가지 일반적인 솔루션을 적용해야합니다.

ORDER BY 인덱스를 사용하고 있는지 확인하십시오. 우리의 경우, 는 우리가 분류하는 필드이기 때문에 좋은 후보입니다. 이런 식으로 우리는 전체 결과 세트를 스캔하고 정렬하지 않고 및 LIMIT를 수행 할 수 있습니다.

~~주요 테이블의 열별로 정렬하십시오. 일반적으로~~

가 조인 순서의 첫 번째 테이블이 아닌 필드별로 정렬되면 인덱스를 사용할 수 없습니다.

표현별로 정렬하지 마십시오. 표현 및 함수는

created_at의 큰 값에주의를 기울이십시오. 큰 ORDER BY 값은 LIMIT가 더 많은 행을 분류하도록 강요합니다. 이것은 성능에 영향을 미칩니다.
우리가 ORDER BY와
결론 ORDER BY

우리가 보았 듯이

는 가능한 빨리 쿼리의 문제를 식별하는 데 매우 유용합니다. 응용 프로그램이 생산 중일 때만 눈에 띄는 많은 문제가 있으며 데이터베이스에 액세스하기 위해 많은 데이터 나 방문자가 많이 있습니다. 이러한 문제를 최대한 빨리 감지하기 위해 를 사용할 수 있다면 향후 성능 문제의 가능성이 훨씬 작습니다. LIMIT 우리의 응용 프로그램에는 필요한 모든 인덱스가 있으며 빠르지 만 이제는 성능 이득을 확인해야 할 때마다 항상 LIMIT 및 인덱스로 전환 할 수 있음을 알고 있습니다. ORDER BY MySQL 성능 인덱싱 (FAQ)

에 대한 FAQS MySQL 성능 인덱싱의 중요성은 무엇입니까?

MySQL 성능 색인화는 데이터베이스 성능을 최적화하는 데 중요합니다. 인덱스 된 열의 값을 기반으로 데이터 테이블의 행에 빠르게 액세스하여 데이터 검색 작업 속도를 크게 높입니다. 인덱스가 없으면 MySQL은 테이블의 모든 행을 통과하여 관련 행을 찾아야하며, 특히 대형 데이터베이스의 경우 매우 시간이 많이 걸릴 수 있습니다.

설명 명령은 MySQL 성능을 향상시키는 데 어떻게 도움이됩니까? LIMIT ORDER BY MySQL의 명령은 MySQL이 쿼리를 수행하는 방법에 대한 정보를 제공하는 강력한 도구입니다. 읽기 테이블의 순서, 수행 된 읽기 작업 유형, 선택할 수있는 인덱스 및 점검 할 추정 행 수를 보여줍니다. 이 정보는 개발자가 쿼리를 최적화하고 데이터베이스 성능을 향상시키는 데 도움이 될 수 있습니다.