顯示定義ID
表定義的自增值ID達(dá)到上限后，在申請(qǐng)下一個(gè)ID時(shí)，得到的值保持不變
-- (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會(huì)創(chuàng)建一個(gè)不可見的，長(zhǎng)度為6 Bytes的row_id
2、InnoDB維護(hù)一個(gè)全局的dict_sys.row_id值，所有無主鍵的InnoDB表，每插入一行數(shù)據(jù)
- 都將當(dāng)前的dict_sys.row_id值作為要插入數(shù)據(jù)的row_id，然后把dict_sys.row_id的值+1
3、代碼實(shí)現(xiàn)上，row_id是一個(gè)8 Bytes的BIGINT UNSIGNED
- 但I(xiàn)nnoDB設(shè)計(jì)時(shí)，給row_id只保留了6 Bytes的空間，寫到數(shù)據(jù)表時(shí)只會(huì)存放最后的6 Bytes
- row_id的取值范圍：0 ~ 2^48-1
- 達(dá)到上限后，下一個(gè)值就是0
4、在InnoDB里面，申請(qǐng)到row_id=N后，就將這行數(shù)據(jù)寫入表中
- 如果表中已經(jīng)有row_id=N的行，新寫入的行就會(huì)覆蓋原有的行
5、推薦顯示創(chuàng)建自增主鍵
- 表自增ID達(dá)到上限后，再插入數(shù)據(jù)時(shí)會(huì)報(bào)主鍵沖突的錯(cuò)誤，影響的是可用性
- 而覆蓋數(shù)據(jù)，意味著數(shù)據(jù)丟失，影響的是可靠性
- 一般來說，可靠性優(yōu)于可用性
XID
1、redolog和binlog相配合的時(shí)候，有一個(gè)共同的字段XID，對(duì)應(yīng)一個(gè)事務(wù)
2、生成邏輯
- MySQL內(nèi)部維護(hù)一個(gè)全局變量global_query_id
- 每次執(zhí)行語句的時(shí)候?qū)lobal_query_id賦值給Query_id，然后global_query_id+1
- 如果當(dāng)前語句是這個(gè)事務(wù)執(zhí)行的第一條語句，把Query_id賦值給這個(gè)事務(wù)的XID
3、global_query_id是一個(gè)純內(nèi)存變量，重啟之后清零
- 因此，在同一個(gè)數(shù)據(jù)庫(kù)實(shí)例中，不同事務(wù)的XID也有可能是相同的
- MySQL重啟之后，會(huì)重新生成新的binlog
- 保證：同一個(gè)binlog文件里，XID是唯一的
- global_query_id達(dá)到上限后，就會(huì)繼續(xù)從0開始計(jì)數(shù)
- 因此理論上，同一個(gè)binlog還是會(huì)出現(xiàn)相同的XID，只是概率極低
4、global_query_id是8 Bytes，上限為2^64-1
- 執(zhí)行一個(gè)事務(wù)，假設(shè)XID是A
- 接下來執(zhí)行2^64次查詢語句，讓global_query_id回到A
- 再啟動(dòng)一個(gè)事務(wù)，這個(gè)事務(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ù)一個(gè)max_trx_id的全局變量
- 每次需要申請(qǐng)一個(gè)新的trx_id，就獲得max_trx_id的當(dāng)前值，然后max_trx_id+1
4、InnoDB數(shù)據(jù)可見性的核心思想
- 每一行數(shù)據(jù)都記錄了更新它的trx_id
- 當(dāng)一個(gè)事務(wù)讀到一行數(shù)據(jù)的時(shí)候，判斷數(shù)據(jù)可見性的方法
- 事務(wù)的一致性視圖和這行數(shù)據(jù)的trx_id做對(duì)比
5、對(duì)于正在執(zhí)行的事務(wù)，可以通過information_schema.innodb_trx看到事務(wù)的trx_id
操作序列
| 時(shí)刻 |
session A |
session B |
| T1 |
BEGIN;
SELECT * FROM t LIMIT 1; |
|
| T2 |
|
USE information_schema;
SELECT trx_id,trx_mysql_thread_id FROM innodb_trx; |
| T3 |
INSERT INTO t VALUES (null); |
|
| T4 |
|
SELECT trx_id,trx_mysql_thread_id FROM innodb_trx; |
-- T2時(shí)刻
mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id | trx_mysql_thread_id |
+-----------------+---------------------+
| 281479812572992 | 30 |
+-----------------+---------------------+
-- T4時(shí)刻
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時(shí)刻，trx_id的值其實(shí)為0，而很大的值只是為了顯示用的（區(qū)別于普通的讀寫事務(wù)）
3、T2時(shí)刻，trx_id是一個(gè)很大的數(shù)字，因?yàn)樵赥1時(shí)刻，session A并未涉及更新操作，是一個(gè)只讀事務(wù)
- 對(duì)于只讀事務(wù)，InnoDB不會(huì)分配trx_id
4、session A在T3時(shí)刻執(zhí)行INSERT語句時(shí)，InnoDB才真正分配trx_id
只讀事務(wù)
1、在上面的T2時(shí)刻，很大的trx_id是由系統(tǒng)臨時(shí)計(jì)算出來的
- 把當(dāng)前事務(wù)的trx變量的指針地址轉(zhuǎn)成整數(shù)，再加上2^48
2、同一個(gè)只讀事務(wù)在執(zhí)行期間，它的指針地址是不會(huì)變的
- 不論是在innodb_trx還是innodb_locks表里，同一個(gè)只讀事務(wù)查出來的trx_id都是一樣的
3、如果有多個(gè)并行的只讀事務(wù)，每個(gè)事務(wù)的trx變量的指針地址肯定是不同的
- 不同的并發(fā)只讀事務(wù)，查出來的trx_id是不同的
4、加上2^48的目的：保證只讀事務(wù)顯示的trx_id值比較大，用于區(qū)別普通的讀寫事務(wù)
5、trx_id與row_id的邏輯類似，定義長(zhǎng)度為8 Bytes
- 在理論上，可能會(huì)出現(xiàn)一個(gè)讀寫事務(wù)與一個(gè)只讀事務(wù)顯示的trx_id相同的情況
- 但概率極低，并且沒有什么實(shí)質(zhì)危害
6、只讀事務(wù)不分配trx_id的好處
- 可以減少事務(wù)視圖里面活躍數(shù)組的大小
- 當(dāng)前正在運(yùn)行的只讀事務(wù)，是不影響數(shù)據(jù)的可見性判斷
- 因此，在創(chuàng)建事務(wù)的一致性視圖時(shí)，只需要拷貝讀寫事務(wù)的trx_id
- 可以減少trx_id的申請(qǐng)次數(shù)
- 在InnoDB里，即使只執(zhí)行一條普通的SELECT語句，在執(zhí)行過程中，也要對(duì)應(yīng)一個(gè)只讀事務(wù)
- 如果普通查詢語句不申請(qǐng)trx_id，就可以大大減少并發(fā)事務(wù)申請(qǐng)trx_id的鎖沖突
- 由于只讀事務(wù)不分配trx_id，trx_id的增加速度會(huì)變慢
7、max_trx_id會(huì)持久化存儲(chǔ)，重啟不會(huì)重置為0，只有到達(dá)2^48-1的上限后，才會(huì)重置為0
thread_id
1、SHOW PROCESSLIST的第一列就是thread_id
2、系統(tǒng)保存了一個(gè)環(huán)境變量thread_id_counter
- 每新建一個(gè)連接，就將thread_id_counter賦值給這個(gè)新連接的線程變量
3、thread_id_counter定義為4 Bytes，因此達(dá)到2^32-1后就會(huì)重置為0
- 但不會(huì)在SHOW PROCESSLIST里面看到兩個(gè)相同的thread_id
- 因?yàn)镸ySQL設(shè)計(jì)了一個(gè)唯一數(shù)組的邏輯，給新線程分配thread_id，邏輯代碼如下
do {
new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);
參考資料
《MySQL實(shí)戰(zhàn)45講》
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。
您可能感興趣的文章:- 關(guān)于mysql自增id，你需要知道的
- MySQL表自增id溢出的故障復(fù)盤解決
- 關(guān)于MySQL自增ID的一些小問題總結(jié)
- 關(guān)于Mysql自增id的這些你可能還不知道
- mysql自增id超大問題的排查與解決
- MySQL分表自增ID問題的解決方法
- 兩種mysql對(duì)自增id重新從1排序的方法
- 利用mysql事務(wù)特性實(shí)現(xiàn)并發(fā)安全的自增ID示例
- mysql自增ID起始值修改方法
- MySQL的自增ID(主鍵) 用完了的解決方法
