INSERT DELAYED使用总结

今天研究了下insert delayed这个特性。

背景
RD反馈，他有一个异步的队列缓冲表，现在在做优化，看如果用insert delayed能不能提升性能？这个业务要求呢，本来就是异步插入到数据库的，所以并不是那么急着访问。听着，好像延迟插入也没什么不妥哈。
环境：mysql 5.6 innodb表
MySQL的这个特性只是听说过，没仔细研究过，趁这个机会研究下。

结论
首先查了下官网资料：
MySQL的这个特性，是MySQL对标准SQL的一个扩展，从MySQL 3.22.15 引入，5.6已经不推荐使用，5.7已经不支持了（虽然能识别，但是已经被忽略掉，而且会生成ER_WARN_LEGACY_SYNTAX_CONVERTED警告），在后续的版本中会废弃掉。
5.1：
mysql> show create table t2G
1. row **

   Table: t2

Create Table: CREATE TABLE t2 (
id int(11) DEFAULT NULL,
name char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert delayed into t2(id,name) values(1,'a');
ERROR 1616 (HY000): DELAYED option not supported for table ‘t2'

5.6：
mysql> show create table t1G
1. row **

   Table: t1

Create Table: CREATE TABLE t1 (
id int(11) DEFAULT NULL,
name char(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert delayed into t1(id,name) values(1,'a');
Query OK, 1 row affected, 1 warning (0.00 sec)

1 row in set (0.00 sec)

insert delayed研究
insert delayed使用限制：

• INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. For engines that do not supportDELAYED, an error occurs.
• insert delayed 只适用于MyISAM、MEMORY、ARCHIVE、BLACKHOLE引擎的表，对于不支持的引擎会报错。
• An error occurs for INSERT DELAYED if used with a table that has been locked with LOCK TABLES because the insert must be handled by a separate thread, not by the session that holds the lock.
• insert delayed必须由单独的线程完成，如果线程已经持有lock tables锁，则insert delayed会报错。
• For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERTstatements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM.
• 对于MyISAM表，如果在数据文件中没有空闲块，支持并发的select和insert。在这种情况下，你会很少需要使用insert delayed。
• INSERT DELAYED should be used only for INSERT statements that specify value lists. The server ignores DELAYED forINSERT ... SELECT or INSERT ... ON DUPLICATE KEY UPDATE statements.
• insert delayed 只适用于指定具体值的insert，对于insert...select...或insert...on duplicate update不支持。
• Because the INSERT DELAYED statement returns immediately, before the rows are inserted, you cannot useLAST_INSERT_ID() to get the AUTO_INCREMENT value that the statement might generate.
• insert delayed执行完会立马返回结果。但是insert delayed是缓存在内存中，并没有真正的执行，不能使用LAST_INSERT_ID()来获取表的自增键。
• DELAYED rows are not visible to SELECT statements until they actually have been inserted.
• delayed的行在未真正插入数据库前，不能使用select查出结果。

• INSERT DELAYED is handled as a simple INSERT (that is, without the DELAYED option) whenever the value of binlog_format is STATEMENT or MIXED. (In the latter case, the statement does not trigger a switch to row-based logging, and so is logged using the statement-based format.)
  This does not apply when using row-based binary logging mode (binlog_format set to ROW), in which INSERT DELAYED statements are always executed using the DELAYED option as specified, and logged as row-update events.
• DELAYED is ignored on slave replication servers, so that INSERT DELAYED is treated as a normal INSERT on slaves. This is because DELAYED could cause the slave to have different data than the master.
• 在从库上，delayed会被忽略。这是因为delayed可能会导致主从数据不一致。
• Pending INSERT DELAYED statements are lost if a table is write locked and ALTER TABLE is used to modify the table structure.
• 如果表被写入索引，并且使用alter table来修改表结构，则挂起的insert delayed将会丢失。
• INSERT DELAYED is not supported for views.
• insert delayed不支持视图。
• INSERT DELAYED is not supported for partitioned tables.
• insert delayed不支持分区表。
  The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the “thread” is the thread that received an INSERT DELAYED statement and “handler” is the thread that handles all INSERT DELAYED statements for a particular table.

下面详细描述当您使用INSERT或REPLACE的DELAYED选项时会发生什么情况。 在此描述中，“线程”是接收到INSERT DELAYED语句的线程，“处理程序”是处理特定表的所有INSERT DELAYED语句的线程。

• When a thread executes a DELAYED statement for a table, a handler thread is created to process all DELAYEDstatements for the table, if no such handler already exists.
• 当一个线程为一个表执行一个DELAYED语句时，如果没有这个处理程序，就会创建一个处理程序线程来处理表的所有DELAYED语句。
• The thread checks whether the handler has previously acquired a DELAYED lock; if not, it tells the handler thread to do so. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler waits for all ALTER TABLE locks or FLUSH TABLES statements to finish, to ensure that the table structure is up to date.
• 线程检查处理程序以前是否获取了DELAYED锁; 如果没有，它会通知处理程序线程这样做。 即使其他线程在表上具有READ或WRITE锁，也可以获得DELAYED锁。 但是，处理程序将等待所有ALTER TABLE锁定或FLUSH TABLES语句完成，以确保表结构是最新的。
• The thread executes the INSERT statement, but instead of writing the row to the table, it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.
• 线程执行INSERT语句，但不是将行写入表中，而是将最后一行的副本放入由处理程序线程管理的队列中。 任何语法错误都被线程注意到并被报告给客户端程序。
• The client cannot obtain from the server the number of duplicate rows or the AUTO_INCREMENT value for the resulting row, because the INSERT returns before the insert operation has been completed. (If you use the C API, the mysql_info() function does not return anything meaningful, for the same reason.)
• 客户端无法从服务器获取重复行数或结果行的AUTO_INCREMENT值，因为INSERT在真正的插入操作完成之前返回。 （如果使用C API，出于同样的原因，mysql_info（）函数不会返回任何有意义的内容。
• The binary log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the binary log is updated when the first row is inserted.
• 当行插入到表中时，二进制日志由处理程序线程更新。 在多行插入的情况下，插入第一行时更新二进制日志。
• Each time that delayed_insert_limit rows are written, the handler checks whether any SELECT statements are still pending. If so, it permits these to execute before continuing.
• 每次写入delayed_insert_limit行时，处理程序都将检查是否有任何SELECT语句仍处于待处理状态。 如果是这样，它允许这些在继续之前执行。
• When the handler has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED statements are received within delayed_insert_timeout seconds, the handler terminates.
• 当处理程序的队列中没有更多的行时，表将被解锁。 如果在delayed_insert_timeout秒内没有收到新的INSERT DELAYED语句，则处理程序终止。
• If more than delayed_queue_size rows are pending in a specific handler queue, the thread requesting INSERT DELAYED waits until there is room in the queue. This is done to ensure that mysqld does not use all memory for the delayed memory queue.
• 如果多于delayed_queue_size行在特定的处理程序队列中挂起，则请求INSERT DELAYED的线程将等待，直到队列中有空间。 这样做是为了确保mysqld不会将全部内存用于延迟内存队列。
• The handler thread shows up in the MySQL process list with delayed_insert in the Command column. It is killed if you execute a FLUSH TABLES statement or kill it with KILL thread_id. However, before exiting, it first stores all queued rows into the table. During this time it does not accept any new INSERT statements from other threads. If you execute an INSERT DELAYED statement after this, a new handler thread is created.
• 处理程序线程显示在Command列中的delayed_insert的MySQL进程列表中。 如果你执行一个FLUSH TABLES语句或者用KILL thread_id杀死它，它就会被杀死。 但是，在退出之前，它首先将所有排队的行存储到表中。 在此期间，它不接受来自其他线程的任何新的INSERT语句。 如果在此之后执行INSERT DELAYED语句，则会创建一个新的处理程序线程。
  This means that INSERT DELAYED statements have higher priority than normal INSERT statements if there is an INSERT DELAYED handler running. Other update statements have to wait until the INSERT DELAYED queue is empty, someone terminates the handler thread (with KILL thread_id), or someone executes a FLUSH TABLES.
• The following status variables provide information about INSERT DELAYED statements.
  Status Variable

Meaning
Delayed_insert_threads
Number of handler threads
Delayed_writes
Number of rows written with INSERT DELAYED
Not_flushed_delayed_rows
Number of rows waiting to be written