了解主鍵、外鍵、索引

主鍵

主鍵的主要作用是保證表的完整、保證表數據行的唯一性質，

① 業務主鍵（自然主鍵）：在數據庫表中把具有業務邏輯含義的字段作為主鍵，稱為“自然主鍵(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(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,  -- 隨機名稱   `age` int(10) NULL DEFAULT NULL,   -- 隨機數生成年齡   `key` varchar(40) CHARACTER 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代碼：

       

	<insert id="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<HashMap> 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個的情況下，需要確保數據維度的唯一性，采取復合主鍵加上限制。