索引引用的表列

我想在連接表的列中進行搜尋。以下查詢的最佳索引配置是什麼？

Table A
------------
Id
ValueA
-- Table A has many more columns

Table B
------------
Id
AId
ValueB
-- Table B has many more columns

目前，我在主鍵上有聚集索引，對列AId、、ValueA和ValueB。

SELECT *
FROM TableA a
INNER JOIN TableB b ON a.Id = b.AId
WHERE 
   a.ValueA LIKE 'SearchTerm%' OR
   b.ValueB LIKE 'SearchTerm%'

更新 表 A 可能會達到低數百萬，而表 B 將只有低數万條目。

在現實世界中，表 A 將是一個交易列表，其中的值是某種交易參考。表 B 將是一個使用者列表，其中的值可以是任何東西，例如名稱或任何其他描述性資訊。

目前我們使用一個 ORM，它本質上會做一個SELECT *. 當然這可以改變，但對應用程序端會有相當大的影響。

出於兩個原因，您的查詢對於優化器來說是一個棘手的問題。

1. 優化器目前沒有將表之間的析取 (OR) 轉換為聯合（相關 Q & A）的邏輯。
2. 優化器不會考慮推遲查找（僅在最後一刻處理鍵）。這與SELECT *給定查詢的組件有關。

總之，這些限制意味著很少有索引可以幫助編寫（ORM 生成）查詢，撇開（非聚集）列儲存索引的可能性。

如果性能不足，您可能不得不硬著頭皮編寫自定義查詢。在這種情況下，修改現有索引ValueB以包含 AId將是有用的，再加上如下的重寫：

SELECT TA.*, TB.* 
FROM 
(
   -- Disjunction as union
   SELECT A.Id, b.Id
   FROM TableA a 
   INNER JOIN TableB b 
       ON b.AId = a.Id
   WHERE 
       a.ValueA LIKE 'SearchTerm%'

   UNION

   SELECT A.Id, b.Id
   FROM TableB b 
   INNER JOIN TableA a 
       ON a.Id = b.AId
   WHERE 
       b.ValueB LIKE 'SearchTerm%'
) AS M (A_Id, B_Id)
-- Lookups
JOIN dbo.TableA AS TA ON TA.Id = M.A_Id
JOIN dbo.TableB AS TB ON TB.Id = M.B_Id;

目標平面形狀為：

這在實踐中有多好取決於幾個因素，包括LIKE謂詞的選擇性（並且它們始終是前綴搜尋）。

---

猜測架構：

DROP TABLE IF EXISTS dbo.TableB, dbo.TableA;

CREATE TABLE dbo.TableA 
(
   Id integer NOT NULL PRIMARY KEY, 
   ValueA varchar(256) NULL INDEX iValueA,
   Padding char(4000) NOT NULL DEFAULT '', -- other columns

   /*INDEX i (ValueA) INCLUDE (Id)*/
);

CREATE TABLE dbo.TableB 
(
   Id integer NOT NULL PRIMARY KEY,
   AId integer NOT NULL REFERENCES dbo.TableA (Id) INDEX iAId,
   ValueB varchar(256) NULL /*INDEX iValueB*/,
   Padding char(2000) NOT NULL DEFAULT '', -- other columns

   INDEX i (ValueB) INCLUDE (AId)
);

UPDATE STATISTICS dbo.TableA WITH ROWCOUNT = 3000000, PAGECOUNT = 3000000;
UPDATE STATISTICS dbo.TableB WITH ROWCOUNT = 30000, PAGECOUNT = 30000;

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