MySQL分区表管理命令汇总

目录
  • 一、ANALYZE和CHECK PARTITION 分析和检查分区
  • 二、REPAIR 修复分区
  • 三、OPTIMIZE 分区
  • 四、REBUILD分区
  • 五、新增和删除分区

前言:

分区是一种表的设计模式,正确的分区可以极大地提升数据库的查询效率,完成更高质量的SQL编程。但是如果错误地使用分区,那么分区可能带来毁灭性的的结果。

分区功能并不是在存储引擎层完成的,因此不只有InnoDB存储引擎支持分区,常见的存储引擎MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持,如CSV、FEDORATED、MERGE等就不支持分区。在使用此分区功能前,应该对选择的存储引擎对分区的支持有所了解。

MySQL数据库在5.1版本时添加了对分区的支持,分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言,从逻辑上讲,只有一个表或一个索引,但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区(指将同一个表中不同行的记录分配到不同的物理文件中),并不支持垂直分区(指将同一表中不同列的记录分配到不同的物理文件中)。此外,MySQL数据库的分区是局部分区索引,一个分区中既存放了数据又存放了索引。而全局分区是指,数据存放在各个分区中,但是所有数据的索引放在一个对象中。目前,MySQL数据库还不支持全局分区,接下来我们一起来看看分区表的管理吧!

一、ANALYZE和CHECK PARTITION 分析和检查分区

ANALYZE分析分区:

ALTER TABLE t_test_task_result ANALYZE PARTITION p20220218,p20220219;
+------------------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------------------------+---------+----------+----------+
| testdb.t_test_task_result | analyze | status | OK |
+------------------------------------------+---------+----------+----------+
1 row in set (0.01 sec)

CHECK 检查分区是否存在错误:

 ALTER TABLE t_test_task_result check PARTITION p20220218,p20220219;
+------------------------------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------------------------+-------+----------+----------+
| testdb.t_test_task_result | check | status | OK |
+------------------------------------------+-------+----------+----------+
1 row in set (0.03 sec)

二、REPAIR 修复分区

ALTER TABLE t_test_task_result repair PARTITION p20220218,p20220219;
+------------------------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------------------------+--------+----------+----------+
| testdb.t_test_task_result | repair | status | OK |
+------------------------------------------+--------+----------+----------+

三、OPTIMIZE 分区

该命令主要是用于回收空闲空间和分区的碎片整理。对分区执行该命令,相当于依次对分区执行 CHECK PARTITION, ANALYZE PARTITION,REPAIR PARTITION命令。

会锁表:

ALTER TABLE t_test_task_result OPTIMIZE PARTITION p20220218,p20220219;
+------------------------------------------+----------+----------+---------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------------------------------+----------+----------+---------------------------------------------------------------------------------------------+
| testdb.t_test_task_result | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| testdb.t_test_task_result | optimize | status | OK |
+------------------------------------------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (1 min 35.75 sec)

四、REBUILD分区

重建分区,它相当于先删除分区中的数据,然后重新插入。这个主要是用于分区的碎片整理。这个操作会操作锁表

ALTER TABLE t_test_task_result REBUILD PARTITION p20220218,p20220219;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

ALTER TABLE t_test_task_result REBUILD PARTITION p20220225,p20220226,p20220227,p20220228;
Query OK, 0 rows affected (2 min 9.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

五、新增和删除分区

添加分区测试: ADD PARTITION(新增分区)

ALTER TABLE t_test_task_result ADD PARTITION (PARTITION p20220401 VALUES LESS THAN (TO_DAYS('2022-04-01')));

ALTER TABLE t_test_task_result ADD PARTITION (PARTITION p20220401 VALUES LESS THAN (TO_DAYS('2022-04-01')));
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

删除分区测试:DROP PARTITION(删除分区)

ALTER TABLE t_test_task_result DROP PARTITION p20220218;
##提示删除分区是物理删除,操作非常快
 ALTER TABLE t_test_task_result DROP PARTITION p20220218;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@testdb 15:02: [testdb]> ALTER TABLE t_test_task_result DROP PARTITION p20220219,p20220220;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

到此这篇关于MySQL分区表管理命令汇总 的文章就介绍到这了,更多相关MySQL分区表管理命令内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 详解MySQL分区表

    前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理.本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助. 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这

  • MySQL分区表实现按月份归类

    目录 建表 查看数据库文件: 插入 查询 删除 补充:Mysql自动按月表分区 MySQL单表数据量,建议不要超过2000W行,否则会对性能有较大影响.最近接手了一个项目,单表数据超7000W行,一条简单的查询语句等了50多分钟都没出结果,实在是难受,最终,我们决定用分区表. 建表 一般的表(innodb)创建后只有一个 idb 文件: create table normal_table(id int primary key, no int) 查看数据库文件: normal_table.ibd

  • MySQL优化之分区表

    当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何优化呢? 常用的方式不外乎那么几种: 1.分表,即把一个很大的表达数据分到几个表中,这样每个表数据都不多. 优点:提高并发量,减小锁的粒度 缺点:代码维护成本高,相关sql都需要改动 2.分区,所有的数据还在一个表中,但物理存储数据根据一定的规则存放在不同的文件中,文件也可以放到另外磁盘上 优点:代码维护量小,基本不用改动,提高IO吞吐量 缺点:表的并发程度没有增加 3.拆分业务,这个本质还是分表. 优点:长期支持更好 缺点:代码

  • MySQL 分区表中分区键为什么必须是主键的一部分

    目录 水平拆分VS垂直拆分 分区表 MySQL8.0中分区表的变化 为什么分区键必须是主键的一部分? 本地分区索引VS全局索引 总结 前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理 随着业务的不断发展,数据库中的数据会越来越多,相应地,单表的数据量也会越到越大,大到一个临界值,单表的查询性能就会下降. 这个临界值,并不能一概而论,它与硬件能力.

  • 关于MySQL分区表的一个性能BUG

    目录 二.使用pt-pmap进行栈分析 三.关于本列中瓶颈点的分析 四.分区表中多次建立template的情况 五.关于一个特殊的流程 六.问题模拟 七.总结 一.问题描述 最近遇到一个问题,也就是使用分区表进行数据查询/加载的时候比普通表的性能下降了约50%,主要瓶颈出现在CPU,既然是CPU瓶颈理所当然的我们可以采集perf top -a -g和pstack来寻找性能瓶颈所在,同时和普通表进行对比,发现CPU主要耗在函数build_template_field上如下图: 二.使用pt-pma

  • Mysql分区表的管理与维护

    改变一个表的分区方案只需使用alter table 加 partition_options 子句就可以了.和创建分区表时的create table语句很像. 创建表 CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THA

  • MySQL分区表管理命令汇总

    目录 一.ANALYZE和CHECK PARTITION 分析和检查分区 二.REPAIR 修复分区 三.OPTIMIZE 分区 四.REBUILD分区 五.新增和删除分区 前言: 分区是一种表的设计模式,正确的分区可以极大地提升数据库的查询效率,完成更高质量的SQL编程.但是如果错误地使用分区,那么分区可能带来毁灭性的的结果. 分区功能并不是在存储引擎层完成的,因此不只有InnoDB存储引擎支持分区,常见的存储引擎MyISAM.NDB等都支持分区.但是并不是所有的存储引擎都支持,如CSV.FE

  • mysql下mysqladmin日常管理命令总结(必看篇)

    mysqladmin 工具的使用格式: mysqladmin [option] command [command option] command ...... 参数选项: -c number 自动运行次数统计,必须和 -i 一起使用 -i number 间隔多长时间重复执行 0)每个两秒查看一次服务器的状态,总共重复5次. [root@test-huanqiu ~]# mysqladmin -uroot -p -i 2 -c 5 status 1)查看服务器的状况:status [root@te

  • Mysql常用命令汇总

    一.Mysql安装目录 数据库目录:/var/lib/mysql/ 配置文件:/usr/share/mysql(mysql.server命令及配置文件) 相关命令:/usr/bin(mysqladmin mysqldump等命令) 启动脚本:/etc/init.d/mysql(启动脚本文件mysql的目录) 二.系统管理 连接MySQL 格式: mysql -h 主机地址 -u用户名 -p用户密码 例 1:连接到本机上的MySQL. 复制代码 代码如下: hadoop@ubuntu:~$ mys

  • 通过实例学习MySQL分区表原理及常用操作

    1.分区表含义 分区表定义指根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分.实际上,表的不同部分在不同的位置被存储为单独的表.用户所选择的.实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数. 分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表   2.分区表优点 1)分区表更容易维护.对于那些已经失去保存意义的数据,通常可以通过删除与那些数据

  • Mysql账户管理原理与实现方法详解

    本文实例讲述了Mysql账户管理原理与实现方法.分享给大家供大家参考,具体如下: 账户管理 在生产环境下操作数据库时,绝对不可以使用root账户连接,而是创建特定的账户,授予这个账户特定的操作权限,然后连接进行操作,主要的操作就是数据的crud MySQL账户体系:根据账户所具有的权限的不同,MySQL的账户可以分为以下几种 服务实例级账号:,启动了一个mysqld,即为一个数据库实例:如果某用户如root,拥有服务实例级分配的权限,那么该账号就可以删除所有的数据库.连同这些库中的表 数据库级别

  • mysql事务管理操作详解

    本文实例讲述了mysql事务管理操作.分享给大家供大家参考,具体如下: 本文内容: 什么是事务管理 事务管理操作 回滚点 默认的事务管理 首发日期:2018-04-18 什么是事务管理: 可以把一系列要执行的操作称为事务,而事务管理就是管理这些操作要么完全执行,要么完全不执行(很经典的一个例子是:A要给B转钱,首先A的钱减少了,但是突然的数据库断电了,导致无法给B加钱,然后由于丢失数据,B不承认收到A的钱:在这里事务就是确保加钱和减钱两个都完全执行或完全不执行,如果加钱失败,那么不会发生减钱).

  • mysql用户管理操作实例分析

    本文实例讲述了mysql用户管理操作.分享给大家供大家参考,具体如下: 本文内容: 用户的介绍 查看用户 创建用户帐户 修改账户 删除帐户 关于匿名用户 首发日期:2018-04-19 用户的介绍: mysql的客户端连接是以用户名来登录服务端. 服务端可以对用户的权限来进行更改,所以每个用户对数据库或对数据表的权限都是不一样的. 一般来说不应该使用root用户登录,因为root用户拥有最高的权限,可以进行删除数据库等"危险"操作.为了安全,应该使用其他用户登录,并且给他分配合适的权限

  • Linux基础之正则表达式,用户、组管理命令介绍

    通配符(Globbing) 通配符与元字符类似,通配符主要用于文件名的匹配,而元字符则主要用在字符串的匹配上: 下面介绍几种常用的通配符: * 表示匹配任意位数的任意字符 ? 表示匹配一位任意字符 ^ 表示取反,不包含的意思 [] 表示此区间内的任意一个字符 {} 表示一种集合 \ 转义字符,使具有特殊意义的字符失去原有意义 | 表示'或',匹配一组可选的字符 元字符 元字符是用来描述字符的特殊字符. 常用的元字符及意义如下: * 重复前面的字符0次或者多次 . 匹配任意字符一次 \+ 匹配前面

  • win2003 IIS+MySQL服务管理助手

    提示:如果你安装MySQL的服务名不是mysql,请使用文本编辑器打开该bat文件,批量替换文件中的mysql. mysql.bat 程序源码使用方法:将下面的文件复制保存为mysql.bat直接运行即可,如果你的服务器禁止了bat的运行请自行修改. 复制代码 代码如下: @Echo Off TITLE IIS6+MySQL服务管理助手v0.1 :start CLS COLOR 1f :: 使用COLOR命令对控制台输出颜色进行更改 MODE con: COLS=31 LINES=18 :: M

随机推荐