MySQL의 작동 방식

MySQL 논리적 아키텍처

MySQL 논리적 아키텍처는 세 가지 계층으로 나누어집니다. 최상위 계층은 MySQL에만 국한되지 않는 클라이언트 계층으로, 연결 처리, 인증 인증, 보안 등의 기능이 모두 이 계층에서 처리됩니다.

쿼리 구문 분석, 분석, 최적화, 캐싱, 내장 기능(시간, 수학, 암호화 등)을 포함한 대부분의 MySQL 핵심 서비스는 중간 계층에 있습니다. 모든 교차 스토리지 엔진 기능도 이 계층에서 구현됩니다. : 저장 절차, 트리거, 뷰 등

하위 계층은 MySQL에서 데이터 저장 및 검색을 담당하는 스토리지 엔진입니다. 중간 서비스 계층은 API를 통해 스토리지 엔진과 통신합니다.

MySQL 쿼리 프로세스

MySQL에 요청을 보낼 때:

1. 클라이언트/서버 통신 프로토콜

MySQL 클라이언트/서버 통신 프로토콜은 " "반이중"입니다: 언제든지 서버가 클라이언트에 데이터를 보내거나 클라이언트가 서버에 데이터를 보냅니다. 이 두 작업은 동시에 발생할 수 없습니다. 한쪽 끝이 메시지 전송을 시작하면 다른 쪽 끝은 응답하기 전에 전체 메시지를 받아야 합니다. 따라서 메시지를 작은 조각으로 나누어 독립적으로 보낼 수도 없고 보낼 필요도 없습니다. 흐름.

클라이언트는 쿼리 요청을 별도의 데이터 패킷으로 서버에 보내기 때문에 쿼리문이 매우 긴 경우 max_allowed_packet 매개변수를 설정해야 합니다. 그러나 쿼리가 너무 크면 서버가 더 많은 데이터를 받아들이지 않고 예외가 발생한다는 점에 유의해야 합니다.

반대로, 서버가 사용자에게 응답하는 데이터는 일반적으로 여러 데이터 패킷으로 구성된 많은 데이터입니다. 그러나 서버가 클라이언트의 요청에 응답할 때 클라이언트는 단순히 처음 몇 개의 결과를 가져온 다음 서버에 전송을 중지하도록 요청하는 대신 반환된 전체 결과를 완전히 수락해야 합니다. 따라서 실제 개발에서는 쿼리를 최대한 단순하게 유지하고 필요한 데이터만 반환하며, 통신 시 데이터 패킷의 크기와 개수를 줄이는 것이 매우 좋은 습관입니다. 이것이 우리가 SELECT를 사용하지 않으려고 노력하는 이유이기도 합니다. * 쿼리 하나에 LIMIT 제한을 추가합니다.

2. 쿼리 캐시

쿼리 문을 구문 분석하기 전에 쿼리 캐시가 켜져 있으면 MySQL은 쿼리 문이 쿼리 캐시의 데이터에 도달하는지 확인합니다. 현재 쿼리가 쿼리 캐시에 부딪히면 사용자 권한을 한 번 확인한 후 바로 캐시에 있는 결과를 반환합니다. 이 경우 쿼리가 구문 분석되지 않고 실행 계획이 생성되지 않으며 실행되지 않습니다.

MySQL은 해시 값으로 인덱싱된 참조 테이블(HashMap과 유사한 데이터 구조)에 캐시를 저장합니다. 이 해시 값은 쿼리 자체, 현재 쿼리 중인 데이터베이스, 클라이언트 프로토콜 버전 번호 등을 통해 결과에 영향을 미칠 수 있습니다. .정보를 계산할 수 있습니다. 따라서 두 쿼리(공백, 주석) 간의 문자 차이로 인해 캐시가 누락됩니다.

쿼리에 mysql 라이브러리의 사용자 정의 함수, 저장 함수, 사용자 변수, 임시 테이블 또는 시스템 테이블이 포함되어 있으면 쿼리 결과가 캐시되지 않습니다. 예를 들어, NOW() 또는 CURRENT_DATE() 함수는 쿼리 시간이 다르기 때문에 다른 쿼리 결과를 반환합니다. 또 다른 예는 CURRENT_USER 또는 CONNECION_ID()가 포함된 쿼리 문이 이러한 쿼리 결과를 캐시하기 때문에 다른 결과를 반환하는 것입니다. 그것은 말이 되지 않습니다.

3. 캐시 무효화

MySQL의 쿼리 캐시 시스템은 쿼리에 포함된 각 테이블을 추적합니다. 이러한 테이블(데이터 또는 구조)이 변경되면 이 테이블과 관련된 모든 캐시된 데이터가 유효하지 않게 됩니다. 이 때문에 MySQL은 쓰기 작업 중에 해당 테이블에 대한 모든 캐시를 무효화해야 합니다. 쿼리 캐시가 매우 크거나 조각화되어 있는 경우 이 작업으로 인해 시스템이 많이 소모될 수 있으며 시스템이 잠시 정지될 수도 있습니다. 또한 시스템에서 쿼리 캐시의 추가 소비는 쓰기 작업뿐만 아니라 읽기 작업에도 적용됩니다.

1. 이 SQL 문이 캐시에 도달하지 않더라도 시작하기 전에 모든 쿼리 문을 확인해야 합니다.

2. 쿼리 결과를 캐시할 수 있으면 실행이 완료된 후 결과가 캐시에 저장되므로 시스템 소모도 늘어나게 됩니다.

이를 바탕으로 쿼리 캐싱이 시스템 성능을 향상시키지 않는다는 점을 알아야 합니다. 어떤 경우에도 캐싱 및 무효화는 추가 소비를 가져옵니다. 캐시로 인한 리소스 절약이 자체적으로 소비되는 리소스보다 클 경우에만 시스템 성능이 향상됩니다. 그러나 캐시를 켜면 성능이 향상될 수 있는지 평가하는 것은 매우 어렵습니다. 시스템에 성능 문제가 있는 경우 쿼리 캐시를 켜고 데이터베이스 디자인을 일부 최적화해 볼 수 있습니다. 예:

1 하나의 큰 테이블을 여러 개의 작은 테이블로 교체하고 과도하게 사용하지 않도록 주의하세요.

2. 일괄 삽입으로 교체 루프 단일 삽입

3. 캐시 공간 크기를 합리적으로 제어합니다. 일반적으로 크기를 수십 메가바이트로 설정하는 것이 더 적합합니다.

4. 특정 쿼리문을 캐시해야 하는지 여부를 제어하세요

특히 쓰기 집약적인 애플리케이션의 경우 쿼리 캐시를 쉽게 설정하지 마세요. 어쩔 수 없다면 query_cache_type을 DEMAND로 설정할 수 있습니다. 이때 SQL_CACH를 추가한 쿼리만 캐시되고 다른 쿼리는 캐시되지 않습니다.

4. 구문 구문 분석 및 전처리

MySQL은 키워드를 통해 SQL 문을 구문 분석하고 해당 구문 분석 트리를 생성합니다. 이 프로세스 파서는 주로 문법 규칙을 통해 검증하고 파싱합니다. 예를 들어 SQL에서 잘못된 키워드가 사용되었는지, 키워드의 순서가 올바른지 등이다. 전처리에서는 구문 분석 트리가 MySQL 규칙에 따라 적합한지 추가로 확인합니다. 예를 들어 쿼리할 데이터 테이블과 데이터 컬럼이 존재하는지 등을 확인한다.

5. 쿼리 최적화

구문 트리가 합법적인 것으로 간주되고 최적화 프로그램이 이를 쿼리 계획으로 변환한 후, 대부분의 경우, 쿼리는 여러 가지 방법으로 실행될 수 있으며 최종적으로 해당 결과를 반환하는 것이 최적화 프로그램의 역할입니다.

MySQL의 쿼리 최적화 프로그램은 최적의 실행 계획을 생성하기 위해 많은 최적화 전략을 사용하는 매우 복잡한 구성 요소입니다.

1 테이블 재정의 연결 순서(여러 테이블이 있는 경우) 쿼리와 연결되어 있으면 반드시 SQL에 지정된 순서를 따르지는 않지만 연결 순서를 지정하는 몇 가지 기술이 있습니다.

2. MIN() 및 MAX() 함수를 최적화합니다. 특정 열의 최소값, 해당 열에 인덱스가 있는 경우 B+Tree 인덱스의 가장 왼쪽 끝만 찾으면 되고, 그렇지 않으면 최대값을 찾을 수 있습니다.)

3 종료합니다. query early (Limit 사용시, search 충분한 수의 결과 집합에 도달하면 즉시 쿼리가 종료됩니다)

4. Optimize sorting (이전 버전의 MySQL에서는 두 가지 전송 정렬이 사용됩니다. 즉, 정렬해야 하는 행 포인터와 필드를 먼저 메모리에서 읽습니다. 정렬한 다음 정렬 결과에 따라 데이터 행을 읽습니다. 새 버전에서는 단일 전송 정렬, 즉 모든 데이터를 읽습니다. 행을 한 번에 정렬한 다음 주어진 열에 따라 정렬)

#🎜 🎜#

6. 쿼리 실행 엔진

파싱 및 최적화 단계를 완료한 후, MySQL은 해당 실행 계획을 생성하고 쿼리 실행 엔진은 실행 계획에서 제공한 지침에 따라 명령을 점진적으로 실행합니다. 실행 결과가 생성됩니다. 전체 실행 프로세스에 걸친 대부분의 작업은 스토리지 엔진에 의해 구현된 인터페이스를 호출하여 완료됩니다. 이러한 인터페이스를 핸들러 API라고 합니다. 쿼리 프로세스의 각 테이블은 핸들러 인스턴스로 표시됩니다. 실제로 MySQL은 쿼리 최적화 단계에서 각 테이블에 대한 핸들러 인스턴스를 생성합니다. 이 인스턴스의 인터페이스를 기반으로 모든 열 이름을 포함하여 테이블 관련 정보를 얻을 수 있습니다. , 테이블의 인덱스 통계 등. 스토리지 엔진 인터페이스는 매우 풍부한 기능을 제공하지만 하단에는 타워 블록과 같은 인터페이스가 수십 개만 있어 쿼리 작업의 대부분을 완료합니다.

7. 결과를 클라이언트에 반환

쿼리 실행의 마지막 단계는 결과를 클라이언트에 반환하는 것입니다. 쿼리할 수 있는 데이터가 없더라도 MySQL은 쿼리의 영향을 받은 행 수, 실행 시간 등 쿼리와 관련된 정보를 반환합니다.

쿼리 캐시가 켜져 있고 쿼리를 캐시할 수 있는 경우 MySQL은 결과도 캐시에 저장합니다.

결과 세트를 클라이언트에 반환하는 것은 증분 및 점진적 반환 프로세스입니다. MySQL이 첫 번째 결과를 생성할 때 클라이언트에 결과 집합을 점차적으로 반환하기 시작할 수도 있습니다. 이런 방식으로 서버는 너무 많은 결과를 저장하고 너무 많은 메모리를 소비할 필요가 없으며, 클라이언트도 가능한 한 빨리 반환된 결과를 얻을 수 있습니다. 결과 집합의 각 행은 ①에서 설명한 통신 프로토콜을 충족하는 데이터 패킷으로 전송된 후 TCP 프로토콜을 통해 전송됩니다. 전송 과정에서 MySQL 데이터 패킷이 캐시된 후 전송될 수 있습니다. 배치.

MySQL 전체 쿼리 실행 과정

1 클라이언트가 MySQL 서버에 쿼리 요청을 보냅니다

2 서버는 먼저 쿼리 캐시를 확인하고 캐시에 도달하면 즉시 캐시에 저장된 결과를 반환합니다. 그렇지 않으면 다음 레벨 세그먼트

3을 입력합니다. 서버는 SQL 구문 분석 및 전처리를 수행한 다음 최적화 프로그램은 해당 실행 계획

4을 생성합니다. 쿼리를 실행하기 위한 실행 계획 엔진 API

위 내용은 MySQL의 작동 방식의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!