由于有40个布尔列，导致速度显着下降吗? [英] Considerable slowdown due to 40 boolean columns?

问题描述

我正在尝试将音乐风格的列添加到事件表中，根据我的收集，可以通过为每种音乐风格添加一个列或通过执行多对多表关系来完成不需要，因为我希望每个事件在表中仅返回一次.您是否认为连续拥有如此多的布尔列，我将面临相当大的数据库速度下降? (数据将仅由用户读取). 谢谢:)

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 :)

推荐答案

这些列本身不会降低数据库的速度，但是请记住，为每种音乐风格添加一个布尔列是很糟糕的设计.随着时间的流逝，您的应用程序中可能的音乐风格可能会发生变化:也许必须添加新的音乐风格，或删除多余的或无用的音乐风格，无论如何.使用建议的设计，您必须修改数据库结构才能将新列添加到表中.这通常很痛苦，容易出错，并且您还必须仔细检查所有查询，以确保它们不会因为新结构而中断.

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.

您应该设计数据库架构，使其足够灵活，以允许应用程序内容随时间变化.例如，您可能有一个主表，每种音乐风格都有一行，定义了它的ID及其名称，描述等.然后，一个关系表包含一个实体(事件，如果我正确理解了您的问题)与主表中的音乐风格.您可以通过放置外键来确保一致性，以确保数据始终干净(例如，您不能引用主表中没有的音乐风格).这样，您无需修改​​数据库结构中的任何内容即可修改音乐风格.

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.

这篇关于由于有40个布尔列，导致速度显着下降吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！