关于mysql:技术分享-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>

【腾讯云】轻量 2核2G4M,首年65元

阿里云限时活动-云数据库 RDS MySQL  1核2G配置 1.88/月 速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据