《Mysql入門淺談innodb_autoinc_lock_mode的表現(xiàn)形式和選值參考方法》要點(diǎn)：
本文介紹了Mysql入門淺談innodb_autoinc_lock_mode的表現(xiàn)形式和選值參考方法，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
前提條件,percona 5.6版本,事務(wù)隔離級(jí)別為RRMYSQL教程
mysql> show create table test_autoinc_lock\G
*************************** 1. row ***************************
Table: test_autoinc_lock
Create Table: CREATE TABLE `test_autoinc_lock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test_autoinc_lock;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 12 | 2 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 7 |
| 6 | 9 |
| 7 | 10 |
+----+------+
8 rows in set (0.00 sec)
條件1 innodb_autoinc_lock_mode設(shè)置為0MYSQL教程
session1
begin;delete from test_autoinc_lock where a>7;//這時(shí)未提交
session2
mysql> insert into test_autoinc_lock(a) values(100);//gap鎖的存在,這時(shí)處于鎖等待
session3
mysql> insert into test_autoinc_lock(a) values(2);//這時(shí)同樣處于等待狀態(tài),理論上這個(gè)不是gap鎖的鎖定范圍,那么它是在等什么呢
session4
mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 2317
trx_state: LOCK WAIT
trx_started: 2016-10-31 19:28:05
trx_requested_lock_id: 2317:20
trx_wait_started: 2016-10-31 19:28:05
trx_weight: 1
trx_mysql_thread_id: 9
trx_query: insert into test_autoinc_lock(a) values(2)
trx_operation_state: setting auto-inc lock
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 360
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
這時(shí)查看session3是等待自增鎖,一直處于setting auto-inc lock狀態(tài)MYSQL教程
session2
MYSQL教程
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionMYSQL教程
這時(shí)session3鎖等待超時(shí)退出MYSQL教程
session3MYSQL教程
這時(shí)再看session3可以發(fā)現(xiàn)insert完成.MYSQL教程
mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 9 rows in set (0.00 sec)//注意看這時(shí)的最大自增值是13,也就是之前自增最大值上+1,也就是說session2后來釋放了預(yù)計(jì)生成的自增id,將13留給了session3,自增id值的申請(qǐng)完全是串行順序的.
結(jié)論：innodb_autoinc_lock_mode為0時(shí)的,也就是官方說的traditionalMYSQL教程
級(jí)別,該自增鎖是表鎖級(jí)別,且必須等待當(dāng)前SQL執(zhí)行完成后或者回滾掉才會(huì)釋放,這樣在高并發(fā)的情況下可想而知自增鎖競爭是比較大的.MYSQL教程
條件2 innodb_autoinc_lock_mode設(shè)置為1MYSQL教程
session1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test_autoinc_lock where a>7; Query OK, 2 rows affected (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 9 rows in set (0.00 sec)//注意看這時(shí)的最大自增值是13 session2 mysql> insert into test_autoinc_lock(a) values(100);//同樣gap鎖的存在,這時(shí)處于鎖等待 session3 mysql> insert into test_autoinc_lock(a) values(5); Query OK, 1 row affected (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 15 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 10 rows in set (0.00 sec)//session3直接完成了,并且注意觀察插入的自增id值是15,也就是跳過了預(yù)計(jì)分配給session2的14,可以看到自增id值立馬就分配給了session3,而不必等session2執(zhí)行完成
結(jié)論：innodb_autoinc_lock_mode為1時(shí)的,也就是官方說的consecutiveMYSQL教程
級(jí)別,這時(shí)如果是單一的insert SQL,可以立即獲得該鎖,并立即釋放,而不必等待當(dāng)前SQL執(zhí)行完成(除非在其他事務(wù)中已經(jīng)有session獲取了自增鎖).另外當(dāng)SQL是一些批量insert sql時(shí),比如insert into ...select ...,load data,replace ..select..時(shí),這時(shí)還是表級(jí)鎖,可以理解成退化為必須等待當(dāng)前SQL執(zhí)行完才釋放.MYSQL教程
可以認(rèn)為,該值為1時(shí)是相對(duì)比較輕量的鎖,也不會(huì)對(duì)復(fù)制產(chǎn)生影響,唯一的缺陷是產(chǎn)生的自增值不一定是完全連續(xù)的(不過個(gè)人認(rèn)為這個(gè)往往不是很重要,也沒必要根據(jù)自增id值來統(tǒng)計(jì)行數(shù)之類)MYSQL教程
條件3 innodb_autoinc_lock_mode設(shè)置為2MYSQL教程
先說結(jié)論：當(dāng)innodb_autoinc_lock_mode設(shè)置為2時(shí),所有insert種類的SQL都可以立馬獲得鎖并釋放,這時(shí)的效率最高.但是會(huì)引入一個(gè)新的問題：當(dāng)binlog_format為statement時(shí),這時(shí)的復(fù)制沒法保證平安,因?yàn)榕康膇nsert,比如insert ..select..語句在這個(gè)情況下,也可以立馬獲取到一大批的自增id值,不必鎖整個(gè)表,slave在回放這個(gè)sql時(shí)必然會(huì)產(chǎn)生錯(cuò)亂.我們做個(gè)測(cè)試驗(yàn)證復(fù)制不是平安的.MYSQL教程
master session1
mysql> show variables like '%binlog_for%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> insert into test_autoinc_lock(a) select * from test_auto;
Query OK, 8388608 rows affected, 1 warning (29.85 sec)
Records: 8388608 Duplicates: 0 Warnings: 1
master session2(注意session2在session1執(zhí)行完成之前執(zhí)行)
mysql> insert into test_autoinc_lock(a) values(2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test_autoinc_lock where a=2;
+---------+------+
| id | a |
+---------+------+
| 1376236 | 2 |
+---------+------+
1 row in set (0.00 sec)
slave session1(這時(shí)可看到1376236主鍵沖突)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.9.73.139
Master_User: ucloudbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 75823243
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 541
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto'
Skip_Counter: 0
Exec_Master_Log_Pos: 75822971
我們這時(shí)解析下主庫的binlog不難發(fā)現(xiàn)問題原因,第一條批量insert還沒執(zhí)行完時(shí),第二條簡單insert執(zhí)行時(shí)獲得了自增id值為1376236的鎖,這時(shí)在主庫寫入是沒有問題的,但是反應(yīng)到從庫時(shí),因?yàn)槭腔趕tatement的復(fù)制,必然出現(xiàn)主鍵沖突.MYSQL教程
SET INSERT_ID=1376236/*!*/; #161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c Query thread_id=20 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1477921471/*!*/; insert into test_autoinc_lock(a) values(2) /*!*/; # at 75822940 #161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d Xid = 274 COMMIT/*!*/; # at 75822971 #161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b Query thread_id=57 exec_time=30 error_code=0 SET TIMESTAMP=1477921466/*!*/; BEGIN /*!*/; # at 75823050 # at 75823082 #161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1 Intvar SET INSERT_ID=1/*!*/; #161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba Query thread_id=57 exec_time=30 error_code=0 SET TIMESTAMP=1477921466/*!*/; insert into test_autoinc_lock(a) select * from test_auto
總結(jié)：MYSQL教程
1 innodb row復(fù)制時(shí),可將innodb_autoinc_lock_mode設(shè)置為2,這時(shí)可在所有insert情況下表獲得最大并發(fā)度MYSQL教程
2 innodb statement復(fù)制時(shí),可將innodb_autoinc_lock_mode設(shè)置為1,保證復(fù)制平安的同時(shí),獲得簡單insert語句的最大并發(fā)度MYSQL教程
3 myisam引擎情況下,無論什么樣自增id鎖都是表級(jí)鎖,設(shè)置innodb_autoinc_lock_mode參數(shù)無效(測(cè)試略)MYSQL教程
4 實(shí)際上提問者說到的在innodb引擎下自增id值作為主鍵的情況下,相比uuid或者自定義的主鍵,是可以提到插入速度的,因?yàn)閕nnodb是主鍵聚集索引,實(shí)際的主鍵值必須依照主鍵順序存取,那么自增id本身就是升序的,那么在插入數(shù)據(jù)時(shí),底層就不必再做額外的排序操作,也減少了索引頁分裂的次數(shù),從而大大增加insert速度(除非其他方案也能保證主鍵完全自增)MYSQL教程
以上這篇淺談innodb_autoinc_lock_mode的表現(xiàn)形式和選值參考辦法就是小編分享給大家的全部內(nèi)容了,希望能給大家一個(gè)參考,也希望大家多多支持維易PHP.MYSQL教程
歡迎參與《Mysql入門淺談innodb_autoinc_lock_mode的表現(xiàn)形式和選值參考方法》討論，分享您的想法，維易PHP學(xué)院為您提供專業(yè)教程。
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/13635.html
