## 1. TiDB节点
– **CPU**：8-16核，高主频
– **内存**：32-64GB
– **磁盘**：SSD，200GB以上
– **网络**：万兆网络

## 2. TiKV节点
– **CPU**：16-32核
– **内存**：64-128GB
– **磁盘**：NVMe SSD，1TB以上
– **网络**：万兆网络

## 3. PD节点
– **CPU**：4-8核
– **内存**：16-32GB
– **磁盘**：SSD，100GB以上
– **网络**：万兆网络

## 4. 存储规划
– **TiKV存储**：使用NVMe SSD，提供高I/O性能
– **TiDB存储**：使用SSD，存储日志和临时文件
– **PD存储**：使用SSD，存储元数据

## 5. 网络规划
– **网络拓扑**：使用万兆交换机，确保节点间网络延迟低
– **网络带宽**：确保足够的网络带宽，避免网络瓶颈
– **网络隔离**：将业务网络和管理网络分离

## 6. 硬件推荐
– **TiDB节点**：
– CPU：Intel Xeon Gold 6248或更高
– 内存：64GB DDR4
– 磁盘：2×480GB SSD RAID1
– 网络：10GbE

– **TiKV节点**：
– CPU：Intel Xeon Gold 6248或更高
– 内存：128GB DDR4
– 磁盘：4×1.6TB NVMe SSD
– 网络：10GbE

– **PD节点**：
– CPU：Intel Xeon Gold 6248或更高
– 内存：32GB DDR4
– 磁盘：2×480GB SSD RAID1
– 网络：10GbE

## 1. TiDB配置
– **max-connections**：最大连接数，根据并发量设置
“`toml
max-connections = 10000
“`

– **txn-total-size-limit**：事务总大小限制，默认100MB
“`toml
txn-total-size-limit = 209715200 # 200MB
“`

– **stmt-count-limit**：单个语句的最大执行时间（秒）
“`toml
stmt-count-limit = 1000000
“`

– **tmp-storage-size**：临时存储大小
“`toml
tmp-storage-size = -1
“`

– **oom-action**：OOM时的操作
“`toml
oom-action = “cancel”
“`

## 2. TiKV配置
– **storage.block-cache.capacity**：块缓存大小，建议设置为内存的40%
“`toml
[storage.block-cache]
capacity = “64GB”
“`

– **raftstore.capacity**：Raft存储容量
“`toml
[raftstore]
capacity = “1.6TB”
“`

– **raftstore.raft-base-tick-interval**：Raft基础 tick 间隔
“`toml
[raftstore]
raft-base-tick-interval = “200ms”
“`

– **server.grpc-concurrency**：gRPC并发数，建议设置为CPU核心数
“`toml
[server]
grpc-concurrency = 32
“`

– **rocksdb.max-open-files**：RocksDB最大打开文件数
“`toml
[rocksdb]
max-open-files = 8192
“`

– **rocksdb.defaultcf.write-buffer-size**：写入缓冲区大小
“`toml
[rocksdb.defaultcf]
write-buffer-size = “1GB”
max-write-buffer-number = 4
“`

– **storage.gc.enable-compaction-filter**：启用压缩过滤器进行垃圾回收
“`toml
[storage.gc]
enable-compaction-filter = true
“`

– **storage.gc.ratio-threshold**：垃圾回收阈值
“`toml
[storage.gc]
ratio-threshold = 0.5
“`

## 3. PD配置
– **replication.max-replicas**：最大副本数
“`toml
[replication]
max-replicas = 3
“`

– **schedule.leader-schedule-limit**： leader 调度限制
“`toml
[schedule]
leader-schedule-limit = 4
“`

– **schedule.region-schedule-limit**： region 调度限制
“`toml
[schedule]
region-schedule-limit = 2048
“`

– **schedule.replica-schedule-limit**：副本调度限制
“`toml
[schedule]
replica-schedule-limit = 64
“`

## 4. 系统配置
– **文件描述符限制**：
“`bash
echo “* soft nofile 65535” >> /etc/security/limits.conf
echo “* hard nofile 65535” >> /etc/security/limits.conf
“`

– **TCP参数**：
“`bash
echo “net.core.somaxconn = 4096” >> /etc/sysctl.conf
echo “net.ipv4.tcp_max_syn_backlog = 4096” >> /etc/sysctl.conf
echo “net.ipv4.tcp_fin_timeout = 30” >> /etc/sysctl.conf
echo “net.ipv4.tcp_tw_reuse = 1” >> /etc/sysctl.conf
sysctl -p
“`

– **I/O调度**：
“`bash
# 查看当前I/O调度
cat /sys/block/nvme0n1/queue/scheduler
# 设置为none调度器
echo none > /sys/block/nvme0n1/queue/scheduler
“`

## 1. 表结构设计
– **使用合适的数据类型**：选择合适的数据类型，减少存储空间
“`sql
— 优化前：使用VARCHAR(255)存储IP地址
CREATE TABLE test.table (ip VARCHAR(255));
— 优化后：使用INT UNSIGNED存储IP地址
CREATE TABLE test.table (ip INT UNSIGNED);
“`

– **避免使用TEXT/BLOB**：大字段会影响更新删除性能
“`sql
— 优化前：使用TEXT存储大文本
CREATE TABLE test.table (content TEXT);
— 优化后：使用VARCHAR或分离存储
CREATE TABLE test.table (content VARCHAR(1000));
“`

– **合理设置默认值**：为字段设置合适的默认值
“`sql
CREATE TABLE test.table (
id INT PRIMARY KEY AUTO_INCREMENT,
status INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
“`

## 2. 索引设计
– **减少索引数量**：过多的索引会影响更新删除性能
“`sql
— 优化前：多个索引
CREATE INDEX idx_name ON test.table(name);
CREATE INDEX idx_age ON test.table(age);
CREATE INDEX idx_address ON test.table(address);
— 优化后：只保留必要的索引
CREATE INDEX idx_name_age ON test.table(name, age);
“`

– **使用合适的索引类型**：根据查询模式选择合适的索引类型
“`sql
— 使用前缀索引
CREATE INDEX idx_name ON test.table(name(10));
“`

– **避免在频繁更新的列上创建索引**：减少索引维护开销
“`sql
— 避免在频繁更新的status列上创建索引
— 优化前：
CREATE INDEX idx_status ON test.table(status);
— 优化后：不创建索引或使用其他方案
“`

## 3. 分区设计
– **使用分区表**：将数据分散到多个分区，提高更新删除性能
“`sql
— 按时间分区
CREATE TABLE test.table (
id INT PRIMARY KEY,
created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);
“`

– **使用哈希分区**：均匀分布数据，避免热点
“`sql
— 按ID哈希分区
CREATE TABLE test.table (
id INT PRIMARY KEY,
name VARCHAR(50)
) PARTITION BY HASH (id) PARTITIONS 8;
“`

– **使用列表分区**：根据业务逻辑分区
“`sql
— 按状态分区
CREATE TABLE test.table (
id INT PRIMARY KEY,
status INT
) PARTITION BY LIST (status) (
PARTITION p_active VALUES IN (1),
PARTITION p_inactive VALUES IN (0),
PARTITION p_other VALUES IN (2, 3, 4)
);
“`

## 4. 主键设计
– **使用自增主键**：避免热点问题
“`sql
— 使用自增主键
CREATE TABLE test.table (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
“`

– **避免使用UUID作为主键**：会导致数据分布不均匀
“`sql
— 优化前：使用UUID
CREATE TABLE test.table (
id VARCHAR(36) PRIMARY KEY,
name VARCHAR(50)
);
— 优化后：使用自增主键
CREATE TABLE test.table (
id INT PRIMARY KEY AUTO_INCREMENT,
uuid VARCHAR(36),
name VARCHAR(50)
);
“`

## 5. 其他设计建议
– **使用批量更新/删除**：减少网络往返
“`sql
— 批量更新
UPDATE test.table SET status = 1 WHERE id IN (1, 2, 3, 4, 5);
— 批量删除
DELETE FROM test.table WHERE id IN (1, 2, 3, 4, 5);
“`

– **使用事务批量提交**：减少事务开销
“`sql
— 事务批量提交
START TRANSACTION;
UPDATE test.table SET status = 1 WHERE id = 1;
UPDATE test.table SET status = 1 WHERE id = 2;
DELETE FROM test.table WHERE id = 3;
COMMIT;
“`

– **关闭自动提交**：减少事务开销
“`sql
— 关闭自动提交
SET autocommit = 0;
— 执行批量更新/删除
UPDATE test.table SET status = 1 WHERE id IN (1, 2, 3);
DELETE FROM test.table WHERE id IN (4, 5, 6);
— 手动提交
COMMIT;
“`

## 1. 准备工作
– **步骤1**：分析数据特点
– 数据量大小
– 数据结构
– 更新删除频率
– 更新删除范围

– **步骤2**：选择更新删除方法
– 批量更新/删除
– 并行更新/删除
– 分批次更新/删除
– 分区表操作

– **步骤3**：优化Schema
– 优化表结构
– 优化索引设计
– 优化分区策略

## 2. 配置优化
– **步骤1**：调整TiDB配置
– 增加max-connections
– 调整txn-total-size-limit
– 优化tmp-storage-size

– **步骤2**：调整TiKV配置
– 增加block-cache.capacity
– 调整write-buffer-size
– 优化gc参数

– **步骤3**：调整系统配置
– 增加文件描述符限制
– 优化TCP参数
– 调整I/O调度

## 3. 更新删除执行
– **步骤1**：执行批量更新/删除
“`sql
— 批量更新
UPDATE test.table SET status = 1 WHERE id BETWEEN 1 AND 1000;
— 批量删除
DELETE FROM test.table WHERE id BETWEEN 1 AND 1000;
“`

– **步骤2**：使用并行更新/删除
“`bash
# 使用多个线程并行更新/删除
python parallel_update_delete.py
“`

– **步骤3**：分批次更新/删除
“`bash
# 分批次更新/删除
python batch_update_delete.py
“`

– **步骤4**：使用分区表操作
“`sql
— 对特定分区进行更新
UPDATE test.table PARTITION (p2023) SET status = 1 WHERE created_at < '2023-01-01'; -- 对特定分区进行删除 DELETE FROM test.table PARTITION (p2023) WHERE created_at < '2023-01-01'; ``` ## 4. 监控和调优 - **步骤1**：监控更新删除性能 - 更新/删除吞吐量 - 更新/删除延迟 - 资源使用率 - 锁竞争情况 - **步骤2**：分析瓶颈 - 网络瓶颈 - CPU瓶颈 - I/O瓶颈 - 内存瓶颈 - 锁竞争 - 索引维护 - **步骤3**：调整优化策略 - 调整批量大小 - 调整并发度 - 优化更新删除方法 - 调整索引设计 ## 5. 验证结果 - **步骤1**：验证数据完整性 ```sql -- 验证更新结果 SELECT COUNT(*) FROM test.table WHERE status = 1; -- 验证删除结果 SELECT COUNT(*) FROM test.table WHERE id BETWEEN 1 AND 1000; ``` - **步骤2**：验证性能指标 - 更新/删除吞吐量 - 更新/删除延迟 - 资源使用率 - **步骤3**：验证业务正常运行 - 测试查询性能 - 测试其他业务功能

## 1. 批量更新调优
– **调整批量大小**：
– 测试不同批量大小的性能
– 选择最佳批量大小（通常为1000-5000条）

– **使用IN子句**：
“`sql
— 使用IN子句批量更新
UPDATE test.table SET status = 1 WHERE id IN (1, 2, 3, …, 1000);
“`

– **使用范围条件**：
“`sql
— 使用范围条件批量更新
UPDATE test.table SET status = 1 WHERE id BETWEEN 1 AND 1000;
“`

– **使用JOIN更新**：
“`sql
— 使用JOIN更新
UPDATE test.table t1
JOIN test.table2 t2 ON t1.id = t2.id
SET t1.status = t2.status;
“`

## 2. 批量删除调优
– **调整批量大小**：
– 测试不同批量大小的性能
– 选择最佳批量大小（通常为1000-5000条）

– **使用IN子句**：
“`sql
— 使用IN子句批量删除
DELETE FROM test.table WHERE id IN (1, 2, 3, …, 1000);
“`

– **使用范围条件**：
“`sql
— 使用范围条件批量删除
DELETE FROM test.table WHERE id BETWEEN 1 AND 1000;
“`

– **使用TRUNCATE**：
“`sql
— 清空表
TRUNCATE TABLE test.table;
“`

– **使用分区管理**：
“`sql
— 删除整个分区
ALTER TABLE test.table DROP PARTITION p2023;
— 重建分区
ALTER TABLE test.table REORGANIZE PARTITION p2024 INTO (
PARTITION p2024Q1 VALUES LESS THAN (‘2024-04-01’),
PARTITION p2024Q2 VALUES LESS THAN (‘2024-07-01’),
PARTITION p2024Q3 VALUES LESS THAN (‘2024-10-01’),
PARTITION p2024Q4 VALUES LESS THAN (‘2025-01-01′)
);
“`

## 3. 并行更新/删除调优
– **使用多线程**：
“`python
import threading
import pymysql

def update_data(start, end):
conn = pymysql.connect(host=’192.168.1.10′, port=4000, user=’root’, password=’password’, db=’test’)
cursor = conn.cursor()
sql = “UPDATE test.table SET status = 1 WHERE id BETWEEN %s AND %s”
cursor.execute(sql, (start, end))
conn.commit()
cursor.close()
conn.close()

# 创建多个线程
threads = []
batch_size = 10000
total = 100000
for i in range(0, total, batch_size):
t = threading.Thread(target=update_data, args=(i, i + batch_size – 1))
threads.append(t)
t.start()

# 等待所有线程完成
for t in threads:
t.join()
“`

– **调整并发度**：
– 根据CPU核心数和网络带宽调整并发度
– 避免过度并发导致性能下降

– **使用连接池**：
“`java
// 配置连接池
HikariConfig config = new HikariConfig();
config.setJdbcUrl(“jdbc:mysql://192.168.1.10:4000/test”);
config.setUsername(“root”);
config.setPassword(“password”);
config.setMaximumPoolSize(50);
config.setMinimumIdle(20);
HikariDataSource dataSource = new HikariDataSource(config);
“`

## 4. 分批次更新/删除调优
– **计算批次大小**：
– 根据数据量和内存大小计算批次大小
– 避免单次批次过大导致内存不足

– **使用游标**：
“`python
import pymysql

def update_in_batches(batch_size):
conn = pymysql.connect(host=’192.168.1.10′, port=4000, user=’root’, password=’password’, db=’test’)
cursor = conn.cursor()

# 获取最大ID
cursor.execute(“SELECT MAX(id) FROM test.table”)
max_id = cursor.fetchone()[0]

# 分批次更新
for i in range(1, max_id + 1, batch_size):
end_id = min(i + batch_size – 1, max_id)
sql = “UPDATE test.table SET status = 1 WHERE id BETWEEN %s AND %s”
cursor.execute(sql, (i, end_id))
conn.commit()
print(f”Updated batch {i} to {end_id}”)

cursor.close()
conn.close()

# 分批次更新，每批10000条
update_in_batches(10000)
“`

– **监控批次执行**：
– 监控每个批次的执行时间
– 调整批次大小以获得最佳性能

## 5. 其他调优方法
– **禁用Binlog**：
“`sql
— 禁用Binlog
SET SESSION sql_log_bin = 0;
“`

– **调整事务隔离级别**：
“`sql
— 使用读已提交隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
“`

– **使用临时表**：
“`sql
— 创建临时表
CREATE TEMPORARY TABLE temp_ids (id INT);
— 插入需要更新/删除的ID
INSERT INTO temp_ids VALUES (1), (2), (3), …, (1000);
— 使用临时表进行更新
UPDATE test.table t
JOIN temp_ids tmp ON t.id = tmp.id
SET t.status = 1;
— 使用临时表进行删除
DELETE t FROM test.table t
JOIN temp_ids tmp ON t.id = tmp.id;
— 删除临时表
DROP TEMPORARY TABLE temp_ids;
“`

– **优化网络传输**：
– 使用压缩传输
– 减少网络往返
– 使用本地连接

– **优化垃圾回收**：
“`sql
— 手动触发垃圾回收
CALL dbms_stats.garbage_collect(‘test’, ‘table’);
“`

## 1. 准备环境
– **步骤1**：部署TiDB集群
“`bash
tiup cluster deploy fgedudb v6.1.0 topology.yaml –user root -p
tiup cluster start fgedudb
“`

– **步骤2**：创建测试数据库和表
“`sql
CREATE DATABASE test;
USE test;
CREATE TABLE sbtest1 (
id INT PRIMARY KEY,
k INT NOT NULL DEFAULT ‘0’,
c CHAR(120) NOT NULL DEFAULT ”,
pad CHAR(60) NOT NULL DEFAULT ”
);
“`

– **步骤3**：导入测试数据
“`bash
# 使用sysbench导入测试数据
sysbench –db-driver=mysql –mysql-host=192.168.1.10 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=test –table-size=1000000 prepare
“`

## 2. 运行基准测试
– **步骤1**：测试单条更新
“`bash
# 单条更新测试
sysbench –db-driver=mysql –mysql-host=192.168.1.10 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=test –table-size=1000000 –threads=64 –time=60 oltp_write_only run
“`

– **步骤2**：测试批量更新
“`bash
# 批量更新测试
python batch_update_test.py
“`

– **步骤3**：测试单条删除
“`bash
# 单条删除测试
sysbench –db-driver=mysql –mysql-host=192.168.1.10 –mysql-port=4000 –mysql-user=root –mysql-password=password –mysql-db=test –table-size=1000000 –threads=64 –time=60 oltp_delete run
“`

– **步骤4**：测试批量删除
“`bash
# 批量删除测试
python batch_delete_test.py
“`

– **步骤5**：测试并行更新/删除
“`bash
# 并行更新/删除测试
python parallel_update_delete_test.py
“`

– **步骤6**：测试分区表操作
“`bash
# 分区表操作测试
python partition_table_test.py
“`

## 3. 分析测试结果
– **步骤1**：收集测试结果
– 更新/删除吞吐量
– 更新/删除延迟
– 资源使用率
– 锁竞争情况

– **步骤2**：分析瓶颈
– 网络瓶颈
– CPU瓶颈
– I/O瓶颈
– 内存瓶颈
– 锁竞争
– 索引维护

– **步骤3**：比较不同更新删除方法的性能
– 单条更新/删除 vs 批量更新/删除
– 批量更新/删除 vs 并行更新/删除
– 并行更新/删除 vs 分区表操作

## 4. 优化和验证
– **步骤1**：实施优化方案
– 调整批量大小
– 调整并发度
– 优化更新删除方法
– 调整索引设计

– **步骤2**：重新运行基准测试
“`bash
# 重新运行测试
python batch_update_delete_test.py
“`

– **步骤3**：对比优化前后的结果
– 更新/删除吞吐量提升
– 更新/删除延迟降低
– 资源使用率优化
– 锁竞争减少

## 5. 生成报告
– **步骤1**：整理测试数据
– 不同更新删除方法的性能对比
– 不同批量大小的性能表现
– 不同并发度的性能表现
– 分区表 vs 非分区表的性能对比

– **步骤2**：生成性能报告
– 测试环境
– 测试方法
– 测试结果
– 优化建议