查看SQL的执行计划是数据库性能调优的关键步骤。不同的数据库管理系统（DBMS）有不同的方法来生成和查看执行计划。以下是一些常见DBMS（如Oracle、MySQL、PostgreSQL、SQL Server）的详细示例和代码。
Oracle
在Oracle中，可以使用EXPLAIN PLAN语句生成执行计划，并使用DBMS_XPLAN包查看执行计划。
示例步骤
- 生成执行计划：
EXPLAIN PLAN FOR
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';
- 查看执行计划：
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语句生成和查看执行计划。
示例步骤
- 生成并查看执行计划：
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选项查看实际执行的详细信息。
示例步骤
- 生成执行计划：
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';
示例输出
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来生成执行计划。
示例步骤
- 生成执行计划文本：
SET SHOWPLAN_TEXT ON;
GO
SELECT emp_id, emp_name
FROM employees
WHERE emp_department = 'IT';
GO
SET SHOWPLAN_TEXT OFF;
GO
- 生成执行计划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
