MySQL > alter table partition; Increase knowledge!

@[toc] Songgo has written an article to introduce MySQL to implement the partition table with MyCat, I don’t know if there is any partner to study, MySQL also has its own partition function, we can create a table with partition, and do not need to use any external tools, today we will take a look at.

1. What is table partitioning

MySQL database data is stored as a file on disk. By default, the MySQL database data is stored in /var/lib/mysql/ directory. Command to view:

A database is a folder, a library, with its corresponding table information, as follows:

In MySQL, if the storage engine is MyISAM, you will see three types of files in the data directory:.frm,.myi,.myd.

*.frmThis is a table definition, a file that describes the structure of the table.
*.myd: This is the data information file, is the table data file.
*.myi: This is the index information file.

If the storage engine is InnoDB, you will see two types of files in the data directory:.frm and.ibd.

*.frm: table structure file.
*.ibd: table data and index files.

Regardless of the storage engine, if the amount of data in a table is too large, the *. Myd, *. Myi, and *. Ibd files will be too large, and data lookup will be slow.

In order to solve this problem, we can use the MySQL partition function, in physics, general a list the corresponding files, divided into many small pieces, so, when we find a data, need not done in a single file the whole traversal, we just need to know which one the data in the data block, and then on that a block of data to find line; On the other hand, if the amount of data in a table is too large to fit on one disk, in this case, we can use table partitioning to allocate data to different disks.

MySQL has added support for partitioning since 5.1. Partitioning is the process of breaking up a table or index into smaller, more manageable parts. For developers, after partition table use way and do not partition is basically the same, only on the physical storage, originally this table only a data file, now turned into multiple, each partition is independent of the object, and can handle alone, also can be used as part of a larger object for processing.

It is important to note that partitioning is not done at the storage engine level. Common storage engines such as InnoDB, MyISAM, NDB and so on all support partitioning. However, not all storage engines support partitioning. For example, CSV, FEDORATED, and MERGE do not support partitioning. Therefore, before using the partitioning function, you need to understand the partitioning function of the selected storage engine.

2. Two ways of partitioning

Unlike MyCat, which has both vertical and horizontal partitioning, MySQL database supports horizontal partitioning, which does not support vertical partitioning.

2.1 Horizontal segmentation

Let’s start with a simple diagram to give you a sense of what horizontal segmentation is:

Suppose I have table-1, Table-2 and Table-3 tables in my DB, horizontal segmentation is to take my 40-meter broadsword, align it with the black line, cut a sword or cut N sword!

Once the hack is complete, place the cut portion in another database instance as follows:

Mysql > insert table into DB; mysql > insert table into DB;

The number of tables in both DB is complete, that is, how many tables there were in DB before, and now there are still several tables.
The data in each table is incomplete, the data is split into different DB.

This is the horizontal sharding of the database, which can also be understood as the sharding by data row, that is, according to a certain rule of a certain field in the table, the table data is divided into multiple libraries, each table contains a part of the data, that is, the horizontal sharding does not change the table structure.

2.2 Vertical segmentation

Let’s start with a simple diagram to get a feel for vertical segmentation:

The so-called vertical segmentation is to take my 40 meters broadsword, aimed at the black line cut. After the hack is complete, put the different tables into different database instances and look like this:

At this time, we found the following characteristics:

The number of tables in each database instance is incomplete.
The data for the tables in each database instance is complete.

That’s vertical segmentation. In general, vertical sharding can be divided by business, with tables for different business placed in different database instances.

The MySQL database supports horizontal partitions.

In addition, MySQL database partitions are local partitioned indexes, that is, a partition stores both data and indexes. Currently, MySQL database does not support global partitioning (data is stored in various partitions, but all data indexes are placed in one object).

3. Why do I need a table partition

More data can be stored in a single table.
Data in partitioned tables is easier to maintain, and large amounts of data can be deleted in batches by clearing entire partitions, or new partitions can be added to support newly inserted data. In addition, a separate partition can be optimized, checked, repaired, and so on.
Part of the query can be determined from the query conditions only fall on a few partitions, the query speed will be very fast.
Partitioned table data can also be distributed across different physical devices, making efficient use of multiple hardware devices.
Partitioned tables can be used to avoid specific bottlenecks, such as mutually exclusive access for InnoDB single indexes and inode lock contention for ext3 file systems.
Individual partitions can be backed up and restored.

Limitations and disadvantages of partitioning:

A table can have a maximum of 1024 partitions.
If a partitioned field has columns with a primary key or unique index, all primary key and unique index columns must be included.
Partitioned tables cannot use foreign key constraints.
A NULL value invalidates partition filtering.
All partitions must use the same storage engine.

4. Partitioning practices

With all that said, let’s look at an example.

First let’s check whether MySQL currently supports partitioning.

You can check whether MySQL supports partitioning by running the show variables like ‘%have_partitioning%’ command before MySQL5.6.1. If have_PARTITIONING is set to YES, partitioning is supported.

As of MySQL5.6.1, the have_partitioning parameter has been removed and replaced with SHOW PLUGINS. If there is a partition row and the value of the STATUS column is ACTIVE, partitions are supported, as shown in the following figure:

Now that we’ve confirmed that MySQL supports partitioning, we can start partitioning!

Let’s look at several different partitioning strategies.

4.1 the RANGE partition

The RANGE partition is relatively simple. It is divided according to the value of a field. However, this field must be a primary key or a field in the combined primary key.

For example, partition by id of user:

If id is less than 100, data is inserted into p0.
If the ID is greater than or equal to 100 and less than 200, partition P1 is inserted.
If id is greater than or equal to 200, partition P2 is inserted.

The above rule covers all ranges of IDS, and without the third rule, an error will be reported when a record with ID 300 is inserted.

Create table SQL as follows:

create  table  user(
  id int primary key,
  username varchar(255)
)engine=innodb
  partition by range(id)(
     partition  p0  values  less  than(100),
     partition  p1  values  less  than(200),
     partition  p2  values  less  than maxvalue  
);
Copy the code

/ var/lib/mysql.test08; /var/lib/ mysql.test08; /var/lib/ mysql.test08;

As you can see, there are several data files at this point.

In the INFORMATION_schema. partitions table, we can view the details of partitions:

You can also write a SQL query:

select * from information_schema.partitions where table_schema='test08' and table_name='user'\G
Copy the code

Each row shows information about a partition, including how it was partitioned, its scope, its fields, how many records it currently has, and so on.

A typical use scenario of the RANGE partition is to partition the table by date. For example, users registered in the same year are placed in the same partition, as follows:

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  createDate date.primary key (id,createDate)
)engine=innodb
  partition by range(year(createDate))(
     partition  p2022  values  less  than(2023),
     partition  p2023  values  less  than(2024),
     partition  p2024  values  less  than(2025));Copy the code

** Note that createDate is a member of the federated primary key. ** If createDate is not a primary key and is just an ordinary field, the following error will be thrown:

Now, if we want to query users registered in 2022, the system will only search for the partition P2022, which can be confirmed by explain execution plan:

If you want to delete users registered in 2022, you only need to delete the partition:

alter table user drop partition p2022;
Copy the code

As you can see from the figure above, after deletion, the data is gone.

4.2 the LIST partition

The LIST partition is similar to the RANGE partition, except that the LIST partition is selected based on column values matching some value in a discrete set of values, rather than being continuous. Here’s an example:

Suppose I have a user table, the user has a gender, now want to separate the user by gender, male store in a partition, female store in a partition, SQL:

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int.primary key(id, gender)
)engine=innodb
  partition by list(gender)(
     partition  man  values  in  (1),
     partition  woman  values  in  (0));
Copy the code

If the value of gender is 1 or 0, the execution will fail. The final result is as follows:

In this way, the efficiency of querying male and female users is high, and the efficiency of deleting a user of a certain gender is also high.

4.3 the HASH partitioning

The purpose of HASH partitioning is to evenly distribute data among predefined partitions to ensure that the amount of data in each partition is roughly the same. In RANGE and LIST partitions, you must explicitly specify in which partition a given column value or collection of column values should be stored; In HASH partitioning, MySQL does this automatically, and all the user has to do is specify an expression based on the column to be hashed and the number of partitions.

To PARTITION a TABLE using HASH partitions, add PARTITION BY HASH (expr) to the CREATE TABLE statement, where expr is a field or an expression that returns an integer. In addition, the number of PARTITIONS is specified by the PARTITIONS attribute. If this is not specified, the default number of PARTITIONS is 1. In addition, HASH PARTITIONS cannot be deleted, so DROP PARTITIONS cannot be used to delete PARTITIONS.

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int.primary key(id, gender)
)engine=innodb partition by hash(id) partitions 4;
Copy the code

4.4 the KEY partition

The KEY partition is similar to the HASH partition, but the KEY partition supports all data types except text and BLOB, while the HASH partition only supports numbers.

The KEY partition cannot be partitioned using user-defined expressions. The KEY partition is partitioned using the HASH function provided by the system.

If a table has a primary KEY or unique index and no field is specified when creating a KEY partition, the system selects the primary KEY column as the partition field by default. If no primary KEY column is specified, the system selects a non-empty unique index column as the partition field by default.

Here’s an example:

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int.primary key(id, gender)
)engine=innodb partition by key(id) partitions 4;
Copy the code

4.5 the COLUMNS partition

COLUMN partition is a partition function introduced in 5.5. There are only two partitions, RANGE COLUMN and LIST COLUMN. Supports integer, date, and character string. This partitioning method is very similar to the partitioning method of RANGE and LIST.

COLUMNS Vs RANGE Vs LIST partition

Partitions for date fields do not need to be converted using functions.
The COLUMN partition supports multiple fields as partitioning keys but does not support expressions as partitioning keys.

Type supported by COLUMNS

Supports tinyINT, SmallINT, MediumInt, int, bigINT. Decimal and float are not supported.
The time type can be date or datetime.
The following characters are supported: CHAR, varchar, binary, varbinary. Text and BLOB are not supported.

Here’s an example:

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  createDate date.primary key(id, createDate)
)engine=innodb PARTITION BY RANGE COLUMNS(createDate) (
    PARTITION p0 VALUES LESS THAN ('1990-01-01'),
    PARTITION p1 VALUES LESS THAN ('2000-01-01'),
    PARTITION p2 VALUES LESS THAN ('2010-01-01'),
    PARTITION p3 VALUES LESS THAN ('2020-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
Copy the code

This is RANGE COLUMNS, and the partition values are continuous.

The LIST COLUMNS partition is similar to enumerations:

create  table  user(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  createDate date.primary key(id, createDate)
)engine=innodb PARTITION BY LIST COLUMNS(createDate) (
    PARTITION p0 VALUES IN ('1990-01-01'),
    PARTITION p1 VALUES IN ('2000-01-01'),
    PARTITION p2 VALUES IN ('2010-01-01'),
    PARTITION p3 VALUES IN ('2020-01-01'));Copy the code

5. Common partition commands

Add partition:

alter table user add partition (partition p3 values less than (4000)); - range partition
Copy the code

alter table user add partition (partition p3 values in (40));  - lists the partition
Copy the code

Delete table partition (delete data) :

alter table user drop partition p30;
Copy the code

Drop all partitions of a table (without data loss) :

alter table user remove partitioning; 
Copy the code

Redefining range partitioned tables (without losing data) :

alter table user partition by range(salary)(
partition p1 values less than (2000),
partition p2 values less than (4000)); 
Copy the code

Redefining hash partitioned tables without losing data:

alter table user partition by hash(salary) partitions 7; 
Copy the code

Partition merge: To merge two partitions into one without data loss:

alter table user  reorganize partition p1,p2 into (partition p1 values less than (1000));
Copy the code

6. Summary

I wonder if you still remember the MyCat tutorial written by Songge in 2019. Are these partitioning strategies very similar to those in MyCat? If you’re interested, go ahead and give it a try

References: