spatialite性能优化

一、sqlite部分优化：

1.建立索引

参考链接：

https://blog.csdn.net/lianghe_work/article/details/45060731

http://www.cnblogs.com/noTice520/archive/2012/09/29/2708069.html

https://blog.csdn.net/chu_qun/article/details/3719532

https://blog.csdn.net/h183288132/article/details/49465891

例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。

建立：create index idxT1 on t1(num,word1,word2);

注意spatialite并不会默认把主列建立索引。

这里索引名可以随便取，只要保证不重复，容易识别就行了，通常是表名加列名

使用：select count(*) from t1,t3 where t1.word2=t3.word2;

使用时照常使用

2.以内存模式打开：

官方给的函数是

int LoadOrSaveDb(sqlite3 *pInMemory, const char *zFilename, int isSave){
    int rc;                   /* Function return code */
    sqlite3 *pFile;           /* Database connection opened on zFilename */
    sqlite3_backup *pBackup;  /* Backup object used to copy data */
    sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
    sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

    /* Open the database file identified by zFilename. Exit early if this fails
    ** for any reason. */
    rc = sqlite3_open(zFilename, &pFile);
    if( rc==SQLITE_OK ){

        /* If this is a 'load' operation (isSave==0), then data is copied
        ** from the database file just opened to database pInMemory.
        ** Otherwise, if this is a 'save' operation (isSave==1), then data
        ** is copied from pInMemory to pFile.  Set the variables pFrom and
        ** pTo accordingly. */
        pFrom = (isSave ? pInMemory : pFile);
        pTo   = (isSave ? pFile     : pInMemory);

        /* Set up the backup procedure to copy from the "main" database of
        ** connection pFile to the main database of connection pInMemory.
        ** If something goes wrong, pBackup will be set to NULL and an error
        ** code and  message left in connection pTo.
        **
        ** If the backup object is successfully created, call backup_step()
        ** to copy data from pFile to pInMemory. Then call backup_finish()
        ** to release resources associated with the pBackup object.  If an
        ** error occurred, then  an error code and message will be left in
        ** connection pTo. If no error occurred, then the error code belonging
        ** to pTo is set to SQLITE_OK.
    */
        pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
        if( pBackup ){
            (void)sqlite3_backup_step(pBackup, -1);
            (void)sqlite3_backup_finish(pBackup);
        }
        rc = sqlite3_errcode(pTo);
    }

    /* Close the database connection opened on database file zFilename
    ** and return the result of this function. */
    (void)sqlite3_close(pFile);
    return rc;
}

二、spatialite空间查询优化 

1.空间索引

和sqlite相似，对geometry的boundingbox建立索引。

建立索引：SELECT CreateSpatialIndex('TableName', 'geometry');即对表面为TableName的几何建立了索引

使用：

Spatialite官方文档说明，与sqlite不同，不会自动使用建立的索引进行查询，这里需要：

SELECT col FROM TableName WHERE ROWID IN (SELECT pkid FROM idx_TableName_geometry WHERE xmin > 10 AND xmax<20 AND ymin>10 AND ymax <20);