Mysql table partitions are partitioned by day, and mysql partitions by day

Mysql table partitions are partitioned by day, and mysql partitions by day
 MySQL table partitioning refers to splitting a table into several small tables according to the set conditions. MySQL 5.1 and later versions support table partitioning, after using the table partition, the performance of large tables in MySQL is improved during statistics query. Using MySQL table partitions has the following advantages:

 

1. You can store more data than a single disk or file system partition.

2. It is easy to delete unnecessary or outdated data.

3. Some queries can be greatly optimized

4. When Aggregate functions such as SUM ()/COUNT () are involved

5. Higher IO Throughput

In earlier MySQL versions, you can query show variables like 'have _ partitioning '; variable parameters are used to determine whether table partitions are supported in the system. This parameter is not used in MySQL. By default, the partition function is supported, however, not all data engines Support Table Partitioning. Currently, the following Table Partitioning engines can be used: InnoDB, MyISAM, and MEMORY. In addition, the BLACKHOLE engine also supports partitions, however, the data in the BLACKHOLE engine is empty, so partition usage does not actually make sense. However, InnoDB and MyISAM engine tables are used for partitioning, note that when using the table engine partition, you must use the table engine, we recommend that you enable innodb_file_per_table (independent tablespace) when using the InnoDB Engine table, which is also independent and easy to manage, if the path of the partition is not specified when the table partition is created, it is in the datedir directory by default. Split the table data files under the library folder to create many small files, of course, when creating or modifying a table, if there is a data directory for the specified table file, you can also make a table partition and place the data on different disks, finally, you need to define a primary key in the used partition field. If there is a primary key, then it will form a joint primary key in combination with the previous primary key, at the same time, this field should not be NULL because of the primary key. We recommend that you add and modify table partitions in the alter table after the table is created. This ensures that it is not prone to errors when you modify and Create Table partitions, because table partitions in MySQL are different from those in Oracle that can be created after a table is created, data is divided into partition files according to the given conditions, of course, you can perform subpartitions on the basis of the partitions in MySQL, but it is generally rarely used. Note that when you delete a partition, data will also be lost, so you need to be careful when deleting table partitions. There are four partition types in MySQL:

 

RANGE partition: multiple rows are allocated to the partition based on the column values in a given continuous interval.

LIST partition: similar to partitioning by RANGE, the difference is that LIST partition is selected based on the column value matching a value in a discrete value set.

HASH partition: select a partition based on the return value of a user-defined expression. This expression uses the column values of the rows to be inserted into the table for calculation. This function can contain any expressions that are valid in MySQL and generate non-negative integer values.

KEY partitioning: similar to HASH partitioning, the difference is that KEY partitioning only supports computing one or more columns, and the MySQL server provides its own HASH function. One or more columns must contain integer values.

Among them, we usually use the partitioning method to partition by time in the field, so we will use the RANGE Partitioning Method to partition. Here we will talk about the most commonly used time conditions for Table Partitioning, partition by time can be used according to the conditions such as year, month, and day. Because partition by time condition is used, RANGE partition is selected here. There are three partition conditions:

 

Less than: If the value is less than or equal to, the time is LESS

Less than max: not equal

IN: contained IN a certain range

You can use day (), to_days (), and other related time functions for the time-sharing condition. Note that the return value must be an integer. There is a table below

 

In this example, the date field is partitioned by day. First, you need to change the date field to the primary key, and then add the partition splitting rule.

 

Alter table 'employee _ tbl'

Drop primary key,

Add primary key ('id', 'date'); -- the previous id is the primary key, so here 2nd the primary key is the primary key of the Union.

Alter table 'employee _ tbl' add primary key ('date ');

-- To facilitate the management of partition names, the names are named "p time ".

Alter table 'employee _ tbl' partition by range (to_days (date ))(

PARTITION 'p00001101' values less than (to_days ('20140901 ')),

PARTITION 'p00001102' values less than (to_days ('20140901 ')),

PARTITION 'p00001103' values less than (to_days ('20140901 ')),

PARTITION 'p00001104' values less than (to_days ('20140901 ')),

PARTITION 'p00001105' values less than (to_days ('20140901 ')),

PARTITION 'p00001106' values less than (to_days ('20140901 ')),

PARTITION 'p00001107' values less than (to_days ('20140901 ')),

PARTITION 'p00001108' values less than (to_days ('20140901 ')),

PARTITION 'p00001109' values less than (to_days ('20140901 ')),

PARTITION 'p00001110' values less than (to_days ('20140901 '))

);

In this way, partitions are created by day. If you are not satisfied, you can re-partition the partition. It is also very easy to add partitions later:

 

Alter table employee_tbl add partition (PARTITION p00001111 values less than (TO_DAYS ('2017-11-11 ')));

Delete partition:

 

Alter table employee_tbl drop partition p00001101;

Finally, we can query the MySQL System dictionary library to learn details about all partitions.

 

SELECT

*

FROM

Information_schema. PARTITIONS t

WHERE

T. PARTITION_NAME IS NOT NULL

After a partition is created, table partitions can be automatically added through process and Event Control.

Top

0

Step on

0

View comments