Detailed explanation of the difference between Mysql temporary table and partition table

Temporary tables and memory tables

A memory table refers to a table that uses the Memory engine. The syntax for creating a table is create table … engine=memory. The data of this type of table is stored in memory and will be cleared when the system restarts, but the table structure still exists. Except for these two features that look "strange", from other features, it is a normal table.

Temporary tables can use various engine types. If you use a temporary table using the InnoDB engine or the MyISAM engine, data is written to disk. Of course, temporary tables can also use the Memory engine.

Temporary table characteristics

• The syntax for creating a table is create temporary table ….
• A temporary table can only be accessed by the session that created it and is not visible to other threads. Therefore, the temporary table t created by session A in the figure is not visible to session B.
• A temporary table can have the same name as a normal table.
• When session A has a temporary table and a normal table with the same name, the show create statement, as well as the add, delete, modify, and query statements access the temporary table.
• The show tables command does not display temporary tables.

Since the temporary table can only be accessed by the session that created it, it will be automatically deleted when the session ends. It is precisely because of this feature that temporary tables are particularly suitable for join optimization scenarios.

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);

Temporary tables of different sessions can have the same name. If multiple sessions execute join optimization at the same time, there is no need to worry about table creation failure due to duplicate table names. No need to worry about data deletion. If you use a normal table, if the client is abnormally disconnected during the process execution, or the database is abnormally restarted, you will need to clean up the data tables generated in the middle of the process. Since temporary tables are automatically recycled, this additional operation is not required. Application of temporary tables

Cross-database query of the sharded database and table system

The general scenario of sharding databases and tables is to distribute a logically large table to different database instances. for example. Split a large table ht into 1024 sub-tables according to field f, and then distribute them to 32 database instances.

The selection of partition key is based on "reducing cross-database and cross-table queries". If most statements contain the equality condition of f, then f should be used as the partition key. In this way, after the proxy layer parses the SQL statement, it can determine which sub-table to route the statement to for query. for example

select v from ht where f=N;

At this time, we can use the table partitioning rules (for example, N%1024) to confirm which table the required data is placed on. This type of statement only needs to access one sharded table and is the most popular statement form for sharded databases and tables.

However, if there is another index k on this table, and the query statement is as follows:

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

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

Implementing sorting in the process code of the proxy layer puts a lot of pressure on the proxy end, especially it is easy to cause problems such as insufficient memory and CPU bottleneck.

Aggregate the data obtained from each sub-database into a table in a MySQL instance, and then perform logical operations on this aggregated instance.

Create a temporary table temp_ht in the summary database, which contains three fields: v, k, and t_modifified;

Execute on each sub-library

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

Insert the results of the sub-database execution into the temp_ht table;

implement

select v from temp_ht order by t_modified desc limit 100;

Why can a temporary table be renamed?

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

When executing this statement, MySQL will create an frm file for this InnoDB table to save the table structure definition and a place to save the table data.

This frm file is placed in the temporary file directory. The file name suffix is ​​.frm and the prefix is ​​"#sql{process id}_{thread id}_serial number". You can use the select @@tmpdir command to display the instance's temporary file directory.

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. Therefore, the temporary tables created by session A and session B will not have duplicate files on disk.

MySQL maintains data tables. In addition to physical files, there is also a mechanism in the memory to distinguish different tables. Each table corresponds to a table_def_key. For temporary tables, table_def_key adds "server_id+thread_id" to the "database name+table name".

That is to say, the two temporary tables t1 created by session A and session B have different table_def_key and disk file names, so they can coexist.

Engine-level behavior of partition tables

ATE TABLE `t` (
		`ftime` datetime NOT NULL,
		`c` int(11) DEFAULT NULL,
		KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
The
B
 (PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 	PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 	PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
 PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
 insert into t values('2017-4-1',1),('2018-4-1',1); 

When the table is initialized, only two rows of data are inserted. The select statement of sessionA locks the gap between the two records of ftime. The gap and lock status are shown in the figure:

That is to say, the gap between the two records 2017-4-1 and 2018-4-1 will be locked, and then both insert statements of sessionB should enter the lock waiting state. However, in terms of effect, the first insert statement can be executed successfully, because for the engine, p2018 and p2019 are different tables, and the next record of 2017 is not 2018-4-1 but the supremum in p2018, so the index at time t1 is as shown in the figure:

Due to the rules of the partition table, sessionA only operates p2018. SessionB can insert 2018-2-1, but it needs to wait for sessionA's gap lock to write to 2017-12-1.

For MYISAM engine:

Because sessionA sleeps for 100 seconds, and since MyISAM only supports table locks, this update will lock the read of the entire table t. However, the result is that the first statement of B can be executed, and the second statement enters the lock waiting state.

This is because the MyISAM table lock is only implemented at the engine layer. The table lock added by sessionA is on p2018, so it will only block queries executed on the partition, and queries falling on other partitions will not be affected. It seems that the partition table is not bad, so why not use it? One reason we use the partition table is that the single table is too large. If we do not use the partition table, we have to use the manual table partitioning method.

Manual table partitioning requires creating t_2017, t_2018, and t_2019, that is, finding all the sub-tables that need to be updated and executing them one by one. This is no different from the partitioned table. In one, the server decides which partition to use, while in the other, the application layer code decides which sub-table to use. Therefore, there is no actual difference from the engine layer. In fact, the main difference is at the server level: opening table behavior.

Partitioning strategy

Whenever a partitioned table is accessed for the first time, MySQL needs to access all partitions: if there are many partitions, for example, 1000 partitions have been checked, and when MySQL is started, the open_files_limit defaults to 1024, then when accessing the table, an error will be reported because all files are opened and the upper limit is exceeded.

The partitioning strategy used by mysiam is called the general partitioning strategy, and each access to the partition is controlled by the server layer. There are serious performance issues.

The innodb engine introduces a local partitioning strategy, which manages the behavior of opening partitions within innodb itself.

Server-level behavior of partition tables

From the server layer, a partition table is just a table.

Although B only operates on the 2017 partition, A holds the MDL lock of the entire table t, which blocks B's alter statement. If you use a common shard table, there will be no MDL conflict with the query statement on another shard table.

summary:

• When mysql opens a partitioned table for the first time, it needs to access all partitions
• At the server level, this is considered to be the same table, so all partitions share the MDL lock
• At the engine level, these are considered to be different tables, so after the MDL lock, only necessary partitions will be accessed according to the partition table rules.

Partition table application scenarios

The advantage of partitioned tables is that they are transparent to the business. Compared with user partitioned tables, the business code using partitioned tables is simpler, and partitioned tables can easily clean up historical data.

The alter table t drop partition operation deletes the partition file. Its effect is similar to that of drop. Compared with delete, it has the advantages of fast speed and less impact on the system.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.