CN111522880B - 一种基于mysql数据库集群的提升数据读写性能的方法 - Google Patents

Abstract

本发明公开了一种基于mysql数据库集群的提升数据读写性能的方法，包括搭建mysql数据库集群系统；利用mycat中间体实现mysql数据库集群系统读写分离、分库功能；指定表的存储引擎为MYISAM；创建新表时实施mysql分区策略；使用redis数据库进行数据缓存。利用mycat+mysql设计了一套大型mysql数据库集群系统，能够将滑坡、泥石流实时监测数据快速写入数据库，使用MYISAM数据表保存北斗高精度定位数据；能够利用多主多从的结构提升数据存储可靠性，使用mysql分区进一步提升查询效率；能够利用关键词索引提高检索速度，使用redis缓存来提升读取性能；能够实现读写分离、分库功能，有效提高数据读写速度。整体上确保了随着数据量的日益增加，系统的读写性能不会出现降低。

Description

一种基于mysql数据库集群的提升数据读写性能的方法

技术领域

本发明涉及数据库技术领域，尤其涉及一种基于mysql数据库集群的提升数据读写性能的方法。

背景技术

我国地貌复杂多变，地质灾害频发，滑坡、泥石流等地质灾害严重威胁着人民群众的生命财产安全。为了有效预防地质灾害，保障人民生命和财产的安全，开发了一套北斗智能实时监测预警系统。在对滑坡、泥石流等地质灾害定点监测的同时，实时动态监测数据的读写性能关系着整个监测预警系统的运维效率，系统读写数据速度快，能有效提高数据存储与查询效率，提升用户访问速度，保证实时发布预警信息。所以，提高系统读写能力是地质灾害监测预警的关键，然而在当前的解决方案中，大部分只利用单一的主从mysql数据库对北斗高精度定位数据进行存储，随着监测站点的增加，单一主从数据库容易达到瓶颈，导致读写数据速度慢，预警信息发布不及时，给人民群众的生命财产构成了一定的威胁。

发明内容

本发明的目的在于提供一种基于mysql数据库集群的提升数据读写性能的方法，利用mycat+mysql设计了一套大型mysql数据库集群系统，能够将滑坡、泥石流等实时监测数据快速写入数据库，使用MYISAM数据表保存北斗高精度定位数据；能够利用多主多从的结构提升数据存储可靠性，使用mysql分区进一步提升查询效率；能够利用关键词索引提高检索速度，使用redis缓存来提升读取性能；能够实现读写分离、分库功能，有效提高数据读写速度。整体上确保了随着数据量的日益增加，系统的读写性能不会出现降低。

为实现上述目的，本发明提供了一种基于mysql数据库集群的提升数据读写性能的方法，包括：

通过多台mysql数据库服务器之间的主从复制结构和主主复制结构，利用mycat中间件，共同搭建mysql数据库集群系统；

通过配置文件对所述mysql数据库集群系统进行读写分离和分库；

选择表的存储引擎为缺省存贮引擎，并进行表的存储；

基于分区规则，对mysql数据库进行分区；

基于redis数据库进行北斗高精度定位数据缓存。

在一实施方式中，通过多台mysql数据库服务器之间的主从复制结构和主主复制结构，利用mycat中间件，共同搭建mysql数据库集群系统，具体包括：

建立由一台mysql数据库服务器中的主数据库和从数据库组成的主从复制结构；

建立由两台mysql数据库服务器之间互为主从复制结构组成的主主复制结构；

建立由多台mysql数据库服务器中的主数据库和从数据库组成主从复制结构，以及多台mysql数据库服务器之间互为主从复制结构组成的主主复制结构共同组成多主多从结构；

配置mysql数据库服务器的关联IP，完成mysql数据库集群系统的搭建。

在一实施方式中，建立由一台mysql数据库服务器中的主数据库和从数据库组成的主从复制结构，具体包括：

一台mysql数据库服务器中的主数据库启用二进制日志文件后，主数据库中的所有操作都会以事件的方式记录在二进制日志文件中，从数据库通过输入输出接口与主数据库保持通信，并监控主数据库的二进制日志文件的变化。

在一实施方式中，监控主数据库的二进制日志文件的变化，具体包括：

当检测到主数据库的二进制日志文件发生变化，则获取变化数据复制至从数据库的中继日志中，从数据库的一个SQL线程会把对应相关事件执行至从数据库中，完成主从复制。

在一实施方式中，通过配置文件对所述mysql数据库集群系统进行读写分离和分库，具体包括：

获取用户发送过来的SQL语句，基于配置文件对所述SQL语句进行特定分析；所述配置文件包括用户配置及权限文件、表拆分规则文件和配置项文件；所述特定分析包括分片分析、路由分析、读写分离分析和缓存分析；

发送分析处理后的SQL语句至mysql物理服务器；

接收所述mysql物理服务器返回的结果进行处理发送至用户端。

在一实施方式中，获取用户发送过来的SQL语句，基于配置文件对所述SQL语句进行特定分析，具体包括：

基于配置项文件注入数据库名，并添加数据节点，根据表内数据内在的逻辑关系，将同一个表按照分片枚举原则分散到多个数据库中，每个数据库中的表包含目标数量数据。

在一实施方式中，选择表的存储引擎为缺省存贮引擎，并进行表的存储，具体包括：

将每张表存放至存放表格文件、数据文件和索引文件中。

在一实施方式中，基于分区规则，对mysql数据库进行分区，具体包括：

根据分区规则，将一个表分割成N个区块，所述分区规则包括区间值、范围值、特定值列表或者hash函数值；其中，根据日期范围将数据分配到不同的区块，具体包括：

当执行带有日期条件的查询语句时，确定扫描的区块，返回对应的结果。

在一实施方式中，基于redis数据库进行北斗高精度定位数据缓存，具体包括：

在内存中存储数据查询备份，当数据未发生本质改变时，直接获取内存中的数据；

基于定期删除策略、惰性删除策略和内存淘汰机制进行内存清理。

在一实施方式中，基于定期删除策略、惰性删除策略和内存淘汰机制进行内存清理，具体包括：

基于定期删除策略100ms随机检查一次钥匙，判断是否有过期的钥匙，若有过期钥匙则删除；

基于惰性删除策略获取设置生存时间的钥匙，判断设置生存时间的钥匙是否过期，若过期则删除对应的设置生存时间的钥匙；

基于内存淘汰机制获取定期未删除又未即时请求的钥匙，进行分批淘汰处理。

本发明的一种基于mysql数据库集群的提升数据读写性能的方法，通过搭建mysql数据库集群系统；利用mycat中间体实现mysql数据库集群系统读写分离、分库功能；指定表的存储引擎为MYISAM；创建新表时实施mysql分区策略；使用redis数据库进行数据缓存。利用mycat+mysql设计了一套大型mysql数据库集群系统，能够将滑坡、泥石流实时监测数据快速写入数据库，使用MYISAM数据表保存北斗高精度定位数据；能够利用多主多从的结构提升数据存储可靠性，使用mysql分区进一步提升查询效率；能够利用关键词索引提高检索速度，使用redis缓存来提升读取性能；能够实现读写分离、分库功能，有效提高数据读写速度。整体上确保了随着数据量的日益增加，系统的读写性能不会出现降低。

附图说明

为了更清楚地说明本发明实施例或现有技术中的技术方案，下面将对实施例或现有技术描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图仅仅是本发明的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其他的附图。

图1是本发明实施例提供的一种基于mysql数据库集群的提升数据读写性能的方法的流程示意图；

图2是本发明实施例提供的步骤S101的具体流程示意图；

图3是本发明实施例提供的步骤S102的具体流程示意图；

图4是本发明实施例提供的步骤S105的具体流程示意图。

具体实施方式

下面详细描述本发明的实施例，所述实施例的示例在附图中示出，其中自始至终相同或类似的标号表示相同或类似的元件或具有相同或类似功能的元件。下面通过参考附图描述的实施例是示例性的，旨在用于解释本发明，而不能理解为对本发明的限制。

请参阅图1，图1是本发明实施例提供的一种基于mysql数据库集群的提升数据读写性能的方法的流程示意图，具体的，所述基于mysql数据库集群的提升数据读写性能的方法可以包括以下步骤：

S101、通过多台mysql数据库服务器之间的主从复制结构和主主复制结构，利用mycat中间件，共同搭建mysql数据库集群系统；

本发明实施例中，请参阅图2，首先要实现mysql数据库服务器之间的主从复制、主主复制，具体为建立由一台mysql数据库服务器中的主数据库和从数据库组成的主从复制结构；主从数据库之间数据复制的基础是二进制日志文件(binary log file)，一台mysql数据库服务器中的主数据库启用二进制日志文件后，主数据库中的所有操作都会以事件的方式记录在二进制日志文件中，从数据库通过输入输出接口即I/O线程与主数据库保持通信，并监控主数据库的二进制日志文件的变化。当检测到主数据库的二进制日志文件发生变化，则获取变化数据复制至从数据库的中继日志中，从数据库的一个SQL线程会把对应相关事件执行至从数据库中，以此实现从数据库和主数据库的一致性，完成主从复制。建立由两台mysql数据库服务器之间互为主从复制结构组成的主主复制结构，也就实现了主主复制；建立由多台mysql数据库服务器中的主数据库和从数据库组成主从复制结构，以及多台mysql数据库服务器之间互为主从复制结构组成的主主复制结构共同组成多主多从结构；配置mysql数据库服务器的关联IP，完成mysql数据库集群系统的搭建。其中mysql数据库集群系统可实现多主多从，mycat用于实际处理用户请求，维护与集群系统中其他服务器上数据库的连接。不再是单一的主从mysql数据库对北斗高精度定位数据进行存储，避免数据存储瓶颈，导致读写数据速度慢，预警信息发布不及时，给人民群众的生命财产构成了一定的威胁。

S102、通过配置文件对所述mysql数据库集群系统进行读写分离和分库；

本发明实施例中，请参阅图3，mycat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的mysql物理服务器。mycat的原理并不复杂，复杂的是代码。mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。具体的获取用户发送过来的SQL语句，基于配置文件对所述SQL语句进行特定分析；所述配置文件包括用户配置及权限文件即server.xml文件、表拆分规则文件即rule.xml文件和配置项文件即schema.xml文件，server.xml文件其实跟读写分离、分库分表策略关系不大，但是需要用此文件来配置连接mycat的用户及权限等，rule.xml文件定义了对表进行拆分所涉及到的规则，可以灵活的对表使用不同的分片算法，或者对表使用相同的算法但具体的参数不同，schema.xml文件是最主要的配置项，读写分离、分库分表策略、分片节点都是在此文件中配置的。Schema.xml文件中的dataNode标签属于分片信息，也就是分库相关配置。基于配置项文件注入数据库名，并添加数据节点，根据表内数据内在的逻辑关系，将同一个表按照分片枚举原则分散到多个数据库中，每个数据库中的表包含目标数量数据。即注入数据库名，并添加数据节点，根据表内数据内在的逻辑关系，将同一个表按照分片枚举原则分散到多个数据库中，每个数据库中的表只包含一部分数据，从而使得单个表的数据量变小，解决了单库单表数据量过大、高并发性能瓶颈问题。所述特定分析包括分片分析、路由分析、读写分离分析和缓存分析；发送分析处理后的SQL语句至mysql物理服务器；接收所述mysql物理服务器返回的结果进行处理发送至用户端。大多数读写分离的案例是同时支持高可用性的，即mycat+mysql主从复制的集群，并开启mycat的读写分离功能，这种场景需求下，mycat是最为简单并且功能最为丰富的一类Proxy，正常情况下，配置文件也最为简单，不用每个表配置，只需要在schema.xml中的元素上增加dataNode＝“defaultDN”属性，并配置此dataNode对应的真实物理数据库的database，然后dataHost开启读写分离功能即可。

S103、选择表的存储引擎为缺省存贮引擎，并进行表的存储；

本发明实施例中，MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是IndexedSequential Access Method(有索引的顺序访问方法)的缩写，它是存储记录和文件的标准方法，不是事务安全的，而且不支持外键，如果执行大量的select，insert，MyISAM比较适合。

InnoDB：支持事务安全的引擎，支持外键、行锁、事务是它的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

MyISAM是mysql缺省存贮引擎，每张MyISAM表被存放在三个文件中，三个文件是存放表格文件、数据文件和索引文件，存放表格文件是Frm，数据文件是MYD(MYData)，索引文件是MYI(MYIndex)引伸。MyISAM适合读入数据多、并发少的场景，而且MyISAM相对简单，在效率上要优于InnoDB。

MyISAM支持全文索引(FULLTEXT)，InnoDB不支持，MyISAM引擎的表查询、新增的效率要比InnoDB高得多。MyISAM保存表的总行数，执行selectcount(*)from table时，可以直接获取到结果，InnoDB没有保存表的总行数，需要扫描全部数据后得到结果。所以如果是对读取速度要求比较快的应用还是选MyISAM。本方法为有效提高数据读取速度，选取了MyISAM存储引擎。

S104、基于分区规则，对mysql数据库进行分区；

本发明实施例中，根据分区规则，将一个表分割成N个区块，所述分区规则包括区间值、范围值、特定值列表或者hash函数值；其中，根据日期范围将数据分配到不同的区块，具体包括：当执行带有日期条件的查询语句时，确定扫描的区块，返回对应的结果。具体的mysql分区是根据一定的规则，把一个表分割成N个更小的、更容易管理的区块。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上底层是由N个物理区块组成的，每个分区都是一个独立的对象，可以独立处理，也可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。mysql分区有利于管理数据非常大的表，它采用分而治之的逻辑，引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中。本发明方法中mysql提供的分区属于横向分区中的range分区模式，根据日期范围将数据分配到不同的分区，当执行带有日期条件的查询语句时，mysql可以很迅速地确定扫描哪个分区，并立即返回结果，有效地提高了查询效率。

S105、基于redis数据库进行北斗高精度定位数据缓存。

本发明实施例中，请参阅图4，在内存中存储数据查询备份，当数据未发生本质改变时，直接获取内存中的数据；基于定期删除策略、惰性删除策略和内存淘汰机制进行内存清理。基于定期删除策略100ms随机检查一次钥匙，判断是否有过期的钥匙，若有过期钥匙则删除；基于惰性删除策略获取设置生存时间的钥匙，判断设置生存时间的钥匙是否过期，若过期则删除对应的设置生存时间的钥匙；基于内存淘汰机制获取定期未删除又未即时请求的钥匙，进行分批淘汰处理。具体的redis是非关系型数据库Nosql的代表，采用的是基于内存的单进程单线程模型的KV数据库，数据结构简单，不需要建表存储数据，直接存储，而且是分布式可扩展的。redis缓存原理就是在内存中存储数据查询备份，当数据没有发生本质改变时，阻止数据查询去数据库进行操作，而直接在内存中取数据，大大降低了数据库的读写次数，而且从内存中读数据的速度比去数据库查询要快一些，同时又提高了效率。本发明方法中redis采用的是定期删除策略+惰性删除策略+内存淘汰机制，它可以处理超大量的数据，运行在便宜的pc服务器集群上，优化sql语句，提高集群处理数据能力。定期删除策略默认每个100ms随机检查一次，是否有过期的key，有过期key则删除。如果只采用定期删除策略，会导致很多key到时间没有删除。惰性删除策略可以解决上述问题，在获取某个已经设置了生存时间的key时，redis惰性删除策略会检查一下这个key是否已经过期，如果过期了就会删除。如果定期删除没删除key，也没有即时去请求key，即惰性删除没生效，redis的内存会越来越高，此时可以采用内存淘汰机制，分批处理已过期的key。

针对建立的北斗智能实时监测预警系统在单表数据量达到7000万条记录以上时，无法满足数据实时分析处理的性能需求；无法在1秒种内查询出满足条件的实时数据；无法在5秒钟内查询出绘制跨月或跨年度形变曲线所需的数据。本发明提供一种基于mysql数据库集群的提升数据读写性能的方法，该方法首先建立一套大型mysql数据库集群系统，利用该集群系统实现读写分离、分库功能，并采用MYISAM存储引擎保存北斗高精度定位数据，使用mysql分区提升读取性能，而且够利用关键词索引提高检索速度，使用redis缓存机制提高查询效率，利用读写分离、分库、分区和缓存的方法，确保随着数据量的日益增加，系统的读写性能不会出现降低。

在利用北斗卫星导航系统对地质灾害进行实时监测时，由于需要不断存储大量北斗高精度定位数据，单库单表随着存储数据量的增加，很容易达到瓶颈，导致系统的查询率效不断下降，写入数据效率也会随着终端设备的不断增加而降低。使用基于mysql数据库集群的提升数据读写性能的方法，能够实现读写分离、分库功能，提高数据读写速度与系统稳定性；能够快速有序地将数据写入数据库，合理分配到数据存储表；能够利用mysql分区，使用redis缓存机制来提升读取性能；能够有效提高服务器负载均衡能力，提升用户访问速度。

以上所揭露的仅为本发明一种较佳实施例而已，当然不能以此来限定本发明之权利范围，本领域普通技术人员可以理解实现上述实施例的全部或部分流程，并依本发明权利要求所作的等同变化，仍属于发明所涵盖的范围。

Claims (5)

1.一种基于mysql数据库集群的提升数据读写性能的方法，其特征在于，包括：

通过多台mysql数据库服务器之间的主从复制结构和主主复制结构，利用mycat中间件，共同搭建mysql数据库集群系统；

通过配置文件对所述mysql数据库集群系统进行读写分离和分库；

选择表的存储引擎为缺省存贮引擎，并进行表的存储；

基于分区规则，对mysql数据库进行分区；

基于redis数据库进行北斗高精度定位数据缓存；

通过多台mysql数据库服务器之间的主从复制结构和主主复制结构，利用mycat中间件，共同搭建mysql数据库集群系统，具体包括：

建立由一台mysql数据库服务器中的主数据库和从数据库组成的主从复制结构；

建立由两台mysql数据库服务器之间互为主从复制结构组成的主主复制结构；

建立由多台mysql数据库服务器中的主数据库和从数据库组成主从复制结构，以及多台mysql数据库服务器之间互为主从复制结构组成的主主复制结构共同组成多主多从结构；

配置mysql数据库服务器的关联IP，完成mysql数据库集群系统的搭建；

建立由一台mysql数据库服务器中的主数据库和从数据库组成的主从复制结构，具体包括：

一台mysql数据库服务器中的主数据库启用二进制日志文件后，主数据库中的所有操作都会以事件的方式记录在二进制日志文件中，从数据库通过输入输出接口与主数据库保持通信，并监控主数据库的二进制日志文件的变化；

监控主数据库的二进制日志文件的变化，具体包括：

当检测到主数据库的二进制日志文件发生变化，则获取变化数据复制至从数据库的中继日志中，从数据库的一个SQL线程会把对应相关事件执行至从数据库中，完成主从复制；

通过配置文件对所述mysql数据库集群系统进行读写分离和分库，具体包括：

获取用户发送过来的SQL语句，基于配置文件对所述SQL语句进行特定分析；所述配置文件包括用户配置及权限文件、表拆分规则文件和配置项文件；所述特定分析包括分片分析、路由分析、读写分离分析和缓存分析；

发送分析处理后的SQL语句至mysql物理服务器；

接收所述mysql物理服务器返回的结果进行处理发送至用户端；

获取用户发送过来的SQL语句，基于配置文件对所述SQL语句进行特定分析，具体包括：

基于配置项文件注入数据库名，并添加数据节点，根据表内数据内在的逻辑关系，将同一个表按照分片枚举原则分散到多个数据库中，每个数据库中的表包含目标数量数据。

2.如权利要求1所述的基于mysql数据库集群的提升数据读写性能的方法，其特征在于，选择表的存储引擎为缺省存贮引擎，并进行表的存储，具体包括：

将每张表存放至存放表格文件、数据文件和索引文件中。

3.如权利要求1所述的基于mysql数据库集群的提升数据读写性能的方法，其特征在于，基于分区规则，对mysql数据库进行分区，具体包括：

根据分区规则，将一个表分割成N个区块，所述分区规则包括区间值、范围值、特定值列表或者hash函数值；其中，根据日期范围将数据分配到不同的区块，具体包括：

当执行带有日期条件的查询语句时，确定扫描的区块，返回对应的结果。

4.如权利要求1所述的基于mysql数据库集群的提升数据读写性能的方法，其特征在于，基于redis数据库进行北斗高精度定位数据缓存，具体包括：

在内存中存储数据查询备份，当数据未发生本质改变时，直接获取内存中的数据；

基于定期删除策略、惰性删除策略和内存淘汰机制进行内存清理。

5.如权利要求4所述的基于mysql数据库集群的提升数据读写性能的方法，其特征在于，基于定期删除策略、惰性删除策略和内存淘汰机制进行内存清理，具体包括：

基于定期删除策略100ms随机检查一次钥匙，判断是否有过期的钥匙，若有过期钥匙则删除；

基于惰性删除策略获取设置生存时间的钥匙，判断设置生存时间的钥匙是否过期，若过期则删除对应的设置生存时间的钥匙；

基于内存淘汰机制获取定期未删除又未即时请求的钥匙，进行分批淘汰处理。