MySQL Series 7 MySQL Storage Engine

1. MyISAM storage engine

shortcoming:

1. No support for transactions
2. Minimum granularity lock: table level
3. Reading and writing block each other. You cannot read while writing, and you cannot write while reading.
4. Does not support MVCC (supports multi-version concurrency control mechanism)
5. Clustered indexes are not supported
6. No support for data caching
7. Foreign keys are not supported
8. Poor crash recovery

advantage:

1. Supports up to 256TB storage space
2. Read data faster and take up less resources

MyISAM engine storage files:

• tbl_name.frm: table format definition
• tbl_name.MYD: data file
• tbl_name.MYI: index file

Applicable scenarios: The default database engine before MySQL 5.5.5, applicable in scenarios where the data is read-only (or written less) and the table is small (where a long repair operation can be accepted)

2: InnoDB storage engine

Features:

1. 64TB
2. Support Affairs
3. Row-level locks
4. Support multi-version concurrency control mechanism (MVCC)
5. Support clustered index
6. Support data caching
7. Support foreign keys

InnoDB database files:

• tb_name.frm: table format definition
• tb_name.ibd : data file

Note: By default, all innodb table data files are stored in ibddata1, ibddata2, ... in the database directory, which is extremely inconvenient to manage.

Strongly recommended: Enable innodb_file_per_table=ON, and use a separate tablespace for each table to store the table's data and indexes

Enable: innodb_file_per_table

Edit /etc/my.cnf and add innodb_file_per_table under [mysqld]
Restart the server# service mysqld restart
MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+

1. Manage storage engines

View the storage engines supported by MySQL: MariaDB [(none)]> SHOW ENGINES\G

View the current default storage engine:

MariaDB [(none)]> SHOW VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+

Set the default storage engine:

Edit /etc/my.conf and add default_storage_engine = InnoDB under [mysqld]

2. InnoDB storage engine cache

The buffer pool of the InnoDB storage engine usually has a buffer pool hit rate of no less than 99%.

Related state variables:

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'innodb%read%'\G

• Innodb_buffer_pool_reads: Indicates the number of times a page is read from the physical disk
• Innodb_buffer_pool_read_ahead: Number of pre-reads
• Innodb_buffer_pool_read_ahead_evicted: The number of pages that were pre-read but replaced from the buffer pool without being read. This is generally used to determine the efficiency of pre-reading.
• Innodb_buffer_pool_read_requests: Number of times a page is read from the buffer pool
• Innodb_data_read: Total number of bytes read
• Innodb_data_reads: The number of read requests initiated. Each read may require reading multiple pages.

Innodb buffer pool hit rate = Innodb_buffer_pool_read_requests / ( Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads )

Average number of bytes per read = Innodb_data_read / Innodb_data_reads

3. Other Storage Engines

• Performance_Schema: Performance_Schema database;
• Memory: All data is stored in RAM for fast access in environments where quick lookup of reference and other similar data is required. Suitable for storing temporary data. The engine was previously known as the HEAP engine;
• MRG_MyISAM: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as a single object. Applicable to VLDB (Very Large Data Base) environments, such as data warehouses;
• Archive: For storing and retrieving large amounts of rarely referenced archive or security audit information, only supports SELECT and INSERT operations; supports row-level locks and dedicated buffers;
• Federated Federation: A proxy used to access other remote MySQL servers. It creates a client connection to a remote MySQL server and transmits the query to the remote server for execution, and then completes data access. It provides the ability to link separate MySQL servers to create a logical database from multiple physical servers. Very suitable for distributed or data mart environments;
• BDB: A transaction engine that can replace InnoDB and supports COMMIT, ROLLBACK, and other transaction features;
• Cluster/NDB: MySQL's clustered database engine, particularly suited for applications with high-performance search requirements that also demand the highest uptime and availability.
• CSV: The CSV storage engine stores data in text files using the comma-separated value format. Data exchange between other software and applications can be imported and exported in CSV format using the CSV engine;
• BLACKHOLE: The black hole storage engine accepts but does not store data, and retrieval always returns an empty set. This feature can be used in distributed database design, where data is automatically replicated but not stored locally;
• example: a "stub" engine that does nothing. Tables can be created using this engine, but data cannot be stored in or retrieved from them. It is intended as an example of how to get started writing a new storage engine.

This is the end of this article about the MySQL series, part seven, MySQL storage engine. For more information about MySQL storage engine, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!