Mysql Database Optimization (iii)--partitioning and sub-table "personal experience"

　Citation: MyISAM Storage Engine table in the database , each table is stored as a three physical file named after the table name.

1, first of all, there will be any storage engine is indispensable to hold the table structure definition information of the. frm file,

2. In addition, there are also. The MYD and. MYI files, respectively, hold the table's data (. MYD) and index data (. MYI).

Each table has and only such three files are stored as a table of the MyISAM storage type, meaning that no matter how many indexes the table has, it is stored in the same one. The Myi file.

MyISAM supports the following three types of indexes:

1. B-tree Index

B-tree index, as the name implies, is that all the index nodes are stored according tothe Balancetre e data Structure , all the index data nodes are in the leaf node.

2. R-tree Index

The R-tree index is stored in a different way from the B-tree index, and is primarily designed to index fields of storage space and multidimensional data, so the current MySQL version only supports the geometry type of fields for indexing.

3. Full-text Index

The Full-text index is the full-text index that we say, and his storage structure is b-tree. The main thing is to solve inefficient problems that we need to use like queries.

Body:

As in the "citation" in the data file will be more and more large, tens data file will be around 1G. So we want to improve the query speed in the database, we may be able to partition the data in a table ...

Cut into 2 parts:

1. Vertical segmentation of data

　Vertical segmentation of data can also be referred to as vertical segmentation. Think of the database as a large chunk of chunks of "data blocks" (tables), we cut these "chunks" vertically, and then scatter them across multiple database hosts. Such a segmentation method is a vertical (longitudinal) data segmentation.

Advantages of vertical slicing

The splitting of the database is simple and clear, and the splitting rule is explicit;

The application module is clear and easy to integrate.

Data maintenance convenient and easy to locate;

Disadvantages of vertical slicing

Some table associations cannot be completed at the database level and need to be completed in the program;

There is still a quiet performance for tables with extremely frequent access and large data volumes, and may not necessarily meet the requirements;

Transaction processing is relatively more complex;

When the segmentation reaches a certain extent, the extensibility will be limited;

Read-through segmentation can lead to complex system transitions and difficult maintenance.

2, the level of data segmentation

　The vertical segmentation of data can be easily understood as the partitioning of the data according to the table, and the horizontal segmentation is no longer based on the table or function module to slice. In general, simple horizontal slicing is the main way to spread an extremely mundane table into multiple tables, with a subset of the data in each table, according to some rule of a field.

Advantages of horizontal slicing

Table Association is basically able to complete on the database side;

There is no problem that some very large data volumes and high-load tables encounter bottlenecks;

Application-side Overall schema changes are relatively small;

Transaction processing is relatively simple;

As long as the segmentation rules can be defined, it is basically more difficult to meet the extensibility limit;

Disadvantages of horizontal slicing

The segmentation rules are relatively more complex, and it is difficult to abstract a segmentation rule that satisfies the whole database.

Later data maintenance difficulty has increased, manual positioning of data is more difficult;

The coupling degree of each module in the application system is high, which may cause some difficulties in the migration and splitting of the later data.

Problems that may exist in data segmentation and consolidation.

The above-mentioned division is classified as a sub-table, MySQL also has a table called the partition similar table, we look at the similarities and differences:

Why divide tables and partitions?

We often encounter large tables in daily development, so-called large tables are those that store millions or even tens records. Such tables are too large to cause database queries and insertions to take too long, performance is poor, and performance can be even worse if a federated query is involved. 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.

What is a 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. When the app reads and writes, it gets the corresponding sub-table name according to the predefined rules and then goes to manipulate it.

What is a partition?

partitions and sub-tables are similar, and are broken down by rules. The difference is that the table divides the large table into several separate entity tables, while partitioning divides the data into multiple locations, which can be the same disk or on different machines. After partitioning, the surface is still a table, but the data is hashed to multiple locations. When the app reads or writes a large table name, DB automatically organizes the data into the partition.

What is the connection between MySQL sub-tables and partitions?
1. All can improve MySQL's sex high, in the high concurrency state has a good performance.

2. sub-table and partition are not contradictory, can cooperate with each other, for those large traffic, and table data more tables, we can take the table and partition combination of the way (if the merge table, not with the partition, you can use other sub-table test), access is not large, but the table data a lot of tables, We can take the partitioning method and so on.

3. The Sub-table technology is more troublesome, need to manually create a child table, the app server read and write when the child table name needs to be computed. It is better to use merge, but also to create the union relationship between the child table and the Configuration child table.

4. Table partitioning is easy to operate and does not require creating child tables, relative to the table.

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------

Specific practices of the sub-table:

1. mysql Cluster

It is not a sub-table, but it plays the same role as the sub-table. The cluster can share the number of operations on the database and divide the tasks into multiple databases. The cluster can read and write separation, reduce read and write pressure. thereby improving database performance.

2. Custom Rule Sub-table

Large tables can be decomposed into multiple sub-tables according to the rules of the business. Typically there are several types, and you can define the rules yourself.

     

1, Range – This mode allows data to be divided into different ranges. For example, you can divide a table into several partitions by year. 2, hash (hash) – This mode allows you to pass a hash of one or more columns of the table Code class= "SQL keyword" >key 3, Key (key value)-an extension of the hash pattern above, where the hash key 4, list (pre-defined list) – This mode allows the system to split the data through predefined list values. 5, Composite (composite mode) – The combination of the above modes uses

Below is a brief introduction to how to divide the table (by year table).

Suppose the table structure has 4 fields: self-increment ID, name, deposit amount, deposit date

Divide the deposit date as a regular table and create several tables separately

2011: account_2011

2012: account_2012

......

2015: account_2015

When the app reads and writes, it finds the corresponding table name according to the date and needs to be determined manually.

varGettablename =function () {vardata ={name:'Tom', Money:2800.00, Date:'201410013059'    }; varTableName ='Account_'; varYear = parseint (Data.date.substring (0,4)); if(Year < -) {tablename+= .;//account_2011}Else if(Year < -) {tablename+= -;//account_2012}Else if(Year < the) {tablename+= -;//account_2013}Else if(Year < -) {tablename+= the;//account_2014}Else{tablename+= -;//account_2015    }    returnTableName;}

3, using the merge storage engine to achieve the sub-table

The merge sub-table, divided into the main table and the child table, the main table resembles a shell, logically encapsulates the child table, in fact, the data are stored in the child table.

We can insert and query data through the main table, and if we clear the rules of the table, we can also manipulate the sub-table directly.

Child Table 2011

CREATE TABLE ' account_2011 ' (' id '  int(one) not NULL auto_increment, ' name '  ) varchar (CHARACTER) SET UTF8 COLLATE utf8_general_ci null DEFAULT null, ' Money '  float< /c9> not NULL, ' tradedate '  datetime not nullprimary KEY (' id ')) ENGINE=myisamdefault CHARACTER set< /c12>=utf8 collate=utf8_general_ciauto_increment=2CHECKSUM=0  Row_format=dynamicdelay_key_write=0;

Child Table 2012

CREATE TABLE ' account_2012 ' (' id '  int(one) not NULL auto_increment, ' name '  ) varchar (CHARACTER) SET UTF8 COLLATE utf8_general_ci null DEFAULT null, ' Money '  float< /c9> not NULL, ' tradedate '  datetime not nullprimary KEY (' id ')) ENGINE=myisamdefault CHARACTER set< /c12>=utf8 collate=utf8_general_ciauto_increment=2CHECKSUM=0  Row_format=dynamicdelay_key_write=0;

Master table, all year

CREATE TABLE ' Account_all ' (' id '  int(one) not NULL auto_increment, ' name '  ) varchar (CHARACTER) SET UTF8 COLLATE utf8_general_ci null DEFAULT null, ' Money '  float< /c9> not NULL, ' tradedate '  datetime not nullprimary KEY (' id ')) ENGINE=Mrg_myisamdefault CHARACTER SET=utf8 collate=utf8_general_ciunion=(' account_2011 ', ' account_2012 ') insert_method= Lastrow_format=DYNAMIC;

When creating the main table, there is a insert_method, indicating the insertion method, the value can be: 0 is not allowed to insert, first inserted into the Union, the last inserted into the final table in the Union.

When querying through a primary table, it is equivalent to querying all the child tables together. This does not reflect the advantages of the table, it is recommended to query the child table.

specific practices for partitioning:

• RANGE partition: Assigns multiple rows to a partition based on column values that belong to a given contiguous interval.
• List partitioning: Similar to by range partitioning, the difference is that a list partition is selected based on a value in a set of discrete values that match a column value.
• 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 package > contain any expression that is valid in MySQL that produces a non-negative integer value.
• Key partitioning: Similar to partitioning by hash, 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 containing > integer values.

1. Range:

int (oneint(null, date datetime) partition by range (date) (Parti tion p2007 values less than (+), partition p2008 values less than, PA Rtition p2009 values less than ($) partition p2010 values less than maxvalue);

2. List:

int ( intin (1, 3, 5, 7 ,9 in (2,4,6,8,0 ));

3, Hash:

The purpose of the hash partition is to distribute the data evenly across the predefined partitions, ensuring that the data volume of each partition is roughly the same. The hash partition function page needs to return an integer value. The value in the Partitions clause is a non-negative integer, without the addition of the partitions clause, by default the number of partitions is 1.

int (4;

4. Key:

The key partition and the hash partition are similar, the difference is that the hash partition is the user custom function partition, the key partition uses the function which the MySQL database provides to partition, NDB cluster uses the MD5 function to partition, for other storage engine MySQL uses the internal hash function, These functions are based on the same algorithm as password ().

int (4;

5. Columns Partition

In the range above, list, HASH, key four kinds of partitions, the condition of the partition must be shaping, if it is not shaping need to convert it to shaping through a function.

mysql-5.5 begins to support the columns partition, which can be considered as the evolution of the range and list partition, and the columns partition can be partitioned directly using non-shaping data. The columns partition supports the following data types:

All shaping, such as 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.

Partition Management

New Partition

ALTER TABLE sale_dataadd PARTITION (PARTITION p201010 VALUES less THAN (201011));

ALTER TABLE   3 ; (PARTITION p201010 VALUES less THAN (201011));

Delete Partition
--When a partition is deleted, all the data in that partition is also deleted.
ALTER TABLE sale_data DROP PARTITION p201010;

Merging of partitions
The following SQL, merges p201001-p201009 into 3 partitions p2010q1-p2010q3

ALTER TABLE sale_datareorganize PARTITION p201001,p201002,p201003,p201004,p201005,p201006,p201007,p201008, p201009 into (PARTITION p2010q1 values less THAN (201004), PARTITION p2010q2 values less THAN (2 01007), PARTITION p2010q3 VALUES less THAN (201010));

Partitioning should be aware of:

1. When partitioning, either 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 range, so try not null

Mysql Database Optimization (iii)--partitioning and sub-table "personal experience"