MySQL 处理大数据表的 3 种方案，写的太好了，建议收藏！
场景
评估表数据体量
出现问题的原因
如何解决单表数据量太大，查询变慢的问题
方案一：数据表分区
方案二：数据库分表
分区分表的区别
分区分表的联系
分库分表存在的问题
方案三：冷热归档
以上三种方案我们如何选型
场景
当业务数据库表中的数据量越来越大时，您可能会遇到以下情况：
- 数据插入、查询时间变长。
- 随着业务需求的扩展，在表中新增字段会影响性能。
- 表中的数据并非全部都是有效数据，只需查询某个时间区间内的数据。
评估表数据体量
从表容量、磁盘空间、实例容量三个方面评估。
表容量
主要通过表记录数、平均长度、增长量、读写量和总大小进行评估。通常建议 OLTP 表单表数据不超过 2000 万行，大小不超过 15GB。
查询表记录数的 SQL：
select count(*) from table;
select count(1) from table;
但在数据量较大的情况下，可能会超时，因此建议使用以下命令：
use 库名;
show table status like '表名';
或：show table status like '表名'\G;
磁盘空间
查看指定数据库的容量大小：
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
建议数据量占磁盘的使用率保持在 70% 以内。
实例容量
MySQL 基于线程的服务模型，在高并发情况下，单实例可能无法充分利用服务器资源，需根据业务需求调整实例模式。
出现问题的原因
当单表数据量达到数千万甚至上亿时，索引效果会降低。索引的 B+ 树结构层级增加，导致查询时需要更多的磁盘 IO，从而使性能变慢。
如何解决单表数据量太大，查询变慢的问题
可以通过以下三种方案优化数据库：
方案一：数据表分区
表分区可以将数据按条件存储到不同文件，减少查询范围，提高查询效率。分区的优点包括：
- 存储更多数据：可以将大数据表分散到多个分区。
- 优化查询：通过 WHERE 条件的优化，只查询相关分区。
- 并行处理：聚合查询可以并行处理多个分区，提高查询效率。
方案二：数据库分表
水平分表：将数据行分布到多个表中，减小单表数据量。例如，将 4000 万数据拆分为 4 个表，每个表存储 1000 万条数据。
垂直分表：将表中的列拆分。例如，将不常用字段和常用字段拆分到不同的表中，减少查询的字段数。
分区和分表的区别
- 分表：实际分成多个表，分散数据，提高并发能力。
- 分区：仍然是同一张表，但数据分散存储，优化磁盘 IO 性能。
分区和分表的联系
分区和分表可以结合使用。对于大访问量和大数据量的表，可以结合分表和分区方案。
分库分表存在的问题
- 事务管理问题：分库分表后，事务管理复杂，可能需要额外的编程处理。
- 跨库跨表 JOIN 问题：分库后，跨表查询的效率降低，需要多次查询。
- 数据管理负担：需要额外的逻辑处理和数据管理，增加复杂性。
方案三：冷热归档
将业务数据划分为“热数据”和“冷数据”，将不常用的冷数据归档到其他表或库，提升热数据的操作效率。
如何选型
可以根据业务场景选择合适的方案：
- 表分区：适合数据量大，但表结构变化不频繁的场景。
- 数据库分表：适合高并发访问和需要进一步分散数据压力的场景。
- 冷热归档：适合数据有明显冷热区分的场景，如只查询近一个月的数据。
通过这些方案，可以有效优化大数据量表的查询和性能。