delay_key_write

This option applies only to MyISAM tables. It can have one of the following values to affect handling of the DELAY_KEY_WRITE table option that can be used in CREATE TABLE statements.

If DELAY_KEY_WRITE is enabled for a table, the key buffer is not flushed for the table on every index update, but only when the 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-recover-options option (for example, --myisam-recover-options=BACKUP,FORCE). See Section 5.1.3, “Server Command Options”, and Section 14.4.1, “MyISAM Startup Options”.

Another performance option in MySQL is the DELAY_KEY_WRITE option. According to the MySQL documentation the option makes 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 with FLUSH TABLES; but here‘s how they are closed automatically according to the docs:

• 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_cache entries and a thread is no longer using a table.
• FLUSH TABLES; is called.

If DELAY_KEY_WRITE is 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-recover option (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.

1.
LOCK TABLES `test` WRITE;
ALTER TABLE `test` DISABLE KEYS ;

INSERT INTO `test` VALUES (1,‘???‘,80,1),(2,‘???‘,90,2),(1,‘李四‘,80,3),(2,‘王五‘,90,4),(1,‘aa‘,12,5), (3,‘aa‘,123,6),(4,‘aadwa‘,123,7);

ALTER TABLE `test` ENABLE KEYS;
UNLOCK TABLES;

2.

最后看了 binlog 仅1小时 有个表 有1w多次 UPDATE操作，

当delay_key_write 使用的时候，如果出现重启或者掉电等情况，会导致在cache的索引update没来得及更新，所以必须在启动参数加上--myisam-recover，或者在conf设置myisam-recover=BACKUP,FORCE。这样在你启动mysql的时候会检查你的表并同步表和索引.

常用MySQL的童鞋都知道这个myisam类型的表极容易损坏，多数人可能都是用myisamchk命令来人工修复，下面介绍一种自动修复myisam的方法，也是我上午刚学的，共同进步，呵呵~
在MySQL的配置文件my.cnf中，启动项部分加入myisam-recover设置数据恢复功能，具体参数如下：
DEFAULT
与没有使用--myisam-recover选项相同。
BACKUP
如果在恢复过程中，数据文件被更改了，将tbl_name.MYD文件备份为tbl_name-datetime.BAK。
FORCE
即使.MYD文件将丢掉多个行也进行恢复。
QUICK
如果没有删除块，不要检查表中的行。
我设置了BACKUP和FORCE参数，如下：
[mysqld]
myisam-recover=BACKUP,FORCE

参考：

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_delay_key_write

http://www.petefreitag.com/item/441.cfm

http://www.cnblogs.com/zhizhesky/archive/2011/08/22/2149357.html

MySQL DELAY_KEY_WRITE Option,布布扣,bubuko.com

MySQL DELAY_KEY_WRITE Option