Mysql表分区状态查询

Mysql表分区状态查询,第1张

一、查询mysql表是否为分区表:可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息 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='xw_coobill_order' 二、查询表有多少个分区 SELECT TABLE_NAME, COUNT(*) AS CNT FROM information_schema.PARTITIONS WHERE PARTITION_NAME IS NOT NULL GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50 三、分析执行语句 explain partitions select * from range_datetime where hiredate >= '20151207124503' and hiredate<='20151210111230' 四、分区管理 常规HASH和线性HASH的增加收缩分区的原理是一样的。增加和收缩分区后原来的数据会根据现有的分区数量重新分布。HASH分区不能删除分区,所以不能使用DROP PARTITION *** 作进行分区删除 *** 作; 只能通过ALTER TABLE ... COALESCE PARTITION num来合并分区,这里的num是减去的分区数量; 可以通过ALTER TABLE ... ADD PARTITION PARTITIONS num来增加分区,这里是null是在原先基础上再增加的分区数量。

对底层表的封装,意味着索引也是按照分区的子表定义的,而没有全局索引。(所以即使有唯一性索引,在不同子表中可能会有重复数据)

单表数据量超大时索引失效

将单表分区成数个区域,通过分区函数,可以快速地定位到数据的区域。而且相比于索引,分区不需要额外的数据结构记录每个分区的数据,代价更低。只需要一个简单的表达式就可以指向正确的分区

​ 可以只是用简单的分区方式存放表,不要任何索引,只要将查询定位到需要的大致数据位置,通过where条件,将需要的数据限制在少数分区中,则效率是很高的。WARNNING:查询需要扫描的分区个数限制在一个很小的数量。

​ 如果数据有明显的“热点”,可以将热点数据单独放在一个分区,让这个分区的数据能够有机会都缓存在内存中。

​ 如果分区表达式的值可以是NULL:第一个分区会使一个特殊分区。以partition by range year(order_date)为例,所有在order_date列为NULL或者非法值的数据都会被放到第一个分区。那么所有的查询在定位分区后都会增加扫描第一个分区。而且如果第一个分区很大的时候,查询的成本会被这个“拖油瓶”分区无情的增加。

​ 创建一个无用的第一分区可以解决这个问题,partition p_nulls values less than (0)

​ 对于分区列和索引列不匹配的查询,虽然查询能够使用索引,但是无法通过分区定位到目标数据的分区(也就是数据分布相对更加分散),需要遍历每个分区内的索引,除非查询中的条件同时也包含分区条件。所以期望分区条件范围被热门查询索引所包含。

​ 对于 范围分区 技术,需要适当限制分区的数量,否则对于大量数据批量导入的场景,选择分区的成本过高。对于大多数系统,100个左右的分区是没有问题的。

一、背景

话说风和日丽的一天,为提高随着业务增长的大表(3510449行吧)的访问效率,于是决定对表分区,记录如下。

二、实 ***

结合业务,若干条记录会集中在一个日期,查询时也往往只查询一个日期内的数据,于是选取分区字段为时间。

创建分区 比如

CREATE TABLE message_all (

id int(10) NOT NULL AUTO_INCREMENT,

......

createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

PRIMARY KEY ( id , createtime )

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(createtime))

(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,

PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

不过我们表已经有了当然不能这么建,除非你想导一次数据。

如下 *** 作

1、

ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))

(

PARTITION p2015 VALUES LESS THAN (to_days('2016-01-01')),

PARTITION p2016 VALUES LESS THAN (to_days('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (to_days('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

)

或者

2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime))

(

PARTITION p2015 VALUES LESS THAN (YEAR('2016-01-01'))

)

然后追加。

ALTER TABLE message_all ADD PARTITION

(

PARTITION p2016 VALUES LESS THAN (YEAR('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (YEAR('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

)

这里会有几种错误情况:

1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))

[Err] 1492 - For RANGE partitions each partition must be defined

解释:必须指定至少一个分区。

2、[Err] 1492 - A PRIMARY KEY must include all columns in the table's partitioning function

解释:分区字段必须是主键之一。

3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

解释:分区字段为timestamp,换成datetime。

4、[Err] 1526 - Table has no partition for value xxxx

解释:用追加方式第一次必须覆盖目前所有数据。

总结:

1、创建时必须指定至少一个分区。

2、key必须为主键之一。

3、RANGE处必须为INT型,时间字段用函数转——YEAR()、YEARWEEK()、TO_DAYS()。

4、THAN处必须为INT型,时间字段用函数转——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。

5、它就是以两个INT比大小划分的文件。

6、所有ENGINE必须一样。

7、范围分区添加只能在最大值后面追加。

8、分区是有上限的貌似1024个。

用到的其他 *** 作

1、删除分区(直接扔掉分区文件,数据也没了)

ALTER TABLE message_all DROP PARTITION p2016

2、清空分区数据

ALTER TABLE message_all TRUNCATE PARTITION p2017

3、重定义(可实现:分区拆分、合并、重命名)

ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO

(

PARTITION p2016012 VALUES less than(TO_DAYS('2016-03-01')),

PARTITION p2016034 VALUES less than(TO_DAYS('2016-05-01'))

)

检查/查看你的分区

1、SHOW TABLE STATUS LIKE 'message_all'

2、SELECT * FROM information_schema.partitions WHERE table_name='message_all'

3、SHOW CREATE TABLE message_all

4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime>= '2016-01-01' AND createtime <'2016-12-30'如果用到了分区partitions里会有显示。

5、指定分区查

SELECT COUNT(1) FROM message_all PARTITION (p2016) 表别名 WHERE ......

到这里就结束啦,土豆白。

一些概念

水平分区Partition有以下几种模式


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/7183834.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-02
下一篇 2023-04-02

发表评论

登录后才能评论

评论列表(0条)

保存