首页 » 其他 » 正文

sqlite 数据库查询过慢排查

欢迎使用WordPress。这是您的第一篇文章。编辑或删除它,然后开始写作吧!

在生产环境中遇到 sqlite 数据库查询很慢,之前从来没有接触过 sqlite 数据库,所以只能现查资料

查询 SQL 如下

 select * from table where field=xx ORDER by id desc limit 50

猜测 sqlite SQL语句写法 跟 mysql 应该很类似

DB 连接代码片段

$file = '..../log.db';
$this->pdo = new \PDO("sqlite:{$file}");

log.db文件将近35GB,难怪会这么慢

版权声明:枫叶林(https://blog.maplemark.cn)原创内容,请勿非法转载!已委托相关维权机构!

登录数据库

[root@ data]# sqlite3 log.db 
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

查看数据库

sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /..../log.db 

查看表

sqlite> .tables
log_~      log_~         log_~      log_~     
log_~  log_~      log_~     log_~

数据量并没有多少

sqlite> select count(1) from log_~;
27005

查看表结构

sqlite> .schema log_~
CREATE TABLE log_~
(
  id INTEGER PRIMARY KEY AutoIncrement,
  ~ varchar(32),
  time int,
  ~ varchar(32),
  ~ text
);

sqlite删除数据后,文件占空间不变

按照时间维度删除了部分数据,发现 log.db 文件大小依然没有什么变化,猜测跟 mysql 类似问题,即删除了文件,但是实际使用空间并不会减少

手动优化

sqlite> VACUUM;
[root@~ data]# du -sh *
35G    log.db
3.2G    log.db-journal
[root@~ data]# du -sh *
3.7M    log.db

使用 VACUUM 进行优化,优化过程比较漫长,会生成一个临时文件 *-journal,而且还会锁表,暂时不可用,直到优化完成

sqlite基本操作

  • 打开数据库
[LOCAL]# sqlite3 log.db
  • 帮助
sqlite> .help
  • 列出所有表
sqlite> .taples
  • 列出所有库
sqlite> .databases
  • 列出表结构
sqlite> .schema table_name
  • 退出数据库
sqlite> .exit

其他问题

如果 log.db 没有权限,也会导致 VACUUM 执行失败

sqlite> VACUUM;
Error: attempt to write a readonly database

发表评论