STRAIGHT_JOIN优化坑爹SQL

原创

mb5fb94c9d4a0ba 2023-05-17 14:07:20 博主文章分类：数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者mb5fb94c9d4a0ba的原创作品，请联系作者获取转载授权，否则将追究法律责任

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

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