需要一些指导来优化mysql中的报告

BLancast • 5 年前 • 1515 次点击

我的团队维护一个应用程序/数据库,每周处理数百万条记录。这个过程相当简单:

向联系人发送各种活动的通知
发送通知时,将联系人ID、活动ID、消息ID、创建时间、更新时间写入日志
读取每个notificationid/notification\u messageid的记录计数,并在报表中向用户显示。

对日志的写入和读取过程需要非常长的时间,我们正在寻找一种优化它的方法。

在发送通知时发生write语句。它在一个查询中为20条记录批处理插入。下面是一个例子:

INSERT INTO `contact_notification_logs` (`id`, `contact_id`, `campaign_id`, 
`message_id`, `created_at`, `updated_at`, `is_reset`) 
VALUES 
(NULL, '1', '1', '1', '2019-01-23 20:16:21', '2019-01-23 20:16:24', 
'0'),

有两个read语句:

这个非常简单,它运行在列出所有活动的页面上,并显示今天发送的通知的当前计数:

SELECT COUNT(id) FROM contact_notification_logs 
WHERE DATE(created_at) = '[current date]'

这个虽然简单,但执行起来仍然需要很长时间。

第二个read语句有点复杂,因为它内置在应用程序的报告工具中,用户可以在其中指定参数,但根“select count”是相同的。

下面是一个例子:

SELECT COUNT(id) FROM contact_email_logs 
WHERE DATE(created_at) > '2018-12-23'
AND DATE(created_at) < '2019-01-23'
AND campaign_id = 27
AND message_id = 133

还有几点:

数据需要能够实时提取。也就是说,如果我想在这个时间点检查所有通知活动的计数,我可以。所以查询运行以计数所有的时间。
联系人通知日志中有28740585条记录。

我是否遗漏了一些显而易见的东西,使我们能够优化这些查询的运行时间?

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/44152

1515 次点击

文章 [ 2 ] | 最新文章 5 年前

• 1 楼

Rick James 6 年前

无效的日期范围导致检查太多行

WHERE DATE(created_at) > '2018-12-23'
  AND DATE(created_at) < '2019-01-23'
  AND campaign_id = 27
  AND message_id = 133

不要那样写日期比较。它不能使用包含 created_at 因为它隐藏在函数调用中( DATE() )而是:

WHERE created_at >= '2018-12-23'
  AND created_at  < '2018-12-23' + INTERVAL 1 MONTH

如果 数据() 东西是由第三方包生成的,你需要放弃它。

缺乏合适的指标

然后…您需要一个复合索引:

INDEX(campaign_id, message_id,   -- in either order
      created_at)                -- after those

就为了“今天”

SELECT COUNT(*) FROM contact_notification_logs 
    WHERE created_at >= '[current date]'
      AND created_at  < '[current date]' + INTERVAL 1 DAY

INDEX(created_at)  -- the previous index will not help for _this_ query

需要汇总表

有2800万行,你可能会发现我上面的建议是不够的。再提高10倍, build and maintain a Summary Table . 建议使用天,而不是周或月作为解决方案。

其他

不要使用 COUNT(id) 除非你需要检查 id 是 NULL . 相反,使用通用模式: COUNT(*) .

如果 创造在 IS型 DATE ,原始查询是一个月,减去一天。如果是 DATETIME ,则缺少开始日期的午夜。使用我的代码,无论数据类型如何,它都能正常工作。

如需进一步讨论,请提供 SHOW CREATE TABLE .

• 2 楼

Didier 6 年前

对于第一个读取查询: 你在创建的字段上有索引吗?

对于第二个读取查询: 您是否有基于三个字段的索引:created_at、campaign_id和message_id?

如果没有,看看 https://dev.mysql.com/doc/refman/5.5/en/create-index.html

登录后回复