今天遇到的问题。联通智算的数据库，针对这个数据库，是现在的优化方案，可供大家参考（以下内容已脱敏）

MySQL 24张表300万条数据查询优化方案

索引优化、查询优化、表结构优化、系统配置优化等多个方面。

一、索引优化

1. 创建合适的索引

   • 为经常出现在 WHERE、JOIN 和 ORDER BY 子句中的列创建索引

   • 示例：CREATE INDEX idx_name ON table_name(column_name)

   • 考虑创建组合索引来优化多条件查询

2. 索引维护

   • 定期使用OPTIMIZE TABLE命令整理索引碎片

   • 使用ANALYZE TABLE更新统计信息

   • 监控索引使用情况，删除冗余索引

3. 覆盖索引

   • 创建包含查询所需所有列的索引，避免回表操作

   • 示例：SELECT column1, column2 FROM table WHERE indexed_column = value

二、查询优化

1. 优化 SQL 语句

   • 避免使用SELECT *，只查询需要的列

   • 减少不必要的 JOIN 操作，能用子查询替代的尽量使用子查询

   • 使用 EXPLAIN 分析查询执行计划，找出性能瓶颈

2. 分页查询优化

   • 使用 LIMIT 进行分页：SELECT * FROM table LIMIT offset, limit

   • 对于深度分页，考虑使用游标分页替代传统分页

3. 查询缓存

   • 对不经常变化的数据使用查询缓存：SELECT SQL_CACHE * FROM table

   • 注意：对于大型高并发系统，查询缓存可能不划算

4. 避免全表扫描

   • 确保查询条件能够利用索引

   • 避免在索引列上使用函数或计算

三、表结构与数据优化

1. 表结构优化

   • 垂直切分：将大表按列拆分为多个小表，减少单表宽度

   • 选择合适的数据类型，避免使用过大的类型

2. 数据分区

   • 对大表进行分区，如按时间或 ID 范围分区

   • 示例：ALTER TABLE table PARTITION BY RANGE (column) (...)

3. 数据归档

   • 将历史数据迁移到归档表，减少主表数据量

   • 实现冷热数据分离，热数据保留在 MySQL，冷数据归档到数据仓库

4. 预计算与中间表

   • 对统计查询创建中间表，定时更新

   • 示例：创建产品统计表并定时更新销售计数

四、系统配置优化

1. MySQL 参数调优

   • 调整innodb_buffer_pool_size(物理内存的 70-80%)

   • 优化 IO 性能：innodb_io_capacity = 2000, innodb_flush_neighbors = 0(SSD 建议关闭)

   • 并发控制：innodb_read_io_threads = 8, innodb_write_io_threads = 4

2. 硬件优化

   • 使用 SSD 硬盘提高 I/O 性能

   • 增加服务器内存容量

   • 升级 CPU 处理能力

3. 架构扩展

   • 水平分表：按用户 ID 哈希或时间范围分表

   • 考虑使用数据库分片技术

   • 整合 Elasticsearch 处理复杂搜索查询

五、日常维护与监控

1. 定期维护

   • 定期执行OPTIMIZE TABLE和ANALYZE TABLE

   • 清理无用数据和索引

2. 性能监控

   • 监控慢查询：SELECT * FROM information_schema.processlist WHERE TIME > 10

   • 检查索引使用情况

3. 持续优化

   • 随着数据增长，提前规划分表策略

   • 定期评估和调整优化策略

优化效果参考案例

1. 订单状态查询：

   • 优化前：4.8 秒 → 添加组合索引后：0.12 秒

2. 用户历史订单分页：

   • 优化前：第 100 页需要 8 秒 → 使用游标分页后：稳定在 0.3 秒内

3. 全表统计：

   • 优化前：直接查询导致 OOM → 分批处理 + 中间表：稳定完成

对于 300 万级数据表的优化，首先要做好索引设计和查询优化，这是性价比最高的方案。随着数据增长，需要提前规划分表策略和架构扩展方案。