复制 order 表的效率挑战
作者：TiDB 社区干货传送门
- 2023-01-06 北京
本文字数：19897 字
阅读完需：约 65 分钟
作者： pepezzzz 原文来源：https://tidb.net/blog/d8310c21
测试环境
阿里云集群配置
| | InstanceType | 配置 | 数量 || ———————– | ————– | —————————– | – || PD Server | ecs.c6.large | 2CPU / 4GB 内存 | 3 || TiDB Server | ecs.c6.2xlarge | 8CPU / 16GB 内存 | 2 || TiKV Server | ecs.i3.2xlarge | 8CPU / 64GB 内存 / 1920 GB NVMe | 4 || dumpling/tidb-lightning | ecs.i3.2xlarge | 8CPU / 64GB 内存 / 1920 GB NVMe | 1 |
TiDB 和 dumpling/tidb-lightning 的节点 CPU 数量都是 8，所以本次测试的单节点操作的并行度为 8。
数据环境
记录数
MySQL [test_order]> select count(1) from test_order;+----------+| count(1) |+----------+| 30318907 |+----------+1 row in set (1.26 sec)
复制代码
表结构
共 317 个字段和不含主键 11 个索引。
注：表名、库名、列名做过脱敏处理
| oc_b_order | CREATE TABLE `test_order` ( `id` bigint not null , `zbill_no` varchar(50) not null , `zsource_cod` varchar(60) default null , `zab_d_shop_id` bigint(20) default null , `zab_d_shop_ecod` varchar(50) default null, `zab_d_shop_title` varchar(100) default null, `zab_d_phy_wrhs_id` bigint(20) default '0', `zab_d_phy_wrhs_ecod` varchar(50) default null, `zab_d_phy_wrhs_enam` varchar(100) default null, `zab_d_customer_id` bigint(20) default null, `zab_d_customer_ecod` varchar(20) default null, `zab_d_customer_enam` varchar(50) default null, `zuser_id` bigint(20) default null, `zuser_nick` varchar(200) default null, `zorder_type` int(11) default null, `zorder_stat` int(11) default null, `zoccupy_stat` int(11) default null, `zsuffix_info` text default null, `zunique_key` varchar(200) default null, `zorder_flag` varchar(20) default null, `zproduct_amnt` decimal(18,4) default null, `zproduct_discount_amnt` decimal(18,4) default null, `zorder_discount_amnt` decimal(18,4) default null, `zadjust_amnt` decimal(18,4) default null, `zship_amnt` decimal(18,4) default null, `zservice_amnt` decimal(18,4) default null, `zorder_amnt` decimal(18,4) default null, `zreceived_amnt` decimal(18,4) default null, `zconsign_amnt` decimal(18,4) default null, `zconsign_ship_amnt` decimal(18,4) default null, `zamt_receive` decimal(18,4) default null, `zcod_amnt` decimal(18,4) default null, `zjd_receive_amnt` decimal(18,4) default null, `zjd_settle_amnt` decimal(18,4) default null, `zlogistics_cost` decimal(18,4) default null, `zor_inv` int(11) default null, `zinv_header` varchar(100) default null, `zinv_content` varchar(200) default null, `zor_geninv_notice` int(11) default '0', `zweight` decimal(18,4) default null, `zor_calcweight` int(11) default '0', `zab_d_logistics_id` bigint(20) default null, `zab_d_logistics_ecod` varchar(100) default null, `zab_d_logistics_enam` varchar(50) default null, `zexpresscode` varchar(100) default null, `zorder_date` datetime default null, `zend_tim` datetime default null, `zpay_tim` datetime default null, `zaudit_tim` datetime default null, `zbuyer_email` varchar(100) default null, `zreceiver_nam` varchar(700) default null, `zreceiver_mobile` varchar(500) default null, `zreceiver_phone` varchar(300) default null, `zab_d_region_province_id` bigint(20) default null, `zab_d_region_province_ecod` varchar(20) default null, `zab_d_region_province_enam` varchar(100) default null, `zab_d_region_city_id` bigint(20) default null, `zab_d_region_city_ecod` varchar(20) default null, `zab_d_region_city_enam` varchar(100) default null, `zab_d_region_area_id` bigint(20) default null, `zab_d_region_area_ecod` varchar(20) default null, `zab_d_region_area_enam` varchar(100) default null, `zab_d_region_town_enam` varchar(100) default null, `zreceiver_address` varchar(1000) default null, `zreceiver_zip` varchar(50) default null, `zreceiver_email` varchar(200) default null, `zor_cancel_merge` int(11) default '0', `zor_merge` int(11) default '0', `zor_split` int(11) default '0', `zwms_stat` int(11) default null, `zor_interecept` int(11) default '0', `zor_inreturning` int(11) default '0', `zsalesman_id` bigint(20) default null, `zsalesman_nam` varchar(100) default null, `zall_sku` varchar(500) default null, `zpay_type` int(11) default null, `zbuyer_message` varchar(500) default null, `zorder_source` varchar(200) default null, `zorig_order_id` bigint(20) default null, `zorig_return_order_id` bigint(20) default null, `zor_hasgift` int(11) default '0', `zqty_all` decimal(18,4) default null, `zsku_kind_qty` decimal(18,4) default null, `zsysremark` varchar(600) default null, `zinside_remark` varchar(600) default null, `zseller_memo` varchar(1000) default null, `zmerge_source_cod` text default null, `zplatform` int(11) default null, `zmerge_order_id` bigint(20) default null, `zsplit_order_id` bigint(20) default null, `zscan_tim` datetime default null, `zout_stat` int(11) default null, `ztid` varchar(200) default null, `zorder_tag` varchar(100) default null, `zwms_cancel_stat` int(11) default null, `zreturn_stat` int(11) default '0', `ztb_storecode` varchar(200) default null, `zrefund_confirm_stat` int(11) default null, `zauto_audit_stat` int(11) default null, `zor_jcorder` int(11) default '0', `zdouble11_presale_stat` int(11) default '0', `zdistribution_tim` datetime default null, `zor_invented` int(11) default '0', `zor_combination` int(11) default '0', `zor_out_urgency` int(11) default '0', `zor_shop_commission` int(11) default '0', `zor_has_ticket` int(11) default '0', `zversion` bigint(20) default null, `zad_org_id` bigint(20) default '27', `zad_client_id` bigint(20) default '37', `zownerid` bigint(20) default null, `zownerenam` varchar(50) default null, `zownernam` varchar(50) default null, `zcreationdate` datetime default current_timstamp, `zmodifierid` bigint(20) default null, `zmodifierenam` varchar(50) default null, `zmodifiernam` varchar(50) default null, `zmodifieddate` datetime not null default current_timstamp on update current_timstamp, `zisactive` char(1) default 'y', `zalipay_no` varchar(50) default null, `zbuyer_alipay_no` varchar(50) default null, `zab_d_shop_seller_nick` varchar(100) default null, `zor_force` bigint(20) default '2', `zor_overfive` bigint(20) default '0', `zor_exchange_no_in` bigint(20) default '0', `zor_multi_pack` bigint(20) default '0', `zmakeup_fail_num` bigint(20) default '0', `zlock_stat` int(11) default null, `zpos_bill_id` bigint(20) default null, `zamt_plat_discount` decimal(18,4) default null, `zforce_send_fail_reason` varchar(200) default null, `zprice_label` varchar(200) default null, `zstatus_pay_step` varchar(200) default null, `zab_d_label_enam` varchar(50) default null, `zab_d_label_content` varchar(500) default null, `zinv_stat` int(11) default null, `ztest_inv_notice_id` mediumtext default null, `zscalping_type` int(11) default null, `zpresale_deposit_tim` datetime default null, `zab_d_label_id` bigint(20) default null, `zsg_b_out_bill_no` varchar(20) default null, `zout_type` int(1) default null, `zcainiao_wh_stat` varchar(50) default null, `zpay_stat` int(10) default null, `zpltfm_stat` varchar(30) default null, `zdlvytime` datetime default null, `zexpected_dlvytime` datetime default null, `zdlvy_method` varchar(50) default null, `zlabel_tim` varchar(100) default null, `zrefund_stat` int(10) default null, `zcancel_stat` varchar(50) default null, `zred_enveloper` decimal(10,0) default null, `zinternal_memo` varchar(50) default null, `zrefund_fee` decimal(18,4) default null, `zorder_weight` decimal(18,4) default null, `zorder_gross` decimal(18,4) default null, `zsingle_quantity` decimal(18,4) default null, `zsingle_number` decimal(18,4) default null, `zwms_bill_no` varchar(100) default null, `zreissue_note` varchar(50) default null, `ztarget_cod` varchar(50) default null, `zaudit_failed_type` int(10) default '0', `zor_o2o_order` int(10) default null, `zpresell_type` int(10) default null, `zpresell_way` int(10) default null, `zcopy_reason` varchar(64) default null, `zlive_platform` varchar(5) default null, `zlive_flag` int(11) default null, `zanchor_id` varchar(30) default null, `zanchor_nam` varchar(64) default null, `zor_out_stock_split` int(10) default null, `zor_dlvy_urgent` tinyint(1) default '0', `zcopy_num` int(11) default '0', `zor_lose_copy_order` int(11) default '0', `zor_copy_order` tinyint(1) default '0', `zor_reset_ship` tinyint(1) default '0', `zor_modified_order` tinyint(1) default '0', `zvip_workflow_sn` varchar(50) default null, `zor_vip_update_wrhs` int(2) default null, `zdispute_id` bigint(20) default null, `zhold_release_tim` datetime default null, `zor_history` char(1) default 'n', `zqty_split` bigint(20) default '0', `zor_prom_order` tinyint(1) default '0', `zor_real_lackstock` tinyint(1) default '0', `zor_extra` int(11) default '0', `zor_same_city_purchase` int(11) default '0', `zstore_dlvy_stat` int(11) default null, `zdlvy_store_id` bigint(20) default null, `zdlvy_store_cod` varchar(100) default null, `zdlvy_store_nam` varchar(150) default null, `zoffline_order_cod` varchar(100) default null, `zpos_orderno` varchar(10) default null, `zor_spilt_sku_style` int(11) default '0', `zmerge_error_num` int(11) default null, `zto_sap_stat` tinyint(1) default '0', `zsplit_stat` int(11) default '0', `zsplit_reason` int(11) default '0', `zr_bigint01` bigint(20) default null, `zr_bigint02` bigint(20) default null, `zr_bigint03` bigint(20) default null, `zr_bigint04` bigint(20) default null, `zr_bigint05` bigint(20) default '0', `zr_decimal01` decimal(18,4) default null, `zr_decimal02` decimal(18,4) default null, `zr_decimal03` decimal(18,4) default null, `zr_decimal04` decimal(18,4) default null, `zr_decimal05` decimal(18,4) default null, `zr_varchar01` varchar(20) default null, `zr_varchar02` varchar(20) default null, `zr_varchar03` varchar(50) default null, `zr_varchar04` varchar(50) default null, `zaudit_failed_reason` varchar(100) default null, `zreverse_audit_type` varchar(100) default null, `zr_varchar05` varchar(50) default null, `zwms_cancel_number` int(10) unsigned default '0', `zr_audit_tag` varchar(20) default null, `zor_to_sap` int(2) default '1', `zorder_ecypt_cod` varchar(50) default null, `zac_f_manage_id` bigint(20) default null, `zac_f_manage_ecod` varchar(50) default null, `zac_f_manage_enam` varchar(50) default null, `zcooperate_id` bigint(20) default null, `zcooperate_ecod` varchar(50) default null, `zcooperate_enam` varchar(50) default null, `zlive_events` bigint(20) default null, `zorder_discount` decimal(18,4) default null, `zjitx_requires_dlvy_wrhs_id` bigint(20) default null, `zjitx_requires_dlvy_wrhs_cod` varchar(255) default null, `zjitx_requires_dlvy_wrhs_nam` varchar(255) default null, `zsuggest_prepackage_stat` char(1) default null, `zactual_prepackage_stat` char(1) default null, `zsuggest_presink_stat` char(1) default null, `zactual_presink_stat` char(1) default null, `zsplit_reason_id` bigint(20) default null, `zcustom_reason` varchar(50) default null, `zmerged_cod` varchar(255) default null, `zmerged_sn` varchar(255) default null, `zjitx_requires_merge` char(1) default null, `zjitx_merged_dlvy_sn` varchar(255) default null, `zor_forbidden_dlvy` int(2) default null, `zadvance_type` varchar(50) default null, `zor_self_pick_up` char(1) default '0', `zor_detention` int(11) default '0', `zbasic_price_used` decimal(10,2) default null, `zexpand_price_used` decimal(10,2) default null, `zto_drp_stat` char(1) default '0', `zto_drp_count` int(2) default '0', `zto_drp_failed_reason` varchar(255) default null, `zoaid` varchar(200) default null, `zsg_b_out_bill_id` bigint(20) default null, `zsto_out_bill_no` varchar(50) default null, `zpltfm_dlvy_tim` datetime default null, `zwrhs_dlvy_tim` datetime default null, `zthird_party_fail_stat` char(2) default null, `zhold_reason` varchar(100) default null, `zdetention_reason` varchar(50) default null, `zab_e_custom_label_id` varchar(100) default null, `zab_e_custom_label_enam` varchar(300) default null, `zdetention_date` datetime default null, `zdetention_release_date` datetime default null, `zstock_occupy_date` datetime default null, `zoccupy_success_date` datetime default null, `zhold_date` datetime default null, `zhold_release_date` datetime default null, `zaudit_type` varchar(50) default null, `zaudit_success_date` datetime default null, `zcancel_date` datetime default null, `zexamine_order_date` datetime default null, `zuse_coupon_no` varchar(50) default null, `zhold_release_reason` varchar(50) default null, `zhold_release_nam` varchar(50) default null, `zor_notice_dlvy` char(1) default '0', `zdlvy_in_stat` char(1) default '0', `zor_store_dlvy` int(1) default null, `zgw_vip_cod` varchar(50) default null, `zgw_vip_mobile` varchar(30) default null, `zgw_source_cod` varchar(50) default null, `zgw_source_group` varchar(50) default null, `zestimate_con_tim` datetime default null, `zout_wms_receive_tim` datetime default null, `zlogistics_stat` varchar(50) default null, `zonroad_date` datetime default null, `zonroad_transfer_date` datetime default null, `zarrived_date` datetime default null, `zpltfm_province` varchar(20) default null, `zpltfm_city` varchar(20) default null, `zpltfm_area` varchar(20) default null, `zsap_arrived_date` date default null, `zbusi_type` varchar(50) default null, `zbusi_type_id` bigint(20) default null, `zbusi_type_nam` varchar(50) default null, `zbusi_type_cod` varchar(50) default null, `zorder_source_pltfm_ecod` varchar(200) default null, `zsource_bill_no` varchar(20) default null, `zto_naika_stat` int(10) default '0', `zwhether_need_receipt` char(1) default null, `zreceipt_date` datetime default null, `zsales_organization_id` bigint(20) default null, `zsales_department_id` bigint(20) default null, `zcost_center_id` bigint(20) default null, `zfactory` varchar(50) default null, `zexpiry_date_type` int(5) default null, `zexpiry_date_range` varchar(200) default null, `zor_equal_exchange` int(5) default '0', `zor_out_stock` int(5) default null, `zor_express` char(1) default 'n', `zcurrent_cycle_number` int(11) default null, `zor_encrypted` int(10) default null, `zsales_department_nam` varchar(100) default null, `zaudit_id` bigint(20) default null, `zor_occupy_stock_fail` int(2) default null, `zaudit_nam` varchar(20) default null, `zor_manual_addr` int(11) default '0', `zor_exception` varchar(10) default null, `zexcpt_type` varchar(10) default null, `zexcpt_explain` varchar(100) default null, PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */, UNIQUE KEY `zgsi_test_order_merge_encrypt_cod` (`zorder_ecypt_cod`,`zid`,`zab_d_shop_id`,`zorder_stat`,`zorder_date`,`zor_interecept`,`zor_inreturning`,`zpay_type`,`zplatform`,`zor_same_city_purchase`), KEY `zi_test_order_03` (`ztid`), KEY `zi_test_order_04` (`zexpresscode`), KEY `zi_test_order_05` (`zorder_stat`), KEY `zi_test_order_06` (`zuser_nick`), KEY `zi_test_order_07` (`zab_d_phy_wrhs_id`), KEY `zi_test_order_08` (`zr_varchar04`), KEY `zbill_no` (`zbill_no`), KEY `zindex_orgi_return_id` (`zorig_return_order_id`), KEY `zi_test_order_02` (`zsource_cod`,`zid`), KEY `zidx1` (`zscan_tim`,`zcreationdate`,`zab_d_shop_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /*T! SHARD_ROW_ID_BITS=2 */ |
复制代码
方法一：非事务 DML 语句
非事务 DML 语句介绍
非事务 DML 语句是将一个普通 DML 语句拆成多个 SQL 语句（即多个 batch）执行，以牺牲事务的原子性和隔离性为代价，增强批量数据处理场景下的性能和易用性。v6.1 支持 delete 语句的 batch 语法，v6.4 支持 update 和 insert select 语句的 batch 语法。
使用 batch dry run 语法可以看到第一个子任务和最后一个子任务的语句。
MySQL [test_order]> batch on id limit 1000 dry run insert into test_order_target select * from test_order;+-------------------------------------------------------------------------------------------------------------------------------------+| split statement examples |+-------------------------------------------------------------------------------------------------------------------------------------+| INSERT INTO `test_order`.`test_order_target` SELECT * FROM `test_order`.`test_order` WHERE `id` BETWEEN 5 AND 100902 || INSERT INTO `test_order`.`test_order_target` SELECT * FROM `test_order`.`test_order` WHERE `id` BETWEEN 119960228 AND 119961136 |+-------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (9.78 sec)
复制代码
单次 1000 行插入原表
MySQL [test_order]> batch on id limit 1000 insert into test_order_target select * from test_order;
复制代码
从 CLUSTER_PROCESSLIST 表和 tidb.log 日志上可以看到执行进度。
CLUSTER_PROCESSLIST 的 query 列如下：
/* job 20579⁄30319 */ INSERT INTO `test_order`.`test_order_target` SELECT * FROM `test_order`.`test_order` WHERE `id` BETWEEN 110190802 AND 110191803
tidb.log 日志的第一个和最后一个子任务如下：
[2022/12/30 09:45:53.436 +08:00] [INFO] [nontransactional.go:423] [“start a Non-transactional DML”] [conn=3074535777447707195] [job=”job id: 1, estimated size: 1000, sql: INSERT INTO `test_order`.`test_order_target` SELECT * FROM `test_order`.`test_order` WHERE `id` BETWEEN 5 AND 100902”] [totalJobCount=30319]
[2022/12/30 12:05:55.725 +08:00] [INFO] [nontransactional.go:445] [“Non-transactional DML SQL finished successfully”] [conn=3074535777447707195] [jobID=30319] [jobSize=907] [dmlSQL=“INSERT INTO `test_order`.`test_order_target` SELECT * FROM `test_order`.`test_order` WHERE `id` BETWEEN 119960228 AND 119961136”]
累计执行时间是 2 小时 20 分钟。
插入原表期间 tidb-server 的 CPU 监控如下：
delete 语句的 batch 语法是单会话串行执行，保持在 100%。
插入原表期间 tikv-server 的 CPU 监控如下：
空间占用监控如下：
空间占用约 85.6 GB（导入的平台数据文件约 63 GB )
单次 200 行插入原表
调整单次复制行数为 200，查看复制的效率变化。
MySQL [test_order]> batch on id limit 200 insert into test_order_target2 select * from test_order;+----------------+---------------+| number of jobs | job status |+----------------+---------------+| 151595 | all succeeded |+----------------+---------------+1 row in set (2 hours 40 min 10.55 sec)
MySQL [test_order]> select count(1) from test_order_target2;+----------+| count(1) |+----------+| 30318907 |+----------+1 row in set (2.01 sec)
复制代码
单次 200 行插入无索引表
删除目标表的索引，查看复制的效率变化。
-- 提前准备无索引表 test_order_target3 MySQL [test_order]> create table test_order_target3 like test_order;MySQL [test_order]> alter table test_order_target3 drop index `gsi_test_order_target3_merge_encrypt_code` ;...MySQL [test_order]> alter table test_order_target3 drop index `idx1` ;MySQL [test_order]> batch on id limit 200 insert into test_order_target3 select * from test_order;+----------------+---------------+| number of jobs | job status |+----------------+---------------+| 151595 | all succeeded |+----------------+---------------+1 row in set (1 hour 53 min 23.39 sec)
复制代码
非事务 DML 语句效率
| | 时间 | TiDB CPU | TiKV CPU | 备注 || ————- | ——————— | ———- | ——– | – || 单次 1000 行插入原表 | 2 hours 20 min | 会话节点：100 % | < 300% | || 单次 200 行插入原表 | 2 hours 40 min 10 sec | 会话节点：100 % | < 300% | || 单次 200 行插入无索引 | 1 hour 53 min 23 sec | | | |
方法二：非事务 DML 语句后再建索引
索引加速功能
TiDB v6.3.0 版本增加索引加速功能，并在 v6.5.0 版本进一步完成性能优化。新特性主要提升创建索引过程中在 write reorg 阶段的速度，实现机制是多次取表数据在本地构造索引数据完成排序后，通过 ingest 的方式保存到 TiKV 的 RocksDB 上，取代原有需要经过事务层处理的写入方式，新特性加索引的速度可以提升 10 倍左右。
MySQL [(none)]> show variables like "%tidb_ddl_enable_fast_reorg%";+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| tidb_ddl_enable_fast_reorg | ON |+----------------------------+-------+1 row in set (0.00 sec)-- 核对 tidb_ddl_enable_fast_reorg 变量。v6.5.0 版本默认打开，低版本升级需要手工打开。MySQL [(none)]> show config where name like "%temp-dir%";+------+--------------------+----------+---------------------------------+| Type | Instance | Name | Value |+------+--------------------+----------+---------------------------------+| tidb | 192.168.48.32:4000 | temp-dir | /tidb-deploy/tidb-4000/temp-dir || tidb | 192.168.48.31:4000 | temp-dir | /tidb-deploy/tidb-4000/temp-dir |+------+--------------------+----------+---------------------------------+-- 需要提前使用 tiup cluster edit-config 配置 temp-dir 目录，用于索引数据 ingest 前的数据准备。
复制代码
要验证正在进行或者已经完成的 ADD INDEX 操作是原有的事务方式或索引加速功能，可以执行 ADMIN SHOW DDL JOBS 语句查看 JOB_TYPE 一列中是 txn 或 ingest 关键字。
输出举例如下：
MySQL [test_order]> admin show ddl jobs;+--------+--------------+--------------------+---------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+| JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE |+--------+--------------+--------------------+---------------------+--------------+-----------+----------+-----------+---------------------+---------------------+---------------------+--------+| 5503 | test_order | test_order_target3 | add index /* txn */ | public | 70 | 5388 | 30318907 | 2023-01-01 21:10:45 | 2023-01-01 21:10:45 | 2023-01-01 21:26:14 | synced |...| 5461 | test_order | test_order_target3 | add index /*ingest*/| public | 70 | 5388 | 30318907 | 2022-12-31 09:51:19 | 2022-12-31 09:51:19 | 2022-12-31 09:53:30 | synced |
复制代码
从 START_TIME 和 END_TIME 两个时间相减也可以看出相同的索引语句（第一个 7 列的组合唯一索引）的效率对比：
使用默认的索引参数建索引
基于索引加速功能，将索引创建阶段移到数据导入后，利用并行功能进行提速。
索引并行参数如下：
MySQL [(none)]> show variables like "%tidb_ddl_reorg_%";tidb_ddl_reorg_batch_size 256tidb_ddl_reorg_priority PRIORITY_LOWtidb_ddl_reorg_worker_cnt 4
复制代码
索引脚本如下：
# cat addindex.sql alter table test_order_target3 add UNIQUE index `gsi_test_order_target3_merge_encrypt_code` (`zorder_encryption_code`,`id`,`zab_d_shop_id`,`zorder_status`,`zorder_date`,`zor_interecept`,`zor_inreturning`,`zpay_type`,`zplatform`,`zor_same_city_purchase`);alter table test_order_target3 add index `i_test_order_target3_03` (`ztid`);alter table test_order_target3 add index `i_test_order_target3_04` (`zexpresscode`);alter table test_order_target3 add index `i_test_order_target3_05` (`zorder_status`);alter table test_order_target3 add index `i_test_order_target3_06` (`zuser_nick`);alter table test_order_target3 add index `i_test_order_target3_07` (`zab_d_phy_warehouse_id`);alter table test_order_target3 add index `i_test_order_target3_08` (`zr_varchar04`);alter table test_order_target3 add index `bill_no` (`zbill_no`);alter table test_order_target3 add index `index_orgi_return_id` (`zorig_return_order_id`);alter table test_order_target3 add index `i_test_order_target3_02` (`zsource_code`,`id`);alter table test_order_target3 add index `idx1` (`zscan_time`,`zcreationdate`,`zab_d_shop_id`);
复制代码
索引效率如下：
MySQL [test_order]> source addindex.sql;Query OK, 0 rows affected (3 min 10.56 sec)
Query OK, 0 rows affected (52.99 sec)
Query OK, 0 rows affected (47.40 sec)
Query OK, 0 rows affected (47.31 sec)
Query OK, 0 rows affected (1 min 0.54 sec)
Query OK, 0 rows affected (49.29 sec)
Query OK, 0 rows affected (49.81 sec)
Query OK, 0 rows affected (47.74 sec)
Query OK, 0 rows affected (49.61 sec)
Query OK, 0 rows affected (1 min 1.60 sec)
Query OK, 0 rows affected (50.34 sec)
复制代码
创建索引期间 tidb-server 的 CPU 监控如下：
会话节点的 CPU 最高是 400%，与 tidb_ddl_reorg_worker_cnt:4 能大致对应。
创建索引期间 tikv-server 的 CPU 监控如下
使用优化的索引参数建索引
MySQL [test_order]> set global tidb_ddl_reorg_batch_size=2048;MySQL [test_order]> set global tidb_ddl_reorg_worker_cnt =8;MySQL [test_order]> show variables like "%tidb_ddl_reorg_%";+---------------------------+--------------+| Variable_name | Value |+---------------------------+--------------+| tidb_ddl_reorg_batch_size | 2048 || tidb_ddl_reorg_priority | PRIORITY_LOW || tidb_ddl_reorg_worker_cnt | 8 |+---------------------------+--------------+3 rows in set (0.00 sec)
复制代码
索引效率如下：
MySQL [test_order]> source addindex.sql;Query OK, 0 rows affected (5 min 15.26 sec)
Query OK, 0 rows affected (45.11 sec)
Query OK, 0 rows affected (39.40 sec)
Query OK, 0 rows affected (47.54 sec)
Query OK, 0 rows affected (47.30 sec)
Query OK, 0 rows affected (41.06 sec)
Query OK, 0 rows affected (41.89 sec)
Query OK, 0 rows affected (38.81 sec)
Query OK, 0 rows affected (41.10 sec)
Query OK, 0 rows affected (51.20 sec)
Query OK, 0 rows affected (39.34 sec)
复制代码
第一个索引的创建过程中，出现 TiKV 写入的多次尝试，需要修改 region scatter 策略重新尝试。
[2022/12/30 23:36:04.826 +08:00] [WARN] [localhelper.go:463] [“wait for scatter region encountered error, will retry again”] [region=“{ID=67262,startKey=74800…00F8,endKey=74800…00F8,epoch=\“conf_ver:359 version:3151 \”,peers=\“id:67263 store_id:7 ,id:67264 store_id:2 ,id:67265 store_id:1 \”}“] [error=“rpc error: code = Unknown desc = region 67262 is not fully replicated”]
修改 tidb_scatter_region 变量。
MySQL [test_order]> set global tidb_scatter_region=1;MySQL [test_order]> show variables like "%scatter%";+---------------------+-------+| Variable_name | Value |+---------------------+-------+| tidb_scatter_region | ON |+---------------------+-------+1 row in set (0.00 sec)
复制代码
重新创建的索引效率如下：
MySQL [test_order]> source addindex.sqlQuery OK, 0 rows affected (2 min 11.91 sec)
Query OK, 0 rows affected (42.54 sec)
Query OK, 0 rows affected (37.19 sec)
Query OK, 0 rows affected (38.36 sec)
Query OK, 0 rows affected (53.19 sec)
Query OK, 0 rows affected (38.97 sec)
Query OK, 0 rows affected (41.03 sec)
Query OK, 0 rows affected (37.02 sec)
Query OK, 0 rows affected (38.45 sec)
Query OK, 0 rows affected (49.79 sec)
Query OK, 0 rows affected (40.30 sec)
复制代码
创建索引期间 tidb-server 的 CPU 监控如下：
会话节点的 CPU 最高是 689%，与 tidb_ddl_reorg_worker_cnt:8 能大致对应。
创建索引期间 tikv-server 的 CPU 监控如下：
复制数据后再建索引效率记录
方法三：导出后再导入
数据导出
使用 dumpling 工具以 8 线程备份单表数据，导出的数据文件 63 GB。
# ./dumpling -u root -P 4000 -h 192.168.48.23 --filter "test_order.test_order" --filetype sql -t 8 -o /data1/order-data/ -r 1000000 -F256MiB...[2022/12/31 10:10:26.697 +08:00] [INFO] [versions.go:54] ["Welcome to dumpling"] ["Release Version"=v6.5.0] ["Git Commit Hash"=706c3fa3c526cdba5b3e9f066b1a568fb96c56e3] ["Git Branch"=heads/refs/tags/v6.5.0] ["Build timestamp"="2022-12-27 03:43:05"] ["Go Version"="go version go1.19.3 linux/amd64"]...[2022/12/31 10:10:26.719 +08:00] [INFO] [dump.go:131] ["begin to run Dump"] [conf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false,\"role-arn\":\"\",\"external-id\":\"\",\"object-lock-enabled\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"azblob\":{\"endpoint\":\"\",\"account-name\":\"\",\"account-key\":\"\",\"access-tier\":\"\"},\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":true,\"NoSequences\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"192.168.48.23\",\"Port\":4000,\"Threads\":8,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/data1/order-data/\",\"StatusAddr\":\":8281\",\"Snapshot\":\"438423421374169090\",\"Consistency\":\"snapshot\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"ServerType\":3,\"ServerVersion\":\"6.5.0\",\"HasTiKV\":true},\"Rows\":1000000,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":268435456,\"StatementSize\":1000000,\"SessionParams\":{\"tidb_snapshot\":\"438423421374169090\"},\"Tables\":{},\"CollationCompatible\":\"loose\"}"]...[2022/12/31 10:15:27.360 +08:00] [INFO] [collector.go:255] ["backup success summary"] [total-ranges=701] [ranges-succeed=701] [ranges-failed=0] [total-take=5m0.498002713s] [total-kv-size=67.46GB] [average-speed=224.5MB/s] [total-rows=30318907]...# du -sh /data1/order-data/63G /data1/order-data/
复制代码
导出期间 tidb-server 的 CPU 监控如下：
导出期间 tikv-server 的 CPU 监控如下：
数据准备
批量重命名导出文件，重命名的脚本如下：
[order-data]# cat rename_batch.sh #!/bin/bashfor file in `ls dbname.tablename*`doecho $filenewFile=`echo $file | sed 's/dbname.tablename/new_dbname.new_tablename/g'`echo $newFile#mv $file $newFiledone
复制代码
文件改名前后的对比输出如下：
test_order.test_order.0000006780000.sqltest_order_target.test_order_target2.0000006780000.sqltest_order.test_order-schema.sqltest_order_target.test_order_target2-schema.sql
复制代码
确认文件名符合要求后，使用 rename_batch.sh 生成真正改名的 mv_batch.sh 并执行。
[order-data]# cat rename_batch.sh #!/bin/bashfor file in `ls dbname.tablename*`do#echo $filenewFile=`echo $file | sed 's/dbname.tablename/new_dbname.new_tablename/g'`#echo $newFilemv $file $newFiledone[order-data]# sh rename_batch.sh > mv_batch.sh[order-data]# sh mv_batch.sh
复制代码
数据导入
使用 tidb-lightning 工具以 8 并行工作线程以 local 方式导入单表 63 GB 数据文件，包含所有索引。
创建 toml 配置文件如下：
[lightning]level = "info"file = "tidb-lightning-order.log"check-requirements = trueregion-concurrency = 8[tikv-importer]backend = "local"on-duplicate = "error"sorted-kv-dir = "/data1/sorted/order/"[checkpoint]enable = trueschema = "tidb_lightning_ckp_order"driver = "file"[mydumper]data-source-dir = "/data1/order-data/"[tidb]host = "192.168.48.31"port = 4000user = "root"password = ""pd-addr = "192.168.48.25:2379"status-port = 10080[post-restore]checksum = trueanalyze = false
复制代码
执行 tidb-lightning
#./tidb-lightning --config light-order.toml...[2022/12/31 19:10:25.432 +08:00] [INFO] [lightning.go:382] [cfg] [cfg="{\"id\":1672485025432262754,\"lightning\":{\"table-concurrency\":6,\"index-concurrency\":2,\"region-concurrency\":8,\"io-concurrency\":5,\"check-requirements\":true,\"meta-schema-name\":\"lightning_metadata\",\"max-error\":{\"type\":0},\"task-info-schema-name\":\"lightning_task_info\"},\"tidb\":{\"host\":\"192.168.48.31\",\"port\":4000,\"user\":\"root\",\"status-port\":10080,\"pd-addr\":\"192.168.48.25:2379\",\"sql-mode\":\"ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER\",\"tls\":\"false\",\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"max-allowed-packet\":67108864,\"distsql-scan-concurrency\":15,\"build-stats-concurrency\":20,\"index-serial-scan-concurrency\":20,\"checksum-table-concurrency\":2,\"vars\":null},\"checkpoint\":{\"schema\":\"tidb_lightning_ckp_order\",\"driver\":\"file\",\"enable\":true,\"keep-after-success\":\"remove\"},\"mydumper\":{\"read-block-size\":65536,\"batch-size\":0,\"batch-import-ratio\":0,\"source-id\":\"\",\"data-source-dir\":\"file:///data1/order-data\",\"character-set\":\"auto\",\"csv\":{\"separator\":\",\",\"delimiter\":\"\\\"\",\"terminator\":\"\",\"null\":\"\\\\N\",\"header\":true,\"trim-last-separator\":false,\"not-null\":false,\"backslash-escape\":true},\"max-region-size\":268435456,\"filter\":[\"*.*\",\"!mysql.*\",\"!sys.*\",\"!INFORMATION_SCHEMA.*\",\"!PERFORMANCE_SCHEMA.*\",\"!METRICS_SCHEMA.*\",\"!INSPECTION_SCHEMA.*\"],\"files\":null,\"no-schema\":false,\"case-sensitive\":false,\"strict-format\":false,\"default-file-rules\":true,\"ignore-data-columns\":null,\"data-character-set\":\"binary\",\"data-invalid-char-replace\":\"�\"},\"tikv-importer\":{\"addr\":\"\",\"backend\":\"local\",\"on-duplicate\":\"error\",\"max-kv-pairs\":4096,\"send-kv-pairs\":32768,\"region-split-size\":0,\"region-split-keys\":0,\"sorted-kv-dir\":\"/data1/sorted/order/\",\"disk-quota\":9223372036854775807,\"range-concurrency\":16,\"duplicate-resolution\":\"none\",\"incremental-import\":false,\"engine-mem-cache-size\":536870912,\"local-writer-mem-cache-size\":134217728,\"store-write-bwlimit\":0},\"post-restore\":{\"checksum\":\"required\",\"analyze\":\"off\",\"level-1-compact\":false,\"post-process-at-last\":true,\"compact\":false},\"cron\":{\"switch-mode\":\"5m0s\",\"log-progress\":\"5m0s\",\"check-disk-quota\":\"1m0s\"},\"routes\":null,\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"black-white-list\":{\"do-tables\":null,\"do-dbs\":null,\"ignore-tables\":null,\"ignore-dbs\":null}}"].....[2022/12/31 19:30:16.228 +08:00] [INFO] [local.go:1628] ["import engine success"] [uuid=ec8f0c5c-418c-54b2-9184-b13c5a204994] [size=30242144079] [kvs=363826884] [importedSize=30242144079] [importedCount=363826884][2022/12/31 19:30:16.230 +08:00] [INFO] [backend.go:479] ["import completed"] [engineTag=`test_order`.`test_order`:-1] [engineUUID=ec8f0c5c-418c-54b2-9184-b13c5a204994] [retryCnt=0] [takeTime=2m2.580363441s] [][2022/12/31 19:30:16.233 +08:00] [INFO] [backend.go:491] ["cleanup start"] [engineTag=`test_order`.`test_order`:-1] [engineUUID=ec8f0c5c-418c-54b2-9184-b13c5a204994][2022/12/31 19:30:16.867 +08:00] [INFO] [backend.go:493] ["cleanup completed"] [engineTag=`test_order`.`test_order`:-1] [engineUUID=ec8f0c5c-418c-54b2-9184-b13c5a204994] [takeTime=634.229136ms] [][2022/12/31 19:30:16.867 +08:00] [INFO] [table_restore.go:975] ["import and cleanup engine completed"] [engineTag=`test_order`.`test_order`:-1] [engineUUID=ec8f0c5c-418c-54b2-9184-b13c5a204994] [takeTime=2m3.217168095s] [][2022/12/31 19:30:16.870 +08:00] [INFO] [tidb.go:388] ["alter table auto_increment start"] [table=`test_order`.`test_order`] [auto_increment=211255163][2022/12/31 19:30:17.389 +08:00] [INFO] [tidb.go:390] ["alter table auto_increment completed"] [table=`test_order`.`test_order`] [auto_increment=211255163] [takeTime=519.521074ms] [][2022/12/31 19:30:17.392 +08:00] [INFO] [restore.go:1563] ["restore table completed"] [table=`test_order`.`test_order`] [takeTime=19m49.495832828s] [][2022/12/31 19:30:17.392 +08:00] [INFO] [restore.go:1309] ["cancel periodic actions"] [do=true][2022/12/31 19:30:17.392 +08:00] [INFO] [restore.go:1869] ["switch import mode"] [mode=Normal][2022/12/31 19:30:17.465 +08:00] [INFO] [table_restore.go:750] ["local checksum"] [table=`test_order`.`test_order`] [checksum="{cksum=13119691018140391503,size=98489284541,kvs=394145791}"][2022/12/31 19:30:17.465 +08:00] [INFO] [checksum.go:159] ["remote checksum start"] [table=test_order][2022/12/31 19:30:27.897 +08:00] [INFO] [restore.go:1284] [progress] [total=100.0%] [tables="1/1 (100.0%)"] [chunks="678/678 (100.0%)"] [engines="2/2 (100.0%)"] [restore-bytes=62.83GiB/62.83GiB] [import-bytes=91.73GiB/91.73GiB(estimated)] ["encode speed(MiB/s)"=53.61944249195604] [state=post-processing] [][2022/12/31 19:30:49.645 +08:00] [INFO] [checksum.go:162] ["remote checksum completed"] [table=test_order] [takeTime=32.179808752s] [][2022/12/31 19:30:49.645 +08:00] [INFO] [table_restore.go:1002] ["checksum pass"] [table=`test_order`.`test_order`] [local="{cksum=13119691018140391503,size=98489284541,kvs=394145791}"][2022/12/31 19:30:49.648 +08:00] [INFO] [table_restore.go:843] ["skip analyze"] [table=`test_order`.`test_order`][2022/12/31 19:30:49.652 +08:00] [INFO] [restore.go:1532] ["restore all tables data completed"] [takeTime=20m21.76298669s] [][2022/12/31 19:30:49.652 +08:00] [INFO] [restore.go:1535] ["cleanup task metas"][2022/12/31 19:30:49.653 +08:00] [INFO] [restore.go:1829] ["skip full compaction"][2022/12/31 19:30:49.653 +08:00] [INFO] [restore.go:2018] ["clean checkpoints start"] [keepAfterSuccess=remove] [taskID=1672485025432262754][2022/12/31 19:30:49.653 +08:00] [INFO] [restore.go:1171] ["everything imported, stopping periodic actions"][2022/12/31 19:30:49.653 +08:00] [INFO] [restore.go:2026] ["clean checkpoints completed"] [keepAfterSuccess=remove] [taskID=1672485025432262754] [takeTime=675.614µs] [][2022/12/31 19:30:49.653 +08:00] [INFO] [restore.go:476] ["the whole procedure completed"] [takeTime=20m24.181465093s] [][2022/12/31 19:30:49.707 +08:00] [INFO] [checksum.go:459] ["service safe point keeper exited"][2022/12/31 19:30:49.707 +08:00] [INFO] [main.go:106] ["tidb lightning exit"] [finished=true]
复制代码
导入期间 tidb-server 不工作 CPU 监控略。
导入期间 tikv-server 的 CPU 监控如下：
空间占用监控如下：
空间占用约 51.8 GB。（batch insert select 的方式空间占用约 85.6 GB)
导出数据并导入的效率记录
方法四：手工分段并行插入
16 并发插入原表
可以通过手工对主键进行分段，再分任务对节点进行多会话的并发插入。
# cat SQL1select concat('insert into test_order.test_order_target4 select * FROM test_order where id between ', min(t.id) ,' and ', max(t.id) , ';') from ( select id,row_number () over (order by id) as row_num from test_order.test_order ) t group by floor(t.row_num / 1000) order by min(t.id);// 根据 t.id 的顺序，每 1000 行做成一个 insert into test_order.test_order_target4 select * FROM test_order where id between ... and ... 的分段。
# export SELECT_SQL=`cat SQL1`# mysql -h 192.168.48.32 -P 4000 -u root -D test_order -N -e "${SELECT_SQL}" >INSERT_SQL# time mysql -h 192.168.48.32 -P 4000 -u root -D test_order -N -e "${SELECT_SQL}" >INSERT_SQL
real 0m8.863suser 0m0.085ssys 0m0.011s// 生成 INSERT 语句
# wc -l INSERT_SQL30319 INSERT_SQL# calc 30319/16 1894.9375# /bin/rm INSERT_SQL_PART*# split -l 1895 -d -a2 INSERT_SQL INSERT_SQL_PART// 按总的 INSERT 语句行数量进行 16 个子任务的拆分。
# ls INSERT_SQL_PART*INSERT_SQL_PART00 INSERT_SQL_PART02 INSERT_SQL_PART04 INSERT_SQL_PART06 INSERT_SQL_PART08 INSERT_SQL_PART10 INSERT_SQL_PART12 INSERT_SQL_PART14INSERT_SQL_PART01 INSERT_SQL_PART03 INSERT_SQL_PART05 INSERT_SQL_PART07 INSERT_SQL_PART09 INSERT_SQL_PART11 INSERT_SQL_PART13 INSERT_SQL_PART15# for line in `ls INSERT_SQL_PART*`; do mysql -h 192.168.48.23 -P 4000 -u root -D test_order <${line} & done[1] 28233[2] 28234[3] 28235[4] 28236[5] 28237[6] 28238[7] 28239[8] 28240[9] 28241[10] 28242[11] 28243[12] 28244[13] 28245[14] 28246[15] 28247[16] 28248# // 16 个并发执行 insert 子任务
复制代码
因为是后台运行，需要从连接数监控查看执行时间：
总耗时约 38 分钟。
并行插入期间 tidb-server 的 CPU 监控如下：
并行插入期间 tikv-server 的 CPU 监控如下：
16 并发插入无索引表
可以通过手工对主键进行分段，再分任务对节点进行多会话的并发插入。
从连接数监控查看执行时间：
总耗时约 13 分钟。
总结
各场景对比下：
划线
评论
复制
发布于: 5 小时前阅读数: 6
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/edda2d9ee7569225250e91053】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论