MySQL에서 EXPLAIN을 사용하여 쿼리를 이해하고 속도를 높이는 방법

홈 » Python » MySQL에서 EXPLAIN을 사용하여 쿼리를 이해하고 속도를 높이는 방법

MySQL을 오랫동안 사용하다 보면 언젠가는 하루를 망치는 느린 쿼리를 만나게 될 겁니다. 완료되지 않는 보고서, 부하로 인해 시간 초과되는 API 엔드포인트, 또는 갑자기 로드하는 데 20초가 걸리는 대시보드 등이 있을 수 있습니다. 이런 경우 가장 먼저 사용해야 할 디버깅 도구는 다음과 같습니다. EXPLAIN 명령 계열.

MySQL의 EXPLAIN과 EXPLAIN ANALYZE는 최적화 프로그램이 쿼리를 계획하고 실제로 실행하는 방식을 보여줍니다. 잘 활용하면 어떤 테이블이 스캔되는지, 어떤 인덱스가 사용되는지(또는 무시되는지), 조인이 어떻게 수행되는지, 얼마나 많은 행이 검사되는지, 그리고 실제로 시간이 어디에 소모되는지 파악할 수 있습니다. 이 가이드에서는 이러한 지표의 사용 방법, 출력값을 읽는 방법, 그리고 이러한 통찰력을 구체적인 성능 향상으로 연결하는 방법을 쉽게 설명합니다.

MySQL에서 EXPLAIN이 하는 일(그리고 언제 사용해야 하는가)

MySQL에서는 EXPLAIN 키워드는 명령문을 정상적으로 실행하는 대신 해당 명령문에 대한 계획된 실행 전략을 보여주는 진단 도구입니다. 당신의 장소 EXPLAIN 쿼리 앞에 를 넣으면 MySQL은 요청된 행을 가져올 방법을 설명하는 작은 표로 응답합니다.

EXPLAIN을 다음과 함께 사용할 수 있습니다. SELECT, INSERT, UPDATE, DELETE REPLACE SELECT뿐만 아니라 문장도 가능합니다. 예 :

예:

쿼리 예: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

MySQL은 직원 데이터를 반환하는 대신, 해당 명령문을 실행하는 방법에 대한 행별 설명을 반환합니다. 어떤 테이블을 읽었는지, 어떤 인덱스를 선택했는지, MySQL이 몇 개의 행을 검사할 것으로 예상하는지, 그리고 임시 테이블이나 파일 정렬이 필요한지 등의 추가 참고 사항을 볼 수 있습니다.

쿼리가 예상보다 느리다고 의심되는 경우나 인덱스를 설계하고 MySQL이 실제로 인덱스를 사용할지 확인하려는 경우 EXPLAIN을 사용하세요. 여러 개의 하위 쿼리와 조인이 있는 복잡한 레거시 SQL을 상속받고 최적화 프로그램이 수행하는 작업을 역방향 엔지니어링해야 하는 경우에도 매우 유용합니다.

EXPLAIN의 변형: EXTENDED, PARTITIONS, ANALYZE 및 형식

기본 EXPLAIN은 단지 시작점일 뿐입니다. MySQL은 더 많은 통찰력을 제공하는 여러 가지 확장 기능과 출력 형식을 제공합니다. 이러한 옵션을 이해하면 각 상황에 맞는 적절한 수준의 세부 정보를 선택하는 데 도움이 됩니다.

EXPLAIN EXTENDED 특히 더 많은 최적화 정보를 추가합니다. filtered 열과 다시 작성된 쿼리 텍스트. 실행 후 EXPLAIN EXTENDED ... 당신은 실행할 수 있습니다 SHOW WARNINGS; 최적화 프로그램이 내부적으로 쿼리를 어떻게 다시 작성했는지 확인하는 것은 최적화 결정을 이해하는 데 매우 유용합니다.

EXPLAIN PARTITIONS 쿼리가 파티션된 테이블의 어떤 파티션에 액세스할지 표시합니다. The partitions 열에는 관련 파티션이 나열되어 있어 파티션 정리가 실제로 진행되고 있는지, 실수로 모든 파티션을 건드리고 있지 않은지 확인하는 데 도움이 됩니다.

출력 형식도 유연합니다. CLI에서는 표 형식, 탭으로 구분된 형식, 세로 형식 또는 JSON 형식으로 출력을 얻을 수 있으며 MySQL Workbench와 같은 도구에서는 시각적 계획을 얻을 수 있습니다. JSON 출력은 비용 추정과 중첩된 계획 구조를 포함하기 때문에 자동화 및 심층 분석에 특히 유용하지만 일반 EXPLAIN ANALYZE MySQL은 현재 원시 JSON 출력을 지원하지 않습니다.

MySQL Workbench와 같은 시각적 도구를 사용하면 EXPLAIN을 그래픽 트리로 렌더링할 수 있으며, 이는 복잡한 조인과 하위 쿼리를 이해하기가 더 쉽습니다. Percona Toolkit, EverSQL 또는 Releem과 같은 외부 도구는 EXPLAIN 출력이나 느린 쿼리 로그를 사용하여 어떤 명령문을 먼저 최적화할지 우선순위를 정하는 데 도움이 됩니다.

EXPLAIN 열(테이블 형식) 이해

JSON 형식 없이 클래식 EXPLAIN을 실행하면 MySQL은 해당 명령문에 포함된 테이블이나 하위 쿼리당 한 행을 반환합니다. 이러한 행의 순서는 중요합니다. 이는 실행 중에 테이블에 액세스하는 순서를 보여줍니다.

다음은 여러분이 보게 될 주요 열과 그 열이 알려주는 내용입니다.

• id: 쿼리의 각 부분에 대한 순차적 식별자입니다. 단일 단순 SELECT에는 일반적으로 다음이 있습니다. id = 1여러 개의 ID는 하위 쿼리, 파생 테이블 또는 UNION 부분을 나타냅니다. 일반적으로 숫자가 클수록 작은 숫자보다 먼저 실행되므로 실행 순서를 짐작할 수 있습니다.
• select_type: 전체 쿼리 내에서 해당 SELECT의 역할을 다음과 같은 값으로 설명합니다. SIMPLE (하위 쿼리나 UNION 없음) PRIMARY (복잡한 쿼리의 가장 바깥쪽 SELECT) UNION, UNION RESULT, DERIVED (FROM의 하위 쿼리) 또는 전체 텍스트 관련 유형을 확인할 수 있습니다. 이를 통해 중첩 쿼리, 공용체 또는 파생 테이블을 처리하는지 한눈에 확인할 수 있습니다.
• table: 이 행이 참조하는 테이블이나 내부 결과를 나타냅니다. 실제 테이블 이름이거나 다음과 같은 가상 이름일 수 있습니다. <unionM,N> 내부 UNION 결과의 경우, <derivedN> 파생 테이블의 경우 또는 구체화된 하위 쿼리의 경우 유사한 마커입니다.
• partitions: 분할된 테이블을 함께 사용하는 경우 EXPLAIN PARTITIONS, 쿼리 조건과 일치하는 행이 포함된 파티션을 나열합니다. 나열된 파티션이 너무 많으면 파티션 정리의 이점을 얻지 못할 수 있습니다.
• type: 조인 또는 액세스 유형이라고도 불리는 이 유형은 성능의 가장 중요한 지표 중 하나입니다. MySQL이 행에 액세스하는 방식을 나타내며, 값의 범위는 매우 효율적(예: const, eq_ref, ref) 덜 효율적 (range) 가난한 (index, ALL 전체 테이블 스캔). 다음과 같은 특수 유형 index_merge, unique_subquery예산 및 index_subquery 구체적인 최적화를 나타냅니다.
• possible_keys: MySQL이 쿼리의 이 부분에 사용할 수 있다고 생각하는 인덱스를 나열합니다. NULL이는 MySQL이 유용한 인덱스를 찾지 못했다는 것을 의미하는데, 이는 WHERE 또는 JOIN 조건에 따라 인덱스를 생성해야 한다는 강력한 신호입니다.
• key: 이 액세스를 위해 최적화 프로그램이 선택한 실제 인덱스를 표시합니다. NULL 동안 possible_keys 후보 목록을 나열한 후, 최적화 프로그램은 인덱스를 사용하는 것이 가치가 없다고 판단했는데, 이는 선택성이 낮거나 테이블 크기가 작은 경우가 많았기 때문입니다.
• key_len: 사용된 인덱스의 바이트 수를 표시합니다. 복합 인덱스의 경우, 이는 실제로 사용되는 선행 열의 수를 나타냅니다. 이는 다중 열 인덱스가 완전히 사용되는지, 아니면 부분적으로만 사용되는지 파악하는 데 중요합니다.
• ref: 나열된 인덱스 열과 비교되는 내용을 나타냅니다. key: 다른 테이블 열(조인의 경우)을 가리킬 수도 있고, 상수(간단한 필터의 경우)를 가리킬 수도 있습니다.
• rows: 이 단계에서 MySQL이 검사할 것으로 예상되는 행 수를 추정한 값입니다. 통계에 기반한 대략적인 값이지만, 쿼리 비용이 얼마나 될지, 그리고 인덱스가 검색 공간을 효과적으로 줄이는지 판단하는 데 매우 유용합니다.
• filtered: Available with EXPLAIN EXTENDED, 이 백분율은 검사된 행 중 해당 테이블의 조건을 통과할 것으로 예상되는 행의 수를 나타냅니다. 낮은 백분율과 높은 백분율이 결합된 값입니다. rows 더 나은 인덱싱이나 더 선택적인 조건을 요구하는 경우가 많습니다.
• Extra: 다른 곳에 맞지 않는 추가 메모를 포함하는 자유형 필드(예: Using index, Using where, Using temporary, Using filesort, 전체 텍스트 힌트 등이 있으며, 이 모든 것이 성능에 대한 중요한 단서를 제공합니다.

이 칼럼들을 따로 읽는 대신 함께 읽으면 MySQL이 사용자의 쿼리를 어떻게 충족시키려는지에 대한 간결하면서도 강력한 요약을 얻을 수 있습니다. 조금만 연습하면 전체 테이블 스캔, 인덱스 누락, 불필요한 임시 테이블 등의 위험 신호를 빠르게 발견할 수 있습니다.

EXPLAIN이 액세스 유형을 정의하는 방법( type 열)

The type 열은 쿼리의 상태를 판단하는 가장 빠른 방법 중 하나이므로 특별한 주의를 기울여야 합니다. 문서에서는 이를 조인 유형이라고 부르지만, 행을 찾는 방법을 설명하는 액세스 유형이라고 생각하는 것이 더 정확합니다.

높은 수준에서 액세스 유형은 성능 측면에서 "훌륭함"에서 "끔찍함"까지 다양합니다. 정확한 목록은 길지만 중요한 목록은 다음과 같습니다.

• const / system: MySQL은 기본 키 또는 상수 값을 갖는 고유 인덱스를 통해 테이블을 최대 한 행으로 변환할 수 있습니다. 이는 매우 효율적입니다.
• eq_ref: 이전 테이블의 각 행에 대해 MySQL은 이 테이블에서 정확히 하나의 일치하는 행을 읽습니다. 이는 일반적으로 조인에서 고유 키나 기본 키 참조로 인해 발생합니다.
• ref: MySQL은 상수 또는 열과 일치하는 고유하지 않은 인덱스를 통해 행에 접근합니다. 여러 행이 일치할 수도 있습니다. 그래도 일반적으로는 좋습니다.
• range: MySQL은 인덱스를 사용하여 지정된 값 범위(예: BETWEEN, >=또는 접두사 일치 LIKE 'abc%'). 특히 날짜나 숫자 범위의 경우 이 방법이 종종 허용됩니다.
• index: MySQL은 전체 테이블 데이터가 아닌 전체 인덱스를 스캔합니다. 전체 테이블 스캔보다 낫지만, 큰 인덱스에서는 여전히 비용이 많이 드는 경우가 많습니다.
• ALL: 전체 테이블 스캔. MySQL은 모든 행을 읽어서 일치 항목을 찾습니다. 작은 테이블에서는 문제가 없을 수 있지만, 큰 테이블에서는 인덱싱이나 쿼리 재작성을 통해 이를 방지하는 것이 좋습니다.
• index_merge: MySQL이 동일한 테이블의 여러 인덱스 결과를 결합한다는 것을 나타냅니다. 보기에는 좋지만, 실제로는 좋은 단일 복합 인덱스보다 성능이 떨어지는 경우가 많습니다.
• unique_subquery / index_subquery: 특정 최적화에 사용되는 특수 액세스 유형 IN (SELECT ...) 고유성 또는 고유하지 않은 인덱스 조회를 통한 하위 쿼리는 덜 효율적인 조회 패턴을 대체합니다.

EXPLAIN 출력을 검토할 때 일반적으로 액세스 유형이 최대한 멀리 있기를 원합니다. const / eq_ref / ref 가능한 한 피하세요 ALL 큰 테이블 위에. 당신이 볼 경우 ALL 높은 rows 추정에 따르면, 거의 항상 더 나은 인덱싱 후보입니다.

키, 가능한 키, 키 길이: 인덱스가 정말 도움이 되나요?

인덱스는 종종 쿼리 속도를 높이는 가장 강력한 방법이며, EXPLAIN은 인덱스가 어떻게 사용되는지 정확히 이해하는 데 도움이 됩니다. 인덱스 진단에는 특히 세 가지 열이 중요합니다. possible_keys, key예산 및 key_len.

possible_keys WHERE 및 JOIN 조건에 따라 MySQL이 해당 테이블과 관련이 있다고 생각하는 모든 인덱스를 나열합니다. 이것이 NULL 강력한 필터나 조인에 참여하는 테이블의 경우 관련 열을 포괄하는 인덱스를 추가하는 것이 좋습니다.

key MySQL이 후보 중에서 실제로 어떤 인덱스를 선택했는지 알려줍니다. If key is NULL 항목이 있는 동안 possible_keys최적화 프로그램은 전체 검사가 더 저렴하다고 판단했는데, 이는 일반적으로 인덱스 선택성이 낮거나 통계가 오래되었거나 사용 가능한 인덱스 디자인의 이점을 얻을 수 없는 쿼리 패턴을 나타냅니다.

key_len 선택한 인덱스의 바이트 수가 얼마나 사용되는지 보여줍니다. 복합 인덱스의 경우, 이를 통해 인덱싱된 열 중 검색에 기여하는 열의 수를 확인할 수 있습니다. 인덱스를 작성한 경우 (last_name, first_name) key_len 길이만 커버합니다 last_name, 쿼리가 복합 인덱스를 최대한 활용하지 못하고 있다는 것을 알고 계십니다.

The ref 열은 MySQL이 인덱스와 비교하는 내용(상수 등)을 알려줌으로써 이를 보완합니다. 'Puppo') 또는 조인된 테이블의 열. 조인을 디버깅하는 경우 올바른 조인 열이 표시됩니다. ref 좋은 것과 함께 type 다음과 같은 값 eq_ref 조인이 잘 인덱싱되었다는 신호입니다.

행, 필터링 및 추가: 숨겨진 비효율성 발견

The rows filtered 열은 작업량에 대한 대략적인 아이디어를 제공하는 반면 Extra 성능 문제를 설명하는 특수 작업을 강조합니다. 이 세 가지는 항상 함께 검토해야 합니다.

rows MySQL이 이 단계에서 조사해야 하는 레코드의 수를 추정한 값입니다. 항상 정확하지는 않지만, 몇 개의 행만 스캔할 것으로 예상했을 때 수십만 개의 행을 스캔하는 등 명백히 나쁜 경우를 감지하기에 충분합니다.

filtered (통해 이용 가능 EXPLAIN EXTENDED)는 해당 테이블에 대한 조건을 통과할 검사된 행의 추정 백분율을 제공합니다. 높은 rows 매우 낮은 filtered 백분율은 일반적으로 누락되었거나 최적이 아닌 인덱스 또는 선택성이 낮은 술어를 나타냅니다.

The Extra 이 열은 다른 곳에 맞지 않는 실행에 대한 추가 메모를 집계합니다. 여러분이 접할 수 있는 가장 중요한 가치는 다음과 같습니다.

• Using where: 이 테이블의 행을 필터링하기 위해 WHERE 조건이 적용되고 있습니다.
• Using index: MySQL은 테이블 데이터를 건드리지 않고 인덱스에서만 필요한 모든 열을 제공할 수 있는데, 이를 커버링 인덱스라고 하며 일반적으로 매우 효율적입니다.
• Using temporary: MySQL은 복잡한 GROUP BY 또는 하위 쿼리 처리 등의 중간 결과를 보관하기 위해 내부 임시 테이블을 생성합니다. 대용량 데이터 세트에서는 성능에 심각한 영향을 미칠 수 있습니다.
• Using filesort: MySQL은 ORDER BY나 GROUP BY를 위해 별도의 정렬 단계를 수행하는데, 이는 메모리나 디스크에서 수행될 수 있으며 일반적으로 인덱스 기반 순서를 사용하는 것보다 비용이 더 많이 듭니다.
• 전체 텍스트 관련 참고 사항: FULLTEXT 인덱스를 사용할 때 EXPLAIN은 전체 텍스트 검색이 조인이나 다른 필터와 올바르게 결합되었는지 여부를 보여줄 수 있습니다.

특히주의하십시오 Using temporary Using filesort in Extra 쿼리가 느린 경우, 둘 다 더 나은 인덱스나 재구성된 쿼리에 오프로드할 수 있는 무거운 정렬이나 그룹화 작업을 나타낼 수 있기 때문입니다.

설명 분석: 실제 실행 비용 확인

MySQL 8.0.18부터 더욱 강력한 도구를 사용할 수 있습니다. EXPLAIN ANALYZE실제로 쿼리를 실행하고 런타임 통계를 계획에 첨부합니다. 이는 이론적인 비용 추정과 실제 성과 간의 격차를 메워줍니다.

예측만 하는 일반 EXPLAIN과 달리 EXPLAIN ANALYZE는 명령문을 실행하고 계획의 각 반복자(단계)가 걸리는 시간, 반환하는 행 수, 수행하는 루프 수를 측정합니다. 구문은 간단합니다.

운영: EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

EXPLAIN ANALYZE를 실행하면 MySQL은 다음을 사용합니다. FORMAT=tree 자동으로 추정 및 실제 지표를 결합한 트리 모양의 계획을 출력합니다. 각 노드에 대해 다음을 확인할 수 있습니다.

• 예상 실행 비용: 이 단계의 비용이 얼마나 될지에 대한 최적화 모델입니다.
• 예상 행 수: 이 반복자에서 반환될 것으로 예상되는 행의 수입니다.
• 첫 번째 행까지의 실제 시간: 첫 번째 줄이 만들어지기까지 걸린 시간입니다.
• 루프당 실제 소요 시간: 자식은 포함하지만 부모는 포함하지 않는 반복자를 루프 전체에서 실행하는 데 걸리는 평균 시간(밀리초)입니다.
• 실제 행과 루프: 실제로 반환된 행의 수와 반복자가 실행된 횟수입니다.

EXPLAIN ANALYZE는 SELECT, 다중 테이블 UPDATE, DELETE 및 TABLE 문과 함께 사용할 수 있습니다. 이 기능은 일반적인 EXPLAIN이 한 가지를 제안하지만, 실제 운영 환경에서는 쿼리가 다르게 동작하는 경우에 특히 유용합니다. 이제 추정 행 수와 비용과 실제 행 수와 비용을 비교할 수 있기 때문입니다.

추정치와 실제 값 사이에 큰 차이가 있는 경우 최적화 프로그램의 통계가 틀린 부분이나 복잡한 예측 조건(함수, UDF 또는 저장 루틴 포함)으로 인해 비용 예측이 부정확해지는 부분이 드러납니다. 이러한 장소는 스키마 조정, 새로운 인덱스 또는 쿼리 재작성의 주요 대상입니다.

클래식 EXPLAIN 대 EXPLAIN ANALYZE: 장점과 한계

EXPLAIN과 EXPLAIN ANALYZE가 매우 유용하기는 하지만, 출력을 과도하게 해석하지 않도록 그 맹점을 이해하는 것이 중요합니다.

일반적인 EXPLAIN은 기본적으로 최적화 프로그램이 일어날 것이라고 생각하는 일을 대략적으로 표현한 것입니다. 일부 통계는 대략적인 추정치이며, 특정 내부 최적화는 출력에 전혀 반영되지 않습니다. 예를 들어, 트리거, 저장 함수 또는 사용자 정의 함수가 CPU 시간에 어떤 영향을 미치는지 알려주지 않습니다. 또한 저장 프로시저 내부에서 어떤 일이 발생하는지도 보여주지 않습니다.

출력된 일부 레이블은 실제 상황을 지나치게 단순화한 것입니다. 예를 들어, type 열은 조인 유형이라고 불리지만 실제로는 액세스 유형을 나타냅니다. Extra 가치 Using temporary 메모리 내 임시 테이블과 디스크 내 임시 테이블을 구별하지 않습니다. Using filesort 동일한 라벨로 메모리 기반 정렬과 디스크 기반 정렬을 모두 포괄합니다.

EXPLAIN ANALYZE는 실제 타이밍과 행 수를 보여줌으로써 이러한 문제 중 일부를 해결하지만, 그에 따른 단점도 있습니다. 쿼리를 실행해야 하는데, 리소스 사용량이 많거나 데이터를 수정하는 쿼리의 경우 프로덕션 환경에서 비용이 많이 들거나 위험할 수 있습니다. 또한, 현재 EXPLAIN ANALYZE에서는 원시 JSON 출력을 직접 사용할 수 없으므로, 주로 CLI 또는 클라이언트 도구에서 트리 형식을 사용합니다.

올바른 접근 방식은 EXPLAIN을 사용하여 계획에 대한 빠르고 영향이 적은 통찰력을 얻고, 실제로 쿼리를 실행할 여유가 있는 심층적인 조사에는 EXPLAIN ANALYZE를 사용하는 것입니다.

EXPLAIN 및 EXPLAIN ANALYZE를 사용하여 실제 쿼리 최적화

EXPLAIN을 읽는 것은 더 나은 쿼리로 이어질 때만 유용합니다. 중요한 것은 본 내용을 특정 인덱스와 SQL 변경 사항으로 변환하는 것입니다. EXPLAIN의 도움으로 직접 해결할 수 있는 몇 가지 일반적인 최적화 시나리오를 살펴보겠습니다.

간단한 쿼리를 상상해보세요. employees 관련 인덱스가 없는 테이블:

필터 예: EXPLAIN SELECT * FROM employees WHERE last_name = 'Puppo' AND first_name = 'Kendra';

인덱스가 없으면 EXPLAIN이 표시될 가능성이 높습니다. type = ALL 그리고 매우 큰 값 rows (예: 약 299,000개 행) 전체 테이블 스캔을 나타냅니다. 이는 두 가지 조건을 모두 지원하는 인덱스가 필요하다는 명확한 신호입니다.

순진한 접근 방식 중 하나는 두 개의 별도 인덱스를 만드는 것입니다. last_name 그리고 하나 first_name하지만 그래도 조합 검색은 효율적이지 않습니다. MySQL은 last_name = 'Puppo'인 모든 사람 또는 first_name = 'Kendra'인 모든 사람을 빠르게 찾을 수 있지만, 'Kendra Puppo' 행을 정확히 식별하려면 해당 집합을 교차해야 하는데, 이는 기대하는 만큼 효율적이지 않습니다.

더 나은 솔루션은 가장 선택적이고 자주 사용되는 검색 패턴을 따르는 다중 열 인덱스입니다.

인덱스 생성: CREATE INDEX fullnames ON employees(last_name, first_name);

지금 EXPLAIN을 다시 실행하면 이 인덱스가 사용되었음을 알 수 있습니다. type 뭔가로 개선되다 ref 심지어 const 고유성에 따라 rows 1로 떨어집니다. 이는 단 하나의 행만 건드리면 된다는 것을 확인시켜 주며, 이를 통해 성능이 엄청나게 향상되었음을 설명합니다.

조인 최적화에서도 비슷한 패턴이 나타납니다. 조인에 사용되는 공유 열 X가 있는 두 개의 테이블 A와 B가 있고 처음에 인덱스 없이 두 테이블을 만들었다고 가정해 보겠습니다.

개요: CREATE TABLE A ( X VARCHAR(10), Y VARCHAR(10));
CREATE TABLE B ( X VARCHAR(10), Z VARCHAR(10));

AX에서 필터와 함께 조인을 실행하면 A와 B 모두에서 전체 스캔이 표시될 수 있으며, 각각 약 10,000개의 행이 검사됩니다. 엔진에는 행을 무차별 대입하여 비교하는 것보다 더 나은 옵션이 없기 때문입니다.

조인 쿼리: EXPLAIN SELECT *
FROM A INNER JOIN B ON A.X = B.X
WHERE A.X = '100';

적절한 인덱스와 외래 키 제약 조건을 추가하면 EXPLAIN은 개선된 전략을 반영합니다. 예 :

인덱스 및 FK: CREATE UNIQUE INDEX a_unique_index ON A(X);
CREATE UNIQUE INDEX b_unique_index ON B(X);
ALTER TABLE B ADD CONSTRAINT fk_b_x FOREIGN KEY (X) REFERENCES A(X);

이러한 변경 사항 이후 EXPLAIN은 일반적으로 다음과 같이 표시됩니다. type 개선(예: eq_ref 조인된 테이블에서)뿐만 아니라 rows 수천 개에서 약 1개로 줄어들면서 조인이 모든 것을 스캔하는 대신 효율적인 인덱스 조회를 사용한다는 것이 증명되었습니다.

패턴 매칭, 정렬 및 기타 일반적인 함정

EXPLAIN은 패턴, 정렬 및 그룹화를 처리할 때 특히 유용합니다. 겉보기에 무해한 SQL이 인덱스를 완전히 비활성화할 수 있기 때문입니다. 이런 패턴을 일찍 알아차리면 추측에 드는 시간을 크게 줄일 수 있습니다.

고전적인 문제 중 하나는 선행 와일드카드를 사용한 패턴 매칭입니다. 예를 들어, 다음 쿼리가 있습니다. WHERE email LIKE '%yahoo.com' 표준 인덱스를 사용할 수 없게 만듭니다. email MySQL에는 해당 접미사로 끝나는 행으로 직접 이동할 방법이 없기 때문에 도움이 됩니다. EXPLAIN은 일반적으로 다음을 표시합니다. type = ALL 그리고 큰 rows 카운트.

후행 와일드카드, 예: LIKE 'john%', 는 인덱스 친화적입니다. 엔진이 인덱스를 사용하여 'john'으로 시작하는 행의 범위를 찾을 수 있기 때문입니다. EXPLAIN은 좋은 액세스 유형과 작은 것을 보여줌으로써 이를 확인할 것입니다. rows 인덱스된 열에 대한 추정치입니다.

정렬과 그룹화는 또 다른 성능 향상 요소입니다. EXPLAIN이 표시되면 Using filesort in Extra 큰 것과 함께 rows 추정, ORDER BY 또는 GROUP BY는 명시적인 정렬 단계를 강제할 가능성이 있습니다.

정렬 예: EXPLAIN SELECT sale_id FROM sales ORDER BY sale_date;

인덱스 추가 sale_date MySQL이 정렬된 순서대로 행을 직접 읽을 수 있도록 하며 EXPLAIN은 일반적으로 제거하여 이를 반영합니다. Using filesort 그리고 아마도 액세스 유형을 변경할 수도 있습니다. index 또는 더 나은.

동시에, 색인을 과도하게 사용하지 않도록 주의하세요. EXPLAIN은 어떤 인덱스가 나타나는지 보여줄 수 있습니다. possible_keys 하지만 결코 사용되지 않습니다 key 중요한 쿼리에 의해 발생합니다. 이러한 쿼리는 제거 후보가 될 수 있지만, 먼저 전체 작업 부하를 확인해야 합니다. 한 쿼리가 인덱스를 무시한다고 해서 다른 중요한 쿼리가 해당 인덱스에 의존하지 않는다는 의미는 아닙니다.

경고 표시 및 확장된 설명 마커

EXPLAINed 쿼리가 올바르게 구문 분석되지 않더라도 다음을 사용하여 일부 통찰력을 추출할 수 있습니다. SHOW WARNINGS;. 이 명령은 마지막 비진단 명령문에 대한 정보를 표시하고 MySQL이 쿼리의 일부를 어떻게 해석했는지에 대한 부분적인 힌트를 제공할 수 있습니다.

예를 들어, 존재하지 않는 테이블을 참조하거나 구문 오류가 포함된 쿼리에 EXPLAIN을 실행하면 EXPLAIN이 실패할 수 있지만 SHOW WARNINGS를 사용하면 쿼리의 일부가 처리되는 방식을 나타내는 내부 마커를 여전히 드러낼 수 있습니다. 다음과 같은 표시가 보일 수 있습니다.

• <index_lookup>(query fragment): 쿼리가 유효하다면 해당 조각에 대한 인덱스 조회가 수행될 것이라고 제안합니다.
• <primary_index_lookup>(query fragment): 기본 키 기반 조회가 발생함을 나타냅니다.
• <if>(condition, expr1, expr2): 쿼리의 해당 부분에서 조건부 평가를 지적합니다.
• <temporary table>: 예를 들어 조인 전에 중간 결과를 위한 내부 임시 테이블을 만드는 것을 나타냅니다.

EXPLAIN EXTENDED를 SHOW WARNINGS와 결합하면 계획뿐만 아니라 최적화 프로그램의 재작성된 쿼리도 볼 수 있는 강력한 방법이 되는데, 이를 통해 단순화, 술어 푸시다운 또는 예상치 못한 다른 변환이 드러나는 경우도 있습니다.

EXPLAIN을 넘어서: 데이터 모델, 아키텍처 및 툴링

EXPLAIN은 개별 쿼리에 대한 훌륭한 분석 도구이지만, 지속 가능한 성능은 전체 데이터 모델, 시스템 아키텍처, 하드웨어에 따라서도 달라집니다. 근본적으로 잘못된 스키마나 오버로드된 인스턴스를 where 절만 조정하는 것만으로는 수정할 수 없습니다.

좋은 시작점은 매우 다른 작업 부하를 분리하는 데이터 모델입니다. SQL에서 JSON 처리. 예를 들어, 동일한 MySQL 인스턴스에서 대용량 액세스 로그와 트랜잭션 데이터를 혼합하는 것은 장기적인 문제를 야기할 수 있습니다. 회사가 성장함에 따라 로깅 트래픽으로 인해 I/O가 포화 상태가 되어 중요한 비즈니스 쿼리의 성능이 저하될 수 있습니다. 로그를 전용 저장소나 데이터웨어하우스로 분할하는 것이 확장성이 더 높은 경우가 많습니다.

소프트웨어 아키텍처 또한 중요한 역할을 합니다. 모놀리스 vs. 마이크로서비스, 데이터웨어하우스, 그리고 전문 저장소(추천을 위한 그래프 데이터베이스 등)에 대한 결정은 어떤 종류의 쿼리를 어디에서 실행할지에 영향을 미칩니다. 서비스에 방대한 데이터 세트에 대한 실시간에 가까운 분석이 필요한 경우, 표준 OLTP MySQL 인스턴스는 이러한 쿼리에 가장 적합한 대상이 아닐 수 있습니다.

하드웨어와 인스턴스 크기도 중요합니다. 디스크 I/O, 메모리, CPU 및 네트워크 처리량은 모두 쿼리 지연 시간에 영향을 미칩니다. 사용량이 가장 많은 시간대를 파악하고 해당 시간대를 벗어나는 시간대에 집중적인 분석 또는 관리 쿼리를 예약하면 사용자 대면 성능을 보호할 수 있습니다. 관리형 DBaaS 솔루션을 사용하면 필요에 따라 인스턴스를 확장하거나 성능이 더 뛰어난 클래스로 전환할 수 있습니다.

마지막으로, EXPLAIN에 지속적인 모니터링 및 프로파일링 도구를 추가하세요. MySQL 자체의 성능 스키마와 느린 쿼리 로그, MySQL Workbench, Percona Toolkit의 pt-query-digestEverSQL과 같은 웹 도구와 Releem과 같은 플랫폼은 자동으로 가장 심각한 오류를 찾아내고 최적화 방안까지 제안합니다. EXPLAIN은 이러한 특정 쿼리에 대한 최적의 도구입니다.

EXPLAIN과 EXPLAIN ANALYZE를 도구 상자에 추가하면 느린 쿼리에 대한 추측을 중단하고 구체적인 증거를 바탕으로 추론을 시작할 수 있습니다. ID, 선택 유형, 액세스 방법, 인덱스 사용, 행 추정, 필터 및 추가 플래그를 이해하면 전체 테이블 스캔을 체계적으로 제거하고, 비용이 많이 드는 조인을 줄이고, 더 스마트한 인덱스를 설계하고, 불필요한 정렬 및 임시 테이블을 피할 수 있으며, 스키마 디자인과 인프라의 큰 그림을 주시하면서 MySQL 워크로드가 성장함에 따라 빠르고 예측 가능하게 유지할 수 있습니다.