Mysql设置

---

1️⃣ 场景分析

• 上位机每 30ms 写入一次 PLC 信号
• 数据库同时写入扫码、日志等数据
• 表可能很大（百万级以上），频繁插入查询
• 现象：数据库表数据大 → 写 PLC 延迟 300ms
• 目标：优化 InnoDB Buffer Pool，减少磁盘 I/O，避免阻塞写 PLC 线程

---

2️⃣ Buffer Pool 核心参数

参数	说明	建议配置
innodb_buffer_pool_size	缓存表数据和索引	服务器总内存的 60~70%，例如服务器 16GB → 10GB
innodb_buffer_pool_instances	分块管理 buffer pool，提高多核性能	≥4（如果 buffer pool ≥ 1GB）
innodb_log_file_size	redo log 文件大小	大事务建议 512MB ~ 1GB，减少磁盘刷写次数
innodb_flush_log_at_trx_commit	日志刷盘策略	2（性能好、数据丢失风险低，适合工业实时场景）
innodb_io_capacity	InnoDB I/O 能力	根据磁盘性能设置，SSD 可 2000-4000
innodb_read_io_threads / innodb_write_io_threads	并行 I/O 线程	4~8（根据 CPU 核心数）

---

3️⃣ 推荐配置示例（16GB 内存服务器）

[mysqld]
# 缓存表数据和索引
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8

# redo log
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2

# I/O 优化
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 其他优化
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

✅ 解释：

1. 10GB buffer pool：足够缓存热点表（如 PLC 写入相关表）
2. 8 个实例：减少多线程访问锁竞争，提高并发
3. 日志文件调大 + flush 2：减少频繁刷盘对 PLC 写入的阻塞
4. O_DIRECT：避免 double buffering，占用操作系统内存
5. innodb_io_capacity 设置为 2000：利用 SSD 高速 I/O

---

4️⃣ 上位机与数据库操作优化

1. PLC 写入线程和数据库操作分离

   PLC写线程：
       只负责发信号 -> 不等待数据库
   数据库写线程：
       独立队列/线程异步处理 -> 批量入库
   

2. 批量入库

   • 对扫码/日志数据，累积 100-500 条再批量插入，减少事务开销
   • 可用存储过程或程序端队列实现

3. 索引优化

   • 经常查询字段如 sn、update_time 必须建索引
   • 避免全表扫描导致 PLC 写入线程阻塞

4. 清理旧数据

   • 定期归档历史数据到历史表
   • 删除非今天或超过 N 天的数据

---

5️⃣ 检查与验证

1. 监控 buffer pool 使用率

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

1. 监控慢查询

SET GLOBAL slow_query_log = 'ON';

1. 测试 PLC 写入延迟

• 删除数据库操作，测 30ms 循环
• 加入优化后，确保大表情况下仍然 30~35ms 以内

---

💡 总结

• Buffer pool 调大 + 分块实例化：保证热点表在内存
• 日志文件优化 + flush=2：减少磁盘刷写对 PLC 写入的阻塞
• 数据库异步操作 + 批量插入：写 PLC 不受数据库阻塞
• 索引和数据归档：避免大表导致查询/插入慢

---

---

1️⃣ 表设计与索引优化

1. 主表设计

   • 大表（如 read_2025_sn）千万级行数时，保证主键是 BIGINT AUTO_INCREMENT
   • 避免使用 VARCHAR 作为主键或聚簇索引（影响 InnoDB 插入性能）

2. 索引设计

   • 常用查询字段必须建索引：

     sn, update_time, feedstock_num
     

   • 组合索引：

     • 查询 WHERE sn=? AND update_time BETWEEN ? AND ? → 建复合索引 (sn, update_time)

   • 避免无用索引（每个索引会增加写入负担）

3. 归档历史数据

   • 大表分为 实时表 + 历史表

   • 例如：

     • read_2025_sn：最近 7 天数据
     • read_2025_sn_history：7 天前的数据

---

2️⃣ 写入优化（上位机场景）

1. 异步数据库操作

   • 上位机 PLC 写线程不直接执行 SQL
   • 数据写入通过 队列或独立线程批量入库

   PLC写线程 -> 队列 -> 批量数据库写入线程
   

2. 批量插入

   • 单条插入慢，累积多条（100~500条）一次插入：

     INSERT INTO read_2025_sn (sn, feedstock_num, ...) VALUES (...), (...), ...;
     

3. 事务优化

   • 避免长事务
   • 写入批量数据一次事务提交，减少事务提交次数

---

3️⃣ MySQL 配置优化

核心参数（InnoDB）

参数	作用	推荐值/策略
innodb_buffer_pool_size	缓存表数据和索引	服务器总内存 60~70%
innodb_buffer_pool_instances	分块管理，减少多核竞争	≥4，如果 buffer pool ≥ 1GB
innodb_log_file_size	redo log 大小	512MB~1GB，大事务减少刷盘
innodb_flush_log_at_trx_commit	日志刷盘策略	2（性能好，数据丢失风险低）
innodb_io_capacity	I/O 上限	SSD 可 2000~4000
innodb_file_per_table	每表独立文件	ON，利于表空间管理

---

4️⃣ 查询优化

1. 慢查询日志

   SET GLOBAL slow_query_log = 'ON';
   SHOW VARIABLES LIKE 'slow_query_log_file';
   

   • 定期分析慢查询，优化索引或 SQL

2. 避免 SELECT * 查询

   • 只查询必要字段，减少 I/O

3. 分页查询

   • 大表分页不要用 LIMIT offset, size → offset 大时慢

   • 改用索引范围分页：

     WHERE id > last_id ORDER BY id LIMIT 1000
     

---

5️⃣ 数据归档与清理

1. 定期清理历史数据

   -- DELETE FROM read_2025_sn WHERE update_time < NOW() - INTERVAL 30 DAY;
   

   或者更安全的 分表归档：

   -- INSERT INTO read_2025_sn_history SELECT * FROM read_2025_sn WHERE update_time < '2025-10-01';
   -- DELETE FROM read_2025_sn WHERE update_time < '2025-10-01';
   

2. 自动删除 binlog

   • 防止磁盘被日志填满：

     PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;
     

   • 或配置：

     expire_logs_days = 7
     

---

6️⃣ 磁盘和硬件优化

1. 使用 SSD

   • InnoDB 对随机读写敏感

2. 独立数据盘 + 日志盘

   • Redo log 写入单独磁盘，避免干扰数据 I/O

3. RAID 或 NVMe

   • 高并发场景推荐 NVMe SSD

---

7️⃣ 总结思路

1. 上位机写 PLC 与数据库操作分离（异步、队列、批量）
2. 表和索引优化（热点字段索引 + 分表归档）
3. InnoDB 配置优化（buffer pool 大、日志文件大、flush=2）
4. 查询优化 + 慢查询分析
5. 数据归档与清理（定期删除旧数据）
6. 硬件优化（SSD、独立日志盘）

---