索引MSSQL数据库优化之后缀索引

1.什么是后缀索引？

后缀索引，也称倒序索引（Reverse Index），是一种索引方式，它是基于字符串后缀的排序特性进行建立的。在使用后缀索引时，将目标字符串颠倒后建立索引，因此也称为倒排索引。后缀索引的实现是将字符串切成一个个的后缀，例如将字符串“hello”切成“o”，“lo”和“llo”等后缀，然后再分别将切好的后缀单独进行索引。

2.后缀索引在MSSQL数据库优化中的应用

在MSSQL数据库中，后缀索引可用于提高字符串字段的模糊查询效率，这对于日志等大量存储字符串的表格非常有用。通常，在SQL Server中，为了提高字符串的模糊查询效率，需要事先在该列上创建非聚集索引，但这种方式的查询效率仍然较低。

2.1 后缀索引的优点：

1. 后缀索引可以极大地提高字符串字段的模糊查询效率；

2. 后缀索引占用的存储空间要比创建非聚集索引小得多，因为后缀索引只需要对字符串的后缀进行索引，而非整个字符串；

2.2 后缀索引的缺点：

由于后缀索引是基于倒序排序的，所以对于需要进行正序排序的模糊查询，后缀索引的效率相对较低，此时建议采用其他索引方式。

3.后缀索引的创建

以MSSQL Server 2017为例，以下示例展示了如何在MSSQL Server中创建后缀索引：

首先，为了演示方便，创建一张测试表格，表格中包含一个字符串字段：

CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TestString] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

然后向该表格中插入随机生成的字符串：

DECLARE @Loop INT = 1;
DECLARE @TempString CHAR(10);
WHILE (@Loop < 10000)
BEGIN
    SET @TempString = NEWID();
    INSERT INTO TestTable([TestString])
    VALUES (REPLACE(@TempString, '-', '') + 'TestMatchString');
    SET @Loop = @Loop + 1;
END;

接着，在TestTable中的TestString字段上创建后缀索引：

CREATE NONCLUSTERED INDEX IX_Testtable_TestString
    ON TestTable(TestString DESC)
    INCLUDE (ID);

此处说明一下，由于后缀索引是基于字符串后缀的，所以需要对TestString这个字段进行颠倒，即 DESC。

创建成功后，可以使用以下SQL语句验证所创建的后缀索引的有效性：

SELECT TestTable.ID, TestTable.TestString
FROM TestTable
WHERE TestTable.TestString LIKE '%TestMatchString'
ORDER BY TestTable.ID;

上述SQL语句的查询条件是以“%TestMatchString”作为结尾匹配的字符串，这将使用到TestString列上的后缀索引，如果查询结果正确，说明后缀索引创建成功。

4. 后缀索引的高级应用：音序索引

音序索引全称叫做日语音节序列索引，它是后缀索引的一种具体实现方式。由于日语拼音字母并不是像英语等其他语言的字符，所以对日语拼音字母进行情况敏感的查询及排序非常困难。在这种情况下，一般采用音序这种方式，即将日语拼音字母根据声母及韵母进行分割，然后对它们进行单独的排序，从而实现对日语拼音的情况敏感的查询及排序。

4.1 音序索引的实现方法

音序的建立可以使用CLR User-Defined Function来实现。首先，创建CLR User-Defined Function：

--创建排序函数CSharpAdvancedOrdering
CREATE ASSEMBLY CSharpAdvancedOrdering
    FROM 'D:\CSharpAdvancedOrdering.dll'
    WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.Soundex
(
    @Text NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME CSharpAdvancedOrdering.CSharpAdvancedOrdering.Soundex;
GO

上述代码中，创建了一个名为Soundex的CLR函数，该函数将会引用一个名为CSharpAdvancedOrdering.dll的程序集，该函数接受一个参数@Text，返回一个NVARCHAR类型的字符串。

建立CLR函数之后，可以在MSSQL数据库中创建基于音序索引的后缀索引。创建音序索引时，需要使用该CLR函数计算每个字符串的音序，并将音序作为后缀索引进行索引。

CREATE NONCLUSTERED INDEX IX_TestTable_TestString_Soundex
    ON TestTable(TestString DESC)
    INCLUDE (ID)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    WHERE (dbo.Soundex(TestString) IS NOT NULL);

此处要说明的是，在创建音序索引时，为了避免无效音序的影响，凡是计算出来的音序为NULL的字符串都将不会被建立索引。在查询时，同样需要使用dbo.Soundex函数进行匹配，就像下面这个示例中最后一行代码所示：

SELECT TestTable.ID, TestTable.TestString
FROM TestTable
WHERE dbo.Soundex(TestTable.TestString) = dbo.Soundex('テスト');

5. 后缀索引的总结

后缀索引是一种高效的索引方式，在MSSQL Server中的字符串字段的模糊查询优化中具有广泛的应用场景。倒排索引占用的存储空间较小，可以极大地提高模糊查询效率，但它不适用于需要进行正序排序的模糊查询。音序索引是后缀索引的一种特殊实现，它通过对日语拼音字母进行音序处理使得对日语拼音的情况敏感的查询及排序变得容易。