blocks|key|5288402|text|比你需要的多一个索引就是太多了。少一个就太少了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5288403|我试着去找一个有太多索引是个问题，却什么都找不到的例子|blockquote|5288404|您知道，如果插入太慢，那么就会有太多的插入，而用于读取的索引没有足够快的速度来弥补它。|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|I|8|@]|9|@]|A|$]]|$1|E|3|F|5|6|7|J|8|@]|9|@]|A|$]]]|G|$]]

One more index than you need is too many. One less is too little.

<blockquote>
 I've tried searching for a case where having too many indexes was a
 problem and couldn't really find anything
</blockquote>

You KNOW you have too many if your inserts are too slow, and the index used for reading are not speeding things up enough to make up for it.

blocks|key|5288593|text|没有“神奇的数字”。0之后的每个索引都会稍微减慢insert/更新，但这不应该阻止您创建所需的索引。37确实听起来很多，确保你没有使用不必要的索引。例如，如果你在A，B列上有一个索引，你就不需要在A上有一个单独的索引，或者如果你对A，B，C，D有一个索引，那么你就不需要A，B，C，C上的另一个索引，而且如果在A，B，C，E上有另一个索引，你可能不会获得太多的收益。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|entityMap^0^^$0|@$1|2|3|4|5|6|7|C|8|@]|9|@]|A|$]]]|B|$]]

there is no 'magic number'. Every index after 0 slows down insert/updates a bit, but that shouldn't stop you from creating needed indexes. 37 does sound like a lot, make sure you're not using unnecessary indexes. For example, usually if you have an index on columns A, B you don't need a separate index on A. Or if you have an index on A, B, C, D you don't need another index on A, B, C. And you probably don't gain much by having another index on A, B, C, E.

blocks|key|1189817|text|创建索引是一种权衡。您必须确定维护索引的开销(从而减缓DML操作)和索引消耗的额外空间是否值得从使用索引的查询中获得性能增益。这又取决于查询的好处和这些查询的重要性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1189818|在这个成本效益方程中，表上的索引数与此无关--每个索引都应该单独考虑(尽管特定索引的性能效益可能取决于查询是否可以使用不同的索引)。|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|F|8|@]|9|@]|A|$]]]|D|$]]

Creating an index is a trade-off. You have to determine whether the overhead of maintaining the index (and thus slowing down DML operations) and the additional space consumed by the index is worth the performance gain you get from the queries that use the index. That, in turn, depends on how much the queries benefit and how critical those queries are.

The number of indexes on a table is irrelevent in this cost-benefit equation-- each index ought to be considered on its own (though the performance benefit of a particular index may be dependent on whether a different index is available for the query to use instead).

blocks|key|1189913|text|由于您有4700万行，并且您正在使用数据库进行报告，所以我们讨论的是数据集市/仓库类型的应用程序，而不是OLTP应用程序。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1189914|您需要考虑首先使用星型架构，并且可以(基于所选数据库中的特性)构建连接到维度表的静态聚合表，以便使用批处理提供摘要。以上将为您提供更好的性能和可能更少的索引。通过在加载期间关闭(一些)索引并在加载完成后重新构建索引，可以获得更高的性能。构建一个超过4700万行的索引非常快。|1189915|最重要的是，您需要证明每个索引的需要是一个通用的数据库设计实践。这只能通过详细检查用户报告需求来完成。请记住，即使您定义了索引，您的查询也可能不会使用其中的一个或多个索引，因此您的用户需要、查询分析和良好的数据库设计应该帮助您准确地确定是否使用索引和所需的索引类型。|1189916|我设计了几个数据库和数据集市，但老实说，每个表从未见过这么多的索引。|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|J|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|K|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|L|8|@]|9|@]|A|$]]]|H|$]]

Since you have 47 Million rows and your are using the database for reporting, then we are talking about a Data Mart/Warehouse type of application rather than OLTP application.

You need to consider using a Star Schema to start with and you may (based on the features in your choosen database) to build static aggregate tables connected to dimension tables to provide summaries using batch processing. The above will provide you better performance and possibly less indexes. You can gain further performance by shutting off (some) of your indexes during load and re-building them after load is complete. Building an index over 47 million rows is very fast. 

Above all, you need to justify the need of every index as a general database design practice. This can only be done by examining, in details, user reporting needs. Remember that even if you define indexes, your query may not use one or more of them, so your user needs, query analysis and good database design should help you determine exactly whether to use an index or not and the type of index required.

I have designed several database and data marts, but honestly, never seen that number of indexes per table.

blocks|key|1189857|text|通常，您必须评估您的写入速度、/maintenance需求来读取需求。如果你有几乎100%25的读/写比率，并且你很少更新数据，或者你甚至产生你的数据库的“只读快照”并使用它来进行读缩放，在索引前面开火！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1189858|我会在像系统这样的产品上对我的所有场景进行非常彻底的测试，不过，我听说过索引添加会大大减缓查询编译/更新的速度。|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|F|8|@]|9|@]|A|$]]]|D|$]]

Always you have to evaluate your write speed /maintenance requirements to read requirements. If you have almost a 100% read ratio to write ratio, and you update the data very infrequently or you even produce "read-only snapshots" of your database and use that for read scaling, fire away on the index front!

I would test all my scenarios very thoroughly on a production like system though, I have heard of scenarios where index adds have slowed down query compilation / updates considerably.

blocks|key|5288509|text|我看到的大多数东西都说，拥有超过5个索引将开始减慢插入速度。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5288510|请考虑这一点，您所拥有的每个索引就像在数据库中有一个新的表，其中包含相同的信息，只是排序方式不同。有37个索引就像要做38次插入一样！|5288511|任何规则都不会是一个坚实的数字，但除非这是某种永远不会改变的历史记录，否则这可能会成为许多人的选择。|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|H|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|I|8|@]|9|@]|A|$]]]|F|$]]

Most things that I have seen say that having more than 5 indexes will begin to slow down your insert speed. 

Consider this, every index you have is like having a new table in your database with the same information just sorted a different way. Having 37 indexes is like having to do an insert 38 times!

No rule is going to be a solid number but unless this is some sort of historical record that will never change that may be to many.

I have a table with about 37 indexes. Would that be considered too many? What are some best practices regarding indexing?

EDIT: It is warehousing data, updated daily with new transactions. There are 47 million rows. 3 of the indexes are very necessary. The other 34~ are 'where' clause indexes intended to speed up queries.

How many indexes is too many indexes?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我有一张约有37种索引的桌子。那会不会被认为太多了？关于索引的一些最佳实践是什么？编辑:它是仓储数据，每天更新与新的交易。有4700万行。其中3项指标是非常必要的。其他34~是用于加速查询的“where”子句索引。

问多少个索引是太多的索引？
EN

回答 6

Software Engineering用户

Software Engineering用户

Software Engineering用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问多少个索引是太多的索引？EN

回答 6

Software Engineering用户

Software Engineering用户

Software Engineering用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问多少个索引是太多的索引？
EN