数据库索引：仅选择！
美好的一天，
我有大约 4GB 的数据，分布在大约 10 个不同的表中。 每个表都有很多列，每列都可以作为查询中的搜索条件。 我根本不是DBA，对索引也不太了解，但是我想尽可能加快搜索速度。 重要的一点是，任何时候都不会进行任何更新、插入或删除（表每 4 个月填充一次）。 为每一列创建索引是否合适？ 请记住：没有插入、更新或删除，只有选择！ 另外，如果我可以将所有这些列设为整数而不是 varchar，速度会有所不同吗？
非常感谢！
Good day,
I have about 4GB of data, separated in about 10 different tables. Each table has a lot of columns, and each column can be a search criteria in a query. I'm not a DBA at all, and I don't know much about indexes, but I want to speed up the search as much as possible. The important point is, there won't be any update, insert or delete at any moment (the tables are populated once every 4 months). Is it appropriate to create an index on each and every column? Remember: no insert, update or delete, only selects!
Also, if I can make all of these columns integer instead of varchar, would i make a difference in speed?
Thank you very much!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
答案：不。单独为每一列建立索引并不是一个好的设计。 很多情况下索引需要包含多列，针对不同的需求有不同类型的索引。
其他答案中提到的调整向导是一个很好的第一步（尤其是对于学习者而言）。
不要试图猜测你的方法，或者希望你理解复杂的分析 - 获取针对你的情况的建议。 我们似乎有几个线程在这里，对于特定情况和查询优化非常活跃。
Answer: No. Indexing every column separately is not good design. Indexes need to comprise multiple columns in many cases, and there are different types of indexes for different requirements.
The tuning wizard mentioned in other answers is a good first cut (esp. for a learner).
Don't try to guess your way through it, or hope you understand complex analyses - get advice specific to your situation. We seem to have several threads going here that are quite active for specific situations and query optimization.
您是否考虑过运行索引调整向导？ 将根据工作负载为您提供索引建议。
Have you looked at running the Index Tuning Wizard? Will give you suggestions of indexes based on a workload.
绝对不是。
您必须了解索引的工作原理。 如果你有一个表，比如 1000 条记录，但它是一个 BIT 并且可以有两个值之一，如果你只在该列上建立索引，并且只在该列上建立索引，那么它将毫无价值，因为它的选择性不够。 当您对列建立索引时，请非常清楚将在表上执行什么类型的选择。 当您在列上创建索引时，该索引的选择性是否足以让优化器有效地使用？
到那时，您可能会发现一些精心选择的复合索引将大大优于每列上许多单个索引的解决方案。 黄金法则：如何查询数据库将决定如何创建索引。
Absolutely not.
You have to understand how indexes work. If you have a table of say, 1000 records, but it's a BIT and there can be one of two values, if you index on that column and that column only, it will be worthless, because it will not be selective enough. When you index on a column, be very cognizant of what types of selects are going to be done on the table. When you create an index on a column, will that index be selective enough for the optimizer to use effectively?
To that point, you may very well find that a few carefully selected composite indexes will vastly outperform the solution of many single indexes on each column. The golden rule: how the database is queried will determine how you should make your indexes.
缺少两条信息：每列中有多少个不同的值，以及您正在使用哪个 DBMS。 如果您使用的是 Oracle 并且每列的不同值少于几千个，则可以创建位图索引。 对于精确匹配来说，它们非常节省空间和执行效率。
否则，这是一种权衡：每个索引将添加与包含相同数据的单列名称大致相同的空间量，因此您的空间需求实际上会增加一倍（可能是 2.5 倍）。 所以可能是 10G，这并不是很多数据。
接下来的问题是您的 DBMS 是否能够有效地合并多个基于索引的选择。 很可能不会，除非您对要选择的每个列进行自连接。
最佳答案：在较小的数据集上尝试（这样您就不会花费所有时间来构建索引）并查看它是如何工作的。
Two pieces of missing information: how many distinct values are in each column, and which DBMS you're using. If you're using Oracle and have less than a few thousand distinct values per column, you can create bitmap indexes. These are very space- and execution-efficient for exact matches.
Otherwise, it's a tradeoff: each index will add roughly the same amount of space as a one-column name containing the same data, so you'll essentially double (probably 2.5x) your space requirements. So maybe 10G, which isn't a whole lot of data.
Then there's the question of whether your DBMS will efficiently merge multiple index-based selects. It's quite possible that it won't, unless you do self-joins for every column that you're selecting against.
Best answer: try it on a smaller dataset (so that you're not spending all your time building the indexes) and see how it works.
如果您从表中选择的列集大于所选索引中的列所覆盖的列，那么您将不可避免地在查询计划中进行书签查找，这是查询处理器必须检索未覆盖的列的地方使用关联非聚集索引中叶行的引用 ID 从聚集索引中获取。
根据我的经验，书签查找确实会降低查询性能，因为所需的额外读取量很大，而且聚集索引中的每一行都必须单独解析。 这就是为什么我尝试使 NC 索引尽可能覆盖，这对于所需查询计划众所周知的较小表来说更容易，但如果您有包含大量列且预期有任意查询的大型表，那么这可能不会可行的。
这意味着只有在索引覆盖或选择足够小的数据集以减轻书签查找成本的情况下，您才能使用任何类型的 NC 索引获得实惠 - 事实上，您可能会发现查询优化器如果与所有列都已可用的聚集索引扫描相比，成本过高，则甚至不会查看您的索引。
因此，除非您知道索引会优化给定查询的结果，否则创建索引是没有意义的。 因此，索引的值与它可以针对给定表优化的查询的百分比成正比，而这只能通过分析正在执行的查询来确定，这正是索引优化向导为您所做的。
总之：
1）不要对每一列都建立索引。 这是典型的过早优化。 您无法提前针对所有可能的查询计划优化带有索引的大型表。
2) 在通过索引调整向导捕获并运行基本工作负载之前，不要对任何列建立索引。 此工作负载需要代表应用程序的使用模式，以便向导可以确定哪些索引实际上有助于提高查询性能。
If you are selecting a set of columns from the table greater than those covered by the columns in the selected indexes, then you will inevitably incur a bookmark lookup in the query plan, which is where the query processor has to retrieve the non-covered columns from the clustered index using the reference ID from leaf rows in the associated non-clustered index.
In my experience, bookmark lookups can really kill query performance, due to the volume of extra reads required and the fact that each row in the clustered index has to be resolved individually. This is why I try to make NC indexes covering wherever possible, which is easier on smaller tables where the required query plans are well-known, but if you have large tables with lots of columns with arbitrary queries expected then this probably won't be feasible.
This means you only get bang for your buck with an NC index of any kind, if the index is covering, or selects a small-enough data set that the cost of a bookmark lookup is mitigated - indeed, you may find that the query optimizer won't even look at your indexes if the cost is prohibitive compared to a clustered index scan, where all the columns are already available.
So there is no point in creating an index unless you know that index will optimize the result of a given query. The value of an index is therefore proportional to the percentage of queries that it can optimize for a given table, and this can only be determined by analyzing the queries that are being executed, which is exactly what the Index Tuning Wizard does for you.
so in summary:
1) Don't index every column. This is classic premature optimization. You cannot optimize a large table with indexes for all possible query plans in advance.
2) Don't index any column, until you have captured and run a base workload through the Index Tuning Wizard. This workload needs to be representative of the usage patterns of your application, so that the wizard can determine what indexes would actually help the performance of your queries.