mysql-分区表

摘自：https://baijiahao.baidu.com/s?id=1581064623766883232&wfr=spider&for=pc

一、MySQL表分区和分表的区别

当数据库数据量涨到一定数量时，性能就成为我们不能不关注的问题，如何优化呢？ 常用的方式不外乎那么几种。

1.分表，即把一个很大的表的数据分到几个表中

优点：提高并发量，减小锁的粒度

缺点：代码维护成本高，相关sql都需要改动

2.分区，所有的数据还在一个表中，但物理存储数据根据一定的规则存放在不同的文件中，文件也可以放到不同的磁盘上

优点：代码维护量小，基本不用改动，提高I/O吞吐量

缺点：表的并发程度没有增加

3.拆分业务，这个本质还是分表

优点：长期支持更好

缺点：代码逻辑重构，工作量很大

当然，每种情况都有合适的应用场景，需要根据具体业务具体选择。由于分表和拆分业务和MySQL本身关系不大属于业务层面，我们只说和数据库关系最紧密的方式：表分区。

二、什么是表分区

通俗地讲表分区是将一大表，根据条件分割成若干个小表。MySQL5.1开始支持数据表分区了。分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。程序读写的时候操作的还是大表名字，MySQL服务器自动去组织分区的数据。

三、表分区的优缺点

优点：

分区表的数据更容易维护，如：想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

可以使用分区表来避免某些特殊的瓶颈，如：innodb的单个索引的互斥访问，ext3文件系统的inode锁竞争等

如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好

优化查询，在where字句中包含分区列时，可以只使用必要的分区来提高查询效率，同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。

缺点：

一个表最多只能有1024个分区（MySQL5.6之后支持8192个分区）

在MySQL5.1中分区表达式必须是整数，或者是返回整数的表达式，在5.5之后，某些场景可以直接使用字符串列和日期类型列来进行分区（使用varchar字符串类型列时，一般还是字符串的日期作为分区）。

如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引

分区表中无法使用外键约束

MySQL数据库支持的分区类型为水平分区，并不支持垂直分区，因此，MySQL数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中

目前MySQL不支持空间类型和临时表类型进行分区。不支持全文索引

四、分区表的类型介绍及简单使用

在MySQL中使用分区表前，需要看你的数据库是否支持，查看的命令为：

mysql> show plugins;

有上图中框选的行即表示MySQL支持表分区

MySQL支持5种分区方式：RANGE分区、LIST分区、HASH分区、LINEAR HASH分区和KEY分区。每种分区都有自己的使用场景。

下面简单介绍一下分区表的使用语法：

创建分区表：

CREATE TABLE table_name (

id INT AUTO_INCREMENT,

fieldX VARCHAR NOT NULL,

order_day DATETIME NOT NULL,

PRIMARY KEY(id, order_day)

) ENGINE=Innodb

PARTITION BY RANGE(YEAR(order_day)) (

PARTITION p_2015 VALUES LESS THAN (2015),

PARTITION p_2016 VALUES LESS THAN (2016),

PARTITION p_2017 VALUES LESS THAN (2017),

PARTITION p_catchall VALUES LESS THAN MAXVALUE);

这段语句表示将表内数据按照order_day的年份范围进行分区，2015年一个区，2016一个，2017一个，剩下的一个

注意如果要这么做，则order_day必须包含在主键中，且会产生一个问题，就是当年份超过阈值，到了2018、2019时，需要手动创建这些分区

如果你不愿意这么做，可以尝试使用HASH，如：

id INT PRIMARY KEY AUTO_INCREMENT,

order_day DATETIME NOT NULL

PARTITION BY HASH (id DIV 1000000);

这种分区表示每 100W 条数据建立一个分区，且没有阈值范围的影响

新增分区：

ALTER TABLE table_name ADD PARTITION (PARTITION p_2018 VALUES LESS THAN (2018));

删除分区：

ALTER TABLE table_name DROP PARTITION p_2018;

这里需要注意的是，一旦删除了一个分区，该分区的所有数据也将被删除

分区的合并：

ALTER TABLE table_name

REORGANIZE PARTITION p_201701,p_201702,p_201703 INTO

(

PARTITION p_2017Q1 VALUES LESS THAN (201704)

);

上面的语句表示将分区 p_201701、p_201702、p_201703 合并为一个整体分区 p_2017Q1

既然有分区的合并，对应的自然有分区的分解，分区的分解：

REORGANIZE PARTITION p_2017Q1 INTO

PARTITION p_201701 VALUES LESS THAN (201702),

PARTITION p_201702 VALUES LESS THAN (201703),

PARTITION p_201703 VALUES LESS THAN (201704)

重建分区：

ALTER TABLE table_name rebuild PARTITION p_2017;

重建分区的操作和先删除保存在分区中的所有记录，然后重新插入它们具有同样的效果，它可用于整理分区的碎片

优化分区：

ALTER TABLE table_name OPTIMIZE PARTITION p_2017;

如果从分区中删除了大量的行，或者对一个带有可变长度的行（有VARCHAR、BLOB、TEXT类型的列）做了许多修改，可以使用 "ALTER TABLE ... OPTIMIZE PARTITION" 来回收没有使用的空间，并整理分区数据文件的碎片。

分析分区：

ALTER TABLE table_name ANALYZE PARTITION p_2017;

此操作可以读取并保存分区的键分布

修补分区：

ALTER TABLE table_name REPAIR PARTITION p_2017;

用来修补被破坏的分区

检查分区：

ALTER TABLE table_name CHECK PARTITION p_2017;

这个命令可以告诉你表table_name的分区p_2017中的数据或索引是否已经被破坏。如果发生了这种情况，可使用 "ALTER TABLE ... REPAIR PARTITION" 来修补该分区。

五、对已有表进行分区

说了这么久的分区表，那么能否将原来的普通表改造为分区表呢？答案是肯定的。

一般的，我们可以用如下两种方式办到：

1.可以使用ALTER TABLE来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。

PARTITION BY RANGE (YEAR(order_day)) (

PARTITION p_2018 VALUES LESS THAN (2018),

2.新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表。

六、使用分区表的注意事项

NULL值使分区过滤无效：假设按照RANGE(YEAR(order_day))分区，如果这个表达式计算出来的是NULL值，记录就会被存放到第一个分区，因此当出现NULL值时将会检查第一个分区，解决的方法是将第一个分区建立为NULL分区 PARTITION p_nulls VALUES LESS THAN (0), 或者在MySQL5.5以后，直接使用COLUMN建立分区 PARTITION BY RANGE COLUMNS(order_day)

选择分区的成本：每插入一行数据都需要按照表达式筛选插入的分区地址

分区列和索引列不匹配：如果索引列和分区列不匹配,且查询中没有包含过滤分区的条件，会导致无法进行分区过滤，那么将会导致查询所有分区

打开并锁住所有底层表：分区表的的查询策略是在分区过滤之前，打开并锁住所有底层表，这会造成额外的开销，解决问题的方法是尽量使用批量操作，例如LOAD DATA INFILE，或者一次删除多行数据

七、分区表和普通表的性能比较

感兴趣的同学请自行测试，测试思路如下：

创建两张表，一张是分区表、另一张为普通表

用存储过程分别存入几百万条（甚至更多）数据

对两张表执行相同的SQL查询语句，对比执行时间

还可以通过 EXPLAIN 语句来分析执行情况，你可以发现分区表能通过扫描更少的行达到同样的效果

--END