回答

当 MySQL 单表数据超过 5GB，数据条数达到 1 亿条时，性能可能会显著下降。为了优化查询和操作效率，可以采取以下策略：

1. 分区表（Partitioning）

分区表将大表拆分为多个小表，每个分区可以独立存储和查询，从而提高性能。

实现方式

CREATE TABLE large_table (
    id INT NOT NULL,
    created_at DATETIME,
    data VARCHAR(255)
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

优点减少单表数据量，提高查询效率。支持按分区删除数据，提升删除性能。适用场景数据有明显的时间或范围特征（如按年、月分区）。2. 索引优化

合理的索引可以显著提高查询性能，但过多的索引会影响写入性能。

实现方式单列索引：

CREATE INDEX idx_column ON large_table(column_name);

复合索引：

CREATE INDEX idx_multi_column ON large_table(column1, column2);

注意事项避免在低选择性列（如性别）上创建索引。定期分析查询语句，确保索引被有效使用。3. 分库分表

当单表数据量过大时，可以将数据拆分到多个数据库或表中。

实现方式垂直分表：将不常用的列拆分到另一张表。水平分表：按某种规则（如用户 ID 取模）将数据拆分到多张表。优点减少单表数据量，提升查询和写入性能。支持分布式存储和查询。适用场景数据量极大，单表无法满足性能要求。4. 归档历史数据

将不常用的历史数据归档到其他表或存储中，减少主表的数据量。

实现方式

-- 创建归档表
CREATE TABLE large_table_archive LIKE large_table;

-- 迁移历史数据
INSERT INTO large_table_archive
SELECT * FROM large_table WHERE created_at < '2020-01-01';

-- 删除主表中的历史数据
DELETE FROM large_table WHERE created_at < '2020-01-01';

优点减少主表数据量，提升查询性能。历史数据仍可查询，不影响业务需求。5. 优化查询语句

避免全表扫描和复杂查询，优化 SQL 语句。

实现方式使用 EXPLAIN 分析查询语句，确保索引被使用。避免 SELECT *，只查询需要的列。使用 LIMIT 限制返回的行数。示例

EXPLAIN SELECT * FROM large_table WHERE column_name = 'value';

6. 调整 MySQL 配置

根据硬件资源和业务需求，优化 MySQL 配置。

关键参数innodb_buffer_pool_size：设置为物理内存的 70%-80%，提高缓存命中率。innodb_log_file_size：增大日志文件大小，提升写入性能。query_cache_size：启用查询缓存，减少重复查询的开销。示例

SET GLOBAL innodb_buffer_pool_size = 4G;
SET GLOBAL innodb_log_file_size = 1G;

7. 使用缓存

将热点数据缓存到 Redis 或 Memcached 中，减少数据库查询压力。

实现方式使用 Redis 缓存查询结果。设置合理的缓存过期时间。示例

import redis

# 连接 Redis
r = redis.Redis(host='localhost', port=6379, db=0)

# 缓存查询结果
key = 'query_result'
result = r.get(key)
if not result:
    result = execute_query()
    r.set(key, result, ex=3600)  # 缓存 1 小时

8. 升级硬件

如果软件优化无法满足需求，可以考虑升级硬件。

建议使用 SSD 硬盘，提升 I/O 性能。增加内存，提高缓存命中率。使用多核 CPU，提升并发处理能力。9. 使用读写分离

将读操作和写操作分离到不同的数据库实例，减轻主库压力。

实现方式使用 MySQL 主从复制，将读请求分发到从库。使用中间件（如 MyCat、ProxySQL）实现读写分离。优点提升读性能，减轻主库压力。提高系统的可用性和扩展性。10. 定期维护

定期执行表优化和维护操作，保持数据库性能。

实现方式优化表：

OPTIMIZE TABLE large_table;

分析表：

ANALYZE TABLE large_table;

重建索引：

ALTER TABLE large_table ENGINE=InnoDB;

总结

针对 MySQL 单表数据超过 5GB、数据条数 1 亿条的情况，可以采取以下优化策略：

使用分区表或分库分表，减少单表数据量。优化索引，提升查询性能。归档历史数据，减少主表数据量。优化查询语句，避免全表扫描。调整 MySQL 配置，提升性能。使用缓存，减少数据库查询压力。升级硬件，提升 I/O 和并发能力。使用读写分离，减轻主库压力。定期维护，保持数据库性能。

根据具体业务需求选择合适的优化方案，并结合多种策略实现最佳效果。