MySQL (ix)

First, MVCC

1. Introduction

Multi-version Concurrency Control multiple version concurrency controls, most of the MySQL transactional storage engines, such as Innodb,falcon and PBXT, do not use a simple row-locking mechanism. In fact, they are used in conjunction with another mechanism called "multi-version concurrency control (MVCC)", which is used to increase concurrency. MVCC is used not only in MySQL, but also in Oracle, PostgreSQL, and some other database systems. you can consider MVCC as a compromise of row-level locking, which in many cases avoids the use of locks while providing a smaller overhead. Depending on the implementation, it can allow non-blocking reads, locking only the necessary records when the write operation is in progress. MVCC will save a snapshot of the data at a point in time. This means that transactions can see a consistent view of the data, no matter how long they need to run. This also means that the data for the same table that different transactions see at the same point in time may be different. Each storage engine has different implementations for MVCC. Some of these differences include optimistic and pessimistic concurrency control.

2, InnoDB engine implementation principle

InnoDB: Implement MVCC by adding two additional hidden values for each row of records, one to record when this row of data was created, and another to record when this row of data expires (or is deleted). However, InnoDB does not store the actual time at which these events occur, but instead only stores the system version number when these events occur. This is a growing number of transactions as they are created. Each transaction will record its own system version number at the beginning of the transaction. Each query must check whether the version number of each row of data is the same as the version number of the transaction.

3, the advantages of using it

The main advantage of using the MVCC multi-version concurrency control ratio locking model is that in MVCC, the lock requirements for the retrieval (read) data do not conflict with the lock requirements of the write data, so the read does not block the write, and the write never blocks the read. There are also table and row-level locking mechanisms in the database for applications that cannot easily accept MVCC behavior. However, proper use of MVCC always provides better performance than locks.

Ii. Introduction to the MySQL storage engine

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table). there is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.

1. View the System engine

To view the engines supported by the current system lock:

MariaDB [hellodb]> SHOW engines\g;

View information about all tables in the current library, including the storage Engine:

MariaDB [hellodb]> SHOW TABLE status\g;

You can also view the engines used by the specified table:

Syntax: SHOW TABLE STATUS [{from | in} db_name] [like ' pattern ' | WHERE Expr]

Note: Although we can use like or where to select a table, it is recommended to use where to choose, after all, exact match.

2. How to modify the default storage engine

To view the current system default storage engine:

MariaDB [hellodb]> SHOW VARIABLES like ' default_storage_engine '; +------------------------+--------+| variable_name | Value |+------------------------+--------+| Default_storage_engine | InnoDB |+------------------------+--------+1 row in Set (0.00 sec)

If you want to modify the default storage engine, then identify the engine that the current system can support, and then modify it as needed.

3. Description of property information about the table

Examples of Use:

Mariadb [hellodb]> show table status from hellodb where name= ' Students ' \g;*************************** 1. row ***************************            Name: students          Engine: MyISAM        Version: 10      Row_format: Dynamic            rows: 27 avg_row_length: 25    data_length: 680max_data_length:  281474976710655   index_length: 2048      data_free: 0  Auto_increment: 28    Create_time: 2015-08-29 21:54:14     update_time: 2015-08-29 23:05:55     check_time: null      &nbsP Collation: utf8_general_ci       checksum: null create_ options:        comment:1 row in set  (0.00  Sec

The following information about each line is described:

• Name: Table name;

• Engine: storage engines;

• Version: Versions;

• Row_format: Row format.

{default| dynamic| fixed| commpressed| Redundant| COMPACT}

• Rows: The number of rows in the table, which is the exact value in the MyISAM storage engine, is the estimate in the InnoDB storage engine;

• Avg_row_length: The average number of bytes per row;

• Data_length: The total size of the data in the table, in bytes;

• Max_data_length: The maximum space the table can occupy, in bytes, 0 for the upper limit;

• Index_length: The size of the index, in bytes;

• Data_free: For the MyISAM table, represents the space that has been allocated but not yet used, which contains the space vacated by the previously deleted row;

• Auto_increment: The value of the next auto_increment;

• Create_time: The creation time of the table;

• Update_time: The last modification time of the table data;

• Check_time: The time of the last Check table using check table or MYISAMCHK;

• Collation: Sorting rules;

• Checksum: The Checksum of the table if it is started;

• Create_options: Specify additional options to use when creating tables;

• Comment: Annotated information of the table;

III. storage structure of MyISAM and InnoDB tables

1, MyISAM table, each table has three files, are located in the database directory

TB_NAME.FRM: Table Structure definition

Tb_name. MYD: Data files

Tb_name. MYI: Index File

2, InnoDB table, there are two ways of storage

• Default: Each table has a separate file and a multi-table shared file;

TB_NAME.FRM: The definition of the table structure, located in the database directory;

ibdata#: The shared tablespace file, which is located by default in the data directory (the directory that DataDir points to).

• Stand-alone table space:

TB_NAME.FRM: Each table has a table structure file;

TB_NAME.IBD: A unique tablespace file.

Tablespace: Table space, a unique format data file managed by InnoDB, that can store both data and indexes internally.

Iv. characteristics of each storage engine

1, InnoDB

InnoDB, one of MySQL's database engines, publishes one of the binary standards for MySQL AB. InnoDB was developed by Innobase Oy Company and was acquired by Oracle Corporation in May 2006. Here are a few of its main features:

• Transaction

• FOREIGN key

• MVCC

• Clustered index

• Row-level Locks

• Support for secondary indexes: indexes other than clustered indexes, often referred to as secondary indexes

• Support for adaptive hash indexing

• Support for Hot backup

2, MyISAM

MyISAM is the default storage engine. It is based on older ISAM code, but there are many useful extensions. (Note that MySQL 5.1 does not support ISAM). Each myisam is stored as three files on disk. Each file name starts with the name of the table, and the extension indicates the file type. frm file stores the table definition. The data file has an extension of · MYD (MYData).

Here are a few of its main features:

• Full-Text Indexing

• Compression: Used to implement a data warehouse that saves storage space and improves performance

• Spatial index

• Table-Level Locks

• Defer updating indexes

Features not supported:

• Transactions, foreign keys, and row-level locks are not supported

• Unable to recover data safely after a crash

Applicable scenarios:

Read-only data, smaller tables, tolerance for post-crash modification operations, and data loss

3, ARCHIVE

Its main functions:

• Only insert and select are supported, which supports good compression function;

• It is suitable for storing log information, or other application of data acquisition class implemented by time series;

Features not supported:

• Do not support the transaction, the index is not well supported;

4. CSV

Its main functions:

• Store the data in CSV format;

• Suitable for data exchange scenarios only;

Features not supported:

• Index not supported;

5, Blackhole

Its main functions:

• Without a storage mechanism, any data destined for this engine will be discarded;

• It will record the binary log, so it is often used in multi-level replication architecture as a transit server;

6. MEMORY

Its main functions:

• Save data in memory, memory table;

• Often used to save intermediate data, such as periodic aggregation data;

• Used to implement temporary tables;

• Support hash index, use table level lock;

Features not supported:

• Blob and text data types are not supported;

7, Mrg_myisam

Its main functions:

• A variant of MyISAM, capable of merging multiple MyISAM tables into a single virtual table;

8, NDB

Its main functions:

• Dedicated storage engine in MySQL cluster

9, third-party storage engine

OLTP (online transaction manager):

• XtraDB: Enhanced InnoDB, provided by Percona;

• PBXT:MARIADB comes with this storage engine

  • Supports engine-level replication, foreign key constraints, and provides appropriate support for SSD disks;

  • Support Transactions, MVCC

• Tokudb: Using fractal trees Index, it is suitable to store big data, has a very compression ratio, has been introduced mariadb;

Column Storage Engine:

• Infobright: Currently more famous column engine, suitable for Yu Hai volume data storage scenarios, such as PB level, designed for data analysis and data warehouse design;

• Infinidb

• MonetDB

• Luciddb

10. Open Source Community Storage engine

• Aria: Formerly Maria, can be understood as an enhanced version of MyISAM (support for post-crash security recovery, support data caching);

• Groona: Full-text indexing engine, Mroonga is based on the Groona two times development version;

• Oqgraph: Developed by Open query, supports the storage engine of graph structure;

• SPHINXSE: Provides SQL interface for Sphinx full-text Search server;

• Spider: Can data cut into different shards, more efficient and transparent implementation of the Shard (shared), and support on the Shard support parallel query;

11. How to select the storage engine

• Whether a transaction is required

• Support for type of backup

• Post-crash recovery

• Unique Features

Features of several common storage engines

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/95/wKiom1XnCI_DyudxAAHASA5VrHs004.jpg "title=" 5.png " alt= "Wkiom1xnci_dyudxaahasa5vrhs004.jpg"/>

This article is from the "Bread" blog, make sure to keep this source http://cuchadanfan.blog.51cto.com/9940284/1690965

MySQL (ix)