使用Hibernate管理MySQL分区

Question

我们目前正在评估MySQL分区的使用情况，以供我们的小型应用程序使用。该应用程序基本上只是位于消息队列的末尾，并使用Hibernate将我们的API请求(包括时间戳)记录到数据库。不幸的是，我们收到了很多请求，查询数据库变得非常缓慢。

我们想要做的是按时间戳(每个月)对表进行分区，因为我们的常规查询模式是“在时间A和B之间获得某些请求”。如果A和B是连续两个月，这将主要是真的，那么这只会击中两个分区。

由于MySQL的范围分区必须手动创建，所以我想将这个维护任务添加到我们的Java应用程序中，在那里它可以自动完成。这个想法是这样的：

1. 有一个定期运行的实用程序线程(使用ScheduledExecutorService或其他什么)
2. 在线程中，检查下一个月是否有分区。
3. 如果不是，创建它

这一切都很好，但我仍然试图使用Hibernate获取MySQL的分区信息，并创建分区。做这件事的最好方法是什么(如果这是MySQL特定的话，我很好)？

• Hibernate中是否有特定的API来获取表的MySQL分区信息，以及创建分区？
• 我应该使用原始SQL (SHOW CREATE TABLE ...，ALTER TABLE ... ADD PARTITION)并自己解析输出吗？

编辑：

该表如下所示(我删除了一些与问题无关的列)：

CREATE TABLE `request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `apikey` varchar(32) NOT NULL,
  `timestamp` datetime NOT NULL,
  `rows` int(11) DEFAULT NULL,
  `user_id` varchar(15) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `apikey_idx` (`apikey`),
  KEY `timestamp_idx` (`timestamp`),
  KEY `apikey_timestamp_rows_idx` (`apikey`,`timestamp`,`rows`)
) ENGINE=InnoDB AUTO_INCREMENT=2190385211 DEFAULT CHARSET=utf8

缓慢的查询(显然是由Doctrine生成的)：

SELECT 
  r0_.user_id AS user_id0, COUNT(r0_.id) AS sclr1
FROM
  request r0_
WHERE
  r0_.apikey = 'XXX' AND r0_.rows > 0 AND r0_.timestamp >= '2015-09-15 00:00:00' AND r0_.timestamp < '2015-10-15 00:00:00'
GROUP BY r0_.user_id
HAVING sclr1 > 0
ORDER BY sclr1 DESC
LIMIT 500

当EXPLAIN查询时，MySQL说它在使用apikey_timestamp_rows_idx索引。

一个小上下文:对于给定的API密钥，我们想知道每个用户在给定的时间段内发送了多少个rows > 0请求。

该表目前约有22亿行。

Answer 1

我不知道任何处理表分区的hibernate API。

我认为您别无选择，只能使用本机SQL。您可以将SQL保存在Java代码中(正如我认为您建议的那样)，也可以将其放在存储过程中。

您可以使用Java或MySQL对此进行调度。如果您使用应用程序服务器中的线程执行此操作，那么您的每个应用程序服务器都会有这样一个预定的任务。这样就很难控制作业实际执行的频率。在这种情况下，这可能不是什么大问题，因为与分区相关的查询并不是很重。

您还可以在MySQL中调度它(请参阅How to schedule a MySQL query?)。此选项可能会为工作提供更多的可见性(例如，对您的DBA)，并且更容易管理和监视。

Answer 2

我不认为分区会有帮助。你必须扫描很多行，这才是慢的。

KEY `apikey_idx` (`apikey`),
KEY `apikey_timestamp_rows_idx` (`apikey`,`timestamp`,`rows`)

第一个是不需要的，因为第二个。先放下枪。(这将加快插入速度。)

apikey闻起来像某种哈希，是吗？是妖术吗？通过UNHEXing它可以节省大量磁盘空间，并将其存储到二进制(16)中(在所有使用apikey的表中)。(越小->越少I/O ->越快。)

假设行插入后不会更改.我会建立一个“汇总表”来存储

• 日期(来自timestamp)
• rows>0或否
• apikey
• COUNT(*)

从该汇总表中，一个等效的SELECT将运行得更快。

考虑为其他类似的查询构建(并以增量方式维护)汇总表。

我建议Hibernate正在考虑存储和检索数据的最佳方式。