Mysql某个表有近千万数据,CRUD比较慢,如何优化?

2019/7/18 1:45:06 来源:点点趣说 浏览:

数据千万级别之多,占用的存储空间也比较大,可想而知它不会存储在一块连续的物理空间上,而是链式存储在多个碎片的物理空间上。可能对于长字符串的比较,就用更多的时间查找与比较,这就导致用更多的时间。

可以做表拆分,减少单表字段数量,优化表结构。

在保证主键有效的情况下,检查主键索引的字段顺序,使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。

主要两种拆分 垂直拆分,水平拆分。

垂直分表

也就是“大表拆小表”,基于列字段进行的。一般是表中的字段较多,将不常用的, 数据较大,长度较长(比如text类型字段)的拆分到“扩展表“。 一般是针对那种几百列的大表,也避免查询时,数据量太大造成的“跨页”问题。

垂直分库针对的是一个系统中的不同业务进行拆分,比如用户User一个库,商品Product一个库,订单Order一个库。 切分后,要放在多个服务器上,而不是一个服务器上。为什么? 我们想象一下,一个购物网站对外提供服务,会有用户,商品,订单等的CRUD。没拆分之前, 全部都是落到单一的库上的,这会让数据库的单库处理能力成为瓶颈。按垂直分库后,如果还是放在一个数据库服务器上, 随着用户量增大,这会让单个数据库的处理能力成为瓶颈,还有单个服务器的磁盘空间,内存,tps等非常吃紧。 所以我们要拆分到多个服务器上,这样上面的问题都解决了,以后也不会面对单机资源问题。

数据库业务层面的拆分,和服务的“治理”,“降级”机制类似,也能对不同业务的数据分别的进行管理,维护,监控,扩展等。 数据库往往最容易成为应用系统的瓶颈,而数据库本身属于“有状态”的,相对于Web和应用服务器来讲,是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限,在高并发场景下,垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平分表

针对数据量巨大的单张表(比如订单表),按照某种规则(RANGE,HASH取模等),切分到多张表里面去。 但是这些表还是在同一个库中,所以库级别的数据库操作还是有IO瓶颈。不建议采用。

水平分库分表

将单张表的数据切分到多个服务器上去,每个服务器具有相应的库与表,只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力,突破IO、连接数、硬件资源等的瓶颈。

水平分库分表切分规则

1. RANGE

从0到10000一个表,10001到20000一个表;

2. HASH取模

一个商场系统,一般都是将用户,订单作为主表,然后将和它们相关的作为附表,这样不会造成跨库事务之类的问题。 取用户id,然后hash取模,分配到不同的数据库上。

3. 地理区域

比如按照华东,华南,华北这样来区分业务,七牛云应该就是如此。

4. 时间

按照时间切分,就是将6个月前,甚至一年前的数据切出去放到另外的一张表,因为随着时间流逝,这些表的数据 被查询的概率变小,所以没必要和“热数据”放在一起,这个也是“冷热数据分离”。

分库分表后面临的问题

事务支持

分库分表后,就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务,将付出高昂的性能代价; 如果由应用程序去协助控制,形成程序逻辑上的事务,又会造成编程方面的负担。

跨库join

只要是进行切分,跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题

这些是一类问题,因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案:与解决跨节点join问题的类似,分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行,因此很多时候它的速度要比单一大表快很多。但如果结果集很大,对应用程序内存的消耗是一个问题。

数据迁移,容量规划,扩容等问题

来自淘宝综合业务平台团队,它利用对2的倍数取余具有向前兼容的特性(如对4取余得1的数对2取余也是1)来分配数据,避免了行级别的数据迁移,但是依然需要进行表级别的迁移,同时对扩容规模和分表数量都有限制。总得来说,这些方案都不是十分的理想,多多少少都存在一些缺点,这也从一个侧面反映出了Sharding扩容的难度。

ID问题

一旦数据库被切分到多个物理结点上,我们将不能再依赖数据库自身的主键生成机制。一方面,某个分区数据库自生成的ID无法保证在全局上是唯一的;另一方面,应用程序在插入数据之前需要先获得ID,以便进行SQL路由.

一些常见的主键生成策略

UUID

使用UUID作主键是最简单的方案,但是缺点也是非常明显的。由于UUID非常的长,除占用大量存储空间外,最主要的问题是在索引上,在建立索引和基于索引进行查询时都存在性能问题。

Twitter的分布式自增ID算法Snowflake

在分布式系统中,需要生成全局UID的场合还是比较多的,twitter的snowflake解决了这种需求,实现也还是很简单的,除去配置信息,核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

跨分片的排序分页

一般来讲,分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候,我们通过分片规则可以比较容易定位到指定的分片,而当排序字段非分片字段的时候,情况就会变得比较复杂了。为了最终结果的准确性,我们需要在不同的分片节点中将数据进行排序并返回,并将不同分片返回的结果集进行汇总和再次排序,最后再返回给用户。

看看网友怎么说

家和万事兴23508441:Mysql某个表有近千万数据,CRUD比较慢,如何优化

点点趣说:转发了

若叶公子:转发了

陈裕58:转发了

呼吸二氧化碳:1000万数据并不算多,我们很多这种级别的表,没必要分表分库,做好索引即可

文章来源网络,版权归属原作者,未注明作者均因传阅太多无从查证。本站为公益性非盈利网站,在本网转载其他媒体稿件是为传播更多的信息,此类稿件不代表本网观点。如果本网转载的稿件涉及您的版权、名益权等问题,请尽快与我们联系,我们将第一时间处理!
  • 【环球时报综合报道】中方日前宣布将对参与本次售台武器的美企实施制裁后,美媒分析了相关企业将有哪些项目会成为中方制裁对象。这次参与美国对台22亿美元军售的企业主要有四家,包括对台出售M1A2T坦克的美国通用动力公司,出售“毒刺”便携式防空导弹的雷神公司,以及提供M1A2T坦克配套项目的美国BAE系统公...

  • 在最近这几年时间里,我国的体育运动正在蓬勃发展,而拳击也慢慢进入到了大众的视野中。像武林风、昆仑决这些节目,相信大家更是耳熟能详了。在这其中也诞生出了很多的中国拳王,但要说名气最大的那就要数“武僧”一龙了!一龙俗名刘星君,1987年出生于山东德州,他的成长过程可以说是十分励志了!因为从小就非常喜欢武...

  • 本文笔者将从一个案例来与大家讲述:如何利用移动数据分析,来定义一个应用程序成功与否?如何通过移动数据分析,来让APP提高盈利能力?当你思考出一个新应用程序的点子时,直觉的第一反应也许会要求你尽可能快地开始研发工作。这种心态固然是好的,但要知道开发一款手机应用的成本其实很高,更不用提大多数手机应用都失...

  • 2019年7月16日消息:巴西国家商品供应公司(CONAB)发布的7月份供需报告显示,2018/19年度巴西玉米产量预测数据上调150万吨,为9850万吨,还将2018/19年度大豆产量数据上调20万吨,为1.15亿吨。2018/19年度巴西玉米产量预计为9850万吨,同比增加1770万吨或22%。...

  • 张曼玉刚出道不久时,有已婚富商公开约她,结果被她巧妙化解。[灵光一闪]娱记查小欣爆料,当时是在一个聚会上,这位富商白手兴家成为亿万富豪,被封神童,却也难敌张曼玉的美貌的诱惑,在饭局上直白地问:“你真人更美,很想约会你,你有时间吗?”现场立刻安静下来,看张曼玉将如何接招。只见张曼玉淡定反问:“晚饭?”...

  • SS2赛季才刚开启没多久,光子策划今天就又曝光了部分下版本的更新内容,这节奏是要让游戏时刻保持新鲜啊,那么下版本将会更新哪些内容呢,我们一起来看看。首先是时装的优化,从此次新的夏日时装上大家不难看出,光子慢慢在改变时装的风格,变得更加多元化,以此迎合不同玩家的不同审美。下个版本同样还会在时装上下番功...

  • 1.DDos流量攻击DDos流量攻击是最常见也是最直接的一种攻击方式,攻击者通过控制大量僵尸网络肉鸡,对游戏服务器发起攻击,导致服务器拥堵直至崩溃。造成游戏玩家游戏掉线、登录不上、延迟等一系列不好的体验。根据相关数据显示,一款游戏如果持续遭到一天的攻击,玩家数量将流失80%,这对游戏公司而言是致命的...

  • 吐蕃的确可以说陪伴唐朝走完一生,两个帝国几乎同时建立,交往争斗几百年,又几乎同时灭亡。唐朝的历史大家比较清楚了,这里就简要介绍和分析吐蕃的兴衰之路。吐蕃帝国的兴衰历程吐蕃帝国可谓即熟悉又陌生,熟悉是因为大唐的历史上多次提及和出现过这个名字,而陌生又因为它地处我国西南边疆,史籍流传较少,加上语言文化不...

  • 房地产信托规模必须下降,核心就是让钱少去搞开发大家好,我是陆新之。欢迎来到财经科技新知的时间。今天我们讲一下房地产信托这个话题。最近银保监会对房地产信托提了五点要求,但核心就是:缩小投资规模。对此你是不是想起前段时间对各种互联网金融机构的政策要求。银保监会对房地产信托的五点要求分别是:第一,房地产信...

  • 该走的总要走,该留的也会留!NBA今夏自由市场的繁华盛景可20年难一遇,当年勇士角色球员扎扎-帕楚里亚“那一脚”,改变了联盟整个格局,这就是“蝴蝶效应”!30亿美金的交易市场,拿亿元大合同,年薪三四千万,甚至五千万的都大有人在,但今日小编不说这些大咖,谈一谈今夏4大超值合同,单场50分MVP先生仅7...