大表带来的问题

下面我们来看看除了上面讲的因素之外,还有什么会对数据库的性能,造成影响,首先第一点大表

会给我们带来的问题,另外就是大事务会给我们带来什么样的影响,那什么样的表称之为大表呢,

其实大表就是相对而言的,对于不同的存储引擎,会有不同的限制,以目前InnoDB的版本而言呢,

并没有定义说每个表的最大行数是多少,只要存储空间允许,我们就可以把数据放到MYSQL的表中,

关于MSYQL存储引擎呢,在下面还会有详细的介绍,但是在实际使用过程中呢,我们会发现,当一个表的

数据量超过千万行的时候,就会对数据库的性能造成影响,这里说的大表呢就可以通过维度来进行定义,

一个是一个表的记录超过一千万行,而另一个呢,一个表的数据超过10个G,当然这个定义是相对的,要和我们的

具体业务场景和磁盘IO情况而定,如果我们这个表只是用来记录日志的,只有insert操作和select操作,

而几乎没有update和delete操作的话,这样的表就算是超过1千万行,对我们的业务也不会有什么影响,

但是也是有例外的情况的,之前我们就遇见过这样的一张表,表中的记录差不多10个亿了,但是工作的一直

很稳定,因为很少有人去关注他,只是用于记录一些操作日志,当有一天业务出现变更,日志表插入更多的列,

这时会发现,虽然一张表增加列,就会是一种十分痛苦的事情,当数据被同步到了N个服务器上的时候,那就可以

说是灾难了,另外一个大表的定义,数据库文件超过10个G,这时对于普通的磁盘来说,如果我们使用的是高端磁盘的话,

性能可能就会大一些,那么我们看看大表会带来什么样的问题

首先大表会对查询造成了影响,大表往往就意味着慢查询的产生,前面提高了CPU的处理的效率呢,对于数据库的

性能是至关重要的,而代表往往会降低CPU的处理效率,以这张订单来源日志记录的表来说吧,在这个表中呢,记录了

订单的来源日志,其中订单来源由order_from,这个字段来记录,一共有4中来源,一个是来源于公司的网站,也就是

我们的web,另外就是来源于京东,或者淘宝的第三方平台,刚才说过,之前的公司在国内电商中呢,算是很不错的了,

所以订单表有上亿条记录了,由于之前没有需求对这个表进行查询,所以也一直没有发生什么问题

知道有一天运营要查看一下京东渠道来源的订单量,这就一下产生大量的问题,渠道只有4个,区分度是很低的,

所以要在一个上亿条的表中,筛选出一部分的数据,就要使用大量的磁盘IO,然后磁盘的效率也会很低,再进一步来扩展

这个问题,如果我们要在页面上显示某一个商品从不同渠道来源的订单固定用户的购买,那么这个查询就会在每次,用户访问

商品时,都会执行,这样就产生了大量的慢查询,之所以这样说呢

这就是我之前所遇到过的一个案例,当时就在网页显示各个分类,就是销量的排行,就把整个数据库就给搞死了,

然后这种SQL都是有办法优化的,之后我们也会降到这种查询的优化方式,咱们在这里只是给大家一个印象,就是我们

的大表,会给我们带来的一个影响,还有大表对在DDL数据定义操作也会造成影响,首先来说,大表对于DDL的第一个影响,

我们在大表上建立索引,这个就需要很长的时间,这个会给我们带来什么样的风险呢,当MYSQL5.5之前的版本中,我们使用

MSYQL的InnoDB的话,对于建立索引的操作,实际上是会锁表的,而对于5.5以后的版本,虽然建立索引不会锁表,通常会引起

长时间的主从延迟,也会影响正常的业务,所以要在大表上建立索引,我们一定要非常的小心,在下面的内容中呢,会具体的

谈到,如果在大表中建立索引,希望提供一些帮助

修改表结构是需要长时间锁表的,有什么样的风险呢,首先会造成长时间的主从延迟,这就和建立索引一样了,

由于咱们目前MYSQL的主从这种机制,对于所有的DDL操作,在主库上完成之后,然后到从库上,然后再到从库上进行

相同的操作,这样来完成对表结构的复制的,假设我们一张表在主库上,进行修改需要80秒的时间,一个表结构的修改,

那么在从服务器上呢,至少也需要80秒来进行修改,因为目前MYSQL的主从复制呢,都是使用单线程,所以一旦有大量的修改,

而从服务器上没有完成,其他的数据库操作都无法执行,这样就至少造成480秒以上的主从延迟,这对于大多数应用来说呢,

都是无法接受的,虽然在MYSQL5.6版本中呢,支持多线程的复制,但是也会有一定的限制,这个我们后面再说,2014年我所在的这个公司,

所使用的MYSQL呢,是5.5的版本,所以对这种大表的操作呢,成了一个很棘手的问题,对大表DDL的第二个风险呢,在这种主库上进行表结构

修改的时候呢,这个表所有操作都会被阻塞,这样就会影响正常的数据库操作,如果一个表的访问会很频繁,就像订单来源日志表,我们

每一个订单都会往里面插入,如果我们这个时候对于订单来源日志表进行修改,比如说增加字段,修改某个字段的长度,在刚才的MYSQL

版本中呢,修改都是要进行多表的,那么这个时候呢,如果你要对大的表进行修改呢,你会遇到一个非常棘手的问题,你的数据库的连接数呢,

会猛增,这个是什么原因呢,因为我们连接都被阻塞掉了,因为我们在修改这个表结构,因为所有的修改操作呢,要等表结构修改完成之后,

我们再继续,从前面我们就说过了,数据库连接数被占满了呢,就会出现500错误,这个时候你的老板就得对你大吼了,虽然大表对

我们的数据库有这么大的影响,那么我们就简单看一下如何解决大表的问题

首先对大表的处理方式主要有两种,一种就是咱们听说的分库分表,之所以说是传说的分库分表,相信很少有人

公司实际上做过分库分表的操作,要进行分库分表呢,首先要解决两个难点,一个是分表关键键的选择,这个往往要根据

业务的不同,可以有多种分表的方式,比如对于订单表来说,我们可以以订单号来分表,也可以按地区来分表,所以选择

合适的分表键,我们的后续的业务是十分重要的,另一个难点就是分表之后,跨分区的数据库查询这个问题如何解决,大家

不要认为选择了好的主键之后,就不需要跨分区查询了,好的分区键只能更好的查询,但是这种跨分区查询还是会发生的,

当然还会有其他的问题要解决,这个我们下节内容会详细讲到来给数据库分库分表,我有幸做过分库分表的一个优化,

就是在14年大促之后的事情了,为了解决订单表和订单明细表过大的问题,公司决定,把订单相关表进行分库分表操作,

当时是调用了大量的人力和物力,来进行这个项目,耗时将近两个月完成了,分库分表操作,因为下一次公司大促之前上线,

但是效果还是不错的,但是其中还是出现很多问题,特别是分库分表后使用原有的订单,相关的统计报表,这样的后天操作

功能,合并到一台服务器上,作为后台统计和管理所使用的数据库,下面的章节中我会详细的介绍到,我们分库分表的项目,

如果从上面的介绍呢,大家也可以看出来,分库分表操作呢,需要消耗大量的人力和物力的,同时还要冒着影响先有后端业务的

风险,所以呢我个人认为,分库分表并不是适合所有公司适合的,其实在大多数情况下,我们要选择另外一种方式来对大表进行

一种处理

另外一种处理方式就是对大表进行一种历史数据归档了,特别是对于日志记录的大表呢,像订单这种业务表呢,

我们也可以进行归档,减少对前后端业务的影响,因为对于前端业务来说呢,实际上表结构并没有什么变化,所以

一切的程序都会正常的使用,而只是对于订单表这样的业务表,我们要进行归档,就要增加一个历史查询的入口,

可以说已经归档的历史订单呢,很少有人会进行查询的,所以就算归档表很大,对业务的影响也是很轻微的,而

归档表和正常使用的表可以放在不同的服务器上,这样一方面减少了业务数据,这样一方面减少了热数据在服务器

上的容量,减少核心服务器的查询压力,而对于后端业务来说呢,已经完成相关操作后统计的历史数据,所以对后端的

应用不会有什么影响,但是为了完成不影响前后端,现有的业务逻辑呢,我们对数据进行归档时,两个难点,其一呢是

归档时间点的选择,都是已经不会被使用,或者是很少使用的数据,所以归档时间点呢,比如订单列表我们可以选择

几年前的数据,你也可以选择归档一个月的数据,那么从这里看呢,对于订单列表来说,归档一年前的数据可能更加合适,

而一个月的数据我们就归档一个月的数据就OK了,进行归档数据的第二个难点呢就是如何进行归档操作,对于大表的

增删改查,要十分的小心,如果我们要进行归档,要把已经归档的历史数据呢,从私有的表中进行删除,那如果我们对于

上亿的表的数据分成几十万,不然会轻易的产生主从的延迟,重则就会大量的阻塞,影响咱们正常的业务访问,对于如何

进行大表的增删改查,我们在下面同样会有详细的说明