mysql 数据表分区与分表介绍

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，MYI索引文件，frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。用户读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。用户读写的时候操作的名字还是大表的名字，数据库自动去组织分区的数据。

---

在这里介绍一下MySQL的分区，因为从某一方面它也可以说是 “分表”，MySQL的表分区主要是通过一些特殊的语句，创建独立的空间，事实上创建分区表的每个分区都是有索引的独立表，分区看上去像一个单独的表，数据库分区是一种物理数据库设计技术分区的主要目的是为了让某些特定的查询操作减少响应时间，同时对于应用来讲分区完全是透明的。MySQL的分区主要有两种形式：水平分区（ Horizontal Partitioning ）和垂直分区（ Vertical Partitioning ）。

使用场景：

• 表非常大，以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
• 分区表的数据更容易维护。例如：想批量删除大量数据可以使用清除整个分区的方式。
• 另外，还可以对一个独立分区进行优化、检查、修复等操作。
• 分区表的数据可以分布在不同的物理设备上，从而高效的利用多个硬件设备。
• 可以使用分区表来避免某些特殊的瓶颈，例如 InnoDB 的单个索引的互斥访问、ext3文件系统的 inode 锁竞争等。
• 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表本身也有一定的限制，下面是比较重要的几点

• 一个表最多只能有 1024 个分区。
• 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL5.5中，某些场景中可以直接使用列来进行分区。
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
• 分区表中无法使用外键约束。

分区的优点

与单个磁盘或文件系统分区相比，可以存储更多的数据。

对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反的，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便的实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。

一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。涉及到例如 SUM() 和 COUNT() 这样聚合函数的查询，可以很容易的进行并行处理。这种查询的一个简单例子，如：“select salesperson_id,count(orders) as order_total from sales group by salesperson_id;”。通过 “并行” ，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。