技術分享 | MySQL 大表新增唯一索引的總結

作者：莫善
某網際網路公司高階 DBA。
本文來源：原創投稿
*愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

1 前言

在資料庫的運維工作中經常會遇到業務的改表需求，這可能是DBA比較頭疼的需求，其中新增唯一索引可能又是最頭疼的需求之一了。

MySQL 5.6 開始支援 Online DDL，新增[唯一]索引雖然不需要重建表，也不阻塞DML，但是大表場景下還是不會直接使用Alter Table進行新增，而是使用第三方工具進行操作，比較常見的就屬pt-osc和gh-ost了。本文就來總結梳理一下新增唯一索引的相關內容。

本文對ONLINE DDL討論的也是基於MySQL 5.6及以後的版本。

2 新增唯一索引的方案簡介

這部分內容僅介紹ONLINE DDL、pt-osc和gh-ost三種方案，且僅做簡單介紹，更加詳細的內容請參考官方文件。

2.1 ONLINE DDL

首先我們看一下官方對新增索引的介紹：

Operation	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Creating or adding a secondary index	Yes	No	Yes	No

唯一索引屬於特殊的二級索引，將引用官方介紹新增二級索引的內容做例子。

可以看到ONLINE DDL採用In Place演算法建立索引，新增索引是不阻塞DML，大致流程如下：

同步全量資料。遍歷主鍵索引，將對應的欄位（多欄位）值，寫到新索引。
同步增量資料。遍歷期間將修改記錄儲存到Row Log，等待主鍵索引遍歷完畢後回放Row Log。

也不是完全不阻塞DML，在Prepare和Commit階段需要獲取表的MDL鎖，但Execute階段開始前就已經釋放了MDL鎖，所以不會阻塞DML。在沒有大查詢的情況下，持鎖時間很短，基本可以忽略不計，所以強烈建議改表操作時避免出現大查詢。

由此可見，表記錄大小影響著加索引的耗時。如果是大表，將嚴重影響從庫的同步延遲。好處就是能發現重複資料，不會丟資料。

2.2 pt-osc

# ./pt-online-schema-change --version
pt-online-schema-change 3.0.13
#

建立一張與原表結構一致的新表，然後新增唯一索引。
同步全量資料。遍歷原表，透過【INSERT IGNORE INTO】將資料複製到新表。
同步增量資料。透過觸發器同步增量資料。

觸發器	對映的SQL語句
INSERT 觸發器	REPLACE INTO
UPDATE 觸發器	DELETE IGNORE + REPLACE INTO
DELETE 觸發器	DELETE IGNORE

由此可見，這個方式不會校驗資料的重複值，遇到重複的資料後，如果是同步全量資料就直接忽略，如果是同步增量資料就覆蓋。

這個工具暫時也沒有相關輔助功能保證不丟資料或者在丟資料的場景下終止新增唯一索引操作。

pt-osc有個引數【--check-unique-key-change】可以禁止使用該工具新增唯一索引，如果不使用這個引數就表示允許使用pt-osc進行新增索引，當遇到有重複值的場景，好好謀劃一下怎麼跑路吧。

2.3 gh-ost

# ./bin/gh-ost --version
1.1.5
#

建立一張與原表結構一致的新表，然後新增唯一索引。
同步全量資料。遍歷原表，透過【INSERT IGNORE INTO】將資料複製到新表。
同步增量資料。透過應用原表DML產生的binlog同步增量資料。

binlog語句	對映的SQL語句
INSERT	REPLACE INTO
UPDATE	UPDATE
DELETE	DELETE

由此可見，這個方式也不會校驗資料的重複值，遇到重複的資料後，如果是同步全量資料就直接忽略，如果是同步增量資料就覆蓋。

值得一提的是，這個工具可以透過hook功能進行輔助，以此保證在丟資料的場景下可以直接終止新增唯一索引操作。

hook功能後文會著重介紹。

2.4 小總結

由上述介紹可知，各方案都有優缺點

方案	是否丟資料	建議
ONLINE DDL	不丟資料	適合小表，及對從庫延遲沒要求的場景
pt-osc	可能丟資料，無輔助功能可以避免丟資料的場景	不適合新增唯一索引
gh-ost	可能丟資料，有輔助功能可以避免部分丟資料的場景	適合新增唯一索引

3 新增唯一索引的風險

根據上面的介紹可以得知gh-ost是比較適合大表加唯一索引，所以這部分就著重介紹一下gh-ost新增唯一索引的相關內容，主要是希望能幫助大家避坑。

如果業務能接受從庫長時間延遲，也推薦ONLINE DDL的方案。

3.1 風險介紹

我們都知道使用第三方改表工具新增唯一索引存在丟資料的風險，總結起來大致可以分如下三種：

文中出現的示例表的id欄位預設是主鍵。

第一，新加欄位，並對該欄位新增唯一索引。

id	name	age
1	張三	22
2	李四	19
3	張三	20

alter table t add addr varchar(20) not null default '北京',add unique key uk_addr(addr); #注意這裡是不允許為空

如果這時候使用gh-ost執行上述需求，最後只會剩下一條記錄，變成下面這樣。

id	name	age	addr
1	張三	22	北京

第二，原表存在重複值，如下資料表。

id	name	age	addr
1	張三	22	北京
2	李四	19	廣州
3	張三	20	深圳

alter table t add unique key uk_name(name);

如果這時候使用gh-ost執行上述需求，id=3這行記錄就會被丟棄，變成下面這樣。

id	name	age	addr
1	張三	22	北京
2	李四	19	廣州

第三，改表過程中新寫（包含更新）的資料出現重複值。

id	name	age	addr
1	張三	22	北京
2	李四	19	廣州
3	王五	20	深圳

alter table t add unique key uk_name(name);

如果這時候使用gh-ost執行上述需求，在複製原表資料期間，業務端新增一條如下面INSERT語句的記錄。

insert into t(name,age,addr) values('張三',22,'北京');

這時候，id=1這行記錄就會被新增的記錄覆蓋，變成下面這樣

id	name	age	addr
2	李四	19	廣州
3	王五	20	深圳
4	張三	22	北京

3.2 風險規避

新加欄位，並對該欄位新增唯一索引的風險規避

針對這類場景，規避方式可以禁止【新增唯一索引與其他改表動作】同時使用。最終，將風險轉移到了上述的第二種場景（原表存在重複值）。

如果是工單系統，在前端稽核業務提交的SQL是否只有新增唯一索引操作，不滿足條件的SQL工單不允許提交。

原表存在重複值的風險規避

針對這類場景，規避方式可以採用hook功能輔助新增唯一索引，在改表前先校驗待新增唯一索引的欄位的資料唯一性。

改表過程中新寫（包含更新）的資料出現重複值的風險規避

針對這類場景，規避方式可以採用hook功能新增唯一索引，在全量拷完切表前校驗待新增唯一索引的欄位的資料唯一性。

4 新增唯一索引的測試

4.1 hook功能

gh-ost支援hook功能。簡單來理解，hook是gh-ost工具跟外部指令碼的互動介面。使用起來也很方便，根據要求命名指令碼名且新增執行許可權即可。

具體使用請看官方文件 https://github.com/github/gh-ost/blob/f334dbde5ebbe85589363d369ee530e3aa1c36bc/doc/hooks.md

4.2 hook使用樣例

這個樣例是網上找的，可能很多小夥伴都在用。

（1）建立hook目錄

mkdir /tmp/hook
cd /tmp/hook

（2）改表前執行的hook指令碼

vim gh-ost-on-rowcount-complete-hook

#!/bin/bash

echo "$(date '+%F %T') rowcount-complete schema:$GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME before_row:$GH_OST_ESTIMATED_ROWS"
echo "$GH_OST_ESTIMATED_ROWS" > /tmp/$GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME.txt

（3）全量複製完成後執行的hook指令碼

vim gh-ost-on-row-copy-complete-hook

#!/bin/bash

echo "時間: $(date '+%F %T') 庫表: $GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME 預計總行數: $GH_OST_ESTIMATED_ROWS 複製總行數: $GH_OST_COPIED_ROWS"

if [[ `cat /tmp/$GH_OST_DATABASE_NAME.$GH_OST_TABLE_NAME.txt` -gt $GH_OST_COPIED_ROWS ]];then
  echo '複製總行數不匹配，修改失敗，退出.'
  sleep 5
  exit -1
fi

（4）新增對應許可權

chmod +x /tmp/hook/*

（5）使用
在gh-ost命令新增如下引數即可。

--hooks-path=/tmp/hook

這個hook的工作流程大概如下：

改表前先執行【gh-ost-on-rowcount-complete-hook】指令碼獲取當前表的記錄數【GH_OST_ESTIMATED_ROWS】，並儲存到【GH_OST_DATABASE_NAME.GH_OST_TABLE_NAME.txt】檔案
原表全量資料複製完成後執行【gh-ost-on-row-copy-complete-hook】指令碼，獲取實際複製的記錄數【GH_OST_COPIED_ROWS】，然後和【GH_OST_DATABASE_NAME.GH_OST_TABLE_NAME.txt】檔案存的值做比較，如果實際複製的記錄數小，就視為丟資料了，然後就終止改表操作。反之就視為沒有丟資料，可以完成改表。

其實這個hook是存在風險的：

第一，如果改表過程中原表有刪除操作，那麼實際複製的行數勢必會比【GH_OST_DATABASE_NAME.GH_OST_TABLE_NAME.txt】檔案儲存的值小，所以會導致改表失敗。這種場景對我們來說體驗十分不友好，只要改表過程中目標表存在【DELETE】操作，就會導致新增唯一索引操作失敗。

關於這個問題，之前跟這個hook用例的原作者溝透過，他是知曉這個問題的，並表示他們的業務邏輯是沒有刪除【DELETE】操作，所以不會有影響。

第二，如果改表過程中，新加一條與原表的記錄重複的資料，那麼這個操作不會影響【GH_OST_COPIED_ROWS】的值，最終會改表成功，但是實際會丟失資料。

有小夥伴可能會疑問，上述【gh-ost-on-row-copy-complete-hook】指令碼中，為什麼不用【GH_OST_ESTIMATED_ROWS】的值與【GH_OST_COPIED_ROWS】比較？

首先我們看一下【GH_OST_ESTIMATED_ROWS】的值是怎麼來的。

GH_OST_ESTIMATED_ROWS := atomic.LoadInt64(&this.migrationContext.RowsEstimate) + atomic.LoadInt64(&this.migrationContext.RowsDeltaEstimate)

可以看到【GH_OST_ESTIMATED_ROWS】是預估值，只要原表在改表過程中有DML操作，該值就會變化，所以不能用來和【GH_OST_COPIED_ROWS】作比較。

hook實現邏輯請參考 https://github.com/github/gh-ost/blob/master/go/logic/hooks.go

4.3 加強版 hook 樣例

上面的hook樣例雖然存在一定的不足，但是也給我提供了一個思路，知道有這麼個輔助功能可以規避新增唯一索引引發丟資料的風險。

受這個啟發，並查閱了官方文件後，我整理了個加強版的hook指令碼，只需要一個指令碼就能避免上述存在的幾種問題。

按說應該是兩個指令碼，且程式碼一致即可。
改表前先校驗一次原表是否存在待新增唯一索引的欄位的資料是否是唯一的，如果不滿足唯一性就直接退出新增唯一索引。
切表前再校驗一次，但是我們環境是在程式碼裡面做了校驗，在業務提交工單後直接先判斷唯一性，然後再處理後續的邏輯，所以第一個校驗就省略了（改表工單程式碼代替hook校驗）。

vim gh-ost-on-before-cut-over

這表示在切表前需要執行的hook指令碼，即：切表前檢查一下唯一索引欄位的資料是否有重複值，這樣避免改表過程中新增的資料跟原來的有重複。

#!/bin/bash
work_dir="/opt/soft/zzonlineddl"                                  #工作目錄
. ${work_dir}/function/log/f_logging.sh                           #日誌模組
if [ -f "${work_dir}/conf/zzonlineddl.conf" ]
then
    . ${work_dir}/conf/zzonlineddl.conf                           #改表專案的配置檔案
fi

log_addr='${BASH_SOURCE}:${FUNCNAME}:${LINENO}' #eval echo ${log_addr}

#針對該改表任務生成的配置檔案
#裡面儲存的是這個改表任務的目標庫的從庫連線資訊【mysql_comm】變數的值
#還有資料唯一性的校驗SQL【mysql_sql】變數的值
hook_conf="${work_dir}/hook/conf/--mysql_port--_${GH_OST_DATABASE_NAME}.${GH_OST_TABLE_NAME}"  

. ${hook_conf}

function f_main()
{
    count_info="$(${mysql_comm} -NBe "${mysql_sql}")"
    count_total="$(awk -F: '{print $NF}' <<< "${count_info}")"
    
    f_logging "$(eval echo ${log_addr}):INFO" "庫表: ${GH_OST_DATABASE_NAME}.${GH_OST_TABLE_NAME} 原表預計總行數: ${GH_OST_ESTIMATED_ROWS}, 實際複製總行數: ${GH_OST_COPIED_ROWS}"

    if [ -z "${count_total}" ]
    then
        f_logging "$(eval echo ${log_addr}):ERROR" "唯一索引欄位資料唯一性檢查異常, 終止改表操作"
        exit -1
    fi

    mark=""

    for count in $(echo "${count_info}"|tr ":" " ")
    do
        if [ -n "${count}" ] && [ "${count}x" == "${count_total}x" ]
        then
            [ "${mark}x" == "x" ] && mark="true"
        else 
            mark="false"
        fi
    done
    
    if [ "${mark}x" == "truex" ]
    then
        f_logging "$(eval echo ${log_addr}):INFO" "唯一索引欄位資料唯一性正常, 允許切表"
    else 
        f_logging "$(eval echo ${log_addr}):ERROR" "唯一索引欄位資料唯一性檢測到可能丟失資料, 終止改表操作"
        exit -1
    fi
    exit 0
}

f_main

該指令碼非通用版，僅供參考。

hook_conf變數的值是這樣的，由改表平臺根據業務的SQL語句自動生成。

mysql_comm='mysql -h xxxx -P xxxx -u xxxx -pxxxx db_name'   #這裡是從庫的地址
mysql_sql="select concat(count(distinct rshost,a_time),':',count(*)) from db.table"

其中檢查唯一性的SQL可以使用如下的命令生成，僅供參考。

alter="alter table t add unique key uk_name(name,name2),add unique key uk_age(age);"
echo "${alter}"|awk 'BEGIN{ FS="(" ; RS=")";print "select concat(" } 
    NF>1 { print "count(distinct "$NF"),'\'':'\''," }
    END{print "count(*)) from t;"}'|tr -d '\n'

執行上面的命令會根據業務提交的新增唯一索引的SQL得到一條檢查欄位資料唯一性的SQL。

select concat(count(distinct name,name2),':',count(distinct age),':',count(*)) from t;

需要注意的是，這個加強版的hook也不能100%保證一定不會丟資料，有兩種極端情況還是會丟資料。

第一，如果是大表，在執行【gh-ost-on-before-cut-over】指令碼過程中（大表執行這個指令碼時間較長），新增的記錄跟原來資料有重複，這個就沒法規避了。
第二，在改表過程中，如果業務新增一條與原資料重複的記錄，然後又刪除，這種場景也會導致丟資料。

針對第二個場景可能有點抽象，所以舉一個具體的例子，原表資料如下：

id	name	age	addr
1	張三	22	北京
2	李四	19	廣州
3	王五	20	深圳

現在對name欄位新增唯一索引。

假如現在正在使用gh-ost進行新增唯一索引，這時候業務做了下面幾個操作：

（1）新增一條記錄

insert into t(name,age,addr) values('張三',22,'北京');

這時候原表的資料就會變成像下面這樣。

id	name	age	addr
1	張三	22	北京
2	李四	19	廣州
3	王五	20	深圳
4	張三	22	北京

這時候新表的資料就會變成像下面這樣。

id	name	age	addr
2	李四	19	廣州
3	王五	20	深圳
4	張三	22	北京

id=1和id=4是兩條重複的記錄，所以id=1會被覆蓋掉。

（2）刪除新增的記錄

業務新增記錄後意識到這條資料是重複的，所以又刪除新增這條記錄。

delete from t where id = 4;

這時候原表的資料就會變成像下面這樣。

id	name	age	addr
1	張三	22	北京
2	李四	19	廣州
3	王五	20	深圳

這時候新表的資料就會變成像下面這樣。

id	name	age	addr
2	李四	19	廣州
3	王五	20	深圳

可以發現，這時候如果發生切表，原表id=1的記錄將會丟失，而且這種場景hook的指令碼沒法發現，它檢查原表的name欄位的資料唯一性是正常的。

針對上述兩種極端場景，發生的機率應該是極低的，目前我也沒想到什麼方案解決這兩個場景。

gh-ost官方文件上說--test-on-replica引數可以確保不會丟失資料，這個引數的做法是在切表前停掉從庫的複製，然後在從庫上校驗資料。

gh-ost comes with built-in support for testing via --test-on-replica: 
it allows you to run a migration on a replica, such that at the end of the migration gh-ost would stop the replica, swap tables, reverse the swap, and leave you with both tables in place and in sync, replication stopped.
This allows you to examine and compare the two tables at your leisure.

https://github.blog/2016-08-01-gh-ost-github-s-online-migrati... Testable部分（Testable不是書寫錯誤）

很明顯，這個方式還是沒法解決在實際切表那一刻保證資料不會丟，就是說切表和校驗之間一定是存在時間差，這個時間差內出現新寫入重複資料是沒法發現的，而且大表的這個時間差只會更大。

另外停掉從庫的複製很可能也存在風險，很多業務場景是依賴從庫進行讀請求的，所以要慎用這個功能。

5 總結

如果業務能接受，可以不使用唯一索引。將新增唯一索引的需求改成新增普通二級索引，這樣就可以避免加索引導致資料丟失。

儲存引擎讀寫磁碟，是以頁為最小單位進行。唯一索引較於普通二級索引，在效能上並沒有多大優勢。相反，可能還不如普通二級索引。
在讀請求上，唯一索引和普通二級索引的效能差異幾乎可以忽略不計了。
在寫請求上，普通二級索引可以使用到【Change Buffer】，而唯一索引沒法用到【Change Buffer】，所以唯一索引會差於普通二級索引。

一定要加唯一索引的話，可以跟業務溝通確認是否能接受從庫長時間延遲。如果能接受長時間延遲，可以優先使用ONLINE DDL進行新增唯一索引（小表直接用ONLINE DDL即可）。
如果使用第三方工具新增唯一索引，要優先使用gh-ost（配上hook），新增之前一定要先檢查待加唯一索引欄位的唯一性，避免因為原表存在重複值而導致丟資料。

強烈建議不要馬上刪除【old】表，萬一碰到極端場景導致丟資料了，還可以透過【old】表補救一下。
pt-osc 建議新增【--no-drop-old-table】引數
gh-ost <font color='red'>不建議新增</font>【--ok-to-drop-table】引數

6 寫在最後

本文對MySQL大表新增唯一索引做了一下總結，分享了一些案例和經驗。

總體來說新增唯一索引是存在一定的風險的，各公司的業務場景也不一樣，需求也不同，還可能碰上其他未知的問題，<font color='red'>本文所有內容僅供參考。</font>