MySQL单表过大、主从模式、同步模式优化原理

MYSQL单表数据达2000万性能严重下降?

前言

在中国互联网技术圈流传着这么一个说法：MySQL 单表数据量大于 2000 万行，性能会明显下降。事实上，这个传闻据说最早起源于百度。具体情况大概是这样的，当年的 DBA 测试 MySQL性能时发现，当单表的量在 2000 万行量级的时候，SQL 操作的性能急剧下降，因此，结论由此而来。然后又据说百度的工程师流动到业界的其它公司，随之也带去了这个信息，所以，就在业界流传开这么一个说法。

再后来，阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。对此，有阿里的黄金铁律支撑，所以，很多人设计大数据存储时，多会以此为标准，进行分表操作。

那这个说法到底有没有理论依据的支撑呢，首先这个性能问题可以说有很多因素组成，比如说硬件以及MYSQL本身的参数设置等等。那今天我们可以从MySQL的索引结构来说说这件事。

InnoDB索引数据结构

一个问题？

**InnoDB一棵B+树可以存放多少行数据？这个问题的简单回答是：约2千万。**为什么是这么多呢？因为这是可以算出来的，要搞清楚这个问题，我们先从InnoDB索引数据结构、数据组织方式说起。

我们都知道计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节

而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k

而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

文件系统中一个文件大小只有1个字节，但不得不占磁盘上4KB的空间。

innodb的所有数据文件（后缀为ibd的文件），他的大小始终都是16384（16k）的整数倍。

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？

假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

如果数据库只按这样的方式存储，那么如何查找数据就成为一个问题，因为我们不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样太慢了。所以人们想了一个办法，用B+树的方式组织这些数据。如图所示：

我们先将数据记录按主键进行排序，分别存放在不同的页中（为了便于理解我们这里一个页中只存放3条记录，实际情况可以存放很多），除了存放数据的页以外，还有存放键值+指针的页，如图中page number=3的页，该页存放键值和指向数据页的指针，这样的页由N个键值+指针组成。当然它也是排好序的。这样的数据组织形式，我们称为索引组织表。现在来看下，要查找一条数据，怎么查？

如select * from user where id=5;

这里id是主键,我们通过这棵B+树来查找

首先找到根页，你怎么知道user表的根页在哪呢？其实每张表的根页位置在表空间文件中是固定的，即page number=3的页（这点我们下文还会进一步证明），找到根页后通过二分查找法，定位到id=5的数据应该在指针P5指向的页中，那么进一步去page number=5的页中查找，同样通过二分查询法即可找到id=5的记录：

| 5 | zhao2 | 27 |

现在我们清楚了InnoDB中主键索引B+树是如何组织数据、查询数据的，我们总结一下：

1、InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。

2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

B+树

那么回到我们开始的问题，通常一棵B+树可以存放多少行数据？

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：

根节点指针数*单个叶子节点记录行数

上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

那么现在我们需要计算出非叶子节点能存放多少指针？

其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，16K是页最小的占用磁盘单元，即（16*1024）/14=1170 。

那么可以算出一棵高度为2的B+树，能存放1170*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放 ：1170117016=21902400 条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

怎么得到InnoDB主键索引B+树的高度？

上面我们通过推断得出B+树的高度通常是1-3，下面我们从另外一个侧面证明这个结论。在InnoDB的表空间文件中，约定page number为3 的代表主键索引的根页，而在根页偏移量为64 的地方存放了该B+树的page level。如果page level为1，树高为2，page level为2，则树高为3。即B+树的高度=page level+1；下面我们将从实际环境中尝试找到这个page level。

lineitem表的数据行数为600多万，B+树高度为3，customer表数据行数只有15万，B+树高度也为3。可以看出尽管数据量差异较大，这两个表树的高度都是3，换句话说这两个表通过索引查询效率并没有太大差异，因为都只需要做3次IO。那么如果有一张表行数是一千万，那么他的B+树高度依旧是3，查询效率仍然不会相差太大。

region表只有5行数据，当然他的B+树高度为1。

Sharding Sphere分库分表

Sharding-JDBC

Sharding-JDBC是比较常用的一个组件，它定位的是一个增强版的JDBC驱动，简单来说就是在应用端来完成数据库分库分表相关的路由和分片操作，也是我们本阶段重点去分析的组件。

我们在项目内引入Sharding-JDBC的依赖，我们的业务代码在操作数据库的时候，就会通过Sharding-JDBC的代码连接到数据库。也就是分库分表的一些核心动作，比如SQL解析，路由，执行，结果处理，都是由它来完成的，它工作在客户端。

Sharding-JDBC的相关概念说明

前面我们通过两种方式演示了Sharding-JDBC的分库分表功能的用法，其实，从这个层面来说，Sharding-JDBC相当于增强了JDBC驱动的功能，使得开发者只需要通过配置就可以轻松完成分库分表功能的实现。

在Sharding-JDBC中，有一些表的概念，需要给大家普及一下，逻辑表、真实表、分片键、数据节点、动态表、广播表、绑定表。

逻辑表

逻辑表可以理解为数据库中的视图，是一张虚拟表。可以映射到一张物理表，也可以由多张物理表组成，这些物理表可以来自于不同的数据源。对于mysql, Hbase和ES，要组成一张逻辑表，只需要他们有相同含义的key即可。这个key在mysql中是主键，Hbase中是生成rowkey用的值，是ES中的key。

在前面的分库分表规则配置中，就有用到t_order这个逻辑表的定义，当我们针对t_order表操作时，会根据分片规则映射到实际的物理表进行相关事务操作，如图7-9所示，逻辑表会在SQL解析和路由时被替换成真实的表名。

spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds-$->{0..1}.t_order_$->{0..1}

广播表

广播表也叫全局表，也就是它会存在于多个库中冗余，避免跨库查询问题。

比如省份、字典等一些基础数据，为了避免分库分表后关联表查询这些基础数据存在跨库问题，所以可以把这些数据同步给每一个数据库节点，这个就叫广播表，如图7-10所示。

绑定表

我们有些表的数据是存在逻辑的主外键关系的，比如订单表order_info，存的是汇总的商品数，商品金额；订单明细表order_detail，是每个商品的价格，个数等等。或者叫做从属关系，父表和子表的关系。他们之间会经常有关联查询的操作，如果父表的数据和子表的数据分别存储在不同的数据库，跨库关联查询也比较麻烦。所以我们能不能把父表和数据和从属于父表的数据落到一个节点上呢？

比如order_id=1001的数据在node1，它所有的明细数据也放到node1；order_id=1002的数据在node2，它所有的明细数据都放到node2，这样在关联查询的时候依然是在一个数据库，如图7-11所示

Sharding-JDBC中的分片策略

Sharding-JDBC内置了很多常用的分片策略，这些算法主要针对两个维度

数据源分片
数据表分片

Sharding-JDBC的分片策略包含了分片键和分片算法；

分片键，用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的订单主键的尾数取模分片，则订单主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。

分片算法，就是用来实现分片的计算规则。

Sharding-JDBC提供内置了多种分片算法，包含四种类型分别是

自动分片算法
标准分片算法
复合分片算法
Hinit分片算法

自动分片算法

自动分片算法，就是根据我们配置的算法表达式完成数据的自动分发功能，在Sharding-JDBC中提供了五种自动分片算法

取模分片算法
哈希取模分片算法
基于分片容量的范围分片算法
基于分片边界的范围分片算法
自动时间段分片算法

取模分片算法

最基础的取模算法，它会根据分片字段的值和sharding-count进行取模运算，得到一个结果。

哈希取模分片算法

和取模算法相同，唯一的区别是针对分片键得到哈希值之后再取模

分片容量范围

分片容量范围，简单理解就是按照某个字段的数值范围进行分片

标准分片算法

标准分片策略（StandardShardingStrategy），它只支持对单个分片健（字段）为依据的分库分表，Sharding-JDBC提供了两种算法实现

行表达式分片算法

类型：INLINE
使用 Groovy 的表达式，提供对 SQL 语句中的 = 和 IN 的分片操作支持，只支持单分片键。 对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的 Java 代码开发，如: t_user_$->{u_id % 8} 表示 t_user 表根据 u_id 模 8，而分成 8 张表，表名称为 t_user_0 到 t_user_7

时间范围分片算法

和前面自动分片算法的自动时间段分片算法类似。

类型：INTERVAL

可配置属性：

MySQL主从机制原理

前言

随着日益增长的访问量，单台数据库的应接能力已经捉襟见肘。因此采用主库写数据，从库读数据这种将读写分离开的主从架构便随之衍生了出来。

在生产环境中，常见的主从架构有很多种，在这里给大家介绍几种比较常见的架构模式。

主从复制原理

了解了主从的基本架构及相关配置后，下面就要进入正题了。

对于主从来说，通常的操作是主库用来写入数据，从库用来读取数据。这样的好处是通过将读写压力分散开，避免了所有的请求都打在主库上。同时通过从库进行水平扩展使系统的伸缩性及负载能力也得到了很大的提升。

但是问题就来了，读从库时的数据要与主库保持一致，那就需要主库的数据在写入后同步到从库中。如何保持主库与从库的数据一致性，主库又是通过什么样的方式将数据实时同步到从库的？

基本原理

Mysql 中主从复制时有两个很重要的日志文件：

binlog（二进制日志文件）
relay log（中继日志文件）

在主从同步的过程中，主库会将所有的操作事件记录在 binlog 中，从库通过开启一个 I/O 线程保持与主库的通信，并在一定时间间隔内探测 binlog 日志文件是否发生改变。

如果 binlog 日志发生了变化，主库生成一个 binlog dump 线程向从库 I/O 线程传送 binlog。从库上的 I/O 线程将 binlog 复制到自己的 relay log 中。最终由从库中的 SQL 线程读取 relay log 中的事件重放到从库上。

主从延迟原因

上面的流程我们已经知道了主从复制的相关过程了，但是主库有更新就会同步从库，那为什么会出现主从延迟的情况呢？

随机重放

Mysql 主库中写 binlog 的操作是顺序写的，之前我们提到过，磁盘的顺序读写速度是很快的。同样的，从库中的 I/O 线程操作日志的速度效率也是很高的。但是别忘了，还有一个 SQL 线程来进行数据重放，而重放的过程是随机写盘的。到这里你应该就明白了吧，某一时刻 relay log 里的数据来不及重放进从库，就会产生主从延迟的情况。

主库并发高

知道了从库中 SQL 线程的重放情况，对于主库并发高导致主从延迟肯定就不难理解了。某一时刻，大量写请求打到主库上，意味着要不断对 binlog 进行写入，此时从库中的 SQL 线程就会应接不暇，自然会产生主从延迟。

锁等待

对于 SQL 单线程来说，当遇到阻塞时就会一直等待，直到执行成功才会继续进行。如果某一时刻从库因为查询产生了锁等待的情况，此时只有当前的操作执行完成后才会进行下面的操作，同理也就产生了主从延迟的情况。

主从延迟处理

知道了主从延迟的原因，接下来我们看看如何来进行处理。

并行复制

既然 SQL 单线程进行重放时速度有限，那么能不能采用多线程的方式来进行重放呢？MySQL 5.6 版本后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放，这样就解决了主从延迟的问题。

降低主库并发

你可能会说了，我现在用的低版本的数据库，也没法升版本啊，那我怎么整。对于主库并发高的情况，这种方式你只能通过控制并发来解决延迟了，多用用 Redis。

读主库

这种情况你肯定不陌生，对于一些实时性要求比较高的数据，你总不能读从库去拿吧，万一延迟个大半天，你不得贡献自己的年终奖啊。

总结

主从复制原理

主从复制中有两个很重要的日志文件，binlog和relay log，分别位于主库与从库中。其中 binlog 是主从复制的基础，通过将操作事件写入 binlog 通过 I/O 线程传送至从库进行同步。

主从延迟原因

从库中 SQL 线程重放的过程是随机写盘的，并且 SQL 线程是单线程的，因此数据来不及重放的话就会导致主从延迟。
主库并发高会导致写操作不断写入 binlog，对于 SQL 线程说可能会应接不暇，也会产生主从延迟。
重放过程中如果遇到锁等待也是产生延迟的原因之一。

主从延迟处理

MySQL 5.6版本以后通过并行复制的方式来解决 SQL 单线程产生的主从延迟问题。对于低版本来说，可以通过降低主库的并发来解决。如果对数据实时性要求比较严格的话，可以通过读主库来达到目的。+

MySQL三种同步模式优劣

异步复制

异步复制是 mysql 默认的同步方式，在 master 为 slave 开通账号密码、ip授权之后，slave 可以从 master 进行数据同步，主要依赖的是 master 的 binlog 日志。

slave 会启动两个线程，IO Thread 和 SQL Thread：

IO Thread 负责从 master 拉取 binlog 日志，并写入 relay 中继日志
SQL Thread 负责将 relay 中继日志中的变更进行重放，更新数据来达到跟 master 保持数据一致的目的
这个过程中，slave 通过IO线程拉取 binlog ， master 无需关注是否有 slave 需要同步，只做自己的事情，整个复制过程都是异步完成的，这个就是异步复制。

异步复制的优势是性能好，缺点是数据的安全性比较差

在某一刻主从之间的数据差异可能较大，主机挂掉之后从机接管，可能会丢失一部分数据。

半同步复制

master 更新操作写入 binlog 之后会主动通知 slave，slave 接收到之后写入 relay log 即可应答，master只要收到至少一个ack应答，则会提交事务。

可以发现，相比较于 异步复制 ，半同步复制 需要依赖至少一个 slave 将 binlog 写入 relay log 才行，在性能上有所降低，但是可以保证至少有一个 slave(从数据库)跟 master(主数据库) 的数据是一致的，数据的安全性提高。

对于数据一致性要求高的场景，可以采用半同步复制的同步策略，比如主库挂掉时，准备接管的那一个从库，对数据的一致性要求很比较高。

半同步复制的优点是数据的安全性好，缺点是性能比异步复制稍低

全同步复制

全同步复制 跟 半同步复制 的区别是，全同步复制必须收到所有 slave(从数据库) 的 ack，才会提交事务。

master(主数据库) 的事务提交依赖于后面所有的 slave(从数据库)，这样一来性能就会明显得下降，除非是对所有 slave(从数据库) 数据一致性要求非常高的场景，否则我们一般不采用这种策略。

全同步复制的数据一致性最好，但是性能也是最差的