High Performance MySQL (first chapter MySQL architecture and history)

Logical architecture

MySQL's logical architecture is divided into 3 layers,

1. Connection thread processing.
2. The core functionality of the server, query parsing, parsing, optimization, caching, and all of the inside to functions.
3. Storage engine, responsible for the storage and extraction of data in MySQL, each storage engine has its advantages and disadvantages, the server communicates with the storage engine through the API, which masks the differences between different storage engines.

concurrency control

Read-write locks are also commonly referred to as shared and exclusive locks,

• Read locks are shared, and multiple customers can simultaneously read the same resource at the same time without interfering with each other.
• Write locks are exclusive, meaning that a write lock blocks other write and read locks.

Lock particle size

• Table lock is the most basic strategy in MySQL, it locks the entire table, a user needs to obtain a write lock before writing to the table, which blocks all operations of the table by other users. Only when there is no write lock can the other users read the lock and the read lock is not blocked from each other.
• Row-level locks can support concurrent processing to the greatest extent while also having a large lock overhead. Row-level locks are implemented in the InnoDB storage engine.

Transaction

A transaction is a separate unit of work that can start a transaction with the start TRANSACTION statement, then either commit with commit , or use ROLLBACK to undo all modifications.

There are four major features of transactions,

• atomicity : A transaction must be considered an indivisible minimum unit of work, and the entire transaction is either committed successfully or all failures are rolled.
• consistency : The database is always transitioning from one consistent state to another consistent state.
• isolation : In general, changes made by an office are not visible to other transactions until the final submission.
• Persistence : Once a transaction commits, its modifications are persisted to the database.

Isolation level

• Read uncommited (unread): changes in a transaction are visible to other transactions even if they are not committed, and are seldom used in practical applications.
• Read commited: Most of the database default isolation levels are read commited, but MySQL is not, and a transaction is not visible to other transactions until it mentions the commit.
• REPEATABLE READ (repeatable Read): The default level of MySQL, which guarantees that the results of reading the same record multiple times in the same transaction are consistent.
• SERIALIZABLE (Serializable): The highest isolation level, which locks on every row of data read, can cause a large number of timeouts and lock contention issues.

Dead lock

Deadlocks are two or more transactions that occupy each other on the same resource and request a lock on the resources occupied by the other, leading to a vicious cycle. Only partially or completely rolls back one of the transactions to break the deadlock. This is unavoidable for transactional systems, so the application must consider how to handle deadlocks at design time.

Transaction log

Transaction logs can improve the efficiency of transactions, using transaction logs, where the storage engine modifies the data of a table as long as it needs to modify the memory copy, and then logs the modification behavior to the transaction log on the hard disk without having to persist the modified data to disk each time. After the transaction log is persisted, the in-memory modified data can be slowly brushed back to disk in the background. Most storage engines are now implemented in this way, which we often call pre-write logging.

A transaction in MySQL

There are two types of transactional storage engines available in MySQL: InnoDB and NDB Cluster.

MySQL defaults to auto-commit mode. That is, if you do not explicitly start a transaction, each query is treated as a transaction to perform the commit operation. You can enable or disable the autocommit mode by setting the autocommit variable.

 like ' autocommit ' ;
SET = 1;

MySQL can also set isolation levels by executing the set TRANSACTION isolation level command.

You can also change the isolation level of the current session only:

SET TRANSACTION Isolation  Level READ commited;

Do not mix storage engines in transactions, such as InnoDB and MyISAM, with no problem in normal commit situations. However, if the transaction needs to be rolled back, changes on the non-transactional table cannot be undone.

InnoDB uses a two-phase locking protocol, which is released only at COMMIT or ROLLBACK , and all locks are released at the same time.

In addition, INNODB supports explicit locking through specific statements that are not part of the SQL specification.

SELECT inch SHARE MODE SELECT  for UPDATE

MySQL also supports the LOCK TABLES and UNLOCK TABLES statements, which are implemented at the service layer regardless of the storage engine, and they have their own uses, but they are not a substitute for transaction processing.

Multi-version concurrency control (MVCC)

The implementation of MVCC is achieved by saving a snapshot at a point in time, meaning that the data seen by each transaction is consistent regardless of how long it takes to execute.

Typical have optimistic lock (optimistic) and pessimistic lock (pessimistic).

InnoDB's MVCC is achieved by saving two hidden columns after each row of records. These two columns, a Save row creation time, a save row expiration time. Of course the storage is not the actual time, but the system version number. Each start of a new transaction, the system version number is automatically incremented. The system version number at the start of a transaction is used as the version number of the transaction to compare to the version number of each row of records queried.

• SELECT
  • InnoDB will check each line of records based on two criteria:
    • Only after the version of the data that is older than the current version of the transaction is found (that is, the line version number is less than or equal to the system version number of the transaction), this ensures that the transaction reads the row, either before the transaction begins, or the transaction itself is inserted or modified.
    • The deleted version of the row is either undefined or larger than the current transaction version number. This ensures that the transaction is read to a row that has not been deleted before the transaction begins.
• INSERT
  • InnoDB saves 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 will insert a new record, save the system version number as the line version number, while saving the current system version number to the original line as the row delete identity.

Save these two additional system version numbers so that most operations can be unlocked. This makes the read data operation Simple, performs well, and ensures that only rows that conform to the standard are read. The disadvantage is that each row of records requires additional storage space, more row checking is required, and some additional maintenance work.

MVCC only works under the two isolation levels of repeatable read and read commited . The other two isolation levels are incompatible with MVCC because read uncommited always reads the most recent data rows instead of the data rows that conform to the ex-officio transaction version. The SERIALIZABLE will lock all rows that are read.

MySQL's storage engine

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, a. frm file with the same name as the table is created in the database subdirectory to save the table definition.

You can use the Show Table STATUS command to display information about a table.

Name	The table name.
Engine	The type of storage engine for the table.
Row_format	The format of the row. For MyISAM tables, the optional values are dynamic, fixed, or compressed. The line length of dynamic is variable and typically contains variable-length fields, such as VARCHAR or blobs. Fixed line lengths are constant and contain only fixed-length columns, such as CHAR and INTEGER. Compressed rows are only present in the compressed table.
Rows	The number of rows in the table.
Avg_row_length	The average number of bytes that each row contains.
Data_length	The size, in bytes, of the table data.
Max_data_length	The maximum capacity of the table data, which is related to the storage engine.
Index_length	The size, in bytes, of the index.
Data_free	For the MyISAM table, represents the space that has been allocated but is not currently used.
Auto_increment	The value of the next auto_increment .
Create_time	The time when the table was created.
Update_time	The last modified time of the table data.
Check_time	Use the Check Table command or the Myisamchk tool to check the table last time.
Collation	The default character set and character column collation for the table.
Checksum	If enabled, the real-time checksum of the entire table is saved.
Create_options	Other options specified when creating the table.
Comment	For MyISAM, the marked annotation is saved, and for the InnoDB table, the remaining space information for the table space is saved, and if it is a view, the column contains the text of "view".

MyISAM Storage Engine

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

MyISAM characteristics

• Locking and concurrency : myisam locks the entire table instead of the row. When read, a shared lock is added to all tables that need to be read, and the write is an exclusive lock on the table. However, when a table has a read query, you can also insert a new record into the table (this is called concurrent Insert, CONCURRENT Insert).
• fix : Performing a repair of a table may result in some data loss and the repair operation is very slow. You can check table mytable for errors by checking tables. If there is an error, it can be repaired by executing REPAIR TABLE mytable . In addition, if the MySQL server is closed, you can also check and repair through the MYISAMCHK command-line tool.
• Index attributes : For MyISAM tables, you can create indexes based on the first 500 characters, even for long fields such as blob and text. MyISAM also supports full-text indexing.
• Defer update index key (Delayed key Write): When you create a MyISAM table, if you specify delay_key_write, the modified index data is not written to disk immediately when the modification is complete. Instead, it writes to an in-memory buffer, and the corresponding index block is written to disk only when the buffer is cleaned up or the table is closed. This approach can greatly improve write performance, but will cause index corruption in the case of a database crash and need to perform a repair operation. This feature can be set globally or for a single table.

MyISAM Compression Table

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 compressed table cannot be modified, can greatly reduce disk space consumption, so you can also reduce disk I/O, improve query performance. Indexes are supported, but the indexes are read-only.

MySQL built-in other storage engine

• Archive: Suitable for high speed insertion , quick insert.
• blackhole: There is no storage mechanism, he discards all inserted data, but the server logs the table log, so it can be used to copy the data to the repository, or simply to log.
• CSV: You can process a normal CSV file as a MySQL table, which does not support indexing. The CSV engine can copy or copy files while the database is running.
• Federated: Access a proxy for other MySQL servers.
• Memory: If you need to quickly access the data and will not be modified, restart will be lost later, you can use the Memory meter. The memory table is a table-level lock, so concurrent write performance is low, he 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 will be converted to CHAR, which can lead to a waste of some memory.
• Merge: A variant of MyISAM, a virtual table that is merged by multiple Mysam. After the partitioning feature is introduced, the engine has been discarded.
• NDB cluster : 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.

Third-party storage engine column-oriented storage engine

MySQL is line-oriented by default. The data for each row is stored together, and the server's query is also handled in the behavioral unit. In the case of large data processing, the column-oriented approach may be more efficient. Column-oriented methods can output less data if the entire row of data is not needed. If each column is stored separately, the compression will be more efficient.

Infobright is the most famous column-oriented storage engine. The engine works well at very large data volumes (number 10TB).

Engine for conversion tables

ALTER TABLE = InnoDB;

The syntax above can be applied to any storage engine, but it takes a long time for MySQL to copy data from the original table to a new table, which consumes all of the system's I/O capability during replication, while the original table is read-locked. So take extra care to do this on busy tables. The alternative is to export and import and manually make table copies.

You can use mysqldump to export data to a file and then modify the storage engine options for the CREATE TABLE statement in the file.

There is also a need to export the entire table of data, instead of creating a new storage engine table, and then take advantage of the INSERT ... SELECT syntax to guide the data.

CREATE TABLE  like myisam_table; ALTER TABLE innodb_table ENGINE=InnoDB; INSERT  into SELECT *  from Myisam_table;

The amount of data is small, so it works very well. If the amount of data is large, consider batching and perform transactional commit operations on each piece of data.

TRANSACTION ; INSERT  into SELECT *  from WHERE between  and y; COMMIT;

If necessary, you can lock the original table during execution to ensure that the data for the new table and the original table are consistent.

Percona Toolkit provides a pt-online-schema-change tool that can be used to perform these procedures in a relatively simple and convenient way.

High Performance MySQL (first chapter MySQL architecture and history)