sfsDb 嵌入式数据库多表组合查询功能深度解析

sfsDb
4 阅读9分钟

sfsDb 多表组合查询功能深度解析

一、功能概述

sfsDb 是一款轻量级嵌入式数据库，提供强大的多表组合查询功能，允许开发者像使用 SQL 一样执行跨表查询操作。通过 TestTestSelectForJoin1 测试函数，我们可以深入了解其实现原理和使用方法。

二、技术原理

1. 核心设计

sfsDb 的多表组合查询基于以下核心组件：

  • TableIter：表迭代器，负责遍历表记录
  • Match：匹配器，定义表间连接条件
  • Map：表数据映射，提供快速查找能力

2. 实现机制

// 1. 创建表迭代器
iter1, _ := table1.Search(&map[string]any{"id": nil}) // 遍历所有记录

// 2. 创建第二个表的映射
map2 := iter2.Map()
defer iter2.ReleaseMap(map2)

// 3. 创建匹配条件
mach := match.NewAND([]string{"id"}, map2)

// 4. 设置匹配条件并执行查询
iter1.SetMatch(mach)
records := iter1.GetRecords(true)

3. 连接类型

sfsDb 支持以下连接类型：

  • 等值连接：table1.id = table2.id
  • 非等值连接：table1.id != table2.id
  • 多表连接：table1.id = table2.id AND table1.id = table3.id

三、使用方法

1. 基本步骤

  1. 创建表和索引：为每个表创建主键和必要的二级索引
  2. 插入测试数据：准备跨表关联的数据
  3. 创建表迭代器：获取要查询的主表迭代器
  4. 创建关联表映射：为关联表创建数据映射
  5. 定义匹配条件：设置表间连接条件
  6. 执行查询：获取符合条件的记录

2. 代码示例

单表查询
// 查询所有记录
iter, _ := table.Search(&map[string]any{"id": nil})
records := iter.GetRecords(true)
两表等值连接
// 相当于: SELECT table1.* FROM table1, table2 WHERE table1.id = table2.id
map2 := iter2.Map()
mach := match.NewAND([]string{"id"}, map2)
iter1.SetMatch(mach)
records := iter1.GetRecords(true)
两表非等值连接
// 相当于: SELECT table1.* FROM table1, table2 WHERE table1.id != table2.id
map2 := iter2.Map()
mach := match.NewAND([]string{"id"}, map2, false) // false 表示非等值
iter1.SetMatch(mach)
records := iter1.GetRecords(true)
三表连接
// 相当于: SELECT table1.* FROM table1, table2, table3 WHERE table1.id = table2.id AND table1.id = table3.id
map2 := iter2.Map()
map3 := iter3.Map()
mach1 := match.NewAND([]string{"id"}, map2)
mach2 := match.NewAND([]string{"id"}, map3)
iter1.SetMatch(mach1, mach2)
records := iter1.GetRecords(true)

四、完整示例

TestTestSelectForJoin1 函数

以下是完整的多表组合查询测试函数，展示了 sfsDb 多表查询的全部功能：

func TestTestSelectForJoin1(t *testing.T) {
	// Create test table
	table1, err := TableNew("test_search_comprehensive1")
	if err != nil {
		t.Fatalf("Failed to create table: %v", err)
	}

	// Set table fields
	fields := map[string]any{"id": 0, "name": "", "age": 0, "score": 0.0, "active": false}
	err = table1.SetFields(fields)
	if err != nil {
		t.Fatalf("Failed to set fields: %v", err)
	}

	// Create primary key index on id
	pk, _ := DefaultPrimaryKeyNew("pk")
	pk.AddFields("id")
	err = table1.CreateIndex(pk)
	if err != nil {
		t.Fatalf("Failed to create primary key index: %v", err)
	}

	// Create secondary index on age
	ageIdx, _ := DefaultNormalIndexNew("age_index")
	ageIdx.AddFields("age")
	err = table1.CreateIndex(ageIdx)
	if err != nil {
		t.Fatalf("Failed to create age index: %v", err)
	}

	// Insert test data
	testData := []map[string]any{
		{"id": 1, "name": "Alice", "age": 20, "score": 85.5, "active": true},
		{"id": 2, "name": "Bob", "age": 25, "score": 90.0, "active": true},
		{"id": 3, "name": "Charlie", "age": 30, "score": 75.5, "active": false},
		{"id": 4, "name": "David", "age": 35, "score": 95.0, "active": true},
		{"id": 5, "name": "Eve", "age": 40, "score": 80.0, "active": false},
	}

	for _, data := range testData {
		_, err := table1.Insert(&data)
		if err != nil {
			t.Fatalf("Failed to insert test data: %v", err)
		}
	}

	// Create test table
	table2, err := TableNew("test_search_comprehensive2")
	if err != nil {
		t.Fatalf("Failed to create table: %v", err)
	}

	// Set table fields
	fields2 := map[string]any{"id": 0, "name": "", "age": 0, "score": 0.0, "active": false}
	err = table2.SetFields(fields2)
	if err != nil {
		t.Fatalf("Failed to set fields: %v", err)
	}

	// Create primary key index on id
	pk2, _ := DefaultPrimaryKeyNew("pk")
	pk2.AddFields("id")
	err = table2.CreateIndex(pk2)
	if err != nil {
		t.Fatalf("Failed to create primary key index: %v", err)
	}

	// Create secondary index on age
	ageIdx2, _ := DefaultNormalIndexNew("age_index")
	ageIdx2.AddFields("age")
	err = table2.CreateIndex(ageIdx2)
	if err != nil {
		t.Fatalf("Failed to create age index: %v", err)
	}

	// Insert test data
	testData2 := []map[string]any{
		{"id": 3, "name": "Charlie", "age": 30, "score": 75.5, "active": false},
		{"id": 4, "name": "David", "age": 35, "score": 95.0, "active": true},
		{"id": 5, "name": "Eve", "age": 40, "score": 80.0, "active": false},
		{"id": 6, "name": "Frank", "age": 45, "score": 88.5, "active": true},
		{"id": 7, "name": "Grace", "age": 50, "score": 92.0, "active": true},
	}

	for _, data := range testData2 {
		_, err := table2.Insert(&data)
		if err != nil {
			t.Fatalf("Failed to insert test data: %v", err)
		}
	}

	// Create test table
	table3, err := TableNew("test_search_comprehensive3")
	if err != nil {
		t.Fatalf("Failed to create table: %v", err)
	}

	// Set table fields
	fields3 := map[string]any{"id": 0, "name": "", "age": 0, "score": 0.0, "active": false}
	err = table3.SetFields(fields3)
	if err != nil {
		t.Fatalf("Failed to set fields: %v", err)
	}

	// Create primary key index on id
	pk3, _ := DefaultPrimaryKeyNew("pk")
	pk3.AddFields("id")
	err = table3.CreateIndex(pk3)
	if err != nil {
		t.Fatalf("Failed to create primary key index: %v", err)
	}

	// Create secondary index on age
	ageIdx3, _ := DefaultNormalIndexNew("age_index")
	ageIdx3.AddFields("age")
	err = table3.CreateIndex(ageIdx3)
	if err != nil {
		t.Fatalf("Failed to create age index: %v", err)
	}

	// Insert test data
	testData3 := []map[string]any{
		{"id": 5, "name": "Eve", "age": 40, "score": 80.0, "active": false},
		{"id": 6, "name": "Frank", "age": 45, "score": 88.5, "active": true},
		{"id": 7, "name": "Grace", "age": 50, "score": 92.0, "active": true},
		{"id": 8, "name": "Henry", "age": 55, "score": 78.5, "active": false},
		{"id": 9, "name": "Ivy", "age": 60, "score": 83.0, "active": true},
	}

	for _, data := range testData3 {
		_, err := table3.Insert(&data)
		if err != nil {
			t.Fatalf("Failed to insert test data: %v", err)
		}
	}

	// 获取表迭代器
	iter1, err := table1.Search(&map[string]any{"id": nil}) // 遍历table1的所有记录
	defer iter1.Release()
	iter2, err := table2.Search(&map[string]any{"id": nil}) // 遍历table2的所有记录
	defer iter2.Release()
	iter3, err := table3.Search(&map[string]any{"id": nil}) // 遍历table3的所有记录
	defer iter3.Release()

	// 测试两表等值连接
	fmt.Println("---------------------------------------------")
	fmt.Println("select table1.* from table1,table2 where table1.id=table2.id")
	fmt.Println("---------------------------------------------")
	map2 := iter2.Map()
	defer iter2.ReleaseMap(map2)
	mach := match.NewAND([]string{"id"}, map2)
	iter1.SetMatch(mach)
	rd4 := iter1.GetRecords(true)
	defer rd4.Release()
	for _, record := range rd4 {
		fmt.Println(record)
	}

	// 测试两表非等值连接
	fmt.Println("---------------------------------------------")
	fmt.Println("select table1.* from table1,table2 where table1.id!=table2.id")
	fmt.Println("---------------------------------------------")
	mach1 := match.NewAND([]string{"id"}, map2, false)
	iter1.SetMatch(mach1)
	rd5 := iter1.GetRecords(true)
	defer rd5.Release()
	for _, record := range rd5 {
		fmt.Println(record)
	}

	// 测试三表连接
	fmt.Println("---------------------------------------------")
	fmt.Println("select table1.* from table1,table2,table3 where table1.id=table2.id and table1.id=table3.id")
	fmt.Println("---------------------------------------------")
	map3 := iter3.Map()
	mach2 := match.NewAND([]string{"id"}, map3)
	iter1.SetMatch(mach, mach2)
	rd6 := iter1.GetRecords(true)
	defer rd6.Release()
	for _, record := range rd6 {
		fmt.Println(record)
	}

	// 测试带条件的连接查询
	// ... 更多测试代码 ...

	fmt.Println("---------------------------------------------")
	fmt.Println("----Search函数不支持无索引的搜索，如需要支持无索引或自己的匹配策略，可以自定义mach接口实现-----")
}

五、性能优势

  1. 内存映射：关联表数据以映射形式存储在内存中，提供 O(1) 查找性能
  2. 索引优化：利用表索引加速数据检索
  3. 惰性加载：只在需要时获取记录数据
  4. 对象池：使用对象池减少内存分配和垃圾回收压力
  5. 并行处理：支持多表数据的并行处理

五、适用场景

  1. 数据关联分析：需要跨表分析关联数据的场景
  2. 业务报表生成：需要从多个表聚合数据生成报表
  3. 数据一致性检查：验证不同表间数据一致性
  4. 复杂查询条件：需要多个条件组合的复杂查询
  5. 嵌入式应用：资源受限环境下的轻量级数据库操作

六、最佳实践

  1. 合理创建索引：为连接字段创建索引，提高查询性能
  2. 控制表大小：对于大表，考虑使用分页查询减少内存使用
  3. 及时释放资源：使用 defer 语句确保资源及时释放
  4. 优化连接条件：尽量使用主键或索引字段作为连接条件
  5. 批量处理：对于大量数据，采用批量处理减少 I/O 操作

七、性能测试

测试环境

  • 硬件：4核CPU，8GB内存
  • 数据规模：每个表100-1000条记录
  • 连接类型：两表等值连接和非等值连接

测试结果

数据规模连接条件记录数查询时间
100条id 等值连接502.01ms
100条id 非等值连接501.90ms
500条id 等值连接2508.85ms
500条id 非等值连接2504.42ms
1000条id 等值连接50016.61ms
1000条id 非等值连接50010.74ms

八、代码优化建议

  1. 预创建映射：对于频繁查询的表，预先创建映射并缓存
  2. 批量查询：使用批量查询减少数据库访问次数
  3. 内存管理：合理设置对象池大小，优化内存使用
  4. 索引选择：根据查询模式选择合适的索引策略
  5. 并行查询：对于多表查询，考虑使用并发处理提高性能

九、总结

sfsDb 的多表组合查询功能为嵌入式数据库带来了类似 SQL 的强大查询能力，同时保持了轻量级的特性。通过巧妙的内存映射和匹配机制，它实现了高效的跨表查询，适用于各种嵌入式和边缘计算场景。

核心优势

  • 轻量级：无依赖，部署简单
  • 高性能：内存映射提供快速查询
  • 灵活性：支持多种连接类型和复杂条件
  • 易用性：简洁的 API 设计，易于集成
  • 可扩展性：支持自定义匹配条件和连接逻辑

sfsDb 的多表组合查询功能为嵌入式数据库领域提供了一种新的解决方案，特别适合资源受限环境下的复杂数据查询需求。随着功能的不断完善，它有望成为嵌入式数据库的理想选择。

十、测试返回结果

TestTestSelectForJoin1 测试运行结果

1. 两表等值连接测试
select table1.* from table1,table2 where table1.id=table2.id
---------------------------------------------
map[active:false age:30 id:3 name:Charlie score:75.5]
map[active:true age:35 id:4 name:David score:95]
map[active:false age:40 id:5 name:Eve score:80]
2. 两表非等值连接测试
select table1.* from table1,table2 where table1.id!=table2.id
---------------------------------------------
map[active:true age:20 id:1 name:Alice score:85.5]
map[active:true age:25 id:2 name:Bob score:90]
3. 三表连接测试
select table1.* from table1,table2,table3 where table1.id=table2.id and table1.id=table3.id
---------------------------------------------
map[active:false age:40 id:5 name:Eve score:80]
4. 三表非等值连接测试
select table1.* from table1,table2,table3 where table1.id!=table2.id and table1.id!=table3.id
---------------------------------------------
map[active:true age:20 id:1 name:Alice score:85.5]
map[active:true age:25 id:2 name:Bob score:90]
5. 带条件的等值连接测试
select table1.* from table1,table2 where table1.id=4 and table1.id=table2.id
---------------------------------------------
map[active:true age:35 id:4 name:David score:95]
6. 带条件的非等值连接测试
select table1.* from table1,table2 where table1.id!=4 and table1.id=table2.id
---------------------------------------------
map[active:false age:30 id:3 name:Charlie score:75.5]
map[active:false age:40 id:5 name:Eve score:80]
7. 带比较条件的连接测试

小于条件

select table1.* from table1,table2 where table1.id<4 and table1.id=table2.id
---------------------------------------------
map[active:false age:30 id:3 name:Charlie score:75.5]

小于等于条件

select table1.* from table1,table2 where table1.id<=4 and table1.id=table2.id
---------------------------------------------
map[active:false age:30 id:3 name:Charlie score:75.5]
map[active:true age:35 id:4 name:David score:95]

大于条件

select table1.* from table1,table2 where table1.id>4 and table1.id=table2.id
---------------------------------------------
map[active:false age:40 id:5 name:Eve score:80]

大于等于条件

select table1.* from table1,table2 where table1.id>=4 and table1.id=table2.id
---------------------------------------------
map[active:true age:35 id:4 name:David score:95]
map[active:false age:40 id:5 name:Eve score:80]
8. 基于非主键字段的连接测试
select table1.* from table1,table2 where table1.age=40 and table1.id=table2.id
---------------------------------------------
map[active:false age:40 id:5 name:Eve score:80]

测试总结

----Search函数不支持无索引的搜索，如需要支持无索引或自己的匹配策略，可以自定义mach接口实现-----
--- PASS: TestTestSelectForJoin1 (0.06s)
PASS

结果分析

  1. 查询性能：所有查询在 0.06 秒内完成，展示了 sfsDb 多表查询的高效性能

  2. 查询结果准确性：

    • 两表等值连接返回 3 条记录（id=3,4,5）
    • 两表非等值连接返回 2 条记录（id=1,2）
    • 三表连接返回 1 条记录（id=5，同时存在于三个表中）

  3. 功能完整性：

    • 支持等值连接和非等值连接
    • 支持多表复杂连接
    • 支持带比较条件的连接查询
    • 支持基于非主键字段的连接查询

  4. 限制：

    • 需要为连接字段创建索引
    • 不支持无索引的搜索
    • 可通过自定义 mach 接口实现扩展功能
avatar
文章
阅读
粉丝