MySQL与Oracle 执行计划对比

引言

上一首工作使用的是MySQL数据库，当前这份工作使用的是Oracle数据库
最近在做Oracle数据库的SQL调优，有了一些经验，记录一下调优的方法
并且对比一下MySQL和Oracle的explain用法的区别和相似之处
用于后面自己学习 、回顾。

MySQL与Oracle的索引的区别于联系

联系：
二者的索引均使用B树的变形，
MySQL的innoDB使用的是 B+树；
Oracle的索引使用的是 B*树；

区别：
二者的区别是 B*树除了叶子节点有相连的指针以外，的非叶子节点之间 也有指向的指针。

这样的优势是 加快索引的查询。
劣势是维护成本变高，但有限。

MySQL数据库 慢查询分析步骤和 explain 结果解析

慢查询分析步骤

explain 结果解析

Oracle数据库 慢查询分析步骤和 explain 结果解析

慢查询分析步骤

explain 结果解析

什么是执行计划？

执行计划（Execution Plan）是数据库管理系统在执行SQL查询时生成的一个详细步骤列表。它描述了数据库如何访问数据以及查询的具体执行方式。执行计划对于数据库性能优化至关重要，因为它可以帮助开发人员和DBA识别并优化低效的查询。

MySQL执行计划

在MySQL中，我们通常使用EXPLAIN命令来生成和查看查询的执行计划。EXPLAIN的输出包括以下几个重要的字段：

• id: 查询的序列号。
• select_type: 查询的类型，如简单查询、联合查询等。
• table: 查询中涉及的表。
• type: 连接类型，表示如何访问数据表，如全表扫描、索引扫描等。
• possible_keys: 查询中可能使用的索引。
• key: 实际使用的索引。
• rows: MySQL估计要读取的行数。
• Extra: 其他附加信息，如是否使用了临时表、是否进行文件排序等。

示例

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

输出示例：

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	employees	ref	dept_id_index	dept_id	10	Using where

在这个示例中，MySQL使用了索引dept_id_index，并通过该索引扫描匹配的行。

Oracle执行计划

在Oracle中，生成和查看执行计划通常使用EXPLAIN PLAN FOR命令，并通过DBMS_XPLAN.DISPLAY函数来显示执行计划。Oracle的执行计划包含以下几个重要的组件：

• Operation: 说明每一步的操作类型，如全表扫描、索引扫描等。
• Options: 操作的具体选项。
• Object Name: 涉及的表或索引名称。
• Cost: 执行该操作的相对成本。
• Cardinality: 预计返回的行数。
• Bytes: 预计处理的数据量。

示例

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

输出示例：

ID	Operation	Name	Rows	Bytes	Cost
0	SELECT STATEMENT		10	320	2
1	TABLE ACCESS BY INDEX ROWID	EMPLOYEES	10	320	2
2	INDEX RANGE SCAN	DEPT_ID_IDX	10		1

在这个示例中，Oracle使用了索引DEPT_ID_IDX，并通过该索引扫描匹配的行。

主要差异

1. 生成方式

• MySQL: 使用EXPLAIN命令直接在查询前面添加即可。
• Oracle: 使用EXPLAIN PLAN FOR命令生成执行计划，再通过DBMS_XPLAN.DISPLAY函数显示。

2. 显示内容

• MySQL: 显示的信息更为简洁，字段较少，重点在于连接类型和使用的索引。
• Oracle: 显示的信息更为详细，包含操作类型、成本估算等，更加适合复杂查询的优化分析。

3. 优化器

• MySQL: 使用基于规则的优化器和基于成本的优化器，通常较为简单。
• Oracle: 主要使用基于成本的优化器，能够处理更为复杂的查询和优化场景。

优化建议

1. 使用合适的索引：无论是MySQL还是Oracle，索引的合理使用都可以显著提高查询性能。
2. 分析执行计划：通过分析执行计划，识别全表扫描、临时表使用等低效操作，并进行优化。
3. 统计信息：确保数据库的统计信息是最新的，以便优化器能够生成最优的执行计划。

MySQL优化示例

CREATE INDEX idx_dept_id ON employees(department_id);

Oracle优化示例

CREATE INDEX idx_dept_id ON employees(department_id);
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

总结

MySQL和Oracle在执行计划的生成和显示上有着显著的差异。理解这些差异可以帮助我们更好地优化SQL查询，提高数据库性能。通过合理使用索引、分析执行计划和保持统计信息的更新，可以显著提高查询的效率。希望这篇文章能够帮助您更好地理解和使用MySQL与Oracle的执行计划。