Mysql Partition Function details, as well as instance analysis, mysql instance analysis

Mysql Partition Function details, as well as instance analysis, mysql instance analysis

I. What is database partition?

I wrote an article about mysql table sharding some time ago. The following describes what database partitions are. Take mysql as an example. The data in the mysql database is stored on the disk as a file, which is stored under/mysql/data by default (you can use my. in cnf), a table corresponds to three files, one is the frm table structure, the other is the myd table data, and the other is the myi table index. If the data volume of a table is too large, myd and myi will become very large, and the data query will become very slow. At this time, we can use the mysql partition function, physically, the three files corresponding to this table are divided into many small pieces. In this way, when we look for a piece of data, we don't need to look for it all, you only need to know where the data is, and then find it. If the data in the table is too large, a disk may not be available. In this case, we can allocate data to different disks.

Two partitioning Methods

1. Horizontal partitioning

What is a horizontal partition? It means Partitioning in a horizontal manner. For example, if there are million pieces of data, divide the data into ten portions, and put the first 10 million pieces of data into the first partition, the second 10 million data records are placed in the second partition, and so on. That is to say, we divide the table into very many tables, and use merge as the root table. When a piece of data is retrieved, the data contains all fields in the table structure, that is, the horizontal partition does not change the table structure.

2. vertical partitioning

What is vertical partitioning? The partition is vertical. For example, when designing a user table, we didn't consider it at the beginning, but put all the personal information in a table, in this way, there will be relatively large fields in this table, such as personal profiles, which may not be viewed by many people, so when someone looks at them, they will look for them, when you split a table, you can split such a large field.

I feel that the database partition seems to be an apple switch. Is it a horizontal switch or a vertical switch? According to my personal preferences, the Partitions provided by mysql belong to the first type, which is a horizontal partition, in addition, it can be subdivided into multiple methods. The following is an example.

Ii. mysql Partition

I think there is only one way to partition mysql. Instead, we use different algorithms to distribute data to different blocks.

1. MySQL and above support partition functions

During installation and installation, we can check

[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 that if the above is found, it indicates that it supports partitioning and is enabled 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) 

Check the variables. If they are supported, the above prompt will be displayed.

2. range partitioning

A table partitioned by RANGE is partitioned by the following method. Each partition contains rows whose partition expression values are located in a given continuous interval.

// CREATE a 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 is male, 1 is female,-> 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 INTO mysql> insert into 'test '. 'user' ('name', 'sex') VALUES ('tank', '0')->, ('zhang ', 1), ('ying ', 1), ('zhang ', 1), ('ying', 0), ('test1', 1), ('tank2', 1)->, ('tank1', 1), ('test2', 1), ('test3', 1), ('test4', 1), ('test5', 1 ), ('tank3', 1)->, ('tank3', 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 // you can check where the database table files are stored, my. cnf has configuration, [root @ BlackGhost test] # ls | grep user | xargs du-sh 4.0 K user # P # define MYD 4.0 K user # P # define myi 4.0 K user # P # p1.MYD 4.0 K user # P # p1.MYI 4.0 K user # P # p2.MYD 4.0 K user # P # p2.MYI 4.0 K user # P # p3.MYD 4.0 K user # P # p3.MYI 4.0 K user # P # p4.MYD 4.0 K user # P # p4.MYI 12 K user. frm 4.0 K user. par // retrieve 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. The fourth partition contains 14 data records, and the remaining three partitions have only 11 data records, however, the size of the collected files is 4.0 K. from this we can see that the minimum partition size is 4 K */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.0 K user # P # using MYD 4.0 K user # P # using myi 4.0 K user # P # p1.MYD 4.0 K user # P # p1.MYI 4.0 K user # P # p2.MYD 4.0 K user # P # p2.MYI 4.0 K user # P # p3.MYD 4.0 K user # P # p3.MYI 12 K user. frm 4.0 K user. par/* You can partition an existing table and automatically allocate the data in the table to the corresponding partition according to the rules. This makes it better and saves a lot of trouble, see 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) // shard 15 data Records: 15 Duplicates: 0 Warnings: 0 // a total of 15 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 results. This indicates that mysql> select count (*) has been successfully partitioned to an existing table (*) from aa; + ---------- + | count (*) | + ---------- + | 11 | + ---------- + 1 row in set (0.00 sec)

3. list partitions

In LIST partitions, the definition and selection of each partition are based on the value of a column from a value in a Value LIST set, and the RANGE partition is a set of continuous RANGE values.

// This method fails 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 is male, 1 is female,-> 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) ->); ERROR 1503 (HY000 ): a primary key must include all columns in the table's partitioning function // This method succeeds. 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 is male, 1 is female '->) 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) ->); Query OK, 0 rows affected (0.33 sec)

If a primary shard exists during list partition creation, the primary key must be in the primary partition; otherwise, an error is reported. If I don't need a primary key, the partition is successfully created. Generally, a table will have a primary key. This is a partition limitation.

If you want to test the data, see test the range partition.

4. hash Partition

HASH partitions are mainly used to ensure that data is evenly distributed among pre-defined partitions. All you need to do is to specify a column value or expression based on the column value to be hashed, the number of partitions to be split into and the specified table to be partitioned.

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, see operations on range partitions.

5. key Partition

Partitioning by KEY is similar to partitioning by HASH. Apart from the User-Defined expression used by HASH partition, the HASH function of KEY partition is provided by the MySQL server.

Mysql> create table if not exists 'key _ part' (-> 'news _ id' int (11) not null comment 'news id ', -> 'content' varchar (1000) not null default ''' COMMENT 'news content',-> 'U _ id' varchar (25) not null default ''comment' source IP address ',-> 'create _ time' date not null default '2017-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, see operations on range partitions.

6. subpartition

A subpartition is the re-division of each partition in a partition table. A subpartition can use both HASH and KEY partitions. This is also called composite partitioning ).

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

2. If a partition is created, the number of subpartitions in each partition must be the same

3. subpartitions in the same partition have different names. subpartitions in different partitions can have the same names (5.1.50 is not applicable)

Mysql> create table if not exists 'sub _ part' (-> 'news _ id' int (11) not null comment 'news id ', -> 'content' varchar (1000) not null default ''' COMMENT 'news content',-> 'U _ id' int (11) not null default 0 s COMMENT 'source ip',-> 'create _ time' date not null default '2017-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 subpartitions in different partitions can have the same name, but the following error will be prompted if mysql5.1.50 does not work.

ERROR 1517 (HY000): Duplicate partition name s1

Iii. Partition Management

1. delete partitions.

1.mysql> alter table user drop partition p4; 

2. Add partitions.

// Range add new partition 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 add new partition mysql> alter table list_part add partition (partition p4 values in (0.01, 26, 28); Query OK, 0 rows affected (sec) Records: 0 Duplicates: 0 Warnings: 0 // hash to repartition mysql> alter table hash_part add partition partitions 4; Qu Ery OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 // key re-partition mysql> alter table key_part add partition partitions 4; Query OK, 1 row affected (0.06 sec) // if there is data, Records: 1 Duplicates: 0 Warnings: 0 // Add a new partition to the subpartition. Although I do not specify a subpartition, however, the system will add partition (partition p3 values less than MAXVALUE) to the mysql> alter table subpartition part named in the subpartition; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicate S: 0 Warnings: 0 mysql> show create table subpartition part \ G; * *************************** 1. row *************************** Table: subpartition part Create Table: create table 'sub1 _ part' ('news _ id' int (11) not null comment 'news 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 '2017-00-00 'comment' Time') EN GINE = 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, // SUBPARTITION name is automatically generated SUBPARTITION p3sp1 ENGINE = InnoDB, SUBPARTITION p3sp2 ENGINE = InnoDB )) 1 row in set (0.00 sec)

3. repartition

// Range PARTITION 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 repartition mysql> alter table list_part reorganize partition p0, p1, p2, p3, p4 INTO (PARTITION p0 VALUES in (0.28, 5); Query OK, 0 rows affected (sec) Records: 0 Duplicates: 0 Warnings: 0 // The hash and key partitions cannot be REORGANIZE. The official website clearly states that 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

Iv. Advantages of partitioning

1. partitions can be divided into multiple disks to store more disks.

2. Based on the search condition, that is, the condition after the where clause, you do not need to find all the corresponding partitions.

3. Parallel processing can be performed for big data search.

4. distribute data queries across multiple disks to achieve higher query Throughput

The above mysql Partition Function details and instance analysis are all the content that I have shared with you. I hope you can give us a reference and support the help house.