Behind a simple SQL query statement...

When weSQL Server Management StudioInput: Select * from T1 where c1 = 2;

What happened behind it? How does a database perform a query?-I don't know why.

 

1. query parsing, synatax error check, and authorization : Query processer check the user is authorized to run the query; (the query processor first parses the syntax and checks the authorization. If you have not obtained the authorization to perform related operations or syntax errors, an error is returned .)

2. query re-writing , semantic optimization ; (query processor re-write SQL statement and optimization ).

For example:Select EMP. Name, EMP. Salary
From EMP, Dept
Where EMP. deptno = Dept. DNO

JoinIs redundant. The query processor will rewrite this statement and delete it to the table.DeptOfJoin.

3. compile the SQL into internal query plan ; (compile SQL internal query plan)

the database selects the best method to calculate the result set. For example, perform a full table scan or use an index ( pairs of key and location, directory similar to a book ). Databases compare their costs and form an internal execution plan. The component in the database responsible for calculating and selecting the best execution plan is called the optimizer ( optimizer ).

4. the query plan is handled by Plan executor , which consists of specified operators, e.g. joining, sorting, grouping... (the query plan is executed by the scheduled executor. The scheduler executor contains many operators, such as join, sort, group and so on. You also need to access the Method Manager to determine which data pages or index pages to access or directly cache hits, the buffer manager is responsible for these ......)

5.Transaction Manager is started as well to ensure the acid of operations.(The Transaction Manager is started at the same time to ensure the concurrency consistency of atomic operations, for example, lock manager, index manager,Line manager,The page file manager, buffer manager, log manager, and so on will also be executed, rather than a query executor .)

 

Execute the following statement:Select * from T1 where c1 = 2Simple process:

 

1.ScanT1Table. If it is the beginning, the first record is taken; otherwise, the next record is taken; if the last record of the table is read, the first record is taken.4Step.

2. check whether the record meets Where condition; yes. The 3 step, otherwise return to the 1 step.

3.Add the record to the result set.

4.Return the result set to the client.

 

 

If the table has an index, the Optimizer may compare the full table scan and index, and may decide that the execution plan is a scan index. The procedure is similar,1Step by step:

Scan Index(Clustered or no-clustered)And locate the record.

 

For example:

 Select * from MERs where contactid in

(Select contactid from contacts where contactid = 86)

Order by customername DESC;

 

The steps are as follows:

1.In the table[Contacts]To scan the index and locate the record.

2. In the table [Cus Tomers] scan indexes and locate records.

3.Hash match (inner join), Hash matching, connection.

4.Sort, Sort

 

About join

SQL Server employs three types of Join Operations:

• Nested loops joins
• Merge joins
• Hash joins

 

1. If the join input is small, for example, less than 10 rows, and other join input is large and the index is on its column, nested loops joins is the fastest. (For the reason, see Understanding nested loops joins)
2. If both join inputs are not small, but are sorted in the index column (for example, scanning sorted indexes obtained after scanning the sorted index), merge joins is the fastest. (For the reason, see Understanding merge joins)
3. Hash joins can effectively process a large number of input without sorting or indexing. It is particularly effective for processing intermediate results of complex queries. (For more information, see Understanding hash joins)

 

These are just some simple processes. In fact, the internal execution process of the database is quite complicated. Although we are not a database developer, it is necessary to understand the upstream and downstream knowledge and help our development and application. The technology must not only know how to use it, but also why.I don't know why. A simple internal structure of the database is attached for your reference.