mysql--Storage Engine

Storage Engine: Also known as the table type, refers to the data table storage mechanism, indexing scheme and other ancillary related functions. Different engines, due to different processing methods, will bring different functions or corresponding optimization, according to the actual needs to choose a reasonable engine.

Criteria for choosing the storage engine for tables:
Whether transaction support is required.
Whether foreign keys are supported.
The requirement for concurrent volume.
Handling of error recovery.
Some specific features are supported.

The most common storage engines:
MyISAM, Indexed sequential access method (indexed sequential access methods)
InnoDB
Difference:
Data and index saved files are different: MyISAM is saved separately, while InnoDB is saved to table space

MyISAM supports index compression, while InnoDB indexes and data are bound to save uncompressed, large volumes.

InnoDB are often row-level locks, while myisam are table-level locks, innodb concurrency is high.

InnoDB does not support fulltext types of indexes (the new version of InnoDB is also supported).

InnoDB support transactions, foreign keys, data integrity constraints are stronger. And MyISAM does not support it.

The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.

For fields of type auto_increment, InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields.

The MySQL storage engine is InnoDB by default
It can be found in the My.ini (path:. \mysql\my.ini) configuration file in MySQL, such as:

You can specify a table's storage engine when you create a table or edit a table
Use Table properties: Engine Type
ALTER TABLE Tb_name engine type

Specify the storage engine when creating the table:

The difference between InnoDB and MyISAM:

Save files in different ways
MyISAM a table, three files
TB_NAME.FRM Table Structure File
Tb_name. MYD data file (storage engine)
Tb_name. MYI Index

INNODB a Table A file
Tb_name.frm

All InnoDB tables Use the same InnoDB storage space in the Save Data and index (... \mysql\data\ibdata1)

As the database grows, the InnoDB ibdata1 file in the data directory may be more than one, but will not differentiate between tables

Another: During the installation of MySQL files, the configuration of the MySQL process will also be a step to prompt the installation path of the Tablespace file