01.MySQL Storage Engine

what is a storage engine?The storage engine is a set of programs that is responsible for translating the user's presentation layer (SQL statement) data into the physical layer (database data file) data. MySQL is a plug-in storage engine. What is a plug-in storage engine?MySQL in order to support a more flexible architecture, it put the storage engine into a plug-in, any third party can develop their own plug -in,for the storage engine to complete a bidirectional transformation from the presentation layer to the physical layer, or from the physical layer to the presentation layer. so MySQL has a variety of different storage engines, and the features supported by different storage engines are not the same,Some storage engines support transactions, others do not support, and so on. MySQL supports our free choice of the storage engine used, but Oracle does not support it. Two of the more common storage engines for MySQLThe storage Engine is also known as a table type

TABLE  like ' Table name '\g # can see the property information of a table, and you can see what this table is. Storage Engine

MyISAM Storage Engine (no transaction, table Lock):

MySQL5.5.8 before default storage engineTransaction not supportedMyISAM performance is good, especially suitable for more queries, and less modification of the scene, so it is particularly suitable for data storage. three files per table
. frm: Table structure
        . MYD: Table Data
        . MYI: Table Index
InnoDB Storage Engine (transaction, row lock):MySQL5.5.8 after Oracle acquires MySQL, the default storage engineSupport TransactionsInnoDB is especially suitable for online transaction processing systems (more queries and modifications), such as forums (people post at any time, people see posts)All tables share a tablespace file (not recommended)
Recommendation: A separate tablespace file for each table
. frm: Table structure
. IDB: tablespace (table data and table index)
What to do with the InnoDB engine

# A separate tablespace file per table (recommended) innodb_file_per_table# sets the storage path for InnoDB tablespace files Innodb_data_home_dir ="D:/MySQL5." 5 / InnoDB "

What is db.opt?we found that there is a db.opt file in each database, which is an ASCII text file that defines the character set and collation used by this databaseits content is similar to the following

default - character - Set = GBK default -Collation=gbk_chinese_ci

01.MySQL Storage Engine