5月MySQL问题合集

好的，回到今天的主题，这一篇文章，来分享一些这个月朋友问到的一些MySQL问题，以及自己的见解，当然，不一定对，欢迎大家在留言区讨论。

问题1

在使用pt-osc有没有遇到过什么问题？

以前遇到过，某个表一个字段有重复记录，如果给这个字段加唯一索引，会丢数据。

为什么会出现这种情况，我们再来复习一下pt-osc的原理：

1 创建一张与原始表结构相同的临时表

2 然后对临时表进行表结构变更

3 通过触发器实现增量数据处理

delete =delete ignore

insert  = replace into

update=delete ignore + replace into

4 将原始表中的行复制到新表中

copy rows = insert ignore into

5 复制完成后，把原始表重命名为_xxx_old，将临时表重命名为xxx，再删除_xxx_old表

结合pt-osc的原理，再来分析上面的问题。

使用pt-osc添加唯一索引的时候，因为创建的临时表会先进行表结构变更，也就是添加唯一索引，再从原始表把数据复制到新表里。

这个时候，临时表因为有唯一索引，并且迁移数据是使用的 insert ignore into，也就是只会保留唯一索引这个字段的第一行，后面的全会忽略掉。

这也就是通过pt-osc添加唯一索引会丢数据的原因。

当然，有人会说，既然这个字段要添加唯一索引，那唯一索引的字段重复数据本身就应该处理掉。

实际情况是，这些重复数据，需要研发确定，然后自己处理，决定保留哪些行，而不是我们简单粗暴的insert ignore into掉。

问题2

session1：

begin;

update test_gap set name='cc' where name='c';

session2：

insert into test_gap values(51,'a');  会lock住

insert into test_gap values(41,'a');  不会lock住

RR隔离级别下，为什么name相同只要插入id>50的就会被lock住呢？二级索引对应的主键索引不会加gap lock吧？

表结构和数据如下：

这个问题可以结合这张图理解：

再结合问题的这张表，name和id一起看。

锁的范围是a 50 到 e 30这个区间。

也就是：

a 5
a 10
a 50
gap lock
c 20
gap lock
c 60
gap lock
e 30
...

问题3

不小心update全表数据怎么恢复？

类似这种，可以参考之前写的：MySQL执行delete误删除数据恢复。

也就是通过my2sql工具，解析出回滚语句。

当然，其他场景的数据恢复，可以关注公众号，回复“数据恢复”获得。

问题4

在MySQL中的update的两表关联是不是走不了hash join？
这个我进行了实验，表结构如下：

CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB;

‍
下图是实验过程：
确实update使用不了hash join（关于hash join的详细介绍，可以参考：一文弄懂Join语句优化）。关于这个问题，我问了kimi
当然，这个问题，还是建议在关联字段添加索引。
好的，这些问题就分析到这里，欢迎指正。
更多MySQL干货合集可以：点击跳转。