广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Mysql 分区介绍(九) —— 分区管理
  • 792

0
分享到

Mysql 分区介绍(九) —— 分区管理

2024-04-02 19:04:59 792人浏览 泡泡鱼
摘要

一、分区操作 1. 将没有分区的表改为分区表 ALTER TABLE trb3 PARTITioN BY KEY(id) PARTITIONS 2; 2. 删除分区 # 删除所有分区, 同时数据丢失 ALT

一、分区操作

1. 将没有分区的表改为分区表
ALTER TABLE trb3 PARTITioN BY KEY(id) PARTITIONS 2;
2. 删除分区
# 删除所有分区, 同时数据丢失
ALTER TABLE es2 REMOVE PARTITIONING;

# 删除指定分区, 数据丢失
ALTER TABLE tr DROP PARTITION p2;
3. SELECT指定分区查询
select * from daily_rank_1_1 partition (p2015_04_24) limit 10;
4. 添加分区

如果设置了MAXVALUE则无法添加新分区, 会提示 MAXVALUE can only be used in last partition definition, 这时可以使用修改分区来解决

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
5. 重新划分分区
ALTER TABLE table1 REORGANIZE PARTITION 要修改的分区名(可以多个, 逗号分隔) INTO (
    PARTITION 新分区1的名字 VALUES LESS THAN (值),
    PARTITION 新分区2的名字 VALUES LESS THAN (值)
    ...
)

修改一个分区成两个分区

alter table daily_rank_1_1 reorganize partition p2015_04_28 into(
partition p2015_04_28 values less than (to_days('2015-04-28')),
partition pmax values less than(MAXVALUE)
);

重新划分多个分区

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

分区修改的原则:<br />

    1. 不能与原方案有重叠
    1. 同时对多个分区划分必须是连续的分区
    1. 分区类型不可以更改, 可以通过ALTER TABLE ... PARTITION BY ...实现
6. 修改分区数量(HASH/Key分区)
ALTER TABLE clients COALESCE PARTITION 4;

二、交换分区和子分区

支持交换分区的条件

    1. 表自身不是分区表
    1. 不是临时表
    1. 两个表的结构相同
    1. 表不包含外键
    1. 表的数据没有出界

如果要执行操作, 必须具有DROP权限<br />

    1. 执行 ALTER TABLE ... EXCHANGE PARTITION不会在分区表或交换表上调用任何触发器
    1. auto_increment会发生重置

具体操作:
pt是分区表, p是分区或子分区

1. 与非分区表交换分区
CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");
Mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)
mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.90 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 将p0分区的数据写入e2
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

如果没有匹配到数据, 则提示Found row that does not match the partition

2. 交换一个子分区到一个没有分区的表
mysql> CREATE TABLE es (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30)
    -> )
    ->     PARTITION BY RANGE (id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (50),
    ->         PARTITION p1 VALUES LESS THAN (100),
    ->         PARTITION p2 VALUES LESS THAN (150),
    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
    ->     );
Query OK, 0 rows affected (2.76 sec)
mysql> INSERT INTO es VALUES
    ->     (1669, "Jim", "Smith"),
    ->     (337, "Mary", "Jones"),
    ->     (16, "Frank", "White"),
    ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 将p3sp0的数据交换到es2表
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.29 sec)

如果一个表拥有子分区, 则不能移动这个父分区到表中

三、分区维护

  1. 重建分区
    删除所有记录存储在分区，然后重新插入它们。整理碎片

    ALTER TABLE t1 REBUILD PARTITION p0, p1;

  2. 优化分区
    优化分区来回收未使用的空间和整理的分区中的数据文件

    ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
  3. 分析分区
ALTER TABLE t1 ANALYZE PARTITION p3;

  1. 检查分区

    ALTER TABLE trb3 CHECK PARTITION p1;

  2. 修复分区

    ALTER TABLE t1 REPAIR PARTITION p0,p1;
  3. 获取分区有效信息 
    mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
   Table: trb3
Create Table: CREATE TABLE `trb3` (
`id` int(11) default NULL,
`name` varchar(50) default NULL,
`purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(purchased)) (
PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.00 sec)
select *
from INFORMATION_SCHEMA.PARTITIONS

四、分区修剪

在执行sql时, 优化器会自动根据分区的条件, 进行分区选择来提高性能。

分区修剪的条件: <br />

    1. partition_column = constant
    1. partition_column IN (constant1, constant2, ..., constantN)
      where条件中包含<，>，< =，> =，和< > 等之间范围查询的时候, 就可以使用分区修剪
      SELECT、UPDATE和DELETE都可以修剪分区, 但是INSERT无法修剪分区

五、分区选择

在执行操作的时候优化器会根据语句自动进行修剪, 但是在有些时候是不同的:

  1. 要检查的分区由语句的发布者指定，与分区剪枝不同，它是自动的。
  2. 而分区修剪仅适用于查询，分区明确的选择是查询和多个DML语句支持。
    支持的语句: SELECT、DELETE、INSERT、REPLACE、UPDATE、LOAD DATA.、LOAD XML.

具体的语句:

      PARTITION (partition_names)

      partition_names:
          partition_name, ...
SELECT * FROM employees PARTITION (p1);

mysql> SELECT * FROM employees PARTITION (p0, p2)
    ->     WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name           |
+----+----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)

mysql> SELECT store_id, COUNT(department_id) AS c
    ->     FROM employees PARTITION (p1,p2,p3)
    ->     GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 |        2 |
| 5 |        3 |
+---+----------+
2 rows in set (0.00 sec)

你也可以使用PARTITION 在INSERT...SELECT语句上

mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO employees_copy
    ->     SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname  | lname    | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou    | Waters   |        2 |             4 |
| 11 | Jill   | Stone    |        1 |             4 |
| 12 | Roger  | White    |        3 |             2 |
| 13 | Howard | Andrews  |        1 |             2 |
| 14 | Fred   | Goldberg |        3 |             3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)

也可以在联表中使用

CREATE TABLE stores (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    city VARCHAR(30) NOT NULL
)
    PARTITION BY HASH(id)
    PARTITIONS 2;

INSERT INTO stores VALUES
    ('', 'Nambucca'), ('', 'Uranga'),
    ('', 'Bellingen'), ('', 'Grafton');

CREATE TABLE departments  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
)
    PARTITION BY KEY(id)
    PARTITIONS 2;

INSERT INTO departments VALUES
    ('', 'Sales'), ('', 'Customer Service'),
    ('', 'Delivery'), ('', 'Accounting');

mysql> SELECT
    ->     e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
    ->     s.city AS City, d.name AS department
    -> FROM employees AS e
    ->     JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
    ->     JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
    -> ORDER BY e.lname;
+-------------+---------------+-----------+------------+
| Employee ID | Name          | City      | department |
+-------------+---------------+-----------+------------+
|          14 | Fred Goldberg | Bellingen | Delivery   |
|           5 | Mary Jones    | Nambucca  | Sales      |
|          17 | Mark Morgan   | Bellingen | Delivery   |
|           9 | Andy Smith    | Nambucca  | Delivery   |
|           8 | June Wilson   | Bellingen | Sales      |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)

删除中使用分区选择

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname  | store_id | department_id |
+----+-------+--------+----------+---------------+
|  4 | Jim   | Smith  |        2 |             4 |
|  8 | June  | Wilson |        3 |             1 |
| 11 | Jill  | Stone  |        1 |             4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM employees PARTITION (p0, p1)
    ->     WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

更新中使用分区选择

mysql> UPDATE employees PARTITION (p0)
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

mysql> UPDATE employees PARTITION (p2)
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        2 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

INSERT和REPLACE INTO使用分区选择

mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)

mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)

六、分区的限制

    1. 无法使用存储过程、存储功能、UDF和插件
    1. 无法用户变量或声明变量
    1. 不允许位操作

七、性能影响

    1. 分区的创建、修改、删除取决于文件系统。应该确保large_files_support启用，open_files_limit设置正确
    1. 在执行分区操作时需要上写锁, 但是不影响查询, 分区操作完成后会立即执行插入和更新操作
    1. 分区操作， 查询、更新往往是MYISAM比INNODB更快
    1. 使用索引可以在非分区表提高性能, 使用分区修剪也可以显著的提高性能
    1. 加载数据使用缓冲来提高性能。您应该知道缓冲区每分区使用130KB内存来实现这一点。
    1. Mysql5.6.7之前, 分区最大数为1024个, 从5.6.7开始, 分区表的数最多是8192个, 包括子分区
    1. 分区表不支持查询缓存

Mysql 分区介绍(九) —— 分区管理

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: Mysql 分区介绍(九) —— 分区管理

本文链接: https://www.lsjlt.com/news/37329.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • Mysql 分区介绍(九) —— 分区管理
    一、分区操作 1. 将没有分区的表改为分区表 ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; 2. 删除分区 # 删除所有分区, 同时数据丢失 ALT...
    99+
    2022-10-18
  • MySQL分区介绍
    不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分 mysql> create table t1(     -> col1 i...
    99+
    2022-10-18
  • MySQL的分表和分区介绍
        在日常开发或维护中经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果...
    99+
    2022-10-18
  • MySQL分区建索引以及分区介绍总结
    目录MySQL 分区建索引介绍MySQL 分区介绍介绍总结MySQL 分区建索引介绍 mysql分区后每个分区成了独立的文件，虽然从逻辑上还是一张表其实已经分成了多张独立的表，从&l...
    99+
    2022-11-13
  • Oracle分区表介绍
    http://blog.csdn.net/gapapp/article/details/11631371 一. 分区表理论知识     &n...
    99+
    2022-10-18
  • mysql表分区技术详细介绍
    1、概述     数据库单表到达一定量后，性能会有衰减，像mysql\sql server等犹为明显，所以需要把这些数据进行分区处理。同时有时候可能出现数据剥离什么的，分区...
    99+
    2022-10-18
  • MySQL区分大小写的简单介绍
    下面一起来了解下MySQL区分大小写，相信大家看完肯定会受益匪浅，文字在精不在多，希望MySQL区分大小写这篇短内容是你想要的。【Linux】    lower_case_table_n...
    99+
    2022-10-18
  • Android 系统的分区介绍
    由于Android系统采用Linux架构，所以Android的系统分区可以类比同样采用Linux架构的操作系统（如Windows）。 Android系统分区分类 现在一般常见的Android分区方式共有三种，在不同的Android系统版本上...
    99+
    2023-09-02
    linux android 车载系统
  • mysql创建表分区详细介绍及示例
    mysql创建表分区详细介绍及示例 1. 基本概念1.1 什么是表分区？1.2 表分区与分表的区别1.3 表分区有什么好处？1.4 分区表的限制因素 2. 如何判断当前MySQL是否支持分区？3.分区类型详解3.1 MySQL支...
    99+
    2023-08-17
    mysql 数据库 java
  • Linux系统MBR和GPT分区的区别介绍
    主引导记录（Master Boot Record ， MBR）是指一个存储设备的开头 512 字节。它包含操作系统的引导器和存储设备的分区表。   　　全局唯一标识分区表（GUID PartitiK...
    99+
    2022-06-04
    linux系统分区
  • 管理Mysql用户及分配权限介绍
    本文主要给大家简单讲讲管理Mysql用户及分配权限介绍，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望管理Mysql用户及分配权限介绍这篇文章可以给大家带来一...
    99+
    2022-10-18
  • MySQL数据库表的合并与分区实现介绍
    目录创建数据表数据库表合并数据库表分区创建数据表 创建数据表的，使用字符串应该遵循的原则 从速度方面考虑，要选择固定的列，可以使用CHAR类型要节省空间，使用动态的列，可以使用VARCHAR类型要将列中的内容限制为一种选...
    99+
    2022-09-26
  • linux下磁盘分区的详细介绍
    这篇文章主要讲解了“linux下磁盘分区的详细介绍”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“linux下磁盘分区的详细介绍”吧！Centos下磁盘管理   ...
    99+
    2023-06-13
  • win7如何给硬盘分区 win7硬盘分区步骤介绍（图文）
    很多电脑都预装了Win7系统，但是硬盘一般只分为C盘和D盘，而对于大多数网友来说，都是想要多分几个区的。但是win7如何给硬盘分区？其实这在Win7系统中是比较容易的，可直接对已存在的磁盘作一定的变更而不影响已经存在的数...
    99+
    2023-06-06
    win7如何给硬盘分区 win7硬盘分区 图文 硬盘分区 win7 步骤
  • MySQL分区表管理命令汇总
    目录一、ANALYZE和CHECK PARTITION 分析和检查分区二、REPAIR 修复分区三、OPTIMIZE 分区四、REBUILD分区五、新增和删除分区前言： 分区是一种表...
    99+
    2022-11-13
  • Prism区域管理器IRegionManager用法介绍
    概要 本文主要介绍Prism的IRegionManager, 主要分析源代码的执行流程, 来介绍内部实现的几个核心接口调用过程。 通过本文, 你可以熟练的掌握Prism当中以下接口的...
    99+
    2022-11-13
  • 详细介绍线上MySQL某个历史数据表分区
    本文主要给大家简单讲讲线上MySQL某个历史数据表分区，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望线上MySQL某个历史数据表分区这篇文章可以给大家带来一...
    99+
    2022-10-18
  • 简单介绍区分applet和application的方法
            Java语言是一种半编译半解释的语言。Java的用户程序分为两类：Java Application和Java Applet。这两类程序在组成结构和执行机制...
    99+
    2023-05-31
    java applet application
  • MySQL分区表的分区原理及优缺点
    这篇文章主要介绍“MySQL分区表的分区原理及优缺点”，在日常操作中，相信很多人在MySQL分区表的分区原理及优缺点问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL分...
    99+
    2022-10-18
  • Win7自建隐藏分区的重要性介绍
    1、分区状态该分区的格式为NTFS，没有磁盘卷标也没有分配驱动器号，其磁盘状态描述为：系统、活动、主分区。因为没有驱动器号，所以在资源管理器中是不可见的。 2、该分区中都有什么呢为了一探究竟，笔者为其分配了一个...
    99+
    2023-06-01
    Win7 隐藏分区 重要性
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作