MySQL understands what is a partition table? What is a temporary table?-Mysql Tutorial-php.cn

What is a partition table? What is a temporary table? The following article will take you through the partition tables and temporary tables in MySQL. I hope it will be helpful to you!

MySQL understands what is a partition table? What is a temporary table?

Temporary table

There is a kind of table in MySQL called a temporary table, which refers to using CREATE TEMPORARY TABLE## Created by the # statement, it is a special type of table that allows the storage of temporary results, which can be reused multiple times in a single session and is invisible to other connections. When the connection is interrupted, the data table will be lost, but You can also use DROP TABLE to explicitly delete it if you don't need it. [Related recommendations: mysql video tutorial]

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

Copy after login

If you want to create a temporary table with the same structure as the existing table, use the

CREATE TEMPORARY TABLE statement. If you are in trouble, you can use the following statement

CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0;

Copy after login

Another feature is that the temporary table can have the same name as other tables. For example, even if the user's table exists in the database, a temporary table of user can also be created in the database. surface.

Example of creating a temporary table

Create a new temporary table named tblemployee. This table cannot be seen using SHOW TABLES. of.

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

Copy after login

Insert data into it.

mysql> insert into tblemployee values(1,'张三','北京',2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblemployee;
+----+----------+-------------+-------------+
| id | emp_name | emp_address | emp_dept_id |
+----+----------+-------------+-------------+
|  1 | 张三     | 北京        |           2 |
+----+----------+-------------+-------------+
1 row in set (0.01 sec)

mysql>

Copy after login

Create based on existing table structure

First create two tables.

create table tb_user(user_name varchar(255),user_id int(11));

insert tb_user values("张三",1);
insert tb_user values("李四",2);
insert tb_user values("王五",3);

create table balance(user_id int(11),balance decimal(5,2));
insert balance values(1,200);
insert balance values(2,150);
insert balance values(3,100);

Copy after login

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;

Copy after login

View the data in the temporary table.

mysql> select * from temp_user_balance;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |  200.00 |
| 李四      |  150.00 |
| 王五      |  100.00 |
+-----------+---------+
3 rows in set (0.00 sec)

Copy after login

But when other sessions view this table, an error will be reported.

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

Copy after login

Delete temporary table

DROP TEMPORARY TABLE table_name;

Copy after login

Partition table

MySQL started to support it in 5.1 Partitioning function, partitioning refers to allocating records of different rows in the same table to different physical files according to certain rules. Each area is independent and can be processed independently or as part of the table. Partitioning corresponds to It is said to be transparent and will not affect business.

MySQL only supports horizontal partitioning, not vertical partitioning. Horizontal partitioning allocates records of different rows of the same table to different physical files, while vertical partitioning refers to allocating records of different columns of the same table to in different physical files.

You can use the SHOW PLUGINS command to check whether MySQL has the partitioning function enabled.

MySQL uses the partition by statement to define the data stored in each partition when creating a partition. When querying, the optimizer will filter those partitions that do not have the data we need based on the partition definition, so that there is no need for querying. Scan all partitions to improve efficiency.

Partition type

RANGE partition

It is based on a continuous interval range, Distributing data to different partitions is the most commonly used type of partition. Let's create a partition table with an id column interval.

create table user(id int(11),user_name varchar(255))
partition by range(id)(
partition user0 values less than (100),
partition user1 values less than (200));

Copy after login

After creating this table, the table no longer consists of one ibd, but the ibd of each partition when it was created. You can first check the location of the data directory through the following statement, and then check the ibd created after being partitioned.

show global variables like "%datadir%"
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Copy after login

root@hxl-PC:/var/lib/mysql/test# ls
user#p#user0.ibd  user#p#user1.ibd
root@hxl-PC:/var/lib/mysql/test#

Copy after login

Then we inserted 3 pieces of data into it, but we can see that the third piece with id 250 reported an error. The reason is that a value that is not defined in the partition is to be inserted, and MySQL throws an exception.

mysql> insert user values(50,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(150,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(250,"张三");
ERROR 1526 (HY000): Table has no partition for value 250
mysql>

Copy after login

The solution is to add a partition with a MAXXXVALUE value, so that all values greater than 200 are stored in it. Now you can insert values greater than 200.

alter table user add partition (partition user3 values less than maxvalue);

mysql> insert user values(250,"张三");
Query OK, 1 row affected (0.02 sec)

Copy after login

You can query the PARTITIONS table to obtain the specific information of each partition through the following statement.

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

Copy after login

Since there are now three partitions, there will be three rows. TABLE_ROWS in each row indicates the number of storage, so they are all 1 now, and PARTITION_METHOD indicates the partition type.

.....
*************************** 1. row ***************************

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

Copy after login

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

LIST partition

LIST partition is similar to RANGE, except that the values of the partition column can only store specific values, which is a collection of values in an enumeration list. RANGE is a collection 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)
);

Copy after login

Also insert some data, you can see that an exception is thrown when inserting 10, the reason is that 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 10
mysql>

Copy after login

The remaining 1, 2, 6, and 9 have two entries each in the user0 and user1 partitions.

HASH partition

The purpose of HASH is to evenly distribute data into each defined partition to ensure that the amount of data in each partition is roughly the same. HASH partition There is no need to specify which partition a certain value should be saved in like RANGE and LIST. HASH partitioning is done automatically. We only need to specify the number of partitions.

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

Copy after login

How to know which partition this data is stored in? Take 500 as an example, it is obtained through mod (500, number of partitions), so 500 is in the first partition.

mysql> select mod(500,4)
    -> ;
+------------+
| mod(500,4) |
+------------+
|          0 |
+------------+

Copy after login

For example, 31, then mod(31,4) is 3, so in the fourth partition, if you insert these two numbers, check it through

information_schema.partitions, Then TABLE_ROWS of partitions 1 and 4 are both 1.

通过条件查找数据时，使用到的分区也不一样，比如查找相等的数，那么首先计算这个值应该在哪个分区，然后在进行查找，如果使用<、>来范围查找，则会使用所有分区。

还有HASH可以使用一些函数或其他有效表达式，比如创建时可以使用partition by hash(abs(id))，但并不是所有函数都可以使用，可使用的函数可以参考官网；

KEY分区

Key分区和HASH类似，不同的是， HASH 分区允许使用用户自定义的表达式，KEY 分区不允许使用用户自定义的表达式，需要使用 HASH 函数

KEY分区允许多列，而HASH分区只允许一列，另外在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。

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

Copy after login

但是我并没有找到关于详细分区介绍的算法，而是看到一些说通过PASSWORD的运算，没有搞懂。

Columns分区

Columns分区是5.5引入的分区类型，在此之前，RANGE分区和LIST分区只能支持整数分区，从而需要额外的函数来计算，Columns分区解决了这个问题。

Columns分区可以细分为RANGE Columns 和LIST Columns分区，支持的类型如下：

TINYINT、 SMALLINT、 MEDIUMINT、 INT ( INTEGER) 和 BIGINT，但是不支持DECIMAL或 FLOAT。
DATE和 DATETIME。
CHAR， VARCHAR， BINARY，和 VARBINARY，TEXT和 BLOB列不支持。

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

Copy after login

现在插入一些数据

insert into user (a,b) values (4,11);
insert into user (a,b) values (6,13);

Copy after login

第一条由于(4,11) < (5,12) 所以在p0分区，而(6,13) < (5,12) ，超出预期，在p1分区。

子分区

子分区也称为复合分区，可以对分区表RANGE和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
);

Copy after login

对NULL的处理

MySQL可以在分区键上使用NULL，会把他当做最小分区来处理，也就是会存放到第一个分区，但是在List分区中，NULL值必须定义在列表中，否则不能被插入。

更多编程相关知识，请访问：编程视频！！

The above is the detailed content of MySQL understands what is a partition table? What is a temporary table?. For more information, please follow other related articles on the PHP Chinese website!