MySQL 임시 테이블의 이름이 중복되는 이유는 무엇입니까?

오늘은 임시 테이블의 특징은 무엇이며 어떤 시나리오에 적합한가?라는 질문부터 시작하겠습니다.

먼저 오해하기 쉬운 문제를 명확히 하는 데 도움을 드리고 싶습니다. 어떤 사람들은 임시 테이블을 메모리 테이블이라고 생각할 수도 있습니다. 그러나 이 두 개념은 완전히 다릅니다.

• Memory table은 Memory 엔진을 사용하는 테이블을 의미합니다. 테이블 생성 구문은 create table …engine=memory입니다. **이러한 종류의 테이블의 데이터는 메모리에 저장되며 시스템이 다시 시작되면 지워지지만 테이블 구조는 여전히 존재합니다. **다른 기능에서 "이상하게" 보이는 이 두 가지 기능을 제외하면 일반적인 표입니다.

• 임시 테이블, 다양한 엔진 종류 사용 가능. InnoDB 엔진이나 MyISAM 엔진의 임시 테이블을 사용하는 경우 쓰기 시 데이터가 디스크에 기록됩니다. 물론 임시 테이블도 메모리 엔진을 사용할 수 있습니다.

메모리 테이블과 임시 테이블의 차이점을 명확히 한 후, 임시 테이블의 특징을 살펴보겠습니다.

임시 테이블의 특징

이해를 돕기 위해 다음 작업 순서를 살펴보겠습니다.

보시다시피 임시 테이블은 다음과 같은 특징을 사용합니다.

• 생성 구문 테이블은 임시 테이블 만들기 …입니다.

• 다른 스레드는 특정 세션에서 생성된 임시 테이블에 액세스할 수 없으며 해당 세션에서만 볼 수 있습니다. 따라서 그림에서 세션 A가 생성한 임시 테이블 t는 세션 B에 보이지 않습니다.

• 임시 테이블은 일반 테이블과 동일한 이름을 가질 수 있습니다.

• 세션 A에 같은 이름의 임시 테이블과 일반 테이블이 있는 경우 showcreate 문과 추가, 삭제, 수정, 쿼리 문으로 임시 테이블에 액세스합니다.

• showtables 명령은 임시 테이블을 표시하지 않습니다.

임시 테이블은 해당 테이블을 생성한 세션에서만 접근이 가능하므로 세션이 종료되면 임시 테이블은 자동으로 삭제됩니다.

이전 기사의 조인 최적화 시나리오는 임시 테이블에 이 기능이 있기 때문에 임시 테이블을 사용하는 데 특히 적합합니다. 왜? 그 이유는 주로 다음 두 가지 측면을 포함합니다.

• 다른 세션의 임시 테이블 이름을 변경할 수 있습니다 동시에 조인 최적화를 실행하는 여러 세션이 있는 경우 테이블 생성 실패에 대해 걱정할 필요가 없습니다. 반복되는 테이블 이름에.

• 데이터 삭제에 대해 걱정할 필요가 없습니다. 일반 테이블을 사용하는 경우, 프로세스 수행 중 클라이언트 연결이 비정상적으로 종료되거나, 데이터베이스가 비정상적으로 재시작되는 경우 중간 프로세스에서 생성된 데이터 테이블을 별도로 정리해야 한다. 임시 테이블은 자동으로 재활용되므로 이 추가 작업은 필요하지 않습니다.

임시 테이블 적용

스레드 간 중복 이름 충돌을 걱정할 필요가 없기 때문에 복잡한 쿼리의 최적화 과정에서 임시 테이블을 자주 사용합니다. 그 중 하위 데이터베이스와 하위 테이블 시스템의 데이터베이스 간 쿼리가 일반적인 사용 시나리오입니다.

일반적으로 데이터베이스와 테이블을 샤딩하는 시나리오는 논리적으로 큰 테이블을 여러 데이터베이스 인스턴스에 분산시키는 것입니다. 예를 들어. 주어진 필드 f에 대해 대형 테이블 ht를 1024개의 하위 테이블로 분할하고 이러한 하위 테이블을 32개의 데이터베이스 인스턴스에 배포합니다. 아래 그림과 같이

일반적으로 이러한 종류의 하위 데이터베이스 및 하위 테이블 시스템에는 중간 계층 프록시가 있습니다. 그러나 클라이언트가 데이터베이스에 직접 연결할 수 있도록 하는 일부 솔루션도 있습니다. 즉, 프록시 계층이 없습니다.

이 아키텍처에서 파티션 키 선택은 "데이터베이스 간 및 테이블 간 작업 감소" 원칙을 기반으로 합니다. 대부분의 문에 f와 동등한 조건이 포함되면 f를 파티션 키로 사용해야 합니다. SQL 문을 구문 분석한 프록시는 쿼리를 위해 이를 라우팅할 테이블을 결정합니다.

예를 들어 다음 명령문은

select v from ht where f=N;

이때 하위 테이블 규칙(예: N%1024)을 사용하여 필요한 데이터가 어느 하위 테이블에 위치하는지 확인할 수 있습니다. 이러한 종류의 명령문은 하나의 하위 테이블에만 액세스하면 되며 하위 데이터베이스 및 하위 테이블 체계에서 가장 널리 사용되는 명령문 형식입니다.

그러나 이 테이블에 또 다른 인덱스 k가 있고 쿼리문은 다음과 같다면

select v from ht where k >= M order by t_modified desc limit 100;

이때 파티션 필드 f는 쿼리 조건에 사용되지 않기 때문에 만족하는 파티션만 모두 찾을 수 있습니다. 모든 행을 조건으로 한 다음 균일하게 작업별로 순서를 수행합니다. 이 경우 일반적으로 사용되는 두 가지 아이디어가 있습니다.

첫 번째 아이디어는 프록시 레이어의 프로세스 코드에 정렬을 구현하는 것입니다. 이 방법의 장점은 하위 데이터베이스에서 데이터를 가져온 후 메모리에서 직접 계산에 참여할 수 있다는 것입니다. 그러나 이 솔루션의 단점도 분명합니다.

• 에는 상대적으로 많은 양의 개발 작업이 필요합니다. 우리가 제시한 설명은 비교적 간단합니다. 그룹화 또는 조인과 같은 복잡한 작업이 포함된 경우 중간 계층의 개발 능력이 상대적으로 높을 것입니다.

• 对proxy端的压力比较大，尤其是很容易出现内存不够用和CPU瓶颈的问题。

另一种思路就是，把各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作。

比如上面这条语句，执行流程可以类似这样：

• 在汇总库上创建一个临时表temp_ht，表里包含三个字段v、k、t_modified；

• 在各个分库上执行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

• 把分库执行的结果插入到temp_ht表中；

• 执行select v from temp_ht order by t_modified desc limit 100;

得到结果。 这个过程对应的流程图如下所示：

在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库中的某一个上。

为什么临时表可以重名？

你可能会问，不同线程可以创建同名的临时表，这是怎么做到的呢？

我们在执行

create temporary table temp_t(id int primary key)engine=innodb;

这个语句的时候，MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_ {线程id}_ 序列号”。

从文件名的前缀规则，我们可以看到，其实创建一个叫作t1的InnoDB临时表，MySQL在存储上认为我们创建的表名跟普通表t1是不同的，因此同一个库下面已经有普通表t1的情况下，还是可以再创建一个临时表t1的。

先来举一个例子。

进程号为1234的进程，它的线程id分别为4和5，分别属于会话A和会话B。因此，可以看出，session A和session B创建的临时表在磁盘上的文件名不会冲突。

MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。

• 一个普通表的table_def_key的值是由“库名+表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。

• 而对于临时表，table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”。

也就是说，session A和session B创建的两个临时表t1，它们的table_def_key不同，磁盘文件名也不同，因此可以并存。

在实现上，每个线程都维护了自己的临时表链表。这样每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束的时候，对链表里的每个临时表，执行 “DROPTEMPORARY TABLE +表名”操作。

你会注意到，在binlog中也有DROP TEMPORARY TABLE命令的记录。你一定会觉得奇怪，临时表只在线程内自己可以访问，为什么需要写到binlog里面？这，就需要说到主备复制了。

临时表和主备复制

既然写binlog，就意味着备库需要。 你可以设想一下，在主库上执行下面这个语句序列：

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录，那么在备库就只有create table t_normal表和insert intot_normal select * fromtemp_t这两个语句的binlog日志，备库在执行到insert into t_normal的时候，就会报错“表temp_t不存在”。

你可能会说，如果把binlog设置为row格式就好了吧？因为binlog是row格式时，在记录insert intot_normal的binlog时，记录的是这个操作的数据，即：write_rowevent里面记录的逻辑是“插入一行数据（1,1)”。

确实是这样。如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog里。也就是说，只在binlog_format=statment/mixed的时候，binlog中才会记录临时表的操作。

在这种情况下，执行创建临时表语句的操作会被传递到备用数据库进行处理，从而触发备用数据库的同步线程创建相应的临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。因此，我们需要在主数据库中再运行一个DROP TEMPORARY TABLE命令以便备用数据库执行。

메인 데이터베이스의 서로 다른 스레드가 같은 이름의 임시 테이블을 생성해도 상관없지만 실행을 위해 대기 데이터베이스로 전송되는 작업은 어떻게 처리하나요?

이제 예를 들어 보겠습니다. 다음 순서에서는 인스턴스 S가 M의 대기 데이터베이스입니다.

기본 데이터베이스 M의 두 세션이 동일한 이름의 임시 테이블 t1을 생성했습니다. 이 두 create temporary table t1 문은 대기 데이터베이스 S로 전송됩니다.

단, 대기 데이터베이스의 애플리케이션 로그 스레드는 공유되므로 애플리케이션 스레드에서 create 문을 두 번 실행해야 합니다. 멀티 스레드 복제에도 불구하고 슬레이브 라이브러리에서 동일한 작업자에게 실행을 할당하는 것은 여전히 ​​가능합니다. 그러면 동기화 스레드가 오류를 보고하게 됩니까?

분명히 그렇지 않습니다. 그렇지 않으면 임시 테이블이 버그가 될 것입니다. 즉, 백업 스레드는 실행 중 처리를 위해 두 개의 t1 테이블을 독립적인 임시 테이블로 처리해야 합니다. 이것이 어떻게 달성됩니까? MySQL은 binlog를 기록할 때 이 명령문을 binlog에 실행하기 위해 기본 라이브러리의 스레드 ID를 기록합니다. 이런 방식으로 대기 데이터베이스의 애플리케이션 스레드는 각 명령문을 실행하는 기본 데이터베이스 스레드 ID를 알 수 있으며, 이 스레드 ID를 사용하여 임시 테이블의 table_def_key를 구성합니다.

• 세션 A의 임시 테이블 t1, 대기 데이터베이스의 table_def_key는 다음과 같습니다. 라이브러리 이름 + t1 + "M의 서버 ID" + "세션 A의 thread_id"

• 세션 B의 임시 테이블 t1, 대기 데이터베이스의 table_def_key는 라이브러리 이름 + t1 + "M의 서버 ID"입니다. + "세션 B의 thread_id".

table_def_key가 다르기 때문에 이 두 테이블은 대기 데이터베이스의 애플리케이션 스레드에서 충돌하지 않습니다.

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