mysql's partitioning techniques are described in detail

1. An overview of the

When the total number of records in MySQL exceeds 1 million, will there be a significant performance degradation? The answer is yes, but performance degrades > Depending on the architecture of the system, the application, and the > It depends on a number of factors including indexes, server hardware, etc. When a netizen asked me this question, my most common answer was > Is: sub-table, can be based on id interval or time sequence and other rules to sub-table. Sorting is easy, but the resulting application or even architectural changes are not > It also includes future expansibility.

Previously, one solution was to use MERGE
Type, this is a very convenient cooking. The architecture and programs are largely unchanged, but the disadvantages are obvious:

1. It can only be used on MyISAM table with the same structure
2. Unable to enjoy the full functionality of MyISAM, such as the ability to perform FULLTEXT searches on the MERGE type
3. It requires more file descriptors
4. Reading indexes is slower

At this point, the advantages of the new partitioning feature in MySQL 5.1 (Partition) are obvious:

1. You can store more data than a single disk or file system partition
2. It's easy to delete unused or outdated data
3.1 Some queries can be greatly optimized
4. When it comes to aggregate functions such as SUM()/COUNT(), it can be carried out in parallel
5. Greater IO throughput

Partitions allow rules that can be set to any size to allocate multiple parts of a single table across file systems. In fact, different parts of the table are stored as separate tables in different locations.

Precautions for partitioning:

1, do the partition, or do not define the primary key, or add the partition field to the primary key.
2, the partition field cannot be NULL, otherwise how to determine the partition scope, so try NOT NULL

2. Type of partition

1.RANGE partition: Assigns multiple rows to a partition based on column values belonging to a given continuous interval.
2.LIST partitioning: Similar to RANGE partitioning, the difference is that LIST partitioning is based on the selection of column values matching a value in a discrete set of values.
2.HASH partition: A partition selected based on the return value of a user-defined expression that evaluates to the column value of the rows to be inserted into the table. This function can be wrapped > Contains any expression that is valid in MySQL and produces a non-negative integer value.
3.KEY partitioning: Similar to HASH partitioning, except that KEY only supports computing one or more columns, and the MySQL server provides its own hash function. Must contain one or more columns > An integer value.

You can determine whether MySQL supports partitioning by using the SHOW VARIABLES command, for example:

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| have_partition_engine | YES   |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE '%partition%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| have_partition_engine | YES   |

+-----------------------+-------+

1 row in set (0.00 sec)

1. range partition

　create table t_range( 

　　id int(11), 

　　money int(11) unsigned not null, 

　　date datetime 

　　)partition by range(year(date))( 

　　partition p2007 values less than (2008), 

　　partition p2008 values less than (2009), 

　　partition p2009 values less than (2010) 

　　partition p2010 values less than maxvalue 

　　) ; 

2. list partition

create table t_list( 

　　a int(11), 

　　b int(11) 

　　)(partition by list (b) 

　　partition p0 values in (1,3,5,7,9), 

　　partition p1 values in (2,4,6,8,0) 

　　);

For innodb and myisam engines, when a statement inserts more than one record, if a value in the middle cannot be inserted, innodb will roll back all of its data and myisam's data before the error value can be inserted into the table. For the innodb and myisam engines, when a statement inserts multiple records, if a value in the middle cannot be inserted, innodb will all roll back and myisam's data before the error value can be inserted into the table.

3. hash partition

The purpose of THE hash partition is to distribute the data evenly among the pre-defined partitions, ensuring that the amount of data in each partition is approximately 1.

create table t_hash( 

　　a int(11), 

　　b datetime 

　　)partition by hash (YEAR(b) 

　　partitions 4;

The partition function page of hash needs to return 1 integer value. The value in the partitions clause is 1 non-negative integer, and the default number of partitions is 1 if the partitions clause is not added.

4. key partition

key is similar to hash except that hash is partitioned using user-defined functions, key is partitioned using functions provided by the mysql database, NDB cluster is partitioned using MD5 functions, and for other storage engines mysql USES internal hash functions based on password() 1-like algorithms.

create table t_key( 

　　a int(11), 

　　b datetime) 

　　partition by key (b) 

　　partitions 4;

5. columns partition

In the above RANGE, LIST, HASH, KEY4 partitions, the condition of the partition must be plastic, if not plastic, it needs to be converted to plastic through the function.

mysql-5.5 began to support COLUMNS partitioning, which can be seen as an evolution of RANGE and LIST, and COLUMNS can be partitioned directly using non-plastic data. The COLUMNS partition supports the following data types:
All plastic, INT SMALLINT TINYINT BIGINT. FLOAT and DECIMAL are not supported.
Date types such as DATE and DATETIME. The remaining date types are not supported.
String types such as CHAR, VARCHAR, BINARY, and VARBINARY. BLOB and TEXT types are not supported.
COLUMNS can be partitioned using multiple columns.

The new partition

mysql> ALTER TABLE sale_data
    ->   ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

Deleted partitions

-- When removed 1 A partition that also deletes all data in that partition. 
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

Partition merging

With SQL below, merge p201001-p201009 into 3 partitions p2010Q1-p2010Q3

mysql> ALTER TABLE sale_data
    ->   REORGANIZE PARTITION p201001,p201002,p201003,
    ->                        p201004,p201005,p201006,
    ->                        p201007,p201008,p201009 INTO
    -> (
    ->   PARTITION p2010Q1 VALUES LESS THAN (201004),
    ->   PARTITION p2010Q2 VALUES LESS THAN (201007),
    ->   PARTITION p2010Q3 VALUES LESS THAN (201010)
    -> );
Query OK, 0 rows affected (1.14 sec)
Records: 0  Duplicates: 0  Warnings: 0