MySQL 数据库结果是否会因表中的列数而变慢？
我正在使用 PHP 构建一个需要大量 MySQL 数据库资源的应用程序，但我也需要它的数据非常灵活。目前有许多表具有不同列的数组（包括一些文本、长文本、整数等），将来我想扩展这些表的列数，每当新的数据组出现时必需的。
我的问题是，如果我有一个包含 10 列的表，并且将来将其扩展到 40 列，SQL 查询（通过 PHP）会显着减慢吗？
只要仅查找初始 10 列的初始小型查询不是 SELECT-all (*) 查询，我想知道是否使用了更多资源或处理，因为源表现在更大了。
另外，由于许多列现在始终保留为 NULL 值（例如，每当插入仅需要前 10 列的新条目时），数据库通常会运行得更慢或更大吗？
Using PHP, I am building an application that is MySQL database resource heavy, but I also need it's data to be very flexible. Currently there are a number of tables which have an array of different columns (including some text, longtext, int, etc), and in the future I would like to expand on the number of columns of these tables, whenever new data-groups are required.
My question is, if I have a table with, say, 10 columns, and I expand this to 40 columns in the future, would a SQL query (via PHP) be slowed down considerably?
As long as the initial, small query that is only looking up the initial 10 columns is not a SELECT-all (*) query, I would like to know if more resources or processing is used because the source table is now much larger.
Also, will the database in general run slower or be much larger due to many columns now constantly remaining as NULL values (eg, whenever a new entry that only requires the first 10 columns is inserted)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
由于各种原因，MyISAM 和 InnoDB 在这方面的表现有所不同。
例如，InnoDB 会为磁盘上的每一列分区磁盘空间，无论其中是否有数据，而 MyISAM 会压缩磁盘上的表。在存在大量空列的情况下，InnoDB将浪费大量空间。另一方面，InnoDB 执行行级锁定，这意味着（有注意事项）对同一个表的并发读/写将执行得更好（MyISAM 在写入时执行表级锁定）。
一般来说，在一个表中包含许多列可能不是一个好主意，特别是出于波动性原因。例如，在 InnoDB 中（也可能是 MyISAM？），在一个列的中间重新排列列或更改列的类型（即
varchar 128->varchar 255）表要求将右侧列中的所有数据在磁盘上移动，以便为更改的列腾出（或删除）空间。就您的整体数据库设计而言，最好的目标是尽可能多的列为
not null，这样可以节省空间（您不需要列上的 null 标志，而且您也不需要t 存储空数据）并且还提高了查询和索引性能。如果许多记录将特定列设置为空，您可能应该将其移动到外键关系并使用 JOIN。这样，磁盘空间和索引开销只会发生在实际保存信息的记录上。MyISAM and InnoDB behave differently in this regard, for various reasons.
For instance, InnoDB will partition disk space for each column on disk regardless of whether it has data in it, while MyISAM will compress the tables on disk. In a case where there are large amounts of empty columns, InnoDB will be wasting a lot of space. On the other hand, InnoDB does row-level locking, which means that (with caveats) concurrent read / writes to the same table will perform better (MyISAM does a table-level lock on write).
Generally speaking, it's probably not a good idea to have many columns in one table, particularly for volatility reasons. For instance, in InnoDB (possibly MyISAM also?), re-arranging columns or changing types of columns (i.e.
varchar 128->varchar 255) in the middle of a table requires that all data in columns to the right be moved around on disk to make (or remove) space for the altered column.With respect to your overall database design, it's best to aim for as many columns as possible to be
not null, which saves space (you don't need the null flag on the column, and you don't store empty data) and also increases query and index performance. If many records will have a particular column set to null, you should probably move it to a foreign key relationship and use a JOIN. That way disk space and index overhead is only incurred for records that are actually holding information.最好的解决方案可能是创建一个包含附加字段的新表，并在必要时
JOIN这些表。原始表保持不变，保持速度，但您仍然可以访问额外的字段。Likely, the best solution would be to create a new table with the additional fields and
JOINthe tables when necessary. The original table remains unchanged, keeping it's speed, but you can still get to the extra fields.优化不是一个琐碎的问题。没有什么可以预测的。
一般来说，简短的答案是：是的，它会更慢（因为 DBMS 至少需要从磁盘读取并发送更多数据，显然）。
但是，这很大程度上取决于每个特定情况，它会慢多少。您甚至可能看不到差异，或者速度慢 10 倍。
Optimization is not a trivia question. Nothing can be predicted.
In general short answer is: yes, it will be slower (because DBMS at least need to read from the disk and send more data, obviously).
But, it is very dependent on each particular case how much slower it will be. You can either even don't see the difference, or get it 10x times slower.
很可能，不，它不会显着减慢。
然而，一个更好的问题是：哪种添加更多字段的方法会产生更优雅、更容易理解、更可维护、更具成本效益的解决方案？
通常答案是“视情况而定”。这取决于数据的访问方式、需求的变化方式、数据的更新方式以及表的增长速度。
In all likelihood, no it won't be slowed down considerably.
However, a better question to ask is: Which method of adding more fields results in a more elegant, understandable, maintainable, cost effective solution?
Usually the answer is "It depends." It depends on how the data is accessed, how the requirements will change, how the data is updated, and how fast the tables grow.
您可以将一个主表分成多个 TRANSACTION 表，这样您将获得比现在更快的结果。并在所有事务和主表中将主键设置为UNIQUE KEY。它确实可以帮助您更快地进行查询。
谢谢。
you can divide one master table into multiple TRANSACTION tables so you will get much faster result than you getting now. and also make the primary key as UNIQUE KEY also in all the transaction as well as master tables. its really help you to make your query faster.
Thanks.