模式匹配查询的性能调优

Question

我有两个表格：

表格1：100行

表格2：1000万行

示例：

表格1：tb100

create table tb100
(
    name varchar(50)
);

insert into tb100 values('Mak John'),('Will Smith'),('Luke W')......100 rows.

表格2：tb10mil

create table tb10mil
(
    name varchar(50)
);

insert into tb10mil values('John A Mak'),('K Smith Will'),('James Henry')......10 millions rows.    

create nonclustered index nci_tb10mil_name  on tb10mil(name);

注意: 我想在两个表之间匹配名称，如果任何一个单词(John、Smith、Will)在另一个表中出现。例如，John在John A Mark中出现。

我的尝试：

首先，我创建了用于将tb100的name拆分为行的用户定义函数。

函数：udf_Split

CREATE FUNCTION [dbo].[udf_Split]
(
@InputString VARCHAR(8000), 
@Delimiter VARCHAR(50)
)
RETURNS @Items TABLE (ID INTEGER IDENTITY(1,1), Item VARCHAR(8000))

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END
      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

      DECLARE @Item VARCHAR(8000)
      DECLARE @ItemList VARCHAR(8000)
      DECLARE @DelimIndex INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END 

      IF @Item IS NOT NULL 
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END 

接着我写了以下查询语句：

;with splitdata as
(
    select f.item as data
    from tb100 t
    cross apply dbo.udf_split(t.name,' ') f
)
select t2.name 
from tb10mil t2
inner join splitdata c on charindex(c.data,t2.name)>0
group by t2.name

上述查询需要超过20分钟才能执行完。

- MAK

---

你为什么要使用WHILE循环来进行分割？有很多数据集解决方案可以更快地完成。我建议使用XML分割器或delimitedsplit8k（如果不是nvarchar(MAX)）。那个分割器很可能是你的（第一个）问题所在。 - Thom A

首先，SQL并不是一个文本处理语言，因此您不能期望它有良好的文本拆分性能。其次，如果没有索引，您无法改善性能，并且该函数也无法使用索引。最后，SQL Server已经具备了全文搜索功能以及模糊匹配和在SSIS中进行查找。如果您想在加载10M行时模糊地匹配名称，请在SSIS中使用模糊查找运算符。 - Panagiotis Kanavos

如果您想通过名称将销售表与一组客户记录进行匹配，请在加载数据时执行此操作，并仅在目标表中存储实际的ID。不要尝试每次想要连接表时都重复模糊查找。无论使用哪种技术，模糊匹配始终比两个索引列之间的连接慢。 - Panagiotis Kanavos

如果该查询将被频繁使用，则最好从主表的 AFTER 触发器中维护一个映射表，并将分裂的数据保存在表列值中。然后可以基于这个新的映射表构建连接。 - Eralper

使用 SQL FOR XML PATH 方法拆分字符串时，以下 UDF 函数可以作为替代方案 http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx - Eralper

Answer 1

让我们谈谈性能。

• 第一点是：尽可能避免使用标量函数和多语句表值函数(multi-statement-TVFs)。唯一快速的方法是使用内联表值函数(inline-TVF)（一个一行语句）。

• 第二点：尽可能避免使用循环！

• 第三点（实际上是最重要的）: 尝试将数据存储在针对快速查询进行优化的格式中。在一个单元格中存储多个值违反了第一范式(1NF)，并且会极大地降低查询速度。

您可以尝试以下操作：

模拟您的表格。

CREATE TABLE #t100(ID INT IDENTITY,SomeName VARCHAR(200));
CREATE TABLE #t1M (ID INT IDENTITY,SomeName VARCHAR(200));

INSERT INTO #t100 VALUES('james smith'),('mak john'),('Luke W');
GO
INSERT INTO #t1M values('John A Mak'),('K Smith Will'),('James Henry'),('James John'),('Some other');
GO 

--创建表来单独存储名称片段（这实际上是您应该使用的格式）

CREATE TABLE #t100Splitted(ID INT IDENTITY PRIMARY KEY,ID_t100 INT,Fragment NVARCHAR(200));

--Use an inline XML-splitter
INSERT INTO #t100Splitted(ID_t100,Fragment)
SELECT ID
      ,B.frg.value('text()[1]','nvarchar(200)')
FROM #t100
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT SomeName AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)) A(CastedToXml)
CROSS APPLY A.CastedToXml.nodes('/x[text()]') B(frg);

--add indexes
CREATE INDEX IX_t100_ID_t100 ON #t100Splitted(ID_t100);
CREATE INDEX IX_t100_Fragment ON #t100Splitted(Fragment);

--The same for the second table
CREATE TABLE #t1MSplitted(ID INT IDENTITY PRIMARY KEY,ID_t1M INT,Fragment NVARCHAR(200));

INSERT INTO #t1MSplitted(ID_t1M,Fragment)
SELECT ID
      ,B.frg.value('text()[1]','nvarchar(200)')
FROM #t1M
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT SomeName AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)) A(CastedToXml)
CROSS APPLY A.CastedToXml.nodes('/x[text()]') B(frg);

CREATE INDEX IX_tM_ID_t100 ON #t1MSplitted(ID_t1M);
CREATE INDEX IX_tM_Fragment ON #t1MSplitted(Fragment);
GO

- Check the intermediate results
SELECT * FROM #t100Splitted;
SELECT * FROM #t1MSplitted;
GO

--这个查询将返回所有具有共同片段的行
--您可以重新连接源表以获取值
--您可以按t2.ID_t100分组，以获取较小表的ID（速度更快）

SELECT t1.ID_t1M
FROM #t1MSplitted t1
INNER JOIN #t100Splitted t2 ON t1.Fragment=t2.Fragment
GROUP BY t1.ID_t1M
GO

--清理

DROP TABLE #t100;
GO
DROP TABLE #t1M;
GO
DROP TABLE #t100Splitted;
GO
DROP TABLE #t1MSplitted;
GO

在我的系统上，大约处理了1百万行数据，用时约2分钟。

更新-100行与1000万行性能测试

(有很多查询)

• 仅拆分，将您的数据转换为更好的形式：约17分钟

• 最终选择（仅查找）：<1分钟

• 最终选择，但查找小表的ID：几秒钟
  (一旦您转换了数据，这将是正常速度)

• PSK的方法（内联拆分）：约30分钟

- Shnugo

Answer 2

你可以尝试以下方法：

像下面这样。

最初的回答

  ;WITH splitdata 
     AS (SELECT splitname 
         FROM   (SELECT *, 
                        Cast('<X>' + Replace(F.Name, ' ', '</X><X>') + '</X>' AS XML) 
                        AS  xmlfilter 
                 FROM   tb100 F)F1 
                CROSS apply (SELECT fdata.d.value('.', 'varchar(50)') AS splitName 
                             FROM   f1.xmlfilter.nodes('X') AS fdata(d)) O) 
SELECT DISTINCT t2.NAME 
FROM   tb10mil t2 
       INNER JOIN splitdata S 
               ON T2.NAME LIKE '%' + S.splitname + '%' 

- PSK

---

我真的怀疑，在数百万行数据中，使用带有前导“%”的LIKE连接模式是否会很快...还要提一下：如果名称包含禁止字符（如<、>或&等），这可能会在CAST()上出现错误。关于性能的另一个提示是：GROUP BY比DISTINCT快得多。 - Shnugo

在这个答案中，“LIKE”与性能无关。是不同的字符串拆分方法改善了性能。但与在加载数据时使用适当的查找相比，仍然很糟糕。 - Panagiotis Kanavos

Answer 3

我试图通过避免连接来节省内存并节省处理内存的时间。 我尝试在 ~420k 记录上模拟您的问题，较小表中有 4 个值。

方法是避免连接，并将问题内存空间限制为至少m和n中较大的一个。m x n

select DISTINCT t2.name
from tb10mil  t2
where (SELECT TOP(1) 1 FROM #splitdata where CHARINDEX(data,t2.Problem)>0)=1 

结果：使用本问题中的方法所需时间减少了一半。（从约28秒减少到约14秒）

缺点：该方法只有在其中一个表格相对较小时才有益。

- mvs