詳解MySQL的欄位預設null對唯一索引的影響

2022-09-26 14:02:34

正文

在日常業務開發中，會經常遇到需要保證唯一性的資料業務，如使用者註冊業務。一般註冊業務中允許使用者以手機號或email註冊賬號，且需要保證唯一，不允許重複註冊。當用戶輸入手機號或email登入時，程式會判定輸入資訊的存在與否性，存在則走登入，不存在則走註冊。而保證唯一性就不僅僅需要在程式端做判斷，還需要MySQL的唯一索引去做最後一道防線。那麼唯一索引在一些業務中使用，如果唯一索引欄位中預設值設定為了null，會造成什麼後果呢？

在阿里的《阿里巴巴Java開發手冊》中關於MySQL-索引規範中寫道：【強制】業務上具有唯一特性的欄位，即使是多個欄位的組合，也必須建立唯一索引。

說明：

不要以為唯一索引影響了insert速度，這個速度的損耗可以忽略不計，但提高查詢的速度是明顯的；

另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有髒資料產生。

看一下為何唯一索引為影響insert速度

在MySQL中，唯一索引樹是一個非聚簇索引，每次插入資料時，都會在唯一索引樹上進行遍歷查詢該插入值是否唯一，這也就是為什麼會影響insert的速度，因為多一步遍歷判斷唯一性。

MySQL版本：在docker中啟動一個mysql

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)

假設只存在郵箱註冊：

#建表語句
CREATE TABLE `user_1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT '郵箱',
  `name` varchar(11) DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert資料

#第一次插入：
insert into user(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
#再次插入同樣的資料：
insert into user(email,name) values('aaa@qq.com','aaa');
1062 - Duplicate entry 'aaa@qq.com' for key 'uk-email', Time: 0.005000s

此時對於唯一性來說是沒問題的，可以保證業務的email的唯一性。假設隨著業務的發展，此時需要增加手機號註冊功能，那麼表中就需要增加手機號欄位，且需要保證手機號和郵箱的關聯唯一性。

#建表語句，注意此時phone欄位的預設值為null
CREATE TABLE `user_2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT '郵箱',
  `phone` char(11) DEFAULT NULL COMMENT '手機號',
  `name` varchar(11) DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-email-phone` (`email`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert資料

insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,phone,name) values('bbb@qq.com','13333333333','bbb');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,phone,name) values('bbb@qq.com','13333333333','bbb');
1062 - Duplicate entry 'bbb@qq.com-13333333333' for key 'uk-email-phone', Time: 0.002000s

此時會發現，不帶phone值得前三條資料都能插入成功，帶上郵箱和手機號的值卻能正常判斷唯一性

mysql> select * from user_2;
+----+------------+-------------+------+
| id | email      | phone       | name |
+----+------------+-------------+------+
|  1 | aaa@qq.com | NULL        | aaa  |
|  2 | aaa@qq.com | NULL        | aaa  |
|  3 | aaa@qq.com | NULL        | aaa  |
|  4 | bbb@qq.com | 13333333333 | bbb  |
+----+------------+-------------+------+
4 rows in set (0.00 sec)

這時就需要牽扯到MySQL的唯一索引機制了：在MySQL官方檔案中MySQL索引檔案，描述到：

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL.

解釋一下：唯一索引建立一個約束，使得索引中的所有值都必須是不同的。如果嘗試新增一個鍵值與現有行匹配的新行，則會發生錯誤。如果在唯一索引中為列指定字首值，則列值在字首長度內必須是唯一的。唯一索引允許包含空值的列有多個空值。

先看下explain執行計劃：

mysql> explain select * from user_2 where email='aaa@qq.com' and phone is NULL;
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | user_2 | NULL       | ref  | uk-email-phone | uk-email-phone | 132     | const,const |    3 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql>
mysql> explain select * from user_2 where email='bbb@qq.com' and phone='13333333333';
+----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | user_2 | NULL       | const | uk-email-phone | uk-email-phone | 132     | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

有沒有發現一個有趣的現象，雖然兩個sql語句都使用到了uk-email-phone唯一索引，但是第一條sql的type為ref 第二條sql的type為const 我們知道，explain執行計劃中，const一般是主鍵查詢或者唯一索引查詢是才會出現，而ref一般是使用普通索引時出現。所以，可以得出結論，MySQL在底層對唯一索引的null值做了特殊處理。

我們通過檢視原始碼檔案的1863行，有這麼個註釋：

Scans a unique non-clustered index at a given index entry to determine whether a uniqueness violation has occurred for the key value of the entry. Set shared locks on possible duplicate records

意思是掃描給定索引項處的唯一非聚集索引以確定條目的鍵值是否發生唯一性衝突。對可能重複的記錄設定共用鎖。

也就是說row_ins_scan_sec_index_for_duplicate()該方法就是處理唯一索引的，繼續往下看，在1892行，有一串註釋：

If the secondary index is unique, but one of the fields in the n_unique first fields is NULL, a unique key violation cannot occur, since we define NULL != NULL in this case

如果二級索引是唯一的，但是唯一索引的欄位存在NULL，則不會發生唯一性衝突，在此程式碼中定義了NULL != NULL

繼續往下走，在1996行，走到了row_ins_dupl_error_with_rec()函數，該函數在1825行。在該函數中有以下程式碼：

/* In a unique secondary index we allow equal key values if they
  contain SQL NULLs 
   在唯一的二級索引中，如果包含sql NULL值
*/
  if (!index->is_clustered() && !index->nulls_equal) {
    for (i = 0; i < n_unique; i++) {
      if (dfield_is_null(dtuple_get_nth_field(entry, i))) {
        return (FALSE);
      }
    }
  }

也就是說，在唯一索引中欄位為NULL的情況下，返回false，沒有丟擲DB_DUPLICATE_KEY異常.

經驗

唯一索引重複插入之終極解決方案：給欄位設定空字串初始值，NOT NULL DEFAULT ''即可，不要用null值作為初始值。

以上就是詳解MySQL的欄位預設null對唯一索引的影響的詳細內容，更多關於MySQL欄位預設null唯一索引的資料請關注it145.com其它相關文章！

詳解MySQL的欄位預設null對唯一索引的影響

目錄

正文

看一下為何唯一索引為影響insert速度

MySQL版本：在docker中啟動一個mysql

假設只存在郵箱註冊：

insert資料

經驗

熱門文章