mysql单库负载过高的处理方式

请点击输入图片描述（最多18字）

经常混迹于技术社区，频繁看到这个题目，今天干脆在自己博客重复一遍解决办法：

针对mysql，sqlserver等关系型数据库单表数据过大的处理方式

如果不是阿里云的分布式数据库 DRDS 那种多机器集群方案的话： 先考虑表分区 ；然后考虑分表 ；然后考虑分库。

这个题目是我所经历过的，我做的是GPS应用，早期版本就是选用的关系型数据库Sql Server。当时我选取的方案就是第一种：表分区。 表分区的优势是，如果表结构合理，可以不涉及到程序修改。也就是说，对程序来讲依然是单表读写的效果！

所有轨迹数据存入到一个巨大的表里。有多大呢？

最大存储量超过10亿行。具体数值应该是12亿多点，由于系统设计为只存储30天轨迹，所以线上期间最大存储只到这个数，再后来采用云架构，上云替换成非关系性数据库，获得了更高的写入性能和存储压缩能力。   每日写入量就超过1500万行。上下班交通高峰时候每秒写入量平均超过500行。也就是500iops，距离系统设计的压测指标3000还有一大截

这张大型单表设计要点：（一个聚集索引用于写入，一个联合索引用于查询，没有主键，使用表分区）

明确主键用途：

真的需要查询单行数据时候才需要主键！

我采用无主键设计，用于避免写入时候浪费维护插入数据的性能。最早使用聚集的类似自增的id主键，压测写入超过5亿行的时候，写入性能缩减一半

准确适用聚集：

写入的数据在硬盘物理顺序上是追加，而不是插入！

我把时间戳字段设置为聚集索引，用于聚集写入目的设计。保证硬盘上的物理写入顺序，不浪费性能用于插入数据

职责足够单一： 

用于精准索引！

使用时间+设备联合索引，保证这张表只有一个查询用途。保证系统只有一种查询目的：按照设备号，查询一个时间段的数据。

精确的表分区：

要求查询时候限定最大量或者最大取值范围！

按天进行表分区，实现大数据量下的高效查询。这里是本文重点，按照聚集索引进行，可以让目标数据局限在更小的范围进行，虽然单表数据上亿，但是查询基本上只在某一天的的几千万里进行索引查询

每张表会有各自的特点，不可生搬硬套，总结下我这张表的特点：

只增，不删，不改！

关于不删除中：每天使用作业删除超过30天的那个分区数据除外，因为要清空旧的表分区，腾出新的表分区！

只有一个业务查询：只按照设备编码查询某个时间段

只有一个运维删除：删除旧的分区数据

这张表，是我技术生涯中进步的一个大阶梯，让我我体会到了系统架构的意义。

虽然我的这张举行表看似只有4个关键点，但是这四个非常精准的关键点设计，耗费了我一个月之久！正是这么足够精准的表结构设计，才撑起了后来压测并发量超过3000的并发写入量！压测的指标跟数据库所在的硬盘有直接关系，当时选取的硬盘是4块10000转的SAS盘做了Raid10的环境

关于后来为什么没有更高的实际应用数值，是因为系统后来改版为云架构，使用了阿里云，更改为写入性能更高的非关系型数

您好，很高兴为您解答。

第一先限制Innodb的并发处理.如果innodb_thread_concurrency = 0 可以先改成 16或是64 看机器压力,如果

非常大,先改成16让机器的压力下来,然后慢慢增达,适应自已的业务.

处理方法: set global innodb_thread_concurrency=16

第二: 对于连接数已经超过600或是更多的情况,可以考虑适当的限制一下连接数,让前端报一下错,也别让DB挂了.

DB在了,总是可以用来加载一下数据,当数据加载到了nosql里了,慢慢的DB压力也会降下来的.

限制单用户连接数在500以下. 如:

set global max_user_connections=500

(MySQL随着连接数的增加性能会是下降的,这也是thread_pool出现的原因)

另外对于有的监控程序会读取information_schema下面的表的程序可以考虑关闭下面的参数

innodb_stats_on_metadata=0

set global innodb_stats_on_metadata=0

这个参数主要防止对读取information_schema时造成大量读取磁盘进行信息统计(如果慢查询中出现关于information_schema中表时,也可以考虑禁用该参数)

处理依据:

当学校的一个食堂一分钟只能为两个打饭, 忽然来了100个时人来打饭,又没排队, 不出会现了打饭的师傅要用点时间

去选择为那个用户服务了, 人越多,场面就越乱, 难免出现用户大吼该他的场面, 最后有可能就出现不是打饭了,而时之间相互

打架了,打饭的师傅也将收到同时有90个以上的Server too busy. 如果能排一下队.最多也就50分钟能处理完了.

以前办法,应该可以让MySQLD不会挂掉.如果业务支撑受到限制,还是想办法处理一下.

如若满意，请点击右侧【采纳答案】，如若还有问题，请点击【追问】

希望我的回答对您有所帮助，望采纳！

~ O(∩_∩)O~

MySQL是一个高速度、高性能、多线程、开放源代码，建立在客户/服务器(Client/Server)结构上的关系型数据库管理系统(RDBMS)。它始于1979年，最初是MichaelWidenius为瑞典TcX公司创建的UNIREG数据库系统，当时的UNIREG没有SQL(StructuredQueryLanguage结构化查询语言)接口，限制了它的应用。

1996年5月，Widenius开发出了MySQL的最初版本，开始在Internet上公开发行。MySQL的开发人员从一开始就一直关注它的性能，为此不惜特性集，直到今天，MySQL依然保持本色，以高速度高性能为首要原则。随着时间的推移，MySQL也加入了大型数据库产品的高级特性，如存储过程、视图、触发器等，使其在企业级数据库系统中开始被部署应用[1]。

2008年10月，SUN公司收购了MySQLAB公司，开始进入开源领域。随着重量级 *** 作系统Solaris的开源，SUNMySQL在数据库市场占有的份额将会进一步提高。因此，在生产环境中部署具有负载均衡功能的MySQL服务器集群，对于提高企业数据库应用系统的速度、稳定性及可伸缩性具有很大的现实意义，也可以有效降低应用系统的投资成本。

一、负载均衡基本思路

在一个服务器集群中，尽可能的平均负载量。通常做法是在服务器前端设置一个负载均衡器（专门的硬件设备），MySQL的负载均衡，通常都离不开数据分片（把数据分割成小块，存储到不同的db节点中）、复制等 *** 作。

负载均衡的主要贡献，除了均发数据库请求，还可提供管理读/写策略。在分发请求时则确定那些节点可写，可读，随即将请求发送到指定节点上执行 *** 作。

二、实现负载均衡的方式：

1、mysql读写分离：

mysql复制时，产生了多个数据副本（备库），为减少服务器压力，备库用于处理读 *** 作，主库可同时处理读写是mysql集群实现读写分离的常用策略。

由于备库的复制是异步的，无法实时同步，读写分离的主要难点也在于备库上的脏数据。通常如果使用备库进行读，一般对数据的实时性要求不能太高。对此，mysql提供了几种常见的读写分离方式，例如基于查询的读写分离、基于脏数据、基于会话等，有兴趣可继续研究。

mysql设置的读写分离，减少了主库的请求量，将大量读的 *** 作发送给备库，实现负载均衡。

2、修改DNS

在高并发负载均衡（一）——企业架构分析和DNS中详细介绍了DNS以及DNS如何实现负载，简言之，通过n个服务器IP指定到一个域名，根据请求的不同标识特征，将请求发送给不同的IP服务器进行处理。

3、引入中间件

mysql官方提供了一个mysql负载的中间件，mysql_proxy，也需要在服务器上进行安装，修改配置文件（mysql的服务器IP）,实质与nginx类似，也是一个代理服务器。

4、利用mysql复制分流查询 *** 作

利用mysql的主从复制可以有效的分流更新 *** 作和查询 *** 作，具体的实现是一个主服务器，承担更新 *** 作，多台从服务器，承担查询 *** 作，主从之间通过复制实现数据的同步。多台从服务器一方面用来确保可用性，一方面可以创建不同的索引满足不同查询的需要。

对于主从之间不需要复制全部表的情况，可以通过在主的服务器上搭建一个虚拟的从服务器，将需要复制到从服务器的表设置成blackhole引擎，然后定义replicate-do-table参数只复制这些表，这样就过滤出需要复制的binlog，减少了传输binlog的带宽。因为搭建的虚拟的从服务器只起到过滤binlog的作用，并没有实际纪录任何数据，所以对主数据库服务器的性能影响也非常的有限。

通过复制分流查询的存在的问题是主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在差异，造成查询结果的异议，应用在设计的时候需要有所考虑。

5、采用分布式数据库架构

mysql从5.0.3开始支持分布式事务，当前分布式事务只对Innodb存储引擎支持。分布式的数据库架构适合大数据量，负载高的情况，有良好的扩展性和高可用性。通过在多台服务器之间分布数据实现在多台服务器之间的负载平均，提高了访问的执行效率。具体实现的时候，可以使用mysql的Cluster功能（NDB引擎）或者自己编写程序来实现全局事务。

---