Shulou(Shulou.com)06/01 Report--

This article mainly introduces what the MySQL architecture is. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

MySQL server architecture, the main differences between various storage engines and the importance of differences

Review the historical background of MySQL, benchmark testing, and discuss the principles of MySQL by simplifying details and demonstrating cases.

Text:

MySQL architecture can be applied in a variety of different scenarios, can be embedded in application farmers, and supports data warehouse, content indexing, deployment software, highly available redundant systems, online transaction processing systems, etc.

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

1.1MySQL logical architecture

1.2 concurrency control lock granularity:

Lock policy: strike a balance between lock overhead and data security, and each storage engine can achieve a specified lock policy and granularity

Table lock: the most basic cost of table lock to lock the entire table

Row-level locking: row lock supports maximum concurrency and maximum lock overhead at the storage engine layer (in its own way)

1.3 transactions

Independent unit of work, a set of atomic SQL queries

Isolation level:

Four, each of which specifies the changes made in the transaction, and lower isolation can perform higher concurrency and lower overhead

READ UNCOMMITTED did not submit read

Changes in transactions are not committed in time and are visible to other transactions; transactions read uncommitted data: dirty reading; rarely used

READ COMMITTED submitted for reading

Almost library defaults to isolation level. Non-MySQL; transactions only see the changes made by committed transactions from the beginning to the end, and the changes made by themselves are not visible to other transactions. Cannot be read repeatedly: if the same query is executed twice, the results may be different (modifications from other transactions)

REPEATABLE READ can be read repeatedly

By default, MySQL solves dirty reading, and the same transaction reads the same result many times; phantom reading: when a transaction reads records in a certain range and another transaction inserts a new record in that range, the current transaction reads the range records and magic rows again.

SERIALIZABLE: serializable

Maximum, force transactions to execute serially, avoid phantom reading problems, lock each row of data (which can lead to a large number of timeouts and lock contention), rarely use

Deadlock

1. Two multiple transactions occupy each other on the same resource and request to lock the resources occupied by each other

2. Multiple transactions try to lock resources in different order, which may lead to deadlock

3. Multiple transactions lock the same resource at the same time

The behavior and order of locks are related to the access engine. Statements are executed in the same order, and some storage engines produce deadlocks and some do not.

There are two reasons for deadlocks: because of real data conflicts (which are difficult to avoid), and because of the way the storage engine is implemented

After the deadlock is sent, the deadlock can be broken only if one of the transactions is partially or completely rolled back: InnoDB, that is, roll back the transaction that holds the least row-level exclusive lock

Transactions in 1.3.4MySQL: storage engine implementation

MySQL two transactional storage engines: InnoDB and NDB Cluster

Automatically submit AUTOCOMMIT

Autocommit mode is used by default. If you do not start a transaction explicitly, each query is performed as a transaction. You can use the AUTOCOMMIT variable to enable = 1 = ON, disable = 0 = OFF (all queries are in a transaction until the explicit commit rollback transaction ends) and start a new transaction at the same time. Modifying this variable has no effect on non-transactional tables.

MySQL can set the isolation level through set transaction isolation level, the new level takes effect at the beginning of the next transaction, the configuration file sets the entire library, or you can only change the isolation level of the current session

Set session transaction isolation level read committed

Advice: do not display execution LOCK TABLES at any time, no matter what storage engine you are using

1.4 Multi-version concurrency Control MVCC

Database MySQL, Oracle, postgresql, etc., all implement MVCC, and their implementation mechanisms are different [source].

MVCC: every read connected to the database sees a snapshot of the database in an instant, and the write operation is not visible to the outside world until it is submitted; [source]

When updating, mark the old data as obsolete and add a new version of the data elsewhere (multiple versions of the data, only one up-to-date), allowing previous data to be read

Features:

1. There is a version for each row of data, which is updated every time the data is updated.

2. When modifying copy, the current version is available and can be modified at will. There is no interference between transactions.

3. Compare the version number when saving. If you succeed in commit, you will overwrite the original record, and if you fail, you will discard rollback.

4. Only work under the isolation levels of REPEATABLE READ and READ COMMITTED

1.5MySQL storage engine

Mysql saves a subdirectory under the bit data directory of each database to create a representation. Mysql creates a .frm file with the same name as the table under the subdirectory to save the definition of the table. Different storage engines store data and indexes in different ways, but the definition of the table is handled in the MySQL service layer.

InnoDB: default transactional engine, most important, widely used

Handle a large number of short-term transactions; its performance and automatic crash recovery features are also popular in the requirements for non-transactional storage

The data is stored in a tablespace managed by InnoDB and consists of a series of data files

Use MVCC to support high concurrency and achieve four standard isolation levels. The default is REPEATABLE READ repeatable read, which prevents phantom reading through gap lock next-key locking, which causes InnoDB to lock rows designed by the query and to prevent gaps in the index from calling shadow rows.

Gap lock:

When using a range condition and requesting a lock, InnoDB locks the index entry of the existing data record that meets the condition, and locks the record (gap) whose key value is within the condition range but does not exist. Gap lock: [source]

/ / for example, there are 101 records in the emp table, and their empid values are 1 for update. 2 for update.

InnoDB locks records with an empid value of 101, and locks "gaps" with an empid greater than 101 (these records do not exist).

1. In the above example, if the gap lock is not used, if other transactions insert records greater than 100, the transaction will be read unreal again, but it will cause lock waiting. When there are more concurrent inserts, we should optimize the business logic as much as possible. Use equality conditions to access updated data and avoid using scope conditions.

2. Gap locks are also used when using equality conditions to request locking a record that does not exist. When we delete a record through a parameter, if the parameter does not exist in the database, the library scans the index and finds that it does not exist. The delete statement acquires a gap lock, and the library scans left to the first value smaller than the given parameter, right to the first value larger than the given parameter, and builds an interval. Lock the data in the entire interval [source]

1.5.2MyIsSAM storage engine

Full-text indexing, compression, spatial functions, do not support transactions and row-level locks, can not be safely recovered after a crash

Storage:

Store the table in two files: data .MYD and index file .MYI

A table can contain dynamic or static (fixed-length) rows, and MySQL determines which row format to use according to the table definition

If the table is a variable length row, the default configuration can only handle 256TB data (the length of the pointer to the record is 6 bytes), change the length of the table pointer, modify the MAX_ROWS and AVG_ROW_LENGTH of the table, multiply them = the size of the max that the table can reach, and the modification will cause the entire table and table all index to be rebuilt.

Properties:

1. Lock the whole table, read, share, write and exclusive, but insert new records from the table at the same time: concurrent insert

2. Repair: check and repair operations can be performed manually and automatically, CHECK TABLE mytable checks table errors, REPAIR TABLE mytable repairs, performing repair may lose some data, if the server is shut down, the myisamchk command line according to the check and repair operation

3. Index features: support full-text index, index based on word segmentation, support complex query

4. Delay updating the index key Delayed Key Write. If the DELAY_KEY_WRITE option is specified, after each modification, the modified index data will not be immediately written to disk, to the key buffer of memory, and the corresponding index block will be written to disk when cleaning this area or closing the table to improve write performance. However, when the library or host crashes, it will cause index damage and need to perform repair operations.

Compression table:

After the data is created and imported, the table is no longer modified, which is more suitable. Myisampack can be used to compress (package) the MyISAM table, and the compressed table cannot be modified (unless decompressed, modified, and compressed again); reduce disk space consumption, disk IO, improve query performance, and also support read-only indexes

With the current hardware capability, the cost of decompression is small when reading compressed table data, and the benefits of reducing IO are much greater. Table records are compressed independently when compressed, and there is no need to decompress the whole table when reading a single row.

Performance:

Simple design and compact format storage; typical performance problem is table lock, which is in locked state for a long time: look for table lock

1.5.3 other built-in storage engines

Archive: suitable for log and data acquisition applications, optimized for high-speed insertion and compression, supports row-level locks and professional caches, cache writes use zlib to compress inserted rows, and select scans the whole table

Blackhole: replicate schema and log audits, whose server records blackhole logs, and can copy data to slave logs

CSV: data exchange mechanism that treats CSV files as MySQL tables and does not support indexing

Federated: a proxy that accesses other MySQL servers, creates a client connection for remote mysql, transfers the query to the remote server for execution, and extracts the data needed for sending. It is disabled by default.

Memory: quick access to data that will not be modified. The data is stored in memory and is not IO. The data remains after the table structure is restarted, but the data is gone.

1. Find or map tables. 2. Cache periodically aggregated data. 3. Save intermediate data generated in data analysis.

Support hash index, table-level lock, fast lookup and low concurrent write performance, do not support BLOB/TEXT type columns, fixed length of each row, memory waste

Merge:myisam variants, virtual tables merged by multiple myisam

NDB Cluster engine:

1.5.4 third-party storage engine

OLTP class:

XtraDB is improved based on InnoDB, with performance, scalability and flexible operation.

PBXT:ACID/MVCC, engine-level replication, foreign key constraints, more complex architecture for solid-state storage SSD appropriate support, larger value type BLOB optimization

TokuDB: big data, high compression ratio, large amount of data to create a large number of indexes

RethinkDB: solid state storage

Column oriented

Column is stored separately, with high compression efficiency.

Infobright: large amount of data, data analysis, warehouse application design, highly compressed, sorted by block (a set of metadata); block structure quasi-index, does not support index (large index is also useless), if the query can no longer be executed using column-oriented mode in the storage layer, it needs to be converted to row processing at the server layer.

Community Storage engine: *

1.5.5 choose the right engine

Unless you need to use some features that InnoDB does not have, and there is no alternative, the InnoDB engine is preferred.

Do not mix multiple storage engines, if different storage engines are required:

1. Transaction: transaction expenses are required, and InnoDB XtraDB; does not need to be mainly select insert that MyISAM

2. Backup: shut down the server regularly to perform backup, this factor can be ignored; online hot backup, InnoDB

3. Crash recovery: the amount of data is large, and the damage probability of MyISAM is much higher than that of InnoDB, and the recovery speed is slow.

4. Characteristics held:

1.5.6 engine for converting tables

ALTER TABLE: the simplest

ALTER TABLE mytable ENGINE=InnoDB

This will be executed for a long time, and MySQL will copy the data from the original table to the new table by row, which may consume the system all's IUnip O capability and add a read lock on the original table during replication, and will lose the all features related to the original engine.

Export and Import:

The mysqldump tool exports the data to a file, modifies the storage engine option of the CREATE_TABLE statement in the file, and modifies the table name (the same database cannot have the same table name). Mysqldump automatically adds the DROP TABLE statement before the CREATE_TABLE statement by default.

Create and query: CREATE SELECT

Synthesize the above two methods: first build a new storage engine table, using INSERT …... SELECT syntax derivative

If CREATE TABLE innodb_table LIKE myisam_tableALTER TABLE innodb_table ENGINE=InnoDB;INSERT INTO innodb_table SELECT * FROM myisam_table; has a large amount of data, it is processed in batches (in transaction) 1.6MySQL timeline Timeline

Early MySQL destructive innovation, there are many limitations, and many features can only be said to be second-rate, but feature support and low cost make it popular; 5.x get up early and introduce views, stored procedures, etc., hoping to become an "enterprise" database, but it is not successful.

1.7MySQL development mode

Follow the GPL open source agreement, all the source code is developed to the community, some plug-ins charge

These are all the contents of the article "what is the MySQL Architecture?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.