MySQL table partitioning several methods and notes

Partitioning method 1:hash Partition

Example:

CREATE table Thash (x int, y int) partition by hash (x) partitions 4; That's a good word for a table. The next step is to draw a little deeper into the problem;

CREATE TABLE THASH2 (ID int primary key,x int,y int) partition by hash (x) partitions 4;

Thash2 There is no way to create a grammar that is not what is wrong! Is that it does not and rules. Partitioning columns can only be part of a primary key or unique case

The primary key of the THASH2 is the ID but X is not part of the primary key, so it is out of compliance. So you can't create it. I'm not going to say anything else in-depth. Now let's look at a

Partitioning method.

Partitioning method 2:range Partition

Example:

CREATE table Trang (x int,y int) partition by range (x) (partition p0 values less than , partition P1 values L ESS than(+));

Here's a place where I'm going to say that the two critical values of a partition must be enclosed in parentheses. Otherwise it is a grammatical error.

The advantage of this partitioning is that you can lock only the partitions at query time. You do not need to lock the table. You don't believe us. Let's take a look at its execution plan.

Explain partitions select * from Trang where x <1 ;

　　　　

Lock only one partition p0 see it. But the good thing is not necessarily going to happen.

1: For example, your statement is written in select * from Trange; So people must lock not just a partition AH.

Partitioning method 3:list Partition

Example:

CREATE table tlist (x int, y int) partition by list (x) (partition P0 values in (1,3,5,7), partition P1 values in (2,4,6,8));

MySQL table partitioning several methods and notes