SQL Engine In Nutshell
Hey , i assume that some of you know about SQL or even worked with for some years now and they don’ t know how SQL database engine work . I guess no one need to know about cause you can still do the job from creating database and access using SQL program .
SQL Programing or Query Language ?
When it come to SQL many Developer say its peculiar programing language because SQL engine Contain a compiler that compile every query commands into procedure and the virtual machine run it . The concept of SQL engine compiling and running it make it a programing language . So at some point if someone said its a programing language he will be considered right .
Why using SQL when Programing language can do the work :
Yes , Actually we can create a program that can do simple CRUD operation (Create Read Update and Delete) but when it come to complex queries We can do that by writing a hundred of lines equivalent to few line in SQL .
SQL :
SQL stands for Structured Query Language which could be refer as either a programming or a Query language, the main purpose of SQL to interact with the relational database in which data store in tabular form. SQL can manage a large amount of data, especially if data is written simultaneously and we have many transitions over that data.
When the user uses SQL for data management, there the user gets the ability to perform Create, Retrieve, Update, and Delete data between database. There are various Relational Database Management systems(DBMS) such as MySQL, SQLite, Postgres SQL, etc. and they all provided the same kind of features.
There are some terminologies in Database such as database server, or Database engine, or database management system. In database we can interchangeably use these three terminologies so do not get confused when we say SQL engine or SQL server, they all are same.
SQL ENGINE
A SQL engine is a kind of software that collect and interprets the SQL Command so the appropriate operations can be performed on the relational database. The objective of SQL engine to create, read, update and delete data from a database.
A SQL engine or a SQL server database engine includes two main components a storage engine and a query processor, these days some modern SQL DBMS contains more than one Storage engines. We have many types of SQL engines and they all have different architecture, but used to perform the same objective which includes CRUD operations on the database and many other features.
How it Works :
Lets Dive more into How SQL Engine really work . SQL Engine Handle Query in multiple steps .Every SQL database engine contains two main components Compiler and Virtual machine to execute the queries. The compiler read the query and convert that query to appropriate byte code, then that byte code evaluated by the virtual machine and a proper response given back to the client.
The Complete Execution of a query is Categories into 3 main stages
- Compiling (Parsing, Checks, and Semantics)
- Binding
- Optimizing
- Executing
Compiling-parsing
This is a part of compiling process, and in compiling parsing the query statement is tokenized into individual words with appropriate verbiage and clauses.
Compiling-Check-Semantics
The Compiling Semitics checks the validation of the statement and match it with the system’s catalogue. This Compiling stage validates whether the query is valid or not, it also validates the authority of user to execute the statement.
Compiling-Binding
It creates the corresponding binary representation for the entered query statement. All the SQL server engines has this compiling state where the byte code get generated.
By this stage of compiling the statement has been compiled and now it will be sent to the database server for the optimization and execution.
Optimising
It Optimizes the best algorithm for the byte code. This feature is also known as Query Optimizer or Relational Engine.
Executing
The Virtual machine get the Optimised byte code and execute it.
SQL STATEMENT --> Parsing -->Binding --> Query Optimization --> Query Execution --> ResultStorage Engine, Relational Engine, and Execution Engine
The Storage Engine and the Query Optimizer (Relational Engine) are essentially the two core components of a SQL Database Engine.
The SQL Storage Engine is a software module used to create, read, and update data between the disk and memory while still maintaining data integrity (rollback journals and write-ahead logs).
You can use this command to find out what storage engines are available:
mysql> SHOW ENGINES\GA convenient feature of SQL is that all SQL clients have a SQL Query Optimizer. As discussed earlier, this optimizer chooses the algorithm to use depending on the query that was written. It’s similar to choosing an algorithm in programming languages based on time complexity, but instead the Query Optimizer chooses an algorithm by making an access plan / query plan as a cost-based optimizer. What this does is look at many different potential query access plans for a given query, estimates the cost of each of these plans and selects the one with the lowest cost out of all the choices. However, it doesn’t look at every single possible plan but instead does a cost-based balancing act that considers both the cost of finding potential plans and the cost of the plan themselves.
Once it chooses the best plan, it sends that query plan over to the Execution Engine to run the SQL statement.
Data -> Tables
SQL is usually written from a low level programming language such as C. Because of this, the SQL database engine can turn incoming data into tables of columns and records. It does this with B-trees — a type of binary search tree with several branches that keeps pointing to new data elements. These B-tree structures are what allows the data to be turned into a table of columns with keys being used as “pointers” to other keys.
Conclusion
The SQL database engine is a crucial tool to have in almost any database environment. It is built with low-level programming languages and has its own stages for compiling and executing. It selects the best algorithms by comparing it to alternatives and creates tables by using binary trees. People can use different platforms all with different code and have them all connected to a SQL database engine. SQL allows for sharing and taking care of the data with a well-defined schema, automatic algorithm selection, automatic index maintenance, and fixing performance problems without have to recode and retest.
