MySQL 百万级/千万级表总记录数查询

不念不忘少年蓝@ 2023-10-10 15:24 27阅读 0赞

**业务背景：**基于 InnoDB 存储引擎的表，在数据量达到百万级之后，用 count 函数查询表记录总数会变得很慢，会导致服务请求超时。针对这种情况总结下我所想到的解决办法。

**实际业务场景**：

<table> 
 <tbody> 
  <tr> 
   <td>表名</td> 
   <td>表名含义</td> 
   <td>行记录数</td> 
  </tr> 
  <tr> 
   <td>base_house</td> 
   <td>房屋表</td> 
   <td>4201183</td> 
  </tr> 
 </tbody> 
</table>

**解决方案一：**使用count 函数

-- 执行SQL 语句
    select count(1) from bad_house;
    
    -- SQL 执行时间
    5.133S

**使用场景说明**：针对百万级及其以上的表数据记录总数统计不推荐使用。

**解决方案二：使用预估值**

-- 执行SQL查询
    select TABLE_NAME, TABLE_ROWS
    from  
        INFORMATION_SCHEMA.PARTITIONS  
    where 
        TABLE_SCHEMA = 'test' 
        and TABLE_NAME='bds_house';
    
    -- 执行时间
    0.003S

**使用场景说明：**针对百万级及其以上的表数据记录总数通过MySQL数据管理系统自带库表，查询指定schema和table\_name的记录总数。温馨提示：这里是个预估值，但与实际表记录总数非存在差别，由于MySQL数据管理系统需要定时更新表的记录总数。

**解决方案三：通过自增长Id，统计指定表的记录总数**

前提条件：表主键id 必须使用自增长，且必须确保表中数据几乎没有跳id、删数据的情况下，直接用最大id，减最小id。

-- 执行asc 升序排序，且取第一条记录
    select id from bds_house order by id asc limit 1;
    
    -- 执行时间
    0.9S
    
    -- 执行desc 降序排序，且取第一条记录
    select id from bds_house order by id desc limit 1;
    
    -- 执行时间
    1.2S
    
    -- 最后一步：
    使用desc 降序id值 - 使用asc 升序id值

**解决方案四：创建一张统计表table\_count, 实时记录给表的记录数**

table\_count 表结构设计：

DROP TABLE IF EXISTS table_count;
    CREATE TABLE table_count(
        ID INT NOT NULL AUTO_INCREMENT  COMMENT '主键' ,
        TABLE_NAME VARCHAR(255) NOT NULL   COMMENT '表名' ,
        TABLE_COUNT INT    COMMENT '表记录总数' ,
        CREATED_BY VARCHAR(255)    COMMENT '创建人' ,
        CREATED_TIME DATETIME    COMMENT '创建时间' ,
        UPDATED_BY VARCHAR(255)    COMMENT '更新人' ,
        UPDATED_TIME DATETIME    COMMENT '更新时间' ,
        PRIMARY KEY (ID)
    )  COMMENT = '表记录总数';

设计存储过程：主要实现统计各表的记录总数，我这里以bds\_house 表为实例

create procedure table_count_procedure()
    begin
    	-- 定义字段
    	declare R_TABLE_COUNT int default 0;
    
    	-- 查询bds_count 表记录总数
    	select count(1) into R_TABLE_COUNT  from bds_house;
    	
    	-- 执行数据库插入
    	insert into table_count(table_name, table_count) values('bds_house'， R_TABLE_COUNT);
    end

定时调用存储过程的方式：

单体应用：Spring定时任务框架、Quartz、Java Timer/Task等等

分布式应用：XXL-job、分布式Quartz、Elastic-job 等等