mysql死磕30天第三天

mysql高级特性 分区

对用户来说分区表是一个独立的逻辑表,但是底层由多个物理子表组成。实现分区的代码实际上是一组底层表的句柄对象的封装。对分区表的请求都会通过具柄对象转换成对存储引擎的接口调用。所以分区对于sql层来说是一个完全封装底层实现的黑盒子,对应用是透明的,但是从底层的文件系统来看就很容易发现,每一个分区表都有一个使用#分隔命名的表文件。

分区的一个主要目的是 将数据按照一个较粗的粒度分在不同的表中。分区表的索引只是在各个底层表各自加上一个完全相同的索引。之于存储引擎,分区表的底层表与普通表没有区别。

这里有使用分区表的一个事例:

复制代码

CREATE TABLE sales (
  order_date DATETIME NOT NULL,
  -- Other columns omitted) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE );

复制代码

PARTITION 分区子句可以使用各种函数。但有一个要求,表达式返回的值要是一个确定的整数,
且不能是一个常数。

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

虽然每个操作都会"先打开并锁住所有的底层表",但并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁,则会在分区层释放对应表所。
这个加锁和解锁过程和普通InnoDB上的查询相似。
    分区表的限制:
  1.一个表最多1024个分区
  2.在mysql5.1中,分区表达式必须是整数,或者返回整数的表达式。在mysql5.5中某场景
   中可以直接使用列来进行分区。
  3.如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含起来。
  4.分区表无法使用外键约束。


在数据量巨大的情况,肯定不能在每次查询的时候都扫描全表。考虑到索引在空间和维护上的消耗, 也不希望使用索引。即使真的使用索引,你会发现数据并不是按照想要的方式聚集的,而且会有大量的碎片 产生,最终会导致一个查询产生成千上万的随机IO,应用程序也随之僵死。 在这里需要再陈述一遍,在数据量超大的时候,B-Tree索引就无法起作用了。 除非是索引覆盖查询,否则数据库服务器要根据索引扫描的结果回表,查询所有符合条件的记录,如果 数据量巨大,这将产生大量随机IO,随之,数据库的响应时间将大到不可接受的程度。另外索引维护的代价 也非常高。这正是需要用到分区的场景。
为了保证大数据量的可扩展性,一般有下面两个策略:   1.全局扫描数据,不要任何索引   2.索引数据,并分离热点。
分区可能会遇到的问题:    null值会使分区过滤无效:        null值会被自动过滤到第一个分区。为了便面这种情况,可以创建一个无用的第一个分区。        分区列和索引列不匹配        如果定义的索引列和分区列不匹配,会导致查询无法进行分区过滤。            选择分区的成本可能会很高        打开并锁住所有底层表的成本可能很高        维护分区的成本可能很高。    分区表注意点:    所有分区都必须使用相同的存储引擎        分区函数中可以使用的函数和表达式有一定的限制。        某些存储引擎不支持分区     EXPLAIN PARTITIONS SELECT * FROM sales_by_day where YEAR(day) = 2010\g MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区,而不能根据表达式的值去过滤分区, 即使这个表达式就是分区函数也不行。