MySQL uses a plug-in storage engine.
The main include storage engine is: Myisam,innodb,ndb cluster,maria,falcon,memory,archive,merge,federated.
The most extensive of these are the MyISAM and InnoDB two storage engines, so let's take a brief introduction to them.
Introduction to the MyISAM storage engine
The tables of the MyISAM storage engine are stored in the database and each table is stored as three physical files named after the table name.
1. (. frm file) A file that holds table structure definition information that is indispensable to any storage engine
2. (. myd file) file that holds table data
3. (. myi file) file that holds all the index data for the table
Note: There are only such three files that are stored as tables of the MyISAM storage type
MyISAM tables are stored in a different format with the same suffix name. The MYD file.
The format is divided into: static (fixed) constant length, dynamic variable length, and compression (compressed) of three formats.
Compression: You can create a table by Row_format to specify {compressed | Default} (does not compress by defaults)
Compression can also be done through the Myisampack tool.
Non-compression:
Dynamic: Whenever a field with a variable-length type exists in a table, the table must be in the dynamic format
Static: Fixed if there are no variable-length fields
MyISAM supports the following three types of indexes:
1. B-tree Index
B-tree index, all the index nodes are stored according to the balance Tree data structure, all the index data nodes are in the leaf node.
2. R-tree Index
There are some differences between the storage of R-tree indexes and the B-tree index, which are designed to index the fields of storage space and multidimensional data, and to support fields of the geometry type.
3. Full-text Index
The Full-text index is the full-text index that we say, and his storage structure is b-tree. The main thing is to solve inefficient problems that we need to use like queries.
Note: The sum of the lengths of all fields participating in an index cannot exceed 1000 bytes
Introduction to the Innodb storage engine
1. Support Transaction Installation
Implements all four levels defined by the SQL92 standard (read Uncommitted,read committed,repeatable read and Serializable)
2, data multi-version read
Innodb in the transaction support, in order to ensure the consistency of data has been the performance of the time, through the undo information, to achieve multi-version of the data read.
3, the improvement of locking mechanism
Innodb changed the locking mechanism of MyISAM and implemented the row lock. The implementation of the row lock mechanism is done by index.
4. Implement FOREIGN key
It is recommended that you do not use foreign keys, which are recommended by database System tuning experts
5, the physical structure of INNODB
Same as MyISAM: (. frm file) to hold metadata related to the table structure definition.
Not the same as MyISAM: Table data and index data are stored together
The physical structure of INNODB is divided into two parts:
1. Data files (table data and index data)
Holds data in the data table and all index data, including primary keys and other normal indexes.
The Innodb table space is divided into two forms:
One is to share a tablespace, where all tables and index data are stored in the same tablespace (one or more data files).
by Innodb_data_file_path, adding data files requires a shutdown restart.
Another is the exclusive table space, where the data and indexes of each table are stored in a separate. ibd file.
Note:shared tablespace is a must exist, because InnoDB's undo information and some other metadata information are stored in the shared table space.
The data file for a shared tablespace is two forms that can be set to a fixed size and can be automatically extended size,
Automatically extended files can set the maximum file size and the amount of each extension.
2. log files
Innodb log files are similar to Oracle's redo logs:
You can set up multiple log groups (minimum of 2),
Sequential writes using round-robin strategies,
Even in older versions, there are log archiving features like Oracle.
Note:Don't delete all InnoDB log files, because it is possible to crash your database, fail to start, or lose data.
Innodb can fully restore the database crash the time it was done with the redo log, but had not yet been able to write the data to disk.
Partially completed and write-to-disk incomplete transactions are rolled back and the data is restored.
All parameters of the Innodb are basically prefixed with "innodb_",
The InnoDB storage engine in MySQL can be masked only by parameters (Skip-innodb),
This way, even if we install the InnoDB storage engine in the installation of the compilation, the user cannot create the InnoDB table.
