Author: Zhu Xiujie
- What is dynamic SQL?
In a high-level language, if a SQL statement is embedded and the body structure of the SQL statement is already clear, for example in Java, a piece of code has an SQL “select * from T1 where C1 >5” to execute, during the Java compilation phase, This SQL can be handed over to the database management system to analyze, database software can parse this SQL syntax, generate database aspects of the executable code, such SQL is called static SQL, that is, in the compilation phase can determine what the database to do. If the embedded SQL is not explicitly given, for example, if Java defines a variable of String type SQL: String SQL; The value of this SQL may be equal to the SQL read from the text box or the SQL entered from the keyboard, but the exact value cannot be determined at compile time, until the program is running. This TYPE of SQL is called dynamic SQL
preface
I published the first edition of Rust based ORM in 2020 at rustcc.cn/article?id=…
V1.8 version relies on rust to provide high performance, AND SQL driver relies on SQLX-core. Without special optimization, orM V1.8 version is better than GO, Java and other ORM. Once released, it has been affirmed and adopted by many netizens, and applied to many production systems. V1.8 version borrowed mybatis Plus at the same time with the basic CRUD function and launched Py_SQL to simplify the organization to write SQL psychological pressure, while increasing a series of commonly used plug-ins, greatly convenient for the majority of users.
At the same time, version 1.8 also has some questions raised by netizens, such as:
- By_column *(column:&str,arg: XXX); What is the form of the column to be operated on?
- Can the CRUDTable trait not specify the ID primary key (because some tables may have more than one primary key)?
- The way transactions are managed using TxManager plus TX_ID seems to affect performance because locks are used
- Py_sql uses AST + to interpret execution. There are not only run-time, run-time parse phase, run-time interpreted execution phase. Can py_SQL be optimized in a completely zero-cost way?
- Can you add dynamic SQL storage in XML format, decouples SQL from code, don’t use CDATA escapes (too much trouble), be properly compatible with systems being migrated from Java, and properly reuse the previous Mybais XML?
After a period of time to think and organize, so the launch of V2.0 version, to achieve a full 0 overhead of dynamic SQL, SQL build performance improved N times (only generate SQL), complete query QPS (organize SQL to get results) performance improved at least 2 times, and solve the above problems
For both convenience and performance, for example here is a paging code snippet using HTML_SQL queries (v2.0 version) :
- The HTML file
<! DOCTYPEhtml PUBLIC "- / / / / W3C DTD XHTML 1.1 / / EN" "https://github.com/rbatis/rbatis_sql/raw/main/mybatis-3-mapper.dtd">
<mapper>
<select id="select_by_condition">
select * from biz_activity where
<if test="name != ''">
name like #{name}
</if>
</select>
</mapper>
Copy the code- The main rs file
#[crud_table]
#[derive(Clone, Debug)]
pub struct BizActivity {
pub id: Option<String>,
pub name: Option<String>,
pub pc_link: Option<String>,
pub h5_link: Option<String>,
pub pc_banner_img: Option<String>,
pub h5_banner_img: Option<String>,
pub sort: Option<String>,
pub status: Option<i32>,
pub remark: Option<String>,
pub create_time: Option<NaiveDateTime>,
pub version: Option<i32>,
pub delete_flag: Option<i32>,}#[html_sql(rb, "example/example.html")]
async fn select_by_condition(rb: &mut RbatisExecutor<'_>, page_req: &PageRequest, name: &str) -> Page<BizActivity> { todo!() }
#[async_std::main]
pub async fn main() {
fast_log::init_log("requests.log".1000, log::Level::Info, None.true);
//use static ref
let rb = Rbatis::new();
rb.link("mysql://root:123456@localhost:3306/test").await
.unwrap();
let a = select_by_condition(&mut (&rb).into(), &PageRequest::new(1.10), "test").await
.unwrap();
println!("{:? }", a);
}
Copy the codeJava introduced the most common ORM framework preexistence – Mybatis, MybatisPlus, XML, OGNL expression, DTD file
-
MyBatis provides a more flexible mapping scheme between Java and SQL. MyBatis implements SQL statements and methods and writes them directly to XML files. Why? MyBatis separates the interface from the SQL mapping file, which is independent of each other, but dynamically binds it through the reflection mechanism. In fact, it is the Mapper agent factory [MapperRegistry] and MapperStatement [MapperStatement], they are the Map container, is our common HashMap, ConcurrentHashMap. Therefore,MyBatis uses the interface-oriented approach, which is a good way to realize decoupling and easy for developers to customize and extend, such as we are familiar with the general Mapper and pageHelper plug-in, the way is very simple.
-
What is a DTD file?
A document type definition (DTD) defines a valid XML document building block. It uses a set of legal elements to define the structure of the document. Again, it can be applied to XML files as well as HTML files. Intellij IDEA,CLion,VSCode and other ides all have the ability to use this file as a valid module, label intelligent hint for example:
<? The XML version = "1.0" encoding = "utf-8"? > <! ELEMENT mapper (sql* | insert* | update* | delete* | select* )+> <! ATTLIST mapper >Copy the code<! DOCTYPEhtml PUBLIC "- / / / / W3C DTD XHTML 1.1 / / EN" "https://github.com/rbatis/rbatis_sql/raw/main/mybatis-3-mapper.dtd">
<mapper>
</mapper>
Copy the code- What is an OGNL expression?
Object-graph Navigation Language (OGNL) can be roughly understood as: Object Graphical Navigation Language. Is an open source expression language that makes it easy to manipulate object attributes. Rbatis uses part of ogNL expression design in HTML and Py_SQL, but Rbatis actually operates JSON objects.
For example, (#{name}, which means to get the name parameter from the parameter, and the # symbol means to put the precompiled SQL parameter and replace it with mysql’s ‘? ‘or’ 1 ‘of pg, if’ 1 ‘, if ‘1’, if symbol means insert and replace SQL directly):
<select id="select_by_condition">select * from table where name like #{name}</select>
Copy the codeExploring the implementation Architecture detour – The initial version is executed based on AST+ interpretation
AST abstract syntax tree, you can refer to other blogsBlog.csdn.net/weixin_3940…
- An AST looks something like this
#[derive(Clone, Debug, Serialize, Deserialize)]
pub struct Node {
pub left: Option<Box<Node>>,
pub value: Value,
pub right: Option<Box<Node>>,
pub node_type: NodeType,
}
impl Node{
#[inline]
pub fn eval(&self, env: &Value) -> Result<Value, crate::error::Error> {
if self.equal_node_type(&NBinary) {
let left_v = self.left.as_ref().unwrap().eval(env)? ;let right_v = self.right.as_ref().unwrap().eval(env)? ;let token = self.to_string();
return eval(&left_v, &right_v, token);
} else if self.equal_node_type(&NArg) {
return self.value.access_field(env);
}
return Result: :Ok(self.value.clone()); }}Copy the codeHow does the expression work?
- For example, when the expression ‘1+1’ is executed, it is first resolved into a binary tree of three nodes through the framework. The left leaf Node of the ‘+’ symbol Node is 1, and the right leaf Node is 1
- When executed, it executes the eval () method of the ‘+’ node, which executes the eval () method of the leaf node to get a value of 2 (where the eval () method actually performs the Clone operation), and adds the value of 2 according to the symbol ‘+’, and returns the value.
Conclusion: There are some drawbacks to this architecture, such as a lot of unnecessary Clone operations, node needs to parse -> generate the AST-> execute the AST line by line at runtime. All of these have some time and CPU and memory overhead
Exploring implementation architecture detour – try wASM based
- What is WASM? WebAssembly/ WASM WebAssembly or WASM is a new format that is portable, small, fast to load, and Web compatible.
Rust also has some WASM runtimes, which are frameworks that can do some JIT compilation optimizations. For example, WasmTime/Cranelift/once found that calling cranelift runtime calls cost 800ns/op, which does not seem particularly suitable for ORM with frequent calls in and out of the host-WASM runtime. And the delay of close to 800ns, frankly, is quite unacceptable. See issues github.com/bytecodeall… After some waiting time, the problem is resolved, and the time cost is still at least 50ns. For 20 calls with parameters in SQL, the time delay will still be further extended
Explore the implementation architecture – true zero overhead abstractions, try process macros, is the key to metaprogramming and high performance
We’ve been talking about zero overhead, and C++ implementations follow the “zero overhead principle” : if you don’t use an abstraction, you don’t have to pay for it [Stroustrup, 1994]. And if you do need to use this abstraction, you can make sure it’s the least expensive way to use it. – Stroustrup
- If we use procedure macros to compile the expression directly into pure Rust function code, we achieve a truly exciting overhead of 0! This reduces CPU usage and improves performance
Procedure macro framework, SYN and Quote (parsing and generating a stream of entries, respectively)
We know that syn and quote together are the primary way to implement procedural macros, but SYN and quote only support the Rust syntax specification. How can it parse our custom syntactic sugar?
- The answer is to convert our syntax-sugar to rust conforming syntax, allowing SYN and quote to parse normally and generate a stream of entries
About extensibility – Wrap serde_JSON or copy serde_JSON source?
The expression parameters we execute are all JSON parameters, which involve the use of serde_json. But serde_json doesn’t have a syntax like serde_json::Value + 1, and you’ll get a compilation error!
-
Impl STD ::ops::Add for serde_json::Value{} Implements the interface of the standard library.
-
However, due to the orphan principle (when you implement a trait for a type, you must require that at least one of the types or traits is currently defined in Crate). You can’t implement a third-party trait for a third-party type) you’ll get a compilation error!
Syntax sugar semantics and implement trait support extensions
- (Orphan principle) Solution: Implement a custom structure that relies on the serde_JSON ::Value object and implement syntax rule support for the structure!
A custom structure looks something like this
#[derive(Eq, PartialEq, Clone, Debug)]
pub struct Value<'a> {
pub inner: Cow<'a, serde_json::Value>,
}
Copy the codePerformance optimization 1- Copy-on-write Cow- Avoid unnecessary cloning
- Copy on Write (Copy on Write) technology is an optimization strategy in the program, most applied to read more and Write less scenarios. The main idea is that objects are not copied immediately when they are created. Instead, a large number of read operations are performed by referring to (borrowing from) the original objects. Only when a small number of write operations are performed, the replication operation is performed. The benefit of this approach is to reduce replication operations and improve performance in scenarios where there are more reads and fewer writes.
When implementing expressions, not all operations are ‘write’, most scenarios are based on ‘read’ such as expressions:
<if test="id > 0 || id == 1">
id = ${id}
</if>
Copy the code- Here, the ID is read and determined whether it is greater than 0 or equal to 1
Performance optimization 2- Repeated variable utilization optimization
- The expression defines the variable parameter id and accesses it twice. Then the fn function generated by us is to determine whether the variable ID already exists, and accesses it directly the second time instead of generating it repeatedly. For example:
<select id="select_by_condition">select * from table where id ! = #{id} and 1 ! = #{id}</select>
Copy the codePerformance optimization 3- SQL precompile parameter replacement algorithm optimization
Precompiled SQL needs to be replaced with parameters such as mysql:’? ‘,postgres:’$1’ and so on.
- The key to string replacement performance -rust strings are stored in the heap
The String object rust supports variance-length strings. We know that Vec is stored in the heap (because the heap memory of computers is larger and the stack space is limited)
#[stable(feature = "rust1", since = "1.0.0")]
pub struct String {
vec: Vec<u8>,}Copy the code-
Performance tuning – no format! Functions such as macros that generate String structures to reduce access to heap memory.
-
String substitution with char is clever, because a single char is stored on the stack, which is faster than the heap
-
(here we use new_sql.push(char) to access only the stack memory space)
macro_rules! push_index {
($n:expr,$new_sql:ident,$index:expr) => {
{
let mut num=$index/$n;
$new_sql.push((num+48) as u8 as char);
$index % $n
}
};
($index:ident,$new_sql:ident) => {
if $index>=0 && $index<10{
$new_sql.push(($index+48)as u8 as char);
}else if $index>=10 && $index<100 {
let$index = push_index! (10,$new_sql,$index);
let$index = push_index! (1,$new_sql,$index);
}else if $index>=100 && $index<1000{
let$index = push_index! (100,$new_sql,$index);
let$index = push_index! (10,$new_sql,$index);
let$index = push_index! (1,$new_sql,$index);
}else if $index>=1000 && $index<10000{
let$index = push_index! (1000,$new_sql,$index);
let$index = push_index! (100,$new_sql,$index);
let$index = push_index! (10,$new_sql,$index);
let$index = push_index! (1,$new_sql,$index);
}else{
use std::fmt::Write;
$new_sql.write_fmt(format_args!("{}", $index))
.expect("a Display implementation returned an error unexpectedly"); }}; }for x in sql.chars() {
if x == '\' ' || x == '"' {
if string_start == true {
string_start = false;
new_sql.push(x);
continue;
}
string_start = true;
new_sql.push(x);
continue;
}
if string_start {
new_sql.push(x);
} else {
if x=='? '&& #format_char ! ='? ' {
index+=1; new_sql.push(#format_char); push_index! (index,new_sql); }else{ new_sql.push(x); }}}Copy the codeIn the final validation phase, (zero overhead, compile-time dynamic SQL) efficiency testing is performed
v2.0Request time consuming:3923900800Time:3576816000Time:3248177800Time:3372922200
v1.8Request time consuming:6372459300Time:7709288000Time:6739494900Time:6590053200
Copy the codeConclusion: V2.0 QPS is at least twice as fast as the older version