sql - 按照 DTA 的建议添加 NONCLUSTERED INDEX 会提高性能吗？

Question

我在数据库中定义了以下表

铺表

• 店铺编号
• 店名
• 所有者
• 横幅
• 标题
• 城市编号
• ShopImageId
• 活跃

城市表

• 城市编号
• 城市名
• 国家/地区
• 地区编号

国家/地区表

• 国家/地区
• 国家名称
• 地区编号

地区

• 地区编号
• 地区名

商店图片

• 身份证
• 图片
• 商店编号

这是我的选择查询

SELECT ShopName, Owner, CityName, CountryName,RegionName
FROM Shop S 
INNER JOIN City CT ON CT.CityId=S.CityId
INNER JOIN Country CO ON CO.CountryId=CT.CountryId
INNER JOIN Region R ON CT.RegionId=R.RegionId
LEFT OUTER JOIN ShopImages SI ON S.ShopImageId=SI.Id

WHERE S.Banner like '%restaurant%' OR S.Description like '%restaurant%'
AND S.CityId=10 AND S.Active=1

As of now city table has around 3,000,000 records & Shop has 40,000,000 + records.

获取记录需要时间。所有的聚簇索引(主键)都已经定义好了。

I am trying to optimize with the help of DTA(Database Tuning Advisor). It suggest me to add the below index

CREATE NONCLUSTERED INDEX 
  [_dta_index_CITY_9_2098106515__K9_K20_K1_K2] ON [dbo].[CITY] 
(
    [COUNTRYID] ASC,
    [REGIONID] ASC,
    [CITYID] ASC,
    [CITYNAME] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) 
  ON [PRIMARY]

是否值得添加此索引？我可以采纳 DTA 的所有建议吗？它还建议添加一些统计信息。

如何更好地改进我的上述查询？

Answer 1

在分析索引时很难说 DTA 是错误的，因为我不知道数据分布而且确实如此，但是我添加的第一个索引超出了主键是SHOP.CityID 和 SHOP.Active 上的(可能是复合的)索引。

未经测试我不能给你任何绝对的信息，但这就是推理。

由于您基本上是在 SHOP 上进行过滤，并且没有在任何其他表上进行过滤，因此查询的繁重工作很可能是在 SHOP< 中过滤 5000 万行。

如果数据库从任何其他表开始连接，未过滤的连接将导致针对 CITY 的 3M 行，并且从过滤 SHOP 开始很可能会导致相当大的结果少一点。编译器有充分的理由喜欢“更少”。

这是SHOP上的过滤器；

WHERE S.Banner like '%restaurant%' OR S.Description like '%restaurant%'
  AND S.CityId=10 AND S.Active=1

由于 LIKE 以 % 开头的查询基本上根本无法使用索引，因此您需要尽可能窄且快速的过滤由 S.CityId=10 AND S.Active=1 完成。如果您对这些进行索引，则其他两个条件将不需要扫描使用索引找到的多于几行，而不是扫描 - 可能 - 5000 万行。

我认为建议的索引会产生很大影响的唯一原因是如果 CITY 表有大量字段，索引将允许数据库读取较少的数据从磁盘到达字段。不是说是这样，而是只有尝试才能确定。