01-mysql Architecture and Storage engine

MySQL is designed as a portable database that can run on almost all of the current systems. Although the platforms differ in the underlying implementations, MySQL basically guarantees the consistency of the physical architecture on each platform.

1. Defining databases and instances

There are two words in the database domain that can easily be confused: "Database" and "instance" (instance), the two terms are defined as follows.

• Database : A collection of physical operating system files or other forms of file types.
• instance : A DB instance consists of a background thread and a shared memory area.

A database is a collection of files that are organized according to a data model and stored in level two storage; A database instance is a program that is a layer of data management software located between the user and the operating system. Any action that a user or application makes to a database must be done through a DB instance.

MySQL is designed as a single-process multi-threaded architecture database, which means that the MySQL database instance behaves as a process on the operating system.

When the instance is launched, the MySQL database reads the configuration file and launches the DB instance based on the parameters in the configuration file. Use the following command to see where the MySQL db instance will look for the configuration file when it is started.

localhost:~ xukun06$ MySQL--help | grep my.cnf                       $MYSQL _tcp_port ,/etc/my.cnf/etc/mysql/my.cnf/usr/local/mysql/etc/my.cnf ~/.my.cnf

If there is no configuration file, MySQL launches the instance according to the default parameters set at compile time.

If the same parameter appears in more than one configuration file, MySQL will take the parameters of the last configuration file read.

2. MySQL Architecture

As can be seen, MySQL consists of the following parts:

• Connection Pool Components
• Manage services and tools build
• SQL Interface Components
• Query Analyzer Components
• Optimizer components
• Buffer components
• Plug-in storage engine
• Physical files

One of the most important features of the MySQL database that distinguishes it from other databases is its plug-in storage engine . It is important to note that the storage engine is table-based , not a database.

3. MySQL Logic Architecture

The MySQL logical architecture is divided into the following three tiers:

• Service layer: Complete the functions of connection processing, authorization authentication and security authentication.
• Core layer: Query parsing, analysis, optimization and caching, processing built-in functions, all cross-storage engine functions are done at this level, such as views, stored procedures and triggers.
• Engine layer: Responsible for data access and transaction processing.

Before parsing the query, if the query cache is open, it will first determine whether the query hits the cache, and if the hit cache returns the cached results directly. In this case, the query will not be parsed, the execution plan will not be generated, and it will not be executed.

The so-called "query Cache", which can be understood as a HashMap-like data structure, key is calculated based on the query itself, the target database, the client protocol version number, and some other elements that may affect the query result, value is the result of the query. Therefore, two queries have different characters, including spaces and comments, which will cause the cache to miss. In addition, if the query contains custom functions, times, user variables, or temporary tables, such queries are not cached, because they can have different results for different users or at different times. For example, the now () function is included in the query.

When the data or structure of the table changes, the query cache associated with this table will be invalidated. Therefore, if the query cache is large or fragmented, it is a significant performance cost to put the cache into a failed operation. Therefore, it is not recommended to turn on query caching for write-intensive applications.

4. MySQL Storage Engine

The plug-in storage engine is one of the most important features of MySQL that distinguishes it from other databases. The advantage of the storage engine is that each storage engine has its own characteristics and can build different storage engine tables depending on the application.

Use the following command to see which storage engines are supported by the current MySQL database.

Mysql>show engines;+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+|Engine|Support|Comment|Transactions|Xa|Savepoints|+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+|InnoDB| DEFAULT |Supports transactions, row- LevelLocking and ForeignKeys|YES|YES|YES||Mrg_myisam|YES|Collection ofIdentical MyISAM tables|NO|NO|NO||MEMORY|YES|Hash based, storedinchMemory, useful for TemporaryTables|NO|NO|NO||Blackhole|YES| /Dev/NULLStorage engine (anything you write toIt disappears)|NO|NO|NO||MyISAM|YES|MyISAM Storage Engine|NO|NO|NO||Csv|YES|CSV Storage Engine|NO|NO|NO||ARCHIVE|YES|Archive Storage Engine|NO|NO|NO||Performance_schema|YES|PerformanceSchema                                             |NO|NO|NO||Federated|NO|Federated MySQL Storage Engine| NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+9Rowsinch Set(0.00Sec

4.1 InnoDB Storage Engine

The InnoDB storage engine supports transactions, designed primarily for online transaction processing (OLTP) applications, characterized by row lock design, support for foreign keys, and support for non-locking reads similar to Oracle, where the default read operation does not generate locks. Starting with MySQL version 5.5.8, the InnoDB storage engine is the default storage engine.

The InnoDB storage engine places the data in a logical table space, which is managed by the InnoDB storage engine itself like a black box. Starting with MySQL version 4.1, you can store the table for each InnoDB storage engine in a separate IBD file.

InnoDB achieves high concurrency by using multi-version concurrency control (MVCC), and implements four isolation levels for the SQL standard, which by default is the repeatable level. Also, use a strategy called Next-key locking to avoid phantom reading. In addition, the InnoDB storage engine provides high performance and highly available features such as insert buffering, two write, Adaptive Hash index, and pre-read.

Storing the data in the table, the InnoDB storage engine uses the "aggregation" method, and each table is stored in the logical order of the primary key. If a primary key is not specified when the table is defined, the InnoDB storage engine generates an invisible 6-byte ROWID for each row, which is used as the primary key.

4.2 MyISAM Storage Engine

MyISAM storage Engine does not support transactions, table lock design, full-text indexing, mainly for some OLAP database applications.

MyISAM Storage Engine table, frm file storage table definition, MYD storage data file, MYI Store index file.

The buffer pool of the MyISAM storage engine caches only index files and does not buffer data files, which is very different from most other databases that use the LRU algorithm to cache data. The caching of the data files is done by the operating system itself.

4.3 InnoDB vs MyISAM

	MyISAM	InnoDB
Data files	frm + MYD +myi	Ibd
Cache	Cache index files only	Cache indexes and data
Transaction	Not supported	Support
FOREIGN key	Not supported	Support
Supported indexes	B-tree Index Full-Text Indexing	B-tree Index Full-Text Indexing (adaptive) Hash Index
Lock	Table lock	Row lock
	To compress an index	Do not compress
SELECT * FROM table	Returns the number of rows saved	Full table Scan returns the number of rows
Delete from table	Re-build the table	Delete a row line

01-mysql Architecture and Storage engine