MySQL 数据库中有多少行是 TOO MANY？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1926079/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How many rows in a database are TOO MANY?
提问by Juanjo Conti
I've a MySQL InnoDB table with 1,000,000 records. Is this too much? Or databases can handle this and more? I ask because I noticed that some queries (for example, getting the last row from a table) are slower (seconds) in the table with 1 millon rows than in one with 100.
我有一个包含 1,000,000 条记录的 MySQL InnoDB 表。这太多了吗？或者数据库可以处理这个以及更多？我问是因为我注意到某些查询（例如，从表中获取最后一行）在具有 1 百万行的表中比在具有 100 行的表中慢（秒）。
回答by OMG Ponies
I've a MySQL InnoDB table with 1000000 registers. Is this too much?
我有一个包含 1000000 个寄存器的 MySQL InnoDB 表。这太多了吗？
No, 1,000,000 rows(AKA records) is not too much for a database.
不，1,000,000行（AKA 记录）对于数据库来说不算太多。
I ask because I noticed that some queries (for example, getting the last register of a table) are slower (seconds) in the table with 1 million registers than in one with 100.
我问是因为我注意到某些查询（例如，获取表的最后一个寄存器）在具有 100 万个寄存器的表中比在具有 100 个寄存器的表中慢（秒）。
There's a lot to account for in that statement. The usual suspects are:
该声明中有很多内容需要说明。通常的嫌疑人是：
- Poorly written query
- Not using a primary key, assuming one even exists on the table
- Poorly designed data model (table structure)
- Lack of indexes
- 写得不好的查询
- 不使用主键，假设一个甚至存在于表中
- 设计不良的数据模型（表结构）
- 缺乏索引
回答by amir beygi
I have a database with more than 97,000,000records(30GB datafile), and having no problem .
我有一个包含超过97,000,000条记录（30GB 数据文件）的数据库，并且没有问题。
Just remember to define and improve your table index.
请记住定义和改进您的表索引。
So its obvious that 1,000,000is not MANY ! (But if you don't index; yes, it is MANY )
所以很明显，1,000,000并不多！（但如果你不索引；是的，它是很多）
回答by Journeyman Programmer
Use 'explain' to examine your query and see if there is anything wrong with the query plan.
使用“explain”检查您的查询并查看查询计划是否有任何问题。
回答by Morgan Tocker
I think this is a common misconception - size is only one part of the equation when it comes to database scalability. There are other issues that are hard (or harder):
我认为这是一个常见的误解 - 在数据库可伸缩性方面，大小只是等式的一部分。还有其他困难（或更难）的问题：
How large is the working set (i.e. how much data needs to be loaded in memory and actively worked on). If you just insert data and then do nothing with it, it's actually an easy problem to solve.
What level of concurrency is required? Is there just one user inserting/reading, or do we have many thousands of clients operating at once?
What levels of promise/durability and consistency of performance are required? Do we have to make sure that we can honor each commit. Is it okay if the average transaction is fast, or do we want to make sure that all transactions are reliably fast (six sigma quality control like - http://www.mysqlperformanceblog.com/2010/06/07/performance-optimization-and-six-sigma/).
Do you need to do any operational issues, such as ALTER the table schema? In InnoDB this is possible, but incredibly slow since it often has to create a temporary table in foreground (blocking all connections).
工作集有多大（即需要在内存中加载多少数据并积极处理）。如果只是插入数据，然后什么都不做，其实很容易解决的问题。
需要什么级别的并发？是否只有一个用户插入/读取，或者我们是否有成千上万的客户端同时运行？
需要什么级别的承诺/持久性和性能一致性？我们是否必须确保我们能够兑现每个承诺。如果平均交易速度很快，是否可以，或者我们是否要确保所有交易都可靠快速（六西格玛质量控制，如 - http://www.mysqlperformanceblog.com/2010/06/07/performance-optimization-和-六西格玛/）。
您是否需要处理任何操作问题，例如 ALTER 表架构？在 InnoDB 中，这是可能的，但速度非常慢，因为它通常必须在前台创建一个临时表（阻塞所有连接）。
So I'm going to state the two limiting issues are going to be:
所以我要说明两个限制性问题将是：
- Your own skill at writing queries / having good indexes.
- How much pain you can tolerate waiting on ALTER TABLE statements.
- 您自己编写查询/拥有良好索引的技能。
- 您可以忍受等待 ALTER TABLE 语句的痛苦程度。
回答by Jé Queue
I've seen non-partitioned tables with several billion (indexed) records, that self-joined for analytical work. We eventually partitioned the thing but honestly we didn't see that much difference.
我见过具有数十亿（索引）记录的非分区表，这些表是自联接用于分析工作的。我们最终对这件事进行了分区，但老实说，我们没有看到太大的区别。
That said, that was in Oracle and I have not tested that volume of data in MySQL. Indexes are your friend :)
也就是说，那是在 Oracle 中，我没有在 MySQL 中测试过该数据量。索引是你的朋友 :)
回答by GrayWizardx
If you mean 1 million rows, then it depends on how your indexing is done and the configuration of your hardware. A million rows is not a large amount for an enterprise database, or even a dev database on decent equipment.
如果您的意思是 100 万行，那么这取决于您的索引是如何完成的以及您的硬件配置。一百万行对于企业数据库，甚至是体面设备上的开发数据库来说都不是一个大数目。
if you mean 1 million columns (not sure thats even possible in MySQL) then yes, this seems a bit large and will probably cause problems.
如果您的意思是 100 万列（不确定在 MySQL 中是否可行），那么是的，这看起来有点大，可能会导致问题。
回答by phoebus
Register? Do you mean record?
登记？你的意思是记录？
One million records is not a real big deal for a database these days. If you run into any issue, it's likely not the database system itself, but rather the hardware that you're running it on. You're not going to run into a problem with the DB before you run out of hardware to throw at it, most likely.
如今，一百万条记录对于数据库来说并不是什么大问题。如果遇到任何问题，很可能不是数据库系统本身，而是运行它的硬件。很可能，在没有硬件可用之前，您不会遇到数据库问题。
Now, obviously some queries are slower than others, but if two very similar queries run in vastly different times, you need to figure out what the database's execution plan is and optimize for it, i.e. use correct indexes, proper normalization, etc.
现在，显然有些查询比其他查询慢，但是如果两个非常相似的查询在截然不同的时间运行，您需要弄清楚数据库的执行计划是什么并对其进行优化，即使用正确的索引、适当的规范化等。
Incidentally, there is no such thing as a "last" record in a table, from a logical standpoint they have no inherent order.
顺便说一句，表中没有“最后”记录这样的东西，从逻辑的角度来看，它们没有固有的顺序。
回答by Thomas Bonini
Assuming you mean "records" by "registers" no, it's not too much, MySQL scales really well and can hold as many records as you have space for in your hard disk.
假设您的意思是“寄存器”的“记录”不，它并不过分，MySQL 的伸缩性非常好，可以容纳与硬盘空间一样多的记录。
Obviously though search queries will be slower. There is really no way around that except making sure that the fields are properly indexed.
显然，尽管搜索查询会变慢。除了确保字段被正确索引之外，真的没有办法解决这个问题。
回答by jvilalta
The larger the table gets (as in more rows in it), the slower queries will typically run if there are no indexes. Once you add the right indexes your query performance should improve or at least not degrade as much as the table grows. However, if the query itself returns more rows as the table gets bigger, then you'll start to see degradation again.
表越大（因为其中的行越多），如果没有索引，查询通常会运行得越慢。一旦您添加了正确的索引，您的查询性能应该会随着表的增长而提高或至少不会降低太多。但是，如果查询本身随着表变大而返回更多行，那么您将再次开始看到性能下降。
While 1M rows are not that many, it also depends on how much memory you have on the DB server. If the table is too big to be cached in memory by the server, then queries will be slower.
虽然 1M 行并不多，但这也取决于您在数据库服务器上有多少内存。如果表太大而无法被服务器缓存在内存中，那么查询会变慢。
回答by Louis
Using the query provided will be exceptionally slow because of using a sort merge method to sort the data.
由于使用排序合并方法对数据进行排序，使用提供的查询会异常缓慢。
I would recommend rethinking the design so you are using indexes to retrieve it or make sure it is already ordered in that manner so no sorting is needed.
我建议重新考虑设计，以便您使用索引来检索它或确保它已经以这种方式排序，因此不需要排序。
