Application of partition table in MySQL database

What is partition table

First of all, let's look at the normal storage of tables

The upper part is innodb engine, the lower part is myISAM engine

customer_login_log.frm  #The metadata of each table is saved, including the table structure and the corresponding definition, regardless of the number
 According to the engine, they all have this file
 customer_ login_ log.ibd  #Where InnoDB stores data, files and indexes  

Partition table in: customer_login_log.ibd There will be more than one in this file. Partition table function will be supported after MySQL v5.1.

Check whether the database supports partitioned tables

# Before MySQL v5.6
show variables like '%partition%'; 
# After MySQL V5.6 (including)
mysql>show plugins;

Why do we use partition tables

  • Often encounter tens of thousands or even hundreds of millions of record table data
  • It is difficult to query, and historical data is actually not concerned about
  • To be filed, how to file?
    • According to the data of < 20190100, we can find out the data from where: 101:00 a year ago
    • mysqldump can add the where condition
    • The historical data of the original table should be deleted. Delete table where time <? And time >
      • We only keep the data of nearly half a year in the past three years: delete at 4:00 a.m
        • innodb data is composed of B+Tree structure. If delete where only marks the data to be deleted, it will not be deleted. When inserting data in the future, the space can be reused and the disk file size will not be reduced.
        • mysql>OPTIMIZE table my_table_name;
  • If we have such a kind of file organization: one file for 2017 data, one file for 2018 data, and 2019, we can delete the data according to the file, and specify the query range of the data file, which will improve the query efficiency

3 partition table type

  • HASH partition
  • LIST partition
  • RANGE partition
  • KEY partition

HASH partition

  • Data is stored in different partitions of the table according to the value of MOD (partition key)
  • Basically, it can be evenly distributed in each partition
  • The key value of HASH partition must be INT type, which can be converted to INT type by function
CREATE TABLE `customer_login_log` (
`customer_id` int(10) unsigned NOT NULL COMMENT 'Login user ID',
`login_time` datetime NOT NULL COMMENT 'A kind of user login time',
`login_ip` int(10) unsigned NOT NULL COMMENT 'Sign in IP',
`login_type` tinyint(4) NOT NULL COMMENT 'Login type:0 Unsuccessful 1 successful'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(customer_id) PARTITIONS 4;

insert into customer_login_log values(1,'2020-06-20 22:30:01',1,1);
insert into customer_login_log values(2,'2020-06-20 22:30:02',2,1);
insert into customer_login_log values(3,'2020-06-20 22:30:03',3,1);
insert into customer_login_log values(4,'2020-06-20 22:30:04',4,1);

Use function to perform INT output

CREATE TABLE `customer_login_log` (
`customer_id` int(10) unsigned NOT NULL COMMENT 'Login user ID',
`login_time` timestamp NOT NULL COMMENT 'A kind of user login time', #Notice the data type in this
`login_ip` int(10) unsigned NOT NULL COMMENT 'Sign in IP',
`login_type` tinyint(4) NOT NULL COMMENT 'Login type:0 Unsuccessful 1 successful'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(UNIX_TIMESTAMP(login_time)) PARTITIONS 4;

Conversion function

ABS()
CEILING() (see CEILING() and FLOOR())
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT() (see EXTRACT() function with WEEK specifier)
FLOOR() (seeCEILING() and FLOOR())
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP() (permitted beginning with MySQL 5.6.1 and fully supported
beginning with MySQL 5.6.3, with TIMESTAMP columns)
WEEKDAY()
YEAR()
YEARWEEK()

Content after creation

Check whether the partition table was created successfully

mysql> explain partitions select * from customer_login_log;

Query how much data is stored in each partition

select table_name,partition_name,partition_description,table_rows from information_schema.PARTITIONS where table_name='customer_login_log';

Query specific partition data

select * from customer_login_log partition(p1,p2);
select * from customer_login_log partition(p3) where customer_id=3;

LIST partition

  • Partition according to the list of partition key values

  • The list values of each partition cannot be repeated

  • Each row of data must find the corresponding partition column list, otherwise the insertion fails

    CREATE TABLE `customer_login_log_list` (
      `customer_id` int(10) unsigned NOT NULL COMMENT 'log on user ID',
      `login_time` datetime NOT NULL COMMENT 'User login time',
      `login_ip` int(10) unsigned NOT NULL COMMENT 'Sign in IP',
      `login_type` tinyint(4) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    PARTITION BY LIST(login_type)(
    	PARTITION jishu VALUES in (1,3,5,7,9),
    	PARTITION oushu VALUES in (2,4,6,8)
    );
    
    insert into customer_login_log_list values(1,'2020-06-20 22:30:01',1,1);
    insert into customer_login_log_list values(2,'2020-06-20 22:30:02',2,2);
    insert into customer_login_log_list values(3,'2020-06-20 22:30:03',3,3);
    insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,4);
    

    If the partition key is not in the partition

    mysql> insert into customer_login_log_list values(4,'2020-06-20 22:30:04',4,0);
    ERROR 1526 (HY000): Table has no partition for value 0
    

RANGE partition

  • The data is put into different files according to the different range values of the partition

  • Multiple partitions should be continuous and cannot overlap

  • MAXVALUE with seal

    CREATE TABLE `customer_login_log_range` (
      `customer_id` int(10) unsigned NOT NULL COMMENT 'log on user ID',
      `login_time` datetime NOT NULL COMMENT 'User login time',
      `login_ip` int(10) unsigned NOT NULL COMMENT 'Sign in IP',
      `login_type` tinyint(4) NOT NULL COMMENT 'Login type:0 Unsuccessful 1 successful'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    PARTITION BY RANGE(YEAR(login_time))(
    	PARTITION y2017 VALUES LESS THAN (2017),
    	PARTITION y2018 VALUES LESS THAN (2018),
    	PARTITION y2019 VALUES LESS THAN (2019),
    	PARTITION y2020 VALUES LESS THAN (2020),
    	PARTITION maxyear VALUES LESS THAN MAXVALUE
    );
    
    insert into customer_login_log_range values(1,'2016-06-20 22:30:01',1,1);
    insert into customer_login_log_range values(2,'2017-06-20 22:30:02',2,2);
    insert into customer_login_log_range values(3,'2018-06-20 22:30:03',3,3);
    insert into customer_login_log_range values(5,'2019-06-20 22:30:04',4,4);
    insert into customer_login_log_range values(6,'2020-06-20 22:30:04',4,4);
    insert into customer_login_log_range values(7,'2021-06-20 22:30:04',4,4);
    insert into customer_login_log_range values(8,'2022-06-20 22:30:04',4,4);
    insert into customer_login_log_range values(9,'2023-06-20 22:30:04',4,4);
    

    If we need to add a range partition later, we can't seal the maxvalue

    CREATE TABLE `customer_login_log_range1` (
      `customer_id` int(10) unsigned NOT NULL COMMENT 'log on user ID',
      `login_time` datetime NOT NULL COMMENT 'User login time',
      `login_ip` int(10) unsigned NOT NULL COMMENT 'Sign in IP',
      `login_type` tinyint(4) NOT NULL COMMENT 'Login type:0 Unsuccessful 1 successful'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    PARTITION BY RANGE(YEAR(login_time))(
    	PARTITION y2017 VALUES LESS THAN (2017),
    	PARTITION y2018 VALUES LESS THAN (2018),
    	PARTITION y2019 VALUES LESS THAN (2019),
    	PARTITION y2020 VALUES LESS THAN (2020)
    );
    
    alter table customer_login_log_range1 add PARTITION(
    PARTITION y2021 VALUES LESS THAN (2021),
    PARTITION y2022 VALUES LESS THAN (2022),
    PARTITION y2023 VALUES LESS THAN (2023)
    )
    

    A table with no partitions created

    CREATE TABLE `customer_login_log_range_no` (
      `customer_id` int(10) unsigned NOT NULL COMMENT 'log on user ID',
      `login_time` datetime NOT NULL COMMENT 'User login time',
      `login_ip` int(10) unsigned NOT NULL COMMENT 'Sign in IP',
      `login_type` tinyint(4) NOT NULL COMMENT 'Login type:0 Unsuccessful 1 successful'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert into customer_login_log_range_no values(1,'2016-06-20 22:30:01',1,1);
    insert into customer_login_log_range_no values(2,'2017-06-20 22:30:02',2,2);
    insert into customer_login_log_range_no values(3,'2018-06-20 22:30:03',3,3);
    insert into customer_login_log_range_no values(5,'2019-06-20 22:30:04',4,4);
    insert into customer_login_log_range_no values(6,'2020-06-20 22:30:04',4,4);
    insert into customer_login_log_range_no values(7,'2021-06-20 22:30:04',4,4);
    insert into customer_login_log_range_no values(8,'2022-06-20 22:30:04',4,4);
    insert into customer_login_log_range_no values(9,'2023-06-20 22:30:04',4,4);
    
    alter table customer_login_log_range_no PARTITION BY RANGE(YEAR(login_time))(
    	PARTITION y2017 VALUES LESS THAN (2017),
    	PARTITION y2018 VALUES LESS THAN (2018),
    	PARTITION y2019 VALUES LESS THAN (2019),
    	PARTITION y2020 VALUES LESS THAN (2020),
    	PARTITION y2021 VALUES LESS THAN (2021),
    	PARTITION y2022 VALUES LESS THAN (2022),
    	PARTITION maxyear VALUES LESS THAN MAXVALUE
    )
    # The data will be reassembled according to the partition rules, and the data will enter the corresponding partition
    

    If you want to delete a partition, use the command, you cannot directly delete the document set

     alter table customer_login_log_range drop partition y2017;
    

When the log data is backed up, the concept of partition exchange is added after MySQL v5.7

  • The table structure is the same

  • The archived table cannot be a partitioned table

  • Archive tables cannot have foreign key constraints

  • ARCHIVE

    #Filing table
    CREATE TABLE `arch_customer_login_log_2016` (
    `customer_id` int(10) unsigned NOT NULL COMMENT 'Login user ID',
    `login_time` datetime NOT NULL COMMENT 'A kind of user login time',
    `login_ip` int(10) unsigned NOT NULL COMMENT 'Sign in IP',
    `login_type` tinyint(4) NOT NULL COMMENT 'Login type:0 Unsuccessful 1 successful'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    ALTER TABLE customer_login_log exchange PARTITION p0 WITH TABLE
    arch_customer_login_log_2016;
    ALTER TABLE arch_customer_login_log_2016 ENGINE=ARCHIVE;
    

    Delete partition file

    ALTER TABLE customer_login_log DROP PARTITION p0;
    

    If a partitioned table has a primary key, the partition key must be part of the primary key

    CREATE TABLE `im_product_variants` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `variants_id` varchar(50) NOT NULL DEFAULT '',
    `variants_title` varchar(100) NOT NULL DEFAULT '' COMMENT 'Property name,Multiple attribute values/separate',
    `sku` varchar(50) NOT NULL DEFAULT '',
    `original_price` decimal(10,2) NOT NULL COMMENT 'original price',
    `sale_price` decimal(10,2) NOT NULL COMMENT 'price',
    `variants_weight` int(11) NOT NULL COMMENT 'Unit of weight g',
    `option_name1` varchar(50) NOT NULL DEFAULT '' COMMENT 'Property name 1',
    `option_name2` varchar(50) DEFAULT NULL COMMENT 'Property name 2',
    `option_name3` varchar(50) DEFAULT NULL COMMENT 'Property name 3',
    `option_value1` varchar(50) NOT NULL DEFAULT '' COMMENT 'Property value 1',
    `option_value2` varchar(50) DEFAULT NULL COMMENT 'Property value 2',
    `option_value3` varchar(50) DEFAULT NULL COMMENT 'Property value 3',
    `product_id` varchar(50) NOT NULL DEFAULT '' COMMENT 'product id',
    `image_src` varchar(300) NOT NULL DEFAULT '' COMMENT 'Attribute map src Road path',
    `create_emp` varchar(50) NOT NULL DEFAULT '',
    `update_emp` varchar(50) NOT NULL DEFAULT '',
    `create_time` int(11) NOT NULL,
    `update_time` int(11) NOT NULL,
    PRIMARY KEY (`id`,`create_time`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
    PARTITION BY RANGE(create_time)(
    PARTITION p0 VALUES LESS THAN (1483200000),
    PARTITION p1 VALUES LESS THAN (1514736000),
    PARTITION p2 VALUES LESS THAN (1546272000),
    PARTITION p3 VALUES LESS THAN (1577808000)
    );
    

Q&A

Question 1:

If a table is not a partitioned table and already has data, can it become a partitioned table?

The answer is yes

ALTER TABLE customer_login_log PARTITION BY RANGE(YEAR(login_time))(
PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN (2019),
PARTITION p3 VALUES LESS THAN (2020)
);

Question 2:

After it becomes a partition table, is the data rearranged according to the partition?

The answer is that the data will be rearranged, but I/O consumption is very high when the data volume is large

This kind of table with large amount of data should be partitioned according to the way of modifying the table structure

  • Create a new partition table with the same structure
  • Data import
  • Table name switch

Question 3:

Now that we have separated the tables, how many partition tables do we have?

The answer is 1024

4. Restrictions and precautions of partition table

  • If the table has a primary key, it must contain the partition key of the partitioned table
  • Most of the time, you do not need to use a primary key when you use a partitioned table. The establishment of a primary key may affect performance
  • Don't create too many partitions
  • Partition table does not support foreign keys
  • Zoning rules must be set up in advance, otherwise it will be troublesome to modify them

5 partition table application scenarios

Characteristics of log table

  • The records are very many, very large
  • Obvious time interval characteristics
  • The query frequency is low, and the query frequency of the nearest time point is high
  • Periodic archive dumps are required

In such a table structure, partitioning tables is our best practice

Tags: less MySQL Attribute Database

Posted on Tue, 30 Jun 2020 00:40:43 -0400 by ghostrider1