mysql单表过亿数据，怎样优化查询速度，且不停服-优快云博客

面对单表1亿多条数据导致的访问缓慢问题，要在不停机的情况下进行优化，确实是个挑战。下面梳理了一套从紧急止血到中期优化，再到长期架构升级的完整方案。

先做最紧急的索引优化

当务之急是让核心查询快起来，同时避免对线上服务造成干扰。

分析慢查询：首先使用 SHOW PROCESSLIST;或开启慢查询日志（slow_query_log），找到最消耗资源的SQL语句。然后通过 EXPLAIN命令分析其执行计划，确认是否走了合适的索引。
在线创建索引：切勿直接使用 ALTER TABLE添加索引，这会导致表锁，阻塞写入。请使用 pt-online-schema-change 工具。
```
# 示例：为 users 表的 email 字段添加索引
pt-online-schema-change --alter "ADD INDEX idx_email (email)" D=your_database,t=users --execute
```
这个工具的工作原理是创建一个影子表，将数据分批拷贝到新表（带索引），最后通过原子操作完成切换，对应用影响极小。
优化现有索引：
- 复合索引：为高频查询条件创建复合索引，并遵循最左前缀原则。例如，对于 WHERE status = 'active' AND create_time > '...'的查询，建立 (status, create_time)的复合索引比两个单列索引更高效。
- 覆盖索引：如果查询只涉及部分字段，可以建立包含这些字段的索引，避免回表。例如 SELECT id, name FROM users WHERE status = 1，可以建立 (status, id, name)索引。
- 清理无效索引：使用 pt-index-usage等工具分析索引使用情况，删除那些从未被使用或重复的索引，以减少更新开销。

中期策略：从数据和查询入手

索引优化后，进一步从数据组织和查询方式上深挖潜力。

数据归档与分区：
- 数据归档：识别并归档冷数据（如一年前的订单日志）。可以定期将冷数据迁移到历史表，大幅减少主表体积。这个过程可以通过定时任务在低峰期分批完成。
- 表分区：如果数据有自然边界（如按时间），可以使用分区表。例如，按月份分区后，查询某个月的数据只需扫描一个分区，效率极高。但分区表有其复杂性，需谨慎使用。
查询语句重构：
- 避免 SELECT *：只获取需要的字段，减少网络传输和数据解析的开销。
- 优化分页：LIMIT 1000000, 10这种深度分页会扫描大量数据然后丢弃。可改用基于游标的分页（WHERE id > ? LIMIT 10）或延迟关联技巧。
- 避免索引失效的写法：例如，避免在 WHERE子句中对索引字段使用函数（如 WHERE DATE(create_time) = ...），这会导致索引失效。
引入缓存：
- 使用 Redis 或 Memcached 缓存热点数据或复杂查询的结果。对于不常变更的配置类数据或用户会话信息，缓存效果尤其显著，能直接减少数据库访问。

长期架构解决方案

当数据持续增长，单一数据库实例终究会遇到瓶颈，这时需要考虑架构层面的扩展。

读写分离：
- 采用一主多从的架构。主库负责处理写操作，多个从库负责读操作。应用程序通过中间件或智能数据源将读写请求分发到不同的数据库实例上，有效分担主库压力。MySQL本身的主从复制功能可以较好地支持此方案。
分库分表：
- 这是处理亿级数据量的终极方案，但复杂度和成本最高。
- 水平分表：根据分片键（如用户ID），将一张大表的数据分布到多个数据库的多个表中。
- 实施要点：分片策略的选择（范围、哈希等）至关重要。这通常需要引入 ShardingSphere 或 Vitess 等中间件来管理，应用程序改造量大，并会引入跨分片查询、分布式事务等新问题。

优化实施流程与监控

为了让你更清晰地把握整个优化过程，以下流程图概括了从评估到执行的完整路径及关键监控点。

flowchart TD
    A[开始优化1亿+记录表] --> B{评估阶段}
    
    B --> C[分析慢查询与EXPLAIN]
    B --> D[评估数据冷热<br>与访问模式]
    
    C --> E[紧急优化<br>使用pt-online-schema-change<br>创建索引]
    D --> F[中期优化<br>数据归档<br>查询重构<br>引入缓存]
    
    E --> G{是否解决?}
    F --> G
    
    G -- 是 --> H[持续监控与迭代]
    G -- 否 --> I[长期架构升级<br>读写分离<br>分库分表]
    
    I --> H
    
    subgraph J[全程监控预警]
        K[QPS/TPS]
        L[查询延迟]
        M[连接数 & 锁等待]
    end
    
    H --> J

在整个优化过程中，监控是确保成功的眼睛。请密切关注以下指标：