数据库索引:是好事还是坏事还是浪费时间?
问题说明
这里经常建议添加索引，以解决性能问题.
Adding indexes is often suggested here as a remedy for performance problems.
(我只在说阅读和查询，我们都知道索引会使写入变慢).
(I'm talking about reading & querying ONLY, we all know indexes can make writing slower).
多年来，我在DB2和MSSQL上都尝试了很多方法，结果总是令人失望.
I have tried this remedy many times, over many years, both on DB2 and MSSQL, and the result were invariably disappointing.
我的发现是，无论索引有多明显"，它都会使事情变得更好.事实证明，查询优化器更智能，而我明智地选择的索引几乎总是使事情变得更糟.
My finding has been that no matter how 'obvious' it was that an index would make things better, it turned out that the query optimiser was smarter, and my cleverly-chosen index almost always made things worse.
我应该指出，我的经验主要与小表(<100'000行)有关.
I should point out that my experiences relate mostly to small tables (<100'000 rows).
任何人都可以提供一些有关索引选择的实用指南吗?
Can anyone provide some down-to-earth guidelines on choices for indexing?
正确的答案应该是一系列建议，例如:
The correct answer would be a list of recommendations something like:
- 从不/总是索引少于/多于NNNN记录的表
- 从不/始终考虑使用多字段键上的索引
- 从不/始终使用聚集索引
- 在单个表上永远不会/永远不会使用超过NNN索引
- [我想学习的某些魔术条件]永远不会/总是添加索引
理想情况下，答案将提供一些启发性的例子.
Ideally, the answer will give some instructive examples.
正确答案
索引有点像化疗...太多了，它杀死了您...太少了，您就死了...以错误的方式做了，您死.您必须知道使它不会杀死您的数量，频率和种类.
Indexes are kind of like chemotherapy...too much and it kills you...too little and you die...do it the wrong way and you die. You gotta know just how much, how often, and what kind to make it not kill you.
您的硬件，平台，环境，负载均起作用.因此，回答您的问题.
Your hardware, platform, environment, load all play a role. So to answer your questions..
是的，也许有时.
这篇好文章是转载于：学新通技术网
