일반 프로그래머가 마스터해야 할 SQL 최적화 기술

직장에서든 면접에서든 기본적으로 explain을 사용하여 SQL의 실행 계획을 본 다음 실행 계획에 따라 SQL을 최적화하는 등 일부 SQL 최적화 기술을 익혀야 합니다.

설명의 사용과 관련 분야의 분석은 이제 기본적으로 프로그래머의 표준입니다.

아니요, 꼭 읽어주세요.

1. MySQL 실행 계획이란? 실행 계획을 더 잘 이해하려면 MySQL의 기본 구조와 쿼리의 기본 원리에 대한 간단한 이해가 필요합니다.

MySQL 자체의 기능적 아키텍처는 애플리케이션 계층, 논리 계층, 물리 계층의 세 부분으로 구분됩니다. MySQL뿐만 아니라 대부분의 다른 데이터베이스 제품도 이 아키텍처에 따라 구분됩니다.

애플리케이션 계층은 주로 클라이언트와의 상호 작용, 링크 설정, 링크 상태 기억, 데이터 반환 및 요청 응답을 담당합니다.
논리 계층은 주로 쿼리 처리, 트랜잭션 관리 및 기타 데이터베이스 기능 처리를 담당하며 쿼리를 예로 듭니다.
처음 쿼리 SQL을 수신한 후 데이터베이스는 이를 처리하기 위해 즉시 스레드를 할당합니다. 첫 번째 단계에서 쿼리 프로세서는 SQL 쿼리를 최적화한 후 실행 계획을 생성하여 전달합니다. 실행을 위한 계획 실행자.

계획 실행자는 데이터를 운영하기 위해 하위 트랜잭션 관리자와 스토리지 관리자에 액세스해야 합니다. 각각의 업무 부서가 다릅니다. 마지막으로 물리 계층의 파일을 호출하여 쿼리 구조 정보를 얻고 최종 결과를 얻습니다. 애플리케이션 계층에 응답됩니다.

물리적 계층, 실제 물리적 디스크에 저장되는 파일에는 주로 페니 데이터 파일과 로그 파일이 포함됩니다.
위 설명을 통해 실행 계획을 생성하는 것은 SQL 실행을 위한 필수 단계입니다. 실행 계획을 보면 SQL의 성능을 직관적으로 확인할 수 있습니다. 이를 확인하고 성과분석의 기초로 활용하세요.

2. 실행 계획을 분석하는 방법MySQL은 SQL의 실행 계획을 시각적으로 볼 수 있는 explain 키워드를 제공합니다.

MySQL이 어떻게 인덱스를 사용하여 선택 문을 처리하고 테이블을 조인하는지 보여줍니다. 이는 더 나은 인덱스를 선택하고 더 최적화된 쿼리 문을 작성하는 데 도움이 될 수 있습니다.

다음으로 explain을 사용하여 다음과 같이 쿼리를 작성합니다.

mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)

쿼리 구조에는 12개의 열이 있습니다. 각 열의 의미를 이해하는 것이 실행 계획을 이해하는 데 중요합니다. 다음은 테이블 형식으로 설명됩니다. .

열 이름	Description
id	SELECT 식별자. SELECT의 쿼리 시퀀스 번호입니다.
select_type	SELECT 유형, 다음 중 하나일 수 있음: SIMPLE: 단순 SELECT(UNION 또는 하위 쿼리를 사용하지 않음) PRIMARY: 가장 바깥쪽 SELECT UNION: UNION의 두 번째 또는 다음 SELECT 문DEPENDENT UNION: 외부 쿼리에 따라 UNION의 두 번째 또는 다음 SELECT 문UNION RESULT: UNIONSUBQUERY의 결과: 하위 쿼리의 첫 번째 SELECTDEPENDENT SUBQUERY: 외부 쿼리의 첫 번째 SELECT 하위 쿼리, 외부 쿼리에 따라 다름DERIVED: 파생 테이블의 SELECT(FROM 절의 하위 쿼리)
table	출력 행에서 참조하는 테이블
partitions	을 표시합니다. 쿼리가 분할된 테이블을 기반으로 하는 경우 쿼리가 액세스할 파티션입니다.
type	가입 유형. 다양한 조인 유형이 아래에 제공되며 최고부터 최악까지 정렬됩니다. system: 테이블에는 행이 하나만 있습니다(=시스템 테이블). 이는 const 조인 유형의 특별한 경우입니다. const: 테이블에는 최대 하나의 일치하는 행이 있으며 쿼리 시작 시 읽혀집니다. 행이 하나뿐이므로 이 행의 열 값은 나머지 최적화 프로그램에서 상수로 처리될 수 있습니다. const 테이블은 한 번만 읽기 때문에 빠릅니다. eq_ref: 이전 테이블의 각 행 조합에 대해 이 테이블에서 한 행을 읽습니다. 이것은 아마도 const 유형 외에 가장 좋은 조인 유형일 것입니다. ref: 이전 테이블의 각 행 조합에 대해 일치하는 인덱스 값이 있는 모든 행을 이 테이블에서 읽습니다. ref_or_null: 이 조인 유형은 ref와 비슷하지만 NULL 값이 포함된 행을 구체적으로 검색하기 위해 MySQL을 추가합니다. index_merge: 이 조인 유형은 인덱스 병합 최적화 방법이 사용됨을 나타냅니다. unique_subquery: 이 유형은 IN 하위 쿼리의 참조를 다음 형식으로 대체합니다. value IN (SELECT Primary_key FROM Single_table WHERE some_expr) Unique_subquery는 하위 쿼리를 완전히 대체할 수 있고 더 효율적인 인덱스 조회 함수입니다. index_subquery: 이 조인 유형은 Unique_subquery와 유사합니다. IN 하위 쿼리는 대체될 수 있지만 다음 형식의 하위 쿼리에 있는 고유하지 않은 인덱스에만 해당됩니다. value IN (SELECT key_column FROM Single_table WHERE some_expr) range: 인덱스를 사용하여 행을 선택하여 지정된 범위의 행만 검색합니다( 추천, 최악은 이수준). index: 이 조인 유형은 인덱스 트리만 스캔한다는 점을 제외하면 ALL과 동일합니다. 인덱스 파일은 일반적으로 데이터 파일보다 작기 때문에 일반적으로 ALL보다 빠릅니다. ALL: 이전 테이블의 각 행 조합에 대해 전체 테이블 스캔을 수행하여 쿼리를 최적화해야 함을 나타냅니다. 일반적으로 말하면 쿼리가 최소한 범위 수준에 도달하고 바람직하게는 ref에 도달하는지 확인하는 것이 필요합니다. 위 시스템이 최고이고, 내림차순으로 보면 ALL이 최악입니다.
possible_keys	는 MySQL이 테이블에서 행을 찾는 데 사용할 수 있는 인덱스를 나타냅니다.
key	은 다음과 같은 키를 보여줍니다. MySQL은 실제로 (인덱스)를 사용하기로 결정합니다. 인덱스를 선택하지 않으면 키는 NULL입니다.
key_len	MySQL이 사용하기로 결정한 키 길이를 표시합니다. 키가 NULL이면 길이도 NULL입니다. 길이가 짧을수록 정확성을 잃지 않고 더 좋습니다.
ref	테이블에서 행을 선택하기 위해 키와 함께 사용되는 열 또는 상수를 표시합니다.
rows	MySQL이 쿼리를 실행할 때 확인해야 한다고 생각하는 행 수를 표시합니다. 여러 행에 걸쳐 데이터를 곱하면 처리할 행 수를 추정할 수 있습니다.
filtered	은 조건에 따라 필터링된 행 수의 추정 백분율을 표시합니다.
Extra	이 열에는 MySQL이 쿼리를 어떻게 해결했는지에 대한 세부 정보가 포함되어 있습니다. Distinct: ​​​​MySQL이 일치하는 첫 번째 행을 찾은 후 현재 행 조합에 대한 추가 행 검색을 중지합니다. 최적화된 테이블 선택MySQL은 테이블이나 인덱스를 전혀 순회하지 않고 데이터를 반환하며, 이는 더 이상 최적화할 수 없는 지점까지 최적화되었음을 나타냅니다. 존재하지 않음: MySQL은 LEFT JOIN을 사용하여 쿼리를 최적화하고 찾을 수 있음 LEFT JOIN 표준과 일치하는 1개 행 이후에는 이전 행 조합에 대해 테이블 ​​내에서 더 이상 행이 확인되지 않습니다. 각 레코드에 대한 범위 확인(인덱스 맵: #): MySQL은 사용할 수 있는 좋은 인덱스를 찾지 못했지만 이전 테이블의 열 값을 알 수 있는 경우 일부 인덱스가 사용될 수 있음을 발견했습니다. filesort 사용: MySQL은 정렬된 순서로 행을 검색하는 방법을 파악하기 위해 추가 패스가 필요하며 이는 쿼리를 최적화해야 함을 나타냅니다. 인덱스 사용: 추가 검색 없이 인덱스 트리의 정보만 사용하여 실제 행을 읽어 테이블에서 열 정보를 검색합니다. 임시 사용: 쿼리를 해결하려면 MySQL은 결과를 수용할 임시 테이블을 생성해야 하며, 이는 쿼리를 최적화해야 함을 의미합니다. where:WHERE 절을 사용하여 다음 테이블과 일치하는 행을 제한하거나 고객에게 전송됩니다. sort_union(...) 사용, Union(...) 사용, intersect(...) 사용: 이 함수는 index_merge 조인 유형에 대한 인덱스 스캔을 병합하는 방법을 보여줍니다. 그룹별로 인덱스 사용: 테이블에 액세스하는 인덱스 사용 방법과 유사하게, 그룹별로 인덱스를 사용한다는 것은 MySQL이 추가 작업 없이 GROUP BY 또는 DISTINCT 쿼리의 모든 열을 쿼리하는 데 사용할 수 있는 인덱스를 찾았음을 의미합니다. 하드 디스크 검색 실제 테이블에 액세스합니다.

위 표에 따르면 실행 계획 분석에 좋은 도움을 줄 수 있습니다.

Note: 면접에 대비하려면 다 외우는 것이 가장 좋습니다. 다 외울 수 없다면 123도 말할 수 있어야 하고, 그다음에는 별로 기억나지 않는다고 말할 수 있어야 합니다. Optimize SQL과 관련된 문서를 읽어볼 수 있습니다.

위 내용은 일반 프로그래머가 마스터해야 할 SQL 최적화 기술의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!