## 1. 设置并行查询参数
— 设置最大并行度
SET GLOBAL tidb_max_parallelism = 8;

— 设置执行器并发度
SET GLOBAL tidb_executor_concurrency = 8;

— 设置哈希连接并发度
SET GLOBAL tidb_hash_join_concurrency = 4;

— 设置索引查找并发度
SET GLOBAL tidb_index_lookup_concurrency = 4;

## 2. 会话级并行查询配置
— 会话级设置最大并行度
SET SESSION tidb_max_parallelism = 4;

— 会话级设置执行器并发度
SET SESSION tidb_executor_concurrency = 4;

## 3. SQL级并行查询配置
— SQL级设置并行度
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */ * FROM fgedudb.fgedu_users;

## 4. 查看并行查询配置
SHOW VARIABLES LIKE ‘tidb_max_parallelism’;
SHOW VARIABLES LIKE ‘tidb_executor_concurrency’;
SHOW VARIABLES LIKE ‘tidb_hash_join_concurrency’;
SHOW VARIABLES LIKE ‘tidb_index_lookup_concurrency’;

## 1. 查看并行查询执行计划
EXPLAIN SELECT * FROM fgedudb.fgedu_users WHERE age > 30;

## 2. 查看并行查询执行状态
SHOW PROCESSLIST;

## 3. 查看并行查询性能指标
SELECT * FROM information_schema.processlist WHERE command = ‘Query’;

## 4. 查看系统资源使用情况
— 查看CPU使用情况
TOP

— 查看内存使用情况
FREE -H

— 查看磁盘I/O使用情况
IOTOP

— 查看网络使用情况
NETSTAT -TUNAP

## 1. 修改TiDB配置文件
$ cat > tidb.toml << EOF [performance] max-procs = 8 tidb-max-parallelism = 8 tidb-executor-concurrency = 8 tidb-hash-join-concurrency = 4 tidb-index-lookup-concurrency = 4 [memory] oom-action = "log" EOF ## 2. 重启TiDB服务 $ tiup cluster restart fgedu-tidb-cluster -R tidb ## 3. 验证配置 $ mysql -h 192.168.1.100 -P 4000 -u root -p SHOW VARIABLES LIKE 'tidb_max_parallelism'; SHOW VARIABLES LIKE 'tidb_executor_concurrency'; ## 4. 配置资源组 -- 创建资源组 CREATE RESOURCE GROUP fgedu_high_concurrency WITH ( RU_PER_SEC = 1000, PRIORITY = HIGH, BURSTABLE = TRUE ); -- 绑定用户到资源组 ALTER USER 'fgedu'@'%' RESOURCE GROUP fgedu_high_concurrency;

## 1. CPU配置
– 选择多核CPU，如Intel Xeon E5/E7或AMD EPYC系列
– CPU核心数：根据并行度需求选择，推荐8核以上
– CPU频率：选择高频率CPU，提高单线程性能

## 2. 内存配置
– 内存容量：根据数据量和并行度需求选择，推荐16GB以上
– 内存类型：选择DDR4或DDR5内存，提高内存带宽
– 内存通道：选择多通道内存，提高内存访问速度

## 3. 存储配置
– 存储类型：使用SSD存储，提高I/O性能
– 存储容量：根据数据量选择，推荐500GB以上
– RAID级别：使用RAID 10，提高存储性能和可靠性

## 4. 网络配置
– 网络类型：使用千兆或万兆以太网
– 网络设备：使用高性能交换机和网卡
– 网络拓扑：优化网络拓扑，减少网络延迟

## 1. 查询语句优化
— 优化前
SELECT * FROM fgedudb.fgedu_users u JOIN fgedudb.fgedu_orders o ON u.id = o.user_id WHERE u.age > 30;

— 优化后
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */ u.id, u.name, o.amount
FROM fgedudb.fgedu_users u
JOIN fgedudb.fgedu_orders o ON u.id = o.user_id
WHERE u.age > 30;

## 2. 索引优化
— 创建合适的索引
CREATE INDEX idx_age ON fgedudb.fgedu_users (age);
CREATE INDEX idx_user_id ON fgedudb.fgedu_orders (user_id);

## 3. 表结构优化
— 分区表优化
CREATE TABLE fgedudb.fgedu_orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
order_date DATETIME
) PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

## 4. 统计信息优化
— 更新统计信息
ANALYZE TABLE fgedudb.fgedu_users;
ANALYZE TABLE fgedudb.fgedu_orders;

## 5. 执行计划优化
— 查看执行计划
EXPLAIN SELECT /*+ SET_VAR(tidb_max_parallelism=4) */ * FROM fgedudb.fgedu_users WHERE age > 30;

— 优化执行计划
SELECT /*+ USE_INDEX(u, idx_age) */ * FROM fgedudb.fgedu_users u WHERE u.age > 30;

## 1. 操作系统优化
— 关闭透明大页
echo never > /sys/kernel/mm/transparent_hugepage/enabled

— 调整内核参数
cat > /etc/sysctl.conf << EOF net.core.somaxconn = 65535 net.ipv4.tcp_max_syn_backlog = 65535 net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_keepalive_time = 1200 net.ipv4.tcp_max_tw_buckets = 5000 net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 0 net.ipv4.ip_local_port_range = 1024 65535 fs.file-max = 65536 EOF -- 应用内核参数 sysctl -p ## 2. 内存管理优化 -- 调整内存分配策略 echo 1 > /proc/sys/vm/overcommit_memory

— 调整脏页写入策略
echo 10 > /proc/sys/vm/dirty_background_ratio
echo 20 > /proc/sys/vm/dirty_ratio

## 3. 存储优化
— 调整I/O调度器
echo deadline > /sys/block/sda/queue/scheduler

— 关闭磁盘预读
echo 0 > /sys/block/sda/queue/read_ahead_kb

## 4. 网络优化
— 调整网络缓冲区
cat > /etc/sysctl.conf << EOF net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 EOF -- 应用网络参数 sysctl -p

## 案例1：大数据量聚合查询

### 步骤1：创建测试表
CREATE TABLE fgedudb.fgedu_sales (
id INT PRIMARY KEY,
product_id INT,
amount DECIMAL(10,2),
sale_date DATETIME
);

### 步骤2：插入测试数据
INSERT INTO fgedudb.fgedu_sales (id, product_id, amount, sale_date)
VALUES
(1, 1, 100.00, ‘2023-01-01’),
(2, 2, 200.00, ‘2023-01-02’),
(3, 1, 150.00, ‘2023-01-03’),
(4, 2, 300.00, ‘2023-01-04’),
(5, 3, 250.00, ‘2023-01-05’);

### 步骤3：使用并行查询
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */
product_id,
SUM(amount) AS total_amount
FROM fgedudb.fgedu_sales
GROUP BY product_id
ORDER BY total_amount DESC;

## 案例2：多表连接查询

### 步骤1：创建测试表
CREATE TABLE fgedudb.fgedu_products (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10,2)
);

### 步骤2：插入测试数据
INSERT INTO fgedudb.fgedu_products (id, name, price)
VALUES
(1, ‘product1’, 50.00),
(2, ‘product2’, 100.00),
(3, ‘product3’, 150.00);

### 步骤3：使用并行查询
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */
p.name,
SUM(s.amount) AS total_amount
FROM fgedudb.fgedu_sales s
JOIN fgedudb.fgedu_products p ON s.product_id = p.id
GROUP BY p.name
ORDER BY total_amount DESC;

## 案例3：复杂分析查询

### 步骤1：使用并行查询
SELECT /*+ SET_VAR(tidb_max_parallelism=8) */
DATE_FORMAT(sale_date, ‘%Y-%m’) AS month,
product_id,
SUM(amount) AS monthly_total,
AVG(amount) AS average_amount,
COUNT(*) AS sale_count
FROM fgedudb.fgedu_sales
GROUP BY DATE_FORMAT(sale_date, ‘%Y-%m’), product_id
ORDER BY month, monthly_total DESC;

## 案例1：并行查询性能优化

### 优化前
SELECT
product_id,
SUM(amount) AS total_amount
FROM fgedudb.fgedu_sales
GROUP BY product_id
ORDER BY total_amount DESC;

— 执行时间：10秒

### 优化后
SELECT /*+ SET_VAR(tidb_max_parallelism=8) */
product_id,
SUM(amount) AS total_amount
FROM fgedudb.fgedu_sales
GROUP BY product_id
ORDER BY total_amount DESC;

— 执行时间：2秒

## 案例2：索引优化

### 优化前
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */
*
FROM fgedudb.fgedu_sales
WHERE sale_date BETWEEN ‘2023-01-01’ AND ‘2023-01-31’;

— 执行时间：5秒

### 优化后
— 创建索引
CREATE INDEX idx_sale_date ON fgedudb.fgedu_sales (sale_date);

SELECT /*+ SET_VAR(tidb_max_parallelism=4) */
*
FROM fgedudb.fgedu_sales
WHERE sale_date BETWEEN ‘2023-01-01’ AND ‘2023-01-31’;

— 执行时间：1秒

## 案例3：表结构优化

### 优化前
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */
DATE_FORMAT(sale_date, ‘%Y-%m’) AS month,
SUM(amount) AS monthly_total
FROM fgedudb.fgedu_sales
GROUP BY DATE_FORMAT(sale_date, ‘%Y-%m’);

— 执行时间：8秒

### 优化后
— 创建分区表
CREATE TABLE fgedudb.fgedu_sales_partitioned (
id INT PRIMARY KEY,
product_id INT,
amount DECIMAL(10,2),
sale_date DATETIME
) PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

— 插入数据
INSERT INTO fgedudb.fgedu_sales_partitioned SELECT * FROM fgedudb.fgedu_sales;

— 查询
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */
DATE_FORMAT(sale_date, ‘%Y-%m’) AS month,
SUM(amount) AS monthly_total
FROM fgedudb.fgedu_sales_partitioned
GROUP BY DATE_FORMAT(sale_date, ‘%Y-%m’);

— 执行时间：3秒

## 案例1：并行度调优

### 步骤1：测试不同并行度
— 并行度=2
SELECT /*+ SET_VAR(tidb_max_parallelism=2) */
SUM(amount) AS total_amount
FROM fgedudb.fgedu_sales;

— 并行度=4
SELECT /*+ SET_VAR(tidb_max_parallelism=4) */
SUM(amount) AS total_amount
FROM fgedudb.fgedu_sales;

— 并行度=8
SELECT /*+ SET_VAR(tidb_max_parallelism=8) */
SUM(amount) AS total_amount
FROM fgedudb.fgedu_sales;

### 步骤2：选择最优并行度
— 根据测试结果选择最优并行度
— 例如：并行度=4时性能最佳

## 案例2：资源组调优

### 步骤1：创建资源组
CREATE RESOURCE GROUP fgedu_analytics WITH (
RU_PER_SEC = 2000,
PRIORITY = HIGH,
BURSTABLE = TRUE
);

### 步骤2：绑定用户到资源组
ALTER USER ‘fgedu_analyst’@’%’ RESOURCE GROUP fgedu_analytics;

### 步骤3：测试性能
— 使用资源组
SELECT /*+ SET_VAR(tidb_max_parallelism=8) */
SUM(amount) AS total_amount
FROM fgedudb.fgedu_sales;

## 案例3：系统参数调优

### 步骤1：调整系统参数
— 修改TiDB配置文件
$ cat > tidb.toml << EOF [performance] max-procs = 8 tidb-max-parallelism = 8 tidb-executor-concurrency = 8 tidb-hash-join-concurrency = 4 tidb-index-lookup-concurrency = 4 [memory] oom-action = "log" memory-usage-limit = "80%" EOF ### 步骤2：重启TiDB服务 $ tiup cluster restart fgedu-tidb-cluster -R tidb ### 步骤3：测试性能 SELECT /*+ SET_VAR(tidb_max_parallelism=8) */ SUM(amount) AS total_amount FROM fgedudb.fgedu_sales;