Detailed code explanation of the four partition types of MySQL tables

I have only implemented the following four database partitions: RANGE (range) partitions. There are three more that I will try to implement later. Friends who have implemented them can share what they think is a good write-up. Article

MySQLFour partition types of tables

1. What is table partitioning
In layman terms, table partitioning is a large table. The conditions are divided into several small tables. Mysql5.1 begins to support data table partitioning. For example, if a user table has more than 6 million records, the table can be partitioned according to the date of entry into the database, or the table can be partitioned according to the location. Of course, partitioning can also be based on other conditions.

2. Why partition the table
In order to improve the scalability, manageability and database efficiency of large tables and tables with various access modes.

Some advantages of partitioning include:

Can store more data than a single disk or file system partition.
For data that has lost its meaning, you can usually delete the data easily by deleting the partitions related to those data. On the contrary, in some cases, the process of adding new data can be easily implemented by adding a new partition specifically for those new data. Other advantages commonly associated with partitioning include those listed below. These features in MySQL partitioning are not yet implemented, but are high on our priority list; we hope to include them in the production version of 5.1.
Some queries can be greatly optimized, mainly by means that the data that satisfies a given WHERE statement can be stored in only one or more partitions, so that there is no need to search for other remaining partitions when searching. Because partitioning can be modified after the partitioned table is created, you can reorganize the data to improve the efficiency of commonly used queries if you have not done so when you first configure the partitioning scheme.
Queries involving aggregation functions such as SUM() and COUNT() can be easily processed in parallel. A simple example of such a query is "SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;". By "parallel", this means that the query can be run on each partition simultaneously, and the final result is simply the sum of the results obtained by all partitions.
Get greater query throughput by spreading data queries across multiple disks.
3. Partition type
RANGE partitioning: Allocate multiple rows to partitions based on column values ​​belonging to a given continuous interval.

LIST partitioning: Similar to partitioning by RANGE, the difference is that LIST partitioning is selected based on the column value matching a certain value in a discrete value set.

HASH partitioning: A partition selected based on the return value of a user-defined expression that is calculated using the column values ​​of the rows to be inserted into the table. This function can contain any valid expression in MySQL that produces a non-negative integer value.

KEY partitioning: Similar to HASH partitioning, the difference is that KEY partitioning only supports calculation of one or more columns, and the MySQL server provides its own hash function. There must be one or more columns containing integer values.

RANGE Partitioning

Assigns multiple rows to partitions based on column values ​​belonging to a given contiguous range.

These intervals must be continuous and cannot overlap each other, use VALUES LESS THANoperator to define. Below are examples.

Sql code:

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)

partition BY RANGE (store_id) (
partition p0 VALUES LESS THAN (6),
partition p1 VALUES LESS THAN (11),
partition p2 VALUES LESS THAN (16),
partition p3 VALUES LESS THAN (21)
);

According to this partitioning scheme, all rows corresponding to employees working in stores 1 to 5 are saved in partition P0, and those of stores 6 to 10 Employees are saved in P1, and so on. Note that each partition is defined sequentially, from lowest to highest. This is a requirement of the PARTITION BY RANGE syntax; in this regard, it is similar to a "switch ... case" statement in C or Java. For a new row containing the data (72, 'Michael', 'Widenius', '1998-06-25', NULL, 13), it is easy to determine that it will be inserted into the p2 partition, but if a number is added What will happen to the 21st store? In this scenario, since there is no rule to include stores with a store_id greater than 20, the server will not know where to save the row, which will result in an error. This error can be avoided by using a "catchall" VALUES LESS THAN clause in the CREATE TABLE statement that provides all values ​​greater than the explicitly specified highest value:

Sql code:

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE 表示最大的可能的整数值。现在，store_id 列值大于或等于16（定义了的最高值）的所有行都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25, 30, 或更多 ，可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。在几乎一样的结构中，你还可以基于雇员的工作代码来分割表，也就是说，基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通（店内的）工人，三个数字代码表示办公室和支持人员，四个数字代码表示管理层，你可以使用下面的语句创建该分区表：

Sql代码：

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)

PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);

在这个例子中, 店内工人相关的所有行将保存在分区p0中，办公室和支持人员相关的所有行保存在分区p1中，管理层相关的所有行保存在分区p2中。在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (ad79d2732f294f8b8c01b7007dba89e0= num: ·  设置 V = CEIL(V / 2) ·  设置 N = N & (V – 1) 例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建的： CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6; 现在假设要插入两行记录到表t1中，其中一条记录col3列值为’2003-04-14′，另一条记录col3列值为’1998-10-19′。第一条记录将要保存到的分区确定如下： V = POWER(2, CEILING(LOG(2,7))) = 8 N = YEAR(’2003-04-14′) & (8 – 1)    = 2003 & 7    = 3 (3 >= 6 为假（FALSE）: 记录将被保存到#3号分区中) 第二条记录将要保存到的分区序号计算如下： V = 8 N = YEAR(’1998-10-19′) & (8-1)   = 1998 & 7   = 6 (6 >= 4 为真（TRUE）: 还需要附加的步骤) N = 6 & CEILING(5 / 2)   = 6 & 3   = 2   (2 >= 4 为假（FALSE）: 记录将被保存到#2分区中) 按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

KSY分区

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

Sql代码：

CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。

The above is the detailed content of Detailed code explanation of the four partition types of MySQL tables. For more information, please follow other related articles on the PHP Chinese website!