文章/答案/技术大牛

发布

社区首页 >问答首页 >SQL查询占用的临时表时间太长

问SQL查询占用的临时表时间太长
EN

Stack Overflow用户

提问于 2016-11-03 02:01:07

回答 3查看 93关注 0票数 1

我在MySQL上的sql查询遇到了问题，它运行的方式太长了。将非常感谢您在如何优化它方面的帮助。

所讨论的查询在一个大约有8000行的表上运行，如下所示：

price_id int(5) Primary Key with auto increment
product_id int(5) 
price_amount float
price_date date

基本上，它保存了几种产品的历史价格，因此您可以生成与任何历史日期相关的发票。

这个有问题的查询必须给出与某个日期相关的产品列表的最新价格。例如，要获取9月1日product_id 1、2和4的相关价格，则为：

SELECT * 
FROM prices
WHERE price_id IN (
     SELECT max(prices.price_id) 
     FROM prices
     WHERE product_id in (1,2,4) AND price_date <= '2016-09-01' 
     GROUP BY product_id
)

现在，此查询针对aaaaaages运行。它视情况而定，但现在，例如，对于3 product_ids，它需要大约58秒才能运行。通常发票中有大约120个产品(需要几分钟来返回结果)，所以你可以想象到麻烦。

我在phpmyadmin中分析了查询，基本上看起来是这样的：

Status                        Time 
Sorting result                0.000050 
Sending data                  0.000025 
executing                     0.000009 
Copying to tmp table          0.008586 
Sorting result                0.000052 
Sending data                  0.000026 
executing                     0.000006 
Copying to tmp table          0.008118 
Sorting result                0.000057 
Sending data                  0.000029 
executing                     0.000011 
Copying to tmp table          0.007498 
Sorting result                0.000047 
Sending data                  0.000021 
executing                     0.000005 
Copying to tmp table          0.008479 
Sorting result                0.000056 
Sending data                  0.000031 
executing                     0.000011 
Copying to tmp table          0.007371 
Sorting result                0.000059 
Sending data                  0.000031 
executing                     0.000011 
Copying to tmp table          0.006702 
Sorting result                0.000045 
Sending data                  0.000019 
executing                     0.000005 
Copying to tmp table          0.005319 
Sorting result                0.000034 
Sending data                  0.000015 
executing                     0.000005 
Copying to tmp table          0.005302 
Sorting result                0.000035 
Sending data                  0.000016 
executing                     0.000005 
Copying to tmp table          0.005207 
Sorting result                0.000031 
Sending data                  0.000014 
executing                     0.000005 
Copying to tmp table          0.005243 
Sorting result                0.000034 
Sending data                  0.000016 
executing                     0.000005 
Copying to tmp table          0.005236 
Sorting result                0.000035 
Sending data                  0.000016 
executing                     0.000005 
Copying to tmp table          0.005185 
Sorting result                0.000035 
Sending data                  0.000015 
executing                     0.000005 
Copying to tmp table          0.005256 
Sorting result                0.000033 
Sending data                  0.000017 
executing                     0.000005 
Copying to tmp table          0.005160 
Sorting result                0.000025 
Sending data                  0.000014 
executing                     0.000005 
Copying to tmp table          0.005149 
Sorting result                0.000024 
Sending data                  0.000013 
executing                     0.000005 
Copying to tmp table          0.005356 
Sorting result                0.000038 
Sending data                  0.000016 
executing                     0.000005 
Copying to tmp table          0.005221 
Sorting result                0.000034 
Sending data                  0.000016 
executing                     0.000005 
Copying to tmp table          0.005189 
Sorting result                0.000033 
Sending data                  0.000015 
executing                     0.000005 
Copying to tmp table          0.005370 
Sorting result                0.000038 
Sending data                  0.000017 
executing                     0.000005 
Copying to tmp table          0.005208 
Sorting result                0.000035 
Sending data                  0.000017 
executing                     0.000006 
Copying to tmp table          0.005209 
Sorting result                0.000036 
Sending data                  0.000054 
end                           0.000019 
removing tmp table            0.000021 
end                           0.000008 
query end                     0.000006 
closing tables                0.000020 
freeing items                 0.000052 
Waiting for query cache lock  0.000006 
freeing items                 0.000852 
Waiting for query cache lock  0.000012 
freeing items                 0.000004 
storing result in query cache 0.000019 
logging slow query            0.000005 
logging slow query            0.000010 
cleaning up                   0.000009 

Showing rows 0 - 2 ( 3 total, Query took 58.6074 sec)

因此，我根据数字猜测，大部分时间都花在创建临时表上。(最大堆大小和临时表大小参数为其16M的默认值)。

有没有人知道我如何加快这个查询的速度，或者设计一个新的查询来做同样的事情，但效率更高？

mysql

回答 3

Stack Overflow用户

发布于 2016-11-03 02:07:27

尝试在结果表上使用dinamic ()和inner

  select * from prices as a 
  inner join (      
       SELECT max(prices.price_id )  as max_price_id
       FROM prices
       WHERE product_id in (1,2,4) AND price_date <= '2016-09-01' 
       GROUP BY product_id ) t on t.max_price_id = a.price_id

票数 0

Stack Overflow用户

发布于 2016-11-03 02:15:58

使用相关查询，有时mysql的IN子查询会出现问题

SELECT prices.* 
FROM prices 
WHERE EXISTS (
     SELECT max(p.price_id) 
     FROM prices p
     WHERE p.product_id in (1,2,4) AND p.price_date <= '2016-09-01' 
     AND prices.price_id = p.price_id
     GROUP BY p.product_id
)

票数 0

Stack Overflow用户

发布于 2016-11-03 02:52:41

如果您可以自由添加索引，可以通过(1) product_id，(2) price_date (降序)来索引表，您可以这样做：

SELECT *
FROM prices
WHERE product_id in (1,2,4) AND price_date <= '2016-09-01' 
ORDER BY price_date DESC
LIMIT 1

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/40386742

复制

相似问题

问SQL查询占用的临时表时间太长
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL查询占用的临时表时间太长EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL查询占用的临时表时间太长
EN