nvarchar列上的索引不会影响性能 [英] Index on nvarchar column doesnot affect performance

问题描述

您好，



我的表中有一列有nvarchar（200）数据类型



然后我在另一列上添加索引包括我的nvarchar（200）列



但是当我在nvarchar中使用where条件执行select查询时，性能是相同的（ 200）栏目



我需要知道为什么nvarchar列上的索引不会影响性能？



我尝试过：

Hello,

I have a column in table that have nvarchar(200) datatype

and then I add index on another column include my nvarchar(200) column

but the performance is the same when I do a select query with where condition in the nvarchar(200) column

I need to know why the index on nvarchar column doesn't affect the performance?

What I have tried:

CREATE TABLE dbo.POS_JSON_Transaction_2000
(
  Id INT IDENTITY PRIMARY KEY,
  [Name] Nvarchar(50),
  [Transaction] nvarchar(2000) 
);

CREATE INDEX POS_JSON_Transaction_2000_Transaction_Index ON 
POS_JSON_Transaction_2000(Name) include ([Transaction]);

select * from POS_JSON_Transaction_2000 where [Transaction] like '%"NAME__STRING":"testKA"%'

推荐答案

有一个co这里出现问题。



首先，索引位于名称列上。它可以用于满足 Name 列上的查询，但对 Transaction 列上的查询没有用处。



想象一下按姓氏排序的地址簿。您已根据名字创建了二级索引，并在该索引中包含了该城镇。您现在想要查找特定城镇中的所有条目。二级索引没有帮助，因为你仍然需要通过每个条目来找到匹配的索引。



另一个问题是你正在使用一个包含查询 - WHERE列LIKE'％...％'。索引没有合理的方法来帮助查询。再次，给出一个按字母顺序排列的城镇列表，如果你想找到包含字母p的任何城镇，你仍然需要通过每个条目才能找到它。



如果您正在执行以...开头查询 - WHERE列LIKE'...％' - 那么索引可以帮助。



即使是以...结尾查询 - WHERE列LIKE'％...' - 它可以使用一个索引。



但是对于包含查询，除了用你的每个子字符串创建一个单独的表之外，你做的不多了专栏。



Sargability：为什么％string％慢 - Brent OzarUnlimited® [ ^ ]

获取索引的一种方法是在SQL Server中寻找领先的％通配符 [ ^ ]

There are a couple of problems here.

Firstly, the index is on the Name column. It can be used to satisfy queries on the Name column, but will be of no use for queries on the Transaction column.

Think of it an address book which is sorted by last name. You have created a secondary index based on the first name, and included the town in that index. You now want to find all entries in a specific town. The secondary index wouldn't help, since you'd still have to go through every entry to find the matching ones.

The other problem is that you're using a "contains" query - WHERE column LIKE '% ... %'. There is no sensible way for an index to help with that query. Again, given a list of towns in alphabetical order, if you want to find any town which contains the letter "p", you still have to go through every entry to find it.

If you were performing a "starts with" query - WHERE column LIKE '... %' - then an index could help.

Even for an "ends with" query - WHERE column LIKE '% ...' - it would be possible to use an index.

But for a "contains" query, there's not much you can do, short of creating a separate table with every substring from your column.

Sargability: Why %string% Is Slow - Brent Ozar Unlimited®[^]
One way to get an index seek for a leading %wildcard in SQL Server[^]

这篇关于nvarchar列上的索引不会影响性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！