记录些MySQL题集（14）

lichunericli

于 2024-07-17 23:48:46 发布

阅读量549

点赞数 20

分类专栏：题集文章标签：数据库 mysql

本文链接：https://blog.csdn.net/lichunericli/article/details/140417420

版权

题集专栏收录该内容

24 篇文章 0 订阅

订阅专栏

MySQL 线上问题定位并解决

一、线上故障排查的思路与方向

在程序开发与运行过程中，出现Bug问题的几率无可避免，数据库出现问题一般会发生在下述几方面：

• ①撰写的SQL语句执行出错，俗称为业务代码Bug。
• ②开发环境执行一切正常，线上偶发SQL执行缓慢的情况。
• ③线上部署MySQL的机器故障，如磁盘、内存、CPU100%，MySQL自身故障等。

1.1、线上排查及其解决问题的思路

相对而言，解决故障问题也好，处理性能瓶颈也罢，通常思路大致都是相同的，步骤如下：

• ①分析问题：根据理论知识+经验分析问题，判断问题可能出现的位置或可能引起问题的原因，将目标缩小到一定范围。
• ②排查问题：基于上一步的结果，从引发问题的“可疑性”角度出发，从高到低依次进行排查，进一步排除一些选项，将目标范围进一步缩小。
• ③定位问题：通过相关的监控数据的辅助，以更“细粒度”的手段，将引发问题的原因定位到精准位置。
• ④解决问题：判断到问题出现的具体位置以及引发的原因后，采取相关措施对问题加以解决。
• ⑤尝试最优解（非必须）：将原有的问题解决后，在能力范围内，且环境允许的情况下，应该适当考虑问题的最优解（可以从性能、拓展性、并发等角度出发）。

当然，上述过程是针对特殊问题以及经验老道的开发者而言的，作为“新时代的程序构建者”，那当然得学会合理使用工具来帮助我们快速解决问题：

• ①摘取或复制问题的关键片段。
• ②打开百度或谷歌后粘贴搜索。
• ③观察返回结果中，选择标题与描述与自己问题较匹配的资料进入。
• ④多看几个后，根据其解决方案尝试解决问题。
• ⑤成功解决后皆大欢喜，尝试无果后“找人/问群”。
• ⑥“外力”无法解决问题时自己动手，根据之前的步骤依次排查解决。

1.2、线上排查的方向

数据库出现Bug的几率仅占一小部分，实际上一个业务系统中，各层面的节点都有可能存在一定的故障，但通常情况下来说，系统部署在线上出现问题，经过分析排查后，最终诱发问题的根本原因无非在于如下几点：

• 应用程序本身导致的问题
- • 程序内部频繁触发GC，造成系统出现长时间停顿，导致客户端堆积大量请求。
- • JVM参数配置不合理，导致线上运行失控，如堆内存、各内存区域太小等。
- • Java程序代码存在缺陷，导致线上运行出现Bug，如死锁/内存泄漏、溢出等。
- • 程序内部资源使用不合理，导致出现问题，如线程/DB连接/网络连接/堆外内存等。
• 上下游内部系统导致的问题
- • 上游服务出现并发情况，导致当前程序请求量急剧增加，从而引发问题拖垮系统。
- • 下游服务出现问题，导致当前程序堆积大量请求拖垮系统，如Redis宕机/DB阻塞等。
• 程序所部署的机器本身导致的问题
- • 服务器机房网络出现问题，导致网络出现阻塞、当前程序假死等故障。
- • 服务器中因其他程序原因、硬件问题、环境因素（如断电）等原因导致系统不可用。
- • 服务器因遭到入侵导致Java程序受到影响，如木马病毒/矿机、劫持脚本等。
• 第三方的RPC远程调用导致的问题
- • 作为被调用者提供给第三方调用，第三方流量突增，导致当前程序负载过重出现问题。
- • 作为调用者调用第三方，但因第三方出现问题，引发雪崩问题而造成当前程序崩溃。

虽然上述中没有将所有可能会发生问题的位置写到，但总的来说，发生问题排查时，也就是这几个大的方向，先将发生问题的大体定位，然后再逐步推导出具体问题的位置，从而加以解决。

二、SQL语句执行出错排查

作为一个程序员，对MySQL数据库而言，接触最多的就是SQL语句的撰写，和写业务代码时一样，写代码时会碰到异常、错误，而写SQL时同样如此，比如：

ERROR 1064 (42000): You have an error in your SQL syntax; 
    check the manual that corresponds to your MySQL server version 
        for the right syntax to use near 'xxxxxxx' at line 1

MySQL的错误信息会由三部分组成：

• ErrorCode：错误码，上述错误信息中的1064。
• SQLState：SQL状态，也就是前面信息中的42000。
• ErrorInfo：错误详情，：后面跟的一大长串描述则是具体的错误详情。

当在执行SQL时出现问题，一般都会抛出对应的错误信息，拿到了这些错误信息之后，其实解决的办法就比较简单了，首先可以参考具体的错误详情，如果英语好的小伙伴可以直接尝试阅读，如果英语不好的小伙伴可以借助一些翻译工具来转换成中文，比如上述的错误信息，从其给出的错误详情大致能够阅读出原因：

原文：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xxxxxxx' at line 1
翻译：您的SQL语法有错误；请查看与MySQL-Server版本对应的手册，以了解在第1行“xxxxxxx”附近正确语法的使用方式。

从这段错误提示中可以明显得知：目前咱们执行SQL时遇到了一个语法错误，也就是SQL写错了，具体位置是第一行的'xxxxxxx'位置，所以如果要解决这个问题就很简单了，找到第一行'xxxxxxx'位置，观察后把SQL改对即可。

上述这种方式适用于绝大部分SQL报错的情况，如果遇到一个少见的错误，自己也无法从给出的错误信息中定位问题，这时最好的解决办法并不是自己研究，或者去问他人，而是直接百度/谷歌，作为一位合格的开发者，必须要能够熟练运用搜索引擎来解决问题，开发过程中遇到的80%问题都可以直接从网上找到答案，所以与其自己花精力去思考，或者去麻烦别人解决，不如直接在网上找现成的解决方案。

SQL执行报错，在网上要找这类问题的解决方案其实十分简单，毕竟 MySQL内部对每种不同的错误都有对应的错误码，所以出现错误信息时，先直接去搜索对应的错误码即可，如下：

百度

基本上百度后就能出现对应的错误码详解，以及错误码出现后又该如何解决，这时我们需要做的仅仅只是点进去跟着操作即可，这种方法也是最省事省力的方案。

确实网上的资源无法提供，这时只能自行手动介入排查，最经典也是最有效的排查方法，即是人.肉排查大法。

三、MySQL线上慢查询语句排查

有些SQL可能在开发环境没有任何问题，但放到线上时就会出现偶发式执行耗时较长的情况，所以这类情况就只能真正在线上环境才能测出来，尤其是一些不支持灰度发布的中小企业，也只能放到线上测才能发现问题。

对于排查慢查询SQL而言，首先要做到的就是定位哪些SQL执行时较为耗时，但总不能把所有的SQL都做一遍监控吧？这样未免太影响性能了，其实对于这点咱们无需担心，在MySQL内部实际上早已提供了相关支持，也就是慢查询日志，在项目上线之前手动开启一下慢查询日志即可。

但开启慢查询日志需要配置两个关键参数：

• slow_query_log：取值为on、off，默认为off关闭，项目上线前需要手动开启。
• long_query_time：指定记录慢查询日志的阈值，单位是秒，要指定更细粒度可以用小数表示。

重点是第二个参数，这个慢查询阈值并没有固定的标准，不同的业务系统取值也并不相同，为啥这样说呢？举个例子：现在有两个系统:

• 一个是报表系统，主要负责对账、跑批、统计分析、报表导出....等工作。
• 另一个系统则是一个门户网站，与用户直接交互，主要负责处理用户请求。

以上述这两个系统为例，将两个系统的慢查询日志阈值都设置为1s合适吗？这显然并不合适，因为报表系统中的大量操作基本上都会超出这个时间，毕竟涉及了大量的逻辑处理和运算的耗时工作。那如果将阈值调大呢？比如设置成5s合适吗？也不合适，因为这样可能会监控到报表系统的慢查询SQL，但却无法监控到门户网站的慢查询语句，毕竟在门户网站中超过800ms就算比较慢了。

因此从上述这个案例中可明显感受出来，对于慢查询日志的阈值分配，是一个比较讲究的技术活，设小了可能会收集到大量执行并不慢的SQL语句，设大了又可能造成真正的慢查询语句无法被记录下来。那么具体该如何设置合理的大小呢？

如果想要每次MySQL重启时，慢查询的配置都生效，请记住不用使用set的方式在线修改，而是要将对应的参数配置到MySQL的配置文件中。

3.1、查看慢查询日志

查看慢查询日志的方式，一般如果你的项目配备了完善的监控系统，通常情况下会自动去读取磁盘中的慢查询日志，然后可直接通过监控系统的大屏来观察。但如若未具备完善的监控系统，也可以通过cat这类命令去查看本地的日志文件，慢查询日志的磁盘文件默认位于MySQL的安装目录下，也可以通过slow_query_log_file=/xxx/xxx/xx.log的方式去手动指定。

下面举个例子来做实验，这里我可能会将慢查询阈值设置的很小，主要是为了观察到慢查询日志，因此诸位请不要纠结，实际的慢查询阈值还是需要根据业务来进行合理配置，如下：

-- 在MySQL客户端中直接使用 set 的方式修改

-- 先开启慢查询日志
setglobal slow_query_log =on;

-- 再查询默认的慢查询阈值（默认为10秒）
show variables like'long_query_time';
+-----------------+-----------+
|Variable_name|Value|
+-----------------+-----------+
| long_query_time |10.000000|
+-----------------+-----------+

-- 手动修改慢查询阈值（设置为10ms）
setglobal long_query_time =0.01;

-- 由于前面使用了global关键字设置了全局生效，因此需要重新连接
quit

-- 重连后再次查询慢查询阈值（已生效）
show variables like'long_query_time';
+-----------------+----------+
|Variable_name|Value|
+-----------------+----------+
| long_query_time |0.010000|
+-----------------+----------+

上面这组配置的含义是：当有查询语句的执行时长超过10ms，MySQL就会自动将其记录到慢查询日志中：

-- 一条普普通通的子查询语句
select
*
from
    zz_users 
where
    user_id in(
select user_id from zz_users where user_id <5
)
and
    register_time < now();
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|1|熊猫|女|6666|2022-08-1415:22:01|
|2|竹子|男|1234|2022-09-1416:17:44|
|3|子竹|男|4321|2022-09-1607:42:21|
|4|黑熊|男|8888|2022-09-1723:48:29|
+---------+-----------+----------+----------+---------------------+
4rowsinset(0.015 sec)

从上述的执行结果来看，其实这里执行的耗时为15ms，已经超出了咱们设定的10ms，那接着一起去看看磁盘中的慢查询日志吧！如果你不清楚你本地慢查询日志文件的位置，可以通过下述命令查询：

• show variables like 'slow_query_log_file';

此时找到本地的慢查询日志，接着打开它来瞧瞧其中是否有数据呢？如下：

慢查询日志

因为我是在Windows系统，所以可以直接用记事本打开，如果你是Linux系统，记得使用cat命令即可。从上面日志中记录的查询信息来看，可以得知几个信息：

• 执行慢查询SQL的用户：root，登录IP为：localhost[127.0.0.1]。
• 慢查询执行的具体耗时为：0.014960s，锁等待时间为0s。
• 本次SQL执行后的结果集为4行数据，累计扫描6行数据。
• 本次慢查询发生在db_zhuzi这个库中，发生时间为1667466932（2022-11-03 17:15:32）。
• 最后一行为具体的慢查询SQL语句。

3.2、排查SQL执行缓慢问题

读取慢查询日志后，能够让咱们精准定位到发生慢查询SQL的用户、客户端机器、执行耗时、锁阻塞耗时、结果集行数、扫描行数、发生的库和时间、以及具体的慢查询SQL语句，得到了这些信息后，其实排查引起慢查询的原因就比较简单了。步骤如下：

• 先根据本地慢查询日志文件中的记录，得到具体慢查询SQL执行的相关信息。
• 查看Look_time的耗时，判断本次执行缓慢，是不是由于并发事务导致的长时间阻塞。
• 如果不是，则通过explain索引分析工具，先判断索引的使用情况。

一般来说在开发环境中没有问题的SQL语句，放到线上环境出现执行缓慢的情况，多半原因是由于并发事务抢占锁，造成当前事务长时间无法获取锁资源，因此导致当前事务执行的SQL出现超时，这种情况下需要去定位操作相同行数据的大事务，一般长时间的阻塞是由于大事务持有锁导致的，找出对应的大事务并拆解或优化掉即可。

通过show status like 'innodb_row_lock_%';命令可以查询MySQL整体的锁状态，如下：

锁性能表现

• Innodb_row_lock_current_waits：当前正在阻塞等待锁的事务数量。
• Innodb_row_lock_time：MySQL启动到现在，所有事务总共阻塞等待的总时长。
• Innodb_row_lock_time_avg：平均每次事务阻塞等待锁时，其平均阻塞时长。
• Innodb_row_lock_time_max：MySQL启动至今，最长的一次阻塞时间。
• Innodb_row_lock_waits：MySQL启动到现在，所有事务总共阻塞等待的总次数。

如果你在慢查询日志中，看到了大量由于锁阻塞导致执行超出慢查询阈值的SQL，那可以执行上述这条指令看看整个MySQL的锁状态，如果这些值都比较大时，就意味着你当前这个MySQL节点承载的并发压力过高，此时就急需进行《MySQL架构优化》。

但如果慢查询不是因为锁阻塞导致的，那此时又该如何处理呢？不是锁阻塞导致的，那绝对是SQL执行时本身出现了问题，这时可以先用explain工具分析SQL的执行计划，查看索引的使用情况，找到那些执行计划中扫描行数过多、type=index、all的SQL语句，尝试优化掉即可（但是要注意：如果是8.0以下版本的MySQL，在使用explain工具分析前，一定要记得设置SQL_NO_CACHE，否则会从查询缓存中读取数据）。

不过explain工具在分析复杂SQL时，生成的执行计划可能会比较多，如果你对该工具用的比较熟悉，那可以直接分析生成的执行计划，但这种方式略微会有些难以观测，最好的办法则是之前提到的人肉排查大法。

对于一些较为复杂或庞大的业务需求，可以采取拆分法去逐步实现，最后组装所有的子语句，最终推导出符合业务需求的SQL语句。

所谓的人.肉排查法，也就是将一条复杂的查询语句，拆解成一条条的子语句，毕竟咱们除开要学会拆解需求外，还得掌握拆解复杂SQL的能力，拆解后可以对每条子语句使用explain工具分析，这样就能够精准定位到：复杂语句中导致耗时较长的具体子语句，最后将这条子语句优化后重新组装即可。

拆解排除法还有一个最大的好处是：有时组成复杂SQL的每条子语句都不存在问题，也就是每条子语句的执行效率都挺不错的，但是拼到一起之后就会出现执行缓慢的现象，这时拆解后就可以一步步的将每条子语句组装回去，每组装一条子语句都可以用explain工具分析一次，这样也能够精准定位到是由于那条子语句组合之后导致执行缓慢的，然后进行对应优化即可。

按照上述的方法论，基本上能够让你解除掉所有的慢查询问题，但在之前的SQL优化篇也说到过：如果是由于业务本身就会导致SQL检索数据量较大，那这种情况是无法进行优化的，此时就只能从业务层面着手解决。

最后再说一下为什么这种方法被称之为人.肉排查大法，这个名字是由战争时期的一个场景演变过来的，战争时期基本上都会有地雷战，也就是守的一方会布置地雷去阻碍攻方的进程，如果攻方停下来让地雷兵用仪器探测，然后拆除掉地雷，这个过程会十分耗时，如果这样做就会让守方达成拖延进程的目的。

这种情况下攻方为了进程不被耽误，通常会选择让人直接飞速的跑过去，也就是靠人直接踩雷的行为去引爆地雷，从而做到排雷的效果，以便于后方大部队的进程不会收到影响，而这个方法则被称为人.肉排雷法。

同样的思想换到编程中也相同，目前出现了问题，但是由于组成SQL的子语句过多，无法精准定位具体的问题出现原因，则会挨个拆解后分析，最终得出引出问题的SQL语句，这个思想在解决SQL执行报错时也额外好用，毕竟如果无法通过外力去排查问题时，这种方式就成了上上策，虽然看起来很傻，但却非常非常实用。也包括在Java中也可以去通过System.out.println("...");不断输出的方式排查问题，其思想与之相同。

四、MySQL线上机器故障排查

MySQL数据库线上的机器故障主要分为两方面，一方面是由于MySQL自身引起的问题，比如连接异常、死锁问题等，另一方面则是部署MySQL的服务器硬件文件，如磁盘、CPU100%等现象，对于不同的故障问题排查手段也不同，下面将展开聊一聊常见的线上故障及解决方案。

4.1、客户端连接异常

客户端连接异常也是一种较为常见的故障，这里有可能是因为多方面原因导致的，如下：

• ①数据库总体的现有连接数，超出了MySQL中的最大连接数，此时再出现新连接时会出异常。
• ②客户端数据库连接池与MySQL版本不匹配，或超时时间过小，也可能导致出现连接中断。
• ③MySQL、Java程序所部署的机器不位于同一个网段，两台机器之间网络存在通信故障。
• ④部署MySQL的机器资源被耗尽，如CPU、硬盘过高，导致MySQL没有资源分配给新连接。

当数据库出现连接异常时，基本上就是因为上述四种原因导致的，对于第一二种情况比较简单，基本上问题出在数据库和客户端连接池的配置上面，首先排查一下两者的参数，然后适当调整参数即可。

• ③MySQL、Java程序所部署的机器不位于同一个网段，两台机器之间网络存在通信故障。

这种情况，问题一般都出在交换机上面，由于Java程序和数据库两者不在同一个网段，所以相互之间通信需要利用交换机来完成，但默认情况下，交换机和防火墙一般会认为时间超过3~5分钟的连接是不正常的，因此就会中断相应的连接，而有些低版本的数据库连接池，如Druid只会在获取连接时检测连接是否有效，此时就会出现一个问题：

交换机把两个网段之间的长连接kill了，但是Druid因为只在最开始检测了一次，后续不会继续检测连接是否有效，所以会认为获取连接后是一直有效的，最终就导致了数据库连接出现异常（后续高版本的Druid修复了该问题，可以配置间隔一段时间检测一次连接）。

一般如果是由于网络导致出现连接异常，通常排查方向如下：

• 检测防火墙与安全组的端口是否开放，或与外网机器是否做了端口映射。
• 检查部署MySQL的服务器白名单，以及登录的用户IP限制，可能是IP不在白名单范围内。
• 如果整个系统各节点部署的网段不同，检查各网段之间交换机的连接超时时间是多少。
• 检查不同网段之间的网络带宽大小，以及具体的带宽使用情况，有时因带宽占满也会出现问题。
• 如果用了MyCat、MySQL-Proxy这类代理中间件，记得检查中间件的白名单、超时时间配置。

一般来说上述各方面都不存在问题，基本上连接异常应该不是由于网络导致的问题，要做更为细致的排查，可以在请求链路的各节点上，使用网络抓包工具，抓取对应的网络包，看看网络包是否能够抵达每个节点，如果每个节点的出入站都正常，此时就可以排除掉网络方面的原因。

• ④部署MySQL的机器资源被耗尽，如CPU、硬盘过高，导致MySQL没有资源分配给新连接。

这种情况更为特殊，网络正常、连接数未满、连接未超时、数据库和客户端连接池配置正常....，在一切正常的情况下，有时候照样出现连接不上MySQL的情况咋整呢？在这种情况下基本上会陷入僵局，这时你可以去查一下部署MySQL服务的机器，其硬件的使用情况，如CPU、内存、磁盘等，如果其中一项达到了100%，这时就能够确定问题了。

因为数据库连接的本质，在MySQL内部是一条条的工作线程，要牢记的一点是：操作系统在创建一条线程时，都需要为其分配相关的资源，如果一个客户端尝试与数据库建立新的连接时，此刻正好有一个数据库连接在执行某个操作，导致CPU被打满，这时就会由于没有资源来创建新的线程，因此会向客户端直接返回连接异常的信息。

如果出现这样的问题，就需要先找到导致资源耗尽的连接/线程，然后找到它当时正在执行的SQL语句，最后需要优化相应的SQL语句后才能彻底根治问题。

4.2、MySQL死锁频发

对于死锁问题，MySQL内部其实会默认开启死锁检测算法，当运行期间出现死锁问题时，会主动介入并解除死锁，但要记住：虽然数据库能够主动介入解除死锁问题，但这种方法治标不治本！为啥治标不治本呢？因为死锁现象是由于业务不合理造成的，能出现一次死锁问题，自然后续也可能会多次出现，因此优化业务才是最好的选择，这样才能根治死锁问题。

从业务上解决死锁问题，首先咱们得先定准定位到产生死锁的SQL语句，对于这点需要在MySQL内部会有一个日志，来记录着它自身捕获到的死锁，可以通过如下命令查看：

• SHOW ENGINE INNODB STATUS\G;：查看InnoDB存储引擎的运行状态日志。

当出现死锁时，MySQL会将死锁对应的信息记录到该日志中，但这个日志会记录着InnoDB运行期间的所有状态日志，因此输入之后，要先找到LATEST DETECTED DEADLOCK这块区域的日志：

死锁日志

当咱们手动模拟生产一个死锁后，在InnoDB的日志中就能够找到相应的死锁日志，这里为了方便观察则只列出了死锁的日志，其实InnoDB的运行时日志内容包含很多方面。

在上面的日志中，基本上已经写的很清楚了，在2022-11-04 23:04:34这个时间点上，检测到了一个死锁出现，该死锁主要由两个事务产生，SQL如下：

• (1)：UPDATE zz_account SET balance = balance + 888 WHERE user_name = "熊猫";
• (2)：UPDATE zz_account SET balance = balance + 666 WHERE user_name = "竹子";

在事务信息除开列出了导致死锁的SQL语句外，还给出了两个事务对应的线程ID、登录的用户和IP、事务的存活时间与系统线程ID、持有的锁信息与等待的锁信息.....。

除开两个发生死锁的事务信息外，倒数第二段落还给出了两个事务在哪个锁上产生了冲突，以上述日志为例，发生死锁冲突的地点位于db_zhuzi库中zz_account表的主键上，两个事务都在尝试获取对方持有的X排他锁，后面还给出了具体的页位置、内存地址....。

最后一条信息中，给出了MySQL介入解除死锁的方案，也就是回滚了事务(2)的操作，强制结束了事务(2)并释放了其持有的锁资源，从而能够让事务(1)继续运行。。

经过查看上述日志后，其实MySQL已经为我们记录了产生死锁的事务、线程、SQL、时间、地点等各类信息，因此想要彻底解决死锁问题的方案也很简单了，根据日志中给出的信息，去找到执行相应SQL的业务和库表，优化SQL语句的执行顺序，或SQL的执行逻辑，从而避免死锁产生即可。

最后要注意：如果是一些偶发类的死锁问题，也就是很少出现的死锁现象，其实不解决也行，毕竟只有在一些特殊场景下才有可能触发，重点是要关注死锁日志中那些频繁出现的死锁问题，也就是多次死锁时，每次死锁出现的库、表、字段都相同，这种情况时需要额外重视并着手解决。

4.3、服务器CPU100%

对于CPU100%甚至更高的问题，其实排查起来也比较简单，排查的思路其实很简单：

• 先找到CPU过高的服务器。
• 然后在其中定位到具体的进程。
• 再定位到进程中具体的线程。
• 再查看线程正在执行的代码逻辑。
• 最后从代码层面着手优化掉即可。

上述这个工作具体该如何完成呢？下面一起来实操一下。

首先通过top指令查看系统后台的进程状态：

[root@localhost ~]# top
top -14:09:20 up 2 days,16 min,3 users,  load average:0.45,0.15,0.11
Tasks:98 total,1 running,97 sleeping,0 stopped,0 zombie
%Cpu(s):100.0 us,  0.0 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiBMem:997956 total,286560 free,126120 used,585276 buff/cache
KiBSwap:2097148 total,2096372 free,776 used.626532 avail Mem

   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
76661 root      20022494322570811592 S 99.92.60:28.32 mysql
636 root      20029893661884836 S  0.30.63:39.52 vmtoolsd
1 root      2004603259563492 S  0.00.60:04.27 systemd
2 root      200000 S  0.00.00:00.07 kthreadd
3 root      200000 S  0.00.00:04.21 ksoftirqd/0
5 root       0-20000 S  0.00.00:00.00 kworker/0:0H
7 root      rt   0000 S  0.00.00:00.00 migration/0
8 root      200000 S  0.00.00:00.00 rcu_bh
9 root      200000 S  0.00.00:11.97 rcu_sched
     .......

从如上结果中不难发现，PID为76661的MySQL进程对CPU的占用率达到99.9%，此时就可以确定，机器的CPU利用率飙升是由于该进程引起的。

此时可以再通过top -Hp [PID]命令查看该进程中CPU占用率最高的线程：

[root@localhost ~]# top -Hp76661
.....省略系统资源相关的信息......
   PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
77935 root      20022494322649611560 R 99.92.73:43.95 mysql
77915 root      20022494322649611560 S  0.02.70:00.00 mysql
77916 root      20022494322649611560 S  0.02.70:00.08 mysql
77917 root      20022494322649611560 S  0.02.70:00.00 mysql
77918 root      20022494322649611560 S  0.02.70:00.00 mysql
77919 root      20022494322649611560 S  0.02.70:00.00 mysql
 .......

从top -Hp 76661命令的执行结果中可以看出：其他线程均为休眠状态，并未持有CPU资源，而PID为77935的线程对CPU资源的占用率却高达99.9%！

到此时，导致CPU利用率飙升的“罪魁祸首”已经浮现水面，但此时问题来了！在如果这里是Java程序，此时可以先将该线程的PID转换为16进制的值，然后进一步排查日志信息来确定具体线程执行的业务方法。但此时这里是MySQL程序，咱们得到了操作系统层面的线程ID后，如何根据这个ID在MySQL中找到对应的线程呢？

4.3.1、查看OS线程ID与MySQL线程ID关系（MySQL5.7及以上）

在MySQL5.7及以上的版本中，MySQL会自带一个名为performance_schema的库，在其中有一张名为threads的表，其中表中有一个thread_os_id字段，其中会保存每个连接/工作线程与操作系统线程之间的关系（在5.7以下的版本是隐式的，存在于MySQL内部无法查看）。

-- 先连接MySQL数据库
mysql -uroot -p
Enter password: ***

-- 进入 performance_schema 库
use performance_schema;

-- 查询 threads 表（竖排输出）
SELECT * FROM threads\G;

执行上述命令后，会输出所有已创建的线程，查询之后的最终结果如下：

MySQL~OS关系

从上述中可以明显看出MySQL线程和OS线程之间的关系，当通过前面的top指令拿到CPU利用率最高的线程ID后，在再这里找到与之对应的MySQL线程，同时也能够看到此线程正在执行的SQL语句，最后优化对应SQL语句的逻辑即可。

4.3.2、查看OS线程ID与MySQL线程ID关系（MySQL5.6及以下）

上面讲了MySQL5.7及以上版本的排查方式，但有些小伙伴可能会存在困扰：我的MySQL如果是MySQL5.7版本以下的呢？其实也不是不能排查，只是有些许麻烦！下面一起来过一过~

还记得之前排查死锁时用到的SHOW ENGINE INNODB STATUS\G;命令嘛？执行之后可以查看InnoDB引擎的运行时日志，在里面有一个TRANSACTIONS板块，统计着所有存活事务的信息，此时也可以从中得到相应的OS线程、MySQL线程的映射关系，如下：

MySQL-OS线程

但是这种方式仅能够获取到OS线程、MySQL线程之间的映射关系，无法获取到对应线程/连接正在执行的SQL语句，此时如果线程还在运行，则可以通过show processlist;查询，如下：

查询执行SQL

但这种方式只能看到正在执行的SQL语句，无法查询到最近执行过的语句，所以这种方式仅适用于：线上SQL还在继续跑的情况，如果SQL已经执行结果，其实CPU的占用率也会下降，这类偶发性的CPU占用过高也无需排查，我们要优化的是那些一直导致CPU过高的SQL语句。

同时再补充一句，SHOW ENGINE INNODB STATUS;命令只能显示1MB的数据，如果想要查看完整的InnoDB运行期日志，可以找到innodb_status.[pid]日志文件，MySQL会每隔15s就刷写一次InnoDB的运行日志进去，其中可以查看到完整的运行时日志。

4.3、CPU占用率过高排查总结

其实排查这类问题不难，主要是得先定位到对应的线程，然后再找出OS线程对应的MySQL线程，找到对应的MySQL线程后，再找到其正在执行的SQL语句，因为线程是执行这条语句才导致占用高额CPU资源的，因此最后只需要把SQL的逻辑调整合理后，就能够彻底解决CPU占用过高的问题。

4.4、MySQL磁盘100%

所谓的磁盘100%不是指磁盘空间被用光，而是指磁盘IO达到100%利用率，这种情况下一般会导致其他读写操作都被阻塞，因为操作系统中的IO总线会被占满，无法让给其他线程来读写数据，先来总结一下出现磁盘IO占用过高的原因：

• ①突然大批量变更库中数据，需要执行大量写入操作，如主从数据同步时就会出现这个问题。
• ②MySQL处理的整体并发过高，磁盘I/O频率跟不上，比如是机械硬盘材质，读写速率过慢。
• ③内存中的BufferPool缓冲池过小，大量读写操作需要落入磁盘处理，导致磁盘利用率过高。
• ④频繁创建和销毁临时表，导致内存无法存储临时表数据，因而转到磁盘存储，导致磁盘飙升。
• ⑤执行某些SQL时从磁盘加载海量数据，如超12张表的联查，并每张表数据较大，最终导致IO打满。
• ⑥日志刷盘频率过高，其实这条是①、②的附带情况，毕竟日志的刷盘频率，跟整体并发直接挂钩。

一般情况下，磁盘IO利用率居高不下，甚至超过100%，基本上是由于上述几个原因造成的，当需要排查磁盘IO占用率过高的问题时，可以先通过iotop工具找到磁盘IO开销最大的线程，然后利用pstack工具查看其堆栈信息，从堆栈信息来判断具体是啥原因导致的，如果是并发过高，则需要优化整体架构。如果是执行SQL加载数据过大，需要优化SQL语句......

磁盘利用率过高的问题其实也比较好解决，方案如下：

• ①如果磁盘不是SSD材质，请先将磁盘升级成固态硬盘，MySQL对SSD硬盘做了特殊优化。
• ②在项目中记得引入Redis降低读压力，引入MQ对写操作做流量削峰。
• ③调大内存中BufferPool缓冲池的大小，最好设置成机器内存的70~75%左右。
• ④撰写SQL语句时尽量减少多张大表联查，不要频繁的使用和销毁临时表。

基本上把上述工作都做好后，线上也不会出现磁盘IO占用过高的问题。

五、MySQL线上排查篇总结

相对来说，MySQL中除开Memory引擎外，其他引擎都是基于磁盘的，虽然InnoDB将内存开发到了极致，但最终还是基于磁盘的，InnoDB对内存的占用最大不会超出BufferPool缓冲池的大小，因此MySQL自然也不存在所谓的OOM内存溢出、内存占用过高等问题。

MySQL 之表分区

分库分表相信大家都听说过，但(partitioning)表分区这个概念却鲜为人知，MySQL在5.1版本中开始支持了表分区技术，同时在MySQL5.5中进行了优化，自从MySQL支持的绝大部分引擎都开启了表分区功能。

MySQL 海量数据分库分表

不要为了分库分表而分库分表。引入SOA架构中的一句话：架构不是一蹶而起的，而是慢慢演进的。只有真正需要分库分表来解决问题时，才去真正的做拆分，否则会导致很多不必要的麻烦产生。

阿里开发手册

一、为什么需要分库分表？

编程里面有句话叫做：加一台服务器的收益胜过千万次调优，毕竟机器数量才是真理。

1.1、请求数太高

在高并发情况下，大量读写请求落入数据库处理，最终会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service层来看就是，可用数据库连接锐减甚至无连接可用，接下来面临的就是并发量急剧增加、吞吐量严重下降、连接出现异常、数据库时常宕机、系统经常崩溃一系列后患问题。

1.2、数据查询慢

• 一、单表或单库数据量过大，导致数据检索的效率直线降低。
• 二、单库整体并发连接数接近系统阈值，从而导致此请求获取不到连接数，一直处于等待获取连接的状态。
• 三、已经获取但由于并发过高导致CPU被打满，就算SQL所查询的表数据行很少，也同样因为没有CPU资源无法执行，所以一直处于阻塞状态，最终出现查询过慢的现象。

1.3、数据量太大

• ①当一个库的数据存储量太大时，就算每张表的并发数不多，但是因为是海量数据，单库中存在大量的数据表，每张表都有一部分并发请求，导致最终单库的连接数阈值成为数据库的瓶颈。
• ②当一张表数据太多时，导致单表查询速度严重下降，虽然InnoDB存储引擎的表允许的最大行数为10亿，但是如果一张表的数据行记录达到上亿级别，那就算通过索引去查询一条数据，它也需要至少经过上十次到几十次磁盘IO，从而导致单表查询速度直线下降；一般一张表的数据行数在800~1200W左右最合适。

1.4、单体架构的通病

单库中某张表遇到问题需要修复时，会影响了整个库中所有数据，因为有些严重的情况下需要停机优化后重新上线，这时其它一些没有出现问题的表，也会因此受到影响。

1.5、MySQL数据库瓶颈

上面聊到的各类问题，本质上都是一些数据库瓶颈，一般程序的性能瓶颈都源自于硬件问题，而问题归根到底都属于IO、CPU瓶颈。

2.1、IO瓶颈

IO瓶颈主要分为两方面，一方面是磁盘IO瓶颈，另一方面则是网络IO瓶颈，具体如下：

磁盘IO瓶颈

①MySQL为了提升读写性能，通常都会将一些经常使用的热点数据放入缓冲区，避免每次读写请求都走磁盘IO的方式去操作数据，但当整库数据数据太多时，可能会出现大量的热点数据，此时内存缓冲区中又无法全部放下，因此会导致大量的读写请求产生磁盘IO，通过读写磁盘的方式去完成数据读写，从而导致查询速度下降。

②一次查询数据的过程中，由于涉及到的数据过多，导致无法全部在内存中完成数据检索，如分组、排序、关联查询等场景，内存中相应的缓冲区无法载入要操作的全部数据，因此只能通过分批的方式处理数据，此时又需要经过大量磁盘IO后才得到最终数据集。

这种情况在单表查询时也存在，如果单表中字段过多，导致每一行数据的体积都比较大，因此会超出MySQL磁盘IO每次读取16KB的这个限制，因而也会出现检索单表数据时，一条数据就需要经过多次IO才能拿完。

简单来说磁盘IO瓶颈有两种情况，一种是磁盘IO次数过多，导致IO利用率持续居高不下，另一种情况就是每次读取数据都超出单次IO的最大限制，因此会引发多次IO，从而又演变成第一种情况。

网络IO瓶颈

当一个请求的生成的SQL语句执行后，由于这条语句得到的结果集数据太多，从而会导致相应时的数据包体积过大，这时如果网络带宽不够，就会出现传输过慢的问题，因为底层需要对大的数据包做拆包，然后分批返回，这时也会阻塞其它读写数据的请求，网络带宽就会成为新的瓶颈。

其实网络IO瓶颈的情况也比较好理解，网络带宽就好比一条马路，由于一条SQL返回的结果集过大，因此装载数据包的卡车远远超出了马路的宽度，这时就需要将数据拆分到多辆能通行的小卡车装载，但虽然这样做能够让数据包“变窄”成功返回，但因此也会造成这个大数据包会变长，从而占满整个带宽通道，因此其它网络数据包需要等这个大包传输完成后，才能继续通行。

2.2、CPU瓶颈

上面简单的了解IO瓶颈后，再来看看机器本身的CPU瓶颈，CPU瓶颈也会分为两种，一种是运算密集型瓶颈，另一种则是阻塞密集型瓶颈。

CPU工作原理

线程是操作系统最小的执行单元，因此CPU工作时本质上就是以线程作为载体，然后执行线程任务中的一条条指令，一般情况下单核CPU在同一时刻只能够支撑单挑线程运行，但随着2002年超线程技术的发布后，如今的CPU通常具备两组ALU执行单元，也就是大家常听到的单核双线程、四核八线程。

因为超线程技术的存在，所以一核CPU在同一时刻可支撑两条线程一起运行，这种结构的CPU在现在也被称为大核架构，即一核心具备两逻辑处理器。而传统的单核单线程的CPU则被称为小核架构，目前最新的12/13代CPU还有大小核异构的架构。

但无论是小核、大核、大小核架构的CPU，本质上在机器运行期间，往往整个系统内所有程序的线程数，加起来之后都会远超于CPU核数，如下：

CPU核数与线程数

那在这种情况下是如何工作的呢？相信有相关知识储备的小伙伴第一时间就会想到一个词汇：时间片切换执行，也就是有限的CPU核心会在所有线程之前来回切换，以此来确保系统和程序的正常运转。

其中的原理简单理解起来也并不难，因为每条线程的指令在执行时都需要数据作为基础，因此CPU在执行某条指令后，执行新指令时需要加载数据，此时会去载入数据，而操作系统这时就会将CPU资源切换给其它的线程执行，等这条线程的数据准备好了再切换回来。

运算密集型瓶颈

用户请求生成的SQL语句中包含大量join联表查询、group by分组查询、order by排序等之类的聚合操作，同时这些操作要基于特别大的数据集做运算，导致执行时消耗大量CPU资源，CPU占用率直达100%+，因此无法再给其它线程提供执行所需的CPU资源。

阻塞密集型瓶颈

一张或多张表的数据量特别大，此时基于这些大表做数据检索时，需要扫描的数据行太多，虽然这些SQL语句不会大量消耗CPU资源，但由于数据量过大，会导致长时间占用CPU资源，从而造成其它线程无法获取CPU资源执行。

上面聊到的两种CPU瓶颈中，一种属于大量运算导致CPU资源耗尽，一种属于大表检索导致长时间占用CPU资源，两种情况都会导致CPU遇到瓶颈，从而无法给其它线程提供运行所需的资源。有人也许会说，似乎这是SQL不合理导致的呀？其实不然，因为往往很多正常的SQL也会出现大量消耗CPU、或检索大表数据长时间占用CPU的情况。

无论是IO瓶颈，还是CPU瓶颈，都可以通过升级硬件配置的方式来解决，比如升级磁盘材质、加大网络带宽、增多CPU核数等，但前面讲到过，这种方式面对高并发大流量的冲击，治标不治本。如果客户端流量过大，这时不应该再试图寻找更强壮的马来代替，而是应该选择多匹马的方案来解决。

其实这也是一种节省成本的做法，无限制升级硬件也不是长久之道，而且越到后期，配置越高的硬件成本越高，四颗八核十六线程的CPU，成本价可能还会比一颗32Core 64Thread的CPU要便宜。

1.6、再聊为何需要分库分表

其实不管是并发过高、或访问变慢、亦或数据量过大，本质上都属于数据库遭遇到了瓶颈，但只不过根据情况不同，分为不同类型的数据库瓶颈，但是最终对于客户端而言，就是数据库不可用了或者变慢了。

而导致数据库出现此类问题的原因，实则就是随着业务的发展，系统的数据不断增多、用户量不断增长、并发量不断变大，因此对于数据再进行CRUD操作的开销也会越来越大，再加上物理服务器的CPU、磁盘、内存、IO等资源有限，最终也会限制数据库所能承载的最大数据量、数据处理能力。

当出现上述这类问题，并且无法通过升级硬件、版本、调优等手段解决时，或者只能临时解决，却无法保障未来业务增长的可用性时，此刻就需要合理的设计数据库架构来满足不断增长的业务，这就是分库分表诞生的初衷，目的就是为了避免单库由于压力过高，导致出现之前所说的一系列问题，合理的设计架构能最大限度上提高数据库的整体吞吐量。

二、传统单库架构到分库分表的演进史

早些年我司新开一条业务线切入金融领域，最开始的因为担心风险过大，所以并未投入太多的成本，处于一个试错阶段，最初就把所有业务都怼入一个war包，所有业务共享一个库资源，结构大致如下：

单库架构

而在当时那段时间，金融领域快速发展，慢慢的，Java搭建的金融核心系统开始出现响应变慢，甚至时不时宕机，部署整个金融核心系统的单台Tomcat很快遇到了瓶颈，后来实在因为Tomcat三天俩头宕机重启，迫于无奈开始了业务架构的改进，如下：

应用拆分

因为当时考虑到业务发展速度，并没有使用Nginx对Tomcat进行横向拓展做水平集群，因为如果仅仅只是通过Nginx来做，可能以后还是需要对架构进行升级，进一步按业务拆分成分布式系统，所以经讨论后一致决定直接引入分布式架构对系统进行改造。

经过改造后的业务架构，Java应用这边的确可以抗住每天的流量，但当时因为在做Java程序架构升级的时候，只引入了Redis、MQ降低数据库并发，并没有去对数据库做太多的拓展，因此当时还是所有业务共享一个库。

随着时间的慢慢推移，虽然用MQ、Redis做了流量的削峰，但是也挡不住当时的流量请求，做过金融业务的小伙伴应该清楚，它不像其他业务领域中读多写少，金融业务中读多写也多，同时还需要每日对账、跑批、统计报表.....，因此对数据库的读写操作相当多，而金融业务又要求数据实时性，所以很多操作无法走MQ异步完成，也不能放入Redis做数据缓存，Why？

好比拿股市中最基本的买入卖出为例，原本客户看到的是5$一股，然后用户选择了买入，因为数据放在缓存里没有及时更新，结果最新价格成了10$一股，此时用户买入10000$，按用户的预估应该是会买入2000股左右，结果最终买成了1000股，平白无故导致用户追涨。
也包括大量的写操作也无法走MQ异步完成，比如用户以5$的成本价买入，现在看到了最新的价格为10$一股，然后选择了全仓卖出，这时你将卖出操作发给了MQ异步执行，结果MQ中的卖出消息并未立马被消费，而是到了一小时后价格降到了2.5$一股时，才真正被消费，这回导致原本用户能赚100点，最后反变为倒亏50点。

虽然当时手上的项目并非交易所类型的金融业务，但无论是哪类金融业务，基本上对数据的实时性要求特别高，所以MQ、Redis基本上只能分担很小一部分的流量，其它大部分的流量依旧会需要落库处理。也正因如此，最终数据库成了整个系统的瓶颈口，为了去解决这个问题，最终选用服务独享库的方案进行升级（也就是后续要说的垂直分库模式），如下：

业务分库

而数据库这边经过拆分之后，相较于之前的单库架构，整个数据库系统的稳定性和可用性明显得到改善，但由于某些库是经常需要被访问到的（资金库、信审库、后台库），所以这些核心库以单节点方式去承载流量还是显得有点吃力（吞吐量下降、响应速度变慢），最终又对核心业务库进行横向扩容，架构如下：

横向集群

最终，根据服务不同的业务规模，拆成了规模不同、业务不同的库，但是这其中的拆分规则到底是什么呢？以及拆分的依据又是啥？

三、分库分表正确的拆分手段

现在你的手里有一个西瓜，吃的时候切法有两种，一种是以垂直方向竖切，另一种是以水平方向横切，如下：

横/竖切瓜

这种切割方式在分库分表中也存在，分库分表的拆分规则也可分为：水平、垂直 两个维度。

但水平、垂直该怎么拆？什么场景下拆？拆完会出现的问题又该怎么去解决呢？

注意：分库、分表是两个概念，两者并不是同一个名词，所以这里需要牢记！按拆分的粒度来排序，共计可分为四种方案：垂直分表、水平分表、垂直分库、水平分库。

3.1、不同场景下的分表方案

分表大多是在单表字段过多或数据过多的场景下，会选择的一种优化方案，当一个表字段过多时，应当考虑垂直分表方案，将多余的字段拆分到不同的表中存储。当一个表的数据过多时，或者数据增长速率过快时，应当考虑通过水平分表方案，来降低单表的数据行数。

3.1.1、垂直分表：结构不同，数据不同（表级别）

当一张表由于字段过多时，会导致表中每行数据的体积变大，而之前不仅一次聊到过：单行数据过大带来的后患，一方面会导致磁盘IO次数增多，影响数据的读写效率；同时另一方面结果集响应时还会占用大量网络带宽，影响数据的传输效率；再从内存维度来看，单行数据越大，缓冲区中能放下的热点数据页会越少，当读写操作无法在内存中定位到相应的数据页，从而又会产生大量的磁盘IO。

从上述的几点原因可明显感受到，当单表的字段数量过多时，会导致数据检索效率变低、网络响应速度变慢、数据库吞吐量下降等问题，面对于这种场景时，就可以考虑垂直分表。

例：现在有一张表，总共43个字段，但是对于程序来说，一般经常使用的字段不过其中的十余个，而这些经常使用的字段则被称之为热点字段，假设此时这张表中的热点字段为18个，剩下的冷字段为25个，那么我们就可以根据冷热字段来对表进行拆分，如下：

垂直分表

对字段过多的表做了垂直拆分后，这时就能很好的控制表中单行数据的体积，从而能够让经常使用的字段数据更快的被访问、更快的返回。不过在做垂直拆分时，记得在冷字段的表中多加一个列，作为热字段表的外键映射，保证在需要用到冷数据时也能找到。

对于这种垂直分表的场景在很多业务中都有实现，如用户数据会分为users、user_infos，订单数据会分为order、order_info......。如果表结构是按照数据库三范式设计的，基本上也无需考虑做垂直分表。

经过垂直拆分后的两张或多张表，各自之间的表结构不同，并且各自存储的数据也不同，这是垂直分表后的特性，以上述例子来说，热点字段表会存储热数据，冷字段表会存储冷数据，两张表的拼接起来后会组成完整的数据。

3.1.2、水平分表：结构相同，数据不同（表级别）

前面聊到了字段过多对读写数据时的影响，接着再来看看数据过多时会导致的负面影响，虽然数据库中有索引机制，能够确保单表在海量数据的基础上，检索数据的效率依旧可观，但随着数据不断增长，当达到千万级别时，就会出现明显的查询效率下降的问题。

这里所谓的查询效率下降并非指单表的简单查询语句，而是指一些复杂的SQL语句，毕竟线上往往很多需求，都要经过复杂的SQL运算后才能得到数据，比如多张表联查再跟了一堆分组、排序、过滤、函数处理.....语句，这种情况下再基于这种大表查询，就算走了索引，效率也不会太高，因为其中要涉及到大量数据的处理，因此面对这种情况，就可以对表进行水平拆分。

例：现在有一张表，里面有三千万条数据记录，当基于该表去执行一条在索引上的复杂SQL时，也需要一定时间，至少会比1000万的数据表慢了好几倍，此时可以把这张3000W的表，拆为三张1000W的表，如下：

水平分表

对一张大表做了水平分表之后，咱们能够很好的控制单表的数据行数，3000W条数据的表和1000W条数据的表，查询速度其实不仅仅只是3倍的差距，数据过了千万级别时，数据量每向上增长一个量级，查询的开销也会呈直线性增长，因此做水平分表时，一般要求控制在500-1200W之间为一张表。

阿里内部的单表数据量大概控制在500~600W一张，因为这个数据量级，就算使用分布式策略生成的分布式ID作为主键，也能够很好的把索引树高控制在3~5以内，也就意味着最多三到五次磁盘IO就一定能得到数据，从而将单表的查询性能控制在最佳范围内。

水平拆分之后的两张或多张表，每张表的表、索引等结构完全相同，各表之间不同的地方在于数据，每张表中会存储不同范围的数据。不过拆分之后的水平表究竟会存储哪个范围的数据，这要根据水平分表的策略来决定，你可以按ID来以数据行分表，也可以按日期来以周、月、季、年.......分表。

3.1.3、分表方案总结

分表方案主要是针对于单表字段过多或数据过多的情况去做的，通过垂直、水平分表的手段，能够很好解决单表由于字段、数据量过多产生的一系列负面影响，但无论是垂直分表还是水平分表，都必须建立在单库压力不高，但是单表性能不够的情况下进行的，因为它们都属于库内分表。

如果是数据库整体压力都很大的情况，从而导致的查询效率低下，那不管再怎么做分表也无济于事，毕竟连流量入口都出现了拥塞，自然分表也无法解决问题，所以分表操作只建立在单库压力不高，但是单表查询效率低下的情况适用。

好比把数据库比作一个游乐园，而表则可以比作里面的一个个娱乐项目，由于某些娱乐项目比较火爆，因此可以对同一类型的项目多开几个，从而解决热点项目顾客要排队很久才能玩的问题。但如果是整个游乐园的人流量都非常大，每个项目都有大量顾客排队，这时再去对内部的娱乐项目作拓展，这种方式是行不通的，毕竟游乐园的大门就那么大，游客连进大门都要排队，再在内部作项目拓展显然无济于事。

3.2、不同场景下的分库方案

经过前面的分表总结后可以得知：如果是因为库级别的压力较大，这时就需要考虑分库方案，而不仅仅是分表方案，换到上面的例子中，当整个游乐园的人流量非常大时，应该考虑的是开分园，而并非是在内部作拓展。

分库和分表一样，也可以按垂直和水平两个维度来分，垂直分库本质上就是按业务分库，也就是现在分布式/微服务架构中，业务独享库的概念，而水平分库则是对同一个节点作横向拓展，也就是高可用集群的概念。

3.2.1、垂直分库：结构不同，数据不同（库级别）

当数据库使用单机的结构部署，在大流量/高并发情况下遇到瓶颈时，此时就可以考虑分库方案了，首先来聊聊垂直分库。

在项目开发过程中，一般为了方便团队分工合作和后续管理维护，通常都会对单个项目划分模块，按照业务属性的不同，会将一个大的项目拆分为不同的模块，同时每个业务模块也会在数据库中创建对应的表。

而所谓的垂直分库，就是根据业务属性的不同，将单库中具备同一业务属性的表，全部单独拧出来，放在一个单独的库中存储，也就按业务特性将大库拆分为多个业务功能单一的小库，每个小库只为对应的业务提供服务，这样能够让数据存储层的吞吐量呈几何倍增长。

例：以前面给出的金融项目来说，当单个库无法承载整个业务系统产生的流量压力时，比如此时单个数据库节点的QPS上限为2000，但业务高峰期抵达数据库的瞬时流量，造成了2W个并发请求，这时如果处理不当，数据库基本上会被这波瞬时流量打宕机。

对于前面所说的这种情况，就可以考虑根据业务属性拆分整个大库了，核心思想就是：既然单个节点扛不住，那就加机器用多个节点来抗，在客户端按照不同的业务属性，将过来的请求按照不同的业务特性做分流处理，如下：

垂直分库

原本之前单库时，无论是查询用户业务相关的SQL语句，还是放款/还款之类的SQL语句，不管三七二十一统统发往同一个数据库处理，全部都由这一个数据库节点提供数据支持，但按业务特性做了垂直分库后，用户相关的读写请求落入用户库，放款/还款之类的读写请求会落入资金库.....，这样就能很好的去应对单库面临的负载过高问题。

垂直分库后，每个库中存储的数据都不相同，因为是按照业务特性去将对应的表抽出去了组成新库，所以库结构也是不同的，用户库是由用户相关的表组成、信审库是由心生相关的表组成.......。

3.2.2、水平分库：结构相同，数据不同（库级别）

经过前面的垂直分库后，根据不同的业务类型，将访问压力分发到不同的库处理后，虽然在极大程度上提升了数据层的负荷能力，但如果某类业务的并发数依旧很高，比如经过前面的业务分流后，假设平台库需要承载5000的并发、信审库依旧需要承载1W的并发，这也远超出了单个数据库节点的处理瓶颈，最终可能还是会能把对应的数据库节点打宕机，所以此时可通过水平分库的方案，来提升某类业务库的抗并发吞吐量。如下：

水平分库

通过水平拆分的方案，能够根据压力的不同，分配不同的机器数量，从而使得不同库的抗压性都能满足对应的业务需求，这也就类似于分布式/微服务项目中，对单个服务做集群保证高可用的策略。

水平分库是基于一个节点，然后直接横向拓展，这也就意味着同一业务的数据库，各节点之间的库结构完全相同，但每个节点中的数据是否相同，这就要看你自己去决定了，一般情况下都是不同的，也就是不同节点的库会存储不同范围的数据。

3.2.3、另类的分库方案

前面聊清楚了分库分表中经典的垂直分库和水平分库方案，但除开这两种之外，还有一些另类的分库方案，也就是指一些数据库的高可用方案，例如主从复制、读写分离、双主热备等方案。

主从方案：一般会搭建读写分离，写请求发往主节点处理，读请求发往从节点处理，从节点会完全同步主节点的数据，从而实现读写请求分开处理的效果，能够再一定程度上提升数据存储层整体的并发处理能力。同时当主机挂掉时，从机也能够在很快的时间内替换成主机，以此确保数据层的高可用。

多主方案：一般是双主方案，两台数据库节点之间互为主从，相互同步各自的数据，两台节点中都具备完整的数据，读写请求可以发给任意节点处理。相较于前面的主从读写分离架构，这种双主双写架构的灾备能力更强，因为当其中某个节点宕机时，另一个节点可以完全接替对方的流量，不存在从机切换成主机的时间开销，因此能够保证数据100%不丢失。