EXPLAIN를 사용하여 Azure Database for MySQL - 유연한 서버에서 쿼리 성능 프로파일링

아티클
02/29/2024

적용 대상: Azure Database for MySQL - 단일 서버 Azure Database for MySQL - 유연한 서버

Important

Azure Database for MySQL 단일 서버는 사용 중지 경로에 있습니다. Azure Database for MySQL 유연한 서버로 업그레이드하는 것이 좋습니다. Azure Database for MySQL - 유연한 서버로 마이그레이션하는 방법에 대한 자세한 내용은 Azure Database for MySQL 단일 서버에 대한 새로운 소식을 참조하세요.

EXPLAIN은 쿼리를 최적화하는 데 도움이 되는 편리한 도구입니다. EXPLAIN 문을 사용하여 SQL 문이 실행되는 방법에 대한 정보를 얻을 수 있습니다. 다음 예제에서는 EXPLAIN 문 실행의 출력을 보여 줍니다.

mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 10.00
        Extra: Using where

이 예제에서 키 값은 NULL입니다. 즉, Azure Database for MySQL 유연한 서버는 쿼리에 최적화된 인덱스를 찾을 수 없습니다. 결과적으로 전체 테이블 검색을 수행합니다. 인덱스를 ID 열에 추가하여 이 쿼리를 최적화한 다음, EXPLAIN 문을 다시 실행해 보겠습니다.

mysql> ALTER TABLE tb1 ADD KEY (id);
mysql> EXPLAIN SELECT * FROM tb1 WHERE id=100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ref
possible_keys: id
          key: id
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

이제 출력에서는 Azure Database for MySQL 유연한 서버에서 인덱스를 사용하여 행 수를 1로 제한하여 검색 시간을 크게 단축한다는 것을 보여 줍니다.

커버링 인덱스

커버링 인덱스는 쿼리의 모든 열을 포함하므로 데이터 테이블에서 값 검색이 줄어듭니다. 다음 GROUP BY 문 및 관련 출력에서 이를 보여 줍니다.

mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

출력에서는 적절한 인덱스를 사용할 수 없으므로 Azure Database for MySQL 유연한 서버에서 인덱스를 사용하지 않음을 보여 줍니다. 또한 출력에는 Azure Database for MySQL 유연한 서버에서 GROUP BY 절을 충족하기 위해 임시 테이블을 만든다는 것을 나타내는 Using temporary; Using filesort(임시 테이블 사용 중, 파일 정렬 사용 중)도 표시됩니다.

c2 열에만 인덱스를 만들면 아무런 차이가 없으며 Azure Database for MySQL 유연한 서버에서 여전히 임시 테이블을 만들어야 합니다.

mysql> ALTER TABLE tb1 ADD KEY (c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using temporary; Using filesort

이 경우 c2 값을 인덱스에 직접 추가하여 covered 인덱스를 c1 및 c2 모두에 만들 수 있습니다. 그러면 추가 데이터 조회가 제거됩니다.

mysql> ALTER TABLE tb1 ADD KEY covered(c1,c2);
mysql> EXPLAIN SELECT MAX(c1), c2 FROM tb1 WHERE c2 LIKE '%100' GROUP BY c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: covered
          key: covered
      key_len: 108
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using index

위의 EXPLAIN의 출력과 같이 이제 Azure Database for MySQL 유연한 서버에서 covered 인덱스를 사용하므로 임시 테이블을 만들 필요가 없습니다.

결합된 인덱스

결합된 인덱스는 여러 열의 값으로 구성되며 인덱싱된 열의 값을 연결하여 정렬되는 행의 배열로 간주할 수 있습니다. 이 메서드는 GROUP BY 문에 유용할 수 있습니다.

mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 995789
     filtered: 11.11
        Extra: Using where; Using filesort

Azure Database for MySQL 유연한 서버는 상당히 느린 파일 정렬 작업을 수행합니다. 많은 행을 정렬해야 하는 경우에는 특히 느립니다. 이 쿼리를 최적화하기 위해 결합된 인덱스를 정렬 중인 두 열 모두에 만듭니다.

mysql> ALTER TABLE tb1 ADD KEY my_sort2 (c1, c2);
mysql> EXPLAIN SELECT c1, c2 from tb1 WHERE c2 LIKE '%100' ORDER BY c1 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: index
possible_keys: NULL
          key: my_sort2
      key_len: 108
          ref: NULL
         rows: 10
     filtered: 11.11
        Extra: Using where; Using index

이제 EXPLAIN 문의 출력에서는 인덱스가 이미 정렬되어 있으므로 Azure Database for MySQL 유연한 서버에서 추가로 정렬하지 않기 위해 결합된 인덱스를 사용한다는 것을 보여 줍니다.

결론

다양한 유형의 인덱스와 함께 EXPLAIN을 사용하여 성능을 크게 높일 수 있습니다. 테이블에 인덱스가 있다고 해서 Azure Database for MySQL 유연한 서버에서 이러한 인덱스를 이 쿼리에 사용할 수 있다는 의미는 아닙니다. 항상 EXPLAIN을 사용하여 가정의 유효성을 검사하고, 인덱스를 사용하여 쿼리를 최적화합니다.

다음 단계

가장 중요한 질문에 대한 동료 대답을 찾거나 질문을 게시하거나 대답을 얻기 위해 Stack Overflow를 방문합니다.

EXPLAIN를 사용하여 Azure Database for MySQL - 유연한 서버에서 쿼리 성능 프로파일링

커버링 인덱스

결합된 인덱스

결론

다음 단계

추가 리소스