唯一索引引发的思考

Posted on Edited on In
MySQL Index

最近需要改动线上一个有千万条记录的表，涉及到加字段操作，这个表有索引，按照经验，为了加速修改表结构,去掉索引。由于我删除的是Unique Index, 而服务一直在写,程序依赖数据库的唯一索引去重,导致瞬间有重复数据,唯一索引重新加上时会报 Duplicated Key entry Error,

###回放事件###

我们的数据表，之前是有 UNIQUE INDEX(cuid, aid), 因为去掉索引，服务持续写入，导致有重复记录，所幸的是，这是一个统计表, 不影响功能，所以需要找出重复的记录

1
2
3
4
5
select cuid,aid from (
    select cuid, aid,count(1) as num
    from register_chn 
    group by cuid,aid having num > 1
) t;

显示有8条记录,如果手动删除,是很慢且愚蠢的做法,还是用 SQL 执行,镇定之后执行

1
2
3
4
5
6
7
delete from register_chn where (cuid,aid) in (
    select cuid,aid from (
        select cuid, aid,count(1) as num
        from register_chn
        group by cuid,aid having num > 1
    ) t
);

影响了８条记录.然后瞬间加上索引.所幸是成功了, 事实上当时的合理操作应该是用事务。

1
2
3
4
5
6
7
8
9
10
BEGIN;
delete from register_chn where (cuid,aid) in (
    select cuid,aid from (
        select cuid, aid,count(1) as num
        from register_chn
        group by cuid,aid having num > 1
    ) t
);
alter table register_chn add unique index(cuid, aid);
COMMINT;

###引发的思考###

后来想想, 上述方法虽然解决问题了, 但是有点碰运气成分。如果频繁快速地产生重复记录,也许就没那么好运了,事实上可以执行以下 SQL:

1
alter ignore table register_chn add unique index(cuid, aid)；

如果你以为很简单,那就错了。这个方法在 MySQL 5.0 上使用是没问题的，但是在5.6 之前是有bug的，亲自测试Percona 版本5.5, 的确会失败
官方的解决方法是：

1
2
set session old_alter_table = on;
alter ignore table register_chn add unique index(cuid, aid);

在有主备的情况,记得执行前设置一下

1
set session sql_log_bin=off.

以免备库报错。同样还需要在备库重复一下主库的操作, 这也算是一个不太完美的解决思路。