1.自增ID的優(yōu)缺點(diǎn)

1.1 優(yōu)點(diǎn)

1. 主鍵頁以近乎順序的方式填寫，提升了頁的利用率
2. 索引更加緊湊，性能更好查詢時(shí)數(shù)據(jù)訪問更快
3. 節(jié)省空間
4. 連續(xù)增長的值能避免 b+ 樹頻繁合并和分裂
5. 簡單易懂，幾乎所有數(shù)據(jù)庫都支持自增類型，只是實(shí)現(xiàn)上各自有所不同而已

1.2 缺點(diǎn)

1. 可靠性不高

   存在自增ID回溯的問題，這個(gè)問題直到最新版本的MySQL 8.0才修復(fù)。

2. 安全性不高

   ID不夠隨機(jī)，對(duì)外暴露的接口可以非常容易猜測對(duì)應(yīng)的信息。比如：/User/1/這樣的接口，可以非常容易猜測用戶ID的值為多少，總用戶數(shù)量有多少（泄露發(fā)號(hào)數(shù)量的信息），也可以非常容易地通過接口進(jìn)行數(shù)據(jù)的爬取，因此不太安全。

3. 性能差

   自增ID的性能較差，需要在數(shù)據(jù)庫服務(wù)器端生成。對(duì)于高并發(fā)的負(fù)載，innodb在按主鍵進(jìn)行插入的時(shí)候會(huì)造成明顯的鎖爭用，主鍵的上界會(huì)成為爭搶的熱點(diǎn)，因?yàn)樗械牟迦攵及l(fā)生在這里，并發(fā)插入會(huì)導(dǎo)致間隙鎖競爭。

4. 交互多

   業(yè)務(wù)還需要額外執(zhí)行一次類似 last_insert_id() 的函數(shù)才能知道剛才插入的自增值，這需要多一次的網(wǎng)絡(luò)交互。在海量并發(fā)的系統(tǒng)中，多1條SQL，就多一次性能上的開銷。

5. 局部唯一性

   最重要的一點(diǎn)，自增ID是局部唯一，只在當(dāng)前數(shù)據(jù)庫實(shí)例中唯一，而不是全局唯一，在任意服務(wù)器間都是唯一的。對(duì)于目前分布式系統(tǒng)來說，這簡直就是噩夢(mèng)。

6. 不利于數(shù)據(jù)遷移與擴(kuò)展

1.3 不適合以自增ID主鍵作為主鍵的情況

1. 數(shù)據(jù)量多需要分庫分表，可能會(huì)造成ID重復(fù)
2. 經(jīng)常會(huì)遇到數(shù)據(jù)遷移的情況
3. 新數(shù)據(jù)需要和老數(shù)據(jù)進(jìn)行合并

2.UUID作為主鍵

2.1 介紹

雖然UUID（） 值是 旨在獨(dú)一無二，它們不一定是不可猜測的 或不可預(yù)測。如果需要不可預(yù)測性，UUID 值應(yīng)該以其他方式生成。

UUID：Universally Unique ldentifier 通用 唯一 標(biāo)識(shí)符

對(duì)于所有的UUID它可以保證在空間和時(shí)間上的唯一性。它是通過MAC地址，時(shí)間戳，命名空間，隨機(jī)數(shù)，偽隨機(jī)數(shù)來保證生成ID的唯一性，有著固定的大小(128bit)。它的唯一性和一致性特點(diǎn)使得可以無需注冊(cè)過程就能夠產(chǎn)生一個(gè)新的UUID。UUID可以被用作多種用途，既可以用來短時(shí)間內(nèi)標(biāo)記一個(gè)對(duì)象，也可以可靠的辨別網(wǎng)絡(luò)中的持久性對(duì)象。

MySQL中的UUID組成 = [時(shí)間低位+時(shí)間中位+時(shí)間高位]（16字節(jié)）- 時(shí)鐘序列（4字節(jié)） - MAC地址（12字節(jié)）

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 4b176683-695a-11ed-a641-0a002700000c |
+--------------------------------------+
1 row in set (0.00 sec)

?? 以下是在 MySQL8.0 官方文檔對(duì) UUID 函數(shù)的說明（https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid）：

UUID（） 返回一個(gè)值 符合 RFC 4122 中所述的 UUID 版本 1，表示為五個(gè)十六進(jìn)制數(shù)字的字符串格式，中間用了 “-” 連接。

• 前三個(gè)數(shù)字字符串是從低處生成的， 時(shí)間戳的中間和高部分。高部分也 包括 UUID 版本號(hào)。

• 第四個(gè)數(shù)字字符串保留了時(shí)間唯一性，以防萬一 時(shí)間戳值失去單調(diào)性（例如，由于 到夏令時(shí)）。

• 第五個(gè)數(shù)字字符串是 IEEE 802 節(jié)點(diǎn)編號(hào)，它提供 空間獨(dú)特性。如果 后者不可用（例如，因?yàn)橹鳈C(jī) 設(shè)備沒有以太網(wǎng)卡，或者不知道如何找到主機(jī)上運(yùn)行的接口的硬件地址系統(tǒng)）。在這種情況下，空間唯一性不能 保證。然而，碰撞的概率應(yīng)該非常低。

  僅考慮接口的 MAC 地址 在 FreeBSD、Linux 和 Windows 上。關(guān)于其他操作 系統(tǒng)，MySQL使用隨機(jī)生成的48位數(shù)字。

要在字符串和二進(jìn)制 UUID 值之間進(jìn)行轉(zhuǎn)換，請(qǐng)使用 UUID_TO_BIN（） 和 BIN_TO_UUID（） 函數(shù)。自檢查字符串是否為有效的 UUID 值，使用IS_UUID（） 函數(shù)。

2.2 優(yōu)點(diǎn)

• 保證了全局唯一性
• 更加安全

2.3 缺點(diǎn)

• 存在隱私安全的問題，因?yàn)閁UID包含了MAC地址，也就是機(jī)械的物理地址。
• 無序，隨機(jī)生成與插入，聚集索引頻繁頁分裂，大量隨機(jī)IO，內(nèi)存碎片化，特別是隨著數(shù)據(jù)量越來越多，插入性能會(huì)越差。
• 占用36字節(jié)，比較浪費(fèi)空間。

3.有序UUID作為主鍵

3.1 介紹

UUID唯一性的特點(diǎn)使它作為主鍵帶來了很多的優(yōu)勢(shì)，比較大的問題主要是無序性帶來的索引性能的下降。 使用mysql8自帶的uuid_to_bin可以方便的將時(shí)間相關(guān)的字符高低位進(jìn)行互換，從而解決了這個(gè)性能上的問題。

在通過 UUID()函數(shù) 生成的uuid值中，若將時(shí)間高低位互換，則時(shí)間就是單調(diào)遞增的了，也就變得單調(diào)遞增了。MySQL 8.0可以更換時(shí)間低位和時(shí)間高位的存儲(chǔ)方式，這樣UUID就是有序的UUID了。

MySQL 8.0還解決了UUID存在的空間占用的問題，除去了UUID字符串中無意義的"-"字符串，并且將字符串用二進(jìn)制類型保存，這樣存儲(chǔ)空間降低為了16字節(jié)。

可以通過MySQL8.0提供的uuid_to_bin函數(shù)實(shí)現(xiàn)上述兩個(gè)功能：

-- 生成一個(gè) uuid
SET @uuid = UUID(); 

-- uuid_to_bin(@uuid)：實(shí)現(xiàn)去除無意義的 "-" 字符串
-- uuid_to_bin(@uuid,TRUE)：實(shí)現(xiàn)時(shí)間低位與時(shí)間高位的互換，實(shí)現(xiàn)了該函數(shù)返回值隨時(shí)間遞增
SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

通過函數(shù)uuid_to_bin(@uuid,true)將UUID轉(zhuǎn)化為有序UUID了。全局唯一 + 單調(diào)遞增，這不就是我們想要的主鍵！

3.2 演示使用

3.2.1 前提知識(shí)

3.2.1.1 數(shù)據(jù)類型 - binary

binary存儲(chǔ)的是二進(jìn)制的字符串，binary(N)中的N是指定存儲(chǔ)的最大字節(jié)長度，和 char(N) 類型一樣是 固長 存儲(chǔ)。

CREATE TABLE test01(
    -- 指定 uid 最大可以存儲(chǔ) 16 個(gè)字節(jié)大小，也就是 128 bit
	`uid` BINARY(16) PRIMARY KEY,
	`num` INT NOT NULL
);

3.2.1.2 函數(shù) - hex()

可以將一個(gè)二進(jìn)制字符串轉(zhuǎn)換成十六進(jìn)制的字符串

3.2.1.3 函數(shù) - unhex()

可以將一個(gè)十六進(jìn)制字符串轉(zhuǎn)換成二進(jìn)制的字符串

3.2.2 數(shù)據(jù)庫層

-- 創(chuàng)建測試數(shù)據(jù)庫 test
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;

-- 使用/定位該數(shù)據(jù)庫
USE test;

-- 創(chuàng)建表 test01
CREATE TABLE test01(
    -- 解釋一下為什么定義為16個(gè)字節(jié)：
    -- 因?yàn)?uuid 一共32個(gè)字符，由于每個(gè)字符是十六進(jìn)制的數(shù)字，
    -- 在將 uuid 字符串轉(zhuǎn)為十六進(jìn)制時(shí) 是用 4 個(gè)bit 表示一個(gè)字符
    -- 因此 uuid 字符串轉(zhuǎn)十六進(jìn)制需要 128 個(gè) bit
    -- 一個(gè) 字節(jié) 等于 8 個(gè) bit，所以 128 個(gè) bit 一共就是 16 個(gè) 字節(jié) 
	`uid` BINARY(16) PRIMARY KEY,
	`num` INT NOT NULL
);

-- 增加測試數(shù)據(jù)
INSERT INTO test01(`uid`,`num`) VALUES
(UUID_TO_BIN(UUID(),TRUE),1),
(UUID_TO_BIN(UUID(),TRUE),2),
(UUID_TO_BIN(UUID(),TRUE),3);

-- 以十六進(jìn)制字符串方式顯示 uid
SELECT HEX(uid),num FROM test01;

-- 查詢 某個(gè)uid 的對(duì)應(yīng)記錄的其他數(shù)據(jù)
-- 將十六進(jìn)制字符串轉(zhuǎn)二進(jìn)制字符串與 表中的uid 比較
select num from test01
where uid = unhex('11ED68FE63CC264781770A002700001A')

3.2.3 JAVA層

3.2.3.1 導(dǎo)入mysql的驅(qū)動(dòng)jar包

1?? commons-dbutils-1.7.jar
?? 下載地址：https://repo1.maven.org/maven2/commons-dbutils/commons-dbutils/1.7/

2?? druid-1.1.10.jar

?? 下載地址：https://repo1.maven.org/maven2/com/alibaba/druid/1.1.10/

3.2.3.2 創(chuàng)建 druid.properties 配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&serverTimezone=GMT&useSSL=false
username=root
password=123456
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=20
#max wait time (5000 mil seconds)
maxWait=5000

3.2.3.3 創(chuàng)建 JDBCUtilsByDruid 工具類

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

/**
 * 數(shù)據(jù)庫連接池
 */
public class JDBCUtilsByDruid {
    private static DataSource dataSource;
    static{
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src//druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //獲取連接
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //關(guān)閉連接
    public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection){
        try {
            if(resultSet!=null){
                resultSet.close();
            }
            if(preparedStatement!=null){
                preparedStatement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

3.2.3.4 測試 - 查詢?nèi)坑涗?/h5>

import java.sql.*;

/**
 * @author 狐貍半面添
 * @create 2022-11-21 2:39
 */
public class ConnectionTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtilsByDruid.getConnection();
            String sql = "SELECT HEX(uid) AS uid,num FROM test01";
            stat = conn.prepareStatement(sql);
            rs = stat.executeQuery();
            while (rs.next()) {
                String uid = rs.getString("uid");
                Integer num = rs.getInt("num");
                System.out.println("uid：" + uid + "    num：" + num);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsByDruid.close(rs, stat, conn);
        }
    }
}

運(yùn)行上述代碼：

3.2.3.5 查詢某條記錄

import java.sql.*;

/**
 * @author 狐貍半面添
 * @create 2022-11-21 2:39
 */
public class ConnectionTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtilsByDruid.getConnection();
            String sql = "SELECT num FROM test01 WHERE uid = UNHEX('11ED68FE63CC264781770A002700001A')";
            stat = conn.prepareStatement(sql);
            rs = stat.executeQuery();
            while (rs.next()) {
                Integer num = rs.getInt("num");
                System.out.println("num：" + num);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsByDruid.close(rs, stat, conn);
        }
    }
}

3.2.3.6 增加一條記錄

import java.sql.*;

/**
 * @author 狐貍半面添
 * @create 2022-11-21 2:39
 */
public class ConnectionTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtilsByDruid.getConnection();

            //添加一條記錄
            String insertSql = "INSERT INTO test01(`uid`,`num`) VALUES(UUID_TO_BIN(UUID(),TRUE),4)";
            stat = conn.prepareStatement(insertSql);
            int info = stat.executeUpdate();
            if(info!=0){
                System.out.println("記錄添加成功");
            }else{
                System.out.println("記錄添加失敗");
            }

            //查詢?nèi)繑?shù)據(jù)
            String selectSql = "SELECT HEX(uid) AS uid,num FROM test01";
            rs = stat.executeQuery(selectSql);
            while (rs.next()) {
                String uid = rs.getString("uid");
                Integer num = rs.getInt("num");
                System.out.println("uid：" + uid + "    num：" + num);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsByDruid.close(rs, stat, conn);
        }
    }
}

3.3 手撕uuid_to_bin(uuid(),true)方法實(shí)現(xiàn)

3.3.1 過程分析

-- 1. 得到uuid
SELECT UUID();

-- 2. 在第一步的基礎(chǔ)上將 uuid 字符串的 "-" 刪除
SELECT REPLACE(UUID(),'-','');

-- 3. 在第二步的基礎(chǔ)上調(diào)換時(shí)間低位與時(shí)間高位
SET @uuid = REPLACE(UUID(),'-','');
SELECT CONCAT(SUBSTR(@uuid,13,4),SUBSTR(@uuid,9,4),SUBSTR(@uuid,1,8),SUBSTR(@uuid,17,16));

-- 4. 在第三步的基礎(chǔ)上將小寫字母全變?yōu)榇髮?SET @uuid = REPLACE(UUID(),'-','');
SELECT UPPER(CONCAT(SUBSTR(@uuid,13,4),SUBSTR(@uuid,9,4),SUBSTR(@uuid,1,8),SUBSTR(@uuid,17,16)));

-- 5. 在第四步的基礎(chǔ)上將這個(gè)十六進(jìn)制字符串轉(zhuǎn)為二進(jìn)制字符串
SET @uuid = REPLACE(UUID(),'-','');
SELECT UNHEX(UPPER(CONCAT(SUBSTR(@uuid,13,4),SUBSTR(@uuid,9,4),SUBSTR(@uuid,1,8),SUBSTR(@uuid,17,16))));

3.3.2 存儲(chǔ)函數(shù)封裝

-- 先執(zhí)行這條語句，否則創(chuàng)建函數(shù)時(shí)會(huì)報(bào)錯(cuò)
SET GLOBAL log_bin_trust_function_creators = 1;

-- 6. 進(jìn)行函數(shù)封裝
DELIMITER //
CREATE FUNCTION my_uuid_to_bin() 
RETURNS BINARY(16)
BEGIN
	DECLARE my_uuid CHAR(32);
	SET my_uuid = REPLACE(UUID(), '-', '');
	RETURN (SELECT UNHEX(UPPER(CONCAT(SUBSTR(my_uuid,13,4),SUBSTR(my_uuid,9,4),SUBSTR(my_uuid,1,8),SUBSTR(my_uuid,17,16)))));
END //
DELIMITER ;

3.3.3 使用自定義方法增添數(shù)據(jù)

INSERT INTO test01(`uid`,`num`) VALUES
(my_uuid_to_bin(),13),
(my_uuid_to_bin(),14)

4.自定義UUID

4.1 為什么要有自定義UUID

在上面我們談?wù)摰搅岁P(guān)于 UUID() 函數(shù)的隱私安全的問題，因?yàn)閁UID包含了MAC地址，也就是機(jī)械的物理地址，即用戶的地址信息。

而在當(dāng)今的海量數(shù)據(jù)的互聯(lián)網(wǎng)環(huán)境中，非常不推薦自增ID作為主鍵的數(shù)據(jù)庫設(shè)計(jì)，推薦類似有序UUID的全局唯一的實(shí)現(xiàn)。

另外在真實(shí)的業(yè)務(wù)系統(tǒng)中，主鍵還可以加入業(yè)務(wù)和系統(tǒng)屬性，如用戶的尾號(hào)，機(jī)房的信息等。這樣的主鍵設(shè)計(jì)就更為考驗(yàn)架構(gòu)師的水平了。

4.2 自定義UUID實(shí)例演示

因此如果我們打算以 有序uuid 這樣的類型作為主鍵，可以對(duì)之前提到的有序uuid加以改進(jìn)：

【僅供參考】由于最近在做數(shù)據(jù)庫課設(shè)，需要建立一張訂單表，那么我可以在 上面的自定義my_uuid_to_bin()函數(shù) 中把 mac地址 部分的12個(gè)字節(jié)刪除，以用戶的手機(jī)號(hào)碼后六位加以代替。

這里進(jìn)行一個(gè)簡單演示：

-- 如果存在則刪除數(shù)據(jù)庫 test
DROP DATABASE IF EXISTS test;

-- 創(chuàng)建數(shù)據(jù)庫 test
CREATE DATABASE test CHARSET=utf8mb4;

-- 使用該數(shù)據(jù)庫
USE test;

-- 在 test 數(shù)據(jù)庫中創(chuàng)建表 test01
CREATE TABLE test01(
	`order_id` BINARY(13) PRIMARY KEY,
	`num` INT NOT NULL 
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

-- 先執(zhí)行這條語句，否則創(chuàng)建函數(shù)時(shí)會(huì)報(bào)錯(cuò)
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //
CREATE FUNCTION my_uuid_to_bin(phone CHAR(11)) 
RETURNS BINARY(13)
BEGIN
	DECLARE my_uuid CHAR(32);
	SET my_uuid = REPLACE(UUID(), '-', '');
	RETURN (SELECT UNHEX(UPPER(CONCAT(SUBSTR(my_uuid,13,4),SUBSTR(my_uuid,9,4),SUBSTR(my_uuid,1,8),SUBSTR(my_uuid,17,4),SUBSTR(phone,6,6)))));
END //
DELIMITER ;

INSERT INTO test01(`order_id`,`num`) VALUES
(my_uuid_to_bin('15675229374'),1),
(my_uuid_to_bin('13789302970'),2);

-- 查看表記錄
SELECT HEX(`order_id`) `order_id`,`num` FROM test01;

5.總結(jié)

5.1 自增id主鍵與自定義主鍵的選擇

• 從數(shù)據(jù)在數(shù)據(jù)庫的存儲(chǔ)角度來看，自增id 是int 型，一般比自定義的屬性（uuid等）作為主鍵，所占的磁盤空間要小。但即使我們插入一億數(shù)據(jù)，使用有序uuid的表大小比自增id也只多了 3G，在當(dāng)今的環(huán)境下，3G確實(shí)不算很多，所以還能接受。
• 從數(shù)據(jù)庫的設(shè)計(jì)來看，mysql的底層是InnoDB，它的數(shù)據(jù)結(jié)構(gòu)是B+樹。所以對(duì)于InnoDB的主鍵，盡量用整型，而且是遞增的整型。這樣在存儲(chǔ)/查詢上都是非常高效的。

因此如果只是簡單的單庫單表的場景或者是一些簡單的非核心業(yè)務(wù)，使用自增id主鍵是沒有問題的。但如果是在高并發(fā)場景與分布式架構(gòu)中，就不是很推薦使用自增id，那這時(shí)我們就可以選用自增步長id，改造uuid，雪花算法自造全局自增id等方案來作為我們表的主鍵。

5.2 建議與說明

• 建議盡量不要用跟業(yè)務(wù)特別緊密相關(guān)的字段做主鍵。畢竟，作為項(xiàng)目設(shè)計(jì)的技術(shù)人員，我們誰也無法預(yù)測在項(xiàng)目的整個(gè)生命周期中，哪個(gè)業(yè)務(wù)字段會(huì)因?yàn)轫?xiàng)目的業(yè)務(wù)需求而有重復(fù)，或者重用之類的情況出現(xiàn)。

  很多初學(xué)者都很容易犯的錯(cuò)誤是喜歡用業(yè)務(wù)字段做主鍵，想當(dāng)然地認(rèn)為了解業(yè)務(wù)需求，但實(shí)際情況往往出乎意料，而更改主鍵設(shè)置的成本非常高。

• 非核心業(yè)務(wù) ：對(duì)應(yīng)表的主鍵自增ID，如告警、日志、監(jiān)控等信息。

• 核心業(yè)務(wù) ：**主鍵設(shè)計(jì)至少應(yīng)該是全局唯一且是單調(diào)遞增。**全局唯一保證在各系統(tǒng)之間都是唯一的，單調(diào)遞增是希望插入時(shí)不影響數(shù)據(jù)庫性能。文章來源地址http://www.zghlxwxcb.cn/news/detail-412524.html

到了這里，關(guān)于詳解：MySQL自增ID與UUID的優(yōu)缺點(diǎn)及選擇建議，MySQL有序uuid與自定義函數(shù)實(shí)現(xiàn)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！