主页 / dba / 问题 / 3728
Accepted
Simon Verbeke
Asked: 2011-07-09 10:27:24 +0800 CST

查询优化

  • 772

在另一个问题中，他们告诉我，DBMS 的速度在很大程度上取决于查询的优化。有人知道这方面的好文件吗？在学校我只学了基础知识。

例如，查找具有密码的用户的查询。

我将从包含个人信息或仅包含用户名/密码的表中获取此信息。

我的查询看起来像：

SELECT username, password
FROM tblUsers
WHERE username = foo

这有什么优化吗？或者它只是在非常大的操作中，比如有很多包含大量记录的 JOINS？

编辑：上面的例子只是一个例子。我正在寻找有关查询优化的文档；）

optimization

3 个回答

  1. 我唯一的建议是确保您在用户名列上有一个良好的索引，以便尽可能快地进行查找。通常需要对频繁使用和/或复杂的查询进行优化。对于某些数据库（如 Oracle），重新计算表的统计信息通常也有帮助（因为随着表的增长，这些统计信息可能会变得陈旧，从而导致 SQL 引擎内部的优化选择错误）。

    • 3

  2. 您的查询可以通过这种方式进行优化

    @var = foo

SELECT @var AS username, password
FROM tblUsers
WHERE username = @var

    没有理由向数据库询问您已经提供的信息。

    优化查询既是一门艺术，也是一门科学。尝试在查询中做尽可能少的工作。将查询分成多个部分并使用临时表或表变量来存储您的工作，而不是派生表或子查询。如果某件事看起来太复杂，那么它可能就是这样（这是优化的“艺术”部分。）

    现在这里是“科学”。

    使用 SQL Management Studio 输出查询性能信息。

    Click "Query" / "Include Client Statistics"

    打开一个新的查询窗口后执行一次这条语句：

    SET STATISTICS IO ON

    并在每个查询之前执行此语句以确保您始终从基线开始：

    DBCC FREEPROCCACHE

    执行查询后查看“消息”输出窗口并保存信息。修改查询并再次执行后，您可以比较 I/O 统计信息。

    最后，了解如何使用 SQL Profiler 工具快速捕获和比较大量统计信息，例如每个查询的总读取、写入、CPU 和持续时间 (ms)。

    • 1
  3. Best Answer

    在关系数据库中优化查询的众多方法中，索引是最好的起点。当您索引一列时，数据库会按排序顺序存储该列的记录以提高搜索速度。您为此付出的代价是额外的存储开销和 INSERT/UPDATE/DELETE 语句的速度下降。

    当您在无序列表中搜索值时，最坏的情况是您必须查看每条记录。如果您有一个包含 1,000,000 个项目的列表，则必须扫描所有 1,000,000 个。另一方面，如果列表是有序的，则可以改用二进制搜索，它可以在不超过 20 次查找中找到匹配的记录。

    想到这一点的最简单方法是电话目录，它以姓氏为索引。因此，如果您要查找“Wilson”，则可以跳过大部分页面并直接转到“W”。另一方面，如果你正在寻找一个号码来找出拥有该号码的人的名字，你将被迫从头开始，一次一个地查找，这将是非常痛苦的.

    现在想象一下，您有第二个按号码排序的电话目录。您现在拥有的书籍数量是现在的两倍，这会占用更多空间，但如果您通常根据电话号码查找姓名，那么这种权衡是非常值得的。

    数据库索引与此非常相似。作为基本规则：

    • 您经常包含在 WHERE/GROUP BY 子句中的索引列。

      select * from t1 where poo = 'smelly'

      select category, count(*) from t2 group by category

    • 索引外键（引用其他表中键的列）。例如：

      select * from t1 join t2 on t1.lol = t2.rofl

      如果两个表中的数据都足够大，则外键索引t1.lol可能会带来显着的收益。不应盲目地为外键建立索引：与任何列一样，不适当的索引没有任何好处，有时会影响性能并导致死锁等问题。

    • 索引可以包含多个列。事实上，如果SELECT 列表中的所有列都可以在索引中找到，则可以针对索引而不是表运行查询。多列索引可以通过使用 AND（例如，a = 'lol AND b = 'rofl'）的复合 WHERE 条件来利用，但不能使用 OR。如果之前的谓词被重写为a = 'lol OR b = 'rofl'，您最好使用列a和上的两个单独索引b。

    这只是触及表面，但会给你带来实质性的努力。

    • 1

相关问题