作者:杨涛涛
资深数据库专家,专研 MySQL 十余年。善于 MySQL、PostgreSQL、MongoDB 等开源数据库相干的备份复原、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及银行金融企业提供 MySQL 相干技术支持、MySQL 相干课程培训等工作。
本文起源:原创投稿
*爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。
长期以来,在 MySQL 的开发标准里个别都会这么写: 禁止大事务!话题转到 TiDB ,仍然应该是:禁止大事务!
TiDB 因为事务自身分布式个性,加之后盾 RAFT 复制导致的写放大,十分不举荐应用大事务。
TiDB 在4.0 之前的版本对事务要求有些过于粗疏,比方:
- 单个事务蕴含的 SQL 语句不超过5000条
- 单条 KV entry 不超过6MB
- KV entry 的总条数不超过30w
- KV entry 的总大小不超过100MB
下面的几点限度会导致一些 DML 语句写入碰壁,比方上面这三类经典无过滤条件语句:
- insert ... select ... where 1
- update ... where 1
- delete from ... where 1
非常容易呈现事务过大的谬误: ERROR 8004 (HY000): transaction too large, len:300001。 个别有如下办法来躲避这个问题:
- 针对Insert、delete 语句开启无平安保障的dml batch 个性:TiDB_batch_insert、TiDB_batch_delete。
- 分块拆分整条update 语句。
在4.0 之后的版本里,除了单个kv entry 大小仍然限度为最大6MB,其余几个限度全副被勾销。
只须要在配置文件里加上如下选项就可涵盖大部分事务:
performance.txn-total-size-limit: 10737418240(范畴 1G 到10G)
那是不是4.0 版本后就能够随便写事务了? 当然不是! 因为TiDB 的写放大,也会连带导致内存占用成倍增长,对其余业务会有很大影响,所以TiDB对最大事务反对硬性限度其为10G。比方用DM 来同步MySQL数据到TiDB,大事务会导致内存加大,写入提早剧增,进而影响其余的写性能。
所以还是得禁止大事务,拆分为小事务批量解决。
那如何对大事务进行拆分呢? 单从业务方面讲,业务类型不同,对应的拆分办法不同,可能一本书都写不完。 这里我仅仅从数据库角度,细分为从表角度,再进一步到DML语句角度如何拆分。
下面列的这三条经典语句看起来很简略,然而没有过滤条件,如果表索引数十分多,即便表记录数不大,也会是一个大事务,只不过这个事务只蕴含一条DML语句。然而这类语句的拆分实际上要看表构造怎么定义,分为三种:
- 有主键,并且主键间断
- 有主键,主键不间断
- 表无主键(相似第一种)
第一种最容易拆分,依据主键来划分不同的块即可。
举个例子:
表t1 有100W 条记录,除主键外有6个索引,对表t1 进行update :
update ytt.t1 set log_date = current_date() - interval ceil(rand()*1000) day where 1;
在默认主动提交下,这条语句其实就是隐式大事务语句,在外部转换为 :
beginupdate ytt.t1 set log_date = current_date() - interval ceil(rand()*1000) day where 1;commit;
假如表t1主键为自增且间断,那很简略,把这个事务分为10个小事务,每次更新10W条记录,而不是一次性更新100W条。脚本大抵如下:
root@ytt-ubuntu:~/scripts# cat update_table_batch#!/bin/sh# TiDB 拆分更新for i in `seq 1 10`;do min_id=$(((i-1)*100000+1)) max_id=$((i*100000)) queries="update t1 set log_date = date_sub(current_date(), interval ceil(rand()*1000) day) \ where id >=$min_id and id<=$max_id;" mysql --login-path=TiDB_login -D ytt -e "$queries" &done
第二种,针对不间断的自增主键场景。
第一种最为常见,在 TiDB 里强烈不举荐应用间断自增字段来做主键,这会导致潜在的单 region 写热点问题。所以自增主键举荐应用 auto_random 个性来随机写入,防止连续性。
下面脚本里列出的办法就变得不太适宜。那该怎么拆呢? 能够稍加变通,用窗口函数 row_number() 来补模仿主键,更新表改为 t2 ,改写后的脚本大抵如下:
root@ytt-ubuntu:~/scripts# cat update_table_batch#!/bin/sh# TiDB 拆分更新for i in `seq 1 10`;do min_id=$(((i-1)*100000+1)) max_id=$((i*100000)) queries="update t2 a, (select *,row_number() over(order by id) rn from t2) b set a.log_date = \ date_sub(current_date(), interval ceil(rand()*1000) day) \ where a.id = b.id and (b.rn>=$min_id and b.rn<=$max_id);" mysql --login-path=TiDB_login -D ytt -e "$queries" &done
其实以上两种思路曾经蕴含了绝大多数拆分场景。 MySQL 或者 TiDB 对于没有主键的表都默认蕴含一个隐式自增 ID 来辨别行之间关系,所以为了防止在DML层来减少简单的拆分策略,仍然强烈建议应用显式主键!
结语
尽管 TiDB 4.0 版本后,对大事务反对曾经十分好,但这不是能够轻易用大事务的理由,还是要做好表设计提前拆、检索表数据提前拆等拆分策略,能力更好的让数据库服务好业务。