mysql in 查询效率慢优化

2/22/2017来源:ASP.NET技巧人气:2415

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来改写一下该查询来进行优化。