bitsCN.com
MySQL分区概述:
允许根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数。函数根据用户指定的分区类型来选择,把用户提供的表达式的值作为参数。该表达式可以是一个整数列值,或一个作用在一个或多个列值上并返回一个整数的函数。[z1] 。这个表达式的值传递给分区函数,分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数,也不能是任意数。它不能包含任何查询,但是实际上可以使用MySQL 中任何可用的SQL表达式,只要该表达式返回一个小于MAXVALUE(最大可能的正整数)的正数值。
由于MySQL无全局索引的概念,只有本地分区索引,基于此种原因,一个表中如果有2个或2个以上的唯一索引,此表无法分区。分区函数应用的数据库的列必须是MySQL的主键,否则不能分区。[z2]
对于创建了分区的表,可以使用你的MySQL 服务器所支持的任何存储引擎。在MySQL 5.1版中,同一个分区表的所有分区必须使用同一个存储引擎;例如,不能对一个分区使用MyISAM,而对另一个使用InnoDB。但是,这并不妨碍在同一个 MySQL 服务器中,甚至在同一个数据库中,对于不同的分区表使用不同的存储引擎。
MySQL分区的建立
MySQL可以建立四种分区类型的分区:
RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。详情参见18.2.1节,“RANGE分区”。
· LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。详情参见18.2.2节,“LIST分区”。
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。详情参见18.2.3节,“HASH分区”。
· KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。详情参照:18.2.4. KEY分区。
子分区:子分区是分区表中每个分区的再次分割。书写格式参照:18.2.5. 子分区
(1)关于子分区应注意的地方: 每个分区必须有相同数量的子分区。
· (2)如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区,那么就必须定义所有的子分区。
在建立分区的时候可以指定分区的数据存储位置和索引位置,这样可以跨磁盘或者文件系统保存不同的数据。数据分磁盘存储可以一定程度上增加数据读取速度,因为采用多磁盘后,每个磁盘的I/O操作会降低。而且采用指定分区存储位置能够增大存储量。
无论使用何种类型的分区,分区总是在创建时就自动的顺序编号,且从0开始记录,记住这一点非常重要。当有一新行插入到一个分区表中时,就是使用这些分区编号来识别正确的分区。例如,如果你的表使用4个分区,那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型,确认每个分区编号都定义了一个分区,很有必要。对HASH分区,使用的用户函数必须返回一个大于0的整数值。对于KEY分区,这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。注意:分区的名字是不区分大小写的,且对于RANGE分区和LIST分区,分区的名称是不能重复的。这几种可根据不同的需求来选择,比较常用的是RANGE分区。
常用的MySQL的分区管理:
RANGE 和LIST分区管理
分区对于程序来说是透明的,而且只有删除能在分区层面上操作,其他如查询、修改、增加都不能指定分区。
ALTER TABLE …DROPPARTITION ….(删除分区)
ALTER TABLE … ADD PARTITION (PARTITION p3 VALUESLESS THAN (…));[z3] 增加分区
ALTER TABLE ... REORGANIZE PARTITION …,… INTO (
PARTITION p0 VALUES LESS THAN (…)
);[z4] 合并拆分分区。
HASH和KEY分区管理
添加分区和RANGE、LIST分区方式相同,对于修改分区,不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式,来从HASH或KEY分区的表中删除分区。但是,可以使用“ALTERTABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。
如果要查看分区的信息,可以通过sql语句来查询
SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME='xxx’
分区表效率比较
MySQL分区表实验
分区采用红色,不分区采用蓝色
测试环境:CentOS 虚拟机,1G内存,20G硬盘
实验数据库:test 不分区(内有1张表RPT_MALEVENTS)、test2(与test一样)
背景数据:
mysql> SELECT COUNT(*)FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (10.84 sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (14.63sec)
数据分布:2011/8/4~2011/8/17
分区表结构:
CREATETABLE `RPT_MALEVENTS` (
`RECORD_DATE` date NOT NULL,
`RECORD_HOUR` tinyint(2) NOT NULL,
`RECORD_MINUTE` tinyint(2) NOT NULL,
`RECORD_DATETIME` datetime NOT NULL,
`MC_IP` int(10) unsigned NOT NULL,
`PC_IP` int(10) unsigned NOT NULL,
`NETOBJECT_GROUP_ID` smallint(5) DEFAULTNULL,
`ALERT_TYPE` tinyint(3) NOT NULL,
`SUB_TYPE` smallint(5) NOT NULL,
`SHOW_TYPE` smallint(5) NOT NULL,
`ALERT_ID` tinyint(3) NOT NULL,
`EVENT_COUNT` int(10) unsigned DEFAULT NULL,
PRIMARY KEY(`RECORD_DATE`,`RECORD_HOUR`,`RECORD_MINUTE`,`MC_IP`,`PC_IP`,`ALERT_TYPE`,`SUB_TYPE`,`ALERT_ID`),
KEY `RECORD_DATETIME` (`RECORD_DATETIME`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITIONBY RANGE (TO_DAYS(RECORD_DATE)[z1] ) (PARTITION p2011 VALUES LESS THAN (734503)ENGINE = InnoDB, PARTITION p20110809 VALUES LESS THAN (734724) ENGINE = InnoDB,PARTITION p20110810 VALUES LESS THAN (734725) ENGINE = InnoDB, PARTITIONp20110811 VALUES LESS THAN (734726) ENGINE = InnoDB, PARTITION p20110812 VALUESLESS THAN (734727) ENGINE = InnoDB, PARTITION p20110813 VALUES LESS THAN(734728) ENGINE = InnoDB, PARTITION p20110814 VALUES LESS THAN (734729) ENGINE= InnoDB, PARTITION p20110815 VALUES LESS THAN (734730) ENGINE = InnoDB,PARTITION p20110816 VALUES LESS THAN (734731) ENGINE = InnoDB, PARTITIONp20110817 VALUES LESS THAN (734732) ENGINE = InnoDB, PARTITION p20110818 VALUESLESS THAN (734733) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE[z2] ENGINE = InnoDB)
分区表的物理存储如下,当前用的是innodB的存储引擎,采用分表结构
分析如下
(条件查询查询全部数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (21.62sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (29.20sec)
(查询部分数据,不使用分区函数使用的列)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' ANDRECORD_DATETIME +----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (2.83sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' AND RECORD_DATETIME +----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (5.60sec)
(使用其他条件查询部分数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (8.49sec)
SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (12.88sec)
(小范围查询,在一个分区内查询)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (1.85sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (3.10sec)
分析SQL语句的执行过程
rows表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。
mysql>EXPLAIN PARTITIONS SELECT * FROMRPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p2011[z3] 0816,p20110817,p20110818,pMax
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 355911[z4]
Extra: Using where
1 row in set (0.00sec)
mysql> EXPLAIN SELECT * FROM RPT_MALEVENTS WHERERECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 1002288[z5]
Extra: Using where
1 row in set (0.00sec)
与分区函数使用列无关的查询条件
mysql>EXPLAIN PARTITIONS SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p20110816,p20110817,p20110818,pMax[z6]
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17084274[z7]
Extra: Using where; Using index
1 row in set (0.00sec)
mysql> EXPLAINSELECT COUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: index
possible_keys: NULL
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 17082459
Extra: Using where; Using index
1 row in set (0.00sec)
采用分区函数使用的列
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p20110810,p20110811,p20110812,p20110813,p20110814,p20110815[z8]
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 3767081[z9]
Extra: Using where; Using index
1 row in set (0.08sec)
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: NULL
type: range
possible_keys:PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 8541229[z10]
Extra: Using where; Using index
1 row in set (0.00sec)
删除数据,如果删除1整天的数据,由于我们采用按天分区,
mysql> ALTER TABLERPT_MALEVENTS DROP PARTITION p20110809;[z11]
Query OK, 0 rowsaffected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除后包含索引的和数据的RPT_MALEVENTS#P#p20110809.ibd被删除了
如果采用传统的不分区的方式删除。
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 3929328rows affected (1 min 29.68 sec)
由此可见,删除整个分区内的数据还是很快的,
如果分区表采用传统的方式删除:
mysql> DELETEFROM RPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (19.72 sec)
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (18.75 sec)
采用传统的方式删除一天的数据,用的时间都差不多。
只删除数据后,数据分区配p20110810还在,而且大小不变。可以用ALTER TABLE t1 OPTIMIZE PARTITION来进行回收,但是MySQL5.1.22还没有实现。
跨分区删除。
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (55.20 sec)
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (50.26 sec)
分区表删除比不分区的略慢
[z1]分区函数
[z2]分区信息,从2011-08-09开始
[z3]没有用分区函数使用的列会扫描所有分区
[z4]数据量为681311,分区后扫描行数为355911,虽然查询条件没有分区函数的列,但是mysql的查询优化器会将其对应于时间分区,这样可以减少扫描行数
[z5]数据量为681311,分区后扫描行数为1002288
[z6]查找所有分区
[z7]无关分区函数的字段,会遍历几乎所有行。
[z8]扫描部分分区
[z9]扫描行数随之减少
[z10]估计扫描的行数
[z11]这个分区的数据是所有2011-8-10之前的所有数据,共3929328。
总结:
分区表是在MySQL5.1中新增的的功能,截止到MySQL5.1.22-rc,分区技术并不很成熟,很多分区的维护和管理功能未实现。如,分区内数据存储空间的回收、分区的修复、分区的优化等,MySQL的分区可以用在可以按分区删除的表中,且对数据库的修改操作不大,且频繁按照分区字段进行查询的表中(如恶意代码中的统计表按天分区,经常按照时间进行查询、分组等,且可以按天删除分区)。此外,由于MySQL无全局索引只有分区索引,当一张有2个唯一索引[z5] 的时候,不能将此表分区,分区列中必须包含主键。否则MySQL会报错。
总之,MySQL对于分区的限制很多,且个人认为hash和key的分区实际意义不是太大。
分区引入了一种新的优化查询的方式(当然,也有相应的缺点)。优化器可以使用分区函数修整分区,或者把分区从查询中完全移除掉。它通过推断是否可以在特定的分区上找到数据来达成这种优化。因此在最好的情况下,修整可以让查询访问更少的数据。重要的是要在WHERE子句中定义分区键,即使它看上去像是多余的。通过分区键,优化器就可以去掉不用的分区,否则的话,执行引擎就会像合并表那样访问表的所有分区,这在大表上会非常慢。分区数据比非分区数据更好维护,并且可以通过删除分区来移除老的数据。分区数据可以被分布到不同的物理位置,这样服务器可以更有效地使用多个硬盘驱动器。
[z1]分区函数的返回值必须是整数,新增分区的分区函数返回值应大于任何一个现有分区的分区函数的返回值。
[z2]对于有主键的表错误提示:#1503
A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION,没有主键的则无此约束
[z3]注意:对于通过RANGE分区的表,只可以使用ADD PARTITION添加新的分区到分区列表的高端。即不能添加比这个分区的范围小的分区。
[z4] 对于按照RANGE分区的表,只能重新组织相邻的分区;不能跳过RANGE分区。不能使用REORGANIZEPARTITION来改变表的分区类型;也就是说,例如,不能把RANGE分区变为HASH分区,反之亦然。也不能使用该命令来改变分区表达式或列。
[z5]注意主键和唯一索引的区别
作者“深巷明朝卖杏花”

“本地用户和组”实用程序内置于“计算机管理”中,可以从控制台访问,也可以独立访问。但是,一些用户发现Windows11中缺少本地用户和组。对于可以访问它的一些人来说,该消息显示,此管理单元可能不适用于此版本的Windows10。若要管理此计算机的用户帐户,请使用“控制面板”中的“用户帐户”工具。该问题已在上一次迭代Windows10中报告,并且通常是由于用户端的问题或疏忽引起的。为什么Windows11中缺少本地用户和组?您运行的是Windows家庭版,本地用户和组在专业版及更高版本上可用。活动

由于权限,并不总是可以访问某些文件夹,在今天的指南中,我们将向您展示如何在Windows11上的旧硬盘驱动器上访问用户文件夹。此过程很简单,但可能需要一段时间,有时甚至数小时,具体取决于驱动器的大小,因此请格外耐心并严格按照本指南中的说明进行操作。为什么我无法访问旧硬盘上的用户文件夹?用户文件夹的所有权属于另一台电脑,因此您无法对其进行修改。除了所有权之外,您对该文件夹没有任何权限。如何打开旧硬盘上的用户文件?1.取得文件夹的所有权并更改权限找到旧的用户目录,右键单击它,然后选择属性。导航到“安

Microsoft开始推出作为Windows503145511H22或更高版本的可选更新向公众KB2。这是第一个默认启用Windows11Moment4功能的更新,包括受支持区域中的WindowsCopilot、对“开始”菜单中项目的预览支持、任务栏的取消分组等。此外,它还修复了Windows11的几个错误,包括导致内存泄漏的潜在性能问题。但具有讽刺意味的是,2023年<>月的可选更新对于尝试安装更新的用户甚至已经安装更新的用户来说都是一场灾难。许多用户不会安装此Wi

微软邀请Canary和Dev频道的WindowsInsider项目成员,测试和体验新版画图(Paint)应用,最新版本号为11.2306.30.0。本次版本更新最值得关注的新功能是一键抠图功能,用户只需要点击一下,就能自动消除背景,凸显画面主体,便于用户后续操作。整个步骤非常简单,用户在新版画图应用中导入图片,然后点击工具栏上“移除背景”(removebackground)按钮,就可以删除图片中的背景,用户也可以使用矩形来选择要消除背景的区域。

TranslucentTB是寻求时尚简约桌面外观的Windows11爱好者广泛使用的工具,遇到了障碍。自从发布以来Windows11内部版本22621.1344(22H2)28年2023月日,TranslucentTB对大多数用户不起作用。此错误使用户努力应对其任务栏的有限自定义选项。用户在寻求克服这一挫折的解决方案时,挫败感显而易见。在最近的Windows11更新之后,TranslucentTB无法正常工作的问题已在多个在线平台上广泛报道,包括论坛和社交媒体。用户一直在分享他们的经验,拼命寻找

mysql修改host的方法:1、通过“sudo service mysql stop”停掉mysql服务;2、以安全模式启动mysql,然后重置root密码;3、通过“update user set Host='%' where User='hive';”语句修改host即可。

随着iOS17的推出,Apple使iPhone用户可以下载地图以供离线使用。此功能为可能前往互联网连接不可靠或不存在的地区的用户提供了实用的解决方案。通过将地图下载到他们的设备上,用户可以确保即使失去蜂窝连接,他们也可以始终访问他们想要的路线。离线地图不仅仅是一个位置的静态图像。它们为用户提供了丰富的信息,例如地点的小时数和评级、转弯路线和预计到达时间。这意味着即使没有Wi-Fi或蜂窝连接,用户仍然可以导航和访问有关周围环境的关键信息。在“地图”中使用搜索功能打开“地图”应用,在搜索中搜索某个地

在使用驱动器映射和网络共享时,可能会出错很多问题。无线网络不如有线网络可靠。一直困扰用户的错误是本地安全机构数据库包含内部不一致。这会影响所有网络驱动器,用户无法访问或连接到它。该错误还会导致通过Samba到Linux和其他工作站的网络共享出现问题。在某些情况下,用户会看到消息,云端硬盘无法访问。您可能没有使用此网络资源的权限。本地安全机构数据库包含内部不一致。继续阅读以了解有关问题的所有信息!为什么无法访问网络驱动器?以下是网络驱动器突然停止工作的几个原因:缺少权限:通常,使用权限不足的帐户(


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

禪工作室 13.0.1
強大的PHP整合開發環境

SublimeText3漢化版
中文版,非常好用

SublimeText3 Linux新版
SublimeText3 Linux最新版

記事本++7.3.1
好用且免費的程式碼編輯器

Dreamweaver CS6
視覺化網頁開發工具