MySQL Storage engine

Source: Internet
Author: User
Tags format definition

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.