MySQL 单表十亿级别数据量设计和优化

最新推荐文章于 2024-05-26 00:30:51 发布
最新推荐文章于 2024-05-26 00:30:51 发布
阅读量1w 收藏 34
点赞数 5
分类专栏： 数据库 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42029527/article/details/111194046
版权
本文探讨了一种大规模数据存储方案，通过合理选择数据类型、建立索引和使用分区来提升数据库性能。针对每日4000万条数据的cdnUserRateRecord表，设计了包含18个字段的结构，并基于startTime和stopTime进行分区，以加速查询。同时，展示了如何通过SQL优化避免全表扫描，提高查询效率。
摘要由CSDN通过智能技术生成

需求分析

每天定时4000千万条数据入表（采用load data infile 加载文件入表），保存6个月数据，估算单表总数据量70亿左右。表中有18个字段。每天定时建立两个分区。查询需用到开始时间startTime和结束时间stopTime字段。

设计思路

1.数据库设计和表创建时就要考虑性能

  • 选择合适的数据类型
  1. 使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
  2. 使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
  3. 使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
  4. 尽可能使用not null定义字段
  5. 尽量少用text，非用不可最好分表
  • 选择合适的索引列
  1. 查询频繁的列，在where，group by，order by，on从句中出现的列
  2. where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
  3. 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
  • 分区
  1. 优点：
    （1）可以让单表存储更多的数据
    （2）分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
    （3）部分查询能够从查询条件确定只落在少数分区上，速度会很快
    （4） 可以备份和恢复单个分区
  2. 缺点
    （1）一个表最多只能有1024个分区
    （2）如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
    （3）分区表无法使用外键约束
    （4）NULL值会使分区过滤无效

表结构设计

CREATE TABLE `cdnUserRateRecord` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `bossUserID` varchar(20) DEFAULT NULL COMMENT '主账号',
  `userID` varchar(48) DEFAULT NULL COMMENT '子账号',
  `citycode` varchar(9) DEFAULT NULL COMMENT '地市',
  `areacode` varchar(9) DEFAULT NULL COMMENT '区县',
  `startTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '开始时间',
  `stopTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '结束时间',
  `businessType` varchar(2) DEFAULT NULL COMMENT '业务类型',
  `programID` varchar(128) DEFAULT NULL COMMENT '内容',     
  `programName` varchar(128) DEFAULT NULL COMMENT '内容名称',  
  `contentSources` varchar(2) DEFAULT NULL COMMENT '内容访问来源',   
  `sequenceType` varchar(1) DEFAULT NULL COMMENT '序列号类型',     
  `sequenceNumber` varchar(128) DEFAULT NULL COMMENT '序列号',    
  `terminalIP` varchar(128) DEFAULT NULL COMMENT '终端IP',
  `flow` int(10) DEFAULT '0' COMMENT '用户使用流量',
  `cpid` varchar(20) DEFAULT NULL COMMENT '发起播放的cp的代码',
  `productid` varchar(20) DEFAULT NULL COMMENT '内容归属产品代码',
  `serviceTime` varchar(20) DEFAULT NULL COMMENT '厂商服务时长/秒',
  `cdnmanufacturer` int(2) DEFAULT NULL COMMENT 'cdn 服务厂商',
  PRIMARY KEY (`id`,`startTime`,`stopTime`),   
  KEY `bossUserIDs` (`bossUserID`,`startTime`,`stopTime`),
  KEY `userIDs` (`userID`,`startTime`,`stopTime`),
  KEY `businessType` (`businessType`,`startTime`,`stopTime`),
  KEY `programNames` (`programName`,`startTime`,`stopTime`),
  KEY `cdnmanufacturer` (`cdnmanufacturer`,`startTime`,`stopTime`)
)DEFAULT CHARSET=utf8 COMMENT='cdn用户收视纪录表'
PARTITION BY RANGE (stopTime)
(PARTITION p2020032700 VALUES LESS THAN (1585242000) ENGINE = MyISAM,
 PARTITION p2020032701 VALUES LESS THAN (1585245600) ENGINE = MyISAM,
 PARTITION p2020032702 VALUES LESS THAN (1585249200) ENGINE = MyISAM,
 PARTITION p2020032703 VALUES LESS THAN (1585252800) ENGINE = MyISAM,
 PARTITION p2020032704 VALUES LESS THAN (1585256400) ENGINE = MyISAM,
 PARTITION p2020032705 VALUES LESS THAN (1585260000) ENGINE = MyISAM,
 PARTITION p2020032706 VALUES LESS THAN (1585263600) ENGINE = MyISAM,
 PARTITION p2020032707 VALUES LESS THAN (1585267200) ENGINE = MyISAM,
 PARTITION p2020032708 VALUES LESS THAN (1585270800) ENGINE = MyISAM,
 PARTITION p2020032709 VALUES LESS THAN (1585274400) ENGINE = MyISAM,
 PARTITION p2020032710 VALUES LESS THAN (1585278000) ENGINE = MyISAM,
 PARTITION p2020032711 VALUES LESS THAN (1585281600) ENGINE = MyISAM,
 PARTITION p2020032712 VALUES LESS THAN (1585285200) ENGINE = MyISAM,
 PARTITION p2020032713 VALUES LESS THAN (1585288800) ENGINE = MyISAM,
 PARTITION p2020032714 VALUES LESS THAN (1585292400) ENGINE = MyISAM,
 PARTITION p2020032715 VALUES LESS THAN (1585296000) ENGINE = MyISAM,
 PARTITION p2020032716 VALUES LESS THAN (1585299600) ENGINE = MyISAM,
 PARTITION p2020032717 VALUES LESS THAN (1585303200) ENGINE = MyISAM,
 PARTITION p2020032718 VALUES LESS THAN (1585306800) ENGINE = MyISAM,
 PARTITION p2020032719 VALUES LESS THAN (1585310400) ENGINE = MyISAM,
 PARTITION p2020032720 VALUES LESS THAN (1585314000) ENGINE = MyISAM,
 PARTITION p2020032721 VALUES LESS THAN (1585317600) ENGINE = MyISAM,
 PARTITION p2020032722 VALUES LESS THAN (1585321200) ENGINE = MyISAM,
 PARTITION p2020032723 VALUES LESS THAN (1585324800) ENGINE = MyISAM,
 PARTITION p2020032800 VALUES LESS THAN (1585328400) ENGINE = MyISAM,
 PARTITION p2020032801 VALUES LESS THAN (1585332000) ENGINE = MyISAM,
 PARTITION p2020032802 VALUES LESS THAN (1585335600) ENGINE = MyISAM,
 PARTITION p2020032803 VALUES LESS THAN (1585339200) ENGINE = MyISAM,
 PARTITION p2020032804 VALUES LESS THAN (1585342800) ENGINE = MyISAM,
 PARTITION p2020032805 VALUES LESS THAN (1585346400) ENGINE = MyISAM);

SQL优化

查看表中总数据量，大概19亿条
在这里插入图片描述

查看分区
这里是引用

  1. 避免扫描全表，查询索引列，limit限制返回条数

用时0.6秒，
在这里插入图片描述

Feng_MuJin
关注
专栏目录
MySQL单表百万数据记录分页性能优化技巧
09-10
自己的一个网站，由于单表的数据记录高达了一百万条，造成数据访问很慢，Google分析的后台经常报告超时，尤其是页码大的页面更是慢的不行
用MySQL存下亿级数据——分库分表
最新发布
qq_39039639的博客
08-04 1231
首先，我们需要明白分库分表要解决什么问题。在如今数据量爆炸的时代，业务的数据记录动不动就是以亿计，单表必定是存不下了。
数据库改造：怎样用MySQL对10亿级订单量进行分库分表？
wdj_yyds的博客
04-27 2878
一、背景 随着公司业务增长，如果每天1000多万笔订单的话，3个月将有约10亿的订单量，之前数据库采用单库单表的形式已经不满足于业务需求，数据库改造迫在眉睫。 二、订单数据如何划分 我们可以将订单数据划分成两大类型：分别是热数据和冷数据。 热数据：3个月内的订单数据，查询实时性较高; 冷数据A：3个月 ~ 12个月前的订单数据，查询频率不高; 冷数据B：1年前的订单数据，几乎不会查询，只有偶尔的查询需求; 可能这里有个疑惑为什么要将冷数据分成两类，因为根据实际场景需求，用户基本不会去查...
每日一面 - mysql中，我存十亿个手机号码，考虑存储空间和查询效率，怎么设计？
热门推荐
hashcon
01-06 1万+
问题参考自：https://www.zhihu.com/question/438078173，以下解答思路为个人原创 首先提出假设： 手机号码不会更新，只会插入和删除。 查询包括精确查询某个手机号是否存在，以及获取某一号码段的所有手机号 假设表只有一个字段，就是手机号 phone，并且设置为主键。如果不设置主键并且没有唯一索引，InnoDB 会给我们自动生成一个隐藏主键列，浪费空间。 MyISAM or InnoDB 如果插入和删除并不频繁，手机号是提前载入的字典表，而不是用户主动注册而产生的，则 .
10亿级订单系统分库分表设计思路！
eagle89的专栏
09-15 1692
一、背景 随着公司业务增长，如果每天1000多万笔订单的话，3个月将有约10亿的订单量，之前数据库采用单库单表的形式已经不满足于业务需求，数据库改造迫在眉睫。 二、订单数据如何划分 我们可以将订单数据划分成两大类型：分别是热数据和冷数据。 热数据：3个月内的订单数据，查询实时性较高; 冷数据A：3个月 ~ 12个月前的订单数据，查询频率不高; 冷数据B：1年前的订单数据，几乎不会查询，只有偶尔的查询需求; 可能这里有个疑惑为什么要将冷数据分成两类，因为根据实际场景需求，用
mysql十亿_Mysql：表中有数十亿条记录
weixin_39580715的博客
01-20 874
我需要在Mysql表中保存约78亿条记录.该表既读写又密集.我必须每小时至少保留20亿记录的插入率.而在桌子上搜索不应超过10秒钟.我们有一个UI,用户可以根据不同的colums属性进行搜索.大多数搜索查询可以像：> select * from mytable where prop1 =’sip：100008521149’和PROP2 = ‘asdsa’order by event_time...
如何设计上十亿的用户表
weixin_34009794的博客
11-06 653
当用户表达到了上十亿这样的规模，我们应该如果设计好呢？ 我们假设一般项目的用户有哪些基础应用功能呢？一般至少应该有“注册/登录” 和 “填写/修改用户资料”两个基本基本功能是吧？那么我们就根据这两个基本的功能来设计表，如果有其他扩展功能，可以增加其他扩展表方式，本章不讨论其他扩展表的设计。 针对注册/登录功能，我们采用hash散列设计 user_register 表，表只...
MySQL 对于千万级的大表要怎么优化？
dingte0105的博客
08-09 439
作者：zhuqz链接：https://www.zhihu.com/question/19719997/answer/81930332来源：知乎著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。 第一优化你的sql和索引； 第二加缓存，memcached,redis； 第三以上都做了后，还是慢，就做主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三...
千万级别数据的mysql数据表优化
01-19
　有一张users表，数据量在五千万以上，存在一条查询语句： 　SELECT * FROM users WHERE name LIKE '%明%' AND sex='\u7537' AND age=32 AND created_at BETWEEN 1388505600 AND 1420041600; 　是说从users表中...
mysql千万级数据大表该如何优化?
12-15
4. **更新操作优化**：尽量减少对表中大量数据的UPDATE和DELETE操作，因为这可能导致大量的索引重建和缓存失效。如果可能，使用批量更新或在低并发时段进行。 5. **SQL执行比例**：监控SELECT、UPDATE、DELETE和...
为什么说MySQL单表数据不要超过500万行
09-09
总的来说，MySQL单表数据量是否超过500万行并不是绝对的分水岭，而是需要结合实际情况，包括数据库配置、硬件资源和业务需求，进行综合评估。在数据库设计和优化过程中，应遵循最佳实践，以确保系统性能和稳定性。
mysql十亿数据_MySQL10亿+表数据进行批量删除操作
weixin_33188789的博客
01-19 933
因为开发人员上线的定时删除数据的程序没有执行，坑啊~ 导致了数据库表数据量达到10亿+。开发人员的需求是只需保留一个月的数据即可，数据都是一些java日志。所以提了需求删除数据的数据库变更。表数据统计如下：因为该表的id为自增主键，也可以通过最大最小id进行行数的估算。统计表大小，索引加数据文件一共600多G本次的数据变更需求为删除11月17号之前的数据，即日志数据只需保留一个月即可。研发人员提供...
亿级数据如何设计数据库
okbin1991的博客
05-29 465
电商亿级数据库设计-原理实 战 1. 电商 数据库架构体系剖析 1.1 背景概述 随着互联网普及率的不断提高，中国电商稳步发展，市场零售额 不断增长。根据数据显示，2019年，全国网上零售额106324亿 元，比上年增长16.5%。与此同时，得益于社交电商的流量效 应，电商用户持续增加。数据显示，截至2019年底，中国移动电 商用户规模预计将突破7亿人。 而对于电商系统来说, 具有以下几个特点:...
mysql 上亿级web应用的数据库设计方案
zxlin25
05-21 824
原文链接：http://blog.520.at/post/8ad61_643015     最近研究mysql数据库设计，收获不少（这里我要感谢xiuno作者）。   今天看到微博有朋友转载《mysql sql 百万级数据库优化方案》，我看到这篇文章时，发现有些地方会误导人（毕竟是三年前的文章）。所以我觉得网上的很多文章最好仅供参考比较好，具体情况最好还是以自己测试的结果为准，自己测试...
如何高效地向MySQL中插入10亿条数据
fudaihb的博客
04-11 1654
在面对向MySQL中插入大量数据的挑战时，采取有效的策略和技术是确保数据导入任务成功完成的关键。通过本文的介绍，我们了解到了一系列优化性能的方法和技巧，从数据库准备到数据插入的过程中，都有许多可以优化和改进的地方。首先，我们强调了选择合适的存储引擎和调整MySQL配置的重要性。InnoDB作为默认的存储引擎，具有事务支持和行级锁等特性，更适合处理大规模数据插入任务。通过调整MySQL的相关配置参数，如和等，可以有效地提升数据库的性能和吞吐量。
数据量10亿级别的数据库表，多行存储成一行、一列扩展成多列之数据优化及迁移方案(一)
chhuma专栏
11-30 3979
问题背景： 原表设计如下，业务上是把登陆用户的商品以priority顺序方式列表显示，但是此列表显示非常慢。 create table t_origin (   user_id     varchar2(11),   goods_id    varchar2(19),   priority    number(2),   update_time DATE      ); comm
mysql做十亿条数据查询_数据库优化：mysql数据库单机数十亿数据查询设计
weixin_39747049的博客
02-02 4081
很久没写文章，是不是想着写点什么东西，分享下我的数据库设计思路，主要是针对单机数十亿及以上数据查询优化技巧。如果只是简单的查询，没有频繁的写入操作，对查询速度不要求在毫秒级别，就不需要什么大型的数据库软件设计复杂的集群关系，也不需要分布式水平分割等太重的优化。只需要用mysql在本机笔记本搭建一个普通的环境就行。那么首先是针对mysql做一些普通常见的优化，比如分表分区、建索引、表字段设计以及my...
MySQL大数据征途：10亿数据挑战与6大应对策略，如何在潮头屹立不倒？
java专栏
05-26 424
🔥关注墨瑾轩，带你探索Java的奥秘🚀🔥超萌技术攻略，轻松晋级编程高手🚀🔥技术宝库已备好，就等你来挖掘🚀🔥订阅墨瑾轩，智趣学习不孤单🚀🔥即刻启航，编程之旅更有趣🚀随着大数据浪潮汹涌而来，MySQL这位久经沙场的数据库老将，既展现出了与时俱进的适应力，也面临着前所未有的挑战。在这片数据的汪洋中，MySQL是如何扬帆远航，又遭遇了哪些风浪呢？让我们一起深入探索。
MySQL开发规范：亿级数据表优化与设计指南
MySQL开发规范【X千万/表级别】是一份全面的指南，旨在提升数据库设计、查询优化和性能管理的有效性。该规范主要关注在处理大规模数据（如X千万级别的表）时的最佳实践，确保开发过程遵循良好的编码习惯和可读性。 ...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值