[php] view plain
delete from table_name where 1;
可用last_insert_id()获取刚刚自增过的值。
-
关于mysql auto_increment所带来的锁表操作
在mysql5.1.22之前,mysql的“INSERT-like”语句(包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住,直到整个语句结束(而不是事务结束)。
因此在使用INSERT…SELECT、INSERT…values(…),values(…)时,LOAD DATA等耗费时间较长的操作时,会将整个表锁住,而阻塞其他的“INSERT-like”、Update等语句,推荐使用程序将这些语句分成多条语句,一一插入,减少单一时间的锁表时间。
mysql5.1.22之后mysql进行了改进,引入了参数
innodb_autoinc_lock_mode,通过这个参数控制mysql的锁表逻辑。
在介绍这个之前先引入几个术语,方便说明 innodb_autoinc_lock_mode。
1.“INSERT-like”:
INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()
2.“Simple inserts”:
就是通过分析insert语句可以确定插入数量的insert语句, INSERT, INSERT … VALUES(),VALUES()
3.“Bulk inserts”:
就是通过分析insert语句不能确定插入数量的insert语句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA
4.“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 mod,传统模式)。这种方式就和mysql5.1.22以前一样,为了向后兼容而保留了这种模式,如同前面介绍的一样,这种方式的特点就是“表级锁定”,并发性较差。
二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode,连续模式)。这种方式是新版本中的默认方式,推荐使用,并发性相对较高,特点是“consecutive”,即保证同一条insert语句中新插入的auto_increment id都是连续的。
这种模式下:
“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)存在问题,因为是来一个分配一个,这样当并发执行时,“Bulk inserts”在分配的时会同时向其他的INSERT分配,会出现主从不一致(从库执行结果和主库执行结果不一样),因为binlog只会记录开始的insert id。
测试SBR,执行begin;insert values(),();insert values(),();commit;会在binlog中每条insert values(),();前增加SET INSERT_ID=18/*!*/;。
但是row-based replication RBR时不会存在问题。
另外RBR的主要缺点是日志数量在包括语句中包含大量的update delete(update多条语句,delete多条语句)时,日志会比SBR大很多;假如实际语句中这样语句不是很多的时候(现实中存在很多这样的情况),推荐使用RBR配合innodb_autoinc_lock_mode,不过话说回来,现实生产中“Bulk inserts”本来就很少,因此innodb_autoinc_lock_mode = 1应该是够用了。
最后说一句今天遇到这个问题,在LOCK_MODE为AUTO_INC,而且事物回滚非常的慢,不得已停止数据库重新启动
mysql> select * from innodb_locks;
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
| 2954466:518 | 2954466 | AUTO_INC | TABLE | `test`.`kkkm` | NULL | NULL | NULL | NULL | NULL |
| 2954465:518 | 2954465 | AUTO_INC | TABLE | `test`.`kkkm` | NULL | NULL | NULL | NULL | NULL |
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
本文题目:mysql的auto_increment详解
分享路径:
http://cqcxhl.cn/article/jhcijg.html