Postgresql数据库全文索引初试【对象关系型数据库管理系统】

在postgresql中使用全文索引介绍     

       在近期的开发中由于需要处理比较大的数据量，但是由于没有资金购买商业数据库，而且最好不能用盗版的，所以没办法就用了这个Postgresql对象关系型数据库。首先介绍一下这个数据库，百度百科：“PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES，版本 4.2为基础的对象关系型数据库管理系统（ORDBMS）。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性：复杂查询、外键、触发器、视图、事务完整性、多版本并发控制。同样，PostgreSQL 可以用许多方法扩展，比如， 通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言。并且，因为许可证的灵活，任何人都可以以任何目的免费使用，修改，和分发 PostgreSQL， 不管是私用，商用，还是学术研究使用”。

          它是和mysql一样开源的数据库，但是其功能却比mysql强大多了，它可以处理上百万甚至上千万条的数据，据我了解mysql到来100W条数据时，其效率将会受到严重的影响。本篇文章主要讲一下给数据库全文索引的基本使用。全文索引在商业型数据管理系统中是非常常用的，因为它可以提高数据的检索效率，在没有全文索引的时候我们一般都是用like子句来匹配我们搜索的关键词，我们知道like子句执行的效率是非常低的，这严重影响了系统的运行效率。因此全文索引就可以提高数据的检索效率。全文索引是为我们需要被用户检索的信息进行创建全文索引，独立的存储在一个表中或者是一个字段上面。一下面将就在表中和字段中创建全文索引来进行介绍。

          一、使用独立的表存储全文索引的信息

         下面我举例介绍：（注意postgresql在8.3以后在自带了全文索引，以前的版本需要安装其他的包，具体我也不是很清楚，有兴趣的可以自己看看）

          首先我们创建一个数据表：

         

CREATE TABLE author(  id bigserial NOT NULL,  "name" text NOT NULL,  author_key text NOT NULL,  CONSTRAINT author_key PRIMARY KEY (id),  CONSTRAINT "key" UNIQUE (author_key))WITH (  OIDS=FALSE);

      我们为该表的的name字段创建一个全文索引，并将它的信息独立的存储在一个表中，表如下：

     

CREATE TABLE full_text_index(
 id bigserial NOT NULL,
 text_fti tsvector NOT NULL,)WITH (  OIDS=FALSE);ALTER TABLE full_text_index OWNER TO postgres;
）;

         我们注意看字段text_fti，它的类型是tsvector，说明该字段是存储全文索引的，这就是postgresql数据库中全文索引的字段类型。

       那么如何将我们的信息自动的创建全文索引信息添加到我们的full_text_index 中呢？那么这里将要用到sql中的触发器，

       如下创建触发器函数：

       

CREATE OR REPLACE FUNCTION node_text_fti()  RETURNS trigger AS$BODY$begin  if TG_OP = 'INSERT' then   insert into full_text_index (text_fti) values(to_tsvector(coalesce(NEW.name,'')));  end if;   return NEW;end;$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;ALTER FUNCTION node_text_fti() OWNER TO postgres;

           上面的insert语句的values中一个to_tsvector（coalesce（NEW.name,''））,这就是这个数据库中一个创建全文索引的函数，我们通过调用它，并把相应需创建索引的值给他，就会自动给我们分词。同时需要在author表中创建一个触发器：

           

CREATE TRIGGER create_author_index  AFTER INSERT  ON author  FOR EACH ROW  EXECUTE PROCEDURE node_text_fti();

            这样在你向author表中插入数据的时候将会自动在full_text_index中创建全文索引，此处需要注意的是，需要将这两个表关联起来，可以设置相同的自动递增字段id，那么可以通过这个id来关联这两个表，从而使得在full_text_index表中查询能够映射到author表中。在查询的时候需要通过如下的sql语句进行查询：       

select * from author as a join full_text_index as fti on a.id=fti.id where fti.text_fti @@ to_tsquery('keyword');

           通过join运算将两个表关联起来，这就实现了全文索引，一般情况下都是采用使用一个独立的表来存储全文索引信息。

        二、使用字段存储全文索引信息

        首先也创建一个数据表：

        

CREATE TABLE test_fti(  "ID" integer NOT NULL DEFAULT nextval('"testFTI_ID_seq"'::regclass),  "content" text NOT NULL,  content_fti tsvector,  CONSTRAINT "testID" PRIMARY KEY ("ID"))WITH (  OIDS=FALSE);ALTER TABLE test_fti OWNER TO postgres;

           此处的字段content_fti则是存储全文索引的信息，此处也类似需要创建触发器函数：

        

CREATE OR REPLACE FUNCTION messages_trigger()  RETURNS trigger AS$BODY$begin  new.content_fti :=     setweight(to_tsvector( coalesce(new.content,'')));  return new;end$BODY$  LANGUAGE plpgsql VOLATILE  COST 100;ALTER FUNCTION messages_trigger() OWNER TO postgres;

          在表test处创建一个触发器：

          

CREATE TRIGGER ftitesttrigger  BEFORE INSERT OR UPDATE  ON test_fti  FOR EACH ROW  EXECUTE PROCEDURE messages_trigger();

         这是在你想test中插入数据的时候就会自动将指定字段的信息创建全文索引，并添加到对应的记录中，此处的查询语句就比较简单：

select * from test where content_fti @@ to_tsquery('keyword');

            本文到次就将在postgresql中简单使用全文索引介绍完了，介绍有不到位支出希望指出，谢谢浏览！