Question

让我们就此坐下来。

[KEY] [int] NOT NULL,
[INT1] [int] NULL,
[INT2] [int] NULL,
[INT3] [int] NULL,
[STR1] [varchar](20) NULL,
[STR2] [varchar](20) NULL,
[STR3] [varchar](20) NULL,

The query is very flexed but always like this format: SELECT KEY FROM [TABLE] WHERE...

单列的搜索条件很少,几栏的搜索时间多,[int], 编号或>=或<=,varchar/code>, 始终如>/code>或。与<代码>AND有关的所有条件



由于查询并非总是固定在同一栏目上,所以我想知道,如果我在每个栏目上建立<>INDEX<>/code>,就会提高性能,或者仅仅增加废物。

Answer 1

捐款只是每个单列一个指数——总浪费时间和资源!

基本上,我的做法是:

a goodfirst andgrouping key on any “normal” table (the list for e.g. 查询表等除外) - that s a greater step have been used.
在任何外国关键栏目上设置了无节制指数,这些栏目确实有助于抽签,特别是JOIN。

而这样说!

<><>>

observe your system - see when things are slow
measure system performance
capture a server-side trace to get a representative workload
analyse that workload, and see what additional indices might be helpful
do tweaks - one at a time
measure again and again to see if you ve improved system performance (or not)

你们需要全心全意、有代表性的工作,以了解哪些问题确实是常见的,并使用很多东西,并看到哪些指数会有利于这些经常询问。否则,你可能会为所有错误的问询提供指数帮助,而且你可能实际上会减缓事情......。

你们感到惊讶的是,很少没有分类的指数会真正帮助!

Don t over-index - it shen as ——如果不是更糟的话—— than with no index at all! 情况可能更糟,因为你们的每个指数也需要maintained。一、导言

见Kimberly Tripp的出色博客员额。指数:仅仅因为你可以 t,就意味着你!? 或者基本上,上被捆绑了——她是,她在博客上的任何信息通常都极为有用!

Furthermore, SQL Server 2005 and newer offer DMV s (Dynamic Management Views) that allow you to find out what indices are not being used (those can be removed) or which are missing, according to SQL Server s query optimizer s opinion. See SQL Server - Find missing and unused indexes for more details. But be aware: those are dynamic views - they re reset with each system start and might not be totally accurate - don t just do everything they tell you - take everything with a grain of salt and consider carefully what you do - document it, so that you can undo it, if things get worse rather than better!

Answer 2


如:



  表中大量指数影响到INSERT、UPDATE、DELETE和MERGE报表的绩效,因为所有指数都必须按照表格变动中的数据加以适当调整。

另外,如果你想永远检索<代码>KEY。您的询问一栏,考虑在指数中添加“cluded栏>,以便检索索引,避免查阅表格。但铭记的是,编制一个指数,包括2005年以来服务器库和后来版本提供的栏目。

您可以审查最频繁的过滤器组合,并仅增加几个多栏索引,同时铭记:

Consider the order of the columns if the index will contain multiple columns. The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

Answer 3

如果直接导致正确结果,则采用指数将有助于提问,但如果能够改善参考地点并减少读到的记忆,也会带来巨大好处。

对于所询问的问题,答案是“取决于”。这取决于您的询问。如果是一栏主要栏目,该栏总是出现在搜索中,例如INT1,则就以下方面编制索引:

 unique (INT1, INT2, INT3, REF)

Then any query referencing INT1 and any combination of the other fields will be quick.

此外,任何问询都提到INT2,但并非INt1, 也会受益,因为整个表格不需要阅读——只是指数。尽管INT2不是指数的头角,但问题仍然有好处:亚洲开发银行将超越INT1,只看INT2,但它可以了解表INT2的数值,不必阅读整个表格。

So really you need to better understand the queries which will be done. If one column ALWAYS appears, put that at the head of an index. If another column OFTEN appears, that should be number 2.

如果经常出现两栏,你可以这样做:

unique (INT1, INT2, INT3, REF),
unique (INT2, INT1, INT3, REF)

然后,我们希望,如果未具体说明INT1,但指明INT2,则使用第二个指数。

Don t use too many indexes though, they can take up a lot of disk space.

<>光线> 查阅有关指数的询问。你们需要收集10至20个最低样本查询,并测试其国际交易日志和锁定时间。这是获得真正答案的唯一途径。

友情链接