"MySQL Database" chapter I interpretation: MySQL Architecture and history

Objective:

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

Review MySQL's historical background, Benchmark, and discuss the principles of MySQL by simplifying details and demonstrating cases

Body:

MySQL architecture can be used in many different scenarios, can be embedded in the application of the middle peasants, support data Warehouse, content index, deployment software, highly available redundancy system, online transaction processing system, etc.

MySQL's most important feature is his storage engine architecture, which makes query processing and other system tasks and data storage, extraction and separation;

1.1MySQL Logical Architecture

1.2 Concurrency control

Size of Lock:

Lock policy: Seek balance between lock overhead and data security, with each storage engine implementing a specified lock policy and granularity

Table Locks: Table lock most basic overhead minimum lock whole table

Row-level locks: Row lock maximizes support for concurrent maximum lock overhead at the storage engine layer (in its own way)

1.3 Business

Standalone unit of work, a set of atomic SQL queries

Isolation level:

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

READ UNCOMMITTED uncommitted reads

Changes in the transaction are not committed in a timely manner and are visible to other transactions; The transaction reads uncommitted data: dirty read ; seldom used

Read Committed submit Reading

Almost library default isolation level, non-MySQL; transactions from start to finish only see the changes made by the committed transaction, and the modifications made by itself are not visible to other transactions; non-repeatable reads : Two execution of the same query, the results may be different (modifications to other transactions)

REPEATABLE Read Repeatable reads

mysql default, resolves dirty read, same transaction reads the same result multiple times; Phantom read : When a transaction reads a record in a range, another transaction inserts a new record within that scope, the current transaction reads the range record again, the Phantom row

SERIALIZABLE: Serializable

Highest, force transaction serial execution, avoid phantom read problems, lock when reading each row of data (can cause a lot of timeouts and lock contention), rarely used

Dead lock

1, two multiple transactions occupy each other on the same resource and request to lock the resource occupied by each other;

2. Multiple transactions attempt to lock resources in a different order, potentially creating deadlocks;

3, multiple transactions simultaneously lock the same resource;

The behavior and order of the lock are related to the Access engine, the same sequence of execution statements, some storage engines will generate deadlocks some will not;

The dual cause of deadlocks: Because of real data collisions (which is hard to avoid) because of the way the storage engine is implemented;

After a deadlock is sent, only a partial or complete rollback of one of the transactions can break the deadlock: InnoDB rolls back the transaction that holds the least row-level exclusive lock;

Transactions in 1.3.4MySQL: storage Engine implementation

MySQL Two transactional storage engine: InnoDB, NDB Cluster

Automatic submission of autocommit;

The default is Autocommit mode, and if you do not explicitly start a transaction, each query is committed as a transaction, and you can enable =1 =on by using the autocommit variable, disable =0 =off (all queries are in one transaction until explicit commit Rollback) Start a new transaction at the end of the transaction and modify the variable to have no effect on the non-transactional table;

MySQL can set the isolation level with the SET transaction isolation level, the new levels take effect at the start of the next transaction, the configuration file sets the entire library, or only the isolation level of the current session

Set session transaction Isolation level Read Committed;

Recommendation: Do not display execution lock TABLES at any time, no matter what storage engine is used

1.4 Multi-version concurrency control MVCC

The database MySQL, Oracle, PostgreSQL and so on have realized the MVCC, each realization mechanism different "source"

MVCC: Each read that is connected to the database, a snapshot of the database at a moment, the write operation is not visible until it is committed; "Source"

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

Characteristics:

1, each row of data has a version, each time the data update the version is updated

2, the revision of the current version of the copy, arbitrary modification, no interference between the transactions

3, save the version number, the successful commit overwrites the original record, failed to abandon rollback

4. Only works under two isolation levels of repeatable read and Read Committed

1.5MySQL Storage Engine

MySQL saves each database in a subdirectory of the bit data directory, creating a representation that MySQL creates the definition of a. frm file with the same name as the table in the subdirectory, different storage engines save the data and index differently, but the table definition is handled in the same way as 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, non-transactional storage requirements are also very popular

Data is stored in a table space managed by InnoDB and consists of a series of data files;

With MVCC support high concurrency, and implemented four standard isolation level, by default is repeatable read REPEATABLE read, through the gap lock Next-key locking prevent Phantom Read, Gap Lock makes the InnoDB lock query design line also lock the gap in the index to prevent the shadow line;

Clearance Lock:

When the scope condition is used and the lock is requested, InnoDB locks the index entry for the existing data record that matches the criteria, and the record (GAP) with the key value within the condition but does not exist lock, Gap Lock: "Source"

If there are 101 records in the EMP table, their empid values are,..., 100,101select * from  emp where empid > + for update;

InnoDB locks a record with a qualifying Empid value of 101, and also locks the "gap" of Empid greater than 101 (which do not exist);

1, the above example, if you do not use a gap lock, if other transactions inserted more than 100 records, the transaction again to perform the Phantom read, but will cause lock wait, in the concurrency of more than a long time, to optimize the business logic, use equal conditions to access the update data, avoid the use of scope conditions;

2, when the use of equal conditions request to a non-existent record lock, will also use gap lock, when we delete a record by the parameter, if the parameter does not exist in the database, the library will scan the index, the discovery does not exist, the DELETE statement obtains a gap lock, the library to the left scan to the first value smaller than the given parameter, Scan to the right to the first value larger than the given parameter, construct an interval, lock the entire range of data; "Source"

1.5.2MyIsSAM Storage Engine

Full-text indexing, compression, spatial functions, transaction and row-level locks not supported, no security recovery after crash

Store:

Store a table in two files: data. MYD, index file. MYI

Tables can contain dynamic or static (fixed-length) rows, and MySQL is defined by the table to determine which row format to use

If the table is a long line, the default configuration can only handle 256TB data (pointing to the record's pointer length 6 bytes), changing the table pointer length, modifying the table's max_rows and avg_row_length, multiplying = The table can reach the max size, the modification will result in rebuilding the whole table, the table all index;

Characteristics:

1, lock the whole table, read, share the lock, write, exclusive lock, but at the same time can be read from the table to insert new records: Concurrent insertion

2, Repair: Manual, automatic check and repair operations, check table MyTable checklist errors, REPAIR table MyTable repair, repair may be lost some data, if the server is closed, Myisamchk command line based on inspection and repair operations;

3, Index features: Support full-text indexing, based on the creation of Word segmentation index, support complex query

4, delayed update index key delayed key write, if the Delay_key_write option is specified, the modified index data will not be written to disk immediately after each modification, write to the memory key buffer, clean this area or close the table when the corresponding index block is written to disk, improve write performance, However, when the library or host crashes, the index is corrupted and a repair operation is required

Compression table:

Table after creating and importing data, no longer modified, more suitable, can use Myisampack to MyISAM table compression (packaging), compressed table can not be modified (unless the compression, data modification, re-compression), reduce disk space consumption, disk IO, improve query performance, also support read-only index;

Now the hardware capabilities, read the compressed table data decompression is not very expensive, reduce the benefits of IO much larger, compression table record independent compression, read a single line without extracting the entire table

Performance:

Simple design, compact format storage; Typical performance problems are table lock problems, long-term locked state: Looking for a table lock

Other storage engines built into the 1.5.3

Archive: Suitable for logging and  for high-speed insertion and compression optimization, support row-level locks and professional buffers, cache write the use of zlib compressed inserted rows, select Scan Full table;

Blackhole: Copy schema and log audit, its server Records blackhole table log, can copy data to repository log;

CSV: Data exchange mechanism, the CSV file as a MySQL table to handle, does not support the index;

Federated: Access other MySQL server proxy, create a remote MySQL client connection to transfer the query to the remote server execution, extract the data required to send, disabled by default;

Memory: Fast access to data that will not be modified, the data is stored in RAM, not IO, the table structure is restarted, but the data is gone.

1, Lookup or mapping table, 2, cache periodic aggregation data, 3, save the data analysis of the intermediate data generated

Support Hash index, table-level lock, Find Fast concurrent write performance is low, do not support blob/text type column, fixed length per line, memory wasted

Merge:myisam Variant, multiple MyISAM merged virtual tables

NDB Cluster Engine:

1.5.4 third-party storage engine

OLTP classes:

XTRADB based on InnoDB improvement, performance, measurable, flexible operation

PBXT:ACID/MVCC, Engine-level replication, foreign key constraints, more complex architectures appropriate support for solid state storage SSDs, large value type BLOB optimization

TOKUDB: Big data, high compression ratio, big data volume to create a large number of indexes

RETHINKDB: Solid State storage

Column-oriented

Columns are stored separately for high compression efficiency

Infobright: Large data volume, data analysis, warehouse application design, highly compressed, sorted by block (a set of metadata), block structure quasi-index, index not supported (large index is useless), such as query no longer storage layer using column-oriented pattern execution, you need to convert the server layer to row-based processing

Community Storage Engine: * * *

1.5.5 Choosing the right engine

The InnoDB engine is preferred unless there is a need to use features that some InnoDB do not have and there is no way to replace them.

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

1, business: the need for transaction expenses, InnoDB XtraDB; do not need to be the main select insert that MyISAM

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

3, Crash recovery: a large amount of data, MyISAM collapse of the probability of damage than innodb much higher, slow recovery

4, the characteristics of possession:

1.5.6 Conversion Table engine

ALTER TABLE: The simplest

ALTER TABLE mytable Engine=innodb

This will take a long time for MySQL to copy rows of data from the original table to the new table, which may consume the I/O capability of the system all during replication, while reading the lock on the original table, losing the all attribute associated with the original engine

Export and import:

The Mysqldump tool exports the data to a file, modifies the storage engine options for the Create_table statement in the file, modifies the table name (the same library cannot have the same table name), and mysqldump automatically adds a drop to the create_table statement by default Table statement

Creating and Querying: Create SELECT

Combine the above two methods: first build a new storage engine table, the use of insert ... Select syntax derivative

CREATE table innodb_table like Myisam_tablealter table innodb_table Engine=innodb;insert to innodb_table SELECT * from M Yisam_table, large data volume, batch processing (in-service)

1.6MySQL Time Line Timeline

Early MySQL disruptive innovation, there are many limitations, and many features can only be said to be second-rate, but feature support and low cost of use to make popular; 5.x early morning introduction views, stored procedures, etc., expect to become an "enterprise" database, but not a success, 5.5 significantly improved

1.7MySQL Development Model

Follow the GPL open source agreement, all source code development to the community, some plug-in fees;

1.8 Summary

MySQL layered architecture, the upper level is the server layer of access and query execution engine, the underlying storage engine (most important)

Related articles:

"MySQL Database" chapter II interpretation: MySQL benchmark test

"MySQL Database" chapter III Interpretation: Server performance profiling (top)