MySQL sub-table and partition introduction

In the daily development or maintenance of the large table is often encountered, so-called large table refers to the storage of millions and even tens records of the table. Such tables are too large to cause the database to take too long to query and insert, with poor performance and worse performance if it involves federated queries. The purpose of partition table and table is to reduce the burden of database, improve the efficiency of database, usually point is to improve the efficiency of adding and deleting tables.

First, what is the sub-table:

The Sub-table is a large table according to a certain rule into a number of independent storage space of the entity table, we can be called a child table, each table corresponds to three files, myd data file,. Myi index file,. frm table structure file. These sub-tables can be distributed on the same disk or on different machines.

1. There are 2 ways to optimize the mass data according to the table technology:

1, Vertical segmentation: A large amount of data table, according to the attributes of a field or use frequent classification to split into multiple tables, general e-commerce database: User table, order form, payment system, etc.

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/8A/wKioL1hQ7jmDPCHCAANG5mJz12g690.png-wh_500x0-wm_3 -wmp_4-s_123021846.png "title=" 111.png "alt=" Wkiol1hq7jmdpchcaang5mjz12g690.png-wh_50 "/>

2, Horizontal segmentation: According to the values of one or more columns to put the data row into a number of independent tables, the horizontal table can be combined with a number of low-configuration hosts to achieve high performance.

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/8E/wKiom1hQ7kqDBxVRAALNDOLRIqc744.png-wh_500x0-wm_3 -wmp_4-s_468431345.png "title=" 2222.png "alt=" Wkiom1hq7kqdbxvraalndolriqc744.png-wh_50 "/>

3. Advantages and disadvantages of both:

Horizontal advantages: Split rule abstraction is good, jion operation can basically database do, there is no single table big data, high concurrency performance bottleneck, the application end of the transformation less, improve system stability and load capacity

Disadvantage: Shard transaction consistency is difficult to resolve, before MyCAT2.0 before MySQL5.7, or data weak xa. Large number of data expansion difficulty maintenance, the poor performance of the library join

Vertical advantages: The business is clear after splitting, the splitting rule is clear, the system integrates or expands easily, the database maintenance is simple

Disadvantage: Part of the business can not use join, only through the interface to solve, to provide the system can be complex, by each business different restrictions exist performance bottleneck, not easy data expansion and performance improvement.

Transaction processing is complex, and dividing the table into different libraries according to the business classification will cause some business tables to be too large, and there are single library read-write and storage bottlenecks.

Second, what is the partition

Partitioning is a table of data into more than n regions, after partitioning, the surface is still a table, but the data is hashed to multiple locations according to the size of the data volume, the actual business

1. The partitioning method is as follows:

A, Range partition: mainly used for time column partitioning, value range, row data based on the column values of a given contiguous partition into the partition. such as the Sales Class table, can be divided into years to store sales records

B, List partition: for discrete values, partition the value to be specified when inserting the specified data into the specified partition table, such as specifying certain values in a particular partition.

c, key partition: similar to by the hash partition, the difference is that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.

d, hash partition : A partition that is selected based on the return value of a user-defined expression that is evaluated 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.

Third, the partition instance:

Create redundant format

       if a primary key or unique index exists in a table, the partitioning column must be a unique index of a unique index        Create table t11 (      col1 int not null,       col2 date not null,      col3 int  not null,      col4 int not null,       unique key  (col1,col2)       partition by  Hash (col1)       partitions 4;  hash      create  table t121 (      col1 int not null,       col2 date not null,      col3 int not  null,      col4 int not null,       unique key  (col1,col2))       partition by hash (year (col2))        partitions 4; primary Key Create table t31 (      col1  int not null,      col2 date not null,       col3 int not null,      col4  int not null,      primary key  (col1,col2))        partition by hash (col1)       partitions 8 Primary key and Index exist simultaneously: create table t41 (      col1 int not null,       col2 date not null,      col3  int not null,      col4 int not null,       uniquE key (COL4),      primary key  (col1))        partition by hash (col1)       partitions 5;        a unique index can allow a null value, as long as the partition column is a part of a unique index and does not require the entire unique index column to be a partitioned column create table t223332 ( Col1 int null,col2 date null,col3 int null,col4 int null) partition  by hash (col3) partitions 4;     does not have a primary key or a unique index, you can specify any one column as the partitioned columns create table  t223332 (col1 int null,col2 date null,col3 int null,col4 int null , key (COL4)) Partition by hash (col3) partitions 4;    rang  partition: Mainly used for time-column partitioning, such as a table of sales classes, you can store sales records      definitions based on year: The row data is placed into the partition based on the column values of a given contiguous partition,        id  is the primary key     create table t3 (    id int) engine= Innodb    partItion by range (ID) (    partition p0 values less than  ( ,    partition p1 values less than  ( );      View data Files     t3.frm t.par    insert into t  select 9;    insert into t select 10;     insert into t select 15;     View partition Status    use  Information_schema    select * from paritions where table_schema= ' test and table_name= ' T3 ' \g;    partition_method represents the partition type         error when the partition condition is not met   table has no partition for value 40  Alter table t add partition (Partition p2 values less than maxalue );     MainFor time-column partitioning, such as a table of sales classes, you can place a sales record (year (date)) based on year     create table sales (     money int not null,date datetime) engine=innodb     partition by range  (date) (    partition p2008 values  less than  (     partition p2009 values less than),   (       partition p2010 values less than),   (+)        );       insert  into sales select 100, ' 2008-01-01 ';        insert into  sales select 100. ' 2008-02-01 ';        insert into sales select 100. ' 2008-01-02 ';        insert into sales select 100, ' 2009-03-01 ';        insert into sales select 100, ' 2010-01-01 ';       list  partition: For discrete values, partition the value to be specified when inserting the specified data into the specified partition table,     create table t_list  (A int,b int) engine=innodb     Partition by list (b) (Partition p0 values in (1,3,5,7,9),     partition p1 values in  (0,2,4,6,8));           insert into  t4 select 1, 3;      insert  into  t4 select 1, 5;      insert into   t4 select 1, 8;      insert into  t4  select 1, 6;      table has no partition for  values10       It is worth noting that the list partition does not have a definition like "values less than maxvalue" that contains other values. Any values that will be matched must be found in the list of values. The     list partition can be combined with a range partition to produce a composite sub-partition, which is also possible to combine hash and key partitioning to produce a composite sub-partition.                Note: Innodb myisam difference    when inserting multiple rows of data with insert, the MyISAM, InnoDB storage engine processing is completely different,  myisam  an unsuccessful, previous success value enters the table   innodb as long as one does not succeed, all are unsuccessful create table t (A int,b int) engine=myisam partition  by list (b) (partition p0 values in  (1,3,5,7,9), partition p1 values  in  (0,2,4,6,8));insert into t values  (2,4), (6,19), (5,3); Insert into  t values  (2,4), (6,19), (5,3); error 1526  (HY000):  table has no partition for value 19select  * from t;+------+------+| a    | b    |+---- --+------+|    1 |    2 | |     2 |    4 |+------+------+2 rows in set   (0.00 SEC) Create table tt (a int,b int) engine=innodb partition by  list (b) (partition p0 values in  (1,3,5,7,9), partition p1 values in   (0,2,4,6,8));insert into tt values  (2,4), (6,19), (5,3); insert into tt  values  (2,4), (6,19), (5,3); error 1526  (HY000): table has no partition for value 19  select * from tt; empty set  (0.00 SEC)       hash  partitioning: partition based on the return value of the user's expression, the return value cannot be negative        to add a partition by hash (expr) sentence on the create table  statement, Where expr is an expression that returns an integer, it can be just a number field type for the column name of the MySQL integer        after adding a partitions num clause, NuM is a non-negative       create table t_hash (a int,b date) Engine=innodb       partition by hash (year (b))        Partitions 4;      insert into t_hash select 1, ' 2010-04-01 ';            create table tt_hash (a  int,b date) engine=innodb      partition by hash  (a)       partitions 4;             #######################################      columns Division         differs from other partitions, the partition condition must be integral, and if not integral, it should be converted to integral type by function  columns ticks are the evolution of the Rang list partition.        support for integral type        date type date  DateTime The remaining date types are not supported      &nbsp: String type  char varcha binary  varbinary ,blok and text type are not supported        create table tt_column_range (A int,b int) engine=innodb partition  by range columns (A, b) (      partition p0 values  less than  (0,10),      partition p1 values less  than  (10,20),      partition p2 values less than  ( 20,30),      partition p3 values less than  (30,40),       partition p4 values less than  (40,50)        );                   Sub-partition: MySQL database allows hask or key sub-partitions on rang and list partitions,      create table  ts (A int,b date) Engine=innodb     partition by range (year (b))       subpartition by hash (To_days (b))      subpartitions 3 (      partition p0 values less than  (,   )          partition p0 values less than  ( ),       partition p1 values less than  (2015)   partition p2 values less than maxvalue);  create table ts (A  int,b datepartition by range (year (b)) Subpartition by hash (To_days (b)) (partition  p0 values less than (SUBPARTITION S0,SUBPARTITION S1) partition p1  values less than  (SUBPARTITION S2,SUBPARTITION S3) partition p2  Values less thaN maxvalue (SUBPARTITION S4SUBPARTITION S5))                     each sub-partition must contain the name of the partition.    the name of the sub-partition is unique. Null value CREATE TABLE T3 (    id int) in partition engine=innodb     Partition by range (ID) (    partition p0 values less than   (,    partition p1 values less than ); );  Null value   left-most.

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1882681

MySQL sub-table and partition introduction