MySQL storage engine, concurrency control, transaction

The storage engine is a table-level concept, and each table created should indicate its storage engine, and it is not recommended to cross-use the same storage engine for the same database.

InnoDB: The enhanced version is XTRADB,MARIADB, which uses xtradb     to handle a large number of short-term transactions;     data stored in the tablespace (table  space) ";     (1)   All InnoDB table data and indexes are placed in the same table space;          tablespace files: DataDir defined directories under          data files: ibddata1, ibddata2,  ...     (2)   Each table uses a single table space to store the data and indexes of the table;         innodb_file_per_table=onmariadb [(none)]> show global variables like  ' innodb_file_per% '; +-----------------------+-------+| variable_name          | value |+-----------------------+-------+| innodb_file_per_table |  off   |+-----------------------+-------+1 row in set  (0.00 sec) Create T2 Table:mariadb [mydb]> create table t2  (Id int, name char (30)); Query ok, 0 rows affected  (0.01 sec) found T2 table related data      data files (storing data and indexes) under the System data Catalog:tbl_name.ibd,       table format definition: tbl_name.frm[[email protected] mydb]# lsdb.opt  t1.frm The   t2.frm[[email protected] mydb]# pwd/var/lib/mysql/mydb is based on MVCC to support high concurrency, supporting all four isolation levels, The default level is repeatable read;  Gap lock prevents Phantom read;     using clustered index      support "Adaptive Hash Index"      lock Granularity: row-level lock data storage: tablespace concurrency:mvcc,  Gap Lock Index: Clustered index, secondary index performance: expected operation, adaptive hash, insert buffer backup: Support hot standby (Xtrabacup)

MyISAM: Supports full-text indexing (fulltext index), InnoDB does not support, compression, spatial functions (GIS); But does not support transactions, and is table-level locks, row-level locks are not supported; the applicable scenario cannot be recovered safely after a crash: Read-only (or less-written), smaller tables (can accept long-time repair operations) Aria:crash-safe file: tbl_name.frm: Tabular definition Tbl_nam E.myd: Data file tbl_name. MYI: Index file attributes: Locking and Concurrency: table-level Lock Repair: manual or automatic repair, but possibly missing data index: Nonclustered index deferred update index key: Compressed table row format: Dynamic, fixed, compressed, compact, red Undent

This article is from the "After Tomorrow" blog, please be sure to keep this source http://leeyan.blog.51cto.com/8379003/1709447

MySQL storage engine, concurrency control, transaction