在性能开始降低之前，MySQL数据库有多大

水深无声 2022-11-26 01:55 113阅读 0赞

本文翻译自：[How big can a MySQL database get before performance starts to degrade][]

*At what point does a MySQL database start to lose performance?* **MySQL数据库在什么时候开始失去性能？**

*  *Does physical database size matter?* **物理数据库大小是否重要？**
 *  *Do number of records matter?* **记录数量是否重要？**
 *  *Is any performance degradation linear or exponential?* **任何性能下降是线性还是指数？**

*I have what I believe to be a large database, with roughly 15M records which take up almost 2GB.* **我有一个我认为是一个大型数据库，大约有15M的记录，占用了近2GB。** *Based on these numbers, is there any incentive for me to clean the data out, or am I safe to allow it to continue scaling for a few more years?* **基于这些数字，我是否有动力清理数据，或者我是否可以安全地继续扩展数年？**

--------------------

### \#1楼 ###

参考：[https://stackoom.com/question/kA/在性能开始降低之前-MySQL数据库有多大][https_stackoom.com_question_kA_-MySQL]

--------------------

### \#2楼 ###

*The physical database size doesn't matter.* **物理数据库大小无关紧要。** *The number of records don't matter.* **记录数无关紧要。**

*In my experience the biggest problem that you are going to run in to is not size, but the number of queries you can handle at a time.* **根据我的经验，您要运行的最大问题不是大小，而是一次可以处理的查询数量。** *Most likely you are going to have to move to a master/slave configuration so that the read queries can run against the slaves and the write queries run against the master.* **很可能您将不得不转移到主/从配置，以便可以针对从服务器运行读取查询，并且针对主服务器运行写入查询。** *However if you are not ready for this yet, you can always tweak your indexes for the queries you are running to speed up the response times.* **但是，如果您尚未做好准备，则可以随时调整正在运行的查询的索引，以加快响应时间。** *Also there is a lot of tweaking you can do to the network stack and kernel in Linux that will help.* **此外，您可以对Linux中的网络堆栈和内核进行大量调整，这将有所帮助。**

*I have had mine get up to 10GB, with only a moderate number of connections and it handled the requests just fine.* **我有我的高达10GB，只有中等数量的连接，它处理请求就好了。**

*I would focus first on your indexes, then have a server admin look at your OS, and if all that doesn't help it might be time to implement a master/slave configuration.* **我将首先关注您的索引，然后让服务器管理员查看您的操作系统，如果所有这些都没有帮助它可能是时候实现主/从配置。**

--------------------

### \#3楼 ###

*A point to consider is also the purpose of the system and the data in the day to day.* **要考虑的一点也是系统的目的和日常数据。**

*For example, for a system with GPS monitoring of cars is not relevant query data from the positions of the car in previous months.* **例如，对于具有GPS监控汽车的系统，与前几个月的汽车位置不相关的查询数据。**

*Therefore the data can be passed to other historical tables for possible consultation and reduce the execution times of the day to day queries.* **因此，可以将数据传递到其他历史表以进行可能的咨询，并减少日常查询的执行时间。**

--------------------

### \#4楼 ###

*In general this is a very subtle issue and not trivial whatsoever.* **总的来说，这是一个非常微妙的问题，并不是无关紧要的。** *I encourage you to read [mysqlperformanceblog.com][] and [High Performance MySQL][] .* **我鼓励你阅读[mysqlperformanceblog.com][]和[高性能MySQL][High Performance MySQL] 。** *I really think there is no general answer for this.* **我真的认为没有一般的答案。**

*I'm working on a project which has a MySQL database with almost 1TB of data.* **我正在开发一个项目，它拥有一个拥有近1TB数据的MySQL数据库。** *The most important scalability factor is RAM.* **最重要的可扩展性因素是RAM。** *If the indexes of your tables fit into memory and your queries are highly optimized, you can serve a reasonable amount of requests with a average machine.* **如果表的索引适合内存并且您的查询经过高度优化，则可以使用普通计算机提供合理数量的请求。**

*The number of records do matter, depending of how your tables look like.* **记录的数量很重要，具体取决于表格的外观。** *It's a difference to have a lot of varchar fields or only a couple of ints or longs.* **拥有大量varchar字段或只有几个整数或长整数是不同的。**

*The physical size of the database matters as well: think of backups, for instance.* **数据库的物理尺寸也很重要：例如，考虑备份。** *Depending on your engine, your physical db files on grow, but don't shrink, for instance with innodb.* **根据您的引擎，您的物理db文件会增长，但不会缩小，例如使用innodb。** *So deleting a lot of rows, doesn't help to shrink your physical files.* **因此删除大量行无助于缩小物理文件。**

*There's a lot to this issues and as in a lot of cases the devil is in the details.* **这个问题有很多，而且在很多情况下，魔鬼都在细节中。**

--------------------

### \#5楼 ###

*Also watch out for complex joins.* **还要注意复杂的连接。** *Transaction complexity can be a big factor in addition to transaction volume.* **除交易量之外，交易复杂性也是一个重要因素。**

*Refactoring heavy queries sometimes offers a big performance boost.* **重构大量查询有时会带来很大的性能提升。**

--------------------

### \#6楼 ###

*Performance can degrade in a matter of few thousand rows if database is not designed properly.* **如果数据库设计不当，性能可能会在几千行中降低。**

*If you have proper indexes, use proper engines (don't use MyISAM where multiple DMLs are expected), use partitioning, allocate correct memory depending on the use and of course have good server configuration, MySQL can handle data even in terabytes!* **如果您有适当的索引，请使用正确的引擎（不要使用MyISAM，其中需要多个DML），使用分区，根据使用情况分配正确的内存，当然还有良好的服务器配置，MySQL甚至可以处理TB级数据！**

*There are always ways to improve the database performance.* **总有办法提高数据库性能。**

[How big can a MySQL database get before performance starts to degrade]: https://oldbug.net/q/kA/How-big-can-a-MySQL-database-get-before-performance-starts-to-degrade
[https_stackoom.com_question_kA_-MySQL]: https://stackoom.com/question/kA/%E5%9C%A8%E6%80%A7%E8%83%BD%E5%BC%80%E5%A7%8B%E9%99%8D%E4%BD%8E%E4%B9%8B%E5%89%8D-MySQL%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%89%E5%A4%9A%E5%A4%A7
[mysqlperformanceblog.com]: http://mysqlperformanceblog.com
[High Performance MySQL]: https://rads.stackoverflow.com/amzn/click/com/0596101716