1. Relationship between database and file system

  • InnoDB 、 MyISAMSuch storage engines store tables on file systems. When we want to read data, these storage engines will read data from the file system and send it back to us, and when we want to write data, these storage engines will write the data back to the file system.

MySQL > select * from ‘MySQL’

  • When the MySQL server program starts up, it loads some files to a directory in the file system. The data generated during the running of the MySQL server is also stored in some files in this directory, which is called the directoryThe data directory.

1. Difference between data directory and installation directory

  • The MySQL installation directory (which we can specify ourselves when installing MySQL), we have highlighted the very important bin directory in this installation directory, which stores a lot of commands for controlling client and server programs (many executable files, such as MySQL, mysqld, Mysqld_safe and dozens more).

  • The data directory is used to store data generated during the running of MySQL. It must be different from the installation directory discussed in this chapter. Be sure to tell them apart! Be sure to tell them apart! Be sure to tell them apart!

2, how to determine the data directory in MySQL

  • That was a long talk. What the hellMySQLWhere do I store all my data? Actually,The data directoryThat corresponds to a system variabledatadirWe can check the value of the system variable after the client is connected to the server:
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.00 sec)
Copy the code
  • As you can see from the results, on my computerMySQLIs the data directory of/usr/local/var/mysql/

Third, data directory structure

  • MySQLWhat kind of data is generated as it runs? Of course, it will contain user data for the database, tables, views, and triggers we created. In addition to this user data, to make the program run better,MySQLSome additional data will also be created, so let’s take a closer look at thisThe data directoryThe following content.

1. Representation of a database in a file system

  • What actually happens on the file system whenever we CREATE a DATABASE using the CREATE DATABASE name statement? Each database corresponds to a subdirectory of the data directory, or a folder. Whenever we create a new database, MySQL does two things for us:

    1. Create a subdirectory (or folder) under the data directory with the same name as the database.

    2. Create a file named db.opt under the subdirectory with the same name as the database. This file contains various properties of the database, such as the character set and comparison rules of the database.

  • Let’s see what databases are currently available on my computer:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| charset_demo_db    |
| dahaizi            |
| mysql              |
| performance_schema |
| sys                |
| xiaohaizi          |
+--------------------+
7 rows in set (0.00 sec)
Copy the code

It can be seen that there are currently 7 databases on my computer, among which the charset_demo_DB, dahaizi and xiaohaizi databases are self-defined, and the other 4 databases belong to MySQL’s own system database. Let’s look again at the contents of the data directory on my computer:

.├ ── Auto.cnF ├─ ca-Key. Pem └ ── Ca. Pem └ ─ Charset_demo_db └ ─-Cert. Pem ├ ─ ─ the client-Key. Pem Flag school ── Dahaizi Flag School ── IB_logFile0 Flag School ─ IB_logFile0 Flag School ─ IBDatA1 Flag School ─ IBTMP1 Flag School ─ mysql Flag School ── Performance_schema └ ─ private_key.pem └ ─ Public_key.pem └ ─ Server-Cert. Pem ├ ─ ─ server-Key. Pem Flag ── Sys Flag ─ xiaohaizideMacBook-Pro. Local. Err ├ ─ ─ xiaohaizideMacBook-Pro. Local. Pid └ ─ ─ xiaohaizi6 directories, 16 files
Copy the code

Of course, the files in this data directory are more similar to subdirectories, but if you look closely, all the other databases except information_SCHEMA have subdirectories in the data directory. This information_schema is special.

2. Table representation in the file system

  • Our data is actually inserted into the table in the form of records, and the information in each table can be divided into two types:

    1. Definition of a table structure
    2. Data in a table

  • Table structure is the name of the table, the number of columns in the table, the data type of each column, the constraints and indexes, the character set used and the comparison rules, all of which are reflected in our table construction sentences. To store this information, both InnoDB and MyISAM storage engines create a file that describes the table structure in the corresponding database subdirectory under the data directory. The file name is like this:

The name of the table. FRMCopy the code
  • Let’s say we’re atdahaiziCreate a database namedtestThe table:
mysql> USE dahaizi;
Database changed

mysql> CREATE TABLE test (
    -> c1 INT
    -> );
Query OK, 0 rows affected (0.03 sec)
Copy the code
  • That’s in the databasedahaiziThe corresponding subdirectory will create a name namedtest.frmA file that describes the structure of a table. It is worth noting that the suffix.frm is stored in binary format

(1) How does InnoDB store table data

We’ve talked a lot about InnoDB’s implementation principles. By now we should be familiar with the following:

  • InnoDB actually uses pages as the basic unit to manage storage space. The default page size is 16KB.

  • For the InnoDB storage engine, each index corresponds to a B+ tree, and each node of the B+ tree is a data page. The data pages need not be physically contiguous, because there are bidirectional linked lists between the data pages to maintain the order of the pages.

  • InnoDB’s clustered index leaves store the entire user record, which is called index as data, and data as index.

  • To better manage these pages, InnoDB designers have proposed a table space or file space (English name: Table space or File space is an abstract concept that can correspond to one or more real files on a file system (the number of files corresponding to different table Spaces may vary). Each table space can be divided into many, many, many pages, and our table data is stored in certain pages of a certain table space. The guy who designed InnoDB divides the table space into several different types. Let’s take a look at each one.

(2) System tablespace

  • This so-called system table space can correspond to one or more actual files on the file system. By default, InnoDB creates a 12MB file named IBData1 in the data directory (look for it in your data directory). This file is the file system representation of the corresponding system table space. Why is it only 12 meters? This is because the file is a so-called self-expanding file, which increases the size of the file when it is not enough

  • Of course, if you want the system tablespace to correspond to multiple actual files on the file system, or if you just don’t like the original ibdata1 file name, you can configure the corresponding file paths and their sizes at MySQL startup.

[server]
innodb_data_file_path=data1:512M; data2:512M:autoextend
Copy the code
  • So in theMySQLAfter startup, these two 512MB files will be created asSystem table space, in which theautoextendIndicates that the two files will automatically expand if they are insufficientdata2File size.

In a MySQL server, there is only one system tablespace

(3) file-per-table tablespace

  • In MySQL5.6.6 and later,InnoDBIt doesn’t happen by defaultThe data of each table is stored in the system table space, butCreate a separate table space for each tableThat is, as many tables as we create, there are as many independent table Spaces. useIndependent table spaceTo store table data, a subdirectory of the database to which the table belongs will be created to represent this tableIndependent table spaceThe file name is the same as the table name, but one has been added.ibdSo the full file name looks like this:
The name of the table. IbdCopy the code
  • Let’s say we useIndependent table spaceGo to the storexiaohaiziUnder databasetestTable in the database corresponding to the tablexiaohaiziThe directory will betestThe table creates these two files:
test.frm
test.ibd
Copy the code
  • Among themtest.ibdFiles are for storagetestData and indexes in a table. Of course, we can also specify their own useSystem table spaceorIndependent table spaceTo store data, this function is made up of startup parametersinnodb_file_per_tableControl, let’s say we want to intentionally store all the table data toSystem table spaceWhen it can be startedMySQLThe server is configured like this:
[server]
innodb_file_per_table=0
Copy the code

When the value of innodb_file_per_TABLE is 0, the system tablespace is used. When innodb_file_per_TABLE is set to 1, independent tablespaces are used. However, the innodb_file_per_TABLE parameter only works on newly created tables, not on tables that have already allocated tablespaces. If we want to move a table from an existing system tablespace to a separate tablespace, we can use the following syntax:

ALTER TABLEThe name of the table in TABLESPACE [=] innodb_file_per_table;
Copy the code

To move a table from a separate tablespace to a system tablespace, use the following syntax:

ALTER TABLEThe name of the table in TABLESPACE [=] innodb_system;
Copy the code

Mysql > alter table test; alter table test; alter table test; alter table test;

ALTER TABLE test TABLESPACE innodb_system;
Copy the code

(4) Other types of tablespaces

With the development of MySQL, in addition to the above two old table space, there are now some new types of table space, For example, general tablespace, undo tablespace, and temporary tablespace, we won’t talk about them in detail.