MySQL cache, storage engine

First, MySQL Query Cache

Query caching is not a subsystem of MySQL, but is an integral part of query optimization and execution subsystem. Not only can it cache query results, but it can also cache the query results themselves. If the result of a query is in the cache, the system can simply take the data back to the client and skip the entire query optimization and execution phase, which is particularly effective for queries that are frequently used. This technique is unique to MySQL, and other databases only cache queries and do not cache query results. The query cache must also be able to handle the "dirty" results of the query, that is, the data has changed after the last query, and that the cached queries are cleaned up from time to times.

The query cache is open by default, and if you want to turn off the query caching feature, you can use the Sql_no_cache select (corresponding Sql_cache Select) option: Select sql_no_cache id,lname from MyCustomer;.

Second, Cache and Buffer

The cache and buffer subsystem is responsible for ensuring that the most frequently used data (or structure) is accessed in the most efficient manner possible. In other words, the most frequently used data must reside in memory at any time.

2.1 Table Cache

Table caches are designed to maximize the opening, reading, and closing of tables (on disk. frm file) is created by the cost of the. Therefore, the main purpose of the table cache is to store metadata about the table in memory. This can greatly speed up the speed at which a thread reads table structure information without having to open the file every time . Each thread has its own table cache structure table, which allows the table to be accessed independently of other threads, even if a thread changes the schema of a table (but hasn't committed those changes yet), and another thread can continue to use the table in its original mode. This structure is a simple structure that encapsulates all the metadata information of a table, which is stored in the form of a linked list in memory and related to each thread.

2.2 Record Cache

The record cache is created to speed up the sequential read performance of the storage engine. Therefore, the record cache is typically used only during table scans. It is like a read-ahead buffer that retrieves one chunk at a time, reducing the number of disk accesses during the scan. in general, fewer disk accesses mean higher performance. Interestingly, MySQL also uses the record cache when writing data sequentially: Write the new data (or modified data) to the record cache, and then write all the data to disk when it is full. As a result, write performance has also improved. Because the record cache can improve the performance of sequential read/write (known as the local domain of the reference), it is most often used in conjunction with the MyISAM storage engine (but not limited to this). The record cache is implemented in an agnostic way, and does not interfere with the code used to access the storage engine APIs. Because the record cache is implemented internally within each layer of the API, programmers can enjoy the benefits of record caching without taking any extra steps.

2.3-Key caching

key caches (key cache is actually a buffer used to cache index data , which is a block of data from the index file (b + tree), and only the MyISAM table (. MYI on disk) uses the key cache. The index itself is encapsulated in the key cache structure, which is stored in memory as a linked list. When you open a MyISAM table for the first time, a key cache is created for it. So, how does the system know which index blocks have been used?

The cache has its own monitoring mechanism, which records the frequency at which each index block is used. The key cache is used to record the "heat" of each index block. Here, the heat refers to how many times the index block was used. When an index block becomes "cold" and another index block becomes "hot," the latter is read into the key cache instead of the former. This approach is actually a "least-used" (LRU) paging strategy. When a "dirty" index block is swapped out of memory, its data is written to the index file on disk. If the cleanup is a "clean" index block, just remove it from memory and it's done.

2.4 Permissions Cache

The permission cache (privilege cache) is used to hold authorization data for a user account. The data in the permission cache is read from the respective permission table and centralized into the permission cache during user logon and initialization.

2.5 Host Name cache

The host name cache (hostname cache) is another accessible caching mechanism that is similar to the permission cache. It is also implemented as a stack of elements in a structure. This cached content is all host names connected to the server

2.6 Other caching mechanisms

MySQL's source code also has many small cache mechanisms that are ubiquitous, and the coupling buffers used in complex junction operations are one example. Eg: Some junction operations require that one record of the first table be compared to all records in the second list. In such cases, using a cache to store those records can greatly speed up the join operation without having to read the second table's records into memory repeatedly.

Third, accessing files via the plug-in storage engine

The plug-in storage engine allows the MySQL system to flexibly adapt to a variety of data or file storage and retrieval mechanisms. Mainly include: Myisam,innodb,ndb,archive,federated,memory,merge,partner,community,custom and so on.

The plug-in storage engine allows database specialists to select a storage engine of the best performance for their database based on the needs of a specific application, such as the use of a storage engine with various transaction control capabilities for the database for transactional processing. The memory storage engine is used to read frequently but rarely modified tables (for example, dictionary tables).

The most appealing part of the plug-in storage engine is allowing you to specify a different storage engine for each table in a given database, and you can even change the storage engine after creating a table. This flexibility and modularity makes it possible for database creators to create new storage engines whenever they need to. For example, the following command can be used to change a table's storage engine:

ALTER TABLE MyTable

ENGINE = InnoDB;

You can also change the default storage engine for MySQL by modifying the server configuration variable storage_engine .

3.1 MyISAM

The MyISAM storage engine is the default file access mechanism for MySQL, which is used by all tables that are created without explicitly setting the engine option in the CREATE statement. In addition, MyISAM has a table-level locking mechanism for concurrent operations : When a process updates a table, other processes will not be able to access any of the data in the tables until the operation is completed. MyISAM The advantages are high reliability, wide application range and fast data retrieval speed. MyISAM is the preferred storage engine where data retrieval speed (read performance) is emphasized .

The biggest advantage of ISAM is that the index is very small, and the vast majority of the index of the ISAM tables can be placed in the memory index buffer, so the search is very fast.

3.2 InnoDB

InnoDB is almost always used in applications that need to support transactional processing. The InnoDB supports traditional acid (atomicity, consistency, isolation, durability) transaction processing principles and FOREIGN KEY constraint mechanisms. InnoDB all indexes are in B-tree structure-The index is placed in the leaf node. InnoDB improves concurrency control for MyISAM, which provides row-level locking. With emphasis on reliability and support for transactional processing, InnoDB is the principal storage engine.

3.3 BDB

The BDB storage engine is considered a replacement product for InnoDB, supporting transactions as well as additional transactional functions such as commit and rollback. BDB supports hash lists, B-trees, simple storage mechanisms based on record numbers, and persistent queries. But in the near future, BDB may become an unsupported storage engine (don't understand?). ）。

3.4 Memory

Memory storage engine (sometimes called the heap table) is a memory-resident table that uses a hashing mechanism to speed up the retrieval of commonly used data. The table is much faster than the table stored on disk. They are not different from other storage engines in use, except that the data is stored in memory and only works during MySQL sessions . When the system shuts down (or crashes), the data in the heap table is lost. The memory storage engine is particularly well-suited for storing static data that is accessed frequently but seldom needs to be modified , such as a dictionary table such as postal code, country, region, product catalog, and so on. The heap table can also be used in databases that use snapshot technology to provide distributed or historical data access services.

3.5 Merging

The merge storage engine is built with a MyISAM table with the same structure (tuple layout or pattern), and the effect is that those tables can be used as a single large table. The tables are differentiated according to their respective positions. There is no need to use an additional partitioning mechanism. All tables must reside on the same machine (accessed through the same server). users can access all the data in those tables simply by using statements such as a single operator or select, UPDATE, insert, and delete. Fortunately, the drop command issued on the merge table only relieves those tables from merging, and does not alter those original tables.

The greatest advantage of this type of table is speed. With the merge storage engine, you can divide a large table into smaller tables and save them on separate disks, merging them together to access them at the same time through some merge table rules. But the merge storage engine has several drawbacks :

A, the scope of the merge storage engine is limited to the MyISAM table.

B, do not allow the substitution operation

C, using an index to access a consolidated table is less efficient than a normal table

The merge storage mechanism is suitable for use in very large database applications, such as a data warehouse that needs to spread data across multiple tables or even multiple databases because of the size of the data.

3.6 Archives

The Archive storage Engine (archive storage engines) is a mechanism for saving large amounts of data in some form of compression. Archival storage mechanisms are best suited for storing and retrieving data that is not frequently accessed by archival or historical accumulation.

The archive storage engine does not provide any indexing mechanism, and the only way to access it is to scan the entire table. Therefore, the archival storage engine is not suitable for routine database storage and retrieval operations.

3.7 Union

The Federated Storage Engine (fedreted storage engine) is a mechanism for creating a table from multiple database systems. The Federated storage Engine works much like the merge storage engine, but he also allows you to link data (tables) from multiple database servers. The main use of this mechanism is also to link the tables from other database systems. The federated storage Engine is best suited for use in distributed or data warehousing environments.

The most fascinating part of the federated storage Engine is that it does not move data, nor does it require that the remote table be the same storage engine, and that the federated storage Engine automatically completes the necessary transformations during the storage and retrieval of the data. This fully embodies the power of the plug-in storage engine layer.

3.8 Cluster/NDB

The cluster storage engine (cluster storage engine) (referred to as NDB when required to differentiate itself from cluster products) provides MySQL with the ability to cluster servers. The basic purpose of the cluster storage engine is to centralize the use of multiple MySQL servers to provide database services in a highly available and high-performance environment. The cluster storage engine does not store any data, and the data storage and retrieval operations are performed by the storage engine used by each of the databases in the cluster, and the cluster storage engine is only responsible for controlling how the data is distributed to the elder brother database in the cluster to provide redundancy and improve performance. The NDB storage engine also provides an API for people to create scalable clustering solutions.

3.9 CSV

The CSV storage engine is used to create and read table files in CSV format. The CSV storage engine does not need to copy data to another format, and the CSV table's metadata is stored directly in the database folder on the server, along with his file name. The CSV storage engine allows database users to quickly and easily use structured business data generated by spreadsheet software. The CSV storage engine does not provide any indexing mechanism.

3.10 Black Holes

Black hole storage engines (Blackhole storage engine) allow the system to write data, but do not actually save the data written. However, if the binary logging feature is activated, the relevant SQL statements will be recorded in the log. This kind of storage engine is handy when you just want to test an app to make sure it's writing data, rather than trying to really save that data.

3.11 Customization

Custom storage engine is a storage engine that you create yourself to improve your database server. For example, you want to create a storage engine to read an XML file.

MySQL cache, storage engine