MySQL table partition of the database

Partition the table, but still on the same server,

Partitioning is divided into multiple locations, divided into multiple tables, is actually a logical table (is a virtual table), is divided into multiple tables, but management can be managed by the time of the management of a table.

Partitions are logically the same table, and each table is a separate one.

Here are three common partitioning methods:

Can be segmented by range (range), such as 1--10 or time range

Slicing by hash value: modulo

To slice by list: such as East China area or North China region

Partition-related options are shown below, if the partition is a primary key or part of a primary key

MariaDB [(None)]> help create table; option partition_options is the option to partition, Partition_definition is the option for partitioning

Example

Example one: Partitioning by range (value size)

The following statement represents a students table created as three partitions

Typically starting with the smallest value, there is a default built-in variable MaxValue, which is the maximum value for the table

MariaDB [sunny]> CREATE TABLE testpartition (ID int,name varchar), age tinyint unsigned not null,gender enum (' F ', ' M ' ) partition by Range (age) (partition Youngman values less than (+), partition middleman values less than, partition OL Dman values less than maxvalue);

View the table structure as a table with DESC

MariaDB [sunny]> desc testpartition;

In shell command execution

[[email protected] MySQL] #cd/var/lib/mysql/sunny

You can see that there are three separate table spaces for table testpartition, that is, three testpartition.*ibd files, but the table structure is only one, that is testpartition.frm

For Shell commands, generate a set of random values, inserted into the Testpartition table

Defining gender arrays

[[email protected] sunny] #gender = (' F ' M ')

Defining arrays

Insert 1000 rows of data, note the external use of double quotation marks, use single quotation marks inside SQL statements

[[email protected] sunny] #for i in {1..1000};d o mysql-uroot-ppass1234-e "insert into sunny.testpartition values ($i, ' st Ud$i ', $[$RANDOM%80+18], ' ${gender[$RANDOM%2]} '); "; Done

At this point, 1000 records are generated in sunny.testpartition

With this example, we can conclude that although we are slicing through the scope, we are working on the same table. But these 1000 records are partitioned into different table spaces for storage.

Verify that, like the following command, you can see that testpartition#p#middleman.ibd,testpartition#p#oldman.ibd and testpartition#p#youngman.ibd already have size

[[email protected] sunny] #ll/var/lib/mysql/sunny-h

Example two: partition according to the ID value hash

Note that to specify the number of partitions, such as the following partitions 5 means modulo 5, which is divided into 5 extents, note the type of the hash field, character type such as char or varchar cannot be hash, because the need to fill the extra type, such a field can not be hash

MariaDB [sunny]> CREATE TABLE hashpartition (ID int,name varchar () not null,age tinyint unsigned,gender enum (' F ', ' M ') partition by hash (ID) partitions 5;

Generate 5 table spaces under Path/var/lib/mysql/sunny

HASHPARTITION#P#P0.IBD--HASHPARTITION#P#P4.IBD

Example three: Partitioning by list

Suppose the table listpartition has a field majorid, there are nine values for 1--9, and the list is partitioned according to Majorid

MariaDB [sunny]> CREATE TABLE listpartition (ID int,name varchar () not null,age tinyint unsigned,gender enum (' F ', ' M '), Majorid tinyint unsigned NOT NULL) partition by list (Majorid) (partition P0 values in (1,4,7), partition P1 values in (2 , 5,8), partition P2 values in (3,6,9));

Then the data Majorid for 1,4,7 three values will be assigned to LISTPARTITION#P#P0.IBD.

Majorid data for 2,5,8 three values will be assigned to LISTPARTITION#P#P1.IBD.

Majorid data for 3,6,9 three values will be assigned to LISTPARTITION#P#P2.IBD.

Here, the table partition description is complete.

If table partitioning does not address performance issues, it is recommended that you consider a table or a sub-library

Why the library is divided

The server faces a lot of write operations and cannot load, the solution is as follows

1. Sub-Library: If user information is placed on one library, shopping information is placed on another library

2. If the library cannot be solved, it will distribute a large number of write requests to the backend server through the routing device, and send the same user's information to the same server, so that the same user can quickly view all the orders.

Node-level redundancy: Each host backend has multiple slave servers for decentralized read operations and reduced stress, so each primary server is a cluster. When the primary server is out of the ordinary, a new primary server is directly elected to the backend from the server

Data-level redundancy: When the user requests come, each node does the data storage slot, such as each node to do four slots, each slot is the data storage unit, each slot has an ID, when the user requests come over, the slot to take the mold, the hit slot request to the corresponding slot. Note that the slots can be backed up, each slot has a copy of redundancy, the copy can be placed on the other server on the idle slot, when any one server is abnormal, will not affect the data, the slot mode can guarantee the data read the equalization request. The data on each slot is called a piece of data. Sharding allows for decentralized write operations. In a fragmented architecture, the front-end routing devices are critical, with redundancy at the Shard level, and a copy of each shard can provide read operations. But the fragmentation technique is more complex. Normally, a site usually has three layers: content, business, data layer. As the scale of the business increases, iterations increase and the schema is modified.

Reasons for the Sub-table

A table is a table that breaks up large tables into several instances, and each table is independently available.

When a piece of data reaches millions of, you spend more time searching for it, and if you have a joint query, I think it's possible to die there. The purpose of the sub-table is to reduce the burden on the database and shorten the query time.

Based on personal experience, MySQL executes a SQL process as follows:

1, receive sql;2, put SQL into queue, 3, execute sql;4, return execution result. Where do you spend the most time in this process? The first is the time to wait in the queue, and the second, the SQL execution time. In fact, these two are one thing, waiting for the same time, there must be SQL in the execution. So we want to shorten the execution time of SQL.

There is a mechanism in MySQL is table locking and row locking, why this mechanism is to ensure the integrity of the data, I give an example, if there are two SQL to modify the same table of the same data, this time what to do, is not two SQL can simultaneously modify this data? It is clear that MySQL handles this situation in the form of a table lock (MyISAM storage engine) and a row lock (InnoDB storage engine). Table locking means that you can't operate on this table, and you have to wait until I finish working on the table. Row locking is the same, other SQL must wait until I'm done with this data before I can manipulate this piece of data. If there is too much data, the time to execute is too long, and the longer the wait, which is why we have to divide the table.

Too table partition, sub-table, sub-Library differences, suggested view blog: https://www.cnblogs.com/langtianya/p/4997768.html

For more information on table partitioning, it is recommended to view the blog: http://blog.51yip.com/mysql/949.html

MySQL table partition of the database