Sql 索引 vs 全表扫描 [英] Sql indexes vs full table scan

问题描述

在编写复杂的 SQL 查询时，我们如何确保使用正确的索引并避免全表扫描?我通过确保我只加入具有索引(主键、唯一键等)的列来做到这一点.够了吗?

While writing complex SQL queries, how do we ensure that we are using proper indexes and avoiding full table scans? I do it by making sure I only join on columns that have indexes(primary key, unique key etc). Is this enough?

推荐答案

查看查询的执行计划，了解查询优化器如何认为必须检索事物.该计划通常基于表的统计信息、索引的选择性和连接的顺序.请注意，优化器可以决定执行全表扫描比索引查找便宜".

Look at the execution plan of the query to see how the query optimizer thinks things must be retrieved. The plan is generally based on the statistics on the tables, the selectivity of the indices and the order of the joins. Note that the optimizer can decide that performing a full table scan is 'cheaper' than index lookup.

其他需要查找的内容:

• 尽可能避免子查询.

• avoid subqueries if possible.

尽量减少'OR'-谓词的使用
在 where 子句中

minimize the use of 'OR'-predicates
in the where clause

这篇关于Sql 索引 vs 全表扫描的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！