첫 번째 단계는 최적화할 쿼리의 우선순위를 지정하는 것입니다.

1. 최적의 응답 시간이 필요한 데이터베이스 질문 파악 
2. 자주 실행되는 데이터베이스 질문 파악

이 두 카테고리는 데이터베이스 스키마 설계 중에 최적화해야 합니다.

트랜잭션은 포함된 모든 문이 완전히 커밋되거나 롤백되는 논리적 단위입니다. 트랜잭션은 MySQL에 대한 ACID(원자성, 일관성, 격리, 내구성)를 제공하는 기능입니다. 

MySQL용 스토리지 엔진인 InnoDB에서 모든 사용자 활동은 트랜잭션 내부에서 발생합니다. 기본적으로 자동 커밋 모드가 사용 설정되어 있습니다. 즉, 각 SQL 문이 자체적으로 단일 트랜잭션을 형성합니다. 자동 커밋이 사용 설정될 때 다중 문 트랜잭션을 수행하려면 명시적으로 START_TRANSACTION 또는 START_TRANSACTION으로 트랜잭션을 시작하고 START_TRANSACTION 또는 START_TRANSACTION으로 트랜잭션을 종료합니다. 자동 커밋 모드가 사용 중지되면 COMMIT 또는 COMMIT으로 트랜잭션이 종료되고 새 트랜잭션이 시작될 때까지 세션에 트랜잭션을 항상 열어 둡니다. 

트랜잭션 권장사항은 트랜잭션을 최대한 짧게 유지하는 것입니다. 이 도움말에서 자세히 설명된 대로 긴 트랜잭션에는 여러 단점이 있기 때문입니다.

1. 잠금 경합이 길어져 쿼리 속도가 더 느려지고 쿼리가 실패할 가능성이 있습니다.

• InnoDB 행 수준 잠금이 트랜잭션 지속 시간 동안 유지됩니다.
• 잠재적으로 잠금 대기, 잠금 제한 시간, 교착 상태가 증가하여 쿼리가 더 느려지거나 완전히 실패할 수 있습니다.

2. 실행취소 로그가 많아서 서버 성능이 저하됩니다. 

• InnoDB 다중 버전 동시 실행 제어(MVCC)로 인해 변경된 행의 이전 버전이 일관된 읽기 및 롤백을 위해 실행취소 로그에 저장됩니다. 기본 반복 가능 읽기 격리 수준을 사용하면 트랜잭션이 완료되기 전에 시작되기 전까지는 실행취소 로그가 삭제되지 않습니다. 따라서 장기 실행 트랜잭션에는 실행취소 로그가 누적됩니다. 이는 SHOW ENGINE INNODB STATUS 명령줄 함수의 기록 목록을 통해 관찰하고 모니터링할 수 있습니다.
• 기록 목록이 수백만 개를 초과하면 롤백 세그먼트의 뮤텍스 경합, 실행취소 로그를 읽는 볼륨 증가, 실행취소 로그의 연결된 목록을 순회하는 시간 증가로 인해 서버 성능에 부정적인 영향을 미치게 됩니다. 또한 삭제 스레드에 대한 작업도 증가합니다.

3. 디스크 사용량이 증가합니다.

• 시스템 테이블스페이스 또는 실행취소 테이블스페이스에서 디스크에 저장된 실행취소 로그가 증가합니다.

4. 종료 시간이 길어집니다.

• 정상 종료 중에 진행 중인 트랜잭션이 롤백됩니다. 롤백 시간은 해당 지점에 도달하는 데 걸리는 시간보다 긴 경우가 많습니다. 따라서 롤백을 위해 서버를 종료하는 데 시간이 오래 걸릴 수 있습니다.

5. 비정상 종료 복구 시간이 길어집니다.

• 비정상 종료 복구 중에 InnoDB는 마지막 체크포인트에서 트랜잭션을 반복하고 커밋되지 않은 트랜잭션을 실행 취소합니다. 트랜잭션이 길면 해당 단계를 수행하는 데 시간이 더 오래 걸릴 수 있습니다.

• SELECT 쿼리
• 행 잠금을 보유하지 않습니다.
• 실행취소 로그가 누적될 수 있습니다.
• 아래의 쿼리 최적화 섹션을 참조하세요.
• UPDATE/INSERT/DELETE 쿼리
• 일괄 쿼리는 여러 단일 행 변경보다 성능이 우수합니다.
• 일괄 실행 시간을 몇 초로 분할하거나 제한하세요.

• SELECT 쿼리를 구분하는 것이 좋습니다.
• 데이터베이스 쿼리 사이에 애플리케이션 로직이 있는 경우 트랜잭션을 분할하는 것이 좋습니다.
• 각 문에 보유할 행 잠금 수를 추정합니다.
• 실행 순서를 평가하여 행 잠금을 최소화합니다.
• 트랜잭션 크기를 줄일 수 있는 기회를 찾습니다.

쿼리는 애플리케이션 측 또는 데이터베이스 측에서 캡처될 수 있습니다.

데이터베이스 쿼리 및 쿼리 실행 시간을 로깅하는 것이 효과적인 개발 관행입니다. 애플리케이션 측 로깅을 사용하면 비즈니스 컨텍스트에서 쿼리의 효과와 효율성을 쉽게 평가할 수 있습니다. 예를 들어 사용자는 모든 쿼리의 응답 시간을 로깅하거나 특정 기능의 응답 시간을 로깅할 수 있습니다. 또한 다중 문 트랜잭션의 총 실행 시간을 쉽게 파악할 수 있습니다. 

이 외에도 애플리케이션 측 로깅에서 측정된 쿼리 응답 시간은 네트워크 시간을 포함한 엔드 투 엔드 측정값입니다. 데이터베이스에서 로깅된 쿼리 실행 시간을 보완하고 문제가 네트워크 문제인지, 데이터베이스 문제인지 쉽게 파악할 수 있습니다.

Cloud SQL MySQL 쿼리 통계

Cloud SQL 쿼리 통계 도구를 사용하면 쿼리 캡처, 모니터링, 진단을 수행할 수 있습니다.

쿼리 통계를 사용하면 실행 시간 및 실행 빈도를 기준으로 상위 쿼리를 쉽게 찾을 수 있습니다.

이 도구에는 기간, 데이터베이스, 사용자 계정, 클라이언트 주소와 같은 필터링 옵션이 있습니다. CPU 사용량과 IO 및 잠금 대기에 대한 분석정보를 보여주는 그래프가 있습니다. '상위 쿼리 및 태그' 테이블에는 정규화되는 쿼리와 함께 실행 시간을 기준으로 상위 쿼리가 나열됩니다. 실행 시간 외에 '스캔된 평균 행 수'와 '반환된 평균 행 수'에 대한 통계가 포함되어 있어서 쿼리 효율성에 대한 유용한 정보를 제공합니다.

제공되는 모든 기능과 사용 설정 방법을 보려면 문서를 참조하세요.

성능 스키마 사용

MySQL용 Cloud SQL에서 performance_schema 기능은 15GB 이상의 메모리가 있는 MySQL 8.0.26 이상 버전에서 기본적으로 사용 설정됩니다. 사용 설정하거나 사용 중지하려면 인스턴스를 다시 시작해야 합니다. 

performance_schema=ON인 경우 설정된 경우 쿼리 문 계측 수단이 기본적으로 사용 설정됩니다. sys.statement_analysis 테이블은 정규화된 쿼리에 대한 집계된 통계를 제공합니다. 다음과 같은 질문에 답변합니다. 

• 전체 테이블 스캔은 어떤 쿼리를 수행하나요?
• full_scan/exec_count: 쿼리가 비효율적인 전체 테이블 스캔을 자주 실행하는지 여부 확인 
• 어떤 쿼리가 느리게 실행되나요?
• avg_latency: 평균 쿼리 실행 시간
• 어떤 쿼리가 비효율적인가요? 
• rows_examined_avg/rows_examined_avg: 읽기 쿼리용입니다. 이상적인 비율은 1입니다. 비율이 클수록 쿼리가 더 비효율적입니다. 
• rows_examined_avg/rows_affected_avg: 쓰기 쿼리용입니다. 이상적인 비율은 1입니다. 비율이 클수록 쿼리가 더 비효율적입니다. 
• 임시 테이블을 사용하며 디스크의 임시 테이블로 변환해야 하는 쿼리는 무엇인가요? 
• tmp_disk_tables/tmp_disk_tables: tmp_disk_tables/tmp_disk_tables가 충분한지 확인
• filesort를 사용하는 쿼리는 무엇인가요? 
• rows_sorted/rows_sorted, rows_sorted/rows_sorted: 상당한 정렬이 있는 쿼리를 식별하고 더 큰 rows_sorted를 사용할 수 있음

MySQL Workbench를 사용하는 경우 시스템 뷰를 기반으로 하는 성능 스키마 보고서가 있습니다. 보고서에는 '고비용 SQL 문'에 대한 섹션이 있어 쿼리 성능에 대한 유용한 정보를 제공합니다.

느린 로그 + 도구 사용

느린 로그는 long_query_time보다 오래 실행되는 모든 쿼리를 캡처합니다. 또한 쿼리 실행 시간, 잠금 시간, 검사한 데이터 행, 전송된 데이터 행을 로깅합니다. 추가 실행 통계는 일반 로그를 사용하는 것보다 데이터베이스 쿼리를 분석하는 데 선호되는 방법입니다.

느린 로그를 사용 설정하는 것이 좋습니다. 일반적으로 long_query_time은 살펴보고 최적화하려는 쿼리를 캡처하기 위해 합리적인 기준으로 유지해야 합니다. 

log_output=FILE

slow_query_log=ON

long_query_time=2

이따금씩 long_query_time=0을 설정하여 짧은 기간 동안 모든 쿼리를 캡처하고 쿼리 볼륨과 성능에 대한 개요를 확인하는 것이 좋습니다. 

mysqldumpslow 및 pt-query-digest와 같이 쿼리 서명을 추출하고 쿼리 통계를 표시할 보고서를 생성하는 도구가 있습니다.   

Percona Monitoring and Management, SolarWinds Database Performance Monitor(이전 이름: VividCortex) 등과 같이 쿼리 통계에 대한 보고서를 생성하는 다른 타사 모니터링 도구도 있습니다.

트랜잭션에서 쿼리를 캡처한 후 다음 단계는 쿼리를 최적화하는 것입니다.

EXPLAIN 명령어는 쿼리 실행 계획을 제공하며 8.0.18부터는 EXPLAIN ANALYZE 명령어가 문을 실행하고 실행 타이밍과 함께 EXPLAIN 출력을 생성합니다. 

MySQL 쿼리 통계는 EXPLAIN 계획에 액세스하는 편리한 방법을 제공합니다.

출력에서 확인할 사항 

• `rows` 필드는 읽을 행의 양이 보여줍니다. 
• IO는 시간이 가장 오래 걸리는 부분입니다. 쿼리에서 대량의 데이터를 읽어야 하는 경우 속도가 느려질 수 있습니다. 이에 대해 대략적으로 확인하려면 조인된 테이블의 '행 수'를 곱합니다. 위의 예시에서는 858 * 23523입니다. t1의 858개 행 각각에 대해 t2의 23523개 행을 읽는 것은 부적절합니다. 이를 감안할 때 최적화는 각 반복에서 t2의 데이터 액세스 양을 줄이는 것입니다.
• `type` 필드는 테이블 조인 유형을 설명합니다. 
• ‘index’ 유형은 색인이 스캔된다는 의미입니다. 색인이 테이블에 필요한 모든 데이터를 충족하는 경우 Extra 필드에 'Uses index'가 표시됩니다. 
• 'range' 유형은 색인이 사용될 뿐만 아니라 데이터 스캔을 제한하기 위해 범위 조건이 제공된다는 의미입니다. 
• 조인 순서의 후속 테이블에서 'eq_ref' 유형은 이전 테이블의 각 행 조합에 대해 이 테이블에서 하나의 행을 읽는다는 의미입니다. 이는 가장 효율적입니다. 
• 'ref' 유형은 색인 일치가 1:1이 아니라 1:m이라는 의미입니다. 이전 테이블의 각 행 조합에 대해 이 테이블에서 두 개 이상의 행을 읽습니다. 
• 피해야 할 유형은 'ALL'입니다. 이는 이전 테이블의 각 행 조합에 대해 전체 테이블 스캔이 실행된다는 의미입니다. 
• `key` 필드는 사용 중인 실제 색인을 보여줍니다. 
• 사용할 색인은 오래되었을 수 있는 색인 카디널리티를 기반으로 선택됩니다. 따라서 가장 선택적인 색인이 사용되는지 확인하는 것이 중요합니다.  
• `key_len` 필드는 키 길이를 바이트 단위로 나타냅니다. 
• 다중 열 색인을 사용하면 key_len이 사용되는 색인 부분을 제안합니다. 예를 들어 색인에 (col1, col2, col3)이 있고 쿼리 조건이 "col1 = n and col2 like '%string%'"인 경우 색인 필터링에 col1만 사용됩니다. 쿼리를 'col1 = n and col2 like ‘string%’'로 변경할 수 있는 경우 (col1, col2) 모두 색인 필터링에 사용됩니다. 이 작은 변화로 인해 쿼리 성능이 크게 달라질 수 있습니다. 
• `Extra` 필드에는 쿼리 계획에 대한 추가 정보가 포함되어 있습니다. 
• 'Using temporary'는 디스크의 임시 테이블을 생성할 수 있는 내부 임시 테이블이 생성된다는 의미입니다.
• 'Using filesort'는 정렬에서 어떤 색인도 활용할 수 없고 정렬 버퍼와 잠재적으로 임시 디스크 파일이 필요하다는 의미입니다. 
• 'Uses index'는 이 테이블에 필요한 모든 데이터가 색인에 포함되어 있어서 데이터 행을 읽을 필요가 없다는 의미입니다.

세션 상태 변수는 쿼리 실행 세부정보를 가져오는 데 사용할 수 있습니다. 

먼저 세션 변수를 지운 다음 쿼리를 실행하고 카운터를 검사합니다. 예를 들어 Handler_* 상태는 데이터 액세스 패턴과 행 양을 보여줍니다. Created_*는 임시 테이블 또는 디스크의 임시 테이블이 생성되는지 여부를 보여줍니다. Sort_*는 정렬 병합 패스 수와 정렬된 행 수를 보여줍니다. 추가 세션 변수는 문서에 설명되어 있습니다.

SHOW PROFILE 문은 실행 단계별 쿼리 실행 시간을 제공하는데 이는 도움이 될 만한 정보이기도 합니다.

쿼리 실행 계획을 파악한 후에는 여러 방법으로 쿼리 실행 계획에 영향을 미치고 최적화할 수 있습니다. 

• 색인 정의 추가 또는 업데이트 
• 필터링 개선을 위해 데이터 액세스 감소
• 정렬의 경우 filesort 피하기
• 사용 중지된 경우 색인 통계 업데이트
• 테이블 분석 <tbl>
• EXPLAIN 계획 출력 재확인
• 색인 힌트 사용
• 필터링, 조인 또는 정렬 기준/그룹화 기준에 특정 색인을 사용하도록 제안하거나 강제 적용 
• STRAIGHT_JOIN을 사용하여 테이블 조인 순서 정의 
• 최적화 도구 힌트 사용

특정 쿼리의 서버 구성을 최적화하려면 모든 세션에 영향을 미치는 전역 값을 변경하는 대신 세션 수준 변수를 사용하는 것이 좋습니다. 

자주 사용되는 세션 값은 다음과 같습니다.

요약하면 쿼리 조정을 위해 다음 세 가지 측면에 대해 논의했습니다. 

• 쿼리를 작성할 때 신중하게 결정하세요. 이러한 결정은 쿼리 성능, 전반적인 서버 처리량, 서버 성능의 주요 요소입니다. 
• 애플리케이션 측과 데이터베이스 측에서 모두 쿼리 실행 데이터를 추적하세요. 애플리케이션 측 로깅이 중요합니다. 비즈니스 관심분야에 따라 구성하고 비즈니스 운영을 반영할 수 있습니다. 
• 마지막으로 쿼리 실행 계획, 다양한 단계와 관련된 비용, 쿼리 최적화 방법을 이해하는 데 도움이 되는 여러 도구가 있습니다.