내 연락처 정보
우편메소피아@프로톤메일.com
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
MySQL 로그의 내용은 매우 중요하며 인터뷰 중에 자주 질문을 받습니다. 동시에 로그 관련 지식을 익히는 것은 MySQL의 기본 원리를 이해하는 데 도움이 되며 필요한 경우 문제를 해결하는 데도 도움이 됩니다.
MySQL의 일반적인 로그 유형에는 주로 다음 범주가 포함됩니다(InnoDB 스토리지 엔진용).
바이너리 로그(binlog)와 트랜잭션 로그(redo 로그 및 undo 로그)가 더 중요하며 집중이 필요합니다.
느린 쿼리 로그는 실행 시간이 long_query_time(기본값은 10초, 일반적으로 1초로 설정됨)을 초과하는 모든 쿼리 문을 기록합니다. SQL 느린 쿼리(SQL 실행 시간이 너무 김) 문제를 해결할 때 자주 사용됩니다.
느린 SQL을 찾는 것이 SQL 문의 성능을 최적화하는 첫 번째 단계입니다. 그런 다음 EXPLAIN 명령을 사용하여 느린 SQL을 분석하고 실행 계획에 대한 정보를 얻습니다.
"slow_query_log"와 같은 표시 변수를 사용하면 느린 쿼리 로그가 켜져 있는지 확인할 수 있습니다. 기본적으로 꺼져 있습니다.
SET GLOBAL Slow_query_log=ON으로 켤 수 있습니다.
long_query_time 매개변수는 쿼리가 느린 쿼리로 정의되기까지 걸리는 시간을 정의합니다. 기본값은 10초입니다. SHOW VARIABLES LIKE'%long_query_time%';
수정할 수도 있습니다. 전역 long_query_time = 12로 설정하세요.
실제 프로젝트에서는 느린 쿼리 로그가 상대적으로 클 수 있으며 이를 직접 분석하는 것은 불편할 수 있습니다. 공식 MySQL 느린 쿼리 분석 및 튜닝 도구를 사용할 수 있습니다. mysqldumpslow . 내 블로그도 단순히 mysqldumpslow 도구에 연결되어 있습니다.
[MySQL] mysqldumpslow 도구--느린 쿼리 로그 파일 요약-CSDN 블로그
MySQL에는 현재 느린 쿼리 문 수를 기록하는 변수가 있습니다. '%slo'와 같은 전역 상태 표시를 사용할 수 있습니다.
w_queries%'; 명령을 확인합니다.
MySQL은 우리에게 다음을 제공합니다.설명하다실행 계획에 대한 정보를 얻는 명령입니다.
실행 계획은 MySQL 쿼리 최적화 프로그램에 의해 최적화된 후 SQL 문의 구체적인 실행 방법을 나타냅니다. 실행 계획은 일반적으로 SQL 성능 분석 및 최적화와 같은 시나리오에서 사용됩니다. EXPLAIN의 결과를 통해 데이터 테이블의 쿼리 순서, 데이터 쿼리 연산의 동작 유형, 어떤 인덱스가 적중될 수 있는지, 실제로 어떤 인덱스가 적중되는지, 각 데이터의 레코드 행 수 등의 정보를 학습할 수 있습니다. 테이블 및 기타 정보가 쿼리됩니다. 특히 아래의 몇 가지 일반적인 방법을 통해 SQL을 최적화할 수 있습니다.
1. SELECT * 사용을 피하세요.
SELECT *는 더 많은 CPU를 소비합니다.
SELECT *쓸데없는 필드는 네트워크 대역폭 리소스 소비와 데이터 전송 시간을 증가시킵니다. 특히 대규모 필드(예: varchar,
blob、text)입니다.
SELECT *는 인덱스 최적화를 커버하기 위해 MySQL 옵티마이저를 사용할 수 없습니다. (MySQL 옵티마이저를 기반으로 한 "커버링 인덱스" 전략은 매우 빠르고 효율적이며 업계에서 적극 권장되는 쿼리 최적화 방법입니다.)
SELECT <field list>는 테이블 구조 변경의 영향을 줄일 수 있습니다.
2. 페이지 매김 최적화
일반 페이징은 데이터 양이 적을 때 상대적으로 짧은 시간이 걸립니다.
데이터 양이 수백만 또는 수천만에 달할 정도로 커지면 일반적인 페이징에 매우 오랜 시간이 걸립니다.
최적화 방법 위의 SQL 문을 하위 쿼리로 수정할 수 있습니다.
먼저 Limit의 첫 번째 매개변수에 해당하는 기본 키 값을 쿼리한 다음 이 기본 키 값을 기준으로 필터링하고 제한하므로 효율성이 더 빨라집니다. 그러나 이 방법은 ID가 양수인 경우에만 작동합니다.
그러나 하위 쿼리의 결과로 새 테이블이 생성되므로 성능에 영향을 미치므로 하위 쿼리를 과도하게 사용하는 것은 피해야 합니다. 또한 이 방법은 ID가 양수인 경우에만 적용 가능합니다. 복잡한 페이징 시나리오에서는 필터링 조건을 통해 조건을 충족하는 ID를 필터링해야 하는 경우가 많습니다. 이때 ID는 불연속적입니다.
3. 조인 횟수를 줄이세요
알리바바 개발 매뉴얼:
Zhihu에 대한 토론을 읽을 수 있습니다.
https://www.zhihu.com/question/68258877
https://www.zhihu.com/question/682588774. 외래 키 및 캐스케이드를 사용하지 않는 것이 좋습니다.
Alibaba Java 개발 매뉴얼:
5. 적절한 필드 유형을 선택하십시오.
6. UNION 대신 UNION ALL을 사용해 보세요.
UNION은 두 결과 세트의 모든 데이터를 임시 테이블에 넣은 다음 더 많은 시간과 CPU 리소스를 소비하는 중복 제거 작업을 수행합니다.
UNION ALL은 더 이상 결과 집합을 중복 제거하지 않으며, 얻은 데이터에는 중복 항목이 포함됩니다.
그러나 실제 비즈니스 시나리오에서 중복 데이터가 허용되지 않는 경우 UNION을 계속 사용할 수 있습니다.
7. 일괄 작업
데이터베이스의 데이터 업데이트에 대해 일괄 작업을 사용할 수 있는 경우 이를 최대한 활용하여 데이터베이스 요청 수를 줄이고 성능을 향상시킵니다.
8. 인덱스를 올바르게 사용하세요
이 섹션에는 많은 내용이 있는데, 이에 대해서는 나중에 별도의 블로그에서 소개하겠습니다.
binlog(바이너리 로그는 바이너리 로그 파일)는 주로 테이블 구조 변경(CREATE, ALTER, DROP TABLE.), 테이블 데이터 등 MSQL 데이터베이스에서 변경된 모든 작업(데이터베이스에서 실행되는 모든 DDL 및 DML 문)을 기록합니다. 수정(INSERT.UPDATE, DELETE..)을 수행하지만 SELECT, SHOW 및 데이터베이스를 변경하지 않는 기타 작업은 포함하지 않습니다.
show 바이너리 로그 명령을 사용하면 모든 바이너리 로그 목록을 볼 수 있습니다.
바이너리 기록 방법에는 3가지 유형이 있습니다.
행 모드에 비해 명령문 모드의 로그 파일은 더 작고 디스크 IO 압력도 더 작으며 성능이 더 좋습니다. 그러나 정확도는 행 모드보다 떨어집니다.
MySQL 5.1.5 이전에는 binlog 형식이 STATEMENT뿐이었습니다. 5.1.5에서는 ROW 형식의 binlog를 지원하기 시작했습니다. 버전 5.1.8부터 MySQL은 MIXED 형식의 binlog를 지원하기 시작했습니다. MySQL 5.7.7 이전에는 기본적으로 명령문 모드가 사용되었습니다. MySQL5.7.7은 기본적으로 행 모드를 사용합니다.
binlog에서 사용하는 형식을 보려면 '%binlog format%'과 같은 표시 변수를 사용할 수 있습니다.
binlog의 주요 애플리케이션 시나리오는 마스터-슬레이브, 마스터-마스터 및 마스터-슬레이브는 모두 binlog와 분리될 수 없습니다. 데이터를 동기화하고 데이터 일관성을 보장하려면 Binlog를 사용해야 합니다.
마스터-슬레이브 복제의 원리는 아래 그림에 나와 있습니다.
1. 메인 라이브러리는 데이터베이스의 데이터 변경 사항을 binlog에 기록합니다.
2. 슬레이브 라이브러리를 메인 라이브러리에 연결
3. 슬레이브 라이브러리는 I0 스레드를 생성하여 메인 라이브러리에서 업데이트된 binlog를 요청합니다.
4. 메인 라이브러리는 binlog를 보내기 위해 binlog 덤프 스레드를 생성하고, 슬레이브 라이브러리의 I/0 스레드는 수신을 담당합니다. 5. 슬레이브 라이브러리의 I/0 스레드는 수신된 binlog를 릴레이에 기록합니다. 통나무.
6. 라이브러리의 SQL 스레드에서 릴레이 로그를 읽고 로컬로 데이터를 동기화합니다(즉, SQL을 다시 실행합니다).
InnoDB 스토리지 엔진의 경우 트랜잭션 실행 중에 로그가 먼저 binlogcache에 기록됩니다. 트랜잭션이 제출될 때만 binlogcache의 로그가 디스크의 binlog 파일에 유지됩니다. 메모리에 쓰는 것이 더 빠르며 이는 효율성을 위해 수행됩니다.
트랜잭션의 binlog는 분할할 수 없기 때문에 트랜잭션의 크기에 관계없이 한 번만 작성해야 하므로 시스템은 각 스레드에 메모리 블록을 binlog 캐시로 할당합니다. binlog_cache_size 매개변수를 통해 단일 스레드의 binlogcache 크기를 제어할 수 있습니다. 저장소 내용이 이 매개변수를 초과하면 임시로 디스크에 저장(스왑)해야 합니다.
그러면 binlog는 언제 디스크에 플러시되나요? sync_binlog 매개변수를 통해 biglog 플러시 타이밍을 제어할 수 있습니다. 값 범위는 0-N이고 기본값은 0입니다.
·0: 필수 요구 사항이 없으며 시스템이 디스크에 쓸 시기를 결정합니다.
·1: 트랜잭션이 제출될 때마다 binlog가 디스크에 기록되어야 합니다.
·N: N개의 트랜잭션마다 Binlog가 디스크에 기록됩니다.손실 위험
MySQL5.7 이전에는 sync_binlog의 기본값이 0이었습니다. MySQL5.7 이후에는 sync_binlog의 기본값은 1입니다. 일반적으로 sync_binlog의 값을 0으로 설정하는 것은 권장되지 않습니다. 성능 요구 사항이 상대적으로 높거나 디스크 IO 병목 현상이 발생하는 경우 sync_binlog 값을 적절하게 늘릴 수 있지만 이로 인해 데이터 손실 위험이 높아집니다.
다음 세 가지 상황이 발생하면 MySQL은 새 로그 파일을 재생성하고 파일 일련 번호가 증가합니다.
우리는 InnoD8 스토리지 엔진이 페이지 단위로 저장 공간을 관리한다는 것을 알고 있습니다. 우리가 MySQL에 삽입하는 데이터는 결국 페이지에 존재합니다. 디스크 IO 오버헤드를 줄이기 위해 메모리에 존재하는 Buffer Pool이라는 영역도 있습니다. 우리의 데이터에 해당하는 페이지가 버퍼 풀에 없으면 MSQL은 먼저 디스크의 페이지를 버퍼 풀에 캐시하여 나중에 버퍼 풀의 페이지를 직접 운영하므로 읽기 및 쓰기 성능이 크게 향상됩니다. .
트랜잭션이 커밋된 후 버퍼 풀의 해당 페이지에 대한 수정 사항이 디스크에 유지되지 않을 수 있습니다. 이때 MySQL이 갑자기 충돌하면 이 트랜잭션의 변경 사항이 바로 사라지나요?
분명히 그렇지 않습니다. 그렇다면 분명히 거래의 내구성을 위반할 것입니다.
MySQLInnoDB 엔진은 리두 로그를 사용하여 트랜잭션 내구성을 보장합니다. 리두 로그가 수행하는 주요 작업은 특정 페이지의 특정 오프셋에서 수정된 바이트 수, 수정된 특정 내용 등 페이지 수정 사항을 기록하는 것입니다. 리두 로그의 각 레코드에는 테이블스페이스 번호, 데이터 페이지 번호, 오프셋, 특정 수정 데이터가 포함되어 있으며 수정된 데이터의 길이도 기록할 수 있습니다(리두 로그 유형에 따라 다름).
트랜잭션이 커밋되면 플러싱 전략에 따라 Redo 로그를 디스크에 플러시합니다. 이렇게 하면 MySQL이 충돌하더라도 디스크에 기록하지 못한 데이터를 다시 시작한 후에 복구할 수 있으므로 내구성이 보장됩니다. 거래의. 즉, 리두 로그는 MySQL 충돌 복구 기능을 제공합니다.
Redo Log는 데이터베이스에 대한 모든 수정 작업을 기록합니다. 데이터베이스가 쓰기 작업(INSERT, UPDATE, DELETE)을 수행하면 이러한 작업은 먼저 Redo 로그에 기록된 다음 데이터 파일에 적용됩니다. 이렇게 하면 데이터 수정 작업이 디스크에 완전히 기록되기 전에 시스템에 장애가 발생하더라도 Redo Log는 데이터가 손실되지 않도록 보장할 수 있습니다. 복구 중에 데이터베이스는 데이터 일관성을 보장하기 위해 Redo Log에서 이러한 완료되지 않은 수정 작업을 다시 실행합니다.
Redo Log는 시스템 충돌이나 예상치 못한 정전 이후 데이터베이스를 일관된 상태로 복구하는 데 도움이 됩니다. 복구 프로세스 중에 데이터베이스는 Redo 로그의 레코드를 확인하고 제출되었지만 지속되지 않은 모든 데이터 수정 사항을 데이터 파일에 다시 적용하여 데이터를 복구합니다.
쓰기 작업의 성능을 향상시키기 위해 데이터베이스는 종종 캐싱 메커니즘(예: 버퍼 풀)을 사용하여 수정 작업을 즉시 디스크에 쓰는 대신 임시로 메모리에 저장합니다. Redo Log가 존재하면 이러한 캐싱 메커니즘이 가능합니다. Redo Log가 지속되는 한 캐시의 데이터가 디스크에 기록되지 않은 경우에도 데이터 손실 위험이 없기 때문입니다.
트랜잭션을 커밋하면 로그 버퍼의 리두 로그가 디스크로 플러시됩니다. innodb_flush_log_at를 사용할 수 있습니다.
매개변수 제어를 커밋합니다. innodb_flush_log_at_trx_commit의 올바른 플러시 정책을 설정하는 데 주의를 기울여야 합니다. MySQL에 구성된 플러시 전략에 따라 MySQL이 다운된 후 사소한 데이터 손실 문제가 발생할 수 있습니다.
innodb_flush_log_at_trx_commit 이는 MySQL InnoDB 스토리지 엔진의 중요한 구성 매개변수로 트랜잭션이 제출될 때 로그의 플러시(flush) 및 쓰기(쓰기) 전략을 결정하여 데이터 내구성과 성능에 영향을 미칩니다. 여기에는 0, 1, 2의 세 가지 값이 있습니다. 각 값은 서로 다른 브러싱 전략을 나타냅니다.
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
브러시 전략 요약
1. Redo 로그는 로그 버퍼에 기록되지만 페이지 캐시에는 아직 기록되지 않은 상태입니다. 이때 데이터베이스가 충돌하고 데이터 손실이 발생합니다. (이러한 데이터 손실은 플러시 정책 innodb_flush log_at trx_commit 값이 다음과 같을 때 발생할 수 있습니다. 0);
2. 리두 로그가 페이지 캐시에 기록되었지만 아직 디스크에 기록되지 않았습니다. 운영 체제가 충돌하고 데이터 손실이 발생할 수 있습니다. 이 데이터 손실은 플러시 정책 innodb2 플러시 log_at trx_commit 값이 다음과 같을 때 발생할 수 있습니다. 2).
Undo Log(롤백 로그)는 데이터베이스 시스템에서 데이터 수정 작업을 기록하는 데 사용되는 로그입니다. 트랜잭션 실행 중 데이터에 대한 모든 수정 작업의 역방향 작업(즉, 취소 작업)을 기록합니다. Undo Log는 트랜잭션 롤백에서 중요한 역할을 합니다. Undo Log를 통해 데이터를 트랜잭션 시작 전 상태로 복원할 수 있어 트랜잭션의 원자성을 보장할 수 있습니다.
트랜잭션의 원자성은 트랜잭션의 모든 작업이 모두 실행되거나 전혀 실행되지 않음을 의미합니다. 실행 취소 로그는 다음 메커니즘을 통해 트랜잭션의 원자성을 보장합니다.
실행 취소 작업 기록 트랜잭션이 실행되는 동안 데이터에 대한 모든 수정 작업은 실제 수정 전에 실행 취소 로그에 해당 실행 취소 작업을 기록합니다. 예를 들어 트랜잭션이 레코드 행의 값을 업데이트하는 경우 업데이트하기 전에 이전 값이 실행 취소 로그에 기록됩니다.
롤백 트랜잭션 오류나 명시적인 롤백 등 어떤 이유로 트랜잭션이 실패하면 데이터베이스 시스템은 Undo 로그를 읽고 기록된 Undo 작업에 따라 트랜잭션이 시작되기 전의 상태로 데이터를 복원합니다. 이러한 방식으로 실패한 트랜잭션이 데이터베이스에 영향을 미치지 않도록 하여 트랜잭션의 원자성을 보장할 수 있습니다.
인용하다:
그는 30년 이상 기술 연구에 전념해 왔으며, java, linux, javascript, php, css 등 다양한 언어에 능숙하며, 오픈 소스 분야에 많은 공헌을 했습니다. 나중에 참조할 수 있도록 기술 개발의 일부 문제를 공유하는 개발자 문서 스테이션입니다.
우편메소피아@프로톤메일.com