当数据量比较大的时候,提升查询效率就是需要去考虑的事情了。一个百万级别的表格,如果不做任何优化的话,即使是最简单的查询语句执行起来也是慢的让人难以接受;当然“优化”本身是一个比较复杂的工程,从设计表、字段到查询语句的写法都有很多讲究,这里只考虑索引的方式,且是最普通的索引;
下面的操作中对应数据库表w008_execrise_info(8000数据量), w008_wf02_info(4000数据量)
1 任务表数据
SELECT
w.*
FROM
w008_wf02_info w
WHERE
w.is_removed =0
AND w.wfno = 'WF02'
AND EXISTS (
SELECT
1
FROM
w008_execrise_info info
JOIN w008_privilege_allocation P ON (
info.subjecttyp = P.subjecttyp
AND info.gradetyp = P.gradetyp
AND P.loginname = w.create_by
AND P.verifyusers ='yixi_li'
AND P.wftype = 20
)
WHERE
info.is_removed =0
AND info.wfid = w.wfid
)
ORDER BY
create_date DESC
执行结果:耗时在3秒左右(这个数据量,这个速度就比较慢了)
顺带说一句,这里把w.* 改成对应的字段也会稍微的提高一些查询速度(毕竟少了一步把*转成对应字段的操作),在标准的查询规范中也不会写成table.*这种方式的。
2 添加索引:虽说索引可以提高查询速度,但是不代表加了索引就一定会加快查询速度,有时甚至会适得其反。一般来说索引会加在where 后面的查询字段,尤其是关联字段上面,这里w008_execrise_info 表数据量最大,暂时针对这个表进行处理;w008_execrise_info 表涉及四个字段:subjecttyp,gradetyp,wfid和is_removed。其中is_removed先不考虑,subjecttyp和gradetyp保存的是字典表的数据(数据内容比较少,类似10 20 30 40),wfid保存的是数字类型的字符串。一个索引起到的效果还取决于这样一个条件,一般来说添加索引的字段的值"唯一性"越明显越好,在这里,subjecttyp和gradetyp包含大量的重复值,索引效果会“不明显”一些,而wfid 更像是“主键”,相对应的效果会好很多。
2.1 subjecttyp 和gradetyp添加索引
CREATE INDEX w008_execrise_info_gradetyp_index ON w008_execrise_info (gradetyp);
CREATE INDEX w008_execrise_info_subjecttyp_index ON w008_execrise_info (subjecttyp);
添加后执行时间大约2秒,快了一点点
2.2 对wfid添加索引
CREATE INDEX w008_execrise_info_wfid_index ON w008_execrise_info (wfid);
再次执行,0.2秒左右,快了很多
再补充一句,有很多情况下索引是不起作用的,比如 like后面跟的字段,还有条件语句or关联的字段,这种情况就是要考虑查询策略了。
3 查看当前表的索引内容;
select * from pg_indexes where tablename='w008_execrise_info';
select * from pg_statio_all_indexes where relname='w008_execrise_info';
4 删除索引
DROP INDEX indexName;
5 重置索引
对于一些经常改动的表,如果时间长了发现查询效率变慢,可以考虑重置一下索引;
因为如果表的内容被频繁的修改的话会产生许多类似'索引碎片'的东西,会导致查询索引本身的时间变长;
REINDEX INDEX index_name;//重置单个索引
REINDEX TABLE table_name;//重置整个表的索引
REINDEX DATABASE db_name;//终止整个数据库的所以你