删除主键（聚集索引）以提高插入性能
我们一直在经历 SQL 超时，并已确定该瓶颈是审核表 - 我们系统中的所有表都包含插入、更新和删除触发器，这些触发器会导致新的审核记录。
这意味着审计表是系统中最大且最繁忙的表。然而数据只进入，永远不会出来（在这个系统下），所以不需要 select 性能。
运行 select top 10 返回最近插入的记录，而不是“第一个”记录。当然，order by 可以工作，但我希望选择顶部应该根据它们在光盘上的顺序返回行 - 我希望这会返回最低的 PK 值。
有人建议我们放弃聚集索引，实际上也放弃主键（唯一约束）。正如我之前提到的，无需在该系统中从该表中选择。
聚集索引会对表造成什么样的性能影响？拥有无索引、非集群、无键表会产生哪些（非选择）后果？还有其他建议吗？
编辑
我们的审计涉及 CLR 功能，我现在正在使用 & 进行基准测试。无需 PK、索引、FK 等即可确定 CLR 函数和 CLR 函数的相对成本。的限制条件。
经过调查，性能不佳与 insert 语句无关，而是与协调审核的 CLR 函数有关。删除 CLR 并使用直接的 TSQL 过程后，性能提高了 20 倍。
在测试过程中，我还确定聚集索引和标识列对插入时间几乎没有影响，至少相对于发生的任何其他处理而言是这样。
// updating 10k rows in a table with trigger
// using CLR function
PK (identity, clustered)- ~78000ms
No PK, no index - ~81000ms
// using straight TSQL
PK (identity, clustered) - 2174ms
No PK, no index - 2102ms
We've been experiencing SQL timeouts and have identified that bottleneck to be an audit table - all tables in our system contain insert, update and delete triggers which cause a new audit record.
This means that the audit table is the largest and busiest table in the system. Yet data only goes in, and never comes out (under this system) so no select performance is required.
Running a select top 10 returns recently insert records rather than the 'first' records. order by works, of course, but I would expect that a select top should return rows based on their order on the disc - which I'd expect would return the lowest PK values.
It's been suggested that we drop the clustered index, and in fact the primary key (unique constraint) as well. As I mentioned earlier there's no need to select from this table within this system.
What sort of performance hit does a clustered index create on a table? What are the (non-select) ramifications of having an unindexed, unclustered, key-less table? Any other suggestions?
edit
our auditing involves CLR functions and I am now benchmarking with & without PK, indexes, FKs etc to determine the relative cost of the CLR functions & the contraints.
After investigation, the poor performance was not related to the insert statements but instead the CLR function which orchestrated the auditing. After removing the CLR and instead using a straight TSQL proc, performance improved 20-fold.
During the testing I've also determined that the clustered index and identity columns make little or no difference to the insert time, at least relative to any other processing that takes place.
// updating 10k rows in a table with trigger
// using CLR function
PK (identity, clustered)- ~78000ms
No PK, no index - ~81000ms
// using straight TSQL
PK (identity, clustered) - 2174ms
No PK, no index - 2102ms
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
根据索引女王 Kimberly Tripp 的说法，在表上拥有聚集索引实际上有助于提高 INSERT 性能：
资料来源：名为 聚集索引争论仍在继续的博客文章... .
According to Kimberly Tripp - the Queen of Indexing - having a clustered index on a table actually helps INSERT performance:
Source: blog post called The Clustered Index Debate Continues....
Tibor Karaszi 的博客上提供了有关此场景的出色测试脚本和描述，网址为 SQLblog.com
我的数字与他的不完全匹配 - 我看到更多差异在批处理语句上比在每行语句上更容易。
当行数约为 100 万时，我相当一致地在聚集索引上执行单行插入循环，其执行速度略快于非索引索引（聚集索引的插入时间约为非索引索引的 97%）。
相反，批量插入（10000 行）到非索引而不是聚集索引（聚集插入时间的 75%-85%）更快。
他描述了每个插入中发生的情况：
A great test script and description of this scenarion is available on Tibor Karaszi's blog at SQLblog.com
My numbers don't entirely match his - I see more difference on a batch statement than I do with per-row statements.
With the row count around one million I fairly consistently get a single-row insert loop on clustered index to perform slightly faster than on a non-indexed (clustered taking approximately 97% as long as non-indexed).
Conversely the batch insert (10000 rows) is faster into a non-indexed rather than clustered index (anything from 75%-85% of the clustered insert time).
He describes what's happening on each insert:
没有钥匙的桌子？甚至没有自动递增代理键？ :(
只要键单调递增，插入时的索引维护应该很好——它只是“添加在末尾”。“聚集”只是意味着表的物理布局遵循索引（因为数据是索引的一部分）。只要索引不碎片化（参见单调递增位），那么集群本身/数据就不会在逻辑上碎片化，这应该是'不是性能问题。 （如果有更新，那么集群的情况会略有不同：更新的记录可能会“增长”并导致碎片。）
我的建议是，如果这是所选的路线，那么...对其进行基准测试现实的数据/负载，然后决定这样的建议是否合理。很高兴看到这一更改已被决定，以及为什么
快乐编码。
此外，除了
ORDER BY设计上有缺陷，现在可能可以工作，但这是一个实现细节，可能会以微妙的方式发生变化（就像不同的查询计划一样简单）。使用自动增量键，ORDER BY DESC将始终产生正确的结果（请记住，可以跳过自动增量 ID，但除非“重置”，否则它们将始终基于插入而增加命令）。A table without a key? Not even an auto-incrementing surrogate key? :(
As long as the key is monotonically increasing the index maintenance upon insert should be good -- it's just "added at the end". The "clustered" just means the physical layout of the table follows the index (as the data is part of the index). As long as the index isn't fragmented (see monotonically increasing bit) then the cluster itself/data won't be logically fragmented and this shouldn't be a performance issue. (If there are updates then the clustering is a slightly different story: the record updated may "grow" and cause fragmentation.)
My suggestion is, if that is the chosen route then ... benchmark it with realistic data/load and then decide if such suggestions are warranted. It would be nice to see is this change was decided upon, and why.
Happy coding.
Also, any reliance upon order excepting that from an
ORDER BYis flawed by design. It may work now, but it is an implementation detail and may change in subtle ways (as simple as a different query plan). With the auto-increment key, anORDER BY DESCwould always produce the correct result (bear in mind that auto-incremeent IDs can be skipped, but unless "reset" they will always be increasing based on insert order).我的原始理解是，即使使用聚簇索引进行 INSERT 操作通常也会比使用堆更快。此外，聚集索引的磁盘空间要求较低。
一些有趣的测试/场景可能会对您的特定情况有所启发：http:// technet.microsoft.com/en-us/library/cc917672.aspx。
My primitive understanding is that even INSERT operations are usually faster with a clustered index, than with a heap. Additionally, disk-space requirements are lower with clustered indexes.
Some interesting tests / scenarios that might shed some light for your particular circumstance: http://technet.microsoft.com/en-us/library/cc917672.aspx.