Why does Ali not recommend the use of MySQL partition tables?

What's wrong with the partition table? Why does the company's specifications not allow the use of the partition table?

What is a partition table

Insert two records into the sample table. According to the partitioning rules, the records fall into the p_2018 and p_2019 partitions.
It can be seen that the table contains one .frmfile and 4 .ibdfiles, and each partition corresponds to one .ibdfile:

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

Engine layer behavior of partition table

Give an example of adding gap locks to a partitioned table. The purpose is to show that for InnoDB, this is 4 tables.

  • Partition table gap lock
select * from tt
where ftime='2017-5-1'
for update;
T2insert into tt values ​​('2018-2-1', 1); (Query OK)
insert into tt values ​​('2017-12-1', 1); (blocking)

When the table tt is initialized, only two rows of data are inserted. The select statement of session1 locks the gap between the two records on the index ftime. If it is a normal table, at time T1, on the ftime index of table t, the gap and lock status should be as follows

  • Locking range of ordinary tables

That is, the gap between the two records of '2017-4-1' and '2018-4-1' will be locked.
The two insert statements of sesion2 should all enter the lock waiting state.

But the first insert of session2 succeeded. Because for the engine, p_2018 and p_2019 are two different tables, that is, the next record of 2017-4-1 is not 2018-4-1, but the supremum of the p_2018 partition.
So T1, on the ftime index of table t, the state of gap and lock is actually:

  • Locking range of partition table tt

Due to the partition table rules, the select of session1 only operates partition p_2018, so the lock range is green in the above figure.

Therefore, session2 succeeded in writing 2018-2-1, but to write 2017-12-1, it is necessary to wait for the gap lock of session1.

Part of the result of show engine innodb status at this time:

  • session2 is locked information

MyISAM partition table

# 把表tt改成MyISAM表
alter table t engine=myisam

For the MyISAM engine, these are 4 tables.

  • Verify with MyISAM table lock
alter table t engine=myisam;
update tt set c=sleep(100)
where ftime='2017-4-1';
select * from tt where ftime='2018-4-1';
(Query OK)
select * from tt where ftime='2017-5-1';

In session1, sleep(100) sets the execution time of the statement to 100s. Since the MyISAM engine only supports table locks, this update statement will lock the read on the entire table tt.

But the first query statement of session2 can be executed normally, and the second statement enters the lock wait.

Because MyISAM's table lock is implemented in the engine layer, the table lock added by session1 is actually locked in partition p_2018 . Therefore, only queries executed on this partition will be blocked, and queries on other partitions will not be affected.

The partition table seems to work very well, why is it disabled?
An important reason for using partitioned tables is that the single table is too large. If the partition table is not used, the table must be divided manually.

Manual table VS partition table

For example, divide by year and create ordinary tables t_2017 , t_2018 , t_2019, etc. respectively. Manual sub-tables must also find all sub-tables that need to be updated, and then perform the update in turn.
There is no difference in performance and partition table.

  • The partition table is determined by the server layer which partition to use
  • Manual sub-table is determined by the application layer code which sub-table to use

So from the engine level, there is no difference.

The difference between the two methods is mainly at the server layer. A serious problem with the partition table of the server layer is the behavior of opening the table.

Partition strategy

When accessing a partitioned table for the first time, MySQL needs to access all partitions.
A typical error report scenario: If a partition table has many partitions, such as more than 1000, and when MySQL starts, the default value of the open_files_limit parameter is 1024. When accessing the table, because all files need to be opened, the number of open table files exceeds The upper limit is exceeded and an error is reported.

For example, for a table that contains many partitions, executing insert directly reports an error:

This insert actually only needs to access one partition, but the statement reports an error . This table is MyISAM, if you use InnoDB, this problem will not occur.

MyISAM partition table uses a generic partitioning strategy (generic partitioning), and each access to the partition is controlled by the server layer. The general partition strategy is the code that existed when MySQL first supported partitioned tables. The implementation of file management and table management is very rough, and the performance problems are very serious.

Starting from MySQL 5.7.9, InnoDB introduced native partitioning, which manages the behavior of opening partitions within InnoDB.

Starting from MySQL 5.7.17, MyISAM partition tables are marked as deprecated.
Starting from MySQL 8.0, the creation of MyISAM partition tables has been forbidden, and only engines that have implemented the local partitioning strategy are allowed to be created.

Currently, only InnoDB and NDB engines support local partitioning strategies.

Server layer behavior of partition table

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

As shown in the figure, it is the operation sequence and execution result diagram of this example.

  • MDL lock of partition table
select * from tt
where ftime='2018-4-1';
alter table tt truncate partition p_2017

  • show processlist

Although session2 only needs to operate the p_2107 partition, because session1 holds the MDL lock of the entire table tt, the alter statement of session2 is blocked.

Therefore, when the partition table is doing DDL, the impact will be greater. If you use a common sub-table, when you truncate a sub-table, it will definitely not conflict with the query statement on another sub-table.


At the server layer, it is considered that this is the same table, so all partitions share the same MDL lock.
In the engine layer, it is considered that this is a different table. Therefore, the execution process after the MDL lock will only access the necessary partitions according to the partition table rules.

What is the necessary partition ?
According to the where condition in the SQL statement, combined with the partitioning rules. For example, where ftime='2018-4-1' above, the value calculated by the year function according to the partition rule is 2018, then it will fall into the p_2019 partition.

But if the where condition is changed to where ftime>='2018-4-1', although the query results are the same, at this time, according to the where condition, it is necessary to access the p_2019 and p_others partitions.

If there is no partition key in the where condition of the query statement, you can only access all partitions. Of course, this is not a problem with the partition table. Even if the business sub-table is used, and the key of the sub-table is not used in the where condition, all sub-tables must be accessed.

Application scenarios of partition table

One big advantage is that it is transparent to the business. Compared with the user table, the business code using the partition table is more concise. In addition, the partition table can easily clean up historical data.

If a business runs for long enough, there is often a need to delete historical data based on time. At this time, the partition table partitioned by time can be directly deleted through the alter table t drop partition ... syntax to delete the out-of-date historical data.

alter table t drop partition… is to directly delete partition files, similar to drop ordinary tables. Compared with delete, the advantage is that it is fast and has little impact on the system.

It should be noted that I have introduced you to the range partition (range) as an example. In fact, MySQL also supports partitioning methods such as hash partitioning and list partitioning.
In actual use, there are two problems with the partition table and the user partition table:

  • When you visit for the first time, you need to visit all partitions
  • Shared MDL lock

Therefore, if you want to use a partition table, don't create too many partitions. I have seen a user who made a daily partition strategy and then created a 10-year partition in advance. In this case, the performance of accessing the partition table is naturally not good. There are two problems here:

  • The partition is not as small as possible.
    Single table or single partition has 10 million rows of data. As long as there is no particularly large index, it is already a small table for current hardware capabilities.
  • Do not reserve too many partitions in advance, just create them before use. For
    example, if it is a monthly partition, you can create 12 new partitions for the next year at the end of each year. For historical partitions without data, drop in time

Other problems with partitioned tables, such as queries that need to fetch data across multiple partitions, will slow query performance. Basically, it is not a problem with the partition table itself, but a problem with the amount of data or how it is used.
If your team has maintained a mature sub-database and sub-table middleware, using business sub-tables will not add extra complexity to business development students, and it will be more intuitive for DBA and naturally better.