小桌子上没有索引？ [英] No indexes on small tables?

问题描述

我们应该忘记小的效率，大约97％的时间说：过早的优化是所有邪恶的根源。 （唐纳德克努特）我的SQL表不太可能包含超过几千行（而且那些是大的！）。 SQL Server数据库引擎优化顾问将数据量视为无关紧要。所以我甚至不应该考虑在这些表上放置显式索引。正确？

"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil." (Donald Knuth). My SQL tables are unlikely to contain more than a few thousand rows each (and those are the big ones!). SQL Server Database Engine Tuning Advisor dismisses the amount of data as irrelevant. So I shouldn't even think about putting explicit indexes on these tables. Correct?

推荐答案

索引的值在于超速读取。例如，如果您根据日期列中的一系列日期执行大量SELECT，则在该列上放置索引是有意义的。当然，通常您可以在任何重要频率上加入任何列上的索引。效率增益还与典型记录集的大小与记录数量的比率有关（即，从索引中获取20/2000记录的好处多于获取90/100记录的好处）。对未索引列的查找本质上是线性搜索。

The value of indexes is in speeding reads. For instance, if you are doing lots of SELECTs based on a range of dates in a date column, it makes sense to put an index on that column. And of course, generally you add indexes on any column you're going to be JOINing on with any significant frequency. The efficiency gain is also related to the ratio of the size of your typical recordsets to the number of records (i.e. grabbing 20/2000 records benefits more from indexing than grabbing 90/100 records). A lookup on an unindexed column is essentially a linear search.

索引的成本来自写入，因为每个INSERT还需要对每个列索引进行内部插入。

The cost of indexes comes on writes, because every INSERT also requires an internal insert to each column index.

因此，答案完全取决于您的应用程序 - 如果它类似于动态网站，其中读取次数可以是写入的100倍或1000倍，并且您经常进行，基于数据列的不同查找，索引可能是有益的。但是如果写入数量大大超过读取次数，那么您的调优应该集中在加速这些查询。

So, the answer depends entirely on your application -- if it's something like a dynamic website where the number of reads can be 100x or 1000x the writes, and you're doing frequent, disparate lookups based on data columns, indexing may well be beneficial. But if writes greatly outnumber reads, then your tuning should focus on speeding those queries.

确定和评估少数应用程序最常用的操作所需的时间非常短在JOIN / WHERE列上有和没有索引，我建议你这样做。监控生产应用程序并识别最昂贵，最常见的查询，并将优化工作集中在这两组查询的交集上（这可能意味着索引或完全不同的内容，例如分配更多或更少的内存，这也很聪明）查询或加入缓存）。

It takes very little time to identify and benchmark a handful of your app's most frequent operations both with and without indexes on the JOIN/WHERE columns, I suggest you do that. It's also smart to monitor your production app and identify the most expensive, and most frequent queries, and focus your optimization efforts on the intersection of those two sets of queries (which could mean indexes or something totally different, like allocating more or less memory for query or join caches).

这篇关于小桌子上没有索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！