H5W3
当前位置:H5W3 > 其他技术问题 > 正文

mysql查询当in里的数组过大时,索引(explain)不好使的问题

1、t_track表设计:

clipboard.png

2、t_sales表设计无t_track_id,通过t_track表的t_sale_id关联

3、查询语句:

explain SELECT COUNT(*) FROM ((SELECT `t_sales`.`t_id`, `t_sales`.`t_company`, `t_sales`.`t_fenlei`, `t_sales`.`t_tel`, `t_sales`.`province`, `t_sales`.`city`, `t_track`.`t_id` AS `track_id`, `t_track`.`t_content`, `t_track`.`t_dateline`, `t_track`.`contact_id`, `trace_cat`.`name` AS `cat_id`, `t_city_2`.`city_name`, `t_user`.`t_realname` FROM `t_sales` right join (select *from t_track where t_track.t_uid in (216,220,4507,4508,4509,4510,4511,4629,5292,5602,6186,6187,301,337,2770,1114,2144,2482,2587,336,2848,3386,1910,1970,955,3091,3148,3924,3951,4302,4467,5374,5668,5736,6041,6092,6093,6130,6131,6470,6662,6663,6793,1992,2642,3620,3639,3882,3917,4948,6244,6807) ORDER BY t_track.t_dateline DESC) t_track ON t_sales.t_id = t_track.t_sale_id left join `t_city_2` ON t_sales.t_city = t_city_2.t_id left join `t_user` ON t_track.t_uid = t_user.t_id left join `trace_cat` ON t_track.cat_id = trace_cat.id WHERE (`t_user`.`t_id` IN ('216', '220', '4507', '4508', '4509', '4510', '4511', '4629', '5292', '5602', '6186', '6187', '301', '337', '2770', '1114', '2144', '2482', '2587', '336', '2848', '3386', '1910', '1970', '955', '3091', '3148', '3924', '3951', '4302', '4467', '5374', '5668', '5736', '6041', '6092', '6093', '6130', '6131', '6470', '6662', '6663', '6793', '1992', '2642', '3620', '3639', '3882', '3917', '4948', '6244', '6807')) AND (t_track.t_sale_id IS NOT NULL and (t_track.contact_id IS NULL or t_track.contact_id=0)) GROUP BY `t_track`.`t_sale_id`) UNION ( SELECT `t_sales`.`t_id`, `t_sales`.`t_company`, `t_sales`.`t_fenlei`, `t_sales`.`t_tel`, `t_sales`.`province`, `t_sales`.`city`, `t_track`.`t_id` AS `track_id`, `t_track`.`t_content`, `t_track`.`t_dateline`, `t_track`.`contact_id`, `trace_cat`.`name` AS `cat_id`, `t_city_2`.`city_name`, `t_user`.`t_realname` FROM `t_sales` right join (select *from t_track where t_track.t_uid in (216,220,4507,4508,4509,4510,4511,4629,5292,5602,6186,6187,301,337,2770,1114,2144,2482,2587,336,2848,3386,1910,1970,955,3091,3148,3924,3951,4302,4467,5374,5668,5736,6041,6092,6093,6130,6131,6470,6662,6663,6793,1992,2642,3620,3639,3882,3917,4948,6244,6807) ORDER BY t_track.t_dateline DESC) t_track ON t_sales.t_id = t_track.t_sale_id inner join `contact` ON contact.id = t_track.contact_id left join `t_city_2` ON t_sales.t_city = t_city_2.t_id left join `t_user` ON t_track.t_uid = t_user.t_id left join `trace_cat` ON t_track.cat_id = trace_cat.id WHERE (`t_user`.`t_id` IN ('216', '220', '4507', '4508', '4509', '4510', '4511', '4629', '5292', '5602', '6186', '6187', '301', '337', '2770', '1114', '2144', '2482', '2587', '336', '2848', '3386', '1910', '1970', '955', '3091', '3148', '3924', '3951', '4302', '4467', '5374', '5668', '5736', '6041', '6092', '6093', '6130', '6131', '6470', '6662', '6663', '6793', '1992', '2642', '3620', '3639', '3882', '3917', '4948', '6244', '6807')) AND (t_track.t_sale_id IS NOT NULL and t_track.contact_id!=0 and t_track.contact_id IS NOT NULL) GROUP BY `t_track`.`contact_id` )) `tmpA`

4、执行结果:
t_track的type为All,是非常糟糕的。

clipboard.png

注:1、t_uid已经加了索引;2、当查询语句in里面的数组小于等于12时,type会有好转。

5、这种问题该怎么解决:
(1)、优化查询语句?
(2)、改变表结构,重新设计?
(3)、除了这两种以外还有别的办法吗?

还望详细说明解释。

回答:

本来就是in查询了,还有关联查询,还是分开查询吧,多写几条sql语句,来完成这个任务,或者再做一张统计表,定时统计

回答:

这条sql语句那么长,我都看不下去,说说我的思路,看是否对你有帮助
1.根据possible_keys添加合适的索引,
2.也可以把复杂的sql语句拆分,利用分步计算的逻辑,
3.可以使用mysql视图,存储过程

本文地址:H5W3 » mysql查询当in里的数组过大时,索引(explain)不好使的问题

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址