postgresql-sql语句调优-爱代码爱编程
查看某表已建有的index
select indexname,indexdef from pg_indexes where tablename='ip_assets_assets_ports'
查看某个schema下已建有的index
select indexname,indexdef from pg_indexes where schemaname='public'
创建索引的语句
create index ip_assets_assets_ports_3 on ip_assets_assets_ports (ip_online, mid, ip, port)
删除索引语句
drop index ip_assets_assets_ports_3
优化前SQL语句,耗时40s
select x.company,x.surviveIpNums,y.portNum,z.softNum,g.hardNum from
(select count(distinct ia.search_ip) as surviveIpNums ,ia.mid_name as company,ia.mid from ip_assets_assets ia where ia.ip_online='1' group by mid_name,mid) x
left join
(select count(distinct(iap.ip,iap.port)) as portNum,iap.mid from ip_assets_assets_ports iap where iap.ip_online='1' group by iap.mid) y on x.mid=y.mid
left join
(select count(distinct ar.rule_id) as softNum,ar.mid from ip_assets_assets_rule_infos ar where ar.soft_hard_code=2 and ar.ip_online='1' group by mid)z on y.mid=z.mid
left join
(select count(distinct ar.rule_id) as hardNum,ar.mid from ip_assets_assets_rule_infos ar where ar.soft_hard_code=1 and ar.ip_online='1' group by mid) g on z.mid=g.mid
优化后SQL语句,耗时12.5s
SELECT
x.company,
x.surviveIpNums,
y.portNum,
z.softNum,
g.hardNum
FROM
(
SELECT COUNT (*) AS surviveIpNums,mid_name AS company,mid FROM (SELECT DISTINCT search_ip, mid_name, mid FROM ip_assets_assets WHERE ip_online = '1' ) temp_assets
GROUP BY mid_name, mid
) x
LEFT JOIN (
SELECT COUNT (*) AS portNum,mid FROM (SELECT DISTINCT ip, port, mid FROM ip_assets_assets_ports WHERE ip_online = '1' ) temp_ports GROUP BY mid
) y ON x.mid = y.mid
LEFT JOIN (
SELECT COUNT(*) AS softNum,mid FROM (SELECT DISTINCT rule_id,mid FROM ip_assets_assets_rule_infos WHERE soft_hard_code = 2 AND ip_online = '1') temp_soft GROUP BY mid
) z ON y.mid = z.mid
LEFT JOIN (
SELECT COUNT(*) AS hardNum,mid FROM (SELECT DISTINCT rule_id,mid FROM ip_assets_assets_rule_infos WHERE soft_hard_code = 1 AND ip_online = '1') temp_hard GROUP BY mid
) g ON z.mid = g.mid
执行计划命令
explain analyse sql
执行结果如下图所示: