关键词：
【中文标题】MySQL查询太慢了【英文标题】：MySQL query too much slow 【发布时间】：2019-05-14 23:21:59 【问题描述】：我正在尝试查询以获取一些趋势统计数据，但基准测试真的很慢。查询执行时间约为 134 秒。
我有一个名为 table_1 的 MySQL 表。
在create语句下方
CREATE TABLE `table_1` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`original_id` bigint(11) DEFAULT NULL,
`invoice_num` bigint(11) DEFAULT NULL,
`registration` timestamp NULL DEFAULT NULL,
`paid_amount` decimal(10,6) DEFAULT NULL,
`cost_amount` decimal(10,6) DEFAULT NULL,
`profit_amount` decimal(10,6) DEFAULT NULL,
`net_amount` decimal(10,6) DEFAULT NULL,
`customer_id` bigint(11) DEFAULT NULL,
`recipient_id` text,
`cashier_name` text,
`sales_type` text,
`sales_status` text,
`sales_location` text,
`invoice_duration` text,
`store_id` double DEFAULT NULL,
`is_cash` int(11) DEFAULT NULL,
`is_card` int(11) DEFAULT NULL,
`brandid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_registration_compound` (`id`,`registration`)
) ENGINE=InnoDB AUTO_INCREMENT=47420958 DEFAULT CHARSET=latin1;
我设置了一个由id+registration组成的复合索引。
在查询下方
SELECT
store_id,
CONCAT('[',GROUP_CONCAT(tot SEPARATOR ','),']') timeline_transactions,
SUM(tot) AS total_transactions,
CONCAT('[',GROUP_CONCAT(totalRevenues SEPARATOR ','),']') timeline_revenues,
SUM(totalRevenues) AS revenues,
CONCAT('[',GROUP_CONCAT(totalProfit SEPARATOR ','),']') timeline_profit,
SUM(totalProfit) AS profit,
CONCAT('[',GROUP_CONCAT(totalCost SEPARATOR ','),']') timeline_costs,
SUM(totalCost) AS costs
FROM (select t1.md,
COALESCE(SUM(t1.amount+t2.revenues), 0) AS totalRevenues,
COALESCE(SUM(t1.amount+t2.profit), 0) AS totalProfit,
COALESCE(SUM(t1.amount+t2.costs), 0) AS totalCost,
COALESCE(SUM(t1.amount+t2.tot), 0) AS tot,
t1.store_id
from
(
SELECT a.store_id,b.md,b.amount from ( SELECT DISTINCT store_id FROM table_1) AS a
CROSS JOIN
(
SELECT
DATE_FORMAT(a.DATE, "%m") as md,
'0' as amount
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) month as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date >='2019-01-01' and a.Date <= '2019-01-14'
group by md) AS b
)t1
left join
(
SELECT
COUNT(epl.invoice_num) AS tot,
SUM(paid_amount) AS revenues,
SUM(profit_amount) AS profit,
SUM(cost_amount) AS costs,
store_id,
date_format(epl.registration, '%m') md
FROM table_1 epl
GROUP BY store_id, date_format(epl.registration, '%m')
)t2
ON t2.md=t1.md AND t2.store_id=t1.store_id
group BY t1.md, t1.store_id) AS t3 GROUP BY store_id ORDER BY total_transactions desc
下面的解释
也许我应该将 registration 列中的 timestamp 更改为 datetime？
【问题讨论】：
出于好奇，这个表目前在您的系统中有多少行？猜测大约 48m？ @ChrisForrence 是的，或多或少 4800 万行。 您应该检查您的 MySQL 性能设置。 - innodb_buffer_pool_size 是用于缓存表、索引和其他一些东西的内存量。 - 您可以在 MySQL 中配置多个 innodb_buffer_pool_instances 以增加读/写线程。尝试从查询中删除 order by 或设置正确的索引。更多详情参考网站：1.percona.com/blog/2014/01/28/… 2.saotn.org/mysql-innodb-performance-improvement 【参考方案1】：大约 90% 的执行时间将用于执行 GROUP BY store_id, date_format(epl.registration, '%m')。
很遗憾，您不能使用索引来指向group by 派生值，由于这对您的报告至关重要，因此您需要预先计算。您可以通过将该值添加到表中来做到这一点，例如使用生成的列：
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 和您的日期范围where a.Date >='2019-01-01' and a.Date <= '2019-01-14' 可能不会像您认为的那样。你应该单独run the partSELECT DATE_FORMAT(a.DATE, "%m") as md, ... group by md 看看它做了什么。在目前的状态下，它会给你一个带有元组'01', 0的行，代表“一月”，所以它基本上是一种复杂的select '01', 0的处理方式。除非今天是 15 号或更晚，否则它不会返回任何内容（这可能是无意的）。
特别是，它将不将发票日期限制在该特定范围内，而是限制从任何一年（整个）1 月开始的所有发票。如果这是您想要的，您应该（另外）直接添加该过滤器，例如通过使用FROM table_1 epl where 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 ...
您可能（或可能没有）尝试过这样做。不过，在这种情况下，您将需要一个不同的索引（这将是一个稍微不同的问题）。
1234563子查询只能为您提供 1 到 12 的值，因此可以简化生成 1000 个日期并再次减少它们。但由于它们在 100 多行上运行，它们不会显着影响执行时间，我还没有详细检查这些。其中一些可能是由于获得了正确的输出格式或概括（尽管，如果您按月以外的其他格式动态分组，则需要其他索引/列，但这将是一个不同的问题）。预先计算值的另一种方法是汇总表，例如每天运行一次您的内部查询（昂贵的group by）并将结果存储在一个表中，然后重用它（通过从该表中选择而不是分组）。这对于像发票这样永不改变的数据尤其可行（尽管否则您可以使用触发器来保持汇总表的最新状态）。如果你有几个场景，它也会变得更加可行，例如如果您的用户可以决定按工作日、年份、月份或十二生肖分组，否则您需要为每个人添加一个索引。如果您需要动态限制发票范围（例如 2019-01-01 ... 2019-01-14），它变得不太可行。如果您需要在报告中包含当天，您仍然可以预先计算然后从表中添加当前日期的值（这应该只涉及非常有限的行数，如果您有一个以您的日期列），或使用触发器即时更新您的汇总表。
【讨论】：
嗨@Solarflare，它是 MySQL 5.6.10。在这种情况下我应该如何添加触发器？考虑到所有建议，您能告诉我查询的最终输出吗？ 您可以使用原始查询，只需替换按列分组（我忘了提及，所以我现在明确添加了那部分代码）。备注只是备注。特别是如果您当前的查询返回正确的结果，而且速度很慢，您可以忽略其中的大多数（尽管第一个可以，如果您实际上只打算显示一月份的数据，请提供额外的减速带）；但是按md 而不是 date_format(... 单独分组应该已经显着减少了您的执行时间（如果您有一个 ssd，大约 5-10 秒，对于硬盘可能是 30-40 秒）。
@UgoL 我详细说明了该日期范围部分，因为它可能不是您想要的（您可能从fill in missing dates-question 复制了该代码），因此请检查您当前的查询是否真的为您提供预期结果（这是此优化的先决条件）。
感谢@Solarflare 的所有这些建议。我试图 ALTER 表并设置 md 字段，但它似乎没有尽头。【参考方案2】：
使用PRIMARY KEY(id)，拥有INDEX(id, anything) 几乎没有用处。
看看是否可以避免嵌套子查询。
考虑永久构建“日期”表并在其上添加PRIMARY KEY(md)。目前，两个子查询在连接列 (md) 上都没有索引。
您可能患有“爆炸-内爆”综合症。这是JOINs 展开行数的地方，只是为了让GROUP BY 折叠它们。
不要使用COUNT(xx)，除非您需要检查xx 是否为NULL。只需COUNT(*)。
store_id double -- 真的吗？
TIMESTAMP 与 DATETIME -- 他们的表现大致相同；不要费心去改变它。
既然你只看2019-01，那就去掉
date_format(epl.registration, '%m')
仅此一项，可能会大大加快速度。 （但是，您失去了一般性。）
【讨论】：
您对避免子查询有什么建议吗？您确定在性能方面计算 (*) 是个好主意吗？ @ugol -COUNT(*) 等价于 COUNT(1) 并且比 COUNT(x) 快。不要被* 所迷惑，它在这种情况下并不意味着“所有列”。MySQL UNION ALL 太慢了
...9-12-0203:42:49【问题描述】：我在MySQL中有一个来自4个表的查询。当我单独运行这些查询时，它们会在1秒内快速运行。但是当我使用UNIONALL组合它们时，机器会承受重负载，并且在UNIONALL中执行相同查询至少需要10秒。而且我已经... 查看详情
优化我的mysql语句！ - 兰德（）太慢了
】优化我的mysql语句！-兰德（）太慢了【英文标题】：Optimizingmymysqlstatement!-RAND()TOOSLOW【发布时间】：2010-12-0808:17:51【问题描述】：所以我有一个包含超过80,000条记录的表，这个表称为系统。我还有另一个名为follow的表。我需要... 查看详情
Ucanaccess 太慢了
...dbdjackcess-2.1.3我的数据库是100MB。仅使用Jacksess时，相同的查询需要4-5秒，而这一查询大约需要1-2分钟。我做了一些研 查看详情
为啥这个查询太慢了？获取 A 的列表，其中每个项目都有一个关联的外键 B
】为啥这个查询太慢了？获取A的列表，其中每个项目都有一个关联的外键B【英文标题】：Whyisthisquerytooslow?GettinglistofAwithoneassociatedforeign-keyedBforeachitem为什么这个查询太慢了？获取A的列表，其中每个项目都有一个关联的外键B【... 查看详情
setFormulaArray 太慢了
】setFormulaArray太慢了【英文标题】：setFormulaArraytooslow【发布时间】：2021-06-0807:02:33【问题描述】：我正在尝试编写一个python包装器，该包装器旨在从csv（或mysql）读取多个记录并更新工作表预定义范围的值，该范围由值单元格和... 查看详情
SqlDataAdapter.Fill 即使是一条记录也太慢了
...有主键的表，它有几十万条记录。当我在ManagementStudio中查询它以获取记录时，它带来的速度非常快，但是当我使用下面的代码查找它时，它需要很多秒。我必须使用数据集，因为我 查看详情
如果mysql里面的数据过多，查询太慢怎么办？
...：可以看到MySQL将selectfromAwhereA.xnotin(selectxfromB)//非关联子查询转换成了selectfromAwherenotexists(select1fromBwhereB.x=a.x)//关联子查询如果我们自己是MySQL，在执行非关联子查询时，可以使用很简单的策略：selectfromAwhereA.xnotin(selectxfromBwhere..... 查看详情
Linq to Entity Paging with large dataset太慢了
...OrderBy与skip/take一起使用需要的时间太长（即使是较小的查询也需要20多分钟）。这 查看详情
sqlmap太慢了
】sqlmap太慢了【英文标题】：sqlmapistooslow【发布时间】：2012-07-0506:46:16【问题描述】：这是一个例子。只是想列出数据库：pythonsqlmap.py-u"http://somesite.com/?id=1"--dbs[15:20:32][INFO]fetchingdatabasenames[15:20:32][INFO]fetchingnumberofdatabases[... 查看详情
for循环太慢了
】for循环太慢了【英文标题】：Forloopistooslow【发布时间】：2016-01-0113:01:38【问题描述】：我有以下代码：varCombinatorics=require(\'js-combinatorics\');varfs=require(\'fs\');cp=Combinatorics.cartesianProduct(["4","@","/\\\\","/-\\\\","^","∂","λ","α","(!" 查看详情
iPad 中的洪水填充太慢了
】iPad中的洪水填充太慢了【英文标题】：FloodfillinginiPadtooslow【发布时间】：2011-05-0901:19:28【问题描述】：我在iPad上为我的一个着色应用程序使用洪水填充。该应用程序基本上在图像的黑线内填充颜色，我可以毫无问题地做到这... 查看详情
InAppReview : SKStoreReviewController 太慢了
】InAppReview:SKStoreReviewController太慢了【英文标题】：InAppReview:SKStoreReviewControllerSoSlow【发布时间】：2017-09-2213:03:59【问题描述】：使用SKStoreReviewController进行inAppReview需要时间，直到出现提示，有什么方法可以让它显示得更快？另... 查看详情
插入 WStandardItemModel 太慢了
】插入WStandardItemModel太慢了【英文标题】：InsertinginaWStandardItemModelistooslow【发布时间】：2011-07-0417:59:53【问题描述】：我正在开发一个基于WT构建的应用程序。我们遇到了性能问题，因为它必须在与WStandardItemModel关联的WTableView中... 查看详情
相关子查询的更新语句太慢
】相关子查询的更新语句太慢【英文标题】：UpdatestatementistooslowwithCorrelatedsubquery【发布时间】：2018-06-2506:51:19【问题描述】：我正在使用下面的更新语句，它太慢了（大约需要10-12秒）UPDATEtemp_finaltSETname=(SELECTDISTINCT(nm.name)FROMtabl... 查看详情
postgresql全文搜索查询太慢
】postgresql全文搜索查询太慢【英文标题】：postgresqlfulltextsearchquerytooslow【发布时间】：2017-10-2707:56:27【问题描述】：一张表有两条sql，一条太慢了，谁能告诉我哪里出了问题，如何优化？有他们的查询解释打击：[---------编辑----... 查看详情
为啥使用 information_schema 太慢了？
】为啥使用information_schema太慢了？【英文标题】：Whyisusinginformation_schematoomuchslow?为什么使用information_schema太慢了？【发布时间】：2017-02-2607:34:33【问题描述】：这是我的代码：//dbssize$sql=\'SELECTtable_schemaDB_Name,Round(Sum(data_length+inde... 查看详情
获取数百万条记录太慢了
】获取数百万条记录太慢了【英文标题】：FetchingMillionsRecordsgoingtooslow【发布时间】：2018-07-3010:47:29【问题描述】：我正在尝试使用带有Fetching的聚集索引复制其他表中的6000万条记录。但是在2000万张唱片之后，它变得太慢了。我... 查看详情
导航到包含 ScrollView 的 ViewController 太慢了
】导航到包含ScrollView的ViewController太慢了【英文标题】：NavigatingtoViewControllercontainingScrollViewGoingtooSlow【发布时间】：2016-01-1807:55:50【问题描述】：这就是从ViewControllerA推送到ViewControllerB的方式UIStoryboard*mainStory=[UIStoryboardstoryboardW... 查看详情