SELECT v.sub_id,v.city_id AS city_id ,v.vip_level AS vip_level FROM vip_user_info v
WHERE v.rec_status=1 AND v.cust_type = 1
AND EXISTS(SELECT 1 FROM vip_manager vm WHERE v.sub_id=vm.sub_id AND vm.rec_status=1
AND EXISTS ( SELECT 1 FROM manager_info m WHERE vm.manager_id=m.manager_id AND m.rec_status=1 AND m.manager_type=5 ))-------------------------------------------
其中:
vip_user_info(v) VIP用户信息表
vip_manager(vm) VIP用户-客户经理关系表
manager_info(m) 客户经理信息表因为数据量太大,造成查询时间太长,请问如何进行优化比较好呢?
WHERE v.rec_status=1 AND v.cust_type = 1
AND EXISTS(SELECT 1 FROM vip_manager vm WHERE v.sub_id=vm.sub_id AND vm.rec_status=1
AND EXISTS ( SELECT 1 FROM manager_info m WHERE vm.manager_id=m.manager_id AND m.rec_status=1 AND m.manager_type=5 ))-------------------------------------------
其中:
vip_user_info(v) VIP用户信息表
vip_manager(vm) VIP用户-客户经理关系表
manager_info(m) 客户经理信息表因为数据量太大,造成查询时间太长,请问如何进行优化比较好呢?
WHERE v.rec_status=1 AND v.cust_type = 1
AND v.sub_id=vm.sub_id AND vm.rec_status=1
AND vm.manager_id=m.manager_id AND m.rec_status=1 AND m.manager_type=5 2.给where条件用到的表的字段加索引
SELECT v.sub_id,v.city_id AS city_id ,v.vip_level AS vip_level FROM vip_user_info v
WHERE v.rec_status=1 AND v.cust_type = 1
AND EXISTS(SELECT 1 FROM (select sub_id, manager_id from vip_manager where rec_status=1) vm
WHERE v.sub_id=vm.sub_id
AND EXISTS ( SELECT 1 FROM (select manager_id from manager_info where rec_status=1 and manager_type=5) m
WHERE vm.manager_id=m.manager_id ))
2.给 manager_info,vip_manager 的字段manager_id加上索引.
3.对manager_info,vip_manager,vip_user_info进行表分析.
sub_id ASC
)/
CREATE INDEX idx_sub_id_2 ON vip_manager (
sub_id ASC
)/
CREATE INDEX idx_manager_id_2 ON vip_manager (
manager_id ASC
)/
CREATE INDEX idx_manager_id ON manager_info(
manager_id ASC
)/
analyze table manager_info compute statistics;
analyze table vip_manager compute statistics;
analyze table vip_user_info compute statistics;