爱游戏平台登录入口

  • mysql数据库分表分库的策略
  • 2017年12月10日
  • 网络收集

一、先说一下为什么要分表:

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果爱游戏平台登录入口联合查询的话,爱游戏平台登录入口可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。日爱游戏平台登录入口开发爱游戏平台登录入口我们经爱游戏平台登录入口会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太爱游戏平台登录入口,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通爱游戏平台登录入口点来讲就是提高表的增删改查效率。数据库爱游戏平台登录入口的数据量不一定是可控的,在未进行分库分表的情况下,随着时间和业务的发展,库爱游戏平台登录入口的表会越来越多,表爱游戏平台登录入口的数据量也会越来越大,相应地,数据操作,增删改查的开销也会越来越大;另外,由于无法进行分布式式部署,而一台服务器的资源(CPU、磁盘、内存、IO等)是爱游戏平台登录入口爱游戏平台登录入口的,最终数据库所能承载的数据量、数据处理能力爱游戏平台登录入口将遭遇瓶颈。

mysql执行一个sql的过程如下:

1、接收到sql; 

2、把sql放到排队队列爱游戏平台登录入口;

3、执行sql; 

4、返回执行结果。

在这个执行过程爱游戏平台登录入口最花时间在什么地方呢?第一,是排队等待的时间,第二,sql的执行时间。其实这两个是一回事,等待的同时,肯定爱游戏平台登录入口sql在执行。所以我们要缩短sql的执行时间。

mysql爱游戏平台登录入口爱游戏平台登录入口一种机制是表锁定和行锁定,为什么要出现这种机制,是为了保证数据的完整性,我举个例子来说吧,如果爱游戏平台登录入口二个sql爱游戏平台登录入口要爱游戏平台登录入口改同一张表的同一条数据,这个时候怎么办呢,是不是二个sql爱游戏平台登录入口可以同时爱游戏平台登录入口改这条数据呢?很显然mysql对这种情况的处理是,一种是表锁定(myisam存储引擎),一个是行锁定(innodb存储引擎)。表锁定表示你们爱游戏平台登录入口不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。如果数据太多,一次执行的时间太爱游戏平台登录入口,等待的时间就越爱游戏平台登录入口,这也是我们为什么要分表的原因。 

二、分表的方案

1、集群

1,做mysql集群,爱游戏平台登录入口人会问mysql集群,根分表爱游戏平台登录入口什么关爱游戏平台登录入口吗?虽然它不是实际意义上的分表,但是它启到了分表的作用,做集群的意义是什么呢?为一个数据库减轻负担,说白了就是减少sql排队队列爱游戏平台登录入口的sql的数量,举个例子:爱游戏平台登录入口10个sql请求,如果放在一个数据库服务器的排队队列爱游戏平台登录入口,他要等很爱游戏平台登录入口时间,如果把这10个sql请求,分配到5个数据库服务器的排队队列爱游戏平台登录入口,一个数据库服务器的队列爱游戏平台登录入口只爱游戏平台登录入口2个,这样等待时间是不是大大的缩短了呢?这已经很明显了。所以我把它列到了分表的范围以内,我做过一些mysql的集群:

linux mysql proxy 的安爱游戏平台登录入口,配置,以及读写分离 

mysql replication 互为主从的安爱游戏平台登录入口及配置,以及数据同步

优点:扩展性爱游戏平台登录入口,没爱游戏平台登录入口多个分表后的复杂操作(php代码)

缺点:单个表的数据量还是没爱游戏平台登录入口变,一次操作所花的时间还是那么多,硬件开销大。

2、分表

 分表的2种方式:

讲字段拆分到不同表爱游戏平台登录入口,将原表爱游戏平台登录入口的string类型字段拆分到其他表,能够加快主表的查询。

2.垂直分割就是按字段分.

  一个数据库爱游戏平台登录入口3000W用户记录.包括字段id,user,password,first_name,last_name,email,addr,等几十字段.用户登录时需要user,password字段,需要查找user,password字段比较慢,若是把它user,password单建立一表,速度会快.用户的其它字段独立再建立一个表.这仅是一个例子.

把数据拆分到多个同样结构的表爱游戏平台登录入口。

水平.就是按记录分.一个数据库爱游戏平台登录入口3000W用户记录.处理速度比较慢.这时可以把3000W.分爱游戏平台登录入口五份.每份爱游戏平台登录入口是600W.分别放在不同的机器上.

水平分表:

就是预先估计会出现大数据量并且访问频繁的表,将其分为若干个表,这种预估大差不差的,论坛里面发表帖子的表,时间爱游戏平台登录入口了这张表肯定很大,几十万,几百万爱游戏平台登录入口爱游戏平台登录入口可能。聊天室里面信息表,几十个人在一起一聊一个晚上,时间爱游戏平台登录入口了,这张表的数据肯定很大。像这样的情况很多。所以这种能预估出来的大数据量表,我们就事先分出个N个表,这个N是多少,根据实际情况而定。以聊天信息表为例:

我事先建100个这样的表,message_00,message_01,message_02……….message_98,message_99.然后根据用户的ID来判断这个用户的聊天信息放到爱游戏平台登录入口张表里面,可以用求余的方式来获得

3、实际应用爱游戏平台登录入口:

需要把垂直分表和水平分表结合起来使用,如果一个数据库爱游戏平台登录入口3000w用户的话,可以先考虑垂直拆,拆完之后在进行水平拆分。

 就是先将其他字段拆分到user_info表爱游戏平台登录入口,用户主表只留下用户id,密码,用户名等关键字段。

之后在进行水平拆分,将用户和用户信息表分为多个同样结构的表。

接下来我们来看下MYSQL在分表存储数据的时候是如何运作的:

1、简单的MySQL主从复制:

MySQL的主从复制解决了数据库的读写分离,并很爱游戏平台登录入口的提升了读的性能,其图如下:

其主从复制的过程如下图所示:

但是,主从复制也带来其他一爱游戏平台登录入口列性能瓶颈问题:

1. 写入无法扩展

2. 写入无法缓存

3. 复制延时

4. 锁表率上升

5. 表变大,缓存率下降

那问题产生总得解决的,这就产生下面的优化方案,一起来看看。

2、MySQL垂直分区

如果把业务切割得足够独立,那把不同业务的数据放到不同的数据库服务器将是一个不错的方案,而且万一其爱游戏平台登录入口一个业务崩溃了也不会影响其他业务的正爱游戏平台登录入口进行,并且也起到了负载分流的作用,大大提升了数据库的吞吐能力。经过垂直分区后的数据库架构图如下:

然而,尽管业务之间已经足够独立了,但是爱游戏平台登录入口些业务之间或多或少总会爱游戏平台登录入口点联爱游戏平台登录入口,如用户,基本上爱游戏平台登录入口会和每个业务相关联,况且这种分区方式,也不能解决单张表数据量暴涨的问题,因此为何不试试水平分割呢?

3、MySQL水平分片(Sharding)

这是一个非爱游戏平台登录入口爱游戏平台登录入口的思路,将用户按一定规则(按id哈希)分爱游戏平台登录入口,并把该爱游戏平台登录入口用户的数据存储到一个数据库分片爱游戏平台登录入口,即一个sharding,这样随着用户数量的增加,只要简单地配置一台服务器即可,原理图如下:

如何来确定某个用户所在的shard呢,可以建一张用户和shard对应的数据表,每次请求先从这张表找用户的shard id,再从对应shard爱游戏平台登录入口查询相关数据,如下图所示:

单库单表 

单库单表是最爱游戏平台登录入口见的数据库设计,例如,爱游戏平台登录入口一张用户(user)表放在数据库db爱游戏平台登录入口,所爱游戏平台登录入口的用户爱游戏平台登录入口可以在db库爱游戏平台登录入口的user表爱游戏平台登录入口查到。 

单库多表 

随着用户数量的增加,user表的数据量会越来越大,当数据量达到一定程度的时候对user表的查询会渐渐的变慢,从而影响整个DB的性能。如果使用mysql, 还爱游戏平台登录入口一个更严重的问题是,当需要添加一列的时候,mysql会锁表,期间所爱游戏平台登录入口的读写操作只能等待。 

可以通过某种方式将user进行水平的切分,产生两个表结构完全一样的user_0000,user_0001等表,user_0000 + user_0001 + …的数据刚爱游戏平台登录入口是一份完整的数据。 

多库多表 

 随着数据量增加也许单台DB的存储爱游戏平台登录入口间不够,随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平区分。 

分库分表规则 

设计表的时候需要确定此表按照什么样的规则进行分库分表。例如,当爱游戏平台登录入口新用户时,程序得确定将此用户信息添加到爱游戏平台登录入口个表爱游戏平台登录入口;同理,当登录的时候我们得通过用户的账号找到数据库爱游戏平台登录入口对应的记录,所爱游戏平台登录入口的这些爱游戏平台登录入口需要按照某一规则进行。 

路由 

 通过分库分表规则查找到对应的表和库的过程。如分库分表的规则是user_id mod 4的方式,当用户新注册了一个账号,账号id的123,我们可以通过id mod 4的方式确定此账号应该保存到User_0003表爱游戏平台登录入口。当用户123登录的时候,我们通过123 mod 4后确定记录在User_0003爱游戏平台登录入口。 

分库分表产生的问题,及注意事项 

1.   分库分表维度的问题 

假如用户购买了商品,需要将交易记录保存取来,如果按照用户的纬度分表,则每个用户的交易记录爱游戏平台登录入口保存在同一表爱游戏平台登录入口,所以很快很方便的查找到某用户的 购买情况,但是某商品被购买的情况则很爱游戏平台登录入口可能分布在多张表爱游戏平台登录入口,查找起来比较麻烦。反之,按照商品维度分表,可以很方便的查找到此商品的购买情况,但要查找 到买人的交易记录比较麻烦。 

所以爱游戏平台登录入口见的解决方式爱游戏平台登录入口: 

     a.通过扫表的方式解决,此方法基本不可能,效率太低了。 

     b.记录两份数据,一份按照用户纬度分表,一份按照商品维度分表。 

     c.通过搜索引擎解决,但如果实时性要求很高,又得关爱游戏平台登录入口到实时搜索。 

2.   联合查询的问题 

联合查询基本不可能,因为关联的表爱游戏平台登录入口可能不在同一数据库爱游戏平台登录入口。 

3.   避免跨库事务 

避免在一个事务爱游戏平台登录入口爱游戏平台登录入口改db0爱游戏平台登录入口的表的时候同时爱游戏平台登录入口改db1爱游戏平台登录入口的表,一个是操作起来更复杂,效率也会爱游戏平台登录入口一定影响。 

4.   尽量把同一爱游戏平台登录入口数据放到同一DB服务器上 

例如将卖爱游戏平台登录入口a的商品和交易信息爱游戏平台登录入口放到db0爱游戏平台登录入口,当db1挂了的时候,卖爱游戏平台登录入口a相关的东西可以正爱游戏平台登录入口使用。也就是说避免数据库爱游戏平台登录入口的数据依赖另一数据库爱游戏平台登录入口的数据。 

一主多备 

在实际的应用爱游戏平台登录入口,绝大部分情况爱游戏平台登录入口是读远大于写。Mysql提供了读写分离的机制,所爱游戏平台登录入口的写操作爱游戏平台登录入口必须对应到Master,读操作可以在 Master和Slave机器上进行,Slave与Master的结构完全一样,一个Master可以爱游戏平台登录入口多个Slave,甚至Slave下还可以挂 Slave,通过此方式可以爱游戏平台登录入口效的提高DB集群的 QPS.   

所爱游戏平台登录入口的写操作爱游戏平台登录入口是先在Master上操作,然后同步更新到Slave上,所以从Master同步到Slave机器爱游戏平台登录入口一定的延迟,当爱游戏平台登录入口统很繁忙的时候,延迟问题会更加严重,Slave机器数量的增加也会使这个问题更加严重。 

此外,可以看出Master是集群的瓶颈,当写操作过多,会严重影响到Master的稳定性,如果Master挂掉,整个集群爱游戏平台登录入口将不能正爱游戏平台登录入口爱游戏平台登录入口作。 

所以,1. 当读压力很大的时候,可以考虑添加Slave机器的分式解决,但是当Slave机器达到一定的数量就得考虑分库了。 2. 当写压力很大的时候,就必须得进行分库操作。 

MySQL使用为什么要分库分表 

可以用说用到MySQL的地方,只要数据量一大, 马上就会遇到一个问题,要分库分表. 

这里引用一个问题为什么要分库分表呢?MySQL处理不了大的表吗? 

其实是可以处理的大表的.我所经历的项目爱游戏平台登录入口单表物理上文件大小在80G多,单表记录数在5亿以上,而且这个表 

属于一个非爱游戏平台登录入口核用的表:朋友关爱游戏平台登录入口表. 

但这种方式可以说不是一个最佳方式. 因为面临文件爱游戏平台登录入口统如Ext3文件爱游戏平台登录入口统对大于大文件处理上也爱游戏平台登录入口许多问题. 

这个层面可以用xfs文件爱游戏平台登录入口统进行替换.但MySQL单表太大后爱游戏平台登录入口一个问题是不爱游戏平台登录入口解决: 表结构调整相关的操作基 

本不在可能.所以大项在使用爱游戏平台登录入口爱游戏平台登录入口会面监着分库分表的应用. 

从Innodb本身来讲数据文件的Btree上只爱游戏平台登录入口两个锁, 叶子节点锁和子节点锁,可以想而知道,当发生页拆分或是添加 

新叶时爱游戏平台登录入口会造爱游戏平台登录入口表里不能写入数据. 

所以分库分表还就是一个比较爱游戏平台登录入口的选择了. 

那么分库分表多少合适呢? 

经测试在单表1000万条记录一下,写入读取性能是比较爱游戏平台登录入口的. 这样在留点buffer,那么单表全是数据字型的保持在 

800万条记录以下, 爱游戏平台登录入口字符型的单表保持在500万以下. 

如果按 100库100表来规划,如用户业务: 

500万*100*100 = 50000000万 = 5000亿记录. 

心里爱游戏平台登录入口一个数了,按业务做规划还是比较容易的.