MySQL大表增加唯一索引场景

2023-03-19 10:27:00 浏览数 (2)

‍Oracle中字段、索引的使用，相对来讲，还是比较透明的，人为干涉的较少，过程相对标准，可参考历史文章，

《新增字段在数据块中的体现》

《Oracle删除字段的方式和风险，你都了解么？》

《新增字段的一点一滴技巧》

《探寻大表删除字段慢的原因》

《大表删除字段为何慢？》

《主键和唯一约束的索引肯定唯一？》

但MySQL中对于字段、索引的使用，就需要些技巧，否则就会碰到坑，这是初学MySQL，比较不太适应的一个点，看到技术社区推的这篇文章《技术分享 | MySQL 大表添加唯一索引的总结》，就讲到了MySQL大表添加唯一索引的途径和问题，确实可能是日常工作中常见的场景，但实际操作时，如果不了解原理，很可能就进坑了。

原文如下。

一、前言

在数据库的运维工作中经常会遇到业务的改表需求，这可能是 DBA 比较头疼的需求，其中添加唯一索引可能又是最头疼的需求之一了。

MySQL 5.6 开始支持 Online DDL ，添加[唯一]索引虽然不需要重建表，也不阻塞 DML ，但是大表场景下还是不会直接使用 Alter Table 进行添加，而是使用第三方工具进行操作，比较常见的就属 pt-osc 和 gh-ost 了。本文就来总结梳理一下添加唯一索引的相关内容。

本文对 ONLINE DDL 讨论的也是基于 MySQL 5.6 及以后的版本。

二、添加唯一索引的方案简介

这部分内容仅介绍 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

代码语言：javascript复制
# ./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

代码语言：javascript复制
# ./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

可能丢数据，有辅助功能可以避免部分丢数据的场景

适合添加唯一索引

三、添加唯一索引的风险

根据上面的介绍可以得知 gh-ost 是比较适合大表加唯一索引，所以这部分就着重介绍一下 gh-ost 添加唯一索引的相关内容，主要是希望能帮助大家避坑。

如果业务能接受从库长时间延迟，也推荐 ONLINE DDL 的方案。

3.1 风险介绍

我们都知道使用第三方改表工具添加唯一索引存在丢数据的风险，总结起来大致可以分如下三种：

文中出现的示例表的 id 字段默认是主键。

  • 第一，新加字段，并对该字段添加唯一索引。

id

name

age

1

张三

22

2

李四

19

3

张三

20

代码语言：javascript复制
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

深圳

代码语言：javascript复制
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

深圳

代码语言：javascript复制
alter table t add unique key uk_name(name);

如果这时候使用 gh-ost 执行上述需求，在拷贝原表数据期间，业务端新增一条如下面 INSERT 语句的记录。

代码语言：javascript复制
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.1 hook 功能

gh-ost 支持 hook 功能。简单来理解，hook 是 gh-ost 工具跟外部脚本的交互接口。使用起来也很方便，根据要求命名脚本名且添加执行权限即可。

具体使用请看官方文档：https://github.com/github/gh-ost/blob/f334dbde5ebbe85589363d369ee530e3aa1c36bc/doc/hooks.md

4.2 hook 使用样例

这个样例是网上找的，可能很多小伙伴都在用。

（1）创建 hook 目录

代码语言：javascript复制
mkdir /tmp/hook
cd /tmp/hook

（2）改表前执行的 hook 脚本

代码语言：javascript复制
vim gh-ost-on-rowcount-complete-hook
代码语言：javascript复制
#!/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 脚本

代码语言：javascript复制
vim gh-ost-on-row-copy-complete-hook
代码语言：javascript复制
#!/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）添加对应权限

代码语言：javascript复制
chmod  x /tmp/hook/*

（5）使用 在 gh-ost 命令添加如下参数即可。

代码语言：javascript复制
--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_CO PIED_ROWS】的值，最终会改表成功，但是实际会丢失数据。

有小伙伴可能会疑问，上述【gh-ost-on-row-copy-complete-hook】脚本中，为什么不用【GH_OST_ESTIMATED_ROWS】的值与【GH_OST_COPIED_ROWS】比较？

首先我们看一下【GH_OST_ESTIMATED_ROWS】的值是怎么来的。

代码语言：javascript复制
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校验）。
代码语言：javascript复制
vim gh-ost-on-before-cut-over

这表示在切表前需要执行的 hook 脚本，即：切表前检查一下唯一索引字段的数据是否有重复值，这样避免改表过程中新增的数据跟原来的有重复。

代码语言：javascript复制
#!/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 语句自动生成。

代码语言：javascript复制
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 可以使用如下的命令生成，仅供参考。

代码语言：javascript复制
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 。

代码语言：javascript复制
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）新增一条记录

代码语言：javascript复制
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）删除新增的记录

业务新增记录后意识到这条数据是重复的，所以又删除新增这条记录。

代码语言：javascript复制
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 参数可以确保不会丢失数据，这个参数的做法是在切表前停掉从库的复制，然后在从库上校验数据。

代码语言：javascript复制
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-migration-tool-for-mysql/#testable Testable部分（Testable不是书写错误）

很明显，这个方式还是没法解决在实际切表那一刻保证数据不会丢，就是说切表和校验之间一定是存在时间差，这个时间差内出现新写入重复数据是没法发现的，而且大表的这个时间差只会更大。

另外停掉从库的复制很可能也存在风险，很多业务场景是依赖从库进行读请求的，所以要慎用这个功能。

五、概括

  • 如果业务能接受，可以不使用唯一索引。将添加唯一索引的需求改成添加普通二级索引，这样就可以避免加索 引导致数据丢失。

存储引擎读写磁盘，是以页为最小单位进行。唯一索引较于普通二级索引，在性能上并没有多大优势。相反，可能还不如普通二级索引。

  • 在读请求上，唯一索引和普通二级索引的性能差异几乎可以忽略不计了。
  • 在写请求上，普通二级索引可以使用到【Change Buffer】，而唯一索引没法用到【Change Buffer】，所以唯一索引会差于普通二级索引。
  • 一定要加 唯一索引的话，可以 跟业务沟通确认是否能接受从库长时间延迟。如果能接受长时间延迟，可以优先使用 ONLINE DDL 进行添加唯一索引（小表直接用 ONLINE DDL即可）。
  • 如果使 用第三方工具添加唯一索引 ，要优先使用 gh-ost（配上hook），添加之前一定要先检查待加唯一索引字段的唯一性，避免因为原表存在重复值而导致丢数据。

强烈建议不要马上删除【old】表，万一碰到极端场景导致丢数据了，还可以通过【old】表补救一下。

  • pt-osc 建议添加【--no-drop-old-table】参数
  • gh-ost 不建议添加【--ok-to-drop-table】参数

六、提醒

本文对MySQL大表添加唯一索引做了一下总结，分享了一些案例和经验。

总体来说添加唯一索引是存在一定的风险的，各公司的业务场景也不一样，需求也不同，还可能碰上其他未知的问题，本文所有内容仅供参考。

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

sql ddl hook mysql 索引

0 人点赞