What is MySQL table partitioning?

MySQL表分区

问题

I came across a project today that I don't understandSQLException,刚开始很懵逼！！I don't know if there are any friends who lost their jaws like me.？上BUG

### Error updating database.  Cause: java.sql.SQLException: Table has no partition for value 738735
### Cause: java.sql.SQLException: Table has no partition for value 738735
; uncategorized SQLException; SQL state [HY000]; error code [1526]; Table has no partition for value 738735; nested exception is java.sql.SQLException: Table has no partition for value 738735

未分类的SQLException,What the devil is not classified？The project has never reported this error before,Why do I get this error all of a sudden？Still an incomprehensible mistake,Follow the programmer's problem-solving conventions,Copy the question toCSDN搜一下！I saw some bloggers give the following solutions：

alter table tableName add partition(partition p202112 VALUES LESS THAN (738521) ENGINE = InnoDB);

看了一下,说是MySQLThe middle table can be partitioned to store data,But this blogger is a bit rude,给了解决方案,But this solution is only relative to his case,and did not explain thisSQL的含义,Caused the circle in place for a long time,So just go and learnMySQL的表分区.

前言

Have you ever heard of sub-libraries and sub-tables?,Never heard of table partitioning,Table partitioning is the basis of the same table of data according to specified is divided into several parts,What is the benefit of this division?？I didn't understand it at first,All in one table,Also divided so what is the point？Think back to the actual problem,The table that partitions the table in the project is an operation log table,Record user's records of system business operations,Logging operation every day the quantity is very big still,If the partition can better management？那有人可能会说,The amount is large, why not divide the table horizontally？Level score is not impossible,If we know the operation log, we just need to keep it for a while,rather than permanent,And so much,If according to the time as the basis for the sub-table,That said, there must be a few tables.,而且相对于MySQLThe amount of storage in a single table is500万条记录,I don't think there's been that much in months.,So there is no need to go to the level sub-table,So can solve the problem by means of table partitioning,So the two solutions are no problem,According to actual condition to select a specific choice.

什么是表分区？

Table partitioning is based on certain rules,Save data in different regions in the same table,It's a bit like a horizontal split,Data exists in different tables in the same library,It's just that the granularity of table partitioning is smaller than that of horizontal partitioning.,Table partitioning can backup and restore data for a single partition.better data management,Generally, a single piece of data is managed,Or manage a single table,Can now be managed on a single partition.It can also improve query efficiency,You can locate the partition where the data is located according to the query conditions,which can then be searched on the partition.Partitions can also store data on different physical devices.

分区模式

RANGE

rang分区,见名知意,就是范围,Partitions can be selected based on a range of values,And this field can be a primary key or joint of the primary key field,For example, there is now a table,主键由id和create_atPrimary key consisting of two fields,Then the basis field for selecting partitions can be one of these two,It should be noted that the value of this field must be an integer,如下是通过TO_DAYS()函数对create_atfield to take days,所以符合要求.The second is that the partition is from small to bottom,好比使用if…elsestruct to select branch on a range,The range from top to bottom is from small to large,If there is no suitable partition, an error will be reported.

CREATE TABLE USER(
	ID INT PRIMARY KEY,
    CREATE_AT DATETIME COMMENT '创建时间'
)ENGINE = InnoDB PARTITION BY RANGE (TO_DAYS(create_at))
PARTITIONS 2
(PARTITION `p20210601` VALUES LESS THAN (738307) ,
PARTITION `p20210701` VALUES LESS THAN (738337));

LIST

LIST分区,见名知意,is based on whether there is a fieldLIST中,好比MySQL的IN,If there is now a user table,According to user gender fieldsex进行分区.

CREATE TABLE USER(
	ID INT PRIMARY KEY,
    SEX TINYINT(1) COMMENT '性别'
)ENGINE = InnoDB PARTITION BY LIST (sex)
PARTITIONS 2
(PARTITION `p20210601` VALUES IN (0) ,
PARTITION `p20210701` VALUES IN (1));

single-valued case,You can also fill in multiple values ​​according to actual needs.

HASH

HASH分区,见名知意,is done through the partition fieldHASH计算,Then drop the data into the corresponding partition,假设根据IDfield to proceedHASH计算,Then drop the data into the calculated partitions.

CREATE TABLE USER(
	ID INT PRIMARY KEY
)ENGINE = InnoDB PARTITION BY HASH (ID)
PARTITIONS 2;

KEY

KEY分区和HASHPartitions are somewhat similar,但是KEY支持的数据类型比HASH多,支持除text、blobAll data types other than.KEY分区不允许使用用户自定义的表达式进行分区.KEY分区使用系统提供的HASH函数进行分区.

当表中存在主键或者唯一索引时,如果创建 KEY 分区时没有指定字段系统默认会首选主键列作为分区字段,如果不存在主键列会选择非空唯一索引列作为分区字段.

COLUMNS

COLUMNS分区是5.5开始引入的分区功能,COLUMNS分区包含RANGE COLUMN和LIST COLUMN分区,These two partitions support shaping、日期、字符串;

RANGE COLUMN类似于RANGEIt's just that it supports more data types,and omits some function conversions,就好比RANGEWhen partitioning date type fields, you need to use functions to get integers,而RANGE COLUMN不需要了,可以直接写yyyy-MM-dd格式的日期.

LIST COLUMN分区类似于LIST,But it supports more data types thanLISTPartition more.

总结

通过学习了解MySQL的表分区,In the future development, there can be more solutions when the requirements are encountered.相信大家看完MySQLAfter the partition should be surprised as I do,There's so much we don't knowMySQL的隐藏功能！