mysql表分区

Mysql表分区

SQL标准没有提供太多有关数据存储物理方面的指导。SQL语言本身旨在独立于与其配合使用的模式，表，行或列所基于的任何数据结构或媒体。尽管如此，大多数先进的数据库管理系统已经发展了一些方法来确定将用于存储特定数据的物理位置的文件系统，硬件或什至两者。在MySQL中， InnoDB存储引擎长期以来一直支持表空间的概念，而且即使在引入分区之前，MySQL Server仍可以配置为使用不同的物理目录来存储不同的数据库（请参见参考资料）。第8.12.3节“使用符号链接”，以获取有关如何完成此操作的说明。

分区使您可以根据需要在很大程度上设置的规则在文件系统中分布各个表的各个部分，从而使这一概念更进一步。实际上，表的不同部分作为单独的表存储在不同的位置。用户选择的完成数据划分的规则称为分区功能，在MySQL中可以是模数，与一组范围或值列表的简单匹配，内部哈希函数或线性哈希函数。该函数根据用户指定的分区类型进行选择，并将用户提供的表达式的值作为其参数。此表达式可以是列值，作用于一个或多个列值的函数或一组一个或多个列值，具体取决于所使用的分区类型。

在，，和[ ] 分区的情况下RANGE，分区列的值传递给分区函数，该函数返回一个整数值，该整数值表示应在其中存储该特定记录的分区号。此函数必须是非恒定且非随机的。它可能不包含任何查询，但可以使用在MySQL中有效的SQL表达式，只要该表达式返回或一个整数即可 ， LISTLINEARHASHNULLintval

-MAXVALUE <= intval <= MAXVALUE

（MAXVALUE用于表示所讨论整数类型的最小上限。 -MAXVALUE表示最大下限。）

对于[ LINEAR] KEY， RANGE COLUMNS和LIST COLUMNS分区，分区表达式由一个或多个列的列表组成。

对于[ LINEAR] KEY 分区，分区功能由MySQL提供。

这称为水平分区 -也就是说，表的不同行可以分配给不同的物理分区。MySQL 5.6不支持 垂直分区，在垂直分区中，表的不同列被分配给不同的物理分区。目前尚无计划将垂直分区引入MySQL。

为了创建分区表，您可以使用MySQL服务器支持的大多数存储引擎。MySQL分区引擎在单独的层中运行，并且可以与其中任何一个进行交互。在MySQL 5.6中，同一分区表的所有分区必须使用相同的存储引擎。例如，您不能将MyISAM一个分区InnoDB用于另一个分区。但是，没有什么可以阻止您对同一MySQL服务器甚至同一数据库中的不同分区表使用不同的存储引擎。

MySQL分区不能与使用 MERGE，CSV或 FEDERATED存储引擎。

使用KEY或LINEAR KEY可以进行分区NDB，但是使用此存储引擎的表不支持其他类型的用户定义分区。此外，NDB使用用户定义的分区的 表必须具有显式主键，并且该表的分区表达式中引用的任何列都必须是主键的一部分。但是，如果在用于创建或修改用户分区表的or 语句的PARTITION BY KEYor PARTITION BY LINEAR KEY子句中未列出任何列 ，则该表不需要具有显式主键。有关更多信息，请参见 第18.1.7.1节“ NDB群集中与SQL语法不兼容”。 CREATE TABLEALTER TABLENDB

要将特定的存储引擎用于分区表，仅需要像使用未分区表一样使用该[STORAGE] ENGINE 选项。但是，请记住， 在语句中使用任何分区选项之前，都[STORAGE] ENGINE需要列出（和其他表选项）。此示例显示如何创建一个按表划分为6个分区并使用存储引擎的表： CREATE TABLEInnoDB

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=INNODB
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;

每个PARTITION子句都可以包含一个 [STORAGE] ENGINE选项，但是在MySQL 5.6中这无效。

重要
分区适用于表的所有数据和索引；您不能仅对数据而不对索引进行分区，反之亦然，也不能仅对表的一部分进行分区。

可以使用用于创建分区表的语句子句 的DATA DIRECTORY和 INDEX DIRECTORY选项，将 每个分区的数据和索引分配给特定目录。 PARTITIONCREATE TABLE

DATA DIRECTORY并且Windows INDEX DIRECTORY上MyISAM表的单个分区或子分区不支持。

表的DATA DIRECTORY单个分区和子分区 仅支持该选项 InnoDB。

该表的分区表达式中使用的所有列都必须是该表可能具有的每个唯一键（包括任何主键）的一部分。这意味着不能对由以下SQL语句创建的此类表进行分区：

CREATE TABLE tnp (
id INT NOT NULL AUTO_INCREMENT,
ref BIGINT NOT NULL,
name VARCHAR(255),
PRIMARY KEY pk (id),
UNIQUE KEY uk (name) );

由于键pk和uk 没有共同的列，因此分区表达式中没有可用的列。在这种情况下，可能的解决方法包括将name列添加到表的主键，将id列添加到uk或仅完全删除唯一键。

另外，MAX_ROWS和 MIN_ROWS可被用于确定行，分别的最大和最小数字，可以被存储在每个分区中。该MAX_ROWS选项对于使NDB群集表具有额外的分区来创建很有用，从而可以更好地存储哈希索引。

这里列出了分区的一些优点：

• 分区使在一个表中存储的数据比单个磁盘或文件系统分区中存储的数据更多。

• 通过删除仅包含该数据的一个或多个分区，通常可以轻松地从分区表中删除失去其用途的数据。相反，在某些情况下，通过添加一个或多个用于专门存储该数据的新分区，可以大大简化添加新数据的过程。

• 满足以下条件的某些查询可以大大优化：满足给定WHERE子句的数据只能存储在一个或多个分区上，这会自动从搜索中排除任何剩余的分区。由于可以在创建分区表之后更改分区，因此您可以重新组织数据以增强在首次设置分区方案时可能不会经常使用的频繁查询。排除不匹配分区（以及因此包含的任何行）的能力通常称为
  分区修剪。

  另外，MySQL 5.6支持显式的分区选择查询。例如，

SELECT * FROM t PARTITION (p0,p1) WHERE c < 5

仅选择那些在分区行p0和 p1其匹配的WHERE
条件。在这种情况下，MySQL不检查table的任何其他分区t；当您已经知道要检查的分区时，这可以大大加快查询速度。选择分区还支持数据修改语句
DELETE， INSERT， REPLACE， UPDATE，和 LOAD DATA， LOAD
XML。有关更多信息和示例，请参见这些语句的描述。

转载自：https://dev.mysql.com/doc/refman/5.6/en/partitioning-overview.html

Author:leedaning
本文地址：https://blog.csdn.net/leedaning/article/details/105584594