之前在项目中使用了sqlite数据库，当日志变大时，执行CRUD操作就会变慢
后来尝试删除7天前的记录进行优化
delete from XX_CollectData where CreateTime<='2024-01-24'发现sqlite文件的大小就没有变化，delete命令只是逻辑删除，所在的文件的字节码仍然还在，优化效果仍不明显。
后来使用真空命令VACUUM，发现文件大小确实变小了。
VACUUM
SQLite Vacuum
VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。
如果表中没有明确的整型主键（INTEGER PRIMARY KEY），VACUUM 命令可能会改变表中条目的行 ID（ROWID）。VACUUM 命令只适用于主数据库，附加的数据库文件是不可能使用 VACUUM 命令。
如果有一个活动的事务，VACUUM 命令就会失败。VACUUM 命令是一个用于内存数据库的任何操作。由于 VACUUM 命令从头开始重新创建数据库文件，所以 VACUUM 也可以用于修改许多数据库特定的配置参数。
相关测试程序类【C#代码】
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;
namespace SnakeSqliteDemo
{
/// <summary>
/// 作者：斯内科
/// 时间：2024-01-24
/// 功能：sqlite数据库操作类
/// </summary>
public class SqliteDbHelper
{
/// <summary>
/// 连接字符串
/// </summary>
private string m_strConnectString = string.Empty;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="strDbPath">SQLite数据库文件路径</param>
public SqliteDbHelper(string strDbPath)
{
this.m_strConnectString = "Data Source=" + strDbPath;
}
/// <summary>
/// 创建SQLite数据库文件
/// </summary>
/// <param name="strDbPath">要创建的SQLite数据库文件路径</param>
/// <param name="strdbTableCreateString">要创建的SQLite数据库表格</param>
public static void CreateDB(string strDbPath, string strdbTableCreateString)
{
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + strDbPath))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = strdbTableCreateString;
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 对SQLite数据库执行增删改操作，返回受影响的行数。
/// </summary>
/// <param name="strSql">要执行的增删改的SQL语句</param>
/// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string strSql, SQLiteParameter[] parameters, bool IsDelete = false)
{
try
{
int intAffectedRows = 0;
using (SQLiteConnection connection = new SQLiteConnection(m_strConnectString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = strSql;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
intAffectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
if (IsDelete)
{
//sqlite 真空命令，文件压缩：VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。
//这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = "VACUUM";
command.ExecuteNonQuery();
}
}
}
}
return intAffectedRows;
}
catch(Exception ex)
{
Console.WriteLine("执行sql语句异常，" +ex.Message + "\r\n详细sql语句为：" + strSql);
return -1;
}
}
public int ExecuteNonQueryByDic(string strSql, Dictionary<string, object> dicParams)
{
try
{
int intAffectedRows = 0;
using (SQLiteConnection connection = new SQLiteConnection(m_strConnectString))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = strSql;
if (dicParams != null && dicParams.Count > 0)
{
command.Parameters.Clear();
foreach (string parameterName in dicParams.Keys)
{
command.Parameters.AddWithValue(parameterName, dicParams[parameterName]);
}
}
intAffectedRows = command.ExecuteNonQuery();
}
transaction.Commit();
}
}
return intAffectedRows;
}
catch(Exception ex)
{
Console.WriteLine("执行sql语句异常，" + ex.Message + "\r\n详细sql语句为：" + strSql);
return -1;
}
}
/// <summary>
/// 执行一个查询语句，返回一个关联的SQLiteDataReader实例
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns>返回SQLiteDataReader</returns>
public SQLiteDataReader ExecuteReader(string strSql, SQLiteParameter[] parameters)
{
SQLiteConnection connection = new SQLiteConnection(m_strConnectString);
SQLiteCommand command = new SQLiteCommand(strSql, connection);
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行一个查询语句，返回一个包含查询结果的DataTable
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>
/// <returns>返回数据表</returns>
public DataTable ExecuteDataTable(string strSql, SQLiteParameter[] parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(m_strConnectString))
{
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
return data;
}
}
}
public DataTable GetDataTable(string strSql, Dictionary<string, object> dicParams)
{
using (SQLiteConnection connection = new SQLiteConnection(m_strConnectString))
{
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
if (dicParams != null && dicParams.Count > 0)
{
command.Parameters.Clear();
foreach (string parameterName in dicParams.Keys)
{
command.Parameters.AddWithValue(parameterName, dicParams[parameterName]);
}
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
return data;
}
}
}
public DataSet ExcuteDataSet(string strSql)
{
DataSet ds = new DataSet("MyDataSet");
using (SQLiteConnection connection = new SQLiteConnection(m_strConnectString))
{
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
adapter.Fill(ds);
return ds;
}
}
}
/// <summary>
/// 执行一个查询语句，返回查询结果的第一行第一列
/// </summary>
/// <param name="strSql">要执行的查询语句</param>
/// <returns>返回第一行第一列的值</returns>
public object ExecuteScalar(string strSql)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(m_strConnectString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(strSql, connection))
{
return command.ExecuteScalar();
}
}
}
catch
{
throw;
}
}
public int ExecuteScalarInt(string strSql)
{
object oRes = ExecuteScalar(strSql);
return oRes == null ? 0 : Convert.ToInt32(oRes);
}
/// <summary>
/// 查询数据库中的所有数据类型信息
/// </summary>
/// <returns>返回数据表</returns>
public DataTable GetSchema()
{
using (SQLiteConnection connection = new SQLiteConnection(m_strConnectString))
{
connection.Open();
DataTable data = connection.GetSchema("TABLES");
connection.Close();
return data;
}
}
}
}
