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

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

软件的基本框架如下:

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

                                    |

                            dubbo/zookeeper

                                    |

                                 前台

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

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

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

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

目前mysql提供了多种引擎方式，其中比较常用的是MyISAM和InnoDB,这两个引擎的差别是其中一个是行锁，一个是表索（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等方案都可行。