今天碰到一个数据库问题,需要用到子查询,但尝试了一下发现它很慢:
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 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化...
mysql 性能 优化 pdf MySQL 介绍和优化分享
目标 了解什么是优化 掌握优化查询的方法 掌握优化数据库结构的方法 掌握优化 MySQL 服务器的方法 2. 什么是优化? 合理安排资源、调整系统...查看你的 mysql 数据库是否有慢查询:SHOW STATUS LIKE 'Slow_queries'
mysql性能优化教程.pdf,方面阅读
mysql 性能优化与架构设计的word完整版,不可多得得关于 mysql 性能优化以及架构设计方面的资料。
mysql性能优化
Mysql性能优化教程 优化mysql性能,高并发
主要介绍MySQL数据库中慢查询日志的使用流程、使用方式、以及日志分析工具mysqldumpslow的相关内容
mysql性能优化-慢查询分析、优化索引和配置
基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf基于MySQL的数据库查询性能优化.pdf...
caoz的mysql性能优化教程 caoz的mysql性能优化教程 caoz的mysql性能优化教程 caoz的mysql性能优化教程 caoz的mysql性能优化教程
MySQL性能优化和高可用架构实践.pptx
MySQL 性能优化 Explain ,MySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 Explain
MySQL性能优化中文手册 包括sql语句,存储过程,函数的调试,调优
MySQL性能优化 SQL优化方法技巧
MySQL性能优化教学视频: https://www.bilibili.com/video/av29072634/?p=5 对应的笔记和资料
监控和分析慢查询:开启慢查询日志,使用mysqldumpslow命令分析慢查询,找出并优化执行时间较长的SQL语句。 调整MySQL配置:根据服务器的硬件资源和应用需求,调整MySQL的各项参数,如缓冲池大小、最大连接数等,以...
千金良方:MySQL性能优化金字塔法则.docx
Mysql性能优化教程
mysql性能优化的总结,从各个方面对sql的优化进行了总结,感觉是很好的性能优化的摘要总结,所以上传共享出来,绝对的硬货。绿色版,解压直接用,方便程序猿