MySQL Storage engine

MySQL Storage engine
Available as pluggable components:
-Responsible for performing the actual data I/O operations for the database, different storage engines, and the way they store the data.
-The user accesses the MySQL database via a connection and avoids large coding changes based on the actual need to choose which storage engine.

The default storage engine:
-mysql 5.0/5.1---> MyISAM
-mysql 5.5/5.6---> InnoDB

List the available storage engine types:
Format: SHOW ENGINES;
or show Engines\g

To modify the storage engine for a table:
When a table is built, the default storage engine is used.
-show CREATE TABLE xxx\g; To view

例：mysql>create table ku.abc(>id int(4),>name char(5)>) engine=innodb;mysql>show create table ku.abc\G;

To change the storage engine for an existing table:
-use alter TABLE to change the engine settings.
Mysql>alter table Ku.abc Engine=myisam;
Msyql>show CREATE TABLE ku.abc\g;

To set the default storage engine:
Modifying a configuration file/etc/my.cnf
Vim/etc/my.cnf
[Mysqld]
....
Default-storage-engine=innodb

Service MySQL Restart

Features of the storage engine
Key hotspots of the MyISAM storage engine:
1. Table-level locking is supported and row-level locking is not supported.
2. Do not support transactions
3. Do not enjoy the table space
4. Multi-purpose tables with more query and insert operations.

Related table files:
Table. MYI Store index Information
Table. MYD Storing data
Table. frm Storage Table structure

Key features of the InnoDB storage engine:
1. Support for table-level/row-level locking
2. Support transactions, transaction rollback, foreign key support
3. Shared table Space

Comments:
Role of Lock: Resolves concurrency access violation issues.
Transactions: The process of data access from start to finish is called a transaction
Transaction rollback: Any step of data access fails to resume all operations.
Transactional characteristics: Consistent atomicity Isolation
Transaction log file: Records the operations performed on the table of the InnoDB storage engine.

Related table files:
Table. frm Storage Table structure
Table. IBD Store index Information + data
Ibdsta1
IB_LOGFILE0, Ib_logfile1

Tables that receive many write operations are suitable for use with the InnoDB storage engine.
Tables that receive many read operations are suitable for use with the MyISAM storage engine.

Size of Lock:
1. Table-level Lock: locks the entire table directly at once.
2. Row-level Lock: Lock only one row
3. Page-level Lock: Locks the entire page (the basic storage unit for MySQL management data).

Lock type:
1. Read lock (Shared lock): Supports concurrent reads.
2. Write lock (mutex, exclusive lock): is an exclusive lock, during the lock other threads can not read the table or write a table.

To view the current lock status:
-Check the variable at the beginning of the Table_lock and% as a wildcard character.
Mysql>show STATUS like "table_lock%"

MySQL Storage engine