36 Why Temporary Tables Can be Renamed #

In the previous article, we used temporary tables to optimize join queries. We used them like this:

create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

You might wonder, why use temporary tables? Couldn’t we just use regular tables?

Today, let’s start with this question: what are the characteristics of temporary tables, and why are they suitable for this scenario?

First, let me clarify a common misconception for you: some people might think that temporary tables are memory tables. But these are completely different concepts.

• Memory tables refer to tables that use the Memory engine. The table definition syntax is create table ... engine=memory. The data of these tables is stored in memory and will be cleared when the system restarts, but the table structure remains. Apart from these two “strange” characteristics, it behaves like a normal table.
• Temporary tables can use various engine types. If they use the InnoDB or MyISAM engine, the data is written to disk. Of course, temporary tables can also use the Memory engine.

Now that we’ve cleared up the difference between memory tables and temporary tables, let’s take a look at the characteristics of temporary tables.

Characteristics of Temporary Tables #

To facilitate understanding, let’s look at the following sequence of operations:

Figure 1 Example of temporary table characteristics

From this example, we can see that temporary tables have the following characteristics:

1. The table definition syntax is create temporary table ....
2. A temporary table can only be accessed by the session that creates it and is not visible to other threads. So, the temporary table t created by session A in the figure is not visible to session B.
3. Temporary tables can have the same name as regular tables.
4. When there are tables with the same name (temporary and regular) within session A, the show create statement, as well as the insert, delete, update, and select statements, access the temporary table.
5. The show tables command does not display temporary tables.

Since a temporary table can only be accessed by the session that creates it, it will be automatically deleted when this session ends. It is because of this feature that temporary tables are particularly suitable for optimizing join operations as described at the beginning of the article. But why?

The reasons include the following:

1. Temporary tables with the same name can coexist in different sessions. If multiple sessions execute the join optimization simultaneously, there is no need to worry about table name conflicts.
2. There is no need to worry about data deletion. If regular tables were used, when the client encounters an abnormal disconnection or the database experiences an abnormal restart during the execution process, additional operations would be required to clean up the intermediate data tables. Temporary tables are automatically reclaimed, so no additional operations are needed.

Applications of Temporary Tables #

Because there are no conflicts between threads regarding table names, temporary tables are often used in the optimization process of complex queries. One typical use case is cross-database queries in a sharding system.

In general, a sharding system divides a logically large table into smaller tables that are distributed across different database instances. For example, a large table ht is split into 1024 sub-tables based on the field f and distributed across 32 database instances, as shown in the following diagram:

Figure 2 Simplified diagram of sharding

Usually, in such sharding systems, there is an intermediate proxy layer. However, there are also solutions where clients directly connect to the databases without this proxy layer.

In this architecture, the choice of partition key is based on “minimizing cross-database and cross-table queries”. If most queries include an equality condition on f, then f should be used as the partition key. This way, after the proxy layer parses the SQL statement, it can determine which sub-table to route the query to.

For example, consider the following query:

select v from ht where f=N;

At this point, we can use the partitioning rule (e.g., N%1024) to determine which partition the required data is placed on. This type of statement only needs to access one partition, making it the most popular form of statement in a sharding scheme.

However, if there is another index, k, on this table and the query is like this:

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

In this case, since the partition field f is not used in the query condition, it can only search for all rows that meet the conditions in all partitions and then perform the order by operation uniformly. In this situation, there are two common approaches.

The first approach is to implement sorting in the proxy layer process code.

The advantage of this approach is that it is fast, as it directly participates in the calculation in memory once the data from the sharded databases is obtained. However, the disadvantages of this approach are also quite obvious:

1. It requires a large amount of development work. The example we gave is relatively simple, but if it involves complex operations such as group by or even join, it requires a relatively high level of development capability in the middle layer.
2. It puts a lot of pressure on the proxy side, especially issues such as insufficient memory and CPU bottlenecks are prone to occur.

Another approach is to consolidate the data obtained from each shard database into a table on one MySQL instance, and then perform logical operations on this consolidated instance.

For example, the execution process of the above query can be like this:

• Create a temporary table temp_ht on the consolidation database, which includes three fields: v, k, t_modified.
• Execute select v, k, t_modified from ht_x where k >= M order by t_modified desc limit 100; on each shard database.
• Insert the results obtained from the shard databases into the temp_ht table.
• Execute select v from temp_ht order by t_modified desc limit 100; to get the results.

The corresponding flowchart for this process is shown below:

Figure 3: Flowchart of cross-database query process

In practice, we often find that the computation load of each shard database is not saturated, so we directly place the temporary table temp_ht on one of the 32 shard databases. The query logic in this case is similar to Figure 3. You can think about the specific process yourself.

Why can temporary tables have the same name? #

You may wonder how different threads can create temporary tables with the same name.

Next, let’s take a look at this question.

When we execute create temporary table temp_t(id int primary key) engine=innodb;, MySQL needs to create an .frm file to store the table structure definition for this InnoDB table, and it also needs a place to store table data.

The .frm file is stored in the temporary file directory, with the file name consisting of a suffix .frm and a prefix “#sql{process id}_{thread id}_serial number”. You can use the select @@tmpdir command to display the temporary file directory of the instance.

As for the storage method for table data, different versions of MySQL have different processing methods.

• In versions prior to 5.6, MySQL would create a file in the temporary file directory with the same prefix and a .ibd extension to store data for temporary tables.
• Starting from version 5.7, MySQL introduced a temporary file tablespace dedicated to storing temporary table data. Therefore, there is no need to create .ibd files anymore.

From the prefix rule of the file name, we can see that when creating a temporary InnoDB table called t1, MySQL treats the table name we created and the normal table t1 as different in terms of storage. Therefore, even if there is already a normal table t1 in the same database, we can still create a temporary table t1.

To facilitate further discussion, let me give an example.

Figure 4 Temporary table name

The process ID of this process is 1234, the thread ID of session A is 4, and the thread ID of session B is 5. As you can see, the temporary tables created by session A and session B do not have the same file name on disk.

In addition to having physical files, MySQL also maintains a set of mechanisms in memory to differentiate between different tables, and each table is associated with a table_def_key.

• The table_def_key for a regular table is obtained by concatenating the “database name + table name”. Therefore, if you try to create two regular tables with the same name in the same database, you will find that the table_def_key already exists when creating the second table.
• For temporary tables, the table_def_key is based on the “database name + table name” and is further modified by adding “server_id + thread_id”.

In other words, the table_def_key and disk file name for the two temporary tables t1 created by session A and session B are different, so they can coexist.

In the implementation, each thread maintains its own temporary table linked list. So, when manipulating a table within a session, the linked list is traversed first to check if there is a temporary table with the same name. If there is, the temporary table is prioritized for the operation; if not, the regular table is operated on. At the end of the session, the “DROP TEMPORARY TABLE + table name” operation is performed on each temporary table in the linked list.

At this point, you may find that the DROP TEMPORARY TABLE command is recorded in the binlog. You might wonder why temporary tables, which can only be accessed by the thread itself, need to be written to the binlog.

This leads us to the topic of primary-replica replication.

Temporary Tables and Primary-Replica Replication #

Since binlog is written, it means there is a need for replicas.

Imagine executing the following statement sequence on the primary server:

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*/

If operations related to temporary tables are not recorded, then the binlog on the replica will only have the binlog logs for the create table t_normal statement and the insert into t_normal select * from temp_t statement. When the replica executes the insert into t_normal statement, an error “table temp_t does not exist” will be thrown.

You might say, why not simply set binlog to row format? This is because when binlog_format is row, statements related to temporary tables are not recorded in the binlog. This means that only when binlog_format is set to statement/mixed, will operations on temporary tables be recorded in the binlog.

In this case, the statement to create the temporary table will be transmitted and executed on the replica. When the thread on the primary server exits, the temporary table is automatically deleted. However, the replica sync thread keeps running. Therefore, we need to issue a DROP TEMPORARY TABLE command on the primary server to be executed on the replica.

Someone once asked me an interesting question: When MySQL records binlog, whether it is a create table or an alter table statement, it is recorded verbatim, even the spaces are not changed. However, if you execute drop table t_normal, the system will record the binlog as:

DROP TABLE `t_normal` /* generated by server */

This means that it is changed to the standard format. Why is that?

Now you know the reason: the drop table command can delete multiple tables at once. In the example above, if binlog_format is set to row and the command “drop table t_normal, temp_t” is executed on the primary server, the binlog will only record:

DROP TABLE `t_normal` /* generated by server */

Because there is no table temp_t on the replica, the command is rewritten and transmitted to the replica to avoid stopping the replica synchronization thread.

Therefore, when recording the drop table command in the binlog, the statement must be modified. “/* generated by server */” indicates that this is a command modified by the server.

Speaking of primary-replica replication, there is another issue that needs to be addressed: What happens when different threads on the primary server create temporary tables with the same name and they are executed on the replica? Now, let me give you an example. In the sequence below, instance S is the standby database of instance M.

Figure 5. Operations on temporary tables in the primary-standby relationship

On the primary database M, two sessions create temporary table t1 with the same name. Both “create temporary table t1” statements will be sent to standby database S.

However, the application log thread on the standby database is shared, which means that these two create statements need to be executed in order within the application thread (even if multi-threaded replication is enabled, they could still be assigned to the same worker on the standby database). Will this cause any synchronization errors?

Obviously not, otherwise temporary tables would be a bug. In other words, when the standby thread executes, it treats these two t1 tables as two different temporary tables. How is this achieved?

When recording the binlog, MySQL writes the thread ID of the primary server that executes this statement into the binlog. Therefore, the application thread on the standby database can know the thread ID of the primary server that executes each statement and use this thread ID to construct the table_def_key of the temporary table:

1. The table_def_key of temporary table t1 in session A on the standby database is: database name + t1 + “M’s server ID” + “session A’s thread ID”;
2. The table_def_key of temporary table t1 in session B on the standby database is: database name + t1 + “M’s server ID” + “session B’s thread ID”.

Since the table_def_key is different, these two tables will not conflict in the application thread on the standby database.

Summary #

In this article, I introduced the usage and characteristics of temporary tables to you.

In practical applications, temporary tables are generally used to handle more complex calculation logic. Since temporary tables are visible only to the thread that creates them, there is no need to worry about naming conflicts when multiple threads execute the same processing logic. Temporary tables are automatically deleted when the thread exits, eliminating the need for clean-up and exception handling work.

In binlog_format=‘row’, the operations on temporary tables are not recorded in the binlog, which also eliminates many troubles. This can be a consideration when selecting the binlog_format.

It is worth noting that the temporary tables mentioned above are created by users, also known as user-defined temporary tables. In contrast, there are internal temporary tables, which I have already introduced to you in [Article 17].

Finally, I would like to leave you with a question to think about.

The following sequence of statements creates a temporary table and renames it:

Figure 6. Question about renaming temporary tables

As you can see, we can use the “alter table” syntax to modify the table name of the temporary table, but we cannot use the “rename” syntax. Do you know why?

You can write your analysis in the comments. I will discuss this question with you at the end of the next article. Thank you for listening, and feel free to share this article with more friends to read together.

Previous Question Time #

The previous question was about the join statement of the following three tables:

select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;

If rewritten using straight_join, how do you specify the join order and create indexes on the three tables?

The first principle is to use the Block Nested-Loop Algorithm (BKA) as much as possible. It is important to note that when using the BKA, it doesn’t mean “calculate the join result of two tables first, and then join it with the third table”. Instead, it directly nests the queries.

The specific implementation is as follows: among the conditions t1.c>=X, t2.c>=Y, and t3.c>=Z, select the table with the least amount of filtered data as the first driving table. At this point, there may be two situations.

In the first situation, if the selected driving table is t1 or t3, the remaining part is fixed.

1. If the driving table is t1, the join order is t1->t2->t3, and indexes need to be created on the fields t2.a and t3.b as the being driven tables;
2. If the driving table is t3, the join order is t3->t2->t1, and indexes need to be created on t2.b and t1.a.

At the same time, an index needs to be created on the field c of the first driving table.

In the second situation, if the selected first driving table is t2, the filtering effect of the other two conditions needs to be evaluated.

In summary, the overall idea is to make the data set of each driving table involved in the join as small as possible, because this will reduce the size of the driving table.