1. 首頁 > 生活 > >

索引表怎么做 材料索引表怎么弄

小知識索引表怎么做



文章插圖
索引表怎么做 材料索引表怎么弄

文章插圖
表的主鍵指的針對一張表中的一列或者多列，其結果必須能標識表中每行記錄的唯一性 。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;QueryOK,0rowsaffected(0.00sec)mysql>set@@auto_increment_offset=1;QueryOK,0rowsaffected(0.00sec)mysql>insertintotmpvalues(null),(null),(null);QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0mysql>select*fromtmp;+----+|id|+----+|1||3||5|+----+3rowsinset(0.00sec)但是這塊 MySQL 并不能保證其他的值不沖突，比如插入一條節點 2 的值，也能成功插入，MySQL 默認對這塊沒有什么約束，最好是數據入庫前就校驗好 。
mysql>insertintotmpvalues(2);QueryOK,1rowaffected(0.02sec)mysql>select*fromtmp;+----+|id|+----+|1||2||3||5|+----+4rowsinset(0.00sec)第二個問題：MySQL 自增鍵合并
這個問題一般牽扯到老的系統改造升級，比如多個分部老系統數據要向新系統合并，那之前每個分部的自增主鍵不能簡單地合并，可能會有主鍵沖突 。舉個例子，假設武漢市每個區都有自己的醫保數據，并且以前每個區都是自己獨立設計的數據庫，現在醫保要升級為全市統一，以市為單位設計新的數據庫模型 。
武昌的數據如下，對應表 n1，
mysql>select*fromn1;+----+|id|+----+|1||2||3|+----+3rowsinset(0.00sec)漢陽的數據如下，對應表 n2，
mysql>select*fromn2;+----+|id|+----+|1||2||3|+----+3rowsinset(0.00sec)由于之前兩個區數據庫設計的人都沒有考慮以后合并的事情，所以每個區的表都有自己獨立的自增主鍵，
考慮這樣建立一張匯總表 n3，有新的自增 ID，并且設計導入老系統的 ID 。
mysql>createtablen3(idintauto_incrementprimarykey,old_idint);QueryOK,0rowsaffected(0.07sec)mysql>insertinton3(old_id)select*fromn1unionallselect*fromn2;QueryOK,6rowsaffected(0.01sec)Records:6Duplicates:0Warnings:0mysql>select*fromn3;+----+--------+|id|old_id|+----+--------+|1|1||2|2||3|3||4|1||5|2||6|3|+----+--------+6rowsinset(0.00sec)這樣進行匯總，應用代碼可能不太確定怎么連接老的數據，這張表缺少一個 old_id 到原始表名的映射 。
那基于原始表 ID 與原始表名的映射關系建立一個多值索引 。比如以下例子：
mysql>createtablen4(old_idint,old_namevarchar(64),primarykey(old_id,old_name));QueryOK,0rowsaffected(0.05sec)mysql>insertinton4selectid,'n1'fromn1unionallselectid,'n2'fromn2;QueryOK,6rowsaffected(0.02sec)Records:6Duplicates:0Warnings:0mysql>select*fromn4;+--------+----------+|old_id|old_name|+--------+----------+|1|n1||1|n2||2|n1||2|n2||3|n1||3|n2|+--------+----------+6rowsinset(0.00sec)最終表結構，結合前面兩張表 n3 和 n4，建立一個包含新的自增字段主鍵，原來表 ID，原來表名的新表：
create table n5(idintunsignedauto_incrementprimarykey,old_idint,old_namevarchar(64),uniquekeyudx_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>createtablet_binary(idvarbinary(16)primarykey,r1int,keyidx_r1(r1));QueryOK,0rowsaffected(0.07sec)mysql>insertintot_binaryvalues(uuid_to_bin(uuid()),1),(uuid_to_bin(uuid()),2);QueryOK,2rowsaffected(0.01sec)Records:2Duplicates:0Warnings:0mysql>select*fromt_binary;+------------------------------------+------+|id|r1|+------------------------------------+------+|0x412234A77DEF11EA9AF9080027C52750|1||0x412236E27DEF11EA9AF9080027C52750|2|+------------------------------------+------+2rowsinset(0.00sec)函數 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>createtablet_uuid_short(idbigintunsignedprimarykey,r1int,keyidx_r1(r1));QueryOK,0rowsaffected(0.06sec)mysql>insertintot_uuid_shortvalues(uuid_short(),1),(uuid_short(),2)QueryOK,2rowsaffected(0.02sec)Records:2Duplicates:0Warnings:0mysql>select*fromt_uuid_short;+----------------------+------+|id|r1|+----------------------+------+|16743984358464946177|1||16743984358464946178|2|+----------------------+------+2rowsinset(0.00sec)可以看到 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>createtablet_uuid(idchar(36)primarykey,r1int,keyidx_r1(r1));QueryOK,0rowsaffected(0.06sec)mysql>createtablet_id(idbigintauto_incrementprimarykey,r1int,keyidx_r1(r1));QueryOK,0rowsaffected(0.08sec)簡單寫了一個存儲過程，分別給這些表造 30W 條記錄 。
DELIMITER$$CREATEPROCEDURE`ytt`.`sp_insert_data`(f_tbnameVARCHAR(64),f_numberINTUNSIGNED)BEGINDECLAREiINTUNSIGNEDDEFAULT0;SET@@autocommit=0;[email protected]=CONCAT('insertintot_uuidvalues(uuid(),ceil(rand()*100));');[email protected]=CONCAT('insertintot_binaryvalues(uuid_to_bin(uuid()),ceil(rand()*100));');[email protected]=CONCAT('insertintot_uuid_shortvalues(uuid_short(),ceil(rand()*100));');[email protected]=CONCAT('insertintot_id(r1)values(ceil(rand()*100));');ENDIF;WHILEi<[email protected];EXECUTEs1;SETi=i+1;IFMOD(i,50)=0THENCOMMIT;ENDIF;ENDWHILE;COMMIT;DROPPREPAREs1;SET@@autocommit=1;END$$DELIMITER;接下來分別調用存儲過程，結果和預期一致 。t_uuid 時間最長，t_uuid_short 時間最短 。
mysql>callsp_insert_data('t_uuid',300000);QueryOK,0rowsaffected(5min23.33sec)mysql>callsp_insert_data('t_binary',300000);QueryOK,0rowsaffected(4min48.92sec)mysql>callsp_insert_data('t_id',300000);QueryOK,0rowsaffected(3min40.38sec)mysql>callsp_insert_data('t_uuid_short',300000);QueryOK,0rowsaffected(3min9.94sec)二、與業務有關的屬性做主鍵 。
主鍵的設計要求可讀性很強，類似學生學號（入學年份+所屬系+所讀專業），購物訂單編碼等 。其實非常不建議主鍵用這樣有實際意義的業務字段 ?？梢孕陆ㄒ粋€自增主鍵或者 uuid_short() 函數字段，實際業務字段非主鍵設計，變為普通唯一索引 。比如表 n5：
mysql>createtablen5(idintunsignedauto_incrementprimarykey,usernointunsigned,uniquekeyudx_userno(userno));QueryOK,0rowsaffected(0.08sec)用 userno（用戶編碼）來做主鍵，如果在業務端數據已經錯誤，比如可能由于老師原因錄入錯誤數據，或者是業務系統的 BUG 導致錄入數據有誤，那不僅要對錄入表的主鍵做更改（這可是聚簇索引），還要更改依賴這張表的所有子表，這其實是一個很大的工程 。但是如果有與業務不相關的主鍵，只需要更改業務字段（二級索引）就可以，不需要更改依賴這張表的子表 。
【索引表怎么做 材料索引表怎么弄】關于 MySQL 主鍵的設計思路大致介紹到此，有問題歡迎留言，歡迎指正本篇任何不足之處 。