投稿
  1. 簡單一點首頁
  2. 投稿

論文圖表索引製作方法「表索引怎麼弄」

投稿專員 • • 投稿

表的主鍵指的針對一張表中的一列或者多列，其結果必須能標識表中每行記錄的唯一性。InnoDB 表是索引組織表，主鍵既是數據也是索引。

主鍵的設計原則

1. 對空間佔用要小

上一篇我們介紹過 InnoDB 主鍵的存儲方式，主鍵佔用空間越小，每個索引頁里存放的鍵值越多，這樣一次性放入內存的數據也就越多。

2. 最好是有一定的排序屬性

如 INT32 類型來做主鍵，數值有嚴格的排序，那新記錄的插入只要往原先數據頁後面添加新記錄或者在數據頁後新增空頁來填充記錄即可，這樣有嚴格排序的主鍵寫入速度也會非常快。

3. 數據類型為整形

數據類型早就已經講過，按照前兩點的需求，最理想的當然是選擇整數類型，比如 int32 unsigned。數據順序增長，要麼是數據庫自己生成，要麼是業務自動生成。

一、與業務無關的屬性做主鍵

1.1 自增字段做主鍵

這是 MySQL 最推薦的方式。一般用 INT32 可以滿足大部分場景，單庫單表可以最大保存 42 億行記錄；含有自增字段的新增記錄會順序添加到當前索引節點的後續位置直到數據頁寫滿為止，再寫新頁。這樣會極大程度地減少數據頁的隨機 IO。

用自增字段做主鍵可能需要注意兩個問題：

第一個問題：MySQL 原生自增鍵拆分

如果隨着數據後期增長，有拆庫拆表預期，可以考慮用 INT64；MySQL 原生支持拆庫拆表的自增主鍵，通過自增步長與起始值來確定。最少要有 2 個 MySQL 節點，每個節點自增步長為 2，假設 server_id 分別為 1，2，那自增起始值也可以是 1，2。假設下面是第 1 個 MySQL 節點，設置好了步長和起始值後，表 tmp 插入三行，每行嚴格按照設置的方式插入數據。

mysql> set @@auto_increment_increment=2;
Query OK, 0 rows affected (0.00 sec)


mysql> set @@auto_increment_offset=1;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into tmp values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from tmp;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+
3 rows in set (0.00 sec)

但是這塊 MySQL 並不能保證其他的值不衝突，比如插入一條節點 2 的值，也能成功插入，MySQL 默認對這塊沒有什麼約束，最好是數據入庫前就校驗好。

mysql> insert into tmp values(2);
Query OK, 1 row affected (0.02 sec)


mysql> select * from tmp;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
+----+
4 rows in set (0.00 sec)

第二個問題：MySQL 自增鍵合併

這個問題一般牽扯到老的系統改造升級，比如多個分部老系統數據要向新系統合併，那之前每個分部的自增主鍵不能簡單地合併，可能會有主鍵衝突。舉個例子，假設武漢市每個區都有自己的醫保數據，並且以前每個區都是自己獨立設計的數據庫，現在醫保要升級為全市統一，以市為單位設計新的數據庫模型。

武昌的數據如下，對應表 n1，

mysql> select  * from n1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

漢陽的數據如下，對應表 n2，

mysql> select * from n2;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

由於之前兩個區數據庫設計的人都沒有考慮以後合併的事情，所以每個區的表都有自己獨立的自增主鍵，

考慮這樣建立一張匯總表 n3，有新的自增 ID，並且設計導入老系統的 ID。

mysql> create table n3 (id int auto_increment primary key, old_id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into n3 (old_id) select * from n1 union all select * from n2;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> select * from n3;
+----+--------+
| id | old_id |
+----+--------+
|  1 |      1 |
|  2 |      2 |
|  3 |      3 |
|  4 |      1 |
|  5 |      2 |
|  6 |      3 |
+----+--------+
6 rows in set (0.00 sec)

這樣進行匯總， 應用代碼可能不太確定怎麼連接老的數據，這張表缺少一個 old_id 到原始表名的映射。

那基於原始表 ID 與原始表名的映射關係建立一個多值索引。比如以下例子：

mysql> create table n4(old_id int, old_name varchar(64),primary key(old_id,old_name));
Query OK, 0 rows affected (0.05 sec)


mysql> insert into n4 select id ,'n1' from n1 union all select id,'n2' from n2;
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> select * from n4;
+--------+----------+
| old_id | old_name |
+--------+----------+
|      1 | n1       |
|      1 | n2       |
|      2 | n1       |
|      2 | n2       |
|      3 | n1       |
|      3 | n2       |
+--------+----------+
6 rows in set (0.00 sec)

最終表結構，結合前面兩張表 n3 和 n4，建立一個包含新的自增字段主鍵，原來表 ID，原來表名的新表：

create table n5(
id int unsigned auto_increment primary key,
old_id int,
old_name varchar(64),
unique key udx_old_id_old_name (old_id,old_name)
);

當然，關於數據匯總遷移的話題，討論篇幅太長，不在本節範圍。

1.2 UUID 做主鍵

UUID 和自增主鍵一樣，能保證主鍵的唯一性。但是天生無序、隨機產生、佔用空間大。在 MySQL 里，用 char(36) 來存儲 UUID，沒有專門的 UUID 數據類型，類似這樣的字符串: ‘7985847c-7d59-11ea-8add-080027c52750’。由於 InnoDB 表的特性，應該避免用 char(36) 保存原始 UUID 的方式做表主鍵。

雖然 UUID 無序，且存在空間浪費，但天生隨機這個優點能否利用上？

MySQL 提供了以下的優化方法來讓原始 UUID 可以被用於表主鍵：

函數 uuid_to_bin

MySQL 提供了函數 uuid_to_bin，把 UUID 字符串變為 16 個字節的二進制串。類似於某些數據庫（比如 POSTGRESQL）的 UUID 類型。函數 uuid_to_bin 返回數據類型為 varbinary(16)。

例如表 t_binary，

mysql> create table t_binary(id varbinary(16) primary key,r1 int, key idx_r1(r1));
Query OK, 0 rows affected (0.07 sec)


mysql> insert into t_binary values (uuid_to_bin(uuid()),1),(uuid_to_bin(uuid()),2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from t_binary;
+------------------------------------+------+
| id                                 | r1   |
+------------------------------------+------+
| 0x412234A77DEF11EA9AF9080027C52750 |    1 |
| 0x412236E27DEF11EA9AF9080027C52750 |    2 |
+------------------------------------+------+
2 rows in set (0.00 sec)

函數 uuid_short

varbinary(16) 依然是無序的，為此 MySQL 還提供了一個函數 uuid_short，用來生成類似 UUID 的全局 ID，結果為 INT64。具體計算方式如下：

(server_id & 255) << 56 + (server_startup_time_in_seconds << 24) + incremented_variable++;

  • server_id & 255：占 1 個字節；
  • server_startup_time_in_seconds：占 4 個字節；
  • incremented_variable: 占 3 個字節。

如果滿足以下條件，那這個值就必定是唯一的

1. server_id 唯一併且對函數 uuid_short() 的調用次數不超過每秒 16777216 次，也就是 2^24。所以一般情況下，uuid_short 函數能保證結果唯一。

2. uuid_short 函數生成的 ID 只需一個輕量級的 mutex 來保護，這點比自增 ID 需要的 auto-inc 表鎖更省資源，生成結果肯定更加快速。

下面表 t_uuid_short 演示了如何用這個函數。

mysql> create table t_uuid_short  (id bigint unsigned primary key,r1 int, key idx_r1(r1));
Query OK, 0 rows affected (0.06 sec)


mysql> insert into t_uuid_short values(uuid_short(),1),(uuid_short(),2)
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from t_uuid_short;
+----------------------+------+
| id                   | r1   |
+----------------------+------+
| 16743984358464946177 |    1 |
| 16743984358464946178 |    2 |
+----------------------+------+
2 rows in set (0.00 sec)

可以看到 uuid_short 生成的數據是基於 INT64 有序的，所以這塊可以看做是自增 ID 的一個補充優化，如果每秒調用次數少於 16777216，推薦用 uuid_short，而非自增 ID。

說了那麼多，還是簡單驗證下上面的結論，做個小實驗。

以下實驗涉及到四張表：

  • 新建 t_uuid: uuid 為主鍵
  • 表 t_binary：varbinary(16) 為主鍵
  • 表 t_uuid_short：bigint 為主鍵
  • 新建表 t_id：自增 ID 為主鍵

正如之前的預期，寫性能差異按從最差到最好排列依次為：t_uuid; t_binary；t_id；t_uuid_short。我們來實驗下是否和預期相符。

新增的兩張表結構：

mysql> create table t_uuid(id char(36) primary key, r1 int, key idx_r1(r1));
Query OK, 0 rows affected (0.06 sec)


mysql> create table t_id (id bigint auto_increment primary key, r1 int, key idx_r1(r1));
Query OK, 0 rows affected (0.08 sec)

簡單寫了一個存儲過程，分別給這些表造 30W 條記錄。

DELIMITER $$


CREATE


  PROCEDURE `ytt`.`sp_insert_data`(
  f_tbname VARCHAR(64),
  f_number INT UNSIGNED
  )


    BEGIN
    DECLARE i INT UNSIGNED DEFAULT 0; 
    SET @@autocommit=0;
    IF f_tbname = 't_uuid' THEN
      SET @stmt = CONCAT('insert into t_uuid values (uuid(),ceil(rand()*100));');
   ELSEIF f_tbname = 't_binary' THEN
     SET @stmt = CONCAT('insert into t_binary values(uuid_to_bin(uuid()),ceil(rand()*100));');
    ELSEIF f_tbname = 't_uuid_short' THEN
     SET @stmt = CONCAT('insert into t_uuid_short values(uuid_short(),ceil(rand()*100));');
    ELSEIF f_tbname = 't_id' THEN
      SET @stmt = CONCAT('insert into t_id(r1) values(ceil(rand()*100));');
    END IF;
    
    WHILE i < f_number
    DO 
      PREPARE s1 FROM @stmt;
      EXECUTE s1;
      SET i = i + 1;
      IF MOD(i,50) = 0 THEN
       COMMIT;
      END IF;
    END WHILE;
    COMMIT;
    DROP PREPARE s1;
SET @@autocommit=1;
    END$$
    
 DELIMITER ;

接下來分別調用存儲過程，結果和預期一致。t_uuid 時間最長，t_uuid_short 時間最短。

mysql> call sp_insert_data('t_uuid',300000);
Query OK, 0 rows affected (5 min 23.33 sec)


mysql> call sp_insert_data('t_binary',300000);
Query OK, 0 rows affected (4 min 48.92 sec)


mysql> call sp_insert_data('t_id',300000);
Query OK, 0 rows affected (3 min 40.38 sec)


mysql> call sp_insert_data('t_uuid_short',300000);
Query OK, 0 rows affected (3 min 9.94 sec)    

二、與業務有關的屬性做主鍵。

主鍵的設計要求可讀性很強，類似學生學號（入學年份+所屬系+所讀專業），購物訂單編碼等。其實非常不建議主鍵用這樣有實際意義的業務字段。可以新建一個自增主鍵或者 uuid_short() 函數字段，實際業務字段非主鍵設計，變為普通唯一索引。比如表 n5：

mysql> create table n5(
        id int unsigned auto_increment primary key, 
        userno int unsigned ,
        unique key udx_userno(userno)
        );
Query OK, 0 rows affected (0.08 sec)

用 userno（用戶編碼）來做主鍵，如果在業務端數據已經錯誤，比如可能由於老師原因錄入錯誤數據，或者是業務系統的 BUG 導致錄入數據有誤， 那不僅要對錄入表的主鍵做更改（這可是聚簇索引），還要更改依賴這張表的所有子表，這其實是一個很大的工程。但是如果有與業務不相關的主鍵，只需要更改業務字段（二級索引）就可以，不需要更改依賴這張表的子表。

關於 MySQL 主鍵的設計思路大致介紹到此，有問題歡迎留言，歡迎指正本篇任何不足之處。

原創文章，作者：投稿專員，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/255658.html

贊 (0)
打賞 微信掃一掃 微信掃一掃 支付寶掃一掃 支付寶掃一掃
投稿專員的頭像投稿專員
0 0
上一篇 2024-12-15 12:28
下一篇 2024-12-15 12:28

相關推薦

發表回復

登錄後才能評論