쿼리문 실행 과정에 대해 이야기하는 MySQL 학습

MySQL을 심도 깊게 배우고 싶다면 매크로 아키텍처부터 시작해야 합니다. 이번 글에서는 MySQL 쿼리문을 실행하는 과정을 배워보는 것이 모두에게 도움이 되기를 바랍니다.

이 기사의 MySQL 버전은 8.0.18

Architecture Diagram

Parser

파서의 역할은 다음 작업을 수행하는 것입니다. 클라이언트:

• 문법 분석: SQL 문의 구문, 대괄호와 따옴표가 닫혀 있는지 등을 확인합니다.
• 어휘 분석: SQL 문의 키워드, 테이블 이름, 필드 이름을 노드로 분할하고, 그리고 마지막으로 구문 분석 트리를 얻습니다

전처리기

파서는 주로 문법과 어휘를 확인합니다. 그러나 문법과 어휘는 정확하지만 테이블과 필드가 존재하지 않으면 이 SQL 문은 실행할 수 없습니다. 올바르게 실행됩니다.

따라서 전처리기의 역할은 다음과 같습니다.

의미론적 구문 분석, 구문 분석 트리의 의미가 올바른지, 테이블과 필드가 존재하는지 확인하는 것입니다. 전처리 후에 새로운 구문 분석 트리가 얻어집니다.

쿼리 최적화 프로그램

쿼리 최적화 프로그램 구조

MySQL에서 SQL 문을 실행하는 방법에는 여러 가지가 있지만 결국 동일한 결과를 얻을 수 있지만 오버헤드에는

차이가 있습니다. 실행 방법은 쿼리 최적화 프로그램에 의해 결정됩니다. 예를 들면 다음과 같습니다.

테이블에서 선택할 수 있는 인덱스가 여러 개 있는데, 구체적으로 어떤 인덱스를 선택할지
• 여러 테이블에 대해 관련 쿼리를 수행할 때 어떤 테이블의 데이터가 벤치마크 테이블로 사용되는지

쿼리 최적화 도구입니다. 비용 기반 최적화 프로그램 . 작동 원리는 구문 분석 트리를 기반으로 다양한 실행 방법에 필요한 비용(비용)을 평가하고 최소 비용으로 실행 계획을 얻는 것입니다. 최종 솔루션 .

그러나 오버헤드가 가장 적은 이 실행 방법이 반드시 최적의 실행 방법은 아닙니다. 예를 들어 인덱스를 사용해야 하는데 전체 테이블 스캔을 수행합니다. 쿼리 옵티마이저에는 "최적화"라는 두 단어가 있지만 이 최적화가 만능은 아닌 경우가 많으므로 SQL 문이 합리적으로 작성되었는지 여부를 고려하는 것이 더 필요합니다.

논리적 쿼리 최적화

논리적 쿼리 최적화는 주로 일부 관계 대수를 수행하여 SQL 문을 최적화하는 역할을 담당하므로 SQL 문을 보다 효율적으로 실행할 수 있습니다.

논리적 쿼리 최적화 여러 가지 사례를 사용하여 간단히 이해할 수 있습니다

• 하위 쿼리 병합
  병합 전

  SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2)
  );

  병합 후

  SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2)
  );

  쿼리 조건을 병합하여 여러 하위 쿼리를 병합하고 여러 연결 작업을 단일 테이블 스캔 및 단일 연결로 줄입니다

• 등가 술어 무거운 쓰기
  익숙한 것처럼 퍼지 쿼리처럼 인덱스 범위 쿼리가 수행되기 전 조건 뒤에 %가 기록됩니다. 사실 이것이 쿼리 옵티마이저의 공로입니다
  사용된 조건이 모두 인덱스되어 있다고 가정하고, 다시 쓰기 전

  SELECT * FROM USERINFO WHERE name LIKE 'Abc%';

  다시 쓰기 후

  SELECT * FROM USERINFO WHERE name >= &#39;Abc&#39; AND name < &#39;Abd&#39;;

  이것이 인덱스 범위 쿼리에 대한 답입니다

• 조건부 단순화
  조건부 단순화는 단순화를 달성하기 위해 일부 방정식과 대수 관계도 사용합니다
  식에서 중복 괄호를 제거하고 구문 분석 중에 생성된 AND 및 OR 트리 수준을 줄입니다. , 예를 들어 ((a AND b) AND (c AND d))는 a AND b AND c AND d
  • 로 단순화됩니다. ((a AND b) AND (c AND d)) 简化为 a AND b AND c AND d
  • 常量传递，比如 col1 = col2 AND col2 = 3 简化为 col1 = 3 AND col2 = 3
  • 表达式计算，对于一些可直接求解的表达式会转换为最终的计算结果，比如 col1 = 1+2 简化为 col1 = 3상수 전송, 예: col1 = col2 AND col2 = 3은 col1 = 3 AND col2 = 3
  식 계산으로 단순화되며, 직접 풀 수 있는 일부 표현식의 경우 다음과 같은 최종 계산 결과로 변환됩니다. col1 = 1+2를 col1 = 3으로 단순화

물리적 쿼리 최적화

주로 물리적 쿼리 최적화로 수행 작업은 비용을 평가하는 것입니다.

물리적 쿼리 최적화는 주로 다음 문제를 해결합니다.
단일 테이블 스캔(인덱스 스캔 + 테이블 반환 또는 전체 테이블 스캔)에서 가장 비용이 적게 드는 방법은 무엇입니까?
테이블 연결인데 어떤 연결 방식을 사용하는 것이 가장 저렴할까요?

비용 평가에 대해 간략하게 소개합니다. 비용 평가는 CPU 비용과 IO 비용이라는 두 가지 차원을 기반으로 합니다비용 평가 공식N_page * a_page_IO_time + N_tuple * a_tuple_CPU_timeC_index + N_page_index * a_page_IO_time

	스캔 방법
	순차 스캔
	인덱스 스캔

🎜🎜

위 매개변수에 대한 설명은 다음과 같습니다.

• a_page_IO_time, 데이터 페이지를 로드하는 IO 시간
• N_page, 데이터 페이지 수
• N_tuple, 튜플 수(튜플은 데이터 행으로 이해됨)
• a_tuple_CPU_time, 데이터의 튜플 페이지 구문 분석에 소요된 CPU 시간
• C_index, 인덱스에 소요된 IO 시간
• N_page_index, 인덱스 페이지 수

이 문서를 참조하여 인덱스 비용을 계산할 수 있습니다. MySQL 쿼리가 이 인덱스를 사용하기로 선택한 이유는 무엇입니까? ——MySQL 8.0.22 인덱스 비용 계산 기준

실행 계획

실행 계획은 쿼리 최적화 프로그램의 산물이며 결국 실행을 위해 스토리지 엔진으로 넘겨지게 됩니다. 실행 계획은 MySQL이 이 SQL 문을 어떻게 실행할지 알 수 있도록 도와줍니다.

explain 키워드를 사용하면 SQL 문의 실행 계획을 확인할 수 있으며, 다음 정보를 얻을 수 있습니다.

• id: 중첩 쿼리에서 쿼리의 실행 순서
• possible_keys: 사용할 수 있는 인덱스 이 쿼리
• Key: 실제로 사용된 Index
• rows: 결과를 얻기 위해 검색해야 하는 대략적인 데이터 행 수
• select_type 여러 테이블 간의 연결 유형
• extra: 추가 정보, 인덱스 적용 여부, 인덱스 pushdown 등

스토리지 엔진

MySQL 서버는 데이터 저장, 추출 및 업데이트 방법에 대한 사양을 규정합니다. 이 사양은 스토리지 엔진에 따라 구현 방법이 다르므로 스토리지 엔진도 다릅니다. 고유한 기능과 특성을 제시합니다. 가장 일반적으로 사용되는 스토리지 엔진은 InnoDB와 MyISAM입니다

이 두 스토리지 엔진의 특징에 대해 간략하게 이야기하겠습니다

InnoDB:

• 외래 키 및 트랜잭션을 지원하여 데이터의 무결성과 일관성을 보장합니다
• 보다 자세한 잠금 세분성 지원 , 더 나은 잠금 제어, 더 높은 읽기 및 쓰기 효율성

MyISAM

• 은 트랜잭션을 지원하지 않고 행 잠금만 지원하며 데이터 읽기 전용 시나리오에 적합합니다.

스토리지 엔진은 현재 확장되지 않습니다. 다른 기사에서는 계속해서 비교 내용을 삽입하고 InnoDB

Summary

전에는 클라이언트 소프트웨어에서 SQL 문을 작성하는 방법만 알았습니다. 실행하고 데이터를 가져옵니다

지금까지 저는 쿼리 문이 MySQL 서버에 전달된 후 이러한 일련의 작업을 거쳐야 한다는 것을 마침내 배웠습니다. 파서는 SQL 문의 구문과 어휘가 있는지 확인합니다. 오류가 없으면 키워드를 기준으로 조각으로 분할되고 마지막으로 구문 분석 트리가 형성됩니다

• 전처리기는 SQL 문의 의미를 확인하고 SQL 문이 모호한지, 필드가 있는지 등을 확인합니다. 존재하고 새로운 파싱 트리를 형성

• 쿼리 최적화 프로그램이 가져옵니다. 이 파싱 트리에서 생성된 다양한 실행 계획을 논리적 쿼리 최적화와 물리적 쿼리 최적화를 통해 얻어 최소한의 비용으로 실행 계획을 얻습니다

• 실행 엔진은 이 실행 계획을 가져오고 스토리지 엔진 인터페이스를 호출합니다

• 실행 계획을 기반으로 하는 스토리지 엔진은 데이터 쿼리를 수행하고 운영 체제에서 파일 시스템의 일부 인터페이스를 쿼리하고 호출하여 데이터 쿼리를 완료합니다. 그리고 마지막으로 클라이언트에게 반환합니다

• [관련 권장 사항:

  mysql 비디오 튜토리얼
]

위 내용은 쿼리문 실행 과정에 대해 이야기하는 MySQL 학습의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!