centos7-mysql-Sub-table

The larger the database, the more data in a single table, the query read-write, and the locking mechanism, can cause serious database performance impact.
table lock, Row lock, is to ensure the integrity of the data,
Sub-table
The table is a large table, divided into several separate storage space tables, each table corresponds to myd data files, myi index files, FRM table structure files, these tables can be distributed in a storage device, can also be divided into different storage devices.
The equivalent of a table into more than one table, if the app is already in use, and the original table to operate, and so on, need to develop code changes, because the original table has been divided into several, according to the original table is definitely not searchable.
Split the single table, and then according to a certain algorithm, you can spread the data into a number of tables, the data volume of the database, there will be a significant increase,
Reduce database burden and shorten query time, which is the main purpose of the sub-table
MySQL sub-table-divided into vertical segmentation, and horizontal segmentation.
Vertical segmentation is in accordance with cutting points, horizontal is the row of slicing (demand for different self-selection)
Vertical slicing
General vertical segmentation is to separate the frequently queried columns into a single table for easy query
Split horizontally
Table data is very large, can be divided into multiple tables, improve query efficiency
--------------------------------------------------------------------------------------------------------
Several ways to divide a table
1-mysql Cluster
It's not a table, but it's the same thing, and the task is distributed to multiple MySQL databases.
2-The amount of access to the pre-estimate table, which is divided into several tables in case
The data is dispersed into different tables based on an algorithm.
3-Use the merge storage engine to divide the table
The merge can only be used for MyISAM on the table.
The merge is divided into the main table and the child table, the main table is the location of the child table, if you use MyISAM to divide the table, the app can not need to modify the connection data information, or can directly from the main Table query.
Make an instance of merge
Create Database Gao;
Use Gao;
CREATE TABLE Gao (
ID bigint auto_increment PRIMARY key,
Name varchar (20),
Sex tinyint NOT null default ' 0 ')
Engine=myisam default Charset=utf8 auto_increment=1;
Adding data in bulk
Insert into Gao (name,sex) VALUES (' Gao ', 1);
Insert into Gao (name,sex) select Name,sex from Gao; The data is inserted into the query, so the data are just the same as the test
Start a table, create two sub-tables first
Use Gao;
drop table if exists gao_1;
CREATE TABLE Gao_1 (
ID Bingint primary KEY,
Name varchar (20)
Sex tinyint NOT null default ' 0 ')
Engine=myisam default Charset=utf8;
Because the second table is exactly the same, except for the first name.
CREATE table gao_2 like gao_1;
Create a primary table
drop table if exists Mastergao;
CREATE TABLE Mastergao (
ID bigint primary KEY auto_increment,
Name varchar (20),
Sex tinyint NOT null default ' 0 ')
engine=mergeunion= (gao_1,gao_2) insert_method=last Charset=utf8 auto_increment=1;
Insert_method = Last indicates that the tea is the final form of the Tao. Insert INTO first table
The data is imported into two sub-tables.
Insert into Gao_1 (id,name,sex) Select Id,name,sex from Gao where id%2=1;
Insert into Gao_2 (id,name,sex) Select Id,name,sex from Gao where id%2=0;
id%2 value single-double import into two tables
This time to query two tables with the main table, the data are available to delete the original Gao will Mastergao changed to Gao.
ALTER TABLE Mastergao rename Gao;
--------------------------------------------------------------------------------------------------------------

This article is from the "11627223" blog, please be sure to keep this source http://11637223.blog.51cto.com/11627223/1911976

centos7-mysql-Sub-table