自增主鍵是否會降低資料庫insert性能？如果會的話為什麼還有很多公司採用？

12-29

首先自增主鍵在insert的時候，因為是自增，那麼是不是要有一個自增值鎖來防止這個自增值出現重複？有的話那豈不是會影響性能？
我觀察過BAT以及國內很多大型互聯網公司的網站，比如說京東的商品id，百度的用戶id，新浪微博的用戶id，貼吧的帖子id都是自增主鍵（長期觀察可以發現他們隨時間增長，應該是自增），當然也有反例，比如說QQ空間的說說（內部名稱應該是mood，因為說說最早以前是由心情模塊改來的）說說id用的就是類似於UUID這樣的主鍵（其實格式很像md5）。
他們這樣設計有什麼考慮嗎？（好處壞處都有哪些？）

主鍵無論如何插入的時候都需要鎖住索引，自增不自增已經無所謂了

謝邀。

題主說的自增id鎖的表現形式其實主要是由參數innodb_autoinc_lock_mode控制，我做幾個簡單的測試

前提條件

percona 5.6版本，事務隔離級別為RR

mysql&> show create table test_autoinc_lockG

*************************** 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設置為0

session1

begin;delete from test_autoinc_lock where a&>7;//這時未提交

session2

mysql&> insert into test_autoinc_lock(a) values(100);//gap鎖的存在，這時處於鎖等待

session3

mysql&> insert into test_autoinc_lock(a) values(2);//這時同樣處於等待狀態，理論上這個不是gap鎖的鎖定範圍，那麼它是在等什麼呢

session4

mysql&> select * from information_schema.innodb_trxG

*************************** 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

這時查看session3是等待自增鎖，一直處於setting auto-inc lock狀態

session2

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

這時session3鎖等待超時退出

session3

這時再看session3可以發現insert完成。

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)//注意看這時的最大自增值是13，也就是之前自增最大值上+1，也就是說session2後來釋放了預計生成的自增id，將13留給了session3，自增id值的申請完全是串列順序的。

結論1：

innodb_autoinc_lock_mode為0時的，也就是官方說的traditional

級別，該自增鎖是表鎖級別，且必須等待當前SQL執行完成後或者回滾掉才會釋放，這樣在高並發的情況下可想而知自增鎖競爭是比較大的。

條件2 innodb_autoinc_lock_mode設置為1（這也是官方默認級別）

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)//注意看這時的最大自增值是13

session2

mysql&> insert into test_autoinc_lock(a) values(100);//同樣gap鎖的存在，這時處於鎖等待

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，也就是跳過了預計分配給session2的14，可以看到自增id值立馬就分配給了session3，而不必等session2執行完成

結論2：

innodb_autoinc_lock_mode為1時的，也就是官方說的consecutive

級別，這時如果是單一的insert SQL，可以立即獲得該鎖，並立即釋放，而不必等待當前SQL執行完成（除非在其他事務中已經有session獲取了自增鎖）。另外當SQL是一些批量insert sql時，比如insert into ...select ...,load data,replace ..select..時，這時還是表級鎖，可以理解成退化為必須等待當前SQL執行完才釋放。

可以認為，該值為1時是相對比較輕量的鎖，也不會對複製產生影響，唯一的缺陷是產生的自增值不一定是完全連續的（不過個人認為這個往往不是很重要，也沒必要根據自增id值來統計行數之類）

條件3 innodb_autoinc_lock_mode設置為2

先說結論3：

當innodb_autoinc_lock_mode設置為2時，所有insert種類的SQL都可以立馬獲得鎖並釋放，這時的效率最高。但是會引入一個新的問題：當binlog_format為statement時，這時的複製沒法保證安全，因為批量的insert，比如insert ..select..語句在這個情況下，也可以立馬獲取到一大批的自增id值，不必鎖整個表，slave在回放這個sql時必然會產生錯亂。我們做個測試驗證複製不是安全的。

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執行完成之前執行）

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（這時可看到1376236主鍵衝突）

mysql&> show slave statusG

*************************** 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

我們這時解析下主庫的binlog不難發現問題原因，第一條批量insert還沒執行完時，第二條簡單insert執行時獲得了自增id值為1376236的鎖，這時在主庫寫入是沒有問題的，但是反應到從庫時，因為是基於statement的複製，必然出現主鍵衝突。

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

總結：

1 innodb row複製時，可將innodb_autoinc_lock_mode設置為2，這時可在所有insert情況下表獲得最大並發度

2 innodb statement複製時，可將innodb_autoinc_lock_mode設置為1，保證複製安全的同時，獲得簡單insert語句的最大並發度

3 myisam引擎情況下，無論什麼樣自增id鎖都是表級鎖，設置innodb_autoinc_lock_mode參數無效（測試略）

4 實際上提問者說到的在innodb引擎下自增id值作為主鍵的情況下，相比uuid或者自定義的主鍵，是可以提到插入速度的，因為innodb是主鍵聚集索引，實際的主鍵值必須按照主鍵順序存取，那麼自增id本身就是升序的，那麼在插入數據時，底層就不必再做額外的排序操作，也減少了索引頁分裂的次數，從而大大增加insert速度（除非其他方案也能保證主鍵完全自增）

額，被幾個人邀請，看來是非答不可了。

關係型資料庫中講求ACID，其中第一號的A代表的是Atomicity原子性以及I代表了isolation隔離性，這也就意味著，只要是insert操作，為了避免非原子性操作，不管是行鎖還是表鎖，必鎖。第二個C代表consistency一致性，意味著主鍵作為數據記錄的標識是不可以重複和空缺的。
一個計數器的實現非常容易，遠遠比鎖行的開銷小的多，時間短到可以忽略。對比uuid的方式可就是複雜的多。而且如果你嘗試過，那應該知道亂序的主鍵的問題是每一次都要重排主鍵，效率是非常低下的。UUID是唯一的沒錯，更多的是為避免了集群內自增id的重複，在保證了原子性操作的單DB內uuid的意義不大。
其實還有一種設計方式更為簡單粗暴，只要簡單的把庫id和自增id聯合起來成為一個id，就可以簡單的實現uuid的大部分功能（缺點是要充分考慮可擴展性）。典型的就是身份證號：同一天出生的人有很多，後4位不可能統一編號，但至少每個派出所可以順序編號，那就簡單的把派出所編號（資料庫編號）排在前面就可以了。

主鍵自帶索引，自增生成會產生索性塊熱點爭用，容易產生55分列，導致索引虛胖，效率下降

先想想自增主鍵的優點，再對比一下優劣，最後思考一下如何優化。

mysql innobd引擎用uuid做主鍵絕對是傻X行為！會使主鍵所需存儲增大若干倍，會使輔助索引所需存儲也跟隨增大很多！

正好相反。自增正好是效率最高的一種方式。產生自增主鍵是非常簡單的計算，比G U I D快得多。程序員少操很多心。此外，考慮效率主要在於查詢而不是D D L操作，畢竟查詢才是大頭，但自增一般是int ,bigint 索引效率比guid高百倍，而且guid非常容易產生索引碎片。

所以，你的問題反過來提更合適：既然自增效率高，為什麼一些很大型的公司不用自增而用？

插入本來就要加鎖啊！

觀察的結論不對，隨時間增長不一定就是表的自增列。

一般來說這些id需要有一個全局唯一的生成器來做，而不是讓資料庫來產生。

自增主鍵會導致很多問題，一般是要盡量避免的。

innodb？

自增主鍵確實有額外開銷，全局自增鎖

索引組織表以主鍵來形成索引樹，當然可以自定義主鍵。如果沒有，自增ID是較好的選擇。