집 > 기사 > 데이터 베이스 > MySQL 학습을 위한 임시 테이블 요약

MySQL 학습을 위한 임시 테이블 요약

little bottle앞으로: 2019-04-29 12:03:382337검색

일반 사용자 데이터 테이블에 비해 MySQL/InnoDB의 임시 테이블은 누구에게나 많이 낯설을 것입니다. 또한, 서로 다른 임시 테이블이 생성되는 시기와 장소가 고정되어 있지 않아 미스터리가 더욱 증폭된다. 가장 파악하기 어려운 점은 임시 테이블이 파일을 먼저 생성한 다음 아무것도 하지 않고 파일을 삭제하여 읽고 쓰기 위한 핸들을 남겨두는 경우가 많다는 것입니다. 이는 사람들에게 용이 시작은 봤지만 끝은 보지 못했다는 인상을 줍니다. 이 기사는 다양한 버전의 MySQL에서 임시 테이블의 처리 방법을 자세히 분석하여 모든 사람에게 도움이 되기를 바랍니다.

개요

우리가 자주 이야기하는 임시 테이블에는 두 가지 유형이 있습니다. 하나는 실제로 사용자가 보낸 데이터를 저장하는 데 사용되는 테이블 읽기 및 쓰기 인터페이스입니다. 파일 시스템에는 시간 테이블이 있어야 하며, 다른 종류는 SQL 계산의 중간 과정에서 데이터를 저장하는 데 사용되는 임시 파일이어야 합니다. 읽기 및 쓰기 중에 삭제되어 작업을 위한 파일 핸들이 남을 수 있습니다.

관련 튜토리얼: mysql 비디오 튜토리얼

임시 테이블

임시 테이블은 디스크 임시 테이블과 메모리 임시 테이블로 나눌 수 있으며, 임시 파일은 디스크에만 존재하고 메모리에는 존재하지 않습니다. 구체적으로 임시 테이블의 메모리 형태로는 Memory 엔진과 Temptable 엔진이 있는데, 전자는 문자 종류(varchar, blob, text 종류)의 저장 방식이 실제 문자 수에 상관없이 고정 길이 공간을 사용한다는 점이다. 후자 운영자는 가변 길이 공간 저장소를 사용하므로 메모리의 저장 효율성이 향상되고 디스크 임시 테이블로 변환되는 대신 메모리에서 더 많은 데이터를 처리할 수 있습니다. 메모리 엔진은 5.6 초기부터 사용 가능했으며 Temptable은 8.0에 도입된 새로운 엔진입니다. 한편, 디스크 임시 테이블에는 세 가지 형태가 있는데, 하나는 MyISAM 테이블, 하나는 InnoDB 임시 테이블, 다른 하나는 Temptable 파일 맵 테이블입니다. 마지막 방법은 8.0에서 제공됩니다.

5.6 이하 버전에서는 디스크 임시 테이블이 데이터베이스 구성의 임시 디렉터리에 배치되고 디스크 임시 테이블의 undolog가 일반 테이블의 undo와 함께 배치됩니다(디스크 임시 테이블은 삭제된다는 점 참고) 데이터베이스가 재시작된 후) Crash Recovery를 통해 트랜잭션의 무결성을 보장하기 위해 Redolog가 필요하지 않으므로 Redolog를 작성할 필요는 없지만, Rollback을 지원해야 하기 때문에 undolog는 여전히 필요합니다.

MySQL 5.7 이후에는 디스크 임시 테이블의 데이터와 Undo가 분리되어 별도의 테이블스페이스 ibtmp1에 배치됩니다. 임시 테이블을 분리하는 이유는 주로 테이블 생성 및 삭제 시 메타데이터 유지에 따른 오버헤드를 줄이기 위한 것입니다.

MySQL 8.0 이후에는 디스크 임시 테이블의 데이터가 세션 임시 테이블스페이스 풀(#innodb_temp 디렉터리의 ibt 파일)에 별도로 배치되고, 임시 테이블의 undo는 글로벌 테이블스페이스 ibtmp1에 배치됩니다. 또 다른 큰 개선점은 8.0에서는 디스크 임시 테이블 데이터가 차지한 공간이 연결이 끊어진 후 운영 체제에 해제될 수 있다는 점이지만, 버전 5.7에서는 해제되기 전에 다시 시작해야 합니다.

현재 임시 테이블이 사용되는 상황은 두 가지입니다.

사용자가 명시적으로 임시 테이블을 생성합니다

이것은 사용자가 명시적으로 create temporary table 명령을 실행하여 생성한 테이블 유형입니다. 엔진은 명시적으로 지정되거나 기본 구성된 값(default_tmp_storage_engine)을 사용합니다. 메모리 사용량은 지정된 엔진의 메모리 관리 방법을 따릅니다. 예를 들어 InnoDB 테이블은 버퍼 풀에 캐시된 다음 더티 스레드를 통해 디스크 파일에 다시 기록됩니다. create temporary table创建的表，引擎的类型要么显式指定，要么使用默认配置的值(default_tmp_storage_engine)。内存使用就遵循指定引擎的内存管理方式，比如InnoDB的表会先缓存在Buffer Pool中，然后通过刷脏线程写回磁盘文件。

在5.6中，磁盘临时表位于tmpdir下，文件名类似#sql4d2b_8_0.ibd，其中#sql是固定的前缀，4d2b是进程号的十六进制表示，8是MySQL线程号的十六进制表示(show processlist中的id)，0是每个连接从0开始的递增值，ibd是innodb的磁盘临时表(通过参数default_tmp_storage_engine

5.6에서 디스크 임시 테이블은 tmpdir 아래에 있으며 파일 이름은 #sql4d2b_8_0.ibd와 유사합니다. 여기서 #sql는 고정 접두사, 입니다. >4d2b는 프로세스 번호의 16진수 표현이고, 8은 MySQL 스레드 번호(show processlist의 ID)의 16진수 표현이며, 0은 각 연결의 시작점 0부터 시작하여 값이 증가하는 ibd는 innodb의 디스크 임시 테이블입니다(default_tmp_storage_engine 매개변수로 제어됨). 5.6에서는 디스크 임시 테이블이 생성된 후 tmpdir 하위에 해당 frm 및 엔진 파일이 생성되며 파일 시스템 ls 명령을 통해 볼 수 있습니다. 연결이 종료되면 해당 파일이 자동으로 삭제됩니다. 따라서 5.6의 tmpdir에 유사한 형식의 파일 이름이 많이 있으면 파일 이름을 사용하여 임시 테이블을 사용하는 프로세스와 연결을 결정할 수 있습니다. 이 기술은 tmpdir 디렉토리가 차지하는 문제를 해결할 때 특히 적용 가능합니다. 많은 공간. 사용자가 명시적으로 생성한 이러한 종류의 임시 테이블은 자동으로 해제되며, 연결이 해제되면 해당 공간이 운영 체제에 다시 해제됩니다. 임시 테이블의 언두 로그는 언두 테이블스페이스에 저장되며, 일반 테이블의 언두와 함께 배치된다. 실행 취소 롤백 세그먼트를 사용하면 사용자가 만든 임시 테이블도 롤백을 지원할 수 있습니다. 🎜

5.7에서는 임시 디스크 테이블이 ibtmp 파일에 위치하며, ibtmp 파일 위치 및 크기 제어 방법은 innodb_temp_data_file_path 매개변수에 의해 제어됩니다. 명시적으로 생성된 테이블의 데이터 및 실행 취소는 ibtmp에 있습니다. 사용자 연결이 끊어지면 임시 테이블이 해제되지만 ibtmp 파일에 표시하는 것만으로는 공간이 운영 체제로 다시 해제되지 않습니다. 공간을 확보하려면 데이터베이스를 다시 시작해야 합니다. 그리고 한 가지 주의할 점은 5.6에서는 생성된 파일을 tmpdir 하위에 직접 볼 수 있지만, 5.7에서는 ibtmp 테이블스페이스에 생성되기 때문에 특정 테이블 파일을 볼 수 없다는 점이다. 보시려면 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 테이블을 보셔야 합니다. 여기에 테이블 이름이 있습니다. 네이밍 사양이 5.6과 유사해 공간을 많이 차지하는 연결도 빠르게 찾을 수 있다. innodb_temp_data_file_path控制。显式创建的表的数据和undo都在ibtmp里面。用户连接断开后，临时表会释放，但是仅仅是在ibtmp文件里面标记一下，空间是不会释放回操作系统的。如果要释放空间，需要重启数据库。另外，需要注意的一点是，5.6可以在tmpdir下直接看到创建的文件，但是5.7是创建在ibtmp这个表空间里面，因此是看不到具体的表文件的。如果需要查看，则需要查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表，里面有一列name，这里可以看到表名。命名规格与5.6的类似，因此也可以快速找到占用空间大的连接。

在8.0中，临时表的数据和undo被进一步分开，数据是存放在ibt文件中(由参数innodb_temp_tablespaces_dir控制)，undo依然存放在ibtmp文件中(依然由参数innodb_temp_data_file_path控制)。存放ibt文件的叫做Session临时表空间，存放undo的ibtmp叫做Global临时表空间。这里介绍一下这个存放数据的Session临时表空间。Session临时表空间，在磁盘上的表现是一组以ibt文件组成的文件池。启动的时候，数据库会在配置的目录下重新创建，关闭数据库的时候删除。启动的时候，默认会创建10个ibt文件，每个连接最多使用两个，一个给用户创建的临时表用，另外一个给下文描述的优化器创建的隐式临时表使用。当然只有在需要临时表的时候，才会创建，如果不需要，则不会占用ibt文件。当10个ibt都被使用完后，数据库会继续创建，最多创建四十万个。当连接释放时候，会自动把这个连接使用的ibt文件给释放，同时回收空间。如果要回收Global临时表空间，依然需要重启。但是由于已经把存放数据的文件分离出来，且其支持动态回收(即连接断开即释放空间)，所以5.7上困扰大家多时的空间占用问题，已经得到了很好的缓解。当然，还是有优化空间的，例如，空间需要在连接断开后，才能释放，而理论上，很多空间在某些SQL(如用户drop了某个显式创建的临时表)执行后，即可以释放。另外，如果需要查看表名，依然查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表。需要注意的是，8.0上，显式临时表不能是压缩表，而5.6和5.7可以。

优化器隐式创建临时表

这种临时表，是数据库为了辅助某些复杂SQL的执行而创建的辅助表，是否需要临时表，一般都是由优化器决定。与用户显式创建的临时表直接创建磁盘文件不同，如果需要优化器觉得SQL需要临时表辅助，会先使用内存临时表，如果超过配置的内存(min(tmp_table_size, max_heap_table_siz))，就会转化成磁盘临时表，这种磁盘临时表就类似用户显式创建的，引擎类型通过参数internal_tmp_disk_storage_engine控制。一般稍微复杂一点的查询，包括且不限于order by, group by, distinct等，都会用到这种隐式创建的临时表。用户可以通过explain命令，在Extra列中，看是否有Using temporary这样的字样，如果有，就肯定要用临时表。

在5.6中，隐式临时表依然在tmpdir下，在复杂SQL执行的过程中，就能看到这临时表，一旦执行结束，就被删除。值得注意的是，5.6中，这种隐式创建的临时表，只能用MyISAM引擎，即没有internal_tmp_disk_storage_engine这个参数可以控制。所以，当我们的系统中只有innodb表时，也会看到MyISAM的某些指标在变动，这种情况下，一般都是隐式临时表的原因。

在5.7中，隐式临时表是创建在ibtmp文件中的，SQL结束后，会标记删除，但是空间依然不会返还给操作系统，如果需要返还，则需要重启数据库。另外，5.7支持参数internal_tmp_disk_storage_engine

8.0에서는 임시 테이블의 데이터와 실행 취소가 더 분리되어 있습니다. 데이터는 ibt 파일(innodb_temp_tablespaces_dir 매개변수로 제어됨)에 저장되며, 실행 취소는 여전히 ibtmp 파일에 저장됩니다. (여전히 innodb_temp_data_file_path 매개변수로 제어됨) IBT 파일을 저장하는 것을 세션 임시 테이블스페이스라고 하고, Undo를 저장하는 ibtmp를 글로벌 임시 테이블스페이스라고 합니다. 데이터를 저장하는 Session 임시 테이블스페이스에 대해 소개합니다. 세션 임시 테이블스페이스는 iBT 파일로 구성된 파일 풀 세트로 디스크에 나타납니다. 시작 시 구성된 디렉터리에 데이터베이스가 다시 생성되고 데이터베이스가 닫히면 삭제됩니다. 시작 시 기본적으로 10개의 ibt 파일이 생성되며, 각 연결은 최대 2개를 사용합니다. 하나는 사용자가 생성한 임시 테이블용이고 다른 하나는 아래 설명된 최적화 프로그램이 생성한 암시적 임시 테이블용입니다. 물론, 임시 테이블은 필요할 때만 생성됩니다. 필요하지 않으면 ibt 파일이 사용되지 않습니다. 10개의 iBT가 모두 사용되면 데이터베이스는 계속 생성되며 최대 400,000개까지 생성됩니다. 연결이 해제되면 연결에 사용된 iBT 파일이 자동으로 해제되고 공간이 회수됩니다. 글로벌 임시 테이블스페이스를 재활용하려는 경우에도 다시 시작해야 합니다. 하지만 데이터를 저장하는 파일이 분리되어 동적 재활용(즉, 연결이 끊어지면 공간이 해제되는 것)을 지원하기 때문에, 5.7에서는 오랫동안 모두를 괴롭혔던 공간 점유 문제가 크게 완화됐다. 물론 아직 최적화의 여지가 있습니다. 예를 들어 연결이 끊어진 후 공간을 해제해야 하는 경우 이론적으로는 특정 SQL(예: 사용자가 명시적으로 생성된 임시 테이블을 삭제한 경우) 후에 많은 공간이 해제될 수 있습니다. 해제가 실행됩니다. 추가적으로 테이블 이름을 확인해야 한다면 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 테이블을 확인해보세요. 8.0에서는 명시적 임시 테이블이 압축 테이블이 될 수 없지만 5.6과 5.7에서는 압축 테이블이 가능하다는 점에 유의해야 합니다. 🎜

최적화 프로그램이 암시적으로 임시 테이블을 생성합니다

🎜이런 종류의 임시 테이블은 특정 복잡한 SQL 실행을 지원하기 위해 데이터베이스에서 생성된 보조 테이블은 일반적으로 최적화 프로그램에 의해 결정됩니다. 사용자가 명시적으로 생성한 임시 테이블에 대한 디스크 파일을 직접 생성하는 것과 달리, 최적화 프로그램은 SQL에 임시 테이블 지원이 필요하다고 판단하면 구성된 메모리(min(tmp_table_size, max_heap_table_siz))를 초과하는 경우 먼저 메모리 임시 테이블을 사용합니다. , 이는 디스크 임시 테이블로 변환됩니다. 이러한 종류의 디스크 임시 테이블은 사용자가 명시적으로 생성한 것과 유사합니다. 엔진 유형은 internal_tmp_disk_storage_engine 매개변수에 의해 제어됩니다. 일반적으로 정렬 기준, 그룹 기준, 개별 등을 포함하되 이에 국한되지 않는 약간 더 복잡한 쿼리에서는 암시적으로 생성된 이 임시 테이블을 사용합니다. 사용자는 explain 명령을 사용하여 Extra 열에 "Using temporary"와 같은 단어가 있는지 확인할 수 있습니다. 그렇다면 임시 테이블을 사용해야 합니다. 🎜🎜5.6에서는 암시적 임시 테이블이 여전히 tmpdir에 있습니다. 복잡한 SQL을 실행하는 동안 이 임시 테이블은 실행이 완료되면 삭제됩니다. 5.6에서는 암시적으로 생성된 이 임시 테이블이 MyISAM 엔진만 사용할 수 있다는 점에 주목할 가치가 있습니다. 즉, 제어할 internal_tmp_disk_storage_engine 매개변수가 없습니다. 따라서 시스템에 innodb 테이블만 있는 경우 MyISAM 변경에 대한 일부 표시도 볼 수 있습니다. 이 경우 일반적으로 암시적 임시 테이블로 인해 발생합니다. 🎜🎜5.7에서는 ibtmp 파일에 암시적 임시 테이블이 생성됩니다. SQL이 완료된 후 삭제 표시가 되지만, 반환해야 하는 경우 해당 공간은 여전히 운영 체제에 반환되지 않습니다. 데이터베이스를 다시 시작해야 합니다. 또한 5.7에서는 internal_tmp_disk_storage_engine 매개변수를 지원하며 사용자는 InnoDB 또는 MYISAM 테이블을 디스크 임시 테이블로 선택할 수 있습니다. 🎜

8.0에서는 세션 임시 테이블 공간에 암시적 임시 테이블이 생성됩니다. 즉, 사용자가 명시적으로 생성한 임시 테이블의 데이터와 함께 배치됩니다. 연결에 처음으로 암시적 임시 테이블이 필요한 경우 데이터베이스는 연결이 해제될 때까지 연결에 사용할 ibt 파일로 구성된 풀에서 하나를 가져옵니다. 위에서 언급했듯이 8.0에서는 사용자가 명시적으로 생성한 임시 테이블에도 사용할 풀에서 ibt가 할당될 것이라고 언급했습니다. 각 연결은 임시 테이블을 저장하기 위해 최대 2개의 ibt 파일을 사용합니다. INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES를 쿼리하여 ibt 파일의 위치를 확인할 수 있습니다. 이 테이블에서 각 ibt 파일은 하나의 행이며 현재 시스템에는 여러 ibt 파일에 대한 여러 행이 있습니다. ID라는 컬럼이 있는데, 이 컬럼이 0이면 이 iBT를 사용하지 않는다는 뜻이고, 0이 아니면 이 ID를 가진 커넥션을 사용한다는 뜻이다. 이는 process_id 8과의 연결이 이 iBT 파일을 사용하고 있음을 의미합니다. 또한 목적 열이 있습니다. INTRINSIC 값은 암시적 임시 테이블이 이 iBT를 사용하고 있음을 나타내고 USER는 표시된 임시 테이블이 사용 중임을 나타냅니다. 또한 현재 크기를 나타내는 열 크기도 있습니다. 사용자는 이 테이블을 쿼리하여 전체 데이터베이스에서 임시 테이블의 사용량을 확인할 수 있으므로 매우 편리합니다. INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES来确定ibt文件的去向。这个表中，每个ibt文件是一行，当前系统中有几个ibt文件就有几行。有一列叫做ID，如果此列为0，表示此ibt没有被使用，如果非0，表示被此ID的连接在用，比如ID为8，则表示process_id为8的连接在用这个ibt文件。另外，还有一列purpose，值为INTRINSIC表示是隐式临时表在用这个ibt，USER则表示是显示临时表在用。此外，还有一列size，表示当前的大小。用户可以查询这个表来确定整个数据库临时表的使用情况，十分方便。

在5.6和5.7中，内存临时表只能使用Memory引擎，到了8.0，多了一种Temptable引擎的选择。Temptable在存储格式有采用了变长存储，可以节省存储空间，进一步提高内存使用率，减少转换成磁盘临时表的次数。如果设置的磁盘临时表是InnoDB或者MYISAM，则需要一个转换拷贝的消耗。为了尽可能减少消耗，Temptable提出了一种overflow机制，即如果内存临时表超过配置大小，则使用磁盘空间map的方式，即打开一个文件，然后删除，留一个句柄进行读写操作。读写文件格式和内存中格式一样，这样就略过了转换这一步，进一步提高性能。注意，这个功能是在还没发布的8.0.16版本中才有的，因为还看不到代码，只能通过文档猜测其实现。在8.0.16中，参数internal_tmp_disk_storage_engine已经被去掉，磁盘临时表只能使用InnoDB形式或者TempTable的这种overflow形式。从文档中，我们似乎看出官方比较推荐使用TempTable这个新的引擎。具体性能提升情况，还需要等代码发布后，测试过才能得出结论。

临时文件

相比临时表，临时文件对大家可能更加陌生，临时文件更多的被使用在缓存数据，排序数据的场景中。一般情况下，被缓存或者排序的数据，首先放在内存中，如果内存放不下，才会使用磁盘临时文件的方式。临时文件的使用方式与一般的表也不太一样，一般的表创建完后，就开始读写数据，使用完后，才把文件删除，但是临时文件的使用方式不一样，在创建完后(使用mkstemp系统函数)，马上调用unlink删除文件，但是不close文件，后续使用原来的句柄操作文件。这样的好处是，当进程异常crash，不会有临时文件因为没被删除而残留，但是坏处也是明显的，我们在文件系统上使用ls命令就看不到这个文件，需要使用lsof +L1来查看这种deleted属性的文件。

目前，我们主要在一下场景使用临时文件：

DDL中的临时文件

在做online DDL的过程中，很多操作需要对原表进行重建，对表重建前，需要对各种二级索引排序，而大量数据的排序，不太可能在内存中完成，需要依赖外部排序算法，MySQL使用了归并排序。这个过程中就需要创建临时文件。一般需要的空间大小与原表差不多。但是在使用完之后，会马上清理，所以在做DDL的时候，需要保留出足够的空间。用户可以通过指定innodb_tmpdir来指定这种排序文件的路径。这个参数可以动态修改，一般把他设置在有足够磁盘空间的路径上。临时文件的名字一般是类似ibXXXXXX，其中ib是固定前缀，XXXXXX是大小写字母以及数字的随机组合。

在做online DDL中，我们是允许用户对原表做DML操作的，即增删改查。我们不能直接插入原表中，因此需要一个地方记录对原表的修改操作，在DDL结束后，再应用在新表上。这个记录的地方就是online log，当然如果改动少的话，直接存在内存里(参数innodb_sort_buffer_size可控制，同时这个参数也控制online log每个读写块的大小)面即可。这个onlinelog也是用临时文件存，创建在innodb_tmpdir，最大大小为参数innodb_online_alter_log_max_size

5.6 및 5.7에서는 메모리 임시 테이블이 메모리 엔진만 사용할 수 있습니다. 8.0에서는 Temptable 엔진을 추가로 선택할 수 있습니다. Temptable은 저장 형식에 가변 길이 저장소를 사용하므로 저장 공간을 절약하고, 메모리 사용량을 더욱 향상시키며, 디스크 임시 테이블로의 변환 횟수를 줄일 수 있습니다. 디스크 임시 테이블 세트가 InnoDB 또는 MYISAM인 경우 변환 복사본이 필요합니다. 소비를 최대한 줄이기 위해 Temptable에서는 메모리 임시 테이블이 구성된 크기를 초과하는 경우 디스크 공간 맵 방법을 사용합니다. 읽기 및 쓰기 작업을 위한 핸들입니다. 파일을 읽고 쓰는 형식이 메모리에 있는 형식과 동일하므로 변환 단계를 건너뛰어 성능이 더욱 향상됩니다. 이 기능은 아직 출시되지 않은 버전 8.0.16에서만 사용할 수 있습니다. 코드를 아직 볼 수 없기 때문에 문서를 통해서만 구현을 추측할 수 있습니다. 8.0.16에서는 internal_tmp_disk_storage_engine 매개변수가 제거되었으며, 디스크 임시 테이블은 InnoDB 형식 또는 TempTable의 오버플로 형식만 사용할 수 있습니다. 문서에 따르면 공식적인 권장 사항은 새로운 엔진 TempTable을 사용하는 것으로 보입니다. 결론을 내리기 전에 코드가 출시된 후 구체적인 성능 개선 사항을 테스트해야 합니다. 🎜

임시 파일

🎜임시 테이블에 비해 임시 파일은 누구에게나 더 낯설 수 있고 임시 파일은 파일이 더 많습니다. 주로 데이터 캐싱 및 데이터 정렬 시나리오에 사용됩니다. 일반적인 상황에서는 캐시되거나 정렬된 데이터가 먼저 메모리에 저장됩니다. 메모리를 저장할 수 없으면 디스크의 임시 파일이 사용됩니다. 임시 파일의 용도는 일반 테이블 생성 후 데이터 읽기 및 쓰기를 시작하며, 사용 후 삭제됩니다. ( mkstemp 시스템 기능 사용) 즉시 unlink를 호출하여 파일을 삭제하되 파일을 닫지 말고 원래 핸들을 사용하여 파일을 작동하십시오. 이것의 장점은 프로세스가 비정상적으로 충돌할 때 삭제되지 않았기 때문에 임시 파일이 남지 않는다는 점이지만, 파일 시스템에서 ls 명령을 사용할 때 파일을 볼 수 없다는 단점도 있습니다. 이 삭제된 속성이 있는 파일을 보려면 lsof +L1을 사용하세요. 🎜🎜현재 우리는 주로 다음 시나리오에서 임시 파일을 사용합니다: 🎜

DDL의 임시 파일 h4 >🎜온라인 DDL을 수행하는 과정에서 많은 작업에서 원본 테이블을 재구성해야 합니다. 테이블을 재구성하기 전에 다양한 보조 인덱스를 정렬해야 하며 대량의 데이터 정렬은 메모리에서 완료될 가능성이 낮습니다. 외부 정렬 알고리즘, MySQL은 병합 정렬을 사용합니다. 이 과정에서 임시 파일을 생성해야 합니다. 일반적으로 필요한 공간은 원본 테이블과 거의 같습니다. 다만, 사용 후 바로 정리되기 때문에 DDL을 할 때에는 충분한 공간을 확보해두셔야 합니다. 사용자는 innodb_tmpdir을 지정하여 이 정렬 파일의 경로를 지정할 수 있습니다. 이 매개변수는 동적으로 수정될 수 있으며 일반적으로 디스크 공간이 충분한 경로에 설정됩니다. 임시 파일의 이름은 일반적으로 `ibXXXXXX`와 유사합니다. 여기서 `ib`는 고정 접두사이고 `XXXXXX`는 대문자와 소문자의 무작위 조합입니다. 문자와 숫자. 🎜🎜온라인 DDL을 수행할 때 사용자는 원본 테이블에 대해 추가, 삭제, 수정, 쿼리 등의 DML 작업을 수행할 수 있습니다. 원본 테이블에 직접 삽입할 수 없기 때문에 원본 테이블에 대한 수정 작업을 기록하고 DDL이 완료된 후 새 테이블에 적용할 수 있는 장소가 필요합니다. 이것이 기록되는 곳은 온라인 로그이다. 물론, 변경 사항이 거의 없다면 메모리에 직접 저장할 수도 있다. (`innodb_sort_buffer_size` 매개변수를 조절 가능하며, 이 매개변수도 크기를 조절한다.) 온라인 로그의 각 읽기 및 쓰기 블록). 이 온라인 로그는 innodb_tmpdir에 생성된 임시 파일에도 저장됩니다. 최대 크기는 `innodb_online_alter_log_max_size` 매개변수에 의해 제어됩니다. 이 크기를 초과하면 DDL이 실패합니다. 임시 파일의 이름도 위에서 언급한 정렬 임시 파일의 이름과 유사합니다. 🎜

온라인 DDL의 마지막 단계에서는 모든 정렬된 파일과 생성된 DML을 중간 파일에 적용해야 합니다. 중간 파일 이름은 #sql-ib53-522550444.ibd와 유사합니다. >, 여기서 #sql-ib는 고정 접두사이고, 53은 InnoDB 계층의 테이블 ID이며, 522550444는 무작위로 생성된 숫자입니다. . 동시에 frm 파일도 서버 계층에서 생성됩니다(8.0에서는 사용할 수 없음). 파일 이름은 #sql-4d2b_2a.frm과 유사합니다. 여기서 #sql는 고정 접두사이고 <code>4d2b는 프로세스 번호의 16진수 표현이고, 2a는 스레드 번호(show processlist의 ID)의 16진수 표현입니다. 따라서 이 명명 규칙을 사용하여 어느 스레드가 DDL을 수행하고 있는지 찾을 수도 있습니다. 여기서 주목해야 할 점은 여기서 언급한 중간 파일은 실제로 위에서 언급한 임시 파일이 아닌 임시 테이블이라는 점입니다. 이러한 중간 파일은 ls를 통해 볼 수 있습니다. DDL의 마지막 단계에서 두 개의 임시 파일 이름이 원래 테이블 이름으로 다시 변경됩니다. 이 기능으로 인해 데이터베이스가 중간에 충돌할 경우 쓸모없는 잔여 파일이 디스크에 남을 수 있습니다. 이 경우 먼저 frm 파일의 이름을 ibd 파일과 동일한 이름으로 바꾼 다음 DROP TABLE#mysql50##sql-ib53-522550444`를 사용하여 나머지 파일을 정리할 수 있습니다. drop 명령을 사용하지 않고 ibd 파일을 직접 삭제하면 데이터 사전에 여전히 잔여 정보가 남아 있을 수 있으며 이는 그다지 우아하지 않습니다. 물론 8.0에서는 원자 데이터 사전을 사용하기 때문에 이러한 잔여 파일은 나타나지 않습니다. #sql-ib53-522550444.ibd，其中#sql-ib是固定的前缀，53是InnoDB层的table id，522550444是随机生成的数字。同时，在server层也会生成一个frm文件(8.0中没有)，文件名类似#sql-4d2b_2a.frm，其中#sql是固定前缀，4d2b是进程号的十六进制表示，2a是线程号的十六进制表示(show processlist中的id)。因此我们也可以通过这个命名规则来找到哪个线程在做DDL。这里需要注意一点，这里说的中间文件，其实算是一个临时表，并不是上文说中临时文件，这些中间文件可以通过ls来查看。当在DDL中的最后一步，会把这两个临时文件命名回原来的表名。正因为这个特性，所以当数据库中途crash的时候，可能会在磁盘上留下残余无用的文件。遇到这种情况，可以先把frm文件重命名成与ibd文件一样的名字，然后使用DROP TABLE#mysql50##sql-ib53-522550444`来清理残余的文件。注意，如果不用drop命令，直接删除ibd文件，可能会导致数据字典里面依然有残余的信息，做法不太优雅。当然，在8.0中，由于使用了原子的数据字典，就不会出现这种残余文件了。

BinLog中的缓存操作

BinLog只有在事务提交的时候才会写入到文件中，在没提交前，会先放在内存中(由参数binlog_cache_size控制)，如果内存放慢了，就会创建临时文件，使用方法也是先通过mkstemp创建，然后直接unlink，留一个句柄读写。临时文件名类似MLXXXXXX，其中ML是固定前缀，XXXXXX是大小写字母以及数字的随机组合。单个事务的BinLog太大，可能会导致整个BinLog的大小也过大，从而影响同步，因此我们需要尽可能控制事务大小。

优化创建的临时文件

有些操作，除了在引擎层需要依赖隐式临时表来辅助复杂SQL的计算，在Server层，也会创建临时文件来辅助，比如order by操作，会调用filesort函数。这个函数也会先使用内存(sort_buffer_size)排序，如果不够，就会创建一个临时文件，辅助排序。文件名类似MYXXXXXX，其中MY是固定前缀，XXXXXX是大小写字母以及数字的随机组合。

Load data中用的临时文件

在BinLog复制中，如果在主库上使用了Load Data命令，即从文件中导数据，数据库会把整个文件写入到RelayLog中，然后传到备库，备库解析RelayLog，从中抽取出对应的Load文件，然后在备库上应用。备库上这个文件存储的位置由参数slave_load_tmpdir

BinLog의 캐시 작업

BinLog는 트랜잭션이 제출될 때만 파일에 기록됩니다. 제출 전에는 매개변수 `에 의해 메모리에 저장됩니다. binlog_cache_size` 제어), 메모리 속도가 느려지면 임시 파일이 생성됩니다. 사용 방법은 먼저 mkstemp를 통해 생성한 다음 직접 링크를 해제하고 읽기 및 쓰기용 핸들을 남겨 두는 것입니다. 임시 파일 이름은 `MLXXXXXX`와 유사합니다. 여기서 `ML`은 고정 접두사이고 `XXXXXX`는 대문자, 소문자 및 숫자의 무작위 조합입니다. 단일 트랜잭션의 BinLog가 너무 크면 전체 BinLog 크기가 너무 커져 동기화에 영향을 미칠 수 있으므로 트랜잭션 크기를 최대한 제어해야 합니다.

최적화로 생성된 임시 파일

🎜🎜일부 작업은 복잡한 SQL 계산을 지원하기 위해 엔진 계층의 암시적 임시 테이블에 의존하는 것 외에도 임시 파일도 생성합니다. 서버 계층의 파일 작업별 정렬과 같은 파일을 지원하기 위해 filesort 함수가 호출됩니다. 이 함수는 먼저 메모리(sort_buffer_size)를 사용하여 정렬이 충분하지 않은 경우 정렬을 지원하기 위해 임시 파일을 생성합니다. 파일 이름은 MYXXXXXX와 유사합니다. 여기서 MY는 고정 접두사이고 XXXXXX는 대문자, 소문자 및 숫자의 무작위 조합입니다. 🎜

🎜데이터 로드에 사용되는 임시 파일🎜🎜🎜BinLog 복제에서는 메인 라이브러리에서 데이터 로드 명령을 사용하면 데이터베이스에서 데이터를 가져옵니다. 전체 파일을 RelayLog에 기록한 다음 이를 대기 데이터베이스로 전송합니다. 대기 데이터베이스는 RelayLog를 구문 분석하고 해당 로드 파일을 추출한 다음 이를 대기 데이터베이스에 적용합니다. 대기 데이터베이스에서 이 파일의 저장 위치는 `slave_load_tmpdir` 매개변수에 의해 제어됩니다. 문서에서는 이 디렉터리를 물리적 머신의 메모리 디렉터리나 재시작 후 삭제되는 디렉터리에 구성하지 말 것을 권고하고 있습니다. 복제는 이 파일에 의존하기 때문에 실수로 삭제하면 복제가 중단됩니다. 🎜🎜🎜기타🎜🎜🎜위에 언급된 여러 장소 외에도 임시 파일이 사용되는 다른 장소도 여러 군데 있습니다. 🎜

InnoDB 계층에서는 시작 중에 마지막 외래 키 또는 고유 키 오류, 마지막 교착 상태 정보, 마지막 innodb 상태 정보를 저장하기 위해 여러 개의 임시 파일이 생성됩니다. 메모리 대신 임시 파일을 사용하는 이유는 이러한 인디케이터 작성으로 인해 메모리 사용량이 변동되지 않기 때문입니다.

서버 계층에서 파티션 테이블로 show create table을 사용하면 임시 파일이 사용됩니다. 또한 MYISAM 테이블에서 내부적으로 정렬할 때도 임시 파일이 사용됩니다.

요약 및 제안

MySQL의 임시 테이블과 임시 파일은 실제로 많은 모듈이 포함된 비교적 복잡한 주제이며, 발생 시점을 파악하기 어렵기 때문에 일반 테이블보다 문제 해결이 훨씬 어렵습니다. 온라인에서 발생할 수 있는 어려운 문제를 찾기 위해 독자는 코드를 주의 깊게 연구하는 것이 좋습니다.

위 내용은 MySQL 학습을 위한 임시 테이블 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：

이 기사는 cnblogs.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제

이전 기사："테이블에서 삭제" 문은 무엇을 의미합니까?다음 기사："테이블에서 삭제" 문은 무엇을 의미합니까?