背景

在程序設(shè)計(jì)中，我們往往需要確保數(shù)據(jù)的唯一性，比如在常見(jiàn)的注冊(cè)模塊，我們需要確保一個(gè)手機(jī)號(hào)只能注冊(cè)為一個(gè)賬號(hào)。

這種情況下，我們的程序往往是第一道關(guān)卡，用戶來(lái)注冊(cè)之前，首先判斷這個(gè)手機(jī)號(hào)是否已經(jīng)注冊(cè)，如果已經(jīng)注冊(cè)則返回錯(cuò)誤信息,或直接去登錄。

但是我們不能確保同時(shí)有兩個(gè)人使用同一個(gè)手機(jī)號(hào)注冊(cè)到我們的系統(tǒng)中，因此這里就需要在更深的層次去確保手機(jī)號(hào)在系統(tǒng)的唯一性了。

不同存儲(chǔ)方案，解決方式不一樣。

對(duì)于常用的MySQL數(shù)據(jù)庫(kù)，我們可以使用唯一索引的方式來(lái)作為我們的最后一道防線。

但是最近在使用數(shù)據(jù)庫(kù)的唯一索引時(shí)，發(fā)現(xiàn)一個(gè)比較奇怪的現(xiàn)象。

MySQL數(shù)據(jù)庫(kù)，使用InnoDB存儲(chǔ)引擎，創(chuàng)建了唯一索引時(shí)，在insert操作時(shí)，如果唯一索引上的字段有為NULL的情況，則可以無(wú)限插入。

這有點(diǎn)匪夷所思，但是現(xiàn)實(shí)就是這么一個(gè)情況。

現(xiàn)在就來(lái)具體分析這樣的一個(gè)案例，來(lái)看看底層對(duì)于唯一索引是怎么設(shè)計(jì)的，來(lái)規(guī)避在數(shù)據(jù)庫(kù)設(shè)計(jì)上犯錯(cuò)和踩坑。

案例

假設(shè)現(xiàn)在有一個(gè)用于保存用戶信息的數(shù)據(jù)表user，是使用email注冊(cè)的，當(dāng)前使用email作為唯一索引，同時(shí)這一基本規(guī)則也被其他依賴系統(tǒng)作為設(shè)計(jì)數(shù)據(jù)模型的設(shè)計(jì)基礎(chǔ)。

假設(shè)現(xiàn)在設(shè)計(jì)這樣一個(gè)user表：

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',
  `name` varchar(11) DEFAULT '' COMMENT 'name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1@user.com來(lái)注冊(cè)，執(zhí)行insert語(yǔ)句,執(zhí)行成功

INSERT INTO user (email,name,age) VALUES ('1@user.com','h1',18);

1@user.com再來(lái)注冊(cè)，則再次執(zhí)行，則報(bào)錯(cuò)。

成功規(guī)避了用戶多次創(chuàng)建導(dǎo)致系統(tǒng)產(chǎn)生臟數(shù)據(jù)問(wèn)題。

Duplicate entry '1@user.com' for key 'uk-email'

從這里看，user表的設(shè)計(jì)是符合業(yè)務(wù)要求的，并沒(méi)有出現(xiàn)同一個(gè)email出現(xiàn)多行的情況。

隨著業(yè)務(wù)發(fā)展，單單email注冊(cè)的模式并不適合移動(dòng)互聯(lián)網(wǎng)時(shí)代，所以現(xiàn)在的要求在原有基礎(chǔ)上增加了手機(jī)號(hào)的字段，并要求手機(jī)號(hào)也是唯一的。

于是添加phone字段，并將原有唯一索引刪除，為email和phone設(shè)置新的唯一索引。

ALTER TABLE `user` ADD COLUMN `phone` varchar(11) default NULL AFTER `age`;

DROP INDEX `uk-email` ON `user`;

ALTER TABLE `user` ADD UNIQUE KEY `uk-email-phone` (`email`,`phone`);

假設(shè)用戶1再來(lái)用同樣的email注冊(cè),可以注冊(cè)成功：

INSERT INTO user (email,name,age,phone) VALUES (‘1@user.com',‘h1',18,NULL);

查詢數(shù)據(jù)庫(kù)數(shù)據(jù)，得到以下結(jié)果：

有兩個(gè)email為1@user.com的記錄，他們的phone都是NULL，這怎么可能存在？！難道是MySQL出問(wèn)題了？！不可能，我們?cè)僭嚵硗庖粋€(gè)數(shù)據(jù)

INSERT INTO user (email,name,age,phone) VALUES ('2@user.com','h2',18,'18812345678');

連續(xù)執(zhí)行兩次，第一次執(zhí)行成功，第二次報(bào)錯(cuò)：

Duplicate entry ‘2@user.com-18812345678’ for key ‘uk-email-phone’

查詢user結(jié)果集，得到

從結(jié)果看這樣MySQL的唯一索引也算是正常的啊，那這到底是怎么一回事呢？

原因探尋

業(yè)務(wù)中希望建立的唯一索引是email + phone的組合，但是由于phone一開(kāi)始是沒(méi)有數(shù)據(jù)的，所以新建字段時(shí)默認(rèn)允許為NULL來(lái)兼容老數(shù)據(jù)。

如果程序沒(méi)有控制好，數(shù)據(jù)操作直接打到數(shù)據(jù)庫(kù)，就產(chǎn)生了兩條email為“1@user.com”且phone為NULL的數(shù)據(jù)，那么就會(huì)發(fā)生這種數(shù)據(jù)錯(cuò)亂的情況。

我從 MySQL 5.7官方文檔 中找到了這個(gè)：

Unique Indexes

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.

官方的文檔中明確說(shuō)明在唯一索引中是允許存在多行值為NULL的數(shù)據(jù)存在的。

當(dāng)然我們會(huì)認(rèn)為這是MySQL的一個(gè)bug，其實(shí)早有人這么認(rèn)為了，并給MySQL提出了這個(gè)問(wèn)題https://bugs.mysql.com/bug.php?id=8173。

但是MySQL的開(kāi)發(fā)者并不認(rèn)為這是一個(gè)bug，而是本身的一種設(shè)計(jì)。額，這么說(shuō)，好像也說(shuō)得過(guò)去。那這里就有一個(gè)問(wèn)題了，我們知道索引是使用B+樹(shù)來(lái)維護(hù)的，但是對(duì)于這種非唯一索引是怎么維護(hù)的？

帶著這個(gè)問(wèn)題，我覺(jué)得有兩種可能：

（1）唯一索引時(shí)另外一種數(shù)據(jù)類(lèi)型，正好把有值為NULL的字段過(guò)濾掉了，無(wú)需特殊處理。

（2）還是用的B+樹(shù)索引，但是對(duì)于NULL的索引特殊處理了。

于是我對(duì)email=2@user.com且phone= 18812345678的數(shù)據(jù)執(zhí)行了Explain執(zhí)行計(jì)劃

explain select * from user where `email` = '2@user.com' and `phone` = '18812345678';

這個(gè)查詢正好用到了唯一索引uk-email-phone，索引長(zhǎng)度是134。

對(duì)email=1@user.com且phone為NULL的執(zhí)行類(lèi)似Explain執(zhí)行計(jì)劃

explain select * from user where `email` = '1@user.com' and `phone` is   NULL;

對(duì)比上面兩次不同數(shù)據(jù)的explain執(zhí)行結(jié)果，可以看到其實(shí)都用了uk-email-phone的唯一索引，不同的是第一個(gè)type是const(通過(guò)一次索引就可以找到，用于primary key或unique index)，第二個(gè)type是ref(非唯一性索引掃描)，且rows為2。

所以猜測(cè)這里極有可能是對(duì)NULL進(jìn)行的特殊處理，唯一索引樹(shù)還是用的和非NULL一樣的唯一索引樹(shù)。

源碼分析

上面利用explain，測(cè)試結(jié)果是符合自己的猜測(cè)行為而已。也許只有源碼中才能比較好的知道答案，基于此，在github上找到MySQL相關(guān)的源碼(在此感謝DBA同學(xué)在唯一索引源碼分析上的指點(diǎn))。

在這段源碼https://github.com/mysql/mysql-server/blob/8e797a5d6eb3a87f16498edcb7261a75897babae/storage/innobase/row/row0ins.cc中，有一個(gè)方法 row_ins_scan_sec_index_for_duplicate(),這里會(huì)掃描唯一非聚簇索引樹(shù)，來(lái)確定是否會(huì)發(fā)生唯一性的沖突。

源碼內(nèi)有一段注釋

/* 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 */

在繼續(xù)往下有一段這樣的邏輯

	 cmp = cmp_dtuple_rec(entry, rec, index, offsets);

    if (cmp == 0 && !index->allow_duplicates) {
      if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) {
        err = DB_DUPLICATE_KEY;

        thr_get_trx(thr)->error_info = index;

        /* If the duplicate is on hidden FTS_DOC_ID,
        state so in the error log */
        if (index == index->table->fts_doc_id_index &&
            DICT_TF2_FLAG_IS_SET(index->table, DICT_TF2_FTS_HAS_DOC_ID)) {
          ib::error(ER_IB_MSG_958) << "Duplicate FTS_DOC_ID"
                                      " value on table "
                                   << index->table->name;
        }

        goto end_scan;
      }
    } else {
      ut_a(cmp < 0 || index->allow_duplicates);
      goto end_scan;
    }

跳轉(zhuǎn)到row_ins_dupl_error_with_rec()方法中有一段這樣的邏輯

/* In a unique secondary index we allow equal key values if they
  contain SQL NULLs */

  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，代碼中就沒(méi)有拋出DB_DUPLICATE_KEY的異常了。

所以從源碼來(lái)看，這里實(shí)現(xiàn)了唯一索引允許為NULL的情況了，而且可以知道，這個(gè)唯一索引樹(shù)和其他的二級(jí)索引基本上是沒(méi)什么區(qū)別的。

這也是前面explain時(shí)及時(shí)我們查詢非唯一索引中另一個(gè)字段為空的記錄，也還是用到了同樣的索引和相同的索引長(zhǎng)度。

反觀來(lái)看，如果是我們?cè)谖粗獙?shí)現(xiàn)的情況下，要我們來(lái)設(shè)計(jì)，怎么實(shí)現(xiàn)允許有字段為NULL的唯一索引呢？是否還有比現(xiàn)有MySQL更好的方式來(lái)實(shí)現(xiàn)？

結(jié)論

所以其實(shí)MySQL在唯一索引中允許存在值為NULL的字段。

NULL值在MySQL可以代表是任意值，并且在有字段值為NULL時(shí)，不會(huì)參與校驗(yàn)這個(gè)組合的唯一索引，所以可能插入業(yè)務(wù)上不允許重復(fù)的數(shù)據(jù)，導(dǎo)致臟數(shù)據(jù)。

因此在創(chuàng)建屬于唯一索引的列時(shí)，最好指定字段值不能為空，在已有值為NULL的情況下，創(chuàng)建的字段不允許為空，且默認(rèn)值為空字符。

如果已經(jīng)創(chuàng)建了默認(rèn)值為NULL的字段，則先將其update為空字符，然后再修改為NOT NULL DEFAULT ‘’。

如上述情況建表語(yǔ)句改為

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',
  `name` varchar(11) DEFAULT '' COMMENT 'name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `phone` varchar(11) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-email-phone` (`email`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

并非所有數(shù)據(jù)庫(kù)都是這樣，SQL Server 2005及更老的版本，只允許有一個(gè)NULL值出現(xiàn)。

從https://sqlite.org/faq.html#q26 了解到ANSI SQL-92標(biāo)準(zhǔn)：

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.（如果且僅當(dāng)表中沒(méi)有兩行在唯一列中具有相同的非空值時(shí)，才滿足唯一約束。）

除了MySQL之外，sqlLite、PostgreSQL、Oracle和FireBird也是允許唯一索引上存在多行為NULL。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。