mysql - 如何减少执行选择查询时间
问题描述
减少运行时间(选择查询执行时间)
即使查询不工作
它正在减少代码行
有80个表
select
(ant_return_loss_pass='pass')+(ant_cross_isolation_pass='pass') as pass_count,
(ant_return_loss_pass='fail')+(ant_cross_isolation_pass='fail') as fail_count,
(ant_return_loss_pass='') +(ant_cross_isolation_pass='') as blank_count
from
(
select A.serial_no,
A.pass_fail as ant_return_loss_pass
from ant_return_loss A,
(
select max(register_date) as date
from ant_return_loss
where 1=1
and serial_no >= '184500074'
and serial_no <= '184500076'
group by serial_no
) B
where 1 = 1
and A.register_date = B.date
)AA
,(
select A.serial_no,
A.pass_fail as ant_cross_isolation_pass
from ant_cross_isolation A,
(
select max(register_date) as date
from ant_cross_isolation
where 1=1
and serial_no >= '184500074'
and serial_no <= '184500076'
group by serial_no
) B
where 1 = 1
and A.register_date = B.date
)BB
where 1=1
and AA.serial_no = BB.serial_no
不工作或工作 600 秒
解决方案
下面的查询使用显式连接语法和 case 表达式,使您的查询更容易理解:
SELECT
COUNT( CASE WHEN ant_return_loss_pass = 'pass' AND
ant_cross_isolation_pass = 'pass' THEN 1 END ) AS pass_count
, COUNT( CASE WHEN ant_return_loss_pass = 'fail' AND
ant_cross_isolation_pass = 'fail' THEN 1 END ) AS fail_count
, COUNT( CASE WHEN ant_return_loss_pass = '' AND
ant_cross_isolation_pass = '' THEN 1 END ) AS blank_count
FROM (
SELECT
A.serial_no
, A.pass_fail AS ant_return_loss_pass
FROM ant_return_loss A
INNER JOIN (
SELECT MAX( register_date ) AS date
FROM ant_return_loss
WHERE serial_no >= '184500074'
AND serial_no <= '184500076'
GROUP BY serial_no
) B ON A.register_date = B.date
) AA
INNER JOIN (
SELECT
A.serial_no
, A.pass_fail AS ant_cross_isolation_pass
FROM ant_cross_isolation A
INNER JOIN (
SELECT MAX( register_date ) AS date
FROM ant_cross_isolation
WHERE serial_no >= '184500074'
AND serial_no <= '184500076'
GROUP BY serial_no
) B ON A.register_date = B.date
) BB ON AA.serial_no = BB.serial_no
要检查这是否可以提高性能,但需要访问您的数据库。例如,您是否索引ant_return_loss.serial_no
或ant_cross_isolation.serial_no
这些将有助于子查询的 where 子句。
您是否对查询运行了任何解释计划?见:https ://dev.mysql.com/doc/refman/5.7/en/explain.html
请注意,为避免长时间运行,您可以对整个查询的单独部分使用说明,查找可能有帮助的索引:例如
explain
SELECT MAX( register_date ) AS date
FROM ant_return_loss
WHERE serial_no >= '184500074'
AND serial_no <= '184500076'
GROUP BY serial_no
然后:
explain
SELECT
A.serial_no
, A.pass_fail AS ant_return_loss_pass
FROM ant_return_loss A
INNER JOIN (
SELECT MAX( register_date ) AS date
FROM ant_return_loss
WHERE serial_no >= '184500074'
AND serial_no <= '184500076'
GROUP BY serial_no
) B ON A.register_date = B.date
直到您检查了所有部分以找到提高性能的方法。
推荐阅读
- python - How to make new window in pygame?
- android - Kotlin/Java 继承和接口
- ruby-on-rails - Rails5 没有在 Heroku 上预编译资产
- javascript - 如何使用 regex for jquery 选择器将某些属性应用于元素?
- reactjs - 是否可以在 axios.interceptors 中使用 toast?
- excel - Excel VBA:将工作簿、工作表对象传递给子例程的问题
- ruby-on-rails - 答案中的 NoMethodError#new
- php - PHP 可执行 iptables
- r - 在 R 中一次重新定义多个时间序列
- gremlin - 用 project() 和 select() 在 Gremlin 中总结路径信息