How MySQL mysql data is stored on disk

Storage of MySQL data on disk:
Data BLOCK:
A block of multiple disk blocks in which the storage engine manages data blocks.

A disk is a block device, and the data stored on disk is stored in blocks.
When MySQL reads the table into memory, it is also allowed to read in a piece of the way. Suppose the table to be queried is in the same block as the other tables. In addition to reading the table to be queried, other tables are also read out when the block is loaded.

When a partial table within a block is deleted, this is the form of fragmentation. This reduces the speed of loading to memory.

So a size is generated, a fast inner table is recorded, there is no free space, and the location of the free space.

Record organization type of file:
Heap File organization:
Simply pile up the lines without order, and the records may be anywhere in the file.
Low query efficiency

Sequential file organization:
The rows are recorded in the order of the values of a field (search code, sort code).
The cost of the update is high.

Hash file organization:
Humans divide a table into parts, each part called a bucket. Hashing is done using a hash function based on one or some of the fields in the row, and the result belongs to a range placed in the specified bucket. Multiple buckets make up a table.
The barrel may overflow. So to select an appropriate hash function, let the rows average in each bucket.

The composition of the table:
Tables are made up of table definitions and table data. Similar to the block=inode+data.
Table Definition: Table structure, field type and length, etc.
Table Data: The property value of the field.

Tablespace (table space):
A logical management component that implements the organization of data from multiple tables in a single data file. is an advanced space manager. A bit similar to a logical volume.

Data directory:
Metadata for various relationships.

The name of the relationship
Field name
field type and length
View
Integrity constraints
User's name
Permissions
User's password
...


Data storage for MySQL:
MyISAM:
*.FRM table Structure definition file
*. MYD Table Data File
*. MYI Table Index File

InnoDB:
All tables share a table space file. That is, a large number of presentation data files are placed within a file. This file is called a tablespace file.
It is recommended to store separately.

Modified file:
*.FRM table Structure definition file
*.opt the options file. Contains the default character set and collation.
*.IBD table space (table data and table indexes)

Here's how:
Mysql>show VARIABLES liek '%innodb% '; view and InnoDB related session variables.
Innodb_file _per_table OFF is not enabled by default.

#vim/etc/my.cnf
[Mysqld]
innodb_file_per_table = 1
#systemctl Reload Mysqld

Mysql>show Global VARIABLES liek '%innodb% '; view InnoDB globals.

After MySQL is initialized, the default MySQL database format is MyISAM. The format of other databases depends on the default settings

To view the storage engines supported by MySQL:
Mysql>show ENGINES;

In the table header row:
Engine name
Suuport Yse Express Support, default is indicated as the defaults engine
Commnet Description Information
Transaction whether the transaction is supported
Whether XA supports distributed transactions
SavePoint support for SavePoint, support for transaction engine supports save point

To view status information for a table:
Mysql>show TABLE STATUS [from| In DBNAME] [like ' PATTERN ' | WHERE EXPR]

For example:
Mysql>use MySQL
Mysql>show table status like ' user ' \g View status information for user tables in MySQL Library


To view the character sets supported by the server:
Mysql>show CHARACTER SET;

To view the collation supported by the server:
Mysql>show COLLATION;


Instance:
1. Display character set and collation:
Mysql>show CHARACTER SET; Display Character Set
Mysql>show COLLATION; Show collation

2. Create a database, specify a character set of GBK, and a collation of gbk_chinese_ci
Mysql>create DATABASE IF not EXISTS test CHARACTER SET ' GBK ' COLLATE ' gbk_chinese_ci ';
Or
Mysql>create SCHEMA IF not EXISTS test CHARACTER SET ' GBK ' COLLATE ' gbk_chinese_ci ';

3. Delete the database:
Mysql>drop DATABASE test;

4. Database renaming:
In general, databases cannot be renamed, and the following methods are not recommended.
Idea: The database is essentially a few files, as long as the file name can be modified to change the name of the database in disguise.

#mysqladmin shutdown
#cd $DATA _dir
#mv test.* abc.*
#service mysqld Start

5. Insert data into the courses table:
Mysql>insert into courses (courname) VALUES (' httpd '), (' DNS '), (' MySQL ');
Mysql>select * from courses; View data for a table
Mysql>show INDEXES from courses; To view the index of a table

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1679322

How MySQL mysql data is stored on disk