即使使用非聚集索引和全文索引，SE LECT 查詢也很慢

我正在嘗試從表中查詢 0 到 65,000 行。

伺服器使用的是 Microsoft SQL Server 2014，我無法更改伺服器上的硬體。

架構

[Id] (PK)        INT      
[varchar1]  VARCHAR(4)     Normal Cardinality
[varchar2]  VARCHAR(250)   Normal Cardinality
[varchar3]  VARCHAR(250)   Normal Cardinality
[varchar4]  VARCHAR(100)   Normal Cardinality
[date1]     DATETIME       High Cardinality
[varchar5]  VARCHAR(100)   Low Cardinality
[varchar6]  VARCHAR(1000)  Normal Cardinality
[varchar7]  VARCHAR(100)   Normal Cardinality
[varchar8]  VARCHAR(20)    Normal Cardinality
[varchar9]  VARCHAR(100)   High Cardinality
[xml1]      XML            Low Cardinality

詢問

以下查詢是儲存過程的一部分（其餘部分無關緊要，因為它對儲存過程的性能影響很小）。列名已替換為列類型和數字：

SELECT   [varchar1]
      , [varchar4]
      , [date1]
      , [varchar5]
      , [varchar6]
      , [varchar7]
      , [varchar8]
      , [varchar9]
      , [xml1]
FROM [database].[dbo].[table] WITH (NOLOCK)
WHERE [varchar1] = '0'
AND   ([date1] >='2014-1-1' AND [date1] <= '2017-1-1')
AND   [varchar8] = 'someText'
AND   [varchar9] LIKE '%a%'
ORDER BY [varchar1] ASC, [date1] DESC
OFFSET 0 ROWS
FETCH NEXT 65000 ROWS ONLY

執行計劃

執行計劃 XML：https ://gist.github.com/BlackyWolf/046856518065bfe5293cad78f73340e9

但到目前為止它提供的資訊是：

Query1: Query cost (relative to the batch): 100%
                              Index Seek [NonClustered]
SELECT            Top          [Table].[i_table_index]
Cost: 0%         Cost: 4%              Cost: 96%

我在此搜尋中沒有使用 PK。此查詢具有以下持續時間，具體取決於刪除的列：

select 語句中的所有列 = 18s-27s
沒有 [xml1] = 8s-11s
沒有 [xml1] 和 [varchar4] = 4s-6s

從 Web 到 DB 並返回的總執行需要在 10 秒內。最好這個查詢需要在 4s 內。

返回大小 (MB)

我希望返回 290-310 MB 的數據，誤差範圍為 20 MB，總共 65,000 條記錄。

指數

主鍵上有一個聚集索引。

有一個非聚集索引定義為：

CREATE NONCLUSTERED INDEX [i_table_index]
ON dbo.[table] ([varchar1], [date1] DESC, [varchar8], [varchar9])
INCLUDE ([varchar4], [varchar7], [xml1]);

不幸的是，我不能真正刪除[xml1]，或者[varchar4]我會。老實說，該索引似乎沒有多大幫助，即使執行計劃顯示它正在使用它。

我的 SQL 經驗僅限於我使用 C# 所做的事情。感謝您提供的任何幫助或指導（甚至連結），如果您需要更多資訊，請告訴我，我會盡我所能。

where 子句中的每個元素都在使用函式。使用函式時，sql 無法確定要使用的索引。它無法確定 where 子句中的哪些欄位會導致表掃描。

1. 收斂日期 => 和日期 =< 之間的日期
2. 數據庫真的區分大小寫嗎？你能擺脫 lower()
3. 你能把 contains 變成一個 patindex

Bret 的概述和第二項我會同意，但必須反駁第 1 項和第 3 項：

• 之間很好，因為它將在內部進行優化，完全符合 Bret 的建議。但請注意 between 是inclusive，但僅限於午夜，即如果列僅是日期，則您的範例查詢將包含 2017-1-1 的記錄，或者 2017-1-1 00:00:00 （但不是 2017-1 -1 00:00:00.001 及以上）如果是日期時間。
• 一般來說，針對全文索引的 Contains 會比 patindex 快得多。但是，對於這個特定的查詢，patindex 可能會更快，特別是如果該列包含在查詢主要依賴的索引中（即使用 include 子句）。但請記住， contains 是模糊的，而 patindex 不是（如果使用萬用字元則更窄）。

我看到你剛剛做了一個大更新，所以我可能會做一個單獨的更廣泛的答案。由於我還不能發表評論，我會在這裡問一些事情：

• 您是否可以根據查詢需要添加索引？

• 是替換 CONTAINS(

$$ varchar3 $$, ‘moreText’) 與$$ varchar9 $$LIKE ‘%a%’ 正確（即你肯定不再需要在 varchar3 上進行模糊搜尋嗎？）

• OFFSET 是否始終為 0？

• 你能大致了解一下你期望列 varchar1、date1 和 varchar8 的數據比例嗎？

引用自：https://dba.stackexchange.com/questions/162804