• MySQL分区 是一种数据库优化的技术，它允许将一个大的表、索引或其子集分割成多个较小的、更易于管理的片段，这些片段称为“分区”。
  • 每个分区都可以独立于其他分区进行存储、备份、索引和其他操作。这种技术主要是为了改善大型数据库表的查询性能、维护的方便性以及数据管理效率。

基本概念

物理存储与逻辑分割

  1. 物理上，每个分区可以存储在不同的文件或目录中，这取决于分区类型和配置。
  2. 逻辑上，表数据根据分区键的值被分割到不同的分区里。

查询性能提升

  1. 当执行查询时，MySQL能够确定哪些分区包含相关数据，并只在这些分区上进行搜索。这减少了需要搜索的数据量，从而提高了查询性能。
  2. 对于范围查询或特定值的查询，分区可以显著减少扫描的数据量。

数据管理与维护

  1. 分区可以使得数据管理更加灵活。例如，可以独立地备份、恢复或优化某个分区，而无需对整个表进行操作。
  2. 对于具有时效性的数据，可以通过删除或归档某个分区来快速释放存储空间。

扩展性与并行处理

  1. 分区技术使得数据库表更容易扩展到更大的数据集。当表的大小超过单个存储设备的容量时，可以使用分区将数据分布到多个存储设备上。
  2. 由于每个分区可以独立处理，因此可以并行执行查询和其他数据库操作，从而进一步提高性能。

分区的原理和类型

分区的原理

  1. 分区技术是将表中的记录分散到不同的物理文件中，即每个分区对应一个.idb文件。这是MySQL 5.1及以后版本支持的一项高级功能，旨在提高大数据表的管理效率和查询性能。
  2. 分区类型：MySQL支持水平分区，即根据某些条件将表中的行分配到不同的分区中。这些分区在物理上是独立的，可以单独处理，也可以作为整体处理。
  3. 性能和影响：虽然分区可以提高查询性能和管理效率，但如果不恰当使用，也可能对性能产生负面影响。因此，在使用分区时应谨慎评估其影响。
  4. 索引与分区：在MySQL中，分区是局部的，意味着数据和索引都存储在各自的分区内。目前，MySQL尚不支持全局分区索引。
  5. 分区键与唯一索引：当表存在主键或唯一索引时，分区列必须是这些索引的一部分。这是为了确保分区的唯一性和查询效率。

分区类型

  1. MySQL支持几种不同类型的分区方式，包括RANGE、LIST、HASH和KEY。下面简要介绍这些分区方式的工作原理：
    • RANGE分区：基于列的值范围将数据分配到不同的分区。例如，可以根据日期范围将数据分配到不同的月份或年份的分区中。
    • LIST分区：类似于RANGE分区，但LIST分区是基于列的离散值集合来分配数据的。可以指定一个枚举列表来定义每个分区的值。
    • HASH分区：基于用户定义的表达式的哈希值来分配数据到不同的分区。这种分区方式适用于确保数据在各个分区之间均匀分布。
    • KEY分区：类似于HASH分区，但KEY分区支持计算一列或多列的哈希值来分配数据。它支持多列作为分区键，并且提供了更好的数据分布和查询性能。

使用场景

  1. 性能提升：通过将数据分散到多个分区中，可以并行处理查询，从而提高查询性能。同时，对于涉及大量数据的维护操作（如备份和恢复），可以单独处理每个分区，减少了操作的复杂性和时间成本。
  2. 管理简化：分区可以使得数据管理更加灵活。例如，可以独立地备份、恢复或优化某个分区，而无需对整个表进行操作。这对于大型数据库表来说尤为重要，因为它可以显著减少维护时间和资源消耗。
  3. 数据归档和清理：对于具有时间属性的数据（如日志、交易记录等），可以使用分区来轻松归档旧数据或删除不再需要的数据。通过简单地删除或归档某个分区，可以快速释放存储空间并提高性能。
  4. 可扩展性：分区技术使得数据库表更容易扩展到更大的数据集。当表的大小超过单个存储设备的容量时，可以使用分区将数据分布到多个存储设备上，从而实现水平扩展。

确认MySQL支持分区

  1. 从MySQL5.1开始引入分区功能，可以如下方式查看是否支持：
    1. “老"版本方式：SHOW VARIABLES LIKE ‘%partition%’。
    2. “新"版本方式：show plugins。
# 信息中查看到这一条数据就表示支持分区
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

注意事项

  1. 如果表中存在primary key或者unique key时，分区的列必须是primary key或者unique key的一个组成部分，也就是说，分区函数的列只能从primary key或者unique key这些key中取子集。
  2. 如果表中不存在任何的primary key或者unique key时，则可以指定任何一个列作为分区列。
  3. MySQL5.5版本之前的Range、List、Hash分区要求分区键必须是int；MySQL5.5及以上，支持非整型的Range和List分区，即：range columns 和 list columns。

分区命名

  1. ​分区的名字基本遵循其他MySQL标识符应当遵循的原则，例如用于表和数据库名字的标识符，但是注意，分区的名字是不区分大小写的。
  2. 无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录。

创建分区

RANGE分区

--创建数据库
CREATE DATABASE kubiao DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
--创建分区表，主键：int类型
CREATE TABLE tbl_users (
    `uuid` INT NOT NULL,
    `customerId` VARCHAR(200),
    `pwd` VARCHAR(20),
    `showName` VARCHAR(100),
    `trueName` VARCHAR(100),
    `registerTime` VARCHAR(100)
)
PARTITION BY RANGE (uuid) (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (15),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
--插入数据
INSERT INTO tbl_users VALUES(1,"只为你","123456","qzp","quezhipeng","20200808"),(6,"人生","123456","人生","无名","20200808"),(12,"无须终有","123456","无须终有","无声","20200808"),(100,"坚持","123456","胜利","坚持","20200808");
  1. 通过命令查看分区。
mysql> SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema='kubiao' AND table_name='tbl_users';
  1. 查看分区上的数据。
SELECT * FROM tbl_users PARTITION(p0);
  1. 查看MySQL操作的分区。
EXPLAIN PARTITIONS SELECT * FROM tbl_users WHERE uuid = 2;

List分区

  1. 注意：如果试图操作的列值不在分区值列表中时，那么会失败并报错，要注意的是，LIST分区没有类似如：“VALUES LESS THAN”这样的包含其他值在内的定义，将要匹配的任何值都必须在值列表中找到。
  2. LIST分区除了能和RANGE分区结合起来生成一个复合的子分区，与Hash和KEY分区结合起来成复合的子分区也是可以的。
CREATE TABLE tbl_users2 (
    `uuid` INT NOT NULL,
    `customerId` VARCHAR(200),
    `pwd` VARCHAR(20),
    `showName` VARCHAR(100),
    `trueName` VARCHAR(100),
    `registerTime` VARCHAR(100)
)
PARTITION BY List(uuid) (
    PARTITION p0 VALUES in(1,2,3,5),
    PARTITION p1 VALUES in(7,9,10),
    PARTITION p2 VALUES in(11,15)
);
--插入数据，注意：加入数据需要根据主键来，不可以时主键分区中没有的，否则报错
INSERT INTO tbl_users2 VALUES(1,"只为你","123456","qzp","quezhipeng","20200808"),(7,"人生","123456","人生","无名","20200808"),(10,"无须终有","123456","无须终有","无声","20200808"),(15,"坚持","123456","胜利","坚持","20200808");
  1. 查看数据，与 RANGE 分区一样。

HASH分区

  1. HASH分区：主要用来确保数据在预先确定数目的分区中平均分布，在RANGE和LIST分区中，必须明确指定一个给定的列值或者列值集合以指定应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，要做的只是基于将要被哈希的列值指定的一个表达式，以及指定被分区的表将要被分割成的分区数量，如：
--创建一张hash分区表
CREATE TABLE tbl_users4 (
	`uuid` INT NOT NULL,
	`customerId` VARCHAR(200),
	`pwd` VARCHAR(20),
	`showName` VARCHAR(100),
	`trueName` VARCHAR(100),
	`registerTime` VARCHAR(100)
)
PARTITION BY hash(uuid)
	PARTITIONS 3;
--插入数据，注意：分区是有uuid/3求余数决定,余数为0，在p0;余数为1，在p1;余数为2，在p2,以此类推....
INSERT INTO tbl_users2 VALUES(1,"只为你","123456","qzp","quezhipeng","20200808"),(7,"人生","123456","人生","无名","20200808"),(10,"无须终有","123456","无须终有","无声","20200808"),(15,"坚持","123456","胜利","坚持","20200808");

KEY分区

  1. 类似于按照HASH分区，HASH分区允许用户自定义表达式，而KEY分区不允许使用用户自定义的表达式：HASH分区只支持整数分区，KEY分区支持除了blob或者text类型之外的其他数据类型分区。
  2. 与HASH分区不同，创建KEY分区表的时候，可以不指定分区键，默认会选择使用主键或者唯一键作为分区键，没有主键或唯一键，就必须指定分区键。
--创建一张key分区表
CREATE TABLE tbl_users5 (
	`uuid` INT NOT NULL,
	`customerId` VARCHAR(200),
	`pwd` VARCHAR(20),
	`showName` VARCHAR(100),
	`trueName` VARCHAR(100),
	`registerTime` VARCHAR(100)
)
PARTITION BY LINEAR key(uuid)
	PARTITIONS 3;
--插入数据，注意：分区是有uuid/3求余数决定,余数为0，在p0;余数为1，在p1;余数为2，在p2,以此类推....
INSERT INTO tbl_users5 VALUES(1,"只为你","123456","qzp","quezhipeng","20200808"),(7,"人生","123456","人生","无名","20200808"),(10,"无须终有","123456","无须终有","无声","20200808"),(15,"坚持","123456","胜利","坚持","20200808");

参考

  1. MySQL分区知识及操作详解(完整版)
  2. MySQL分区表：万字详解与实践指南