MySQL 실행 프로세스와 쿼리 캐시에 대한 자세한 소개

이 기사는 MySQL 실행 프로세스 및 쿼리 캐싱에 대해 자세히 소개합니다. 필요한 친구가 참고할 수 있기를 바랍니다.

MySQL은 쿼리 프로세스를 실행합니다.
MySQL에 요청을 보낼 때 MySQL은 정확히 무엇을 수행합니까?

1. 클라이언트가 서버에 쿼리를 보냅니다
2. 캐시가 비활성화된 경우 캐시에 저장된 결과가 즉시 반환됩니다. 그렇지 않으면 다음 단계로 이동합니다.
3. 서버가 SQL 구문 분석 및 전처리를 수행한 후 최적화 프로그램이 해당 실행 계획을 생성합니다.
4.MySQL은 스토리지 엔진의 API를 호출하여 옵티마이저가 생성한 실행 계획을 기반으로 쿼리를 실행합니다.
5. 결과를 클라이언트에 반환합니다.

mysql은 주로 서버 레이어와 스토리지 레이어의 두 부분으로 구성됩니다.
서버 계층에는 주로 커넥터, 쿼리 캐시, 분석기, 최적화 프로그램 및 실행 프로그램이 포함됩니다.
스토리지 계층은 주로 데이터를 저장하고 쿼리하는 데 사용됩니다. 일반적으로 사용되는 스토리지 엔진에는 InnoDB 및 MyISAM이 있습니다.

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

MySQL 클라이언트와 서버 간의 통신 프로토콜은 "반이중"입니다. "이는 언제든지 서버가 클라이언트에 데이터를 보내거나 클라이언트가 서버에 데이터를 보내고 있음을 의미합니다. 이 두 가지 작업은 동시에 발생할 수 없습니다. 따라서 우리는 메시지를 작은 조각으로 잘라서 독립적으로 보낼 수도 없고 보낼 필요도 없습니다.

장점과 단점:
이 프로토콜을 사용하면 MySQL 통신이 간단하고 빨라지지만 여러 곳에서 MySQL이 제한됩니다.
명백한 제한은 흐름 제어가 없다는 것입니다. 한쪽 끝이 메시지 전송을 시작하면 다른 쪽 끝은 응답하기 전에 전체 메시지를 받아야 합니다. 이는 마치 회수 게임과 같습니다. 어느 순간에든 단 한 사람만이 공을 제어할 수 있고, 공을 제어하는 ​​사람만이 공을 뒤로 던질 수 있습니다(메시지 보내기).

(2).Connector

MySQL 클라이언트는 서버와 연결을 설정하고 현재 연결된 사용자의 권한을 얻습니다

(3) 쿼리 캐시
쿼리 문을 구문 분석하기 전에 쿼리 캐시가 켜져 있는 경우, MySQL은 이 캐시를 검사하여 쿼리 캐시의 데이터가 적중되었는지 확인합니다. 이 검사는 대소문자 구분 해시 조회를 통해 구현됩니다.
쿼리와 캐시에 있는 쿼리의 차이가 1바이트만 있어도 캐시된 결과와 일치하지 않습니다. 이 경우 쿼리
는 다음 처리 단계로 들어갑니다.

현재 쿼리가 쿼리 캐시에 도달하는 경우 MySQL은 쿼리 결과를 반환하기 전에 사용자 권한을 한 번 확인합니다. 현재 쿼리가 액세스해야 하는 테이블 정보가 이미 쿼리 캐시에 저장되어 있으므로 쿼리 SQL 문을 구문 분석할 필요가 없습니다. 권한에 문제가 없으면 MySQL은 다른 모든 단계를 건너뛰고 캐시에서 직접 결과를 가져와 클라이언트에 반환합니다. 이 경우 쿼리가 구문 분석되지 않고 실행 계획도 생성되지 않으며 실행되지 않습니다.

ps: 이 캐시는 삭제하기가 매우 쉽기 때문에 mysql8 이후에는 쿼리 캐시 기능이 없다는 점에 유의하세요. 그리고 적중률도 상대적으로 낮습니다.

(3).Analyzer

캐시를 찾을 수 없으므로 sql 문을 실행하기 전에 먼저 sql 문을 구문 분석해야 합니다.

분석기는 주로 SQL 문의 구문 및 의미 분석을 수행하고, 단어의 철자가 틀렸는지 확인하고, 쿼리할 테이블이나 필드가 존재하는지 확인합니다.

(4) 쿼리 최적화

쿼리 수명 주기의 다음 단계 SQL을 실행 계획으로 변환하고, MySQL은 이 실행 계획에 따라 스토리지 엔진과 상호 작용합니다. 여기에는 SQL 구문 분석, 전처리, SQ 실행 계획 최적화 등 여러 하위 단계가 포함됩니다.

이 프로세스에서 오류(예: 구문 오류)가 발생하면 쿼리가 종료될 수 있습니다.

2. 쿼리 캐시 정보

(1)

MySQL의 캐시 적중 여부를 판단하는 방법은 매우 간단합니다. 캐시는 참조 테이블에 저장되고 해시 값으로 참조됩니다.

MySOL 쿼리 캐시는 쿼리에서 반환된 전체 결과를 저장합니다. 쿼리가 캐시에 도달하면 MySQL은 구문 분석, 최적화 및 실행 단계를 건너뛰고 즉시 결과를 반환합니다.

쿼리 캐시 시스템은 쿼리와 관련된 각 테이블을 추적합니다. 이러한 테이블이 변경되면 이 테이블과 관련된 저장소는 데이터가 유효하지 않습니다.

이 메커니즘은 데이터 테이블이 변경될 때 쿼리 결과가 변경되지 않을 가능성이 높기 때문에 상대적으로 비효율적인 것처럼 보이지만 이 간단한 구현 비용은 매우 적고 이는 매우 바쁜 시스템에 매우 중요합니다.

쿼리 캐싱 시스템은 애플리케이션에 완전히 투명합니다. 애플리케이션은 MySQL이 쿼리 캐시 또는 실제 실행에서 결과를 반환하는지 여부를 신경 쓸 필요가 없습니다. 실제로 이 두 가지 방법의 결과는 완전히 동일합니다. 즉, 캐시를 쿼리하기 위해 구문을 사용할 필요가 없습니다. MYSQL 쿼리 버퍼링이 켜져 있는지 여부는 애플리케이션에 투명합니다.

(2) 캐시 적중 결정

캐시 적중 여부를 결정할 때 MySQL은 쿼리 문을 구문 분석, "정규화" 또는 매개 변수화하지 않고 SQL 문과 클라이언트가 보낸 기타 원본 정보를 문자로 직접 사용합니다. 공백, 주석 등의 콘텐츠로 인해 캐시 오류가 발생합니다.

쿼리 문에 불확실한 데이터가 있으면 캐시되지 않습니다. 예를 들어 NOW() 또는 CURRENT_DATE()
함수가 포함된 쿼리는 캐시되지 않습니다.

오해:
다음과 같은 말을 자주 듣습니다. 쿼리에 정의되지 않은 함수가 포함되어 있으면 MySQL은 쿼리 캐시를 확인하지 않습니다." 이 진술은 올바르지 않습니다.

쿼리 캐시를 확인할 때 SQL 문이 구문 분석되지 않았기 때문에 MySQL은 쿼리 문에 그러한 함수가 포함되어 있는지 여부를 알 수 없습니다.

쿼리 캐시를 확인하기 전에 MySQL은 한 가지 작업만 수행합니다. 즉, 대소문자를 구분하지 않는 검사를 통해 SQL 문이 5EL로 시작하는지 확인하는 것입니다.

정확한 진술은 다음과 같아야 합니다. "쿼리 문에 불확실한 함수가 포함되어 있으면 쿼리 캐시에서 캐시된 결과를 찾는 것이 불가능합니다."

참고:
MySQL의 쿼리 캐시는 많은 경우 쿼리 성능을 향상시킬 수 있습니다. 사용할 때 특별한 주의가 필요한 몇 가지 문제가 있습니다. 우선, 쿼리 캐시를 켜면 읽기 및 쓰기 작업 모두에 추가 소비가 발생합니다.

1. 읽기 쿼리는 시작하기 전에 먼저 캐시에 적중하는지 확인해야 합니다.
2. 실행이 완료된 후, MySQL 쿼리 캐시에 해당 쿼리가 존재하지 않는 것으로 확인되면 결과가 쿼리 캐시에 저장되므로 추가적인 시스템 소모가 발생하게 됩니다.
3. 이는 테이블에 데이터를 쓸 때 MySQL이 해당 테이블의 모든 캐시를 무효화해야 하기 때문에 쓰기 작업에도 영향을 미칩니다. 쿼리 캐시가 매우 크거나 조각난 경우 이 작업으로 인해 시스템 소모가 커질 수 있습니다(쿼리 캐시에 많은 메모리가 설정된 경우)

쿼리 캐시가 많은 양의 메모리를 사용하는 경우 캐시 무효화 작업이 A가 될 수 있습니다. 병목 현상이 매우 심각한 문제입니다
캐시에 쿼리 결과가 많이 저장되어 있는 경우 캐시 무효화 작업 중에 전체 시스템이 잠시 정지될 수 있습니다

이 작업은 전역 잠금 작업으로 보호되기 때문에 수행해야 하는 모든 사용자는 이 작업 모든 쿼리는 이 잠금을 기다려야 하며, 캐시 적중 여부를 감지하든 캐시 무효화를 감지하든 이 전역 잠금을 기다려야 합니다.

(3) 쿼리 캐시는 어떤 상황에서 작동할 수 있나요? 이론적으로 쿼리 캐시를 켜거나 끌 때 시스템 효율성을 관찰하여 쿼리를 활성화해야 하는지 여부를 결정할 수 있습니다.

많은 리소스를 소비하는 상대 쿼리는 일반적으로 캐싱에 매우 적합합니다.

예를 들어 COUNT() 등과 같은 일부 요약 계산 쿼리는 구체적입니다. 일반적으로 쿼리 캐싱은 복잡한 SELECT 문에 사용할 수 있습니다. 예를 들어 다중 테이블 JOIN 이후에는 정렬 및 페이징이 필요합니다. 이러한 유형의 쿼리는 실행될 때마다 많은 비용을 소비하지만 반환되는 결과 집합은 매우 작습니다. , 이는 쿼리 캐싱에 매우 적합합니다.

그러나 SELECT에 비해 관련 테이블에 대한 UPDATE, DELETE 및 INSERT 작업이 거의 없다는 점에 유의해야 합니다.

쿼리 캐시가 효과적인지 판단하는 직접적인 데이터는 적중률입니다. 전체 쿼리에서 쿼리 캐시가 반환한 결과의 비율입니다

그러나 캐시 적중률은 판단하기 어려운 값입니다. 좋은 적중률이란 무엇입니까? 구체적인 상황, 구체적인 분석.

쿼리 캐싱으로 인한 효율성 향상이 쿼리 캐싱으로 인한 추가 소비보다 큰 만큼 적중률이 30%라도 시스템 성능에 큰 도움이 됩니다. 또한 어떤 쿼리가 캐시되는지도 중요합니다. 예를 들어 캐시된 쿼리 자체는 많은 비용을 소비하므로 캐시 적중률이 매우 낮더라도 캐시 누락은 다음과 같은 이점이 있을 수 있습니다. 이유:

1 쿼리에 불확실한 함수(예: CURREN_DATE)가 포함되어 있거나 쿼리 결과가 너무 커서 캐시할 수 없기 때문에 쿼리 문을 캐시할 수 없습니다. 이로 인해 캐시되지 않은 캐시 상태 값이 증가합니다.

2.MySQL은 이 쿼리를 처리한 적이 없으므로 결과가 캐시되지 않았습니다.

3. 또 다른 상황은 쿼리 결과가 이전에 캐시되었음에도 불구하고 쿼리 캐시의 메모리가 모두 사용되었기 때문에 MySQL이 일부 캐시를 "제거"해야 하거나 데이터 테이블이 수정되어 캐시가 유효하지 않게 되는 것입니다.

서버에 캐시 누락이 많이 발생했지만 실제로 대부분의 쿼리가 캐시된 경우 다음 상황이 발생한 것입니다.

1 쿼리 캐시가 워밍업을 완료하지 않았습니다. 즉, MySQL은 모든 쿼리 결과를 캐시할 기회가 없었습니다.

2. 쿼리 문이 이전에 실행된 적이 없습니다. 애플리케이션이 쿼리 문을 반복적으로 실행하지 않으면 워밍업이 완료된 후에도 여전히 많은 캐시 누락이 발생합니다.

3. 캐시 무효화 작업이 너무 많습니다.

(4) 쿼리 캐시 구성 및 유지 관리 방법

query_cache_type

쿼리 캐시를 설정할지 여부입니다. 0FN 또는 DEMAND로 설정할 수 있습니다. DEMAND는 쿼리 문에서 SQL_CACHE를 명확하게 나타내는 문만 쿼리 캐시에 배치된다는 의미입니다. 이 변수는 세션 수준 또는 전역 수준일 수 있습니다.

query_cache_size

쿼리 캐시에서 사용하는 총 메모리 공간(바이트)입니다. 이 값은 1024의 정수 배수여야 합니다. 그렇지 않으면 MySQL이 할당하는 실제 데이터는 사용자가 지정한 것과 약간 다를 것입니다.

query_cahce_min_res_unit

쿼리 캐시에 메모리 블록을 할당할 때의 최소 단위입니다.

query_chache_limit

MySQL이 캐시할 수 있는 최대 쿼리 결과입니다. 쿼리 결과가 이 값보다 크면 캐시되지 않습니다. 쿼리 캐시는 데이터가 생성될 때 데이터를 캐시하려고 시도하기 때문에 모든 결과가 반환되어야만 쿼리 결과가 제한을 초과하는지 여부를 알 수 있습니다. 제한을 초과하면 MySQL은 상태 값 Cache_not_cached를 늘리고 삭제합니다. 이러한 상황이 많다는 것을 미리 알고 있다면 쿼리 문에

(5) 대안을 추가하는 것이 좋습니다

MySQL 쿼리 캐시 작업의 원칙은 다음과 같습니다. 쿼리는 실행되지 않지만 쿼리는 여전히 서버로 전송되어야 하며 서버는 여전히 약간의 작업을 수행해야 합니다. 일부 쿼리에 대해 서버와 통신할 필요가 없으면 어떻게 될까요? 이때 클라이언트의 캐시는 MySQL 서버에 대한 부담을 상당 부분 공유하는 데 도움이 될 수 있습니다

요약:

완전히 동일한 쿼리가 발생하는 경우 반복적으로 실행되는 경우 쿼리 캐싱은 데이터베이스에서 결과를 다시 실행하지 않고도 즉시 결과를 반환할 수 있습니다. 우리의 경험에 따르면 동시성이 높은 환경에서 캐시를 쿼리하면 시스템 성능이 저하되거나 심지어 정지될 수도 있습니다.

쿼리 캐시를 꼭 사용해야 한다면 메모리를 너무 많이 설정하지 마시고, 이점이 확인된 경우에만 사용하세요.

그렇다면 쿼리 캐시를 사용해야 하는지 판단하는 방법은 Percona 서버를 사용하는 것이 좋습니다. 더 자세한 로그를 관찰하고 간단한 계산을 수행하는 것이 좋습니다. 캐시 적중률(항상 유용한 것은 아님), "NSERTS 대 SELECT 비율"(이 매개변수도 직관적이지 않음) 또는 "쓰기 적중률"(이 참조가 더 의미 있음)을 볼 수도 있습니다.

쿼리 캐시는 애플리케이션에 완전히 투명하고 추가 코딩이 필요하지 않은 매우 편리한 캐시입니다. 그러나 더 높은 캐싱 효율성을 원한다면 캐시 또는 기타 유사한 솔루션을 사용하는 것이 좋습니다.

【관련 추천:

MySQL 동영상 튜토리얼

】

위 내용은 MySQL 실행 프로세스와 쿼리 캐시에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!