MySQL parameter delay_key_write

This parameter means that before the table is closed, the update operation of the table refers to the disk with the new data instead of the index to the disk, and the changes to the index are recorded in the memory. In this way, the MyISAM table can make index updates faster. When the table is closed, the index is updated to the disk.

 

This parameter only applies to MyISAM engine tables. You can specify delay_key_write when creating table. Or alter table table_name delay_key_write = 1, when the table already exists.

If a table has many update operations, the advantages of this parameter will be well reflected. Because this parameter can delay updating the index to the table. When we need to update a large table frequently, we can consider using this parameter.

 

When will the table be closed? You can understand that when the flash table is used, the table will be closed. In either case, flush table will occur:
When the cache is full and a new thread tries to open a table, the table is not in the cache;
When the number of tables in the cache is more than that in table_cache, thread does not use the table;
In both cases, the table is flushed.

 

Of course, you can also set the start parameter flush_time to set the flush table time.

 

When delay_key_write is used, if a restart or power loss occurs, the index update in the cache cannot be updated. Therefore, you must add -- MyISAM-recover to the startup parameter, in this way, when you start MySQL, you will check your table and synchronize the table and index. or run it before restarting the server.Myisamchk. (However, even in this case, use delay_key_write to ensure that data is not lost, because the keyword information can always be generated from the data row ).

 

Note that if you use this feature, you can use the -- MyISAM-Recover option to start the server and add automatic checks for all MyISAM tables.