Detailed analysis of Table Partitioning technology in MySQL and table parsing in mysql

Detailed analysis of Table Partitioning technology in MySQL and table parsing in mysql

MySQL Partitioning technology (mysql 5.1 is used after version-> it is the technology that the maintenance personnel of the Oracle mysql Technical Team inserted into mysql in the form of plug-ins)

1. Overview

When a single database table reaches a certain amount, the performance will degrade, such as mysql or SQL server. Therefore, you need to partition the data. At the same time, data may be stripped or something, so partition tables are more useful!

The Partition function added in MySQL 5.1 began to increase, and its advantages became more and more obvious:

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
6. Partitions can be set to rules of any size, and multiple parts of a single table are allocated across file systems. In fact, different parts of a table are stored as separate tables in different locations.
   

2. Partition Technical Support

Before 5.6, use this parameter to check whether the configuration supports partitions:

mysql> SHOW VARIABLES LIKE '%partition%';+-----------------------+-------+|Variable_name     | Value |+-----------------------+-------+| have_partition_engine | YES  |+-----------------------+-------+

If yes, your current configuration supports partitions. You can view it in the following way after 5.6 and use it:

mysql> SHOW PLUGINS;+----------------------------+----------+--------------------+---------+---------+| Name            | Status  | Type        | Library | License |+----------------------------+----------+--------------------+---------+---------+| binlog           | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   || mysql_native_password   | ACTIVE  | AUTHENTICATION   | NULL  | GPL   |..................................................................................| INNODB_LOCKS        | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   || INNODB_LOCK_WAITS     | ACTIVE  | INFORMATION SCHEMA | NULL  | GPL   || partition         | ACTIVE  | STORAGE ENGINE   | NULL  | GPL   |+----------------------------+----------+--------------------+---------+---------+

42 rows in set (0.00 sec) last line, we can see that partition is ACTIVE, indicating that partitions are supported.

3. Partition types and Examples

3.1 range Partition

RANGE partition: multiple rows are allocated to the partition based on the column values in a given continuous interval. For example, time and continuous constant value-partition by year

mysql> use mytest;Database changedmysql> create table range_p(   -> perid int(11),   -> pername char(12) not null,   -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) partition by range(year(credate))(   -> partition p2011 values less than (2011),   -> partition p2012 values less than (2012),   -> partition p2013 values less than (2013),   -> partition p2014 values less than (2014),  -> partition p2015 values less than maxvalue   -> );Query OK, 0 rows affected (0.12 sec)

3.2 List partitions

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. For example, attribute values are similar to gender.

mysql> create table list_p( 
  -> perid int(11), 
  -> pername char(12) not null,
  -> sex int(1) not null,
  -> monsalary DECIMAL(10,2),
  -> credate datetime 
  -> ) partition by list(sex) (
  -> partition psex1 values in(1),
  -> partition psex2 values in(2));
Query OK, 0 rows affected (0.06 sec)

Note: The list can only be a number. If you use a character, ERROR 1697 (HY000): VALUES value for partition 'wait X1 'must have type INT is returned.

3.3 discrete partitions

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 include any expressions that contain valid non-negative integer values in MySQL.

-- Hash partitions with int Fields

create table hash_p( perid int(11), pername char(12) not null,sex int(1) not null,monsalary DECIMAL(10,2),credate datetime ) partition by hash (perid) partitions 8;

-- Hash partitions Using Time Functions

mysql> create table hash_p(   -> perid int(11),   -> pername char(12) not null,  -> sex int(1) not null,  -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) partition by hash (year(credate))   -> partitions 8;Query OK, 0 rows affected (0.11 sec)

3.4 key-value partition

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. Its partitioning method is similar to hash:

mysql> create table key_p(   -> perid int(11),   -> pername char(12) not null,  -> sex int(1) not null,  -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) partition by key (perid)   -> partitions 8;Query OK, 0 rows affected (0.12 sec)

3.5 Other Instructions

The mysql-5.5 started to support COLUMNS partitions, which can be seen as the evolution of RANGE and LIST partitions, where COLUMNS partitions can be directly partitioned using non-integer data. COLUMNS partitions support the following data types: All Integer types, such as int smallint tinyint bigint. FLOAT and DECIMAL are not supported. DATE type, such as DATE and DATETIME. Other date types are not supported. String type, such as CHAR, VARCHAR, BINARY, and VARBINARY. BLOB and TEXT types are not supported. COLUMNS can be partitioned using multiple COLUMNS.

mysql> create table range_p(   -> perid int(11),   -> pername char(12) not null,   -> monsalary DECIMAL(10,2),  -> credate datetime   -> ) PARTITION BY RANGE COLUMNS (credate)(   -> partition p20151 values less than ('2015-04-01'),   -> partition p20152 values less than ('2015-07-01'),   -> partition p20153 values less than ('2015-10-01'),   -> partition p20154 values less than ('2016-01-01'),  -> partition p20161 values less than ('2016-04-01'),  -> partition partlog values less than maxvalue   -> );Query OK, 0 rows affected (0.12 sec)

Summary:

Partition Table is a new feature in MySQL5.1. As of MySQL5.1.22-rc, partition technology is not very mature and many partition maintenance and management functions are not implemented. For example, the data storage space in a partition is recycled, the partition is repaired, and the partition is optimized. MySQL partitions can be used in tables that can be deleted by partition, and the database is not modified much, tables that frequently query by partition field (for example, statistical tables in malicious code are partitioned by day, and are often queried and grouped by time, and partitions can be deleted by day ). In addition, because MySQL has no global index but only a partition index, when one table has two unique indexes [z5], this table cannot be partitioned. The partition column must contain the primary key. Otherwise, MySQL reports an error.

In short, MySQL imposes many restrictions on partitions, and I personally think that the hash and key partitions are not of great significance.

Partition introduces a new method for optimizing queries (of course, there are also corresponding shortcomings ). The optimizer can use the Partition Function to trim partitions or completely remove partitions from the query. It determines whether data can be found in a specific partition to achieve this optimization. Therefore, in the best case, trimming allows queries to access less data. It is important to define the partition key in the WHERE clause, even if it looks redundant. Through the partition key, the optimizer can remove unnecessary partitions. Otherwise, the execution engine will access all partitions of the table as it does in the merged table, which will be very slow on the large table. Partition data is better maintained than non-partition data, and old data can be removed by deleting partitions. Partition data can be distributed to different physical locations, so that the server can more effectively use multiple hard drive.

[Z1] the return value of a partition function must be an integer. the return value of a new partition function must be greater than that of any existing partition function.
[Z2] error message for tables with primary keys: #1503
A primary key must include all columns inthe table's partitioning function. If there is no primary key, there is no such constraint.
[Z3] Note: For tables partitioned by RANGE, you can only use add partition to ADD new partitions to the high-end PARTITION list. That is, you cannot add a partition with a smaller range than this partition.
[Z4] For tables partitioned by RANGE, adjacent partitions can only be reorganized. RANGE partitions cannot be skipped. You cannot use REORGANIZEPARTITION to change the table's partition type. That is to say, for example, you cannot change the RANGE partition to a HASH partition, and vice versa. You cannot use this command to change the partition expression or column.
[Z5] note the differences between primary keys and unique indexes.

Official: https://dev.mysql.com/doc/refman/5.5/en/partitioning.html