菜鸟追梦旅行

这个屌丝很懒，什么也没留下！

热门标签

Mysql数据库的分区_mysql有数据库分去吗

作者：菜鸟追梦旅行 | 2024-05-27 05:28:43

踩

mysql有数据库分去吗

Mysql分区

Mysql从5.1版本开始支持分区的功能，分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分，就访问数据库而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数个物理分区对象组成，每个分区都是一个独立的对象，可以作为表的一部分独立的处理。分区对应用来说完全透明的，不影响应用业务逻辑。

Mysql分区的好处：

和单个磁盘或者文件系统相比，分区可以存储更多数据
优化查询，在where子句中包含分区条件，可以只扫描必要的一个或者多个分区而非全表扫描，同时在涉及sum()和count()这类聚合函数查询时候，也可以并行在多个分区处理然后汇总，提高效率。
删除数据可以通过删除与这些数据有关的分区来快速删除
跨多个磁盘来分散数据查询，提高查询吞吐量

分区概述

在MySQL中，InnoDB存储引擎长期支持表空间的概念，并且MySQL服务器甚至在分区引入之前，就能配置为存储不同的数据库使用不同的物理路径，分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数。

分区有利于管理非常大的表，分区通过引入分区键（paritition key）的概念，分区键用于根据某个区间值（范围值）、特定值或者HASH函数值执行数据的聚集，让数据根据规则分布在不同分区中，让一个大对象变成一些小对象。

通过使用show variables命令（MYSQL5.6以下版本）来确定当前的MYSQL是否支持分区，如果看到have_paritition_engine的值为YES，则表示MYSQL的版本不支持分区，5.6以上的版本使用show plugins;命令查看，如下即代表支持分区：

MYSQL支持大部分存储引擎（MyISAM、InnoDB、Memory等存储引擎）创建分区表，不支持MERGE或CSV存储引擎创建分区表，在MYSQL5.1版本中，同一个分区表的所有分区必须使用同一个存储引擎，同一库不同分区表可以使用不同存储引擎。

分区类型

MySQL 5.1中可用的分区类型。这些类型包括：

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

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

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

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

无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的哈希函数自动进行处理。分区的名字是不区分大小写的。例如，下面的CREATE TABLE语句将会产生如下的错误：这是因为MySQL认为分区名字mypart和MyPart没有区别。


CREATE TABLE t2 (val INT)
    PARTITION BY LIST(val)(
    PARTITION mypart VALUES IN (1,3,5),
    PARTITION MyPart VALUES IN (2,4,6)
    );

在MYSQL5.1版本中，RANGE分区、HASH分区、LIST分区都要求分区键必须是INT类型，或者通过表达式返回INT类型，即是MYSQL5.1仅仅支持整数分区，唯一的例外就是分区类型是KEY时，可以使用其他类型列作为分区键（BLOB和TEXT列类型除外），但是在MYSQL5.5版本中，RANGE和LIST分区支持非整型了。

除此之外，无论是那种MYSQL分区类型，要么分区表上没有主键或唯一键，要么分区表的主键和唯一键都必须包含分区键，即不能使用主键或唯一键字段之外的其他字段分区。

RANGE分区

利用RANGE分区的表是利用取值范围将数据分成分区，区间要连续并且不能互相重叠，使用VALUE LESS THAN 操作符进行分区定义。如下员工表按照STORE_ID进行分区：


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)
)；

这种分区方案：商店编号1-5的employee保存在分区p0，商店编号6-10的employee保存在分区p1，由此类推，每个分区都是按照顺序定义，这是PRATITION BY RANGE语法要求，类似JAVA的switch case语句，注意如果此时增加的employee的STORE_ID大于21，则会出现错误，可以通过设置分区的时候使用VALUE LESS THAN MAXVALUE子句。（MAXVALUE 表示最大的可能的整数值）

MYSQL支持在设置分区使用VALUES LESS THAN 子句中使用一个表达式。比如使用日期作为RANGE分区的分区列，

雇员表的store_id列已经被定义为非空（NOT NULL），当然除了使用STORE_ID来分割数据之外，还可以使用基于两个DATE （日期）中的一个的表达式来分割表数据。例如想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示：


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,
    store_id INT
)
PARTITION BY RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2014),
    PARTITION p2 VALUES LESS THAN (2018),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

在RANGE分区中，分区键如果是NULL值会被当作是最小值来处理

MYSQL5.1支持整数列分区，所以使用日期或者字符串的列进行分区的时候必须使用函数转换，查询的时候也需要函数转换后才能利用RANGE分区的特性来提高查询效率。MYSQL5.1分区的日期处理仅支持YEAR()和TO_DAYS()，

MYSQL5.5改进了RANGE分区功能，提供了RANGE COLUMNS分区支持非整数分区，这时候使用日期分区就不需要通过函数转换了。MYSQL5.5分区日期处理上增加了TO_SECONDS()把日期编程秒，从而实现比天分区更细化的分区。


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,
    store_id INT
)
PARTITION BY RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN ('2010-01-01'),
    PARTITION p1 VALUES LESS THAN ('2014-01-01'),
    PARTITION p2 VALUES LESS THAN ('2018-01-01')
)；

RANGE分区在如下场合特别有用：

当需要删除过期数据时。只需简单地使用 ALTER TABLE employees DROP PARTITION p0来删除所有p0分区的数据。对于有大量行的表，这比运行一个如DELETE FROM employees WHERE YEAR(separated) <= 2019这样的一个DELETE查询要快的多。
经常运行分区键的查询。如SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2012 GROUP BY store_id这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

LIST分区

LIST分区是建立离散的值列表，高速数据库特定的值属于哪个分区，LIST分区在很多方面类似RANGE，区别在LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用PARTITION BY LIST(expr)来实现，其中expr是某列值或一个基于某个列值返回一个整数值的表达式，然后通过VALUES IN (value_list)的方式来定义每个分区，其中value_list是一个通过逗号分隔的整数列表。与RANGE不同的是，LIST分区不必申明任何特定的顺序，如下：


CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(30),
    category INT
)
PARTITION BY LIST(category)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

在MYSQL5.1中，LIST分区只能匹配整数列表，CATEGORY只能是INT类型，所以需要额外的转换表来记录CATEGORY编号和名称，当然如果插入的列值或者分区表达式的返回值不包含在分区值列表中，则插入失败，LIST分区没有类似RANGE中的MAXVALUE的方式，插入的值在LIST列表中一定要存在。

在MYSQL5.5中，LIST分区支持非整数列表，使用COLUMNS分区，如下：


CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(30),
    category VARCHAR(30)
)
PARTITION BY LIST COLUMNS(category)
    PARTITION pNorth VALUES IN ('beijing','tianjin'),
    PARTITION pEast VALUES IN ('xian','xinjiang'),
    PARTITION pWest VALUES IN ('shanghai','suzhou','hefei','hangzhou'),
    PARTITION pCentral VALUES IN ('wuhan','zhenzhou')
);

COLUMNS分区

COLUMNS分区实在MYSQL5.5中引入的分区类型，引入COLUMNS分区的目的是为了解决MYSQL5.5之前版本的RANGE分区和LIST分区只支持整数分区，需要通过额外函数计算得到整数或者额外的转换表转换成整数在分区，COLUMNS分区可以细分为RANGE COLUMNS分区和LIST COLUMNS分区，它们都支持整数、日期、字符串三大数据类型：

所有整数类型：tinyint、smallint、mediumint、int、bigint，其他数值类型都不支持，如Decimal和Float

日期时间类型：date、datetime

字符类型：char、varchar、binary、varbinary，不支持text和blob类型作为分区键

注意：COLUMNS分区仅支持一个或者多个字段名作为分区键，不支持表达式作为分区键。（和RANGE分区和LIST分区不同），如下使用多列组合的RANGE COLUMNS分区：


CREATE TABLE employees (
    a INT ,
    b INT 
)
PARTITION BY LIST COLUMNS(a,b)(
    PARTITION p01 VALUES LESS THAN (0,10),
    PARTITION p02 VALUES LESS THAN (10,10),
    PARTITION p03 VALUES LESS THAN (10,20),
    PARTITION p04 VALUES LESS THAN (10,35),
    PARTITION p05 VALUES LESS THAN (10,MAXVALUE),
    PARTITION p06 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

需要注意的是，RANGE COLUMNS 分区键的比较是基于元组类型的比较，如下：

当插入a=1，b=10，数据会被写入p02分区，即元组(1,10)<(10,10)

当插入a=10，b=9，数据会被写入p02分区，即元组(10,9)<(10,10)

当插入a=10，b=10，数据会被写入p03分区，即元组(10,10)<=(10,10)<(10,20)

可以通过在数据库中直接查询来判断元组数据的大小：

select (1,10) < (10,10)

其实RANGE COLUMNS分区键的比较（元组的比较）其实就是多列排序，先根据a字段排序再根据b字段排序，然后根据排序结果来分区存放数据，和RANGE单字段分区排序的规则其实是一致的。

HASH分区

HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布，对一个表执行HASH分区时，MYSQL会对分区键应用一个散列函数，以此确定数据应当放在N个分区的那个分区中。MYSQL支持两种HASH分区：

常规HASH分区
线程HASH分区（LINEAR HASH分区），

常规HASH分区使用取模算法，线性HASH分区使用一个线性的2的幂的运算法则；

使用PARTITION BY HASH (expr) PARTITIONS num子句来创建一个HASH分区表empolyees，其中expr是某整数列值或者一个基于某列值返回一个整数的表达式。num 是一个非负的整数，它表示表分区的数量。默认值是1（不写PARTITIONS num时）


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,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

上面的SQL是基于store_id列的HASH分区表，表被分成4个分区，这里的expr就是store_id，当新增一条记录的时候会根据算法：MOD(expr,num)，即如果插入一个store_id=234的记录，则他会保存在N=MOD(234,4)=2分区中，即第二个分区。可以通过执行计划查询这条记录被保存在那个分区：

explain partition select * from employees where store_id=234

当然expr也可以是MYSQL中任意有效的函数或者表达式，只要返回一个返回一个既非常数、也非随机数的整数（既是变化的但又是确定的），需要注意的是，每当插入或更新（或者可能删除）一行，这个表达式都要计算一次，这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。同时MYSQL也不推荐使用涉及多列的哈希表达式。最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致地增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。换句话说，如果列值与表达式值之比的曲线图越接近由等式y=nx（其中n为非零的常数）描绘出的直线，则该表达式越适合于哈希。这是因为，表达式的非线性越严重，分区中数据产生非均衡分布的趋势也将越严重。

常规HASH分区的弊端：

常规HASH分区是通过取模的方式尽可能的平均分布数据到每个分区的，但是每当需要增加分区或者合并分区的时候，就会出现问题：在原来5个常规分区中，取模算法为N=MOD(expr,5)，根据余数0-4判断数据保存在5个分区中的哪一个，但是当新增一个分区，此时的算法就会变成N=MOD(expr,6)，根据余数0-5判断数据保存在6个分区中的哪一个，原来5个分区中的数据大部分需要重新计算重新分区，这样常规HASH分区在分区管理上的代价太大了，不适合灵活变动，为了降低分区管理的代价，MYSQL提供了线性HASH分区，分区函数是一个线性的2的幂的运算法则。

线性HASH分区

线性哈希分区和常规哈希分区在语法上的唯一区别在于，在PARTITION BY 子句中添加LINEAR关键字，如下：


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,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4

同样使用线性HASH时，计算分区时：假设分区编号为N，num表示非负的分区数量，N的计算步骤如下：

1. 找到下一个大于num的2的幂，我们把这个值称为V ，V可以通过下面的公式得到：

```
 V = POWER(2, CEILING(LOG(2, num)))
```

（假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)=4，则V = POWER(2,4)=16）

2. 设置 N = F(column_list) & (V - 1)，此时对于hired='2009-01-01'这个记录，N=2009 & (4-1) = 2

3. 当 N >= num:

设置 V = CEIL(V / 2)
设置 N = N & (V - 1)

此时hired='2009-01-01'这个记录的N=2 < 4，可以直接判断保存在第二个分区。

线性HASH分区的优点：在分区维护（包括增加、删除、合并、拆分分区）时，MYSQL能够处理的更加迅速，缺点是对于常规HASH分区的取模算法，线性HASH各个分区之间的数据分布不太均衡。

KEY分区

按照KEY进行分区类似于HASH分区，只不过HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MYSQL 服务器提供（不支持用户自定义表达式）。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。同时HASH分区只支持整数分区，而KEY分区支持使用除了BLOB和TEXT类型之外的其他类型的列作为分区键。

通过PARTITION BY KEY(expr)的子句创建一个KEY分区表，expr是零个或者多个字段名的列表，如下：


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

与HASH分区不同，创建KEY分区表时，可以不指定分区键，默认会首先选择使用主键作为分区键，如下：


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

当然没有主键的情况下，会选择非空唯一键作为分区键。如果既没有主键也没有非空唯一键且没有指定分区键则会报错。

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，也就是LINEAR KEY分区时，分区的编号是通过2的幂算法得到，而不是通过模数算法。KEY分区和HASH分区类似，处理大量记录时，能够有效地分散热点。

子分区

子分区是分区表中队每个分区进行再次分割，又称为复合分区，MYSQL从MYSQL5.1开始支持对通过RANGE或者LIST分区的表再进行子分区，子分区既可以使用HASH分区又可以使用KEY分区，如下：


CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    SUBPARTITIONS 2
    (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    )；

ts表有3个分区，每个分区p0，p1，p2又被进一步分成2个子分区，实际上整个表被分成6个分区了，由于PARTITION BY RANGE子句的作用，第一和第二个分区只保存purchased列中值小于1990的记录，复合分区适用于保存非常大量的数据记录。当然也可以使用SUBPARTITION 子句来明确定义子分区也是可以的，如下：


CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    (
        PARTITION p0 VALUES LESS THAN (1990)
        (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    )

需要注意的是：

每个分区必须有相同数量的子分区。
如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。
在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一

子分区可以用于特别大的表，在多个磁盘间分配数据和索引。假设有6个磁盘，分别为/disk0， /disk1， /disk2等。现在考虑下面的例子：


CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    (
        PARTITION p0 VALUES LESS THAN (1990)
        (
            SUBPARTITION s0a 
                DATA DIRECTORY = '/disk0' 
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b 
                DATA DIRECTORY = '/disk2' 
                INDEX DIRECTORY = '/disk3'
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s1a 
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b 
                DATA DIRECTORY = '/disk5/data' 
                INDEX DIRECTORY = '/disk5/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s2a,
            SUBPARTITION s2b
        )
    )

购买日期在1990年前的记录占了大量的存储空间，所以把它分为了四个部分进行存储，组成p0分区的两个子分区（s0a 和s0b）的数据和索引都分别用一个单独的磁盘进行存储。换句话说：

o 子分区s0a 的数据保存在磁盘/disk0中。

o 子分区s0a 的索引保存在磁盘/disk1中。

o 子分区s0b 的数据保存在磁盘/disk2中。

o 子分区s0b 的索引保存在磁盘/disk3中。

· 保存购买日期从1990年到1999年间的记录（分区p1）不需要保存购买日期在1990年之前的记录那么大的存储空间。这些记录分在2个磁盘（/disk4和/disk5）上保存，而不是4个磁盘：

o 属于分区p1的第一个子分区（s1a）的数据和索引保存在磁盘/disk4上 — 其中数据保存在路径/disk4/data下，索引保存在/disk4/idx下。

o 属于分区p1的第二个子分区（s1b）的数据和索引保存在磁盘/disk5上 — 其中数据保存在路径/disk5/data下，索引保存在/disk5/idx下。

· 保存购买日期从2000年到现在的记录（分区p2）不需要前面两个RANGE分区那么大的空间。当前，在默认的位置能够足够保存所有这些记录。

MYSQL分区处理NULL值

MYSQL不禁止分区间上使用NULL，分区键可能是一个字段或者一个用户定义的表达式，一般情况下MYSQL的分区都会把NULL当做零值，或者当最小值进行处理。在RANGE分区中NULL值会被当做最小值，LIST分区中NULL值必须出现在枚举中，否则不会接受，而HASH/KEY分区中，NULL会被当做零值处理。当然还是推荐在设置字段的时候设置非空约束或者默认值来避开MYSQL对NULL值的处理。

分区管理

RANGE和LIST分区

RANGE和LIST分区的在添加、删除和重新定义分区的处理上非常相似，具体如下：

删除分区：ALTER TABLE DROP PARTTION partition_name；


CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
     PARTITION BY RANGE(YEAR(purchased))
        (
                PARTITION p0 VALUES LESS THAN (1990),
                PARTITION p1 VALUES LESS THAN (1995),
                PARTITION p2 VALUES LESS THAN (2000),
                PARTITION p3 VALUES LESS THAN (2005)
        );
Query OK, 0 rows affected (0.01 sec)
 
INSERT INTO tr VALUES
    (1, 'desk organiser', '2003-10-15'),
    (2, 'CD player', '1993-11-05'),
    (3, 'TV set', '1996-03-10'),
    (4, 'bookcase', '1982-01-10'),
    (5, 'exercise bike', '2004-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'popcorn maker', '2001-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '1984-09-16'),
    (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)                  
 
/*
可以通过使用下面的命令查看那些记录已经插入到了分区p2中：*/
mysql> SELECT * FROM tr
    -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+
| id   | name      | purchased  |
+------+-----------+------------+
|    3 | TV set    | 1996-03-10 |
|   10 | lava lamp | 1998-12-25 |
+------+-----------+------------+
2 rows in set (0.00 sec)

删除p2这个分区：当删除了一个分区，也同时删除了该分区中所有的数据。


ALTER TABLE tr DROP PARTITION p2;
Query OK, 0 rows affected (0.03 sec)

可以通过重新运行前面的SELECT查询来验证：可以看出删除一个分区时会连同分区内的数据一起删除。


SELECT * FROM tr WHERE purchased 
    BETWEEN '1995-01-01' AND '1999-12-31';
Empty set (0.00 sec)

此时如果新增一条记录purchased在1995-01-01 到1999-12-31之间的数据，这个数据会被放在p3分区，此时的p3分区的范围是1995-01-01 到2004-12-31；通过命令查看p3分区的记录增加了一个。


SELECT 
    partition_name part, 
    parttion_expression expr, 
    partition_description descr, 
    table_rows 
FROM information_schema.PARTITIONS 
WHERE
TABLE_SCHEMA =schema() 
AND TABLE_NAME ='tr'

通过执行一个SHOW CREATE TABLE命令，可以观察到表的分区结构是如何被改变的：


SHOW CREATE TABLE tr
 
Create Table: CREATE TABLE `tr` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL,
  `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE (YEAR(purchased)) (
  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, 
 
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.01 sec)

删除LIST分区和RANGE分区语法完全一样，需要注意的是删除LIST分区会导致LIST分区不在包含被删除的分区的值列表，所以后续无法写入包含删除分区值列表的数据。而RANGE分区可能会由下一个分区兜底。

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用TRUNCATE TABLE命令:

如果希望改变表的分区而又不丢失数据，使用ALTER TABLE ... REORGANIZE PARTITION语句:

增加分区：ALTER TABLE ... ADD PARTITION partition_name

对于使用RANGE分区的表，可以用这个语句添加新的区间到已有分区列表的最大一段。我们添加一个purchased 在2005-01-01 AND 2009-12-31之间的记录

ALTER TABLE ADD PARTITION (PARTITION p4 VALUES LESS THAN (2010));

注意的是：对于通过RANGE分区的表，只可以使用ADD PARTITION添加新的分区到分区列表的高端。设法通过这种方式在现有分区的前面或之间增加一个新的分区，比如添加PARTITION p5 VALUES LESS THAN (1985)，将会导致下面的一个错误

错误1463 (HY000): 对每个分区，VALUES LESS THAN 值必须严格增长

同理，LIST分区添加时不能添加一个包含现有分区值列表中任意值的分区，也就是说一个固定的分区键值必须制定唯一一个分区，否则也会报错，

重新定义分区：ALTER TABLE ... REORGANIZE PARTITION

重新定义分区就是在不丢失数据的情况下，重新编辑分区，以上面的tr表为例，将p0分区重新拆分成s0和s1分区


ALTER TABLE tr REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (1985),
    PARTITION s1 VALUES LESS THAN (1990)
)；

当然除了拆分一个RANGE分区为多个RANGE分区，还可以合并多个相邻的RANGE分区为一个RANGE分区或多个RANGE分区，注意只能合并相邻2个分区并且重新定义的分区区间必须与原来的分区区间覆盖相同，分区类型合并过程中不能变，不能把RANGE分区定义成HASH分区。


ALTER TABLE tr REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1990)
)；

LIST分区的变更同理。

HASH分区和KEY分区管理

HASH分区和KEY分区在操作上非常相似，具体如下：

减少分区：ALTER TABLE COALESCE PARTTION partition_name；（coalesce 合并的意思）


CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12；

减少HASH分区数量，从12个减少到6个，使用


ALTER TABLE clients COALESCE PARTITION 6；
Query OK, 0 rows affected (0.02 sec)

增加分区：ALTER TABLE ADD PARTITION PARTITIONS num；num代表增加num个分区，而不是增加到num个分区

ALTER TABLE clients ADD PARTITION PARTITIONS 4;

对于KEY分区以及线性的HASH、KEY分区都是一样的，使用上面的命令来进行分数数量的控制。

分表和分库

分表和分区的区别

实现方式上：mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都对应三个文件（MyISAM引擎：一个.MYD数据文件，.MYI索引文件，.frm表结构文件）。

数据处理上：分表后数据都是存放在分表里，总表只是一个外壳，存取数据发生在一个一个的分表里面。分区则不存在分表的概念，分区只不过把存放数据的文件分成了许多小块，分区后的表还是一张表，数据处理还是由自己来完成。

提高性能上：分表后，单表的并发能力提高了，磁盘I/O性能也提高了。分区突破了磁盘I/O瓶颈，想提高磁盘的读写能力，来增加mysql性能。

在这一点上，分区和分表的测重点不同，分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。

实现的难易度上：分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式和分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。分区实现是比较简单的，建立分区表，跟建平常的表没什么区别，并且对代码端来说是透明的。

分区的适用场景

1. 一张表的查询速度已经慢到影响使用的时候。

2.表中的数据是分段的

3.对数据的操作往往只涉及一部分数据，而不是所有的数据

CREATE TABLE sales (

id INT AUTO_INCREMENT,

amount DOUBLE NOT NULL,

order_day DATETIME NOT NULL,

PRIMARY KEY(id, order_day)

) ENGINE=Innodb

PARTITION BY RANGE(YEAR(order_day)) (

PARTITION p_2010 VALUES LESS THAN (2010),

PARTITION p_2011 VALUES LESS THAN (2011),

PARTITION p_2012 VALUES LESS THAN (2012),

PARTITION p_catchall VALUES LESS THAN MAXVALUE);

分表的适用场景

1. 一张表的查询速度已经慢到影响使用的时候。

2.当频繁插入或者联合查询时，速度变慢。

分表的实现需要业务结合实现和迁移，较为复杂。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/菜鸟追梦旅行/article/detail/630076