我可以帮您翻译成中文：我能否设置MySQL自动分区？

Question

我可以帮您翻译成中文：我能否设置MySQL自动分区？

5

我希望对一张非常大的表进行分区。随着业务的增长，按日期进行分区并不是很好，因为每年的分区会变得越来越大。我真正想要的是每1000万条记录分一个区。

MySQL手册中给出了这个简单的例子：

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

但这意味着所有大于16且小于MAXVALUE的内容都会被放在最后一个分区。有没有一种方法可以自动生成新的分区，每隔一定间隔（在我的情况下是1000万条记录），这样我就不必不断地修改活动数据库了？我正在运行Mysql 5.5。

谢谢！

编辑：这是我的实际表格。

CREATE TABLE `my_table` (
`row_id` int(11) NOT NULL AUTO_INCREMENT,
`filename` varchar(50) DEFAULT NULL,
`timestamp` datetime DEFAULT NULL,
`unit_num` int(3) DEFAULT NULL,
`string` int(3) DEFAULT NULL,
`voltage` float(6,4) DEFAULT NULL,
`impedance` float(6,4) DEFAULT NULL,
`amb` float(6,2) DEFAULT NULL,
`ripple_v` float(8,6) DEFAULT NULL,
 PRIMARY KEY (`row_id`),
 UNIQUE KEY `timestamp` (`timestamp`,`filename`,`string`,`unit_num`),
 KEY `index1` (`filename`),
 KEY `index2` (`timestamp`),
 KEY `index3` (`timestamp`,`filename`,`string`),
 KEY `index4` (`filename`,`unit_num`)
 ) ENGINE=MyISAM AUTO_INCREMENT=690892041 DEFAULT CHARSET=latin1

并且一个用于图形查询的示例是...

SELECT DATE_FORMAT(timestamp,'%Y/%m/%d %H:%i:%s') as mytime,voltage,impedance,amb,ripple_v,unit_num 
FROM my_table WHERE timestamp >= DATE_SUB('2015-07-31 00:05:59', INTERVAL 90 DAY) 
AND filename = 'dlrphx10s320upsab3' and unit_num='5' and string='2'ORDER BY timestamp asc;

以下是针对该查询的解释...

mysql> explain SELECT DATE_FORMAT(timestamp,'%Y/%m/%d %H:%i:%s') as mytime,voltage,impedance,amb,ripple_v,unit_num FROM my_table WHERE timestamp >= DATE_SUB('2015-07-31 00:05:59', INTERVAL 90 DAY) AND filename = 'dlrphx10s320upsab3' and unit_num='5' and string='2'ORDER BY timestamp asc;
+----+-------------+------------+------+-------------------------+--------+---------+-------------+-------+----------------------------------------------------+
| id | select_type | table      | type | possible_keys           | key    | key_len | ref         | rows  | Extra                                              |
+----+-------------+------------+------+-------------------------+--------+---------+-------------+-------+----------------------------------------------------+
|  1 | SIMPLE      | unit_tarma | ref  | timestamp,index3,index4 | index4 | 58      | const,const | 13440 | Using index condition; Using where; Using filesort | 
+----+-------------+------------+------+-------------------------+--------+---------+-------------+-------+----------------------------------------------------+

- Doug Wolfgram

你的名字字段被硬性限制在30个字符以内？是时候重新评估你的假设了。另外据我所知，添加PARTITION需要修改模式，但最好由其他人确认一下。 - tadman

1

您可以创建存储过程来更改表以创建新的分区，以满足您的需求。创建存储过程后，您可以使用MySQL事件调度程序，在特定时间间隔后执行该过程。通过这种方式，您可以实现动态自动分区。 - Abhishek Ginani

LOL，tadman。我说那是Mysql提供的示例。 :) - Doug Wolfgram

2个回答

0

首先，我必须问一下分区对你有什么好处？是否有某些查询因此而运行得更快？

没有自动分区。

相反，您应该有一个每天运行的作业，它计算“最后活动”分区中的行数，以查看是否约为10M。如果是这样，请添加另一个分区。

我建议保持“最后”分区（具有MAXVALUE的分区）为空。这样，您可以REORGANIZE PARTITION将其拆分为两个空分区，几乎没有开销。我建议使用ADD PARTITION而不是，因为您可能会在最后一个分区中放置一些内容。

不清楚什么会触发10M。每个store_id都有多行吗？每个店铺都有新行进来吗？如果是这样，那么分区存储store_id，因为所有分区都会一直增长。

好的，所以store_id只是参考手册中的一个无聊的例子。请提供SHOW CREATE TABLE，以便我们可以谈论具体问题，而不是模糊的描述。有太多的方法可以完成这项任务。

这是什么活动？

如果您主要访问“最近”的分区，那么可能需要进行不均匀分布--定期添加一个新的分区并合并相邻的两个旧分区。（我在一个系统中成功地实现了这一点。）

如果您将清除“旧”数据，则明显需要使用PARTITION BY RANGE(TO_DAYS(...))并使用DROP PARTITION加上REORGANIZE PARTITION。

还有很多其他情况。但是我只知道4种情况下分区提供任何性能优势。请参见我的博客。

- Rick James

我有超过6亿行数据，通常每次提取10万行用于绘图。90%的情况下，这10万行数据来自最后5千万行中的某个位置。这样做会更快吗？ - Doug Wolfgram

这要看情况。我没有看到任何索引——它们对性能至关重要。你使用什么SELECT语句？从中，我应该能够推荐一个INDEX并说明分区是否有帮助。 - Rick James

另外... id 字段是从哪里来的？该表的某些列组合是否是唯一的？ - Rick James

以上示例不是实际表格。我添加了我的表的show create table结果。 - Doug Wolfgram

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rick James · Accepted Answer

（此答案针对模式和SELECT。）

由于您预计有数百万行，因此首先我想指出一些改进模式的方法。

- FLOAT(m,n)通常是“错误”的做法，因为它会引起两个圆整。要么使用普通的FLOAT（对于诸如电压之类的度量标准似乎是“正确”的），要么使用DECIMAL(m，n)。 FLOAT是4字节；在给定的情况下，DECIMAL将是3或4字节。

- 当您同时拥有INDEX(a)和INDEX(a，b)时，前者是不必要的，因为后者可以涵盖此类情况。您有3个不必要的键。这会减慢INSERTS。

- INT(3)——您是否在说“3位数”？如果是，请考虑TINYINT UNSIGNED（值0..255）而非4字节的INT。这将节省许多MB的磁盘空间，因此速度更快。（还请参见SMALLINT等以及SIGNED或UNSIGNED。）

- 如果文件名重复很多次，则可以“规范化”它。这将节省许多MB。

- 除非您需要NULL进行某些操作，否则请使用NOT NULL。

- AUTO_INCREMENT=690892041意味着您已经接近灾难的1/3，因为id将达到约20亿。您是否对ID进行了使用？去掉该列可以避免这个问题。并将UNIQUE KEY更改为PRIMARY KEY。（如果您确实需要ID，请让我们进一步讨论。）

- ENGINE=MyISAM——切换会产生一些有利和不利的影响。表格会变得两至三倍大。正确选择的主键将进一步显着加快此SELECT的速度。（而可能会减慢其他SELECTs。）

关于SELECT的说明：由于string和unit_num在查询中是常量，因此ORDER BY timestamp asc, string asc, unit_num asc的最后两个字段是不必要的。如果它们对于在SELECT中不明显的原因而相关，则我的建议可能是不完整的。

WHERE filename = 'foobar'
  AND unit_num='40'
  AND string='2' 
  AND timestamp >= ...

优化处理此类问题最适用的方法是使用INDEX(filename, unit_name, string, timestamp)。列的顺序不重要，但timestamp必须位于末尾。重新排列当前的UNIQUE键可得到最优索引。然而，目前的任何索引都对此SELECT查询性能没有太大帮助。将其作为PRIMARY KEY并使表成为InnoDB类型可以进一步提高速度。

分区？没有优势。不管考虑性能还是其他方面，都没有任何好处。分区的常见用途是清除“旧”数据。如果您打算这样做，请再与我们详细讨论。

在处理大型表时，最好同时考虑所有重要的SELECT查询，以免在加速一个查询的同时破坏其他查询的速度。在这种权衡中，分区甚至可能有所帮助。