在 SqlServer 2017 中,我们有一个包含数百万行的表。该表有几十列。其中一列varchar(50)
具有明确定义的允许值范围,该列的值保证是大约 4,000 个字符串列表中的一个字符串。我们关心的针对此表优化的唯一查询全部专门处理仅具有这些值之一的行,但它们需要检索这些行的所有列。在该列上放置索引是否有意义,如果是,应使用哪种类型的索引?
示例架构:
+---------------------------------------+
| Id | Category | ... 20 other columns|
+------+----------+---------------------+
| 1 | Food | ..... |
+------+----------+---------------------+
| 2 | Lumber | ..... |
+------+----------+---------------------+
示例查询:select * from table where Category = 'food';
因此,在此示例中,该Category
列包含一个来自大约 4000 个列表的字符串。我考虑过聚集索引,但该列不是唯一的。我会添加一个非聚集索引,但查询要求返回所有列,所以它必须从索引返回到主表以检索所有数据,对吗?那我们是要用全表索引还是有更好的选择呢?
我们无法为您回答。这取决于在没有索引的情况下查询性能是否足够好。如果最终用户响应时间、每秒事务数、总体服务器 CPU 或任何对您来说最重要的指标在没有索引的情况下都很好,那么您现在不需要它。如果桌子变大,您将来可能需要它。综上所述,您当前的查询和表结构不会为您提供最佳性能。如果需要改进,那么您有三个主要选择:
在列上创建非聚集主键,在上创建
id
聚集索引Category
。这将为您在问题中列出的查询提供最佳性能。缺点是更改聚簇索引可能会对其他查询产生负面影响。聚簇索引不需要是唯一的。您也不需要表上的主键。我刚刚提出了一个包含两者的示例模式(我假设您今天有一个主键)。
Category
添加一个包含所有其他列的非聚集索引。这也将为您在问题中列出的查询提供最佳性能。作为一个缺点,它会使表所需的磁盘空间加倍,并且会减慢表上的 DML 操作。
Category
添加一个不包含所有其他列的非聚集索引。这不会给你最好的性能。只要您筛选的值足够有选择性并且 SQL Server 选择使用索引,它就会提高性能。如果您需要帮助优化器,Distinguished Answer Erik Darling 的这篇博文中的技术可能会有所帮助。这个选项仍然有 DML 影响,但它应该比选项 2 小得多。
最终,您可以获得满意答案的唯一方法是亲自调查。我们不知道您对 DML 性能、磁盘总使用量、其他查询的性能等重视程度如何。我上面提到的所有选项都有其自身的缺点。祝你好运!