MySQL Deep understanding

MySQL is a relational database, and the associated data is stored in different tables, increasing the flexibility of data manipulation.

Execution process
MySQL is a single-process service, and each request is responded to with threads,
Process:
1, the client requests, the server opens up a thread to respond to the user.
2, the user initiates the SQL request,
3, query the cache, and record user SQL, if queried again, directly check the cache.
4, no cache, Access analyzer,
Parser: Check Syntax
Lexical Analyzer: Slice the commands, separate them by spaces, and get the table, content, and user rights.
5, optimize execution path selection, production execution tree.
6, Storage Engine: Used to manage the storage file system, to provide different management for the upper application.

So what is a storage engine?
The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table). There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.

Common Storage engines:
MyISAM
MyISAM is the default storage engine. It is based on older ISAM code, but there are many useful extensions. (Note that MySQL 5.1 does not support ISAM). Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type. frm file stores the table definition. The data file has an extension of · MYD (MYData).
Binary files that can be used on different systems
InnoDB
1.3.1, InnoDB storage engine, features support for foreign keys, row locks, non-locking reads (by default, read does not generate locks), mysql-4.1 start supporting each INNODB engine in a separate table space. InnoDB uses a MVCC multi-version concurrency control to obtain high concurrency, and implements 4 isolation levels for the SQL standard, while using a strategy called Next-key locking to avoid a read-out (phantom) phenomenon. In addition, the InnoDB engine provides high-performance technologies such as insert buffer, two write (double write), Adaptive Hasi Index (Adaptive Hash Index), and read ahead.

1.3.2, MyISAM storage engine, MyISAM feature is not support things, suitable for OLAP applications, MyISAM table is composed of myd and myi. Before the mysql-5.0 version, MyISAM supported a table size of 4G by default, and MyISAM supports 256T of form data by default after mysql-5.0. MyISAM only the index data is cached.

Two InnoDB Storage Engine

2.2. InnoDB Engine Architecture

Multiple memory blocks of InnoDB make up the memory pool, which is responsible for the following tasks:

1). Maintain multiple internal data structures that all processes/threads need access to.

2). Cache the data on the disk for quick and easy reading, and cache it here before modifying the data on the disk file.

3). Redo the log cache.

The primary role of a background thread is to refresh the data in the memory pool, ensure that the memory cache in the buffer pool is the most recent data, and also flush the modified data file to the disk file

2.2.1, Background thread

The InnoDB storage engine has 7 threads in the background,-–4 IO threads (insert buffer thread,log thread,read thread,write thread), 1 master thread, a lock monitoring thread, An error monitoring thread.

2.2.2, memory

InnoDB Storage engine memory consists of the following three parts: buffer pool, redo log cache (redo log buffer), additional memory pool (additional). You can use Show engine InnoDB status to view the usage of Innodb_buffer_pool.

Innodb_buffer_pool_size: Specifically, the database types in the buffer pool are: index pages, database pages, undo pages, insert buffer pages (insert buffers), adaptive hash (adaptive hashindex), InnoDB stores the lock information (lock info), Data dictionary information (dictionary).

INnodb works by reading the data files into the Innodbbuffer pool by page (16K per page), then preserving the cached data by the least recently used algorithm (LRU), and finally flushing the dirty pages to the file at a certain frequency.

Two important disk-based resources managed by the InnoDB storage engine are the InnoDB tablespace data file and its log file.

If you specify the No INNODB configuration option, MySQL will create a 10MB size auto-extension in MySQL data directory named Ibdata1
A data file, and two log files of 5MB size named Ib_logfile0 and Ib_logfile1.