STRAIGHT_JOIN优化坑爹SQL

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秒 就飞出来了.

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