通过条件过滤提高查询性能

2021-09-10 00:00:00 tsql sql-server

假设我有一个包含 300 万行的表，该表没有 PK 也没有索引.

let's say i have a table with 3 million rows, the table does not have a PK nor Indexes.

查询如下

SELECT SKU, Store, ColumnA, ColumnB, ColumnC
FROM myTable
WHERE (SKU IN (select * from splitString(@skus)) OR @skus IS NULL)
AND (Store IN (select * from splitString(@stores)) OR @stores IS NULL)

请考虑 @sku 和 @store 是 NVARCHAR(MAX) 包含以逗号分隔的 id 列表.SplitString 是一个函数，用于将格式为 '1,2,3' 的字符串转换为如下图所示的 1 列 3 行表格.

Please consider that @sku and @store are NVARCHAR(MAX) containing a list of ids separated by comma. SplitString is a function which converts a string in format '1,2,3' to a table of 1 column and 3 rows as shown in the following picture.

此模式允许我从应用程序发送参数并按 sku 或按商店或两者或无过滤.

This pattern allows me to send arguments from the application and filter by sku or by store or both or none.

我可以做些什么来提高此查询的性能?- 我知道索引是一个好主意，但我真的不知道那些东西，所以对此的指导会有所帮助.还有其他想法吗?

What can I do to improve performance of this query? - I know Indexes are a good idea, but I don't really know about that stuff, so a guidance to that will be helpful. Any other ideas?

推荐答案

这种类型的通用搜索查询在性能上往往很粗糙.

This type of generic search query tends to be rough on performance.

除了建议使用临时表来存储字符串解析的结果之外，您还可以做一些其他的事情:

In addition to the suggestion to use temp tables to store the results of the string parsing, there are a couple other things you could do:

添加索引

通常建议每个表都有一个聚簇索引(虽然似乎仍有争论的空间):向现有表添加聚集索引会提高性能吗?

It's usually recommended that each table have a clustered index (although it seems there is still room for debate): Will adding a clustered index to an existing table improve performance?

除此之外，您可能还想为正在搜索的字段添加索引.

In addition to that, you will probably also want to add indexes on the fields that you're searching on.

在这种情况下，可能类似于:

In this case, that might be something like:

  1. SKU(仅用于搜索 SKU)
  2. 商店、SKU(适用于商店搜索以及商店和 SKU 的组合)

请记住，如果查询匹配太多记录，则可能不会使用这些索引.还要记住，使索引覆盖查询可以提高性能:为什么在创建索引时使用 INCLUDE 子句?

Keep in mind that if the query matches too many records, these indexes might not be used. Also keep in mind that making the indexes cover the query can improve performance: Why use the INCLUDE clause when creating an index?

这里是微软关于创建索引的文档的链接:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql

Here is a link to Microsoft's documentation on creating indexes: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql

使用动态 SQL 构建查询

我需要以警告开头.请注意 SQL 注入，并确保正确编码！如何清理 SQL Server 中的动态 SQL --防止SQL注入

I need to preface this with a warning. Please be aware of SQL injection, and make sure to code appropriately! How to cleanse dynamic SQL in SQL Server -- prevent SQL injection

构建动态 SQL 查询可以让您编写更精简、更直接的 SQL，从而让优化器做得更好.这通常是需要避免的，但我相信它适合这种特殊情况.

Building a dynamic SQL query allows you to write more streamlined and direct SQL, and thus allows the optimizer to do a better job. This is normally something to be avoided, but I believe it fits this particular situation.

这是一个例子(应该根据需要进行调整以考虑 SQL 注入):

Here is an example (should be adjusted to take SQL injection into account as needed):

DECLARE @sql VARCHAR(MAX) = '
    SELECT SKU, Store, ColumnA
    FROM myTable
    WHERE 1 = 1
';

IF @skus IS NOT NULL BEGIN
    SET @sql += ' AND SKU IN (' + @skus + ')';
END

IF @stores IS NOT NULL BEGIN
    SET @sql += ' AND Store IN (' + @stores + ')';
END

EXEC sp_executesql @sql;

相关文章