冰河技术

# 《高性能SQL引擎》SQL引擎实战-第02节：基于通用模板动态生成SQL

作者：冰河
星球：http://m6z.cn/6aeFbs (opens new window)
博客：https://binghe.gitcode.host (opens new window)
文章汇总：https://binghe.gitcode.host/md/all/all.html (opens new window)
源码获取地址：https://t.zsxq.com/0dhvFs5oR (opens new window)

沉淀，成长，突破，帮助他人，成就自我。

  • 本节难度：★★☆☆☆
  • 本节重点：基于通用模板动态生成SQL，从全局视角了解高性能SQL引擎的设计和架构思想，并能够将其灵活应用到自身实际项目中。

大家好，我是冰河~~

高性能SQL引擎最基础和最核心的功能就是通过JSON模板或者直接创建对象组合动态生成SQL，不再依赖各种实体模型来接收和传递数据。高性能SQL引擎自身会设计和实现通用的数据模型。这些通用的数据模型会由专门的SQL构建器将其转化成最终的SQL语句。同时，在高性能SQL引擎中，我们设计和实现了SQL驱动引擎来驱动SQL构建器的执行。

高性能SQL可根据实际需要生成任意SQL，限于篇幅，已经给大家分享了基于高性能SQL引擎的通用模型生成十种典型案例场景的SQL。

# 一、背景

截止到目前，我们已经设计和实现了高性能SQL引擎的核心功能，包括：梳理了高性能SQL引擎的需求和流程、制定了高性能SQL引擎的方案目标和架构设计、制定了通用化落地方案、设计和实现通用数据模板和数据模型、设计和实现SQL构建器和驱动引擎等。

在SQL引擎实战篇章，我们已经基于高性能SQL引擎的通用模型动态生成SQL。接下来，基于高性能SQL引擎的通用模板动态生成SQL。

# 二、本节诉求

基于通用模板动态生成SQL，从全局视角了解高性能SQL引擎的设计和架构思想，并能够将其灵活应用到自身实际项目中。

# 三、实战案例

高性能SQL引擎可以根据实际需要生成任意SQL语句。本节，主要基于高性能SQL引擎的通用模板动态生成十种典型的SQL语句。各位小伙伴可以根据自身实际需要，生成任意自己想要的SQL语句。

案例一：普通查询

查询用户表中用户id为1的用户信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["user_id", "user_name", "address", "sex", "remark"],
  "condition": [{
    "field": "user_id",
    "aboveConditions": "and",
    "middleConditions": "=",
    "fieldValue": "1"
  }]
}
1
2
3
4
5
6
7
8
9
10
11
12
13

生成的SQL语句如下所示。

SELECT
	user_id,
	user_name,
	address,
	sex,
	remark 
FROM
	t_user AS USER 
WHERE
	user_id = 1
1
2
3
4
5
6
7
8
9
10

案例二：in查询

查询用户表中用户id在1,2,3,4中的用户信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["user_id", "user_name", "address", "sex", "remark"],
  "condition": [{
    "field": "user_id",
    "aboveConditions": "and",
    "middleConditions": "in",
    "fieldValue": "1,2,3,4"
  }]
}
1
2
3
4
5
6
7
8
9
10
11
12
13

生成的SQL语句如下所示。

SELECT
	user_id,
	user_name,
	address,
	sex,
	remark 
FROM
	t_user AS USER 
WHERE
	user_id IN ( 1, 2, 3, 4 )
1
2
3
4
5
6
7
8
9
10

案例三：like查询

查询用户表中名字包含小的用户信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["user_id", "user_name", "address", "sex", "remark"],
  "condition": [{
    "field": "user_name",
    "aboveConditions": "and",
    "middleConditions": "like",
    "fieldValue": "'%小%'"
  }]
}
1
2
3
4
5
6
7
8
9
10
11
12
13

生成的SQL如下所示。

SELECT
	user_id,
	user_name,
	address,
	sex,
	remark 
FROM
	t_user AS USER 
WHERE
	user_name LIKE '%小%'
1
2
3
4
5
6
7
8
9
10

案例四：between-and查询

查询用户表中用户id在1~100之间的用户信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["user_id", "user_name", "address", "sex", "remark"],
  "condition": [{
    "field": "user_id",
    "aboveConditions": "and",
    "middleConditions": "between",
    "fieldValue": "1,100"
  }]
}
1
2
3
4
5
6
7
8
9
10
11
12
13

生成的SQL如下所示。

SELECT
	user_id,
	user_name,
	address,
	sex,
	remark 
FROM
	t_user AS USER 
WHERE
	user_id BETWEEN 1 
	AND 100
1
2
3
4
5
6
7
8
9
10
11

案例五：分页查询

分页查询用户信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["user_id", "user_name", "address", "sex", "remark"],
  "condition": [{
    "field": "user_id",
    "aboveConditions": "and",
    "middleConditions": "between",
    "fieldValue": "1,100"
  }],
  "limit": {
    "pageStart": 0,
    "pageSize": 10,
    "databaseType": 0
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

生成的SQL语句如下所示。

SELECT
	user_id,
	user_name,
	address,
	sex,
	remark 
FROM
	t_user AS USER 
WHERE
	user_id BETWEEN 1 
	AND 100 
	LIMIT 0,
	10
1
2
3
4
5
6
7
8
9
10
11
12
13

案例六：降序查询

降序查询用户信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["user_id", "user_name", "address", "sex", "remark"],
  "condition": [{
    "field": "user_id",
    "aboveConditions": "and",
    "middleConditions": "between",
    "fieldValue": "1,100"
  }],
  "orderBy": [{
    "fields": ["user_id"],
    "sort": "desc"
  }]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

生成的SQL语句如下所示。

SELECT
	user_id,
	user_name,
	address,
	sex,
	remark 
FROM
	t_user AS USER 
WHERE
	user_id BETWEEN 1 
	AND 100 
ORDER BY
	user_id DESC
1
2
3
4
5
6
7
8
9
10
11
12
13

案例七：多组排序查询

对用户进行多组排序查询，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["user_id", "user_name", "address", "sex", "remark"],
  "condition": [{
    "field": "user_id",
    "aboveConditions": "and",
    "middleConditions": "between",
    "fieldValue": "1,100"
  }],
  "orderBy": [{
    "fields": ["user_id", "user_type"],
    "sort": "desc"
  }, {
    "fields": ["province_id", "country_id"],
    "sort": "asc"
  }]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

生成的SQL语句如下所示。

SELECT
	user_id,
	user_name,
	address,
	sex,
	remark 
FROM
	t_user AS USER 
WHERE
	user_id BETWEEN 1 
	AND 100 
ORDER BY
	user_id,
	user_type DESC,
	province_id,
	country_id ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

案例八：分组聚合查询

分组聚合查询用户信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "fields": ["province_id", "user_type"],
  "aggregation": [{
    "field": "user_id",
    "aggregationMode": "DISTINCT_COUNT",
    "alias": "userCount"
  }, {
    "field": "amount",
    "aggregationMode": "SUM",
    "alias": "totalAmount"
  }],
  "condition": [{
    "field": "user_id",
    "aboveConditions": "and",
    "middleConditions": "between",
    "fieldValue": "1,100"
  }],
  "groupBy": ["province_id", "user_type"]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

生成的SQL语句如下所示。

SELECT
	province_id,
	user_type,
	count( DISTINCT user_id ) AS userCount,
	sum( amount ) AS totalAmount 
FROM
	t_user AS USER 
WHERE
	user_id BETWEEN 1 
	AND 100 
GROUP BY
	province_id,
	user_type
1
2
3
4
5
6
7
8
9
10
11
12
13

案例九：子查询

通过子查询统计用户的相关信息，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "t_user",
    "alias": "user"
  },
  "joins": [{
    "joinType": "QUERY_SUBSYSTEM",
    "table": {
      "tableName": "t_user",
      "alias": "sub_user"
    },
    "fields": ["user_id", "amount", "province_id", "user_type"],
    "condition": [{
      "field": "user_id",
      "aboveConditions": "and",
      "middleConditions": "between",
      "fieldValue": "1,100"
    }]
  }],
  "fields": ["province_id", "user_type"],
  "aggregation": [{
    "field": "user_id",
    "aggregationMode": "DISTINCT_COUNT",
    "alias": "userCount"
  }, {
    "field": "amount",
    "aggregationMode": "SUM",
    "alias": "totalAmount"
  }],
  "groupBy": ["province_id", "user_type"]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

生成的SQL如下所示。

SELECT
	province_id,
	user_type,
	count( DISTINCT user_id ) AS userCount,
	sum( amount ) AS totalAmount 
FROM
	( SELECT user_id, amount, province_id, user_type FROM t_user AS sub_user WHERE user_id BETWEEN 1 AND 100 ) AS sub_user 
GROUP BY
	province_id,
	user_type
1
2
3
4
5
6
7
8
9
10

案例十：关联查询

对用户分析表和支付分析表进行关联分析查询，构造出的高性能SQL引擎的查询数据模板如下所示。

{
  "table": {
    "tableName": "user_analysis",
    "alias": "user_analysis"
  },
  "joins": [{
    "joinType": "QUERY_SUBSYSTEM",
    "table": {
      "tableName": "user_analysis",
      "alias": "user_analysis"
    },
    "fields": ["analysis_date", "analysis_type", "platform_id"],
    "aggregation": [{
      "field": "register_count",
      "aggregationMode": "SUM",
      "alias": "register_count"
    }, {
      "field": "login_count",
      "aggregationMode": "SUM",
      "alias": "login_count"
    }],
    "condition": [{
      "field": "analysis_date",
      "aboveConditions": "and",
      "middleConditions": ">=",
      "fieldValue": "2000-01-01"
    }],
    "groupBy": ["analysis_date", "analysis_type", "platform_id"]
  }, {
    "joinType": "LEFT",
    "table": {
      "tableName": "pay_analysis",
      "alias": "pay_analysis"
    },
    "fields": ["analysis_date", "analysis_type", "platform_id"],
    "aggregation": [{
      "field": "pay_count",
      "aggregationMode": "SUM",
      "alias": "pay_count"
    }, {
      "field": "change_count",
      "aggregationMode": "SUM",
      "alias": "change_count"
    }],
    "condition": [{
      "field": "analysis_date",
      "aboveConditions": "and",
      "middleConditions": ">=",
      "fieldValue": "2000-01-01"
    }],
    "joinCondition": [{
      "field": "user_analysis.analysis_date",
      "aboveConditions": "and",
      "middleConditions": "=",
      "fieldValue": "pay_analysis.analysis_date"
    }, {
      "field": "user_analysis.analysis_type",
      "aboveConditions": "and",
      "middleConditions": "=",
      "fieldValue": "pay_analysis.analysis_type"
    }, {
      "field": "user_analysis.platform_id",
      "aboveConditions": "and",
      "middleConditions": "=",
      "fieldValue": "pay_analysis.platform_id"
    }],
    "groupBy": ["analysis_date", "analysis_type", "platform_id"]
  }],
  "fields": ["user_analysis.analysis_date", "user_analysis.analysis_type", "user_analysis.platform_id", "pay_analysis.analysis_date", "pay_analysis.analysis_type", "pay_analysis.platform_id"],
  "aggregation": [{
    "field": "register_count",
    "aggregationMode": "SUM",
    "alias": "registerCount"
  }, {
    "field": "login_count",
    "aggregationMode": "SUM",
    "alias": "loginCount"
  }, {
    "field": "pay_count",
    "aggregationMode": "SUM",
    "alias": "payCount"
  }, {
    "field": "change_count",
    "aggregationMode": "SUM",
    "alias": "changeCount"
  }],
  "groupBy": ["user_analysis.analysis_date", "user_analysis.analysis_type", "user_analysis.platform_id", "pay_analysis.analysis_date", "pay_analysis.analysis_type", "pay_analysis.platform_id"]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

生成的SQL语句如下所示。

SELECT
	user_analysis.analysis_date,
	user_analysis.analysis_type,
	user_analysis.platform_id,
	pay_analysis.analysis_date,
	pay_analysis.analysis_type,
	pay_analysis.platform_id,
	sum( register_count ) AS registerCount,
	sum( login_count ) AS loginCount,
	sum( pay_count ) AS payCount,
	sum( change_count ) AS changeCount 
FROM
	(
	SELECT
		analysis_date,
		analysis_type,
		platform_id,
		sum( register_count ) AS register_count,
		sum( login_count ) AS login_count 
	FROM
		user_analysis AS user_analysis 
	WHERE
		analysis_date >= '2000-01-01' 
	GROUP BY
		analysis_date,
		analysis_type,
		platform_id 
	) AS user_analysis
	LEFT JOIN (
	SELECT
		analysis_date,
		analysis_type,
		platform_id,
		sum( pay_count ) AS pay_count,
		sum( change_count ) AS change_count 
	FROM
		pay_analysis AS pay_analysis 
	WHERE
		analysis_date >= '2000-01-01' 
	GROUP BY
		analysis_date,
		analysis_type,
		platform_id 
	) AS pay_analysis ON user_analysis.analysis_date = pay_analysis.analysis_date 
	AND user_analysis.analysis_type = pay_analysis.analysis_type 
	AND user_analysis.platform_id = pay_analysis.platform_id 
GROUP BY
	user_analysis.analysis_date,
	user_analysis.analysis_type,
	user_analysis.platform_id,
	pay_analysis.analysis_date,
	pay_analysis.analysis_type,
	pay_analysis.platform_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

# 四、本节总结

本节，主要对高性能SQL引擎基于通用模板动态生成SQL给出了十种典型的案例场景，高性能SQL引擎可以按需生成各种想要的SQL语句。

最后，可以在评论区写下你学完本章节的收获，祝大家都能学有所成，我们一起搞定高性能SQL引擎。

# 五、写在最后

在冰河的知识星球除了已完结的高性能网关和热更的RPC视频外，还有其他众多高并发、高性能中间件与业务场景项目，像DeepSeek大模型、手写高性能熔断组件、手写通用指标上报组件、手写高性能数据库路由组件、分布式IM即时通讯系统、Sekill分布式秒杀系统、手写RPC、简易商城系统等等，这些项目的需求、方案、架构、落地等均来自互联网真实业务场景，让你真正学到互联网大厂的业务与技术落地方案，并将其有效转化为自己的知识储备。

值得一提的是：冰河自研的Polaris高性能网关比某些开源网关项目性能更高，并且冰河也正在为企业级高性能RPC框架录制视频，全程带你分析原理和手撸代码。 你还在等啥？不少小伙伴经过星球硬核技术和项目的历练，早已成功跳槽加薪，实现薪资翻倍，而你，还在原地踏步，抱怨大环境不好。抛弃焦虑和抱怨，我们一起塌下心来沉淀硬核技术和项目，让自己的薪资更上一层楼。


目前，领券加入星球就可以跟冰河一起学习《DeepSeek大模型》、《手写高性能通用熔断组件项目》、《手写高性能通用监控指标上报组件》、《手写高性能数据库路由组件项目》、《手写简易商城脚手架项目》、《手写高性能RPC项目》和《Spring6核心技术与源码解析》、《实战高并发设计模式》、《分布式Seckill秒杀系统》、《分布式IM即时通讯系统》和《手写高性能Polaris网关》，从零开始介绍原理、设计架构、手撸代码。

花很少的钱就能学这么多硬核技术、中间件项目和大厂秒杀系统与分布式IM即时通讯系统，比其他培训机构不知便宜多少倍，硬核多少倍，如果是我，我会买他个十年！

加入要趁早，后续还会随着项目和加入的人数涨价，而且只会涨，不会降，先加入的小伙伴就是赚到。

另外，还有一个限时福利，邀请一个小伙伴加入，冰河就会给一笔 分享有奖 ，有些小伙伴都邀请了50+人，早就回本了！

# 六、其他方式加入星球

  • 链接 ：打开链接 http://m6z.cn/6aeFbs 加入星球。
  • 回复 ：在公众号 冰河技术 回复 星球 领取优惠券加入星球。

特别提醒： 苹果用户进圈或续费，请加微信 hacker_binghe 扫二维码，或者去公众号 冰河技术 回复 星球 扫二维码加入星球。

**好了，今天就到这儿吧，我是冰河，我们下期见~~

← 第01节：高性能SQL引擎基准性能测试 →