Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what is the partition table and temporary table in MySQL", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let me lead you to study and learn "what are partition tables and temporary tables in MySQL".

Temporary watch

There is a kind of table in MySQL called temporary table, which refers to the creation of CREATE TEMPORARY TABLE statements. It is a special type of table that allows temporary results to be stored, can be reused multiple times in a single session, and is invisible to other connections. When the connection is broken, the data table is lost, but you can also use DROP TABLE to explicitly delete it without it.

CREATE TEMPORARY TABLE table_name (column_1_definition, column_2_definition,....)

If you want to create a temporary table with the same structure as the existing table, it would be too troublesome to use the CREATE TEMPORARY TABLE statement. You can use the following statement

CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0

Another feature is that temporary tables can have the same name as other tables. For example, even if user tables exist in the database, temporary tables for user can be created in the database.

Create a temporary example

Create a new temporary table called tblemployee, which cannot be seen using SHOW TABLES.

Create temporary table tblemployee (id int auto_increment Primary key,emp_name varchar, emp_address varchar, emp_dept_id int)

Insert data into it.

Mysql > insert into tblemployee values; Query OK, 1 row affected (0.00 sec) mysql > select * from tblemployee +-+ | id | emp_name | emp_address | emp_dept_id | +-+ | 1 | Zhang San | Beijing | 2 | +-+ 1 row in set (0.01 sec) mysql >

Based on an existing table structure

First create two tables.

Create table tb_user (user_name varchar), user_id int (11); insert tb_user values (Zhang San, 1); insert tb_user values (Li Si, 2); insert tb_user values (Wang Wu, 3); create table balance (user_id int (11), balance decimal (5Power2)); insert balance values (1200); insert balance values (2150); insert balance values (3100)

Create a temporary table with name and balance

Create temporary table temp_user_balance select user_name,balance from tb_user left join balance on tb_user.user_id=balance.user_id

View the data in the temporary table.

Mysql > select * from temp_user_balance;+-+-+ | user_name | balance | +-+-+ | Zhang San | 200.00 | Lisi | 150.00 | | Wang Wu | 100.00 | +-+-+ 3 rows in set (sec)

However, when other sessions view this table, they report an error.

Mysql > select * from temp_user_balance;ERROR 1146 (42S02): Table 'test.temp_user_balance' doesn't existmysql >

Delete temporary table

DROP TEMPORARY TABLE table_name; partition table

MySQL began to support partitioning in 5.1. partitioning refers to assigning records of different rows in the same table to different physical files according to certain rules. Each partition is independent and can be processed independently or as part of the table. Partitioning is transparent to the application and will not affect the business.

MySQL only supports horizontal partitioning, not vertical partitioning. Horizontal partitioning is assigning records of different rows of the same table to different physical files, while vertical partitioning refers to assigning different column records of the same table to different physical files.

You can use the SHOW PLUGINS command to see if MySQL has the partitioning feature enabled.

MySQL uses partition by statements to define the data stored in each partition when creating partitions. when querying, the optimizer will filter those partitions that do not have the data we need, so that all partitions do not have to be scanned when querying, which improves efficiency.

Partition Typ

RANGE partition

It is based on a contiguous interval range, allocates data to different partitions, is the most commonly used partition type, and creates a partition table with id column intervals.

Create table user (id int, user_name varchar) partition by range (id) (partition user0 values less than, partition user1 values less than)

After creating this table, the table no longer consists of an ibd, but consists of the ibd of each partition at the time of establishment. You can first view the location of the data directory with the following statement, and then view the ibd created after the partition.

Show global variables like "% datadir%" +-+-+ | Variable_name | Value | +-+-+ | datadir | / var/lib/mysql/ | +-+-- -+ 1 row in set (0.00 sec) root@hxl-PC:/var/lib/mysql/test# lsuser#p#user0.ibd user#p#user1.ibdroot@hxl-PC:/var/lib/mysql/test#

Then we insert three pieces of data into it, but we can see that the third item with an id of 250 reported an error because it inserts a value that is not defined in the partition, and MySQL throws an exception.

Mysql > insert user values (50, "Zhang San"); Query OK, 1 row affected (0.01 sec) mysql > insert user values ("Zhang San"); Query OK, 1 row affected (0.01 sec) mysql > insert user values (250, "Zhang San"); ERROR 1526 (HY000): Table has no partition for value 250mysql >

The solution is to add a partition with MAXXXVALUE value so that all values greater than 200 are stored here, so that values greater than 200 can be inserted.

Alter table user add partition (partition user3 values less than maxvalue); mysql > insert user values; Query OK, 1 row affected (0.02 sec)

The specific information of each partition can be obtained by querying the PARTITIONS table with the following statement.

Select * from information_schema.partitions where table_schema=database () and table_name='user'\ G

Because there are now three partitions, there will be three row, and the TABLE_ROWS in each row represents the number of stores, so it is now 1, and PARTITION_METHOD represents the partition type.

. * * 1. Row * * PARTITION_METHOD: RANGE SUBPARTITION_METHOD: NULL PARTITION_EXPRESSION: `id` SUBPARTITION_EXPRESSION: NULL PARTITION_DESCRIPTION: 100TABLE_ROWS: 1.

You can also use explain to see which partition is used in the query.

LIST partition

The LIST partition is similar to RANGE, except that the values of the partition column can only hold a specific one, that is, a collection of values of an enumerated list. And RANGE is a set of continuous interval values.

Create table user (id int (11)) partition by list (id) (partition user0 values in (1, 3, 5, 7, 9), partition user1 values in (0, 2, 4, 6, 8))

If you also insert some data, you can see that an exception is thrown when inserting 10, also because the inserted data is no longer in the partition definition.

Mysql > insert user values (1); Query OK, 1 row affected (0.02 sec) mysql > insert user values (2); Query OK, 1 row affected (0.01 sec) mysql > insert user values (6); Query OK, 1 row affected (0.02 sec) mysql > insert user values (9); Query OK, 1 row affected (0.01 sec) mysql > insert user values (10); ERROR 1526 (HY000): Table has no partition for value 10mysql >

The other 1, 2, 6 and 9 are in user0 and user1 with two each.

HASH partition

The purpose of HASH is to distribute data evenly among the defined partitions, ensuring that the amount of data in each partition is roughly the same. HASH partition does not specify which partition a certain value must be stored in like RANGE and LIST. HASH partition is completed automatically, we only need to specify the number of partitions.

Create table user (id int (11)) partition by hash (id) partitions 4

So how do you know in which partition this data is stored? for example, 500 is obtained through mod (500, the number of partitions), so 500 is in the first partition.

Mysql > select mod (500jue 4)->; +-+ | mod (500jin4) | +-+ | 0 | +-+

For example, 31, then mod (31 mod 4) is 3, so in the fourth partition, if you insert these two numbers and view them through information_schema.partitions, then the TABLE_ROWS of both partitions 1 and 4 is 1.

When looking up data through conditions, different partitions are used, such as looking for equal numbers, then first calculate which partition the value should be in, and then do the lookup, and if you use scope lookup, all partitions will be used.

HASH can also use some functions or other valid expressions, for example, you can use partition by hash (abs (id)) when creating, but not all functions can be used. The functions that can be used can refer to the official website.

KEY partition

The Key partition is similar to HASH, except that the HASH partition allows the use of user-defined expressions, while the KEY partition does not allow the use of user-defined expressions, which requires the use of the HASH function

KEY partitions allow multiple columns, while HASH partitions allow only one column. In addition, partitioned columns in key can be unspecified if there is a primary key or unique key. The default is primary key or unique key. If not, the column must be explicitly specified.

Create table user (id int (11)) partition by key (id) partitions 4

But I did not find a detailed introduction to the partition algorithm, but saw some said through the PASSWORD operation, did not understand.

Columns partition

Columns partition is a partition type introduced in 5.5. before that, RANGE and LIST partitions could only support integer partitions, which required additional functions to calculate, and Columns partitions solved this problem.

Columns partitions can be subdivided into RANGE Columns and LIST Columns partitions. The supported types are as follows:

TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER) and BIGINT, but DECIMAL or FLOAT is not supported.

DATE and DATETIME.

CHAR, VARCHAR, BINARY, and VARBINARY,TEXT and BLOB columns are not supported.

Create table user (an int, b int) partition by range columns (a, b) (partition p0 values less than (5,12), partition p1 values less than (maxvalue, maxvalue))

Now insert some data.

Insert into user (arecom b) values (4meme 11); insert into user (ameme b) values (6jue 13)

The first item is in the p0 partition because (4, 11) < (5, 12), and (6, 13) < (5, 12), exceeding expectations, in the p1 partition.

Subpartition

Subpartitions, also known as compound partitions, can be further partitioned on partition tables RANGE and LIST.

Create table user (id int, purchased date) partition by range (year (purchased)) subpartition by hash (to_days (purchased)) subpartitions 2 (partition p0 values less than (1990), partition p1 values less than (2000), partition p2 values less than maxvalue); treatment of NULL

MySQL can use NULL on the partition key and treat it as if it were the smallest partition, that is, it will be stored in the first partition, but in the List partition, the null value must be defined in the list, otherwise it cannot be inserted.

These are all the contents of the article "what are partition tables and temporary tables in MySQL?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.