大量列，全选将永远 [英] Large Number of columns, Select all takes forever

问题描述

我的表格中有210列，大约有10000行.每行都是唯一的，并且表上有一个主键.问题是我们总是必须选择表上的所有查询来获取所有站点的数据.

There are 210 columns in my table with around 10000 rows. Each row is unique and there is a primary key on the table. The thing is we always had to do select all query on the table to get data of all the sites.

当前，问题在于它花费了太多时间，返回的数据大约为10mb，将来还会很大.

Currently, the problem is it takes too much time and the data returned is around 10mb and it will be large in the future.

该表中包含varchar，文本和日期类型.

The table has varchar, text and date types in it.

有什么办法可以修改结构或某些东西，以使检索速度更快.更多索引或分解表. (尽管我认为非规范化数据很适合检索)

Is there any way I can modify the structure or something to make my retrieval faster. More indexing or breaking down the table. (Although I think denormalized data is good for retrieval)

更新:"为什么宽表会降低查询性能?"

谢谢..

推荐答案

为什么宽表会降低查询性能?

why do wider tables slow down the query performance?

InnoDB以不同的方式存储宽"表.它执行以下操作，而不是将所有列都放在一个字符串中(加上开销，例如长度等)，

InnoDB stores "wide" tables in a different way. Instead of having all the columns together in a single string (plus overhead, such as lengths, etc), it does the following:

• 如果给定行的所有列的总和超过8KB，它将把一些数据移到另一个(记录外")存储区域.
• 哪些列被移出记录取决于列的大小，等等.
• 详细信息取决于选择的ROW_FORMAT.
• 未记录"是另一个16KB块(或多个块).
• 稍后，当执行SELECT *时(或至少获取未记录的列)，它必须再次进行磁盘提取.

• If the total of all the columns for a given row exceeds about 8KB, it will move some of the data to another ("off-record") storage area.
• Which columns are moved off-record depends on the sizes of the columns, etc.
• The details depend on the ROW_FORMAT chosen.
• "Off-record" is another 16KB block (or blocks).
• Later, when doing SELECT * (or at least fetching the off-record column(s)), it must do another disk fetch.

该怎么办?

• 重新考虑有这么多列.
• 考虑垂直分区"，其中您有另一个包含选定的TEXT列的表.建议根据应用程序中的访问模式来选择列组.
• 对于通常很长的列，请考虑在客户端中将其压缩并存储到BLOB而不是TEXT中.大多数文字"以3:1缩小.斑点的发送与文本相同，但这些压缩的斑点会更小，因此溢出的可能性较小.
• 在SQL中进行更多处理-避免返回所有行，或避免返回全文等.在盲目地将大量文本推给客户端时，网络和客户端会成为耗时的重要因素，不仅是SELECT本身.

• Rethink having so many columns.
• Consider "vertical partitioning", wherein you have another table(s) that contains selected TEXT columns. Suggest picking groups of columns based on access patterns in your app.
• For columns that are usually quite long, consider compressing them in the client and storing into a BLOB instead of a TEXT. Most "text" shrinks 3:1. Blobs are sent off-record the same as Texts, however, these compressed blobs would be smaller, hence less likely to spill.
• Do more processing in SQL -- to avoid returning all the rows, or to avoid returning the full text, etc. When blindly shoveling lots of text to a client, the network and client become a sighificant factor in the elapsed time, not just the SELECT, itself.

这篇关于大量列，全选将永远的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！