- 浏览: 88098 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
hairongtian:
我想问一下在GC中的gc roots是什么?教科书的看不懂,能 ...
JVM-GC实践总结(纠正并发线程数 转载)
今天碰到一个数据库问题,需要用到子查询,但尝试了一下发现它很慢:
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水平分区,垂直分区
2013-07-16 22:43 872坚信的物理设计在对高级数据库的性能影响上远比其他因素重要。给 ... -
用mysqldumpslow分析mysql的slow query log
2011-10-30 22:32 776mysql有一个功能就是可以log下来运行的比较慢的sql语句 ... -
mysql group_contact()
2011-09-26 15:33 0GROUP_CONCAT([DISTINCT] expr [, ... -
mysql与MongoDB语法对比
2011-08-10 15:17 886SQL Statement ... -
MongoDB测试代码
2011-08-10 15:10 1225public class MongoDemo { ... -
NoSQL MongoDB语法
2011-08-10 15:07 3459写在前面 本文是由一次演讲整理出来的,文中大部分资料来源 ... -
如何提高上百万条的数据库查询速度
2011-05-06 17:52 8951.对查询进行优化,应 ...
相关推荐
mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化mysql 性能优化...
mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql优化技巧mysql...
mysql慢可能是配置不对,阅读一下这个可能对你有帮助 ...对于Discuz!... 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化。
详细介绍了mysql的优化方法,资料包里包含word文档,ppt和图片
21. MYSQL扩展/优化-提供更快的速度 22. MYSQL何时使用索引 23. MYSQL何时不使用索引 24. 学会使用EXPLAIN 25. 学会使用SHOW PROCESSLIST 26. 如何知晓MYSQL解决一条查询 27. MYSQL非常不错 28. MYSQL应避免...
mysql优化
mysql存储过程优化:使用临时表代替游标 ;巧建MySQL sum索引以提升效率
教程名称:大型门户网站核心技术-Mysql优化 课程目录:【】Mysql优化 资料【】Mysql优化01关键技术【】Mysql优化02表的设计【】Mysql优化03慢查询(一)【】Mysql优化04慢查询(二)【】Mysql优化05慢查询(三)【】Mysql...
MySql优化
mysql使用中的一些优化。
我的mysql优化日记 我的mysql优化日记 我的mysql优化日记 我的mysql优化日记
为什么要开发这个MySQL 优化工具(Why) “一键优化”功能,可以优化本地/远程需要优化的机器,将繁琐的优化工作“傻瓜”式操作 根据您的业务需求Step By Step优化的MySQL服务器参数,起到指引的作用,简化用户...
mysql基础知识和mysql优化整理,mysql基础知识和mysql优化整理,
MySql优化——MySql 优化.doc
mysql优化从以下几个方面介绍 mysql的架构 索引优化分析 查询截取分析 mysql锁机制 主从复制
Mysql优化方法介绍.ppt
windows平台mysql优化配置
对mysql优化时一个综合性的技术,主要包括 a: 表的设计合理化(符合3NF) b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引] c: 分表技术(水平分割、垂直分割) d: 读写[写: update/...
MySQL优化