python - 不创建索引的 SQL 优化

Question

我正在尝试优化在 Oracle 数据库上运行的 SQL 查询。查询是:

SELECT Col_5, Col_10, Col_15, Col_20, ..., Col_100
FROM TABLE
WHERE Col_5 = 37 AND Col_10 IS NOT NULL

TABLE 有 1 亿多行，并且更新频繁。看起来像:

+--------+-------+-----+-------+-----+------------+-----+---------+
| Col_1  | Col_2 | ... | Col_5 | ... |  Col_10    | ... | Col_100 |
+--------+-------+-----+-------+-----+------------+-----+---------+
| 12     | Bob   | ... | 18    | ... | 08/06/1992 | ... | 12.6    |
| 6      | Mike  | ... | 37    | ... | NULL       | ... | 18.0    |
| 18     | Sally | ... | 6     | ... | NULL       | ... | 16.3    |
| :      | :     | :   | :     | :   | :          | :   | :       |
| 94     | Eli   | ... | 37    | ... | 01/22/2003 | ... | 19      |
+--------+-------+-----+-------+-----+------------+-----+---------+

我的查询返回大约 1000 万行，但运行时间大约为 30 分钟。我应该补充一点，这个查询是通过 pd.read_sql(SQL_query, data_connection) 在 Python 中读入 Pandas DataFrame 的。一旦进入 Python，所有后续数据操作都会在几秒钟内执行。出于这个原因，我排除了简单地添加更多 WHERE 子句来减少返回行数的可能性，因为我想对所有返回的 1000 万左右的行执行操作。

不幸的是，创建索引不是一个选项。鉴于此限制，我是否可以加快查询速度？

谢谢!

Answer 1

您的查询返回大约 10% 的表行。它从整行中选择多列。假设发布的示例数据具有代表性，不存在数据聚集，并且选定的行将来自整个表。

所以索引对你没有帮助。您需要全表扫描。

对您的查询运行解释计划以确保您正在使用一个解释计划。如果由于某种原因您正在运行某种形式的索引读取修复该问题(例如通过刷新过时的统计信息)。除此之外，调整全表扫描的方法并不多:如果您有企业版并且有多个可用的 CPU，那么并行查询可能会有所帮助。

但是，让我们回顾一下。

"My query returns about 10 million rows but takes about 30 minutes to run."

瓶颈在哪里？几乎可以肯定不是数据库。 1000 万行约 20 列是一个巨大的数据量，但一个表上的全表扫描应该在几分钟内运行，甚至更少(取决于服务器的性能)。

很可能是网络(除非您在本地运行此查询，即 pandas 客户端位于数据库服务器上)。您正在通过网络传递许多很多数据包。您可能会遇到可用带宽的限制，因此请与您的网络管理团队联系(或安装wireshark!)。如果网络延迟是问题所在，请检查设置 pd.read_sql() chunksize 是否有帮助。

要考虑的另一件事是客户端内存:数据帧是内存中的结构，因此您加载的数据可能超出客户端可以处理的数据。您可以采取的任何措施来减少所选数据的总量都会有所帮助。重新访问查询的投影并删除后续处理不需要的任何列。

<小时/>

' EXPLAIN PLAN FOR command isn't returning anything'

EXPLAIN PLAN 填充表格。 Find out more 。您需要查询该表才能得出计划。 DBMS_XPLAN 内置包是您最好的 friend 。 Check it out 。某些 IDE(例如 PL/SQL Developer)会透明地处理此问题。