MySQL如何存储数据 [英] How does MySQL store data

问题描述

我环顾google，但没有找到任何好的答案。它是否将数据存储在一个大文件中？

I looked around google but didn't find any good answers. Does it store the data in one big file? What methods does it use to make data access quicker them just reading and writing to a regular file?

推荐答案

它是否将数据存储在一个大文件中？

Does it store the data in one big file?

有些DBMS将整个数据库存储在一个文件中，索引和其他对象类型分隔文件，一些拆分文件不是按对象类型，而是通过一些存储/大小标准，有些甚至可以完全绕过文件系统等等...

Some DBMSes store the whole database in a single file, some split tables, indexes and other object kinds to separate files, some split files not by object kind but by some storage/size criteria, some can even entirely bypass the file system, etc etc...

我不知道这些策略MySQL使用（它可能取决于您是否使用MyISAM与InnoDB等），但幸运的是，这并不重要：从客户的角度来看，这是一个DBMS实现细节，客户端应该很少担心。 >

I don't know which one of these strategies MySQL uses (it probably depends on whether you use MyISAM vs. InnoDB etc.), but fortunately, it doesn't matter: from the client perspective, this is a DBMS implementation detail the client should rarely worry about.

它使用什么方法使数据访问更快，只需读取和写入常规文件？

What methods does it use to make data access quicker them just reading and writing to a regular file?

首先，DBMses不只是关于性能：

First of all, DBMses are not just about performance:

• 安全：即使面临断电或网络故障，也必须确保没有数据损坏。 ¹


• DBMS也关于并发性 - 它们必须在多个客户端之间进行仲裁才能访问并可能修改相同的数据。 ²

• They are even more about safety of your data - they have to ensure there is no data corruption even in the face of a power cut or a network failure.¹
• DBMSes are also about concurrency - they have to arbiter between multiple clients accessing and potentially modifying the same data.²

对于你的具体性能问题，关系数据非常容易索引和聚类，这是DBMSes丰富利用来实现性能。除此之外，SQL的基于集合的本质使DBMS选择检索数据的最佳方式（理论上至少，一些DBMS比其他DBMS更好）。有关DBMS性能的更多信息，我们热烈推荐：使用索引，Luke！

As for your specific question of performance, relational data is very "susceptible" to indexing and clustering, which is richly exploited by DBMSes to achieve performance. On top of that, the set-based nature of SQL lets the DBMS choose the optimal way to retrieve the data (in theory at least, some DBMSes are better at that than the others). For more about DBMS performance, I warmly recommend: Use The Index, Luke!

此外，你可能注意到大多数DBMS是相当老的产品。喜欢十年，这在我们行业的术语上真的很好。一个后果是，人们有足够的时间来优化DBMS代码库。

Also, you probably noticed that most DBMSes are rather old products. Like decades old, which is really eons in our industry's terms. One consequence of that is that people had plenty of time to optimize the heck out of the DBMS code base.

你可以在理论上通过文件实现所有这些事情，但我怀疑你最终会看到非常接近DBMS的东西（即使你有时间和资源来实际做）。所以，为什么要重新发明轮子（除非你不想要轮子在第一位）;

You could, in theory, achieve all these things through files, but I suspect you'd end-up with something that looks awfully close to a DBMS (even if you had the time and resources to actually do it). So, why reinvent the wheel (unless you didn't want the wheel in the first place ;) )?

em> ¹ 通常是通过某种日志或事务日志机制。此外，为了最小化逻辑损坏（由于应用程序错误）并促进代码重用的可能性，大多数DBMS支持声明性约束（域，键和引用），触发器和存储过程。

¹ Usually though some kind of "journaling" or "transaction log" mechanism. Furthermore, to minimize the probability of "logical" corruption (due to application bugs) and promote code reuse, most DBMSes support declarative constraints (domain, key and referential), triggers and stored procedures.

² 由隔离事务，甚至允许客户端明确锁定数据库的特定部分。

² By isolating transactions and even by allowing clients to explicitly lock specific portions of the database.

这篇关于MySQL如何存储数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！