先说结论
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
}