有关mysql的一些小技巧

数据库  /  管理员 发布于 6年前   250

1. 大批量乱序数据导入InnoDB很慢如何解决？

InnoDB因为主键聚集索引的关系，如果没有主键或者主键非序列的情况下，导入会越来越慢，如何快速的迁移数据到InnoDB？借助MyISAM的力量 是很靠谱的，先关闭InnoDB的Buffer Pool，把内存空出来，建一张没有任何索引的MyISAM表，然后只管插入吧，concurrent_insert=2，在文件末尾并发插入，速度刚刚 的，插入完成后，ALTER TABLE把索引加上，记得还有ENGINE=InnoDB，就把MyISAM转到InnoDB了，这样的速度远比直接往InnoDB里插乱序数据来得快。

2. 在基于ROW的双Master复制下，如何快速大批量订正？

在A<->B的双Master结构下，假设只有一台提供服务，这是我们常用的架构，需要大批量订正数据，如何做最快？用存储过程一批批提交？这有很多的限制，有时候并不可以把一条或多条SQL拆成几段，怎么办呢？binlog不是很好的工具嘛?! ROW格式的binlog，Slave在应用时是直接使用Handler API，并没有走SQL解析，速度非常快，基本上是IO操作了，那么我们可以在备库上直接执行订正SQL，产生的ROW binlog传到主机，就会很快订正完，基本上都比写存储过程快。

3. ROW格式Replication如何实现不带库名的replicate-do-db？

虽然MySQL有replicate-do-db这个参数，但是在ROW格式的binlog下必须使用”db.table”的方式才能生效，USE对ROW格式是无效的。现在我有一个Instance，只需要复制Master的某几个库，但是是ROW格式，SQL都 没有使用db前缀，怎么办？可以这么做，把主库需要的库导出来，不需要的库导出结构即可，在Slave导入这些数据及结构，配置skip-slave- errors=all，这样Master复制过来的binlog，只要发现有库有表结构，就不会报找不到表，就不会阻塞复制，但是 UPDATE/DELETE过来没有数据也会被跳过错误，间接的实现了replicate-do-db。

4. A<C>BC>CC>D结构切换到A<C>B, C<C>D结构出现Slave_lag一直增常如何避免？

这种情况常见与一个双Master集群分离出一套双Master集群，例如从原集群分离一部分库。过快的切换BC>C到C<C>D容易导致主备出现slave_lag，并且一直增长，原因在于A<C>B集群产生的SQL，随同server_id带到了CC>D这个M-S中，当A,B产生的SQL在C,D还没消化完成就CHANGE MASTER为C<C>D时，会导致这写SQL在C,D之间来回传输，因为C,D都认为这个SQL不是自己产生的，因而不销毁，自己执行后写入binlog，于是Slave_Lag就一直增长。
避免的方法很简单，部分写切到C后，先断开BC>C的复制，等一会，看D上已经没有Slave_Lag了，再CHANGE MASTER为C<C>D，这样A,B传过来的SQL都消化完了。

5. 表中存在很多重复数据时，如何删除这些重复数据最快？

在需要给表中某些字段加唯一索引时，而字段中又存在需要重复清理数据的问题，不少DBA都应该遇到过。一般在处理时总是想在数据库中只保留一条，其他的删除，但是这样的SQL写出来总是效率不高，怎么办？其实可以转换思路，把重复的都选出一条出来，存到一张临时表，然后删除原表中所有存在重复的，再把临时表的数据库全部插入原库，这是比较通用并且高效的做法。