Mysql大数据表处理方案

忘是亡心i 2023-10-04 20:41 26阅读 0赞

## 场景： ##

当我们业务数据库表中的数据越来越多，如果你也和我遇到了以下类似场景，那让我们一起来解决这个问题

*  数据的插入,查询时长较长
 *  后续业务需求的扩展 在表中新增字段 影响较大
 *  表中的数据并不是所有的都为有效数据 需求只查询时间区间内的

## 评估表数据体量 ##

我们可以从表容量/磁盘空间/实例容量三方面评估数据体量，接下来让我们分别展开来看看

### 表容量： ###

表容量主要从表的记录数、平均长度、增长量、读写量、总大小量进行评估。一般对于OLTP的表，建议单表不要超过2000W行数据量，总大小15G以内。访问量：单表读写量在1600/s以内

查询行数据的方式： 我们一般查询表数据有多少数据时用到的经典sql语句如下：

*  select count(\*) from table
 *  select count(1) from table 但是当数据量过大的时候，这样的查询就可能会超时，所以我们要换一种查询方式

1.  use 库名
2.  show table status like '表名' ; 或 show table status like '表名'\\G ;

上述方法不仅可以查询表的数据，还可以输出表的详细信息 , 加 \\G 可以格式化输出。包括表名 存储引擎 版本 行数 每行的字节数等等，大家可以自行试一下哈

### 磁盘空间 ###

查看指定数据库容量大小

select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
    from information_schema.tables
    order by data_length desc, index_length desc;

查询单个库中所有表磁盘占用大小

select
    table_schema as '数据库',
    table_name as '表名',
    table_rows as '记录数',
    truncate(data_length/1024/1024, 2) as '数据容量(MB)',
    truncate(index_length/1024/1024, 2) as '索引容量(MB)'
    from information_schema.tables
    where table_schema='mysql'
    order by data_length desc, index_length desc;

查询出的结果如下：

![1874e5b502f209f21e9d70d1af804252.png][]

建议数据量占磁盘使用率的70%以内。同时，对于一些数据增长较快，可以考虑使用大的慢盘进行数据归档（归档可以参考方案三）

### 实例容量 ###

MySQL是基于线程的服务模型，因此在一些并发较高的场景下，单实例并不能充分利用服务器的CPU资源，吞吐量反而会卡在mysql层，可以根据业务考虑自己的实例模式

## 出现问题的原因 ##

上面我们已经查到我们数据表的体量了 那么为什么单表数据量越大 业务的执行效率就越慢 根本原因是什么呢？

一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的`B+`树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

<

[1874e5b502f209f21e9d70d1af804252.png]: https://img-blog.csdnimg.cn/img_convert/1874e5b502f209f21e9d70d1af804252.png

发表评论取消回复

表情：

评论列表（有 0 条评论，26人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL大数据处理：挑战与解决方案

在当今大数据时代，MySQL作为一款广泛使用的关系型数据库管理系统，在大数据处理方面确实面临着一些挑战。下面将挑战和可能的解决方案进行概述： 1. **数据量大**：

Dear 丶/ 2025年03月19日 15:54/ 0 赞/ 104 阅读

相关 MySQL：数据表结构问题及解决方案

在MySQL中，数据表结构包括字段名、数据类型、是否允许NULL值等信息。以下是一些常见的结构问题及其解决方法： 1. **字段不存在**： - 解决方法：检查字段名称

爱被打了一巴掌/ 2025年01月09日 14:18/ 0 赞/ 86 阅读

相关【Mysql数据库系列】数据表事务锁处理

往期回顾： [【Mysql数据库系列】Mysql锁表信息查看与解决][Mysql_Mysql] -------------------- 1、查看是否是操作数据

左手的ㄟ右手/ 2024年03月26日 19:05/ 0 赞/ 169 阅读

相关 mysql 历史数据表迁移方案

当业务运行一段时间后，会出现有些表数据量很大，可能对系统性能产生不良的影响，常见的如订单表、登录log表等，这些数据很有时效性，比如我们一般很少去查上个月的订单，最多也就是报表

我就是我/ 2023年10月18日 14:34/ 0 赞/ 236 阅读

相关 Mysql大数据表处理方案

场景：当我们业务数据库表中的数据越来越多，如果你也和我遇到了以下类似场景，那让我们一起来解决这个问题数据的插入,查询时长较长后续业务需求的扩展在表中

忘是亡心i/ 2023年10月04日 20:41/ 0 赞/ 27 阅读

相关大数据处理方案

原文网址：[大数据处理方案\_IT利刃出鞘的博客-CSDN博客][IT_-CSDN] 简介本文介绍大数据的一些处理方案。本内容来源于一次面试：如何统计大数据量重复

电玩女神/ 2023年10月02日 16:09/ 0 赞/ 31 阅读

相关 mysql 清除大数据表单

　　背景：mysql数据库中有个日志表记录高达800多万，影响了mysql的正常业务访问，现需要清理三个月之前的所有数据，大概600多万(大概13G) 　　方法一：传统del

布满荆棘的人生/ 2023年09月24日 10:26/ 0 赞/ 104 阅读

相关大文件处理方案

处理海量数据问题，无非就是：分而治之/hash映射 + hash统计 + 堆/快速/归并排序； Bloom filter/Bitmap； Trie树/数据库/倒排

小鱼儿/ 2022年11月10日 01:36/ 0 赞/ 244 阅读

相关 mysql数据表命令是_MySQL_数据表命令

数据表操作关于Mysql的数据类型，点击一下 1.创建数据表： create \[temporapy\] table \[if not exists\] \[( \[

阳光穿透心脏的1/2处/ 2022年10月25日 04:57/ 0 赞/ 304 阅读

相关读取大数据表

private PreparedStatement ps=null; private ResultSet rs=null; ps=conn.prepare

绝地灬酷狼/ 2022年06月05日 08:41/ 0 赞/ 213 阅读