Mysql Zone Actual Warfare

First, what is database partitioning

Recently I wrote an article about MySQL table partitioning. Now let's talk about what database partitioning is. Take MySQL as an example. The data in MySQL database is stored on disk in the form of files. By default, it is placed under / mysql/data (which can be viewed by datadir in my.cnf). A table mainly corresponds to three files. One is frm storage table structure, the other is myd storage table data, and the other is myi storage table index. If the amount of data in a table is too large, then myd and myi will become very large, and the search of data will become very slow. At this time, we can use MySQL partitioning function to physically divide the three files corresponding to this table into many small pieces, so that when we search for a data, we do not need all of them. Find it, as long as you know where the data is, and then find it in that one. If the table's data is too large, one disk may not fit. At this time, we can allocate the data to different disks.

Two ways of partitioning

1. Horizontal zoning

What is horizontal partition? For example, if there are 100W pieces of data, which are divided into ten parts, the first 10W pieces of data are put in the first partition, the second 10W pieces of data are put in the second partition, and so on. That is to say, divide the table into ten parts, and use merge to divide the table. It's a bit like that. When a piece of data is extracted, the data contains all the fields in the table structure, that is to say, the horizontal partition does not change the structure of the table.


    ALTER TABLE `yl_hospital_url` PARTITION BY RANGE(ID) (  
    PARTITION `p0` VALUES LESS THAN (100000) ,   
    PARTITION `p1` VALUES LESS THAN (200000) ,   
    PARTITION `p2` VALUES LESS THAN (300000) ,   
    PARTITION `p3` VALUES LESS THAN (400000) ,   
    PARTITION `p4` VALUES LESS THAN (500000) ,   
    PARTITION `p5` VALUES LESS THAN (600000) ,   
    PARTITION `p6` VALUES LESS THAN (700000) ,  
    PARTITION `p6` VALUES LESS THAN (700000) ,   
    PARTITION `p7` VALUES LESS THAN (MAXVALUE)   
    ) ;  

Second, the partition of mysql

I think there's only one way to partition mysql, just using different algorithms and rules to distribute data to different blocks.

1, MySQL 5.1 and above support partitioning

When installing, we can check it out.


    [root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition  
     === Partition Support ===  
     Plugin Name:      partition  
     Description:      MySQL Partitioning Support  
     Supports build:   static  
     Configurations:   max, max-no-ndb  

Check it out. If you find the above item, it means that it supports partitioning, which is open by default. If you have installed mysql


    mysql> show variables like "%part%";  
    +-------------------+-------+  
    | Variable_name     | Value |  
    +-------------------+-------+  
    | have_partitioning | YES   |  
    +-------------------+-------+  
    1 row in set (0.00 sec)  

Look at the variables, and if supported, there will be a hint above.

2, range partition

The table partitioned according to the RANGE partition is partitioned in the following way, each partition contains rows whose partition expression values are located in a given continuous interval.


    //Create range partition table  
    mysql> CREATE TABLE IF NOT EXISTS `user` (  
     ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user ID',  
     ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name',  
     ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 For men, 1 for women',  
     ->   PRIMARY KEY (`id`)  
     -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
     -> PARTITION BY RANGE (id) (  
     ->     PARTITION p0 VALUES LESS THAN (3),  
     ->     PARTITION p1 VALUES LESS THAN (6),  
     ->     PARTITION p2 VALUES LESS THAN (9),  
     ->     PARTITION p3 VALUES LESS THAN (12),  
     ->     PARTITION p4 VALUES LESS THAN MAXVALUE  
     -> );  
    Query OK, 0 rows affected (0.13 sec)  
      
    //Insert some data  
    mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0')  
     -> ,('zhang',1),('ying',1),('Zhang',1),('Reflect',0),('test1',1),('tank2',1)  
     -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)  
     -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)  
     -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);  
    Query OK, 25 rows affected (0.05 sec)  
    Records: 25  Duplicates: 0  Warnings: 0  
      
    //Look at the place where the database table files are stored. my.cnf has configuration. After datadir  
    [root@BlackGhost test]# ls |grep user |xargs du -sh  
    4.0K    user#P#p0.MYD  
    4.0K    user#P#p0.MYI  
    4.0K    user#P#p1.MYD  
    4.0K    user#P#p1.MYI  
    4.0K    user#P#p2.MYD  
    4.0K    user#P#p2.MYI  
    4.0K    user#P#p3.MYD  
    4.0K    user#P#p3.MYI  
    4.0K    user#P#p4.MYD  
    4.0K    user#P#p4.MYI  
    12K    user.frm  
    4.0K    user.par  
      
    //Extract data  
    mysql> select count(id) as count from user;  
    +-------+  
    | count |  
    +-------+  
    |    25 |  
    +-------+  
    1 row in set (0.00 sec)  
      
    //Delete the fourth partition  
    mysql> alter table user drop partition p4;  
    Query OK, 0 rows affected (0.11 sec)  
    Records: 0  Duplicates: 0  Warnings: 0  
      
    /**The data stored in the partition is lost. There are 14 data in the fourth partition and the remaining three partitions. 
    There are only 11 pieces of data, but all the files are 4.0K in size. From here we can see the partition. 
    The smallest block is 4K 
    */  
    mysql> select count(id) as count from user;  
    +-------+  
    | count |  
    +-------+  
    |    11 |  
    +-------+  
    1 row in set (0.00 sec)  
      
    //The fourth block has been deleted  
    [root@BlackGhost test]# ls |grep user |xargs du -sh  
    4.0K    user#P#p0.MYD  
    4.0K    user#P#p0.MYI  
    4.0K    user#P#p1.MYD  
    4.0K    user#P#p1.MYI  
    4.0K    user#P#p2.MYD  
    4.0K    user#P#p2.MYI  
    4.0K    user#P#p3.MYD  
    4.0K    user#P#p3.MYI  
    12K    user.frm  
    4.0K    user.par  
      
    /*Existing tables can be partitioned, and the data in the tables can be automatically partitioned according to rules. 
    In this way, we can save a lot of things. Look at the following operations*/  
    mysql> alter table aa partition by RANGE(id)  
     -> (PARTITION p1 VALUES less than (1),  
     -> PARTITION p2 VALUES less than (5),  
     -> PARTITION p3 VALUES less than MAXVALUE);  
    Query OK, 15 rows affected (0.21 sec)   //Partitioning 15 data  
    Records: 15  Duplicates: 0  Warnings: 0  
      
    //There are altogether 15 articles.  
    mysql> select count(*) from aa;  
    +----------+  
    | count(*) |  
    +----------+  
    |       15 |  
    +----------+  
    1 row in set (0.00 sec)  
      
    //Delete a partition  
    mysql> alter table aa drop partition p2;  
    Query OK, 0 rows affected (0.30 sec)  
    Records: 0  Duplicates: 0  Warnings: 0  
      
    //There are only 11, indicating that the existing table partitioning has been successful.  
    mysql> select count(*) from aa;  
    +----------+  
    | count(*) |  
    +----------+  
    |       11 |  
    +----------+  
    1 row in set (0.00 sec)   

3, list partition

The definition and selection of each partition in LIST partition is based on that the value of a column belongs to a value list set, while the RANGE partition belongs to a set of continuous interval values.


    //Failure in this way  
    mysql> CREATE TABLE IF NOT EXISTS `list_part` (  
     ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user ID',  
     ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT 'province',  
     ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name',  
     ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 For men, 1 for women',  
     ->   PRIMARY KEY (`id`)  
     -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
     -> PARTITION BY LIST (province_id) (  
     ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
     ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
     ->     PARTITION p2 VALUES IN (13,14,15,19),  
     ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
     -> );  
    ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function  
      
    //Successful in this way  
    mysql> CREATE TABLE IF NOT EXISTS `list_part` (  
     ->   `id` int(11) NOT NULL  COMMENT 'user ID',  
     ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT 'province',  
     ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name',  
     ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 For men, 1 for women'  
     -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
     -> PARTITION BY LIST (province_id) (  
     ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
     ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
     ->     PARTITION p2 VALUES IN (13,14,15,19),  
     ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
     -> );  
    Query OK, 0 rows affected (0.33 sec)  

When the list partition is created above, if there is a primary key in the partition, the primary key must be in it, otherwise an error will be reported. If I don't use the primary key, the partition will be created successfully. In general, a table must have a primary key, which is the limitation of a partition.

If you test data, refer to the range partition test for operation.

4, hash partition

HASH partitions are mainly used to ensure that data is evenly distributed in pre-determined number of partitions. All you have to do is specify a column value or expression based on the column value to be hashed and the number of partitions that the partitioned table will be partitioned into.


    mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  
     ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'comment ID',  
     ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT 'comment',  
     ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT 'source IP',  
     ->   PRIMARY KEY (`id`)  
     -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
     -> PARTITION BY HASH(id)  
     -> PARTITIONS 3;  
    Query OK, 0 rows affected (0.06 sec)  

For testing, please refer to the operation of range partition.

5, key partition

Partitioning by KEY is similar to partitioning by HASH, except for user-defined expressions used by HASH partitions, and the hash function of KEY partitions is provided by MySQL servers.


    mysql> CREATE TABLE IF NOT EXISTS `key_part` (  
     ->   `news_id` int(11) NOT NULL  COMMENT 'Journalism ID',  
     ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',  
     ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'source IP',  
     ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'time'  
     -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
     -> PARTITION BY LINEAR HASH(YEAR(create_time))  
     -> PARTITIONS 3;  
    Query OK, 0 rows affected (0.07 sec)  

For testing, please refer to the operation of range partition.

6. Subpartition

Subpartitions are re-partitions of each partition in the partition table. Subpartitions can use either HASH or KEY partitions. This is also called composite partitioning.

1. If a subpartition is created in one partition, the other partitions should also have subpartitions

2. If partitions are created, the number of subpartitions in each partition must be the same

3. Subpartitions in the same partition have different names. Subpartition names in different partitions can be the same (5.1.50 does not apply).


    mysql> CREATE TABLE IF NOT EXISTS `sub_part` (  
     ->   `news_id` int(11) NOT NULL  COMMENT 'Journalism ID',  
     ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',  
     ->   `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT 'source IP',  
     ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'time'  
     -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
     -> PARTITION BY RANGE(YEAR(create_time))  
     -> SUBPARTITION BY HASH(TO_DAYS(create_time))(  
     -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),  
     -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),  
     -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)  
     -> );  
    Query OK, 0 rows affected (0.07 sec)  

The official website says that sub-partitions in different partitions can have the same name, but MySQL 5.1.50 will not prompt the following error

ERROR 1517 (HY000): Duplicate partition name s1

7, Columns partition

Columns partition is a partition type introduced in MySQL 5.5. The introduction of Columns partition solves the problem that RANGE partition and LIST partition only support integer partition before MySQL 5.5, resulting in the need for additional functions to compute integers or convert them into integer partitions through additional conversion tables. Columns partitions can be subdivided into RANGE Columns partitions and LIST Columns partitions. Both RANGE Columns partitions and LIST Columns partitions support integer, date, time and string data types.

Application scenarios:

Daily reports, annual reports, etc. of commodity sales

Every day is divided into a table. Each table is divided into 24 sub-areas by year, month and day, and the data per hour is divided into one sub-area.


    CREATE TABLE `year_log` (  
      `id` int(11) DEFAULT NULL,  
      `money` int(11) unsigned NOT NULL,  
      `date` datetime DEFAULT NULL  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
    PARTITION BY RANGE (year(date))  
    (  
    PARTITION p2007 VALUES LESS THAN (2008),  
    PARTITION p2008 VALUES LESS THAN (2009),  
    PARTITION p2009 VALUES LESS THAN MAXVALUE  
    );  
      
    CREATE TABLE `daily_log` (  
    `id` int(11) NOT NULL,  
    `sid` char(36) NOT NULL,  
    `sname` char(20) DEFAULT NULL,  
    `date` datetime NOT NULL,  
    PRIMARY KEY (`id`,`date`)  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
    PARTITION BY RANGE COLUMNS(`date`)  
    (PARTITION p1 VALUES LESS THAN ('2000-01-02'),  
    PARTITION p2 VALUES LESS THAN ('2000-01-03'),  
    PARTITION p3 VALUES LESS THAN ('2000-01-04'),  
    PARTITION p4 VALUES LESS THAN ('2000-01-05'),  
    PARTITION p5 VALUES LESS THAN ('2000-01-06'),  
    PARTITION p6 VALUES LESS THAN ('2000-01-07'),  
    PARTITION p7 VALUES LESS THAN ('2000-01-08'),  
    PARTITION p367 VALUES LESS THAN (MAXVALUE));  

III. Zoning Management

1. Delete partitions

mysql> alter table user drop partition p4;    

2. New partitions


    //range adds new partitions  
    mysql> alter table user add partition(partition p4 values less than MAXVALUE);  
    Query OK, 0 rows affected (0.06 sec)  
    Records: 0  Duplicates: 0  Warnings: 0  
      
    //list adds new partitions  
    mysql> alter table list_part add partition(partition p4 values in (25,26,28));  
    Query OK, 0 rows affected (0.01 sec)  
    Records: 0  Duplicates: 0  Warnings: 0  
      
    //hash redistribution  
    mysql> alter table hash_part add partition partitions 4;  
    Query OK, 0 rows affected (0.12 sec)  
    Records: 0  Duplicates: 0  Warnings: 0  
      
    //key redistribution  
    mysql> alter table key_part add partition partitions 4;  
    Query OK, 1 row affected (0.06 sec)    //Data will also be redistributed  
    Records: 1  Duplicates: 0  Warnings: 0  
      
    //A new partition is added to the subpartition. Although I did not specify a subpartition, the system will name the subpartition.  
    mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);  
    Query OK, 0 rows affected (0.02 sec)  
    Records: 0  Duplicates: 0  Warnings: 0  
      
    mysql> show create table sub1_part\G;  
    *************************** 1. row ***************************  
     Table: sub1_part  
    Create Table: CREATE TABLE `sub1_part` (  
     `news_id` int(11) NOT NULL COMMENT 'Journalism ID',  
     `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',  
     `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'source IP',  
     `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT 'time'  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
    !50100 PARTITION BY RANGE (YEAR(create_time))  
    SUBPARTITION BY HASH (TO_DAYS(create_time))  
    (PARTITION p0 VALUES LESS THAN (1990)  
     (SUBPARTITION s0 ENGINE = InnoDB,  
     SUBPARTITION s1 ENGINE = InnoDB,  
     SUBPARTITION s2 ENGINE = InnoDB),  
     PARTITION p1 VALUES LESS THAN (2000)  
     (SUBPARTITION s3 ENGINE = InnoDB,  
     SUBPARTITION s4 ENGINE = InnoDB,  
     SUBPARTITION good ENGINE = InnoDB),  
     PARTITION p2 VALUES LESS THAN (3000)  
     (SUBPARTITION tank0 ENGINE = InnoDB,  
     SUBPARTITION tank1 ENGINE = InnoDB,  
     SUBPARTITION tank3 ENGINE = InnoDB),  
     PARTITION p3 VALUES LESS THAN MAXVALUE  
     (SUBPARTITION p3sp0 ENGINE = InnoDB,    //Names of subpartitions are automatically generated  
     SUBPARTITION p3sp1 ENGINE = InnoDB,  
     SUBPARTITION p3sp2 ENGINE = InnoDB))  
    1 row in set (0.00 sec)  

3. Rezoning


    //range re-partitioning  
    mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  
    Query OK, 11 rows affected (0.08 sec)  
    Records: 11  Duplicates: 0  Warnings: 0  
      
    //list re-partitioning  
    mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));  
    Query OK, 0 rows affected (0.28 sec)  
    Records: 0  Duplicates: 0  Warnings: 0  
      
    //REORGANIZE is not available for hash and key partitions. The official website makes it clear.  
    mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;  
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1  

Fourth, the advantages of zoning

1. Partitions can be divided into multiple disks and stored a little more.

2. According to the search condition, that is, the condition behind where, the search only finds the corresponding partition and does not need to find all the partitions.

3. Parallel processing can be performed in large data search.

4. Decentralize data queries across multiple disks to achieve greater query throughput

Tags: MySQL less P4 Database

Posted on Fri, 06 Sep 2019 01:19:40 -0700 by clicket