表结构如下，文章只有690篇。
文章表article(id,title,content) 标签表tag(tid,tag_name) 标签文章中间表article_tag(id,tag_id,article_id)
其中有个标签的tid是135，查询标签tid是135的文章列表。
690篇文章，用以下的语句查询，奇慢：
select id,title from article where id in( select article_id from article_tag where tag_id=135 )
其中这条速度很快：
select article_id from article_tag where tag_id=135
查询结果是五篇文章，id为428,429,430,431,432
用下面sql来查文章也很快：
select id,title from article where id in( 428,429,430,431,432 )
解决方法：
select id,title from article where id in( select article_id from (select article_id from article_tag where tag_id=135) as tbt )
其它解决方法：（举例）
mysql> select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');
为了节省篇幅，省略了输出内容，下同。
67 rows in set (12.00 sec)
只有67行数据返回，却花了12秒，而系统中可能同时会有很多这样的查询，系统肯定扛不住。用desc看一下(注：explain也可)
mysql> desc select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839'); +----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+ | 1 | PRIMARY | abc_number_prop | ALL | NULL | NULL | NULL | NULL | 2679838 | Using where | | 2 | DEPENDENT SUBQUERY | abc_number_phone | eq_ref | phone,number_id | phone | 70 | const,func | 1 | Using where; Using index | +----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+ 2 rows in set (0.00 sec)
可以看出，在执行此查询时会扫描两百多万行，难道是没有创建索引吗，看一下
mysql>show index from abc_number_phone; +------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | abc_number_phone | 0 | PRIMARY | 1 | number_phone_id | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 0 | phone | 1 | phone | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 0 | phone | 2 | number_id | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | number_id | 1 | number_id | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | created_by | 1 | created_by | A | 36879 | NULL | NULL | | BTREE | | | | abc_number_phone | 1 | modified_by | 1 | modified_by | A | 36879 | NULL | NULL | YES | BTREE | | | +------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.06 sec) mysql>show index from abc_number_prop; +-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | abc_number_prop | 0 | PRIMARY | 1 | number_prop_id | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | number_id | 1 | number_id | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | created_by | 1 | created_by | A | 311268 | NULL | NULL | | BTREE | | | | abc_number_prop | 1 | modified_by | 1 | modified_by | A | 311268 | NULL | NULL | YES | BTREE | | | +-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.15 sec)
从上面的输出可以看出，这两张表在number_id字段上创建了索引的。
看看子查询本身有没有问题。
mysql> desc select number_id from abc_number_phone where phone = '82306839'; +----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+ | 1 | SIMPLE | abc_number_phone | ref | phone | phone | 66 | const | 6 | Using where; Using index | +----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
没有问题，只需要扫描几行数据，索引起作用了。
查询出来看看：
mysql> select number_id from abc_number_phone where phone = '82306839'; +-----------+ | number_id | +-----------+ | 8585 | | 10720 | | 148644 | | 151307 | | 170691 | | 221897 | +-----------+ 6 rows in set (0.00 sec)
直接把子查询得到的数据放到上面的查询中
mysql> select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897); 67 rows in set (0.03 sec)
速度也快，看来MySQL在处理子查询的时候是不够好。我在MySQL 5.1.42 和 MySQL 5.5.19 都进行了尝试，都有这个问题。
搜索了一下网络，发现很多人都遇到过这个问题：
参考资料1：MySQL优化之使用连接(join)代替子查询
参考资料2：MYSQL子查询和嵌套查询优化实例解析
根据网上这些资料的建议，改用join来试试。
修改前：
select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');
修改后：
select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839'; mysql> select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839'; 67 rows in set (0.00 sec)
效果不错，查询所用时间几乎为0。看一下MySQL是怎么执行这个查询的
mysql>desc select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839'; +----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+ | 1 | SIMPLE | b | ref | phone,number_id | phone | 66 | const | 6 | Using where; Using index | | 1 | SIMPLE | a | ref | number_id | number_id | 4 | eap.b.number_id | 3 | | +----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+ 2 rows in set (0.00 sec)
小结：当子查询速度慢时，可用JOIN来改写一下该查询来进行优化。
网上也有文章说，使用JOIN语句的查询不一定总比使用子查询的语句快。
mysql手册也提到过，具体的原文在mysql文档的这个章节：
I.3. Restrictions on Subqueries
13.2.8. Subquery Syntax
摘抄：
1）关于使用IN的子查询：
Subquery optimization for IN is not as effective as for the = operator or for IN(value_list) constructs.
A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.
The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
The optimizer rewrites the statement to a correlated subquery:
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.
An implication is that an IN subquery can be much slower than a query written using an IN(value_list) construct that lists the same values that the subquery would return.
2）关于把子查询转换成join的：
The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.
An exception occurs for the case where an IN subquery can be rewritten as a SELECT DISTINCT join. Example:
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);
That statement can be rewritten as follows:
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;
But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery
总结
以上就是本文关于mysql in语句子查询效率慢的优化技巧示例的全部内容，感兴趣的朋友而可以参阅：浅谈mysql的子查询联合与in的效率、企业生产MySQL优化介绍等，有什么问题可以留言，欢迎大家一起交流参考。
希望本文所述对大家有所帮助。
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
这一波大模型产业落地浪潮里，不少企业其实处在 “干瞪眼“的状态。
一种情况是，很多大模型产品看得见却摸不着，在台上一个个遥遥领先——今天Sora技精四座，明天英伟达的机器人又赢得满堂彩，可是到了台下一问：啥时候能用上啊？答曰：遥遥无期。
另一种情况是，企业想用上大模型，却又难免瞻前顾后——既要考虑场景融合，又得兼顾安全性，还要考虑打通现有系统，再加上各种部署成本和繁琐的采购流程……最后只能拂袖：罢了，再等等吧。
稳了！魔兽国服回归的3条重磅消息！官宣时间再确认！
昨天有一位朋友在大神群里分享，自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址，com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后，确实是网易的网址，也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情，因为以前都没有出现这样的情况，现在突然提示跳转到国服战网的网址，是不是说明了简体中文客户端已经开始进行更新了呢？