首页 > 其他分享 >千万级的大表，如何做性能调优

千万级的大表，如何做性能调优

时间：2025-01-20 22:32:57浏览次数：1  
标签：orders 千万级 order 调优 user time 查询 id 大表

https://www.cnblogs.com/12lisu/p/18680990

 

前言

大表优化是一个老生常谈的话题，但随着业务规模的增长，总有人会“中招”。

很多小伙伴的数据库在刚开始的时候表现良好，查询也很流畅，但一旦表中的数据量上了千万级，性能问题就开始浮现，查询慢、写入卡、分页拖沓、甚至偶尔直接宕机。这

时大家可能会想，是不是数据库不行？是不是需要升级到更强的硬件？

其实很多情况下，根本问题在于没做好优化。

今天，我们就从问题本质讲起，逐步分析大表常见的性能瓶颈，以及如何一步步优化。

我最近开源了一个基于 SpringBoot+Vue+uniapp 的商城项目，里面的技术亮点挺多的，欢迎访问和star。[https://gitee.com/dvsusan/susan_mall]

一、为什么大表会慢？

在搞优化之前，先搞清楚大表性能问题的根本原因。数据量大了，为什么数据库就慢了？

1. 磁盘IO瓶颈

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

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

举例：

假设有一张订单表orders，里面存了5000万条数据，你想要查询某个用户的最近10条订单：

SELECT * FROM orders WHERE user_id = 123 ORDER BY order_time DESC LIMIT 10;

如果没有索引，数据库会扫描整个表的所有数据，再进行排序，性能肯定会拉胯。

2. 索引失效或没有索引

如果表的查询没有命中索引，数据库会进行全表扫描（Full Table Scan），也就是把表里的所有数据逐行读一遍。

这种操作在千万级别的数据下非常消耗资源，性能会急剧下降。

举例：

比如你在查询时写了这样的条件：

SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';

这里用了DATE()函数，数据库需要对所有记录的order_time字段进行计算，导致索引失效。

3. 分页性能下降

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

即使你只需要10条数据，但数据库仍然需要先扫描出前面所有的记录。

举例：

查询第1000页的10条数据：

SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

这条SQL实际上是让数据库先取出前9990条数据，然后丢掉，再返回后面的10条。

随着页码的增加，查询的性能会越来越差。

4. 锁争用

在高并发场景下，多个线程同时对同一张表进行增删改查操作，会导致行锁或表锁的争用，进而影响性能。

二、性能优化的总体思路

性能优化的本质是减少不必要的IO、计算和锁竞争，目标是让数据库尽量少做“无用功”。

优化的总体思路可以总结为以下几点：

  1. 表结构设计要合理：尽量避免不必要的字段，数据能拆分则拆分。
  2. 索引要高效：设计合理的索引结构，避免索引失效。
  3. SQL要优化：查询条件精准，尽量减少全表扫描。
  4. 分库分表：通过水平拆分、垂直拆分减少单表数据量。
  5. 缓存和异步化：减少对数据库的直接压力。

接下来，我们逐一展开。

三、表结构设计优化

表结构是数据库性能优化的基础，设计不合理的表结构会导致后续的查询和存储性能问题。

1. 精简字段类型

字段的类型决定了存储的大小和查询的性能。

  • 能用INT的不要用BIGINT。
  • 能用VARCHAR(100)的不要用TEXT。
  • 时间字段建议用TIMESTAMP或DATETIME，不要用CHAR或VARCHAR来存时间。

举例：

-- 不推荐
CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    order_status VARCHAR(255),
    remarks TEXT
);

-- 优化后
CREATE TABLE orders (
    id BIGINT,
    user_id INT UNSIGNED,
    order_status TINYINT, -- 状态用枚举表示
    remarks VARCHAR(500) -- 限制最大长度
);

这样可以节省存储空间，查询时也更高效。

2. 表拆分：垂直拆分与水平拆分

垂直拆分

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

示例：
将订单表分为两个表：orders_basic 和 orders_details。

-- 基本信息表
CREATE TABLE orders_basic (
    id BIGINT PRIMARY KEY,
    user_id INT UNSIGNED,
    order_time TIMESTAMP
);

-- 详情表
CREATE TABLE orders_details (
    id BIGINT PRIMARY KEY,
    remarks VARCHAR(500),
    shipping_address VARCHAR(255)
);

水平拆分

当单表的数据量过大时，可以按一定规则拆分到多张表中。

示例：
假设我们按用户ID对订单表进行水平拆分：

orders_0 -- 存user_id % 2 = 0的订单
orders_1 -- 存user_id % 2 = 1的订单

拆分后每张表的数据量大幅减少，查询性能会显著提升。

四、索引优化

索引是数据库性能优化的“第一杀器”，但很多人对索引的使用并不熟悉，导致性能不升反降。

1. 创建合适的索引

为高频查询的字段创建索引，比如主键、外键、查询条件字段。

示例：

CREATE INDEX idx_user_id_order_time ON orders (user_id, order_time DESC);

上面的复合索引可以同时加速user_id和order_time的查询。

2. 避免索引失效

  • 别对索引字段使用函数或运算。
    错误：

    SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';

    优化：

    SELECT * FROM orders WHERE order_time >= '2023-01-01 00:00:00'
  AND order_time < '2023-01-02 00:00:00';

  • 注意隐式类型转换。
    错误：

    SELECT * FROM orders WHERE user_id = '123';

    优化：

    SELECT * FROM orders WHERE user_id = 123;

五、SQL优化

1. 减少查询字段

只查询需要的字段，避免SELECT *。

-- 错误
SELECT * FROM orders WHERE user_id = 123;

-- 优化
SELECT id, order_time FROM orders WHERE user_id = 123;

2. 分页优化

深度分页时，使用“延迟游标”的方式避免扫描过多数据。

-- 深分页（性能较差）
SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

-- 优化：使用游标
SELECT * FROM orders WHERE order_time < '2023-01-01 12:00:00'
  ORDER BY order_time DESC LIMIT 10;

六、分库分表

1. 水平分库分表

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

常见的分库分表规则：

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

七、缓存与异步化

1. 使用Redis缓存热点数据

对高频查询的数据可以存储到Redis中，减少对数据库的直接访问。

示例：

// 从缓存读取数据
String result = redis.get("orders:user:123");
if (result == null) {
    result = database.query("SELECT * FROM orders WHERE user_id = 123");
    redis.set("orders:user:123", result, 3600); // 设置缓存1小时
}

2. 使用消息队列异步处理写操作

高并发写入时，可以将写操作放入消息队列（如Kafka），然后异步批量写入数据库，减轻数据库压力。

八、实战案例

问题：

某电商系统的订单表存储了5000万条记录，用户查询订单详情时，页面加载时间超过10秒。

解决方案：

  1. 垂直拆分订单表：将订单详情字段拆分到另一个表中。
  2. 创建复合索引：为user_id和order_time创建索引。
  3. 使用Redis缓存：将最近30天的订单缓存到Redis中。
  4. 分页优化：使用search_after代替LIMIT深分页。

九、总结

大表性能优化是一个系统性工程，需要从表结构、索引、SQL到架构设计全方位考虑。

千万级别的数据量看似庞大，但通过合理的拆分、索引设计和缓存策略，可以让数据库轻松应对。

最重要的是，根据业务特点选择合适的优化策略，切勿盲目追求“高大上”的方案。

希望这些经验能帮到你！

最后说一句(求关注，别白嫖我)

如果这篇文章对您有所帮助，或者有所启发的话，帮忙关注一下我的同名公众号：苏三说技术，您的支持是我坚持写作最大的动力。

求一键三连：点赞、转发、在看。

关注公众号：【苏三说技术】，在公众号中回复：进大厂，可以免费获取我最近整理的10万字的面试宝典，好多小伙伴靠这个宝典拿到了多家大厂的offer。

标签：orders,千万级,order,调优,user,time,查询,id,大表
From： https://www.cnblogs.com/chinasoft/p/18682614

相关文章

  • 千万级的大表，如何做性能调优？
    前言大表优化是一个老生常谈的话题，但随着业务规模的增长，总有人会“中招”。很多小伙伴的数据库在刚开始的时候表现良好，查询也很流畅，但一旦表中的数据量上了千万级，性能问题就开始浮现，查询慢、写入卡、分页拖沓、甚至偶尔直接宕机。这时大家可能会想，是不是数据库不行？是不是需要升......
  • Linux性能调优：技术宅的魔法秘籍
    各位观众朋友们，大家好！今天，咱们来聊聊一个听起来就特别技术范儿，但实际上和我们每个人的生活都息息相关的话题——Linux系统性能调优。别急，我知道你们可能已经在心里默念：“这不就是那些技术宅才关心的事儿嘛，跟我有啥关系？”别走开，我保证，这事儿比你想象的有意思多了，而且说不定还......
  • JVM虚拟机监控及性能调优实战
    大家好，欢迎来到程序视点！我是小二哥。今天我们再来聊聊jvisualvm目录jvisualvm介绍代码语言：txt复制1.jvisualvm是JDK自带的可以远程监控内存，跟踪垃圾回收，执行时内存，CPU/线程分析，生成堆快照等的工具。2.jvisualvm是从JDK1.6开始被继承到JDK中的。jvisualvm使用jvisualvm......
  • MySql操作指南4--MySQL查询优化与性能调优
    性能优化是数据库开发的重要组成部分，合理优化SQL查询、索引设计以及程序逻辑能够大幅提升系统性能。本文将详细介绍SQL查询优化、Golang中的性能调优技术以及MySQL的索引与分区管理方法。1、SQL查询优化 查询计划的作用查询计划是数据库执行SQL语句的过程及其成......
  • 机器学习模型调优指南
    机器学习模型调优指南机器学习模型参数调优的作用在于优化模型的性能，使其能够在给定任务上更好地泛化和预测。通过合理调整模型的超参数，能够提高模型的准确性、降低过拟合或欠拟合的风险、加快训练过程等。具体来说，机器学习模型参数调优的作用可以从以下几个方面来理解：1.......
  • Mysql--实战篇--SQL优化（查询优化器，常用的SQL优化方法，执行计划EXPLAIN，Mysql性能调优，慢
    一、查询优化1、查询优化器(QueryOptimizer)MySQL查询优化器（QueryOptimizer）是MySQL数据库管理系统中的一个关键组件，负责分析和选择最有效的执行计划来执行SQL查询。查询优化器的目标是尽可能减少查询的执行时间和资源消耗，从而提高查询性能。查询语句不同关键字（where、......
  • 基于多目标粒子群优化算法的计及光伏波动性的主动配电网有功无功协调优化（Matlab代码实
     ......
  • 基于Informer网络实现电力负荷时序预测——cross validation交叉验证与Hyperopt超参数
    前言系列专栏:【深度学习：算法项目实战】✨︎涉及医疗健康、财经金融、商业零售、食品饮料、运动健身、交通运输、环境科学、社交媒体以及文本和图像处理等诸多领域，讨论了各种复杂的深度神经网络思想，如卷积神经网络、循环神经网络、生成对抗网络、门控循环单元、长短期记忆......
  • 15. 你知道哪些JVM性能调优参数？
    「堆栈内存相关」-Xms设置初始堆的大小-Xmx设置最大堆的大小-Xmn设置年轻代大小，相当于同时配置-XX:NewSize和-XX:MaxNewSize为一样的值-Xss每个线程的堆栈大小-XX:NewSize设置年轻代大小(for1.3/1.4)-XX:MaxNewSize年轻代最大值(for1.3/1.4)-XX:NewRatio年轻代与......
  • 12. 调优命令有哪些？
    SunJDK监控和故障处理命令有jpsjstatjmapjhatjstackjinfojps：JVMProcessStatusTool,显示指定系统内所有的HotSpot虚拟机进程。jstat：JVMstatisticsMonitoring是用于监视虚拟机运行时状态信息的命令，它可以显示出虚拟机进程中的类装载、内存、垃圾收集、JIT编译等运行数......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99