把 Lucene 索引数据存到数据库表中 | 隔叶黄莺 Yanbin Blog

2010-10-19 | 阅读(2,240)

一般我们都是把 Lucene 索引存放在文件系统中，大数据量时会考虑用分布式文件系统，如 Hadoop 及 MapReduce、GFS 的应用。也许你会想我们有数据库作为集中的数据存储地，是否可以把 Lucene 索引文件存储到关系型数据库中。可以这么做，不过好像性能上有些问题，本文就此也作这样一个尝试。

看 http://wiki.apache.org/lucene-java/LuceneFAQ
Can I store the Lucene index in a relational database?
Lucene does not support that functionality out of the box, but several people have implemented JdbcDirectory's. The reports we have seen so far indicate that performance with such implementations is not great, but it is doable.

Lucene 里内置了 FSDirectory、MMapDirectory、RAMDirectory 这样的与索引存储相关的实现。如果要存储到数据库中，必须实现一个 DbDirectory,对于 Java 也就是 JdbcDirectory。但事情远没有这么简单，还需要一堆的类来为 JdbcDirectory 服务，如锁机制、缓存机制、内存镜像、不同数据库讲的方言也不一样。

幸好 Compass 实现了把索引存储到数据库的功能，我们可以借用它的代码。先到 http://www.compass-project.org/ 下载最新版的 compass-2.2.0-with-dependencies.zip，当前是 2.2.0，所使用的 Lucene 是 2.4.1，有点老了。

所以我们也用 Lucene 2.4.1，用最新的 3.0.2 会有些问题，另外也依赖了compass 的 compass-2.2.0.jar 包。再就是直接使用 compass 为我们写好的 JdbcDirectory 和一批外缘类，需要把 compass-2.2.0-with-dependencies.zip\compass-2.2.0\src\main\src\org\apache\lucene 目录里的类引进到我们的工程中。

现在就可以开始来写我们的代码了，要演示的例子是索引存储到 MySql 数据库表中，并基于该表里的索引数据进行查询。还有数据库和表要我们自己预先创建好，比如数据库是 unmi_db，索引表是 lucene_index，创建表的 SQL 如下：

CREATE TABLE `lucene_index` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name_` varchar(50) DEFAULT NULL,
  `value_` blob,
  `size_` decimal(10,2) DEFAULT NULL,
  `lf_` timestamp NULL DEFAULT NULL,
  `deleted_` bit(1) DEFAULT NULL,
  PRIMARY KEY (`Id`)
)

CREATE TABLE `lucene_index` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`name_` varchar(50) DEFAULT NULL,

`value_` blob,

`size_` decimal(10,2) DEFAULT NULL,

`lf_` timestamp NULL DEFAULT NULL,

`deleted_` bit(1) DEFAULT NULL,

PRIMARY KEY (`Id`)

)

请参考这个：Appendix A. Lucene Jdbc Directory

看 Java 代码了：

package cc.unmi.lucene;

import java.io.IOException;
import java.sql.Connection;

import javax.sql.DataSource;

import org.apache.lucene.analysis.standard.StandardAnalyzer;
import org.apache.lucene.document.*;
import org.apache.lucene.index.IndexWriter;
import org.apache.lucene.queryParser.QueryParser;
import org.apache.lucene.search.*;
import org.apache.lucene.store.jdbc.*;
import org.apache.lucene.store.jdbc.datasource.*;
import org.apache.lucene.store.jdbc.dialect.MySQLDialect;
import org.apache.lucene.store.jdbc.lock.NoOpLock;

public class TestJdbcDirectory {

    private static DataSource ds = null;

    static {
        ds = new DriverManagerDataSource("com.mysql.jdbc.Driver",
                "jdbc:mysql://localhost:3306/unmi_db", "root", "", false);
    }

    /**
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) {
        index();
        search();
    }

    private static void index() {

        JdbcDirectorySettings settings = new JdbcDirectorySettings();

        settings.setLockClass(NoOpLock.class);
        JdbcDirectory jdbcDir = new JdbcDirectory(ds, new MySQLDialect(),
                 "lucene_index");

        Connection conn = null;
        try {
            conn = DataSourceUtils.getConnection(ds);

            IndexWriter writer = new IndexWriter(jdbcDir, new StandardAnalyzer(), true);

            Document doc = new Document();

            Field field1 = new Field("url", "<a href="http://unmi.cc">http://unmi.cc</a>", Field.Store.YES,
                    Field.Index.NOT_ANALYZED);
            Field field2 = new Field("title", "隔叶黄莺 Unmi Blog - 软件编程实践",
                    Field.Store.YES, Field.Index.ANALYZED);
            doc.add(field1);
            doc.add(field2);

            writer.addDocument(doc);

            writer.optimize();

            writer.close();

            DataSourceUtils.commitConnectionIfPossible(conn);
        } catch (IOException e) {
            DataSourceUtils.safeRollbackConnectionIfPossible(conn);
            e.printStackTrace();
        } finally {
            DataSourceUtils.releaseConnection(conn);
        }
    }

    private static void search() {

        JdbcDirectorySettings settings = new JdbcDirectorySettings();
        settings.setLockClass(NoOpLock.class);

        JdbcDirectory jdbcDir = new JdbcDirectory(ds, new MySQLDialect(),
                settings, "lucene_index");

        Connection conn = null;
        try {
            conn = DataSourceUtils.getConnection(ds);
            Searcher searcher = new IndexSearcher(jdbcDir);

            QueryParser parser = new QueryParser("title",new StandardAnalyzer());

            TopDocs topDocs = searcher.search(parser.parse("unmi blog"),20);   

            // Get an array of references to matched documents
            ScoreDoc[] scoreDosArray = topDocs.scoreDocs;   
               for(ScoreDoc scoredoc: scoreDosArray){
                  //Retrieve the matched document and show relevant details
                  Document doc = searcher.doc(scoredoc.doc);
                  System.out.println(doc.get("url") + "; Score: " + scoredoc.score
                            +"\r\n"+doc.get("title"));
               }

        }catch(Exception ex){
            ex.printStackTrace();
        }
        finally {
            DataSourceUtils.releaseConnection(conn);
        }
    }
}

100

101

102

103

104

105

106

107

package cc.unmi.lucene;

import java.io.IOException;

import java.sql.Connection;

import javax.sql.DataSource;

import org.apache.lucene.analysis.standard.StandardAnalyzer;

import org.apache.lucene.document.*;

import org.apache.lucene.index.IndexWriter;

import org.apache.lucene.queryParser.QueryParser;

import org.apache.lucene.search.*;

import org.apache.lucene.store.jdbc.*;

import org.apache.lucene.store.jdbc.datasource.*;

import org.apache.lucene.store.jdbc.dialect.MySQLDialect;

import org.apache.lucene.store.jdbc.lock.NoOpLock;

public class TestJdbcDirectory {

private static DataSource ds = null;

static {

ds = new DriverManagerDataSource("com.mysql.jdbc.Driver",

"jdbc:mysql://localhost:3306/unmi_db", "root", "", false);

}

/**

* @param args

* @throws Exception

public static void main(String[] args) {

index();

search();

}

private static void index() {

JdbcDirectorySettings settings = new JdbcDirectorySettings();

settings.setLockClass(NoOpLock.class);

JdbcDirectory jdbcDir = new JdbcDirectory(ds, new MySQLDialect(),

"lucene_index");

Connection conn = null;

try {

conn = DataSourceUtils.getConnection(ds);

IndexWriter writer = new IndexWriter(jdbcDir, new StandardAnalyzer(), true);

Document doc = new Document();

Field field1 = new Field("url", "<a href="http://unmi.cc">http://unmi.cc</a>", Field.Store.YES,

Field.Index.NOT_ANALYZED);

Field field2 = new Field("title", "隔叶黄莺 Unmi Blog - 软件编程实践",

Field.Store.YES, Field.Index.ANALYZED);

doc.add(field1);

doc.add(field2);

writer.addDocument(doc);

writer.optimize();

writer.close();

DataSourceUtils.commitConnectionIfPossible(conn);

} catch (IOException e) {

DataSourceUtils.safeRollbackConnectionIfPossible(conn);

e.printStackTrace();

} finally {

DataSourceUtils.releaseConnection(conn);

}

private static void search() {

JdbcDirectorySettings settings = new JdbcDirectorySettings();

settings.setLockClass(NoOpLock.class);

JdbcDirectory jdbcDir = new JdbcDirectory(ds, new MySQLDialect(),

settings, "lucene_index");

Connection conn = null;

try {

conn = DataSourceUtils.getConnection(ds);

Searcher searcher = new IndexSearcher(jdbcDir);

QueryParser parser = new QueryParser("title",new StandardAnalyzer());

TopDocs topDocs = searcher.search(parser.parse("unmi blog"),20);

// Get an array of references to matched documents

ScoreDoc[] scoreDosArray = topDocs.scoreDocs;

for(ScoreDoc scoredoc: scoreDosArray){

//Retrieve the matched document and show relevant details

Document doc = searcher.doc(scoredoc.doc);

System.out.println(doc.get("url") + "; Score: " + scoredoc.score

+"\r\n"+doc.get("title"));

}

}catch(Exception ex){

ex.printStackTrace();

}

finally {

DataSourceUtils.releaseConnection(conn);

}

执行结果可以看到：

http://unmi.cc; Score: 0.10848885
隔叶黄莺 Unmi Blog - 软件编程实践

来看下数据库表里是什么样子，两次执行了上面程序的结果：

Lucene Index Database

红色的框里是第一次执行时产生的记录，蓝色框里的是第二次执行时产生的记录。

看到每次创建索引都会生成一批记录，一直累加，但是前面的记录是无法使用的。但是找不到办法来清除，即使创建 IndexWriter 是指定 create 为 true 也没用，删除策略指定只保留最后的也没用。如果是用文件系统来保存索引，只要 IndexWriter 的 create 为 true 就只会在索引目录中保存最新的文件。每次创建的时候 delete lucene_index 清理索引表是最不用思考的。

还有一个严重的 Bug 就是 org.apache.lucene.store.jdbc.dialect.MySQLDialect 类中

    /**
     * MySQL requires quoting the blob column with connector J 3.1 when using emulateLocators=true.
     */
    public String openBlobSelectQuote() {
        return "'";
    }

    public String closeBlobSelectQuote() {
        return "'";
    }

/**

* MySQL requires quoting the blob column with connector J 3.1 when using emulateLocators=true.

public String openBlobSelectQuote() {

return "'";

}

public String closeBlobSelectQuote() {

return "'";

}

改为反向的单引号，

    public String openBlobSelectQuote() {
        return "`";
    }

    public String closeBlobSelectQuote() {
        return "`";
    }

public String openBlobSelectQuote() {

return "`";

}

public String closeBlobSelectQuote() {

return "`";

}

不然你可能会碰到这样的错误：

org.apache.lucene.store.jdbc.JdbcStoreException: Failed to execute sql [select name_, 'value_' as x, size_ from lucene_index where name_ = ?]; nested exception is java.sql.SQLException: "pos" + "length" arguments can not be larger than the BLOB's length.
java.sql.SQLException: "pos" + "length" arguments can not be larger than the BLOB's length.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)

也许是要用 connector J 3.1 的驱动，不过我现在用的是 mysql-connector-java-5.1.13 的驱动。

还有就是 JdbcDirectory 是使用锁的时候有些麻烦事，我前面的代码用:

settings.setLockClass(NoOpLock.class);

禁用了锁，默认是用 PhantomReadLock 锁，请谨慎行事。要是碰到像更多的诸如此类：

------"org.apache.lucene.store.LockObtainFailedException: Lock obtain timed out: PhantomReadLock[write.lock/lucene_index]"------------

的错误后，希望你一直还有勇气四处找寻解决办法，在 compass 的世界里好像也为 JdbcDirectory 惹来了不少的疑问。的确 JdbcDirectory 为我们解决了集群环境的索引安置问题，但引来了新的问题，我想我还是回到老路上去，即使集群环境中搞一边一国也容易些，放共享目录好了。

本文链接 https://yanbin.blog/store-lucene-index-in-database/, 来自隔叶黄莺 Yanbin Blog

seetimee on 体验 Python FastAPI 的并发能力及线, 进程模型感谢
Yanbin on Mockito 3.4.0 开始可 Mock 静态方法有一个补救，新写了一篇 https://yanbin.blog/mockito-mock-static-method-in-multiple...
Yanbin on 升级到 Spring Boot 3 后 javax.inject.Named 不可用怎么，被抄袭了！算是被机器翻译引用的？
oleksi on 升级到 Spring Boot 3 后 javax.inject.Named 不可用https://www.springcloud.io/post/2023-02/springboot-3-javax-inject-name...
Zhe on 体验 Python FastAPI 的并发能力及线, 进程模型你在trio下得用 trio.sleep()，调用asyncio.sleep() 就会报错。

M	T	W	T	F	S	S
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30