Database of MySQL Storage engine introduction

1 Introduction to Storage Engine Concepts

The storage engine is how to realize how to store the data, how to index the stored data and how to update and query the data. The storage engine in the database is actually a set of tables that use the engine, and the table in the database sets what storage engine, so the table has different "effects" in terms of how the data is stored, how the data is updated, the performance of the data query, and whether it supports indexing.

Improve the overall functionality of your server's applications by choosing a different storage engine to get extra speed or functionality. For example, if you are working on a large amount of temporary data, you may need to use the memory MySQL storage engine. The memory storage engine can store all the tabular data in memory. Alternatively, you might need a database that supports transactional processing (to ensure that the data is backed up when transaction processing is unsuccessful). These different technologies and associated functions are called storage engines (also known as table types) in MySQL. MySQL has a number of different storage engines configured by default and can be pre-set or enabled in MySQL server. You can choose the storage engine for servers, databases, and tables to provide you with maximum flexibility when choosing how to store your information, how to retrieve it, and what performance and features you need to combine with your data.

2 MySQL Concept introduction

Plug-in storage engine is one of the most important features of MySQL database, the user can choose how to store and index data, use transaction, etc. according to the needs of the application.

Data in MySQL is stored in files (or memory) in a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels, and ultimately providing a wide range of different capabilities and capabilities. In MySQL, these different technologies and associated functions are called storage engines.

Use the command show engines to view the supported storage engines in MySQL. The storage engines supported by MySql5.0 include MyISAM, InnoDB, BDB, Memory, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CVS, blackhole, federated, etc. Only InnoDB and BDB provide transaction security tables, and other storage engines are non-transactional security tables.

If you do not specify a storage engine when you create a new table, the system uses the default storage engine, MySql5.5 before the default storage engine is myisam,5.5 and then InnoDB. If you want to modify the default storage engine, you can set default-table-type in the parameters file.

MySQL Branch mainstream has the following three kinds:

Mysql-->oracle Corp.

MARIADB: True open source MySQL, learn from the results of Community research and development, storage engine for Aria, is an enhanced version of MyISAM, can do a crash after the safe recovery, MARIADB engine has the following three:

Myisam-->aria

Innodb-->xtra (Percona company does patch to InnoDB, so better performance)

TOKUDB: Supports mass storage

Percona-server (another branch of the MySQL database issued by Percona Corporation)

3 Mysql Configuration File Introduction

View the order of the configuration files, the same configuration item appears in multiple profiles, the configuration appears after it takes effect, and you can define the path to the additional loaded configuration file with the option Defaults-extra-file or--defaults-file

[[Email protected] script] #my_print_defaults--verbose

Default options is read from the following files in the given order:

/ETC/MYSQL/MY.CNF/ETC/MY.CNF ~/.my.cnf

Example

Common configuration

Vim/etc/my.cnf.d/server.cnf

[Server]

Skip_name_resolve = on #跳过名称解析

Innodb_file_per_table = on #每表使用独立的表空间

Max_connections = 20000 #最大并发连接数

Each database is equivalent to a subdirectory under path/var/lib/mysql/, such as creating a database for sunny, then a directory of sunny is generated under the path/var/lib/mysql/, but not directly in the directory/var/lib/mysql/ Create a subdirectory, or you will get a problem, you cannot generate a new database

4 InnoDB Engine Introduction

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. Compared with the traditional ISAM and MyISAM, InnoDB's greatest feature is the support for acid-compatible transaction (Transaction) functions, similar to PostgreSQL.

InnoDB is a transactional storage engine that is suitable for scenarios where transaction requirements are high, but is more suitable for dealing with a large number of short-term transactions (i.e. small transactions, that is, the processing of a few statements, such as more than 10 statements);

Support high concurrency based on MVCC (Mutli Version Concurrency Control), support four isolation levels, default level is Repeatable-read, Gap lock to prevent phantom reading;

Use a clustered index (primary key index), and the index and content together. Generally ordered, clustered index can only one, other indexes are nonclustered, MyISAM no clustered index

Support "Adaptive hash Index", key value data, i.e. KV

Lock granularity: Row level lock, Gap lock, relational database write lock is exclusive;

Row-level Lock: InnoDB changes one row only, row-level strength. MyISAM Modify a row to lock down the entire table

Gap Lock: Lock the line and line of the direct gap, such as the query is two lines of information, there is a need to insert a new record between the two rows, here the gap lock, is to lock the gap between the two rows, so that the gap can not be inserted between two rows

Example: Interpretation of a transaction

transaction type, such as a account minus 80,b account add 80, but in case the system is abnormal, a minus after B has not added, this is not allowed, or the system is normal, B plus 80, or a minus 80 is restored, this is based on transaction completion

Transactions can be interleaved and parallel, but consider the isolation

View the status information for all tables (table properties), as in the following statement, \G will display the contents vertically, and each row will be displayed once, and if there are more than one line of information, multiple

MariaDB [sunny]> Show Table status\g;

Physically, the InnoDB table consists of a shared tablespace, a log file group (redo filegroup), and a table structure definition file. If Innodb_file_per_table is set to ON, a TABLE_NAME.IBD file is generated for each table, in which data, indexes, and internal data dictionary information about the table are stored. The table structure file ends with a. frm, which is independent of the storage engine.

Data is stored in tablespace (table space) in two ways:

(1) Data and indexes of all tables of InnoDB in all databases are stored in the same table space;

Tablespace file: DataDir defined in the directory, such as the default/var/lib/mysql/path, behaves as Ibdata1, Ibdata2, ... The number of files and the number of tables does not necessarily correspond

(2) Innodb_file_per_table=on, which means that each table uses a separate tablespace file;

Data files (data and indexes, stored in database directories) for each table are stored in a dedicated tablespace file and stored in the database directory: TBL_NAME.IBD (table name. ibd) If the table is classlist under Database sunny, the tablespace file is/var/lib/ Mysql/sunny/classlist.ibd.

table Structure definition : In the database directory, tbl_name.frm (table name. frm), such as/VAR/LIB/MYSQL/SUNNY/CLASSLIST.FRM, which is stored in tabular format

IBDATA1 Introduction

InnoDB system tablespace File Ibdata1 holds three kinds of data:

A) rollback segment

b) All InnoDB table metadata information (This is why InnoDB cannot directly copy the table definition file (table name. frm) and the table data file (table name. ibd) to another library, as the MyISAM table does, because there is also some metadata information in the Ibdata1 file)

c) Double Write,insert buffer dump, etc.

innodb_file_per_table Introduction

When Innodb_file_per_table is turned on, it produces the table definition file table name. frm, and table data file table name. IDB, so that data for each table will exist in your. idb file, and if you close, all data will exist in the system tablespace file Ibdata1 file. This will ibdata1 very busy and bloated, and ibdata1 can not be shrunk, such as a large table dropped on the line, the ibdata1 can not be automatically reduced (need to use Optimiza table to optimize), and if enabled, the data exists in the. IDB file, It can be reduced at any time;

Advantages and disadvantages of shared tablespace and stand-alone table spaces

Shared tablespace and exclusive tablespace can be converted by parameter innodb_file_per_table, or, if 1, exclusive tablespace is turned on, otherwise shared table storage is turned on.

Independent table spaces are significantly more efficient than sharing in situations where server resources are limited and single-table data is not particularly high. However, MySQL defaults to a shared table space.

The advantages and disadvantages of specific shared tablespace and stand-alone table spaces are as follows:

To share a table space:

Advantages:

You can put the table space into multiple files on each disk (the Tablespace file size is not limited by the size of the table, such as a table can be spread over the files on the different steps). Data and documents are easily managed together.

Disadvantages:

All the data and indexes are stored in a file that will have a very large file, although it is possible to divide a large file into smaller files, but multiple tables and indexes are stored in the table space, so there will be a lot of voids in the table space after a large number of deletions for a table, especially for statistical analysis, Such applications as day-value systems are most unsuitable for sharing table spaces.

Stand-alone table space:

Advantages:

1. Each table has a self-contained table space.

2. The data and indexes for each table will exist in the table space themselves.

3. You can implement a single table to move through different databases.

4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)

A) The drop table operation automatically reclaims the tablespace, if for statistical analysis or a daily value table, delete a large amount of data can pass: ALTER TABLE TableName ENGINE=INNODB;

b) The use of TRUNCATE table for Innodb-plugin InnoDB also shrinks the space.

c) For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much and there is a chance to process it.

Disadvantages:

Single table increased too large, such as more than 100 g.

Precautions:

※ for innodb_file_per_table-enabled parameter options, only data, index and insert buffers are stored in the. IDB file for each table, and undo information, system transaction information, two write buffers, etc., are stored in the original shared table space.

※ The data segment is the leaf node of the B + tree, and the index segment is the non-indexed node of the B + tree.

The management of the ※INNODB storage engine is done by the engine itself, and the table space consists of scattered pages and segments.

※ The area consists of 64 consecutive pages, each page size is 16K, that is, each zone size is 1MB, when creating a new table, there are 32 pages of fragmented pages to hold the data, the application after the use of the zone, (InnoDB up to 4 zones per request, to ensure the sequential performance of data)

※ Page types are: Data page, undo page, System page, transactional data page, insert buffer bitmap page, and insert buffer free list page.

InnoDB Summary: Characteristics of InnoDB

Data storage: Table space;

Concurrency: MVCC, Gap Lock, row-level lock;

Index: Clustered index, secondary index;

Performance: Pre-read operation, memory data buffer, memory index cache, Adaptive hash index, insert operation buffer;

Backup: Support hot standby; Backup is a key knowledge.

View status information for the storage engine, such as the status of InnoDB

SHOW ENGINE INNODB STATUS;

5 MyISAM Engine Introduction

Features of MyISAM:

Supports full-text indexing (fulltext index), compression, spatial functions (GIS), which are features not supported by InnoDB

Transaction not supported

Lock granularity: Table-level lock

Crash does not guarantee table security recovery

Application scenario: read-only (data Warehouse) or read more write less scenes, smaller tables (to ensure that the recovery after the crash time is shorter);

Files: Three files per table, stored in a database directory

TBL_NAME.FRM: tabular definition; constraints, data structures

Tbl_name. MYD: Data files;

Tbl_name. MYI: Index file;

Example

Creating the table Tbl1, specifying the engine as MyISAM, generates three tables tbl1.frm TBL1 under the path/var/lib/mysql/sunny. MYD TBL1. MYI

CREATE TABLE Tbl1 (ID int,name char (4)) Engine=myisam;

Characteristics:

Locking and Concurrency: table-level locks;

FIX: Manual or automatic repair, but may lose data;

Index: Nonclustered index;

Delayed index updates;

Table compression;

InnoDB supports online transactions, MyISAM supports read-only data warehouses or read-write-less scenarios

6 Other storage engines

CSV: The CSV file (a comma-delimited text file) as the MySQL table file;

Mrg_myisam: Merging multiple MYISAM tables into a virtual table;

Blackhole: Black holes, similar to/dev/null, do not really store data;

Memory: Storage engine, support hash index, table level lock, often used for temporary table, not persistent storage

Federated: A storage Engine interface for accessing tables on other remote MySQL servers;

MARIADB additional support for a variety of storage engines:

Oqgraph, Sphinxse, Tokudb, Cassandra, CONNECT, Squence 、...

InnoDB content refer to the following content

Https://www.cnblogs.com/benshan/archive/2013/01/08/2851714.html

Https://www.cnblogs.com/Aiapple/p/5689634.html

Https://baike.baidu.com/item/innodb/8970025?fr=aladdin

Database of MySQL Storage engine introduction