MySQL Carpet-style learning (a) Introduction to the overall structure of--mysql

Recently I feel that my skills are learning with the work, there is always some knowledge is not mastered, is prepared based on the "MySQL Authoritative guide" and "high-performance MySQL," The two books to systematically learn, each study of the notes into a blog form. First, in order to deepen their impressions, the second is to provide themselves with better learning ability, third, and we share.
1. Logical Architecture

First tier: Not MySQL exclusive, most web-based client, server tools, such as: Connection processing, authorization authentication, security, etc.
Second tier: Core service tiers, including query parsing, parsing, optimization, caching, built-in functions, all cross-domain storage engines are at this level: stored procedures, triggers, views
Third layer: Contains the storage engine, responsible for the storage and extraction of MySQL data.
2. Concurrency control for MySQL
MySQL concurrency control at the server layer and the storage engine layer.
2.1 Read/write lock
When dealing with concurrent reads and writes, the problem can be solved by implementing a locking system consisting of two types of locks, both of which are called shared and exclusive (exclusive lock), also called Read and write locks
Describes the concept of a lock: read locks are shared, and write locks are exclusive.
In the actual database application, every moment the lock occurs, the MySQL lock internal management transparent.
2.3 Lock particle size
One way to increase the concurrency of shared resources is to make locked objects more selective. Try to lock only part of the data that needs to be modified. An ideal way to lock only the modified pieces of data. The less data is locked, the higher the concurrency.
Locking is also a resource-intensive, lock-out operation, including acquiring locks, detecting whether locks are lifted, releasing locks, and so on.
The so-called locking strategy is the balance between the cost of locks and the security of data, which of course affects performance.
MySQL offers a variety of options, each of which can implement its own lock policy and lock granularity. Here are two locking strategies:
1. Table lock
MySQL basic strategy with minimal overhead.
2, row-level lock
Maximum support concurrency, as well as maximum lock overhead. Row-level locks are implemented only at the storage engine layer, and the MySQL server layer is not implemented. The server layer does not understand the lock implementation in the storage engine at all.
All of the storage engines show the locking mechanism in their own way.
3. Business
Acid principle
Atomic Atomicity, consistency consistency, isolation isolation, persistence durability
1. Isolation level
READ UNCOMMITTED (uncommitted), transactions can be read to uncommitted data, called dirty reads
Read Committed, which is not visible to other transactions before a transaction is committed
Repeatable read, which solves the problem of dirty reads, which ensures that the same record is read more than once in the same transaction. But in theory it's not possible to solve phantom reads, so-called Phantom reads, when a transaction is reading a range of records, another transaction inserts a new record into that range, which produces a magic line when the transaction is read again. MySQL's InnoDB and Xtrsdb storage engine solves the Phantom read problem with multiple version concurrency control (mvvc,multiversion concurrency control).
Serializable serializable, serial execution, highest isolation level, timeout and lock contention critical, infrequently used
2. Deadlock
A deadlock is a vicious circle in which two or more transactions occupy each other on the same resource and request a lock on the resource occupied by the other. Deadlocks can occur when a transaction attempts to lock a resource in a different order. Deadlocks can also occur when multiple transactions lock the same resource at the same time.
For example:
Transaction 1:
Start transaction;
Update goods Set price = a WHERE goods_id = 4 and date= ' 2015-4-22 ';
Update goods Set price = where goods_id = 3 and date = ' 2015-4-22 ';
Commit
Transaction 2:
Start transction;
Update goods Set price = max where goods_id = 3 and date = ' 2015-4-22 ';
Update goods Set price = n where goods_id = 4 and date = ' 2015-4-22 ';
Commit
To solve this problem, the database system implements a variety of deadlock detection and deadlock time-out mechanism, the more complex the system, such as: InnoDB storage engine, the more able to detect the deadlock of the cyclic dependency, and immediately return an error.
There is also a liberating way to discard a lock request when the time of the query reaches the setting of the lock wait timeout. InnoDB currently handles deadlocks by rolling back a transaction that holds a minimum of row-level lock exclusive locks.
The behavior and order of locks are related to the storage engine. Executing statements in the same order, some storage engines generate deadlocks, and some do not. There are two reasons for deadlocks: True data collisions, and how the storage engine is implemented.
3. Transaction log
Improved transaction efficiency, if the modification has been logged to the transaction log and persisted, but the data itself has not been written back to disk, at this time the system crashes, the storage engine can automatically restore this part of the modified data when restarted. The specific implementation depends on the storage engine.
4. mysql in the transaction
Auto-commit (AUTOCOMMIT)
MySQL defaults to autocommit mode, and you can enable or disable autocommit mode by setting the AUTOCOMMIT variable:
Show VARIABLES like ' autocommit ';

1 or on means enabled, and 0 or off indicates off.
MySQL can set the isolation level by executing the SET TRANSACTION ISOLATION Level command, which will take effect at the beginning of the next transaction.
Example: Set session transaction Isolation level Read Committed;
Mixing the storage engine with a transaction
If transactional and non-transactional tables (InnoDB and MyISAM) are mixed in a transaction, the normal commit is not problematic, and if the transaction is rolled back, non-transactional table changes cannot be undone, which causes the database to be in an inconsistent state.
Implicit and explicit lock-in
InnoDB in the execution of the transaction, the lock can be executed at any time, the lock is released only when the commit or rollback is executed, and all locks are released at the same time, all of which are described as implicit locks, and InnoDB are automatically locked according to the isolation level when needed.
In addition, INNODB supports display locking through specific statements.
Select ... lock in share mode– shared lock
Select ... for update– exclusive lock
MySQL also supports lock tables and unlock tables, which are implemented at the server level, regardless of the storage engine.
5, multi-version concurrency control
Most transactional storage engine implementations of MySQL are not simple row-level locks. Based on elevated concurrency considerations, multiple versions of concurrency control (MVCC) are generally implemented simultaneously, including Oracle, PostgreSQL. But the implementation is different.
It can be thought that MVCC is a variant of row-level locking, but in many cases he avoids lock-up operations and is less expensive. Although the implementation mechanism is different, most non-blocking read operations are implemented, and write operations only lock the necessary rows.
The implementation of MVCC is achieved by saving data at a point-in-time snapshot. In other words, no matter how long the implementation time, each thing sees the data is consistent.
It is divided into optimistic (optimistic) concurrency control and pessimistic (pressimistic) concurrency control. Here's how it works:
The MVCC of InnoDB is achieved by saving two hidden columns after each row of records. These two columns save the creation time of a row, and the expiration time of a saved row (delete time). Of course the storage is not the real time but the system version number, each start a new transaction, the system version number will be automatically added. The system version number at the start of a transaction is used as the version number of the transaction to query the version number of each row of records to compare, below repeatable read isolation level MVCC how to work:
SELECT
InnoDB checks each row of records according to the following criteria:
A.innodb only finds data rows that are earlier than the current version of the transaction, which ensures that the rows read by the transaction are either already present before the start of the transaction or are inserted or modified by the transaction itself.
B. The deleted version number of the row is either undefined or greater than the current transaction version number, which ensures that the transaction read to the row is not deleted before the transaction begins
Only those that meet the above two criteria will be queried.
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 for each row deleted as a row delete identity
UPDATE
InnoDB saves the current system version number as the row version number for the inserted row record, while saving the current system version number to the original line as the delete identity
Save these two version numbers so that most of the operations are not locked. It makes the data easy to operate, performs well, and guarantees that only the rows that are read into the composite requirements are available. The disadvantage is that each row of records requires additional storage space, more row checking and some additional maintenance work.
MVCC only in Repeatable read and committed read
6. mysql Storage engine
6.1 InnoDB Storage Engine
The data is stored in a tablespace, which is a black box managed by InnoDB and consists of a series of data files that, after Mysql4.1, InnoDB can place the table's data and indexes in a separate file.
InnoDB uses MVCC to support high concurrency, the default isolation level is repeateable read, and the presence of Phantom reads is prevented by a gap lock (nex-key locking) policy, so that InnoDB not only locks the rows involved in the query but also locks the gaps in the index. Prevents insertion of phantom rows.
InnoDB is based on clustered indexes and has high performance on primary key queries, although his level two index (non-primary key index) must contain primary key columns, and all other indexes will be large if the primary key columns are large. So if the index is larger then the primary key is as small as possible.
6.2 MyISAM Storage Engine
MyISAM stores tables in two files: Data files and index files, and the number of rows that can be stored is typically limited by the available disk space or the maximum size of a single file in the operating system.
Characteristics:
1, locking and concurrency
Locks the entire table, reads all tables that need to be read plus shared locks, and writes the table with exclusive locks
2. Repair
MySQL can perform check and repair operations manually or automatically, but differs from transactional replies and crash fixes. Repairing a table can result in some data loss, and the repair operation is very slow.
3. Index characteristics
Even a long field such as blob and text can be indexed based on its first 500 characters. Full-text indexing is also supported, which is an index based on participle creation
Big Data volume
When we create or manage a lot of innodb data in the amount of data between 3~5TB or larger, this is but the amount of machine is not a shard (shard), these systems work well. If the volume of data continues to grow to 10TB it may be necessary to build a data warehouse, Infobright is the most successful solution for MySQL Data Warehouse.
Conversion table's storage engine:
1. ALTER TABLE mytable engine = InnoDB, long execution time, consumes I/O capability, and locks on the original table, will lose all features related to the original engine, for example: if you convert a InnoDB table to MyISAM and then turn back, All foreign keys on the original InnoDB table will be lost.
2. Export and Import
Use the Mysqldump tool to export data to a file, and then modify the storage engine option for the file CREATE TABLE statement, while modifying the table name, because the same table name cannot exist in the same database, even if a different storage engine is used.
3. Create and query
Combining the above two methods of efficiency and security, do not need to export the entire table of data, but first create a new storage engine table. Then use the Insert ... Select syntax to come to the data
CREATE table innodb_table like myisam_table;
ALTER TABLE innodb_table ENGINE=INNODB;
INSERT INTO innodb_table select * from Myisam_table;
Large amount of data considering batch processing
Start transaction;
INSERT INTO innodb_table select * from myisam_table where ID between x and y;
Commit

MySQL Carpet-style learning (a) Introduction to the overall structure of--mysql