数据大表的性能优化

Grazy
26 阅读6分钟

一、为什么大数据表性能会变慢

1. IO 瓶颈

表的数据都是存储在磁盘上的，数据库的查询通常就会涉及到数据块的读取。

当数据量很大的时候，单次查询可能需要从多个磁盘中读取大量的数据，磁盘的读写数据就会限制数据的查询性能。

2. 索引失效或没有索引

如果查询中没有命中索引，那么就会执行合理使用索引 全表扫描 ，把数据表中的全部记录都扫描一遍。在大数据表的情况下，那么查询速度就会出现肉眼可见的下降。

例如：在营业厅大屏项目中，ETC使用量表中统计整个省的ETC使用记录，数据表的数据达到 6000 万条。现在需要查询 2025-05-02 一天的数据。（索引未命中情况）

select count(1) from db_ETC_use where Date(use_Time) = '2025-05-02'

这里用了DATE()函数，数据库需要对所有记录的use_Time字段进行计算，导致索引失效，最终是使用到全记录扫描。

3. 深度分页

分页查询是大表中很常见的场景，但深度分页（比如第1000页之后）会导致性能问题。

即使你只需要10条数据，但数据库仍然需要先扫描出前面所有的记录。然后再取后面满足条件的数据，如此，随着页码的增加，性能会越来越差

4. 锁资源的竞争

在大数据量的表中，锁竞争对性能的影响更显著，而小表通常受影响较小的原因主要与并发操作规模、锁的粒度、事务执行时间以及资源争用概率等因素相关。小表因数据量少、锁持有时间短、争用概率低，通常不易成为瓶颈

（1）更高的并发操作频率

  • 场景：大表通常存储核心业务数据（如订单、日志），会被频繁读写。
  • 问题：高并发下，多个事务同时修改不同行或相同行的概率更高，锁请求密集，竞争加剧。

（2） 锁粒度与范围

  • 行级锁：大表通常使用行级锁（如InnoDB），但若事务需要修改多行（例如范围查询WHERE status='pending'），可能锁定大量行甚至间隙（Gap Lock）。
  • 锁升级风险：某些数据库（如SQL Server）在行锁过多时可能升级为页锁或表锁，阻塞所有操作。

（3） 长事务问题

  • 大事务处理：对大表的批量更新、统计等操作可能长时间持有锁。
  • 连锁效应：长事务未提交时，后续事务需等待，延迟累积导致吞吐量骤降。
5. 字段类型不合适

（1）单行数据体积

  • 例如：
    • INT（4字节） vs BIGINT（8字节） → 存储数字时，若数值范围在INT内，使用BIGINT会浪费50%空间。
    • CHAR(100)（固定100字节） vs VARCHAR(100)（实际数据长度+1~2字节） → 存储短字符串时，CHAR可能浪费空间。
  • 影响结果：
    • 单表总数据量越大，磁盘IO压力越高。
    • 内存缓冲池（如InnoDB Buffer Pool）能缓存的数据页减少，缓存命中率下降。

（2）索引体积

索引的本质是有序的数据结构（如B+树），字段类型越大，索引体积越大。

  • 例如：
    • 主键为VARCHAR(100) vs INT → 前者每个索引条目多占用约96字节。
  • 影响结果：
    • 索引树层级增加，查询时需遍历更多节点，降低索引效率。
    • 索引文件占用更多磁盘和内存空间。

（3）索引效率

  • 索引扫描速度：
    • 字段类型越小，单个索引条目体积越小，单个数据页可容纳更多索引条目。
  • 例如：
    • 若一个索引页大小为16KB，存储INT类型可存约4096条，存储BIGINT则减少一半。
  • 影响结果：
    • 减少磁盘IO次数，加快索引扫描速度。

二、具体优化方式

1. 合理的数据类型

数据库字段的类型会影响到存储的大小和查询的性能。

  • 能用INT的不要用BIGINT。
  • 能用VARCHAR(100)的不要用TEXT。
  • 时间字段建议用TIMESTAMP或DATETIME，不要用CHAR或VARCHAR来存时间。
2. SQL语句的优化
  • 避免使用 select * 来查询数据，按照业务所需的字段来获取
  • 避免使用 超过 3 张表的 JOIN ，使用 JOIN 的时候尽量使用 小表驱动大表
3. 合理使用索引
  • 为高频查询的字段创建索引，比如主键、外键、查询条件字段。在多条件的情况下，考虑创建联合索引，但是要注意最左匹配原则。
  • 避免索引失效。例如：对索引字段使用函数或运算、隐式类型转换、左模糊查询...
4. 深度分页优化

方案一 （推荐）：

使用 游标分页 的方式。如果主键的ID是自增的，在查询请求后保存该批次记录的ID最大值，在下次调用时携带上一个条件 ID > 最大Id 作为过滤条件，然后再使用 limit 获取对应的记录数。例如：SELECT * FROM t_order WHERE id > 100000 LIMIT 10。

方案二 （推荐）：

使用 范围查询 的方式。确定 ID 是自增的前提下，用户根据ID范围进行分页是比较好的解决方案，例如：SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id LImit 10。

方案三 （应急过渡方案）：

使用 子查询 + 索引覆盖 的方式，例如：SELECT * FROM t_user as t1, (SELECT id FROM t_user LIMIT 999999,20) as t2 WHERE t1.id = t2.id (子查询内只查询ID, 使用到索引覆盖)。数据量过大时，客户端仍有OOM风险。

方案四：

使用 Elasticsearch 的search_after代替LIMIT深分页。

5. 分库分表

垂直拆分

当表中字段过多，某些字段并不是经常查询的，可以将表按照业务逻辑拆分为多个小表。

水平拆分

当单表的数据量过大时，可以按一定规则拆分到多张表中。拆分后每张表的数据量大幅减少，查询性能会显著提升。

分库

当单表拆分后仍无法满足性能需求，可以通过分库分表将数据分散到多个数据库中。

常见的分库分表规则：

  • 按用户ID取模。
  • 按时间分区。

例如： 根据成员ID取出表的后缀，用来匹配对应的库表位置 image-20250502220036536.png

6. 使用中间件分担数据库压力
  • 对高频查询的数据可以存储到Redis中，减少对数据库的直接访问。
  • 高并发写入时，可以将写操作放入消息队列（如Kafka），然后异步批量写入数据库，减轻数据库压力。
avatar
文章
阅读
粉丝