欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql in语句子查询效率慢的优化技巧示例

程序员文章站 2022-08-30 19:25:07
表结构如下,文章只有690篇。 文章表article(id,title,content) 标签表tag(tid,tag_name) 标签文章中间表arti...

表结构如下,文章只有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优化介绍等,有什么问题可以留言,欢迎大家一起交流参考。

希望本文所述对大家有所帮助。