PostgreSQL索引探究
来源:互联网 发布:网络直播打赏经济 编辑:程序博客网 时间:2024/06/16 20:52
创建包含10个列(c01 - c10)的表my_table,用如下语句创建2个索引,并向表中插入6w条记录。这6w条记录的c01列,全部都是2017年04月21日的数据。
CREATE INDEX my_table_index1 ON my_table USING btree (c05, c01, c02)TABLESPACE smart_history_index;CREATE INDEX my_table_index2 ON my_table USING btree (c01, c02, c03, c04, c05)TABLESPACE smart_history_index;
使用以下SQL1语句查询,查询时间为2分钟。
SQL1:
select * from my_table t1 where (select count(*) from my_table t2 where ((t1.c01 = t2.c01 ) or (t1.c01 is null and t2.c01 is null)) and((t1.c02 = t2.c02 ) or (t1.c02 is null and t2.c02 is null)) and((t1.c03 = t2.c03 ) or (t1.c03 is null and t2.c03 is null)) and((t1.c04 = t2.c04 ) or (t1.c04 is null and t2.c04 is null)) and((t1.c05 = t2.c05 ) or (t1.c05 is null and t2.c05 is null)) and((t1.c06 = t2.c06 ) or (t1.c06 is null and t2.c06 is null)) and((t1.c07 = t2.c07 ) or (t1.c07 is null and t2.c07 is null)) and((t1.c08 = t2.c08 ) or (t1.c08 is null and t2.c08 is null)) and((t1.c09 = t2.c09 ) or (t1.c09 is null and t2.c09 is null)) and((t1.c10 = t2.c10 ) or (t1.c10 is null and t2.c10 is null)))>1;
使用以下SQL1语句查询,查询时间为2.8秒。
SQL2:
select * from my_table t1 where (select count(*) from my_table t2 where ((t1.c01 = t2.c01 ) or (t1.c01 is null and t2.c01 is null)) and((t1.c02 = t2.c02 ) or (t1.c02 is null and t2.c02 is null)) and((t1.c03 = t2.c03 ) or (t1.c03 is null and t2.c03 is null)) and((t1.c04 = t2.c04 ) or (t1.c04 is null and t2.c04 is null)) and((t1.c05 = t2.c05 ) or (t1.c05 is null and t2.c05 is null)) and((t1.c06 = t2.c06 ) or (t1.c06 is null and t2.c06 is null)) and((t1.c07 = t2.c07 ) or (t1.c07 is null and t2.c07 is null)) and((t1.c08 = t2.c08 ) or (t1.c08 is null and t2.c08 is null)) and((t1.c09 = t2.c09 ) or (t1.c09 is null and t2.c09 is null)) and((t1.c10 = t2.c10 ) or (t1.c10 is null and t2.c10 is null)) and(c01 >= '2017-04-20 00:00:00' and c01 < '2017-04-21 00:00:00'))>1 and (c01 >= '2017-04-20 00:00:00' and c01 < '2017-04-21 00:00:00');
虽然SQL2最后对于c01列的where条件并为实质上减少过滤出的数据量。但是能够显著的提高查询效率(60倍),进一步使用以下SQL3语句监视索引使用情况发现,SQL2只调用了my_table_index2,而SQL1既调用了my_table_index1也调用了my_table_index2。
SQL3:
select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where relname = 'my_table'order by idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;
显然my_table_index1肯定比my_table_index2的辨识度更高。之所以SQL优化解析器会做出这样的选择,猜测可能是因为my_table_index2中列c01处于第一个的位置,而SQL2最后的c01查询条件刚好暗示强化了解释器去选择my_table_index2。
在此基础上我进行了进一步的实验:如果drop掉my_table_index1只保留my_table_index2,则无论是SQL1还是SQL2都能达到大约2.8秒的查询速度;如果drop掉my_table_index2只保留my_table_index1,则无论是SQL1还是SQL2都需要2分钟才能完成查询;当然my_table_index1也并不是一无是处,如果将两个索引全部drop掉,那么做一次查询(全表扫描)大约需要10分钟。
最后说两点启示:
1.索引不能随便建,如果建的不好,不仅影响插入效率,也会影响查询效率。
2.SQL语句优化之路,任重而道远。
- PostgreSQL索引探究
- PostgreSQL索引
- Postgresql 索引
- postgresql索引
- 索引监控探究
- Mysql全文索引探究
- 【MongoDb探究】08-索引
- mysql索引探究
- MongoDB索引探究
- PostgreSql查看索引
- PostGresql索引深入分析
- PostgreSQL学习手册(索引)
- PostgreSQL 位图索引
- postgresql 索引类型
- postgresql 索引类型
- POSTGRESQL 数据库 索引
- PostgreSQL的索引选型
- PostgreSql重建索引
- 抓取分支
- 习题7.1
- Anaconda的Spyder读取中文文件报错处理
- Angular4_Select选择改变事件
- 史上最简单的 SpringCloud 教程 | 第一篇: 服务的注册与发现(Eureka)|修订版
- PostgreSQL索引探究
- 数据库事务的四大特性以及事务的隔离级别
- [Java集合] 彻底搞懂HashMap,HashTable,ConcurrentHashMap之关联.
- MISCONF Redis is configured to save RDB snapshots, but it is currently not able to persist on disk.
- Oracle数据库导入导出DMP文件
- 针对Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1的解决方案
- 初识node
- spring+jpa(hibernate) 配置文件代码
- Android HTTPS之自签名证书认证(二)