使用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屋！