Oracle（72）如何查看SQL的执行计划？

查看SQL的执行计划是数据库性能调优的关键步骤。不同的数据库管理系统（DBMS）有不同的方法来生成和查看执行计划。以下是一些常见DBMS（如Oracle、MySQL、PostgreSQL、SQL Server）的详细示例和代码。

Oracle

在Oracle中，可以使用EXPLAIN PLAN语句生成执行计划，并使用DBMS_XPLAN包查看执行计划。

示例步骤

1. 生成执行计划：

EXPLAIN PLAN FOR
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

2. 查看执行计划：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

示例输出

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     2 |    40 |     3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES           |     2 |    40 |     3 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMP_DEPARTMENT  |     2 |       |     1 |
-----------------------------------------------------------------------------------

MySQL

在MySQL中，可以使用EXPLAIN语句生成和查看执行计划。

示例步骤

1. 生成并查看执行计划：

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

示例输出

+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_emp_department   | idx_emp_department   | 53      | const |    2 |   100.00 | Using where |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+------+----------+-------------+

PostgreSQL

在PostgreSQL中，可以使用EXPLAIN生成执行计划，并使用ANALYZE选项查看实际执行的详细信息。

示例步骤

1. 生成执行计划：

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

2. 生成并查看实际执行计划：

EXPLAIN ANALYZE
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

示例输出

                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using idx_emp_department on employees  (cost=0.12..8.14 rows=2 width=36)
   Index Cond: (emp_department = 'IT'::text)

SQL Server

在SQL Server中，可以使用SET SHOWPLAN_TEXT或SET SHOWPLAN_XML来生成执行计划。

示例步骤

1. 生成执行计划文本：

SET SHOWPLAN_TEXT ON;
GO

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';
GO

SET SHOWPLAN_TEXT OFF;
GO

2. 生成执行计划XML：

SET SHOWPLAN_XML ON;
GO

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';
GO

SET SHOWPLAN_XML OFF;
GO

示例输出（文本）

StmtText
---------------------------------------------------------------------------
  |--Index Seek(OBJECT:([employees].[idx_emp_department]), SEEK:([employees].[emp_department]='IT') ORDERED FORWARD)

示例输出（XML）

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT emp_id, emp_name FROM employees WHERE emp_department = 'IT'" StatementType="SELECT">
          <QueryPlan>
            <RelOp NodeId="0" PhysicalOp="Index Seek" LogicalOp="Index Seek">
              <IndexScan Object="[employees].[idx_emp_department]" Ordered="true">
                <SeekPredicates>
                  <SeekPredicateNew>
                    <StartRange ScanType="EQ">
                      <RangeColumns>
                        <ColumnReference Column="emp_department" />
                      </RangeColumns>
                      <RangeExpressions>
                        <ScalarOperator ScalarString="'IT'">
                          <Const ConstValue="'IT'" />
                        </ScalarOperator>
                      </RangeExpressions>
                    </StartRange>
                  </SeekPredicateNew>
                </SeekPredicates>
              </IndexScan>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

总结

不同的DBMS有不同的方法来生成和查看SQL执行计划。通过分析执行计划，可以了解查询的执行路径、使用的索引、扫描类型、估计的行数和执行成本等信息。这些信息对于识别性能瓶颈和优化查询至关重要。

Oracle

EXPLAIN PLAN FOR
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

MySQL

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

PostgreSQL

EXPLAIN
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

EXPLAIN ANALYZE
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';

SQL Server

SET SHOWPLAN_TEXT ON;
GO

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';
GO

SET SHOWPLAN_TEXT OFF;
GO

SET SHOWPLAN_XML ON;
GO

SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';
GO

SET SHOWPLAN_XML OFF;
GO