MySQL Table Partitioning

MySQL table partitioning and sub-library tables are all designed to increase the throughput of the database. Partitions are similar to sub-tables, which logically divide a large amount of data into multiple tables, either horizontally or vertically. Partitioning is the splitting of a data file of a table into multiple. Different data is split into different files. This improves the IO performance of the database by storing multiple data files for a table with very large data volumes.

Now that we are working on the file for the data table, we need to understand the storage of the MySQL table first. We know that MySQL has a variety of storage engines, and different storage engines store different file formats. This is mainly illustrated by the two storage engines, InnoDB and MyISAM.

InnoDB

Structure of the. frm File data table

. IDB file table data files, exclusive tablespace, each table has an. idb file

. ibdata File table data file, shared table space, all tables use this one data

File

MyISAM

Structure of the. frm File data table

. myd File Data files

. myi File Index file

First, check to see if our current database version supports partitioning

1 show variables like '%partition% ';

How do you partition it? At the time of the database slicing, we know that the horizontal slice can be divided into different tables according to the mode of the specified field, or it can be sliced according to the date, or segmented by ID, 11 million in the first table, 1,000,001 to 2 million in the second table and so on. In short we have a lot of ways in the process of slicing. Then the database on the table partition gives us a variety of options to choose from.

MySQL Table Partitioning Policy

The RANGE partition assigns multiple rows to a partition based on the column values that belong to a given contiguous interval

1 DROP TABLE IF EXISTS ' p_range ';2 CREATE TABLE ' P_range ' (3' ID 'int(10) not NULL auto_increment,4' Name 'Char(20) not NULL,5 PRIMARY KEY (' id ')6) Engine=myisam auto_increment=9 DEFAULT charset=UTF87 /*!50100 PARTITION by RANGE (ID)8 (PARTITION p0 VALUES less THAN (8) ENGINE = MyISAM)*/;

Maximum Value

1 PARTITION by RANGE (ID) 2 (3 PARTITION p0 values less THAN (8),4 PARTITION p1 values less THAN MAXVALUE)

Applicable scenarios:

This means that all data records with IDs greater than 7 exist in the P1 partition.

RANGE partitioning is particularly useful in the following situations:

• When you need to delete "old" data. If you use the partitioning scheme given in the recent example above, you simply use "ALTER TABLE employees drop PARTITION p0;" To remove all rows corresponding to all employees who have stopped working 1991 years ago. For a table with a large number of rows, this is more than running a "DELETE from employees where year (separated) <=

1990; "A DELETE query like this is much more effective.

• You want to use a column that contains a date or time value, or that contains a value that begins to grow from some other series.

• Frequently run queries that depend directly on the columns used to split the table. For example, when executing a

"Select COUNT (*) from Employees WHERE Year (separated) = $ GROUP by store_id;" When such a query, MySQL can very quickly determine that only the partition P2 need to be scanned, because the remaining points Zone cannot contain any records that conform to the WHERE clause

A list partition is similar to partitioning by RANGE, except that a list partition is selected based on a value in a set of discrete values that matches a column value.

1 DROP TABLE IF EXISTS ' p_list ';2 CREATE TABLE ' p_list ' (3' ID 'int(10) not NULL auto_increment,4' typeID ' Mediumint (Ten) not NULL DEFAULT ' 0 ',5' TypeName 'Char(20) DEFAULT NULL,6 PRIMARY KEY (' id ', ' typeid ')7) Engine=myisam auto_increment=9 DEFAULT charset=UTF88 /*!50100 PARTITION by LIST (typeid)9 (PARTITION p0 values in (1,2,3,4) engine = MyISAM, PARTITION p1 values in (5,6,7,8) engine = MyISAM)*/;

A HASH partition selects a partition based on the return value of a user-defined expression, which is calculated using the column values of those rows that will be inserted into the table. This function can contain any expression that is valid in MySQL that produces a non-negative integer value. HASH partitioning is primarily used to ensure that data is evenly distributed over a predetermined number of partitions. In the RANGE and list partitions, you must explicitly specify which partition a given column value or set of column values should be kept in, and in the hash partition, MySQL does the work automatically, all you have to do is to specify a column value or expression based on the column value that will be hashed. and the number of partitions that the partitioned table will be divided into

1 DROP TABLE IF EXISTS ' P_hash ';2 CREATE TABLE ' P_hash ' (3' ID 'int(10) not NULL auto_increment,4' StoreID ' Mediumint (Ten) not NULL DEFAULT ' 0 ',5' Storename 'Char(255) DEFAULT NULL,6 PRIMARY KEY (' id ', ' storeid ')7) Engine=innodb auto_increment=11 DEFAULT charset=UTF88 /*!50100 PARTITION by HASH (StoreID)9 Partitions 4*/;

The simple point is that the data can be deposited according to partition by hash (expr); The expr here can

is the key name or it can be an expression such as year (time), in the case of an expression

"But remember that whenever you insert or update (or maybe delete) a row, the expression counts

This means that very complex expressions can cause performance problems, especially when executing

An operation that affects a large number of rows (for example, BULK Insert). ”

This expression is evaluated once when performing a delete, write, or update.

The distribution of the data uses a modulus based on the results of the user function to determine which numbered partition to use. In other words, for an expression "expr", the partition number that will save the record is N, where "n = MOD (expr, num)".

For example, the above StoreID is 10; so N=mod (10,4); N is equal to 2, then this record is stored in the P2 partition.

If you insert an expression that has a column value of ' 2005-09-15′ ' to the table, the partition that holds the record is determined as follows: MoD (year (' 2005-09-01′), 4) = mod (2005,4) = 1; is stored in the P1 partition.

Partition Attention Point

1, repartitioning, if the original partition inside the MaxValue, then the new partition must also contain

MaxValue otherwise it is wrong.

ALTER TABLE P_RANGE2X

Reorganize partition P1,P2

into (partition P0 values less than (5), partition P1 values less than maxvalue);

[ERR] 1520–reorganize of range partitions cannot change total ranges except for last partition where it can extend the R Ange

2, when the partition is deleted, the data will also be deleted ALTER TABLE p_range drop partition p0;

3. If there is no maxvalue in the range partition list, the data cannot be written to the database table if the new data is greater than the current partition range data value.

4, modify the table name does not need to delete the partition after the change, modify the table name after the partition storage myd myi corresponding will also automatically change.

If you want to delete all the data from all partitions, but retain the table definition and partition mode of the table, use the TRUNCATE Table command. (see section 13.2.9, "TRUNCATE syntax").

If you want to change the partition of a table without losing data, use the ALTER TABLE ... REORGANIZE PARTITION "statement. Refer to the following, or in section 13.1.2, "ALTER TABLE Syntax" For information about REORGANIZE PARTITION.

5, when partitioning the table, regardless of the partitioning method if there is a primary key in the table, then the primary key must be in the partition column. Limitations of Table partitioning.

6, List mode partition is not similar to the range of less than MaxValue, that is, all the data of the list partition table must be in the Partition field Value list collection.

7. In MySQL version 5.1, all partitions of the same partitioned table must use the same storage engine, for example, you cannot use MyISAM for one partition and InnoDB for another.

8, the name of the partition is not case-sensitive, MYP1 and MYP1 are the same.

Reference video content: http://www.roncoo.com/course/view/658088f6e77541f5835b61800314083e

MySQL Table Partitioning