建表為什么還設(shè)置個(gè)自增id

這篇文章主要講解了“建表為什么還設(shè)置個(gè)自增 id”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“建表為什么還設(shè)置個(gè)自增 id”吧！

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),廣信企業(yè)網(wǎng)站建設(shè),廣信品牌網(wǎng)站建設(shè),網(wǎng)站定制,廣信網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,廣信網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

1.建表規(guī)約

在工作中，創(chuàng)建表的時(shí)候，DBA 也會(huì)審核一下建表 SQL，檢查是否符合規(guī)范以及常用字段是否設(shè)置索引。

CREATE TABLE `xxxx` (   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',   `create_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) COMMENT '創(chuàng)建時(shí)間',   `update_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3) COMMENT '更新時(shí)間',   PRIMARY KEY (`id`) USING BTREE,   KEY `idx_create_time` (`create_time`) USING BTREE,   KEY `idx_update_time` (`update_time`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='表注釋';

所以在我使用的過(guò)程中，流水號(hào)都是單獨(dú)設(shè)置了一個(gè)字段，比如叫 trans_no，但是這次就遇到了疑問(wèn)：trans_no 既然是唯一的，那為什么不直接用  trans_no 當(dāng)做 id 呢?

下面開(kāi)始通過(guò)查閱相關(guān)資料，一步一步的了解是為什么?

2.主鍵

什么是主鍵?

MySQL primary key

這段定義咱們主要關(guān)注最后一句：

" When choosing primary key values, consider using arbitrary values (a  synthetic key) rather than relying on values derived from some other source (a  natural key). "

意思是創(chuàng)建主鍵的時(shí)候盡量使用 MySQL 自增主鍵而不是使用業(yè)務(wù)生成的值當(dāng)做主鍵。

主鍵的特征

簡(jiǎn)而言之：

非空、唯一、少更改或不更改 。

如何添加主鍵

可以在 create 創(chuàng)建表的時(shí)候指定，也可以使用 alter 語(yǔ)句后面添加主鍵，不過(guò)官方建議在創(chuàng)建表時(shí)就指定。

為什么要添加主鍵

1. 主鍵可以唯一標(biāo)識(shí)這一行數(shù)據(jù)，從而保證在刪除更新操作時(shí)，只是操作這一行數(shù)據(jù)。

2. 索引需要，每個(gè) InnoDB 表又有一個(gè)特殊的索引，即聚簇索引，用來(lái)存儲(chǔ)行數(shù)據(jù)。通常，聚簇索引和主鍵同義。

• 聲明主鍵，InnoDB 會(huì)將主鍵作為聚簇索引。

• 未聲明時(shí)，會(huì)在 UNIQUE 所有鍵列所在位置找到第一個(gè)索引，NOT NULL 并將其作為聚簇索引

• 未聲明且找不到合適的 UNIQUE 索引，則內(nèi)部生成一個(gè)隱藏的聚簇索引 GEN_CLUST_INDEX，這個(gè)隱藏的行 ID 是 6  字節(jié)且單調(diào)增加。

3.索引

這里僅介紹 InnoDB 引擎，具體可以參考官方文檔，并且介紹的相對(duì)比較簡(jiǎn)單。

索引的分類

聚簇索引：表存儲(chǔ)是根據(jù)主鍵列的值組織的，以加快涉及主鍵列的查詢和排序。在介紹主鍵時(shí)也對(duì)聚簇索引進(jìn)行了介紹。

二級(jí)索引：也可以叫輔助索引，在輔助索引中會(huì)記錄對(duì)應(yīng)的主鍵列以及輔助索引列。根據(jù)輔助索引進(jìn)行搜索的時(shí)候，會(huì)先根據(jù)輔助索引獲取到對(duì)應(yīng)的主鍵列，然后再根據(jù)主鍵去聚簇索引里面搜索。一般不建議主鍵很長(zhǎng)，因?yàn)橹麈I很長(zhǎng)輔助索引就會(huì)使用更多的空間。

" 補(bǔ)充：

回表：先在二級(jí)索引查詢到對(duì)應(yīng)的主鍵值，然后根據(jù)主鍵再去聚簇索引里面取查詢。

索引覆蓋：二級(jí)索引記錄了主鍵列和二級(jí)索引列，如果我只查詢主鍵列的值和二級(jí)索引列的值，那就不需要回表了。 "

索引的物理結(jié)構(gòu)

InnoDB 使用的 B+ 數(shù)數(shù)據(jù)結(jié)構(gòu)，根據(jù)聚簇索引值(主鍵/UNQIUE/或者自己生成)構(gòu)建一顆 B+  樹(shù)，葉子節(jié)點(diǎn)中存放行記錄數(shù)據(jù)，所以每個(gè)葉子節(jié)點(diǎn)也可以叫數(shù)據(jù)頁(yè)。每個(gè)數(shù)據(jù)頁(yè)大小默認(rèn)為 16k，支持自定義。

數(shù)據(jù)的插入

當(dāng)數(shù)據(jù)插入時(shí)，InnoDB 會(huì)使頁(yè)面 1/16 空閑，以備將來(lái)插入和更新索引記錄。

1. 順序插入(升序或降序)：會(huì)將索引頁(yè)剩余的大約 15/16 裝滿

2. 隨機(jī)插入：只會(huì)使用容量的 1/2 到 15/16

在隨機(jī)插入中，會(huì)頻繁的移動(dòng)、分頁(yè)，從而造成大量的碎片，并且使索引樹(shù)不夠緊湊。而使用順序插入的方式，則數(shù)據(jù)比較緊湊，有更高的空間利用率。

4.總結(jié)

Q&A

Q: 什么是回表和索引覆蓋?

A:

回表：先在二級(jí)索引查詢到對(duì)應(yīng)的主鍵值，然后根據(jù)主鍵再去聚簇索引里面取查詢。

索引覆蓋：二級(jí)索引記錄了主鍵列和二級(jí)索引列，如果我只查詢主鍵列的值和二級(jí)索引列的值，那就不需要回表了。

Q: 為什么要設(shè)置自增主鍵 id ?

A:

可以唯一標(biāo)識(shí)一行數(shù)據(jù)，在 InnoDB 構(gòu)建索引樹(shù)的時(shí)候會(huì)使用主鍵。

自增 id 是順序的，可以保證索引樹(shù)上的數(shù)據(jù)比較緊湊，有更高的空間利用率以及減少數(shù)據(jù)頁(yè)的分裂合并等操作，提高效率。

一般使用手機(jī)號(hào)、身份證號(hào)作為主鍵等并不能保證順序性。

流水號(hào)一般相對(duì)較長(zhǎng)，比如 28 位，32 位等，過(guò)長(zhǎng)的話會(huì)二級(jí)索引占用空間較多。同時(shí)為了業(yè)務(wù)需求，流水號(hào)具有一定的隨機(jī)性。

感謝各位的閱讀，以上就是“建表為什么還設(shè)置個(gè)自增 id”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)建表為什么還設(shè)置個(gè)自增 id這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

分享標(biāo)題：建表為什么還設(shè)置個(gè)自增id
瀏覽路徑：http://bm7419.com/article48/jdgehp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、軟件開(kāi)發(fā)、外貿(mào)網(wǎng)站建設(shè)、小程序開(kāi)發(fā)、定制網(wǎng)站、做網(wǎng)站

廣告

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