Mysql table sharding scenario analysis and simple table sharding operations _ MySQL

Mysql table sharding scenario analysis and simple table sharding operations Why table sharding?

First of all, you must know the situations where table sharding requires you to use table sharding when the number of records in a single table reaches millions to tens of millions. the purpose of table Sharding is to reduce the burden on the database, shorten the query time.

Table partitioning can be performed in two ways::

1. horizontal split: data rows are placed in two independent tables based on the values of one or more columns of data.
Horizontal segmentation is usually used in the following scenarios:
The table is very large. After partitioning, the data to be read and the number of index pages can be reduced during Query. at the same time, the number of indexes is reduced and the query speed is improved.
Data in a table is inherently independent. for example, data in different regions or periods is recorded in a table. in particular, some data is often used, while others are not.
Data needs to be stored on multiple media.
Horizontal segmentation increases the complexity of applications. it usually requires multiple table names during query, and the union operation is required to query all data. In many database applications, this complexity will exceed the advantage it brings, because as long as the index keyword is not large, the amount of data in the table is increased by two to three times when the index is used for query, the query increases the number of times an index layer disk is read.

2 vertical split: put the master code and some columns in one table, and then put the master code and other columns in another table.
If some columns in a table are commonly used, but other columns are not commonly used, vertical segmentation can be used. In addition, vertical segmentation can reduce data rows and store more data on one data page, this reduces the number of I/O queries. The disadvantage is that redundant columns need to be managed and join operations are required to query all data.

Scenario:

Blog system

Vertical Segmentation:

The title, author, category, and creation time of the article are slow in change frequency, frequently queried data, and it is best to have good real-time data. We call it cold data.
The blog's page views, replies, and other similar statistical information, or other frequently-changing data, we call it active data.
Therefore, when designing the database structure, we should consider table sharding. First, we should deal with vertical table sharding.
After vertical table sharding:
First, the storage engine is used differently. cold data can be better queried using MyIsam. Active data can be used in Innodb for better update speed.
Second, perform more slave database configuration for cold data, because more operations are queries, so as to speed up the query. For hot data, more master databases can be used for horizontal table sharding.
In fact, for some special active data, you can also consider using memcache, redis and other caches, and then update the database after a certain amount of accumulation.

Horizontal Segmentation:

When the number of blogs reaches a high level, horizontal splitting should be adopted to reduce the pressure on each single table to improve performance.
For example, a blog cold data table can be divided into 100 tables. when 1 million users are browsing a single table, 1 million requests will be made, it is possible that 10 thousand data requests are performed for each table (because it is impossible to perform an absolute average, just assume), which reduces the pressure a lot.

Mysql sharding method: http://blog.csdn.net/heirenheiren/article/details/7896546

Use the Merge storage engine to display horizontal table sharding instances:

View the storage engine of mysql

mysql> show engines  /G;

Real-world scenario simulation

Step 1:Create a member table

DROP table IF EXISTS member;create table member(    id bigint auto_increment primary key,    name varchar(20),    sex tinyint not null default '0')ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; 

Step 2: Create a stored procedure and insert millions of data

# IF a defined stored procedure inserts EXISTS, delete the drop procedure if exists inserts; # The custom Terminator delimiter // # create procedure inserts () begin DECLARE I int; set I = 1; WHILE (I <= 10) DO insert into member (name, sex) values (concat ('name', I), I % 2 ); SET I = I + 1; END WHILE; end; # use the custom Terminator to END the stored procedure definition // # restore Terminator; delimiter; # call inserts ();

By default, the MySQL syntax uses the semicolon ";" as the marker for the end of an SQL statement. you can use the delimiter command to change it to another symbol. for example, "delimiter //" indicates that it uses // as the submission symbol.

To demonstrate table sharding, 10 data records are inserted to the instance for simulation.

Step 3: Create a sub-table

# Table sharding 1 # DROP table if exists tb_member1; create table tb_member1 (id bigint primary key auto_increment, name varchar (20), sex tinyint not null default '0 ') ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 1; # table sharding 2 # DROP table if exists tb_member2; # Copy table 1 create table tb_member2 like tb_member1;

Step 4: Create a primary table. The definition of the primary table is different from that of the target table to be split.

# Master table # DROP table if exists tb_member; create table tb_member (id bigint auto_increment, name varchar (20), sex tinyint not null default '0', INDEX (id )) ENGINE = merge union = (tb_member1, tb_member2) INSERT_METHOD = LAST AUTO_INCREMENT = 1;

Query the index information of the tb_member table

mysql> show index from tb_member /G;

Step 5: Divide the data in the target table into two sub-tables.

INSERT INTO tb_member1(tb_member1.id,tb_member1.name,tb_member1.sex)     SELECT member.id,member.name,member.sex    FROM member where member.id%2=0 ;INSERT INTO tb_member2(tb_member2.id,tb_member2.name,tb_member2.sex)    SELECT member.id,member.name,member.sex    FROM member where member.id%2=1 ;

Of course, in actual scenarios, perform the unique identification operation as needed, and take the hash and so on. here, we only use the simple de-modulo table.

Step 6: View table sharding data

Step 7:View summary table data

In this way, the data in the table member is separated. The Table Group is tb_member, and tb_member1 and tb_member2 are table shards. after table sharding, the data is stored in the table sharding table. The total table is only a shell, and the accessed data is stored in one table shard.

For merge tables, note that
1. the structure of each sub-table must be consistent. The structure of the master table and sub-table must be consistent,
2. the index of each sub-table exists in the merge table. Therefore, you cannot perform a unique search based on the index in the merge table.
3. the sub-table must be the MyISAM engine.
4. REPLACE does not work in the merge table
5. AUTO_INCREMENT will not work as expected

The INSERT_METHOD parameter used to create a Mysql Merge table has several parameters.
LAST if you execute the insert command to operate the merge table, the insert operation adds the data to the LAST sub-table.
Similarly, when inserting data, the data is added to the FIRST sub-table.

For more information, see [http://www.cnblogs.com/dennisit/p/3649931.html].