為 charindex 函式拆分/儲存長字元串的最快方法
我有一個 1 TB 的數字字元串。給定一個 12 個字元的數字序列，我想獲取該序列在原始字元串（
charindex函式）中的起始位置。我已經使用 SQL Server 使用 1GB 字元串和 9 位子字元串對此進行了測試，將字元串儲存為
varchar(max).Charindex需要 10 秒。將 1GB 字元串分解為 900 字節的重疊塊，並在二進制排序規則中使用 chunkofstring 創建一個表（StartPositionOfChunk，Chunkofstring），索引需要不到 1 秒。用於 10GB、10 位數字子串的後一種方法將 charindex 提高到 1.5 分鐘。我想找到一種更快的儲存方法。例子
數字字元串：0123456789 - 要搜尋的子字元串 345
charindex(‘345’,‘0123456789’) 給出 4
方法 1：我現在可以將其儲存在包含一列的 SQL Server 表 strtable 中
colstr並執行：select charindex('345',colstr) from strtable方法2：或者我可以通過拆分原始字元串來組成一個表strtable2（pos，colstr1） ： 1;012 | 2;123 | 3;234 aso然後我們可以進行查詢
select pos from strtable2 where colstr1='345'方法 3：我可以通過將原始字元串拆分成更大的塊來組成一個表strtable2 (pos2,colstr2) 1;01234 | 4;34567 | 7;6789然後
select pos2+charindex('345',colstr2) from strtable2 where colstr2 like '%345%'第一種方法是最慢的。
第二種方法會炸毀數據庫儲存大小！
方法 3：在二進制排序規則中將 colstr2 長度設置為 900 字節，在該列上創建索引對於 1GB 字元串和 9 位子字元串搜尋需要 1 秒。對於 10GB 字元串和 10 位子字元串，需要 90 秒。
任何其他想法如何使它更快（也許通過利用由數字組成的字元串，長整數，….）？
搜尋始終在 1TB 數字字元串中搜尋 12 位子字元串 SQL Server 2017 開發人員版，16 核，16GB RAM。主要目標是搜尋速度！10GB 字元串中的 10 位數字（用於性能測試）。
我建議使用方法 2 的風格並將搜尋範圍拆分為多個目標表。10000 個表是一個很好的第一次嘗試。例如，如果您搜尋“012345678901”，那麼您的查詢將查看與以“0123”開頭的數據關聯的表。您仍然會有大約一萬億行，但將數據拆分為許多表具有以下優點：
- 現在所有可能的 12 位字元串都可以放入 INT 中。
- 無論如何，為 1 TB 字元串建構更高效的搜尋表示可能會很昂貴。使用許多表，您可以輕鬆地並行化作業，甚至可以臨時要求為您的 VM 分配更多 CPU。
- 您可以建構單個表作為概念證明，以確定完整字元串的查詢時間和總空間要求。
- 如果您需要進行任何類型的數據庫維護，您會很高興您沒有創建一個巨大的表。
在這一點上，我的主要問題是您使用壓縮的行儲存還是列儲存。下面的程式碼為“0123”搜尋空間創建了一個行儲存表，並在其中插入了 1 億行。如果您的字元串足夠隨機，那麼您還可以期望每個表看到大約 1 億行。
DROP TABLE IF EXISTS #t; SELECT TOP (10000) 0 ID INTO #t FROM master..spt_values t1 CROSS JOIN master..spt_values t2 OPTION (MAXDOP 1); DROP TABLE IF EXISTS dbo.Q229892_RAW_100M_RANGE; CREATE TABLE dbo.Q229892_RAW_100M_RANGE ( STRING_PIECE INT NOT NULL, STR_POS BIGINT NOT NULL ); INSERT INTO dbo.Q229892_RAW_100M_RANGE WITH (TABLOCK) SELECT ABS(CHECKSUM(NEWID()) % 100000000), TRY_CAST(ABS(CHECKSUM(NEWID())) AS BIGINT) * TRY_CAST(ABS(CHECKSUM(NEWID())) AS BIGINT) FROM #t t1 CROSS JOIN #t t2 OPTION (MAXDOP 4); DROP TABLE IF EXISTS dbo.T0123_Q229892_PAGE_COMPRESSION; CREATE TABLE dbo.T0123_Q229892_PAGE_COMPRESSION ( STRING_PIECE INT NOT NULL, STR_POS BIGINT NOT NULL, PRIMARY KEY (STRING_PIECE, STR_POS) ) WITH (DATA_COMPRESSION = PAGE); INSERT INTO dbo.T0123_Q229892_PAGE_COMPRESSION WITH (TABLOCK) SELECT STRING_PIECE, STR_POS FROM dbo.Q229892_RAW_100M_RANGE;壞消息是您可能需要大約 15.4 TB 的完整數據集。好消息是，即使緩衝區記憶體中沒有相關數據，查詢對我來說也只需要 1 毫秒，對於像您這樣大的數據集幾乎總是如此。
-- 1 ms CHECKPOINT; DBCC DROPCLEANBUFFERS; SELECT MIN(STR_POS) FROM dbo.T0123_Q229892_PAGE_COMPRESSION WHERE STRING_PIECE = 45678901; -- searching for '012345678901'您可能可以將這些數據放在您擁有的最便宜的儲存上，並且仍然可以看到良好的響應時間，因為查詢執行的邏輯讀取很少。
對於列儲存，您無法查找所需的數據，而且您仍然極不可能將所有數據放入記憶體中，因此在查詢中讀取盡可能少的壓縮數據非常重要。我強烈建議對您的表進行分區。一種行之有效的簡單方法是使用搜尋字元串的前四位數字來查找表名，然後使用後兩位數字作為分區。再次使用“012345678901”，您將轉到保存“0123”數據的表的分區 45。100 個分區是一個很好的數字，可以避免由太多分區引起的問題，最終每個分區平均會有大約 100 萬行。可以放入單個行組的最大行數是 1048576，因此使用這種方法，您將執行盡可能少的 IO。
DROP TABLE IF EXISTS dbo.Q229892_RAW_1M_RANGE; CREATE TABLE dbo.Q229892_RAW_1M_RANGE ( STRING_PIECE INT NOT NULL, STR_POS BIGINT NOT NULL ); INSERT INTO dbo.Q229892_RAW_1M_RANGE WITH (TABLOCK) SELECT TOP (1000000) ABS(CHECKSUM(NEWID()) % 1000000), TRY_CAST(ABS(CHECKSUM(NEWID())) AS BIGINT) * TRY_CAST(ABS(CHECKSUM(NEWID())) AS BIGINT) FROM master..spt_values t1 CROSS JOIN master..spt_values t2 OPTION (MAXDOP 1); DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION partition100 (tinyint) AS RANGE LEFT FOR VALUES ('; DECLARE @i int = 0; WHILE @i < 100 BEGIN SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N', '; SET @i += 1; END SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N');'; EXEC sp_executesql @IntegerPartitionFunction; GO CREATE PARTITION SCHEME partition100_scheme AS PARTITION partition100 ALL TO ([DEFAULT]); DROP TABLE IF EXISTS dbo.T0123_Q229892_COLUMNSTORE; -- this table must be partitioned by PART_ID! CREATE TABLE dbo.T0123_Q229892_COLUMNSTORE ( PART_ID TINYINT NOT NULL, STRING_PIECE INT NOT NULL, STR_POS BIGINT NOT NULL, INDEX CCS CLUSTERED COLUMNSTORE ) ON partition100_scheme (PART_ID); GO DECLARE @part_id TINYINT = 0; SET NOCOUNT ON; WHILE @part_id < 100 BEGIN INSERT INTO dbo.T0123_Q229892_COLUMNSTORE WITH (TABLOCK) SELECT @part_id, STRING_PIECE, STR_POS FROM dbo.Q229892_RAW_1M_RANGE OPTION (MAXDOP 1); SET @part_id = @part_id + 1; END; GO使用這種方法，完整的數據集將需要大約 10.9 TB。我不清楚如何使它更小。在這種情況下，搜尋查詢會慢一些。在我的機器上大約需要 25 毫秒，但這主要取決於 IO：
CHECKPOINT; DBCC DROPCLEANBUFFERS; SELECT MIN(STR_POS) FROM dbo.T0123_Q229892_COLUMNSTORE WHERE PART_ID = 45 AND STRING_PIECE = 678901; -- searching for '012345678901'關於列儲存方法的一個重要說明是 10.9 TB 的數據是針對 100% 壓縮的數據。在避免增量儲存的同時有效地填充這樣的表將是一項挑戰。在此過程中的某個時間點，您最終可能會在增量儲存中獲得未壓縮的數據，這很容易需要超過用於行儲存方法的 15.4 TB。
僅使用 16GB RAM 來儲存和處理 1TB 的數據可能是一項挑戰。每個核心 1GB 相當不平衡，尤其是對於這種工作負載。每個核心 8GB 將是一個更好的起點，更可取。
也就是說，我仍然很想嘗試方法 2 的變體：
將所有可能的 12 個字元的子字元串儲存
bigint在聚集列儲存表中（如果有用，則使用存檔壓縮）：CREATE TABLE dbo.Search ( pos bigint NOT NULL, fragment bigint NOT NULL, INDEX CCS CLUSTERED COLUMNSTORE WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) -- optional );您可能必須實現某種方式將源數據增量載入到此表中。確保在完成的列儲存結構中得到最大大小的行組（1,048,576 行） 。請參閱數據載入指南。
在創建聚集列儲存索引之前，您可以在未索引的行儲存表中暫存 1048576 的倍數的行，然後將結果直接切換到分區主表中。確切的方法取決於您打算如何載入數據、是否將其添加到以及您對 SQL Server 的熟悉程度。
使用這種方法可以獲得非常好的性能，但與列儲存一樣，您需要實現有效的分區和段消除。
fragment如上面連結的文件中所述，在替換鍵控的行儲存聚集索引時對列進行分區並串列建構列儲存索引fragment是實現此目的的方法。這也將最小化儲存需求，因為fragment同一範圍內的值將儲存在同一段中。這允許有效的值變基和位打包。載入時，嘗試將您在 SQL Server 中使用的字元串限制為非 LOB（最大）類型。如果您確實發現使用 LOB 最適合吞吐量，那麼通常會找到數據長度的最佳點，超過該點時性能會顯著下降。
根據結構的最終大小和 I/O 子系統的速度，您可能會發現這種方法始終提供足夠好的性能。增加可用記憶體將顯著改善情況。