千万级的大表,如何做性能优化?

向量数据库关系型数据库NoSQL数据库

项目实战

架构师课程

大家好,我是苏三,又跟大家见面了。

前言

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

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

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

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

今天,我们就从 问题本质 讲起,逐步分析大表常见的性能瓶颈,以及如何一步步优化,希望对你会有所帮助。

1 为什么大表会慢?

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

1.1 磁盘IO瓶颈

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

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

举例:

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


        
        
            

          
 SELECT
 
           * 
          
 FROM
 
           orders 
          
 WHERE
 
           user\_id = 
          
 123
 
           
          
 ORDER
 
           
          
 BY
 
           order\_time 
          
 DESC
 
           
          
 LIMIT
 
           
          
 10
 
          ;
          
   

 
        
      

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

1.2 索引失效或没有索引

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

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

举例:

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


        
        
            

          
 SELECT
 
           * 
          
 FROM
 
           orders 
          
 WHERE
 
           
          
 DATE
 
          (order\_time) = 
          
 '2023-01-01'
 
          ;
          
   

 
        
      

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

1.3 分页性能下降

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

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

举例:

查询第1000页的10条数据:


        
        
            

          
 SELECT
 
           * 
          
 FROM
 
           orders 
          
 ORDER
 
           
          
 BY
 
           order\_time 
          
 DESC
 
           
          
 LIMIT
 
           
          
 9990
 
          , 
          
 10
 
          ;
          
   

 
        
      

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

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

1.4 锁争用

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

2 性能优化的总体思路

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

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

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

接下来,我们逐一展开。

3 表结构设计优化

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

3.1 精简字段类型

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

  • 能用 INT 的不要用 BIGINT
  • 能用 VARCHAR(100) 的不要用 TEXT
  • 时间字段建议用 TIMESTAMPDATETIME ,不要用 CHARVARCHAR 来存时间。

举例:


        
        
            

          
 -- 不推荐
 
          
   

 
          
 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
 
          ) 
          
 -- 限制最大长度
 
          
   

 
          );
          
   

 
        
      

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

如果对表设计比较感兴趣,可以看看我之前的另一篇文章《 表设计的18条军规 》,里面有详细的介绍。

3.2 表拆分:垂直拆分与水平拆分

垂直拆分

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

示例 : 将订单表分为两个表: orders\_basicorders\_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的订单
 
          
   

 
        
      

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

4 索引优化

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

4.1 创建合适的索引

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

示例:


        
        
            

          
 CREATE
 
           
          
 INDEX
 
           idx\_user\_id\_order\_time 
          
 ON
 
           orders (user\_id, order\_time 
          
 DESC
 
          );
          
   

 
        
      

上面的复合索引可以同时加速 user\_idorder\_time 的查询。

4.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
 
                ;
                
   

 
              
            

如果对索引失效问题比较感兴趣,可以看看我之前的另一篇文章《 聊聊索引失效的10种场景,太坑了 》,里面有详细的介绍。

5 SQL优化

5.1 减少查询字段

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


        
        
            

          
 -- 错误
 
          
   

 
          
 SELECT
 
           * 
          
 FROM
 
           orders 
          
 WHERE
 
           user\_id = 
          
 123
 
          ;
          
   

 
          
   

 
          
 -- 优化
 
          
   

 
          
 SELECT
 
           
          
 id
 
          , order\_time 
          
 FROM
 
           orders 
          
 WHERE
 
           user\_id = 
          
 123
 
          ;
          
   

 
        
      

5.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
 
          ;
          
   

 
        
      

如果对SQL优化比较感兴趣,可以看看我之前的另一篇文章《 聊聊sql优化的15个小技巧 》,里面有详细的介绍。

6 分库分表

6.1 水平分库分表

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

常见的分库分表规则:

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

如果对分库分表比较感兴趣,可以看看我之前的另一篇文章《 阿里二面:为什么要分库分表? 》,里面有详细的介绍。

7 缓存与异步化

7.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小时
 
          
   

 
          }
          
   

 
        
      

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

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

如果对Kafka的一些问题比较感兴趣,可以看看我之前的另一篇文章《 我用kafka两年踩过的一些非比寻常的坑 》,里面有详细的介绍。

8 实战案例

问题:

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

解决方案:

  1. 垂直拆分订单表 :将订单详情字段拆分到另一个表中。
  2. 创建复合索引 :为 user\_idorder\_time 创建索引。
  3. 使用Redis缓存 :将最近30天的订单缓存到Redis中。
  4. 分页优化 :使用 search\_after 代替 LIMIT 深分页。

总结

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

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

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

希望这些经验能帮到你!

最后欢迎

加入苏三的星球

,你将获得:AI开发项目课程、苏三AI项目、

商城微服务实战、秒杀系统实战

商城系统实战、秒杀系统实战、代码生成工具、系统设计、性能优化、技术选型、底层原理、Spring源码解读、工作经验分享、痛点问题

、面试八股文

等多个优质专栏。

还有1V1答疑、修改简历、职业规划、送书活动、技术交流。

扫描下方二维码,即可加入星球:

picture.image

目前星球已经更新了 5200+ 篇优质内容,还在持续爆肝中.....

星球已经被 官方推荐了3次 ,收到了小伙伴们的一致好评。戳我加入学习,已有 1600+ 小伙伴加入学习。

picture.image

0
0
0
0
关于作者
关于作者

文章

0

获赞

0

收藏

0

相关资源
字节跳动 NoSQL 的实践与探索
随着 NoSQL 的蓬勃发展越来越多的数据存储在了 NoSQL 系统中,并且 NoSQL 和 RDBMS 的界限越来越模糊,各种不同的专用 NoSQL 系统不停涌现,各具特色,形态不一。本次主要分享字节跳动内部和火山引擎 NoSQL 的实践,希望能够给大家一定的启发。
相关产品
评论
未登录
看完啦,登录分享一下感受吧~
暂无评论