MySQL's plug-in storage engine

Reprint https://www.zhihu.com/question/19866767/answer/14942009

http://whuai.blog.51cto.com/3539000/862197

• Oracle: Data files include: Control files, data files, redo log files, parameter files, archive files, password files. This is divided according to the function line of the file, and all the files are binary encoded files, the efficiency of the database algorithm is greatly improved. Because of the unity of Oracle file management, you can specify a unified standard for parsing and optimizing SQL execution:
  RBO (rule-based optimizer), CBO (cost-based optimizer)
  The optimization of CPU, memory and IO resources is optimized by optimizing the selection of the optimizer, as well as the invincible hint rules, which gives great freedom to SQL optimization.
• MySQL: One of the biggest features is the freedom to choose the storage engine. Each table is a file, and you can choose the appropriate storage engine. Common engines are InnoDB, MyISAM, Ndbcluster and so on. But because of this open plug-in storage engine, such as the need for database and the loose coupling between the engine. This results in a significantly reduced file consistency. There are some unavoidable bottlenecks in SQL execution optimization. It is a soft rib in multi-table association, sub-query optimization, statistic function, and only supports very simple hint.
• SQL Server: The data schema is basically vertically divided into: Protocol layer (Protocol level), relational engine (relational engines), Storage engine (storage engine), Sqlos. The process of SQL execution is the process of parsing by layer, where the optimizer in relational engine is cost-based (CBO), and its working process is very similar to that of Oracle. On top of the cost is also a lot of support hint, including: Connection hints, query hints, table hints.
• • If you use a different database, such as Oracle or SQL Server, you will find that there are many tables under a database, and there is no need to specify the storage engine when creating the table.
    Why don't you specify that there is no storage engine on behalf of Oracle and SQL Server, in fact there is a storage engine for all databases, and Oracle and SQL Server have
    The entire Oracle or SQL Server has only one storage engine, so you don't need to specify the storage engine when you create the table, because there's only one storage engine.
    While MySQL, he has multiple storage engines, you don't have to use this storage engine can be used by another storage engine, either you choose
    
    1, based on the table: he is not based on the database, that is, when you create the database is unable to specify the storage engine, create DATABASE xxx engine=xx, can only be table-based
    When creating a table, specify why you cannot specify the storage engine for the database, but only the storage engine of the table.
    The problem is simple: there are multiple tables under a database, each table may be used differently, some for archiving, some for historical queries, and some for transactional support, so you can create a table to match your business situation by specifying the appropriate storage engine for your table business.
    If you are based on a database, then you cannot do this, no matter what the business table under the database is business, can only obey the storage engine specified when the database was created, then there is no point.
    
    2. Plug-in type
    Plug-in means that you can change your table at any time using the storage engine, such as when you build the table with the InnoDB, with a period of time, use uncomfortable, you can change the storage engine at any time, engine=xx, change to another storage engine, this is plug-in, can be changed at any time storage engine, Plug and unplug at any time

    • In MySQL, there are also many types of system objects, including tables, views, stored procedures, storage functions, etc., but because of MySQL's plug-in storage engine and other implementation features, each of its objects are cached differently, or that the cache of these objects is not managed in a unified manner, Each object's cache has its own characteristics, and the content of the cache is also very different, the following is the first to describe the Table object caching.

      The table Dictionary object cache, as its name implies, caches the dictionary information (defined content) of a Table object in memory to improve the efficiency of table access. Once a table has been accessed for the first time, when the server is not closed and the table definition has not been modified, access to the table is only necessary to find the cached object from memory and do the corresponding operation, without having to read its definition and parse it from the system table again, and then do the corresponding operation.

      When a user queries the data for a table, the system first finds the table. As already mentioned above, because MySQL implements the table cache, so first will look for this table from the Cache, table Dictionary object cache is managed by the hash table, MySQL system, there is a hash table (the source code name is Table_def_ Cache) is used to store organizational table objects.

      So first the name of the table (including the schema name) to construct a hash key value (key), used to search for objects from the hash table.

      But for the table object's cache, not simply to save some of the table's definition through the hash, then the cache may have no meaning, or the limitations are very large, this may cause a user to do on the Table object what flags or modifications will affect other users, This impact is unpredictable, and more importantly, MySQL is a plug-in database, each user to get the Table object will need to instantiate the table, this instantiation of the object can only be used by themselves, so not simple all users use the same cache object can be done. Its cache is actually used a kind of can be called "Shared private cache", it seems that this argument is contradictory, in fact, it is not the case, it uses a structure called Table_share in the caching process, a struct that only corresponds to a Table object in MySQL, This is not a distinction between any storage engine, it is actually a definition of a specific table translation or mapping, that is, when the need to open a table, the table is first stored in the MySQL system table (of course, the system table is divided into different storage engines, different storage engines have their own system tables, The MySQL system table described here should be a general name, in fact, a specific storage engine system table, if you want to use, the first need to read all the information from the system table in memory in the table, the information includes the table names, schema names, all column information, column default values, table character set, The path of the corresponding frm file, the owning storage-indexing engine (the table in MySQL can define its own storage engine separately), the primary key, and so on, of course, there is a lot of other information, all of which are read into memory first through the structure of the Table_share to store, The equivalent of this struct is the first level of a Table object cache, and it can be seen from the name that the struct is a table object that the user can share, so it is static, not allowed to be modified (in-memory), read from the system table, until the table is deleted from the cache, without any changes in the middle.

      Then the user to access a table, just constructs the table_share is not enough, and this struct object is not directly to the user's object, after constructing the structure, the first need to cache it, because this structure is the core of our discussion here, it is the object we want to cache , so we first need to cache this table object into Table_def_cache based on the key computed above, and this cache operation ends here.

      However, if this question has been visited before, then it is not necessary to construct the shared structure as above, but rather directly through the hash key value in the Table_def_cache to find the shared structure.

      From the above narrative know, when the system gets a SHARE object, the system will actually construct a new object to the current operation, this object has already said, certainly not table_share, because this is the cache object, it is static, read-only, What really interacts with the user is a derivative of table_share, which corresponds to a struct whose name is table, which is the real object used in the operation, so how does it change from table_share to table?

      In fact, many of the members of the two structures are the same, and can be copied directly past, it has been said that Table_share is a static cache object, so relatively speaking, TABLE can be called a relatively dynamic, a number of instances of the operation, One of the members in table is directly pointing to the Table_share, and some members, such as the record, are used to construct a record in the insert operation, which is constructed in advance based on each column defined by the table and its data type. field is used to store all of the column information in this table, and this information actually clones the information in the share completely. Some of the other small details are not described, but there are two very important things to say.

      As mentioned above, the object of table is a dynamic, instantiated object, it is an open table, it is not in the upper layer of MySQL, but is specific to a certain storage engine, so here also need to construct its information about the storage engine and open this table.

      Because MySQL is a plug-in database management system, for the management of Table objects, MySQL layer and storage engine layer is here separate, table is a bridge between them, the lower layer is the storage engine, the upper layer is MySQL, for a MySQL storage engine, Have to provide some common interfaces to drive their storage engines, including: Close_connection, Savepoint_set, Savepoint_rollback, Savepoint_release, Commit, rollback , create (creating a handle), ha_create (Create a table), Ha_open (Open Table), Ha_close (Close table), Ha_write_row (insert a record), Ha_delete_row (delete a record), Ha_ Reset, which restores the instance to the newly opened state, is an upper-level call to operate on the corresponding storage engine, or it can be referred to as a channel of communication between MySQL and the storage engine.

      Then the transition from share to table, or the instantiation process called share, requires that the function create be called before creating a corresponding storage engine handle, which is then opened by the function Ha_open to open the table. Opening a table is primarily about initializing the newly created storage engine handle. After opening, the instantiation of the table is completed, and this opened instance handle hangs in the table structure, as can be seen from here, the table is an instantiated object corresponding to an operation, it can only be used by an operation at the same time.

      After being instantiated, this can interact directly with the storage engine, such as inserting a record that can directly invoke the function Ha_write_row under the stored engine handle that has been instantiated under the table.

      When an operation is completed, it is not necessary to instantiate the table, at this time the system is not to release the local instance directly, but to save it, and save it for the next time a user accesses the table again without the need to instantiate again, directly take over to use, Of course, some additional actions may be required, such as recovering the instance state and calling the function Ha_reset.

      The system save instance is placed directly in the share of a free_tables linked list, but first to be removed from the used_tables linked list, both of which are used to save all instances of the table, Used_tables used to store the instance being used, free_ Tables is used to store all currently unused instances, it is possible in the case of high concurrency, there may be multiple in the used_tables, but not in the free_tables, after the completion of execution is the opposite, then if there are users then manipulate the table, the system can directly from the Free_ Tables find one to use.

      Now you can know that in MySQL, Table object cache is actually two parts, part is share cache, that is, multiple different tables of share object cache, and the other part is the cache of each instance object after the share structure is instantiated. The method that MySQL uses to manage the size of cache space is realized by counting, by default, the total number of share in the system cannot exceed 400, and the number of all table instances of all share cannot exceed 400.

      All of the above is about the Table object share structure of the cache, since it is the cache, it must have been deleted or eliminated the problem, of course, here is no exception. So under what circumstances will the share structure be eliminated or deleted? Obviously, if the table is only to be modified and so does not involve the modification of the table definition operation, share is not deleted, only may be eliminated, because if the query too many tables, the Table object cache number is limited, when the number is reached, The system will automatically eliminate some infrequently used share, which is easy to understand.

      In general, only after modifying the table structure, dependencies, table definitions, and so on, because the version of the table is updated, if it is not safe to continue caching it, or is wrong, or causes some unpredictable problems, So in this case, the cached share object of the Table object must be removed from the cache, and the Table object cache structure that is instantiated on top of it is deleted, so that when the other user waits for the table object to be modified (because the table is locked during the modification, the operation needs to wait), Again, as described above, the first is to find the cache object of the table from the cache, if not found, then read it from the Data dictionary (system table), and then proceed.

      Here is a description of the table's cache and some other content.

      Summarize:

      1) The caching mechanism of the table mentioned above is of great benefit, because it is not a full dictionary cache (full dictionary caching means that all data dictionary information is loaded into memory at the start of the database, so it is very efficient in use, but there is a large shortage of DDL operations), This implementation reduces the cost of the dictionary cache maintenance that is caused by DDL operations or the rollback of the DDL, as it is used to load and then be removed directly after the modification and then re-loaded.

      2) effective use of memory space, because you can set the size of the Table object cache space to control the use of memory, and only used objects will be loaded into memory, improve the memory utilization.

      3) The above described in the MySQL table cache implementation scheme is relatively advanced, but in terms of efficiency is still some optimization of space, such as mentioned above, in order to control the size of the cache space is based on the number of instances of the calculation, the default maximum value in the system is 400, If more than this value system will automatically eliminate some of the infrequently used instances, but if the definition of a table is very large, then in the case of concurrency, it is possible to create many instances, assuming that nearly 400, then it is possible to calculate the operating system memory, this is not controllable, is not predictable. The same is true for share caches, which can have the same problem if a user accesses a large number of different defined tables.

      4) from the above can be seen, in order to achieve plug-in database, in fact, there are some cost of efficiency, in the table cache, the middle of a layer of share cache, the real use of the time also need to instantiate, because each user's operation and different time state are different, Therefore, each user must again on the basis of share to instantiate a new object out, so that the memory, CPU brings a certain program of waste and pressure.

      Solution to the problem:

      1) Share cache: I personally think there is a better way to manage table cache space precisely by the size of the space, because for share, it is static, it is a struct, by using a count to control the use of memory, there may be a case of running out of memory, then for the share object , it can be streamed (flattened), that is, the size of the structure to calculate, to apply for the corresponding space, the structure of all the information in a fixed order to write to this memory, so that the size of a share space is fixed, This can be done by setting the space size to manage the Table object cache space, so the above mentioned memory light problem naturally solved, of course, this size needs to be based on the computer's memory size reasonable settings, at least not unpredictable problems.

      2) Table cache: The cache of table instances also has the above problem, the solution is similar to the above idea, because this object is always used, it is an instance, so it can not be directly like the above, the flow of it, but by applying for a connection of space, All pointers in this instance, or the values of their members, point to (possibly aligned) the specified position in this space, so that the use of the struct does not change, but it occupies a fixed amount of space, the same can be done manually by the user to set the size of the table instance cache space to manage the cache space, This also avoids the problem that the table definition is too large to cause memory to run out.

      This article is from the "Waiching" blog, make sure to keep this source http://whuai.blog.51cto.com/3539000/862197

MySQL's plug-in storage engine