写点什么

复制 order 表的效率挑战

  • 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 2057930319 */ 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
用户头像

TiDB 社区官网:https://tidb.net/ 2021-12-15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

评论

发布
暂无评论
复制 order 表的效率挑战_新版本/特性发布_TiDB 社区干货传送门_InfoQ写作社区