問題描述
假設(shè)我有一個包含 300 萬行的表，該表沒有 PK 也沒有索引.
let's say i have a table with 3 million rows, the table does not have a PK nor Indexes.
查詢?nèi)缦?/p>
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 是一個函數(shù)，用于將格式為 '1,2,3' 的字符串轉(zhuǎn)換為如下圖所示的 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.
此模式允許我從應(yīng)用程序發(fā)送參數(shù)并按 sku 或按商店或兩者或無過濾.
This pattern allows me to send arguments from the application and filter by sku or by store or both or none.
我可以做些什么來提高此查詢的性能?- 我知道索引是一個好主意，但我真的不知道那些東西，所以對此的指導(dǎo)會有所幫助.還有其他想法嗎?
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.
除了建議使用臨時表來存儲字符串解析的結(jié)果之外，您還可以做一些其他的事情:
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:
添加索引
通常建議每個表都有一個聚簇索引(雖然似乎仍有爭論的空間):向現(xiàn)有表添加聚集索引會提高性能嗎?
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:
- SKU(僅用于搜索 SKU)
- 商店、SKU(適用于商店搜索以及商店和 SKU 的組合)
請記住，如果查詢匹配太多記錄，則可能不會使用這些索引.還要記住，使索引覆蓋查詢可以提高性能:為什么在創(chuàng)建索引時使用 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?
這里是微軟關(guān)于創(chuàng)建索引的文檔的鏈接: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
使用動態(tài) SQL 構(gòu)建查詢
我需要以警告開頭.請注意 SQL 注入，并確保正確編碼！如何清理 SQL Server 中的動態(tài) 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
構(gòu)建動態(tài) SQL 查詢可以讓您編寫更精簡、更直接的 SQL，從而讓優(yōu)化器做得更好.這通常是需要避免的，但我相信它適合這種特殊情況.
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.
這是一個例子(應(yīng)該根據(jù)需要進(jìn)行調(diào)整以考慮 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;
這篇關(guān)于通過條件過濾提高查詢性能的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！