使用mysql中的日期类型字段优化查询 [英] optimize query with date type field in mysql
问题描述
我目前准备了以下查询:
I currently have the following query prepared:
select sum(amount) as total
from incomes
where (YEAR(date) = '2019' and MONTH(date) = '07')
and incomes.deleted_at is null
在稍微回顾一下它时,请注意表中包含大量数据花费的时间太长,因为它经历了所有这一切.我对优化查询了解不多,但是我想开始记录和研究这种情况,请阅读一点说明,尽管可以为日期类型字段创建一个
将不会使用索引.这是正确的吗?我应该遵循哪些步骤来改善其性能?我应该尝试重组查询吗? index
一旦 WHERE
子句的列用函数包装,在这种情况下, YEAR
和 MONTH
,MySQL
when reviewing it a bit, notice that it takes too long to have a lot of data in the table, since it goes through all this. I do not know much about optimizing queries, but I want to start documenting and researching for this case, reading a little note that although it is possible to create an index
for a date type field, MySQL
will not use an index once a column of the WHERE
clause is wrapped with a function in this case YEAR
and MONTH
. So is this correct? What steps should I follow to improve its performance? Should I try to restructure my query?
推荐答案
我建议将查询编写为:
select sum(i.amount) as total
from incomes i
where i.date >= '2019-07-01' and
i.date < '2019-08-01' and
i.deleted_at is null;
此查询可以利用收入(已删除的日期,日期,金额)
上的索引:
This query can take advantage of an index on incomes(deleted_at, date, amount)
:
create index idx_incomes_deleted_at_date_amount on incomes(deleted_at, date, amount)
这篇关于使用mysql中的日期类型字段优化查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!