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

Blog outline:

1. Why separate tables? 2, MySQL sub-table 3, using merge storage engine to achieve sub-table 4, MySQL partition 1, why sub-table?

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

One mechanism in mysql is table locking and row locking to ensure data integrity. Table locking means that none of you can operate on the table until I finish operating on the table. The same is true for row locking, other sql must wait until I have finished working on this data before I can operate on this data. When this happens, we can consider subtables or partitions.

2. MySQL subtable

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.

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.

The Mysql subtable is divided into vertical segmentation and horizontal segmentation. The specific differences are as follows:

Vertical splitting refers to the splitting of data table columns. A table with more columns is divided into multiple tables. Usually we split vertically according to the following principles: separate the less commonly used fields in a single table; split large fields such as text,blob (binary large object, binary large objects) and put them in the schedule.

Often the columns of combined queries are placed in a table; vertical splitting is more often the step that should be performed at the beginning of the design of the data table, and then the query is critical with join.

Horizontal split refers to the split of data table rows, splitting the data of a table into multiple tables to store. Horizontal splitting principle, usually, we use hash, modularization and other methods to split the table, for example, a user table users with 400W, to improve its query efficiency, we divide it into four tables users1,users2,users3. Users4 divides the data into four tables by using ID modeling. Id%4= then queries, updates, and deletes some business logic by taking models. It can also be archived and split by region, year and other fields. When we split the table, we have to constrain the user's query behavior. For example, we split by year, at this time in the page design constraints on the user must first select the year, and then can query.

3. Using merge storage engine to realize sub-table.

Note: only the original table of the myisam engine can be divided into tables using the 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. 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.

Take a chestnut:

1) create a complete table mysql > create database test;mysql > use test;mysql > create table member (- > id bigint auto_increment primary key,-> name varchar (20),-> sex tinyint not null default'0'->) engine=myisam default charset=utf8 auto_increment=1;mysql > insert into member (name,sex) values ('tom1',1); mysql > insert into member (name,sex) select name,sex from member;mysql > select count (*) from member +-+ | count (*) | +-+ | 4096 | +-+ 1 row in set (0.00 sec) 2) divide the complete table above

Notes for sub-table:

The field definitions of the child table and the main table need to be consistent, including data type, data length, etc.; when the sub-table is completed, all operations (addition, deletion, modification and query) need to be carried out on the main table, although the main table does not store the actual data. Mysql > create table tb_member1 like member;mysql > create table tb_member2 like member;mysql > create table tb_member (- > id bigint auto_increment primary key,-> name varchar (20),-> sex tinyint not null default'0'->) engine=merge union= (tb_member1,tb_member2) insert_method=last charset=utf8

Note: when creating the main table above, the specified "insert_method=last" has three optional parameters, namely: last: insert into the last table; first: insert into the first table; NO: means that the table can not do any write operation, only used as a query.

3) the structure of the three tables you just created is as follows:

4) divide the data into two tables: mysql > insert into tb_member1 (id,name,sex) select id,name,sex from member where id%2=0;Query OK, 2048 rows affected (0.01sec) Records: 2048 Duplicates: 0 Warnings: 0mysql > insert into tb_member2 (id,name,sex) select id,name,sex from member where id%2=1;Query OK, 2048 rows affected (0.01sec) Records: 2048 Duplicates: 0 Warnings: 05) View the data in the main table and two child tables

Some of the data in the first subtable are as follows:

Some of the data in the second subtable are as follows:

Some of the data queried in the main table section are as follows:

The total number of rows of data is as follows:

Note: the summary table is just a shell, and the access to data occurs in a child table. Each child table has its own independent related table file, and the main table is only a shell, and there is no complete related table file. When it is determined that the data that can be found in the main table is exactly the same as that found before the sub-table, the original table can be deleted, and then the read and write operation of the table can be carried out on the main table after the sub-table. The local files corresponding to the above three tables are as follows:

As you can see, the local data file of the master table that can query all the data is very small, which verifies that the data does not exist in this master table.

6) insert data into the main table, as follows:

You can see that the two new pieces of data are inserted in the second table, because when creating the main table, the specified "insert_method" is last, that is, all operations to insert data are performed on the last table. You can modify the insertion method through the alter instruction, as follows:

Mysql > alter table tb_member INSERT_METHOD=first

After modifying the insertion method, and then inserting data into the table by yourself, you can find that all the data is written to the first table (I have inserted four pieces of data here), as shown below:

Four new pieces of data have been added above, and you can find that all of them have been inserted into the first table.

If you change the insert method to no, it means that no more data can be inserted into the table, as follows:

.

4. MySQL Partition 1) what is a partition?

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. App still operates on table names when reading and writing, and db automatically organizes the partitioned data.

There are two main forms of zoning:

Horizontal partitioning: this form of partitioning partitions the rows of the table, and all the columns defined in the table can be found in each dataset, so the characteristics of the table are still maintained.

To take a simple example: a table containing ten-year invoice records can be partitioned into ten different partitions, each containing records for one year.

Vertical partitioning: generally speaking, this partitioning method reduces the width of the target table by dividing the table vertically, so that certain columns are divided into specific partitions, and each partition contains rows corresponding to the columns in it.

To take a simple example: a table that contains large text and BLOB columns, and these text and BLOB columns are not often accessed, so it is necessary to divide these infrequently used text and BLOB into another partition to improve access speed while ensuring their data relevance.

2) check whether the current database supports partitioning

Before MySQL 5.6, use the following parameters to check whether the current configuration supports partitions (if it is yes, partitions are supported):

Mysql > SHOW VARIABLES LIKE'% partition%' +-+-+ | Variable_name | Value | +-+-+ | have_partition_engine | YES | + -+

View it in the following ways at 5.6 and beyond:

Mysql > show plugins

Among the returned results, there are the following fields (if status is listed as "ACTIVE", partitioning is supported):

3) Table partition mysql > create table user (- > id int not null auto_increment,-> name varchar (30) not null default'',-> sex int (1) not null default '0mm,-> 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->)

Note: in the table created above, when the value of the id column is less than 3, it will be inserted into the p0 partition, records greater than 3 and less than 6 will be inserted into the p1 partition, and so on, all records with id values greater than 12 will be inserted into the p4 partition.

4) use stored procedures to insert some data mysql > delimiter / /

5) go to the directory where the datasheet file is stored and have a look:

You can see that the data is scattered into different files, and the local file names are all "user#P#p0...". Where p0 is the custom partition name.

6) number of statistical data rows

7) View partition information from the partition table in the information_schema system library

8) query data from the partition

9) add and merge partitions (need to merge partitions first and then add new partitions)

1. Add Partition:

Note: since the last partition range specified when creating the table is maxvalue, it is not possible to add partitions directly, as follows:

MAXVALUE can only be used in the last partition definition

However, the partition whose maxvalue is finally defined cannot be deleted directly, because if the partition is deleted, the data in the partition will also be lost. Therefore, if you need to add a new partition, you should merge the partition first, and then add the partition. Only in this way can the integrity of the data be guaranteed.

Mysql > alter table user reorganize partition p4 into (partition p03 values less than (15), partition p04 values less than maxvalue)

The purpose of the above command is to divide the last partition into two partitions, one is the partition you need, and the last partition is maxvalue (which must also be maxvalue), so you finish adding partitions.

The documents on this table are as follows:

Query the data in the new partition as follows:

two。 Merge Partition

Merge the four partitions p0, p1, p2, and p3 into p02:

Mysql > alter table user-> reorganize partition p0 values less than p1 into-> (partition p02 values less than (12))

You can see that p02 will integrate the data of the three partitions of p0Magi, p1and p2, and p3, as follows:

The local files are as follows:

10) Delete partition mysql > alter table user drop partition p02; # delete partition p02

Note: after the partition is deleted, the data in the partition will also be deleted. All the data in the table that deletes partition p02 is as follows:

-this is the end of this article. Thank you for reading-

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.