博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
MySQL 表分区梳理
阅读量:6094 次
发布时间:2019-06-20

本文共 4282 字,大约阅读时间需要 14 分钟。

hot3.png

本篇侧重mysql 表分区之 range partioning

分区表达式在指定范围内,分区应当是连续的,不重叠的,使用 VALUES LESS THAN 操作符定义。

employees 表用来记录有20个店的音箱连锁店。表:

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT NOT NULL,    store_id INT NOT NULL);

<一> 比如按照 store_id 进行分区。

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN (21));

1到5号商店的雇员存储在 p0分区表中,6到10号商店雇员存储在p1分区表中......

但是问题来了,如果sotre_id 是21 ,将怎么存储呢?mysql 将会抛出错误。因为mysql 不知道store_id > 20的商店雇员存储在哪里。所以应该这么修改,

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN MAXVALUE);

MAXVALUE 所表示的整数值,比最大的整数值还要大(在数学意义上, 它是整数值的最小边界)。

说明:当数据量很大的时候,可以通过 alter table 的方式,将比如21到25的表数据置于p4 分区中。

<二>按job_code 分区

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (job_code) (    PARTITION p0 VALUES LESS THAN (100),    PARTITION p1 VALUES LESS THAN (1000),    PARTITION p2 VALUES LESS THAN (10000));

<三>按separated 分区

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT,    store_id INT)PARTITION BY RANGE ( YEAR(separated) ) (    PARTITION p0 VALUES LESS THAN (1991),    PARTITION p1 VALUES LESS THAN (1996),    PARTITION p2 VALUES LESS THAN (2001),    PARTITION p3 VALUES LESS THAN MAXVALUE);

<四>按 timestamp 分区,利用 UNIX_TIMESTAMP() 函数

说明:mysql 5.5.1 后,timestamp 只允许使用UNIX_TIMESTAMP 函数。

CREATE TABLE quarterly_report_status (    report_id INT NOT NULL,    report_status VARCHAR(20) NOT NULL,    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),    PARTITION p9 VALUES LESS THAN (MAXVALUE));

<五>.删除旧分区

比如删除 employees 分区p0

ALTER TABLE employees DROP PARTITION p0

另一种方式,如:

DELETE FROM employees WHERE YEAR(separated) <= 1990

但是,按分区删除的效率是比较高的。

<六>.在分区上查找数据

 EXPLAIN PARTITIONS SELECT COUNT(*) FROM employees  WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;,

mysql 知道分区p2 需要被扫描,而其它分区则不包含符合条件的记录。

<七>.按Range Columns 进行分区

使用DATE 或者 DATETIME 列作为分区列,通过 RANGE COLUMNN 进行分区

CREATE TABLE members (    firstname VARCHAR(25) NOT NULL,    lastname VARCHAR(25) NOT NULL,    username VARCHAR(16) NOT NULL,    email VARCHAR(35),    joined DATE NOT NULL)PARTITION BY RANGE COLUMNS(joined) (    PARTITION p0 VALUES LESS THAN ('1960-01-01'),    PARTITION p1 VALUES LESS THAN ('1970-01-01'),    PARTITION p2 VALUES LESS THAN ('1980-01-01'),    PARTITION p3 VALUES LESS THAN ('1990-01-01'),    PARTITION p4 VALUES LESS THAN MAXVALUE);

转载于:https://my.oschina.net/pingjiangyetan/blog/608102

你可能感兴趣的文章
Eclipse编译PostgreSQL 9.2.2
查看>>
Class字节码文件结构详解
查看>>
[又拍云]云计算中又一架重要的马车
查看>>
Ruby on Rails 学习笔记(一)
查看>>
Linux下安装pymysql步骤
查看>>
PHP+MySQL数据库教程
查看>>
jQuery UI Datepicker 添加时分秒
查看>>
linux查看和终止进程
查看>>
Beyond Compare比较表格小技巧
查看>>
以太坊·代币开发详解
查看>>
LibreOffice 中文版安装指南
查看>>
c++中调用c代码的3 种方法
查看>>
ISO C 标准定义的头文件
查看>>
nginx配置
查看>>
joomla1.5前台组件开发过程分享(附中文开发教程两本)
查看>>
Sharepoint 2013企业内容管理学习笔记(二) 全自动化内容管理
查看>>
DateDiff()倒计时
查看>>
c++11新特性--decltype auto
查看>>
RawCap
查看>>
screen命令的最常见的使用方法
查看>>