首页 > 数据库技术 > 详细

STRAIGHT_JOIN优化坑爹SQL

时间:2014-08-28 14:52:31      阅读:247      评论:0      收藏:0      [点我收藏+]


select d.device_id, d.device_token, d.app_name, d.user_id, d.app_name_aliases, d.app_version, vipruid
from apns_device d, user_group u
where
d.user_id <> ‘0‘
and d.status =1
and u.user_group_id = ‘176‘and(
u.user_id = d.encrypt_user_id and d.encrypt_user_id is not null
)and d.app_name in(
‘唯品会‘, ‘唯品会 hd‘, ‘achievo_ad‘
)and app_version in(
‘1.1.5‘, ‘1.1.6‘, ‘1.1.5‘, ‘1.1.2‘, ‘1.1.1‘, ‘1.1.2‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘, ‘1.5.0‘, ‘1.4.9‘, ‘1.4.8‘, ‘1.4.6‘, ‘1.4.4‘, ‘2.0.0‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘, ‘1.5.0‘, ‘1.4.9‘, ‘2.5‘, ‘2.4.1‘, ‘2.4‘, ‘2.3‘, ‘2.2.1‘, ‘2.2‘, ‘2.1‘, ‘2.0.1‘, ‘2.0‘, ‘1.7.0‘, ‘1.5.8‘, ‘1.5.6‘, ‘1.5.5‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘, ‘1.5.0‘, ‘1.4.5‘, ‘2.5.1‘, ‘2.5‘, ‘2.4‘, ‘2.3.2‘, ‘2.3.1‘, ‘2.3‘, ‘2.2‘, ‘2.1‘, ‘2.0.3‘, ‘2.0.2‘, ‘2.0‘, ‘1.8.6‘, ‘1.8.5‘, ‘1.8.4‘, ‘1.6.11‘, ‘1.6.1‘, ‘1.6.0‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.0‘, ‘1.4.5‘, ‘1.1‘, ‘1.1‘, ‘1.0.1‘, ‘1.1.2‘, ‘1.0.0‘, ‘1.0‘, ‘1.0‘, ‘2.3.1‘, ‘2.3.0‘, ‘2.2.1‘, ‘2.2.0‘, ‘2.1.0‘, ‘2.0.0‘, ‘1.6.5‘, ‘1.6.4‘, ‘1.4.7‘, ‘1.4.6‘, ‘8.2.5‘, ‘2.6‘, ‘2.5.1‘, ‘2.5‘, ‘2.4‘, ‘2.3.1.1‘, ‘2.3.1‘, ‘2.3.0.1‘, ‘2.3‘, ‘2.2.4‘, ‘2.2.3‘, ‘2.2.2‘, ‘2.2.1‘, ‘2.2‘, ‘2.1.1‘, ‘2.1‘, ‘1.7.1‘, ‘1.6.4.1‘, ‘1.6.3‘, ‘1.6.2‘, ‘1.6.1.1‘, ‘1.6.1‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘
)order by d.device_id limit 0, 30000;


+----+-------------+-------+-------+--------------------------------------------------------------------+---------------+---------+------------------------------+-------+-------------+
| id | select_type | table | type  | possible_keys                                                      | key           | key_len | ref                          | rows  | Extra       |
+----+-------------+-------+-------+--------------------------------------------------------------------+---------------+---------+------------------------------+-------+-------------+
|  1 | SIMPLE      | d     | index | idx_userid,idx_app_status,idx_appname_deviceid,idx_encrypt_user_id | PRIMARY       | 4       | NULL                         | 71720 | Using where |
|  1 | SIMPLE      | u     | ref   | user_group_id                                                      | user_group_id | 306     | const,mpms.d.encrypt_user_id |     1 | Using index |
+----+-------------+-------+-------+--------------------------------------------------------------------+---------------+---------+------------------------------+-------+-------------+
2 rows in set (0.00 sec)




desc

select d.device_id, d.device_token, d.app_name, d.user_id, d.app_name_aliases, d.app_version, vipruid
from user_group u  STRAIGHT_JOIN apns_device d
where
d.user_id <> ‘0‘
and d.status =1
and u.user_group_id = ‘176‘and(
u.user_id = d.encrypt_user_id and d.encrypt_user_id is not null
)and d.app_name in(
‘唯品会‘, ‘唯品会 hd‘, ‘achievo_ad‘
)and app_version in(
‘1.1.5‘, ‘1.1.6‘, ‘1.1.5‘, ‘1.1.2‘, ‘1.1.1‘, ‘1.1.2‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘, ‘1.5.0‘, ‘1.4.9‘, ‘1.4.8‘, ‘1.4.6‘, ‘1.4.4‘, ‘2.0.0‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘, ‘1.5.0‘, ‘1.4.9‘, ‘2.5‘, ‘2.4.1‘, ‘2.4‘, ‘2.3‘, ‘2.2.1‘, ‘2.2‘, ‘2.1‘, ‘2.0.1‘, ‘2.0‘, ‘1.7.0‘, ‘1.5.8‘, ‘1.5.6‘, ‘1.5.5‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘, ‘1.5.0‘, ‘1.4.5‘, ‘2.5.1‘, ‘2.5‘, ‘2.4‘, ‘2.3.2‘, ‘2.3.1‘, ‘2.3‘, ‘2.2‘, ‘2.1‘, ‘2.0.3‘, ‘2.0.2‘, ‘2.0‘, ‘1.8.6‘, ‘1.8.5‘, ‘1.8.4‘, ‘1.6.11‘, ‘1.6.1‘, ‘1.6.0‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.0‘, ‘1.4.5‘, ‘1.1‘, ‘1.1‘, ‘1.0.1‘, ‘1.1.2‘, ‘1.0.0‘, ‘1.0‘, ‘1.0‘, ‘2.3.1‘, ‘2.3.0‘, ‘2.2.1‘, ‘2.2.0‘, ‘2.1.0‘, ‘2.0.0‘, ‘1.6.5‘, ‘1.6.4‘, ‘1.4.7‘, ‘1.4.6‘, ‘8.2.5‘, ‘2.6‘, ‘2.5.1‘, ‘2.5‘, ‘2.4‘, ‘2.3.1.1‘, ‘2.3.1‘, ‘2.3.0.1‘, ‘2.3‘, ‘2.2.4‘, ‘2.2.3‘, ‘2.2.2‘, ‘2.2.1‘, ‘2.2‘, ‘2.1.1‘, ‘2.1‘, ‘1.7.1‘, ‘1.6.4.1‘, ‘1.6.3‘, ‘1.6.2‘, ‘1.6.1.1‘, ‘1.6.1‘, ‘1.5.4‘, ‘1.5.3‘, ‘1.5.2‘, ‘1.5.1‘
)order by d.device_id limit 0, 30000;


+----+-------------+-------+------+--------------------------------------------------------------------+---------------------+---------+----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys                                                      | key                 | key_len | ref            | rows  | Extra                                        |
+----+-------------+-------+------+--------------------------------------------------------------------+---------------------+---------+----------------+-------+----------------------------------------------+
|  1 | SIMPLE      | u     | ref  | user_group_id                                                      | user_group_id       | 4       | const          |   188 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | d     | ref  | idx_userid,idx_app_status,idx_appname_deviceid,idx_encrypt_user_id | idx_encrypt_user_id | 303     | mpms.u.user_id | 72489 | Using where                                  |
+----+-------------+-------+------+--------------------------------------------------------------------+---------------------+---------+----------------+-------+----------------------------------------------+




执行计划看似 STRAIGHT_JOIN要慢N个数量级.


但实际执行起来.


第一条SQL需要十多秒.

第二条STRAIGHT_JOIN仅0.01秒 就飞出来了.






当然,,要先确保驱动表的数据趋势..






STRAIGHT_JOIN优化坑爹SQL

原文:http://blog.csdn.net/shine0181/article/details/38897513

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!