Shulou(Shulou.com)05/31 Report--

MySQL architecture and history is what, for this problem, this article details the corresponding analysis and solutions, hoping to help more small partners who want to solve this problem to find a simpler and easier way.

MySQL Architecture and History 1. Design for Separation of Processing and Storage

MySQL's most important and distinctive feature is its pluggable storage engine architecture (separating query processing, system tasks, data storage, and retrieval).

2. Architecture: hierarchical role Remarks connection layer connection processing, authorization authentication, etc. RDMS has a design service layer query parsing, caching, analysis optimization, built-in functions, cross-engine functions MySQL core services and functions, service layer is not designed Foreign key engine layer is responsible for data storage and extraction of underlying functions, will not parse SQL, different engines do not communicate directly with each other at this level, only respond to requests from the upper level. Processing Foreign Keys 3. Optimization and Execution

The service layer parses the query, creates the parse tree, then rewrites the query, determines the order in which the tables are read, selects the appropriate index, and so on. In this step, the user can influence the optimizer's decision using the hint keyword or force index, or see how the optimizer optimizes the decision using the explain command.

The optimizer doesn't care what engine the table uses, but the storage engine has an impact on optimizing queries.

4. lock

Read locks are shared and do not block each other.

Write locks are exclusive, blocking each other, and only one user can be writing to the same row of data at a given time.

Most of the time, the internal management of MySQL locks is transparent.

The smaller the amount of data locked on a given resource, the higher the concurrent reads of the system.

Locks consume resources, and if the system spends a lot of time managing locks rather than accessing data, the performance of the system may suffer.

A balance is typically sought between the overhead of locking and the security of the data by imposing row-level locks on tables.

Each MySQL engine can implement its own lock bias and lock granularity.

Write locks have higher priority than read locks, and when concurrent, write lock requests may be inserted at the head of the queue, but read locks can only be placed at the head of other read locks.

The service layer uses table-level locking for changes involving the contents of the entire table, and the locking mechanism of the engine layer is ignored.

Row-level locking is implemented only at the storage engine level.

InnoDB uses a two-phase locking protocol, which allows locking at any time during transaction execution, but only releases all locks held by the transaction at the same time when the entire transaction is committed or rolled back. 'Implicit lock'

LOCK TABLE or UNLOCK TABLE can be used at the service level, but it may interact with transactions and have unpredictable consequences. Try not to use it during business.

5. Business

A transaction is a set of atomic SQL queries, a set of independent units of work. All statements within a transaction either execute or fail.

A complete database system needs to satisfy ACID characteristics:

Atomicity: The smallest unit of a transaction considered indivisible.

Consistency: The database always transitions from one consistent state to another.

Isolation: Changes made by a transaction are invisible to the transaction until it is finally committed.

Durability: Once committed, it is preserved permanently and is not affected by system crashes.

A database that implements ACID features requires stronger hardware.

Even though the storage engine does not support transactions, some ACID features can be provided through lock tables.

Non-transactional tables can only be auto-committed.

Transactional tables force the current active transaction to commit when performing DDL operations or locking tables

Isolation class (ANSI)

READ UNCOMMITTED: Uncommitted changes in a transaction are also read by other transactions,'dirty read', and performance is not much better.

READ COMMITTED: Post-commit changes in a transaction are read by other transactions, but result in non-repeatable reads. MSSQL,ORACLE

READ REPEATABLE: Read the same data value multiple times in a transaction, but the newly inserted data is not counted, that is, the reading of the primary key range may be inconsistent,'magic read', MySQL default level.

SEARIALIZE(serial): cancel parallel, completely serial, pessimistic lock.

Set [session] transaction isolation level [RU]| RC| RR| SX] Sets the isolation level, global settings take effect at the beginning of the next transaction, session-level settings only take effect for the current transaction

isolation level dirty read possibility non-repeatable read phantom read possibility locked read RUYNRCNYNRNNYNSXNNNY deadlock

Two or more transactions compete for the same set of resources and request locks on resources occupied by each other.

Solution: Complete RDMS contains deadlock detection and deadlock timeout mechanisms.

InnoDB: Rolls back transactions that hold the fewest row-level X locks.

Deadlocks are unavoidable in transactional RDMS. After a deadlock occurs, only partial or complete rollback of one of the transactions can break the dilemma.

Transaction log (redo)

The efficiency of transactions can be improved. The storage engine only needs to modify its memory copy when modifying the data of the table, and then record the modified behavior to the transaction log file on the hard disk in batches.

The transaction log uses a forward appending method to ensure sequential IO when writing. When recording modified behavior in batches, after the transaction log is persistent, the data in memory can be slowly flushed back to disk in the background.

6. Multiversion concurrency control (MVCC)

MVCC is a variant of row-level locking that avoids locking operations in many cases and only works at RR and RC isolation levels.

MVCC is achieved by keeping a snapshot of data rows at a point in time.

Depending on the transaction start time, each transaction may see different data for the same table at the same time.

InnoDB's MVCC is implemented by keeping two hidden version numbers behind each record (including records already stored in undo). That is, the system version number when the data was created and the system version number when the data became invalid.

Other business:

SELECT time: only finds rows older than the current transaction was created (in extreme cases, this row was modified or created in this transaction, i.e., reads the modified row data in this transaction).

INSERT time: Save the version number of this transaction version for the newly inserted row as the creation version number of the row. Also, if this line is not modified in this transaction, the line expiration version number is left blank.

Delete: assign the version number of the transaction to the invalid version number of the deleted row

UPDATE: Assign the version number of the transaction to the invalid version number of the modified row, insert the modified data, and pay the version number of the transaction to the creation version number of the newly inserted modified data.

Keeping these two extra version numbers allows most reads to be performed without S-locking, but these extra rows take up undo space. It is recommended that undo be separated from the shared table space and that the transaction length be reduced.

7. storage engine

Different engines store data and indexes differently, but table definitions are handled uniformly at the service level.

Show table status to display information about the table

Attribute Attribute Value Description Name:user Table Name Engine:InnoDB Storage Engine Row_format:Dynamic row format, variable row length Rows3 corresponds to InnoDB, Estimated row values Avg_row_length5461 Average bytes per rowData_length16384 Table data bytes Max_data_length0 Table maximum bytes (InnoDB unlimited) Index_length0 Non-primary key index bytes Data_free4194304 bytes allocated but unused Auto_incrementNULL next self increment starting point Create_time2018-01- 20 4 20:02:00 01 Creation time Update_timeNULL Last update time Check_timeNULLCHECK TABLE command time used Collationutf8_bin The table default character set and collation ChecksumNULL (if validation is enabled) ChecksumCreate_optionsstats_persistent= 0Other non-default options when creating the tableCommentUsers and privileges table Remarks

The InnoDB engine is designed to handle a large number of short-term transactions (mostly committed normally), and the default is the InnoDB engine. MySQL version 8.0 also replaces the tables in the mysql library from MyISAM to the InnoDB engine.

The InnoDB engine is evolving towards scalability, scalability, configurability, performance, and more new features.

InnoDB Engine Overview

InnoDB data is stored in tablespaces, and it is recommended to use separate tablespaces, i.e., data and indexes for each table are stored in separate files.

InnoDB uses MVCC to support high concurrency and implements four standard isolation levels. The default isolation level is RR, and the gap lock mechanism solves the problem that range reading may cause phantom reading due to new inserted values.

Gap locks also lock gaps in the index by locking rows that were originally designed by the query, preventing new rows from being inserted.

InnoDB tables are built based on clustered indexes, i.e. index organization tables. This design makes the query efficiency of the primary key very high. Its secondary index, that is, non-clustered index (ordinary index, non-primary key index) points to its corresponding primary key position by means of links, that is, the secondary index contains primary key columns. This creates a problem where one primary key column is large, and the size of the other indexes is equally large. This feature requires us to minimize the maximum length of our primary key columns.

The file storage format of InnoDB engine tables is independent and can be used across platforms to some extent.

InnoDB's notable optimization features: predictable reads when reading data from disk, introduction of an adaptive hash index that can create Hash indexes in memory to speed up reads, and an insert buffer that can speed up inserts (cache non-primary key non-unique indexes and merge them into non-primary key indexes every 10 seconds).

InnoDB engine tables support hot physical backup (non-logical backup into sql files) i.e. XtraBackup or official Enterprise Backup

Interesting CSV engine

Excel files can be saved as CSV format, put into MySQL data directory, you can open in MySQL use.

Slowly decommissioned engines:

Federated engine, originally designed to establish Oracle, SQL server to MySQL data links, later used to create cross-instance connections (similar to SQL server synonyms or linked servers), but often caused problems, disabled by default, MariaDB provides an improved version of FederatedX.

Memory engine, data only exists in memory, table structure remains after restart, but all data will be lost. Support HASH index, table-level locking, low concurrency, fixed row length.

Common third-party storage engines:

XtraDB engine, Percona's improved version based on InnoDB engine

TokuDB engine, based on fractal tree index engine, has a high compression ratio, can create index on a large amount of data.

Infobright engine, column organized engine, design for big data.

Change the table storage engine: alter table t1 engine = InnoDB; during execution, a new table with the same table structure and different engines will be created, and then data will be read from the original table to the new table by row. The table will be locked, consuming a lot of system IO. Try not to do it during business peak periods, or use pt-online-schema-change tools to modify online.

About MySQL architecture and history is how the answer to the question shared here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

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.