如何决定何时在表列上使用索引 [英] How to decide when use index on table column

问题描述

我什么时候应该在桌子上使用索引？

When should I use index on table?

1. 从哪个行索引有意义？


2. 如果我有持续行的表，只编辑了来的列（不在'where'子句中），即使表只有大约15行，也会使索引有意义吗？编辑：在这种情况下，非索引选择/阅读比索引读取更有效吗？

编辑：
现在我我正在使用firebird 2.5，但大多数时候我使用的是SQL Server 2005/2008。

Now I am working with firebird 2.5, but most of the time I am using SQL Server 2005/2008.

推荐答案

一般来说，我的索引策略是这样的（我现在只使用SQL Server - 适应你的根据需要拥有自己的数据库系统）：

In general, my indexing strategy would be something like this (I'm using SQL Server exclusively for now - adapt to your own database system as needed):

• 选择良好群集密钥 - 不是GUID，不是一个 VARCHAR（250）或其他东西 - 良好群集密钥狭窄，独特，稳定，不断增加 - 像 INT IDENTITY 之类的东西是完美的。使这成为您的集群主键 - >为您提供表上的第一个索引

• pick a good clustering key - not a GUID, not a VARCHAR(250) or something - a good clustering key is narrow, unique, stable, ever-increasing - something like a INT IDENTITY is perfect. Makes this your clustered primary key -> gives you your first index on the table

对于任何用作外键的列到另一个表中 - 添加一个索引。它可以是单个列索​​引 - 或者它可能是复合索引 - 无论哪种方式最适合您的情况。重要的是外键列是该索引中的第一个列（如果您使用的是复合索引） - 否则， JOIN 或用于检查参照完整性将无法用于您的系统

for any column that is being used as a foreign key into another table - add an index. It can either be a single column index - or it might be a compound index - whatever works best for your case. It's important that the foreign key column be the first column in that index (if you're using a compound index) - otherwise, the benefits for the JOIN's or for checking referential integrity won't be available to your system

现在就是这样。

然后：运行您的系统 - 观察并测量 - 建立基线。应用程序足够快吗？如果是 - >你已经完成 - 回家享受你的业余时间。

Then: run your system - observe, and measure - establish a baseline. Is the app fast enough? If yes -> you're done - go home and enjoy your spare time.

如果没有：然后开始收集数据和指示为什么应用程序不快足够。看看例如像SQL Server中的DMV那样告诉你性能最差的查询，或者缺少索引DMV 。分析那些。看看你可以改进什么。一次添加一个索引并再次：观察，测量，与您的基线进行比较。

If not: then start collecting data and indications as to why the app isn't fast enough. Look at e.g. things like the DMV's in SQL Server that tell you about the worst performing queries, or the missing index DMV. Analyze those. See what you could improve. Add one index at a time and again: observe, measure, compare to your baseline.

如果您有改进 - >保留该索引地方和这个测量是你的新基线。冲洗并重复，直到您（和您的用户）对应用程序的性能感到满意为止（然后回家并享受休假）。

If you have improvement -> leave that index in place and this measurement is your new baseline. Rinse and repeat until you (and your users) are happy with the app's performance (and then go home and enjoy your time off).

SQL Server中的过度索引可能比没有任何索引更差。不要开始有太多的索引开始！只建立好的聚类PK和外键非聚簇索引 - 这就是全部 - 然后观察，测量，优化和重复那个循环。

Over-indexing in SQL Server can be worse than not having any indexes. Don't start out with too many indices to begin with! Only establish good clustered PK and foreign key nonclustered indices - that's all - then observe, measure, optimize & repeat that cycle.

这篇关于如何决定何时在表列上使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！