About MySQL partition, sub-table, cluster

First of all, I've been confusing the concept of partitions and sub-tables here to summarize

I. What is a partition

MySQL database data in the situation of the file exists on the disk, a table mainly corresponds to three files, one is the FRM storage table structure, one is myd storage table data, one is Myi table index.

Two ways to Partition

1, Horizontal partitioning

What is horizontal partitioning? is a horizontal partition, for example, if there is 100W data, divided into 10 parts, the first 10W data is placed into the second partition, the second 10W of data to be placed in the secondary partition, and so on. That is, the table is divided into very, root with the merge to divide the table, a bit like oh. When a piece of data is fetched, this data contains all the fields in the table structure, that is, horizontal partitioning, and does not alter the structure of the table.

2, Vertical partitioning

What is vertical partitioning? is the vertical partition, for example, in the design of the user table, the beginning of the time did not consider good, and put all the information of the individual into a table, so that the table there will be relatively large fields, such as personal profile, and these profiles, perhaps not a lot of people to see, so wait until someone wants to see, In the search, sub-table, you can put such a large section, separate.

Feel the database partition seems to be cut apples, in the end is cut horizontally, or vertical cut, according to personal preferences, MySQL provides the partition belongs to the first, horizontal partitioning, and subdivided into many ways: range partition, list partition, hash partition, key partition, sub-partition

Two. Partition Management Example:

1. Delete partition mysql> ALTER TABLE user drop partition P4;  mysql> ALTER TABLE user drop partition p4;2, new partition view copy print?//range add a new partition  mysql> ALTER TABLE user add partition (parti tion P4 values less than MAXVALUE);  Query OK, 0 rows affected (0.06 sec)  records:0  duplicates:0  warnings:0    //list Add new partition  mysql> ALTER TABLE List_part add partition (partition P4 values in (25,26,28));  Query OK, 0 rows affected (0.01 sec)  records:0  duplicates:0  warnings:0    //hash repartition  mysql> ALTER TABLE Hash_part add partition partitions 4;  Query OK, 0 rows affected (0.12 sec)  records:0  duplicates:0  warnings:0  

　　

Third, the advantages of partitioning

1, partitions can be divided into multiple disks, storing a larger

2, according to the search criteria, that is, the condition behind the where, find only the corresponding partition not all look up

3, in the big data search can be processed in parallel.

4. Distribute data queries across multiple disks for greater query throughput

Differences between partitions and sub-tables:

A partition logically has only one table, whereas a table breaks a table into multiple tables. Sub-tables and partitions are not contradictory, can be used in conjunction with each other.

Four, sub-table

In fact, the sub-table is divided into horizontal and logical sub-tables, the difference between the table and partition is the partition is done by the database, in the code perspective to see or a table, the sub-table is actually controlled by the code of a logical sub-table. Make some sub-table rules, such as modulo

Implementation examples:

Horizontal Sub-table

Insert into Tb_member1 (id,name,sex) Select Id,name,sex from member where Id%2=0;insert into Tb_member2 (id,name,sex) Selec T id,name,sex from member where id%2=1;

Vertical sub-table: For example, in a blog system, the title of the article, author, classification, creation time, etc., is the change frequency is slow, the number of queries more, and preferably have good real-time data, we call it cold data.
and the number of blog views, replies, and so on, similar statistics, or other high-frequency changes in the data, we call it active data.
After we carry out the longitudinal table:
1, the use of storage engine is different, cold data use MyISAM can have better query data. Active data, can use InnoDB, can have better update speed.
2, the cold data for more from the library configuration, because more operations are queries, so as to speed up the query. For thermal data, it is possible to handle the horizontal table of the main library relatively.
3, for some special active data, you can also consider the use of memcache, Redis and other caches, such as accumulated to a certain amount to update the database.

Five, cluster

Cluster is not very understanding, here simply record the user summary

A MySQL cluster and node
A cluster is a combination of a set of nodes.
Node type: Management node, SQL node, data node.
Management node: To manage other nodes, there can only be one.
SQL node: A bridge between the application and data nodes to store the table structure. The higher the number of SQL nodes, the smaller the load on a single SQL node and the better the system performance.
Data node: Used to store data, the storage engine can only be NDB, otherwise the data cannot be stored. Each data node can configure multiple mirrors, when a single data node fails, as long as the mirror is normal, does not affect the normal use of the cluster.
The application accesses the SQL node, the SQL node accesses the data node, the data node returns the result to the SQL node, and the SQL node is returned to the application. The management node does not participate in the process, but only the SQL nodes and data nodes are configured for management.

About MySQL partition, sub-table, cluster