mysql資料庫主鍵的思考

自增主鍵優點

1.資料庫AUTO_INCREMENT，innodb的索引特性導緻了自增id做主鍵是效率最好的，而且是增量增長，按順序存放，對于檢索非常有利；

使用者表user百萬記錄，主鍵為UUID和主鍵為自增Id，作基準測試，機器不同可能結果會有差異

1）普通單條或者20條左右的記錄檢索，uuid為主鍵的相差不大幾乎效率相同；

2）範圍查詢特别是上百成千條的記錄查詢，自增id的效率要大于uuid；

3）在範圍查詢做統計彙總的時候，自增id的效率要大于uuid；

4）在存儲上面，自增id所占的存儲空間是uuid的1/2；

5）在備份恢複上，自增ID主鍵稍微優于UUID。

使用者表1000W記錄測試：

1）普通單條或者20條左右的記錄檢索，自增主鍵效率是uuid主鍵的2到3倍；

2）但是範圍查詢特别是上百成千條的記錄查詢，自增id的效率要大于uuid；

3）在範圍查詢做統計彙總的時候，自增id主鍵的效率是uuid主鍵1.5到2倍；

5）在寫入上面，自增ID主鍵的效率是UUID主鍵的3到10倍，相差比較明顯，特别是update小範圍之内的資料上面。

6）在備份恢複上，自增ID主鍵稍微優于UUID。

2.數字型，占用空間小，易排序；

3.如果通過非系統增加記錄時，可以不用指定該字段，不用擔心主鍵重複問題。

缺點以及解決方案

1.因為自動增長，在手動要插入指定ID的記錄時會顯得麻煩

此處的id在生成之前應該沒有業務含義

2.是當系統與其它系統內建時需要資料導入時，很難保證原系統的ID不發生主鍵沖突（前提是老系統也是數字型的）特别是在新系統上線時，新舊系統并行存在，并且是異庫異構的資料庫的情況下，需要雙向同步時，自增主鍵将是你的噩夢。

當資料依然時單庫，資料遷移時，必然資料量不大，增量也不大，此時可以預估未來一段時間的id增長個數，然後以遠高于以前id編号的數字，開始自增，這樣可以允許新舊系統并行一段時間，但是需要盡快遷移。異構資料庫現在其實很少遇到這樣的案例，一般都統一為mysql，如果不是，可以重新思考解決方案

3.若系統也是數字型的，在導入時，為了區分新老資料，可能想在老資料主鍵前統一加一個字元辨別（例如“o”，old）來表示這是老資料，那麼自動增長的數字型又面臨一個挑戰。

同2所示，老資料資料較小，直接可以區分

4.表鎖

在MySQL5.1.22之前，InnoDB自增值是通過其本身的自增長計數器來擷取值，該實作方式是通過表鎖機制來完成的（AUTO-INC LOCKING）。鎖不是在每次事務完成後釋放，而是在完成對自增長值插入的SQL語句後釋放，要等待其釋放才能進行後續操作。比如說當表裡有一個auto_increment字段的時候，innoDB會在記憶體裡儲存一個計數器用來記錄auto_increment的值，當插入一個新行資料時，就會用一個表鎖來鎖住這個計數器，直到插入結束。如果大量的并發插入，表鎖會引起SQL堵塞。

在5.1.22之後，InnoDB為了解決自增主鍵鎖表的問題，引入了參數innodb_autoinc_lock_mode，該實作方式是通過輕量級互斥量的增長機制完成的。它是專門用來在使用auto_increment的情況下調整鎖政策的，目前有三種選擇：

插入類型說明：

INSERT-LIKE：指所有的插入語句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等
Simple inserts：指在插入前就能确定插入行數的語句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE這類語句。
Bulk inserts：指在插入前不能确定得到插入行的語句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.
Mixed-mode inserts:指其中一部分是自增長的，有一部分是确定的。
---檢視show variables like 'innodb_autoinc_lock_mode';
0：通過表鎖的方式進行，也就是所有類型的insert都用AUTO-inc locking。
---1：預設值，對于simple insert 自增長值的産生使用互斥量對記憶體中的計數器進行累加操作，對于bulk insert 則還是使用表鎖的方式進行。
2：對所有的insert-like 自增長值的産生使用互斥量機制完成，性能最高，并發插入可能導緻自增值不連續，可能會導緻Statement 的 Replication 出現不一緻，使用該模式，需要用 Row Replication的模式。

盡量減少Bulk inserts的使用，一般情況下也很少用到，而且現在主流mysql都在5.6+

5.自增在bulk insert時不連續

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+

mysql> show create table health_package;
  health_package | CREATE TABLE `health_package` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `package_id` int(11) NOT NULL COMMENT '套系 id',
  `module_id` int(11) NOT NULL COMMENT '子產品 id',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time, common column by DB rules',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified time,common column by DB rules ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1761 DEFAULT CHARSET=utf8 COMMENT='This table stores module and package of health for ...'

ql> insert into health_package(package_id,module_id) select package_id,module_id from health_package limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings:

現在我的表是從1761開始自增，現在使用bulk insert，插入了10條資料

mysql> show create table health_package;
  health_package | CREATE TABLE `health_package` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `package_id` int(11) NOT NULL COMMENT '套系 id',
  `module_id` int(11) NOT NULL COMMENT '子產品 id',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time, common column by DB rules',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modified time,common column by DB rules ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1776 DEFAULT CHARSET=utf8 COMMENT='This table stores module and package of health for ...'

mysql> select * from health_package limit 1760,15
    -> ;
+------+------------+-----------+---------------------+---------------------+
| id   | package_id | module_id | gmt_create          | gmt_modified        |
+------+------------+-----------+---------------------+---------------------+
| 1761 |          1 |         1 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1762 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1763 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1764 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1765 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1766 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1767 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1768 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1769 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
| 1770 |      10001 |         5 | 2018-02-11 19:40:08 | 2018-02-11 19:40:08 |
+------+------------+-----------+---------------------+---------------------+
10 rows in set (0.00 sec)

此時再看一下，自增已經是1776了，查詢資料庫，發現資料庫裡面隻是多了10條資料，如果你再插入一條資料，此時已經從1776開始了。這是因為參數innodb_autoinc_lock_mode = 1時，每次會“預申請”多餘的id(handler.cc:compute_next_insert_id)，而insert執行完成後，會特别将這些預留的id空出，就是特意将預申請後的目前最大id回寫到表中(dict0dict.c:dict_table_autoinc_update_if_greater)。

這個預留的政策是“不夠時多申請幾個”，實際執行中是分步申請。至于申請幾個，是由當時“已經插入了幾條資料N”決定的。當auto_increment_offset＝1時，預申請的個數是 N-1。

是以，如果N為1，則不預申請，N為2，則預申請1個(3),N為3，已經預申請了，N為4，預申請3個(5,6,7),5,6,7已經預申請了，N為8，預申請個7個(9,10,11,12,13,14,15),是以最終的結果就是1761+15=1776，當然可以繼續往後推此處不做例子，下來可以自己去試試。

6.主從複制

在 statement 模式下，由于他是記錄的執行語句，是以，為了讓這些語句在 slave 端也能正确執行，那麼他還必須記錄每條語句在執行的時候的一些相關資訊，也就是上下文資訊，以保證所有語句在 slave 端杯執行的時候能夠得到和在 master 端執行時候相同的結果。在自增主鍵上面，有可能會出現不一緻的情況
在 row 模式下，bin-log 中可以不記錄執行的 SQL 語句的上下文相關的資訊，僅僅隻需要記錄哪一條記錄被修改了，修改成什麼樣了。是以 row 的日志内容會非常清楚的記錄下每一行資料修改的細節，非常容易了解。所有自增主鍵也不會出現問題
但是在 row 模式下，所有的執行的語句當記錄到日志中的時候，都将以每行記錄的修改來記錄，這樣可能會産生大量的日志内容

除以下幾種情況外，在運作時可以動态改變 binlog 的格式：

·存儲流程或者觸發器中間；

·啟用了 NDB；

·目前會話使用 row 模式，并且已打開了臨時表；

3.Mixed 模式，那麼在以下幾種情況下會自動将 binlog 的模式由 statement 模式變為 row 模式：

·當 DML 語句更新一個 NDB 表時；

·當函數中包含 UUID() 時；

· 2 個及以上包含 AUTO_INCREMENT 字段的表被更新時；

· 執行 INSERT DELAYED 語句時；

· 用 UDF 時；

· 視圖中必須要求運用 row 時，例如建立視圖時使用了 UUID() 函數；

這個問題我自己沒有遇到過，其實很多複雜情況下都會出現主從不一緻的情況，不同的模式都有可能，隻要不寫很複雜的sql語句，網際網路公司也是禁止的，一般問題不大

7.主主雙向複制

因為多主都可以對伺服器有寫權限，主鍵自增長一定會出現重複。

必須保證兩台伺服器上插入的自增長資料不同

A查奇數ID，B插偶數ID，步長不一樣

在這裡我們在A,B上加入參數，以實作奇偶插入

A：my.cnf上加入參數

auto_increment_offset = 1

auto_increment_increment = 2

這樣A的auto_increment字段産生的數值是：1, 3, 5, 7, …等奇數ID了

B：my.cnf上加入參數

auto_increment_offset = 2

這樣B的auto_increment字段産生的數值是：2, 4, 6, 8, …等偶數ID了

在每個叢集節點組的master上面，設定（auto_increment_increment），讓目前每個叢集的起始點錯開 1，步長選擇大于将來基本不可能達到的切分叢集數，達到将 ID 相對分段的效果來滿足全局唯一的效果。

8.自增ID主鍵+步長

優點是：實作簡單，後期維護簡單，對應用透明。

缺點是：第一次設定相對較為複雜，因為要針對未來業務的發展而計算好足夠的步長;

規劃：

比如計劃總共N個節點組，那麼第i個節點組的my.cnf的配置為：
auto_increment_offset  i
auto_increment_increment  N

假如規劃48個節點組，N為48，現在配置第8個節點組，這個i為8，第8個節點組的my.cnf裡面的配置為：
auto_increment_offset  8
auto_increment_increment  48

不可取，禁止使用。一般情況下涉及到多庫時，分庫分表肯定是基于業務的考量，例如我可以根據城市來分，當某個城市資料量大了之後，我可能需要調整城市的分布。id一般會根據業務字段來生成，我隻要拿到id就知道插入哪個表。如果id沒有分片資訊，如果要擷取多餘的字段，就會多增加幾次sql操作，這會降低效率。使用uuid效果一樣，并且還會降低效率，但是實作簡單。

結論

1.小型系統或者系統架構初期，資料沒有超過百萬或者千萬，可以使用自增主鍵

2.當資料成長到幾百萬或者超過千萬時，并且增量很高時，這時就涉及到分庫分表，這時就必須使用自動生成id方案

作者：glowd

原文：

https://blog.csdn.net/zengqiang1/article/details/79312774

mysql資料庫主鍵的思考

自增主鍵優點

缺點以及解決方案

結論

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述