MySQL表太大了吗? [英] How big is too big for a MySQL table?

问题描述

我最终被说服将较小的表放到一个大的表中，但是对于MySQL表，究竟有多大呢?

I was finally convinced to put my smaller tables into one large one, but exactly how big is too big for a MySQL table?

我有一个包含18个字段的表格.有些是TEXT，有些是短的VARCHAR(16)，有些是更长的VARCHAR(100).

I have a table with 18 fields. Some are TEXT, some are short VARCHAR(16), others longer VARCHAR(100).

现在，我们每天可获得约​​200,000行，即每月超过600万行.太大太大了?有多少个字段还是行才有关系?

Right now we get about 200,000 rows a day, which would be 6 million+ a month. How big is too big? Does it matter how many fields you have, or just rows?

推荐答案

太大是太大"这个问题并没有很好的通用解决方案-这种担忧通常取决于您对数据的处理方式和处理方式.您的性能考虑因素是

There's not a great general solution to the question "How big is too big" - such concerns are frequently dependent on what you're doing with your data and what your performance considerations are.

表格大小有一些基本限制.您最多只能有1000列.每个记录不能超过8k.这些限制根据数据库引擎而变化. (这里是针对InnoDB的.)

There are some fundamental limits on table sizes. You can't have more than 1000 columns. Your records can't be bigger than 8k each. These limits change depending on database engine. (The ones here are for InnoDB.)

听起来您已经将几个不同的数据集合并到一个表中.您可能有一些字段可以告诉您该记录与什么数据集有关，以及一些数据字段和一些时间戳信息.那不是很广泛的记录(除非您记录了每个请求的所有输入参数.)您的主要问题将是选择性.以有意义的方式索引此表将是一个挑战.如果您的公用字段可以有足够的选择性，而无需查询表就可以使用它们来获取所需的记录，那么这将是一个巨大的好处. (参见表扫描)

It sounds like you've merged several different data sets into one table. You probably have some fields that tell you what data set this record pertains to, along with some data fields, and some timestamp information. That's not a very wide record (unless you're logging, say, all the input parameters of each request.) Your main problem will be with selectivity. Indexing this table in a meaningful way will be a challenge. If your common fields can be selective enough that you can use them to get to the records you want without consulting the table, that will be a huge plus. (Cf. table scan)

对于每天这么多的记录(基本上是一整天两秒钟，并且我假设您有一个峰值加载期，它要高得多)，您还需要确保专门查看优化在提高插入速度上.通常，索引越多=插入速度越慢.如果可以，请考虑将过时的记录完全归档到另一个表中.在以前的工作场所中，我们在单独的表中使用了上个月，前三个月，前六个月的归档策略.另一个想法是删除较旧的记录.许多环境根本不需要特定日期后的信息.停留在三个月前的日志记录上通常过于昂贵.

For that many records per day (basically, two a second all day, and I'm presuming you have a peak-load period where it's much higher), you'll also want to make sure that you specifically look at optimizations on improving insertion speed. As a general rule, more indexes = slower insertions. If you can, consider archiving off outdated records to another table entirely. In prior workplaces, we've used an archival strategy of Last Month, Prior Three Months, Prior Six Months, each in separate tables. Another idea is to delete older records. Many environments simply don't need information beyond a certain date. Hanging on to logging records from three months ago is often overly expensive.

最后，不要忽略表的物理存储.记录越薄，读取(或就此插入)记录所需的物理IO越少.您可以将索引存储在单独的物理硬盘驱动器上.如果您的记录中有很多冗余数据，则压缩表实际上可能会提高速度.如果您有少量现金要消耗，请考虑使用良好的RAID阵列剥离数据的价值.

Finally, don't neglect the physical storage of your table. The thinner your records are, the less physical IO needs to occur to read (or for that matter, to insert) a record. You can store your indexes on a separate physical hard drive. If there's a lot of redundant data in your records storing the table compressed might actually be a speed increase. If you have a little cash to burn, consider the value of a good RAID array for striping your data.

因此，要回答您的基本问题:有很多记录，但是只要仔细调整，就不会有问题.

So, to answer your basic question: it's a lot of records, but with a careful eye towards tuning, it won't be a problem.

这篇关于MySQL表太大了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！