mysql--Storage Engine

Core Knowledge points

1.InnoDB: Data and indexes are stored in separate files, clustered indexes, row-level locks, transactions, MVCC

2.MyISAM:

(1) Cons: Transaction and table-level locks are not supported because table locks are not supported and lock particles are larger, so they are suitable for read-only and small files.

(2) Files: Data files and index files to. MyD and. Myi end.

(3) Compression table: The compression table can not be modified to reduce disk consumption, can also reduce disk I/O, support index, so read-only, record alone, to go to a single row does not need to extract the entire table.

3.memory: Do not need disk I/O, query speed is very fast, using table-level locks, concurrent write ability is low, temporary table in the system is to use memory.

4.Archive:

(1) Row-level locks and private buffers, so high concurrent writes are supported.

(2) Only insert and Select,select are supported for full table scan, and only support serial check, when a person to check the time will block other people's queries.

(3) All written rows are compressed, so there is less disk I/O.

(4) Suitable for log and data collection

In the file system, MySQL saves each database (also known as a schema) as a subdirectory under the data directory.

When you create a table, MySQL creates a. frm file with the same name as the table in the database subdirectory to save the table definition.

MySQL uses file system directories and files to save database and table definitions, case sensitivity and specific platforms are closely related.

One, InnoDB storage engine

InnoDB is the default transactional engine for MySQL and the most important and extensive storage engine.

It is designed to handle a large number of short-term transactions, and most of the short-term transactions are normally committed and will be rolled back well.

InnoDB's performance and auto-recovery features make it popular in non-transactional storage requirements.

Unless there are specific reasons to use other storage engines, the InnoDB engine should be a priority.

InnoDB data is stored in a table space (tablespace) , which is a black box managed by InnoDB and consists of a number of columns of data files .

In later versions of MySQL4.1, InnoDB can store data and indexes for each table in a separate file .

InnoDB can also use bare devices as a storage medium for table spaces, but modern file systems make bare devices no longer a necessary option.

The InnoDB employs MVCC to support high concurrency and achieves four standard isolation levels . Its default level is REPEATABLE READ (repeatable read), and the presence of Phantom reads is prevented by the gap lock policy.

The gap lock allows the InnoDB not only to lock the rows involved in the query, but also to lock gaps in the index to prevent the insertion of phantom rows.

InnoDB tables are built on clustered indexes . The index structure of InnoDB is very different from that of other storage engines in MySQL, and clustered indexes have high performance on primary key queries .

However, its two-level index (secondary index, non-primary key indexes) must contain primary key columns, so if the primary key column is large, all other indexes will be large.

Therefore, if there are more indexes on the table, the primary key should be as small as possible.

The INNODB storage format is platform independent, meaning that data and index files can be copied from the Intel platform to the PowerPC or Sun SPARC platform.

Many optimizations are made within the InnoDB, including the predictable read-ahead reading from disk, the ability to automatically create a hash index in memory to speed up the adaptive hash index of the read operation, and the insertion buffer that accelerates the insert operation.

Second, MyISAM storage engine

In MySQL5.1 and previous versions, MyISAM is the default storage engine.

MyISAM provides a number of features, including full-text indexing, compression, spatial functions, and so on, but MyISAM does not support transactional and row-level locks , and there is no doubt that a failure to recover safely after a crash is not possible.

Although the MyISAM engine does not support transactions and does not support post-crash security recovery, it is not useless.

For read-only data, or if the table is small and can tolerate repair operations, you can still continue to use MyISAM.

(1) Storage

MyISAM will store the table in two files: data files and index files, respectively. MyD and. Myi are extensions .

The MyISAM table can contain dynamic or static (fixed-length) rows. MySQL determines which row format to use based on the table definition.

MyISAM the number of row records that a table can store, typically limited by the available disk space, or the maximum size of a single file in the operating system.

In MySQL5.0, if the MyISAM table is a variable-length row, the default configuration processes only 256TB of data, because the pointer to the data record is 6 bytes long.

In earlier versions, the pointer length was 4 bytes by default, so only 4GB of data could be processed. All MySQL versions support a 8-byte pointer.

To change the length of the MyISAM table pointer (up or down), you can do so by modifying the values of the table's Max_Rows and avg_row_length options, which is the maximum size the table can reach.

Modifying these two parameters causes the entire table and all indexes of the table to be rebuilt.

(2) Features

As one of MySQL's earliest storage engines, MyISAM has some features that have been developed for many years to meet the actual needs of users.

Locking and concurrency

MyISAM locks the entire table instead of the row. A shared lock is added to all tables that need to be read, and an exclusive lock is added to the table when it is written.

However, when a table has a read query, you can also insert a new record into the table.

Repair

For MyISAM tables, MySQL can perform check and repair operations manually or automatically, but the fix and transaction recovery mentioned here are different concepts.

Repairing a table can result in some data loss, and the repair operation is very slow. You can check the table for errors by checking tables mytable, and if there are errors you can fix them by executing repair table mytable.

In addition, if the MySQL server is turned off, you can also check and repair through the MYISAMCHK command-line tool.

Index attributes

For a MyISAM table, you can create an index based on its first 500 characters, even if it is a long field such as BLOB and text.

MyISAM also supports full-text indexing, which is a segmentation-based index that can support complex queries.

(3) MyISAM compression table

If the table is not modified after the data is created and imported, then such a table or a MyISAM compression table is used.

You can use Myisampack to compress the MyISAM table (also called a pack). A compressed table cannot be modified (unless you first unsuppress the table, modify the data, and then compress again).

Compressing tables can significantly reduce disk space consumption , so you can also reduce disk I/O, which improves query performance .

The compression table also supports indexes, but the indexes are also read-only. With today's hardware capabilities, the cost of extracting data from compressed tables is not significant for most scenarios, and the benefits of reducing I/O are much greater.

The records in the table are compressed independently , so there is no need to decompress the entire table when reading a single line (or even the entire page where the row is pressed).

(4) MyISAM performance

The MyISAM engine is designed to be simple, data is stored in a compact format , so performance is good in some scenarios.

MyISAM has some server-level performance scaling restrictions, such as a mutex lock on the index key buffer (key cache), and mariadb the segment-based index key buffer mechanism to avoid the problem.

But MyISAM the most typical performance problem is the table lock problem, if you find all the queries are in the "Locked" state for a long time, then there is no doubt that table lock is the culprit.

Third, Memory engine

It is useful to use the memory table if you need fast access to the data, and the data is not modified, and restarts are lost later.

The memory meter is at least one order of magnitude faster than the MyISAM table because all of the data is stored in memory and does not require disk I/O.

The structure of the memory table is retained after a reboot, but the data is lost.

Memory meters can play a good role in many scenarios:

(1) for lookup (lookup) or mapping (mapping) tables

(2) User cache results of periodic aggregated data

(3) User-saved intermediate data from data analysis

The memory table supports hash indexes, so the query operation is very fast . Although the memory meter is very fast, it cannot replace a traditional disk-based table.

The memory table is a table-level lock , so the performance of concurrent writes is low .

It does not support BLOB or text type columns, and the length of each row is fixed, so even if a varchar column is specified,

The actual storage is also converted to char, which can result in some wasted memory.

If MySQL needs to use temporary tables to hold intermediate results while executing the query, the temporary table used internally is the Menory table.

If the intermediate result is significantly beyond the memory table limit, or if it contains a blob or text field, the temporary table is converted to the MyISAM table.

Four, NDB cluster engine

In 2003, MySQL AB acquired the NDB database from Sony Ericsson and then developed the NDB cluster storage engine as an interface between SQL and NDB native protocols.

The MySQL server, the NDB cluster storage engine, and the combination of a distributed, share-nothing, disaster-tolerant, highly available NDB database are known as MySQL clusters.

Five, the CSV engine

The CSV engine can treat a normal CSV file (a comma-separated value file) as a MySQL table, but the table does not support indexing.

The CSV engine can be copied or copied out while the database is running.

You can store the data in a spreadsheet such as execl as a CSV file and then copy it to the MySQL data directory to open it in MySQL.

The data is also written to a CSV engine table, and other external programs can immediately read CSV-formatted data from the table's data file.

So the CSV engine can be useful as a mechanism for exchanging data.

Mysql> Create TableInfo (IDint(4) not NULL, nameChar(Ten) not NULL) engine=csv; Query OK,0Rows Affected (0.12sec) MySQL> Insert  intoInfoValues(1,'Kobe Bryant'); Query OK,1Row affected (0.12sec) MySQL> Insert  intoInfoValues(2,'Yarn'); Query OK,1Row affected (0.00sec) MySQL> Insert  intoInfoValues(3,'Bird'); Query OK,1Row affected (0.00Sec

Effect:

VI. Blackhole Engine

Black engine does not implement any storage mechanism, it discards all inserted data , does not make any guarantee. However, the server logs the log of the Blackhole table, so it can be used to copy data to the repository, or simply log in.

This special storage engine can play a role in some special replication architectures and log audits, but it is not recommended.

Seven, Archive

The archive storage engine supports only insert and select operations and does not support indexing until MySQL5.1.

The archive engine caches all writes and uses ZILB to compress the inserted rows, so there is less disk I/O than the MyISAM table.

However, a full table scan is required for each select query . So the archive table is suitable for log and data collection applications , which often require full table sweeping when doing data analysis. Or it can be used in situations where a faster insert operation is required.

The archive engine supports row-level locks and dedicated buffers , so high concurrent inserts can be implemented, and the archive engine blocks other select executions for consistent reads before a query begins to know all the rows that exist in the returned table.

In addition, the implementation of bulk inserts is not visible until the operation is complete. This operation mimics some of the features of transactions and MVCC, but the archive engine is not a transactional engine, but a simple engine that is optimized for insertion and compression.

mysql--Storage Engine