51Testing软件测试论坛

标题: MySQL高级特性之分区表 [打印本页]

作者: 乐哈哈yoyo    时间: 2019-2-2 10:38
标题: MySQL高级特性之分区表
对于用户而言,分区表是一个独立的逻辑表,但是在底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装,对分区表的请求都会通过句柄对象转化成对存储引擎的接口调用
意义
MySQL在创建表的时候可以通过使用 PARTITION BY 子句定义每个分区存放的数据。在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这样查询就可以无需扫描所有分区——只需要查找包含需要数据的分区即可。
分区的一个主要目的是 将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起,另外,当我们想要一次批量删除整个分区的数据也会变得很方便。
在以下的场景中,分区可以起到很大的作用:
分区表本身也有一些限制,下面几点尤为重要:
分区表的原理
存储引擎管理分区的各个底层表和管理普通表并没有什么区别(所有的底层表都必须使用相同的存储引擎)
,分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度看,底层表和一个普通表并没有什么区别,存储引擎也无需知道这是一个普通表还是一个分区表的一部分。
分区表上的操作按照下面的操作逻辑进行:
SELECT 查询
当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据
INSERT 操作
当写入一条记录的的时候,分区层先打开并锁住所有的底层表,然后确定哪个分区接收这条记录,再将记录写入对应底层表
DELETE 操作
当删除一条记录的的时候,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作
UPDATE 操作
当更新一条记录时,分区层先打开并锁住所有的底层表,MySQL先确定需要更新的记录再哪个分区,然后取出数据并更新,再判断更新后的数据应该放在哪个分区,最后对底层表进行写入操作,并对原数据所在的底层表进行删除操作。
这些操作都是支持过滤的。
虽然每个操作都会“先打开并锁住所有的底层表”, 但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁,则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。
分区表的类型
MySQL支持多种分区表,我们看到最多的就是根据范围进行分区,每个分区存储落在某个范围内的记录。分区表达式可以是列,也可以是包含列的表达式。
例如,如下表就将每一年的销售额都存放在不同的分区中:
CREATE TABLE sales(    order_date DATETIME NOT NULL,    ....)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 分区子句中可以使用各种函数。但是有一个要求, 表达式返回的值必须是一个确定的整数,且不能是一个常数。
MySQL还支持键值、哈希和列表分区等。
如何使用分区表
如果我们希望从一个非常大的表中查询出一段时间的记录,我们应该如何查询这个表,如何才能更加高效?
因为数据量非常大,肯定不能在每次查询的时候都扫描全表,考虑到索引在空间和维护上的消耗,我们也不希望使用索引。即使真的使用索引,也会发现数据并不是按照想要的方式进行聚集,会产生大量的碎片,最终导致一个查询产生成千上万的随机I/O。而事实上,当数据量超级大时,B-Tree索引就已经无法祈祷作用了。
因此我们可以选择一些更粗粒度但消耗更少的方式检索数据,例如在大量的数据上只索引对应的一小块元数据。
这正是分区要做的事情,理解分区可以将其当作索引的最初形态。 因为分区无需额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置,也就无须额外的数据结构——所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。
为了保证大数据量的可扩展性,一般有以下两个策略:
什么情况下会出问题
上面介绍的两个分区策略都基于两个非常重要的假设:查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。
事实证明,这两个假设在某些场景下会有问题:






欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/) Powered by Discuz! X3.2