Insert into select 语句中 prepare 时间解析
作者：TiDB 社区干货传送门
作者： yilong 原文来源：https://tidb.net/blog/f49df23a
先说结论
insert into select 语句执行计划中的 prepare 耗时说明。
prepare 总耗时 ≈ next_time + get_datum_time + get_row_time
totalNextTime: 累计所有 Next 操作的耗时
totalGetDatumTime: 累计所有 GetDatumRow 操作的耗时
totalGetRowTime: 累计所有 getRow 操作的耗时
注意：这里的 prepare 是 insert into select 准备数据的耗时。与 prepare 执行语句缓存没有关系。
问题来源
表结构和 data 使用“数据准备” 中的数据。
从执行计划可以看出消耗总时间为 time:256.6ms=prepare:160.1ms + insert:96.5ms
insert 主要是写入流程中的 prewrite 和 commit 的时间。
但是从执行计划中无法看到 prepare 的消耗是在哪里，所以想要找出这里时间的耗时。
mysql> explain analyze insert into test_aim select * from test partition(P_202501_1) where pk_id>=1 and pk_id<=5000;+--------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+--------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------+| Insert_1 | N/A | 0 | root | | time:256.6ms, loops:1, prepare: 160.1ms, insert:96.5ms, RU:10269.241872 | N/A | 15.9 MB | N/A || └─TableReader_8 | 5000.96 | 5000 | root | partition:P_202501_1 | time:39.7ms, loops:6, cop_task: {num: 6, max: 33.3ms, min: 3.39ms, avg: 14ms, p95: 33.3ms, tot_proc: 79ms, copr_cache_hit_ratio: 0.00, build_task_duration: 7.38µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:6, total_time:83.6ms}} | data:TableRangeScan_7 | 12.5 MB | N/A || └─TableRangeScan_7 | 5000.96 | 5000 | cop[tikv] | table:test | tikv_task:{proc max:32.2ms, min:3.25ms, avg: 13.5ms, p80:17.1ms, p95:32.2ms, iters:0, tasks:6}, time_detail: {total_process_time: 79ms} | range:[1,5000], keep order:false | N/A | N/A |+--------------------------+---------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------+3 rows in set (0.27 sec)Records: 5000 Duplicates: 0 Warnings: 0
复制代码
代码分析
insert into select 主要的代码 （以 8.5.1 为例）
可以看到以下 3 个动作并没有记录时间，怀疑时间消耗在这里。
Next() - 读取 chunk 数据并解码列数据
GetDatumRow() - 将 chunk.Row 转换为带类型的 DatumRow
getRow() - 类型转换和行数据构造
修改代码给日志中添加时间打印
func insertRowsFromSelect(ctx context.Context, base insertCommon) error { // process `insert|replace into ... select ... from ...`
// 添加统计变量 var ( totalNextTime time.Duration totalGetDatumTime time.Duration totalGetRowTime time.Duration totalRowsProcessed int64 )
// 在函数返回前添加统计日志打印 defer func() { logutil.Logger(ctx).Info("INSERT SELECT 性能统计", zap.Duration("total_next_time", totalNextTime), zap.Duration("total_get_datum_time", totalGetDatumTime), zap.Duration("total_get_row_time", totalGetRowTime), zap.Int64("total_rows_processed", totalRowsProcessed), zap.Duration("avg_next_per_row", totalNextTime/time.Duration(totalRowsProcessed)), zap.Duration("avg_get_datum_per_row", totalGetDatumTime/time.Duration(totalRowsProcessed)), zap.Duration("avg_get_row_per_row", totalGetRowTime/time.Duration(totalRowsProcessed))) }()
e := base.insertCommon() selectExec := e.Children(0) fields := exec.RetTypes(selectExec) chk := exec.TryNewCacheChunk(selectExec) iter := chunk.NewIterator4Chunk(chk) rows := make([][]types.Datum, 0, chk.Capacity())
sessVars := e.Ctx().GetSessionVars() batchSize := sessVars.DMLBatchSize batchInsert := sessVars.BatchInsert && !sessVars.InTxn() && variable.EnableBatchDML.Load() && batchSize > 0 memUsageOfRows := int64(0) memUsageOfExtraCols := int64(0) memTracker := e.memTracker extraColsInSel := make([][]types.Datum, 0, chk.Capacity()) // In order to ensure the correctness of the `transaction write throughput` SLI statistics, // just ignore the transaction which contain `insert|replace into ... select ... from ...` statement. e.Ctx().GetTxnWriteThroughputSLI().SetInvalid() for { // 1. 记录Next耗时 start := time.Now() err := exec.Next(ctx, selectExec, chk) nextTime := time.Since(start) totalNextTime += nextTime if err != nil { return err } if chk.NumRows() == 0 { break } chkMemUsage := chk.MemoryUsage() memTracker.Consume(chkMemUsage) var totalMemDelta int64 for innerChunkRow := iter.Begin(); innerChunkRow != iter.End(); innerChunkRow = iter.Next() { totalRowsProcessed++ // 2. 记录GetDatumRow耗时 start = time.Now() innerRow := innerChunkRow.GetDatumRow(fields) getDatumTime := time.Since(start) totalGetDatumTime += getDatumTime e.rowCount++ // 3. 记录getRow耗时 start = time.Now() row, err := e.getRow(ctx, innerRow) getRowTime := time.Since(start) totalGetRowTime += getRowTime if err != nil { return err } extraColsInSel = append(extraColsInSel, innerRow[e.rowLen:]) rows = append(rows, row) if batchInsert && e.rowCount%uint64(batchSize) == 0 { memUsageOfRows = types.EstimatedMemUsage(rows[0], len(rows)) memUsageOfExtraCols = types.EstimatedMemUsage(extraColsInSel[0], len(extraColsInSel)) totalMemDelta += memUsageOfRows + memUsageOfExtraCols e.Ctx().GetSessionVars().CurrInsertBatchExtraCols = extraColsInSel if err = base.exec(ctx, rows); err != nil { return err } rows = rows[:0] extraColsInSel = extraColsInSel[:0] totalMemDelta += -memUsageOfRows - memUsageOfExtraCols memUsageOfRows = 0 if err = e.doBatchInsert(ctx); err != nil { return err } } } memTracker.Consume(totalMemDelta)
if len(rows) != 0 { memUsageOfRows = types.EstimatedMemUsage(rows[0], len(rows)) memUsageOfExtraCols = types.EstimatedMemUsage(extraColsInSel[0], len(extraColsInSel)) memTracker.Consume(memUsageOfRows + memUsageOfExtraCols) e.Ctx().GetSessionVars().CurrInsertBatchExtraCols = extraColsInSel } err = base.exec(ctx, rows) if err != nil { return err } rows = rows[:0] extraColsInSel = extraColsInSel[:0] memTracker.Consume(-memUsageOfRows - memUsageOfExtraCols - chkMemUsage) } return nil}
复制代码
测试结果
可以看到 next_time(39.7ms) + get_datum_time(16.5ms) + get_row_time(103.3ms) = 159.5ms 和 prepare:160.1ms 基本相同。可以证明结论。
[2025/06/12 11:40:21.062 +08:00] [INFO] [insert_common.go:463] ["INSERT SELECT 性能统计"] [conn=2097154] [session_alias=] [total_next_time=39.691623ms] [total_get_datum_time=16.490822ms] [total_get_row_time=103.316001ms] [total_rows_processed=5000] [avg_next_per_row=7.938µs] [avg_get_datum_per_row=3.298µs] [avg_get_row_per_row=20.663µs]
复制代码
数据准备
创建表 test
CREATE TABLE `test` ( `pk_id` bigint(20) NOT NULL COMMENT 'pk_id', `multi_test_id` varchar(5) DEFAULT NULL, `data_dt` date NOT NULL, `data_test_cd` varchar(20) DEFAULT NULL, `testid` varchar(9) DEFAULT NULL, `rpt_test` varchar(10) DEFAULT NULL, `cctest` varchar(3) DEFAULT NULL, `test_id` varchar(18) DEFAULT NULL, `test_nm` varchar(200) DEFAULT NULL, `idx1` decimal(25,6) DEFAULT NULL COMMENT '指标1', `idx2` decimal(25,6) DEFAULT NULL COMMENT '指标2', `idx3` decimal(25,6) DEFAULT NULL COMMENT '指标3', `idx4` decimal(25,6) DEFAULT NULL COMMENT '指标4', `idx5` decimal(25,6) DEFAULT NULL COMMENT '指标5', `idx6` decimal(25,6) DEFAULT NULL COMMENT '指标6', `idx7` decimal(25,6) DEFAULT NULL COMMENT '指标7', `idx8` decimal(25,6) DEFAULT NULL COMMENT '指标8', `idx9` decimal(25,6) DEFAULT NULL COMMENT '指标9', `idx10` decimal(25,6) DEFAULT NULL COMMENT '指标10', `idx11` decimal(25,6) DEFAULT NULL COMMENT '指标11', `idx12` decimal(25,6) DEFAULT NULL COMMENT '指标12', `idx13` decimal(25,6) DEFAULT NULL COMMENT '指标13', `idx14` decimal(25,6) DEFAULT NULL COMMENT '指标14', `idx15` decimal(25,6) DEFAULT NULL COMMENT '指标15', `idx16` decimal(25,6) DEFAULT NULL COMMENT '指标16', `idx17` decimal(25,6) DEFAULT NULL COMMENT '指标17', `idx18` decimal(25,6) DEFAULT NULL COMMENT '指标18', `idx19` decimal(25,6) DEFAULT NULL COMMENT '指标19', `idx20` decimal(25,6) DEFAULT NULL COMMENT '指标20', `idx21` decimal(25,6) DEFAULT NULL COMMENT '指标21', `idx22` decimal(25,6) DEFAULT NULL COMMENT '指标22', `idx23` decimal(25,6) DEFAULT NULL COMMENT '指标23', `idx24` decimal(25,6) DEFAULT NULL COMMENT '指标24', `idx25` decimal(25,6) DEFAULT NULL COMMENT '指标25', `idx26` decimal(25,6) DEFAULT NULL COMMENT '指标26', `idx27` decimal(25,6) DEFAULT NULL COMMENT '指标27', `idx28` decimal(25,6) DEFAULT NULL COMMENT '指标28', `idx29` decimal(25,6) DEFAULT NULL COMMENT '指标29', `idx30` decimal(25,6) DEFAULT NULL COMMENT '指标30', `idx31` decimal(25,6) DEFAULT NULL COMMENT '指标31', `idx32` decimal(25,6) DEFAULT NULL COMMENT '指标32', `idx33` decimal(25,6) DEFAULT NULL COMMENT '指标33', `idx34` decimal(25,6) DEFAULT NULL COMMENT '指标34', `idx35` decimal(25,6) DEFAULT NULL COMMENT '指标35', `idx36` decimal(25,6) DEFAULT NULL COMMENT '指标36', `idx37` decimal(25,6) DEFAULT NULL COMMENT '指标37', `idx38` decimal(25,6) DEFAULT NULL COMMENT '指标38', `idx39` decimal(25,6) DEFAULT NULL COMMENT '指标39', `idx40` decimal(25,6) DEFAULT NULL COMMENT '指标40', `idx41` decimal(25,6) DEFAULT NULL COMMENT '指标41', `idx42` decimal(25,6) DEFAULT NULL COMMENT '指标42', `idx43` decimal(25,6) DEFAULT NULL COMMENT '指标43', `idx44` decimal(25,6) DEFAULT NULL COMMENT '指标44', `idx45` decimal(25,6) DEFAULT NULL COMMENT '指标45', `idx46` decimal(25,6) DEFAULT NULL COMMENT '指标46', `idx47` decimal(25,6) DEFAULT NULL COMMENT '指标47', `idx48` decimal(25,6) DEFAULT NULL COMMENT '指标48', `idx49` decimal(25,6) DEFAULT NULL COMMENT '指标49', `idx50` decimal(25,6) DEFAULT NULL COMMENT '指标50', `idx51` decimal(25,6) DEFAULT NULL COMMENT '指标51', `idx52` decimal(25,6) DEFAULT NULL COMMENT '指标52', `idx53` decimal(25,6) DEFAULT NULL COMMENT '指标53', `idx54` decimal(25,6) DEFAULT NULL COMMENT '指标54', `idx55` decimal(25,6) DEFAULT NULL COMMENT '指标55', `idx56` decimal(25,6) DEFAULT NULL COMMENT '指标56', `idx57` decimal(25,6) DEFAULT NULL COMMENT '指标57', `idx58` decimal(25,6) DEFAULT NULL COMMENT '指标58', `idx59` decimal(25,6) DEFAULT NULL COMMENT '指标59', `idx60` decimal(25,6) DEFAULT NULL COMMENT '指标60', `idx61` decimal(25,6) DEFAULT NULL COMMENT '指标61', `idx62` decimal(25,6) DEFAULT NULL COMMENT '指标62', `idx63` decimal(25,6) DEFAULT NULL COMMENT '指标63', `idx64` decimal(25,6) DEFAULT NULL COMMENT '指标64', `idx65` decimal(25,6) DEFAULT NULL COMMENT '指标65', `idx66` decimal(25,6) DEFAULT NULL COMMENT '指标66', `idx67` decimal(25,6) DEFAULT NULL COMMENT '指标67', `idx68` decimal(25,6) DEFAULT NULL COMMENT '指标68', `idx69` decimal(25,6) DEFAULT NULL COMMENT '指标69', `idx70` decimal(25,6) DEFAULT NULL COMMENT '指标70', `idx71` decimal(25,6) DEFAULT NULL COMMENT '指标71', `idx72` decimal(25,6) DEFAULT NULL COMMENT '指标72', `idx73` decimal(25,6) DEFAULT NULL COMMENT '指标73', `idx74` decimal(25,6) DEFAULT NULL COMMENT '指标74', `idx75` decimal(25,6) DEFAULT NULL COMMENT '指标75', `idx76` decimal(25,6) DEFAULT NULL COMMENT '指标76', `idx77` decimal(25,6) DEFAULT NULL COMMENT '指标77', `idx78` decimal(25,6) DEFAULT NULL COMMENT '指标78', `idx79` decimal(25,6) DEFAULT NULL COMMENT '指标79', `idx80` decimal(25,6) DEFAULT NULL COMMENT '指标80', `idx81` decimal(25,6) DEFAULT NULL COMMENT '指标81', `idx82` decimal(25,6) DEFAULT NULL COMMENT '指标82', `idx83` decimal(25,6) DEFAULT NULL COMMENT '指标83', `idx84` decimal(25,6) DEFAULT NULL COMMENT '指标84', `idx85` decimal(25,6) DEFAULT NULL COMMENT '指标85', `idx86` decimal(25,6) DEFAULT NULL COMMENT '指标86', `idx87` decimal(25,6) DEFAULT NULL COMMENT '指标87', `idx88` decimal(25,6) DEFAULT NULL COMMENT '指标88', `idx89` decimal(25,6) DEFAULT NULL COMMENT '指标89', `idx90` decimal(25,6) DEFAULT NULL COMMENT '指标90', `idx91` decimal(25,6) DEFAULT NULL COMMENT '指标91', `idx92` decimal(25,6) DEFAULT NULL COMMENT '指标92', `idx93` decimal(25,6) DEFAULT NULL COMMENT '指标93', `idx94` decimal(25,6) DEFAULT NULL COMMENT '指标94', `idx95` decimal(25,6) DEFAULT NULL COMMENT '指标95', `idx96` decimal(25,6) DEFAULT NULL COMMENT '指标96', `idx97` decimal(25,6) DEFAULT NULL COMMENT '指标97', `idx98` decimal(25,6) DEFAULT NULL COMMENT '指标98', `idx99` decimal(25,6) DEFAULT NULL COMMENT '指标99', `idx100` decimal(25,6) DEFAULT NULL COMMENT '指标100', `test_test_dt_tm` timestamp NULL DEFAULT NULL, PRIMARY KEY (`pk_id`,`data_dt`) /*T![clustered_index] NONCLUSTERED */, KEY `idx_test_idx_tbl` (`test_id`,`data_test_cd`,`testid`,`rpt_test`,`cctest`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=4 */PARTITION BY RANGE (TO_DAYS(`DATA_DT`))(PARTITION `P_202412` VALUES LESS THAN (739617), PARTITION `P_202501_1` VALUES LESS THAN (739627), PARTITION `P_202501_2` VALUES LESS THAN (739637), PARTITION `P_202501_3` VALUES LESS THAN (739648), PARTITION `P_202502_1` VALUES LESS THAN (739658), PARTITION `P_202502_2` VALUES LESS THAN (739668), PARTITION `P_202502_3` VALUES LESS THAN (739676), PARTITION `P_202503_1` VALUES LESS THAN (739686), PARTITION `P_202503_2` VALUES LESS THAN (739696), PARTITION `P_202503_3` VALUES LESS THAN (739707), PARTITION `P_202504_1` VALUES LESS THAN (739717), PARTITION `P_202504_2` VALUES LESS THAN (739727), PARTITION `P_202504_3` VALUES LESS THAN (739737), PARTITION `P_202505_1` VALUES LESS THAN (739747), PARTITION `P_202505_2` VALUES LESS THAN (739757), PARTITION `P_202505_3` VALUES LESS THAN (739768), PARTITION `P_202506_1` VALUES LESS THAN (739778), PARTITION `P_202506_2` VALUES LESS THAN (739788), PARTITION `P_202506_3` VALUES LESS THAN (739798), PARTITION `P_202507_1` VALUES LESS THAN (739808), PARTITION `P_202507_2` VALUES LESS THAN (739818), PARTITION `P_202507_3` VALUES LESS THAN (739829), PARTITION `P_202508_1` VALUES LESS THAN (739839), PARTITION `P_202508_2` VALUES LESS THAN (739849), PARTITION `P_202508_3` VALUES LESS THAN (739860), PARTITION `P_202509_1` VALUES LESS THAN (739870), PARTITION `P_202509_2` VALUES LESS THAN (739880), PARTITION `P_202509_3` VALUES LESS THAN (739890), PARTITION `P_202510_1` VALUES LESS THAN (739900), PARTITION `P_202510_2` VALUES LESS THAN (739910), PARTITION `P_202510_3` VALUES LESS THAN (739921), PARTITION `P_202511_1` VALUES LESS THAN (739931), PARTITION `P_202511_2` VALUES LESS THAN (739941), PARTITION `P_202511_3` VALUES LESS THAN (739951), PARTITION `P_202512_1` VALUES LESS THAN (739961), PARTITION `P_202512_2` VALUES LESS THAN (739971), PARTITION `P_202512_3` VALUES LESS THAN (739982))
复制代码
创建表 test_aim
CREATE TABLE `test_aim` ( `pk_id` bigint(20) NOT NULL COMMENT 'pk_id', `multi_test_id` varchar(5) DEFAULT NULL, `data_dt` date NOT NULL, `data_test_cd` varchar(20) DEFAULT NULL, `testid` varchar(9) DEFAULT NULL, `rpt_test` varchar(10) DEFAULT NULL, `cctest` varchar(3) DEFAULT NULL, `test_id` varchar(18) DEFAULT NULL, `test_nm` varchar(200) DEFAULT NULL, `idx1` decimal(25,6) DEFAULT NULL, `idx2` decimal(25,6) DEFAULT NULL, `idx3` decimal(25,6) DEFAULT NULL, `idx4` decimal(25,6) DEFAULT NULL, `idx5` decimal(25,6) DEFAULT NULL, `idx6` decimal(25,6) DEFAULT NULL, `idx7` decimal(25,6) DEFAULT NULL, `idx8` decimal(25,6) DEFAULT NULL, `idx9` decimal(25,6) DEFAULT NULL, `idx10` decimal(25,6) DEFAULT NULL, `idx11` decimal(25,6) DEFAULT NULL, `idx12` decimal(25,6) DEFAULT NULL, `idx13` decimal(25,6) DEFAULT NULL, `idx14` decimal(25,6) DEFAULT NULL, `idx15` decimal(25,6) DEFAULT NULL, `idx16` decimal(25,6) DEFAULT NULL, `idx17` decimal(25,6) DEFAULT NULL, `idx18` decimal(25,6) DEFAULT NULL, `idx19` decimal(25,6) DEFAULT NULL, `idx20` decimal(25,6) DEFAULT NULL, `idx21` decimal(25,6) DEFAULT NULL, `idx22` decimal(25,6) DEFAULT NULL, `idx23` decimal(25,6) DEFAULT NULL, `idx24` decimal(25,6) DEFAULT NULL, `idx25` decimal(25,6) DEFAULT NULL, `idx26` decimal(25,6) DEFAULT NULL, `idx27` decimal(25,6) DEFAULT NULL, `idx28` decimal(25,6) DEFAULT NULL, `idx29` decimal(25,6) DEFAULT NULL, `idx30` decimal(25,6) DEFAULT NULL, `idx31` decimal(25,6) DEFAULT NULL, `idx32` decimal(25,6) DEFAULT NULL, `idx33` decimal(25,6) DEFAULT NULL, `idx34` decimal(25,6) DEFAULT NULL, `idx35` decimal(25,6) DEFAULT NULL, `idx36` decimal(25,6) DEFAULT NULL, `idx37` decimal(25,6) DEFAULT NULL, `idx38` decimal(25,6) DEFAULT NULL, `idx39` decimal(25,6) DEFAULT NULL, `idx40` decimal(25,6) DEFAULT NULL, `idx41` decimal(25,6) DEFAULT NULL, `idx42` decimal(25,6) DEFAULT NULL, `idx43` decimal(25,6) DEFAULT NULL, `idx44` decimal(25,6) DEFAULT NULL, `idx45` decimal(25,6) DEFAULT NULL, `idx46` decimal(25,6) DEFAULT NULL, `idx47` decimal(25,6) DEFAULT NULL, `idx48` decimal(25,6) DEFAULT NULL, `idx49` decimal(25,6) DEFAULT NULL, `idx50` decimal(25,6) DEFAULT NULL, `idx51` decimal(25,6) DEFAULT NULL, `idx52` decimal(25,6) DEFAULT NULL, `idx53` decimal(25,6) DEFAULT NULL, `idx54` decimal(25,6) DEFAULT NULL, `idx55` decimal(25,6) DEFAULT NULL, `idx56` decimal(25,6) DEFAULT NULL, `idx57` decimal(25,6) DEFAULT NULL, `idx58` decimal(25,6) DEFAULT NULL, `idx59` decimal(25,6) DEFAULT NULL, `idx60` decimal(25,6) DEFAULT NULL, `idx61` decimal(25,6) DEFAULT NULL, `idx62` decimal(25,6) DEFAULT NULL, `idx63` decimal(25,6) DEFAULT NULL, `idx64` decimal(25,6) DEFAULT NULL, `idx65` decimal(25,6) DEFAULT NULL, `idx66` decimal(25,6) DEFAULT NULL, `idx67` decimal(25,6) DEFAULT NULL, `idx68` decimal(25,6) DEFAULT NULL, `idx69` decimal(25,6) DEFAULT NULL, `idx70` decimal(25,6) DEFAULT NULL, `idx71` decimal(25,6) DEFAULT NULL, `idx72` decimal(25,6) DEFAULT NULL, `idx73` decimal(25,6) DEFAULT NULL, `idx74` decimal(25,6) DEFAULT NULL, `idx75` decimal(25,6) DEFAULT NULL, `idx76` decimal(25,6) DEFAULT NULL, `idx77` decimal(25,6) DEFAULT NULL, `idx78` decimal(25,6) DEFAULT NULL, `idx79` decimal(25,6) DEFAULT NULL, `idx80` decimal(25,6) DEFAULT NULL, `idx81` decimal(25,6) DEFAULT NULL, `idx82` decimal(25,6) DEFAULT NULL, `idx83` decimal(25,6) DEFAULT NULL, `idx84` decimal(25,6) DEFAULT NULL, `idx85` decimal(25,6) DEFAULT NULL, `idx86` decimal(25,6) DEFAULT NULL, `idx87` decimal(25,6) DEFAULT NULL, `idx88` decimal(25,6) DEFAULT NULL, `idx89` decimal(25,6) DEFAULT NULL, `idx90` decimal(25,6) DEFAULT NULL, `idx91` decimal(25,6) DEFAULT NULL, `idx92` decimal(25,6) DEFAULT NULL, `idx93` decimal(25,6) DEFAULT NULL, `idx94` decimal(25,6) DEFAULT NULL, `idx95` decimal(25,6) DEFAULT NULL, `idx96` decimal(25,6) DEFAULT NULL, `idx97` decimal(25,6) DEFAULT NULL, `idx98` decimal(25,6) DEFAULT NULL, `idx99` decimal(25,6) DEFAULT NULL, `idx100` decimal(25,6) DEFAULT NULL, `test_test_dt_tm` timestamp NULL DEFAULT NULL, PRIMARY KEY (`pk_id`,`data_dt`) /*T![clustered_index] NONCLUSTERED */) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
复制代码
插入 10000 条数据到 test 表 P_202501_1 分区
package main
import ( "database/sql" "fmt" "math" "math/rand" "strconv" "time"
_ "github.com/go-sql-driver/mysql")
func main() { // 数据库连接配置 db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/test") if err != nil { panic(err.Error()) } defer db.Close()
// 准备插入语句 stmt, err := db.Prepare(`INSERT INTO test( pk_id, multi_test_id, data_dt, data_test_cd, testid, rpt_test, cctest, test_id, test_nm, idx1, idx2, idx3, idx4, idx5, idx6, idx7, idx8, idx9, idx10, idx11, idx12, idx13, idx14, idx15, idx16, idx17, idx18, idx19, idx20, idx21, idx22, idx23, idx24, idx25, idx26, idx27, idx28, idx29, idx30, idx31, idx32, idx33, idx34, idx35, idx36, idx37, idx38, idx39, idx40, idx41, idx42, idx43, idx44, idx45, idx46, idx47, idx48, idx49, idx50, idx51, idx52, idx53, idx54, idx55, idx56, idx57, idx58, idx59, idx60, idx61, idx62, idx63, idx64, idx65, idx66, idx67, idx68, idx69, idx70, idx71, idx72, idx73, idx74, idx75, idx76, idx77, idx78, idx79, idx80, idx81, idx82, idx83, idx84, idx85, idx86, idx87, idx88, idx89, idx90, idx91, idx92, idx93, idx94, idx95, idx96, idx97, idx98, idx99, idx100, test_test_dt_tm ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )`) if err != nil { panic(err.Error()) } defer stmt.Close()
// 随机数种子 rand.Seed(time.Now().UnixNano())
// P_202501_1 分区对应的日期范围 (2025-01-01 到 2025-01-10) startDate := time.Date(2025, 1, 1, 0, 0, 0, 0, time.UTC) endDate := time.Date(2025, 1, 10, 0, 0, 0, 0, time.UTC)
// 生成10000条数据 for i := 1; i <= 10000; i++ { // 生成随机数据，日期限制在P_202501_1分区范围内 data := generateTestData(i, startDate, endDate)
// 执行插入 _, err := stmt.Exec( data.pk_id, data.multi_test_id, data.data_dt, data.data_test_cd, data.testid, data.rpt_test, data.cctest, data.test_id, data.test_nm, data.idx1, data.idx2, data.idx3, data.idx4, data.idx5, data.idx6, data.idx7, data.idx8, data.idx9, data.idx10, data.idx11, data.idx12, data.idx13, data.idx14, data.idx15, data.idx16, data.idx17, data.idx18, data.idx19, data.idx20, data.idx21, data.idx22, data.idx23, data.idx24, data.idx25, data.idx26, data.idx27, data.idx28, data.idx29, data.idx30, data.idx31, data.idx32, data.idx33, data.idx34, data.idx35, data.idx36, data.idx37, data.idx38, data.idx39, data.idx40, data.idx41, data.idx42, data.idx43, data.idx44, data.idx45, data.idx46, data.idx47, data.idx48, data.idx49, data.idx50, data.idx51, data.idx52, data.idx53, data.idx54, data.idx55, data.idx56, data.idx57, data.idx58, data.idx59, data.idx60, data.idx61, data.idx62, data.idx63, data.idx64, data.idx65, data.idx66, data.idx67, data.idx68, data.idx69, data.idx70, data.idx71, data.idx72, data.idx73, data.idx74, data.idx75, data.idx76, data.idx77, data.idx78, data.idx79, data.idx80, data.idx81, data.idx82, data.idx83, data.idx84, data.idx85, data.idx86, data.idx87, data.idx88, data.idx89, data.idx90, data.idx91, data.idx92, data.idx93, data.idx94, data.idx95, data.idx96, data.idx97, data.idx98, data.idx99, data.idx100, data.test_test_dt_tm, )
if err != nil { panic(err.Error()) }
if i%1000 == 0 { fmt.Printf("已插入 %d 条数据\n", i) } }
fmt.Println("数据插入完成，共插入 10000 条数据到 P_202501_1 分区")}
// 测试数据结构体type testData struct { pk_id int64 multi_test_id string data_dt time.Time data_test_cd string testid string rpt_test string cctest string test_id string test_nm string idx1 float64 idx2 float64 idx3 float64 idx4 float64 idx5 float64 idx6 float64 idx7 float64 idx8 float64 idx9 float64 idx10 float64 idx11 float64 idx12 float64 idx13 float64 idx14 float64 idx15 float64 idx16 float64 idx17 float64 idx18 float64 idx19 float64 idx20 float64 idx21 float64 idx22 float64 idx23 float64 idx24 float64 idx25 float64 idx26 float64 idx27 float64 idx28 float64 idx29 float64 idx30 float64 idx31 float64 idx32 float64 idx33 float64 idx34 float64 idx35 float64 idx36 float64 idx37 float64 idx38 float64 idx39 float64 idx40 float64 idx41 float64 idx42 float64 idx43 float64 idx44 float64 idx45 float64 idx46 float64 idx47 float64 idx48 float64 idx49 float64 idx50 float64 idx51 float64 idx52 float64 idx53 float64 idx54 float64 idx55 float64 idx56 float64 idx57 float64 idx58 float64 idx59 float64 idx60 float64 idx61 float64 idx62 float64 idx63 float64 idx64 float64 idx65 float64 idx66 float64 idx67 float64 idx68 float64 idx69 float64 idx70 float64 idx71 float64 idx72 float64 idx73 float64 idx74 float64 idx75 float64 idx76 float64 idx77 float64 idx78 float64 idx79 float64 idx80 float64 idx81 float64 idx82 float64 idx83 float64 idx84 float64 idx85 float64 idx86 float64 idx87 float64 idx88 float64 idx89 float64 idx90 float64 idx91 float64 idx92 float64 idx93 float64 idx94 float64 idx95 float64 idx96 float64 idx97 float64 idx98 float64 idx99 float64 idx100 float64 test_test_dt_tm time.Time}
// 生成随机测试数据（限定在指定日期范围内）func generateTestData(id int, startDate, endDate time.Time) testData { // 计算日期范围内的天数 days := int(endDate.Sub(startDate).Hours() / 24) // 生成随机日期（在指定范围内） randomDay := rand.Intn(days) dataDt := startDate.AddDate(0, 0, randomDay)
// 生成随机指标值 (0-1000000 之间的随机数，保留6位小数) generateIndex := func() float64 { return roundFloat(rand.Float64()*1000000, 6) }
// 生成随机字符串 randomString := func(length int) string { const charset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789" b := make([]byte, length) for i := range b { b[i] = charset[rand.Intn(len(charset))] } return string(b) }
// 生成随机客户ID cstID := "C" + strconv.Itoa(rand.Intn(99999999999999999))
// 生成随机客户名称 names := []string{"张三", "李四", "王五", "赵六", "钱七", "孙八", "周九", "吴十", "郑十一", "王十二"} cstNm := names[rand.Intn(len(names))] + "有限公司"
// 生成所有指标值 var idxs [100]float64 for i := 0; i < 100; i++ { idxs[i] = generateIndex() }
return testData{ pk_id: int64(id), multi_test_id: randomString(5), data_dt: dataDt, data_test_cd: []string{"D", "W", "M", "Q", "Y"}[rand.Intn(5)], testid: randomString(9), rpt_test: randomString(10), cctest: []string{"CNY", "USD", "EUR", "JPY", "GBP"}[rand.Intn(5)], test_id: cstID, test_nm: cstNm, idx1: idxs[0], idx2: idxs[1], idx3: idxs[2], idx4: idxs[3], idx5: idxs[4], idx6: idxs[5], idx7: idxs[6], idx8: idxs[7], idx9: idxs[8], idx10: idxs[9], idx11: idxs[10], idx12: idxs[11], idx13: idxs[12], idx14: idxs[13], idx15: idxs[14], idx16: idxs[15], idx17: idxs[16], idx18: idxs[17], idx19: idxs[18], idx20: idxs[19], idx21: idxs[20], idx22: idxs[21], idx23: idxs[22], idx24: idxs[23], idx25: idxs[24], idx26: idxs[25], idx27: idxs[26], idx28: idxs[27], idx29: idxs[28], idx30: idxs[29], idx31: idxs[30], idx32: idxs[31], idx33: idxs[32], idx34: idxs[33], idx35: idxs[34], idx36: idxs[35], idx37: idxs[36], idx38: idxs[37], idx39: idxs[38], idx40: idxs[39], idx41: idxs[40], idx42: idxs[41], idx43: idxs[42], idx44: idxs[43], idx45: idxs[44], idx46: idxs[45], idx47: idxs[46], idx48: idxs[47], idx49: idxs[48], idx50: idxs[49], idx51: idxs[50], idx52: idxs[51], idx53: idxs[52], idx54: idxs[53], idx55: idxs[54], idx56: idxs[55], idx57: idxs[56], idx58: idxs[57], idx59: idxs[58], idx60: idxs[59], idx61: idxs[60], idx62: idxs[61], idx63: idxs[62], idx64: idxs[63], idx65: idxs[64], idx66: idxs[65], idx67: idxs[66], idx68: idxs[67], idx69: idxs[68], idx70: idxs[69], idx71: idxs[70], idx72: idxs[71], idx73: idxs[72], idx74: idxs[73], idx75: idxs[74], idx76: idxs[75], idx77: idxs[76], idx78: idxs[77], idx79: idxs[78], idx80: idxs[79], idx81: idxs[80], idx82: idxs[81], idx83: idxs[82], idx84: idxs[83], idx85: idxs[84], idx86: idxs[85], idx87: idxs[86], idx88: idxs[87], idx89: idxs[88], idx90: idxs[89], idx91: idxs[90], idx92: idxs[91], idx93: idxs[92], idx94: idxs[93], idx95: idxs[94], idx96: idxs[95], idx97: idxs[96], idx98: idxs[97], idx99: idxs[98], idx100: idxs[99], test_test_dt_tm: time.Now(), }}
// 四舍五入保留指定位数的小数func roundFloat(val float64, precision uint) float64 { ratio := math.Pow(10, float64(precision)) return math.Round(val*ratio) / ratio}
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 2
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/844b03bf375785e861c170e3b】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论