mysql 高效率子查询_优化MySQL子查询：解决MySQL子查询效率慢的问题

优化MySQL子查询：解决MySQL子查询效率慢的问题

发布日期：2015-10-21 11:25

来源：

标签：

MySQL数据库

MySQL教程

优化MySQL查询

优化MySQL子查询

本章节我们将学习如何解决MySQL子查询效率慢的问题，下面我们就对MySQL的优化方法做一下具体讲解，希望大家多多支持中国站长网络学院。

文章表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)

在SqlServer中好不会这样慢，但是在MySQL里效率却比较差

解决方法：

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 都进行了尝试，都有这个问题。

搜索了一下网络，发现很多人都遇到过这个问题：

根据网上这些资料的建议，改用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来改写一下该查询来进行优化。