MySQL Table partitioning technology

table Partitioning refers to the decomposition of a table in a database into smaller, easier-to-manage parts according to certain rules. Logically, there is only one table, but the bottom layer is made up of multiple physical partitions.

What are the benefits of table partitioning:

C. Refine the query. When you include a partitioning condition in a where statement, you can scan only one or more partitioned tables to improve query efficiency, and when you involve the sum and count statements, you can also work on multiple partitions in parallel, and finally summarize the results.

E. You can use partitioned tables to avoid certain special bottlenecks, such as mutually exclusive access to a single index of InnoDB, ext3 ask for the Inode lock competition for your system.

   Today, I learned the technique of partitioning the database table by consulting the relevant information and hands-on operation. Personal understanding, in fact, when there is big Data data table, the data table data in accordance with certain rules, classified into the specified regional space,
   If you want to "change the table"   operation, the area of the operation will not be the entire table, but the table of an area, the actual " space for Time ", will undoubtedly improve the efficiency of execution.

limiting factors for partitioned tables

A. A table can have up to a maximum of three partitions

In b.mysql5.1, the partition expression must be an integer, or an expression that returns an integer. Support for non-integer expression partitioning is provided in MySQL5.5.

C. If there is a primary key or a unique indexed column in the partition field, then more primary and unique index columns must be included. That is, the partition field either does not contain a primary key or an indexed column, or contains all primary keys and index columns.

D. Foreign KEY constraints cannot be used in partitioned tables

E.mysql partitions apply to all data and indexes of a table, cannot be partitioned on table data only, not indexed, and cannot be partitioned only on index partitions, not table partitions, or only part of a table's data.

How to determine whether the current MySQL supports partitioning

command: show variables like ‘%partition%‘

where Variable_name's value = 1, I tested it, indicating that it can be partitioned normally. The version of my database is:

the types of partitions supported by MySQL are：range partition, list partition, hash partition, key partition. Where Range,list,hash partitions such as general use int type, key partition uses Blob,text type, etc.

  Next Create the data table

  Followed by:
  
   The above error indicates that partition by cannot be used alone (stand-alone). Be careful here!
   Then take the second usage:  
  

partition success, OH Ah!

A few notes:
A. For partition S1, 1<= ID< 10; for partition S2, represents 10<= ID< 20; for partition S3, represents 20<= ID< 30; for partition S4, indicates ID>= 30, no upper limit B. If you reverse the order of less than (10) and than (20), you will get an error, such as: VALUES, than value Must is strictly increasing for each partition,
So also with the attention order problem
C. The maximum partition for a table is: 1024. (mentioned above), in the limited table partition, finally add: partition XXX values less than maxvalue, is very necessary.
D.Regardless of the type of partition,the partition key must be a primary key or a unique key, unless both are not, the following error will be reported by the person.

If you are using the registration date as the partition key, you need to use a date-processing function to convert to an integer, such as year (RegDate), To_days (RegDate), To_seconds (RegDate), and only support these three functions.

Or use the RANGE COLUMNS partition, you do not need to convert the date, as shown below

CreateTableUsers_par (IDIntNotNull, Usrnamevarcharnot null Usremail varchar (not nullint not nullnull partition by   < Span style= "color: #0000ff;" > range columns (regdate)  (partition P0 values less than ( "2005-05-05  ' values Less than ( 2009-09-09 ' 

The range partition is especially useful for deleting outdated data or a range of data, only the ALTER TABLE tbl_name TRUNCATE partition Partition_name can be
More efficient than the DELETE statement, there is a query that often uses partition keys, can improve query performance, because just scan some partitions ok

Table's partitioning function 

Next, we look at the partition that we created, the related syntax:EXPLAIN Partitions SELECT * from ' demo 'Of course, we need to do tests, "practice is the only criterion to test truth." Syntax: Explain partitions SQL statements, such asand so on, you can do other tests as well. Next, try the table partitioning form in other ways. List Table partition creation:Syntax: DESC PARTITIONS SELECT * from table_name; at the same time, syntax: show CREATE TABLE table_name;

Hash Table partition creation:

   key partition creation is similar to hash partition. You can refer to the above. The creation of the 
 
 Sub-partition: Partition table splits each partition again and becomes a composite partition. Reference:  data segmentation--mysql partition table and its performance analysis 
 
    
 
 Address:/HTTP/ Www.cnblogs.com/zmxmumu/p/4450857.html 
 Partition Management supplemental    
    range and list partitions are very similar to the partition management on delete, add, redefine, and so on, as shown below.    
    Delete partition (ALTER TABLE TBL_ Name drop partition Partition_name), after the partition is deleted, the partition's data is deleted together. 

MySQLdrop0 rows affected (0.240   

Add partition (ALTER TABLE TBL_NAME add partition)

Mysql>AlterTable Users_parAdd partition (Partition P0Values less than (20)); ERROR1481 (HY000): MAXVALUE canonly be used in-- here is an error because the Add partition must be added at the largest end of the original partition, When you add a partition to a list partition, the value of the new partition's value list cannot contain the value from the list of values in any existing partition, otherwise the error mysql> alter table student add Partition (partition P2 values in ( ' 

 redefine partition (ALTER TABLE TBL_NAME REORGANIZE partition partition_name into), you can split one partition into multiple, and vice versa, you can merge multiple into one or more. 

 mysql> alter  Table Users_par Reorganize partition p1 into (Partition P0 values less than (20), partition P1 Span style= "color: #0000ff;" >values less than (30)); Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: Span style= "color: #800000; Font-weight:bold; " >0 Warnings: 0        

It is important to note that when the range and list partitions are redefined, only adjacent partitions can be redefined, the partitions cannot be skipped, and the redefined partition interval must be consistent with the original partition range, and the partition type cannot be changed.

Management of hash and key partitioning

Reduce the number of partitions, using the Coaleace keyword

MySQL21 row affected (0.040   

Increase the number of partitions

MySQL21 row affected (0.040   

MySQL partitioning facilitates query optimization, fast deletion of outdated data, improved query throughput, and more.

MySQL Table partitioning technology