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

The following mainly brings you how to optimize MySQL big data and decomposition storage. I hope that how to optimize MySQL big data and decomposition storage can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

1): the index of the sql statement has no effect

2): the amount of data queried is too large, resulting in slow data query.

In the course of work, there will be a large amount of data in each database, for example, the number of visits and so on will cause the data query to be slow, so how to solve this problem, let's move on:

Partitions and subtables:

Our database data is getting larger and larger, followed by too much data in a single table. As a result, the query book reading becomes slow, and the application operation is seriously affected by the locking mechanism of the table, which leads to the bottleneck of database performance.

1. Sub-table

What is a sub-table?

Sub-table is to decompose a large table into several physical tables with independent storage space according to certain rules, each table corresponds to three files, MYD data file, .MYI index file, .frm table structure file. These tables can be distributed on the same disk or on different machines. When app reads and writes, it gets the corresponding table name according to the pre-defined rules, and then manipulates it. Mainly for myisam storage, if it is innodb storage, it will be .idb files and .frm files

Split a single database table into multiple data tables, and then allow users to access different tables according to a certain algorithm (such as using hash, or using remainder (modular)), so that the data is distributed into multiple data tables, reducing the access pressure of a single data table. The performance of database access is improved. The purpose of sub-table is to reduce the burden of the database and shorten the query time.

Note: when the client accesses, it does not know that the table has been separated, but it still belongs to a logical whole. for the client, the client is mainly concerned about the content of the query and the speed and efficiency of the query, but as a DBA must understand these; only in this way can we meet the requirements of the customer.

In addition, when dividing the table, it can be divided into two types: vertical segmentation and horizontal segmentation:

Vertical splitting refers to the splitting of data table columns, dividing a table with more columns into multiple tables.

Horizontal split refers to the split of data table rows, splitting the data of a table into multiple tables to store.

The way to divide the table:

1) mysql cluster

It is not a sub-table, but it plays the same role as a sub-table. The cluster can share the number of database operations and share the tasks to multiple databases. The cluster can separate reading and writing, reducing the pressure of reading and writing. Thus improve the performance of the database.

2) pre-estimate the tables with large amount of data and frequently accessed, and divide them into several tables

For example, the app of entertainment news can calculate each hour and the approximate access situation of each day through the number of visits per minute. If this is the case, then we will store the data in separate tables, such as creating 10000 tables and setting a threshold. When a certain amount of data reaches a pre-set value, we want to store the contents in the next table to ensure the performance of the database.

3) using merge storage engine to realize sub-table.

For DBA, if you want to separate the existing big data scale is more painful, the most painful thing is to change the code, because the sql statement in the program has been written, using merge storage engine to achieve sub-table, this method is more suitable.

So let's introduce the usage and functions of merge:

Merge Storage engine:

Merge sub-table, divided into the main table and the child table, the main table is similar to a shell, logically encapsulates the child table, in fact, the data is stored in the child table.

Note: word tables cannot be subdivided where they are used to store real data, but they can be merged. If you want to create multiple word tables, create a few more at the beginning of creation, and several are probably needed for estimation.

We can insert and query the data through the main table, or we can directly manipulate the child tables if the rules of dividing the tables are clear.

So let's give a demonstration of merge. I hope you can have a deeper understanding of merge.

Create a complete table that stores all the member information (table name is tty)

Mysql > drop database IF EXISTS test; = > delete test if it exists

Mysql > create database test;= > create test database

Mysql > use test; = > enter the test library

Create table tty (= > create tty table

Id bigint auto_increment primary key, = > set id number as primary key

Name varchar (20), the character type of = > name

Character types of sextinyint not nulldefault'0' = > gender

) engine=myisam default charset=utf8 auto_increment=1; = > the storage engine is the myisam,utf-8 character set and can be automatically extended.

Next, add some data to it:

Mysql > insert into tty (name,sex) values ('tom1',1)

Mysql > insert into tty (name,sex) select name,sex from tty

If the second statement is executed several times, there will be a lot of data.

After execution, let's query how many pieces of data there are:

Mysql > select * from tty; {there are 8192 data items}

Let's do a sub-table, here we divide tty into two tables tb_tty1,tb_tty2.

Create the tb_ tty1 table:

Mysql > use test

DROP table IF EXISTS tb_tty1

Create table tb_tty1 (

Id bigint primary key

Name varchar (20)

Sex tinyint not null default'0'

) ENGINE=MyISAM DEFAULT CHARSET=utf8

DROP table IF EXISTS tb_tty2

Create table tb_tty2 (

Id bigint primary key

Name varchar (20)

Sex tinyint not null default'0'

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Create a tb_tty2 table

/ / you can also use the following statement create table tb_tty2 like tb_tty1 to create a tb_tty2

Create the master table tb_tty

DROP table IF EXISTS tb_tty

Note: INSERT_METHOD, this parameter INSERT_METHOD = NO indicates that the table cannot be written and used only as a query, and INSERT_METHOD = LAST indicates that it is inserted into the last table. INSERT_METHOD = first means to insert into the first table.

Take a look at the structure of tb_ Ty table, tb_tty1, and tb_tty2:

Mysql > desc tb_tty

Next, we divide the data into two sub-tables:

Mysql > insert into tb_tty1 (id,name,sex) select id,name,sex from tty where id%2=0

Mysql > insert into tb_tty2 (id,name,sex) select id,name,sex from tty where id%2=1

If you want to divide into three tables, you can use ID%3=0, ID%3=1, id%=2

View the data of two child tables: {as mentioned earlier, there are 8192 pieces of data}

Note: the summary table is just a shell, and the access to data occurs in a child table.

Note: each child table has its own independent related table file, while the main table is only a shell and there is no complete related table file.

2. Zoning

What is zoning?

Partitions are similar to subtables in that they are decomposed according to the rules. The difference is that the sub-table decomposes the large table into several independent physical tables, while the partition divides the data into segments and stores it in multiple locations. After the partition, the table is still a table, but the data is hashed to multiple locations.

In addition, there are two types of partitions:

Vertical and horizontal zoning

Horizontal partitioning (Horizontal Partitioning) is a form of partitioning that partitions the rows of a table, and all the columns defined in the table can be found in every dataset, so the characteristics of the table are still maintained.

Vertical partitioning (Vertical Partitioning) generally reduces the width of the target table through vertical partitioning of the table, so that some specific columns are divided into specific partitions, and each partition contains rows corresponding to the columns in it.

Check to see if partitioning is supported when the configuration will be configured:

Mysql > show plugins

In the display result, you can see that partition is ACTIVE, indicating that partitioning is supported.

I demonstrated a sub-table earlier, and then I'll show you how to partition:

Mysql > create database test2

Mysql > use test2

Mysql > create table if not exists user (

Id int not null auto_increment

Name varchar (30) not null default''

Sex int (1) not null default'0'

Primary key (id)

) default charset=utf8 auto_increment=1

Partition by range (id) (

Partition p0 values less than (3)

Partition p1 values less than (6)

Partition p2 values less than (9)

Partition p3 values less than (12)

Partition p4 values less than maxvalue

);

Insert some data

Mysql > insert into test2.user (name,sex) values ('tom1','0')

Mysql > insert into test2.user (name,sex) values ('tom2','1')

Mysql > insert into test2.user (name,sex) values ('tom3','1')

Mysql > insert into test2.user (name,sex) values ('tom4','0')

Mysql > insert into test2.user (name,sex) values ('tom5','0')

Mysql > insert into test2.user (name,sex) values ('tom6','1')

Mysql > insert into test2.user (name,sex) values ('tom7','1')

Mysql > insert into test2.user (name,sex) values ('tom8','1')

Mysql > insert into test2.user (name,sex) values ('tom9','1')

Mysql > insert into test2.user (name,sex) values ('tom10','1')

Mysql > insert into test2.user (name,sex) values ('tom11','1')

Mysql > insert into test2.user (name,sex) values ('tom12','1')

Mysql > insert into test2.user (name,sex) values ('tom13','1')

Mysql > insert into test2.user (name,sex) values ('tom14','1')

Take a look at the place where the database table files are stored.

Through the command:

Mysql > select count (id) as count from user

View partition information from the partitions table in the information_schema system library

Query data from a partition

Mysql > select * from test2.user partition (p0)

New partition

Mysql > alter table test2.user add partition (partition partionname values less than (n))

Delete p5 when using this command before making new additions

Delete partition

When a partition is deleted, all data in that partition is also deleted.

Merging of divisions

The following SQL merges p1-p3 into two partitions p01-p02

Mysql > alter table test2.user

-> reorganize partition p1, p2m, p3 into

-> (partition p01 values less than (8)

Partition P02 values less than (12)

->)

For the above about how to optimize MySQL big data and decompose storage, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.