mysql 임시 테이블의 용도는 무엇입니까?

mysql 임시 테이블의 역할: 1. 사용자가 직접 만든 임시 테이블은 임시 데이터를 저장하는 데 사용됩니다. 2. 사용자는 복잡한 SQL을 실행할 때 임시 테이블을 사용하여 그룹화, 정렬, 중복 제거 및 기타 작업을 수행할 수 있습니다. default , 데이터베이스 연결을 끊으면 임시 테이블이 자동으로 삭제됩니다.

이 튜토리얼의 운영 환경: Windows 10 시스템, MySQL 버전 5.7, Dell G3 컴퓨터.

mysql 임시 테이블의 용도는 무엇입니까?

MySQL 임시 테이블의 역할

MySQL 임시 테이블은 사용자가 임시 데이터를 저장하기 위해 직접 만든 임시 테이블, 내부적으로 복잡한 SQL을 실행할 때 MySQL은 그룹화, 정렬, 중복 제거 등의 작업을 수행하기 위해 임시 테이블을 사용해야 합니다. 다음은 MySQL 임시 테이블의 몇 가지 개념, 분류 및 일반적인 문제를 정리합니다.

MySQL 임시 테이블 유형

1. 임시 테이블 생성 구문을 통해 생성된 임시 테이블인 외부 임시 테이블은 메모리, innodb, myisam 등으로 스토리지 엔진을 지정할 수 있습니다. 세션 종료 후 삭제됩니다. 임시 테이블이 비임시 테이블과 동시에 존재하는 경우 비임시 테이블은 표시되지 않습니다. show tables 명령은 임시 테이블 정보를 표시하지 않습니다.
information_schema.INNODB_TEMP_TABLE_INFO 시스템 테이블을 통해 외부 임시 테이블에 대한 관련 정보를 볼 수 있습니다. 이 부분은 아직까지 비교적 드물게 사용됩니다.

2. 일반적으로 내부 임시 테이블은 group by, order by, discover, Union 등과 같은 복잡한 SQL을 실행할 때 사용됩니다. 실행 계획에 Using temporary가 포함되어 있으면 Undo Rollback도 있지만 부족할 때 사용됩니다. 공간이 확보되면 MySQL은 작업 완료를 돕기 위해 내부적으로 자동으로 생성된 임시 테이블을 사용합니다.

MySQL 임시 테이블 관련 매개변수

1.max_heap_table_size: 사용자가 생성한 메모리 테이블의 최대값. 또한 tmp_table_size와 함께 사용되어 메모리 내 내부 임시 테이블의 크기를 제한합니다.
2.tmp_table_size: 메모리 내 내부 임시 테이블의 최대값은 max_heap_table_size 매개변수와 함께 결정되며, 둘 중 최소값을 취합니다. 임시 테이블이 이 값을 초과하면 메모리에서 디스크로 이동됩니다.
3.innodb_tmpdir: max_tmp_tables

4.default_tmp_storage_engine: 외부 임시 테이블(임시 테이블 생성으로 생성된 테이블)을 위한 기본 스토리지 엔진입니다.

5.innodb_temp_data_file_path: innodb 엔진 아래의 임시 파일 속성. innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

6.Internal_tmp_disk_storage_engine: 디스크의 내부 임시 테이블 스토리지 엔진으로 제한하는 것이 좋습니다. 선택 값은 myisam 또는 innodb입니다. innodb 테이블을 사용할 때 임시 테이블에 열이 너무 많거나 행 크기가 제한을 초과하는 경우와 같은 일부 시나리오에서는 "행 크기가 너무 크거나 열이 너무 많습니다"라는 오류가 발생할 수 있습니다. 임시 테이블의 innodb 엔진을 myisam으로 다시 변경해야 합니다. tmpdir: 임시 테이블 디렉터리. 임시 테이블의 크기가 특정 임계값을 초과하면 메모리에서 디스크로 전송됩니다.
7. tmpdir 변수는 디스크에서 임시 테이블이 있는 디렉터리를 나타냅니다.

MySQL 임시 테이블 관련 상태 변수

1.Created_tmp_disk_tables: SQL 문 실행 시 디스크에 MySQL이 생성한 내부 임시 테이블의 개수 이 값이 클 경우 가능한 이유는 최대 메모리 값 때문입니다. 임시 테이블에 할당된 양이 상대적으로 크거나 SQL에 정렬, 그룹화, 중복 제거 및 기타 작업이 많아 SQL을 최적화해야 합니다.

2.Created_tmp_files: 생성된 임시 테이블 수

3.Created_tmp_tables: SQL 문 실행 시 MySQL이 생성한 내부 임시 테이블 수.

4.Slave_open_temp_tables 문이나 혼합 모드는 사용 중에만 볼 수 있습니다.
slave_open_temp_tables의 값은 복제를 통해 현재 슬레이브가 생성한 임시 테이블 수를 나타냅니다. Binlog_format은 명령문에서만 유효하며 혼합됩니다.
참고: 중지 슬레이브는 쓸모가 없으며 기본 데이터베이스를 수동으로 삭제하거나 세션을 종료해야 합니다. .
다음은 라이브러리의 binlog 기록 정보입니다.

MySQL 임시 테이블 주의사항

1. MySQL 임시 테이블은 사용 가능한 디스크 공간을 감소시킬 수 있습니다.
MySQL 버전 5.7 이전에는 myisam 임시 테이블의 스토리지 엔진이 SQL 실행이 완료된 후 자동으로 임시 테이블을 삭제합니다. 하지만 버전 5.7부터는 임시 테이블의 기본 스토리지 엔진이 innodb로 변경되어 어느 정도 성능이 향상되었으나 innodb 엔진의 임시 테이블이 ibtmp1 테이블스페이스를 공유하기 때문에 여러 세션이 임시 임시 테이블을 생성하게 된다. 동시에 높은 동시성 테이블에서는 테이블 공간이 매우 커지고 MySQL을 다시 시작하지 않으면 동적으로 줄일 수 없으며 해제될 수 없습니다.

임시 테이블스페이스의 최대값(예: 10G)을 다음과 같이 설정할 수 있습니다.
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
임시 테이블스페이스가 최대값인 10G에 도달하면 SQL 실행 정상적인 실행 적용에 영향을 미치는 오류를 보고합니다.
과도한 임시 테이블 공간 문제의 경우 일반적으로 다음과 같은 다른 방법으로 문제를 해결할 수 있습니다.
임시 테이블의 스토리지 엔진을 myisam으로 설정하면 일부 성능 문제가 발생할 수 있지만 디스크 공간이 발생하지는 않습니다. 문제.

2. SQL 문:
(1) 적절한 인덱스 추가
(2) where 조건에서 더 많은 데이터 필터링
(3) SQL 다시 작성 및 실행 계획 최적화
(4) 임시 테이블을 사용해야 하는 경우 동시성 줄여야합니다. SSD 하드 드라이브를 사용하는 것이 좋습니다.

3.undo 관련
1) innodb_rollback_segments 구성 옵션을 사용하여 롤백 세그먼트 수를 정의합니다. 기본 설정은 최대값이기도 한 128입니다. 하나의 롤백 세그먼트는 항상 시스템 테이블스페이스에 할당되고, 32개의 롤백 세그먼트는 임시 테이블스페이스(ibtmp1)용으로 예약됩니다. 따라서 테이블스페이스를 실행 취소하기 위해 롤백 세그먼트를 할당하려면 innodb_rollback_segments를 33보다 큰 값으로 설정하십시오. 별도의 Undo 테이블스페이스를 구성하면 시스템 테이블스페이스의 롤백 세그먼트가 비활성화됩니다.

즉, 롤백 세그먼트가 128을 초과하면 긴급 구호를 위한 임시 테이블이 필요하다는 것입니다.

tablespace -> segment -> extent(64个page，1M) -> page（16kb）

2) truncate undo
innodb_undo_log_truncate가 트리거되면 undo 테이블스페이스 자르기 작업은 innodb_log_group_home_dir에 정의된 서버 로그 디렉터리에 임시 undo_space_number_trunc.log 파일을 생성합니다. 자르기 작업 중에 시스템 오류가 발생하면 임시 로그 파일을 통해 시작 프로세스가 잘린 실행 취소 테이블스페이스를 인식하고 작업을 계속할 수 있습니다.

4.binlog 캐시 관련
바이너리 로그 캐시를 사용하면 값이 binlog_cache_size에 설정된 값에 도달하고, 임시 파일을 사용하여 트랜잭션의 변경 사항에서 트랜잭션 수를 저장합니다. Binlog_stmt_cache_disk_use 상태 변수를 통해 별도로 추적할 수 있습니다.

요약

1. 위의 이해로 볼 때 MySQL 임시 테이블은 일일 모니터링 및 최적화에서 피할 수 없습니다.
2. 또한, 소량의 정보를 임시로 저장하기 위한 중간 테이블 등 비즈니스 구현에도 적절하게 사용할 수 있습니다.
3. 복제 과정에서 binlog_format이 ROW 모드일 경우 임시 테이블과 관련된 binlog 로그가 기록되지 않습니다(drop 명령 제외).
【관련 권장 사항: mysql 비디오 튜토리얼】

위 내용은 mysql 임시 테이블의 용도는 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!