A simple MySQL partition instance is used to solve the problem of large data tables.

Simple MySQL partition instance

MySQL database partition
MySQL database partitions are also called MySQL database table shards. When a database in a table is very large, the query speed is very slow, which affects the efficiency and increases the I/O pressure, in this way, a table can be divided into multiple tables according to certain rules. The main table sharding types include range, list, hash, and key, for more information, see MySQL and later reference manual.

MySQL partitions support MySQL 5.1 and later versions. If your MySQL version is 5.1 or later, check whether partitions are supported:
Mysql> show variables like 'H % ';
+ --------- + -------- +
| Variable_name | value |
+ --------- + -------- +
| Have_community_features | Yes |
| Have_compress | Yes |
| Have_crypt | Yes |
| Have_csv | Yes |
| Have_dynamic_loading | Yes |
| Have_geometry | Yes |
| Have_innodb | no |
| Have_ndbcluster | no |
| Have_openssl | no |
| Have_partitioning | Yes |-> if this parameter is set to yes, Table Partitioning is supported. If it is set to no, recompile the table.
| Have_query_cache | Yes |
| Have_rtree_keys | Yes |
| Have_ssl | no |
| Have_symlink | Yes |
| Hostname | localhost. localdomain |
+ --------- + -------- +
15 rows in SET (0.02 Sec)

If this feature is not supported, download mysql5.1and install it again, and then compile and install mysql-5.1.30.tar.gz.

1.download mysql-5.1.30.tar.gz source code,
2. Extract
Tar xvzf mysql-5.1.30.tar.gz
CD mysql-5.1.30
./Configure-Prefix =/usr/local/MySQL \
-Localstatedir =/data \
-Datadir =/data \
-With-plugins = partition // Add Partition Function

Make & make install
CD/usr/local/MySQL/bin
./Mysql_install_db
./Mysqld_safe &
3. If the installation is successful, MySQL supports the partition function.

4. Simple partitioning instance. Take the range partition type as an example:
Create Table users00 (
UID int unsigned not null auto_increment primary key,
Name varchar (30) not null default "",
Email varchar (30) not null default ""
) Partition by range (UID)
(Partition P0 values less than (10000) data directory = "/data00/" index directory = "/data00 /",
Partition P1 values less than (20000) data directory = "/data00/" index directory = "/data00 /",
Partition P2 values less than (30000) data directory = "/data00/" index directory = "/data00 /",
Partition P3 values less than maxvalue data directory = "/data00/" index directory = "/data00 /"
);
The partition type of this table is range, which is divided into four partitions. The UID is 0 ~ 10000 in P0, 10001 ~ 20000 in P1...
View the data directory of the users00 table:
....
-RW -- 1 Root 8620 Nov 22 14: 47 users00.frm
-RW -- 1 Root 32 Nov 22 users00.par
Lrwxrwxrwx 1 Root 24 Nov 22 users00 # P # Running MYD->/data00/users00 # P # Running MYD
Lrwxrwxrwx 1 Root 24 Nov 22 users00 # P # Running myi->/data00/users00 # P # Running myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p1.myd->/data00/users00 # P # p1.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p1.myi->/data00/users00 # P # p1.myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p2.myd->/data00/users00 # P # p2.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p2.myi->/data00/users00 # P # p2.myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p3.myd->/data00/users00 # P # p3.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p3.myi->/data00/users00 # P # p3.myi
....

At this time, the table data has reached the physical partition, which can reduce the I/O of the same directory. At the same time, the 3 W records of a single table are reduced to the 1 W data volume of a single table, which improves the query efficiency.

5. Modify and merge partitions

The following is an example of dividing P0 partitions into 2 partitions S0 and S1:

Mysql> alter table users00 reorganize partition P0 into (partition S0 values less than (3999) data directory = '/data00/'index directory ='/data00 /', partition S1 values less than (10000) data directory = '/data00/'index directory ='/data00 /');
Query OK, 0 rows affected (0.03 Sec)
Records: 0 duplicates: 0 Warnings: 0

Mysql>
Split successfully. View/data00/data directory:
.....
-RW -- 1 Root 8620 Nov 22 :01 users00.frm
-RW -- 1 Root 40 Nov 22 :01 users00.par
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p1.myd->/data00/users00 # P # p1.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p1.myi->/data00/users00 # P # p1.myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p2.myd->/data00/users00 # P # p2.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p2.myi->/data00/users00 # P # p2.myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p3.myd->/data00/users00 # P # p3.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p3.myi->/data00/users00 # P # p3.myi
Lrwxrwxrwx 1 Root 24 Nov 22 :01 users00 # P # s0.myd->/data00/users00 # P # s0.myd
Lrwxrwxrwx 1 Root 24 Nov 22 :01 users00 # P # s0.myi->/data00/users00 # P # s0.myi
Lrwxrwxrwx 1 Root 24 Nov 22 :01 users00 # P # s1.myd->/data00/users00 # P # s1.myd
Lrwxrwxrwx 1 Root 24 Nov 22 :01 users00 # P # s1.myi->/data00/users00 # P # s1.myi
......

The following table lists the S0 and S1 partitions as P0 partitions.

mysql> alter table users00 reorganize partition S0, S1 into (partition P0 values less than (10000) data directory = "/data00/" index directory = "/data00/");
query OK, 0 rows affected (0.03 Sec)
records: 0 duplicates: 0 Warnings: 0

In this case, S0 and S1 are merged into P0 partitions.
....
-RW -- 1 Root 8620 Nov 22 :04 users00.frm
-RW -- 1 Root 32 Nov 22 :04 users00.par
Lrwxrwxrwx 1 Root 24 Nov 22 users00 # P # Running MYD->/data00/users00 # P # Running MYD
Lrwxrwxrwx 1 Root 24 Nov 22 users00 # P # Running myi->/data00/users00 # P # Running myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p1.myd->/data00/users00 # P # p1.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p1.myi->/data00/users00 # P # p1.myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p2.myd->/data00/users00 # P # p2.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p2.myi->/data00/users00 # P # p2.myi
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p3.myd->/data00/users00 # P # p3.myd
Lrwxrwxrwx 1 Root 24 Nov 22 39 39 users00 # P # p3.myi->/data00/users00 # P # p3.myi
....

For details about partition information and functions, refer to the MySQL reference manual, which provides a very detailed introduction. Here I am just a simple implementation, and many places are not detailed enough, it is only used as a reference for database sharding and data separation.