mysqlwhere慢_MySQL中where条件中IN的慢查询优化MySQL中where条件中IN的慢查询优化
我们在编写SQL查询语句时，有时候会遇到连表查询的情况，有时的业务场景为，要查询满⾜某种条件的⼀系列id的数据。
优化⽅式⼀：
⽰例：查询出指定时间之后凡是上传过图⽚的⽤户所在的镇和镇的管理员名。这种优化⽅式是另外⼀个博主那⾥借鉴过来的。
SQL：
SELECT DISTINCT user_name,town_name FROM t_farmers WHERE id IN
(SELECT DISTINCT farmer_id FROM t_farmers_images WHERE create_time>='2017-07-05')
---------------------
作者：pengyufight
来源：CSDN
版权声明：本⽂为博主原创⽂章，转载请附上博⽂链接！
getch为什么用不了这样查询的话，速度相当的慢，其中farmers表有六千多记录，farmers_images表有近20万条记录，这样的场景，可能都要查询10⼏分钟，IN能否让索引失效要看数据库的版本和查询的条件。解决办法是使⽤左连接或右连接来连表查询，这样的查询是秒级的。
SQL：
SELECT DISTINCT b.user_wn_name FROM (SELECT DISTINCT farmer_id FROM t_farmers_images WHERE
create_time>='2017-08-18') a
LEFT JOIN t_farmers b ON a.farmer_id=b.id
优化⽅式⼆：
⽰例：就是连表查询后，过滤掉符合条件的id的数据。
SELECT
c2.id,
SUM(sales_value-return_cost) sales_value,
SUM(sales_quantity-return_quantity) sales_quantity,
SUM(profit)/SUM(sales_value) as profit_rate
FROM (SELECT
product_id, sales_value, return_cost,sales_quantity, return_quantity,profit
FROM SalesDayStats
WHERE day BETWEEN '20190701' AND '20190731') as s
INNER JOIN Products p ON s.product_id = p.id
INNER JOIN Categories c ON c.id = p.spu_id
INNER JOIN Categories c2 ON c2.id = c.second_id
where c2.id in (1011,1012,1013,1014,1015)
GROUP BY c2.id插件大全
虽然只有46万的数据，但是查了10⼏秒，⾮常影响⽤户体验，优化⽅案可以类似于⽅式⼀那种使⽤左连接
优化2.1---左连接
SELECT
c2.id,
SUM(sales_value-return_cost) sales_value,
SUM(sales_quantity-return_quantity) sales_quantity,
SUM(profit)/SUM(sales_value) as profit_rate
FROM (SELECT
product_id, sales_value, return_cost,sales_quantity, return_quantity,profit
FROM SalesDayStats
WHERE day BETWEEN '20190701' AND '20190731') as s
matlab 等高线图INNER JOIN Products p ON s.product_id = p.id
INNER JOIN Categories c ON c.id = p.spu_id
INNER JOIN Categories c2 ON c2.id = c.second_id
LEFT JOIN (select id from Categories where id in(1011,1012,1013,1014,1015)) tt ON c2.id = tt.id GROUP BY c2.id
查询时间瞬间降为了1s左右。
优化2.2---使⽤having
SELECT
c2.id,
SUM(sales_value-return_cost) sales_value,
SUM(sales_quantity-return_quantity) sales_quantity,
SUM(profit)/SUM(sales_value) as profit_rate
FROM (SELECT
mysql面试题sql优化product_id, sales_value, return_cost,sales_quantity, return_quantity,profit
FROM SalesDayStats
WHERE day BETWEEN '20190701' AND '20190731') as s
INNER JOIN Products p ON s.product_id = p.id
INNER JOIN Categories c ON c.id = p.spu_id
INNER JOIN Categories c2 ON c2.id = c.second_id
二郎山绒山羊GROUP BY c2.id HAVING c2.id in (1011,1012,1013,1014,1015)
查询时间⼤概为0.8s
如果⼤家有什么更好的解决⽅法，欢迎讨论。vb显示星期几的代码
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论