mysql bigint查询慢_MySQL查询太慢

大约90％的执行时间将用于执行GROUP BY store_id,date_format(epl.registration,’％m’).

不幸的是,您不能使用索引对派生值进行分组,并且由于这对报表至关重要,因此您需要预先计算.您可以通过将该值添加到表中来实现此目的,例如使用生成的列：

alter table table_1 add md varchar(2) as (date_format(registration, '%m')) stored

我在这里保留了该月份使用的varchar格式,也可以在该月份中使用数字(例如tinyint).

这需要MySQL 5.7,否则您可以使用触发器来实现相同的目的：

alter table table_1 add md varchar(2) null;

create trigger tri_table_1 before insert on table_1

for each row set new.md = date_format(new.registration,'%m');

create trigger tru_table_1 before update on table_1

for each row set new.md = date_format(new.registration,'%m');

然后添加一个索引,最好是覆盖索引,以store_id和md开头,例如

create index idx_table_1_storeid_md on table_1

(store_id, md, invoice_num, paid_amount, profit_amount, cost_amount)

如果您还有其他类似的报告,则可能要检查它们是否使用其他列,并可以从覆盖更多列中受益.该索引将需要约1.5GB的存储空间(而驱动器读取1.5GB所需的时间基本上将由一手来定义您的执行时间,而不会缓存).

然后将您的查询更改为按此新的索引列分组,例如

...

SUM(cost_amount) AS costs,

store_id,

md -- instead of date_format(epl.registration, '%m') md

FROM table_1 epl

GROUP BY store_id, md -- instead of date_format(epl.registration, '%m')

)t2 ...

该索引还将处理执行时间的其他9％SELECT DISTINCT store_id FROM table_1,这将从以store_id开头的索引中受益.

现在您的查询已处理了99％,下面进一步说明：

>子查询b和您的日期范围,其中a.Date> =’2019-01-01’和a.Date< ='2019-01-14'可能不会执行您认为的操作.您应该将run the part SELECT DATE_FORMAT(a.DATE,“％m”)作为md,…按md分别分组以查看其作用.在当前状态下,它将给您一行包含元组’01’的行,0代表“一月”,因此基本上是选择’01’的元组的一种复杂方式,0.除非今天是15号或更高版本,否则它不返回任何内容(这可能是意外的).

>特别是,它不会将发票日期限制在该特定范围内,而是会限制来自任何一年(整个)一月的所有发票.如果您打算这样做,则应该(另外)直接添加该过滤器,例如通过使用FROM table_1 epl,其中epl.md =’01’GROUP BY …,将执行时间减少了大约12倍.因此(除了第15个和上一个问题),使用您当前的范围如果使用相同的结果

...

SUM(cost_amount) AS costs,

store_id,

md

FROM table_1 epl

WHERE md = '01'

GROUP BY store_id, md

)t2 ...

对于不同的日期范围,您必须调整该字词.为了强调我的观点,这与按日期过滤发票(例如,按日期

...

SUM(cost_amount) AS costs,

store_id,

md

FROM table_1 epl

WHERE epl.registration >='2019-01-01'

and epl.registration <= '2019-01-14'

GROUP BY store_id, md

)t2 ...

您可能(也可能未)尝试这样做.在这种情况下,您将需要一个不同的索引(这将是一个稍微不同的问题).

>在其余的查询中可能还有一些其他的优化,简化或美化功能,例如,BY BY组t1.md,t1.store_id看起来是多余的和/或错误的(表明您实际上不在MySQL 5.7上),以及b子查询只能给您1到12的值,因此可以简化生成1000个日期并再次减少它们的时间.但是,由于它们在100多个行上进行操作,因此它们不会显着影响执行时间,我也没有详细检查它们.其中一些可能是由于获得了正确的输出格式或归纳(尽管,如果按月以外的其他格式动态分组,则需要其他索引/列,但这将是另一个问题).

预先计算值的另一种方法是汇总表,例如每天运行一次内部查询(昂贵的分组依据),并将结果存储在表中,然后重用(通过从该表中选择而不是进行分组依据).这对于永不更改的数据(如发票)特别可行(尽管否则,您可以使用触发器使汇总表保持最新状态).如果您有多种情况(例如,如果您的用户可以决定按工作日,年,月或生肖分组,否则您将需要为每个索引添加索引.如果您需要动态限制发票范围(例如2019-01-01 … 2019-01-14),它将变得不太可行.如果您需要在报表中包括当前日期,您仍然可以预先计算,然后从表中添加当前日期的值(该表只包含非常有限的行数,如果索引开头为您的日期列),或使用触发器即时更新汇总表.