这个参数是指，在表关闭之前，将对表的update操作指跟新数据到磁盘，而不更新索引到磁盘，把对索引的更改记录在内存。在关闭表的时候一起跟新索引到磁盘。
- 这个参数只对MyISAM引擎表有作用。你可以在create table的时候，指定DELAY_KEY_WRITE。或者ALTER TABLE table_name DELAY_KEY_WRITE= 1，当表已经存在的时候。
- 如果你的某个表有很多update操作，这个参数的优势会很好的体现出来。因为这个参数能延迟更新索引到表关闭。当我们需要经常跟新一个大表的时候，可以考虑使用这个参数。
那么，表关闭会在什么时候发生？你可以理解成当flash table的时候，表将关闭。那么有2种情况将会发生 flush table：
- 当cache 满了一个新的thread试图打开一个表的时候，那个表没有在cache；
- 当cache里的表数比table_cache多时thread不在使用表；
- 调用flush table命令的时候；
这个2种情况将会flush table。
当然，你也可以直接设置启动参数flush_time ，设置每多少时间flush table一次。
当DELAY_KEY_WRITE使用的时候，如果出现重启或者掉电等情况，会导致在cache的索引update没来得及更新，所以必须在启动参数加上--myisam-recover，这样在你启动mysql的时候会检查你的表并同步表和索引.
要注意的是，但如果你使用该特性，你应用--myisam-recover选项启动服务器，为所有MyISAM表添加自动检查
############还有另外一篇英文的，好像就是这个的原版，也记下来#############
Another performance option in MySQL is the DELAY_KEY_WRITE option. According to theMySQL documentation the optionmakes index updates faster because
they are not flushed to disk until the table is closed.
Note that this option applies only to MyISAM tables,
You can enable it on a table by table basis using the following SQL statement:
ALTER TABLE sometable DELAY_KEY_WRITE = 1;
This can also be set in the advanced table options in the MySQL Query Browser.
This performance option could be handy if you have to do a lot of update, because you can delay writing the indexes until tables are closed. So frequent updates to large tables, may want to check out this option.
Ok, so when does MySQL close tables?
That should have been your next question. It looks as though tables are opened when they are needed, but then added to the table cache. This cache can be flushed manually withFLUSH TABLES; but here's how they are closed automatically according
to thedocs:
- When the cache is full and a thread tries to open a table that is not in the cache.
- When the cache contains more than
table_cacheentries and a thread is no longer using a table. FLUSH TABLES;is called.
If
DELAY_KEY_WRITEis enabled, this means that the key buffer for tables with this option are not flushed on every index update, but only when a table is closed. This speeds up writes on keys a lot, but if you use this feature, you
should add automatic checking of all MyISAM tables by starting the server with the--myisam-recoveroption (for example,--myisam-recover=BACKUP,FORCE).
So if you do use this option you may want to flush your table cache periodically, and make sure you startup using the myisam-recover option.