mysql没有数据in慢_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

原文：https://blog.csdn.net/pengyufight/article/details/77523404

版权声明：本文为博主原创文章，转载请附上博文链接！

这样查询的话，速度相当的慢，其中farmers表有六千多记录，farmers_images表有近20万条记录，这样的场景，可能都要查询10几分钟，IN能否让索引失效要看数据库的版本和查询的条件。解决办法是使用左连接或右连接来连表查询，这样的查询是秒级的。

SQL：

SELECT DISTINCT b.user_name,b.town_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

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

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

如果大家有什么更好的解决方法，欢迎讨论。