Mysql在大容量数据下使用总结

Mysql在大容量数据下使用总结

原文:http://www.db2china.net/home/space.php?uid=115557&do=blog&id=32643

说说最近的一些工作情况吧,我们组最近在设备云管理平台的基础开发,目前框架基本完成了,在一定数量的设备情况下,功能可以说已经实现,实现了租户、权限、虚拟等多种云概念。我主要负责用户认证管理和设备信息的收集(日志)。在之前的几轮功能测试中都没有问题,但是在性能测试过程中出现了很大的问题。认证部分按照设定的格式采用API的方式对外提供服务。

 

软件的基本框架如下:

API—————-后台————–bitas———-数据库(Mysql)

|

dubbo/zookeeper

|

前台

其中使用了Spring框架,Bean的大量注入。这也是目前很多平台的基' ;&,"vIE' ;&,"小型的服务器前后台之间采用API的形式,便于后期在移动终端的快速开发和迭代。

 

整体而言,在这个过程中我学会了很多,Java编程,java的反射、注入,服务器设计。总之,这段开发经历是苦逼的,但真的就是痛并快乐着。平台逐步的完善,逐步有了云的感觉。

 

在使用Mysql的过程中出现了大数据量的问题(千万级别),在进行该项目开发前,我的数据库水平真的仅仅停留在基础水平,但项目的实践真的是一个非常不错的学习,不敢说对数据库有了很深刻的理解,但真的比之前考虑的更清晰,至少也经历了大数据量的存储问题。说明一下一些问题:

>>  每天几千万的数据量,如何设计表

目前mysql提供了多种引擎方式,其中比较常用的是MyISAMInnoDB,这两个引擎的差别是其中一个是行锁,一个是表索(MyISAM),因此需要在使用时考虑这两个锁的差异,已经相关的需求。

 

两个引擎在无条件的count时,MyISAM的性能更好,维护了当前所有的表项量,而InnoDB则需要进行遍历。因此性能差异巨大,而当查询添加了条件之后,两个引擎没什么区别,都需要遍历和比较。目前Mysql较新的版本都采用InnoDB,而5.1左右的版本是使用MyISAM。因此在选择数据库时需要对应的版本,当然也可以再创建表的过程中设置对应,即ENGINE = MyISAM/InnoDB等。同样也可以在运行中修改对应的引擎 alter table tablename type=innodb;

因此在创建表的过程中就需要进行相关的考虑,在第一版的过程中由于缺乏经验,根本没有注意这些事项。导致大数据量以后基本不能使用。数据主要用于计算和汇聚有效信息,同时还可以进行相关信息的反向追踪,同时也是为了82号令文件等。当然还有很多后期可用于分析的数据。

 

设备信息收集,在设备数量较少的情况下,一些问题根本不会出现,但在大量设备的情况下,各种问题就会出现。数据如何保存,在项目中数据量非常大,每种信息每天的数据量大约在4千万左右,还有的数据甚至超过1亿。在设计表结果之前就需要分析。

 

Mysql数据库的性能在小数据量的情况下,基本没什么区别,但是当数据量超过百万级,就会出现性能快速下降,导致很多逻辑无法完成。

 

>> 如何建表:

分表,按天或者按星期分表,按千万级甚至亿的级别,通常采用按天分表,甚至有按照小时分表的。在删除数据的过程中直接清空数据,而不需要逐行的删除

建表的过程最好采用简单的存储过程即可实现大量的表创建,减少了创建相同表的大量重复代码。(该存储过程可采用简单的拼接实现)

分区,Mysql支持数据库的分区,目前支持多种的分区方式,分区可以将不同的数据分布在不同的文件系统上。删除数据的过程中可采用删除分区,删除分区即可删除对应的数据。分区中还可以建立对应的索引。

 

就个人的理解,分表很难做到联合查询,特别是当数据量都非常大时。而分区还能进行整表查询。但分表相对简单,且比较好维护。

目前我大部分都采用了分表的方式,但考虑到未来的(只能转接收人处理啦)

 

>> 数据的查询

主键查询,速度很快,大量数据仍然能保持较好的查询效率。但主键在初次设计时很难精研出来。可以尝试多个字段合并成一个主键,或者建立联合主键。

建立索引,大数据量时,在使用索引时实际上也会占用很大的内存,因此不是索引越多越好,索引有点儿类似Hash的意思,能够较快的查询,但是索引的选择很重要。比如选择时间,这个索引量将非常多。同时还可以为表建立多个索引,具体根据业务的查询需求,建立索引之后通常查询的效率会提高不少。索引最好选择一些特征值。

减少使用字符串匹配相关的查询,字符串查询会进行匹配,字符串比较是逐个字符的比较,性能很差。在字符串数量处在一定范围内的情况下,可以采用整形代替字符串。可以建立两种表,其中一张表维护了可能存在的字符串信息,并分配对应的主键ID。在其他使用该字符串的数据表中使用字符串的ID,在大量的字符串匹配操作性能将大大的提高。在做页面(内部服务)点击汇总的过程中直接采用字符串匹配出现了前台卡死,而改用这种机制以后性能大幅度提高。但这种智能针对字符串规模处在一定情况下的方案。

使用Limit查询,数据库查询在大数据量情况下性能非常差,但是如果使用一定的限制条件,这个速度也会提高, 比如查询某台设备上某个用户的上网信息,如果不限制条件,再千万级别的数据库中,如果硬件条件很一般可能要超过10分钟。但是添加limit限制后,性能会大幅度的提高。这种方式可运用在前台显示的处理。若不限制,前台页面会被卡死,用户体验非常差。甚至前后台通信都很有可能就出现异常。

单表的数据删除处理

在Mysql中如果数据超过千万条,删除数据也非常的慢,带有条件的删除会更慢,通常采用分批次删除的方式,在删除中添加Limit。分多次将相关数据删除,否则单纯的删除数据就有可能使得程序无法正常运行,甚至导致数据越来越多。最终整个数据库的数据未被删除,同时BItas相关的软件都会设置超时时间,超过一定的时间就有可能出现异常,因此大数据量的情况下分批次删除是必须的。

时间处理,建议转换为Int性。方便整合和聚合处理,同时能够较好的建立主键或索引。

数据库的更新操作,在java中可以直接先更新,再进行插入操作,通过捕获异常来实现,还可以通过数据库的主键进行插入或更新的写法。Replace into等都是不错的方式,肯定比先查,再更新或插入的效率更高。

 

>> 服务器相关的设计与数据库的交互

在收集设备信息的过程如下:

设备端上传信息—————》服务器

|

解析

|

计算

|

存数据库

从设备角度出发,上传文件最简单,而且便于模块化,只需要按照固定的格式填充字段即可。在服务器上定义各种类型信息的解析处理方法,实现数据的解析。将数据保存到数据库操作,是目前服务器必然会进行的处理。但存在如下的一些问题:

数据库性能差,导致整个过程性能差

通常文件模板固定,解析时间基本也确定,将部分需要运算的信息存放内存,然后将信息存储在数据库中。如果直接进行数据库的操作,信息的插入都是单条或少量数据的插入,多次交互,导致存储数据库部分占用了大部分时间,因此通常需要在插入数据库之前先缓存在内存中,然后周期的同步到数据库,这种批量的插入性能会更高。有测试表明批量插入的性能远超单挑插入。因此尽可能的在数据库中使用事务,提高性能

内存有限,但数据库插入效率有限

为了保证不会出现内存堆积,解析和存储数据库之间的操作必须进行同步管理。最好能够进行设备端、服务器、数据库三者之间的同步管理。同时不同的服务器配置,可能都需要进行相关的测试,确定合适的配置容量。

  服务器和数据库部署在不同设备上

由于文件解析本身占用了系统的IO,而数据库本身也设计到文件操作,可以考虑将两者分开部署,减少服务器的压力。在两台设备上测试,性能有一定的提升。目前只进行相关的测试,还未确定有效。

>> 关于缓存的使用

目前在项目中使用的缓存较少,但是还是有一些可以使用的地方,未来实现分布式系统的过程中必然会使用缓存实现数据共享。

设备管理平台可以讲服务器进行分离,部署在不同的设备上,数据共享必然会使用到缓存。Redis、memcached等方案都可行。

Comments are closed.