MySQL JSON_EXTRACT 性能

Question

我们有一个日志表，它会随着新事件的发生而增长。目前，我们存储了大约 120,000 行日志事件。

事件表如下所示:

'CREATE TABLE `EVENTS` (
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`EVENT` varchar(255) NOT NULL, 
`ORIGIN` varchar(255) NOT NULL,
`TIME_STAMP` TIMESTAMP NOT NULL, 
`ADDITIONAL_REMARKS` json DEFAULT NULL, 
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=137007 DEFAULT CHARSET=utf8'

Additional_Remarks 是一个 JSON 字段，因为不同的应用程序登录到这个表中，可以为发生的事件添加更多信息。我不想在这里放置任何数据结构，因为这些信息可能不同。例如，一个项目管理应用程序可以记录:

ID, "new task created", "app", NOW(), {"project": {"id": 1}, "creator": {"id": 1}}

虽然其他应用程序没有项目或创建者，但他们希望在 Additional_Remarks 字段中存储猫和所有者。

查询可以使用 Additional_Remarks 字段来过滤某个特定应用程序的信息，例如:

SELECT
DISTINCT(ADDITIONAL_REMARKS->"$.project.id") as 'project',
COUNT(CASE WHEN EVENT = 'new task created' THEN 1 END) AS 'new_task'
FROM EVENTS
WHERE DATE(TIMESTAMP) >= DATE(NOW()) - INTERVAL 30 DAY
AND ORIGIN = "app"
GROUP BY project
ORDER BY new_task DESC
LIMIT 10;

输出 EXPLAIN 查询:

'1', 'SIMPLE', 'EVENTS', NULL, 'ALL', NULL, NULL, NULL, NULL, '136459', '100.00', 'Using where; Using temporary; Using filesort'

通过此查询，我获得了过去 30 天内创建任务最多的前 10 个项目。工作正常，但是随着我们的表的增长，这个查询变得越来越慢。对于 120.000 行，此查询需要超过 30 秒。

你知道有什么提高速度的方法吗？表中具有最高 ID 的最新信息比旧条目更重要。通常我只查找最近 X 天发生的条目。在第一个条目比 where 子句早 X 天后停止查询会很有用，因为所有其他条目甚至更早。

Answer 1

如果 TIME_STAMP 被索引，DATE 函数将不允许使用索引，因为它是不确定的。

WHERE DATE(TIMESTAMP) >= DATE(NOW()) - INTERVAL 30 DAY

可以重写为。

 WHERE TIMESTAMP >= UNIX_TIMESTAMP(DATE(NOW()) - INTERVAL 30 DAY)

Do you know any way to improve the speed?

我能看到加速查询的唯一方法是多列索引 TIMESTAMP 和 ORIGIN 像这样>

编辑

交付表可能会提高查询速度，因为它将使用新索引。

SELECT 
  ADDITIONAL_REMARKS->"$.project.id" AS 'project',
  COUNT(CASE WHEN EVENT = 'new task created' THEN 1 END) AS 'new_task'
FROM ( 

  SELECT 
   *
  FROM EVENTS 
  WHERE
    TIME_STAMP >= UNIX_TIMESTAMP(DATE(NOW()) - INTERVAL 30 DAY)
  AND
    ORIGIN = "app"
) 
 AS events_within_30_days

GROUP BY project
ORDER BY new_task DESC
LIMIT 10;