在涉及nvarchar列的多个LIKE操作的SQL查询中,我面临着性能问题.该查询的目的是在三个表(除了只有三个可搜索列的表A之外)的几乎每一列中搜索子字符串,从Extra One到Extra Eight. 如果在这些树表中的任何一个中找到匹配,我需要从表A(C-&>;B--&>A)中检索记录. 表 struct

Table A
- Id
- ExtraOne
...
- ExtraThree

Table B
- Id
- ExtraOne
...
- ExtraEight
- A_Id (FK to table A)


Table C
- Id
- ExtraOne
...
- ExtraEight
- B_Id (FK to table B)

目前,这是我正在使用的查询:

SELECT [t1].[id]
FROM
(
    SELECT DISTINCT [t0].[id]
    FROM
    (
        SELECT [b].[id]
        FROM [Table A] AS [b]
        WHERE
        (
            (
                ([b].[id] LIKE '%searchText%')
                OR ([b].[extraone] LIKE '%searchText%')
            )
            OR ([b].[extratwo] LIKE '%searchText%')
        )
        UNION
        SELECT [b0].[id]
        FROM [Table A] AS [b0]
        INNER JOIN [Table B] AS [c] ON [b0].[id] = [c].[A_Id]
        WHERE
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        [c].[id] LIKE '%searchText%'
                                    )
                                    OR ([c].[name] LIKE '%searchText%')
                                )
                                OR ([c].[extraone] LIKE '%searchText%')
                            )
                            OR ([c].[extratwo] LIKE '%searchText%')
                        )
                        OR ([c].[extrathree] LIKE '%searchText%')
                    )
                    OR ([c].[extrafour] LIKE '%searchText%')
                )
                OR ([c].[extrafive] LIKE '%searchText%')
            )
            OR ([c].[extrasix] LIKE '%searchText%')
        )
        UNION
        SELECT [b1].[id]
        FROM [Table A] AS [b1]
        INNER JOIN [Table B] AS [c0] ON [b1].[id] = [c0].[A_id]
        INNER JOIN [Table C] AS [d] ON [c0].[id] = [d].[B_id]
        WHERE
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        [d].[id] LIKE '%searchText%'
                                    )
                                    OR ([d].[name] LIKE '%searchText%')
                                )
                                OR ([d].[extraone] LIKE '%searchText%')
                            )
                            OR ([d].[extratwo] LIKE '%searchText%')
                        )
                        OR ([d].[extrathree] LIKE '%searchText%')
                    )
                    OR ([d].[extrafour] LIKE '%searchText%')
                )
                OR ([d].[extrafive] LIKE '%searchText%')
            )
            OR ([d].[extrasix] LIKE '%searchText%')
        )
    ) AS [t0]
) AS [t1]
ORDER BY [t1].[id]
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY

我希望优化此查询以获得更好的性能.以下是我有几个问题:

  • 是否有其他技术或优化可以应用来提高查询中类似操作的性能?

  • 我应该考虑使用全文搜索而不是LIKE来搜索nvarchar列中的子字符串吗?

  • 如何确保在搜索条件所涉及的列上创建适当的索引以提高查询性能?

到目前为止,我通过使用Union而不是进行简单的左连接实现了性能提升,但仍然需要一些优化.

此外,我还注意到,当FETCH NEXT ROWS数为100时,它的速度(3倍)比LIMIT为10时快得多.

推荐答案

请使用下面这样的可读性 struct 重写:

WITH 
t0 AS
(
SELECT b.id
FROM   TableA AS b
WHERE     b.id       LIKE '%searchText%'
       OR b.extraone LIKE '%searchText%'
       OR b.extratwo LIKE '%searchText%'
UNION
SELECT b0.id
FROM   TableA AS b0
       INNER JOIN TableB AS c 
          ON b0.id = c.A_Id
WHERE  c.id LIKE '%searchText%'
       OR (c.name LIKE '%searchText%')
       OR (c.extraone LIKE '%searchText%')
       OR (c.extratwo LIKE '%searchText%')
       OR (c.extrathree LIKE '%searchText%')
       OR (c.extrafour LIKE '%searchText%')
       OR (c.extrafive LIKE '%searchText%')
       OR (c.extrasix LIKE '%searchText%')
UNION
SELECT b1.id
FROM TableA AS b1
      INNER JOIN TableB AS c0 ON b1.id = c0.A_id
      INNER JOIN TableC AS d ON c0.id = d.B_id
WHERE d.id LIKE '%searchText%'
      OR (d.name LIKE '%searchText%')
      OR (d.extraone LIKE '%searchText%')
      OR (d.extratwo LIKE '%searchText%')
      OR (d.extrathree LIKE '%searchText%')
      OR (d.extrafour LIKE '%searchText%')
      OR (d.extrafive LIKE '%searchText%')
      OR (d.extrasix LIKE '%searchText%')
)
SELECT DISTINCT t0.id
FROM   t0
ORDER  BY t0.id OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;

如果您想要性能,则不会使用classic 索引.你需要你自己的 struct 来做到这一点.

1-对于要搜索的每个列,在另一个表中将值拆分为三元组,该表引用表名、键值、三元组的第一个字符的位置为字符串值

2-在此表上创建索引

3-重写您的查询以使用这个新表

Sql相关问答推荐

如何将资源密集型自连接转换为更快的查询?

更新在两个或多个面中具有交点的面

使用来自不同深度的嵌套组的值执行计算的干净方法?

Android房间fts4匹配语法AND OR

BigQuery-当子查询不返回任何结果时,所有结果为零

SQL Server中使用min()和max()从选定的特定值id表中删除不必要的时间

基于变量的条件 WHERE 子句

如何对 jsonb 中的字段执行求和,然后使用结果过滤查询的输出

JSON对象查询SQL服务器

SQL 多个不满足的条件失败

Postgres如何在一个日历周中前进和回填值

每个ID的SQL返回可能的最低级别及其值

如何对 SQL 表中的连续时间戳进行分组?

SQL 计数和过滤查询优化

SQL Server Where 条件

如何在 SQL Server 中参数化 Select top 'n'

如何比较同一张表中的行并进行相应更新

具有 GROUP BY 和 HAVING 的 BIGQUERY SQL 代码以过滤掉观察结果

在整个递归级别上终止递归查询 - Postgres

为什么在将值插入表变量期间添加尾随空格?为什么仅对一行使用 TRIM() 函数会修剪所有行?