MySQL MySQL如何存储数据

How does MySQL store data

提问by Tallboy

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

我环顾了 Goole，但没有找到任何好的答案。它是否将数据存储在一个大文件中？它使用哪些方法来使数据访问比仅读取和写入常规文件更快？

采纳答案by Branko Dimitrijevic

Does it store the data in one big file?

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

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...

一些 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.

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

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

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

First of all, DBMses are not just about performance:

首先，DBM 不仅仅与性能有关：

• They are even more about safetyof 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.²

• 他们甚至更关心您的数据安全——他们必须确保即使在断电或网络故障的情况下也不会损坏数据。¹
• DBMS 还与并发性有关——它们必须在访问和可能修改相同数据的多个客户端之间进行仲裁。²

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 充分利用了这一点来实现性能。最重要的是，SQL 基于集合的特性让 DBMS 选择检索数据的最佳方式（至少在理论上，一些 DBMS 比其他 DBMS 在这方面做得更好）。有关 DBMS 性能的更多信息，我强烈推荐：使用索引，卢克！

Also, you probably noticed that most DBMSes are rather old products. Like decadesold, 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 都是相当老的产品。就像几十年前一样，这在我们行业的术语中真的是亿万年。这样做的结果之一是人们有足够的时间来优化 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 ;) )?

理论上，您可以通过文件来实现所有这些功能，但我怀疑您最终会得到一些看起来非常接近 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.

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

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

²通过隔离事务，甚至允许客户端显式锁定数据库的特定部分。

回答by jafar690

This question is a bit old but I decided to answer it anyway since I have been doing some digging on the same. My answer is based on the linux file system. Basically mySQL stores data in files in your hard disk. It stores the files in a specific directory that has the system variable "datadir". Opening a mysqlconsole and running the following command will tell you exactly where the folder is located.

这个问题有点老了，但我还是决定回答它，因为我一直在做同样的挖掘。我的答案是基于 linux 文件系统。基本上，mySQL 将数据存储在硬盘中的文件中。它将文件存储在具有系统变量“datadir”的特定目录中。打开mysql控制台并运行以下命令将告诉您文件夹的确切位置。

mysql>  SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

As you can see from the above command, my "datadir" was located in /var/lib/mysql/. The location of the "datadir" may vary in different systems. The directory contains folders and some configuration files. Each folder represents a mysql database and contains files with data for that specific database. below is a screenshot of the "datadir" directory in my system.

从上面的命令中可以看出，我的“datadir”位于/var/lib/mysql/. “datadir”的位置在不同的系统中可能会有所不同。该目录包含文件夹和一些配置文件。每个文件夹代表一个 mysql 数据库，并包含包含该特定数据库数据的文件。下面是我系统中“datadir”目录的屏幕截图。

Each folder in the directory represents a MySQL database. Each database folder contains files that represent the tables in that database. There are two files for each table, one with a .frmextension and the other with a .idbextension. See screenshot below.

目录中的每个文件夹代表一个 MySQL 数据库。每个数据库文件夹都包含代表该数据库中表的文件。每个表有两个文件，一个带有.frm扩展名，另一个带有.idb扩展名。请参阅下面的屏幕截图。

The .frmtable file stores the table's format. Details: MySQL .frm File Format

该.frm表文件存储表的格式。详细信息：MySQL .frm 文件格式

The .ibdfile stores the table's data. Details: InnoDB File-Per-Table Tablespaces

该.ibd文件存储表的数据。详细信息：InnoDB File-Per-Table 表空间

That's it folks! I hope I helped someone.

就是这样的人！我希望我帮助了某人。

回答by Rama Hoetzlein

When you store data in a flat file, it is compact and efficient to read sequentially, but there is no fast way to access it randomly. This is especially true of variable-length data such as documents, names or strings. To allow for fast random access, most databases store information in a single file using a data structure called a B-Tree. This structure allows for insert, deletion, and search to be fast, but it can use up to 50% more space than the original file. Typically, however, this is not an issue as disk space is cheap and larger, while the primary tasks usually require fast access. For more information: http://en.wikipedia.org/wiki/B-tree

当您将数据存储在平面文件中时，顺序读取是紧凑且高效的，但是没有快速的方法可以随机访问它。对于诸如文档、名称或字符串之类的可变长度数据尤其如此。为了允许快速随机访问，大多数数据库使用称为 B 树的数据结构将信息存储在单个文件中。这种结构允许快速插入、删除和搜索，但它最多可以使用比原始文件多 50% 的空间。然而，这通常不是问题，因为磁盘空间便宜且更大，而主要任务通常需要快速访问。更多信息：http: //en.wikipedia.org/wiki/B-tree

Looking carefully into the MySQL docs, we find that indices may be optionally set to "BTREE" or "HASH" type. Inside a single MySQL file, multiple indices are stored which may use either data structure.

仔细查看 MySQL 文档，我们发现索引可以选择设置为“BTREE”或“HASH”类型。在单个 MySQL 文件中，存储了多个索引，这些索引可以使用任一数据结构。

Although safety and concurrency are important, these are not WHY databases exist, but added features. The very first databases existbecause it is not possible to randomly access a sequential file containing variable length data.

尽管安全性和并发性很重要，但这并不是数据库存在的原因，而是添加的功能。最初的数据库存在是因为不可能随机访问包含可变长度数据的顺序文件。

回答by apesa

Technically everything is a "file" including folders.. your entire hard drive is giant file. Having said that, yes relational databases, MySQL included store data in a Data file on the hard drive. The difference between a Database and writing/reading to a file is apples and oranges. Databases provide a structured way to store and search/retrieve data in a way you could never replicate by just reading and writing to a file.. Unless you wrote your own db of course..

从技术上讲，一切都是一个“文件”，包括文件夹......你的整个硬盘都是巨大的文件。话虽如此，是的关系数据库，MySQL 将存储数据包含在硬盘驱动器上的数据文件中。数据库和写入/读取文件之间的区别是苹果和橘子。数据库提供了一种结构化的方式来存储和搜索/检索数据，这种方式您永远无法通过读取和写入文件来复制。当然，除非您编写自己的数据库。

hope that helps.

希望有帮助。