mysql innodb 调优_MySQL innodb优化总结

MySql插入有3中模式

Simple inserts

就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()

Bulk inserts

就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA

Mixed-mode inserts

下面两种，不确定是否需要分配auto_increment id

INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');

INSERT … ON DUPLICATE KEY UPDATE

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

这种方式就和mysql5.1.22以前一样，为了向后兼容而保留了这种模式，如同前面介绍的一样，这种方式的特点就是“表级锁定”，并发性较差

二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

这种方式是新版本中的默认方式，推荐使用，并发性相对较高，特点是“consecutive”，即保证同一条insert语句中新插入的auto_increment id都是连续的。

这种模式下,对应MySQL的三种插入模式,会有三种不同的插入方法(见上面mysql的3种插入方式)：

“Simple inserts”：直接通过分析语句，获得要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。

“Bulk inserts”：因为不能确定插入的数量，因此使用和以前的模式相同的表级锁定。

“Mixed-mode inserts”：直接分析语句，获得最坏情况下需要插入的数量，然后一次性分配足够的auto_increment id，只会将整个分配的过程锁住。需要注意的是，这种方式下，会分配过多的id，而导致”浪费“。比如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');会一次性的分配5个id，而不管用户是否指定了部分id；INSERT ... ON DUPLICATE KEY UPDATE一次性分配，而不管将来插入过程中是否会因为duplicate key而仅仅执行update操作。

注意：当master mysql版本<5.1.22，slave mysql版本>=5.1.22时，slave需要将innodb_autoinc_lock_mode设置为0，因为默认的innodb_autoinc_lock_mode为1，对于INSERT ... ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');的执行结果不同，现实环境一般会使用INSERT ... ON DUPLICATE KEY UPDATE。

三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

这种模式是来一个分配一个，而不会锁表，只会锁住分配id的过程，和innodb_autoinc_lock_mode = 1的区别在于，不会预分配多个，这种方式并发性最高。但是在replication中当binlog_format为statement-based时(简称SBR statement-based replication,就是那个只记录mysql语句的binglog模式)存在问题，因为是来一个分配一个，这样当并发执行时，“Bulk inserts”在分配的时会同时向其他的INSERT分配，会出现主从不一致(从库执行结果和主库执行结果不一样)，因为binlog只会记录开始的insert id。