关于插入和增益增益的索引问题数据库中的更新
我有一个问题，关于每个月大小稳定增长的表的索引增益和使用索引的查询增益之间的界限。
情况是，我有两个表，Table1 和 Table2。每个表每月都会缓慢但有规律地增长（Table1 大约有 100 个新行，Table2 大约有几行）。
我的具体问题是是否有一个索引或删除它。我进行了一些测量，发现 Table2 上的覆盖索引改进了我的 SELECT 查询，并且改进了一些查询，但同样，我必须考虑利弊，但很难做出决定。
对于Table1，可能不需要有索引，因为那里的 SELECT 查询并不常见。
我将不胜感激任何建议、提示或只是关于什么是好的解决方案的好建议。 顺便说一下，我使用 IBM DB2 版本 9.7 作为我的数据库系统
此致
Mestika
I’m having a question about the fine line between the gain of an index to a table there is growing steadily in size every month and the gain of queries with an index.
The situation is, that I’ve two tables, Table1 and Table2. Each table grows slowly but regularly each month (with about 100 new rows for Table1 and a couple of rows for Table2).
My concrete question is whether to have an index or to drop it. I’ve made some measurement that an covering index on Table2 improve my SELECT queries and some rather much but again, I’ve to consider the pros and cons but having a really hard time to decide.
For Table1 it might not be necessary to have an index because the SELECT queries there is not that common.
I would appreciate any suggestion, tips or just good advice to what is a good solution.
By the way, I’m using IBM DB2 version 9.7 as my Database system
Sincerely
Mestika
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
任何额外的索引都会使您的插入速度变慢，而查询速度更快。
要做出明智的决定，您必须准确衡量您期望看到的数据量。如果您有多个客户端同时访问数据库，那么编写一个小型多线程应用程序来模拟插入和查询的最大负载可能是有意义的。
您的结果将取决于数据的性质以及您正在运行的硬件。如果您想知道适合您的用例的最佳答案，就无法使用您的数据和硬件进行准确的测试。
然后您必须问自己：
我需要哪种查询性能？
如果无论如何没有索引查询性能也足够好，很简单：不要添加索引！
我需要哪种插入性能？
通过附加索引，它可以降到所需的限制以下吗？如果没有，很简单：添加索引！
如果您发现您绝对需要索引来提高查询性能，并且无法通过索引获得所需的插入性能，则可能需要购买更好的硬件。固态磁盘可以为数据库服务器创造奇迹，而且它们的价格也越来越便宜。
如果你的系统对每个人来说都运行良好，那就不用担心，让它按原样运行。
Any additional index will make your inserts slower and your queries faster.
To take a smart decision, you will have to measure exactly by how much, with the amount of data that you expect to see. If you have multiple clients accessing the database at the same time, it may make sense to write a small multithreaded application that simulates the maximum load, both for inserts and for queries.
Your results will depend on the nature of your data and on the hardware that you are running. If you want to know the best answer for your usecase, there is no way around testin accurately yourself with your data and your hardware.
Then you will have to ask yourself:
Which query performance do I need?
If the query performance is good enough without the index anyway, easy: Don't add the index!
Which insert performance do I need?
Can it drop below the needed limit with the additional index? If not, easy: Add the index!
If you discover that you absolutely need the index for query performance and you can't get the required insert performance with the index, you may need to buy better hardware. Solid state discs can do wonders for database servers and they are getting affordable.
If your system is running fine for everyone anyway, worry less, let it run as is.