MySQL高手实战

SpringBoot 底层如何去和 MySQL 交互了呢？

我们在写做 Java 项目时，一般都是引入 MyBatis 框架来和 MySQL 数据库交互，如果需要在 MySQL 上执行什么语句，只需要在 Mapper.xml 文件中定义对应的 SQL 语句即可

那么他底层到底是如何将定义在 Mapper.xml 文件中的 SQL 语句拿到 MySQL 中执行的呢？

其实 SpringBoot 项目底层就是通过和 MySQL 建立网络连接，之后通过这个连接来进行通信的

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

那么要建立和 MySQL 之间的连接，就要在项目中引入 MySQL 连接的 JDBC（Java Database Connectivity） 驱动，JDBC 就提供了 Java 程序和 MySQL 进行交互的接口

我们在 Java Web 刚入门的时候应该就学习了 JDBC，而在 MyBatis 中也是对 JDBC 进行了封装和抽象，提供了更丰富的功能给我们使用，先来看一下下边这张图，来详细说一下 SpringBoot 与 MySQL 如何进行交互：

用户发起一次 HTTP 请求的流程：

先从用户端说起，当用户对 SpringBoot 系统发起一个 HTTP 请求时，Tomcat 会创建一个线程来处理用户的请求，这个线程通过 JDBC 驱动来和数据库建立网络连接，将需要执行的 SQL 语句发送给 MySQL 数据库，MySQL 收到 SQL 之后执行语句，并将结果通过网络连接返回给 Tomcat 中的线程，最终再经过业务逻辑的处理，返回给用户

JDBC 驱动会为每一个线程都创建一个数据库连接吗？

上边说到了工作线程要和 MySQL 进行通信，就要建立一个数据库连接

那么假想一下如果对每一个线程都建立一个数据库连接，这里建立的连接是 TCP 连接，光建立连接就需要 3 次握手（3 次网络 IO），并且在使用完进行销毁，那么带来的性能开销是相当庞大的

基于这个问题，就引入了 数据库连接池，像我们常用的连接池就是 Druid 数据库连接池（阿里巴巴开源）

通过连接池，可以集中管理一批数据库连接，来供多个线程使用，使用完不销毁，再放回连接池可以进行复用，这样就省去了不断创建连接以及销毁连接的性能开销

在 Druid 连接池中维护了多个与 MySQL 的数据库连接，因此在 MySQL 中也是会有对应的连接池用于管理和外部的连接的，这里就省略不画了，只画了 Druid 中的连接池：

生产环境下数据库的硬件配置经验

对于大多数在校生来说，使用 MySQL 一般就是在 Linux 上安装一个 MySQL，对于生产环境中具体使用什么样的配置去安装 MySQL 以及什么配置对应可以抗下多少的并发请求不是太清楚，在本章会给出生产环境中数据库如何进行规划以及对于数据库的压测和监控如何实现的解决方案！

生产环境中数据库的配置

在生产环境中，如果需要去部署一个 MySQL 数据库，该选用什么样的机器配置呢？

这里会给出一个经验值，在以后真正需要线上部署时，可以根据系统并发量的大小来选择数据库所部署的配置

一般来说，Java 应用部署时，通常会选择 2 核 4 G 或者 4 核 8 G 的机器来进行部署

而数据库部署时，选用的配置相对来说要更高一些，通常选择 8 核 16 G 或者 16 核 32 G

上边说的部署配置是基于系统并发访问量比较大的情况，比如说 每秒有几百上千 的时候，需要比较高的配置，而像平常做一个简单的学习项目，没有什么并发量，数据量也不大，我们部署的目的是只要可以跑起来就行，所以一般是 1 核 2 G 或者 2 核 4 G 的机器就可以部署了

Java 应用生产配置经验

以大量的高并发线上系统的生产经验来看：

Java 应用部署在 4 核 8 G 的机器上，每秒抗下 500 左右的访问量一般来讲是没有问题的

为什么要说是一般来讲呢？

因为 Java 应用每秒可以抗下多少的并发请求，一方面是和部署的机器配置有关，另一方面还和 Java 应用本身相关

如果在 Java 应用中，一个接口要 1s 的时间才可以处理完毕，那么一台机器每秒也就能处理 100 - 200 个左右的请求

但是如果一个接口只需要 100 ms 就可以处理完毕，那么一台机器每秒处理几百个请求还是没有太大问题的

那么如果有一台 4 核 8 G 的机器部署 Java 应用，每个请求 0.01ms 就可以处理完毕，可以扛到每秒几千甚至上万的并发吗？

其实是不可以的，在实际环境中不能只看请求的处理时间，因为该请求可能还会进行磁盘 IO、网络 IO、内存占用、CPU 消耗，还要考虑这些因素带来的影响

在实际生产环境中 ，一台 4 核 8 G 的机器在每秒抗 500 的并发请求时，它的 CPU 负载就已经很高了，如果请求再多一些就可能导致机器宕机

如果 Java 应用比较吃内存的话，也会导致内存的占用率非常高，导致 JVM 频繁进行垃圾回收

所以基本上 500+ 的请求就已经达到了极限了，很难再提高并发请求的数量

数据库生产配置经验

在高并发场景中，我们要了解 Java 应用的性能瓶颈在哪些方面，才可以针对性能瓶颈进行优化！

对于 Java 应用来说，如果所有的操作都在内存中操作，这样情况下性能是非常高的

所以说 Java 应用的性能瓶颈一般不在于内存中的操作，而是比较耗时的 网络操作 ，Java 应用收到的每一个请求，都会再发送网络请求到数据库中，压力最终还是落在了数据库中

所以在整个 Java 应用中，性能瓶颈主要是 Java 应用的网络请求 和 数据库

而 Java 应用的所有请求最后都是落到了数据库中，而数据库还需要做许多的数据操作以及磁盘 IO，因此数据库的配置一般都要比 Java 应用更高

根据以往的生产经验来看，数据库的机器配置为 8 核 16 G 的话，每秒抗下 1000 - 2000 的请求是没有太大问题的，如果请求再多一些的话，整个数据库的 CPU、磁盘 IO、内存负载都会处于比较高的状态，可能会导致数据库的宕机

如果数据库的机器配置为 16 核 32 G 的话，每秒抗下 2000 - 4000 的请求是没有太大问题的

数据库硬盘的选择

并且数据库的磁盘 尽量采用 SSD 固态硬盘 而不是机械硬盘，数据库中是要做许多磁盘 IO 操作的，磁盘的 IO 速度对数据库的性能会产生比较大的影响

部署后的压测

在选好数据库的硬件配置之后，如果在比较规范化的公司中，会交给专业的 DBA 进行 MySQL 数据库的部署，DBA 就会根据以往的经验，使用 MySQL 生产调优的参数模板去部署 MySQL

可能还会对 Linux 中的一些 OS 参数进行调整，比如最大文件句柄的参数，通过调大最大文件句柄，可以让 Linux 接收更多的 TCP 连接，因为 Linux 中一切皆文件，但实际中不仅仅只简单地调大最大文件句柄就可以了（需要多个参数配合调整）

拿到 MySQL 数据库的第一步

在 MySQL 数据库部署好之后，先不要直接进行开发，而是先通过压测软件对数据库进行压测，观察数据库的 CPU、磁盘 IO、网络 IO、内存负载情况，判断数据库每秒可以处理多少的请求

那么通过对数据库进行压测就可以清楚数据库每秒处理任务的性能，心里有个底

这样在以后压测 Java 系统时，如果每秒并发量不理想，需要进行调优，就可以判断到底是 Java 系统出现了性能瓶颈还是数据库出现了性能瓶颈

因为有可能数据库每秒可以抗下 2000 个请求，但是 Java 应用每秒只可以抗下 500 的请求，如果事先没有对数据库进行压测，那你只知道 Java 应用每秒只可以处理 500 个请求，但是并不知道具体是 Java 应用的问题，还是数据库的问题！

数据库压测工具

这里推荐一个数据库压测工具：sysbench

通过 sysbench 可以在数据库中构建大量数据进行性能压测

Linux（CentOS） 安装命令：

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
# 如果看到了版本号证明安装成功
sysbench --version

sysbench 压测数据构造

使用以下命令基于 sysbench 对数据库进行压测：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

参数解释：

• db-driver：指定数据库引擎，例如 pgsql（PostgreSQL）或 mysql。
• time：测试持续时间。
• threads：并发线程数。
• report-interval：报告输出周期。
• mysql-host：数据库服务器的地址。
• mysql-port：数据库服务器的端口。
• mysql-user：数据库用户的用户名。
• mysql-password：数据库用户的密码。
• --mysql-db= test_db --tables=20 --table-size=1000000：这一行命令的意思是在 test_db 数据库里，构建 20 张测试表，每个测试表里 100 万条测试数据
• oltp_read_write：执行 oltp 数据库的读写测试
• --db-ps-mode=disable：禁止 ps 模式
• prepare：表示根据上边的命令设置去构造对应的数据，也就是创建 20 张测试表，每张测试表 100 万条数据

sysbench 压测命令

在进行压测的时候，命令最后的 prepare 变为了 run，表示运行压测

• 使用 oltp_read_write 模式测试数据库的综合读写 TPS

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run

• 使用 oltp_read_only 模式测试数据库的只读性能

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run

• 使用 oltp_delete 模式测试数据库的删除性能
• 使用 oltp_update_index 模式测试数据库的更新索引字段性能
• 使用 oltp_update_non_index 模式测试数据库的更新非索引字段性能
• 使用 oltp_insert 模式测试数据库的插入性能
• 使用 oltp_write_only 模式测试数据库的写入性能

通过指定不同模式来测试数据库不同操作的性能，只需要将命令中的 oltp_read_only 字段换成对应的模式即可！

• 清除压测数据：完成压测之后，可以清除数据

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

sysbench 压测结果分析

sysbench 每秒会输出如下的中间结果：

# thds 4：有 4 个线程在压测
# tps: 374，每秒执行了 374 个事务
# qps:7533，每秒执行了 7533 个请求
# r/w/o 表示 qps 中读、写、其他请求的占比
# lat：表示 95% 的请求延迟都在 17.95 ms 以内
# err、reconn：每秒错误、网络重连的次数
[ 1s ] thds: 4 tps: 374.23 qps: 7533.43 (r/w/o: 5280.08/977.98/1275.36) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00

最后压测完成后，会输出一个统计结果：

SQL statistics:
    queries performed:
        read: 49084 # 压测期间执行的读请求数量
        write: 9513 # 压测期间执行的写请求数量
        other: 11523 # 压测期间执行的其他请求数量
        total: 70120 # 压测期间执行的总请求数量
    transactions: 3506 (350.33 per sec.) # 执行的事务数量
    queries: 70120 (7006.63 per sec.) # 执行的请求数量
    ignored errors: 0 (0.00 per sec.)
    reconnects: 0 (0.00 per sec.)
General statistics:
    total time: 10.0062s # 10 s 的压测，执行了 3506 个事务
    total number of events: 3506
Latency (ms):
         min: 4.56 # 请求中，最小延迟 4.56
         avg: 11.41 # 平均延迟
         max: 39.24 # 最高延迟
         95th percentile: 19.65 # 95% 请求延迟都小于 19.65
         sum: 39997.58 
Threads fairness:
    events (avg/stddev): 876.5000/5.22
    execution time (avg/stddev): 9.9994/0.00

观察机器负载情况

在压测时，要观察机器的负载情况，通过不断增加压测的线程数去让数据库承载更高的 QPS，看数据库在极限情况下可以抗下多少的 QPS

接下来说一下压测中评估数据库 QPS 的标准：

比如说，在实际压测中，机器在抗下 2000 的 QPS 时，机器整个负载水平是比较高的，但是还能再增加少许请求，那么可以说这台数据库可以抗下 2000 的 QPS

但是如果机器在抗下 2000 的 QPS 时，机器的整个负载几乎都爆满了，再多一些请求打进来，就把机器给打垮了，那就不能说这台数据库可以抗下 2000 的 QPS 了

接下来说一下压测期间可以通过哪些命令来观察机器的负载情况：

• 观察机器 CPU、内存 负载

使用 top 命令来观察 CPU 负载，使用 top 命令会输出：

load average: 0.06, 0.03, 0.05 # 指标说明
# 三个值分别为：1分钟负载、5分钟负载、15分钟负载

对于不同核的 CPU 来说，load average 的含义也不同：

单核 CPU：

1、Load < 1，系统不繁忙

2、Load = 1，已经没有资源应对更多请求（所有资源都在被使用，也没有请求在阻塞）

3、Load > 1，已经有很多请求阻塞，等待处理

双核 CPU：

1、Load < 2，系统不繁忙

2、Load = 2，已经没有资源应对更多请求（所有资源都在被使用，也没有请求在阻塞）

那么如果是 4 核 CPU，Load < 4 表示系统不繁忙

并且通过 top 命令还可以查看内存的使用率，这个比较清晰，通过 Mem 这一行的数据就可以看出来

• 观察机器磁盘 IO 负载

1、通过 dstat -d 命令查看磁盘 IO

输出磁盘 IO 的吞吐量，每秒钟读取 以及 每秒钟写入的数据

普通的机械硬盘每秒钟可以读取上百 MB 的数据量

[root@localhost ~]# dstat -d
-dsk/total-
 read  writ
 879k   69k
   0     0 
   0     0 
   0  1557k^C

2、通过 dstat -r 命令查看系统 IO 情况

输出磁盘的 每秒随机读取次数 以及 每秒随机写入次数

磁盘的随机读写每秒在两三百次以内都是可以接受的

[root@localhost ~]# dstat -r
--io/total-
 read  writ
6.89  4.07 
   0     0 
   0     0 
   0  68.0 ^C

3、通过 dstat -n 查看网卡的流量情况

查看每秒钟网卡 接收的流量 以及 发送的流量 大小

如果机器使用千兆网卡，每秒网卡的总流量就在 100MB 左右

[root@localhost ~]# dstat -n
-net/total-
 recv  send
   0     0 
3327B  705B
1820B 1118B^C

生产环境数据库监控

在生产环境上数据库是一定要有监控的，包括 CPU 负载、内存负载、磁盘 IO 负载、网络带宽负载、QPS 等等一系列数据进行监控

一般生产环境中比较简单通用的可视化监控平台是基于 Prometheus + Grafana 来搭建的

通过 Prometheus 进行数据的采集和存储，通过监控组件（mysql_exporter）从指定的 MySQL 数据库中采集监控数据，将采集到的数据放入到时序数据库中

最后通过 Grafana 将监控数据进行可视化，以此来监控生产环境的机器性能以及 MySQL 性能

具体搭建过程，已经有网上也有许多资料，这里就不再重复了

BufferPool 生产优化经验

高并发场景下如何访问 BufferPool

如果在高并发场景下，大量线程需要操作 MySQL，也就是大量线程对 BufferPool 进行并发操作，在 BufferPool 中对这些共享变量（flush、free、lru 链表）操作，是需要通过 加锁来保证线程同步的，虽然是在内存中对 BufferPool 进行操作，但是将多个线程并发操作给转为了串行操作，还是有些降低性能的！

通过多个 BufferPool 优化高并发性能

上边在高并发场景中，只有一个 BufferPool 的话，可能多个线程在这一个 BufferPool 中排队对共享变量进行操作比较慢，在生产环境中，可以给 MySQL 设置多个 BufferPool 来提升性能！

如果部署 MySQL 的机器配置比较好，内存比较大，可以多给 BufferPool 分配一些内存，并且设置多个 BufferPool，通过一下两个参数设置：

innodb_buffer_pool_size # BufferPool 大小
innodb_buffer_pool_instances # BufferPool 个数

比如，可以给 BufferPool 分配 8G 内存，设置 4 个 BufferPool，那么每个的内存大小为 2G

这样原来只有 1 个 BufferPool 的情况下，多个线程并发操作在这一个 BufferPool 中进行排队

现在有 4 个 BufferPool 了，多个线程可以同时在这 4 个 BufferPool 中排队进行操作，性能成倍提升

因此在生产环境中，在机器配置足够的情况下，可以通过增加 BufferPool 的数量来提升 MySQL 的性能！

BufferPool 的大小可以动态变化吗？

其实是可以的，MySQL 通过 chunk 机制 来实现 BufferPool 大小动态调整的功能

chunk 其实就是 BufferPool 中的子单元，一个 BufferPool 由许多的 chunk 组成，每个 chunk 中包含了一系列的描述数据块和缓存页

chunk 的大小由：innodb_buffer_pool_chunk_size 来控制，默认大小为 128MB

那么假设现在有一个 BufferPool 大小为 2GB，那么这个 BufferPool 就是由 16 个 chunk 组成，一个 chunk 128MB

BufferPool 与 chunk 关系如下图：

当在 MySQL 运行期间，如果需要增大 BufferPool 的内存大小，那么只需要申请对应的 chunk 块，再将申请到的 chunk 分配给 BufferPool 就可以了

通过 chunk 机制可以极大提升 BufferPool 的灵活性

BufferPool 生产环境参数配置

我们知道在 MySQL 中，是将磁盘文件中的数据读取到内存的 BufferPool 中，在 BufferPool 中对数据进行操作的，那么从理论上来讲，如果部署 MySQL 的机器内存有 32 G，那么给 BufferPool 分配 30G 的大小会不会更好呢？

其实这样是行不通的，因为机器在启动的时候，操作系统就要占用几个 G 的内存，并且机器上运行的其他应用也是需要占用内存的

因此，一般建议将 BufferPool 的大小设置为机器内存的 60%！

BufferPool 数量的确定：

在确定 BufferPool 的大小之后，接下来可以确定 BufferPool 的数量了

通过计算公式确定：

BufferPool 总大小 = (chunk 大小 * Bufferpool 数量) * 2

那么假设部署 MySQL 的机器内存为 32 GB，那么 BufferPool 的大小应该设置为 32GB * 60% ≈ 20GB，chunk 大小为 128MB，代入上边公式，得到 BufferPool 数量为 （BufferPool 总大小 / 2） / chunk 大小，也就是 20GB/2/128MB = 16 个

因此 BufferPool 的数量就设置为 16 个

根据上边的经验值对 BufferPool 的总大小以及数量进行配置，可以尽可能的保证数据库的高性能

当配置过后，可以通过命令：show engine innodb status; 来查看配置是否生效！

表空间和数据区概念

这里说一下 MySQL 中表空间和数据区的概念

表空间 其实就是我们所创建的表，创建的表对应一个表空间，表空间的磁盘文件上存储着该表里每一行的数据

由于数据在磁盘中是以数据页为单位进行存储的，一个数据页大小为 16KB，表空间中的数据肯定是需要多个数据页进行存放的，因此表空间的磁盘文件中会包含多个数据页

但是表空间的磁盘文件中包含太多数据页不太好管理，因此设计了 数据区 的概念，一个数据区对应着 64 个连续的数据页，一个数据页 16KB，因此一个数据区的大小为 1MB，256 个数据区被划分为了一组

而每一组数据区的第一个数据区，会放一些描述数据

每一个数据区的前 3 个数据页，也会放一些描述数据

数据区结构如下图：

这里为什么要说一下数据区的概念呢？

因为 MySQL 的预读机制中使用到了数据区的概念， 主要有两种预读机制：

1. 线性预读：参数 innodb_read_ahead_threshold 默认值是 56，表示如果顺序的访问了一个区里的多个数据页，访问的数据页的数量超过了这个阈值，就会触发预读机制，把下一个相邻区中的所有数据页都加载到缓存里去

   查看默认值：show variables like 'innodb_read_ahead_threshold'

2. 随机预读：如果 Buffer Pool 里缓存了一个区里的 13 个连续的数据页，而且这些数据页都是比较频繁会被访问的，此时就会直接触发预读机制，把这个区里的其他的数据页都加载到缓存里去。性能不稳定，在 5.5 中已经被废弃，默认是 OFF

   show variables like 'innodb_random_read_ahead'

那么这两种预读机制都是判断一个数据区中加载了多少个数据页，再去判断是否触发预读机制，一个数据区默认情况下其实就是 64 个数据页

磁盘随机读写和顺序读写对MySQL性能的影响

当 MySQL 进行数据的增删改查时，会进行磁盘的 随机读写 和 顺序读写 ，接下来说一下这两种磁盘写操作会在什么时候执行，并且他们执行的性能差距！

部署 MySQL 机器的时候，磁盘 选用 SSD 固态硬盘 （机械硬盘）

SSD 随机读写速度 >> 机械硬盘

先来说一下磁盘的随机读写

# user 表 插入 删除 经过多次的插入、删除  -> MySQL 中的数据顺序就已经被打乱了 -> 随机读、随机写
user1
user2
user6

user5
...

# 顺序读写：不停追加

上边我们已经说到了表空间的概念，如果对表空间中的数据进行修改的话，那么最后落到磁盘上去就是磁盘的随机读写，因为表空间中的数据在执行了很多插入和删除操作之后，数据在磁盘中存储的位置并不是一直顺序存储的，因此只能使用 随机读写 去修改磁盘中的数据

因此随机读写的速度对 MySQL 的性能有着很大的影响，磁盘随机读的速度主要由两个指标进行观察：IOPS、响应延迟

(在部署 MySQL 之后，线上真正使用之前，需要对 MySQL 进行压测操作：MySQL 每秒可以承载最大的并发请求量 -> 观测该机器磁盘负载、内存负载、CPU负载..)

• IOPS：指的是每秒可以执行多少个磁盘随机读写操作。在对 MySQL 压测的时候，可以通过 dstat -r 来观察磁盘每秒可以执行的随机读写次数
• 响应延迟：假如磁盘支持每秒 200 个随机读写操作，响应延迟指的是每个操作的耗时，耗时越低，SQL 语句执行的性能就越高

因此，在部署 MySQL 的机器上推荐使用 SSD 固态硬盘，相比于机械硬盘来说有着更好的随机读写的性能！

接下来说一下磁盘的顺序读写

对 redo log、bin log、undo log 这些日志文件的读写是 磁盘的顺序读写 ，只要不停的向日志文件末尾追加数据就可以了，因此磁盘的顺序读写速度是是很快的，几乎可以和内存操作速度相当

并且还有 os cache 机制，先将写入日志的数据写到 os cache 中，这部分是在内存中的，之后再落入磁盘中去

而每次对 MySQL 的操作，都会记录到日志中去，因此日志中的顺序读写操作也决定着 MySQL 的性能！

而磁盘的顺序读写的性能最核心的指标就是 磁盘每秒读写数据的吞吐量 ，通过吞吐量的大小决定着磁盘写日志文件的性能，从而决定 SQL 语句的执行性能

由此可以看出磁盘速度极大程度上影响了 MySQL 语句的执行速度，因此部署 MySQL 的机器使用 SSD 固态硬盘会带来很大程度上的提升！

SQL 执行插入数据：

1、插入表数据 -> 随机读写

2、插入日志数据 -> 顺序读写

下图为磁盘随机读写、顺序读写、内存读写的性能对比：

总结一下，一条 SQL 的写入伴随着磁盘的随机读写和顺序读写，因此这两个的速度对 MySQL 的性能影响是很大的，所以建议在部署 MySQL 的机器上要安装 SSD 固态硬盘，可以很大程度上提升 MySQL 的性能！

生产案例：每一个月左右MySQL就会出现性能抖动

今天说一个生产环境的案例，MySQL 的性能抖动问题

具体现象也就是每隔一个月左右的时间，MySQL 就会出现性能急剧下降的情况，通过 linux 命令对问题进行排查之后，发现了每隔 30 天有一次 RAID 充放电的日志，那么推测性能急剧下降就是由于 RAID 充放电所导致的

生产环境中将数据库部署在了 64G 高配服务器上，并使用了 6 块硬盘组成了 RAID10 磁盘阵列

那么接下来说一下为什么 RAID 需要充放电，以及 RAID 充放电为什么会导致 MySQL 性能急剧下降：

RAID 是独立冗余磁盘阵列（将 RAID 的技术介绍放在了后边），作用是提供管理机器中的多块磁盘以及磁盘之间的数据冗余的功能

而使用多块磁盘组成 RAID 阵列时，一般会有一个 RAID 卡，这个卡中是有一块缓存区域的，当把 RAID 的缓存模式设置为 write back 时，所有写入到磁盘阵列中的数据，会先写入到 RAID 卡中的缓存区域，大幅度提升磁盘写的性能（和操作系统中的 os cache 类似）

这块缓存区域就相当于是内存，那么就带来了一个问题：如果机器突然断电，在 RAID 卡里缓存区域中的数据就会丢失，因此 RAID 卡一般都会有自己独立的 锂电池 或者 电容，如果机器突然断电，RAID 卡是基于锂电池运行的，因此不会导致数据丢失的问题

那么锂电池是会出现性能损耗的，一般来讲每隔 30-90 天都会自动对锂电池进行充放电一次，来延迟锂电池的寿命以及校准电池容量

在锂电池充放电的过程中，RAID 的缓存模式会由 write back 变为 write through，也就是通过 RAID 写数据的话，会直接写入磁盘，而不是先写入 RAID 卡的缓存区域中，因此在充放电过程中，RAID 的写性能会急剧下降！

那么问题原因找到了，如何解决呢？

其实就是将充放电的时间给控制一下就可以了，也就是手动充放电，通过脚本在凌晨业务低峰期的时候进行充放电，并且设置在充放电的时候禁止 RAID 缓存模式由 write back 变为 write through

这样子可以避免在业务高峰期期间，RAID 中的锂电池自动充放电，导致 MySQL 性能急剧下降，影响正常业务请求！

目前顶尖的互联网大厂的数据库服务器的 RAID 就是通过这个方案来避免性能抖动问题！

这里补充一下 RAID 相关的内容，RAID 分为了不同的技术方案，常见的有：

• RAID0：最简单的一种形式，没有提供冗余或错误修复能力，但是实现成本最低，只需要2块以上的硬盘即可提高整个磁盘的性能和吞吐量。

• RAID1：又称磁盘镜像，原理是把一个磁盘的数据镜像到另一个磁盘上，在不影响性能的情况下最大程度保证系统的可靠性和可修复性。

  常用的RAID组别—RAID5（随机写比较慢，因为需要计算奇偶校验位的数值，随机读和顺序读很快，RAID5比较适合读业务，最好使用在从（Slave）数据库服务器上）

• RAID5：又称分布式奇偶校验磁盘阵列，通过分布式奇偶校验块把数据分散到多个磁盘上，这样如果任何一个盘数据失效，都可以从奇偶校验块中重建。但是如果两块磁盘失效，则整个卷的数据都无法恢复。

• RAID10：又称分片的镜像，它是对磁盘先做RAID1之后，对两组RAID1的磁盘再做RAID0，所以对读写都有良好的性能，相对于RAID5重建起来更简单，也更快。

生产案例：数据库无法连接，Too many connections

在这个生产案例中：

数据库被部署在了一个很高配的服务器上，内存有 64GB

Java 应用部署在两台机器上，每个 Java 应用设置的连接池的最大数量为 200，也就是每个 Java 应用可以和 MySQL 建立 200 个连接，那么 2 个 Java 应用也就和 MySQL 建立 400 个连接，按理来说，这么高配的服务器接收 400 个 TCP 连接是没有问题的！

但是此时数据库出现了异常，报错 Too many connections，也就是数据库给你说连接已经太多了，无法再建立了

对于 64GB 的高配服务器来说，不可能连 400 个连接都没办法建立

造成这个问题的原因其实就是 Linux 服务器的文件句柄数量的设置，默认情况下是 1024，由于在服务器中还有其他应用占用了一部分文件句柄数量，从而导致留给 MySQL 建立连接的文件句柄数量不够，因此无法建立连接！

为什么文件句柄数量会限制 MySQL 的连接数量呢？

因为在 Linux 服务器中，一切皆文件，因此 MySQL 建立的连接都会作为文件存在，因此文件句柄数量限制着 MySQL 无法建立太多的连接！

修改文件句柄数量

vi /etc/security/limits.conf
# 添加如下行
* soft nofile 65535
* hard nofile 65535

修改之后，问题就可以解决了

重启当前 linux 服务器的会话（也就是将 xshell 关闭之后，再重新连接上），使用命令查看修改是否生效：

# 显示当前用户的最大文件描述符限制可以使用以下命令：
ulimit -n

在 Linux 中，为了避免某个进程耗光了机器的所有资源，因此默认情况下会对进程以及用户所打开的文件句柄数量进行限制的，因此在生产环境部署系统之后，需要对 Linux 内核参数做出一些调整，来让系统达到比较好的性能

就比如对于 Kafka 来说，如果不优化 Linux 内核参数的话，就会导致 Kafka 无法创建足够多的线程运行

生产案例：大量数据刷盘导致的数据库性能抖动问题优化

先说一下这次 数据库性能抖动的现象 ：当有数据进行更新、插入的时候，一般情况下可能几十毫秒就完成了，但是却出现了执行了几秒才完成的现象

造成这个问题的原因： 就是大量数据刷盘从而导致该 SQL 执行时间很长

我们知道，在 MySQL 中，对数据进行更新会先在 BufferPool 中更新，之后会将修改操作写入 redo log 日志文件中去

写 redo log 日志文件的过程就是，在 MySQL 的默认配置下，会有一个存储 redo log 的目录，里边有两个 redo log 磁盘日志文件，每个文件的大小为 48MB，那么当第一个 redo log 磁盘文件写满之后，会去写第二个 redo log 磁盘文件，当第二个 redo log 磁盘文件写满之后，又会去覆盖第一个 redo log 磁盘文件中的数据

那么当更新数据的时候，恰好碰到了第二个 redo log 磁盘文件写满的情况，此时会去覆盖第一个 redo log 磁盘文件中的数据，如果此时第一个 redo log 磁盘文件中有一些更新的数据还没有刷到磁盘中去的话，就会先将这部分数据页给刷到磁盘中去，如果这部分数据很多的话，就会导致花费比较长的时间

那么经过以上分析，性能抖动的原因找到了，如何解决呢？

解决思路就是：让 MySQL 尽量快地将数据从内存给刷到磁盘中去

让部署数据库机器的磁盘使用 SSD 固态硬盘，之后使用 fio 工具来对部署数据库的机器的 SSD 固态硬盘每秒钟最多能承载磁盘 IO 次数做一个测试，测试出来每秒最多的磁盘 IO 次数之后，可以将这个值设置给 MySQL 的参数：innodb_io_capacity，通过设置这个参数，可以让数据库以最大速率将缓存页刷到磁盘中去

可以使用 fio 工具来对部署数据库的机器的 SSD 固态硬盘每秒钟最多能承载磁盘 IO 次数做一个测试，测试出来每秒最多的磁盘 IO 次数之后，可以将这个值设置给 MySQL 的参数：innodb_io_capacity，尽可能让数据库以最大速率进行刷盘操作，

好友推荐系统索引设计实战

接下来根据具体的项目场景来对索引设计进行讲解

在这里以社交网站中的查询推荐好友功能为例，作用主要是根据一些条件查询用户

首先，最基本的，需要根据省份、城市、性别、年龄、爱好、登录时间（7 天内登录）进行筛选，我们先将 SQL 语句中的 where 条件写出来：

where provience = 'xxx' and city = 'xxx' and sex = 'xx' and age >= 18 and age <= 30 and hobby in ('xx', 'xx') 

• 索引设计的时机

注意在实际开发项目过程中，设计索引的时机其实并不是在建立数据库表之后就立即设置索引，而是等业务代码完成之后，再根据业务代码去设计索引，通过良好的索引设计让大多数查询都可以走索引！

那我们来看一下根据上边的 where 条件该来如何设计索引

• 联合索引初始设计

首先，索引的设计原则中，建议不要将选择性比较低的列作为索引，比如性别，但是我们的查询条件中无法避免性别的查询，因此可以将选择性比较低的列（性别）放在联合索引中比较靠后的位置

那么对查询推荐好友来说，基本都需要带上 provience、city、sex 三个列作为条件，因此设计的联合索引中，可以将这 3 个列放在比较靠前的位置，可以更好的遵循最左前缀原则，避免索引失效

因此，先将联合索引设置为：(provience, city, sex)

• age 列索引优化

接下来需要对年龄进行查询，而在索引规则中，如果有一个字段使用了范围查询，那么该字段后边的列就不会走索引了，所以一定要将范围查询的列放在索引的最后边

因此 SQL 语句和联合索引都需要进行优化，在 SQL 语句中，将 age 列的范围查询向后移，如下：

where provience = 'xxx' and city = 'xxx' and sex = 'xx' and hobby in ('xx', 'xx') and age >= 18 and age <= 30

联合索引设置为：(provience, city, sex, hobby, age)

• 对 7 天内登录用户的索引优化

接下来，在 SQL 中加上对登陆时间在 7 天以内的用户进行查询的，表中肯定有一个字段 last_login_time，要判断是否 7 天内登录，可以判断 last_login_time 是否大于（当前时间 - 7 天），那么就要进行函数计算，将当前时间减去七天，但是如果使用了函数计算，就无法走索引了，那么怎么办呢？

-- 这里 login_time 应该大于等于 7 天前的时间，这里使用伪代码来写了
where provience = 'xxx' and city = 'xxx' and sex = 'xx' and hobby in ('xx', 'xx') and age >= 18 and age <= 30 and login_time >= (current_time - 7days)

从 SQL 语句的角度来看，这个问题是无法解决了，但是可以从业务的角度来让这个字段走上索引

我们可以给表中单独增加一个字段 last_login_in_7_days 表示该用户在 7 天内是否登录，如果登录了值为 1，否则值为 0，这样判断用户 7 天内是否登录就可以走索引了

联合索引设置为：(provience, city, sex, hobby, last_login_in_7_days, age)

• 对 sex 索引列优化

假设，在查询的时候，没有对 sex 列进行条件查询，那么就会导致不符合最左前缀原则，从而导致 sex 后边的索引都无法使用，这种情况下该怎么办呢？

我们可以通过修改 SQL 语句来解决这个问题，如果不根据 sex 进行筛选，那就通过 in 语句，让 sex 列所有的枚举值都在 in 语句的条件中，让 SQL 可以走 sex 这一列的索引：

where provience = 'xxx' and city = 'xxx' and sex in ('male', 'female') and hobby in ('xx', 'xx') and last_login_in_7_days = 1 and age >= 18 and age <= 30 

这种优化方式不仅针对于 sex 列可以使用，而且针对爱好 hobby 列也可以使用，只要将所有枚举值拿到，再加上 in 语句就可以实现

• 使用辅助索引对其他少数查询进行优化

那么通过上边的联合索引 (provience, city, sex, hobby, last_login_in_7_days, age) 就可以抗下平常 80% 的查询了，那么还有剩下的 20% 查询，是不符合普遍情况的

比如只根据性别查询，并且根据用户评分进行排序，这其中涉及的 2 个字段为：sex、score

where sex = 'female' order by score

像这种情况的查询，是无法走我们上边设计的联合索引的（不符合最左前缀原则），通过 sex 进行筛选，会导致筛选之后还会有很多数据，如果不走索引的话进行磁盘排序，会导致性能很差，因此可以再设计辅助索引，让 order by 可以走 score 列的索引，使用索引排序，速度相比于不使用索引排序更快，因此对于这 20% 的非常规查询，可以设置辅助索引来应对！

比如，设计辅助索引为：(sex, score) ，通过 sex 进行过滤，之后根据 score 进行排序，可以通过索引进行排序，相比于不适用索引，性能提升很多！

总结一下，通过索引实战，可以看出大部分的查询都是比较常规的，我们可以去设计一个联合索引来覆盖到 80% 的查询，而对于非常规的 20% 的查询，可以通过设计一些辅助索引来优化性能！

亿级数据量表SQL调优实战

先说一下案例背景： 在电商平台中，有一个商品表，数据量级别上亿，有一天晚上突然出现大量的慢查询告警，导致每一个数据库连接执行慢查询耗费几十秒的时间，以至于数据库的连接被打满，无法建立新的连接，导致用户无法查询数据库中相关数据，相关的慢查询语句为：

select * from products where category = 'xx' and sub_category = 'xx' order by id desc limit xx, 10;

简化后的 SQL 语句如上，就是对商品表进行简单的查询，并且进行排序、分页处理

问题分析：

平常在运行的时候，并没有发现出现慢查询，因此 SQL 出现慢查询先查找代码有无提交，或者表中数据有无变化，经过查看，发现运营人员在商品分类表中新添加了几个分类，猜测和这个操作有关系！

那么先查看 SQL 的执行计划，找到慢查询出现的原因，对 SQL 进行 explain 之后，发现执行计划中 possible_key = index_category 表示要使用 index_category 这个索引，但是在 key 这一列的值为 PRIMARY，说明并没有用到 category 这个索引，而是使用了聚簇索引，并且 Extra 列的值为 Using where，因此分析慢查询出现的原因为： 在聚簇索引上扫描数据，并且使用 where 条件对数据进行过滤导致 SQL 执行过慢

那么尝试一下强制让 SQL 走我们自己定义的 index_category 这个索引，发现 SQL 耗时仅仅上百毫秒，慢查询的现象消失了，因此问题就被解决了

通过 force index 就可以让 MySQL 强制走我们自己定义的索引，改变 MySQL 的执行计划！

问题原因：

既然问题解决了，接下来还要分析一下问题出现的原因，为什么 MySQL 不使用我们自己定义的 index_category 索引，而是去扫描聚簇索引呢？

这就是 MySQL 内部的优化，它认为使用了 index_category 这个索引之后，只能查出来索引数据+主键值，而 SQL 语句中是 select *，因此在扫描二级索引之后，还要根据主键值去聚簇索引中回表查询其他列的数据

因此性能不如直接去聚簇索引中扫描，直接根据 id 倒序扫描，取出来 10 条数据就可以了，这样会更快一些

那么 MySQL 优化之后，为什么去聚簇索引中扫描反而出现了慢查询呢？

这就和上边说的运维人员在商品分类表中新添加了几个分类有关系了，由于这几个分类在商品表中没有对应数据，因此导致每一个用户来商品表查询这几个分类的商品时，都发现找不到对应的商品，因此每一次查询都会将整个聚簇索引给全部扫描一遍，上亿的数据，因此导致出现了慢查询！

所以说，这些问题在生产环境中都是无法避免的，因此一定要掌握 MySQL 中的执行计划，才可以通过执行计划找出 SQL 语句为什么执行速度太慢的原因！

十亿量级评论表SQL调优实战

先说一下案例背景：

在电商系统的评论表中，数据量非常大，达到了十亿量级，因此对评论的数据库进行分库分表处理，在分库分表之后，基本上单表的评论数据在百万级别左右

那么就存在着某些商品比较火爆，销量达到几百万，评论的数量也有几十万条，用户可能会查看该商品中的评论，不停的分页查询，以及跳转到某一页查看具体的好评、差评信息，SQL 语句如下：

select * from comments where product_id = xx and is_good_comment = 1 order by desc limit 10000, 20

上边 SQL 语句的意思就是从评论表中取出来 20 条数据，limit 中的 offset 为 10000 条，分页中每页 20 条数据，10000 条数据也就是 500 页，表明用户要查询第 501 页的好评数据

**索引建立情况：**评论表中的核心索引为 index_product_id ，那么你可能会有疑问，为什么不对 (product_id, is_good_comment) 两个字段做联合索引呢？不要着急，在下边会说这个问题

慢查询原因分析：

那么上边的 SQL 语句出现了慢查询的情况，SQL 执行耗时达到 1-2 秒，接下来我们根据已知信息分析一下为什么 SQL 耗时这么久

where 条件中使用 product_id 进行条件过滤，而 product_id 作为索引存在，因此 MySQL 是会走这个二级索引的，但是由于是 select *，因此走二级索引查不到全部的数据，因此还需要进行回表查询

那么上边 SQL 执行的流程是先通过 product_id 索引对评论数据进行筛选，就按火爆商品来举例，筛选出来几十万条评论数据，那么接下来还要拿这几十万条评论数据回表查询，回表时通过 is_good_comment 字段筛选数据，筛选完数据之后可能还有十万条数据，此时再根据 id 字段进行倒序排序，这里又是基于临时磁盘文件进行排序，比较耗时，排序完毕之后，再根据偏移量取出当前分页的数据

因此，通过分析，这条 SQL 慢在了两个地方，第一是拿了几十万条数据进行回表查询；第二是对十万条数据进行磁盘排序

SQL 优化：

这里是有三个优化方案的：

• 第一个方案是对 (product_id, is_good_comment) 建立联合索引，product_id 值是唯一的，但是 is_good_comment 字段的值就几个，如果建立联合索引会导致数据分布的不均匀，并且可能在用户评论之后，会有后台定时任务来对 is_good_comment 字段更新，更新时会导致索引节点移动，因此 is_good_comment 不适合用于建立联合索引，所以没有使用这种方案
• 第二个方案是使用 force index 强制 MySQL 走聚簇索引，但是一般不建议使用 force index，因此没有使用这种方案
• 第三个方案是改写 SQL 语句，本次优化使用的就是这种方案

改写后的 SQL 语句如下：

select * from comments a, (select id from comments where product_id = xx and is_good_comment = 1 order by id desc limit 10000, 20) b where a.id = b.id;

通过执行计划可以看到，上边的 SQL 语句会先执行括号内的子查询，执行子查询的时候 MySQL 就会认为直接走聚簇索引比较快一些，根据 id 倒序扫描数据，在扫描过程中根据 product_id 和 is_good_comment 两个字段进行数据的过滤，此时过滤出来了十万条数据，再通过 limit 取出来我们所需要的 20 条数据

接着就会拿这 20 条数据再去聚簇索引中找到完整的数据就可以了，优化之后 SQL 执行耗时为几百毫秒，流程如下：

总结一下： 昨天我们说的 SQL 调优案例是强制让 MySQL 走二级索引来优化，但是这一次是我们通过修改 SQL 语句来让 MySQL 走聚簇索引来优化，因此在 MySQL 中对 SQL 的优化方式并不是固定的，也没有通用的方法，因此一定要掌握执行计划，了解 SQL 底层如何处理数据，才可以很快找到慢查询的原因！

千万级数据删除导致的慢查询SQL调优实战

先说一下案例背景：

刚开始，线上系统收到了大量的慢查询告警，检查之后，发现慢查询的都是一些比较简单的 SQL 语句，基本上都是单行查询，因此理论上性能应该是极高的，但是却变成了慢查询，因此考虑可能不是 SQL 语句性能的问题，而是 MySQL 服务器负载过高从而导致 SQL 语句执行过慢！

MySQL 服务器负载过高所导致的问题：

• 如果 MySQL 的 磁盘 IO 负载过高 ，那么此时你的 SQL 语句来执行的时候，那么 MySQL 可能就顾不上你的磁盘请求了，导致你的 SQL 执行速度过慢
• 如果 MySQL 的 网络 IO 负载过高 ，会导致你的应用与 MySQL 建立连接以及数据发送的速度过慢，从而导致 SQL 执行速度过慢的现象出现
• 如果 MySQL 的 CPU 负载过高 也会导致这种情况出现，CPU 负责的任务太多了，导致轮不到你的 SQL 执行任务，从而导致 SQL 执行过慢

1、因此，第一步就是先排查 MySQL 服务器的问题

那么在查看服务器负载之后，发现磁盘 IO、网络 IO、内存、CPU 负载都处于正常水平，并没有出现高负载的情况，因此慢查询的原因应该不是服务器负载过高所导致的

2、第二步是对 SQL 语句的执行耗时进行分析

使用 MySQL 中的 profilling 工具来对 SQL 语句进行性能分析，profilling 工具记录了 SQL 语句的各种资源使用情况，包括 CPU、IO、上下文切换、内存使用等情况

• 先通过命令开启会话级别的 profilling 功能，之后 MySQL 就会记录 SQL 语句的 profilling 信息了：

SET profiling = 1;

• 分析 SQL 语句

使用 SHOW PROFILES 命令可以查看当前会话中所有已执行的SQL语句的Profiling信息

要查看特定SQL语句的详细信息，可以使用 SHOW PROFILE 命令，指定相应的查询ID（Query_ID）

# 查看所有 SQL 语句的 profilling 信息以及 query id
show profiles;
# 查看指定 SQL 语句信息
show profile cpu, block io for query [query_id]

当分析了 SQL 语句的 profilling 信息之后，发现它的 Sending Data 耗时最高，SQL 执行耗时 1s 左右，Sending Data 就占用了几乎 1s，因此 SQL 执行速度慢的原因找到了，是因为 Sending Data

3、进一步分析

但是仅仅凭借 Sending Data 还找不出为什么 SQL 执行这么慢，因此使用了 show engine innodb status 命令来查看 InnoDB 存储引擎当前的状态，此时发现 history list length 值特别大，达到了上万的级别

那么通过查询相关资料发现 history list length 值特别大表明数据库中有着大量事务正在执行，大量事务在执行的时候，会去构建 MVCC 的 undo log 版本链，如果大量事务一直不结束，就会导致这个 undo log 版本链过长，最后导致 history list length 值特别大

因此猜测原因可能与 MySQL 中正在执行的大量事务有关

4、找到原因

那么经过排查，发现后台在跑一个定时任务，这个定时任务开启了一个事务去删除上千万条数据，并且这个事务运行的时间也很长，因此导致这个事务在运行期间，产生了大量的 undo log 版本链

那么此时其他事务来查询数据的时候，会把这些上千万条删除的数据都扫描一遍，因为这些数据是在记录版本链中的，而查询的话通过 MVCC 机制是需要去扫描记录版本链，因此 SQL 慢查询的原因就找到了：长事务中大量的删除操作，导致记录版本链过长，其他事务来查询数据时，需要扫描很多的记录版本链，导致普通的查询都会变得非常慢！

5、解决问题

针对这种问题，直接将删除很多数据的长事务给 kill 掉就可以了

之后要避免在业务高峰期的时候运行大量删除数据的语句，放在业务低峰期去执行，比如凌晨，这样会好一些

MySQL 中的分库分表方案解决方案

如果在互联网公司中，可能有些表的数据规模膨胀速度很快，比如用户、订单数据，可能达到几千万甚至上亿级别的数据，那么数据量规模过大带来的性能负担是很重的，主要问题为：

• 数据量大的问题 ：MySQL 索引底层数据结构使用 B+ 树，如果单表数据量过大，会导致 B+ 树层级过深，磁盘 IO 次数增加，降低索引性能
• 写性能瓶颈的问题 ：在单机、主从架构中，如果是并发访问量很高的情况，尤其是写请求的 QPS 很高的情况下，很容易达到 MySQL 的性能瓶颈，只靠简单的主从架构，可以提升高可用和读性能，但是无法提升 MySQL 的写性能

因此需要对数据进行分片处理，来更好的解决这两方面的问题

如何分库分表？

对数据库进行分库分表时，是需要数据库的中间件支持的，比较常用的是 Sharding-Sphere ，是 Apache 开源的分布式数据库生态系统，有两个产品目前广泛使用： ShardingSphere-JDBC 、 ShardingSphere-Proxy

• ShardingSphere-JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。 它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架
• ShardingSphere-Proxy 定位为透明化的数据库代理端，通过实现数据库二进制协议，对异构语言提供支持。 目前提供 MySQL 和 PostgreSQL 协议，透明化数据库操作，对 DBA 更加友好。

什么情况下需要分库分表呢？

比如说在互联网公司中，这里以电商场景举例，毕竟电商的用户量相对来说是比较大的

对于电商中的用户表来说，一般中小型的互联网公司，用户量可能就已经达到了几千万的级别，从而导致用户表的索引层次太深，查询能下降

一般建议 MySQL 中单表的数据量不要超过 1000 万，基本上单表数据量在 100 万以内，性能上不会出现太大的问题

因此对于用户量达到了几千万级别的用户表，需要进行分库分表！

将千万级别的用户数据分散到 100 个表中，user_001、user_002、...、user_100 一百个表，再将这 100 张表分散到多台服务器中去

至于具体分配多少台服务器主要看两个方面：

• 看该表的压力有多大，用户表的话，压力一般不会太大，因为基本上不会出现同时间大量用户登陆注册的情况
• 看该表的数据量有多大，有一个参考标准就是 1 亿条数据的总大小在 1GB - 几 GB 之间，该用户表中几千万的数据最多也就几个 GB，因此该量级的数据对服务器的压力并不大

所以说将这 100 个表分配到 2 台服务器即可，每台服务器上安装一个数据库，将 100 张表均匀分配在 2 台服务器上，一般指定表中的某一个字段来分，比如用户表中的 user_id 字段，对 user_id 进行哈希后，路由到对应的表中去

分库分表后的适配问题

当进行分库分表之后，相应的应用端会出现一些业务上的问题，需要进行分库分表的适配处理，就比如：

• 当根据用户表的 user_id 进行分库分表，那么当用户登录的时候，直接根据 user_account 进行登录，此时是没有 user_id 的值的，这时该如何查找对应的库和表呢？

常规解决方案的话是建立一个映射表（user_account -> user_id），通过 user_account 对这个映射表进行分库分表处理，之后就可以根据 user_account 找到映射表中对应的 user_id，再通过 user_id 去用户表中查询数据

虽然这种方式会带来一定的性能损耗，但是为了解决分库分表的问题，只能付出一定的性能代价

添加映射表之后，通过 account 查找用户数据如下图红线：

分库分表后更复杂的查询问题

上边只说了简单的根据 user_account 查询用户数据所带来的问题，而实际场景中还有还需要根据用户名、手机号、性别等查询条件进行复杂查询，对于这类复杂查询来说，也没有太好的办法，只能对用户表数据的 binlog 进行监听，将要搜索的字段同步到 ES 中去，建立好搜索的索引，之后通过 ES 进行复杂查询，定位到对应的 user_id，再根据 user_id 去分库分表架构中查询对应的用户数据

分库分表中表数量的建议

建议在前期进行分库分表的时候，将表的数量分的多一些，否则当分库分表之后，再运行一两年又到达了性能上线之后，再重新添加表的数量，修改路由规则、数据迁移非常麻烦，所以一开始就建议多分一些表

比如表中上千万的数据，如果刚分完之后，每个表的数据为 100w，经过几年，又会达到近千万的数据，很快达到性能上线，因此可以多分一些表，分表之后达到单表 10w 左右的数据，通过观察数据的增长量，确保 10-20 年之内不会打到表中性能上限即可

所以说宁可多分一些表，也不要后期表不够了，再去进行扩容、数据迁移、路由修改等一系列复杂操作吗

至此，整套分库分表方案就说完了，基本上就包含了上边的几块内容

订单系统中的分库分表解决方案

使用分库分表的场景，除了之前我们说的用户表，还有订单表也需要进行分库分表，接下来我们对订单数据量分析一下

就比如在中小型的电商公司来说，假设注册用户量达到 1000w，那么根据二八原则，电商系统的 80% 访问量都是由 20% 的用户所生成，因此日活用户大概为 200w，但是像 20% 的日活比例就已经算非常高了，一般公司也达不到，因此就按 10% 来算，日活用户在 100w 左右

那么如果要生成订单的话，需要用户购买商品，这个比例就更低了，就按日活用户的 1% 来算，每天生成 1w 的订单量，那么一年生成 360w 的订单量，只需要 3 年订单表就会成为千万级别的大表

而在 MySQL 中，一般建议将单表数据量控制在百万级别，不要超过千万，如果 100w 以内不会出现性能问题，因此要对订单表进行分库分表设计

对订单表分库分表所带来的问题

订单表分库分表时，我们要考虑如何对分库分表之后的数据进行查询

比如：

• 用户对自己订单的分页查询
• 后台系统对所有订单数据进行复杂的分页查询

这里以这两个问题为例，说一下在分库分表中如何解决

首先对第一个问题来说，用户对自己订单的分页查询，还是和之前一样，按照（user_id, order_id）做一个映射表，将这个映射表也给分库分表

那么当用户需要分页查询自己的订单数据时，先根据 user_id 哈希映射定位到对应的映射表，获取自己所有订单的 order_id，再拿 order_id 去进行分页，分页之后，再根据 order_id 进行哈希映射，定位到对应的表中，获取完整的订单数据

那么这是用户对订单分页数据的简单查询，如果用户也对订单数据进行状态、订单商品名称查询该如何筛选呢？

在映射表中放更多的字段就可以了，就比如将映射表由（user_id, order_id）改为（user_id -> order_id, order_status, product_name）

那么如果用户需要根据订单状态分页查询的话，只需要根据 user_id 先取出自己的所有映射数据，再根据映射数据中的 order_status、product_name 进行筛选匹配，并且进行分页，最后将得到的 order_id 去分库分表中获取完整的数据

对于第二个问题，后台系统对所有订单数据进行复杂的分页查询，一般就是根据订单中的多条数据进行组合查询，比如根据订单的完成状态、完成时间、订单类型等多个条件进行复杂查询，可以将订单数据的搜索条件都同步到 ES 里，再使用 ES 来进行复杂搜索，找出来一批 order_id，再通过 order_id 去分库分表中找到对应的数据

所以，分库分表带来查询的问题的话，一般就是两种解决方案：

• 建立映射表，对映射表也分库分表
• 使用 ES 进行复杂查询