First, InnoDB:
deal with a large number of short-term transactions;
data is stored in "table space";
1, the data and index of all InnoDB tables are placed in the same table space;
tablespace file: Under directory defined by DataDir
data file: Ibddata1,ibddata2, ...
2, each table uses a table space alone, storing the table data and indexes;
Innodb_file_per_table=on
data files (storing data and indexes): TBL_NAME.IBD
table format definition: tbl_name.frm
based on MVCC (multi-version Concurrency Control) to support high concurrency, all four transaction isolation levels are supported, the default level is repeatable read, and a gap lock prevents the appearance of Phantom reads;
using Clustered Indexes
support for "adaptive Hash Index"
row-level locks
Data storage: Table Space
Concurrency: MVCC, Gap lock
index: Clustered index, secondary index
performance: Pre-read operation, adaptive hash, insert buffer
Backup: Support hot standby (xtrabackup)
mariadb, although also known as InnoDB, but the use of XTRADB (Percona company provided)
Second, MyISAM:
supports full-text indexing (fulltext index), compression, spatial functions (GIS), but does not support transactions, and is table-level lock;
unable to recover safely after a crash
usage Scenarios: Read-only (or less write), smaller tables (can accept long-time repair operations)
Aria:crash-safe
File:
tbl_name.frm: Table-style definition
Tbl_name. MYD: Data Files
Tbl_name. MYI: Index file
Features:
Locking and Concurrency: table-level locks
FIX: Manual or automatic repair, but may lose data
indexes: Nonclustered indexes
defer updating index keys
Compression Table
row format: Dynamic,fixed,compressed,compact,redundent
third, the other storage engine:
CSV: Use regular CSV (fields separated by commas) as MySQL tables
Mrg_myisam: Merging multiple MYISAM tables into a single virtual table
blackhole: Similar to/dev/null, does not really store any data
Memory : All the data are stored in RAM, Memory table, the only table that supports hash index; table-level lock
Temp Table
Performance_schema: Pseudo-Storage engine
ARCHIVE: Only select and insert operations are supported, row-level locks and private buffers are supported, transactions are not supported
Federated: A proxy for accessing other remote MySQL servers by creating a client connection to a remote MySQL server and transferring the query to a remote server for execution
then complete the data storage, the implementation on the MARIADB is called Federatedx
other storage engines supported by MARIADB:
oqgraph, Sphinxse, Tokudb, Cassandra, CONNECT, SEQUENCE
Iv. compiling and installing MySQL:http://www.cnblogs.com/fansik/p/5270334.html
v. Concurrency control: Lock
Lock Policy:
The equilibrium mechanism sought in lock granularity and data security;
each storage engine can implement its lock policy and lock granularity on its own;
MySQL also implements the lock at server level, table-level lock, user can display the request;
(1) LOCK TABLES
tbl_name [[as] alias] Lock_type
[Tbl_name [[as] alias] lock_type] ...
Lock_type:
READ [LOCAL]
| [Low_priority] WRITE
UNLOCK TABLES
(2) FLUSH TABLES tb_name[,...] [with READ LOCK]
(3) SELECT clase [for UPDATE] [with READ LOCK]
MySQL Storage engine