SQLite 翻页功能优化

好久没有接触数据库了，最近因为工作的原因，又开始在Qt上使用数据库，这次主要用的是Qt自带的sqlite,使用方便简单。

项目需求：需要实时存储网络报文数据，并能实时查询，查询时要求全部查询或自定义查询，且都具有翻页、跳转等功能。

实现过程中遇到了以下问题：

  1. 网络报文是实时的，且数量不定，故一条一条进行存储效率太低；
  2. 翻页显示时，界面要求只能显示最多25条数据，随着数据量增到，使用limit进行分页，检索效率越来越低。

针对问题一：

经查询资料，决定采用事务的方式进行存储，即使用db.transaction()。但是这种方式支持大批量的数据，且在进行执行事务时，不支持多线程同时读写。

故在实现时，采用定时器，先将网络报文进行临时存储，当定时器溢出时，将缓存数据一次性存储到数据库中。由于项目中建立了2个线程，一个写，一个读，由于采用事务机制，不支持同时读写，需要在线程加上互斥锁进行保护。

 1 bool status = true;
 2 db.transaction();
 3 QSqlQuery query(db)
 4 query.prepare("INSERT INTO data_log(timeStamp,pipe,topic,srcType)VALUES (:timeStamp,:pipe,:topic,:srcType)"
 5 for(int i=0;i<num,i++)
 6 {
 7    LOGMSG log = dataList[i];//dataList缓存了要存的数据内容    
 8    query.bindValue(":srcType",log.srcType);
 9    //省略，主要是进行参数绑定
10    query.bindValue(":timeStamp",static_cast<int>(log.TimeStamp));
11    status = query.exec();
12    if(!status)
13    {
14         qDebug()<<"addData db exec() is error!<<endl;
15         qDebug()<<query.lastError().text()<<endl;
16         db.rollback();
17         return;
18    }
19    query.finish();
20 
21 }
22 db.commit();//提交事务
23 dataList.remove(0,num);
View Code

 

针对问题二：

为了实现分页功能，刚开始采用的SQL语句如下：

select * from data_log limit 偏移量起始位置，条数
// 举例
query.prepare("select * from data_log limit "+QString::number(CurrentItem)+","+QString::number(PAGE_NUMBER));

但是随着数据量越来越大，偏移量的值也越来越大，检索效率越来越低，故将SQL语句优化如下：

 

select * from data_log where id>=偏移量 limit 条数
//举例
query.prepare(QString("select * from data_log where id>= %1 limit %2").arg(CurrentItem-1).arg(PAGE_NUMBER));

检索效率明显提升！（当然这里有个前提，创建数据库时，id为主键，且为AUTOINCREMENT)

检索效率差，除了优化SQL语句，建立索引也是一种好方法，效果也很明显，这里不再赘述。

 

posted @ 2022-11-16 09:43  羽……  阅读(201)  评论(0)  编辑  收藏  举报