Batch on 修改表数据怎么使用可以提高效率

🪐 TiDB 技术问题
,
1

表结构
：CREATE TABLE tbl-game-4d3d2d-bkk01-final (
4d3d2d_id bigint(20) NOT NULL AUTO_INCREMENT,
4d3d2d_inv_no varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT ‘0’,
4d3d2d_member_id int(11) DEFAULT NULL,
4d3d2d_member_username varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
4d3d2d_produk_id int(11) DEFAULT ‘0’,
4d3d2d_produk_code varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
4d3d2d_produk_name varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
4d3d2d_periode_id int(11) DEFAULT NULL,
4d3d2d_periode_name varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
4d3d2d_game varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
4d3d2d_mode varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT ‘diskon’,
4d3d2d_tebakan varchar(5) DEFAULT NULL,
4d3d2d_4d decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_3d decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_2d decimal(14,2) DEFAULT ‘0.00’,
4d3d2d decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_disc_4d decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_disc_3d decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_disc_2d decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_disc decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_bayar decimal(14,2) DEFAULT ‘0.00’,
reg_date timestamp DEFAULT CURRENT_TIMESTAMP,
deleted_at timestamp NULL DEFAULT NULL,
4d3d2d_tebakan_win varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
4d3d2d_4d_prize decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_3d_prize decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_2d_prize decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_prize decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_winloss_status_4D varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT ‘none’,
4d3d2d_winloss_amount_4D decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_winloss_status_3D varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT ‘none’,
4d3d2d_winloss_amount_3D decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_winloss_status_2D varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT ‘none’,
4d3d2d_winloss_amount_2D decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_winloss_status varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT ‘none’,
4d3d2d_winloss_amount decimal(14,2) DEFAULT ‘0.00’,
4d3d2d_check_code varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT ‘0’,
referral_bonus decimal(5,2) DEFAULT NULL,
agent_name varchar(50) DEFAULT NULL,
four_game_mode varchar(40) DEFAULT NULL,
bet_result_json json DEFAULT NULL,
referral_odds decimal(14,2) DEFAULT NULL COMMENT ,
4d3d2d_pre_payout decimal(14,2) DEFAULT NULL COMMENT ,
PRIMARY KEY (4d3d2d_id,4d3d2d_periode_name) /*T![clustered_index] CLUSTERED */,
KEY 4d3d2d_query_idx (4d3d2d_member_id,4d3d2d_produk_name,4d3d2d_periode_name),
KEY 4d3d2d_tebakan_idx (4d3d2d_tebakan),
KEY bet_records_query (4d3d2d_periode_name,4d3d2d_game,4d3d2d_mode,agent_name,4d3d2d_tebakan,deleted_at),
KEY predicttion_idx (4d3d2d_produk_code,4d3d2d_produk_name,4d3d2d_periode_name,4d3d2d_member_id),
KEY reg_date_idx (reg_date),
KEY inv_no (4d3d2d_inv_no,4d3d2d_member_username),
KEY 4d3d2d_winloss_status (4d3d2d_winloss_status),
KEY history_and_invoices (4d3d2d_produk_code(3),4d3d2d_periode_name(4),4d3d2d_game(2),4d3d2d_member_username(10),4d3d2d_inv_no),
KEY history_detail_list_idx (4d3d2d_member_id,4d3d2d_produk_code,4d3d2d_produk_name,4d3d2d_periode_name,4d3d2d_game),
KEY member_transaction_history (4d3d2d_produk_id,4d3d2d_periode_name,4d3d2d_member_id,4d3d2d_game,4d3d2d_inv_no),
KEY final_query_idx (4d3d2d_member_id,4d3d2d_produk_code,4d3d2d_produk_name,4d3d2d_periode_name,4d3d2d_inv_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY LIST COLUMNS(4d3d2d_periode_name)
(PARTITION pGame706 VALUES IN (‘706’),
PARTITION pGame707 VALUES IN (‘707’),
PARTITION pGame708 VALUES IN (‘708’),

sql 语句：
BATCH ON 4d3d2d_id LIMIT 50000
UPDATE ${tbName}
SET
4d3d2d_tebakan_win = #{tebakanWin},
4d3d2d_prize = 0,
4d3d2d_winloss_status = ‘LOSS’,
4d3d2d_winloss_amount = 0,
bet_result_json = ‘’
WHERE
4d3d2d_periode_name = #{period}
AND agent_name in

#{agentName}

问题1：
我现在处理1000w 数据的话 要10分钟. 我这sql效率是合理的么
问题2：我把 4d3d2d_id 改成生成随机数后对sql 有没有影响

2

问题 1：SQL 执行效率

处理 1000 万条数据需要 10 分钟，这个效率在某些情况下可能是合理的，但通常可以进一步优化。以下是一些可能的优化措施：

  1. 索引：
  • 确保在 4d3d2d_periode_name 和 agent_name 上有适当的索引，这可以加速 WHERE 子句的过滤。
  • 如果经常执行类似的批量更新，可以考虑添加组合索引。
  1. 批量处理：
  • 你已经使用了 BATCH ON，这有助于控制每次处理的数据量。可以尝试调整 LIMIT 的大小，以找到最优的批量大小。
  • 确保在每个批次之间有适当的延时，以减轻数据库压力。
  1. 表结构：
  • 检查表中是否有不必要的字段或过多的索引，过多的索引会影响写入速度。
  1. 并行处理：
  • 如果你的数据库支持，可以考虑并行执行多个更新操作，以提高整体效率。
  1. 优化设置：
  • 检查数据库的配置，尤其是缓存和并发设置，以确保它们适合于大数据量操作。

问题 2：随机生成 4d3d2d_id 的影响

将 4d3d2d_id 改为随机数可能会对 SQL 性能产生以下影响：

  1. 主键性能：
  • 如果 4d3d2d_id 是表的主键，并且你将其改为随机数，可能会导致插入性能下降。因为随机插入会导致更多的页分裂，影响数据库的写入效率。
  1. 索引效率：
  • 随机主键可能导致索引变得不连续，从而影响查询性能。如果有基于 4d3d2d_id 的查询，可能会变得更慢。
  1. 数据分布：
  • 随机生成的 ID 可能导致数据在分区中的不均匀分布，这可能影响查询的性能。
3

我的主键是 PRIMARY KEY (4d3d2d_id ,4d3d2d_periode_name )
4d3d2d_id 这个 改成随机数。会导致插入效率降低嘛，我是想要提高 插入的并发的效率。
但是我由有 根据4d3d2d_id 的更新语句

4

应该没什么好办法提高效率了，再快就是按id区间分几个sql并发执行，比一个一个跑快