歡迎光臨
每天分享高質量文章

MySQL 中主鍵的幾種表設計組合的實際應用效果

(給資料分析與開發加星標,提升資料技能

來源:張泰峰

www.cnblogs.com/ztfjs/p/mysql_primary.html

瞭解主鍵、外鍵、索引

 

主鍵

主鍵的主要作用是保證表的完整、保證表資料行的唯一性質,

① 業務主鍵(自然主鍵):在資料庫表中把具有業務邏輯含義的欄位作為主鍵,稱為“自然主鍵(Natural Key)”。

 

自然主鍵的含義就是原始資料中存在的不重覆欄位,直接使用成為主鍵欄位。 這種方式對業務的耦合太強,一般不會使用。

② 邏輯主鍵(代理主鍵):在資料庫表中採用一個與當前表中邏輯信息無關的欄位作為其主鍵,稱為“代理主鍵”。

邏輯主鍵提供了一個與當前表資料邏輯無關的欄位作為主鍵,邏輯主鍵被廣泛使用在業務表、資料表,一般有幾種生成方式:uuid、自增。其中使用最多的是自增,邏輯主鍵成功的避免了主鍵與資料表關聯耦合的問題,與業務主鍵不同的是,業務主鍵的資料一旦發生更改,那麼那個系統中關於主鍵的所有信息都需要連帶修改,這是不可避免的,並且這個更改是隨業務需求的增量而不斷的增加、膨脹。而邏輯主鍵與應用耦合度低,它與資料無任何必要的關係,你可以只關心:第一條資料; 而不用關心: 名字是a的那條資料。  某一天名字改成b, 你還是只關心:第一條資料。

業務的更改幾乎是不可避免的,前期任何產品經理言之鑿鑿的不修改論調都是不可靠、不切實際的。我們必須考慮主鍵資料在更改的情況下,資料能否平穩度過危機。

③ 複合主鍵(聯合主鍵):通過兩個或者多個欄位的組合作為主鍵。

 

複合主鍵可以說是業務主鍵的升級版本,通常一個業務欄位不能夠確定一條資料的唯一性,例如 張三的身份證是34123322, 張三這種大眾名稱100%會出現重覆。我們可以用姓名 + 身份證的方式表示主鍵,宣告一個唯一的記錄。

有時候,複合主鍵是複雜的。 姓名+身份證 不一定能表示不重覆,雖然身份證在17年消除了重覆的問題,但是之前的資料呢? 可能我們需要新增一個地址作為聯合主鍵,例如 姓名 + 身份證 + 聯繫地址確認一個人的身份。在其他的業務中,例如訪問控制,用戶 + 終端 + 終端型別 + 站點 + 頁面 + 時間,可能六個欄位的聯合才能夠去確定一個欄位的唯一性,這另複雜度陡升。

另外如果其他表要與該表關聯則需要取用複合主鍵的所有欄位,這就不單純是性能問題了,還有儲存空間的問題了,當然你也可以認為這是合理的資料冗餘,方便查詢,但是感覺有點得不償失。

使用複合主鍵的原因可能是:對於關係表來說必須關聯兩個物體表的主鍵,才能表示它們之間的關係,那麼可以把這兩個主鍵聯合組成複合主鍵即可。

如果兩個物體存在多個關係,可以再加一個順序欄位聯合組成複合主鍵,但是這樣就會引入業務主鍵的弊端。當然也可以另外對這個關係表添加一個邏輯主鍵,避免了業務主鍵的弊端,同時也方便其他表對它的取用。

外鍵

外鍵是一種約束,表與表的關聯約束,例如a表依賴關聯b表的某個欄位,你可以設置a表欄位外鍵關聯到b表的欄位,將兩張表強制關聯起來,這時候產生兩個效果

① 表 b 無法被刪除,你必須先刪除a表

 

② 新增的資料必須與表b某行關聯

這對某些需要強耦合的業務操作來說很有必要,但、 要強調但是,外鍵約束我認為,不可濫用,沒有合適的理由支撐它的使用的話,將導致業務強制耦合。另外對開發人員不夠友好。使用外鍵一定不能超過3表相互。否則將引出很多的麻煩而不得不取消外鍵。

索引

索取用於快速找出在某個列中有一特定值的行,不使用索引,MySQL必須從第一條記錄開始讀完整個表,直到找出相關的行,表越大,查詢資料所花費的時間就越多,如果表中查詢的列有一個索引,MySQL能夠快速到達一個位置去搜索資料檔案,而不必查看所有資料,那麼將會節省很大一部分時間。

例如:有一張person表,其中有2W條記錄,記錄著2W個人的信息。有一個Phone的欄位記錄每個人的電話號碼,現在想要查詢出電話號碼為xxxx的人的信息。

 

如果沒有索引,那麼將從表中第一條記錄一條條往下遍歷,直到找到該條信息為止。

 

如果有了索引,那麼會將該Phone欄位,通過一定的方法進行儲存,好讓查詢該欄位上的信息時,能夠快速找到對應的資料,而不必在遍歷2W條資料了。其中MySQL中的索引的儲存型別有兩種BTREE、HASH。 也就是用樹或者Hash值來儲存該欄位,要知道其中詳細是如何查找的,就需要會演算法的知識了。我們現在只需要知道索引的作用,功能是什麼就行。

 

優點:

 

1、所有的MySql列型別(欄位型別)都可以被索引,也就是可以給任意欄位設置索引

 

2、大大加快資料的查詢速度

 

缺點:

 

1、創建索引和維護索引要耗費時間,並且隨著資料量的增加所耗費的時間也會增加

 

2、索引也需要占空間,我們知道資料表中的資料也會有最大上線設置的,如果我們有大量的索引,索引檔案可能會比資料檔案更快達到上線值

 

3、當對錶中的資料進行增加、刪除、修改時,索引也需要動態的維護,降低了資料的維護速度。

 

使用原則:

 

 索引需要合理的使用。

 

1、對經常更新的表就避免對其進行過多的索引,對經常用於查詢的欄位應該創建索引,

 

2、資料量小的表最好不要使用索引,因為由於資料較少,可能查詢全部資料花費的時間比遍歷索引的時間還要短,索引就可能不會產生優化效果。

 

3、在一同值少的列上(欄位上)不要建立索引,比如在學生表的”性別”欄位上只有男,女兩個不同值。相反的,在一個欄位上不同值較多可是建立索引。

 

測試主鍵的影響力

 

為了說明業務主鍵、邏輯主鍵、複合主鍵對資料表的影響力,博主使用java生成四組測試資料,首先準備表結構為:

`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,  -- 自增
  `dt` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,     -- 使用uuid模擬不同的id
  `name` varchar(255CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  -- 隨機名稱
  `age` int(10NULL DEFAULT NULL,   -- 隨機數生成年齡
  `key` varchar(40CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  -- 唯一標識 使用uuid測試
  PRIMARY KEY (`id`USING BTREE -- 設置主鍵

將生成四組千萬條的資料: 

 

1. 自增主鍵   test_primary_a 

2. 自增主鍵  有索引 test_primary_d 

3. 無主鍵 無索引 test_primary_b 

4. 複合主鍵 無索引 test_primary_c 

 

使用java, spring boot + mybatis每次批量一萬條資料,插入一千次,記錄每次插入時間,總插入時間:

 

mybatis代碼:

       

"insertTestData">
        insert into test_primary_${code} (
        `dt`,
        `name`,
        `age`,
        `key`
        ) values
        <foreach collection="items" item="item"  index"index" separator =",">
            (
            #{item.dt},
            #{item.name},
            #{item.age},
            #{item.key}
            )
        </foreach>

java代碼,使用了mybatis插件提供的事務處理:

 

@Transactional(readOnly = false)
   public Object testPrimary (String type{
       HashMap result = new HashMap();
       // 記錄總耗時 開始時間
       long start = new Date().getTime();
       // 記錄總耗時 插入條數
       int len = 0;
       try{
           String[] names = {"趙一""錢二""張三" , "李四""王五""宋六""陳七""孫八""歐陽九" , "徐10"};
           for (int w = 0; w 1000; w++) {
               // 記錄萬條耗時
               long startMil = new Date().getTime();

               ArrayList items = new ArrayList<>();
               for (int i = 0; i 10000; i++) {
                   String dt = StringUtils.uuid();
                   String key = StringUtils.uuid();
                   int age = (int)((Math.random() * 9 + 1) * 10); // 隨機兩位
                   String name = names[(int)(Math.random() * 9 + 1)];
                   HashMap item = new HashMap<>();
                   item.put("dt", dt);
                   item.put("key", key);
                   item.put("age", age);
                   item.put("name", name);
                   items.add(item);
               }
               len += tspTagbodyMapper.insertTestData(items, type);
               long endMil = new Date().getTime();
               // 萬條最終耗時
               result.put(w, endMil - startMil);
           }
           long end = new Date().getTime();
           // 總耗時
           result.put("all", end - start);
           result.put("len", len);
           return result;
       } catch (Exception e) {
           System.out.println(e.toString());
           result.put("e", e.toString());
       }
       return result;
   }

最終生成的資料表情況:

 

1、自增主鍵   test_primary_a  ———-  資料長度  960MB

 

62分鐘插入一千萬條資料  平均一萬條資料插入 4秒

 

2、自增主鍵  有索引 test_primary_d    資料長度  1GB    索引長度  1.36GB

 

75分鐘插入一千萬條資料  平均一萬條資料插入 4.5秒

 

3、無主鍵 無索引 test_primary_b   ———–   資料長度  960MB

 

65分鐘插入一千萬條資料  平均一萬條資料插入 4.2秒

 

4、複合主鍵 無索引 test_primary_c    ———–   資料長度  1.54GB

 

219分鐘插入一千萬條資料 平均一萬條資料插入 8秒, 這裡有一個問題, 複合主鍵的資料插入耗時是線性增長的,當資料小於100萬 插入時常在五秒左右, 當資料變大,插入時長無限變大,在1000萬條資料時,平均插入一萬資料秒數已經達到15秒了。

 

 

查詢速度

 

註意索引的建立時以name欄位為開頭,索引的生效第一個條件必須是name

 

簡單查詢:

 

select name,age from test_primary_a where age=20   — 自增主鍵 無索引 結果條數11萬 平均3.5秒

 

select name,age from test_primary_a where name=’張三’ and age=20   — 自增主鍵 有索引 結果條數11萬 平均650豪秒

 

select name,age from test_primary_b where age=20   — 無主鍵 無索引 結果條數11萬 平均7秒

 

select name,age from test_primary_c where age=20    — 聯合主鍵 無索引 結果條數11萬 平均4.5秒

 

 

稍複雜條件:

 

select name,age,`key`,dt from test_primary_a where age=20 and (name=’王五’ or name = ‘張三’) and dt like ‘%abc%’      — 自增主鍵 無索引 結果條數198 平均4.2秒

 

select dt,name,age,`key` from test_primary_d where  (name=’王五’ or name = ‘張三’) and age=20 and dt like ‘%abc%’      — 自增主鍵 有索引 結果條數204 平均650豪秒

 

select name,age,`key`,dt from test_primary_d where age=20 and (name=’王五’ or name = ‘張三’) and dt like ‘%abc%’      — 無主鍵 無索引 結果條數194 平均5.9秒

 

select name,age,`key`,dt from test_primary_c where age=20 and (name=’王五’ or name = ‘張三’) and dt like ‘%abc%’      — 聯合主鍵 無索引 結果條數11萬 平均5秒

 

這樣的陳述句更誇張一點:

 

select name,age,dt from test_primary_c where dt like ‘%0000%’ and name=’張三’        — 聯合主鍵 無索引 結果條數359 平均8秒

 

select name,age,dt from test_primary_c where dt like ‘%0000%’ and name=’張三’        — 自增主鍵 有索引 結果條數400 平均1秒

 

 

初步結論

 

從實際應用中可以看出:用各主鍵的對比,在匯入速度上,在前期百萬資料時,各表表現一致,在百萬資料以後,複合主鍵的新增時長將線性增長,應該是因為每一條新增都需要判斷是否重覆,而資料量一旦增大,每次新增都需要全表篩查。

 

另外一點,邏輯主鍵 + 索引的方式占用空間一共2.4G, 複合主鍵占用1.54G 相差大約1個G , 但是實際查詢效果看起來索引更勝一籌,只要查詢方法得當,索引應該是當前的首選。

 

最後,關於複合主鍵的作用? 我想應該是在業務主鍵欄位不超過2-3個的情況下,需要確保資料維度的唯一性,採取複合主鍵加上限制。

赞(0)

分享創造快樂