Shulou(Shulou.com)06/01 Report--

The growing size of the database, too much data in a single table, query read and write, and locking mechanism will lead to serious impact on database performance.

Table locks, row locks, are all to ensure the integrity of data

Sub-table

A sub-table is a table that divides a large table into several independent storage spaces. Each table corresponds to MYD data file, MYI index file and FRM table structure file. These tables can be distributed in one storage device or in different storage devices.

Equivalent to a table into multiple tables, if there is APP already in use, and will operate on the original table, etc., need to develop modified code, because the original table has been divided into several, according to the original table is definitely not searched.

Split the single table, and then according to a certain algorithm, the data can be dispersed into multiple tables, which will significantly improve the database with a large amount of data.

Reduce the burden of the database and shorten the query time, which is the main purpose of the table.

Mysql subtable-divided into vertical segmentation and horizontal segmentation.

Vertical segmentation is divided according to columns, and horizontal is row segmentation (selected according to different requirements)

Vertical slicing

Generally, vertical segmentation divides frequently queried columns into a single table, which is convenient for query.

Horizontal split

The data of the table is so large that it can be divided into multiple tables to improve the query efficiency.

-

Several ways of dividing tables

1-mysql cluster

This is not a sub-table, but it also has the same function, and the task is shared among multiple mysql databases.

2-pre-estimate the number of visits to the table, divided into several tables in advance, just in case

According to a certain algorithm, the data is distributed into different tables.

3-use merge storage engine to divide tables

Merge can only be used for sub-table on myisam.

Merge is divided into the main table and the sub-table. The location of the sub-table is in the main table. If you use myisam to divide the table, APP can not modify the information of the join data, or it can query directly from the main table.

Make an example 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

Add data in batch

Insert into gao (name,sex) values ('gao',1)

Insert into gao (name,sex) select name,sex from gao; inserts the queried data, so the data is all the same for testing purposes only

To start with sub-tables, first create two child tables

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 watch is exactly the same as the one above, except for the name.

Create table gao_2 like gao_1

Create a master 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 for the last table of tea, first for inserting into the first table

The data is imported into two child 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

The values after id%2 are imported into two tables.

At this time, query the two tables and the main table, and with all the data, you can delete the original gao and change mastergao to gao.

Alter table mastergao rename gao; completed

-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.