IT不打烊的tdsql_mysql学习记录-批量插入数据

`、导数据，100万数据

压测： sysbench --db-driver=mysql --mysql-host=xxxx --mysql-port=xxxx --mysql-user=xx --mysql-password=xxxxx --mysql-db=xx --tables=1 --table-size=1000000 oltp_read_write prepare

非分布式：

1）登录数据库，创建库表。 CREATE DATABASE testdb; USE testdb;

CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50) NOT NULL, phone VARCHAR(100) NOT NULL );

CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT NOT NULL, product_name VARCHAR(100) NOT NULL, amount DECIMAL(10, 2) NOT NULL );

2）在部署机执行下面脚本插入数据。(mysql配置改为你自己实例的信息) #!/bin/bash

MySQL 配置

DB_USER="用户名" DB_PASS="密码" DB_NAME="testdb" DB_HOST="实例IP" DB_PORT="实例端口" BATCH_SIZE=2000 # 每批次插入量 TOTAL_USERS=1000000 # 总用户数 TOTAL_ORDERS=1000000 # 总订单数

进度显示函数

show_progress() { local current=$1 local total=$2 local type=$3 printf "\r插入$type" $((current*100/total))$current $total }

安全执行函数（含错误重试）

safe_exec() { local sql=$1 for attempt in {1..3}; do if mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" --password="$DB_PASS" "$DB_NAME" -e "$sql" ; then return 0 else echo "尝试$attempt 失败，正在重试..." sleep $((attempt*2)) fi done echo "永久失败:$sql" exit 1 }

插入用户数据（单线程）

echo "开始插入用户数据..." for ((i=1; i<=TOTAL_USERS; i+=BATCH_SIZE)); do end=((i+BATCH_SIZE-1)) ((end > TOTAL_USERS)) && end=TOTAL_USERS

构建批量插入SQL

SQL="START TRANSACTION;" SQL+="INSERT INTO users (user_id, username, phone) VALUES " for ((j=i; j<=end; j++)); do SQL+="($j, 'user$j', '123456789${j:0:8}')," done SQL="${SQL%,}; COMMIT;"

safe_exec "$SQL" show_progress$end $TOTAL_USERS "用户" done echo -e "\n用户数据插入完成！"

插入订单数据（单线程）

echo "开始插入订单数据..." for ((i=1; i<=TOTAL_ORDERS; i+=BATCH_SIZE)); do end=((i+BATCH_SIZE-1)) ((end > TOTAL_ORDERS)) && end=TOTAL_ORDERS

构建批量插入SQL

SQL="START TRANSACTION;" SQL+="INSERT INTO orders (id, user_id, product_name, amount) VALUES " for ((j=i; j<=end; j++)); do user_id=$((1 + RANDOM amount=$((RANDOM % 101)) # 0-100随机整数 SQL+="($j,$user_id, 'product$j',$amount)," done SQL="${SQL%,}; COMMIT;"

safe_exec "$SQL" show_progress$end $TOTAL_ORDERS "订单" done echo -e "\n订单数据插入完成！"

数据完整性验证

echo -e "\n最终数据校验:" mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" --password="$DB_PASS" "$DB_NAME" <<EOF SELECT (SELECT COUNT() FROM users) AS total_users, (SELECT COUNT() FROM orders) AS total_orders, (SELECT COUNT(DISTINCT user_id) FROM orders) AS active_users, (SELECT MAX(amount) FROM orders) AS max_amount, (SELECT AVG(amount) FROM orders) AS avg_amount; EOF

SELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id = u.user_id WHERE o.user_id=用户ID;

分布式：

1）登录数据库，创建库表。 CREATE DATABASE testdb; USE testdb;

CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50) NOT NULL, phone VARCHAR(100) NOT NULL ) shardkey=user_id;

CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT NOT NULL, product_name VARCHAR(100) NOT NULL, amount DECIMAL(10, 2) NOT NULL )shardkey=id;

2）在部署机执行下面脚本插入数据。(mysql配置改为你自己实例的信息) #!/bin/bash

MySQL 配置

DB_USER="用户名" DB_PASS="密码" DB_NAME="testdb" DB_HOST="实例IP" DB_PORT="实例端口" BATCH_SIZE=2000 # 每批次插入量 TOTAL_USERS=1000000 # 总用户数 TOTAL_ORDERS=1000000 # 总订单数

进度显示函数

show_progress() { local current=$1 local total=$2 local type=$3 printf "\r插入$type" $((current*100/total))$current $total }

安全执行函数（含错误重试）

safe_exec() { local sql=$1 for attempt in {1..3}; do if mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" --password="$DB_PASS" "$DB_NAME" -e "$sql" ; then return 0 else echo "尝试$attempt 失败，正在重试..." sleep $((attempt*2)) fi done echo "永久失败:$sql" exit 1 }

插入用户数据（单线程）

echo "开始插入用户数据..." for ((i=1; i<=TOTAL_USERS; i+=BATCH_SIZE)); do end=((i+BATCH_SIZE-1)) ((end > TOTAL_USERS)) && end=TOTAL_USERS

构建批量插入SQL

SQL="START TRANSACTION;" SQL+="INSERT INTO users (user_id, username, phone) VALUES " for ((j=i; j<=end; j++)); do SQL+="($j, 'user$j', '123456789${j:0:8}')," done SQL="${SQL%,}; COMMIT;"

safe_exec "$SQL" show_progress$end $TOTAL_USERS "用户" done echo -e "\n用户数据插入完成！"

插入订单数据（单线程）

echo "开始插入订单数据..." for ((i=1; i<=TOTAL_ORDERS; i+=BATCH_SIZE)); do end=((i+BATCH_SIZE-1)) ((end > TOTAL_ORDERS)) && end=TOTAL_ORDERS

构建批量插入SQL

SQL="START TRANSACTION;" SQL+="INSERT INTO orders (id, user_id, product_name, amount) VALUES " for ((j=i; j<=end; j++)); do user_id=$((1 + RANDOM amount=$((RANDOM % 101)) # 0-100随机整数 SQL+="($j,$user_id, 'product$j',$amount)," done SQL="${SQL%,}; COMMIT;"

safe_exec "$SQL" show_progress$end $TOTAL_ORDERS "订单" done echo -e "\n订单数据插入完成！"

数据完整性验证

echo -e "\n最终数据校验:" mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" --password="$DB_PASS" "$DB_NAME" <<EOF SELECT (SELECT COUNT() FROM users) AS total_users, (SELECT COUNT() FROM orders) AS total_orders, (SELECT COUNT(DISTINCT user_id) FROM orders) AS active_users, (SELECT MAX(amount) FROM orders) AS max_amount, (SELECT AVG(amount) FROM orders) AS avg_amount; EOF

查看下面查询语句的执行计划。 SELECT u.username, u.phone FROM orders o JOIN users u ON o.user_id = u.user_id WHERE o.user_id=1234; 参考：cloud.tencent.com/edu/learnin…