顯示定義ID

創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站設(shè)計制作、做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的團(tuán)風(fēng)網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

表定義的自增值ID達(dá)到上限后，在申請下一個ID時，得到的值保持不變

-- (2^32-1) = 4,294,967,295
-- 建議使用 BIGINT UNSIGNED
CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295;
INSERT INTO t VALUES (null);

-- AUTO_INCREMENT沒有改變
MySQL> SHOW CREATE TABLE t;
+-------+------------------------------------------------------+
| Table | Create Table           |
+-------+------------------------------------------------------+
| t  | CREATE TABLE `t` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------+

mysql> INSERT INTO t VALUES (null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

InnoDB row_id

1、如果創(chuàng)建的InnoDB表沒有指定主鍵，那么InnoDB會創(chuàng)建一個不可見的，長度為6 Bytes的row_id

2、InnoDB維護(hù)一個全局的dict_sys.row_id值，所有無主鍵的InnoDB表，每插入一行數(shù)據(jù)

• 都將當(dāng)前的dict_sys.row_id值作為要插入數(shù)據(jù)的row_id，然后把dict_sys.row_id的值+1

3、代碼實現(xiàn)上，row_id是一個8 Bytes的BIGINT UNSIGNED

• 但I(xiàn)nnoDB設(shè)計時，給row_id只保留了6 Bytes的空間，寫到數(shù)據(jù)表時只會存放最后的6 Bytes
• row_id的取值范圍：0 ~ 2^48-1
• 達(dá)到上限后，下一個值就是0

4、在InnoDB里面，申請到row_id=N后，就將這行數(shù)據(jù)寫入表中

• 如果表中已經(jīng)有row_id=N的行，新寫入的行就會覆蓋原有的行

5、推薦顯示創(chuàng)建自增主鍵

• 表自增ID達(dá)到上限后，再插入數(shù)據(jù)時會報主鍵沖突的錯誤，影響的是可用性
• 而覆蓋數(shù)據(jù)，意味著數(shù)據(jù)丟失，影響的是可靠性
• 一般來說，可靠性優(yōu)于可用性

XID

1、redolog和binlog相配合的時候，有一個共同的字段XID，對應(yīng)一個事務(wù)

2、生成邏輯

• MySQL內(nèi)部維護(hù)一個全局變量global_query_id
• 每次執(zhí)行語句的時候?qū)lobal_query_id賦值給Query_id，然后global_query_id+1
• 如果當(dāng)前語句是這個事務(wù)執(zhí)行的第一條語句，把Query_id賦值給這個事務(wù)的XID

3、global_query_id是一個純內(nèi)存變量，重啟之后清零

• 因此，在同一個數(shù)據(jù)庫實例中，不同事務(wù)的XID也有可能是相同的
• MySQL重啟之后，會重新生成新的binlog
  • 保證：同一個binlog文件里，XID是唯一的
• global_query_id達(dá)到上限后，就會繼續(xù)從0開始計數(shù)
  • 因此理論上，同一個binlog還是會出現(xiàn)相同的XID，只是概率極低

4、global_query_id是8 Bytes，上限為2^64-1

• 執(zhí)行一個事務(wù)，假設(shè)XID是A
• 接下來執(zhí)行2^64次查詢語句，讓global_query_id回到A
• 再啟動一個事務(wù)，這個事務(wù)的XID也是A

InnoDB trx_id

1、XID是由Server層維護(hù)的

2、InnoDB內(nèi)部使用的是trx_id，為的是能夠在InnoDB事務(wù)和Server層之間做關(guān)聯(lián)

3、InnoDB內(nèi)部維護(hù)一個max_trx_id的全局變量

• 每次需要申請一個新的trx_id，就獲得max_trx_id的當(dāng)前值，然后max_trx_id+1

4、InnoDB數(shù)據(jù)可見性的核心思想

• 每一行數(shù)據(jù)都記錄了更新它的trx_id
• 當(dāng)一個事務(wù)讀到一行數(shù)據(jù)的時候，判斷數(shù)據(jù)可見性的方法
  • 事務(wù)的一致性視圖和這行數(shù)據(jù)的trx_id做對比

5、對于正在執(zhí)行的事務(wù)，可以通過information_schema.innodb_trx看到事務(wù)的trx_id

操作序列

-- T2時刻
mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id   | trx_mysql_thread_id |
+-----------------+---------------------+
| 281479812572992 |     30 |
+-----------------+---------------------+

-- T4時刻
mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id   | trx_mysql_thread_id |
+-----------------+---------------------+
| 7417540   |     30 |
+-----------------+---------------------+

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
| Id | User   | Host  | db     | Command | Time | State     | Info    |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL    | Daemon | 344051 | Waiting on empty queue | NULL    |
| 30 | root   | localhost | test    | Sleep | 274 |      | NULL    |
| 31 | root   | localhost | information_schema | Query |  0 | starting    | SHOW PROCESSLIST |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+

1、trx_mysql_thread_id=30就是線程ID，即session A所在的線程

2、T1時刻，trx_id的值其實為0，而很大的值只是為了顯示用的（區(qū)別于普通的讀寫事務(wù)）

3、T2時刻，trx_id是一個很大的數(shù)字，因為在T1時刻，session A并未涉及更新操作，是一個只讀事務(wù)

• 對于只讀事務(wù)，InnoDB不會分配trx_id

4、session A在T3時刻執(zhí)行INSERT語句時，InnoDB才真正分配trx_id

只讀事務(wù)

1、在上面的T2時刻，很大的trx_id是由系統(tǒng)臨時計算出來的

• 把當(dāng)前事務(wù)的trx變量的指針地址轉(zhuǎn)成整數(shù)，再加上2^48

2、同一個只讀事務(wù)在執(zhí)行期間，它的指針地址是不會變的

• 不論是在innodb_trx還是innodb_locks表里，同一個只讀事務(wù)查出來的trx_id都是一樣的

3、如果有多個并行的只讀事務(wù)，每個事務(wù)的trx變量的指針地址肯定是不同的

• 不同的并發(fā)只讀事務(wù)，查出來的trx_id是不同的

4、加上2^48的目的：保證只讀事務(wù)顯示的trx_id值比較大，用于區(qū)別普通的讀寫事務(wù)

5、trx_id與row_id的邏輯類似，定義長度為8 Bytes

• 在理論上，可能會出現(xiàn)一個讀寫事務(wù)與一個只讀事務(wù)顯示的trx_id相同的情況
• 但概率極低，并且沒有什么實質(zhì)危害

6、只讀事務(wù)不分配trx_id的好處

• 可以減少事務(wù)視圖里面活躍數(shù)組的大小
  • 當(dāng)前正在運行的只讀事務(wù)，是不影響數(shù)據(jù)的可見性判斷
  • 因此，在創(chuàng)建事務(wù)的一致性視圖時，只需要拷貝讀寫事務(wù)的trx_id
• 可以減少trx_id的申請次數(shù)
  • 在InnoDB里，即使只執(zhí)行一條普通的SELECT語句，在執(zhí)行過程中，也要對應(yīng)一個只讀事務(wù)
  • 如果普通查詢語句不申請trx_id，就可以大大減少并發(fā)事務(wù)申請trx_id的鎖沖突
  • 由于只讀事務(wù)不分配trx_id，trx_id的增加速度會變慢

7、max_trx_id會持久化存儲，重啟不會重置為0，只有到達(dá)2^48-1的上限后，才會重置為0

thread_id

1、SHOW PROCESSLIST的第一列就是thread_id

2、系統(tǒng)保存了一個環(huán)境變量thread_id_counter

• 每新建一個連接，就將thread_id_counter賦值給這個新連接的線程變量

3、thread_id_counter定義為4 Bytes，因此達(dá)到2^32-1后就會重置為0

• 但不會在SHOW PROCESSLIST里面看到兩個相同的thread_id
• 因為MySQL設(shè)計了一個唯一數(shù)組的邏輯，給新線程分配thread_id，邏輯代碼如下

do {
  new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

參考資料

《MySQL實戰(zhàn)45講》

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對創(chuàng)新互聯(lián)的支持。

網(wǎng)頁名稱：MySQL自增ID耗盡實例講解
分享網(wǎng)址：http://m.newbst.com/article12/gpgedc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、網(wǎng)站制作、ChatGPT、域名注冊、網(wǎng)站改版、網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)