MySQL 쿼리 성능 최적화 세부정보

고성능 데이터베이스의 경우: 라이브러리 테이블 구조 최적화, 인덱스 최적화 및 쿼리 최적화가 함께 진행되어야 합니다

1. 쿼리 속도가 느린 이유는 무엇인가요?

쿼리는 실제로 일련의 하위 작업으로 구성됩니다. 쿼리 최적화는 실제로 일부 하위 작업을 제거하거나 실행되는 하위 작업 수를 줄이는 것을 의미합니다.

2. 느린 쿼리 기본 : 데이터 접근 최적화

(1) 불필요한 데이터를 데이터베이스에 요청하는지 여부

1) 불필요한 데이터 쿼리:
예를 들어 select를 통해 많은 수의 결과를 쿼리하고, 처음 N 행을 얻은 후 결과 집합을 닫습니다. 실제로 MySQL은 모든 결과를 쿼리합니다. . 설정하면 클라이언트가 데이터의 일부를 수신한 다음 나머지 데이터를 삭제합니다. 따라서 제한 키워드 제한을 사용하여 이전 n개 레코드만 쿼리하면 됩니다. 와일드카드

*

대신 특정 필드 이름을 사용해야 합니다. 3) 항상 모든 열
을 제거하고 select *와 같은 문 쓰기를 금지합니다.

(2) MySQL이 추가 레코드를 스캔했는지 여부

쿼리가 필요한 데이터만 반환하는지 확인한 후(즉, 쿼리의 특정 필드에 와일드카드를 사용하지 않음) 맞춤형 쿼리 * )

다음으로 주목해야 할 것은 반환된 결과가 너무 많은 데이터를 스캔했는지 여부입니다. MySQL의 세 가지 가장 간단한 지표는 다음과 같습니다. (1) 응답 시간

(2) 스캔된 행 수

(3) 반환된 행 수입니다.

응답 시간

응답 시간: 서비스 시간(실제 쿼리 시간)과 대기열 시간(차단 대기 시간)을 포함합니다.

스캔된 행 수 및 반환된 행 수
쿼리를 분석할 때 쿼리로 스캔된 행 수를 확인하는 것은 매우 유용합니다. 쿼리가 효율적인지 여부.

검색된 행 수 및 액세스 유형
MySQL에는 전체 테이블 스캔, 인덱스 스캔, 범위 스캔, 고유 인덱스 쿼리, 지속적인 참조 등

여기서 인덱스를 추가하는 역할이 나옵니다. 인덱스를 사용하면 MySQL은 스캔되는 행 수를 최소화하면서 가장 효율적인 방법으로 레코드를 찾을 수 있습니다.
3. 쿼리를 재구성하는 방법

실제로 필요한 결과를 얻기 위한 보다 최적의 방법을 찾는 것이 목적입니다.

(1) 하나의 복잡한 쿼리 또는 여러 개의 단순 쿼리

SQL을 작성할 때 자주 고려해야 하는 질문은 복잡한 쿼리를 여러 개의 단순 쿼리로 나누어야 하는지 여부입니다.

MySQL의 경우 연결 및 연결 해제는 매우 가볍고 작은 쿼리 결과를 반환하는 데 매우 효율적입니다. 쿼리는 가능한 한 적게 하는 것이 좋지만 작업 부하가 크게 줄어드는지 측정한 후 큰 쿼리를 작은 쿼리로 나누어야 하는 경우가 있습니다.
(2) 분할 쿼리

분할과 정복의 아이디어. 때로는 많은 데이터가 오랫동안 잠기는 것을 방지하기 위해 대규모 쿼리를 조각으로 나누고, 부분적으로 실행하고, 단계 사이에 지연을 설정해야 할 때도 있습니다.

예를 들어 데이터를 삭제할 때 삭제해야 할 데이터를 한꺼번에 삭제하면 트랜잭션에 오랜 시간이 걸릴 수 있습니다. 하지만 대용량 삭제를 여러 개로 분할하여 분할할 수 있습니다. 조건부 제한을 통해 실행을 삭제하면 효율성이 향상됩니다.
(3) 관련 쿼리 분해

많은 고성능 애플리케이션은 관련 쿼리를 분할합니다. 예를 들어

mysql>select * from tag    
left join tag_post on tag_post.tag_id=tag.id    
left join post on tag_post.post_id = post.idwhere tag.tag='mysql';

는

mysql>select * from tag where tag='mysql';mysql>select * from tag_post where tag_id=1234;
mysql>select * from post where post.id in (123,345,456,8933);

이렇게 분해되는 이유는 무엇인가요? (1)
캐시

를 더 효율적으로 만듭니다. (예를 들어 위에서 쿼리한 태그가 캐시된 경우 애플리케이션은 첫 번째 쿼리를 건너뛸 수 있습니다.)

( 2) 중단 후 쿼리 아래에서 단일 쿼리를 실행하면 잠금 경합을 줄일 수 있습니다.

(3) 어떤 경우에는 효율성이 더 높아집니다. 예를 들어 위의 분해 후 키워드 쿼리를 사용하는 것이 더 효율적입니다.

4. 쿼리 실행의 기본

먼저 쿼리 실행 경로의 개략도를 살펴보겠습니다.

단계는 다음과 같습니다.
(1) 클라이언트가 서버에 쿼리를 보냅니다.

(2) 서버는 먼저 쿼리 캐시를 확인하고 캐시에 도달하면 즉시 저장된 결과를 반환합니다. 캐시에 저장되고, 그렇지 않으면 다음 단계로 이동합니다.

(3) 서버는 SQL을 파싱하고 전처리한 후 옵티마이저가 해당 실행 계획을 생성합니다.

(4) MySQL은 스토리지 엔진의 API를 호출하여 옵티마이저가 생성한 실행 계획에 따라 쿼리를 실행합니다.

(5) 결과를 클라이언트에 반환합니다.

(1) MySQL 클라이언트/서버 통신 프로토콜

통신 프로토콜이 내부적으로 어떻게 구현되는지 이해할 필요는 없고, 통신 프로토콜이 어떻게 구현되는지만 이해하면 됩니다. 프로토콜이 작동합니다.

MySQL의 클라이언트와 서버 통신 프로토콜은 반이중 방식입니다. 즉, 한 쪽에서만 동시에 다른 쪽에게 데이터를 보낼 수 있습니다.

(2) 쿼리 캐시

SQL 문을 구문 분석하기 전에 캐시가 켜져 있으면 MySQL은 쿼리가 SQL 문에 있는 데이터에 도달하는지 확인하는 것을 우선적으로 고려합니다. 쿼리 캐시. 캐시에 적중되면 캐시에서 직접 결과 세트를 가져와 클라이언트에 반환합니다. 캐시에 적중되지 않으면 다음 단계로 진입합니다.

(3) 쿼리 최적화

이 부분에서 가장 중요한 것은 쿼리 문을 다양한 방법으로 실행할 수 있으며 모두 반환된다는 것입니다. 결국 동일한 결과로 옵티마이저의 역할은 가장 효율적인 실행 계획을 찾는 것입니다.

다음은 MySQL 쿼리 최적화 프로그램이 자동으로 처리할 수 있는 최적화 유형입니다.
(1) 관련 테이블의 순서 재정의: 데이터 테이블의 관련 순서가 쿼리에 지정된 순서와 항상 일치하지는 않습니다. 이는 최적화 프로그램과 관련이 있습니다.

(2) 외부 조인을 내부 조인으로 변환:

(3) 등가 변환 규칙 사용: 일부 비교를 줄이거나 일부 ID를 제거할 수 있습니다. 예를 들어 (5=5 및 a>5)는 (a>5)로 다시 작성됩니다.

(4) COUNT(), MIN() 및 MAX() 함수 최적화: 인덱스와 열이 비어 있도록 허용되는지 여부는 B를 사용하여 최소값 찾기와 같은 표현식 유형을 최적화하는 데 도움이 될 수 있습니다. -트리 구조적 특징, B-Tree의 가장 왼쪽 레코드만 쿼리하면 끝입니다. max() 함수를 찾는 경우에도 마찬가지입니다. 그러나 COUNT(*) 함수의 경우 MyISAM 저장 유형은 테이블의 총 레코드 행 수를 구체적으로 저장하기 위해 변수 를 유지합니다.

(5) Covered Index Scan: 인덱스의 열에 모든 쿼리에 사용해야 하는 열이 포함되어 있는 경우 MySQL은 해당 데이터 행을 쿼리하지 않고 인덱스를 직접 사용하여 필요한 데이터를 반환할 수 있습니다.

(6) 하위 쿼리 최적화

(8) 쿼리 조기 종료: MySQL은 쿼리 요구 사항이 충족되었다고 판단되면 항상 쿼리를 즉시 종료할 수 있습니다. 예를 들어 키워드를 제한합니다.

(9) OR 대신 IN 목록 비교: MySQL은 먼저 IN 문의 데이터를 정렬한 다음 이진 검색을 사용하여 목록의 데이터가 요구 사항을 충족하는지 확인합니다. . 이것은 O(logn) 복잡도 연산입니다. OR로 동일하게 변환하면 O(n) 시간 복잡도가 됩니다.

(4) 정렬 최적화

어쨌든 정렬은 비용이 많이 드는 작업이므로 빅데이터 정렬은 피해야 합니다. 따라서 정렬을 위해서는 반드시 인덱스 컬럼을 사용해야 합니다. 인덱스를 사용하여 정렬 결과를 생성할 수 없는 경우에는 반드시 테이블 쿼리 레코드 가 반환되는 상황이 발생합니다. 크기가 커서 파일 정렬이 사용됩니다.

위 내용은 MySQL 쿼리 성능 최적화 세부정보의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!