由于 40 个布尔列导致速度显着减慢？
我正在尝试将音乐风格列添加到事件表中，根据我收集的信息，这可以通过为每种音乐风格添加一列或通过执行我不想要的多对多表关系来完成因为我希望每个事件在表中只返回一次。您是否认为连续有那么多布尔列，我会面临相当大的数据库速度减慢吗？ （数据仅由用户读取）。 谢谢 ：）
I'm trying to add musical style columns to an event table and from what I've gathered this can be either done by adding a column for each musical style or by doing a many-to-many table relation which I don't want because I want each event returned only once in a table. Do you think that having that many boolean columns in a row, that I would face considerable database slowdown? (Data will only be read by the users).
Thank you :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这些列本身不会减慢数据库速度，但请记住，为每种音乐风格添加布尔列是非常糟糕的设计。随着时间的推移，应用程序中可能的音乐风格可能会发生变化：也许必须添加新的音乐风格，必须删除多余或无用的音乐风格，等等。根据您提出的设计，您必须修改数据库结构才能将新列添加到表中。这通常是痛苦的、容易出错的，而且您还必须检查所有查询以确保它们不会因为新结构而中断。
您应该设计数据库模式，使其足够灵活，以允许应用程序内容随时间变化。例如，您可以有一个主表，其中每种音乐风格占一行，定义其 ID 及其名称、描述等。然后，一个关系表，其中包含实体（事件，如果我正确理解您的问题）和来自主桌的音乐风格。您可以通过放置外键来强制一致性，以确保数据始终干净（例如，您不能引用主表中没有的音乐风格）。这样，您可以修改音乐风格，而无需触及数据库结构中的任何内容。
阅读一些数据库规范化会对您有很大帮助；您不必一路走来拥有一个完全规范化的数据库，但了解背后的原理将使您能够设计高效且干净的数据库结构。
The columns will not slow down the database per se, but keep in mind that adding a boolean column for every music style is very poor design. Over time, the possible musical styles in your application are likely to change: maybe new ones must be added, redundant or useless ones must be removed, whatever. With your proposed design, you'd have to modify your database structure to add the new columns to the table. This is usually painful, error-prone, and you'll also have to go over all your queries to make sure they don't break because of the new structure.
You should design your database schema so that it's flexible enough to allow for variance over time in the contents of your application. For example, you could have a master table with one row for every musical style, defining its ID and its name, description etc. Then, a relationships table that contains the relationship between an entity (event, if I understood your question correctly) and a music style from the master table. You enforce consistency by putting foreign keys in place, to ensure data is always clean (eg. you cannot reference a music style that is not in the master table). This way ,you can modify the music styles without touching anything in the database structure.
Reading a bit on database normalization will help you a lot; you don't have to go all the way to have a fully normalized database, but understanding the principles behind will allow you to design efficient and clean database structures.
可能的答案是否
在一行中拥有多个布尔列不会显着降低数据库性能；假设您的索引设置正确。
编辑：话虽这么说，分配一个详细信息表并
JOIN来获取这些数据可能是最佳选择......但你说你不想这样做。我假设您想要做一些事情，例如拥有一个事件行，其中包含一堆“isCountry”、“isMetal”、“isPunk”等列，并且您将查询标记为
或类似内容的所有事件。
这种设计的缺点是，要添加/删除音乐风格，您需要更改数据库架构。
另一种选择是带有
ID和Name的TBLMusicalStyles，然后是仅包含的和TBLEventStylesEventIDStyleID然后您可以加入它们并只需在样式表中搜索...并且添加和删除样式将相对简单。
The likely answer is no
Having multiple boolean columns in a row should not significantly slow down DB performance; assuming your indices are set up appropriately.
EDIT: That being said it may be optimal to assign a details table and
JOINto it to get this data... but you said you didn't want to do that.I assume you want to do something like have an event row with a bunch of columns like "isCountry", "isMetal", "isPunk" and you'll query all events marked as
or something like that.
The weakness of this design is that to add/remove musical styles you need to change your DB schema.
An Alternative is a
TBLMusicalStyleswith anIDand aName, then aTBLEventStyleswhich will just containsEventIDandStyleIDThen you could join them and just search on the styles table... and adding and removing styles would be relatively simple.
不涉及音乐风格的请求的执行不会受到影响。
如果您的列被正确索引，那么涉及查找与客户端提供的音乐风格相匹配的行的请求实际上应该更快。
然而，所有其他涉及音乐风格的请求都会明显变慢，而且也更难编写。例如，“获取与当前行至少共享一种样式的所有行”将是一个更难编写和执行的请求。
The performance of requests that do not involve musical styles will not be affected.
If your columns are properly indexed, then requests that involve finding lines that match client-provided musical styles should actually be faster.
However, all other requests that involve musical styles will be significantly slower, and also harder to write. For instance, "get all lines that share at least one style with the current line" would be a much harder request to write and execute.