mysql分区详解

    mysql从5.0版本开始引入分区，对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理字表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转化成为存储引擎的接口调用。索引分区对于SQL层来说是一个完全封装底层实现的黑盒子，对于应用是透明的，但是从底层的文件系统很容易发现，每个分区表都有一个使用#分割命名的表文件。

1、分区表的实现

    mysql实现分区表的方式----对底层表的封装---意味索引也是按照分区的字表定义的，而没有全局索引。这个oracle不同，在oracle中可以更加灵活的定义索引和表是否进行分区。

    mysql在创建表时使用partition by定义每个分区存在的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区--只需查找所包含的数据的分区就可以了。

2、分区表的作用

    分区在一下场景中起到非常大的作用：

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

3、分区的限制

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

 4、分区表的原理

    分区表有多个相关的底层表实现，这些地层表也是有句柄对象表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个地层表和管理普通表一样，分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无需知道这是一个普通表还是一个分区表的一部分。

    分区表上的操作可以按照下面的操作逻辑进行：

•  select查询：当查询一个分区表的时候，分区层先打开并锁住所有底层表，优化器判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
• insert操作：当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定那个分区接受这条记录，再将记录写入对应底层表。
• delete操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应表进行删除操作。
• update操作：当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在那个分区，然后取出数据并更新，在判断更新后的数据该放在那个分区，然后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

    虽然每个操作都会“先打开并锁住所有底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应的表锁。

5、分区表的类型

    mysql支持多种分区表，最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。mysql还支持键值、哈希和列表分区。还有一些其他的分区技术包括：

• 根据键值进行分区，来减少InnoDB的互斥竞争；
• 使用数学模函数来进行分区，然后将数据轮询放入不同的分区。
• 假设表有一个自增的主键列ID，希望根据时间将最近的热点数据集中存放。那么必须将时间戳包含在主键当中才行，而这和主键本身的意义相矛盾。这种情况下也可以使用这样的分区来实现相同的目的：HASH（id div 1000000),这将为100万数据建立一个分区。这样一方面实现了当初的分区目的，另一方面比起使用时间范围分区避免了一个问题，就是当超过一定阀值时，如果使用时间范围分区就必须新增分区。

6、如何使用分区表

    如果一张表数据量巨大，肯定不能每次查询都进行扫描全表。考虑到索引在空间和维护上的消耗，也不希望使用索引。这个时候两条路可选：让所有的查询只在数据表上做顺序扫描，或者将数据表和索引全部都缓存在内存里。

    在数据量超大的时候，b-tree索引就无法起作用了。除非是索引覆盖查询，否则数据库需要根据扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机I/O，随之，数据库的响应时间将大到不可接受的程度。意识到这一点，于是就完全抛弃使用b-tree索引，而选择一些更粗粒度的但消耗更少的方式检索数据。

    这正是分区要做的事情。理解分区时还可以将其当做索引的最初形态，以代价非常小的方式定位到需要的数据在那一片“区域”。在这片“区域”中，你可以做顺序扫描，可以建索引，还可以将数据缓存到内存。因为分区无须额外的数据结构记录每个分区有那些数据，所以代价非常低，只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

7、分区带来的问题

• null值会使分区过滤无效：一个容易让人误解的地方就是分区表达式的值可以是null：第一个分区是一个特殊分区。假如按照partition by range year(order_date)分区，那么所有order_date为null或者一个非法值的时候，记录都会被存放在第一个分区。当查询的时候，例如：where order_date between '2012-01-01' and '2012-01-31'。实际上，mysql会检查两个分区，而不是之前猜想的一个。即检查2012分区和第一个分区。
• 分区列和索引列不匹配：如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每个分区内对应的索引。
• 选择分区的成本可能很高：分区的类型很多，不同类型的分区实现也不相同，所以他们的性能也各不相同。尤其是范围分区，在查询过程中成本会很高，且随着分区数量的增长，成本会越来越高。
• 打开并锁住所有底层表的成本可能很高：当查询访问分区表时，mysql需要打开并锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，索引无法通过分区过滤降低次开销，并且该开销也和分区类型无关，会影响所有的查询。
• 维护分区的成本可能会很高：某些分区维护操作的速度会非常快（如add、delete），而有些操作，会开销很大（alter语句需要复制数据）