MySQL Partition Partition I

http://blog.csdn.net/binger819623/article/details/5280267

First, the concept of zoning
Second, why use zoning? Advantages
Iii. type of partition
Four, sub-partition
V. Modification of partitions (additions, deletions, decomposition, merging)
Vi. partitioning characteristics of different engines
Vii. Limitations of Zoning



Zoning Concepts
partitions are for differentdatabase, with different characteristics. Here specifically forMysqlDatabase. In myIn SQL database, the concept of partitioning is only available from MySQL 5.1. However, it is only available in the MySQL advanced version.

Partitioning is to divide a database, or its components (such as a table) into a few small parts. And it's all about ' horizontal partitioning ', that is, dividing the rows of a table.


Benefits of Partitioning
1. You can improve the databasePerformance
2. Maintenance of large tables (more rows) is faster and easier because the data is distributed in different logicalthe document;
3. Deleting a partition or its data is easy because it does not affect other tables.

Note: Pruning, which is truncated. It means that when youWhen you query, only the partitions that you want to query are scanned ... Other parts do not scan: This greatly improves performance.


Partition Type
There are 4 types of partitions:
Range Partition: is a continuous row value, partitioned by range, for example: ID less than 100;id greater than 100 less than 200;
List partition: Similar to a range partition, but it holds a collection of discrete values.
Hash Partition: YesUser-definedThe value returned by the expression to be partitioned. can write partitions (number of partitions), or use partitions directlyStatements, such as partition P0 values in ...
Key partitioning: Similar to hash partitions except that partitions support one or more columns, and MySQLThe server itself provides a hashFunction.

Specific Description:
Partition syntax:
CREATE TABLE t (ID int,name varchar) ENGINE=MYISAM partition by range (ID);

to partition by range:
CREATE TABLE Orders_range
(
ID int auto_increment PRIMARY key,
Customer_surname varchar (30),
store_id int,
salesperson_id int,
Order_date Date,
Note varchar (500)
) Engine=myisam
Partition by range (ID)
(
Partition P0 values less than (5),
Partition P1 values less than (10),
Partition P3 values less than (15)
);
In fact, the above partition is created, we can know that its table type is MyISAM, and each partition of the engine is also MyISAM, this can be viewed through the show create TABLE TableName. When we insert the data into the table, if we want to see information less than 8, it retrieves P0 and P12 partitions later. That's very fast.

To partition by list:
CREATE TABLE Orders_list
(
ID int auto_increment,
Customer_surname varchar (30),
store_id int,
salesperson_id int,
Order_date Date,
Note varchar (500),
Index IDX (ID)
) Engine=myisam partition by list (store_id)
(
Partition P0 values in (1,3),
Partition P1 values in2,4,6),
Partition P3 values in (10)
);

The list partition can only put the value you inserted in a certain partition, if there is no value, the display cannot be inserted.

To partition by hash:
CREATE TABLE Orders_hash
(
ID int auto_increment PRIMARY key,
Cutomer_surname varchar (30),
store_id int,
salesperon_id int,
Order_date date,
Note Varcahr (500)
) Engine=myisam partition by hash (ID) partitions 4;

If it's divided into 4 partitions, what data are in which partitions when I insert the data? When I retrieve an ID value, does it explicitly say which partition to put in? Or is there an internal mechanism?
The most important thing to do with hash partitioning is to ensure that the data is allocated, which is the expression that is provided when you create table. You do not have to define a separate partition, as long as you use the Partitions keyword and the number of extents you want to divide. Statement as described above.

To partition by key:
CREATE TABLE Orders_key
(
ID int auto_increment,
Customer_surname varchar (30),
store_id int,
alesperson_id int,
Order_date Date,
Note Varcahr (500),
Index_idx (ID)
) Engine=myisam partition by key (order_date) partitions 4;
This partition is similar to a hash partition except MySQLThe server uses its own hash expression, unlike other types of partitions, without requiring an int or null expression.

To partition by sub-partition:
CREATE TABLE Orders_range
(
ID int auto_increment PRIMARY key,
Customer_surname varchar (30),
store_id int,
salesperson_id int,
Order_date Date,
Note varchar (500)
) Engine=myisam partition by range (ID)
Subpartition by Hash (store_id) subpartitions 2
(
Partition P0 values less than (5),
Partition P1 values less than (10),
Partition P3 values less than (15)
);
When inserting data into a table, what data is placed in the sub-partition?

================================================
MySQL Partition Partition II (cont.)
Get partition information
MySQL can get information about partitioned tables in the following ways:
Show Create Tabe table; Table Detail Structure
Show table status; Various parameter states of the table
SELECT * FROM information_schema.partitions;//Data dictionary to view partition information for a table
Explain partitions select * from table; Through thisStatement to show which partitions are scanned and how they are used.


make changes to the partition (Modify, merge, redefine partitions)
Modifying Partitions
To Modify a partial partition:
Due to our usual use ofThe database is mostlyDynamic, so it is OK to modify only one of the table partitions.
You can add or drop a range or list table partition, or you can merge or decompose a hash or key partition table. These actions are performed in the ALTER TABLE statement.
Use the Add partition keyword to add an existing partitioned table.
Alter
Table
Orders_range
Add
Partition
(
Partition P5 values less than (MaxValue)
)

The Reorganize partition keyword allows you to modify part or all of a table's partitions without losing data.
Splitting breaks down an existing partition:
Alter Table Orders_range
Reorganize partition P0 into

(
Partition N0 values less than (5000),
Partition N1 values less than (10000)
);

Merge partition: Like the above divides the p0 into N0 and N1, now merges 2 into one.
Alter table Orders_range Reorganize partition n0,n1 into
(
Partition P0 values less than (10000)
);

Modify all the Partitions: Specify multiple partitions before or after the INTO keyword
Alter table Orders_range Reorganize partition P0,P1,P2,P3,P4,P5 into
(
Partition R0 values less than (25000),
Partition R1 values less than (50000),
Partition R2 values less than (MaxValue)
);

Coalesce Merge Partitions:
Another method of the merge partition is the ALTER TABLE....COALESCE partition statement, you cannot delete the hash or key partition
Alter table Orders_key coalesce Partition1;

Redefine redefining partitions
Alter table Orders_range partition by hash (ID) partitions 4;

Delete a partition (delete, delete all partitions)
Drop Partition:
The range or list can beType of partition is deleted by the drop partition keyword
Alter table Orders_range drop partition p0;

Attention:
1. When the partition is deleted, you will delete all the data of this partition, and the DELETE statement is equal;
2. Make an ALTER TABLE. Drop partition must have drop permission;
3. Run this Deletecommand, it does not return deleted rows and can be viewed by the select COUNT () statement.
If you want to delete multiple partitions, you can use the following command statement: Alter table Orders_range drop partition p1,p2;


Delete all partitions
Delete all the partitions in the table with the following command statement, and finally a regular table.
Alter table Orders_range Remove partitioning;



when partitioning operations, it is helpful to understand the impact on performance:
1. Creating partitioned tables is slightly slower than regular tables without partitions;
2. Deletion is faster than DELETE statement by ALTER TABLE....DROP PARTITION statement;
3. Adding partitions on the range or list partition type (alter TABLE...ADD partition statement) is fairly fast because there is no moving data into the new partition.
4. When the ALTER TABLE....ADD partition statement is executed on a partition of a key or hash type, the more data is needed to depend on how many rows are already in the table, the longer it takes to add a new partition. When creating a table, using linear hash or linear key partitioning is fairly fast.
5. For hundreds of rows of records, make ALTER TABLE ... COALESCE partition, ALTER TABLE ... reorganize partition, alter Table...partition by Operation Command, is quite slow.
6. When using the Add Partition command, linear hash and linear key partitioning will make the coalesce partition operation faster, ALTER TABLE ... remove partitioning faster than others because MySQL does not require whichFiles to replace rows, even if the data is moved.


partitions for various storage engines
MyThe SQL partition can partition all MySQL-supported storage engines, such as: MyISAM, InnoDB, archive, Ndbcluster (linear key only), Falcon, unsupported partition engine: Merge, Federated, CSV, Blackhole

Note: The table types of all partitions and sub-partitions are consistent;
Index maintenance depends on the table type;
Locks some rows and also relies on the storage engine;
Partitions also belong to the top level of the storage engine, so performance does not have a significant impact when you do update and insert.

Limitations when using partitioning for various storage engines:
MyISAM Engine:
The MyISAM engine allows different parts of a table to be stored in different places, including index directories and data directories, when partitioning is used.
Here is an information about the distribution of data to 4 different physicalThe MyISAM partition on the disk.
Create Table Orders_hash2
(
Id int Auto_increment primary KEY, ...
) Engine=myisam

Partition by hash (ID)
(
Partition p0 index directory= '/data0/orders/idx '
Data directory= '/data0/orders/data ',
Partition P1 index directory= '/data1/orders/idx '
Data directory= '/data1/orders/data ',
Partition P2 index directory= '/data2/orders/idx '
Data directory= '/data2/orders/data ',
Partition P3 index directory= '/data3/orders/idx '
Data directory= '/data3/orders/data ',
);

Note: The above specific 4 distributions, inIt is not currently supported on Windows systems.

InnoDB Engine:
InnoDB partition Management is different from the management of the MyISAM engine.


Limitations of partitioning
Here are some of the restrictive constraints on MySQL partitioning
Common limitations:
All partitions must use the same engine;
Bulk loading is slow;
The maximum number of partitions per table is 1024;
Three-dimensional data types (GIS) are not supported;
The temporary table cannot be partitioned;
It is not possible to partition the log table;

Foreign key and Index aspects:
Foreign keys are not supported;
Full-text table index is not supported;
Load cache and load index into cache are not supported;

Sub-partitioning aspects:
Only partitions of range and list types are allowed to be partitioned;
The type of the sub-partition is only allowed as hash or key.

Partition expression aspects:
Range,list, the hash partition must be of type int;
Key partition can not have TEXT,BLOB type;
The use of UDFs, stored functions, variables, operators (|,,^,<<,>>,~) and some built-in functions is not allowed;
SQL mode cannot be changed after the table is created;
In a partitioned expression, subqueries are not allowed ;
A reference to at least one column must be included in the partition expression, and a unique index column (including the primary key)

===================================================
MySQL partition Table limitations summary

Mysql5.1 has been released for a long time, this article according to the Official document translation and some of their own tests, the limitations of the MySQL partition table to do some summary, because of personal ability and test environment reasons, there may be wrong place, also please see can point out in time, of course, interested friends can go to the official website.

The version tested in this article

Mysql> select version (); +------------+| Version ()  |+------------+| 5.1.33-log | +------------+1 row in Set (0.00 sec)

I. Restrictions on partitioning keys, Primary keys, and Unique keys

In the 5.1 partition table, the unique constraint is explicitly defined, and each unique constraint must contain the partition key (also including the primary KEY constraint) of the partition table.
This sentence may not be easy to understand, we do a few experiments:

create TABLE t1 (id int not NULL, UID int. NOT NULL, PRIMARY KEY ( ID) PARTITION by RANGE (ID) (PARTITION p0 values less THAN (5) engine = INNODB, PARTITION p1 values less THAN (TEN) engine = INNODB);  create TABLE t1 (id int not NULL, UID int. NOT NULL, PRIMARY KEY (ID)) PARTITION B Y RANGE (ID) (PARTITION p0 VALUES less THAN (5) ENGINE = MyISAM DATA directory= '/tmp ' INDEX directory= '/tmp ', PARTITION p       1 VALUES less THAN ENGINE = MyISAM DATA directory= '/tmp ' INDEX directory= '/tmp '); mysql> CREATE TABLE T1 --(ID int not NULL, UID int. NOT NULL, PRIMARY KEY (ID), U Nique KEY (UID), PARTITION by RANGE (ID)--PARTITION p0 VALUES less THAN (5), parti tion P1 VALUES less THAN (ten)); ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table ' s partitioning function 

II. limitations on the storage engine
The 2.1 merge engine does not support partitioning, and the partition table does not support merge.
2.2 Federated engine does not support partitioning. This limitation may be removed in a later version.
2.3 CSV engine does not support partitioning
2.4 Blackhole engine does not support partitioning
2.5 using partitioned tables on the Ndbcluster engine, the partition type can only be a key (or LINEAR key) partition.
2.6 When upgrading MySQL, if you have a table using the key partition (no matter what the engine, except Ndbcluster), then you need to dumped this table in reloaded.
2.7 The storage engine for all partitions or sub-partitions of the partitioned table must be the same, and this limit may be canceled in a later version.
Do not specify any engines (using the default engine).
All partitions or sub-partitions specify the same engine.

III. Limitations on functions
In a statement that creates a partitioned table in mysql5.1, only the following functions are included:
ABS ()
CEILING () and floor () (if you are using these 2 functions to create a partitioned table, the partition key using the function is an int type), for example

mysql> CREATE TABLE t (c FLOAT) PARTITION by LIST (floor (c)) (-    PARTITION p0 VALUES in (1,3,5),    part Ition p1 VALUES in (2,4,6)    );; ERROR 1491 (HY000): The PARTITION function returns the wrong type mysql> CREATE TABLE t (c int) PARTITION by LIST (Floo R (c)) (    PARTITION p0 values in (1,3,5), and    PARTITION P1 values in (2,4,6))    ; Query OK, 0 rows affected (0.01 sec)

Day ()
DayOfMonth ()
DAYOFWEEK ()
DayOfYear ()
DATEDIFF ()
EXTRACT ()
HOUR ()
Microsecond ()
MINUTE ()
MOD ()
MONTH ()
QUARTER ()
SECOND ()
Time_to_sec ()
To_days ()
WEEKDAY ()
Year ()
Yearweek ()

Iv. Other Restrictions

4.1 Object Limits
The following objects cannot appear in the partition expression
Stored functions, Stored procedures, UDFs, or plugins.
Declared variables or user variables.

4.2 Operational limits
Operations such as add and subtract multiplication appear in the partition expression, but the result of the operation must be an int or null. |, &, ^, <<, >>,, ~ etc are not allowed in the partition expression.

4.3 Sql_mode restrictions
It is highly recommended that you never change MySQL sql_mode after creating a partitioned table. Because in different modes, some functions or operations may return the results differently.

4.4 Performance considerations. Omitted

4.5 supports a maximum of 1024 partitions, including sub-partitions.
When you create a partition table with many partitions but no more than 1024 limits, if the error Got error from storage engine, that means you need to increase the Open_files_limit parameter.

4.6 Foreign keys are not supported. In MySQL, the InnoDB engine only supports foreign keys.

4.7 Fulltext Indexes (full-text indexing) is not supported, including the MyISAM engine.

Mysql> CREATE TABLE articles (    ID INT UNSIGNED auto_increment not NULL PRIMARY KEY,    title VARCHAR (2 XX),    body TEXT,    Fulltext (title,body),    PARTITION by    HASH (ID)    Partitions 4; ERROR 1214 (HY000): The Used table type doesn ' t support fulltext indexes

4.8 Spatial column types is not supported.
4.9 Temporary tables cannot be partitioned.

mysql> CREATE Temporary TABLE T1       (      ID int not NULL,        UID int not null,        Primar Y KEY (ID)-    PARTITION by RANGE (ID)       -PARTITION p0 VALUES less THAN (5) ENGINE = MyISAM,  -PARTITION p1 VALUES less THAN (ten) ENGINE = MyISAM    ); ERROR 1562 (HY000): Cannot create temporary table with partitions

4.10 Log table does not support partitioning.

mysql> ALTER TABLE Mysql.slow_log PARTITION by KEY (start_time) partitions 2; ERROR 1221 (HY000): Incorrect usage of PARTITION and log table

5.11 The partition key must be of type int, or it can be null by returning an int type through an expression. The only exception is when the partition type is a key partition, other types of columns can be used as partition keys (except BLOB or TEXT columns).

Mysql> CREATE TABLE TKC (C1 CHAR),    PARTITION by KEY (C1),    partitions 4; Query OK, 0 rows Affected (0.00 sec) mysql> CREATE TABLE tkc2 (C1 CHAR),    PARTITION by HASH (C1),    parti tions 4;  ERROR 1491 (HY000): The PARTITION function returns the wrong type mysql> CREATE TABLE tkc3 (C1 INT),    PARTITION by HASH (C1),    partitions 4; Query OK, 0 rows Affected (0.00 sec)

5.12 the partition key cannot be a subquery. A partitioning key may not is a subquery, even if that subquery resolves to an integer value or NULL

5.13 only rang and list partitions can be sub-partitioned. Hash and key partitions cannot be sub-partitioned.

The 5.14 partition table does not support key caches.

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024; Query OK, 0 rows Affected (0.00 sec) mysql> CACHE INDEX login,user_msg,user_msg_p in keycache1;+-----------------+----- ---------------+----------+---------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+-----------------+--------------------+----------+-- -------------------------------------------------------------------+| Test.login | Assign_to_keycache | Status | OK | | test.user_msg | Assign_to_keycache | Status | OK | | test.user_msg_p | Assign_to_keycache | Note | The storage engine for the table doesn ' t support Assign_to_keycache | +-----------------+--------------------+----------+------------------------------------------------------------ ---------+3 rows in Set (0.00 sec) 

The 5.15 partition table does not support insert DELAYED.

mysql> Insert  DELAYED into user_msg_p values (18156629,0,0,0,0,0,0,0,0,0); ERROR 1616 (HY000): DELAYED option not supported for table ' User_msg_p '

5.16 Data directory and INDEX directory parameters will be ignored in the partition table.
This limitation should not exist:

mysql> CREATE TABLE T1       (      ID INT not NULL,        UID int. NOT NULL,        PRIMARY KEY (ID) c7/>->)    , PARTITION by RANGE (ID)       , PARTITION p0 VALUES less THAN (5) ENGINE = MyISAM DATA directory= ' /tmp ' INDEX directory= '/tmp ',  PARTITION p1 VALUES less THAN (ten) ENGINE = MyISAM DATA directory= '/tmp ' INDE X directory= '/tmp '    ); Query OK, 0 rows affected (0.01 sec)

5.17 partition table does not support Mysqlcheck and Myisamchk
Mysqlcheck and Myisamchk are already supported in version 5.1.33

./mysqlcheck-u-p-r Test user_msg_p;test.user_msg_p                                    OK./myisamchk-i/u01/data/test/user_msg_p#p#p0. myichecking MyISAM file:/u01/data/test/user_msg_p#p#p0. Myidata records:4423615   Deleted blocks:       0-check file-size-check record Delete-chain-check key delete-chain-ch Eck Index Reference-check data record references Index:1key:  1:  keyblocks used:  98% Packed  :    0%  Max levels:  4Total:    keyblocks used:  98%  Packed:    0% User time 0.97, System time 0.02Maximum resident Set Size 0, Integral resident set size 0non-physical pagefaults 324, physical pagefaults 0, Swaps 0Bl Ocks in 0 off 0, Messages in 0 out 0, signals 0Voluntary context switches 1, involuntary context switches 5

5.18 the partition key of the partitioned table creates an index, the index is also partitioned. The partition key does not have global index one said.
5.19 use ALTER TABLE in partition Table ... Order BY, you can only order by within each partition.

=================================================

MySQL partition (Partition) script

MySQL 5.1 In the new feature partition (partition) Shell script. Note MySQL only supports less than or equal to 1024 partitions.

#!/bin/sh

# Set These values
Part=0
ori=5000
step=5000
max=3000000

For NUM in ' seq-f%f $ORI $STEP $MAX | cut-d.-f1 '
Do
echo "PARTITION $PART VALUES less THAN ($NUM)," >>/tmp/partition.sql
part= ' expr $PART + 1 '
Done
echo "PARTITION $PART VALUES less THAN MAXVALUE >>/tmp/partition.sql

MySQL Partition Partition I