PostgreSQL索引创建方法及相关问题
发布时间：2020-12-13 17:35:14 所属栏目：百科 来源：网络整理
导读：IT程序员开发必备-各类资源下载清单 ，史上最全IT资源，个人收藏总结！ postgre建索引方法： 单字段索引： CREATE INDEX index_name ON table_name (field1); 联合索引： CREATE INDEX index_name ON table_name (field1,field2); mysql的联合索引的使用，在
IT程序员开发必备-各类资源下载清单，史上最全IT资源，个人收藏总结！
单字段索引：
CREATE INDEX index_name ON table_name (field1);
联合索引：
CREATE INDEX index_name ON table_name (field1,field2);
mysql的联合索引的使用，在select里的where条件是要求有序的，比如where field1=100和where field1=100 and field2=1000都可以利用到上面这个组合索引（多条件尽量不要用or，至少用
UNION代替OR
），但使用where field2=1000或where field2=1000 and field1=100就利用不到索引了。
在postgre里，postgre的查询规则器会自动优化和选择索引，所以，一般情况下，mysql中的后两种情况，也是可以利用到组合索引的。
上面建索引的方法跟mysql中类似。
条件(部分)索引：
CREATE INDEX index_name ON table_name (field1) WHERE field1 > 100;
注意：想要使用条件索引，在使用select语句时，where条件最好加上field>100（最少100）的条件，否则索引很可能利用不到。
postgre里看索引是否利用情况跟mysql中一样，但返回信息的格式差别巨大。
看查询规则使用：
EXPLAIN
1、没对字段field1建索引前：
EXPLAIN SELECT * FROM table_name WHERE field1 = 1000; QUERY PLAN ------------------------------------------------------------ Seq Scan on table_name (cost=0.00..393.00 rows=6243 width=345) Filter: (field1 = 1000)
cost里面的是些postgre的预估时间等，关键看红色部分，filter表示全表扫描。 2、对field建索引后：
EXPLAIN SELECT * FROM table_name WHERE field1 = 1000; QUERY PLAN ------------------------------------------------------------ Index Scan using index_name on table_name (cost=0.00..10.00 rows=1 width=243) Index Cond: (field1 = 1000)
这次，利用到了索引。
当然，利用EXPLAIN至少postgre规则器的预估值，要看准确值可以利用ANALYZE.
如：
EXPLAIN ANALYZE SELECT * FROM table_name WHERE field1 = 1000; QUERY PLAN ------------------------------------------------------------ Index Scan using index_name on table_name (cost=0.00..10.00 rows=1 width=243)(actual time=0.025..0.053 rows=1 loops=1) Index Cond: (field1 = 1000) actual部分就是真实执行所用的开销。 PostgreSql查询时使用了查询规则器，这也导致一些意料之外的情况，比如索引利用不到（查询规则器自以为是的帮你“优化”了查询方式，导致利用不了结果）。如： EXPLAIN SELECT * FROM table_name WHERE field1 > 1000; QUERY PLAN ------------------------------------------------------------ Seq Scan on table_name (cost=0.00..393.00 rows=62430 width=345) Filter: (field1 > 1000)
当field1>1000的结果集很多，或者postgre的查询规则器认为很多，它会觉得直接使用filter会更合理，就会使索引白建了，这方面要引起注意。
另外一类情况在使用order by子句时，postgre往往会多做一些额外的sort（在建立了多字段索引的情况下），如（下面内容引用自参考文献）：
CREATE INDEX mytable_categoryid_userid_
ctime
ON mytable (category_id,user_id,ctime);
EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY
DESC; NOTICE:QUERY PLAN: Sort(cost=2.03..2.03 rows=1 width=16) ->Index Scanusing mytable_categoryid_userid_ctime
onmytable(cost=0.00..2.02 rows=1 width=16)
数据库多做了一个没有要求的排序。
为了跳过排序这一步，并不需要其它另外的索引，只要将查询语句稍微改一下。这里用的是postgres，将给该数据库一个额外的提示--在ORDERBY语句中，加入where语句中的字段。这只是一个技术上的处理，并不是必须的，因为实际上在另外两个字段上，并不会有任何的排序操作，不过如果加入，postgres将会知道哪些是它应该做的。
EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY category_id DESC,user_id DESC,
ctime
DESC; NOTICE:QUERY PLAN: Index Scan Backward using mytable_categoryid_userid_add daon my table
(cost=0.00..2.02 rows=1 width=16)
这就避免了不必要的sort了。
注意：上面提到的联合索引、多字段索引、组合索引指的是同一个东西。
还有，mysql中可以利用select SQL_CACHE/SQL_NO_CACHE * from table_name来让mysql利用或不利用查询缓存。在postgresql中貌似没这种概念，也就没有对应关键字。
参考：
http://www.perfgeeks.com/?p=460 （主要是将mysql索引的，讲的很好）
http://www.kuqin.com/postgreSQL8.1_doc/performance-tips.html
http://baike.baidu.com/view/2079871.htm （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |