MySQL表分区

前言

主要参考与MySQL官方文档

https://dev.mysql.com/doc/mysql-partitioning-excerpt/8.0/en/partitioning.html

一、为什么要采用表分区

当数据量过大的时候（通常是指百万级或千万级数据的时候），这个时候需要将一张表的数据划分几张表存储，一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。

二、什么是分区

通俗地讲表分区是将一大表，根据条件分割成若干个小表

三、查看MySQL是否支持分区

1
2
3

# 通过以下命令去查看mysql是否支持分区
SHOW VARIABLES LIKE '%partition%'
# 如果输出的变量为yes表示mysql是支持分区的

四、分区的类型

Range分区

RANGE分区，基于属于一个给定连续区间的列值，把多行分配给分区。

List分区

LIST分区，类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

HASH分区

HASH分区，基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算，这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式

KEY分区

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

五、创建表分区

RANGE分区

按照range分区的表是通过如下一种方式进行分区的，每个分区包含哪些分区表达式的值位于一个给定的连续区间内的行

CREATE TABLE employee(
		id INT NOT NULL,
		fname VARCHAR(30) DEFAULT NULL,
		lname VARCHAR(30) DEFAULT NULL,
		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（定义的最大值）的行都存储在partition中p3。在将来的某个时候（当存储数量增加到25、30或更多时），您可以使用一条 ALTER TABLE语句为存储21-25、26-30等添加新的分区。

查询表分区信息

1 2	SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema='xx_admin' AND TABLE_NAME='employee'

可以看到employee表有p0~p3 4个表分区