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


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

    实际业务场景

    表名表名含义行记录数
    base_house房屋表4201183

    解决方案一:使用count 函数

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

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

    解决方案二:使用预估值

    1. -- 执行SQL查询
    2. select TABLE_NAME, TABLE_ROWS
    3. from
    4. INFORMATION_SCHEMA.PARTITIONS
    5. where
    6. TABLE_SCHEMA = 'test'
    7. and TABLE_NAME='bds_house';
    8. -- 执行时间
    9. 0.003S

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

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

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

    1. -- 执行asc 升序排序,且取第一条记录
    2. select id from bds_house order by id asc limit 1;
    3. -- 执行时间
    4. 0.9S
    5. -- 执行desc 降序排序,且取第一条记录
    6. select id from bds_house order by id desc limit 1;
    7. -- 执行时间
    8. 1.2S
    9. -- 最后一步:
    10. 使用desc 降序id值 - 使用asc 升序id值

    解决方案四:创建一张统计表table_count, 实时记录给表的记录数

    table_count 表结构设计:

    1. DROP TABLE IF EXISTS table_count;
    2. CREATE TABLE table_count(
    3. ID INT NOT NULL AUTO_INCREMENT COMMENT '主键' ,
    4. TABLE_NAME VARCHAR(255) NOT NULL COMMENT '表名' ,
    5. TABLE_COUNT INT COMMENT '表记录总数' ,
    6. CREATED_BY VARCHAR(255) COMMENT '创建人' ,
    7. CREATED_TIME DATETIME COMMENT '创建时间' ,
    8. UPDATED_BY VARCHAR(255) COMMENT '更新人' ,
    9. UPDATED_TIME DATETIME COMMENT '更新时间' ,
    10. PRIMARY KEY (ID)
    11. ) COMMENT = '表记录总数';

    设计存储过程:主要实现统计各表的记录总数,我这里以bds_house 表为实例

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

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

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

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

  • 相关阅读:
    基于新版OpenCV5(C++)+OpenVINO Toolkit案例算法模型示例使用(一条语义分割与目标检测示例搞懂OpenVINO模型部署机制)
    si9000 单端(线)&差分(动)线板层结构与阻抗计算
    JaveEE进阶----Spring Web MVC入门
    [python]常用配置读取方法
    记一次dotnet拆分包,并希望得大佬指点
    浅析vue中computed,method,watch,watchEffect的区别
    机器视觉系统选型-环形光源分类及应用场景
    【数据结构】绪论
    生信学院|10月13日《SOLIDWORKS参数化应用——DriveWorksXpress》
    ES6和CommonJS导入导出语法区别
  • 原文地址:https://blog.csdn.net/zhouzhiwengang/article/details/127853259