优化数据库查询

Question

我使用的是的MySQL数据库，它是MySQL的旧版本，由于某些原因不能更新为最新版本。

我创建了这个查询来获取当前年份的月份记录。这个查询在我的本地系统上运行得很好，我有大约10,000条记录，但当我到达生产服务器时，它也很好，但是在一个月后，记录在那个表中达到了100万，所以查询需要超过10秒，当应用上有巨大的流量时，它会使服务器变慢，mysql占用高CPU。但是当记录较少时，查询工作正常，否则查询需要花费大量时间来返回记录，有时大约需要25-26秒。

这是我的查询

SELECT SUM(`coins`) as coins, subscription_id 
FROM `user_rewards` `t` 
WHERE YEAR(date_awarded) = YEAR(CURRENT_DATE) 
  AND MONTH(date_awarded) = MONTH(CURRENT_DATE) 
GROUP BY subscription_id 
ORDER BY coins DESC LIMIT 10

是否有可能以更好的方式优化这个查询？

这是我的user_rewards表

id, subscription_id, coins, date_awarded

一个subscription_id可以有多个记录，这就是为什么我在我的组中按组和做组的原因.

下面是表架构。

CREATE TABLE `user_rewards` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date_awarded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `subscription_id` bigint(20) NOT NULL,
 `coins` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_sub_id` (`subscription_id`),
 CONSTRAINT `fk_sub_id` FOREIGN KEY (`subscription_id`) REFERENCES `subscription_info` (`subscription_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12907 DEFAULT CHARSET=latin1

Answer 1

第一个可见的改进(假设未来没有date_awarded值)是

WHERE date_awarded >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

第二，(subscription_id, date_awarded, coins)的索引(或者(date_awarded, subscription_id, coins)，取决于表统计，必须进行测试)。