当前位置:首页 > 开发 > 数据库 > 正文

MySQL性能优化小记:MySQL子查询很慢的问题

发表于: 2012-01-13   作者:codingstandards   来源:转载   浏览:
摘要: 今天碰到一个数据库问题,需要用到子查询,但尝试了一下发现它很慢: mysql> select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839'); 为了节省篇幅,省略了输出内容,下同。 67 rows in

今天碰到一个数据库问题,需要用到子查询,但尝试了一下发现它很慢:


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:使用连接(JOIN)来代替子查询(Sub-Queries) mysql优化系列记录
http://blog.csdn.net/hongsejiaozhu/article/details/1876181
参考资料2:网站开发日记(14)-MYSQL子查询和嵌套查询优化
http://dodomail.iteye.com/blog/250199

 

根据网上这些资料的建议,改用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语句的查询不一定总比使用子查询的语句快。

参考资料3:改变了对Mysql子查询的看法
http://hi.baidu.com/yzx110/blog/item/e694f536f92075360b55a92b.html


本文链接:http://codingstandards.iteye.com/blog/1344833


正好手头有本《高性能MySQL》,翻阅了一下,第4.4节“MySQL查询优化器的限制”4.4.1小节“关联子查询”正好讲到这个问题。


MySQL有时优化子查询很差,特别是在WHERE从句中的IN()子查询。像上面我碰到的情况,其实我的想法是MySQL会把

select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

变成下面的样子

select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897);

但不幸的是,实际情况正好相反。MySQL试图让它和外面的表产生联系来“帮助”优化查询,它认为下面的exists形式更有效率

select * from abc_number_prop where exists (select * from abc_number_phone where phone = ' 82306839' and number_id = abc_number_prop.number_id);


mysql> select * from abc_number_prop where exists (select * from abc_number_phone where phone = '82306839' and number_id = abc_number_prop.number_id);

67 rows in set (10.89 sec)


mysql> desc select * from abc_number_prop where exists (select * from abc_number_phone where phone = '82306839' and number_id = abc_number_prop.number_id);
+----+--------------------+------------------+--------+-----------------+-------+---------+-------------------------------------+---------+--------------------------+
| id | select_type        | table            | type   | possible_keys   | key   | key_len | ref                                 | rows    | Extra                    |
+----+--------------------+------------------+--------+-----------------+-------+---------+-------------------------------------+---------+--------------------------+
|  1 | PRIMARY            | abc_number_prop  | ALL    | NULL            | NULL  | NULL    | NULL                                | 2660707 | Using where              |
|  2 | DEPENDENT SUBQUERY | abc_number_phone | eq_ref | phone,number_id | phone | 70      | const,eap.abc_number_prop.number_id |       1 | Using where; Using index |
+----+--------------------+------------------+--------+-----------------+-------+---------+-------------------------------------+---------+--------------------------+
2 rows in set (0.01 sec)


这种in子查询的形式,在外部表(比如上面的abc_number_prop)数据量较大的时候效率是很差的。(如果对于较小的表,不会造成显著地影响)


文中说到一种优化方式就是,手工将in里面的子查询查询出来,然后再拼装执行 ,这在程序中是可行的。


文中说到:但是总是认为子查询效率很差也是不对的,有时候可能子查询更好些。怎么确定这个事情呢,应该经过评测来决定(执行查询、用desc/explain等来看)。


在网上也能找到《高性能MySQL》的这节内容

参考资料4:MySQL 数据库优化(12)Limitations of the MySQL Query Optimizer
http://www.chenyajun.com/2009/01/03/1657




MySQL性能优化小记:MySQL子查询很慢的问题

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号