首页 数据库

MYSQL表分区介绍

发布时间:2018年11月20日 评论数：1 阅读数：3468

当数据库数据量涨到一定数量时，如何优化呢？ 

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

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

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

什么是表分区

通俗地讲表分区是将一大表，根据条件分割成若干个小表。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分区。每种分区都有自己的使用场景。