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

Let's learn about the partition and sub-table of mysql. I believe you will benefit a lot after reading it. The text is not much in essence. I hope the short content of mysql partition and sub-table is what you want.

Mysql subtables and partitions

1.mysql 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. In the case of the Innodb storage engine, the index file and the data file are stored in the same location. These tables can be distributed on the same disk or on different machines.

When app reads and writes, it gets the corresponding indication according to the pre-defined rules, and then operates it.

Split a single database table into multiple data tables, and then when the user accesses it, according to a certain algorithm (such as using hash, you can also use the residual method), let the user access different tables, 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 mysql subtable is divided into vertical segmentation and horizontal segmentation.

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

Vertical segmentation is usually carried out according to the following principles:

Put the infrequently used fields on a separate table

Split text,blob (binary large object, binary large object) and other large fields and put them in the schedule

The columns of frequently combined queries are placed in one table.

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

Horizontal split principle

Usually, we use hash, modularization, etc., to split the table.

When we split the table, we have to constrain the user's query behavior.

There are several ways to divide the table:

1) pre-estimate tables with large amounts of data and frequently accessed, and divide them into several tables

2) using merge storage engine to realize sub-table

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

And insert point data into it:

Mysql > select * from member

+-- +

| | id | name | sex | |

+-- +

| | 1 | tom | 1 |

| | 2 | tom | 1 | |

| | 3 | tom | 1 | |

| | 4 | tom | 1 | |

| | 5 | tom | 1 | |

| | 6 | tom | 1 | |

| | 7 | tom | 1 | |

| | 8 | tom | 1 | |

| | 9 | tom | 1 | |

| | 10 | tom | 1 | |

| | 11 | tom | 1 | |

| | 12 | tom | 1 | |

| | 13 | tom | 1 | |

| | 14 | tom | 1 | |

| | 15 | tom | 1 | |

| | 16 | tom | 1 | |

+-- +

Here we divide the member into two tables, tb_member1,tb_member2.

Mysql > use test

Mysql > create table tb_member1 (

-> id bigint primary key

-> name varchar (20)

-> sex tinyint not null default'0'

->) engine=myisam default charset=utf8

You can create the same table as tb_member1 more succinctly with the following command:

Mysql > create table tb_member2 like tb_member1

Create the master table tb_member

Mysql > create table tb_member (

-> id bigint primary key

-> name varchar (20)

-> sex tinyint not null default'0'

->) engine=merge union= (tb_member1,tb_member2) insert_method=last charset=utf8

Look at the structure of the tb_ membership table:

Mysql > desc tb_member; +-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | bigint (20) | NO | PRI | NULL |

| | name | varchar (20) | YES | | NULL |

| | sex | tinyint (4) | NO | | 0 | |

+-+ +

Note: the field definition of the child table should be consistent with that of the main table.

Next, divide the data into two subtables:

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

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

View the data for the two child tables:

Mysql > select * from tb_member1

+-- +

| | id | name | sex | |

+-- +

| | 2 | tom | 1 | |

| | 4 | tom | 1 | |

| | 6 | tom | 1 | |

| | 8 | tom | 1 | |

| | 10 | tom | 1 | |

| | 12 | tom | 1 | |

| | 14 | tom | 1 | |

| | 16 | tom | 1 | |

+-- +

8 rows in set (0.00 sec)

Mysql > select * from tb_member2

+-- +

| | id | name | sex | |

+-- +

| | 1 | tom | 1 |

| | 3 | tom | 1 | |

| | 5 | tom | 1 | |

| | 7 | tom | 1 | |

| | 9 | tom | 1 | |

| | 11 | tom | 1 | |

| | 13 | tom | 1 | |

| | 15 | tom | 1 | |

+-- +

8 rows in set (0.00 sec)

Take a look at the data of the main table:

Mysql > select * from tb_member

+-- +

| | id | name | sex | |

+-- +

| | 2 | tom | 1 | |

| | 4 | tom | 1 | |

| | 6 | tom | 1 | |

| | 8 | tom | 1 | |

| | 10 | tom | 1 | |

| | 12 | tom | 1 | |

| | 14 | tom | 1 | |

| | 16 | tom | 1 | |

| | 1 | tom | 1 |

| | 3 | tom | 1 | |

| | 5 | tom | 1 | |

| | 7 | tom | 1 | |

| | 9 | tom | 1 | |

| | 11 | tom | 1 | |

| | 13 | tom | 1 | |

| | 15 | tom | 1 | |

+-- +

16 rows in set (0.00 sec)

Summary: each child table has its own independent table file, the main table is just a shell, and there is no complete table file.

[root@localhost] # ls-l / usr/local/mysql/data/test/tb_member*

-rw-r- 1 mysql mysql 8614 Feb 13 21:44 / usr/local/mysql/data/test/tb_member1.frm

-rw-r- 1 mysql mysql 160 Feb 13 21:47 / usr/local/mysql/data/test/tb_member1.MYD

-rw-r- 1 mysql mysql 2048 Feb 13 21:47 / usr/local/mysql/data/test/tb_member1.MYI

-rw-r- 1 mysql mysql 8614 Feb 13 21:44 / usr/local/mysql/data/test/tb_member2.frm

-rw-r- 1 mysql mysql 160 Feb 13 21:47 / usr/local/mysql/data/test/tb_member2.MYD

-rw-r- 1 mysql mysql 2048 Feb 13 21:47 / usr/local/mysql/data/test/tb_member2.MYI

-rw-r- 1 mysql mysql 8614 Feb 13 21:46 / usr/local/mysql/data/test/tb_member.frm

-rw-r- 1 mysql mysql 42 Feb 13 21:46 / usr/local/mysql/data/test/tb_member.MRG

two。 Zoning

What is zoning?

The difference between partition and sub-table: a sub-table decomposes a large table into several independent physical tables, while a partition divides the data into segments and stores them in multiple locations. after partitioning, the table is still a large 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: partitions the rows of the table, and all the columns defined in the table can be found in each dataset, so the properties of the table are maintained.

Vertical partitioning: 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.

Zoning technical support

Before 5.6, use the following parameters to see if the current configuration supports partitioning

Mysql > show variables like'% partition%'

YES when the have_partition_engine option is displayed

After 5.6, view it in the following way

Mysql > show plugins

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

The following shows a table partition by range (range)

Create a range partition table

Mysql > create table 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 data:

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

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

Mysql > insert into user (name,sex) values ('tom3','2')

Mysql > insert into user (name,sex) select name,sex from user; (repeat several times to get double data)

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

[root@localhost] # ls-l / usr/local/mysql/data/test/user*

-rw-r- 1 mysql mysql 8614 Feb 13 21:59 / usr/local/mysql/data/test/user.frm

-rw-r- 1 mysql mysql 98304 Feb 13 22:00 / usr/local/mysql/data/test/user#P#p0.ibd

-rw-r- 1 mysql mysql 98304 Feb 13 22:00 / usr/local/mysql/data/test/user#P#p1.ibd

-rw-r- 1 mysql mysql 98304 Feb 13 22:00 / usr/local/mysql/data/test/user#P#p2.ibd

-rw-r- 1 mysql mysql 98304 Feb 13 22:00 / usr/local/mysql/data/test/user#P#p3.ibd

-rw-r- 1 mysql mysql 98304 Feb 13 22:00 / usr/local/mysql/data/test/user#P#p4.ibd

View partition information from the partition table in the system database

Mysql > select * from information_schema.partitions where table_schema='test' and table_name='user'\ G

Merge partitions:

Eg: merge p1-p3 into 2 p01-p02

Mysql > alter table user

-> reorganize partition p1, p2m, p3 into

-> (partition p01 values less than (8)

Partition P02 values less than (12)

->)

Look at the database table file again:

[root@localhost] # ls-l / usr/local/mysql/data/test/user*

-rw-r- 1 mysql mysql 8614 Feb 13 22:03 / usr/local/mysql/data/test/user.frm

-rw-r- 1 mysql mysql 98304 Feb 13 22:03 / usr/local/mysql/data/test/user#P#p01.ibd

-rw-r- 1 mysql mysql 98304 Feb 13 22:03 / usr/local/mysql/data/test/user#P#p02.ibd

-rw-r- 1 mysql mysql 98304 Feb 13 22:00 / usr/local/mysql/data/test/user#P#p0.ibd

-rw-r- 1 mysql mysql 98304 Feb 13 22:00 / usr/local/mysql/data/test/user#P#p4.ibd

Performance testing of unpartitioned tables and partitioned tables

Create an unpartitioned table

Mysql > create table tab2 (C1 int,c2 varchar (30), c3 date)

-> partition by range (year (c3)) (partition p0 values less than (1995)

-> partition p1 values less than (1996)

-> partition p2 values less than (1997)

-> partition p3 values less than (1998)

-> partition p4 values less than (1999)

-> partition p5 values less than (2000)

-> partition p6 values less than (2001)

-> partition p7 values less than (2002)

-> partition p8 values less than (2003)

-> partition p9 values less than (2004)

-> partition p10 values less than (2010)

-> partition p11 values less than maxvalue)

Insert 100000 pieces of data through a stored procedure

Create a stored procedure:

Mysql > delimiter $$

Mysql > create procedure load_part_tab ()

-> begin

-> declare v int default 0

-> while v

< 10000 ->

Do

-> insert into tab1

-> values (v values testing partitions',adddate ('1995-01-01), (rand (v) * 36520) mod 3652))

-> set v = v + 1

-> end while

-> end

-> $$

Execute the stored procedure:

Mysql > call load_part_tab ()

Insert data into the tab2 table

Insert into tab2 select * from tab1

Test the performance of SQL

Mysql > select count (*) from tab1 where c3 > '1995-01-01' and c3

< '1995-12-31'; +----------+ | count(*) | +----------+ | 990 | +----------+ 1 row in set (0.11 sec) mysql>

Select count (*) from tab2 where c3 > '1995-01-01' and c3

< '1995-12-31'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.03 sec) 分区表比未分区表的执行时间少很多。 创建索引后情况测试 mysql>

Create index idx_of_c3 on tab1 (c3)

Query OK, 0 rows affected (0.28 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > create index idx_of_c3 on tab2 (c3)

Query OK, 0 rows affected (0.22 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > select count (*) from tab1 where c3 > '1996-01-01' and c3

< '1996-12-31'; +----------+ | count(*) | +----------+ | 1006 | +----------+ 1 row in set (0.11 sec) 重启mysql服务 mysql>

Select count (*) from tab1 where c3 > '1996-01-01' and c3 < '1996-12-31'

+-+

| | count (*) |

+-+

| | 1006 |

+-+

1 row in set (0.00 sec)

After the creation of the index, there is little difference between the partitioned table and the unpartitioned table (the larger the amount of data, the more obvious the difference)

After reading this article on the partition and sub-table of mysql, many readers will certainly want to know more about it. For more industry information, you can follow our industry information section.

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.