Shulou(Shulou.com)05/31 Report--

What is the SQL execution plan of various databases? I believe many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The execution plan (execution plan, also known as query plan or interpretation plan) is the concrete step for the database to execute SQL statements, such as accessing the data in the table through index or full table scan, the implementation of join query and the order of join and so on. If the performance of the SQL statement is not satisfactory, we should first look at its execution plan. This paper mainly introduces how to obtain and understand the execution plan in various databases, and gives reference documents for further analysis.

Many management and development tools now provide the ability to view graphical execution plans, such as MySQL Workbench, Oracle SQL Developer, SQL Server Management Studio, DBeaver, etc., but instead of using such tools, we introduce viewing execution plans using commands provided by the database.

Let's start with a simple summary of viewing the execution plan in various databases:

MySQL execution plan

The way to get the execution plan in MySQL is simple by adding the EXPLAIN keyword before the SQL statement:

Executing this statement returns a tabular execution plan with 12 columns of information:

EXPLAIN in MySQL supports SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

The next thing we need to do is to understand the meaning of these fields in the execution plan. The following table lists the roles of the fields in the MySQL execution plan:

For the above example, there is only one SELECT clause and id is 1; first perform a full table scan of the employees table (type = ALL), processing 107 rows of data, using WHERE conditional filtering to estimate the remaining 33.33% of the data (estimated inaccurately), and then use the primary key of the departments table (key = PRIMARY) to find a row of matching data (type = eq_ref, rows = 1) for these data.

Use the new ANALYZE option of MySQL 8.0 to display additional information such as the actual execution time:

Where Nested loop inner join means to join two tables using a nested loop join, and employees is the driver table. Cost represents the estimated cost, rows represents the estimated number of rows returned; actual time shows the actual time it takes to return the first row and all data rows, followed by rows indicates the number of rows returned by the iterator, and loops represents the number of iterator loops.

Oracle execution plan

There are several ways to view the execution plan in Oracle, and this article uses the following ways:

Use the EXPLAIN PLAN FOR command to generate and save the execution plan

Displays the saved execution plan.

First, generate an execution plan:

The EXPLAIN PLAN FOR command does not run the SQL statement, so the execution plan created is not necessarily the same as the actual plan when the statement was executed.

This command saves the generated execution plan to the global temporary table PLAN_TABLE, and then uses the stored procedure format in the system package DBMS_XPLAN to display the execution plan in that table. The following statement lets you view the last execution plan in the current session:

EXPLAIN PLAN FOR in Oracle supports SELECT, UPDATE, INSERT, and DELETE statements.

Next, we also need to understand the meaning of the various information in the implementation plan:

Plan hash value is the hash value of the statement. SQL statements and execution plans are stored in the library cache, and statements with the same hash can reuse existing execution plans, that is, soft parsing

Id is a sequence number, but does not represent the order in which it is executed. The order of execution is judged according to indentation, the more indents, the first to execute, and the same indentation is executed from top to bottom. The asterisk in front of Id indicates that predicate judgment is used, refer to the following Predicate Information

Operation represents the current operation, that is, how to access the data of the table, how to join the table, how to sort, and so on.

Name displays the accessed table name, index name, or subquery, etc., provided that the current operation involves these objects

Rows is the number of rows returned by the current operation estimated by Oracle, also known as cardinality (Cardinality)

Bytes is Oracle's estimate of the amount of data involved in the current operation

Cost (% CPU) is the cost that Oracle calculates to perform this operation

Time is Oracle's estimate of how long it will take to perform the operation

Predicate Information displays predicate information related to Id. Access is an access condition, which affects how the data is accessed (whether the table is scanned or indexed); filter is a filtering condition, which is filtered according to which the data is obtained.

In the above example, the execution order of the Id is 3-> 2-> 5-> 4-> 1. First, Id = 3 scan primary key index DEPT_ID_PK,Id = 2 press primary key ROWID to access table DEPARTMENTS, and the results have been sorted; second, Id = 5 full table scan accesses EMPLOYEES and uses filter to filter data, Id = 4 sorts and filters based on department number; finally, merge join is performed by Id = 1. Obviously, here Oracle chooses to sort and merge joins to achieve the join of the two tables.

For information about Oracle execution plans and SQL tuning, you can refer to the official Oracle document "SQL Tuning Guide".

SQL Server execution plan

SQL Server Management Studio provides an easy way to view a graphical execution plan, and here we introduce a way to view it through commands:

SET STATISTICS PROFILE ON

The above command turns on the analysis function of the SQL Server statement, and the statement executed after it is opened will return the corresponding execution plan:

The execution plan in SQL Server supports SELECT, INSERT, UPDATE, DELETE, and EXECUTE statements.

The execution order of the steps of the SQL Server execution plan is judged according to indentation, the more indents, the first to execute, and the same indentation is executed from top to bottom. Next, we need to understand the meaning of the various information in the execution plan:

Rows indicates the number of records actually generated by this step

Executes indicates the number of times the step was actually performed

StmtText contains a specific description of each step, that is, how to access and filter the data of the table, how to join the table, how to sort, and so on.

StmtId, the number of the statement

NodeId, the node number of the current operation step, which does not represent the execution order

Parent, the parent node of the current operation step, executes the child node first, then the parent node

PhysicalOp, a nested loop implementation of physical operations, such as join operations

LogicalOp, logical operations, such as inner join operations

Argument, the parameter used by the operation

DefinedValues, defined variable valu

EstimateRows, which estimates the number of rows returned

EstimateIO, estimated IO cost

EstimateCPU, estimated CPU cost

AvgRowSize, the average returned row size

TotalSubtreeCost, the cumulative cost of the current node

OutputList, the list of fields output by the current node

Warnings, the warning message from the estimate

Type, the type of current procedure

Parallel, whether to execute in parallel

EstimateExecutions, the number of times this step is expected to be performed

For the above statements, the nodes execute in the order of 3-> 4-> 2-> 1. First, the third row is executed, and three rows of data are returned by scanning the employees table with filtering through the clustered index (primary key), and the estimated number of rows (3.0841121673583984) is very close to this. Then the fourth row is executed, and the clustered index is cyclically used to find the departments table, and the loop returns one row of data each time. Line 2 is their parent node, indicating that the Inner Join,Argument column is implemented using Nested Loops (OUTER REFERENCES: ([e]. [department_id])) that the driver table is employees; line 1 represents the entire query and does not perform the actual operation.

Finally, you can turn off the parsing function of the statement using the following command:

SET STATISTICS PROFILE OFF

For information about the SQL Server execution plan and SQL tuning, you can refer to the SQL Server official documentation for the execution plan.

PostgreSQL execution plan

The way to get the execution plan in PostgreSQL is similar to MySQL by adding the EXPLAIN keyword before the SQL statement:

EXPLAIN in PostgreSQL supports SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, and CREATE MATERIALIZED VIEW AS statements.

The order in which PostgreSQL executes the plan is judged by indentation. The more indents are, the more they are executed first, and the same indentation is executed from top to bottom. For the above example, first perform a full table scan (Seq Scan) on the employees table, using salary > 15000 as the filter condition; cost shows the estimated cost of returning the first row (0.00) and the cost of returning all rows (3.34), respectively; rows represents the estimated number of rows returned; and width represents the estimated size of the returned rows (in Byte). The scan results are then put into the memory hash table, and both cost are equal to 3.34, because it is calculated and stored in the hash table at one time after all the data has been scanned. Next, scan the departments and calculate the hash value based on the department_id, and then match the previous hash table (d.department_id = e.department_id). The top line indicates that the database uses Hash Join to implement the connection operation.

EXPLAIN in PostgreSQL can also use the ANALYZE option to display the actual run time and more information of the statement:

EXPLAIN ANALYZE gets more information by executing the statement. Where actual time is the average time spent in each iteration (ms), which is also divided into startup time and completion time; loops represents the number of iterations; Hash operation also shows the number of barrels (Buckets), batch number (Batches), and memory occupied (Memory Usage). Batches greater than 1 means temporary storage that needs to be used to disk; Planning Time is the time to generate the execution plan; and Execution Time is the actual time to execute the statement, excluding Planning Time.

For the execution plan and performance optimization of PostgreSQL, you can refer to the performance tips of the official PostgreSQL documentation.

SQLite execution plan

SQLite also provides the EXPLAIN QUERY PLAN command to get the execution plan of the SQL statement:

EXPLAIN QUERY PLAN in SQLite supports statements such as SELECT, INSERT, UPDATE, DELETE, etc.

The SQLite execution plan is also shown as indented, the more indents are executed first, and the same indents are executed from top to bottom. The above example first scans the employees table, and then looks for the data in the departments through the primary key in turn for this result. SQLite supports only one connection implementation, that is, nested loops join.

In addition, a simple EXPLAIN in SQLite can also be used to display the sequence of virtual machine instructions that execute the statement:

After reading the above, do you have a good grasp of the SQL implementation plan of various databases? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.