主页 / dba / 问题 / 65504
Accepted
Ram
Asked: 2014-05-21 11:02:11 +0800 CST

对大型mysql表进行分区

  • 772

我有一个大 (40GB) 表我想分区。这些行基本上是一次写入多次读取。目前我正在使用带有file_per_table的mysql 5.5 innodb。由于重建整个文件需要很长时间，因此很难优化此表。

我希望对表进行分区，以便只写入一个分区；我认为这意味着优化表格将花费更少的时间，因为每个非当前文件将只优化一次，无需再次触及。

我是 mysql 中的表分区的新手，我不确定这样做的正确方法是什么。我知道没有“文件大小”分区方案，所以下一个最好的事情是在行范围内进行 SWAG，这将导致我更喜欢的文件大小（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 repo 并升级到 5.6。是时候试一试了。使用较小的表，我尝试使用 Online 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)

我错过了什么？

mysql partitioning

1 个回答

  1. Best Answer

    首先，您应该考虑以另一种方式解决问题。

    • 升级到 MySQL 5.6，它可以在OPTIMIZE TABLE没有阻塞的情况下工作（对于 InnoDB 表），因为它受InnoDB Online DDL支持。

    • 如果无法升级，可以尝试使用 Percona Toolkit 的 pt-online-schema-change，它可以无阻塞地进行表重建。

      $ pt-online-schema-change h=localhost,D=mydatabase,t=mytable --execute
    --alter="ENGINE=InnoDB"

    如果您坚持使用分区，是的，您必须id在您显示的表中创建分区键。您可以使用 将表转换为分区ALTER TABLE。如果您需要转换操作是非阻塞的，请使用 pt-online-schema-change。

    无法分区为固定大小的分区。您必须按值进行分区。但是，每个分区达到特定大小真的那么重要吗？

    重新评论您对分区大小的评论：

    使用 RANGE 分区时，我所做的就是设置一个时间表来 ALTER TABLE 并不时拆分最后一个分区。如果您有固定的增长率，这很容易，但如果您有不规则的增长模式，您可以改为设置定期检查来检查每个分区的行数（使用INFORMATION_SCHEMA.PARTITIONS），如果它快满了。

    例如，让我们设置一个按 range on 分区的表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，因为重组只会影响最后一个空分区，因此会非常快。

    ALTER TABLE mytable REORGANIZE PARTITION p4 INTO 
(PARTITION p4 VALUES LESS THAN (4000), PARTITION p5 VALUES LESS THAN MAXVALUE);

    即使您错过了一天并且将一些数据放入旧的p4，也可能没有太多数据。但是如果你一两个月忽视这一点，并且p4填满了大量数据，那么 REORGANIZE 将花费更长的时间。

    • 8

相关问题