大型mysql表的分区
我有一个大的(40 to )表，我想对其进行分区。行本质上是写的，一次读很多次。目前，我正在file_per_table中使用MySQL5.5Innodb。很难优化这个表，因为重建整个文件需要很长时间。
我希望对表进行分区，以便只有一个分区被写入；我认为这将意味着优化表将花费更少的时间，因为每个非当前文件只需要优化一次，不需要再次访问。
我对mysql中的表分区还很陌生，我不知道该怎么做才是正确的。我知道没有“文件大小”分区方案，所以第二个最好的方法是在将导致文件大小的行范围内进行搜索(3-4GB似乎不错，所以按我们目前的速度，每年大约有3-4个文件)。我的想法是在id上进行分区，但这不能满足文件分区的技术要求(“分区表达式中用于分区表的所有列都必须是表可能拥有的每个唯一键的一部分”)。那么有什么正确的方法去做这件事呢？以下是对重要部分进行裁剪的表格定义：
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| TransactionId | int(11) | NO | MUL | NULL | |
| Parent | int(11) | NO | MUL | 0 | |
| Headers | longtext | YES | | NULL | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+时光飞逝..。我们已经转向使用mysql.com回购，并升级到5.6。是审判的时候了。使用一个较小的表，我尝试使用联机DDL优化。我没有得到预期的结果：
mysql> optimize table Users;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+----------+----------+-------------------------------------------------------------------+
| rt4.Users | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| rt4.Users | optimize | status | OK |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.43 sec)
mysql> desc Users;
+-----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(200) | NO | UNI | NULL | |
| Password | varchar(256) | YES | | NULL | |
| AuthToken | varchar(16) | YES | | NULL | |
| Comments | text | YES | | NULL | |
| Signature | text | YES | | NULL | |
| EmailAddress | varchar(120) | YES | MUL | NULL | |
| FreeformContactInfo | text | YES | | NULL | |
| Organization | varchar(200) | YES | | NULL | |
| RealName | varchar(120) | YES | | NULL | |
| NickName | varchar(16) | YES | | NULL | |
| Lang | varchar(16) | YES | | NULL | |
| EmailEncoding | varchar(16) | YES | | NULL | |
| WebEncoding | varchar(16) | YES | | NULL | |
| ExternalContactInfoId | varchar(100) | YES | | NULL | |
| ContactInfoSystem | varchar(30) | YES | | NULL | |
| ExternalAuthId | varchar(100) | YES | | NULL | |
| AuthSystem | varchar(30) | YES | | NULL | |
| Gecos | varchar(16) | YES | | NULL | |
| HomePhone | varchar(30) | YES | | NULL | |
| WorkPhone | varchar(30) | YES | | NULL | |
| MobilePhone | varchar(30) | YES | | NULL | |
| PagerPhone | varchar(30) | YES | | NULL | |
| Address1 | varchar(200) | YES | | NULL | |
| Address2 | varchar(200) | YES | | NULL | |
| City | varchar(100) | YES | | NULL | |
| State | varchar(100) | YES | | NULL | |
| Zip | varchar(16) | YES | | NULL | |
| Country | varchar(50) | YES | | NULL | |
| Timezone | varchar(50) | YES | | NULL | |
| PGPKey | text | YES | | NULL | |
| Creator | int(11) | NO | | 0 | |
| Created | datetime | YES | | NULL | |
| LastUpdatedBy | int(11) | NO | | 0 | |
| LastUpdated | datetime | YES | | NULL | |
| SMIMECertificate | text | YES | | NULL | |
+-----------------------+--------------+------+-----+---------+----------------+
36 rows in set (0.00 sec)
mysql> select @@VERSION;
+------------+
| @@VERSION |
+------------+
| 5.6.19-log |
+------------+
1 row in set (0.00 sec)我遗漏了什么？
回答 1
Database Administration用户
发布于 2014-05-20 19:52:16
首先，你应该考虑用另一种方式解决这个问题。
- 升级到MySQL 5.6，其中
OPTIMIZE TABLE工作时不阻塞(对于InnoDB表)，因为InnoDB在线DDL支持它。 - 如果无法升级，请尝试使用Percona的pt-online模式更改，它可以在不阻塞的情况下执行表重建。$pt-联机模式-change h=localhost，D=mydatabase，t=mytable --execute -alter=“ENGINE=InnoDB”
如果您无法使用分区，是的，您必须将id作为表中的分区键。可以使用ALTER TABLE将表转换为分区。如果需要非阻塞的转换操作，请使用pt-online模式更改。
无法将分区划分到固定大小的分区。你必须用值来划分。但是，按每个分区设置一个特定的大小真的很重要吗？
关于分区大小的评论：
在使用范围分区时，我所做的是设置一个计划来更改表，并不时地拆分最后一个分区。如果您有一个正常的增长速度，这很容易，但是如果您有不规则的增长模式，您可以设置一个定期检查，检查每个分区的行数(使用信息_SCHEMA.PARTITIONS)，如果已经满了，可以发送电子邮件给您。
例如，让我们在id上设置一个按区域划分的表。
CREATE TABLE `mytable` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`transactionid` int(11) NOT NULL,
`parent` int(11) NOT NULL,
`headers` longtext,
`creator` int(11) NOT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `transactionid` (`transactionid`,`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */随着MAX(id)接近3000，它正接近于填充p3并溢出到p4中。所以是时候重组了。在任何数据溢出到p4之前最好这样做，因为reorg只会影响最后一个空分区，因此会非常快。
ALTER TABLE mytable REORGANIZE PARTITION p4 INTO
(PARTITION p4 VALUES LESS THAN (4000), PARTITION p5 VALUES LESS THAN MAXVALUE);即使您错过了一天，并且在旧的p4中获得了一些数据，但它很可能不是很多数据。但是，如果您忽略了这一点一两个月，并且p4填充了大量的数据，那么重组将花费更长的时间。
https://dba.stackexchange.com/questions/65504
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号