title: SQLite数据库插入优化
date: 2022-05-24 15:47:27
SQLite数据库插入优化 #
需求 #
视频取证业务中，由于部分监控录像品牌视频碎块太多，扫描视频过程中每三秒会产生百万级数据需要插入数据库，原有数据插入接口太慢，无法达到要求，导致我们产品的扫描速度很慢，极大影响客户体验。
视频取证产品使用的是SQLite数据库，SQLite 是一种轻量级、基于文件的关系数据库管理系统 (RDBMS)，以其简单性、可移植性和独立性而闻名。它适用于需要独立数据库解决方案的嵌入式系统、移动应用程序和小型项目。SQLite的设计初衷是用于单用户或嵌入式应用，专注于轻量级、低资源消耗。因此，当插入数据量较大时，SQLite的性能会受到影响，比不上MySQL等其他数据库。若替换其他数据库完全没有必要。
性能优化过程 #
现有性能 #
以465.76GB镜像文件为例，取最后十次提交日志：
本次提交数量：5897 本次提交耗时：0.3652427 本次提交速率：16145.428779274713 总提交数量：126400613 总提交耗时：4654.896980299996 平均速率：27154.330919661686
本次提交数量：358796 本次提交耗时：12.5037099 本次提交速率：28695.16350503301 总提交数量：126759409 总提交耗时：4667.4006901999965 平均速率：27158.45872546426
本次提交数量：50902 本次提交耗时：3.0472488 本次提交速率：16704.24810734194 总提交数量：126810311 总提交耗时：4670.447938999996 平均速率：27151.637842076394
本次提交数量：5102 本次提交耗时：0.997792 本次提交速率：5113.2901446393635 总提交数量：126815413 总提交耗时：4671.445730999996 平均速率：27146.93058691558
本次提交数量：375604 本次提交耗时：14.2310447 本次提交速率：26393.28369195552 总提交数量：127191017 总提交耗时：4685.676775699996 平均速率：27144.64165766083
本次提交数量：490755 本次提交耗时：17.804466 本次提交速率：27563.589944230844 总提交数量：127681772 总提交耗时：4703.481241699996 平均速率：27146.227536319784
本次提交数量：254451 本次提交耗时：9.9592031 本次提交速率：25549.333359814704 总提交数量：127936223 总提交耗时：4713.440444799996 平均速率：27142.853399398085
本次提交数量：543249 本次提交耗时：19.6061585 本次提交速率：27708.07958121934 总提交数量：128479472 总提交耗时：4733.046603299996 平均速率：27145.194790691683
本次提交数量：424834 本次提交耗时：14.9654391 本次提交速率：28387.673569831975 总提交数量：128904306 总提交耗时：4748.012042399996 平均速率：27149.111006644
本次提交数量：491245 本次提交耗时：18.247528 本次提交速率：26921.180775828925 总提交数量：129395551 总提交耗时：4766.259570399996 平均速率：27148.238380382798
扫描完成！耗时：1h36m29.3569587s
扫描任务总耗时1小时36分钟29秒，最终提交1亿两千万条数据，提交数据总耗时4766秒，平均速率每秒提交两万七千一百多条数据。
申明：由于环境影响，每次测试数据上下有波动。
第一次优化： #
情况分析 #
后台为了适配多种数据结构的适配，采用了统一的接口，并通过反射操作进行数据存储，反射会极大的影响数据处理性能。
func (ds *DataService) SaveData(cid, eid, tid int64, typeName string, datas interface{}) (row int64, err error) {
v, ok := vmodel.DataType[typeName]
if !ok {
err = fmt.Errorf("not exist typename %s", typeName)
return
}
t := reflect.TypeOf(v)
if t == nil {
err = fmt.Errorf("invalid vmodel.DataType: %s", typeName)
return
}
result, err := json.Marshal(datas)
if err != nil {
return
}
dataArray := reflect.New(reflect.PtrTo(reflect.SliceOf(reflect.PtrTo(t))))
err = json.Unmarshal([]byte(result), dataArray.Interface())
if err != nil {
return
}
arrayElem := dataArray.Elem().Elem()
if !arrayElem.IsValid() {
return
}
arrayData := make([]interface{}, arrayElem.Len(), arrayElem.Len())
for i := 0; i != arrayElem.Len(); i++ {
e := arrayElem.Index(i)
if e.CanAddr() {
arrayData[i] = e.Addr().Interface()
} else {
arrayData[i] = e.Interface()
}
}
row, err = proxy.Data.SaveData(cid, eid, typeName, arrayElem.Interface())
if err != nil {
return
}
return
}
优化方案 #
针对大数据量的数据结构，单独适配提交接口，省去反射操作导致的性能影响。
if TypeName == file.RelateBlockType {
_, err = service.Data.SaveData_RelateBlock(Task.CaseId, Task.EvidenceId, Task.Id, TypeName, Result)
} else {
_, err = service.Data.SaveData(Task.CaseId, Task.EvidenceId, Task.Id, TypeName, Result)
}
使用事务机制，可以批量插入数据，可以极大的提升写入速度。
func (dp *DataProxy) SaveData_RelateBlock(eid int64, typeName string, relateBlock []*file.RelateBlock) (row int64, err error) {
engin, _, err := db.GetDataEngine(eid, typeName, true)
if err != nil {
return 0, err
}
tableName := db.GetDataTableName(eid, typeName)
const batchSize = 1000
totalRows := int64(0)
// 开始事务
err = engin.Transaction(func(tx *gorm.DB) error {
for i := 0; i < len(relateBlock); i += batchSize {
end := i + batchSize
if end > len(relateBlock) {
end = len(relateBlock)
}
batch := relateBlock[i:end]
result := tx.Table(tableName).Create(batch) // 使用事务对象插入
if result.Error != nil {
return result.Error
}
totalRows += result.RowsAffected
}
return nil
})
if err != nil {
return totalRows, err
}
return totalRows, nil
}
batchSize = 1000，自测最优速度，实测batchSize 最大值在3500-3700之间，当大于这个值时，会报错：too many SQL variables。网上说batchSize等于SQLITE_MAX_VARIABLE_NUMBER，经过测试好像不是，这个问题没有深究。
- 版本在 2020-05-22 号前的 3.32.0 默认 999
- 版本在 3.32.0 后的默认 32766
SQLITE_MAX_VARIABLE_NUMBER 限制了 SQL 查询中绑定变量（占位符）的最大数量。它的默认值是 32766，即每个查询最多允许 32766 个绑定变量。
如何查询SQLITE_MAX_VARIABLE_NUMBER值？
sqlite3 （命令行输入）
SQLite version 3.47.0 2024-10-21 16:30:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .limits （命令行再输入）
length 1000000000
sql_length 1000000000
column 2000
expr_depth 1000
compound_select 500
vdbe_op 250000000
function_arg 127
attached 10
like_pattern_length 50000
variable_number 32766 （SQLITE_MAX_VARIABLE_NUMBER）
trigger_depth 100
worker_threads 0
sqlite>
性能对比 #
本次提交数量：16071 本次提交耗时：0.1709622 本次提交速率：94003.23580300207 总提交数量：126664811 总提交耗时：1304.5277936 平均速率：97096.29156344254
本次提交数量：452834 本次提交耗时：4.7283291 本次提交速率：95770.40650575697 总提交数量：127117645 总提交耗时：1309.2561227 平均速率：97091.50317957111
本次提交数量：572900 本次提交耗时：5.5622101 本次提交速率：102998.6263913332 总提交数量：127690545 总提交耗时：1314.8183328 平均速率：97116.49268539922
本次提交数量：255012 本次提交耗时：2.7220906 本次提交速率：93682.40719100239 总提交数量：127945557 总提交耗时：1317.5404234 平均速率：97109.39772901089
本次提交数量：260614 本次提交耗时：2.5024996 本次提交速率：104141.47518744858 总提交数量：128206171 总提交耗时：1320.042923 平均速率：97122.7289402316
本次提交数量：239560 本次提交耗时：2.3600754 本次提交速率：101505.23157014391 总提交数量：128445731 总提交耗时：1322.4029984 平均速率：97130.5503355701
本次提交数量：288596 本次提交耗时：2.9334688 本次提交速率：98380.45661164011 总提交数量：128734327 总提交耗时：1325.3364671999998 平均速率：97133.31684894577
本次提交数量：568353 本次提交耗时：5.5328899 本次提交速率：102722.62963338563 总提交数量：129302680 总提交耗时：1330.8693571 平均速率：97156.5535791988
本次提交数量：9738 本次提交耗时：0.1054929 本次提交速率：92309.52983565719 总提交数量：129312418 总提交耗时：1330.9748499999998 平均速率：97156.16940470364
本次提交数量：83133 本次提交耗时：1.0249508 本次提交速率：81109.25909809524 总提交数量：129395551 总提交耗时：1331.9998007999998 平均速率：97143.82158487184
扫描完成！耗时：29m13.093422s
可以看到扫描任务总耗时已经缩短到29分钟13秒，最终提交1亿两千万条数据，提交数据总耗时1331秒，平均速率每秒97143条数据。插入性能已经极大提生。
第二次优化： #
情况分析 #
因为业务需求，每次刚刚写入数据库的数据，会立马被读取，去进行缩略图提取的任务，而sqlite默认在读的时候会对库加锁，不让继续写。因此读文件导致的锁竞争也会整体影响写数据的速度。
优化方案 #
WAL（Write-Ahead Logging）模式是SQLite3最新的日志模式，也是目前性能最佳的选择。WAL模式对并发读写访问提供了更好的支持，并减少了IO操作的次数，因此能够提高性能和响应时间。
在WAL模式下，日志记录不再是直接写入数据库文件，而是写入一个称为写入日志（write-ahead-log）的文件。在写入日志文件之前，事务对数据库的修改会被写入内存中的WAL文件。这意味着数据库的修改操作不再阻塞其他事务的读写操作。
WAL模式通过减少磁盘操作和并发读写访问的能力来提高性能。在WAL模式中，读操作可以立即完成，而不需要等待写操作完成。同时，WAL模式避免了频繁的IO操作，显著提升了写入性能。
在WAL模式下，读操作可以立即完成，而不需要等待写操作完成。当一个事务需要读取数据库时，它会先读取WAL文件，并根据逻辑记录的内容重构数据库的状态。即使同时有其他事务在修改数据库，读操作也不会受到影响。
写操作在WAL模式下也得到了改进。当一个事务执行写操作时，它首先将修改记录写入WAL文件，并将逻辑记录标记为“已提交”。然后，WAL文件的内容会按顺序写入数据库文件。通过这种方式，WAL模式避免了频繁的磁盘IO操作，提高了写入性能。
Gorm开启WAL模式方法：
func (db *DbManager) getSqlDriver(dsn string, WALModel bool) (*gorm.DB, error) {
gormDb, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{})
if err != nil {
return nil, err
}
if WALModel {
gormDb.Exec("PRAGMA journal_mode=WAL;")
}
return gormDb, err
}
性能对比 #
本次提交数量：63541 本次提交耗时：0.7611553 本次提交速率：83479.6788513461 总提交数量：126338735 总提交耗时：1174.5417230999994 平均速率：107564.28019138459
本次提交数量：207272 本次提交耗时：1.847883 本次提交速率：112167.27465970519 总提交数量：126546007 总提交耗时：1176.3896060999994 平均速率：107571.51061503252
本次提交数量：213402 本次提交耗时：1.9882569 本次提交速率：107331.20051035658 总提交数量：126759409 总提交耗时：1178.3778629999993 平均速率：107571.10514388548
本次提交数量：302122 本次提交耗时：2.6607481 本次提交速率：113547.76500639049 总提交数量：127061531 总提交耗时：1181.0386110999993 平均速率：107584.5698911207
本次提交数量：369731 本次提交耗时：3.4887755 本次提交速率：105977.29776536209 总提交数量：127431262 总提交耗时：1184.5273865999993 平均速率：107579.83601018421
本次提交数量：819281 本次提交耗时：7.422267 本次提交速率：110381.50473433522 总提交数量：128250543 总提交耗时：1191.9496535999992 平均速率：107597.28199311931
本次提交数量：7839 本次提交耗时：0.2082594 本次提交速率：37640.557881180874 总提交数量：128258382 总提交耗时：1192.1579129999993 平均速率：107585.06117469362
本次提交数量：436286 本次提交耗时：4.1007606 本次提交速率：106391.4825947167 总提交数量：128694668 总提交耗时：1196.2586735999994 平均速率：107580.96960142288
本次提交数量：430331 本次提交耗时：3.7038419 本次提交速率：116185.03478779696 总提交数量：129124999 总提交耗时：1199.9625154999994 平均速率：107607.52717862716
本次提交数量：270552 本次提交耗时：2.7364728 本次提交速率：98868.87967605598 总提交数量：129395551 总提交耗时：1202.6989882999994 平均速率：107587.64433892063
扫描完成！耗时：27m12.9716868s
可以看到扫描任务总耗时已经缩短到27分钟12秒，提交数据总耗时1202秒，平均速率每秒107587条数据。整体提升效果虽然不大，但由于并发读原因，缩略图任务从4小时12分钟，已缩短至 3小时46分分钟。
第三次优化： #
情况分析 #
数据提交是在插件通过http请求传输到后台，再进行存储。因此文件传输也会影响插入速度。
优化方案 #
由于是单机版产品，可以在插件中直接提交数据，减少http数据传输导致的速度太慢问题。
性能对比 #
本次提交数量：265830 本次提交耗时：1.727355 本次提交速率：153894.2487213109 总提交数量：126541024 总提交耗时：812.0033392999987 平均速率：155838.05863296922
本次提交数量：238051 本次提交耗时：1.6474042 本次提交速率：144500.66352871992 总提交数量：126779075 总提交耗时：813.6507434999987 平均速率：155815.10373191247
本次提交数量：411942 本次提交耗时：2.5760617 本次提交速率：159911.54249139296 总提交数量：127191017 总提交耗时：816.2268051999987 平均速率：155828.03234308705
本次提交数量：447637 本次提交耗时：2.9583972 本次提交速率：151310.6488878505 总提交数量：127638654 总提交耗时：819.1852023999987 平均速率：155811.71830991586
本次提交数量：9980 本次提交耗时：0.0691937 本次提交速率：144232.78419856145 总提交数量：127648634 总提交耗时：819.2543960999988 平均速率：155810.7403605792
本次提交数量：754626 本次提交耗时：5.0771531 本次提交速率：148631.72040252242 总提交数量：128403260 总提交耗时：824.3315491999988 平均速率：155766.52394854158
本次提交数量：9235 本次提交耗时：0.0687567 本次提交速率：134314.1831996009 总提交数量：128412495 总提交耗时：824.4003058999988 平均速率：155764.73477870916
本次提交数量：291033 本次提交耗时：2.0696496 本次提交速率：140619.45558320597 总提交数量：128703528 总提交耗时：826.4699554999988 平均速率：155726.8079057233
本次提交数量：288612 本次提交耗时：1.9096872 本次提交速率：151130.50975049735 总提交数量：128992140 总提交耗时：828.3796426999988 平均速率：155716.21192858677
本次提交数量：403411 本次提交耗时：2.2078511 本次提交速率：182716.579030171 总提交数量：129395551 总提交耗时：830.5874937999988 平均速率：155787.9837655704
扫描完成！耗时：20m38.326593s
扫描时间已经缩短到20分钟38秒，提交数据总耗时830秒，平均速率每秒155787条数据。
第四次优化： #
情况分析 #
在SQLite中，数据库配置的参数都由编译指示（pragma）来实现的，而其中synchronous选项有三种可选状态，分别是full、normal、off。简要说来，full写入速度最慢，但保证数据是安全的，不受断电、系统崩溃等影响，而off可以加速数据库的一些操作，但如果系统崩溃或断电，则数据库可能会损毁。
优化方案 #
SQLite3中，该选项的默认值就是full，如果我们再插入数据前将其改为off，则会提高效率，既禁用写同步。
需要注意，打开写同步会导致系统崩溃或断电的情况下导致数据丢失，目前对于我们产品来说问题不大，但取消任务也可能会导致数据丢失，但目前没有考虑。
func (db *DbManager) getSqlDriver(dsn string, WALModel bool) (*gorm.DB, error) {
gormDb, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{})
if err != nil {
return nil, err
}
if WALModel {
gormDb.Exec("PRAGMA journal_mode=WAL;")
gormDb.Exec("PRAGMA synchronous = OFF;") // 设置 PRAGMA synchronous 为 OFF，禁用写同步
}
return gormDb, err
}
性能对比 #
本次提交数量：320239 本次提交耗时：1.5155745999999999 本次提交速率：211298.7377856557 总提交数量：126338735 总提交耗时：641.0061334000002 平均速率：197094.42455703023
本次提交数量：304766 本次提交耗时：1.4957225 本次提交速率：203758.38432596956 总提交数量：126643501 总提交耗时：642.5018559000002 平均速率：197109.93802905208
本次提交数量：326637 本次提交耗时：1.6357978 本次提交速率：199680.54731458865 总提交数量：126970138 总提交耗时：644.1376537000002 平均速率：197116.4661321521
本次提交数量：668516 本次提交耗时：3.3291445 本次提交速率：200807.14429788193 总提交数量：127638654 总提交耗时：647.4667982000002 平均速率：197135.44285953158
本次提交数量：9980 本次提交耗时：0.0449063 本次提交速率：222240.5319520869 总提交数量：127648634 总提交耗时：647.5117045000002 平均速率：197137.18395031724
本次提交数量：640272 本次提交耗时：3.3323363 本次提交速率：192139.0707174423 总提交数量：128288906 总提交耗时：650.8440408000001 平均速率：197111.59349682406
本次提交数量：63170 本次提交耗时：0.3055877 本次提交速率：206716.4352491936 总提交数量：128352076 总提交耗时：651.1496285000002 平均速率：197116.10109595564
本次提交数量：470002 本次提交耗时：2.395864 本次提交速率：196172.23682145565 总提交数量：128822078 总提交耗时：653.5454925000001 平均速率：197112.64093830466
本次提交数量：302921 本次提交耗时：1.5790298 本次提交速率：191839.9513422736 总提交数量：129124999 总提交耗时：655.1245223000001 平均速率：197099.93231007463
本次提交数量：270552 本次提交耗时：1.4943262 本次提交速率：181052.83839632873 总提交数量：129395551 总提交耗时：656.6188485000001 平均速率：197063.41250421776
扫描完成！耗时：18m3.6127236s
扫描任务总耗时18分03秒，提交数据总耗时656秒，平均提交速率每秒197063条。
第五次优化： #
情况分析 #
数据扫描过程中产生的数据会等待数据完全提交之后，再继续扫描。因此提交数据中间的时间浪费也是导致扫描速度慢的主要原因。
优化方案 #
要保证插件在扫描的时候提交数据，提交数据的时候继续扫描，错开时间，同时还要保证数据提交协程每次只有一个？
var concurrencyCh = make(chan struct{}, 1)
case <-ticker.C: //3秒提交一次数据
concurrencyCh <- struct{}{}
relateFiles2 := make([]*file2.RelateBlock, len(relateFiles))
relateFiles = relateFiles[:0]
wg.Add(1)
go func() {
defer wg.Done()
s.writeData(relateFiles2)
<-concurrencyCh
}()
注意数据拷贝，不然会导致数据错乱
性能对比 #
本次提交数量：411798 本次提交耗时：2.1555518 本次提交速率：191040.64212235587 总提交数量：125191532 总提交耗时：647.2781692999998 平均速率：193412.2575698615
本次提交数量：450177 本次提交耗时：2.28864 本次提交速率：196700.6606543624 总提交数量：125641709 总提交耗时：649.5668092999998 平均速率：193423.84370808097
本次提交数量：444626 本次提交耗时：2.3642943 本次提交速率：188058.6524274918 总提交数量：126086335 总提交耗时：651.9311035999998 平均速率：193404.3862974849
本次提交数量：459672 本次提交耗时：2.3378427 本次提交速率：196622.2962733977 总提交数量：126546007 总提交耗时：654.2689462999998 平均速率：193415.88457718925
本次提交数量：424131 本次提交耗时：2.1475108 本次提交速率：197498.89034318243 总提交数量：126970138 总提交耗时：656.4164570999998 平均速率：193429.24240648208
本次提交数量：810616 本次提交耗时：4.3770795 本次提交速率：185195.63101378444 总提交数量：127780754 总提交耗时：660.7935365999998 平均速率：193374.70317502503
本次提交数量：698718 本次提交耗时：3.6598641 本次提交速率：190913.64621981455 总提交数量：128479472 总提交耗时：664.4534006999999 平均速率：193361.147470458
本次提交数量：466650 本次提交耗时：2.5062204 本次提交速率：186196.7127871116 总提交数量：128946122 总提交耗时：666.9596210999998 平均速率：193334.22582214556
本次提交数量：255661 本次提交耗时：1.4855797 本次提交速率：172095.10873095534 总提交数量：129201783 总提交耗时：668.4452007999998 平均速率：193287.0231477022
本次提交数量：193768 本次提交耗时：1.2214007 本次提交速率：158644.08789023946 总提交数量：129395551 总提交耗时：669.6666014999998 平均速率：193223.83811610774
扫描完成！耗时：13m31.9696606s
扫描任务总耗时13分31秒，提交数据总耗时669秒，平均提交速率每秒193223条。由于环境原因提交耗时和速率稍有下降，但整体扫描时间缩短，符合预期效果。
第六次优化： #
情况分析 #
DEFAULT_PAGE_SIZE=4096 是 SQLite 的一个配置选项，用来设置数据库的页面大小。SQLite 数据库的存储是基于“页面”的，每一页的数据通常包含一个数据库表或索引的多个记录。页面大小决定了每一页所能存储的数据量，影响着数据库的性能和空间效率。
增大页面大小（例如 8192 字节或更大）通常能提高写入操作的效率，因为每次 I/O 操作会处理更多的数据，减少了磁盘寻址的开销。然而，较大的页面可能会导致：
- 内存占用增大，因为 SQLite 会将更多的数据加载到内存中。
- 对于非常小的数据库来说，较大的页面可能会导致内存浪费，因为每一页的存储量过大，未完全填充页面会造成空间浪费。
- 缩短查询的效率。
页面大小最大值为65536，通常为512到32768之间的2次幂。
优化方案 #
实测PRAGMA page_size = 40960对于我的业务速度更快
func (db *DbManager) getSqlDriver(dsn string, WALModel bool) (*gorm.DB, error) {
gormDb, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{})
if err != nil {
return nil, err
}
if WALModel {
gormDb.Exec("PRAGMA page_size = 32768;")
gormDb.Exec("PRAGMA journal_mode=WAL;")
gormDb.Exec("PRAGMA synchronous = OFF;") // 设置 PRAGMA synchronous 为 OFF，禁用写同步
gormDb.Exec("VACUUM;") //设置页面大小后要执行VACUUM命令
}
return gormDb, err
}
当然还有其他配置项可进行调整优化，具体获取方式如下：
func Test_SQLite(t *testing.T) {
// 连接 SQLite 数据库
db, err := sql.Open("sqlite3", "your_database.db")
if err != nil {
log.Fatal(err)
}
defer db.Close()
_, err = db.Exec("PRAGMA journal_mode=WAL;")
if err != nil {
log.Fatal(err)
}
// 获取 SQLite 引擎的版本
var version string
row := db.QueryRow("SELECT sqlite_version();")
if err := row.Scan(&version); err != nil {
log.Fatal(err)
}
// 打印版本信息
fmt.Println("SQLite version:", version)
var limit int
err = db.QueryRow("SELECT sqlite_limit(9, -1)").Scan(&limit)
if err != nil {
log.Printf("Error getting limit: %v", err)
}
fmt.Printf("SQLITE_MAX_VARIABLE_NUMBER = %d\n", limit)
// 执行 PRAGMA compile_options 语句
rows, err := db.Query("PRAGMA compile_options;")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
// 打印所有选项，查找 SQLITE_MAX_VARIABLE_NUMBER
var option string
for rows.Next() {
err := rows.Scan(&option)
if err != nil {
log.Fatal(err)
}
fmt.Println(option)
if option == "SQLITE_MAX_VARIABLE_NUMBER" {
fmt.Println("SQLITE_MAX_VARIABLE_NUMBER is set.")
}
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
}
SQLite version: 3.46.1
2024/11/08 20:02:59 Error getting limit: no such function: sqlite_limit
SQLITE_MAX_VARIABLE_NUMBER = 0
ATOMIC_INTRINSICS=1
COMPILER=gcc-7.1.0
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=1
DEFAULT_WORKER_THREADS=0
DIRECT_OVERFLOW_READ
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_RTREE
ENABLE_UPDATE_DELETE_LIMIT
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=8192
MAX_EXPR_DEPTH=1000
MAX_FUNCTION_ARG=127
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=0xfffffffe
MAX_PAGE_SIZE=65536
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=32766
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_W32
OMIT_DEPRECATED
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1
性能对比 #
默认4096
本次提交数量：434742 本次提交耗时：2.2563143 本次提交速率：192677.94384851435 总提交数量：124924843 总提交耗时：682.1520413999997 平均速率：183133.42981956524
本次提交数量：442735 本次提交耗时：2.3260672 本次提交速率：190336.28951046645 总提交数量：125367578 总提交耗时：684.4781085999997 平均速率：183157.9073528314
本次提交数量：475816 本次提交耗时：2.430444 本次提交速率：195773.28257717518 总提交数量：125843394 总提交耗时：686.9085525999997 平均速率：183202.54351714425
本次提交数量：414459 本次提交耗时：2.2304693 本次提交速率：185816.94892639856 总提交数量：126257853 总提交耗时：689.1390218999996 平均速率：183211.00530905818
本次提交数量：429761 本次提交耗时：2.2469453 本次提交速率：191264.55815368536 总提交数量：126687614 总提交耗时：691.3859671999996 平均速率：183237.1786674586
本次提交数量：542287 本次提交耗时：2.8832281 本次提交速率：188083.28068112265 总提交数量：127229901 总提交耗时：694.2691952999996 平均速率：183257.30402747146
本次提交数量：650415 本次提交耗时：3.4503263 本次提交速率：188508.25790012963 总提交数量：127880316 总提交耗时：697.7195215999997 平均速率：183283.270771537
本次提交数量：692542 本次提交耗时：3.5574756 本次提交速率：194672.3120181063 总提交数量：128572858 总提交耗时：701.2769971999996 平均速率：183341.0457114022
本次提交数量：496583 本次提交耗时：2.6779366 本次提交速率：185434.9352408119 总提交数量：129069441 总提交耗时：703.9549337999996 平均速率：183349.01114091754
本次提交数量：364790 本次提交耗时：1.8958125 本次提交速率：192418.81778920649 总提交数量：129434231 总提交耗时：705.8507462999996 平均速率：183373.3713231608
扫描完成！耗时：14m15.1331508s
PRAGMA page_size = 8192;
本次提交数量：351563 本次提交耗时：1.74048 本次提交速率：201991.97922412207 总提交数量：125408527 总提交耗时：696.8328506000005 平均速率：179969.3095582654
本次提交数量：337177 本次提交耗时：1.6405582 本次提交速率：205525.77775052417 总提交数量：125745704 总提交耗时：698.4734088000005 平均速率：180029.33600011363
本次提交数量：360320 本次提交耗时：1.7418034 本次提交速率：206866.05618062292 总提交数量：126106024 总提交耗时：700.2152122000005 平均速率：180096.09303372388
本次提交数量：358488 本次提交耗时：1.7744909 本次提交速率：202023.01403743462 总提交数量：126464512 总提交耗时：701.9897031000005 平均速率：180151.5199461334
本次提交数量：328199 本次提交耗时：1.7002102 本次提交速率：193034.36716236616 总提交数量：126792711 总提交耗时：703.6899133000005 平均速率：180182.64665099033
本次提交数量：665446 本次提交耗时：3.6357474 本次提交速率：183028.6669530452 总提交数量：127458157 总提交耗时：707.3256607000005 平均速率：180197.27557156884
本次提交数量：607733 本次提交耗时：3.1744789 本次提交速率：191443.38933864076 总提交数量：128065890 总提交耗时：710.5001396000006 平均速率：180247.5226424289
本次提交数量：548586 本次提交耗时：3.0160173 本次提交速率：181890.86647480435 总提交数量：128614476 总提交耗时：713.5161569000006 平均速率：180254.46902112034
本次提交数量：526139 本次提交耗时：3.0017366 本次提交速率：175278.20395700276 总提交数量：129140615 总提交耗时：716.5178935000006 平均速率：180233.6217581144
本次提交数量：293616 本次提交耗时：1.7229108 本次提交速率：170418.57303349656 总提交数量：129434231 总提交耗时：718.2408043000006 平均速率：180210.077490859
扫描完成！耗时：14m36.1238253s
PRAGMA page_size = 32768
本次提交数量：401703 本次提交耗时：2.113701 本次提交速率：190047.2204914508 总提交数量：125283406 总提交耗时：660.7282707 平均速率：189614.1145395067
本次提交数量：415157 本次提交耗时：2.2785035000000002 本次提交速率：182205.99617248776 总提交数量：125698563 总提交耗时：663.0067742000001 平均速率：189588.65563880687
本次提交数量：383705 本次提交耗时：2.041398 本次提交速率：187961.8771057873 总提交数量：126082268 总提交耗时：665.0481722000001 平均速率：189583.66216227005
本次提交数量：457821 本次提交耗时：2.4507947 本次提交速率：186805.12080428444 总提交数量：126540089 总提交耗时：667.4989669 平均速率：189573.46044695427
本次提交数量：371113 本次提交耗时：2.0288619 本次提交速率：182916.83628146403 总提交数量：126911202 总提交耗时：669.5278288000001 平均速率：189553.2889610637
本次提交数量：667636 本次提交耗时：3.8167794 本次提交速率：174921.29621114596 总提交数量：127578838 总提交耗时：673.3446082 平均速率：189470.3491293212
本次提交数量：701927 本次提交耗时：4.0077466 本次提交速率：175142.56016086446 总提交数量：128280765 总提交耗时：677.3523548000001 平均速率：189385.5747174262
本次提交数量：580064 本次提交耗时：3.0862997 本次提交速率：187948.04665276027 总提交数量：128860829 总提交耗时：680.4386545000001 平均速率：189379.054449353
本次提交数量：348194 本次提交耗时：1.8878260999999998 本次提交速率：184441.77670814068 总提交数量：129209023 总提交耗时：682.3264806000001 平均速率：189365.39424115673
本次提交数量：225208 本次提交耗时：1.290852 本次提交速率：174464.61716757613 总提交数量：129434231 总提交耗时：683.6173326 平均速率：189337.2575966193
扫描完成！耗时：13m53.8350117s
PRAGMA page_size = 65536
本次提交数量：408814 本次提交耗时：2.0995702 本次提交速率：194713.18463178797 总提交数量：125408527 总提交耗时：656.7121511000001 平均速率：190964.2250260473
本次提交数量：434867 本次提交耗时：2.2103785 本次提交速率：196738.70334877036 总提交数量：125843394 总提交耗时：658.9225296000002 平均速率：190983.59571404153
本次提交数量：402638 本次提交耗时：2.0581124 本次提交速率：195634.60188083022 总提交数量：126246032 总提交耗时：660.9806420000002 平均速率：190998.07767138808
本次提交数量：396058 本次提交耗时：2.0033899 本次提交速率：197693.91869251212 总提交数量：126642090 总提交耗时：662.9840319000002 平均速率：191018.31100979185
本次提交数量：458338 本次提交耗时：2.4742645 本次提交速率：185242.119425793 总提交数量：127100428 总提交耗时：665.4582964000002 平均速率：190996.8343434721
本次提交数量：679254 本次提交耗时：3.4836216 本次提交速率：194985.01214942517 总提交数量：127779682 总提交耗时：668.9419180000002 平均速率：191017.60341471076
本次提交数量：509680 本次提交耗时：2.763081 本次提交速率：184460.75232684094 总提交数量：128289362 总提交耗时：671.7049990000003 平均速率：190990.63158825762
本次提交数量：452940 本次提交耗时：2.3351085 本次提交速率：193969.57357655972 总提交数量：128742302 总提交耗时：674.0401075000003 平均速率：191000.95167556472
本次提交数量：590108 本次提交耗时：3.0712546 本次提交速率：192139.06916085692 总提交数量：129332410 总提交耗时：677.1113621000003 平均速率：191006.1139705101
本次提交数量：101821 本次提交耗时：0.5229523 本次提交速率：194704.18238910125 总提交数量：129434231 总提交耗时：677.6343144000003 平均速率：191008.96788942176
扫描完成！耗时：13m49.7302785s
分别设置page_size = 4096、8192、32768、65536，测试其速度。可见总耗时有所下降，但幅度不大。由于测试环境影响，本次优化最优解不如第五次优化。若感觉影响不大，可不采用本次优化方式。
优化结果 #
到目前为止，扫描任务总耗时从1小时36分钟29秒已优化到13分31秒，已符合预期效果。数据插入性能已不再是影响扫描速度慢的主要原因，但是数据插入速度还有优化空间。
其他方案尝试： #
方案一： #
情况分析 #
找到一个方法，说在执行插入前，可以先进行执行准备，执行准备相当于将sql语句提前编译，省去每次执行sql语句时候的语法检查等操作，可以极大的优化sql语句的执行效率，其原理有点像 LuaJit 将 Lua 语言成静态机器码，提高运行速度。
优化方案 #
但不知道是不是我使用方式不对，未达到想要的效果，实测速度比之前要慢，可留作参考
func (dp *DataProxy) SaveData_RelateBlock(cid, eid int64, typeName string, relateBlock []*file.RelateBlock) (row int64, err error) {
engin, _, err := db.GetDataEngine(cid, eid, typeName, true)
if err != nil {
return 0, err
}
tableName := db.GetDataTableName(eid, typeName)
const batchSize = 1000
totalRows := int64(0)
// 这里加锁 for循环 relateBlock缓存起来去拿
dp.Mutex.Lock()
defer dp.Mutex.Unlock()
// 开始事务
err = engin.Transaction(func(tx *gorm.DB) error {
// 使用原生 SQL 通过 db.DB().Prepare 来获取预编译语句
sqlDB, err := tx.DB()
if err != nil {
return err
}
// 创建一个预编译的插入语句
insertSQL := fmt.Sprintf(
"INSERT INTO %s (cid, eid, tid, nid, pid, `delete`, `index`, start_offset, `length`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
tableName,
)
stmt, err := sqlDB.Prepare(insertSQL) // 获取一个原生的预编译语句
if err != nil {
return err
}
defer stmt.Close()
for i := 0; i < len(relateBlock); i += batchSize {
end := i + batchSize
if end > len(relateBlock) {
end = len(relateBlock)
}
batch := relateBlock[i:end]
for _, block := range batch {
// 执行预编译的插入语句，传入具体的参数
_, execErr := stmt.Exec(block.Cid, block.Eid, block.Tid, block.Nid, block.Pid, block.Delete, block.Index, block.StartOffset, block.Length)
if execErr != nil {
return execErr
}
}
totalRows += int64(len(batch)) // 累加已插入的行数
}
return nil
})
if err != nil {
return totalRows, err
}
return totalRows, nil
}
方案二： #
情况分析 #
单个数据性能如果已无法提升，可考虑分库操作。SQLite 并没有像某些传统的关系型数据库（如 MySQL 或 PostgreSQL）那样支持严格的表级锁或行级锁。它的锁机制更侧重于整个数据库的锁定。换句话说，通常情况下，SQLite 会对整个数据库加锁，而不是单独对某个表进行加锁。相对来说分库简单。
优化方案 #
由于我们的数据一向采用pid、nid作为查询条件。那么在插入数据库时，可以将pid作为区分条件，当pid为单数时，插入数据库1，但pid为单数时插入数据库2。
查询时也根据单双数进入不同的数据库查询。还有添加索引等其他问题需要考虑，经过实测插入和查询可行，但目前为止性能已达到要求，已经没有必要，可预留作为后续优化方式。