mysql分区未生效_Mysql 分区

使用场景

千万级别的数据

限制

一个表最多只能有1024个分区。

在MySQL 5.1中，分区表达式必须是整数，或者是返回整数的表达式。在MySQL 5.5中，某些场景中可以直接使用列来进行分区。

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

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

原理

分区表由多个相关的底层表实现，这些底层表也是由句柄对象(Handler object)表示，所以我们也可以直接访问各个分区。

分区表的索引只是在各个底层表上各自加上一个完全相同的索引

从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

SELECT 查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

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

DELETE 删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。

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

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

类型

目前MySQL支持范围分区(RANGE)，列表分区(LIST)，哈希分区(HASH)以及KEY分区四种。

CREATE TABLE members (

firstname VARCHAR(25) NOT NULL,

lastname VARCHAR(25) NOT NULL,

username VARCHAR(16) NOT NULL,

email VARCHAR(35),

joined DATE NOT NULL

)

PARTITION BY RANGE( YEAR(joined) ) (

PARTITION p0 VALUES LESS THAN (1960),

PARTITION p1 VALUES LESS THAN (1970),

PARTITION p2 VALUES LESS THAN (1980),

PARTITION p3 VALUES LESS THAN (1990),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

需要显示写明 partitions 分区总数量。select 时需要自己指定 partition 范围。

CREATE TABLE employees (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30),

hired DATE NOT NULL DEFAULT '1970-01-01',

separated DATE NOT NULL DEFAULT '9999-12-31',

job_code INT,

store_id INT

)

PARTITION BY HASH( YEAR(hired) )

PARTITIONS 4;

缺陷

NULL值会使分区过滤无效。假设按照PARTITION BY RANGE YEAR(order_date)分区，那么所有order_date为NULL或者是一个非法值的时候，记录都会被存放到第一个分区

分区列和索引列不匹配。假设在列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶子节点都在内存中，那么扫描的速度还可以接受，但如果能跳过某些分区索引当然会更好。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。

选择分区的成本可能很高。

打开并锁住底层表的成本可能很高。当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销。

维护分区的成本可能很高