高性能的MySQL（7）分区技术详解

高性能的MySQL（7）分区技术详解2014-10-22在我之前的2篇博客中已经简单介绍过MySQL5.1之后的分区技术的基本理论和分区技术的表存储文件及特点，博客地址如下：

http://janephp.blog.51cto.com/4439680/1305220

http://janephp.blog.51cto.com/4439680/1305937

今天要介绍一下分区技术一些使用场景和机制。

MySQL实现分区表的方式--对底层表封装--意味着索引也按照分区的子表定义的，而没有全局索引。

一、在下面的场景中，作用非常大：

1、表非常大无法全部放到内存中，或者表的最后部分有热点数据，其他均是历史数据。

2、分区的数据更容易维护，可以对整个分区操作，还可以对独立分区进行优化、检查、修复操作。

3、分区表的数据可以分布在不同的物理设备上

4、可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引互斥访问。

分区表本身也有一些限制：

1、一个表最多只能有1024个分区

2、分区表达式必须是整数，或者返回整数的表达式。

3、分区表无法使用外键

二、分区表的原理

存储引擎管理分区的各个底层表和管理普通表一样，所有底层表都必须使用相同的引擎，从存储引擎来看，底层表和普通表么有任何不同。

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

SELECT：

当查询一个分区表的时候，分区层先打开并锁住所有底层表，优化器先判断是否可以过滤掉部分分区，然后进行操作。

INSERT：

当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这个记录，操作。

DELETE：

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这个记录，操作。

UPDATE：

当更新一条记录时，分区层先打开并锁住所有的底层表，确定分区，然后取出数据并更新，再判断更新后的数据放到哪个分区，然后进行写入操作，并对原数据所在底层表进行删除。

URL：http://www.bianceng.cn/database/MySQL/201410/46075.htm

但并不是每个操作都会锁住所有的表，如果引擎有自己的行级锁，例如InnoDB，则会在分区层释放对应的表锁。

三、如何使用

一般有2个策略：

1、全量扫描数据，不要任何索引。

2、索引数据，并分离热点。

但是必须注意到一下几点：

1、NULL会使分区过滤无效。

第一个分区是一个特殊分区，假设按照PARTITION BY RANGE YEAR（order_date）分区，那么所有order_date为null的或者非法值的，都会被存储到第一个分区。所以这样的查询where order_date between "2012-01-01" and "2012-12-31"会检测2个分区，除了2012这个分区还会检测第一个分区。

为了避免这种情况，可以创建一个无用的第一分区，例如 PARTITION p_null VALUES LESS THAN（0），这样即使检索代价很小的。

2、分区列和索引列不匹配，会导致无法进行分区过滤。

如果a上有索引，而列b进行分区，因为每个分区都有自己独立的索引，所以扫描列b上的索引就需要扫描每一个分区对应的索引。

特别在一个关联查询中，分区表在关联顺序的第二个表，并且索引分区列不匹配，则关联时针对第一个表符合条件的每一个表，都需要访问并搜索第二个表的所有分区。

3、选择分区、打开并锁表，维护分区可能代价很高。

4、所有分区都必须相同的存储引擎。

5、某些引擎不支持分区。

6、分区函数中可以使用的函数和表达式也有一些限制啊。