[MYSQL] mysql主从延迟案例(有索引但无主键)

[MYSQL] mysql主从延迟案例(有索引但无主键)

导读

mysql的主从延迟问题还是很常见的, 通常都是没得索引或者数据量太大导致的. 如果有索引,选择性不好,还是会导致主从延迟增大. 本文主要分享一个 表有索引(where使用了的),但无主键 导致主从延迟增大的案例,并附2种解决方法.

模拟

5.7和8.0都可以, 搭建一套主从环境, 参数如下:

# hash_scan有BUG(hash碰撞),所以很多客户使用的table_scan
slave_rows_search_algorithms='TABLE_SCA,IDEX_SCA'

# 开启binlog中sql记录, 方便观察
binlog_rows_query_log_events = O

准备如下表, 即无主键, 但是存在索引, 而索引字段是日期, 每天数据量在10W左右.

create table (
id int,
name varchar(200),
startdate date,
key(startdate)
);

使用Python模拟表中的数据, 本次模拟1000W行数据(大概耗时2分半),分布很均匀(实际生产肯定没这么均匀的)

import datetime
import pymysql
conn = (
	host='127.0.0.1',
	port=08,
	user='root',
	password='12456',
	)
startdate = datetime.datetime.strptime('2024-11-11','%Y-%m-%d')
datediff = (days=1)
idvalue = 0
for i in range(100):
	startdate += datediff
	for j in range(100):
		cursor = ()
		sql = 'insert into  values'
		for k in range(1000):
			idvalue += 1
			sql += f'({idvalue},"ddcw","{startdate.strftime("%Y-%m-%d")}"),'
		_ = (sql[:-1])
		_ = cursor.fetchall()
		connmit()

然后等待主从数据同步(也可以重建主从,就看愿不愿意等了)

本次模拟删除5天的数据量, 即50W行. 主库直接走范围索引, 应该会非常快.

-- 主库删除数据
delete from  where startdate>='2024-12-01' and startdate <= '2024-12-05';

查看从库主从延迟. 理论上是非常慢, 大概率跑不出来(太久). 为了方便观察, 我们可以使用如下脚本来查看延迟.

while true;do sleep 1; echo -n "`date`    ";mysql -h127.0.0.1 -uroot -P08 -p12456 -e 'show slave status\G' 2>/dev/null | grep Seconds_Behind_Master;done

主库跑了2秒半, 但从库一直没有跑出来.

这种情况其实可以不用等了, 即使花半个月跑完了, 这种类型的SQL大概率每过几天就会来一次的.所以延迟基本上就不可能追上来了.

解决方案

加主键然后重建主从. 这是最稳妥的方法, 如果数据量太大的话, 也可以选择只重建某张表. 基础操作了,就不演示了.

注意:直接加自增列有数据不一致的风险(今天刚看到有大佬文章在讲), 加完后重建主从就没那么多问题了.

还有种花里胡哨的方法, 就是设置回放的算法为hash_scan, 这样每次扫描就会快很多. 区区10W行不在话下. 而且最主要的是不需要重建, 只需要重启复制进程即可.(甚至都不需要重启mysql)

-- 停止主从进程 (会回滚到delete之前)
stop slave;
-- 验证数据量
select count(*) from ;
-- 修改参数
set global slave_rows_search_algorithms='index_scan,hash_scan';
-- 启动主从
start slave;

然后我们观察延迟, 发现很快就降下来了(1分钟就跑完了,虽然没得主库快, 但也是能接受的)

如果主从延迟非常大, 也就是从库已经跑了很多数据了, 那么回滚的时候会比较慢. 可参考:我这里延迟接近2小时,回滚耗时.6秒.

说明hash_scan确实快.

那么代价是什么呢? hash存在hash碰撞(虽然概率低, 但目前有好几个客户都遇到了), 也就是数据可能不一致. 所以等待延迟下来后, 我们得再把参数修改回去.

-- 停止主从
stop slave;
-- 修改参数
set global slave_rows_search_algorithms='index_scan,hash_scan';
-- 启动主从
start slave;

最后再校验下数据库的数据是否一致(仅校验无主键的表即可.) 我这里就简单使用checksum table来校验了

数据当然是一致的啦(hash碰撞的概率非常低的, 而且hash_scan是8.0的默认选择)

总结

mysql的表都建议加上主键/唯一键, 实在没得选的, 可以整联合主键, 还是选不上的, 就普通索引吧, 但前提是选择性好一点的. 那种一个key对应10W+的在主库上可能没啥影响, 但从库回放的时候就暴漏出性能问题了. 这种的话, 把date改成datetime. 让数据更分散一点,应该会好很多, 但就是得业务侧配合了.

没事多看些mysql的文档, 起码常见参数还是要搞懂的, 不常见的有个大概映像就行.