随着业务需求的增加,资料量会伴随着系统成长而增长。在储存的资料越来越多的况下,势必会产生一些存取效能上的问题。这时除了建立合适的索引之外,对於数据量多的资料表可透过将资料分割,表切分区的方式维护做查询优化,使查询时不但能缩小资料范围(只要搜寻指定范围内的分区资料),还能方便管理旧资料的删除作业(指定分区删除)。
范例表结构:
CREATE TABLE `act` (
`actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活动ID',
`status` bigint(20) DEFAULT NULL COMMENT '活动状态 0(下架) 1(上架) 2(准备中)',
`start_time` int(11) unsigned NOT NULL,
`end_time` int(11) unsigned NOT NULL,
`platform_id` bigint(20) DEFAULT NULL,
`updated_at` int(11) unsigned NOT NULL,
`created_at` int(11) unsigned NOT NULL,
PRIMARY KEY (`actives_id`)
);
注意:
当表有Primary key或unique index的状况下,使用於分区的栏位必须包含在其中,不然会无法建立。
当要插入的数据值超出partition设定范围时是无法INSERT的。
example.
介绍一下 MYSQL Partition以下种类:
range
Example. 针对actives_id定义范围做分区。
ALTER TABLE act
PARTITION BY RANGE (actives_id) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
查询分区内资料:
SELECT * FROM table_name PARTITION (partition_name);
分区名称 | 值范围 |
---|---|
p0 | <5 |
p1 | 5 ~ <10 |
p2 | 10 ~ <15 |
pmax | 15之後的值 |
list
Example. 使用act表中的status栏位(判断活动目前状态)做list分区。
(1.) 先将Primary key改成复合式PK -> (actives_id,status)
ALTER TABLE `t2`.`act` DROP PRIMARY KEY, ADD PRIMARY KEY (`actives_id`, `status`);
(2.) 新增list分区
ALTER TABLE act
PARTITION BY LIST (status) (
PARTITION p0 VALUES IN (0),
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2),
PARTITION p3 VALUES IN (4,5)
);
(3.) 测试资料确认
INSERT INTO `act` ( `status`, `start_time`, `end_time`, `platform_id`, `updated_at`, `created_at`) VALUES ( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '5', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200');
删除单一分区(该分区内资料会被删除):
ALTER TABLE act DROP PARTITION p0; //partition & 数据都被删除
mysql> SELECT * FROM act PARTITION (p0);
ERROR 1735 (HY000): Unknown partition 'p0' in table 'act'
移除表分区定义(现有资料不会被删除):
ALTER TABLE act REMOVE PARTITIONING; //变回未分区前的表结构
查询各分区资讯:
语法:
select table_schema, table_name, partition_name,partition_method, partition_description, table_rows from information_schema.partitions where table_schema = 'database_name' and table_name = 'table_name';
mysql> select table_schema, table_name, partition_name,partition_method, partition_description, table_rows from information_schema.partitions where table_schema = 'active' and table_name = 'act';
+--------------+------------+----------------+------------------+-----------------------+------------+
| table_schema | table_name | partition_name | partition_method | partition_description | table_rows |
+--------------+------------+----------------+------------------+-----------------------+------------+
| t1l | act | p1 | RANGE | 10 | 5 |
| t1l | act | p2 | RANGE | 15 | 0 |
| t1l | act | pmax | RANGE | MAXVALUE | 0 |
+--------------+------------+----------------+------------------+-----------------------+------------+
3 rows in set (0.01 sec)
今天介绍了几个分区使用,明天继续把剩下的介绍完~ 在来透过procedure实作我们的自动切表流程。
>>: Day17 Let's ODOO: Data Files
Solving Linear Systems of Equations using HHL HHL ...
在串接API时,遇到最大的坎就是Message内文加密了, 就让我们来试看看罗~ Message内文...
这边有一个javascript变数: var subtitles = {{ json_dual }}...
JavaScript 学得差不多了,觉得应该可以开始 Vue 看看了 但在 Vue 之前要先引入它 ...
之前用MediaPipe做过侦测到中指就打马赛克的主题, 下方有朋友留言提议能不能做「侦测到爱心就放...