MySQL中的分区表

对用户来说,分区表是一个独立的逻辑表,但是底层是由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求,都会通过句柄对象转化成存储引擎的接口调用。分区表在很多场景中可以起到非常大的作用,但是其本身也存在一些限制。以下对MySQL中的分区表作一个简单的归纳。

01 分区表的原理

1、MySQL在创建时使用PARTITION BY子句定义每个分区存放的数据。

2、分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。

3、分区表的数据更容易维护。

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

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

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

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

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

9、分区表由多个相关的底层表实现。

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

11、从存储引擎的角度来看,底层表和一个普通表没有任何不同。

12、分区表的每个操作都会“先打开并锁定所有的底层表”。

13、如果存储引擎能够自己实现行级锁,例如InnoDB,则会在分区层释放对应表锁。

02 分区表的类型

分区表的类型主要有以下几种:

(1)根据范围进行分区。每个分区存储落在某个范围的分区,表达式可以是列,也可以是包含列的表达式。

(2)根据时间间隔进行分区。

(3)根据键值、哈希、列表进行分区。

(4)使用数据模函数进行分区。

03 如何使用分区表

使用分区表,主要有两个策略:

(1)全量扫描数据,不要任何索引。可以使用简单的分区方式存放表,不要任何索引,根据分区的规则大致定位需要的数据位置。只要能够使用WHERE条件,将需要的数据限制在少数分区中,则效率是很高的。

(2)索引数据,并分离热点。如果数据有明显的热点,将热点数据单独放在一个分区中,让这个分区的数据能够有机会都缓冲在内存中。

04 使用分区表可能出的问题

1、NULL值会使分区过滤无效。NULL或者非法值会被存放到第一个分区。如果第一个分区非常大,相当于使用全量扫描数据。解决办法:可以创建一个“无用”的第一个分区。

2、分区列和索引列不匹配。如果定义的索引列和分区列不匹配,会导致查询无法进行分区过滤。

3、选择分区的成本可能很高。对于范围分区,随着分区数量的增长,成本会越来越高。其他的分区类型,比如键分区和哈希分区,则没有这样的问题。

4、打卡并锁住所有底层表的成本可能很高。可以用批量操作的方式来降低单个操作的此类开销。

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

6、对于MyISAM的分区表,不能使用LOAD_INDEX_INTO_CACHE操作。

7、对于MyISAM表,使用分区表时需要打开更多的文件描述符。每一个分区对于存储引擎来说都是一个独立的表。

05 查询优化

1、分区最大的优点就是优化器可以根据分区函数来过滤一些分区。根据粗粒度索引的优势,通过分区过滤通常可以让查询扫描更少的数据。

2、使用EXPLAIN PARTITION可以观察优化器是否执行了分区过滤。

3、MySQL只能在使用分区函数的列本身进行比较是才能过滤分区,而不能根据表达式的值去过滤分区。

4、即便在创建分区时可以使用表达式,但在查询时却只能根据列来过滤分区。

06 合并表

1、对于分区表,用户无法访问底层的各个分区。合并表允许用户单独访问各个子表。

2、分区表和优化器的结合更加紧密,这也是未来发展的趋势,而合并表是将一种将被淘汰的技术。

3、在合并表上无法使用REPLACE语法,无法使用自增字段。

4、如果一个查询访问合并表,那么它需要访问所有子表。

5、一个MyISAM表可以是多个合并表的子表。

发布了64 篇原创文章 · 获赞 10 · 访问量 7644
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览