[php] view plain
delete from table_name where 1;
可用last_insert_id()獲取剛剛自增過的值。
-
關(guān)于mysql auto_increment所帶來的鎖表操作
在mysql5.1.22之前,mysql的“INSERT-like”語句(包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)會(huì)在執(zhí)行整個(gè)語句的過程中使用一個(gè)AUTO-INC鎖將表鎖住,直到整個(gè)語句結(jié)束(而不是事務(wù)結(jié)束)。
因此在使用INSERT…SELECT、INSERT…values(…),values(…)時(shí),LOAD DATA等耗費(fèi)時(shí)間較長的操作時(shí),會(huì)將整個(gè)表鎖住,而阻塞其他的“INSERT-like”、Update等語句,推薦使用程序?qū)⑦@些語句分成多條語句,一一插入,減少單一時(shí)間的鎖表時(shí)間。
mysql5.1.22之后mysql進(jìn)行了改進(jìn),引入了參數(shù) innodb_autoinc_lock_mode,通過這個(gè)參數(shù)控制mysql的鎖表邏輯。
在介紹這個(gè)之前先引入幾個(gè)術(shù)語,方便說明 innodb_autoinc_lock_mode。
1.“INSERT-like”:
INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()
2.“Simple inserts”:
就是通過分析insert語句可以確定插入數(shù)量的insert語句, INSERT, INSERT … VALUES(),VALUES()
3.“Bulk inserts”:
就是通過分析insert語句不能確定插入數(shù)量的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,傳統(tǒng)模式)。
這種方式就和mysql5.1.22以前一樣,為了向后兼容而保留了這種模式,如同前面介紹的一樣,這種方式的特點(diǎn)就是“表級(jí)鎖定”,并發(fā)性較差。
二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode,連續(xù)模式)。
這種方式是新版本中的默認(rèn)方式,推薦使用,并發(fā)性相對(duì)較高,特點(diǎn)是“consecutive”,即保證同一條insert語句中新插入的auto_increment id都是連續(xù)的。
這種模式下:
“Simple inserts”:直接通過分析語句,獲得要插入的數(shù)量,然后一次性分配足夠的auto_increment id,只會(huì)將整個(gè)分配的過程鎖住。
“Bulk inserts”:因?yàn)椴荒艽_定插入的數(shù)量,因此使用和以前的模式相同的表級(jí)鎖定。
“Mixed-mode inserts”:直接分析語句,獲得最壞情況下需要插入的數(shù)量,然后一次性分配足夠的auto_increment id,只會(huì)將整個(gè)分配的過程鎖住。
需要注意的是,這種方式下,會(huì)分配過多的id,而導(dǎo)致“浪費(fèi)”。
比如INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');會(huì)一次性的分配5個(gè)id,而不管用戶是否指定了部分id;
INSERT … ON DUPLICATE KEY UPDATE一次性分配,而不管將來插入過程中是否會(huì)因?yàn)閐uplicate key而僅僅執(zhí)行update操作。
注意:當(dāng)master mysql版本<5.1.22,slave mysql版本>=5.1.22時(shí),slave需要將innodb_autoinc_lock_mode設(shè)置為0,因?yàn)槟J(rèn)的innodb_autoinc_lock_mode為1,對(duì)于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的執(zhí)行結(jié)果不同,現(xiàn)實(shí)環(huán)境一般會(huì)使用INSERT … ON DUPLICATE KEY UPDATE。
三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode,交叉模式)。
這種模式是來一個(gè)分配一個(gè),而不會(huì)鎖表,只會(huì)鎖住分配id的過程,和innodb_autoinc_lock_mode = 1的區(qū)別在于,不會(huì)預(yù)分配多個(gè),這種方式并發(fā)性最高。
但是在replication中當(dāng)binlog_format為statement-based時(shí)(簡稱SBR statement-based replication)存在問題,因?yàn)槭莵硪粋€(gè)分配一個(gè),這樣當(dāng)并發(fā)執(zhí)行時(shí),“Bulk inserts”在分配的時(shí)會(huì)同時(shí)向其他的INSERT分配,會(huì)出現(xiàn)主從不一致(從庫執(zhí)行結(jié)果和主庫執(zhí)行結(jié)果不一樣),因?yàn)閎inlog只會(huì)記錄開始的insert id。
測試SBR,執(zhí)行begin;insert values(),();insert values(),();commit;會(huì)在binlog中每條insert values(),();前增加SET INSERT_ID=18/*!*/;。
但是row-based replication RBR時(shí)不會(huì)存在問題。
另外RBR的主要缺點(diǎn)是日志數(shù)量在包括語句中包含大量的update delete(update多條語句,delete多條語句)時(shí),日志會(huì)比SBR大很多;假如實(shí)際語句中這樣語句不是很多的時(shí)候(現(xiàn)實(shí)中存在很多這樣的情況),推薦使用RBR配合innodb_autoinc_lock_mode,不過話說回來,現(xiàn)實(shí)生產(chǎn)中“Bulk inserts”本來就很少,因此innodb_autoinc_lock_mode = 1應(yīng)該是夠用了。
最后說一句今天遇到這個(gè)問題,在LOCK_MODE為AUTO_INC,而且事物回滾非常的慢,不得已停止數(shù)據(jù)庫重新啟動(dòng)
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 |
+-------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
向AI問一下細(xì)節(jié)