一、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);