How does MySQL store data

Question

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 than just reading and writing to a regular file?

Answer 1

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 mysql console and running the following command will tell you exactly where the folder is located.

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.

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 .frm extension and the other with a .idb extension. See screenshot below.

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

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

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

Answer 2

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

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?

First of all, DBMses are not just about performance:

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

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!

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.

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 ;) )?

---

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