Mysql 分区

作者: 黄靠谱 | 来源:发表于2019-01-22 12:13 被阅读16次

mysql 分区

参考博客：
http://blog.csdn.net/three_man/article/details/46982105

陈川大佬的博客
https://www.jianshu.com/p/01b9f028d9c7

飞神的博客（分区和分库分表PK）
https://www.jianshu.com/p/d5d4fa4bd713

Mysql官网
https://dev.mysql.com/doc/refman/8.0/en/partitioning-overview.html

概论

分区的优势：海量数据时，把一张大表按照业务上的字段拆分成几个独立的分区，从而提升综合性能。这个特性由数据库自身维护，用户在操作数据时仍然像操作一张表，非常友好的一种数据库特性。
每个分区的锁也是独立的（表锁和行级锁）
分区键和索引键不是一个列的时候，基于普通索引列的查询也会走索引，相当于N个分区有N个普通索引，并行的执行索引
分区类型：水平分区(Range分区、List分区、Hash分区、Key分区）和垂直分区（把常用字段和不常用字段放在不同的分区里面）
要在WHERE后面带分区列，且不能是表达式，否则就是大表扫描所有分区
使用EXPLAIN PARTITIONS SELECT来判断是否进行了分区过滤

基本概念

为什么要分区？

数据量增多之后，索引遇到了瓶颈，即使构建了相关索引，查询和操作的速度仍然很慢。
没有分区时，都是通过分表的形式来实现一个业务大表，拆分为几个小表，来实现提速的效果，但是代码的复杂度就提升了，因为在代码层面上就是同时要操作几张表。
分区可以看作是MySQL底层帮我们实现分表，分区表是一个独立的逻辑表，底层由多个物理子表组成

比如按照年份拆分:例如查询 2018、2019年所有的name=“huang”的数据，分区的话，代码查询的是同一张表，只是查询条件不同了而已。分表的话，则需要查询2张不同的表

//分表
select *  from user_2019 where name="huangzs";
select *  from user_2018 where name="huangzs";

//分区
select * from user where name="huangzs" and Year(registDate) in ('2019','2018');

分区表的特点：

高性能：Mysql查询优化器会分析sql，根据查询条件选择对应的分区进行查询，而不是全表扫描；另外如果在分区字段上创建索引的话，那么每个独立的分区拥有独立的索引，性能显著提升
易编程：对外编程来说，不同分区的子表，仍然是一张表，减少了编程的复杂度，具体的分区选择由查询优化器决定
分区的锁是独立的，分区表的表锁之间相互并不关联，例如把p1分区的表加独占锁，同时可以往p2分区插入数据，提高了并发性。如果分区键上再建立索引的话，可以实现行级锁，大大提升写的性能。

适用的业务场景：使用数据时有明显的区分，比如时间先后、批次

很适合大量历史数据，少量活跃数据的场景
适合有批次概念、有时间概念的查询业务场景

创建分区的技术条件：

分区字段必须包含在主键里面，所以要么按照ID来做Range分区，要么用分区键和ID做联合主键
分区键的运算结果必须为整数，除了使用YEAR, TO_DAY等日期函数外，还可以使用其数学函数，比如取模，按7取模是周几等

分区表的存储结构(InnoDB）：1个pizza_order.frm文件、1个pizza_order.par文件、N个pizza_order#p#p1.ibd文件

分区的分类

水平分区

Range 分区:适合范围查找（例如时间，例如ID，金额等等)
List 分区：适合 In查询，或者精确查询（例如批次 01 、 02 批次)
HASH 分区：只适合精确查询，不适合范围查找（预先设定好几个块，按照hash算法填充)

垂直分区
把不常用的大字段的字段单独分区出来，提高效率，又不影响数据的结构和完整性。

分区子句中可以使用各种函数，但表达式的返回值必须是一个确定的整数，且不能是一个常数。MySQL还支持一些其他分区，比如键值、哈希、列表分区，但在生产环境中很少见到。

创建Range分区

创建订单表，按照创建日期进行分区，这样按照日期查询的时候，就会根据区块查询，而不是所有的数据扫描了

CREATE TABLE pizza_order(
    id bigint(20) NOT NULL AUTO_INCREMENT,
    created DATETIME NOT NULL COMMENT '创建时间',
    PRIMARY KEY (id, created)) ENGINE=InnoDB PARTITION BY RANGE(YEAR(created))(
    PARTITION p1 VALUES LESS THAN (2015),   
    PARTITION p2 VALUES LESS THAN (2016), 
    PARTITION p3 VALUES LESS THAN (2017),
    PARTITION p_latest VALUES LESS THAN MAXVALUE); 


INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('3', '2014-01-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('4', '2015-07-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('1', '2016-01-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('2', '2016-07-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('5', '2017-02-02 00:01:01');

EXPLAIN select * from pizza_order where CREATED='2014-01-01 00:00:00';

EXPLAIN select * from pizza_order where CREATED>'2016-02-02 00:01:01';

List分区,这种如果插入语句不在IN中，则会插入失败

PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

Hash分区:PARTITIONS为分区的数量，即会根据分区键的值计算出一个hash值，然后以4为模进行存储，好处是，不用再重新建分区了。
比如对ID进行Hash分区，非常适合根据ID进行的精确查找，而且分布会很均匀

PARTITION BY HASH(store_id)
PARTITIONS 4;

Key分区：类似于Hash分区，但是Hash分区的值必须是整数，和比如store_id，比如 year(date)，但是Key分区支持除了 Bol、text字段的其它所有字段类型，自己有函数做Hash散列

分区的使用

使用分区的前提是：查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价
在WHERE条件中带入分区列，如果没有这些条件，MySQL会扫描所有分区

查看分区情况

来查看分区查询走了哪几个块区。
EXPLAIN PARTITIONS select * from pizza_order where CREATED>'2016-02-02 00:01:01';
查看该表的所有分区块的存储情况

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION 
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='pizza_order';

注意事项

要在WHERE后面带分区列，且不能是表达式，否则就是大表扫描所有分区

//全表扫描
EXPLAIN  PARTITIONS  SELECT * FROM `pizza_order` where id in (1,3);
//只扫描 p-2016 和p-latest 2个分区
EXPLAIN PARTITIONS select * from pizza_order where CREATED>'2016-02-02 00:01:01';

使用EXPLAIN PARTITIONS SELECT来判断是否进行了分区过滤
分区键的运算结果是Null的会自动分配到第一个分区
对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引一说（当然也可以不对分区字段添加索引）
5.7版本开始，只有InnoDB和NDB索引引擎支持分区（8.0版本也是如此)，Myisam 5.6还是支持的，5.7.17就不支持了

分区的限制

分区最大数：8192
Innodb分区表不支持外键
不支持全文搜索

网友评论

本文标题：Mysql 分区

本文链接：https://www.haomeiwen.com/subject/wtsljqtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！