What is partition table

Partition is to decompose a table into smaller and more manageable parts according to certain rules. Logically, there is only one table, but in fact, the table may be composed of N physical partition objects. Each partition is an independent object, which can be processed independently and as a part of the table.

Small test knife

1. See if mysql supports partitions

#Check the mysql version
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.11-log |
+------------+
1 row in set (0.02 sec)
#Check whether table partition is supported
mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name                       | Status   | Type               | Library         | License |
+----------------------------+----------+--------------------+-----------------+---------+
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL            | GPL     |
+----------------------------+----------+--------------------+-----------------+---------+
45 rows in set (0.14 sec)

If the status of partition is active, mysql supports partition.

2. Create a partition table

mysql> CREATE TABLE tb_partition_demo (
    ->   id int not null  auto_increment,
    ->   name varchar(20) DEFAULT NULL,
    ->   create_time date DEFAULT  NULL,
    ->   KEY (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> PARTITION BY RANGE (YEAR(create_time))
    -> (PARTITION partition_2018 VALUES LESS THAN (2019) ENGINE = InnoDB,
    ->  PARTITION partition_2019 VALUES LESS THAN (2020) ENGINE = InnoDB,
    ->  PARTITION partition_2020 VALUES LESS THAN (2021) ENGINE = InnoDB,
    ->  PARTITION partition_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (0.87 sec)

After the table is created successfully, we will find that the table (TB partition demo) contains a. frm file and four. ibd files (each partition corresponds to a. ibd file), that is to say:

• For the engine layer, these are four tables
• For the Server layer, this is a table

Therefore, the partition is completely transparent to the application. We can use the partition table just like the normal table, without affecting the application's business logic.

3. Write data to partition table

mysql> insert into tb_partition_demo(name,create_time) values ('Liu Bei','2018-01-01'),('Guan Yu','2019-01-01'),('Zhang Fei','2020-01-01');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

#Query the data to see if it is written successfully
mysql> select * from tb_partition_demo;
+----+--------+-------------+
| id | name   | create_time |
+----+--------+-------------+
|  1 | Liu Bei   | 2018-01-01  |
|  2 | Guan Yu   | 2019-01-01  |
|  3 | Zhang Fei   | 2020-01-01  |
+----+--------+-------------+
3 rows in set (0.02 sec)

The three pieces of data written above, "Liu Bei" will be written to the partition [2018], "Guan Yu" will be written to the partition [2019], "Zhang Fei" will be written to the partition [2020], which can be verified by the following sql statements:

mysql> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.PARTITIONS a where a.TABLE_NAME='tb_partition_demo';
+-------------------+------------------+------------+
| TABLE_NAME        | PARTITION_NAME   | TABLE_ROWS |
+-------------------+------------------+------------+
| tb_partition_demo | partition_2018   |          1 |
| tb_partition_demo | partition_2019   |          1 |
| tb_partition_demo | partition_2020   |          1 |
| tb_partition_demo | partition_others |          0 |
+-------------------+------------------+------------+
4 rows in set (0.02 sec)

It can be seen that partition 2018, partition 2019 and partition 2020 do have one piece of data, and the updating and deleting operations of data are the same as those of ordinary tables. Is it convenient.

Let's take a look at the performance comparison between partitioned tables and normal tables

To facilitate comparison, we first create a common table like the partition table above. The creation statement is as follows:

mysql>  CREATE TABLE tb_normal_demo (
    ->      id int not null  auto_increment,
    ->      name varchar(20) DEFAULT NULL,
    ->      create_time date DEFAULT  NULL,
    ->      KEY (id)
    ->     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

After adding 200W pieces of data to TB normal demo and TB partition demo tables respectively (the program for writing data is not shown here), execute the following sql respectively

#Query common table
mysql> select count(1) from tb_normal_demo where create_time between '2019-01-01' and '2019-12-31';
+----------+
| count(1) |
+----------+
|    744352 |
+----------+
1 row in set (2.12 sec)

#Query partition table
mysql> select count(1) from tb_normal_demo where create_time between '2019-01-01' and '2019-12-31';
+----------+
| count(1) |
+----------+
|    744352 |
+----------+
1 row in set (0.22 sec)

It can be seen that when querying table data according to partition fields, the advantages of partition tables are reflected as follows.

Of course, the Range partition type is used above. In fact, there are many partition types in Mysql, such as:

• Range partition
• List partition
• Columns partition
• Hash partition
• Key partition
• Sub partition

The usage is almost the same. There is not too much description here.

Advantages of using partitioned tables

The biggest advantage of partitioned table is that it is transparent to business. Compared with partitioned table, the business code of partitioned table is more concise, and partitioned table can easily clean up historical data (you only need to drop a historical partition, syntax: alter table t drop partition...). When using partitioned table, you should pay attention to:

• Partition is not as thin as possible. In fact, a single table or a single partition's data of 1000W rows, as long as there is no particularly large index, is already a small table for current hardware capabilities.
• Don't reserve too many partitions in advance, just create them in advance before using them. For example, create 12 partitions of the next year at the end of each year. For historical partitions without data, drop them in time.

Query data should not be fetched across many partitions as much as possible, which may lead to slow query, so full consideration should be given in advance before using partitions.

Disadvantages of using partition tables

• When Mysql first opens the partition table, it needs to access all partitions (there are many open files)
• In the Server layer, Mysql thinks that this is the same table, so all partitions use an MDL lock (with large lock granularity), which affects concurrency.