Architecture and History of MySQL

The main feature of MySQL is its storage engine architecture, which separates query processing from the storage/extraction of other system tasks and data.

MySQL top-level services are some such as connection processing, authorization authentication, security, etc.

MySQL's core service capabilities are mostly in the second tier architecture. Including query parsing, analysis, optimization, caching, and all of the built-in functions, all of the capabilities across the storage engine are implemented at this level: stored procedures, triggers, and views.

The third layer contains the storage engine. The storage engine is responsible for the storage and extraction of data in MySQL. The server communicates through the API Fish storage engine.

MySQL parses the query and creates an internal data structure (parse tree) and then optimizes it, including rewriting the query, determining the table's reading order, and selecting the appropriate index number. The user can influence the decision-making process by using a special keyword to prompt the optimizer. You can also request the optimizer to interpret the various factors of the optimization process so that users can know how the server is making optimization decisions and provide a reference base for users to refactor queries and schemas. The optimizer does not care what storage engine is used, but the storage engine affects the optimizer. The optimizer requests the storage engine to provide capacity or a specific overhead information, as well as statistics on table data, and so on. For a SELECT statement, the server checks the query cache before parsing the query, and if it can find the corresponding query, the server does not have to perform the entire process of query parsing, optimization and execution, but returns the results directly from the query cache.

When dealing with concurrent reads or writes, the problem is solved by implementing a locking system consisting of two types of locks. These two types of locks are often referred to as shared locks and exclusive locks (exclusive lock), also called read locks, and write locks (write lock). Read locks are shared and not mutually exclusive. Multiple customers can simultaneously read the same resource at the same time without interfering with each other. Write locks are exclusive, and a write lock blocks other write and read locks, and only one user can execute them.

The lock policy seeks to balance the cost of the lock and the security of the data. Most databases impose row-level locks on the table.

Table locks are the most basic lock policy in MySQL, and are the least expensive policies. It locks the entire table. The Read local table lock supports some concurrent operations and writes the lock up to a higher priority than the read lock.

Row-level locks can support concurrent processing to the greatest extent while also having a large lock overhead. Row-level locks are implemented only at the storage engine level.

A transaction is a set of atomic SQL queries or a separate unit of work. Start a transaction with start transaction, either commit the thing with commit, or undo all changes with rollback.

Four features of a transaction:

Atomicity (atomicity): A transaction must be considered an indivisible minimum unit of work, and all operations in the entire transaction are either committed successfully or all failed to rollback.

Consistency (consistency): A database is always transitioning from one consistency state to another consistent state.

Isolation (Isolation): Changes made to a transaction are not visible to other transactions until the final commit.

Persistence (Durability): Once the data is submitted, the changes are persisted to the database.

If the storage engine does not support transactions, you can provide a degree of protection to the application through the Lock table statement.

There are four isolation levels defined in the SQL standard, each of which specifies the modifications made in a transaction, which are visible within the transaction, and which are not. Lower-level isolation can typically perform higher concurrency and lower system overhead.

READ UNCOMMITTED (for commit): Changes in the transaction, even if not committed, are visible to other transactions. A transaction can be read as committed data that is called dirty read.

Read COMMITTED: At the beginning of a transaction, you can only see the changes made by the conception submission. Any modifications made to a transaction from the beginning to the commit are not visible to other transactions. Also called non-repeatable read.

REPEATABLE READ (Repeatable Read): This level guarantees that the same transaction in China can read the same record multiple times as a result. A repeatable level produces a phantom read, that is, when something is reading a range of records, another transaction is inserting a new record within that range, and when the previous transaction reads the record for that range again, a magic line is generated. Repeatable Read Poems MySQL's default transaction isolation level.

SERIALIZABLE (serialized): SERIALIZABLE is the highest isolation level. It avoids phantom reads by forcing the transaction to execute serially. Serializable locks on every row of data that is read, resulting in a large number of timeouts and lock contention issues.

A deadlock is a vicious cycle in which two or more transactions occupy each other on the same resource and request a lock on the resource occupied by the other. The database system realizes the deadlock detection and deadlock timeout mechanism. InnoDB currently handles deadlocks by rolling back transactions that hold the fewest row-level exclusive locks. The behavior and order of the locks are related to the storage engine. There are two reasons for deadlocks: true data collisions and the way the storage engine is implemented.

Using the transaction log, the storage engine only needs to modify its memory copy when modifying the table's data, and then log the modification behavior to the transaction log that is persisted on the hard disk. The transaction log is appended, so the write log operation is sequential I/O within a small area of the disk. After the transaction log is persisted, the in-memory modified data can be slowly brushed back to disk in the background. It is often called a write-in log, which requires two logs to modify the data.

MySQL provides two types of transactional storage engines: InnoDB and NDB Cluster.

MySQL defaults to auto-commit mode. If you do not explicitly start a transaction, each query is treated as a transaction to perform the commit operation. The autocommit variable can be used to enable or disable autocommit mode in the current connection

SET autocommit = 1/0; (1 means enabled, 0 is forbidden)

MySQL can set the isolation level by using the SET TRANSACTION isolation Levels command.

SET SESSION TRANSACTION Isolation Level READ commited

　　

The InnoDB uses a two-phase locking protocol. Locks can be executed at any time during the execution of a transaction, and locks are released only when a commit or rollback is executed, and all locks are released at the same time. These locks are implicitly locked, and InnoDB automatically locks up when needed, depending on the isolation level. MySQL also supports the lock tables and unlock tables statements, which are implemented at the server level. In addition to disabling autocommit in the transaction, you can use the lock table, and you do not need to explicitly execute the lock table at any other time.

　　

　　

MVCC (Multi-version concurrency control) is achieved by saving a snapshot of the data at a point in time.

The MVCC of InnoDB is achieved by saving two hidden columns after each row of records. A saved row creation time, a save line expiration time, this time is not the real time, but the system version number. Each start of a new transaction, the system version number is automatically incremented. The system version number of the transaction start time is the version number of the transaction.

SELECT

InnoDB checks each row of records according to the following two criteria:

A.innodb only data rows with versions older than the current transaction version are found

B. The deleted version of the row is either defined or larger than the current transaction's version number

INSERT

InnoDB Save the current system version number as the row version number for each newly inserted row

DELETE

InnoDB saves the current system version number as the deletion identity for each row deleted

UPDATE

InnoDB to insert a new record for each row, save the current system version number to the line version number, while saving the current system version number to the original behavior as row delete identity

The MVCC works under the two isolation levels of repeatable read and Read Committed.

MySQL saves each database as a single word directory 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. In Windows, file name capitalization is not sensitive and is case sensitive in Unix class. You can use information about the Show Table status Reality table.

InnoDB Storage Engine

InnoDB is the default transactional engine for MySQL. It is designed to handle a large number of short-term transactions. InnoDB's performance and automatic crash recovery features make it popular in non-transactional storage requirements.

InnoDB data is stored in a table space, which is a black box managed by InnoDB and consists of a series of data files. InnoDB can store the data and indexes of each table in a separate file. InnoDB can also use bare devices as storage media for table spaces.

The InnoDB employs MVCC to support high concurrency and achieves four standard isolation levels. The default level is repeatable read, and the presence of Phantom reads is prevented by the gap lock (next-key locking) 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.

The occurrence of the gap lock is most concentrated in the same transaction first delete after the insert case, when we pass a parameter to delete a record, if the parameter exists in the database, this time produces a normal row lock, lock this record and then delete, finally release the lock. If this record does not exist, then when the delete is executed to obtain a gap lock, the database will be scanned to the left to a drop than the given value parameter small value, to the right scan to the first value larger than the given parameter, and then as a boundary, build an interval, lock the entire range of data. This can easily cause deadlocks.

InnoDB tables are based on clustered indexes, and clustered indexes have a high performance on primary key queries. However, its level two index must contain a primary key column. Therefore, if the index on the table is relatively long, the primary key should be as small as possible. InnoDB supports hot backup.

MyISAM

MyISAM provides full-text indexing, compression, spatial functions, and so on, but MyISAM does not support transactional and row-level locks and cannot be recovered safely after a crash.

MyISAM will store the table in two files: Data files and index files, respectively. MyD and. Myi are extensions. MyISAM tables can contain dynamic or static rows. The number of row records that the MyISAM table can store, typically limited by the amount of disk space available, or the maximum size of a single file for the operating system China year. In MySQL 5.0, if the MyISAM table is time-varying, the default configuration can handle only 256TB of data, because the pointer to a data record is 6 bytes long. All MySQL versions support a 8-byte pointer. To change the length of the MyISAM table pointer, you can do so by modifying the values of the table's Max_Rows and avg_row_length options.

MyISAM characteristics

Locking and Concurrency: MyISAM locks the entire table instead of the row. All tables that need to be read are shared with the read, and exclusive locks are added to all tables upon writing. A read can also insert a new record, called a concurrent insert (concurrent insert).

FIX: For MyISAM tables, MySQL can perform check and repair operations manually or automatically. Repairing a table can result in some data loss, and the repair operation is slow. You can check the table for errors by checking tables mytable, and if you have errors you can fix them by executing repair table mytable. If MySQL is turned off, the MYISAMCHK command line tool can be used to check and repair the operation.

Index attributes: For MyISAM tables, you can create an index based on the first 500 characters, even when the Blob and text fields are long. MyISAM supports full-text indexing.

Delay Update index key (delay key Write): When you create a MyISAM table, if you specify the Delay_key_write option, the modified index data is not written to the disk immediately upon completion of the modification execution, but it is written to the in-memory key buffer (in-memory key Buffer), the corresponding index block is written to disk only when the key buffer is cleared or the table is closed. This can improve performance, but when the database or host crashes, it causes index corruption and requires a repair operation.

If the table is not modified after the data is created and imported, the table is suitable for use with MyISAM compression tables. You can use Myisampack to compress the MyISAM table. When you compress a table, you cannot modify it, and you can significantly reduce disk space consumption, which can reduce disk I/O and improve query performance. The compression table also supports indexing, but the index is only read-only. The records in the table are compressed independently, so it is not necessary to extract the entire table when reading a single line.

Archive engine

The archive storage engine supports only insert and select operations. The archive engine caches all writes and uses zlib to compress the inserted rows, so there is less disk I/O than the MyISAM table. But every select query needs to perform a full table scan. The archive table is suitable for log and data collection applications, which often require full table scanning for data analysis. The archive engine supports row-level locks and dedicated buffers. The archive engine organizes other select executions to achieve consistent reads until a query returns all the rows that exist in the table. Archive also implements bulk inserts that are not visible to read operations before they are completed. But the archive engine is not a transactional engine, but a simple engine optimized for telling insertions and compression.

　　

Blackhole Engine

The Blackhole engine does not implement any storage mechanism and discards all inserted data without any saving. However, the server logs the Blackhole table, so it can be used to replicate the database to the backup repository. This storage engine can play a role in some special replication architectures and log audits.

　　

CSV engine

The CSV engine can handle the normal CSV file as a MySQL table, but the table does not support indexing. The CSV engine can copy or copy files while the database is running. The CSV engine can be used as a mechanism for exchanging data.

　　

Federated engine

The federated engine is a proxy that accesses other MySQL servers, creates a client connection to a remote MySQL server, transfers the query to the remote server for execution, and then extracts or sends the required data. The default is disabled.

Memory engine

If you need to access the data quickly, and the data is not modified, it doesn't matter if the reboot is lost later, you can use the Memory table (heap table). All the data in the memory table is stored in RAM and does not require disk I/O. The structure of the memory table is retained after a reboot, but the data is lost.

Application scenarios for memory meters:

Used to find (lookup) or map (mapping) tables

Results for caching periodic aggregated data (periodically aggregated)

Used to hold intermediate data generated in the data analysis

The memory table supports hash indexes. Memory table is a table-level lock, so concurrent write performance is low. A blob or text type of column is not supported, and the length of each row is fixed, so even if varchar is specified, the actual storage is also char. If MySQL is in the process of executing the query China needs to use temporary tables to hold intermediate results, the internal use of temporary tables when the memory table. If the result is too large to exceed the memory table limit or contain a blob or text field, the temporary table is converted to the MyISAM table.

Merge engine

The merge table is a virtual table that is merged by multiple MyISAM tables.

　　

NDB Cluster engine

As an excuse for SQL to NDB native protocols directly. MySQL server, NDB cluster storage engine, and the combination of distributed, share-nothing, disaster-tolerant, high-availability NDB databases are known as MySQL clusters.

　　

MySQL is line-oriented by default, and the data for each row is stored together, and the server queries are handled in a behavioral unit. Column-oriented methods can be more efficient in large data processing. Infobright is designed for data analysis and data warehouse applications. Data is compressed, sorted by block, and each block should have a set of metadata. When you process a query, the access metadata determines whether the block is skipped, or even the metadata can be used to meet the query requirements. However, the engine does not support indexing. Its own block structure is a quasi-index (quasi-index).

Selection of storage engines

Most of the time, InnoDB is the right storage engine option. If you need to use full-text indexing, consider the combination of InnoDB plus sphinx instead of selecting a MyISAM that supports full-text indexing.

The storage engine needs to be selected from the following angles: transactions (if transactions are supported, InnoDB is the best option, and if transactions are not required and are dominated by select and delete operations, then MyISAM is a good choice), backup, crash recovery, and unique features.

If the amount of data continues to grow to a level above 10TB, you may need to establish a data warehouse. Infobright is the most successful solution for MySQL Data Warehouse

Transforming the storage Engine

ALTER TABLE

ALTER TABLE mytable enigine = InnoDB;

The statement executes for a long time. MySQL will copy data from the original table to a new table, which may consume all of the system's I/O capability during replication and read the lock on the original table. and all engine-related features are lost.

Export Import

You can use the Mysqldump tool to export the data to a file and then modify the storage engine options for the CREATE TABLE statement in the file, noting that the table name is also modified. Mysqldump automatically adds a drop TABLE statement before the CREATE TABLE statement.

Create and query

Create a new storage engine table first, and then take advantage of the insert ... Select statement to pour the data

CREATE TABLE innodb_table like myisam_table;

ALTER TABLE innodb_table Engine=innodb;

INSERT into innodb_table SELECT * from myisam_table;

The large amount of data can be considered to do batch processing, for each piece of data to perform transaction commit operations, so as not to avoid large things caused excessive undo.

　　　　

　

Architecture and History of MySQL