MySQL's partition partition

Preface: When the data stored in a table is particularly high, such as a single. MyD data has reached 10G, it is inevitable that the reading efficiency is very low, this time we can use to divide the data into several tables to solve the problem.
Mode one: Through the business logic according to the size of the data by id%10 this to be divided into user1,user2,user3 and so on, but this will have a lot of problems we need to maintain such a hash relationship,

And every time we read the data and write the data to judge the table, this is our program to identify the table and read the table.
Mode two: MySQL can be partitioned through partition, this partition shows us the data is still in a data table, does not affect our reading query data,

But the MySQL internal file mechanism implements the data stored in different data files, the advantage is that MySQL automatically separate the corresponding data into the different. myd files.

Greatly reduces the size of the file, the data is allocated, it is good to improve the efficiency.

Today's analysis is the way two uses MySQL's partition to partition

The database name: Test
MySQL Installation path:/data/local/mysql/
MySQL's partition partition is divided into two categories: by range partition (ranges), by hash partition (list)

One: Partitioning by scope

Mysql> CREATE TABLE Topic (

Tid int (4) primary key auto_increment,

Title char (+) NOT null default ')

-Engine=myisam Default Charset=utf8

-Partition by Range (TID) (

-Partition test0 values less than (10),

-partition test1 values less than (20),

-partition test2 values less than (MaxValue));

Query OK, 0 rows affected (0.05 sec)

Enter the file directory of the test database to view the generated files
ls-all/data/local/mysql/data/test/
It is particularly important to note that the file type is. The MyD store here is the data

-RW-RW----1 mysql mysql 8590 01:27 topic.frm

-RW-RW----1 MySQL mysql 01:27 topic.par

-RW-RW----1 mysql mysql 0 20:13 topic#p#test0. MYD

-RW-RW----1 MySQL mysql 1024x768 20:13 topic#p#test0. MYI

-RW-RW----1 mysql mysql 0 20:13 topic#p#test1. MYD

-RW-RW----1 MySQL mysql 1024x768 20:13 topic#p#test1. MYI

-RW-RW----1 mysql mysql 0 20:13 topic#p#test2. MYD

-RW-RW----1 MySQL mysql 1024x768 20:13 topic#p#test2. MYI

Let's add some data to see how it works

mysql> INSERT into topic (' title ') VALUES (' a ');

Query OK, 1 row Affected (0.00 sec)

-RW-RW----1 MySQL mysql 8660 May 6 23:52 t.frm

-RW-RW----1 MySQL mysql 192 May 6 23:52 T.myd

-RW-RW----1 MySQL mysql 2048 May 6 23:52 t.myi

-RW-RW----1 mysql mysql 8590 01:27 topic.frm

-RW-RW----1 MySQL mysql 01:27 topic.par

-RW-RW----1 mysql mysql 20:16 topic#p#test0. MYD

-RW-RW----1 MySQL mysql 2048 may 20:16 topic#p#test0. MYI

-RW-RW----1 mysql mysql 0 20:13 topic#p#test1. MYD

-RW-RW----1 MySQL mysql 1024x768 20:13 topic#p#test1. MYI

-RW-RW----1 mysql mysql 0 20:13 topic#p#test2. MYD

-RW-RW----1 MySQL mysql 1024x768 20:13 topic#p#test2. MYI

Found Topic#p#test0. MyD file size increased the description data was written to the topic#p#test0. MyD is written to the partition we created before the TEST0 partition.

We're going to insert another piece of data and see what happens.

mysql> INSERT INTO topic (' tid ', ' title ') VALUES (one, ' H ');

Query OK, 1 row Affected (0.00 sec)

-RW-RW----1 mysql mysql 8590 01:27 topic.frm

-RW-RW----1 MySQL mysql 01:27 topic.par

-RW-RW----1 mysql mysql 20:16 topic#p#test0. MYD

-RW-RW----1 MySQL mysql 2048 may 20:16 topic#p#test0. MYI

-RW-RW----1 mysql mysql 20:18 topic#p#test1. MYD

-RW-RW----1 MySQL mysql 2048 may 20:18 topic#p#test1. MYI

-RW-RW----1 mysql mysql 0 20:13 topic#p#test2. MYD

-RW-RW----1 MySQL mysql 1024x768 20:13 topic#p#test2. MYI
A data ID of 11 was inserted to discover Topic#p#test1. MyD file size increased the description data was written to the partition for test1 inside went

Let's check the data in the table below.

Mysql> select * from topic;

+-----+-------+

| Tid | Title |

+-----+-------+

| 1 | A |

| 11 | H |

+-----+-------+

2 rows in Set (0.00 sec)

Description: MySQL's partition is written to the corresponding range in the range (range) partition by the range of the ID of the field (int type).

Second: partition according to the hash point

Mysql> CREATE TABLE Area (

UID Int (10),

Uname char (6),

aid int)

Engine=myisam CharSet UTF8

-Partition by List (aid) (

-Partition HB values in (1),

-Partition HN values in (2),

Partition GD values in (3),

Partition GX values in (4));

Query OK, 0 rows affected (0.01 sec)

To view the generated file information
ls-all/data/local/mysql/data/test/

-RW-RW----1 mysql mysql 8618 19:52 area.frm

-RW-RW----1 MySQL mysql 19:52 area.par

-RW-RW----1 mysql mysql 0 19:52 area#p#gd. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#gd. MYI

-RW-RW----1 mysql mysql 0 19:52 area#p#gx. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#gx. MYI

-RW-RW----1 mysql mysql 0 19:52 area#p#hb. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#hb. MYI

-RW-RW----1 mysql mysql 0 19:52 area#p#hn. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#hn. MYI

We insert a user information from Hubei

mysql> insert INTO ' area ' (' uname ', ' aid ') values (' Dong Zi ', 1);

Query OK, 1 row Affected (0.00 sec)

We'll check the information again to discover AREA#P#HB. MyD files are written to the data

-RW-RW----1 mysql mysql 8618 19:52 area.frm

-RW-RW----1 MySQL mysql 19:52 area.par

-RW-RW----1 mysql mysql 0 19:52 area#p#gd. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#gd. MYI

-RW-RW----1 mysql mysql 0 19:52 area#p#gx. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#gx. MYI

-RW-RW----1 mysql mysql 20:03 area#p#hb. MYD

-RW-RW----1 MySQL mysql 1024x768 20:03 area#p#hb. MYI

-RW-RW----1 mysql mysql 0 19:52 area#p#hn. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#hn. MYI

We'll insert a message from the people of Hunan.

mysql> insert INTO ' area ' (' uname ', ' aid ') VALUES (' Lxm ', 2);

Query OK, 1 row Affected (0.00 sec)

After inserting we found AREA#P#HN. The size of the MyD file adds a description to the data that was written

-RW-RW----1 mysql mysql 8618 19:52 area.frm

-RW-RW----1 MySQL mysql 19:52 area.par

-RW-RW----1 mysql mysql 0 19:52 area#p#gd. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#gd. MYI

-RW-RW----1 mysql mysql 0 19:52 area#p#gx. MYD

-RW-RW----1 MySQL mysql 1024x768 19:52 area#p#gx. MYI

-RW-RW----1 mysql mysql 20:03 area#p#hb. MYD

-RW-RW----1 MySQL mysql 1024x768 20:03 area#p#hb. MYI

-RW-RW----1 mysql mysql 20:06 area#p#hn. MYD

-RW-RW----1 MySQL mysql 1024x768 20:06 area#p#hn. MYI

Look at the data in the table.

Mysql> select * from area;

+------+--------+------+

| UID | uname | Aid |

+------+--------+------+

| NULL |    Dong Zi | 1 |

| NULL |    LXM | 2 |

+------+--------+------+

2 rows in Set (0.00 sec)
Note: Partitioning according to the hash point is automatically written to the corresponding partition data file according to an ID field associated with the inserted information, thus realizing the partitioning of the table.

MySQL's partition partition